Birden çok kitaptan farklı başlıklara sahip tablolar oluşturun

Sorunun formülasyonu

Bir klasörde birkaç dosyamız var (örneğimizde – genel durumda 4 adet – istediğiniz kadar) Raporlar:

İçeride, bu dosyalar şöyle görünür:

Burada:

• İhtiyacımız olan veri sayfası her zaman denir Fotoğraflar, ancak çalışma kitabında herhangi bir yerde olabilir.
  
• Sayfanın ötesinde Fotoğraflar Her kitabın başka sayfaları olabilir.
• Veri içeren tablolar farklı sayıda satıra sahiptir ve çalışma sayfasında farklı bir satırla başlayabilir.
• Farklı tablolardaki aynı sütunların adları farklı olabilir (örneğin, Miktar = Miktar = Miktar).
• Tablolardaki sütunlar farklı bir düzende düzenlenebilir.

Görev: sayfadaki tüm dosyalardan satış verilerini toplayın Fotoğraflar daha sonra üzerinde bir özet veya başka herhangi bir analiz oluşturmak için ortak bir tabloya dönüştürün.

Adım 1. Bir sütun adları dizini hazırlama

Yapılacak ilk şey, sütun adları ve bunların doğru yorumlanması için tüm olası seçenekleri içeren bir referans kitabı hazırlamaktır:

Sekmedeki Tablo olarak biçimlendir butonunu kullanarak bu listeyi dinamik bir “akıllı” tabloya dönüştürüyoruz. Ana Sayfa (Ana Sayfa — Tablo Olarak Biçimlendir) veya klavye kısayolu Ctrl+T ve komutla Power Query'ye yükleyin Veriler – Tablodan/Aralıktan (Veri — Tablodan/Aralıktan). Excel'in son sürümlerinde, olarak yeniden adlandırıldı. yaprakları ile (sayfadan).

Power Query sorgu düzenleyici penceresinde, geleneksel olarak adımı sileriz Değişen Tip ve butona tıklayarak bunun yerine yeni bir adım ekleyin f_xformül çubuğunda (görünmüyorsa, sekmede etkinleştirebilirsiniz) Değerlendirme) ve formülü yerleşik Power Query dili M'ye girin:

=Table.ToRows(Kaynak)

Bu komut, önceki adımda yükleneni dönüştürür Kaynak referans tablosunu, her biri sırayla bir çift değer olan iç içe listelerden (Liste) oluşan bir listeye dönüştürün Oldu bir satırdan:

Bu tür verilere biraz sonra, yüklenen tüm tablolardan üstbilgileri toplu olarak yeniden adlandırırken ihtiyacımız olacak.

Dönüştürmeyi tamamladıktan sonra komutları seçin Ana Sayfa — Kapat ve Yükle — Kapat ve Yükle… ve ithalat türü Sadece bir bağlantı oluşturun (Ana Sayfa — Kapat&Yükle — Kapat&Yükle… — Yalnızca bağlantı oluştur) ve Excel'e geri dönün.

Adım 2. Tüm dosyalardan her şeyi olduğu gibi yüklüyoruz

Şimdi tüm dosyalarımızın içeriğini klasörden yükleyelim – şimdilik olduğu gibi. Takım seçimi Veri – Veri al – Dosyadan – Klasörden (Veri — Veri Al — Dosyadan — Klasörden) ve sonra kaynak kitaplarımızın bulunduğu klasör.

Önizleme penceresinde, dönüştürmek (Dönüştür) or değişim (Edit):

Ve sonra indirilen tüm dosyaların içeriğini genişletin (İkili) sütun başlığında çift oklu düğme içerik:

İlk dosya örneğinde Power Query (Vostok.xlsx) bize her çalışma kitabından almak istediğimiz sayfanın adını soracak - seçin Fotoğraflar ve Tamam'a basın:

Bundan sonra (aslında), sonuçları sol panelde açıkça görülebilen, kullanıcı için açık olmayan birkaç olay meydana gelecektir:

