Bina jadual dengan pengepala berbeza daripada berbilang buku

Perumusan masalah

Kami mempunyai beberapa fail (dalam contoh kami - 4 keping, dalam kes umum - seberapa banyak yang anda suka) dalam satu folder Laporan:

Di dalam, fail ini kelihatan seperti ini:

Di mana:

• Helaian data yang kami perlukan sentiasa dipanggil pics, tetapi boleh berada di mana-mana dalam buku kerja.
  
• Di sebalik helaian pics Setiap buku mungkin mempunyai helaian lain.
• Jadual dengan data mempunyai bilangan baris yang berbeza dan mungkin bermula dengan baris yang berbeza pada lembaran kerja.
• Nama lajur yang sama dalam jadual berbeza mungkin berbeza (contohnya, Kuantiti = Kuantiti = Kuantiti).
• Lajur dalam jadual boleh disusun dalam susunan yang berbeza.

Tugas: kumpulkan data jualan daripada semua fail daripada helaian pics ke dalam satu jadual biasa untuk membina ringkasan atau sebarang analitik lain di atasnya.

Langkah 1. Menyediakan direktori nama lajur

Perkara pertama yang perlu dilakukan ialah menyediakan buku rujukan dengan semua pilihan yang mungkin untuk nama lajur dan tafsiran yang betul:

Kami menukar senarai ini kepada jadual "pintar" dinamik menggunakan butang Format sebagai jadual pada tab Laman Utama (Laman Utama — Format sebagai Jadual) atau pintasan papan kekunci Ctrl+T dan muatkannya ke dalam Power Query dengan arahan Data – Daripada Jadual/Julat (Data — Daripada Jadual/Julat). Dalam versi terkini Excel, ia telah dinamakan semula kepada Dengan daun (Dari helaian).

Dalam tetingkap editor pertanyaan Power Query, kami secara tradisinya memadamkan langkah tersebut Jenis Diubah dan tambahkan langkah baharu sebagai gantinya dengan mengklik pada butang f_xdalam bar formula (jika ia tidak kelihatan, maka anda boleh mendayakannya pada tab ULASAN) dan masukkan formula di sana dalam bahasa Power Query terbina dalam M:

=Table.ToRows(Sumber)

Perintah ini akan menukar yang dimuatkan dalam langkah sebelumnya Source jadual rujukan ke dalam senarai yang terdiri daripada senarai bersarang (Senarai), yang setiap satu daripadanya, seterusnya, adalah sepasang nilai Ia telah-menjadi dari satu baris:

Kami memerlukan data jenis ini sedikit kemudian, apabila menamakan semula secara besar-besaran pengepala daripada semua jadual yang dimuatkan.

Selepas melengkapkan penukaran, pilih arahan Laman Utama — Tutup dan Muatkan — Tutup dan Muatkan dalam… dan jenis import Buat sambungan sahaja (Laman Utama — Tutup&Muat — Tutup&Muat ke… — Buat sambungan sahaja) dan kembali ke Excel.

Langkah 2. Kami memuatkan segala-galanya daripada semua fail sebagaimana adanya

Sekarang mari muatkan kandungan semua fail kami dari folder – buat masa ini, seperti sedia ada. Memilih pasukan Data – Dapatkan data – Dari fail – Dari folder (Data — Dapatkan Data — Dari fail — Dari folder) dan kemudian folder di mana buku sumber kami berada.

Dalam tetingkap pratonton, klik Tukar (Transformasi) or Tukar (Edit):

Dan kemudian kembangkan kandungan semua fail yang dimuat turun (Perduaan) butang dengan anak panah berganda dalam tajuk lajur Kandungan:

Power Query pada contoh fail pertama (Vostok.xlsx) akan meminta kami nama helaian yang ingin kami ambil daripada setiap buku kerja – pilih pics dan tekan OK:

Selepas itu (sebenarnya), beberapa peristiwa yang tidak jelas kepada pengguna akan berlaku, akibatnya dapat dilihat dengan jelas di panel kiri:

