tartalom
A Microsoft Excel számos eszközzel rendelkezik adatok gyűjtésére több táblázatból (különböző lapokból vagy különböző fájlokból): közvetlen hivatkozások, funkció KÖZVETETT (KÖZVETETT), Power Query és Power Pivot kiegészítők stb. A barikád innen oldaláról nézve minden jól néz ki.
De ha egy inverz problémába ütközik – az adatok egy táblázatból különböző lapokra történő szétosztása –, akkor minden sokkal szomorúbb lesz. Jelenleg sajnos nincsenek civilizált beépített eszközök ilyen adatleválasztásra az Excel arzenáljában. Tehát makrót kell használnod a Visual Basicben, vagy a makrórögzítő + Power Query kombinációt kell használnod egy kis "fájlfinomítással".
Nézzük meg közelebbről, hogyan lehet ezt megvalósítani.
A probléma megfogalmazása
Kiinduló adatunk van egy ilyen, több mint 5000 soros táblát eladásra:
Feladat: a táblázat adatainak városonkénti szétosztása a könyv külön lapjain. Azok. a kimeneten minden lapon csak azokat a sorokat kell megkapnia a táblázatból, ahol az eladás a megfelelő városban volt:
Készít
Annak érdekében, hogy ne bonyolítsuk a makrókódot, és a lehető legkönnyebben érthetővé tegyük, hajtsunk végre néhány előkészítő lépést.
Először is, a hozzon létre egy külön keresőtáblát, ahol egyetlen oszlopban lesz felsorolva az összes város, amelyhez külön lapokat szeretne létrehozni. Természetesen ez a könyvtár nem tartalmazza a forrásadatokban szereplő összes várost, hanem csak azokat, amelyekről jelentésre van szükségünk. Egy ilyen tábla létrehozásának legegyszerűbb módja a parancs használata Adatok – Az ismétlődések eltávolítása (Adatok – Ismétlődések eltávolítása) oszlopmásolathoz Város vagy funkciót EGYEDI (EGYEDI) – ha az Excel 365 legújabb verziójával rendelkezik.
Mivel az Excelben az új lapok alapértelmezés szerint az aktuális (előző) előtt (balra) jönnek létre, érdemes ebben a könyvtárban a városokat csökkenő sorrendbe rendezni (Z-től A-ig), majd a létrehozás után a várost. A lapok ábécé sorrendben lesznek elrendezve.
Másodszor, a пkonvertálja mindkét táblát dinamikussá („okos”), hogy könnyebb legyen velük dolgozni. A parancsot használjuk Kezdőlap – Táblázat formázása (Főoldal — Táblázat formázása) vagy billentyűparancsot Ctrl+T. A megjelenő lapon építész (Tervezés) hívjuk őket tablProdaji и TableCity, illetve:
1. módszer. Makró lapokra osztáshoz
A Speciális lapon fejlesztő (Fejlesztő) kattintson a gombra Visual Basic vagy használja a billentyűparancsot más+F11. A megnyíló makrószerkesztő ablakban a menün keresztül szúrjon be egy új üres modult Beszúrás – Modul és másold oda a következő kódot:
Sub Splitter() Minden cellához In Range("таблГорода") Range("таблПродажи").AutoFilter Field:=3, Criteria1:=cell. Value Range("таблПродажи[#All]").SpecialCells(xlC). Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Данные").ShowAllData End Sub Itt egy hurokkal Mindegyikhez… Következő megvalósította az átjárást a könyvtár celláin TableCity, ahol minden város esetében kiszűrjük (módszer Automatikus szűrő) az eredeti értékesítési táblázatban, majd az eredményeket másolja az újonnan létrehozott lapra. Útközben a létrehozott lapot átnevezzük a város azonos nevére, és bekapcsoljuk rajta az oszlopok szélességének automatikus illesztését a szépség érdekében.
A létrehozott makrót Excelben futtathatja a lapon fejlesztő gomb makrók (Fejlesztő – Makrók) vagy billentyűparancsot más+F8.
2. módszer: Hozzon létre több lekérdezést a Power Queryben
Az előző módszernek kompaktsága és egyszerűsége ellenére van egy jelentős hátránya – a makró által létrehozott lapok nem frissülnek, amikor az eredeti értékesítési táblázatot módosítják. Ha menet közbeni frissítésre van szükség, akkor a VBA + Power Query csomagot kell használnia, vagy inkább makróval kell létrehoznia, nem csak statikus adatokat tartalmazó lapokat, hanem frissített Power Query-lekérdezéseket.
A makró ebben az esetben részben hasonló az előzőhöz (ciklusa is van Mindegyikhez… Következő a könyvtárban lévő városok közötti iterációhoz), de a cikluson belül már nem szűrés és másolás történik, hanem Power Query lekérdezés létrehozása és annak eredményeinek feltöltése egy új munkalapra:
Sub Splitter2() Minden cellához In Range("City table") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source , {{""Category"", type text}, {""Name", type text}, {""City"", type text}, {""Manager", type text}, {""Ügylet" date "", type datetime}, {""Cost"", type number}})," & Chr(13) & "" & Chr(10) & " #""Sorok szűrővel"" = Table.Se " & _ "lectRows(#""Módosított típus"", mindegyik ([City] = """ & cell. Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Sorok alkalmazott szűrővel""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Szolgáltató =Microsoft.Mashup.OleDb.1;Adatforrás=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Tartomány("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = Hamis .FillAdjacentFormulas = Hamis .PreserveFormatting = Igaz .RefreshOnFileOpen = Hamis .BackgroundQuery = Igaz .RefreshStyle = xlInsertDeleteCells .SavePassword. SaveData = Igaz. AdjustColumnWidth = Igaz Indítása után városonként ugyanazokat a lapokat fogjuk látni, de a már létrehozott Power Query lekérdezések alkotják őket:
A forrásadatok bármilyen változása esetén elegendő a megfelelő táblázat frissítése a jobb egérgombbal - a parancs segítségével Frissítés és mentés (Frissítés) vagy frissítse az összes várost egyszerre tömegesen a gombbal Az összes frissítése lap dátum (Adatok – Összes frissítése).
- Mik azok a makrók, hogyan kell létrehozni és használni őket
- A munkafüzet lapjainak mentése külön fájlként
- Adatgyűjtés a könyv összes lapjáról egy táblázatba