Bygg tabeller med forskjellige overskrifter fra flere bøker

Formulering av problemet

Vi har flere filer (i vårt eksempel - 4 stykker, i det generelle tilfellet - så mange du vil) i en mappe Rapporter:

Innvendig ser disse filene slik ut:

Der:

• Databladet vi trenger kalles alltid Bilder, men kan være hvor som helst i arbeidsboken.
  
• Utover arket Bilder Hver bok kan ha andre ark.
• Tabeller med data har et annet antall rader og kan starte med en annen rad på regnearket.
• Navnene på de samme kolonnene i forskjellige tabeller kan variere (f.eks. Mengde = Antall = Antall).
• Kolonner i tabeller kan ordnes i en annen rekkefølge.

Oppgave: samle inn salgsdata fra alle filer fra arket Bilder i én felles tabell for deretter å bygge et sammendrag eller annen analyse på den.

Trinn 1. Klargjør en katalog med kolonnenavn

Det første du må gjøre er å utarbeide en oppslagsbok med alle mulige alternativer for kolonnenavn og deres korrekte tolkning:

Vi konverterer denne listen til en dynamisk "smart" tabell ved å bruke Formater som tabell-knappen på fanen Hjemprodukt (Hjem — Formater som tabell) eller hurtigtast Ctrl+T og last den inn i Power Query med kommandoen Data – Fra tabell/område (Data – fra tabell/område). I nyere versjoner av Excel har den fått nytt navn til Med blader (Fra ark).

I Power Query-spørringsredigeringsvinduet sletter vi tradisjonelt trinnet Endret type og legg til et nytt trinn i stedet for det ved å klikke på knappen f_xi formellinjen (hvis den ikke er synlig, kan du aktivere den på fanen Anmeldelse) og skriv inn formelen der i det innebygde Power Query-språket M:

=Table.ToRows(Source)

Denne kommandoen vil konvertere den som ble lastet i forrige trinn kilde referansetabell til en liste som består av nestede lister (List), som hver i sin tur er et par verdier Det var-ble fra en linje:

Vi vil trenge denne typen data litt senere, når vi skal gi nytt navn til overskrifter fra alle innlastede tabeller.

Etter å ha fullført konverteringen, velg kommandoene Hjem — Lukk og last — Lukk og last inn... og type import Bare opprette en forbindelse (Hjem — Lukk&last — Lukk&last til... — Opprett kun tilkobling) og gå tilbake til Excel.

Trinn 2. Vi laster inn alt fra alle filer som de er

La oss nå laste inn innholdet i alle filene våre fra mappen – foreløpig som den er. Å velge lag Data – Hent data – Fra fil – Fra mappe (Data — Hent Data — Fra fil — Fra mappe) og deretter mappen der kildebøkene våre er.

Klikk på i forhåndsvisningsvinduet Konverter (Forvandle) or Endring (Redigere):

Og utvide deretter innholdet i alle nedlastede filer (Binær) knapp med doble piler i kolonneoverskriften Innhold:

Power Query på eksemplet med den første filen (Vostok.xlsx) vil spørre oss navnet på arket vi vil ta fra hver arbeidsbok – velg Bilder og trykk OK:

Etter det (faktisk) vil flere hendelser som ikke er åpenbare for brukeren oppstå, hvis konsekvenser er tydelig synlige i venstre panel:

1. Power Query tar den første filen fra mappen (vi vil ha den Vostok.xlsx — se Eksempel på fil) som et eksempel og importerer innholdet ved å lage en spørring Konverter eksempelfil. Denne spørringen vil ha noen enkle trinn som kilde (filtilgang) Navigasjon (arkvalg) og eventuelt heve titlene. Denne forespørselen kan bare laste inn data fra én bestemt fil Vostok.xlsx.
   
2. Basert på denne forespørselen vil funksjonen knyttet til den bli opprettet Konverter fil (indikert med et karakteristisk ikon f_x), hvor kildefilen ikke lenger vil være en konstant, men en variabelverdi – en parameter. Dermed kan denne funksjonen trekke ut data fra en hvilken som helst bok som vi glir inn i den som et argument.
   
3. Funksjonen vil bli brukt etter tur på hver fil (binær) fra kolonnen Innhold – step er ansvarlig for dette Ring egendefinert funksjon i vår spørring som legger til en kolonne i listen over filer Konverter fil med import av resultater fra hver arbeidsbok:

   

4. Ekstra kolonner fjernes.
5. Innholdet i nestede tabeller utvides (trinn Utvidet tabellkolonne) – og vi ser de endelige resultatene av datainnsamling fra alle bøker:

   
   

Trinn 3. Sliping

Det forrige skjermbildet viser tydelig at den direkte monteringen "som den er" viste seg å være av dårlig kvalitet:

• Kolonnene er omvendt.
• Mange ekstra linjer (tomme og ikke bare).
• Tabelloverskrifter oppfattes ikke som overskrifter og er blandet med data.
  

Du kan fikse alle disse problemene veldig enkelt - bare finjuster spørringen Konverter eksempelfil. Alle justeringer som vi gjør på den vil automatisk falle inn i den tilhørende Konverter fil-funksjonen, noe som betyr at de vil bli brukt senere ved import av data fra hver fil.

Ved å åpne en forespørsel Konverter eksempelfil, legg til trinn for å filtrere unødvendige rader (for eksempel etter kolonne Column2) og heve overskriftene med knappen Bruk første linje som overskrifter (Bruk første rad som overskrifter). Bordet vil se mye bedre ut.

For at kolonner fra ulike filer automatisk skal passe under hverandre senere, må de ha samme navn. Du kan utføre en slik massedøping i henhold til en tidligere opprettet katalog med én linje med M-kode. La oss trykke på knappen igjen f_x i formellinjen og legg til en funksjon for å endre:

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

Denne funksjonen tar tabellen fra forrige trinn Forhøyede overskrifter og gir nytt navn til alle kolonnene i den i henhold til den nestede oppslagslisten Overskrifter. Tredje argument MissingField.Ignore er nødvendig slik at det ikke oppstår en feil på de overskriftene som er i katalogen, men ikke i tabellen.

Egentlig er det alt.

Går tilbake til forespørselen Rapporter vi vil se et helt annet bilde – mye finere enn det forrige:

• Hva er Power Query, Power Pivot, Power BI og hvorfor en Excel-bruker trenger dem
  
• Samle data fra alle filer i en gitt mappe
• Samle data fra alle arkene i boken i én tabell