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.
Uwaga: Innym sposobem konsolidowania danych jest użycie dodatku Power Query. Aby uzyskać więcej informacji, zobacz Power Query dla pomocy programu 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:
-
Dodaj Kreatora tabeli przestawnej i wykresu przestawnego do paska narzędzi Szybki dostęp. W tym celu wykonaj następujące czynności:
-
Kliknij strzałkę obok tego paska narzędzi, a następnie kliknij polecenie Więcej poleceń.
-
Kliknij pozycję Dostosuj pasek narzędzi Szybki dostęp (...) w lewym dolnym rogu poniżej wstążki, a następnie kliknij pozycję Więcej poleceń.
-
W obszarze Wybierz polecenia z wybierz pozycję Wszystkie polecenia.
-
Wybierz z listy pozycję Kreator tabeli przestawnej i wykresu przestawnego, kliknij przycisk Dodaj, a następnie kliknij przycisk OK.
-
-
Kliknij pustą komórkę (która nie jest częścią tabeli przestawnej) w skoroszycie.
-
Kliknij ikonę Kreatora tabeli przestawnej i wykresu przestawnego na pasku narzędzi Szybki dostęp.
-
Na stronie kreatora Krok 1 kliknij pozycję Wiele zakresów konsolidacji, a następnie kliknij przycisk Dalej.
-
Na stronie kreatora Krok 2a kliknij pozycję Pola strony utworzy użytkownik, a następnie kliknij przycisk Dalej.
-
Na stronie kreatora Krok 2b wykonaj następujące czynności:
-
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.
-
W obszarze Ile pól stron potrzebujesz? kliknij pozycję 0, a następnie kliknij przycisk Dalej.
-
-
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.
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:
-
Dodaj Kreatora tabeli przestawnej i wykresu przestawnego do paska narzędzi Szybki dostęp. W tym celu wykonaj następujące czynności:
-
Kliknij strzałkę obok tego paska narzędzi, a następnie kliknij polecenie Więcej poleceń.
-
Kliknij pozycję Dostosuj pasek narzędzi Szybki dostęp (...) w lewym dolnym rogu poniżej wstążki, a następnie kliknij pozycję Więcej poleceń.
-
W obszarze Wybierz polecenia z wybierz pozycję Wszystkie polecenia.
-
Wybierz z listy pozycję Kreator tabeli przestawnej i wykresu przestawnego, kliknij przycisk Dodaj, a następnie kliknij przycisk OK.
-
-
Kliknij pustą komórkę (która nie jest częścią tabeli przestawnej) w skoroszycie.
-
Na stronie kreatora Krok 1 kliknij pozycję Wiele zakresów konsolidacji, a następnie kliknij przycisk Dalej.
-
Na stronie kreatora Krok 2a kliknij pozycję Utwórz jedno pole strony, a następnie kliknij przycisk Dalej.
-
Na stronie kreatora Krok 2b wykonaj następujące czynności:
-
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.
-
-
Kliknij przycisk Dalej.
-
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.
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:
-
Dodaj Kreatora tabeli przestawnej i wykresu przestawnego do paska narzędzi Szybki dostęp. W tym celu wykonaj następujące czynności:
-
Kliknij strzałkę obok tego paska narzędzi, a następnie kliknij polecenie Więcej poleceń.
-
Kliknij pozycję Dostosuj pasek narzędzi Szybki dostęp (...) w lewym dolnym rogu poniżej wstążki, a następnie kliknij pozycję Więcej poleceń.
-
W obszarze Wybierz polecenia z wybierz pozycję Wszystkie polecenia.
-
Wybierz z listy pozycję Kreator tabeli przestawnej i wykresu przestawnego, kliknij przycisk Dodaj, a następnie kliknij przycisk OK.
-
-
Kliknij pustą komórkę (która nie jest częścią tabeli przestawnej) w skoroszycie.
-
Na stronie kreatora Krok 1 kliknij pozycję Wiele zakresów konsolidacji, a następnie kliknij przycisk Dalej.
-
Na stronie kreatora Krok 2a kliknij pozycję Pola strony utworzy użytkownik, a następnie kliknij przycisk Dalej.
-
Na stronie kreatora Krok 2b wykonaj następujące czynności:
-
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.
-
W obszarze Ile pól stron potrzebujesz? kliknij liczbę pól stron, których chcesz używać.
-
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.
-
-
Kliknij przycisk Dalej.
-
-
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.