Konsolidowanie wielu arkuszy w jedną tabelę przestawną

Konsolidowanie danych jest skutecznym sposobem łączenia danych z różnych źródeł w jednym raporcie. Na przykład jeśli masz tabelę przestawną z wartościami wydatków poszczególnych oddziałów regionalnych, możesz użyć funkcji konsolidacji danych w celu wprowadzenia tych danych do raportu wydatków firmy. Ten raport może zawierać sumy i średnie wartości sprzedaży, bieżące stany magazynowe i najlepiej sprzedające się produkty w całej firmie.

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.

Konfigurowanie danych źródłowych

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.

Przykładowe źródła danych dla konsolidacji raportu w formie tabeli przestawnej

Pola stron w konsolidacji danych

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.

Przykładowy skonsolidowany raport w formie tabeli przestawnej z polem strony.

Używanie nazwanych zakresó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.

Inne sposoby konsolidowania danych

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).

Konsolidowanie wielu zakresów

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.

Konsolidowanie danych bez użycia 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. Kliknij strzałkę obok tego paska narzędzi, a następnie kliknij polecenie Więcej poleceń.

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

    3. Wybierz z listy pozycję Kreator tabeli przestawnej i wykresu przestawnego, kliknij przycisk Dodaj, a następnie kliknij przycisk OK.

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

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

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

  5. Na stronie kreatora Krok 2a kliknij pozycję Pola strony utworzy użytkownik, a następnie kliknij przycisk Dalej.

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

    1. Wróć do skoroszytu i zaznacz zakres komórek, a następnie w Kreatorze tabeli przestawnej i wykresu przestawnego kliknij 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? kliknij pozycję 0, a następnie kliknij przycisk Dalej.

  7. Na stronie kreatora Krok 3 wybierz, czy tabela przestawna ma zostać dodana do nowego, czy do istniejącego arkusza, a następnie kliknij pozycję Zakończ.

Konsolidowanie danych z użyciem jednego pola strony

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. Kliknij strzałkę obok tego paska narzędzi, a następnie kliknij polecenie Więcej poleceń.

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

    3. Wybierz z listy pozycję Kreator tabeli przestawnej i wykresu przestawnego, kliknij przycisk Dodaj, a następnie kliknij przycisk OK.

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

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

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

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

    1. Wróć do skoroszytu i zaznacz zakres komórek, a następnie w Kreatorze tabeli przestawnej i wykresu przestawnego kliknij pozycję Dodaj.

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

  6. Kliknij przycisk Dalej.

  7. Na stronie kreatora Krok 3 wybierz, czy tabela przestawna ma zostać dodana do nowego, czy do istniejącego arkusza, a następnie kliknij pozycję Zakończ.

Konsolidowanie danych z użyciem wielu pól stron

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. Kliknij strzałkę obok tego paska narzędzi, a następnie kliknij polecenie Więcej poleceń.

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

    3. Wybierz z listy pozycję Kreator tabeli przestawnej i wykresu przestawnego, kliknij przycisk Dodaj, a następnie kliknij przycisk OK.

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

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

  4. Na stronie kreatora Krok 2a kliknij pozycję Pola strony utworzy użytkownik, a następnie kliknij przycisk Dalej.

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

    1. Wróć do skoroszytu i zaznacz zakres komórek, a następnie w Kreatorze tabeli przestawnej i wykresu przestawnego kliknij 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? kliknij liczbę pól stron, których chcesz używać.

    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. Kliknij przycisk Dalej.

  6. Na stronie kreatora Krok 3 wybierz, czy tabela przestawna ma zostać dodana do nowego, czy do istniejącego arkusza, a następnie kliknij pozycję Zakończ.

Początek strony

Rozwijaj umiejętności związane z pakietem Office
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

Dziękujemy za opinię!

Dziękujemy za opinię! Wygląda na to, że połączenie Cię z jednym z naszych agentów pomocy technicznej pakietu Office może być pomocne.

×