Cuprins
Microsoft Excel are multe instrumente pentru colectarea datelor din mai multe tabele (din foi diferite sau din fișiere diferite): link-uri directe, funcție INDIRECT (INDIRECT), suplimentele Power Query și Power Pivot etc. Din această parte a baricadei, totul arată bine.
Dar dacă te confrunți cu o problemă inversă – răspândirea datelor dintr-un tabel în foi diferite – atunci totul va fi mult mai trist. În acest moment, nu există instrumente civilizate încorporate pentru o astfel de separare a datelor în arsenalul Excel, din păcate. Așa că va trebui să utilizați o macrocomandă în Visual Basic sau să utilizați combinația de înregistrare macro + Power Query cu puțin „rafinare fișier” după.
Să aruncăm o privire mai atentă asupra modului în care acest lucru poate fi implementat.
Formularea problemei
Avem ca date inițiale un astfel de tabel cu o dimensiune de peste 5000 de rânduri pentru vânzări:
Sarcină: să distribuiți datele din acest tabel în funcție de oraș pe foi separate ale acestei cărți. Acestea. la ieșire, trebuie să obțineți pe fiecare foaie numai acele rânduri din tabelul în care a fost vânzarea în orașul corespunzător:
Pregăti
Pentru a nu complica codul macro și a-l face cât mai ușor de înțeles, haideți să efectuăm câțiva pași pregătitori.
În primul rând, creați un tabel de căutare separat, unde o singură coloană va lista toate orașele pentru care doriți să creați foi separate. Desigur, acest director poate să nu conțină toate orașele prezente în datele sursă, ci doar acelea pentru care avem nevoie de rapoarte. Cel mai simplu mod de a crea un astfel de tabel este folosirea comenzii Date – Eliminați duplicatele (Date — Eliminați duplicatele) pentru copierea coloanei Oraș sau funcție UNIC (UNIC) – dacă aveți cea mai recentă versiune de Excel 365.
Întrucât foile noi în Excel sunt create în mod implicit înainte (în stânga) celei curente (anterioare), este de asemenea logic să sortați orașele din acest director în ordine descrescătoare (de la Z la A) – apoi după creare, orașul foile vor fi aranjate alfabetic.
În al doilea rând, пconvertiți ambele tabele în dinamic („inteligent”) pentru a facilita lucrul cu ei. Folosim comanda Acasă – Formatați ca tabel (Acasă — Format ca tabel) sau scurtătură de la tastatură Ctrl+T. Pe fila care apare Constructor (Proiecta) hai sa-i numim tablProdaji и TableCity, respectiv:
Metoda 1. Macro pentru împărțirea pe foi
În fila Avansat dezvoltator (Dezvoltator) faceți clic pe buton Visual Basic sau utilizați comanda rapidă de la tastatură Alt+F11. În fereastra editorului de macrocomenzi care se deschide, inserați un nou modul gol prin meniu Inserare – Modul și copiați următorul cod acolo:
Sub Splitter() pentru fiecare celulă din interval("таблГорода") Range("таблПродажи"). Câmp de filtrare automată:=3, Criteria1:=celulă.Interval de valori("таблПродажи[#All]").SpecialCells(xlVisibleell).Copy. Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Данные").ShowAllData End Sub Aici cu o buclă Pentru fiecare... În continuare implementat trecerea prin celulele directorului TableCity, unde pentru fiecare oraș este filtrat (metoda Filtru automat) în tabelul original de vânzări și apoi copierea rezultatelor în foaia nou creată. Pe parcurs, foaia creată este redenumită cu același nume al orașului și pe ea este activată adaptarea automată a lățimii coloanelor pentru frumusețe.
Puteți rula macrocomanda creată în Excel din filă dezvoltator buton Macrocomenzi (Dezvoltator – Macrocomenzi) sau scurtătură de la tastatură Alt+F8.
Metoda 2. Creați mai multe interogări în Power Query
Metoda anterioară, cu toată compactitatea și simplitatea ei, are un dezavantaj semnificativ – foile create de macro nu sunt actualizate atunci când se fac modificări în tabelul de vânzări original. Dacă este necesară actualizarea din mers, atunci va trebui să utilizați pachetul VBA + Power Query sau, mai degrabă, să creați folosind o macrocomandă nu doar foi cu date statice, ci interogări Power Query actualizate.
Macro-ul în acest caz este parțial similar cu cel precedent (are și un ciclu Pentru fiecare... În continuare pentru a repeta peste orașe din director), dar în interiorul buclei nu va mai exista filtrare și copiere, ci crearea unei interogări Power Query și încărcarea rezultatelor acesteia într-o foaie nouă:
Sub Splitter2() Pentru fiecare celulă din Interval ("Tabelul orașului") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & "Sursa = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Conținut]," & Chr(13) & "" & Chr(10) & " #""Tip schimbat"" = Table.TransformColumnTypes(Sursa , {{""Categorie", tastați text}, {""Nume"", tastați text}, {""Oraș", tastați text}, {""Manager"", tastați text}, {""Ofertă data "", tastați datetime}, {""Cost", tip număr}})," & Chr(13) & "" & Chr(10) & " #""Rânduri cu filtru aplicat"" = Table.Se " & _ "lectRows(#""Tipul schimbat"", fiecare ([City] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Rânduri cu filtru aplicat""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Furnizor =Microsoft.Mashup.OleDb.1;Sursa de date=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePasword .False SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.Value Celula următoare End Sub După lansare, vom vedea aceleași foi în funcție de oraș, dar interogările Power Query deja create le vor forma:
Cu orice modificare a datelor sursă, va fi suficient să actualizați tabelul corespunzător cu butonul din dreapta al mouse-ului - comanda Actualizați și salvați (Reîmprospăta) sau actualizați toate orașele simultan în bloc folosind butonul Actualizați toate fila Date (Date — Actualizează tot).
- Ce sunt macrocomenzile, cum să le creați și să le utilizați
- Salvarea foilor registrului de lucru ca fișiere separate
- Colectarea datelor din toate foile cărții într-un singur tabel