Gawe tabel kanthi header beda saka macem-macem buku

Formulasi masalah

Kita duwe sawetara file (ing conto kita - 4 potongan, ing kasus umum - akeh sing disenengi) ing siji folder lapuran:

Ing njero, file kasebut katon kaya iki:

Ing endi:

• Lembar data sing kita butuhake mesthi diarani Photos, nanging bisa ing ngendi wae ing buku kerja.
  
• Ngluwihi sheet Photos Saben buku bisa uga duwe lembar liyane.
• Tabel kanthi data duwe nomer baris sing beda lan bisa diwiwiti kanthi baris sing beda ing lembar kerja.
• Jeneng kolom sing padha ing tabel sing beda-beda bisa beda-beda (contone, Jumlah = Jumlah = Jumlah).
• Kolom ing tabel bisa disusun kanthi urutan sing beda.

Tugas: ngumpulake data dodolan saka kabeh file saka sheet Photos dadi siji tabel umum supaya bisa nggawe ringkesan utawa analitik liyane.

Langkah 1. Nyiapake direktori jeneng kolom

Babagan pisanan sing kudu ditindakake yaiku nyiyapake buku referensi kanthi kabeh opsi kanggo jeneng kolom lan interpretasi sing bener:

Kita ngowahi dhaptar iki dadi tabel "pinter" dinamis kanthi nggunakake tombol Format minangka tabel ing tab kasebut Home (Ngarep - Format minangka Tabel) utawa trabasan keyboard Ctrl+T lan muat menyang Power Query kanthi printah Data - Saka Tabel / Range (Data — Saka Tabel/Rentang). Ing versi paling anyar saka Excel, wis diganti jeneng kanggo Kanthi godhong (Saka sheet).

Ing jendhela editor pitakon Power Query, kita bakal mbusak langkah kasebut kanthi tradisional Jinis sing diowahi lan nambah langkah anyar tinimbang iku kanthi ngeklik tombol f_xing garis rumus (yen ora katon, sampeyan bisa ngaktifake ing tab review) lan ketik rumus kasebut ing basa Power Query M sing dibangun:

=Tabel.ToRows(Sumber)

Printah iki bakal ngowahi sing dimuat ing langkah sadurunge sumber tabel referensi dadi dhaptar sing kasusun saka dhaptar bersarang (Dhaptar), sing saben-saben, minangka pasangan nilai. Iku-dadi saka siji baris:

Kita bakal mbutuhake jinis data iki mengko, nalika massal ngganti jeneng header saka kabeh tabel dimuat.

Sawise rampung konversi, pilih printah Ngarep — Nutup lan Muat — Nutup lan Muat ing… lan jinis impor Mung nggawe sambungan (Ngarep — Tutup&Muat — Tutup&Muat menyang… — Mung nggawe sambungan) lan bali menyang Excel.

Langkah 2. We mbukak kabeh saka kabeh file minangka

Saiki ayo mbukak isi kabeh file saka folder - saiki, kaya saiki. Milih tim Data - Entuk data - Saka file - Saka folder (Data - Entuk Data - Saka file - Saka folder) banjur folder ing ngendi buku sumber kita.

Ing jendhela pratinjau, klik Ngonversi (Transformasi) or Ganti (Edit):

Banjur nggedhekake isi kabeh file sing diundhuh (Binar) tombol karo panah pindho ing judhul kolom Content:

Power Query ing conto file pisanan (Vostok.xlsx) bakal takon jeneng sheet sing arep dijupuk saka saben buku kerja - pilih Photos banjur pencet OK:

Sawise iku (nyatane), sawetara acara sing ora dingerteni pangguna bakal kedadeyan, akibat sing katon jelas ing panel kiwa:

1. Power Query bakal njupuk file pisanan saka folder kasebut (kita bakal duwe Vostok.xlsx — ndeleng Tuladha file) minangka conto lan ngimpor isi kanthi nggawe pitakon Ngonversi file sampel. Pitakonan iki bakal duwe sawetara langkah prasaja kaya sumber (akses file) pandhu arah (pilihan lembar) lan bisa uga ngunggahake judhul. Panjaluk iki mung bisa mbukak data saka siji file tartamtu Vostok.xlsx.
   
2. Adhedhasar panyuwunan iki, fungsi sing ana gandhengane bakal digawe Ngonversi file (dituduhake dening lambang karakteristik f_x), ing ngendi file sumber ora bakal dadi konstanta, nanging nilai variabel - parameter. Mangkono, fungsi iki bisa ngekstrak data saka buku apa wae sing dilebokake minangka argumen.
   
3. Fungsi kasebut bakal ditrapake ing saben file (Binary) saka kolom Content – langkah tanggung jawab kanggo iki Telpon fungsi khusus ing pitakon kita sing nambah kolom menyang dhaptar file Ngonversi file kanthi asil impor saka saben buku kerja:

   

4. Kolom ekstra dibusak.
5. Isi tabel nested ditambahi (step Kolom tabel sing ditambahi) - lan kita ndeleng asil pungkasan pangumpulan data saka kabeh buku:

   
   

Langkah 3. Sanding

Gambar sadurunge kanthi jelas nuduhake manawa perakitan langsung "kaya" dadi kualitas sing ora apik:

• Kolom kasebut dibalik.
• Akeh garis ekstra (kosong lan ora mung).
• Header tabel ora dianggep minangka header lan dicampur karo data.
  

Sampeyan bisa ndandani kabeh masalah iki kanthi gampang - mung ngapiki pitakon Convert Sample File. Kabeh pangaturan sing kita tindakake bakal kanthi otomatis tiba ing fungsi file Ngonversi sing gegandhengan, sing tegese bakal digunakake mengko nalika ngimpor data saka saben file.

Kanthi mbukak panyuwunan Ngonversi file sampel, tambahake langkah-langkah kanggo nyaring baris sing ora perlu (contone, miturut kolom Column2) lan ngunggahake judhul kanthi tombol Gunakake baris pisanan minangka header (Gunakake baris pisanan minangka header). Tabel bakal katon luwih apik.

Supaya kolom saka macem-macem file bisa pas karo saben liyane mengko, padha kudu dijenengi padha. Sampeyan bisa nindakake ganti jeneng massa kasebut miturut direktori sing digawe sadurunge kanthi siji baris kode M. Ayo pencet tombol maneh f_x ing garis rumus lan nambah fungsi kanggo ngganti:

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

Fungsi iki njupuk tabel saka langkah sadurunge Header munggah pangkat lan ngganti jeneng kabeh kolom ing iku miturut dhaftar nested lookup Headlines. Argumentasi katelu MissingField.Abaikan dibutuhake supaya ing judhul sing ana ing direktori, nanging ora ana ing meja, kesalahan ora kedadeyan.

Bener, iku kabeh.

Bali menyang panyuwunan lapuran kita bakal weruh gambar sing beda banget - luwih apik tinimbang sing sadurunge:

• Apa Power Query, Power Pivot, Power BI lan kenapa pangguna Excel mbutuhake
  
• Nglumpukake data saka kabeh file ing folder tartamtu
• Nglumpukake data saka kabeh lembar buku dadi siji tabel