Konsolidowanie wielu arkuszy w jednej tabeli przestawnej w programie Excel

Aby podsumować wyniki znajdujące się w oddzielnych zakresach arkuszy i przygotować raport na ich temat, możesz skonsolidować dane z poszczególnych zakresów arkuszy w jednej tabeli przestawnej w głównym arkuszu. Oddzielne zakresy arkuszy mogą być w tym samym skoroszycie co główny arkusz lub w innym. Podczas konsolidacji dane są składane, co ułatwia ich regularne lub doraźne aktualizowanie i agregowanie.

Powstały w ten sposób skonsolidowany raport w formie tabeli przestawnej może mieć następujące pola na liście pól tabeli przestawnej, które można dodać do tabeli przestawnej: Wiersz, Kolumna i Wartość. Ponadto raport może zawierać maksymalnie cztery pola filtru stron o nazwach Strona1, Strona2, Strona3 i Strona4.

Poszczególne zakresy danych powinny zostać rozmieszczone w formacie krzyżowym tak, aby nazwy wierszy i kolumn dla elementów, które mają być sumowane, pasowały do siebie. Podczas określania danych na potrzeby raportu nie uwzględniaj żadnych sum wierszy ani sum kolumn z danych źródłowych. W poniższym przykładzie pokazano cztery zakresy w formacie krzyżowym.

Podczas konsolidacji danych mogą być używane pola stron, które zawierają elementy reprezentujące co najmniej jeden zakres źródłowy. Na przykład jeśli konsolidujesz dane budżetowe z działów Marketing, Sprzedaż oraz Produkcja, pole strony może zawierać jeden element służący do wyświetlania danych z każdego działu oraz element służący do wyświetlania połączonych danych. W poniższym przykładzie pokazano skonsolidowaną tabelę przestawną zawierającą jedno pole strony i wiele zaznaczonych elementów.

Jeśli zakres danych może ulec zmianie podczas następnego konsolidowania danych (to znaczy może się zmienić liczba wierszy), zastanów się, czy dla każdego zakresu źródłowego w poszczególnych arkuszach nie powinna zostać zdefiniowana nazwa. Następnie używaj tych nazw podczas konsolidowania zakresów w głównym arkuszu. Jeśli zakres źródłowy się rozszerza, możesz zaktualizować zakres dla nazwy w oddzielnym arkuszu, aby uwzględnić nowe dane przed odświeżeniem tabeli przestawnej.

Program Excel umożliwia również konsolidowanie danych innymi metodami, które pozwalają na pracę z danymi w wielu formatach i układach. Na przykład możesz tworzyć formuły zawierające odwołania 3-W lub używać polecenia Konsoliduj (na karcie Dane w grupie Narzędzia danych).

Do konsolidowania wielu zakresów możesz używać Kreatora tabeli przestawnej i wykresu przestawnego. W kreatorze możesz wybrać, czy chcesz używać jednego pola strony, wielu pól stron, czy w ogóle nie używać pól stron.

Aby połączyć dane ze wszystkich zakresów i utworzyć konsolidację, która nie zawiera pól stron, wykonaj następujące czynności:

1. Dodaj Kreatora tabeli przestawnej i wykresu przestawnego do paska narzędzi Szybki dostęp. W tym celu wykonaj następujące czynności:

   1. Wybierz strzałkę obok paska narzędzi, a następnie wybierz pozycję Więcej poleceń.

   2. Wybierz pozycję Dostosuj pasek narzędzi Szybki dostęp (...) w lewym dolnym rogu poniżej wstążki, a następnie wybierz pozycję Więcej poleceń.

   3. W obszarze Wybierz polecenia z wybierz pozycję Wszystkie polecenia.

   4. Na liście wybierz pozycję Kreator tabel przestawnych i wykresów przestawnych, wybierz pozycję Dodaj, a następnie wybierz przycisk OK.

2. Zaznacz pustą komórkę (która nie jest częścią tabeli przestawnej) w skoroszycie.

3. Wybierz ikonę Kreatora tabeli przestawnej i wykresu przestawnego na pasku narzędzi Szybki dostęp.

4. Na stronie kreatora Krok 1 wybierz pozycję Wiele zakresów konsolidacji, a następnie wybierz pozycję Dalej.

5. Na stronie kreatora Krok 2a wybierz pozycję Utworzę pola stron, a następnie wybierz pozycję Dalej.

6. Na stronie kreatora Krok 2b wykonaj następujące czynności:

   1. Wstecz do skoroszytu, zaznacz zakres komórek, a następnie w Kreatorze tabel przestawnych i wykresów przestawnych wybierz pozycję Dodaj.

      Porada: Jeśli zakres komórek znajduje się w innym skoroszycie, najpierw otwórz skoroszyt, aby ułatwić sobie zaznaczanie danych.

   2. W obszarze Ile pól stron potrzebujesz?, wybierz pozycję 0, a następnie wybierz pozycję Dalej.

7. Na stronie Kreatora Krok 3 wybierz między dodaniem tabeli przestawnej do nowego lub istniejącego arkusza, a następnie wybierz pozycję Zakończ.

Aby dołączyć jedno pole strony zawierające element dla każdego zakresu źródłowego oraz element, który konsoliduje wszystkie zakresy, wykonaj następujące czynności:

