Tabela przestawna w wielu zakresach danych

Sformułowanie problemu

Tabele przestawne to jedno z najbardziej niesamowitych narzędzi w programie Excel. Ale do tej pory niestety żadna z wersji Excela nie potrafi zrobić tak prostej i niezbędnej rzeczy w locie jak zbudowanie podsumowania dla kilku początkowych zakresów danych znajdujących się np. na różnych arkuszach lub w różnych tabelach:

Zanim zaczniemy, wyjaśnijmy kilka punktów. Uważam, że a priori w naszych danych spełnione są następujące warunki:

• Tabele mogą mieć dowolną liczbę wierszy z dowolnymi danymi, ale muszą mieć ten sam nagłówek.
• Na arkuszach z tabelami źródłowymi nie powinno być żadnych dodatkowych danych. Jeden arkusz – jeden stół. Aby kontrolować, radzę użyć skrótu klawiaturowego Ctrl+Koniec, który przenosi Cię do ostatnio używanej komórki w arkuszu. Najlepiej byłoby, gdyby była to ostatnia komórka w tabeli danych. Jeśli po kliknięciu Ctrl+Koniec podświetlona jest każda pusta komórka po prawej lub pod tabelą – usuń te puste kolumny po prawej lub wiersze pod tabelą za tabelą i zapisz plik.

Metoda 1: Tworzenie tabel dla tabeli przestawnej za pomocą dodatku Power Query

Począwszy od wersji 2010 dla programu Excel, dostępny jest bezpłatny dodatek Power Query, który może zbierać i przekształcać dowolne dane, a następnie podawać je jako źródło do tworzenia tabeli przestawnej. Rozwiązanie naszego problemu za pomocą tego dodatku wcale nie jest trudne.

Najpierw stwórzmy nowy pusty plik w Excelu – odbędzie się w nim montaż, a następnie zostanie w nim utworzona tabela przestawna.

Następnie na karcie Dane (jeśli masz Excel 2016 lub nowszy) lub na karcie Zapytanie o moc (jeśli masz Excel 2010-2013) wybierz polecenie Utwórz zapytanie – Z pliku – Excel (Pobierz dane — Z pliku — Excel) i określ plik źródłowy z tabelami, które mają zostać zebrane:

W wyświetlonym oknie wybierz dowolny arkusz (nie ma znaczenia który) i naciśnij przycisk poniżej zmiana (Edytować):

Okno Edytora Power Query Query powinno zostać otwarte na wierzchu programu Excel. Po prawej stronie okna na panelu Parametry żądania usuń wszystkie automatycznie utworzone kroki z wyjątkiem pierwszego – Źródło (Źródło):

Teraz widzimy ogólną listę wszystkich arkuszy. Jeśli oprócz arkuszy danych w pliku znajdują się jeszcze inne arkusze boczne, to na tym etapie naszym zadaniem jest wybranie tylko tych arkuszy, z których należy załadować informacje, wyłączając wszystkie pozostałe za pomocą filtru w nagłówku tabeli:

Usuń wszystkie kolumny z wyjątkiem kolumny Daneklikając prawym przyciskiem myszy nagłówek kolumny i wybierając Usuń inne kolumny (Usunąć inne kolumny):

Możesz następnie rozwinąć zawartość zebranych tabel, klikając podwójną strzałkę u góry kolumny (pole wyboru Użyj oryginalnej nazwy kolumny jako przedrostka możesz to wyłączyć):

Jeśli zrobiłeś wszystko poprawnie, w tym momencie powinieneś zobaczyć zawartość wszystkich tabel zebranych jedna pod drugą:

Pozostaje podnieść pierwszy wiersz do nagłówka tabeli za pomocą przycisku Użyj pierwszego wiersza jako nagłówków (Użyj pierwszego wiersza jako nagłówków) Zakładka Strona główna (Dom) i usuń zduplikowane nagłówki tabeli z danych za pomocą filtra:

Zapisz wszystko, co zostało zrobione za pomocą polecenia Zamknij i załaduj – Zamknij i załaduj w… (Zamknij i załaduj — Zamknij i załaduj do…) Zakładka Strona główna (Dom)i w oknie, które się otworzy, wybierz opcję Tylko połączenie (Tylko połączenie):

Wszystko. Pozostaje tylko zbudować podsumowanie. W tym celu przejdź do zakładki Wstaw – tabela przestawna (Wstaw — tabela przestawna), wybierz opcję Użyj zewnętrznego źródła danych (Użyj zewnętrznego źródła danych)a następnie klikając przycisk Wybierz połączenie, nasza prośba. Dalsze tworzenie i konfiguracja pivota odbywa się w całkowicie standardowy sposób, przeciągając potrzebne nam pola do obszaru wierszy, kolumn i wartości:

Jeśli w przyszłości zmienią się dane źródłowe lub zostanie dodanych kilka kolejnych arkuszy sklepowych, to wystarczy zaktualizować zapytanie i nasze podsumowanie za pomocą polecenia Odśwież wszystko Zakładka Dane (Dane — Odśwież wszystko).

Metoda 2. Łączymy tabele za pomocą polecenia UNION SQL w makrze

