Veidojiet tabulas ar dažādām galvenēm no vairākām grāmatām

Problēmas formulēšana

Mums ir vairāki faili (mūsu piemērā - 4 gabali, vispārīgā gadījumā - tik daudz, cik vēlaties) vienā mapē Ziņojumi:

Iekšpusē šie faili izskatās šādi:

Kurā:

• Mums vajadzīgā datu lapa vienmēr tiek izsaukta pics, bet var atrasties jebkurā darbgrāmatas vietā.
  
• Ārpus palaga pics Katrai grāmatai var būt citas lapas.
• Tabulām ar datiem ir atšķirīgs rindu skaits, un tās var sākties ar citu rindu darblapā.
• Vienu un to pašu kolonnu nosaukumi dažādās tabulās var atšķirties (piemēram, Daudzums = Daudzums = Daudzums).
• Kolonnas tabulās var sakārtot citā secībā.

Uzdevums: savākt pārdošanas datus no visiem failiem no lapas pics vienā kopējā tabulā, lai pēc tam tajā izveidotu kopsavilkumu vai jebkuru citu analīzi.

Solis 1. Kolonnu nosaukumu direktorija sagatavošana

Pirmā lieta, kas jādara, ir sagatavot uzziņu grāmatu ar visām iespējamām kolonnu nosaukumu opcijām un to pareizu interpretāciju:

Mēs pārvēršam šo sarakstu par dinamisku “viedo” tabulu, izmantojot cilnes pogu Formatēt kā tabulu Sākumlapa (Sākums — formatēt kā tabulu) vai īsinājumtaustiņš Ctrl+T un ielādējiet to programmā Power Query ar komandu Dati — no tabulas/diapazona (Dati — no tabulas/diapazona). Jaunākajās Excel versijās tas ir pārdēvēts par Ar lapām (No lapas).

Power Query vaicājumu redaktora logā mēs tradicionāli izdzēšam darbību Mainīts tips un pievienojiet jaunu soli tā vietā, noklikšķinot uz pogas f_xformulas joslā (ja tas nav redzams, varat to iespējot cilnē Pārskats) un ievadiet formulu iebūvētajā Power Query valodā M:

=Table.ToRows(Source)

Šī komanda pārveidos to, kas tika ielādēta iepriekšējā darbībā avots atsauces tabulu sarakstā, kas sastāv no ligzdotiem sarakstiem (Saraksts), no kuriem katrs savukārt ir vērtību pāris Tā bija-kļuva no vienas rindas:

Šāda veida dati mums būs nepieciešami nedaudz vēlāk, masveidā pārdēvējot galvenes no visām ielādētām tabulām.

Pēc konvertēšanas atlasiet komandas Sākums — Aizvērt un ielādēt — Aizvērt un ielādēt… un importa veids Vienkārši izveidojiet savienojumu (Sākums — Aizvērt&Ielādēt — Aizvērt&Ielādēt uz... — Tikai izveidot savienojumu) un dodieties atpakaļ uz programmu Excel.

2. darbība. Mēs ielādējam visu no visiem failiem, kā tas ir

Tagad ielādēsim visu mūsu failu saturu no mapes — pagaidām, kā tas ir. Komandu izvēle Dati — iegūt datus — no faila — no mapes (Dati — iegūt datus — no faila — no mapes) un pēc tam mapi, kurā atrodas mūsu avota grāmatas.

Priekšskatījuma logā noklikšķiniet uz Pārvērst (Pārveidot) or Mainīt (Edit):

Un pēc tam izvērsiet visu lejupielādēto failu saturu (Binārs) poga ar dubultbultiņām kolonnas virsrakstā saturs:

Power Query pirmā faila piemērā (Vostok.xlsx) prasīs mums tās lapas nosaukumu, kuru vēlamies ņemt no katras darbgrāmatas – izvēlieties pics un nospiediet OK:

Pēc tam (faktiski) notiks vairāki lietotājam nepārprotami notikumi, kuru sekas ir skaidri redzamas kreisajā panelī:

