Construiți tabele cu anteturi diferite din mai multe cărți

Formularea problemei

Avem mai multe fișiere (în exemplul nostru – 4 bucăți, în cazul general – câte doriți) într-un folder Rapoarte:

În interior, aceste fișiere arată astfel:

Unde:

• Fișa tehnică de care avem nevoie se numește întotdeauna Fotografii, dar poate fi oriunde în registrul de lucru.
  
• Dincolo de cearșaf Fotografii Fiecare carte poate avea alte foi.
• Tabelele cu date au un număr diferit de rânduri și pot începe cu un rând diferit pe foaia de lucru.
• Numele acelorași coloane din tabele diferite pot diferi (de exemplu, Cantitate = Cantitate = Cant).
• Coloanele din tabele pot fi aranjate într-o ordine diferită.

Sarcină: colectați date de vânzări din toate fișierele din foaie Fotografii într-un singur tabel comun pentru a construi ulterior un rezumat sau orice alte analize pe acesta.

Pasul 1. Pregătirea unui director de nume de coloane

Primul lucru de făcut este să pregătiți o carte de referință cu toate opțiunile posibile pentru numele coloanelor și interpretarea corectă a acestora:

Transformăm această listă într-un tabel „inteligent” dinamic folosind butonul Format ca tabel din filă Acasă (Acasă — Format ca tabel) sau scurtătură de la tastatură Ctrl+T și încărcați-l în Power Query cu comanda Date – Din tabel/gamă (Date - Din tabel/gamă). În versiunile recente de Excel, acesta a fost redenumit în Cu frunze (Din foaie).

În fereastra editorului de interogări Power Query, ștergem în mod tradițional pasul Tip schimbat și adăugați un nou pas în loc de acesta făcând clic pe butonul f_xîn bara de formule (dacă nu este vizibilă, atunci o puteți activa în filă Recenzie) și introduceți formula acolo în limbajul încorporat Power Query M:

=Table.ToRows(Sursa)

Această comandă o va converti pe cea încărcată în pasul anterior Sursă tabel de referință într-o listă constând din liste imbricate (List), fiecare dintre acestea, la rândul său, o pereche de valori A fost-a devenit dintr-o linie:

Vom avea nevoie de acest tip de date puțin mai târziu, când vom redenumește în masă anteturile din toate tabelele încărcate.

După finalizarea conversiei, selectați comenzile Acasă — Închideți și încărcați — Închideți și încărcați în... și tipul de import Doar creați o conexiune (Acasă — Închide&Încărcare — Închide&Încărcare la... — Creați doar conexiune) și reveniți la Excel.

Pasul 2. Încărcăm totul din toate fișierele așa cum este

Acum să încărcăm conținutul tuturor fișierelor noastre din folder – deocamdată, așa cum este. Alegerea echipelor Date – Obține date – Din fișier – Din folder (Date — Obține date — Din fișier — Din folder) și apoi folderul în care se află cărțile noastre sursă.

În fereastra de previzualizare, faceți clic Converti (Transforma) or Schimba (Editați | ×):

Și apoi extindeți conținutul tuturor fișierelor descărcate (Binar) butonul cu săgeți duble în antetul coloanei Conţinut:

Power Query pe exemplul primului fișier (Vostok.xlsx) ne va întreba numele foii pe care dorim să o luăm din fiecare registru de lucru – alegeți Fotografii și apăsați OK:

După aceea (de fapt), vor avea loc mai multe evenimente care nu sunt evidente pentru utilizator, ale căror consecințe sunt clar vizibile în panoul din stânga:

1. Power Query va prelua primul fișier din folder (il vom avea Vostok.xlsx — vedea Exemplu de fișier) de exemplu și își importă conținutul prin crearea unei interogări Convertiți fișierul eșantion. Această interogare va avea câțiva pași simpli, cum ar fi Sursă (acces la fișiere) Navigare (selectarea foii) si eventual ridicarea titlurilor. Această solicitare poate încărca date numai dintr-un anumit fișier Vostok.xlsx.
   
2. Pe baza acestei solicitări, va fi creată funcția asociată acesteia Convertiți fișierul (indicat printr-o pictogramă caracteristică f_x), unde fișierul sursă nu va mai fi o constantă, ci o valoare variabilă – un parametru. Astfel, această funcție poate extrage date din orice carte pe care o introducem ca argument.
   
3. Funcția va fi aplicată pe rând fiecărui fișier (Binar) din coloană Conţinut – pasul este responsabil pentru asta Apelați funcția personalizată în interogarea noastră care adaugă o coloană la lista de fișiere Convertiți fișierul cu rezultatele importului din fiecare registru de lucru:

   

4. Coloanele suplimentare sunt eliminate.
5. Conținutul tabelelor imbricate este extins (pasul Coloana tabel extinsă) – și vedem rezultatele finale ale colectării datelor din toate cărțile:

   
   

Pasul 3. Slefuire

Captura de ecran anterioară arată clar că ansamblul direct „ca atare” s-a dovedit a fi de proastă calitate:

• Coloanele sunt inversate.
• Multe linii suplimentare (goale și nu numai).
• Anteturile de tabel nu sunt percepute ca antete și sunt amestecate cu date.
  

Puteți remedia toate aceste probleme foarte ușor - doar modificați interogarea Conversie fișier eșantion. Toate ajustările pe care le facem acestuia vor intra automat în funcția Conversie fișier asociată, ceea ce înseamnă că vor fi folosite ulterior la importul datelor din fiecare fișier.

Prin deschiderea unei cereri Convertiți fișierul eșantion, adăugați pași pentru a filtra rândurile inutile (de exemplu, după coloană Column2) și ridicând titlurile cu butonul Utilizați prima linie ca antete (Folosiți primul rând ca antete). Masa va arăta mult mai bine.

Pentru ca ulterior coloanele din fișiere diferite să se potrivească automat unele sub altele, acestea trebuie să fie denumite la fel. Puteți efectua o astfel de redenumire în masă conform unui director creat anterior cu o linie de cod M. Să apăsăm din nou butonul f_x în bara de formule și adăugați o funcție pentru a schimba:

= Table.RenameColumns(#”Elevated Headers”, Headers, MissingField.Ignore)

Această funcție preia tabelul de la pasul anterior Antete ridicate și redenumește toate coloanele din acesta conform listei de căutare imbricate Prima pagină. Al treilea argument MissingField.Ignorați este necesar astfel încât pe acele titluri care sunt în director, dar nu sunt în tabel, să nu apară o eroare.

De fapt, asta-i tot.

Revenind la cerere Rapoarte vom vedea o imagine complet diferită – mult mai frumoasă decât cea anterioară:

• Ce este Power Query, Power Pivot, Power BI și de ce un utilizator Excel are nevoie de ele
  
• Colectarea datelor din toate fișierele dintr-un folder dat
• Colectarea datelor din toate foile cărții într-un singur tabel