Paragraf
Formulasi masalah
Minangka data input, kita duwe file Excel, ing ngendi salah sawijining lembar ngemot sawetara tabel kanthi data dodolan ing ngisor iki:
Elinga:
- Tabel saka macem-macem ukuran lan karo macem-macem set produk lan wilayah ing baris lan kolom tanpa ngurutake.
- Garis kosong bisa dilebokake ing antarane tabel.
- Jumlah tabel bisa wae.
Loro asumsi penting. Dianggep:
- Ndhuwur saben tabel, ing kolom pisanan, ana jeneng manajer sing dodolan ing tabel kasebut (Ivanov, Petrov, Sidorov, lsp.)
- Jeneng barang lan wilayah ing kabeh tabel ditulis kanthi cara sing padha - kanthi akurasi cilik.
Tujuan utama yaiku ngumpulake data saka kabeh tabel dadi siji tabel normal sing rata, trep kanggo analisis sabanjure lan nggawe ringkesan, yaiku:
Langkah 1. Sambungake menyang file
Ayo nggawe file Excel kosong anyar lan pilih ing tab Data Command Entuk Data - Saka File - Saka Buku (Data - Saka file - Saka buku kerja). Nemtokake lokasi file sumber karo data dodolan banjur ing jendhela navigator pilih sheet sing dibutuhake lan klik tombol Ngonversi Data (Transformasi Data):
Akibaté, kabeh data saka iku kudu dimuat menyang editor Power Query:
Langkah 2. Ngresiki sampah
Mbusak langkah-langkah sing digawe kanthi otomatis jinis dipunéwahi (Jenis sing diganti) и Header munggah pangkat (Headers sing dipromosikan) lan nyisihake garis kosong lan garis kanthi total nggunakake saringan null и Gunggung dening kolom pisanan. Akibaté, kita entuk gambar ing ngisor iki:
Langkah 3. Nambahake manajer
Supaya ngerti mengko ngendi sing dodolan, iku perlu kanggo nambah kolom ing meja kita, ngendi ing saben baris ana jeneng sing cocog. Kanggo iki:
1. Ayo nambah kolom tambahan karo nomer baris nggunakake printah Tambah Kolom - Kolom Indeks - Saka 0 (Tambah kolom - Kolom indeks - Saka 0).
2. Tambah kolom karo rumus karo printah Nambahake Kolom - Kolom Kustom (Tambah kolom - Kolom khusus) lan ngenalake konstruksi ing ngisor iki ing kana:
Logika rumus iki prasaja - yen nilai sel sabanjure ing kolom pisanan yaiku "Produk", tegese kita wis kesandhung ing wiwitan tabel anyar, mula kita nampilake nilai sel sadurunge kanthi jeneng manager. Yen ora, kita ora nampilake apa-apa, ie null.
Kanggo entuk sel induk kanthi jeneng mburi, kita deleng tabel saka langkah sadurunge # "Indeks ditambahake", banjur nemtokake jeneng kolom sing kita butuhake [Kolom 1] ing kurung kothak lan nomer sel ing kolom kasebut ing kurung kriting. Nomer sel bakal kurang siji tinimbang sing saiki, sing dijupuk saka kolom Index, saben.
3. Iku tetep kanggo isi ing sel kosong karo null jeneng saka sel sing luwih dhuwur kanthi printah Transform - Isi - Mudhun (Transform - Isi - Mudhun) lan mbusak kolom sing ora dibutuhake maneh kanthi indeks lan baris kanthi jeneng mburi ing kolom pisanan. Akibaté, kita entuk:
Langkah 4. Ngelompokake menyang tabel kapisah dening managers
Langkah sabanjure yaiku nglumpukake baris kanggo saben manajer menyang tabel sing kapisah. Kanggo nindakake iki, ing tab Transformasi, gunakake klompok kanthi printah (Transform - Group By) lan ing jendhela sing mbukak, pilih kolom Manager lan operasi Kabeh baris (Kabeh baris) kanggo mung ngumpulake data tanpa nggunakake fungsi aggregating. padha (jumlah, rata-rata, lsp). P.):
Akibaté, kita entuk tabel sing kapisah kanggo saben manajer:
Langkah 5: Ngowahi Tabel Nested
Saiki kita menehi tabel sing dumunung ing saben sel saka kolom asil Kabeh data ing wangun prayoga.
Pisanan, mbusak kolom sing ora perlu maneh ing saben tabel Manager. Kita nggunakake maneh Kolom khusus tab transformasi (Transformasi - Kolom khusus) lan rumus ing ngisor iki:
Banjur, kanthi kolom sing diwilang liyane, kita ngunggahake baris pisanan ing saben tabel menyang judhul:
Lan pungkasanipun, kita nindakake transformasi utama - mbukak saben meja nggunakake M-fungsi Tabel.UnpivotOtherColumns:
Jeneng wilayah saka header bakal pindhah menyang kolom anyar lan kita bakal luwih sempit, nanging ing wektu sing padha, tabel sing luwih normal. sel kosong karo null diabaikan.
Mbusak kolom penengah sing ora perlu, kita duwe:
Langkah 6 Expand Nested Tables
Iku tetep kanggo nggedhekake kabeh tabel nested dinormalisasi menyang dhaptar siji nggunakake tombol karo panah pindho ing header kolom:
... lan pungkasane entuk apa sing dikarepake:
Sampeyan bisa ngekspor tabel asil bali menyang Excel nggunakake printah Ngarep — Nutup lan Muat — Nutup lan Muat ing… (Ngarep — Tutup&Muat — Tutup&Muat menyang…).
- Gawe tabel kanthi header beda saka macem-macem buku
- Nglumpukake data saka kabeh file ing folder tartamtu
- Nglumpukake data saka kabeh lembar buku dadi siji tabel