Contents [show]
Perumusan masalah
Sebagai data input, kami mempunyai fail Excel, di mana salah satu helaian mengandungi beberapa jadual dengan data jualan dalam bentuk berikut:
Perhatikan bahawa:
- Jadual dengan saiz yang berbeza dan dengan set produk dan wilayah yang berbeza dalam baris dan lajur tanpa sebarang pengisihan.
- Garis kosong boleh disisipkan di antara jadual.
- Bilangan jadual boleh menjadi apa-apa.
Dua andaian penting. Diandaikan bahawa:
- Di atas setiap jadual, dalam lajur pertama, terdapat nama pengurus yang jualannya ditunjukkan oleh jadual (Ivanov, Petrov, Sidorov, dll.)
- Nama barang dan wilayah dalam semua jadual ditulis dengan cara yang sama – dengan ketepatan kes.
Matlamat utama adalah untuk mengumpul data daripada semua jadual ke dalam satu jadual normal rata, mudah untuk analisis seterusnya dan membina ringkasan, iaitu dalam satu ini:
Langkah 1. Sambung ke fail
Mari buat fail Excel kosong baharu dan pilihnya pada tab Tarikh Perintah Dapatkan Data – Dari Fail – Dari Buku (Data — Dari fail — Dari buku kerja). Tentukan lokasi fail sumber dengan data jualan dan kemudian dalam tetingkap navigator pilih helaian yang kami perlukan dan klik pada butang Tukar Data (Transform Data):
Akibatnya, semua data daripadanya harus dimuatkan ke dalam editor Power Query:
Langkah 2. Bersihkan sampah
Padamkan langkah yang dijana secara automatik jenis yang diubah suai (Jenis Ditukar) и Pengepala dinaikkan (Tajuk Digalakkan) dan hapuskan baris dan baris kosong dengan jumlah menggunakan penapis sifar и JUMLAH mengikut lajur pertama. Hasilnya, kami mendapat gambar berikut:
Langkah 3. Menambah pengurus
Untuk memahami kemudian di mana jualannya, adalah perlu untuk menambah lajur pada jadual kami, di mana dalam setiap baris akan ada nama keluarga yang sepadan. Untuk ini:
1. Mari tambah lajur tambahan dengan nombor baris menggunakan arahan Tambah Lajur – Lajur Indeks – Daripada 0 (Tambah lajur — Lajur indeks — Daripada 0).
2. Tambah lajur dengan formula dengan arahan Menambah Lajur – Lajur Tersuai (Tambah lajur — Lajur tersuai) dan memperkenalkan pembinaan berikut di sana:
Logik formula ini adalah mudah - jika nilai sel seterusnya dalam lajur pertama ialah "Produk", maka ini bermakna kita telah terjumpa pada permulaan jadual baharu, jadi kita memaparkan nilai sel sebelumnya dengan nama pengurus. Jika tidak, kami tidak memaparkan apa-apa, iaitu batal.
Untuk mendapatkan sel induk dengan nama akhir, kami mula-mula merujuk kepada jadual dari langkah sebelumnya #”Indeks ditambah”, dan kemudian nyatakan nama lajur yang kami perlukan [Lajur1] dalam kurungan segi empat sama dan nombor sel dalam lajur itu dalam kurungan kerinting. Nombor sel akan kurang satu daripada yang semasa, yang kami ambil dari lajur indeks, Masing-masing.
3. Ia kekal untuk mengisi sel kosong dengan sifar nama dari sel yang lebih tinggi dengan arahan Transform – Isi – Bawah (Ubah — Isi — Bawah) dan padamkan lajur yang tidak diperlukan lagi dengan indeks dan baris dengan nama keluarga dalam lajur pertama. Hasilnya, kami mendapat:
Langkah 4. Pengumpulan ke dalam jadual berasingan oleh pengurus
Langkah seterusnya ialah mengumpulkan baris untuk setiap pengurus ke dalam jadual berasingan. Untuk melakukan ini, pada tab Transformasi, gunakan perintah Kumpulan mengikut (Transform – Group By) dan dalam tetingkap yang terbuka, pilih lajur Pengurus dan operasi Semua baris (Semua baris) untuk hanya mengumpul data tanpa menggunakan sebarang fungsi pengagregatan untuk mereka (jumlah, purata, dll.). P.):
Akibatnya, kami mendapat jadual berasingan untuk setiap pengurus:
Langkah 5: Ubah Jadual Bersarang
Sekarang kita memberikan jadual yang terletak dalam setiap sel lajur yang terhasil Semua data dalam bentuk yang baik.
Mula-mula, padamkan lajur yang tidak lagi diperlukan dalam setiap jadual Pengurus. Kita guna lagi Lajur tersuai tab Transformasi (Transform — Lajur tersuai) dan formula berikut:
Kemudian, dengan satu lagi lajur yang dikira, kami menaikkan baris pertama dalam setiap jadual kepada tajuk:
Dan akhirnya, kami melakukan transformasi utama - membuka setiap jadual menggunakan fungsi M Jadual.NyahpivotLajur Lain:
Nama kawasan daripada pengepala akan masuk ke lajur baharu dan kami akan mendapat lebih sempit, tetapi pada masa yang sama, jadual ternormal yang lebih panjang. Sel kosong dengan sifar tidak dipedulikan.
Menghapuskan lajur perantaraan yang tidak perlu, kami mempunyai:
Langkah 6 Kembangkan Jadual Bersarang
Ia kekal untuk mengembangkan semua jadual bersarang yang dinormalkan ke dalam senarai tunggal menggunakan butang dengan anak panah berganda dalam pengepala lajur:
… dan akhirnya kami mendapat apa yang kami mahukan:
Anda boleh mengeksport jadual yang terhasil kembali ke Excel menggunakan arahan Laman Utama — Tutup dan Muatkan — Tutup dan Muatkan dalam… (Laman Utama — Tutup&Muatkan — Tutup&Muat ke…).
- Bina jadual dengan pengepala berbeza daripada berbilang buku
- Mengumpul data daripada semua fail dalam folder tertentu
- Mengumpul data daripada semua helaian buku ke dalam satu jadual