Power Query võib olla suureks abiks, kui soovite ühendada mitu töövihikut üheks töövihikuks.
Oletame näiteks, et teil on müügiandmed erinevate piirkondade (ida, lääs, põhi ja lõuna) kohta. Saate Power Query abil ühendada need andmed erinevatest töövihikutest üheks tööleheks.
Kui teil on need töövihikud erinevates asukohtades/kaustades, on mõistlik need kõik ühte kausta teisaldada (või luua koopia ja panna see töövihiku koopia samasse kausta).
Nii et alustuseks on mul kaustas neli töövihikut (nagu allpool näidatud).
Nüüd tutvustan selles õpetuses kolme stsenaariumi, kus saate Power Query abil kombineerida erinevate töövihikute andmeid.
- Iga töövihiku andmed on Exceli tabelis ja kõigi tabelite nimed on samad.
- Igas töövihikus on andmed sama töölehe nimega. See võib juhtuda juhul, kui kõikides töövihikutes on leht nimega „kokkuvõte” või „andmed” ja soovite neid kõiki kombineerida.
- Igas töövihikus on palju lehti ja tabeleid ning soovite kombineerida konkreetseid tabeleid/lehti. See meetod võib olla kasulik ka siis, kui soovite kombineerida tabelit/lehti, millel pole järjepidevat nime.
Vaatame, kuidas nende töövihikute andmeid igal juhul kombineerida.
Igas töövihikus on andmed sama ülesehitusega Exceli tabelis
Allolev tehnika toimiks, kui teie Exceli tabelid on üles ehitatud sarnaselt (samad veerunimed).
Iga tabeli ridade arv võib varieeruda.
Ärge muretsege, kui mõnel Exceli tabelil on täiendavaid veerge. Saate mallina valida ühe tabelitest (või „võtme”, nagu Power Query seda nimetab) ja Power Query kasutaks seda kõigi teiste Exceli tabelite ühendamiseks.
Juhul kui teistes tabelites on täiendavaid veerge, eiratakse neid ja kombineeritakse ainult need, mis on määratud mallis/võtmes. Näiteks kui teie valitud malli/võtmete tabelis on 5 veergu ja mõnes teises töövihiku ühes tabelis on 2 täiendavat veergu, eiratakse neid täiendavaid veerge.
Nüüd on mul kaustas neli töövihikut, mida tahan kombineerida.
Allpool on tabel tabelist, mis mul ühes töövihikus on.
Siin on sammud nende töövihikute andmete ühendamiseks üheks töövihikuks (ühe tabelina).
- Minge vahekaardile Andmed.
- Klõpsake rühmas Hangi ja muuda rippmenüüd Uus päring.
- Hõljutage kursorit valikul „Failist” ja klõpsake nuppu „Kaustast”.
- Sisestage dialoogiboksis Kaust failide kausta failitee või klõpsake nuppu Sirvi ja leidke kaust.
- Klõpsake nuppu OK.
- Avanevas dialoogiboksis klõpsake nuppu Ühenda.
- Klõpsake nuppu "Kombineeri ja laadige".
- Avanevas dialoogiboksis „Failide kombineerimine” valige vasakul paanil tabel. Pange tähele, et Power Query näitab teile tabelit esimesest failist. See fail toimiks mallina (või võtmena) teiste failide ühendamiseks. Power Query otsib nüüd teistest töövihikutest tabelit 1 ja ühendab selle selle töövihikuga.
- Klõpsake nuppu OK.
See laadib lõpptulemuse (kombineeritud andmed) teie aktiivsele töölehele.
Pange tähele, et koos andmetega lisab Power Query automaatselt töövihiku nime kombineeritud andmete esimeseks veeruks. See aitab jälgida, millised andmed millisest töövihikust pärinevad.
Kui soovite andmeid enne Excelisse laadimist esmalt redigeerida, valige 6. sammus „Kombineeri ja muuda“. See avab Power Query redaktoris lõpptulemuse, kus saate andmeid redigeerida.
Mõned asjad, mida teada:
- Kui valite malliks Exceli tabeli (7. toimingus), kasutab Power Query selle tabeli veerunimesid teiste tabelite andmete ühendamiseks. Kui teistel tabelitel on täiendavaid veerge, eiratakse neid. Kui nendel teistel tabelitel pole veergu, mis on teie mallitabelis, paneks Power Query selle jaoks lihtsalt tühi.
- Veerud ei pea olema samas järjekorras, nagu Power Query kasutab veergude kaardistamiseks veerupäiseid.
- Kuna olete võtmeks valinud tabeli 1, otsib Power Query kõigist töövihikutest tabelit 1 ja ühendab need kõik. Kui see ei leia samanimelist Exceli tabelit (selles näites tabel 1), annab Power Query teile vea.
Uute failide lisamine kausta
Võtame nüüd mõne minuti ja mõistame, mida me ülaltoodud sammudega tegime (mis võttis meil vaid mõne sekundi).
Nelja erineva töövihiku andmed ühendasime mõne sekundi jooksul ühte tabelisse, avamata isegi ühtegi töövihikut.
Kuid see pole veel kõik.
Power Query tõeline POWER on see, et nüüd, kui lisate kausta rohkem faile, ei pea te neid samme kordama.
Kõik, mida vajate uue töövihiku teisaldamiseks kausta, päringu värskendamiseks ja see ühendab automaatselt kõigi selle kausta töövihikute andmed.
Näiteks kui ülaltoodud näites lisan uue töövihiku - „Mid-West.xlsx” kausta ja värskendage päringut, annab see mulle kohe uue kombineeritud andmekogumi.
Päringu värskendamiseks toimige järgmiselt.
- Paremklõpsake töölehele laaditud Exceli tabelit ja klõpsake nuppu Värskenda.
- Paremklõpsake paanil „Töövihiku päring” päringut ja klõpsake nuppu Värskenda
- Minge vahekaardile Andmed ja klõpsake nuppu Värskenda.
Igas töövihikus on andmed sama töölehe nimega
Kui teil pole Exceli tabelis andmeid, kuid kõik lehtede nimed (millest soovite andmeid kombineerida) on samad, võite kasutada selles jaotises näidatud meetodit.
On mõned asjad, millega peate olema ettevaatlik, kui tegemist on ainult tabelite andmetega, mitte Exceli tabeliga.
- Töölehtede nimed peaksid olema samad. See aitab Power Queryl teie töövihikuid läbi vaadata ja kombineerida igas töövihikus sama nimega töölehtede andmed.
- Power Query on tõstutundlik. See tähendab, et töölehte nimega „andmed” ja „Andmed” peetakse erinevaks. Sarnaselt peetakse veergu, mille päis on „Kauplus”, ja veergu, mille pealkiri on „pood”.
- Kuigi on oluline, et veerupäised oleksid samad, ei ole oluline sama järjekord. Kui veeru 2 veerus „East.xlsx” on veeru „West.xlsx” 4. veerg, sobitab Power Query selle päiste kaardistamisega õigesti.
Nüüd vaatame, kuidas kiiresti kombineerida erinevate töövihikute andmeid, kus töölehe nimi on sama.
Selles näites on mul nelja failiga kaust.
Igas töövihikus on mul tööleht nimega „Andmed”, mis sisaldab andmeid järgmises vormingus (pange tähele, et see pole Exceli tabel).
Siin on sammud mitme töövihiku andmete ühendamiseks üheks tööleheks.
- Minge vahekaardile Andmed.
- Klõpsake rühmas Hangi ja muuda rippmenüüd Uus päring.
- Hõljutage kursorit valikul „Failist” ja klõpsake nuppu „Kaustast”.
- Sisestage dialoogiboksis Kaust failide kausta failitee või klõpsake nuppu Sirvi ja leidke kaust.
- Klõpsake nuppu OK.
- Avanevas dialoogiboksis klõpsake nuppu Ühenda.
- Klõpsake nuppu "Kombineeri ja laadige".
- Avanevas dialoogiboksis „Failide kombineerimine” valige vasakul paanil „Andmed”. Pange tähele, et Power Query näitab töölehe nime esimesest failist. See fail toimiks võtmena/mallina teiste failide ühendamiseks. Power Query vaatab läbi iga töövihiku, leiab lehe nimega „Andmed” ja ühendab need kõik.
- Klõpsake nuppu OK. Nüüd vaatab Power Query läbi iga töövihiku, otsige sealt töölehte nimega „Andmed” ja seejärel ühendage kõik need andmekogumid.
See laadib lõpptulemuse (kombineeritud andmed) teie aktiivsele töölehele.
Kui soovite andmeid enne Excelisse laadimist esmalt redigeerida, valige 6. sammus „Kombineeri ja muuda“. See avab Power Query redaktoris lõpptulemuse, kus saate andmeid redigeerida.
Igas töövihikus on andmed erinevate tabelite või lehtede nimedega
Mõnikord ei pruugi te saada struktureeritud ja järjepidevaid andmeid (nt samanimelised tabelid või sama nimega tööleht).
Oletame näiteks, et saate andmed kelleltki, kes lõi need andmekogumid, kuid nimetas töölehed Idaandmeteks, Lääneandmeteks, Põhjaandmeteks ja Lõunaandmeteks.
Või on inimene loonud Exceli tabeleid, kuid erinevate nimedega.
Sellistel juhtudel saate endiselt kasutada Power Query'i, kuid peate seda tegema mõne täiendava sammuga.
- Minge vahekaardile Andmed.
- Klõpsake rühmas Hangi ja muuda rippmenüüd Uus päring.
- Hõljutage kursorit valikul „Failist” ja klõpsake nuppu „Kaustast”.
- Sisestage dialoogiboksis Kaust failide kausta failitee või klõpsake nuppu Sirvi ja leidke kaust.
- Klõpsake nuppu OK.
- Avanevas dialoogiboksis klõpsake nuppu Redigeeri. See avab Power Query redaktori, kus näete kõigi kaustas olevate failide üksikasju.
- Hoidke juhtklahvi all ja valige veerud „Sisu” ja „Nimi”, paremklõpsake ja valige „Eemalda muud veerud”. See eemaldab kõik muud veerud, välja arvatud valitud veerud.
- Klõpsake päringiredaktori lindil nuppu „Lisa veerg” ja seejärel nuppu „Kohandatud veerg”.
- Nimetage dialoogiboksis Kohandatud veeru lisamine uuele veerule nimi „Andmete importimine” ja kasutage järgmist valemit = Excel. töövihik ([SISU]). Pange tähele, et see valem on tõstutundlik ja peate selle sisestama täpselt nii, nagu olen siin näidanud.
- Nüüd näete uut veergu, kuhu on kirjutatud tabel. Nüüd selgitan, mis siin juhtus. Esitasite Power Queryle töövihikute nimed ja Power Query tõi igast töövihikust (mis praegu asub tabeli lahtris) objektid, näiteks töölehed, tabelid ja nimevahemikud. Võite klõpsata teksti tabeli kõrval oleval tühjal ja näete teavet allosas. Sel juhul, kuna meil on igas töövihikus ainult üks tabel ja üks tööleht, näete ainult kahte rida.
- Klõpsake veeru „Andmete importimine” ülaosas topeltnoole ikooni.
- Avanevas veeruandmete kastis tühjendage märkeruut „Kasuta algset veergu eesliitena” ja klõpsake siis nuppu OK.
- Nüüd näete laiendatud tabelit, kus näete tabeli iga objekti kohta ühte rida. Sel juhul on iga töövihiku puhul leheobjekt ja tabeli objekt eraldi loetletud.
- Veerus Kind filtreerige loend ainult tabeli kuvamiseks.
- Hoidke juhtklahvi all ja valige veerg Nimi ja andmed. Nüüd paremklõpsake ja eemaldage kõik muud veerud.
- Veerus Andmed klõpsake andmepäise paremas ülanurgas topeltnoole ikooni.
- Avanevas veeruandmekastis klõpsake nuppu OK. See ühendab kõigi tabelite andmed ja kuvatakse Power Query'is.
- Nüüd saate teha kõik vajalikud ümberkujundamised ja seejärel minna vahekaardile Avaleht ja klõpsata sulgemisel ja laadimisel.
Nüüd lubage mul kiiresti selgitada, mida me siin tegime. Kuna lehtede nimedes või tabelinimedes ei olnud järjepidevust, kasutasime Power Query kõigi töövihikute objektide toomiseks valemit = Excel.Workbook. Need objektid võivad sisaldada lehti, tabeleid ja nimega vahemikke. Kui meil olid kõik objektid kõikidest failidest olemas, filtreerisime need, et arvestada ainult Exceli tabelitega. Seejärel laiendasime tabelite andmeid ja ühendasime need kõik.
Selles näites filtreerisime andmed ainult Exceli tabelite kasutamiseks (13. samm). Kui soovite lehti ja mitte tabeleid kombineerida, saate lehti filtreerida.
Märkus - see meetod annab teile kombineeritud andmed isegi siis, kui veerunimed ei vasta üksteisele. Näiteks kui saidil East.xlsx on veerg, mis on valesti kirjutatud, saate lõpuks 5 veergu. Power Query täidab andmed veergudes, kui see on leitud, ja kui ta veergu ei leia, teatab see väärtusest „null”.
Sarnaselt, kui teil on tabelite töölehtedel täiendavaid veerge, lisatakse need lõpptulemusse.
Kui nüüd saate rohkem töövihikuid, millest peate andmeid ühendama, kopeerige ja kleepige need lihtsalt kausta ja värskendage Power Query
