Bangun tabel dengan tajuk berbeda dari beberapa buku

Rumusan masalah

Kami memiliki beberapa file (dalam contoh kami – 4 buah, dalam kasus umum – sebanyak yang Anda suka) dalam satu folder laporan:

Di dalam, file-file ini terlihat seperti ini:

Di mana:

• Lembar data yang kita butuhkan selalu disebut Foto, tapi bisa di mana saja di buku kerja.
  
• Di luar lembaran Foto Setiap buku mungkin memiliki lembaran lain.
• Tabel dengan data memiliki jumlah baris yang berbeda dan dapat dimulai dengan baris yang berbeda pada lembar kerja.
• Nama kolom yang sama dalam tabel yang berbeda mungkin berbeda (misalnya, Kuantitas = Kuantitas = Qty).
• Kolom dalam tabel dapat diatur dalam urutan yang berbeda.

Tugas: mengumpulkan data penjualan dari semua file dari sheet Foto ke dalam satu tabel umum untuk selanjutnya membuat ringkasan atau analitik lainnya di atasnya.

Langkah 1. Mempersiapkan direktori nama kolom

Hal pertama yang harus dilakukan adalah menyiapkan buku referensi dengan semua opsi yang memungkinkan untuk nama kolom dan interpretasinya yang benar:

Kami mengubah daftar ini menjadi tabel "pintar" dinamis menggunakan tombol Format sebagai tabel pada tab Beranda (Beranda — Format sebagai Tabel) atau pintasan keyboard Ctrl+T dan memuatnya ke Power Query dengan perintah Data – Dari Tabel/Rentang (Data — Dari Tabel/Rentang). Dalam versi Excel terbaru, telah diubah namanya menjadi Dengan daun (Dari lembar).

Di jendela editor kueri Power Query, kami biasanya menghapus langkah Jenis yang Diubah dan tambahkan langkah baru sebagai gantinya dengan mengklik tombol f_xdi bilah rumus (jika tidak terlihat, Anda dapat mengaktifkannya di tab ULASAN) dan masukkan rumus di sana dalam bahasa Power Query bawaan M:

=Table.ToRows(Sumber)

Perintah ini akan mengonversi yang dimuat pada langkah sebelumnya sumber tabel referensi menjadi daftar yang terdiri dari daftar bersarang (Daftar), yang masing-masing, pada gilirannya, adalah sepasang nilai Itu-menjadi dari satu baris:

Kami akan membutuhkan jenis data ini nanti, saat mengganti nama header secara massal dari semua tabel yang dimuat.

Setelah menyelesaikan konversi, pilih perintah Beranda — Tutup dan Muat — Tutup dan Muat di… dan jenis impor Buat saja koneksi (Beranda — Tutup&Muat — Tutup&Muat ke… — Hanya buat koneksi) dan kembali ke Excel.

Langkah 2. Kami memuat semuanya dari semua file apa adanya

Sekarang mari kita memuat isi semua file kita dari folder – untuk saat ini, apa adanya. Memilih tim Data – Dapatkan data – Dari file – Dari folder (Data — Dapatkan Data — Dari file — Dari folder) dan kemudian folder tempat buku sumber kami berada.

Di jendela pratinjau, klik mengubah (Mengubah) or Perubahan (Ubah):

Dan kemudian perluas konten semua file yang diunduh (Biner) tombol dengan panah ganda di judul kolom Konten:

Power Query pada contoh file pertama (Vostok.xlsx) akan menanyakan nama sheet yang ingin kita ambil dari setiap workbook – pilih Foto dan tekan OK:

Setelah itu (sebenarnya), beberapa peristiwa yang tidak jelas bagi pengguna akan terjadi, konsekuensinya terlihat jelas di panel kiri:

1. Power Query akan mengambil file pertama dari folder (kami akan memilikinya Vostok.xlsx — melihat Contoh file) sebagai contoh dan mengimpor kontennya dengan membuat kueri Konversi file sampel. Kueri ini akan memiliki beberapa langkah sederhana seperti sumber (akses berkas) Navigasi (pemilihan lembar) dan mungkin menaikkan judul. Permintaan ini hanya dapat memuat data dari satu file tertentu Vostok.xlsx.
   
2. Berdasarkan permintaan ini, fungsi yang terkait dengannya akan dibuat Konversi file (ditunjukkan oleh ikon karakteristik f_x), di mana file sumber tidak lagi berupa konstanta, tetapi nilai variabel – parameter. Dengan demikian, fungsi ini dapat mengekstrak data dari buku apa pun yang kita masukkan ke dalamnya sebagai argumen.
   
3. Fungsi akan diterapkan secara bergantian ke setiap file (Binary) dari kolom Konten – langkah bertanggung jawab untuk ini Panggil fungsi khusus dalam kueri kami yang menambahkan kolom ke daftar file Konversi file dengan hasil impor dari setiap buku kerja:

   

4. Kolom tambahan dihapus.
5. Isi tabel bersarang diperluas (langkah Kolom tabel yang diperluas) – dan kami melihat hasil akhir pengumpulan data dari semua buku:

   
   

Langkah 3. Pengamplasan

Tangkapan layar sebelumnya dengan jelas menunjukkan bahwa perakitan langsung "sebagaimana adanya" ternyata berkualitas buruk:

• Kolom dibalik.
• Banyak baris tambahan (kosong dan tidak hanya).
• Header tabel tidak dianggap sebagai header dan dicampur dengan data.
  

Anda dapat memperbaiki semua masalah ini dengan sangat mudah – cukup ubah kueri Convert Sample File. Semua penyesuaian yang kami buat untuk itu akan secara otomatis masuk ke fungsi Konversi file terkait, yang berarti mereka akan digunakan nanti saat mengimpor data dari setiap file.

Dengan membuka permintaan Konversi file sampel, tambahkan langkah-langkah untuk memfilter baris yang tidak perlu (misalnya, menurut kolom Column2) dan menaikkan judul dengan tombol Gunakan baris pertama sebagai header (Gunakan baris pertama sebagai header). Meja akan terlihat jauh lebih baik.

Agar kolom dari file yang berbeda secara otomatis cocok di bawah satu sama lain nanti, mereka harus diberi nama yang sama. Anda dapat melakukan penggantian nama massal seperti itu sesuai dengan direktori yang dibuat sebelumnya dengan satu baris kode-M. Ayo tekan tombolnya lagi f_x di bilah rumus dan tambahkan fungsi untuk diubah:

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

Fungsi ini mengambil tabel dari langkah sebelumnya Header yang ditinggikan dan mengganti nama semua kolom di dalamnya sesuai dengan daftar pencarian bersarang Headlines. Argumen ketiga MissingField.Abaikan diperlukan agar pada heading-heading yang ada di direktori tersebut, tetapi tidak ada dalam tabel, tidak terjadi kesalahan.

Sebenarnya, itu saja.

Kembali ke permintaan laporan kita akan melihat gambar yang sama sekali berbeda – jauh lebih bagus dari yang sebelumnya:

• Apa itu Power Query, Power Pivot, Power BI dan mengapa pengguna Excel membutuhkannya
  
• Mengumpulkan data dari semua file dalam folder tertentu
• Mengumpulkan data dari semua lembar buku menjadi satu tabel