Power Query var ļoti palīdzēt, ja vēlaties apvienot vairākas darbgrāmatas vienā darbgrāmatā.
Piemēram, pieņemsim, ka jums ir pārdošanas dati par dažādiem reģioniem (austrumiem, rietumiem, ziemeļiem un dienvidiem). Izmantojot Power Query, šos datus no dažādām darbgrāmatām varat apvienot vienā darblapā.
Ja jums ir šīs darbgrāmatas dažādās vietās/mapēs, ieteicams tās pārvietot vienā mapē (vai izveidot kopiju un ievietot šo darbgrāmatas kopiju tajā pašā mapē).
Sākumā man mapē ir četras darbgrāmatas (kā parādīts zemāk).
Tagad šajā apmācībā es aptveru trīs scenārijus, kuros varat apvienot datus no dažādām darbgrāmatām, izmantojot Power Query:
- Katrā darbgrāmatā ir dati Excel tabulā, un visu tabulu nosaukumi ir vienādi.
- Katrā darbgrāmatā ir dati ar vienu un to pašu darblapas nosaukumu. Tas var notikt, ja visās darbgrāmatās ir lapa ar nosaukumu “kopsavilkums” vai “dati”, un jūs vēlaties tos visus apvienot.
- Katrā darbgrāmatā ir daudz lapu un tabulu, un jūs vēlaties apvienot konkrētas tabulas/lapas. Šī metode var būt noderīga arī tad, ja vēlaties apvienot tabulu/lapas, kurām nav konsekventa nosaukuma.
Apskatīsim, kā katrā gadījumā apvienot datus no šīm darbgrāmatām.
Katrā darbgrāmatā ir dati Excel tabulā ar tādu pašu struktūru
Tālāk minētā metode darbosies, ja jūsu Excel tabulas ir strukturētas vienādi (vienādi kolonnu nosaukumi).
Rindu skaits katrā tabulā var atšķirties.
Neuztraucieties, ja dažās Excel tabulās ir papildu slejas. Jūs varat izvēlēties vienu no tabulām kā veidni (vai kā “atslēgu”, kā to sauc Power Query), un Power Query to izmantotu, lai ar to apvienotu visas pārējās Excel tabulas.
Ja citās tabulās ir papildu kolonnas, tās tiks ignorētas un tiks apvienotas tikai tās, kas norādītas veidnē/atslēgā. Piemēram, ja atlasītajā veidnē/atslēgu tabulā ir 5 kolonnas un vienā no tabulām citā darbgrāmatā ir 2 papildu kolonnas, šīs papildu kolonnas tiks ignorētas.
Tagad manā mapē ir četras darbgrāmatas, kuras es vēlos apvienot.
Zemāk ir tabulas momentuzņēmums, kas man ir vienā no darbgrāmatām.
Tālāk ir norādītas darbības, lai šos darbgrāmatas datus apvienotu vienā darbgrāmatā (kā viena tabula).
- Dodieties uz cilni Dati.
- Grupā Iegūt un pārveidot noklikšķiniet uz nolaižamās izvēlnes Jauns vaicājums.
- Virziet kursoru uz “No faila” un noklikšķiniet uz “No mapes”.
- Dialoglodziņā Mape ievadiet tās mapes faila ceļu, kurā ir faili, vai noklikšķiniet uz Pārlūkot un atrodiet mapi.
- Noklikšķiniet uz Labi.
- Atvērtajā dialoglodziņā noklikšķiniet uz apvienošanas pogas.
- Noklikšķiniet uz "Apvienot un ielādēt".
- Atvērtajā dialoglodziņā “Apvienot failus” kreisajā rūtī atlasiet tabulu. Ņemiet vērā, ka Power Query parāda tabulu no pirmā faila. Šis fails darbosies kā veidne (vai atslēga) citu failu apvienošanai. Power Query tagad meklēs “1. tabulu” citās darbgrāmatās un apvienos to ar šo.
- Noklikšķiniet uz Labi.
Galīgais rezultāts (kombinētie dati) tiks ielādēts jūsu aktīvajā darblapā.
Ņemiet vērā, ka kopā ar datiem Power Query automātiski pievieno darbgrāmatas nosaukumu kā kombinēto datu pirmo kolonnu. Tas palīdz izsekot, kādi dati ir iegūti no kādas darbgrāmatas.
Ja vēlaties vispirms rediģēt datus pirms to ievietošanas programmā Excel, 6. darbībā atlasiet “Apvienot un rediģēt”. Tas atvērs gala rezultātu Power Query redaktorā, kur varēsit rediģēt datus.
Dažas lietas, kas jāzina:
- Ja kā veidni atlasīsit Excel tabulu (7. darbībā), Power Query izmantos šajā tabulā esošo kolonnu nosaukumus, lai apvienotu datus no citām tabulām. Ja citās tabulās ir papildu slejas, tās tiks ignorētas. Ja šajās citās tabulās nav slejas, kas atrodas jūsu veidņu tabulā, Power Query tam vienkārši pievienos “null”.
- Slejām nav jābūt tādā pašā secībā, kā Power Query kolonnu kartēšanai izmanto kolonnu galvenes.
- Tā kā kā atslēgu esat izvēlējies 1. tabulu, Power Query visās darbgrāmatās meklēs 1. tabulu un apvienos tās visas. Ja tā neatrod Excel tabulu ar tādu pašu nosaukumu (šajā piemērā 1. tabula), Power Query parādīs kļūdu.
Jauno failu pievienošana mapei
Tagad veltīsim minūti un sapratīsim, ko mēs darījām, veicot iepriekš minētās darbības (kas mums prasīja tikai dažas sekundes).
Mēs dažu sekunžu laikā apvienojām datus no četrām dažādām darbgrāmatām vienā tabulā, pat neatverot nevienu darbgrāmatu.
Bet tas vēl nav viss.
Patiesais Power Query POWER ir tāds, ka tagad, pievienojot mapei vairāk failu, jums nav jāatkārto neviena no šīm darbībām.
Viss, kas jums jādara, lai pārvietotu jauno darbgrāmatu mapē, atsvaidzinātu vaicājumu, un tas automātiski apvienos datus no visām šīs mapes darbgrāmatām.
Piemēram, iepriekš minētajā piemērā, ja es pievienoju jaunu darbgrāmatu - “Mid-West.xlsx” uz mapi un atsvaidzināt vaicājumu, tas man uzreiz parādīs jauno kombinēto datu kopu.
Lūk, kā atsvaidzināt vaicājumu:
- Ar peles labo pogu noklikšķiniet uz Excel tabulas, kuru ielādējāt darblapā, un noklikšķiniet uz Atsvaidzināt.
- Rūtī “Darbgrāmatas vaicājums” ar peles labo pogu noklikšķiniet uz vaicājuma un noklikšķiniet uz Atsvaidzināt
- Dodieties uz cilni Dati un noklikšķiniet uz Atsvaidzināt.
Katrā darbgrāmatā ir dati ar vienu un to pašu darblapas nosaukumu
Ja jums nav datu Excel tabulā, bet visi lapu nosaukumi (no kuriem vēlaties apvienot datus) ir vienādi, varat izmantot šajā sadaļā parādīto metodi.
Ir dažas lietas, kurām jābūt piesardzīgām, ja tie ir tikai tabulas dati, nevis Excel tabula.
- Darblapu nosaukumiem jābūt vienādiem. Tas palīdzēs Power Query pārskatīt jūsu darbgrāmatas un apvienot datus no darblapām, kurām katrā darbgrāmatā ir vienāds nosaukums.
- Power Query ir reģistrjutīgs. Tas nozīmē, ka darblapa ar nosaukumu “dati” un “Dati” tiek uzskatīta par atšķirīgu. Līdzīgi kolonna ar virsrakstu “Veikals” un sleja ar “veikals” tiek uzskatīta par atšķirīgu.
- Lai gan ir svarīgi, lai sleju galvenes būtu vienādas, secībai nav jābūt vienādai. Ja “East.xlsx” 2. sleja ir “West.xlsx” 4. sleja, Power Query to pareizi saskaņos, kartējot galvenes.
Tagad apskatīsim, kā ātri apvienot datus no dažādām darbgrāmatām, kurās darblapas nosaukums ir vienāds.
Šajā piemērā man ir mape ar četriem failiem.
Katrā darbgrāmatā man ir darblapa ar nosaukumu “Dati”, kas satur datus šādā formātā (ņemiet vērā, ka šī nav Excel tabula).
Tālāk ir norādītas darbības, kā apvienot datus no vairākām darbgrāmatām vienā darblapā.
- Dodieties uz cilni Dati.
- Grupā Iegūt un pārveidot noklikšķiniet uz nolaižamās izvēlnes Jauns vaicājums.
- Virziet kursoru uz “No faila” un noklikšķiniet uz “No mapes”.
- Dialoglodziņā Mape ievadiet tās mapes faila ceļu, kurā ir faili, vai noklikšķiniet uz Pārlūkot un atrodiet mapi.
- Noklikšķiniet uz Labi.
- Atvērtajā dialoglodziņā noklikšķiniet uz apvienošanas pogas.
- Noklikšķiniet uz "Apvienot un ielādēt".
- Atvērtajā dialoglodziņā “Apvienot failus” kreisajā rūtī atlasiet “Dati”. Ņemiet vērā, ka Power Query parāda darblapas nosaukumu no pirmā faila. Šis fails darbosies kā atslēga/veidne citu failu apvienošanai. Power Query izskatīs katru darbgrāmatu, atradīs lapu ar nosaukumu “Dati” un apvienos visus šos.
- Noklikšķiniet uz Labi. Tagad Power Query izskatīs katru darbgrāmatu, meklējiet tajā darblapu ar nosaukumu “Dati” un pēc tam apvienojiet visas šīs datu kopas.
Galīgais rezultāts (kombinētie dati) tiks ielādēts jūsu aktīvajā darblapā.
Ja vēlaties vispirms rediģēt datus pirms to ievietošanas programmā Excel, 6. darbībā atlasiet “Apvienot un rediģēt”. Tas atvērs gala rezultātu Power Query redaktorā, kur varēsit rediģēt datus.
Katrā darbgrāmatā ir dati ar dažādiem tabulu nosaukumiem vai lapu nosaukumiem
Dažreiz jūs, iespējams, nesaņemat strukturētus un konsekventus datus (piemēram, tabulas ar tādu pašu nosaukumu vai darblapu ar tādu pašu nosaukumu).
Piemēram, pieņemsim, ka datus iegūstat no personas, kas izveidojusi šīs datu kopas, bet darblapas nosauca par Austrumu dati, Rietumu dati, Ziemeļu dati un Dienvidu dati.
Vai arī persona, iespējams, ir izveidojusi Excel tabulas, bet ar dažādiem nosaukumiem.
Šādos gadījumos jūs joprojām varat izmantot Power Query, taču jums tas jādara, veicot dažas papildu darbības.
- Dodieties uz cilni Dati.
- Grupā Iegūt un pārveidot noklikšķiniet uz nolaižamās izvēlnes Jauns vaicājums.
- Virziet kursoru uz “No faila” un noklikšķiniet uz “No mapes”.
- Dialoglodziņā Mape ievadiet tās mapes faila ceļu, kurā ir faili, vai noklikšķiniet uz Pārlūkot un atrodiet mapi.
- Noklikšķiniet uz Labi.
- Atvērtajā dialoglodziņā noklikšķiniet uz pogas Rediģēt. Tiks atvērts Power Query redaktors, kurā redzēsit detalizētu informāciju par visiem mapē esošajiem failiem.
- Turiet nospiestu vadības taustiņu un atlasiet slejas “Saturs” un “Nosaukums”, ar peles labo pogu noklikšķiniet un atlasiet “Noņemt citas slejas”. Tādējādi tiks noņemtas visas pārējās kolonnas, izņemot atlasītās kolonnas.
- Vaicājumu redaktora lentē noklikšķiniet uz “Pievienot kolonnu” un pēc tam noklikšķiniet uz “Pielāgota kolonna”.
- Dialoglodziņā Pievienot pielāgotu kolonnu piešķiriet jaunajai kolonnai nosaukumu “Datu importēšana” un izmantojiet šādu formulu = Excel.Darba grāmata ([SATURS]). Ņemiet vērā, ka šī formula ir reģistrjutīga un jums tā jāievada tieši tā, kā es šeit parādīju.
- Tagad jūs redzēsit jaunu kolonnu, kurā ir rakstīta tabula. Tagad ļaujiet man paskaidrot, kas šeit notika. Jūs norādījāt Power Query darbgrāmatu nosaukumus, un Power Query no katras darbgrāmatas (kas pašlaik atrodas tabulas šūnā) ir ielādējusi objektus, piemēram, darblapas, tabulas un nosauktos diapazonus. Jūs varat noklikšķināt uz baltās vietas blakus tekstam Tabula, un jūs redzēsit informāciju apakšā. Šajā gadījumā, tā kā katrā darbgrāmatā mums ir tikai viena tabula un viena darblapa, jūs varat redzēt tikai divas rindas.
- Noklikšķiniet uz dubultās bultiņas ikonas slejas “Datu importēšana” augšdaļā.
- Atvērtajā slejas datu lodziņā noņemiet atzīmi no izvēles rūtiņas “Lietot sākotnējo kolonnu kā prefiksu” un pēc tam noklikšķiniet uz Labi.
- Tagad jūs redzēsit izvērstu tabulu, kurā redzēsit vienu rindu katram tabulas objektam. Šajā gadījumā katrai darbgrāmatai lapas objekts un tabulas objekts ir uzskaitīti atsevišķi.
- Slejā Veids filtrējiet sarakstu, lai tiktu rādīta tikai tabula.
- Turiet nospiestu vadības taustiņu un atlasiet sleju Vārds un dati. Tagad ar peles labo pogu noklikšķiniet un noņemiet visas pārējās kolonnas.
- Slejā Dati noklikšķiniet uz dubultās bultiņas ikonas datu galvenes augšējā labajā stūrī.
- Atvērtajā slejas datu lodziņā noklikšķiniet uz Labi. Tas apvienos visu tabulu datus un parādīs tos Power Query.
- Tagad jūs varat veikt visas nepieciešamās izmaiņas un pēc tam atveriet cilni Sākums un noklikšķiniet uz Aizvērt un ielādēt.
Tagad ļaujiet man mēģināt ātri izskaidrot, ko mēs šeit darījām. Tā kā lapu nosaukumos vai tabulu nosaukumos nebija konsekvences, mēs izmantojām = Excel. Darbgrāmatas formulu, lai ielādētu visus Power Query darbgrāmatu objektus. Šie objekti var ietvert lapas, tabulas un nosauktos diapazonus. Kad mums bija visi objekti no visiem failiem, mēs tos filtrējām, lai ņemtu vērā tikai Excel tabulas. Tad mēs paplašinājām tabulās esošos datus un apvienojām visus šos.
Šajā piemērā mēs filtrējām datus, lai izmantotu tikai Excel tabulas (13. darbībā). Ja vēlaties apvienot lapas, nevis tabulas, varat filtrēt lapas.
Piezīme - šī metode sniegs jums kombinētos datus pat tad, ja kolonnu nosaukumos ir neatbilstība. Piemēram, ja vietnē East.xlsx jums ir kļūdaini uzrakstīta kolonna, jūs saņemsiet 5 slejas. Power Query aizpildīs datus kolonnās, ja tas tos atradīs, un, ja nevar atrast kolonnu, tā ziņos par vērtību “null”.
Tāpat, ja kādā no tabulu darblapām ir dažas papildu kolonnas, tās tiks iekļautas galarezultātā.
Tagad, ja iegūstat vairāk darbgrāmatu, no kurām jāapvieno dati, vienkārši kopējiet un ielīmējiet to mapē un atsvaidziniet Power Query
