Izmantojot Power Query, ir kļuvis vieglāk strādāt ar darblapās vai pat darbgrāmatās izkliedētiem datiem.
Viena no lietām, kur Power Query var ietaupīt daudz laika, ir apvienot dažāda lieluma tabulas un kolonnas, pamatojoties uz atbilstošu kolonnu.
Zemāk ir video, kurā es precīzi parādīju, kā apvienot tabulas programmā Excel, izmantojot Power Query.
Ja vēlaties lasīt tekstu, nevis skatīties videoklipu, zemāk ir rakstiski norādījumi.
Pieņemsim, ka jums ir tabula, kā parādīts zemāk:
Šajā tabulā ir dati, kurus vēlos izmantot, taču tajā joprojām trūkst divu svarīgu sleju - “Produkta ID” un “Reģions”, kurā darbojas tirdzniecības pārstāvis.
Šī informācija tiek sniegta kā atsevišķas tabulas, kā parādīts zemāk:
Lai visu šo informāciju apkopotu vienā tabulā, šīs trīs tabulas ir jāapvieno, lai pēc tam varētu izveidot rakurstabulu un to analizēt vai izmantot citiem pārskatu sniegšanas/informācijas paneļa mērķiem.
Un, apvienojoties, es nedomāju vienkāršu kopēšanas pastu.
Jums būs jāsaskaņo attiecīgie ieraksti no 1. tabulas ar datiem no 2. un 3. tabulas.
Tagad varat paļauties uz VLOOKUP vai INDEX/MATCH.
Vai arī, ja esat VBA gudrinieks, varat to uzrakstīt.
Bet šīs iespējas ir laikietilpīgas un sarežģītas salīdzinājumā ar Power Query.
Šajā apmācībā es parādīšu, kā apvienot šīs trīs Excel tabulas vienā.
Lai šī tehnika darbotos, jums ir jābūt savienojošām kolonnām. Piemēram, 1. un 2. tabulā kopējā kolonna ir “Prece”, bet 1. un 3. tabulā kopējā sleja ir “Pārdošanas pārstāvis”. Ņemiet vērā arī to, ka šajās savienojošajās kolonnās nedrīkst atkārtoties.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).
Apvienot tabulas, izmantojot Power Query
Šīs tabulas esmu nosaucis, kā parādīts zemāk:
- 1. tabula - Sales_Data
- 2. tabula - Pdt_Id
- 3. tabula - Novads
Šīs tabulas nav obligāti jāpārdēvē, bet labāk ir dot nosaukumus, kas apraksta tabulu.
Vienā reizē Power Query varat apvienot tikai divas tabulas.
Tātad mums vispirms būs jāapvieno 1. tabula un 2. tabula, un tad nākamajā solī jāapvieno 3. tabula.
1. tabulas un 2. tabulas apvienošana
Lai apvienotu tabulas, vispirms šīs tabulas jāpārvērš savienojumos Power Query. Kad esat izveidojis savienojumus, varat tos viegli apvienot.
Lai tabulu Excel saglabātu kā savienojumu Power Query, veiciet tālāk norādītās darbības.
- Tabulā Sales_Data atlasiet jebkuru šūnu.
- Noklikšķiniet uz cilnes Dati.
- Grupā Iegūt un pārveidot noklikšķiniet uz “No tabulas/diapazona”. Tas atvērs vaicājumu redaktoru.
- Vaicājumu redaktorā noklikšķiniet uz cilnes “Fails”.
- Noklikšķiniet uz opcijas “Aizvērt un ielādēt”.
- Dialoglodziņā “Importēt datus” atlasiet “Izveidot tikai savienojumu”.
- Noklikšķiniet uz Labi.
Iepriekš minētās darbības radītu savienojumu ar nosaukumu Sales_Data (vai jebkuru nosaukumu, ko esat piešķīris Excel tabulai).
Atkārtojiet iepriekš minētās darbības 2. un 3. tabulai.
Tātad, kad esat pabeidzis, jums būs trīs savienojumi (ar nosaukumu Sales_Data, Pdt_Id un reģions).
Tagad redzēsim, kā apvienot tabulu Sales_Data un Pdt_Id.
- Noklikšķiniet uz cilnes Dati.
- Grupā Iegūt un pārveidot datus noklikšķiniet uz Iegūt datus.
- Nolaižamajā izvēlnē noklikšķiniet uz Apvienot vaicājumus.
- Noklikšķiniet uz Apvienot. Tiks atvērts dialoglodziņš Apvienot.
- Dialoglodziņā Apvienot pirmajā nolaižamajā izvēlnē atlasiet “Sales_Data”.
- Otrajā nolaižamajā izvēlnē atlasiet “Pdt_Id”.
- Priekšskatījumā “Sales_Data” noklikšķiniet uz slejas “Prece”. To darot, tiks atlasīta visa kolonna.
- Priekšskatījumā “Pdt_Id” noklikšķiniet uz slejas “Vienums”. To darot, tiks atlasīta visa kolonna.
- Nolaižamajā izvēlnē “Join Kind” atlasiet “Left Outer (viss no pirmā, atbilstība no otrā)”.
- Noklikšķiniet uz Labi.
Iepriekš minētās darbības atvērs vaicājumu redaktoru un parādīs Sales_Data datus ar vienu papildu kolonnu (no Pdt_Id).
Excel tabulu apvienošana (1. un 2. tabula)
Tagad tabulu apvienošanas process notiks vaicājumu redaktorā, veicot šādas darbības:
- Papildu slejā (Pdt_Id) noklikšķiniet uz divvirzienu bultiņas galvenē.
- Atvērtajā opciju lodziņā noņemiet atzīmi no visu kolonnu nosaukumiem un atlasiet tikai vienumu. Tas ir tāpēc, ka esošajā tabulā jau ir sleja produkta nosaukums, un mēs vēlamies tikai produkta ID katram produktam.
- Noņemiet atzīmi no opcijas “Lietot sākotnējo kolonnas nosaukumu kā prefiksu”.
- Noklikšķiniet uz Labi.
Tādējādi tiks iegūta tabula, kurā ir visi ieraksti no tabulas Sales_Data, un papildu sleja, kurā ir arī produktu ID (no tabulas Pdt_Id).
Tagad, ja vēlaties apvienot tikai divas tabulas, varat ielādēt šo Excel.
Bet mums ir jāapvieno trīs tabulas, tāpēc vēl ir daudz darāmā.
Šī iegūtā tabula ir jāsaglabā kā savienojums (lai mēs varētu to izmantot, lai to apvienotu ar 3. tabulu).
Tālāk ir norādītas darbības, lai šo apvienoto tabulu (ar datiem no Sales_Data un Pdt_Id tabulas) saglabātu kā savienojumu:
- Noklikšķiniet uz cilnes Fails
- Noklikšķiniet uz opcijas “Aizvērt un ielādēt”.
- Dialoglodziņā “Importēt datus” atlasiet “Izveidot tikai savienojumu”.
- Noklikšķiniet uz Labi.
Tādējādi jaunie sapludinātie dati tiks saglabāti kā savienojums. Ja vēlaties, varat pārdēvēt šo savienojumu.
3. tabulas apvienošana ar rezultātu tabulu
Trešās tabulas apvienošanas process ar iegūto tabulu (ko mēs ieguvām, apvienojot 1. un 2. tabulu) ir tieši tāds pats.
Tālāk ir aprakstītas šo tabulu apvienošanas darbības.
- Noklikšķiniet uz cilnes Dati.
- Grupā Iegūt un pārveidot datus noklikšķiniet uz “Iegūt datus”.
- Nolaižamajā izvēlnē noklikšķiniet uz “Apvienot vaicājumus”.
- Noklikšķiniet uz "Apvienot". Tiks atvērts dialoglodziņš Apvienot.
- Dialoglodziņā Apvienot pirmajā nolaižamajā izvēlnē atlasiet “Apvienot1”.
- Otrajā nolaižamajā izvēlnē atlasiet “Reģions”.
- Priekšskatījumā “Apvienot1” noklikšķiniet uz slejas “Pārdošanas pārstāvis”. To darot, tiks atlasīta visa kolonna.
- Reģiona priekšskatījumā noklikšķiniet uz slejas “Pārdošanas pārstāvis”. To darot, tiks atlasīta visa kolonna.
- Nolaižamajā izvēlnē “Join Kind” atlasiet Left Outer (viss no pirmā, atbilstība no otrā).
- Noklikšķiniet uz Labi.
Iepriekš minētās darbības atvērs vaicājumu redaktoru un parādīs datus no sapludināšanas1 ar vienu papildu kolonnu (reģions).
Tagad tabulu apvienošanas process notiks vaicājumu redaktorā, veicot šādas darbības:
- Papildu slejā (Reģions) noklikšķiniet uz divvirzienu bultiņas galvenē.
- Atvērtajā opciju lodziņā noņemiet atzīmi no visiem kolonnu nosaukumiem un atlasiet tikai Reģions.
- Noņemiet atzīmi no opcijas “Lietot sākotnējo kolonnas nosaukumu kā prefiksu”.
- Noklikšķiniet uz Labi.
Iepriekš minētās darbības sniegs jums tabulu, kurā ir apvienotas visas trīs tabulas (tabula Sales_Data ar vienu kolonnu Pdt_Id un viena reģionam).
Tālāk ir norādītas darbības, kā ielādēt šo tabulu programmā Excel:
- Noklikšķiniet uz cilnes Fails.
- Noklikšķiniet uz “Aizvērt un ielādēt”.
- Dialoglodziņā “Importēt datus” atlasiet opcijas Tabula un jaunas darblapas.
- Noklikšķiniet uz Labi.
Tādējādi iegūtā apvienotā tabula tiktu parādīta jaunā darblapā.
Viena no labākajām Power Query lietām ir tā, ka jūs varat viegli pielāgot visas izmaiņas pamatdatos (1., 2. un 3. tabula), vienkārši atsvaidzinot tās.
Piemēram, pieņemsim, ka Laura tiek pārvesta uz Āziju un jūs saņemat jaunus datus par nākamo mēnesi. Tagad jums vairs nav jāatkārto iepriekš minētās darbības. Viss, kas jums jādara, ir atsvaidzināt galdu, un tas visu atkārtos jūsu vietā.
Dažu sekunžu laikā jums būs jauna apvienotā tabula.
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).
- Apvienojiet datus no vairākām darblapām vienā darblapā programmā Excel.
- 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)