1. Power Query paņems pirmo failu no mapes (mums tas būs Vostok.xlsx — redzēt Faila piemērs) kā piemēru un importē tā saturu, izveidojot vaicājumu Konvertēt parauga failu. Šim vaicājumam būs dažas vienkāršas darbības, piemēram avots (piekļuve failam) Navigācija (lokšņu atlase) un, iespējams, virsrakstu paaugstināšana. Šis pieprasījums var ielādēt datus tikai no viena konkrēta faila Vostok.xlsx.
   
2. Pamatojoties uz šo pieprasījumu, tiks izveidota ar to saistītā funkcija Konvertēt failu (norādīts ar raksturīgu ikonu f_x), kur avota fails vairs nebūs konstante, bet gan mainīgā vērtība – parametrs. Tādējādi šī funkcija var iegūt datus no jebkuras grāmatas, ko mēs tajā iekļaujam kā argumentu.
   
3. Funkcija pēc kārtas tiks piemērota katram failam (bināram) no kolonnas saturs – solis ir par to atbildīgs Izsaukt pielāgoto funkciju mūsu vaicājumā, kas failu sarakstam pievieno kolonnu Konvertēt failu ar importēšanas rezultātiem no katras darbgrāmatas:

   

4. Papildu kolonnas tiek noņemtas.
5. Ligzdoto tabulu saturs ir izvērsts (solis Paplašināta tabulas kolonna) – un mēs redzam galīgos datu vākšanas rezultātus no visām grāmatām:

   
   

Solis 3. Slīpēšana

Iepriekšējais ekrānuzņēmums skaidri parāda, ka tiešā montāža “tāda, kāda tā ir”, izrādījās sliktas kvalitātes:

• Kolonnas ir apgrieztas otrādi.
• Daudzas papildu rindas (tukšas un ne tikai).
• Tabulu galvenes netiek uztvertas kā galvenes un tiek sajauktas ar datiem.
  

Visas šīs problēmas var novērst ļoti vienkārši — vienkārši pielāgojiet vaicājumu Konvertēt parauga failu. Visas tajā veiktās korekcijas automātiski ietilps saistītajā faila konvertēšanas funkcijā, kas nozīmē, ka tās vēlāk tiks izmantotas, importējot datus no katra faila.

Atverot pieprasījumu Konvertēt parauga failu, pievienojiet darbības, lai filtrētu nevajadzīgas rindas (piemēram, pēc kolonnas Column2) un paceļot virsrakstus ar pogu Izmantojiet pirmo rindiņu kā galvenes (Izmantojiet pirmo rindu kā galvenes). Galds izskatīsies daudz labāk.

Lai kolonnas no dažādiem failiem vēlāk automātiski ietilptu viena zem otras, tām ir jābūt vienādam nosaukumam. Jūs varat veikt šādu masveida pārdēvēšanu saskaņā ar iepriekš izveidotu direktoriju ar vienu M-koda rindiņu. Nospiedīsim pogu vēlreiz f_x formulas joslā un pievienojiet funkciju, lai mainītu:

= Tabula.Pārdēvēt kolonnas (# "Paaugstinātas galvenes", galvenes, trūkst lauka. Ignorēt)

Šī funkcija pārņem tabulu no iepriekšējās darbības Paaugstinātas galvenes un pārdēvē visas tajā esošās kolonnas saskaņā ar ligzdoto uzmeklēšanas sarakstu Virsraksti. Trešais arguments Trūkst lauka. Ignorēt ir nepieciešams, lai tajos virsrakstos, kas atrodas direktorijā, bet nav tabulā, nerastos kļūda.

Patiesībā tas arī viss.

Atgriežoties pie pieprasījuma Ziņojumi redzēsim pavisam citu ainu – daudz jaukāku nekā iepriekšējā:

• Kas ir Power Query, Power Pivot, Power BI un kāpēc tie ir nepieciešami Excel lietotājam
  
• Datu vākšana no visiem failiem dotajā mapē
• Datu apkopošana no visām grāmatas lapām vienā tabulā