✅ Ühendage mitme töölehe andmed Excelis üheks tööleheks

Lang L: none (table-of-contents)

Ühendage Power Query abil mitme töölehe andmed

Hiljuti sain ühelt lugejalt küsimuse mitme töövihiku mitme töölehe ühendamise kohta üheks tööleheks.

Palusin tal kasutada Power Queryd erinevate lehtede ühendamiseks, kuid siis mõistsin, et kellelegi uuele Power Query jaoks võib see olla raske.

Nii otsustasin kirjutada selle õpetuse ja näidata täpseid samme mitme lehe ühendamiseks üheks tabeliks Power Query abil.

Allpool video, kus näitan, kuidas Power Query abil mitme lehe/tabeli andmeid kombineerida:

Allpool on kirjalikud juhised mitme lehe ühendamiseks (juhul kui eelistate kirjutatud teksti video asemel).

Märkus. Power Queryt saab kasutada lisandmoodulina rakendustes Excel 2010 ja 2013 ning see on alates Excel 2016 sisseehitatud funktsioon. Teie versiooni põhjal võivad mõned pildid tunduda teistsugused (selles õpetuses kasutatud pildid on pärit Excel 2016 -st).

Ühendage Power Query abil mitme töölehe andmed

Power Query abil erinevate lehtede andmete kombineerimisel peavad andmed olema Exceli tabelis (või vähemalt nimivahemikes). Kui andmed pole Exceli tabelis, ei tööta siin näidatud meetod.

Oletame, et teil on neli erinevat lehte - ida, lääs, põhi ja lõuna.

Kõigil neil töölehtedel on andmed Exceli tabelis ja tabeli struktuur on järjepidev (st päised on samad).

Andmete allalaadimiseks ja selle järgimiseks klõpsake siin.

Selliseid andmeid on Power Query abil väga lihtne kombineerida (mis töötab Exceli tabeli andmetega tõesti hästi).

Selle tehnika parimaks toimimiseks on parem omada oma Exceli tabelitele nimesid (saate töötada ka ilma selleta, kuid tabelite nimetamisel on seda lihtsam kasutada).

Panin tabelitele järgmised nimed: Ida_andmed, Lääne_andmed, Põhja_andmed ja Lõuna_andmed.

Siin on sammud mitme töölehe ühendamiseks Exceli tabelitega Power Query abil.

Minge vahekaardile Andmed.
Klõpsake rühmas Andmete hankimine ja teisendamine valikut „Hangi andmed”.
Valige suvand „Teistest allikatest”.
Klõpsake suvandit „Tühi päring”. See avab Power Query redaktori.
Sisestage päringredaktoris valemiribale järgmine valem: = Excel.Praegune töövihik(). Pange tähele, et Power Query valemid on tõstutundlikud, seega peate kasutama täpselt mainitud valemit (muidu kuvatakse tõrge).
Vajutage sisestusklahvi. See näitab teile kogu töövihiku tabelite nimesid (see näitab teile ka nimetatud vahemikke ja/või seoseid, kui need on töövihikus olemas).
[Valikuline samm] Selles näites tahan ühendada kõik tabelid. Kui soovite kombineerida ainult konkreetseid Exceli tabeleid, võite klõpsata nime päises rippmenüü ikoonil ja valida need, mida soovite kombineerida. Sarnaselt, kui olete nimetanud vahemikud või ühendused ja soovite ainult tabeleid kombineerida, saate need ka nimetatud vahemikud eemaldada.
Klõpsake päise lahtris Sisu kahekordset noolt.
Valige veerud, mida soovite ühendada. Kui soovite ühendada kõik veerud, veenduge, et märkeruut (Vali kõik veerud) oleks märgitud.
Tühjendage märkeruut „Kasuta veeru esialgset nime eesliitena”.
Klõpsake nuppu OK.

Ülaltoodud sammud ühendaksid kõikide töölehtede andmed ühte tabelisse.

Kui vaatate tähelepanelikult, leiate, et viimasel veerul (paremal) on Exceli tabelite nimi (East_Data, West_Data, North_Data ja South_Data). See on identifikaator, mis ütleb meile, milline kirje millisest Exceli tabelist pärineb. See on ka põhjus, miks ma ütlesin, et Exceli tabelitele on parem omada kirjeldavaid nimesid.

Siin on mõned muudatused, mida saate teha Power Query enda kombineeritud andmetes.

