Neseniai iš skaitytojo gavau klausimą, kaip sujungti kelis tos pačios darbaknygės darbalapius į vieną darbalapį.
Paprašiau, kad jis naudotų „Power Query“, kad sujungtų skirtingus lapus, bet tada supratau, kad naujai „Power Query“ tai padaryti gali būti sunku.
Taigi nusprendžiau parašyti šią mokymo programą ir parodyti tikslius veiksmus, kaip sujungti kelis lapus į vieną lentelę naudojant „Power Query“.
Žemiau vaizdo įrašas, kuriame parodau, kaip sujungti duomenis iš kelių lapų/lentelių naudojant „Power Query“:
Žemiau yra rašytinės instrukcijos, kaip sujungti kelis lapus (jei jums labiau patinka rašytinis tekstas, o ne vaizdo įrašas).
Pastaba: „Power Query“ gali būti naudojamas kaip priedas programoje „Excel 2010“ ir 2013 m. Ir yra integruota funkcija nuo „Excel 2016“. Atsižvelgiant į jūsų versiją, kai kurie vaizdai gali atrodyti kitaip (šioje pamokoje naudojami vaizdai yra iš „Excel 2016“).
Sujunkite duomenis iš kelių darbalapių naudodami „Power Query“
Derinant duomenis iš skirtingų lapų naudojant „Power Query“, duomenys turi būti įtraukti į „Excel“ lentelę (arba bent jau įvardintus diapazonus). Jei duomenų nėra „Excel“ lentelėje, čia pateiktas metodas neveiks.
Tarkime, kad turite keturis skirtingus lapus - rytus, vakarus, šiaurę ir pietus.
Kiekvieno iš šių darbalapių duomenys yra „Excel“ lentelėje, o lentelės struktūra yra nuosekli (t. Y. Antraštės yra vienodos).
Spustelėkite čia, jei norite atsisiųsti duomenis ir sekti toliau.
Tokius duomenis labai lengva sujungti naudojant „Power Query“ (kuri tikrai gerai veikia su „Excel“ lentelės duomenimis).
Kad ši technika veiktų geriausiai, geriau turėti „Excel“ lentelių pavadinimus (dirbkite ir be jos, tačiau lengviau naudoti, kai lentelės yra pavadintos).
Lentelėms daviau tokius pavadinimus: „East_Data“, „West_Data“, „North_Data“ ir „South_Data“.
Štai veiksmai, kaip sujungti kelis darbalapius su „Excel“ lentelėmis naudojant „Power Query“:
- Eikite į skirtuką Duomenys.
- Grupėje „Gauti ir transformuoti duomenis“ spustelėkite parinktį „Gauti duomenis“.
- Eikite į parinktį „Iš kitų šaltinių“.
- Spustelėkite parinktį „Tuščia užklausa“. Bus atidarytas „Power Query“ redaktorius.
- Užklausų redaktoriuje įveskite šią formulę formulės juostoje: = Excel.Dabartinė darbo knyga(). Atminkite, kad „Power Query“ formulėse yra skiriamos didžiosios ir mažosios raidės, todėl turite naudoti tikslią formulę, kaip minėta (kitaip gausite klaidą).
- Paspauskite klavišą Enter. Tai parodys visus lentelių pavadinimus visoje darbaknygėje (taip pat bus parodyti įvardyti diapazonai ir (arba) ryšiai, jei jie yra darbaknygėje).
- [Neprivalomas veiksmas] Šiame pavyzdyje noriu sujungti visas lenteles. Jei norite sujungti tik konkrečias „Excel“ lenteles, galite spustelėti išskleidžiamojo meniu piktogramą pavadinimo antraštėje ir pasirinkti tas, kurias norite sujungti. Panašiai, jei nurodėte diapazonus ar ryšius ir norite tik sujungti lenteles, galite pašalinti ir tuos pavadintus diapazonus.
- Turinio antraštės langelyje spustelėkite dvigubą rodyklę.
- Pasirinkite stulpelius, kuriuos norite sujungti. Jei norite sujungti visus stulpelius, įsitikinkite, kad pažymėta (Pasirinkti visus stulpelius).
- Atžymėkite parinktį „Naudoti pradinį stulpelio pavadinimą kaip priešdėlį“.
- Spustelėkite Gerai.
Pirmiau minėti veiksmai sujungtų visų darbalapių duomenis į vieną lentelę.
Jei atidžiai pažvelgsite, pamatysite, kad paskutinis stulpelis (dešinėje) turi „Excel“ lentelių pavadinimą („East_Data“, „West_Data“, „North_Data“ ir „South_Data“). Tai identifikatorius, nurodantis, kuris įrašas buvo iš kokios „Excel“ lentelės. Taip pat dėl šios priežasties sakiau, kad geriau turėti aprašomuosius „Excel“ lentelių pavadinimus.
Štai keletas pakeitimų, kuriuos galite atlikti bendrame „Power Query“ duomenyse:
- Vilkite ir įdėkite stulpelį Pavadinimas į pradžią.
- Pašalinkite „_Data“ iš pavadinimo stulpelio (taigi pavadinimo stulpelyje paliekate Rytų, Vakarų, Šiaurės ir Pietų). Norėdami tai padaryti, dešiniuoju pelės mygtuku spustelėkite antraštę Pavadinimas ir spustelėkite Pakeisti reikšmes. Dialogo lange Pakeisti vertes pakeiskite _Data tuščiu.
- Pakeiskite stulpelį Duomenys, kad būtų rodomos tik datos (o ne laikas). Norėdami tai padaryti, spustelėkite stulpelio „Data“ antraštę, eikite į skirtuką „Transformuoti“ ir pakeiskite duomenų tipą į Data.
- Pervardykite užklausą į „ConsolidatedData“.
Dabar, kai turite bendrus duomenis iš visų „Power Query“ darbalapių, galite juos įkelti į „Excel“ - kaip naują lentelę naujame darbalapyje.
Padaryti tai. atlikite toliau nurodytus veiksmus:
- Spustelėkite skirtuką „Failas“.
- Spustelėkite Uždaryti ir įkelti.
- Dialogo lange Importuoti duomenis pasirinkite Lentelė ir Naujas darbalapio parinktys.
- Spustelėkite Gerai.
Pirmiau minėti veiksmai sujungtų duomenis iš visų darbalapių ir gautų tuos sujungtus duomenis naujame darbalapyje.
Viena problema, kurią turite išspręsti naudodami šį metodą
Jei naudojote aukščiau aprašytą metodą, kad sujungtumėte visas darbaknygės lenteles, greičiausiai susidursite su problema.
Žiūrėkite sujungtų duomenų eilučių skaičių - 1304 (tai tiesa).
Dabar, jei atnaujinsiu užklausą, eilučių skaičius pasikeis į 2607. Atnaujinkite dar kartą ir ji pasikeis į 3910.
Čia ir yra problema.
Kiekvieną kartą, kai atnaujinate užklausą, ji prideda visus pradinių duomenų įrašus prie sujungtų duomenų.
Pastaba: su šia problema susidursite tik tuo atveju, jei derinote naudodami „Power Query“ VISOS EXCEL LENTELĖS darbo knygoje. Jei pasirinkote konkrečias lenteles, kurias norite sujungti, su šia problema nesusidursite.Suprasime šios problemos priežastį ir kaip ją ištaisyti.
Kai atnaujinate užklausą, ji grįžta atgal ir atlieka visus veiksmus, kuriuos atlikome, kad sujungtume duomenis.
Žingsnyje, kuriame naudojome formulę = Excel.CurrentWorkbook (), jis mums pateikė visų lentelių sąrašą. Pirmą kartą tai gerai veikė, nes buvo tik keturi stalai.
Tačiau kai atnaujinate, darbaknygėje yra penkios lentelės, įskaitant naują lentelę, kurią įvedė „Power Query“, kur turime bendrus duomenis.
Taigi kiekvieną kartą atnaujinant užklausą, išskyrus keturias „Excel“ lenteles, kurias norime sujungti, ji taip pat prideda esamą užklausos lentelę prie gautų duomenų.
Tai vadinama rekursija.
Štai kaip išspręsti šią problemą.
Kai „Power Query“ formulės juostoje įterpsite = „Excel.CurrentWorkbook“ () ir paspausite „Enter“, gausite „Excel“ lentelių sąrašą. Kad įsitikintumėte, jog tik sujungti lenteles iš darbalapio, turite kažkaip filtruoti tik tas lenteles, kurias norite sujungti, ir pašalinti visa kita.
Norėdami įsitikinti, kad turite tik reikiamas lenteles, atlikite šiuos veiksmus:
- Spustelėkite išskleidžiamąjį meniu ir užveskite žymeklį ant teksto filtrų.
- Spustelėkite parinktį Yra.
- Dialogo lange Filtro eilutės įveskite _Data laukelyje šalia parinkties „yra“.
- Spustelėkite Gerai.
Galite nepastebėti jokių duomenų pakeitimų, tačiau tai padarius, gauta lentelė nebus vėl įtraukta atnaujinus užklausą.
Atminkite, kad atlikdami aukščiau aprašytus veiksmus naudojome „_Duomenys“Filtruoti, kai taip pavadinome lenteles. Bet ką daryti, jei jūsų lentelės nėra nuosekliai pavadintos? Ką daryti, jei visi lentelių pavadinimai yra atsitiktiniai ir neturi nieko bendro.
Štai kaip tai išspręsti - naudokite filtrą „nelygu“ ir įveskite užklausos pavadinimą (kuris mūsų pavyzdyje būtų „ConsolidatedData“). Tai užtikrins, kad viskas išliks ta pati ir sukurta užklausų lentelė bus filtruojama.
Be to, kad „Power Query“ labai palengvina visą skirtingų duomenų (ar net to paties lapo) duomenų sujungimo procesą, dar vienas jų naudojimo pranašumas yra tai, kad jis tampa dinamiškas. Jei prie bet kurios lentelės pridėsite daugiau įrašų ir atnaujinsite „Power Query“, ji automatiškai pateiks sujungtus duomenis.Svarbi pastaba: šiame vadove naudojamame pavyzdyje antraštės buvo tos pačios. Jei antraštės skiriasi, „Power Query“ sujungs ir sukurs visus naujos lentelės stulpelius. Jei to stulpelio duomenys yra prieinami, jie bus rodomi, kitu atveju bus rodoma nulinė.
Jums taip pat gali patikti šie „Power Query“ vadovėliai:
- Sujunkite duomenis iš kelių „Excel“ darbaknygių (naudodami „Power Query“).
- Kaip atsukti duomenis „Excel“ naudojant „Power Query“ (dar vadinamą „Get & Transform“)
- Gaukite failų pavadinimų sąrašą iš aplankų ir poaplankių (naudodami „Power Query“)
- Sujunkite „Excel“ lenteles naudodami „Power Query“.
- Kaip palyginti du „Excel“ lapus/failus
