Maak tabellen met verschillende koppen uit meerdere boeken

Formulering van het probleem

We hebben verschillende bestanden (in ons voorbeeld – 4 stuks, in het algemeen – zoveel als je wilt) in één map Rapporten:

Binnenin zien deze bestanden er als volgt uit:

Waarin:

• Het gegevensblad dat we nodig hebben, wordt altijd genoemd Foto's, maar kan overal in de werkmap staan.
  
• Voorbij het blad Foto's Elk boek kan andere bladen hebben.
• Tabellen met gegevens hebben een ander aantal rijen en kunnen beginnen met een andere rij op het werkblad.
• De namen van dezelfde kolommen in verschillende tabellen kunnen verschillen (bijvoorbeeld Hoeveelheid = Hoeveelheid = Aantal).
• Kolommen in tabellen kunnen in een andere volgorde worden gerangschikt.

Taak: verzamel verkoopgegevens uit alle bestanden van het blad Foto's in één gemeenschappelijke tabel om er vervolgens een samenvatting of andere analyses op te bouwen.

Stap 1. Een directory met kolomnamen voorbereiden

Het eerste dat u moet doen, is een naslagwerk maken met alle mogelijke opties voor kolomnamen en hun juiste interpretatie:

We zetten deze lijst om in een dynamische "slimme" tabel met behulp van de knop Opmaken als tabel op het tabblad Home (Home - Opmaken als tabel) of sneltoets Ctrl+T en laad het in Power Query met de opdracht Gegevens – Uit tabel/bereik (Gegevens — Uit tabel/bereik). In recente versies van Excel is het hernoemd naar: Met bladeren (Van blad).

In het Power Query-query-editorvenster verwijderen we traditioneel de stap Type gewijzigd en voeg in plaats daarvan een nieuwe stap toe door op de knop te klikken f_xin de formulebalk (als het niet zichtbaar is, dan kunt u het inschakelen op het tabblad Beoordeling) en voer de formule daar in de ingebouwde Power Query-taal M in:

=Tabel.ToRows(Bron)

Deze opdracht converteert degene die in de vorige stap is geladen bron referentietabel in een lijst die bestaat uit geneste lijsten (List), die elk op hun beurt een paar waarden zijn Het was-werd uit één regel:

We zullen dit soort gegevens iets later nodig hebben, wanneer we de kopteksten van alle geladen tabellen massaal hernoemen.

Selecteer na het voltooien van de conversie de opdrachten Home — Sluiten en laden — Sluiten en laden in… en type import Maak gewoon een verbinding (Home — Sluiten&Laden — Sluiten&Laden naar… — Alleen verbinding maken) en ga terug naar Excel.

Stap 2. We laden alles van alle bestanden zoals het is

Laten we nu de inhoud van al onze bestanden uit de map laden - voor nu, zoals het is. Teams kiezen Gegevens – Gegevens ophalen – Uit bestand – Uit map (Gegevens — Gegevens ophalen — Uit bestand — Uit map) en dan de map waar onze bronboeken staan.

Klik in het voorbeeldvenster op Converteren (Transformeren) or Veranderen (Edit):

En vouw vervolgens de inhoud van alle gedownloade bestanden uit (Binair) knop met dubbele pijlen in de kolomkop Content:

Power Query op het voorbeeld van het eerste bestand (Vostok.xlsx) zal ons de naam vragen van het blad dat we uit elke werkmap willen halen – kies Foto's en druk op OK:

Daarna zullen (in feite) verschillende gebeurtenissen plaatsvinden die niet duidelijk zijn voor de gebruiker, waarvan de gevolgen duidelijk zichtbaar zijn in het linkerpaneel:

1. Power Query haalt het eerste bestand uit de map (we zullen het hebben) Vostok.xlsx — zien Bestandsvoorbeeld) als voorbeeld en importeert de inhoud ervan door een query te maken Voorbeeldbestand converteren. Deze query heeft enkele eenvoudige stappen, zoals: bron (toegang tot bestanden) Navigatie (bladselectie) en eventueel het verhogen van de titels. Dit verzoek kan alleen gegevens uit één specifiek bestand laden Vostok.xlsx.
   
2. Op basis van dit verzoek wordt de bijbehorende functie gemaakt Converteer bestand (aangegeven door een karakteristiek pictogram) f_x), waarbij het bronbestand niet langer een constante is, maar een variabele waarde - een parameter. Deze functie kan dus gegevens extraheren uit elk boek dat we erin schuiven als argument.
   
3. De functie wordt op zijn beurt toegepast op elk bestand (binair) uit de kolom Content – step is hiervoor verantwoordelijk Aangepaste functie oproepen in onze zoekopdracht die een kolom toevoegt aan de lijst met bestanden Converteer bestand met importresultaten uit elke werkmap:

   

4. Extra kolommen worden verwijderd.
5. De inhoud van geneste tabellen wordt uitgevouwen (stap Uitgebreide tabelkolom) – en we zien de eindresultaten van het verzamelen van gegevens uit alle boeken:

   
   

Stap 3. Schuren

De vorige screenshot laat duidelijk zien dat de directe montage "as is" van slechte kwaliteit bleek te zijn:

• De kolommen zijn omgekeerd.
• Veel extra lijnen (leeg en niet alleen).
• Tabelkoppen worden niet gezien als kopteksten en worden vermengd met gegevens.
  

U kunt al deze problemen heel eenvoudig oplossen - pas de query Voorbeeldbestand converteren aan. Alle aanpassingen die we eraan doen, vallen automatisch in de bijbehorende functie Bestand converteren, wat betekent dat ze later worden gebruikt bij het importeren van gegevens uit elk bestand.

Door een verzoek te openen Voorbeeldbestand converteren, voeg stappen toe om onnodige rijen te filteren (bijvoorbeeld op kolom) Column2) en de koppen verhogen met de knop Gebruik de eerste regel als koptekst (Gebruik de eerste rij als kopteksten). De tafel zal er veel beter uitzien.

Om ervoor te zorgen dat kolommen uit verschillende bestanden later automatisch onder elkaar passen, moeten ze dezelfde naam hebben. U kunt zo'n massale hernoeming uitvoeren volgens een eerder aangemaakte directory met één regel M-code. Laten we nogmaals op de knop drukken f_x in de formulebalk en voeg een functie toe om te wijzigen:

= Table.RenameColumns(#”Verhoogde Headers”, Headers, MissingField.Ignore)

Deze functie haalt de tabel uit de vorige stap Verhoogde koppen en hernoemt alle kolommen erin volgens de geneste opzoeklijst Headlines. derde argument MissingField.Negeren is nodig zodat op die koppen die in de directory staan, maar niet in de tabel, geen fout optreedt.

Eigenlijk is dat alles.

Terugkerend naar het verzoek Rapporten we zullen een heel ander beeld zien - veel mooier dan de vorige:

• Wat is Power Query, Power Pivot, Power BI en waarom heeft een Excel-gebruiker ze nodig?
  
• Gegevens verzamelen van alle bestanden in een bepaalde map
• Gegevens van alle bladen van het boek in één tabel verzamelen