Bou tabelle met verskillende opskrifte uit verskeie boeke

Formulering van die probleem

Ons het verskeie lêers (in ons voorbeeld - 4 stukke, in die algemene geval - soveel as wat jy wil) in een gids Berigte:

Binne lyk hierdie lêers soos volg:

Waarin:

• Die datablad wat ons benodig word altyd genoem Fotos, maar kan enige plek in die werkboek wees.
  
• Anderkant die laken Fotos Elke boek kan ander velle hê.
• Tabelle met data het 'n ander aantal rye en kan met 'n ander ry op die werkblad begin.
• Die name van dieselfde kolomme in verskillende tabelle kan verskil (byvoorbeeld, Hoeveelheid = Hoeveelheid = Hoeveelheid).
• Kolomme in tabelle kan in 'n ander volgorde gerangskik word.

Taak: versamel verkoopsdata van alle lêers vanaf die blad Fotos in een algemene tabel om vervolgens 'n opsomming of enige ander analise daarop te bou.

Stap 1. Berei 'n gids van kolomname voor

Die eerste ding om te doen is om 'n naslaanboek voor te berei met alle moontlike opsies vir kolomname en hul korrekte interpretasie:

Ons skakel hierdie lys om in 'n dinamiese "slim" tabel deur die Formateer as tabel-knoppie op die oortjie te gebruik What is This (Tuis — Formateer as tabel) of sleutelbordkortpad Ctrl+T en laai dit in Power Query met die opdrag Data – Van tabel/reeks (Data – van tabel/reeks). In onlangse weergawes van Excel is dit hernoem na Met blare (Van blad).

In die Power Query-navraagredigeerdervenster vee ons tradisioneel die stap uit Tipe verander en voeg 'n nuwe stap in plaas daarvan by deur op die knoppie te klik f_xin die formulebalk (as dit nie sigbaar is nie, kan jy dit op die oortjie aktiveer Resensie) en voer die formule daar in in die ingeboude Power Query-taal M:

=Table.ToRows(Bron)

Hierdie opdrag sal die een omskakel wat in die vorige stap gelaai is Bron verwysingstabel in 'n lys wat bestaan ​​uit geneste lyste (Lys), wat elkeen op sy beurt 'n paar waardes is Dit was-word van een reël:

Ons sal hierdie tipe data 'n bietjie later nodig hê, wanneer opskrifte van alle gelaaide tabelle massa hernoem word.

Nadat u die omskakeling voltooi het, kies die opdragte Tuis — Maak toe en laai — Maak toe en laai in... en tipe invoer Skep net 'n verbinding (Tuis — Maak toe en laai — Maak toe en laai na … — Skep slegs verbinding) en gaan terug na Excel.

Stap 2. Ons laai alles van alle lêers soos dit is

Kom ons laai nou die inhoud van al ons lêers uit die gids – vir nou, soos dit is. Die keuse van spanne Data – Kry data – Van lêer – Van gids (Data - Kry Data - Van lêer - Van gids) en dan die gids waar ons bronboeke is.

Klik in die voorskouvenster Skakel (Transformeer) or verandering (Edit):

En brei dan die inhoud van alle afgelaaide lêers uit (Binêr) knoppie met dubbele pyle in die kolomopskrif inhoud:

Power Query op die voorbeeld van die eerste lêer (Vostok.xlsx) sal ons die naam vra van die blad wat ons uit elke werkboek wil neem – kies Fotos en druk OK:

Daarna (in werklikheid) sal verskeie gebeure plaasvind wat nie duidelik vir die gebruiker is nie, waarvan die gevolge duidelik sigbaar is in die linkerpaneel:

1. Power Query sal die eerste lêer uit die gids neem (ons sal dit hê Vostok.xlsx — sien Lêer voorbeeld) as 'n voorbeeld en voer die inhoud daarvan in deur 'n navraag te skep Skakel voorbeeldlêer om. Hierdie navraag sal 'n paar eenvoudige stappe hê soos Bron (lêer toegang) navigasie (bladkeuse) en moontlik die titels verhoog. Hierdie versoek kan slegs data vanaf een spesifieke lêer laai Vostok.xlsx.
   
2. Op grond van hierdie versoek sal die funksie wat daarmee geassosieer word, geskep word Skakel lêer om (aangedui deur 'n kenmerkende ikoon f_x), waar die bronlêer nie meer 'n konstante sal wees nie, maar 'n veranderlike waarde - 'n parameter. Hierdie funksie kan dus data uit enige boek onttrek wat ons as 'n argument daarin inskuif.
   
3. Die funksie sal om die beurt toegepas word op elke lêer (Binêr) vanaf die kolom inhoud – stap is hiervoor verantwoordelik Bel persoonlike funksie in ons navraag wat 'n kolom by die lys lêers voeg Skakel lêer om met invoerresultate van elke werkboek:

   

4. Ekstra kolomme word verwyder.
5. Die inhoud van geneste tabelle word uitgebrei (stap Uitgebreide tabelkolom) – en ons sien die finale resultate van data-insameling uit alle boeke:

   
   

Stap 3. Skuur

Die vorige skermkiekie toon duidelik dat die direkte samestelling "soos dit is" van swak gehalte geblyk het te wees:

• Die kolomme is omgekeer.
• Baie ekstra lyne (leeg en nie net nie).
• Tabelopskrifte word nie as opskrifte beskou nie en word met data gemeng.
  

Jy kan al hierdie probleme baie maklik regstel – pas net die Convert Sample File-navraag aan. Alle aanpassings wat ons daaraan maak, sal outomaties in die geassosieerde Skakel lêer-funksie val, wat beteken dat hulle later gebruik sal word wanneer data vanaf elke lêer ingevoer word.

Deur 'n versoek oop te maak Skakel voorbeeldlêer om, voeg stappe by om onnodige rye te filtreer (byvoorbeeld volgens kolom Column2) en verhoog die opskrifte met die knoppie Gebruik eerste reël as opskrifte (Gebruik eerste ry as opskrifte). Die tafel sal baie beter lyk.

Om kolomme van verskillende lêers later outomaties onder mekaar in te pas, moet hulle dieselfde genoem word. U kan so 'n massa-hernoeming uitvoer volgens 'n voorheen geskepde gids met een reël M-kode. Kom ons druk weer die knoppie f_x in die formulebalk en voeg 'n funksie by om te verander:

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

Hierdie funksie neem die tabel van die vorige stap af Verhoogde kopskrifte en hernoem alle kolomme daarin volgens die geneste opsoeklys nuus. Derde argument MissingField.Ignoreer is nodig sodat op daardie opskrifte wat in die gids is, maar nie in die tabel is nie, 'n fout nie voorkom nie.

Eintlik is dit al.

Keer terug na die versoek Berigte ons sal 'n heeltemal ander prentjie sien - baie mooier as die vorige een:

• Wat is Power Query, Power Pivot, Power BI en waarom 'n Excel-gebruiker dit nodig het
  
• Versamel data van alle lêers in 'n gegewe vouer
• Versamel data van alle velle van die boek in een tabel