Koostage mitmest raamatust erinevate päistega tabeleid

Probleemi sõnastamine

Meil on ühes kaustas mitu faili (meie näites - 4 tükki, üldiselt - nii palju kui soovite) Aruanded:

Sees näevad need failid välja järgmised:

Kusjuures:

• Andmeleht, mida vajame, kutsutakse alati välja Fotod, kuid see võib olla töövihikus suvalises kohas.
  
• Väljaspool lina Fotod Igal raamatul võib olla teisi lehti.
• Andmeid sisaldavatel tabelitel on erinev arv ridu ja need võivad töölehel alata erineva reaga.
• Samade veergude nimed erinevates tabelites võivad erineda (näiteks Kogus = Kogus = Kogus).
• Tabelite veerge saab paigutada erinevas järjekorras.

Ülesanne: koguda lehelt kõikidest failidest müügiandmed Fotod ühte ühisesse tabelisse, et seejärel koostada sellele kokkuvõte või muu analüütika.

Samm 1. Veergude nimede kataloogi ettevalmistamine

Esimese asjana tuleb ette valmistada teatmeteos, mis sisaldab kõiki võimalikke veergude nimede valikuid ja nende õiget tõlgendamist:

Teisendame selle loendi dünaamiliseks "nutikaks" tabeliks, kasutades vahekaardil nuppu Vorminda tabelina Avaleht (Avaleht — vorminda tabelina) või klaviatuuri otsetee Ctrl+T ja laadige see käsuga Power Querysse Andmed – tabelist/vahemikust (Andmed – tabelist/vahemikust). Exceli viimastes versioonides on see ümber nimetatud Lehtedega (Lehelt).

Power Query päringuredaktori aknas kustutame tavapäraselt sammu Muudetud tüüp ja lisage selle asemele uus samm, klõpsates nuppu f_xvalemiribal (kui see pole nähtav, saate selle lubada vahekaardil Ülevaade) ja sisestage sinna valem sisseehitatud Power Query keeles M:

=Tabel.ToRiud(Allikas)

See käsk teisendab eelmises etapis laaditud käsu allikas viitetabel loendiks, mis koosneb pesastatud loenditest (List), millest igaüks on omakorda väärtuspaar See oli-sai ühest reast:

Seda tüüpi andmeid vajame veidi hiljem, kui kõigi laaditud tabelite päiseid massiliselt ümber nimetame.

Pärast teisendamise lõpetamist valige käsud Avaleht — sulge ja laadi — sulge ja laadi... ja impordi liik Looge lihtsalt ühendus (Avaleht — Sule&laadi — Sule&laadi... — Loo ainult ühendus) ja minge tagasi Excelisse.

2. samm. Laadime kõik failid nii, nagu on

Nüüd laadime kõigi failide sisu kaustast – praegu, nagu on. Meeskondade valimine Andmed – hangi andmed – failist – kaustast (Andmed — hangi andmed — failist — kaustast) ja seejärel kaust, kus on meie lähteraamatud.

Klõpsake eelvaate aknas Muutma (teisendus) or Muutma (Edit):

Seejärel laiendage kõigi allalaaditud failide sisu (binaarne) nupp topeltnooltega veeru päises sisu:

Power Query esimese faili näitel (Vostok.xlsx) küsib meilt igast töövihikust lehe nime – valige Fotod ja vajuta OK:

Pärast seda (tegelikult) toimuvad mitmed kasutajale ebaselged sündmused, mille tagajärjed on vasakpoolsel paneelil selgelt nähtavad:

1. Power Query võtab kaustast esimese faili (meil on see Vostok.xlsx — vaata Faili näide) näiteks ja impordib selle sisu päringu loomisega Teisenda näidisfail. Sellel päringul on mõned lihtsad sammud, näiteks allikas (juurdepääs failile) NAVIGATSIOON (lehevalik) ja võib-olla pealkirjade tõstmine. See päring saab laadida andmeid ainult ühest konkreetsest failist Vostok.xlsx.
   
2. Selle päringu alusel luuakse sellega seotud funktsioon Teisenda fail (tähistatud iseloomuliku ikooniga f_x), kus lähtefail ei ole enam konstant, vaid muutuja väärtus – parameeter. Seega saab see funktsioon välja võtta andmeid igast raamatust, mille me sellesse argumendina libistame.
   
3. Funktsiooni rakendatakse kordamööda igale veeru failile (binaarne). sisu – selle eest vastutab samm Helista kohandatud funktsioonile meie päringus, mis lisab failide loendisse veeru Teisenda fail igast töövihikust imporditud tulemustega:

   

4. Täiendavad veerud eemaldatakse.
5. Pesastatud tabelite sisu on laiendatud (samm Laiendatud tabeli veerg) – ja näeme kõigi raamatute andmete kogumise lõpptulemusi:

   
   

Samm 3. Lihvimine

Eelmine ekraanipilt näitab selgelt, et otsene kokkupanek "nagu on" osutus halva kvaliteediga:

• Veerud on tagurpidi.
• Palju lisaridu (tühjad ja mitte ainult).
• Tabelipäiseid ei tajuta päistena ja need segatakse andmetega.
  

Saate kõik need probleemid väga lihtsalt lahendada – lihtsalt näpistage päringut Näidisfaili teisendamine. Kõik selles tehtavad kohandused langevad automaatselt seotud faili teisendamisfunktsiooni, mis tähendab, et neid kasutatakse hiljem igast failist andmete importimisel.

Päringu avamisega Teisenda näidisfail, lisage samme mittevajalike ridade filtreerimiseks (nt veeru järgi Column2) ja pealkirjade tõstmine nupuga Kasutage esimest rida päistena (Kasutage esimest rida päistena). Tabel näeb palju parem välja.

Selleks, et erinevatest failidest pärit veerud hiljem automaatselt üksteise alla mahuksid, tuleb neile anda sama nimi. Sellise massilise ümbernimetamise saate teha eelnevalt loodud kataloogi järgi ühe M-koodi reaga. Vajutame uuesti nuppu f_x valemiribale ja lisage muutmiseks funktsioon:

= Tabel.Ümbernimeta veerud (#"kõrgendatud päised", päised, puuduv väli. Ignoreeri)

See funktsioon võtab tabeli eelmisest etapist Kõrgendatud päised ja nimetab kõik selles olevad veerud ümber vastavalt pesastatud otsinguloendile Pealkirjad. Kolmas argument Puuduv väli. Ignoreeri on vajalik selleks, et nende pealkirjade puhul, mis on kataloogis, kuid pole tabelis, ei tekiks viga.

Tegelikult on see kõik.

Taotluse juurde tagasi pöördudes Aruanded näeme hoopis teistsugust pilti – palju ilusamat kui eelmine:

• Mis on Power Query, Power Pivot, Power BI ja miks Exceli kasutaja neid vajab
  
• Andmete kogumine kõigist antud kaustas olevatest failidest
• Andmete kogumine raamatu kõigilt lehtedelt ühte tabelisse