Innym rozwiązaniem naszego problemu jest to makro, które tworzy zbiór danych (cache) dla tabeli przestawnej za pomocą polecenia JEDNOŚĆ Język zapytań SQL. To polecenie łączy tabele ze wszystkich określonych w tablicy Nazwy arkuszy arkusze książki w jedną tabelę danych. Oznacza to, że zamiast fizycznie kopiować i wklejać zakresy z różnych arkuszy do jednego, robimy to samo w pamięci RAM komputera. Następnie makro dodaje nowy arkusz o podanej nazwie (zmienna Nazwa arkusza wyników) i tworzy na nim pełnoprawne (!) podsumowanie na podstawie zebranej pamięci podręcznej.

Aby użyć makra, użyj przycisku Visual Basic na karcie wywoływacz (Deweloper) lub skrót klawiaturowy inny+F11. Następnie wstawiamy nowy pusty moduł przez menu Wstaw – moduł i skopiuj tam następujący kod:

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim SheetsNames As Variant 'nazwa arkusza, w którym zostanie wyświetlony wynikowy element przestawny ResultSheetName = "Pivot" 'tablica arkusza nazwy z tabelami źródłowymi SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'tworzymy pamięć podręczną dla tabel z arkuszy z SheetsNames Za pomocą ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) For i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Next i Set objRS = CreateObject("ADODB.Recordset") objRS .Open Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) End With 'ponownie utwórz arkusz, aby wyświetlić wynikową tabelę przestawną Po błędzie Wznów Dalej Application.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo t. Name = ResultSheetName 'wyświetl wygenerowane podsumowanie pamięci podręcznej w tym arkuszu Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Ustaw objRS = Nic z wsPivot objPivotCache.CreatePivotTable=RwsTableDestination:" objPivotCache = Nic Zakres("A3").Wybierz End With End Sub    

Gotowe makro można następnie uruchomić za pomocą skrótu klawiaturowego inny+F8 lub przycisk Makra na karcie wywoływacz (Deweloper — Makra).

Wady tego podejścia:

• Dane nie są aktualizowane, ponieważ pamięć podręczna nie ma połączenia z tabelami źródłowymi. Jeśli zmienisz dane źródłowe, musisz ponownie uruchomić makro i ponownie zbudować podsumowanie.
• Przy zmianie ilości arkuszy konieczna jest edycja kodu makra (tablica Nazwy arkuszy).
  

Ale w końcu otrzymujemy prawdziwy, pełnoprawny stół przestawny, zbudowany na kilku zakresach z różnych arkuszy:

Nie!

Uwaga techniczna: jeśli podczas uruchamiania makra pojawi się błąd typu „Dostawca nie jest zarejestrowany”, najprawdopodobniej masz zainstalowaną 64-bitową wersję programu Excel lub zainstalowaną niekompletną wersję pakietu Office (brak dostępu). Aby naprawić sytuację, zamień fragment w kodzie makra:

	 Dostawca=Microsoft.Jet.OLEDB.4.0;  

do:

	Dostawca=Microsoft.ACE.OLEDB.12.0;  

Pobierz i zainstaluj bezpłatny aparat przetwarzania danych z programu Access z witryny firmy Microsoft — Microsoft Access Database Engine 2010 Redistributable

Metoda 3: Skonsoliduj kreatora tabeli przestawnej ze starych wersji programu Excel

Ta metoda jest trochę przestarzała, ale warto wspomnieć. Formalnie rzecz biorąc, we wszystkich wersjach do roku 2003 włącznie, w Kreatorze tabeli przestawnej istniała opcja „zbudowania elementu obrotowego dla kilku zakresów konsolidacji”. Jednak tak skonstruowany raport będzie niestety tylko żałosnym pozorem prawdziwego, pełnoprawnego podsumowania i nie obsługuje wielu „żetonów” konwencjonalnych tabel przestawnych:

W takim przestawieniu nie ma nagłówków kolumn na liście pól, nie ma ustawienia elastycznej struktury, zestaw używanych funkcji jest ograniczony i ogólnie rzecz biorąc, nie jest to bardzo podobne do tabeli przestawnej. Być może dlatego, począwszy od 2007 roku, firma Microsoft usunęła tę funkcję ze standardowego okna dialogowego podczas tworzenia raportów tabeli przestawnej. Teraz ta funkcja jest dostępna tylko za pomocą przycisku niestandardowego Kreator tabeli przestawnej(Kreator tabeli przestawnej), który w razie potrzeby można dodać do paska narzędzi szybkiego dostępu za pomocą Plik – Opcje – Dostosuj pasek narzędzi szybkiego dostępu – Wszystkie polecenia (Plik — Opcje — Dostosuj pasek narzędzi szybkiego dostępu — Wszystkie polecenia):

Po kliknięciu na dodany przycisk należy w pierwszym kroku kreatora wybrać odpowiednią opcję:

A następnie w następnym oknie wybierz kolejno każdy zakres i dodaj go do ogólnej listy:

Ale znowu nie jest to pełne podsumowanie, więc nie oczekuj od niego zbyt wiele. Tę opcję mogę polecić tylko w bardzo prostych przypadkach.

• Tworzenie raportów za pomocą tabel przestawnych
• Skonfiguruj obliczenia w tabelach przestawnych
• Czym są makra, jak ich używać, gdzie skopiować kod VBA itp.
• Zbieranie danych z wielu arkuszy do jednego (dodatek PLEX)