1. Power Query akan mengambil fail pertama daripada folder (kami akan memilikinya Vostok.xlsx — lihat Contoh fail) sebagai contoh dan mengimport kandungannya dengan mencipta pertanyaan Tukar fail sampel. Pertanyaan ini akan mempunyai beberapa langkah mudah seperti Source (akses fail) navigation (pemilihan helaian) dan mungkin menaikkan tajuk. Permintaan ini hanya boleh memuatkan data daripada satu fail tertentu Vostok.xlsx.
   
2. Berdasarkan permintaan ini, fungsi yang dikaitkan dengannya akan dibuat Tukar fail (ditunjukkan oleh ikon ciri f_x), di mana fail sumber tidak lagi menjadi pemalar, tetapi nilai berubah - parameter. Oleh itu, fungsi ini boleh mengekstrak data daripada mana-mana buku yang kita selitkan ke dalamnya sebagai hujah.
   
3. Fungsi ini akan digunakan secara bergilir-gilir pada setiap fail (Binari) dari lajur Kandungan – langkah bertanggungjawab untuk ini Panggilan fungsi tersuai dalam pertanyaan kami yang menambahkan lajur pada senarai fail Tukar fail dengan hasil import daripada setiap buku kerja:

   

4. Lajur tambahan dialih keluar.
5. Kandungan jadual bersarang dikembangkan (langkah Lajur jadual lanjutan) – dan kami melihat hasil akhir pengumpulan data daripada semua buku:

   
   

Langkah 3. Mengempelas

Tangkapan skrin sebelumnya dengan jelas menunjukkan bahawa pemasangan langsung "seadanya" ternyata tidak berkualiti:

• Lajur diterbalikkan.
• Banyak baris tambahan (kosong dan bukan sahaja).
• Pengepala jadual tidak dianggap sebagai pengepala dan bercampur dengan data.
  

Anda boleh membetulkan semua masalah ini dengan mudah – cuma tweak pertanyaan Tukar Fail Contoh. Semua pelarasan yang kami buat padanya akan secara automatik jatuh ke dalam fungsi fail Tukar yang berkaitan, yang bermaksud ia akan digunakan kemudian apabila mengimport data daripada setiap fail.

Dengan membuka permintaan Tukar fail sampel, tambahkan langkah untuk menapis baris yang tidak diperlukan (contohnya, mengikut lajur Column2) dan menaikkan tajuk dengan butang Gunakan baris pertama sebagai tajuk (Gunakan baris pertama sebagai pengepala). Meja akan kelihatan lebih baik.

Untuk membolehkan lajur daripada fail yang berbeza dimuatkan secara automatik di bawah satu sama lain kemudian, ia mesti dinamakan sama. Anda boleh melakukan penamaan semula secara besar-besaran mengikut direktori yang dibuat sebelum ini dengan satu baris kod-M. Jom tekan butang sekali lagi f_x dalam bar formula dan tambah fungsi untuk menukar:

= Table.RenameColumns(#”Elevated Headers”, Header, MissingField.Abaikan)

Fungsi ini mengambil jadual dari langkah sebelumnya Pengepala dinaikkan dan menamakan semula semua lajur di dalamnya mengikut senarai carian bersarang Tajuk-tajuk utama. Hujah ketiga MissingField.Abaikan diperlukan supaya pada tajuk yang ada dalam direktori, tetapi tidak dalam jadual, ralat tidak berlaku.

Sebenarnya, itu sahaja.

Berbalik kepada permintaan Laporan kita akan melihat gambar yang sama sekali berbeza - jauh lebih bagus daripada yang sebelumnya:

• Apakah itu Power Query, Power Pivot, Power BI dan sebab pengguna Excel memerlukannya
  
• Mengumpul data daripada semua fail dalam folder tertentu
• Mengumpul data daripada semua helaian buku ke dalam satu jadual