Cuprins
Formularea problemei
Să ne uităm la o soluție frumoasă pentru una dintre situațiile standard cu care se confruntă majoritatea utilizatorilor Excel mai devreme sau mai târziu: trebuie să colectați rapid și automat date dintr-un număr mare de fișiere într-un singur tabel final.
Să presupunem că avem următorul folder, care conține mai multe fișiere cu date din orașele filiale:
Numărul de fișiere nu contează și se poate schimba în viitor. Fiecare fișier are o foaie numită Vânzăriunde se află tabelul de date:
Numărul de rânduri (comenzi) din tabele, desigur, este diferit, dar setul de coloane este standard peste tot.
Sarcină: pentru a colecta date din toate fișierele într-o singură carte cu actualizarea automată ulterioară atunci când adăugați sau ștergeți fișiere de oraș sau rânduri din tabele. Conform tabelului final consolidat, atunci va fi posibil să construiți orice rapoarte, tabele pivot, date de filtrare-sortare etc. Principalul lucru este să puteți colecta.
Alegem armele
Pentru soluție, avem nevoie de cea mai recentă versiune de Excel 2016 (funcționalitatea necesară este deja încorporată implicit în ea) sau versiuni anterioare de Excel 2010-2013 cu programul de completare gratuit instalat Interogare de alimentare de la Microsoft (descărcați-l aici). Power Query este un instrument super flexibil și super puternic pentru a încărca date în Excel din lumea exterioară, apoi a le elimina și a le procesa. Power Query acceptă aproape toate sursele de date existente – de la fișiere text la SQL și chiar Facebook 🙂
Dacă nu ai Excel 2013 sau 2016, atunci nu poți citi mai departe (glumesc). În versiunile mai vechi de Excel, o astfel de sarcină poate fi realizată doar prin programarea unei macrocomenzi în Visual Basic (ceea ce este foarte dificil pentru începători) sau prin copierea manuală monotonă (care durează mult timp și generează erori).
Pasul 1. Importați un fișier ca probă
În primul rând, să importăm datele dintr-un registru de lucru ca exemplu, astfel încât Excel „preia ideea”. Pentru a face acest lucru, creați un nou registru de lucru gol și...
- dacă aveți Excel 2016, deschideți fila Date și apoi Creare interogare – Din fișier – Din carte (Date - Interogare nouă - Din fișier - Din Excel)
- dacă aveți Excel 2010-2013 cu programul de completare Power Query instalat, atunci deschideți fila Interogare de alimentare și selectați pe el Din fișier – Din carte (Din fișier — Din Excel)
Apoi, în fereastra care se deschide, accesați folderul nostru cu rapoarte și selectați oricare dintre fișierele orașului (nu contează care, pentru că toate sunt tipice). După câteva secunde, ar trebui să apară fereastra Navigator, unde trebuie să selectați foaia de care avem nevoie (Vânzări) în partea stângă, iar conținutul acesteia va fi afișat în partea dreaptă:
Dacă faceți clic pe butonul din colțul din dreapta jos al acestei ferestre Descarcă (Sarcină), apoi tabelul va fi importat imediat în foaie în forma sa originală. Pentru un singur fișier, acest lucru este bine, dar trebuie să încărcăm multe astfel de fișiere, așa că vom merge puțin diferit și vom face clic pe butonul Corecție (Editați | ×). După aceea, editorul de interogări Power Query ar trebui să fie afișat într-o fereastră separată cu datele noastre din carte:
Acesta este un instrument foarte puternic care vă permite să „terminați” tabelul la vizualizarea de care avem nevoie. Chiar și o descriere superficială a tuturor funcțiilor sale ar dura aproximativ o sută de pagini, dar, dacă este foarte pe scurt, folosind această fereastră puteți:
- filtrați datele inutile, liniile goale, liniile cu erori
- sortați datele după una sau mai multe coloane
- scapă de repetiție
- împărțiți textul lipicios pe coloane (după delimitatori, număr de caractere etc.)
- puneți textul în ordine (eliminați spațiile suplimentare, corectați majuscule etc.)
- convertiți tipurile de date în toate modurile posibile (transformați numere precum textul în numere normale și invers)
- transpuneți (rotiți) tabele și extindeți tabele încrucișate bidimensionale în cele plate
- adăugați coloane suplimentare la tabel și utilizați formule și funcții în ele folosind limbajul M încorporat în Power Query.
- ...
De exemplu, să adăugăm o coloană cu numele text al lunii în tabelul nostru, astfel încât ulterior să fie mai ușor să construim rapoarte cu tabel pivot. Pentru a face acest lucru, faceți clic dreapta pe antetul coloanei datași selectați comanda Dublează coloana (Coloană duplicat), apoi faceți clic dreapta pe antetul coloanei duplicate care apare și selectați Comenzi Transform – Lună – Numele lunii:
Ar trebui să se formeze o nouă coloană cu numele text ale lunii pentru fiecare rând. Făcând dublu clic pe un titlu de coloană, îl puteți redenumi din Data copierii la un mai confortabil Lună, de exemplu.
Dacă în unele coloane programul nu a recunoscut corect tipul de date, atunci îl puteți ajuta făcând clic pe pictograma format din partea stângă a fiecărei coloane:
Puteți exclude linii cu erori sau linii goale, precum și manageri sau clienți inutile, folosind un filtru simplu:
Mai mult decât atât, toate transformările efectuate sunt fixate în panoul din dreapta, unde pot fi întotdeauna derulate înapoi (încrucișate) sau își pot modifica parametrii (angrenajul):
Ușoară și elegantă, nu-i așa?
Pasul 2. Să transformăm cererea noastră într-o funcție
Pentru a repeta ulterior toate transformările de date făcute pentru fiecare carte importată, trebuie să convertim cererea creată într-o funcție, care va fi apoi aplicată, la rândul său, tuturor fișierelor noastre. A face acest lucru este de fapt foarte simplu.
În Editorul de interogări, accesați fila Vizualizare și faceți clic pe butonul Editor avansat (Vizualizare — Editor avansat). Ar trebui să se deschidă o fereastră în care toate acțiunile noastre anterioare vor fi scrise sub formă de cod în limbajul M. Vă rugăm să rețineți că calea către fișierul pe care l-am importat pentru exemplu este codificată în cod:
Acum să facem câteva ajustări:
Sensul lor este simplu: prima linie (calea fișierului) => transformă procedura noastră într-o funcție cu un argument filepath, iar mai jos schimbăm calea fixă către valoarea acestei variabile.
Toate. Click pe finalizarea si ar trebui sa vezi asta:
Nu vă fie teamă că datele au dispărut – de fapt, totul este în regulă, totul ar trebui să arate așa 🙂 Am creat cu succes funcția noastră personalizată, în care întregul algoritm de importare și procesare a datelor este memorat fără a fi legat de un anumit fișier . Rămâne să îi dăm un nume mai ușor de înțeles (de exemplu Obțineți date) în panoul din dreapta în câmp Prenume și poți culege Acasă — Închideți și descărcați (Acasă — Închideți și încărcați). Vă rugăm să rețineți că calea către fișierul pe care l-am importat pentru exemplu este codificată în cod. Veți reveni la fereastra principală Microsoft Excel, dar în partea dreaptă ar trebui să apară un panou cu conexiunea creată la funcția noastră:
Pasul 3. Colectarea tuturor fișierelor
Tot ce e mai greu este în urmă, partea plăcută și ușoară rămâne. Accesați fila Date – Creare interogare – Din fișier – Din folder (Date — Interogare nouă — Din fișier — Din folder) sau, dacă aveți Excel 2010-2013, similar filei Interogare de alimentare. În fereastra care apare, specificați folderul în care se află toate fișierele orașului sursă și faceți clic OK. Următorul pas ar trebui să deschidă o fereastră în care vor fi listate toate fișierele Excel găsite în acest folder (și subfolderele sale) și detaliile pentru fiecare dintre ele:
Clic Schimba (Editați | ×) și din nou intrăm în fereastra familiară a editorului de interogări.
Acum trebuie să adăugăm o altă coloană la tabelul nostru cu funcția creată, care va „trage” datele din fiecare fișier. Pentru a face acest lucru, accesați fila Adăugați coloană – coloană personalizată (Adăugați o coloană — Adăugați o coloană personalizată) iar în fereastra care apare, introduceți funcția noastră Obțineți date, specificând pentru acesta ca argument calea completă către fiecare fișier:
După ce faceți clic pe OK coloana creată ar trebui adăugată la tabelul nostru din dreapta.
Acum să ștergem toate coloanele inutile (ca în Excel, folosind butonul din dreapta al mouse-ului - Elimină), lăsând doar coloana adăugată și coloana cu numele fișierului, deoarece acest nume (mai precis, orașul) va fi util să aveți în totalul datelor pentru fiecare rând.
Și acum „momentul wow” - faceți clic pe pictograma cu propriile săgeți din colțul din dreapta sus al coloanei adăugate cu funcția noastră:
… debifați Folosiți numele coloanei originale ca prefix (Folosiți numele coloanei originale ca prefix)și faceți clic OK. Și funcția noastră va încărca și procesa datele din fiecare fișier, urmând algoritmul înregistrat și colectând totul într-un tabel comun:
Pentru o frumusețe completă, puteți elimina și extensiile .xlsx din prima coloană cu nume de fișiere – prin înlocuirea standard cu „nimic” (clic dreapta pe antetul coloanei – Substitui) și redenumiți această coloană în Oraș. Și, de asemenea, corectați formatul datelor din coloana cu data.
Toate! Click pe Acasă – Închideți și încărcați (Acasă — Închideți și încărcați). Toate datele colectate de interogare pentru toate orașele vor fi încărcate în foaia Excel actuală în formatul „tabel inteligent”:
Conexiunea creată și funcția noastră de asamblare nu trebuie să fie salvate separat în niciun fel - sunt salvate împreună cu fișierul curent în mod obișnuit.
În viitor, cu orice modificări în folder (adăugarea sau eliminarea orașelor) sau în fișiere (modificarea numărului de linii), va fi suficient să faceți clic dreapta direct pe tabel sau pe interogarea din panoul din dreapta și să selectați comanda Actualizați și salvați (Reîmprospăta) – Power Query va „reconstrui” toate datele din nou în câteva secunde.
PS
Amendament. După actualizările din ianuarie 2017, Power Query a învățat cum să colecteze registrele de lucru Excel de la sine, adică nu mai este nevoie să facă o funcție separată - se întâmplă automat. Astfel, al doilea pas din acest articol nu mai este necesar și întregul proces devine considerabil mai simplu:
- Alege Creare cerere – Din fișier – Din folder – Selectați folder – OK
- După ce apare lista de fișiere, apăsați Schimba
- În fereastra Editor de interogări, extindeți coloana Binar cu o săgeată dublă și selectați numele foii care urmează să fie preluată din fiecare fișier
Și asta e tot! Cântec!
- Reproiectați tabelul încrucișat într-unul plat, potrivit pentru construirea de tabele pivot
- Crearea unei diagrame cu bule animate în Power View
- Macro pentru a asambla foi din diferite fișiere Excel într-unul singur