Konten
Rumusan masalah
Sebagai data input, kami memiliki file excel, dimana salah satu sheetnya berisi beberapa tabel dengan data penjualan form sebagai berikut:
Perhatikan bahwa:
- Tabel dengan ukuran berbeda dan dengan rangkaian produk dan wilayah berbeda dalam baris dan kolom tanpa penyortiran apa pun.
- Baris kosong dapat disisipkan di antara tabel.
- Jumlah tabel bisa berapa saja.
Dua asumsi penting. Ini diasumsikan bahwa:
- Di atas setiap meja, di kolom pertama, ada nama manajer yang penjualannya digambarkan oleh tabel tersebut (Ivanov, Petrov, Sidorov, dll.)
- Nama barang dan wilayah di semua tabel ditulis dengan cara yang sama – dengan akurasi huruf besar-kecil.
Tujuan utamanya adalah untuk mengumpulkan data dari semua tabel ke dalam satu tabel datar yang dinormalisasi, nyaman untuk analisis selanjutnya dan membuat ringkasan, yaitu dalam tabel ini:
Langkah 1. Hubungkan ke file
Mari buat file Excel kosong baru dan pilih di tab Data perintah Dapatkan Data – Dari File – Dari Buku (Data — Dari file — Dari buku kerja). Tentukan lokasi file sumber dengan data penjualan dan kemudian di jendela navigator pilih lembar yang kita butuhkan dan klik tombol Konversi Data (Transformasi Data):
Akibatnya, semua data darinya harus dimuat ke editor Power Query:
Langkah 2. Bersihkan sampah
Hapus langkah-langkah yang dibuat secara otomatis tipe yang dimodifikasi (Tipe Berubah) и Header yang ditinggikan (Tajuk yang Dipromosikan) dan singkirkan baris kosong dan baris dengan total menggunakan filter nol и JUMLAH oleh kolom pertama. Hasilnya, kita mendapatkan gambar berikut:
Langkah 3. Menambahkan manajer
Untuk memahami nanti di mana penjualan siapa, perlu menambahkan kolom ke tabel kami, di mana di setiap baris akan ada nama keluarga yang sesuai. Untuk ini:
1. Mari tambahkan kolom bantu dengan nomor baris menggunakan perintah Tambahkan Kolom – Kolom Indeks – Dari 0 (Tambahkan kolom — Kolom indeks — Dari 0).
2. Tambahkan kolom dengan rumus dengan perintah Menambahkan Kolom – Kolom Kustom (Tambahkan kolom — Kolom khusus) dan perkenalkan konstruksi berikut di sana:
Logika rumus ini sederhana – jika nilai sel berikutnya di kolom pertama adalah “Produk”, maka ini berarti kita telah menemukan awal tabel baru, jadi kita menampilkan nilai sel sebelumnya dengan nama manajer. Jika tidak, kami tidak menampilkan apa pun, yaitu null.
Untuk mendapatkan sel induk dengan nama belakang, pertama-tama kita lihat tabel dari langkah sebelumnya #"Indeks ditambahkan", lalu tentukan nama kolom yang kita butuhkan [Kolom 1] dalam tanda kurung siku dan nomor sel dalam kolom tersebut dalam tanda kurung kurawal. Nomor sel akan menjadi satu kurang dari yang sekarang, yang kami ambil dari kolom Indeks, Masing-masing.
3. Tetap mengisi sel kosong dengan nol nama dari sel yang lebih tinggi dengan perintah Transform – Isi – Bawah (Transformasi — Isi — Turun) dan hapus kolom yang tidak lagi diperlukan dengan indeks dan baris dengan nama belakang di kolom pertama. Hasilnya, kita mendapatkan:
Langkah 4. Pengelompokan ke dalam tabel terpisah oleh manajer
Langkah selanjutnya adalah mengelompokkan baris untuk setiap manajer ke dalam tabel terpisah. Untuk melakukan ini, pada tab Transformation, gunakan perintah Group by (Transform – Group By) dan di jendela yang terbuka, pilih kolom Manager dan operasikan All rows (All rows) untuk mengumpulkan data tanpa menerapkan fungsi agregasi apa pun ke mereka (jumlah, rata-rata, dll.). P.):
Akibatnya, kami mendapatkan tabel terpisah untuk setiap manajer:
Langkah 5: Ubah Tabel Bersarang
Sekarang kami memberikan tabel yang terletak di setiap sel dari kolom yang dihasilkan Semua data dalam bentuk yang layak.
Pertama, hapus kolom yang tidak lagi diperlukan di setiap tabel manajer. Kami menggunakan lagi Kolom khusus tab Transformasi (Transformasi — Kolom khusus) dan formula berikut:
Kemudian, dengan kolom terhitung lainnya, kami menaikkan baris pertama di setiap tabel ke judul:
Dan akhirnya, kami melakukan transformasi utama – membuka setiap tabel menggunakan fungsi-M Tabel.UnpivotOtherColumns:
Nama-nama daerah dari header akan masuk ke kolom baru dan kita akan mendapatkan tabel yang lebih sempit, tetapi pada saat yang sama, tabel yang lebih panjang. Sel kosong dengan nol diabaikan.
Menyingkirkan kolom perantara yang tidak perlu, kami memiliki:
Langkah 6 Perluas Tabel Bersarang
Tetap memperluas semua tabel bersarang yang dinormalisasi menjadi satu daftar menggunakan tombol dengan panah ganda di header kolom:
… dan kami akhirnya mendapatkan apa yang kami inginkan:
Anda dapat mengekspor tabel yang dihasilkan kembali ke Excel menggunakan perintah Beranda — Tutup dan Muat — Tutup dan Muat di… (Beranda — Tutup&Muat — Tutup&Muat ke…).
- Bangun tabel dengan tajuk berbeda dari beberapa buku
- Mengumpulkan data dari semua file dalam folder tertentu
- Mengumpulkan data dari semua lembar buku menjadi satu tabel