Spis treści
Microsoft Excel posiada wiele narzędzi do zbierania danych z kilku tabel (z różnych arkuszy lub z różnych plików): bezpośrednie linki, funkcja POŚREDNI (POŚREDNI), dodatki Power Query i Power Pivot itp. Z tej strony barykady wszystko wygląda dobrze.
Ale jeśli napotkasz problem odwrotny – rozłożenie danych z jednej tabeli na różne arkusze – wszystko będzie znacznie smutniejsze. W tej chwili w arsenale Excela nie ma niestety wbudowanych cywilizowanych narzędzi do takiej separacji danych. Będziesz więc musiał użyć makra w Visual Basic lub użyć kombinacji rejestratora makr i dodatku Power Query z niewielkim „doprecyzowaniem pliku”.
Przyjrzyjmy się bliżej, jak można to wdrożyć.
Sformułowanie problemu
Jako dane wyjściowe mamy taką tabelę o wielkości ponad 5000 wierszy do sprzedaży:
Zadanie: rozmieścić dane z tej tabeli według miast na osobnych arkuszach tej książki. Tych. na wyjściu musisz uzyskać na każdym arkuszu tylko te wiersze z tabeli, w której sprzedaż była w odpowiednim mieście:
Przygotować
Aby nie komplikować kodu makra i uczynić go tak łatwym do zrozumienia, jak to możliwe, wykonajmy kilka kroków przygotowawczych.
Po pierwsze, utwórz osobną tabelę przeglądową, gdzie pojedyncza kolumna zawiera listę wszystkich miast, dla których chcesz utworzyć oddzielne arkusze. Oczywiście katalog ten może nie zawierać wszystkich miast występujących w danych źródłowych, a jedynie te, dla których potrzebujemy raportów. Najłatwiejszym sposobem utworzenia takiej tabeli jest użycie polecenia Dane – Usuń duplikaty (Dane — Usuń duplikaty) dla kopii kolumny Miasto lub funkcja UNIK (UNIKALNY) – jeśli masz najnowszą wersję Excel 365.
Ponieważ nowe arkusze w Excelu są domyślnie tworzone przed (po lewej) bieżącym (poprzednim), sensowne jest również sortowanie miast w tym katalogu w kolejności malejącej (od Z do A) – a następnie po utworzeniu miasto arkusze zostaną ułożone alfabetycznie.
Po drugie, пprzekonwertuj obie tabele na dynamiczne („inteligentne”), aby ułatwić pracę z nimi. Używamy polecenia Strona główna – Formatuj jako tabelę (Strona główna — Formatuj jako tabelę) lub skrót klawiaturowy Ctrl+T. Na wyświetlonej karcie Konstruktor (Projekt) zadzwońmy do nich tablProdaji и TabelaMiastoodpowiednio:
Metoda 1. Makro do dzielenia przez arkusze
Na karcie Zaawansowane wywoływacz (Deweloper) kliknij przycisk Visual Basic lub użyj skrótu klawiaturowego inny+F11. W oknie edytora makr, które się otworzy, wstaw nowy pusty moduł za pomocą menu Wstaw – moduł i skopiuj tam następujący kod:
Sub Splitter() Dla każdej komórki w zakresie("таблГорода") Range("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible).Copy Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Następna komórka Worksheets("Inne").ShowAllData End Sub Tutaj z pętlą Dla każdego … Dalej zaimplementował przejście przez komórki katalogu TabelaMiasto, gdzie dla każdego miasta jest filtrowany (metoda Autofiltr) w oryginalnej tabeli sprzedaży, a następnie skopiowanie wyników do nowo utworzonego arkusza. Po drodze utworzony arkusz zostaje przemianowany na tę samą nazwę miasta i włącza się na nim automatyczne dopasowanie szerokości kolumn dla piękna.
Stworzone makro możesz uruchomić w Excelu na zakładce wywoływacz przycisk Makra (Deweloper — Makra) lub skrót klawiaturowy inny+F8.
Metoda 2. Utwórz wiele zapytań w Power Query
Poprzednia metoda, przy całej swojej zwartości i prostocie, ma istotną wadę – arkusze tworzone przez makro nie są aktualizowane, gdy wprowadzane są zmiany w oryginalnej tabeli sprzedaży. Jeśli konieczne jest aktualizowanie w locie, musisz użyć pakietu VBA + Power Query, a raczej utworzyć za pomocą makra nie tylko arkusze z danymi statycznymi, ale także zaktualizowane zapytania Power Query.
Makro w tym przypadku jest częściowo podobne do poprzedniego (ma też cykl Dla każdego … Dalej do iteracji po miastach w katalogu), ale wewnątrz pętli nie będzie już filtrowania i kopiowania, ale tworzenie zapytania Power Query i przesyłanie jego wyników do nowego arkusza:
Sub Splitter2() Dla każdej komórki w zakresie("Tabela miast") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & "#""Zmieniony typ"" = Table.TransformColumnTypes(Source , {{""Kategoria"", wpisz tekst}, {""Nazwa"", wpisz tekst}, {""Miasto"", wpisz tekst}, {""Menedżer"", wpisz tekst}, {""Oferta date "", wpisz datetime}, {""Koszt"", wpisz numer}})," & Chr(13) & "" & Chr(10) & " #""Wiersze z zastosowanym filtrem"" = Table.Se " & _ "lectRows(#""Zmieniony typ"", każdy ([Miasto] = """ & komórka.Wartość & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Wiersze z zastosowanym filtrem""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Dostawca =Microsoft.Mashup.OleDb.1;Źródło danych=$Skoroszyt$;Lokalizacja=" & cell.Value & ";Właściwości rozszerzone=""""" _ , Miejsce docelowe:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("WYBIERZ *FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False Koniec z ActiveSheet.Name = cell.Value Następna komórka End Sub Po jego uruchomieniu zobaczymy te same arkusze według miasta, ale utworzone już zapytania Power Query będą je formować:
Przy jakichkolwiek zmianach w danych źródłowych wystarczy zaktualizować odpowiednią tabelę prawym przyciskiem myszy – polecenie Zaktualizuj i zapisz (Odświeżać) lub zaktualizuj wszystkie miasta naraz za pomocą przycisku Aktualizacja Wszystko Zakładka Dane (Dane — Odśwież wszystko).
- Czym są makra, jak je tworzyć i używać
- Zapisywanie arkuszy skoroszytu jako osobnych plików
- Zbieranie danych ze wszystkich arkuszy księgi w jedną tabelę