Nesen no lasītāja saņēmu jautājumu par vairāku darblapu apvienošanu vienā darbgrāmatā vienā darblapā.
Es lūdzu viņam izmantot Power Query, lai apvienotu dažādas lapas, bet tad es sapratu, ka kādam jaunam Power Query lietotājam tas var būt grūti.
Tāpēc es nolēmu uzrakstīt šo apmācību un parādīt precīzas darbības, lai apvienotu vairākas lapas vienā tabulā, izmantojot Power Query.
Zem video, kurā parādīts, kā apvienot datus no vairākām lapām/tabulām, izmantojot Power Query:
Zemāk ir rakstiski norādījumi, kā apvienot vairākas lapas (ja vēlaties rakstisku tekstu, nevis video).
Piezīme. Power Query var izmantot kā pievienojumprogrammu programmā Excel 2010 un 2013, un tā ir iebūvēta funkcija, sākot no Excel 2016. Pamatojoties uz jūsu versiju, daži attēli var izskatīties savādāk (šajā apmācībā izmantotie attēlu attēli ir no programmas Excel 2016).
Apvienojiet datus no vairākām darblapām, izmantojot Power Query
Apvienojot datus no dažādām lapām, izmantojot Power Query, datiem jābūt Excel tabulā (vai vismaz nosauktajos diapazonos). Ja dati nav Excel tabulā, šeit norādītā metode nedarbosies.
Pieņemsim, ka jums ir četras dažādas lapas - austrumi, rietumi, ziemeļi un dienvidi.
Katrā no šīm darblapām ir dati Excel tabulā, un tabulas struktūra ir konsekventa (t.i., galvenes ir vienādas).
Noklikšķiniet šeit, lai lejupielādētu datus un sekotu līdzi.
Šāda veida datus ir ļoti viegli apvienot, izmantojot Power Query (kas patiešām labi darbojas ar datiem Excel tabulā).
Lai šī tehnika darbotos vislabāk, labāk ir norādīt Excel tabulu nosaukumus (strādājiet arī bez tās, taču to ir vieglāk izmantot, ja tabulas ir nosauktas).
Tabulām esmu devis šādus nosaukumus: East_Data, West_Data, North_Data un South_Data.
Tālāk ir norādītas darbības, lai apvienotu vairākas darblapas ar Excel tabulām, izmantojot Power Query.
- Dodieties uz cilni Dati.
- Grupā Iegūt un pārveidot datus noklikšķiniet uz opcijas Iegūt datus.
- Dodieties uz opciju “No citiem avotiem”.
- Noklikšķiniet uz opcijas “Tukšs vaicājums”. Tiks atvērts Power Query redaktors.
- Vaicājumu redaktorā formulas joslā ierakstiet šādu formulu: = Excel.Pašreizējā darba grāmata(). Ņemiet vērā, ka Power Query formulas ir reģistrjutīgas, tāpēc jums jāizmanto precīza formula, kā minēts (pretējā gadījumā jūs saņemsit kļūdu).
- Nospiediet taustiņu Enter. Tādējādi tiks parādīti visi tabulu nosaukumi visā darbgrāmatā (tiks parādīti arī nosauktie diapazoni un/vai savienojumi, ja tādi ir darbgrāmatā).
- [Neobligāta darbība] Šajā piemērā es vēlos apvienot visas tabulas. Ja vēlaties apvienot tikai noteiktas Excel tabulas, varat noklikšķināt uz nolaižamās ikonas nosaukuma galvenē un atlasīt tās, kuras vēlaties apvienot. Līdzīgi, ja esat nosaucis diapazonus vai savienojumus un vēlaties apvienot tikai tabulas, varat noņemt arī nosauktos diapazonus.
- Satura galvenes šūnā noklikšķiniet uz divvirzienu bultiņas.
- Atlasiet slejas, kuras vēlaties apvienot. Ja vēlaties apvienot visas kolonnas, noteikti atzīmējiet (Atlasīt visas kolonnas).
- Noņemiet atzīmi no opcijas “Izmantot sākotnējo slejas nosaukumu kā prefiksu”.
- Noklikšķiniet uz Labi.
Iepriekš minētās darbības apvienotu visu darblapu datus vienā tabulā.
Ja paskatās uzmanīgi, pēdējā slejā (labajā pusē) ir Excel tabulu nosaukums (East_Data, West_Data, North_Data un South_Data). Šis ir identifikators, kas mums norāda, kurš ieraksts nāk no kādas Excel tabulas. Tas ir arī iemesls, kāpēc es teicu, ka ir labāk, ja Excel tabulām ir aprakstoši nosaukumi.
Tālāk ir norādītas dažas izmaiņas, ko varat veikt, apvienojot datus pašā Power Query.
- Velciet un novietojiet sleju Nosaukums uz sākumu.
- Noņemiet “_Data” no nosaukuma slejas (lai nosaukuma slejā paliktu austrumi, rietumi, ziemeļi un dienvidi). Lai to izdarītu, ar peles labo pogu noklikšķiniet uz galvenes Nosaukums un noklikšķiniet uz Aizstāt vērtības. Dialoglodziņā Aizstāt vērtības aizstājiet _Data ar tukšu.
- Mainiet sleju Dati, lai tiktu rādīti tikai datumi (nevis laiks). Lai to izdarītu, noklikšķiniet uz slejas Datums galvenes, dodieties uz cilni Pārveidot un mainiet datu tipu uz Datums.
- Pārdēvējiet vaicājumu uz ConsolidatedData.
Tagad, kad ir apkopoti dati no visām Power Query darblapām, varat tos ielādēt programmā Excel - kā jaunu tabulu jaunā darblapā.
Lai to izdarītu. izpildiet tālāk norādītās darbības.
- Noklikšķiniet uz cilnes “Fails”.
- Noklikšķiniet uz Aizvērt un ielādēt.
- Dialoglodziņā Datu importēšana atlasiet tabulas un jaunas darblapas opcijas.
- Noklikšķiniet uz Labi.
Iepriekš minētās darbības apvienotu datus no visām darblapām un sniegtu šos apvienotos datus jaunā darblapā.
Viena problēma, kas jums jāatrisina, izmantojot šo metodi
Ja esat izmantojis iepriekš minēto metodi, lai apvienotu visas darbgrāmatas tabulas, visticamāk, jums radīsies problēma.
Skatiet apvienoto datu rindu skaitu - 1304 (kas ir pareizi).
Tagad, ja atsvaidzināšu vaicājumu, rindu skaits mainās uz 2607. Atsvaidziniet vēlreiz un tas mainīsies uz 3910.
Šeit ir problēma.
Katru reizi, atsvaidzinot vaicājumu, tas apvienotajiem datiem pievieno visus sākotnējo datu ierakstus.
Piezīme. Ar šo problēmu saskarsities tikai tad, ja apvienošanai izmantojāt Power Query VISAS EXCEL TABULAS darba grāmatā. Ja esat izvēlējies kombinēt noteiktas tabulas, jūs nesaskarsities ar šo problēmu.Sapratīsim šīs problēmas cēloni un to, kā to novērst.
Kad atsvaidzināt vaicājumu, tas atgriežas un veic visas darbības, ko veicām, lai apvienotu datus.
Darbībā, kurā mēs izmantojām formulu = Excel.CurrentWorkbook (), tas deva mums visu tabulu sarakstu. Tas pirmo reizi darbojās labi, jo bija tikai četri galdi.
Bet, atsvaidzinot, darbgrāmatā ir piecas tabulas, ieskaitot jauno tabulu, kuru Power Query ievietojis, kur mums ir apvienotie dati.
Tāpēc katru reizi, kad atsvaidzināt vaicājumu, izņemot četras Excel tabulas, kuras mēs vēlamies apvienot, tas arī pievieno esošajiem vaicājumu tabulām iegūtajiem datiem.
To sauc par rekursiju.
Lūk, kā atrisināt šo problēmu.
Kad Power Query formulas joslā ievietojat = Excel.CurrentWorkbook () un nospiediet taustiņu Enter, tiek parādīts Excel tabulu saraksts. Lai pārliecinātos, ka tabulas var apvienot tikai no darblapas, jums kaut kā jāfiltrē tikai šīs tabulas, kuras vēlaties apvienot, un jānoņem viss pārējais.
Tālāk ir norādītas darbības, lai pārliecinātos, ka jums ir tikai nepieciešamās tabulas.
- Noklikšķiniet uz nolaižamās izvēlnes un virziet kursoru uz teksta filtriem.
- Noklikšķiniet uz opcijas Satur.
- Dialoglodziņā Filtrēt rindas laukā blakus opcijai “satur” ievadiet _Dati.
- Noklikšķiniet uz Labi.
Jūs, iespējams, neredzēsit izmaiņas datos, taču, to darot, tabula netiks atkārtoti pievienota, kad vaicājums tiks atsvaidzināts.
Ņemiet vērā, ka iepriekšminētajās darbībās mēs izmantojām “_Dati”, Lai filtrētu, kā mēs šādi nosaucām tabulas. Bet ko darīt, ja jūsu tabulas netiek konsekventi nosauktas. Ko darīt, ja visi tabulu nosaukumi ir nejauši un tiem nav nekā kopīga.
Lūk, kā to atrisināt - izmantojiet filtru “nav vienāds” un ievadiet vaicājuma nosaukumu (kas mūsu piemērā būtu ConsolidatedData). Tas nodrošinās, ka viss paliek nemainīgs un izveidotā vaicājumu tabula tiek filtrēta.
Papildus tam, ka Power Query padara visu šo dažādu lapu (vai pat vienas lapas) datu apvienošanas procesu diezgan vienkāršu, vēl viens ieguvums no tā izmantošanas padara to dinamisku. Ja kādai no tabulām pievienojat vairāk ierakstu un atsvaidzināt Power Query, tas automātiski sniegs apvienotos datus.Svarīga piezīme: šajā apmācībā izmantotajā piemērā galvenes bija vienādas. Ja galvenes ir atšķirīgas, Power Query apvienos un izveidos visas slejas jaunajā tabulā. Ja dati par šo kolonnu ir pieejami, tie tiks parādīti, pretējā gadījumā - nulle.
Jums varētu patikt arī šādas Power Query apmācības:
- Apvienojiet datus no vairākām darbgrāmatām programmā Excel (izmantojot Power Query).
- Kā atvienot datus programmā Excel, izmantojot Power Query (pazīstams arī kā Get & Transform)
- Iegūstiet failu nosaukumu sarakstu no mapēm un apakšmapēm (izmantojot Power Query)
- Apvienojiet tabulas programmā Excel, izmantojot Power Query.
- Kā salīdzināt divas Excel lapas/failus
