Ngawangun tabel sareng header anu béda tina sababaraha buku

Rumusan masalah

Kami gaduh sababaraha file (dina conto urang - 4 lembar, dina kasus umum - saloba anjeun resep) dina hiji folder. laporan:

Di jero, file ieu katingalina kieu:

Dimana:

• Lembar data anu urang butuhkeun sok disebut Photos, tapi tiasa dimana waé dina buku kerja.
  
• Saluareun lambaran Photos Unggal buku tiasa gaduh lambaran sanés.
• Tabél sareng data gaduh jumlah baris anu béda sareng tiasa dimimitian ku baris anu béda dina lembar kerja.
• Ngaran kolom anu sami dina tabel anu béda-béda tiasa bénten (contona, Jumlah = Jumlah = Jumlah).
• Kolom dina tabel bisa disusun dina urutan béda.

Tugas: ngumpulkeun data penjualan tina sadaya file tina lambaran Photos kana hiji méja umum supados salajengna ngawangun kasimpulan atanapi analitik anu sanés.

Lengkah 1. Nyiapkeun diréktori ngaran kolom

Hal kahiji anu kedah dilakukeun nyaéta nyiapkeun buku rujukan kalayan sagala pilihan anu mungkin pikeun nami kolom sareng interpretasi anu leres:

Kami ngarobih daptar ieu kana méja "pinter" dinamis nganggo tombol Format salaku méja dina tab imah (Imah - Format salaku Méja) atawa potong kompas keyboard Ctrl+T sareng muatkeun kana Power Query sareng paréntahna Data - Ti Table / rentang (Data — Tina Tabél/Rentang). Dina vérsi panganyarna tina Excel, éta geus diganti jadi Kalayan daun (Ti lambaran).

Dina jandela éditor pamundut Power Query, urang sacara tradisional ngahapus léngkahna Dirobah Tipe tur nambahkeun hiji hambalan anyar tinimbang eta ku ngaklik on tombol f_xdina bar rumus (upami teu katingali, teras anjeun tiasa ngaktipkeun dina tab tingalian) sareng lebetkeun rumusna dina basa Power Query M anu diwangun:

=Table.ToRows(Sumber)

Paréntah ieu bakal ngarobih anu dimuat dina léngkah sateuacana sumber tabel rujukan kana daptar nu diwangun ku béréndélan nested (Daptar), nu masing-masing, kahareupna mangrupakeun sapasang nilai. Éta-jadi ti hiji baris:

Urang bakal butuh tipe ieu data saeutik engké, nalika massa renaming headers tina sagala tabel dimuat.

Saatos réngsé konvérsi, pilih paréntah Imah - Tutup sareng Muatan - Tutup sareng Muat dina… jeung jenis impor Ngan nyieun sambungan (Imah — Tutup&Muat — Tutup&Muat ka… — Ngan ukur nyieun sambungan) sarta balik deui ka Excel.

Hambalan 2. Urang muka sagalana ti sakabéh file sakumaha anu kasebut

Ayeuna hayu urang ngamuat eusi sadaya file urang tina polder - pikeun ayeuna, sakumaha ayeuna. Milih tim Data - Kéngingkeun data - Tina file - Tina folder (Data - Kéngingkeun Data - Tina file - Tina folder) terus folder tempat buku sumber urang.

Dina jandela tilik, klik ngarobah (Transformasi) or ngarobah (Édit):

Teras dilegakeun eusi sadaya file anu diunduh (binér) tombol kalawan panah ganda dina judul kolom eusi:

Power Query dina conto file kahiji (Vostok.xlsx) bakal naroskeun ka kami nami lambaran anu badé dicandak tina unggal buku kerja - pilih Photos terus pencét OK:

Sanggeus éta (kanyataanana), sababaraha kajadian anu teu atra ka pamaké bakal lumangsung, konsékuansi nu jelas katempo dina panel kénca:

1. Power Query bakal nyandak file munggaran tina polder (urang bakal gaduhna Vostok.xlsx — tempo Conto file) salaku conto sareng ngimpor eusina ku cara nyiptakeun pamundut Ngarobih file sampel. query ieu bakal mibanda sababaraha léngkah basajan kawas sumber (aksés file) napigasi (pamilihan lambar) jeung kamungkinan ngangkat judul. Paménta ieu ngan ukur tiasa ngamuat data tina hiji file khusus Vostok.xlsx.
   
2. Dumasar kana pamundut ieu, fungsi anu aya hubunganana bakal didamel Ngarobih file (ditunjukkeun ku ikon ciri f_x), dimana file sumber moal deui konstan, tapi nilai variabel - parameter. Ku kituna, fungsi ieu bisa nimba data tina sagala buku nu urang dieunakeun kana eta salaku argumen.
   
3. Pungsi bakal dilarapkeun dina gilirannana ka unggal file (Binary) ti kolom eusi – hambalan tanggung jawab ieu Telepon fungsi custom dina pamundut urang anu nambihan kolom kana daptar file Ngarobih file kalawan hasil impor ti unggal workbook:

   

4. Kolom tambahan dipiceun.
5. Eusi tabel nested dilegakeun (step kolom tabel nambahan) - sareng urang ningali hasil ahir pangumpulan data tina sadaya buku:

   
   

Lengkah 3. Sanding

Potret layar saméméhna jelas nunjukkeun yén rakitan langsung "sakumaha" tétéla kualitasna goréng:

• Kolom dibalikkeun.
• Seueur garis tambahan (kosong sareng henteu ngan ukur).
• Header tabel henteu dianggap salaku header sareng dicampur sareng data.
  

Anjeun tiasa ngalereskeun sadaya masalah ieu kalayan gampang - ngan ukur tweak pamundut Convert Sample File. Sadaya panyesuaian anu urang lakukeun bakal otomatis digolongkeun kana fungsi file Convert anu aya hubunganana, anu hartosna éta bakal dianggo engké nalika ngimpor data tina unggal file.

Ku muka pamundut Ngarobih file sampel, tambahkeun léngkah pikeun nyaring baris anu teu dipikabutuh (contona, ku kolom Column2) sareng naékkeun judul kalayan tombol Paké baris kahiji salaku lulugu (Paké baris kahiji salaku lulugu). tabél bakal kasampak leuwih hadé.

Supados kolom tina file béda pikeun otomatis pas handapeun silih engké, maranéhna kudu ngaranna sarua. Anjeun tiasa ngalakukeun renaming massa sapertos numutkeun diréktori anu parantos didamel sareng hiji garis M-code. Hayu urang pencét deui tombol f_x dina bar rumus sareng tambahkeun fungsi pikeun robih:

= Table.RenameColumns(#"Elevated Headers", Headers, MissingField.Ignore)

Pungsi ieu nyokot tabel ti hambalan saméméhna headers elevated sarta ngaganti ngaran sakabeh kolom di dinya nurutkeun daptar lookup nested headline. Argumen katilu MissingField.Abaikan diperlukeun ku kituna dina maranéhanana lulugu anu aya dina diréktori, tapi teu dina tabél, kasalahan teu lumangsung.

Sabenerna, éta kabéh.

Balik deui kana pamundut laporan urang bakal ningali gambar anu béda pisan - langkung saé tibatan anu sateuacana:

• Naon Power Query, Power Pivot, Power BI sareng kunaon pangguna Excel peryogi aranjeunna
  
• Ngumpulkeun data tina sadaya file dina polder anu dipasihkeun
• Ngumpulkeun data tina sakabéh lambar buku kana hiji méja