Daptar eusi
Rumusan masalah
Salaku data input, urang gaduh file Excel, dimana salah sahiji lembar ngandung sababaraha tabel sareng data penjualan tina bentuk ieu:
Catet éta:
- Méja ukuran anu béda-béda sareng sét produk sareng daérah anu béda dina barisan sareng kolom tanpa asihan.
- Garis kosong bisa diselapkeun antara tabel.
- Jumlah tabel tiasa wae.
Dua asumsi penting. Dianggap yén:
- Luhureun unggal méja, dina kolom kahiji, aya nami manajer anu jualan na tabel ngagambarkeun (Ivanov, Petrov, Sidorov, jsb)
- Ngaran barang sareng wilayah dina sadaya tabel ditulis dina cara anu sami - kalayan akurasi kasus.
Tujuan pamungkas nya éta pikeun ngumpulkeun data tina sakabéh tabel kana hiji tabel dinormalisasi datar, merenah pikeun analisis saterusna sarta ngawangun kasimpulan, nyaéta dina hiji ieu:
Lengkah 1. Sambungkeun ka file
Hayu urang jieun file Excel kosong anyar sareng pilih dina tab data parentah Kéngingkeun Data - Tina File - Tina Buku (Data - Tina file - Tina buku kerja). Sebutkeun lokasi file sumber sareng data penjualan teras dina jandela navigator pilih lambaran anu urang peryogikeun teras klik kana tombol Ngarobah Data (Transformasi Data):
Hasilna, sadaya data ti dinya kedah dimuat kana pangropéa Power Query:
Lengkah 2. Ngabersihan runtah
Hapus léngkah anu dihasilkeun sacara otomatis tipe dirobah (Tipe Robah) и headers elevated (Judul Promosi) sareng nyingkirkeun garis sareng garis kosong kalayan total nganggo saringan null и total ku kolom kahiji. Hasilna, urang meunang gambar di handap ieu:
Lengkah 3. Nambahkeun manajer
Dina raraga ngartos engké dimana nu jualan, perlu pikeun nambahkeun hiji kolom kana tabel kami, dimana dina unggal baris bakal aya ngaran kulawarga pakait. Kanggo ieu:
1. Hayu urang tambahkeun kolom bantu kalayan nomer garis nganggo paréntah Tambahkeun Kolom - Kolom Indéks - Tina 0 (Tambahkeun kolom - Kolom indéks - Tina 0).
2. Tambahkeun kolom kalawan rumus jeung paréntah Nambahkeun Kolom - Kolom Adat (Tambihkeun kolom - Kolom khusus) sareng ngenalkeun konstruksi di handap ieu di dinya:
Logika rumus ieu saderhana - upami nilai sél salajengna dina kolom kahiji nyaéta "Produk", maka ieu hartosna urang kasandung dina awal tabel énggal, janten urang ningalikeun nilai sél sateuacana kalayan ngaran manajer. Upami teu kitu, urang teu nembongkeun nanaon, ie null.
Pikeun meunangkeun sél indungna kalawan ngaran tukang, urang tingal tabel ti hambalan saméméhna #"Indéks ditambahkeun", lajeng nangtukeun nami kolom kami butuh [Kolom1] dina kurung kuadrat sareng nomer sél dina kolom éta dina kurung keriting. Jumlah sél bakal hiji kirang ti nu ayeuna, nu urang nyokot tina kolom daptar eusiMasing-masing.
3. Eta tetep ngeusian dina sél kosong kalawan null ngaran ti sél luhur kalawan paréntah Transformasi - Eusian - Turun (Transformasi - Eusian - Turun) sareng mupus kolom anu henteu diperyogikeun deui kalayan indéks sareng barisan kalayan nami tukang dina kolom kahiji. Hasilna, urang meunang:
Lengkah 4. Grup kana tabel misah ku manajer
Lengkah saterusna nyaéta ngagolongkeun baris pikeun tiap manajer kana tabel anu misah. Jang ngalampahkeun ieu, dina tab Transformasi, paké Grup ku paréntah (Transformasi - Grup Ku) sareng dina jandela anu muka, pilih kolom Manajer sareng operasi Sadaya baris (Sadaya baris) pikeun ngan saukur ngumpulkeun data tanpa nerapkeun fungsi agrégat. aranjeunna (jumlah, rata, jsb). P.):
Hasilna, urang meunang tabel misah pikeun tiap manajer:
Lengkah 5: Transformasi Nested Tables
Ayeuna urang masihan tabel anu perenahna di unggal sél tina kolom hasilna Sadaya data dina bentuk santun.
Kahiji, ngahapus kolom nu geus euweuh diperlukeun dina unggal tabel pingpinan. Urang make deui kolom custom tab transformasi (Transformasi - Kolom khusus) sarta rumus di handap ieu:
Lajeng, ku kolom diitung sejen, urang ngangkat baris kahiji dina unggal tabel kana lulugu:
Sarta pamustunganana, urang ngalakukeun transformasi utama - unfolding unggal tabel ngagunakeun M-fungsi Table.UnpivotOtherColumns:
Ngaran-ngaran daérah tina lulugu bakal lebet kana kolom énggal sareng urang bakal langkung sempit, tapi dina waktos anu sami, méja anu dinormalisasi deui. Sél kosong kalawan null teu dipalire.
Nyingkirkeun kolom perantara anu teu perlu, urang gaduh:
Lengkah 6 Dilegakeun Nested Tables
Tetep dilegakeun sadaya tabel nested anu dinormalisasi kana daptar tunggal nganggo tombol sareng panah ganda dina lulugu kolom:
… sarta kami tungtungna meunang naon urang hayang:
Anjeun tiasa ngékspor tabel anu dihasilkeun deui ka Excel nganggo paréntah Imah - Tutup sareng Muatan - Tutup sareng Muat dina… (Imah — Tutup&Muat — Tutup&Muat ka…).
- Ngawangun tabel sareng header anu béda tina sababaraha buku
- Ngumpulkeun data tina sadaya file dina polder anu dipasihkeun
- Ngumpulkeun data tina sakabéh lambar buku kana hiji méja