1. Dodaj Kreatora tabeli przestawnej i wykresu przestawnego do paska narzędzi Szybki dostęp. W tym celu wykonaj następujące czynności:

   1. Wybierz strzałkę obok paska narzędzi, a następnie wybierz pozycję Więcej poleceń.

   2. Wybierz pozycję Dostosuj pasek narzędzi Szybki dostęp (...) w lewym dolnym rogu poniżej wstążki, a następnie wybierz pozycję Więcej poleceń.

   3. W obszarze Wybierz polecenia z wybierz pozycję Wszystkie polecenia.

   4. Na liście wybierz pozycję Kreator tabel przestawnych i wykresów przestawnych, wybierz pozycję Dodaj, a następnie wybierz przycisk OK.

2. Zaznacz pustą komórkę (która nie jest częścią tabeli przestawnej) w skoroszycie.

3. Na stronie kreatora Krok 1 wybierz pozycję Wiele zakresów konsolidacji, a następnie wybierz pozycję Dalej.

4. Na stronie kreatora Krok 2a wybierz pozycję Utwórz dla mnie jedno pole strony, a następnie wybierz pozycję Dalej.

5. Na stronie kreatora Krok 2b wykonaj następujące czynności:

   1. Wstecz do skoroszytu, zaznacz zakres komórek, a następnie w Kreatorze tabel przestawnych i wykresów przestawnych wybierz pozycję Dodaj.

      Porada: Jeśli zakres komórek znajduje się w innym skoroszycie, najpierw otwórz skoroszyt, aby ułatwić sobie zaznaczanie danych.

6. Wybierz pozycję Dalej.

7. Na stronie Kreatora Krok 3 wybierz między dodaniem tabeli przestawnej do nowego lub istniejącego arkusza, a następnie wybierz pozycję Zakończ.

Możesz utworzyć wiele pól stron i przypisać własne nazwy elementów dla każdego zakresu źródłowego. Umożliwi to tworzenie konsolidacji częściowych lub pełnych; na przykład jedno pole strony, które konsoliduje Marketing i Sprzedaż bez Produkcji, oraz inne pole strony, które konsoliduje wszystkie trzy działy. Aby utworzyć konsolidację, która korzysta z wielu pól stron, wykonaj następujące czynności:

1. Dodaj Kreatora tabeli przestawnej i wykresu przestawnego do paska narzędzi Szybki dostęp. W tym celu wykonaj następujące czynności:

   1. Wybierz strzałkę obok paska narzędzi, a następnie wybierz pozycję Więcej poleceń.

   2. Wybierz pozycję Dostosuj pasek narzędzi Szybki dostęp (...) w lewym dolnym rogu poniżej wstążki, a następnie wybierz pozycję Więcej poleceń.

   3. W obszarze Wybierz polecenia z wybierz pozycję Wszystkie polecenia.

   4. Na liście wybierz pozycję Kreator tabel przestawnych i wykresów przestawnych, wybierz pozycję Dodaj, a następnie wybierz przycisk OK.

2. Zaznacz pustą komórkę (która nie jest częścią tabeli przestawnej) w skoroszycie.

3. Na stronie kreatora Krok 1 wybierz pozycję Wiele zakresów konsolidacji, a następnie wybierz pozycję Dalej.

4. Na stronie kreatora Krok 2a wybierz pozycję Utworzę pola stron, a następnie wybierz pozycję Dalej.

5. Na stronie kreatora Krok 2b wykonaj następujące czynności:

   1. Wstecz do skoroszytu, zaznacz zakres komórek, a następnie w Kreatorze tabel przestawnych i wykresów przestawnych wybierz pozycję Dodaj.

      Porada: Jeśli zakres komórek znajduje się w innym skoroszycie, najpierw otwórz skoroszyt, aby ułatwić sobie zaznaczanie danych.

   2. W obszarze Ile pól stron chcesz użyć? wybierz liczbę pól stron, których chcesz użyć.

   3. W obszarze Jakich etykiet elementów chcesz używać w polach stron do określania wybranego zakresu danych? dla każdego pola strony zaznacz zakres komórek, a następnie wprowadź nazwę dla tego zakresu.

      Przykład

      • Jeśli w obszarze Ile pól stron potrzebujesz? została wybrana pozycja 1, zaznacz każdy zakres, a następnie w polu Pole pierwsze wprowadź inną nazwę. Jeśli masz cztery zakresy, a każdy z nich odpowiada innemu kwartałowi roku obrachunkowego, zaznacz pierwszy zakres, wprowadź K1, zaznacz drugi zakres, wprowadź K2 i powtórz ten proces dla kwartałów K3 i K4.

      • Jeśli w obszarze Ile pól stron potrzebujesz? została wybrana pozycja 2, wykonaj w polu Pole pierwsze te same czynności co w poprzednim przykładzie. Następnie zaznacz dwa zakresy i w polu Pole drugie wprowadź tę samą nazwę, na przykład H1 i H2. Zaznacz pierwszy zakres, wprowadź H1, zaznacz drugi zakres, wprowadź H1, zaznacz trzeci zakres, wprowadź H2, zaznacz czwarty zakres, a następnie wprowadź H2.

   4. Wybierz pozycję Dalej.

6. Na stronie Kreatora Krok 3 wybierz między dodaniem tabeli przestawnej do nowego lub istniejącego arkusza, a następnie wybierz pozycję Zakończ.