Lohistage ja asetage veerg Nimi algusesse.
Eemaldage nimede veerust „_andmed” (nii jääb nimeveerust idas, läänes, põhjas ja lõunas). Selleks paremklõpsake päisel Nimi ja klõpsake nuppu Asenda väärtused. Asendage dialoogiboksis Asenda väärtused _Data tühjaga.
Muutke veergu Andmed, et kuvada ainult kuupäevad (mitte kellaaeg). Selleks klõpsake veeru päist Kuupäev, minge vahekaardile „Teisenda” ja muutke andmetüüp kuupäevaks.
Nimetage päring ümber ConsolidatedData.

Nüüd, kui teil on Power Query kõigi töölehtede kombineeritud andmed, saate need Excelisse laadida - uue tabelina uue tabelina.

Selle tegemiseks. järgige alltoodud samme:

Klõpsake vahekaarti „Fail”.
Klõpsake nuppu Sule ja Laadi.
Valige dialoogiboksis Andmete importimine Tabel ja Uus tööleht.
Klõpsake nuppu OK.

Ülaltoodud sammud ühendaksid kõikide töölehtede andmed ja annaksid need kombineeritud andmed uuele töölehele.

Üks probleem, mille peate selle meetodi kasutamisel lahendama

Kui olete töövihiku kõigi tabelite ühendamiseks kasutanud ülaltoodud meetodit, on teil tõenäoliselt probleem.

Vaadake kombineeritud andmete ridade arvu - 1304 (mis on õige).

Kui nüüd päringut värskendada, muutub ridade arv 2607. Värskendage uuesti ja see muutub 3910 -ks.

Siin on probleem.

Iga kord, kui värskendate päringut, lisab see kombineeritud andmetele kõik algsete andmete kirjed.

Märkus. Selle probleemiga seisate silmitsi ainult siis, kui olete kombineerimiseks kasutanud Power Queryt KÕIK EXCELI TABELID töövihikus. Kui valisite kombineerimiseks konkreetsed tabelid, ei pea te seda probleemi silmitsi seisma.

Mõistame selle probleemi põhjust ja kuidas seda parandada.

Kui värskendate päringut, läheb see tagasi ja järgib kõiki andmete ühendamiseks tehtud samme.

Etapis, kus kasutasime valemit = Excel.CurrentWorkbook (), see andis meile kõigi tabelite loendi. See töötas esimest korda hästi, kuna laudu oli ainult neli.

Värskendamisel on aga töövihikus viis tabelit - sealhulgas uus tabel, mille Power Query sisestas, kus meil on kombineeritud andmed.

Nii et iga kord, kui värskendate päringut, lisaks neljale Exceli tabelile, mida soovime ühendada, lisab see saadud andmetele ka olemasoleva päringutabeli.

Seda nimetatakse rekursiooniks.

Siin on, kuidas seda probleemi lahendada.

Kui sisestate Power Query valemiribale = Excel.CurrentWorkbook () ja vajutate sisestusklahvi, saate Exceli tabelite loendi. Veendumaks, et saate tabeleid ühendada ainult töölehelt, peate kuidagi filtreerima ainult need tabelid, mida soovite ühendada, ja eemaldama kõik muu.

Siin on juhised selle tagamiseks, et teil on ainult nõutavad tabelid.

Klõpsake rippmenüüd ja hõljutage kursorit tekstifiltrite kohal.
Klõpsake suvandit Sisaldab.
Dialoogiboksis Filtreeri read sisestage suvandi „sisaldab” kõrval olevale väljale _Data.
Klõpsake nuppu OK.

Te ei pruugi andmetes muudatusi näha, kuid seda tehes väldite päringu värskendamisel saadud tabeli uuesti lisamist.

Pange tähele, et ülaltoodud sammudes oleme kasutanud "_ Andmed”, Et filtreerida tabeleid sel viisil nimetades. Aga mis siis, kui teie tabeleid ei nimetata järjepidevalt. Mis siis, kui kõik tabelinimed on juhuslikud ja neil pole midagi ühist.

Siin on viis selle lahendamiseks - kasutage filtrit „ei võrdu” ja sisestage päringu nimi (mis meie näites oleks ConsolidatedData). See tagab, et kõik jääb samaks ja saadud päringutabel filtreeritakse välja.

Lisaks asjaolule, et Power Query muudab kogu selle erinevate lehtede (või isegi sama lehe) andmete kombineerimise üsna lihtsaks, on selle kasutamise teine eelis see, et see muudab selle dünaamiliseks. Kui lisate mõnele tabelile rohkem kirjeid ja värskendate Power Queryt, annab see teile automaatselt kombineeritud andmed.

Oluline märkus: selles õpetuses kasutatud näites olid päised samad. Kui päised on erinevad, ühendab Power Query ja loob kõik uue tabeli veerud. Kui selle veeru andmed on saadaval, kuvatakse need, vastasel juhul kuvatakse see null.

Samuti võivad teile meeldida järgmised Power Query õpetused: