Bumuo ng mga talahanayan na may iba't ibang mga header mula sa maraming aklat

Pagbubuo ng problema

Mayroon kaming ilang mga file (sa aming halimbawa - 4 na piraso, sa pangkalahatang kaso - hangga't gusto mo) sa isang folder Ulat:

Sa loob, ganito ang hitsura ng mga file na ito:

Kung saan:

• Ang data sheet na kailangan namin ay palaging tinatawag pics, ngunit maaaring nasaanman sa workbook.
  
• Lampas sa sheet pics Ang bawat libro ay maaaring may iba pang mga sheet.
• Ang mga talahanayan na may data ay may ibang bilang ng mga row at maaaring magsimula sa ibang row sa worksheet.
• Maaaring magkaiba ang mga pangalan ng parehong column sa iba't ibang talahanayan (halimbawa, Dami = Dami = Dami).
• Maaaring isaayos ang mga column sa mga talahanayan sa ibang pagkakasunud-sunod.

Gawain: mangolekta ng data ng mga benta mula sa lahat ng mga file mula sa sheet pics sa isang karaniwang talahanayan upang makabuo ng buod o anumang iba pang analytics dito.

Hakbang 1. Paghahanda ng direktoryo ng mga pangalan ng column

Ang unang bagay na dapat gawin ay maghanda ng isang reference na libro na may lahat ng posibleng opsyon para sa mga pangalan ng column at ang kanilang tamang interpretasyon:

Kino-convert namin ang listahang ito sa isang dynamic na "smart" na talahanayan gamit ang Format bilang table na button sa tab Tahanan (Home — Format bilang Talahanayan) o keyboard shortcut Ctrl+T at i-load ito sa Power Query gamit ang command Data – Mula sa Talahanayan/Saklaw (Data — Mula sa Talahanayan/Saklaw). Sa mga kamakailang bersyon ng Excel, pinalitan ito ng pangalan sa May mga dahon (Mula sa sheet).

Sa window ng editor ng query ng Power Query, tradisyonal naming tinatanggal ang hakbang Binagong Uri at magdagdag ng bagong hakbang sa halip na ito sa pamamagitan ng pag-click sa button f_xsa formula bar (kung hindi ito nakikita, maaari mo itong paganahin sa tab Pagsusuri) at ilagay ang formula doon sa built-in na Power Query na wika M:

=Table.ToRows(Source)

Iko-convert ng command na ito ang na-load sa nakaraang hakbang pinagmulan reference table sa isang listahan na binubuo ng mga nested list (Listahan), na ang bawat isa, naman, ay isang pares ng mga halaga Ito ay-naging mula sa isang linya:

Kakailanganin namin ang ganitong uri ng data sa ibang pagkakataon, kapag pinapalitan ng maramihang pangalan ang mga header mula sa lahat ng na-load na mga talahanayan.

Pagkatapos makumpleto ang conversion, piliin ang mga command Tahanan — Isara at I-load — Isara at I-load sa… at uri ng pag-import Gumawa lang ng koneksyon (Home — Isara&I-load — Isara&I-load sa… — Lumikha lamang ng koneksyon) at bumalik sa Excel.

Hakbang 2. Nilo-load namin ang lahat mula sa lahat ng mga file

Ngayon, i-load natin ang mga nilalaman ng lahat ng ating mga file mula sa folder – sa ngayon, gaya ng dati. Pagpili ng mga koponan Data – Kumuha ng data – Mula sa file – Mula sa folder (Data — Kumuha ng Data — Mula sa file — Mula sa folder) at pagkatapos ay ang folder kung saan ang aming mga mapagkukunang libro.

Sa window ng preview, i-click Palitan (Pagbabago) or Baguhin (I-edit):

At pagkatapos ay palawakin ang mga nilalaman ng lahat ng na-download na mga file (Binary) button na may dobleng arrow sa heading ng column nilalaman:

Power Query sa halimbawa ng unang file (Vostok.xlsx) tatanungin kami ng pangalan ng sheet na gusto naming kunin mula sa bawat workbook – pumili pics at pindutin ang OK:

Pagkatapos nito (sa katunayan), maraming mga kaganapan na hindi halata sa gumagamit ang magaganap, ang mga kahihinatnan nito ay malinaw na nakikita sa kaliwang panel:

1. Kukunin ng Power Query ang unang file mula sa folder (magkakaroon tayo nito Vostok.xlsx — makita Halimbawa ng file) bilang isang halimbawa at ini-import ang nilalaman nito sa pamamagitan ng paglikha ng query I-convert ang sample na file. Ang query na ito ay magkakaroon ng ilang simpleng hakbang tulad ng pinagmulan (pag-access sa file) nabigasyon (pagpili ng sheet) at posibleng itaas ang mga pamagat. Ang kahilingang ito ay maaari lamang mag-load ng data mula sa isang partikular na file Vostok.xlsx.
   
2. Batay sa kahilingang ito, malilikha ang function na nauugnay dito I-convert ang file (ipinahiwatig ng isang icon na katangian f_x), kung saan ang source file ay hindi na magiging pare-pareho, ngunit isang variable na halaga - isang parameter. Kaya, ang function na ito ay maaaring mag-extract ng data mula sa anumang aklat na ilalagay namin dito bilang isang argumento.
   
3. Ang function ay ilalapat naman sa bawat file (Binary) mula sa column nilalaman – ang hakbang ay responsable para dito Tumawag sa custom na function sa aming query na nagdaragdag ng column sa listahan ng mga file I-convert ang file na may mga resulta ng pag-import mula sa bawat workbook:

   

4. Ang mga karagdagang column ay tinanggal.
5. Ang mga nilalaman ng mga nested table ay pinalawak (step Pinahabang haligi ng talahanayan) – at nakikita namin ang mga huling resulta ng pangongolekta ng data mula sa lahat ng aklat:

   
   

Hakbang 3. Sanding

Ang nakaraang screenshot ay malinaw na nagpapakita na ang direktang pagpupulong "as is" ay naging mahina ang kalidad:

• Ang mga hanay ay baligtad.
• Maraming dagdag na linya (walang laman at hindi lamang).
• Ang mga header ng talahanayan ay hindi nakikita bilang mga header at inihahalo sa data.
  

Madali mong ayusin ang lahat ng problemang ito – i-tweak lang ang query sa Convert Sample File. Lahat ng pagsasaayos na gagawin namin dito ay awtomatikong mahuhulog sa nauugnay na function ng Convert file, na nangangahulugang gagamitin ang mga ito sa ibang pagkakataon kapag nag-i-import ng data mula sa bawat file.

Sa pamamagitan ng pagbubukas ng isang kahilingan I-convert ang sample na file, magdagdag ng mga hakbang upang i-filter ang mga hindi kinakailangang row (halimbawa, ayon sa column Column2) at itinaas ang mga heading gamit ang button Gamitin ang unang linya bilang mga header (Gamitin ang unang hilera bilang mga header). Magiging mas maganda ang hitsura ng mesa.

Upang ang mga column mula sa iba't ibang file ay awtomatikong magkasya sa ilalim ng isa't isa sa ibang pagkakataon, dapat pareho ang pangalan ng mga ito. Maaari kang magsagawa ng gayong mass renaming ayon sa isang naunang ginawang direktoryo na may isang linya ng M-code. Pindutin natin muli ang pindutan f_x sa formula bar at magdagdag ng isang function upang baguhin:

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

Kinukuha ng function na ito ang talahanayan mula sa nakaraang hakbang Mga nakataas na header at pinapalitan ang pangalan ng lahat ng column dito ayon sa nested lookup list Mga pamagat. Pangatlong argumento MissingField.Huwag pansinin ay kinakailangan upang sa mga heading na iyon na nasa direktoryo, ngunit wala sa talahanayan, ang isang error ay hindi mangyayari.

Actually, yun lang.

Pagbabalik sa kahilingan Ulat makikita natin ang isang ganap na naiibang larawan – mas maganda kaysa sa nauna:

• Ano ang Power Query, Power Pivot, Power BI at kung bakit kailangan ito ng isang user ng Excel
  
• Pagkolekta ng data mula sa lahat ng mga file sa isang naibigay na folder
• Pagkolekta ng data mula sa lahat ng mga sheet ng libro sa isang talahanayan