1. Power Query, klasörden ilk dosyayı alacaktır (bunu alacağız Vostok.xlsx — görmek Dosya örneği) örnek olarak verir ve içeriğini bir sorgu oluşturarak içe aktarır Örnek dosyayı dönüştür. Bu sorgunun aşağıdaki gibi bazı basit adımları olacaktır. Kaynak (dosya erişimi) Navigasyon (sayfa seçimi) ve muhtemelen başlıkları yükseltmek. Bu istek yalnızca belirli bir dosyadan veri yükleyebilir Vostok.xlsx.
   
2. Bu isteğe bağlı olarak, onunla ilişkili işlev oluşturulacaktır. Dosyayı dönüştür (karakteristik bir simgeyle gösterilir f_x), burada kaynak dosya artık bir sabit değil, değişken bir değer olacak - bir parametre. Böylece, bu işlev, içine kaydırdığımız herhangi bir kitaptan bir argüman olarak veri çıkarabilir.
   
3. İşlev, sütundan her dosyaya (İkili) sırayla uygulanacaktır. içerik – adım bundan sorumludur Özel işlevi çağır dosya listesine bir sütun ekleyen sorgumuzda Dosyayı dönüştür her çalışma kitabından içe aktarma sonuçlarıyla:

   

4. Fazla sütunlar kaldırılır.
5. İç içe geçmiş tabloların içeriği genişletilir (adım Genişletilmiş tablo sütunu) – ve tüm kitaplardan veri toplamanın nihai sonuçlarını görüyoruz:

   
   

Adım 3. Zımparalama

Önceki ekran görüntüsü, “olduğu gibi” doğrudan montajın düşük kalitede olduğunu açıkça göstermektedir:

• Sütunlar ters çevrilir.
• Birçok ekstra satır (boş ve sadece değil).
• Tablo başlıkları başlık olarak algılanmaz ve verilerle karıştırılır.
  

Tüm bu sorunları çok kolay bir şekilde çözebilirsiniz – sadece Örnek Dosyayı Dönüştür sorgusunda ince ayar yapın. Üzerinde yaptığımız tüm ayarlamalar, otomatik olarak ilgili Dosyayı dönüştür işlevine girecektir; bu, daha sonra her dosyadan veri içe aktarılırken kullanılacakları anlamına gelir.

Bir istek açarak Örnek dosyayı dönüştür, gereksiz satırları filtrelemek için adımlar ekleyin (örneğin, sütuna göre Column2) ve buton ile başlıkların yükseltilmesi Başlık olarak ilk satırı kullan (İlk satırı başlık olarak kullanın). Tablo çok daha iyi görünecek.

Farklı dosyalardan alınan sütunların daha sonra otomatik olarak birbirinin altına sığabilmesi için aynı adla adlandırılması gerekir. Bir satır M kodu ile önceden oluşturulmuş bir dizine göre böyle bir toplu yeniden adlandırma gerçekleştirebilirsiniz. düğmeye tekrar basalım f_x formül çubuğunda ve değiştirmek için bir işlev ekleyin:

= Table.RenameColumns(#”Yükseltilmiş Başlıklar”, Başlıklar, MissingField.Ignore)

Bu işlev, tabloyu önceki adımdan alır Yükseltilmiş başlıklar ve içindeki tüm sütunları iç içe arama listesine göre yeniden adlandırır haber başlıkları. Üçüncü argüman MissingField.Ignore dizinde bulunan ancak tabloda olmayan başlıklarda hata oluşmaması için gereklidir.

Aslında hepsi bu.

İsteğe dönüş Raporlar tamamen farklı bir resim göreceğiz – öncekinden çok daha güzel:

• Power Query, Power Pivot, Power BI nedir ve bir Excel kullanıcısının bunlara neden ihtiyacı vardır?
  
• Belirli bir klasördeki tüm dosyalardan veri toplama
• Kitabın tüm sayfalarından verileri tek bir tabloda toplama