Búðu til töflur með mismunandi hausum úr mörgum bókum

Mótun vandans

Við höfum nokkrar skrár (í dæminu okkar - 4 stykki, í almennu tilfellinu - eins margar og þú vilt) í einni möppu Skýrslur:

Að innan líta þessar skrár svona út:

Þar sem:

• Gagnablaðið sem við þurfum er alltaf kallað Myndir, en getur verið hvar sem er í vinnubókinni.
  
• Fyrir utan blaðið Myndir Hver bók getur haft önnur blöð.
• Töflur með gögnum hafa mismunandi fjölda lína og geta byrjað á annarri línu á vinnublaðinu.
• Nöfn sömu dálka í mismunandi töflum geta verið mismunandi (td. Magn = Magn = Magn).
• Hægt er að raða dálkum í töflum í aðra röð.

Verkefni: safna sölugögnum úr öllum skrám af blaðinu Myndir í eina sameiginlega töflu til að byggja síðan saman samantekt eða aðra greiningu á henni.

Skref 1. Undirbúningur möppu yfir dálkanöfn

Það fyrsta sem þarf að gera er að útbúa uppflettirit með öllum mögulegum valkostum fyrir dálknöfn og rétta túlkun þeirra:

Við umbreytum þessum lista í kraftmikla „snjöll“ töflu með því að nota Format as table hnappinn á flipanum Heim (Heima - Snið sem töflu) eða flýtilykla Ctrl+T og hlaðið því inn í Power Query með skipuninni Gögn – Frá töflu/sviði (Gögn — úr töflu/sviði). Í nýlegum útgáfum af Excel hefur það verið breytt í Með laufblöðum (Úr blaði).

Í Power Query fyrirspurn ritstjóra glugganum eyðum við venjulega skrefinu Breytt gerð og bættu við nýju skrefi í stað þess með því að smella á hnappinn f_xí formúlustikunni (ef það er ekki sýnilegt, þá geturðu virkjað það á flipanum Review) og sláðu inn formúluna þar á innbyggða Power Query tungumálinu M:

=Table.ToRows(Source)

Þessi skipun mun umbreyta þeirri sem var hlaðin í fyrra skrefi Heimild tilvísunartöflu í lista sem samanstendur af hreiðri listum (List), sem hver um sig er par af gildum Það var-varð úr einni línu:

Við munum þurfa þessa tegund af gögnum aðeins síðar, þegar massa endurnefna hausa úr öllum hlaðnum töflum.

Eftir að umbreytingunni er lokið skaltu velja skipanirnar Heim — Loka og hlaða — Loka og hlaða inn... og tegund innflutnings Búðu bara til tengingu (Heima — Loka&hlaða — Loka&hlaða til... — Búa aðeins til tengingu) og farðu aftur í Excel.

Skref 2. Við hleðum öllu úr öllum skrám eins og þær eru

Nú skulum við hlaða innihald allra skráa okkar úr möppunni - í bili, eins og er. Að velja lið Gögn – Fáðu gögn – Úr skrá – Úr möppu (Gögn — Fá gögn — Úr skrá — Úr möppu) og svo möppuna þar sem heimildabækurnar okkar eru.

Í forskoðunarglugganum, smelltu á Umbreyta (Breyta) or Breyta (Breyta):

Og stækkaðu síðan innihald allra niðurhalaðra skráa (Tvöfaldur) hnappur með tvöföldum örvum í dálkafyrirsögninni innihald:

Power Query á dæmi um fyrstu skrána (Vostok.xlsx) mun spyrja okkur um nafn blaðsins sem við viljum taka úr hverri vinnubók – veldu Myndir og ýttu á OK:

Eftir það (reyndar) munu nokkrir atburðir eiga sér stað sem eru ekki augljósir fyrir notandann, afleiðingar þeirra eru greinilega sýnilegar á vinstri spjaldinu:

1. Power Query mun taka fyrstu skrána úr möppunni (við munum hafa hana Vostok.xlsx — sjá Dæmi um skrá) sem dæmi og flytur inn innihald þess með því að búa til fyrirspurn Umbreyta sýnishornsskrá. Þessi fyrirspurn mun hafa nokkur einföld skref eins og Heimild (aðgangur að skrá) Navigation (blaðaval) og hugsanlega hækka titlana. Þessi beiðni getur aðeins hlaðið gögnum úr einni tiltekinni skrá Vostok.xlsx.
   
2. Byggt á þessari beiðni verður aðgerðin sem henni tengist búin til Umbreyta skrá (táknað með einkennandi táknmynd f_x), þar sem frumskráin verður ekki lengur fasti, heldur breytilegt gildi - færibreyta. Þannig getur þessi aðgerð dregið gögn úr hvaða bók sem er sem við rennum inn í hana sem rök.
   
3. Aðgerðinni verður beitt aftur á móti á hverja skrá (Binary) úr dálknum innihald – Step ber ábyrgð á þessu Hringdu í sérsniðna aðgerð í fyrirspurn okkar sem bætir dálki við listann yfir skrár Umbreyta skrá með innflutningsniðurstöðum úr hverri vinnubók:

   

4. Auka dálkar eru fjarlægðir.
5. Innihald hreiðraðra taflna er stækkað (skref Útvíkkaður töfludálkur) – og við sjáum lokaniðurstöður gagnasöfnunar úr öllum bókum:

   
   

Skref 3. Slípun

Fyrra skjáskotið sýnir greinilega að bein samsetning „eins og hún er“ reyndist vera af lélegum gæðum:

• Dálkunum er snúið við.
• Margar aukalínur (tómar og ekki bara).
• Töfluhausar eru ekki álitnir sem hausar og þeim er blandað saman við gögn.
  

Þú getur lagað öll þessi vandamál mjög auðveldlega - fínstilltu bara umbreyta sýnishornsskrárfyrirspurninni. Allar breytingar sem við gerum á því falla sjálfkrafa inn í tilheyrandi Breyta skrá aðgerð, sem þýðir að þær verða notaðar síðar þegar gögn eru flutt inn úr hverri skrá.

Með því að opna beiðni Umbreyta sýnishornsskrá, bættu við skrefum til að sía óþarfa línur (til dæmis eftir dálki Column2) og hækka fyrirsagnirnar með hnappinum Notaðu fyrstu línu sem haus (Notaðu fyrstu línu sem haus). Taflan mun líta miklu betur út.

Til þess að dálkar úr mismunandi skrám passi sjálfkrafa undir hver annan síðar verða þeir að heita eins. Þú getur framkvæmt slíka massa endurnefna í samræmi við áður búið til möppu með einni línu af M-kóða. Við skulum ýta á hnappinn aftur f_x í formúlustikunni og bættu við falli til að breyta:

= Table.RenameColumns(#“Hækkaðir hausar“, hausar, MissingField.Ignore)

Þessi aðgerð tekur töfluna frá fyrra skrefi Hækkaðir hausar og endurnefnir alla dálka í henni í samræmi við hreiðraða uppflettilistann Fyrirsagnir. Þriðja rök MissingField.Ignore þarf svo að á þeim fyrirsögnum sem eru í skránni, en eru ekki í töflunni, komi ekki upp villa.

Reyndar, það er allt.

Vend aftur að beiðninni Skýrslur við munum sjá allt aðra mynd – miklu flottari en sú fyrri:

• Hvað er Power Query, Power Pivot, Power BI og hvers vegna Excel notandi þarfnast þeirra
  
• Að safna gögnum úr öllum skrám í tiltekinni möppu
• Að safna gögnum úr öllum blöðum bókarinnar í eina töflu