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
Ta metoda wykorzystuje starszą funkcję tabeli przestawnej. W nowszych wersjach programu Excel wiele scenariuszy jest obsługiwanych przy użyciu dodatku Power Query w celu połączenia danych przed utworzeniem tabeli przestawnej. Aby uzyskać więcej informacji, zobacz Power Query (Pobieranie & przekształcanie danych) do Pomocy programu Excel.
Konsolidowanie wielu zakresów
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.
Wynikowy raport w formie tabeli przestawnej korzysta z pól ogólnych, takich jak 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.
Używanie pól stron
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.
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), należy rozważyć zdefiniowanie nazwy dla każdego zakresu źródłowego w poszczególnych arkuszach. 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.
Używanie odwołań 3D lub polecenia Konsoliduj
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 przy użyciu kreatora
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.
Aby otworzyć Kreatora tabeli przestawnej i wykresu przestawnego, naciśnij klawisze ALT+D, a następnie klawisz P.
Dodaj Kreatora tabeli przestawnej i wykresu przestawnego do paska narzędzi Szybki dostęp. W tym celu wykonaj następujące czynności:
- Wybierz strzałkę obok paska narzędzi, a następnie wybierz pozycję Więcej poleceń.
- 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ń.
- W obszarze Wybierz polecenia z wybierz pozycję Wszystkie polecenia.
- Z listy wybierz pozycję Kreator tabel przestawnych i wykresów przestawnych, wybierz pozycję Dodaj, a następnie wybierz przycisk OK.
Zaznacz pustą komórkę (która nie jest częścią tabeli przestawnej) w skoroszycie.
Wybierz ikonę kreatora tabeli przestawnej i wykresów przestawnych na pasku narzędzi Szybki dostęp.
Na stronie Krok 1 kreatora wybierz pozycję Wiele zakresów konsolidacji (aby połączyć dane z wielu zakresów), a następnie wybierz przycisk Dalej.
Na stronie Krok 2a kreatora wybierz pozycję Utworzę pola stron, a następnie wybierz przycisk Dalej.
Na stronie kreatora Krok 2b wykonaj następujące czynności:
Przejdź wstecz do skoroszytu i zaznacz zakres komórek, a następnie z powrotem 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.
W obszarze Ile pól stron potrzebujesz? wybierz pozycję 0, a następnie wybierz przycisk Dalej.
Na stronie Krok 3 kreatora wybierz, czy tabela przestawna ma zostać dodana do nowego, czy do istniejącego arkusza, a następnie wybierz 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:
Dodaj Kreatora tabeli przestawnej i wykresu przestawnego do paska narzędzi Szybki dostęp. W tym celu wykonaj następujące czynności:
- Wybierz strzałkę obok paska narzędzi, a następnie wybierz pozycję Więcej poleceń.
- 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ń.
- W obszarze Wybierz polecenia z wybierz pozycję Wszystkie polecenia.
- Z listy wybierz pozycję Kreator tabel przestawnych i wykresów przestawnych, wybierz pozycję Dodaj, a następnie wybierz przycisk OK.
Zaznacz pustą komórkę (która nie jest częścią tabeli przestawnej) w skoroszycie.
Na stronie Krok 1 kreatora wybierz pozycję Wiele zakresów konsolidacji, a następnie wybierz przycisk Dalej.
Na stronie Krok 2a kreatora wybierz pozycję Utwórz dla mnie pole pojedynczej strony, a następnie wybierz przycisk Dalej.
Na stronie kreatora Krok 2b wykonaj następujące czynności:
Przejdź wstecz do skoroszytu i zaznacz zakres komórek, a następnie z powrotem 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.
Wybierz pozycję Dalej.
Na stronie Krok 3 kreatora wybierz, czy tabela przestawna ma zostać dodana do nowego, czy do istniejącego arkusza, a następnie wybierz 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:
Dodaj Kreatora tabeli przestawnej i wykresu przestawnego do paska narzędzi Szybki dostęp. W tym celu wykonaj następujące czynności:
- Wybierz strzałkę obok paska narzędzi, a następnie wybierz pozycję Więcej poleceń.
- 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ń.
- W obszarze Wybierz polecenia z wybierz pozycję Wszystkie polecenia.
- Z listy wybierz pozycję Kreator tabel przestawnych i wykresów przestawnych, wybierz pozycję Dodaj, a następnie wybierz przycisk OK.
Zaznacz pustą komórkę (która nie jest częścią tabeli przestawnej) w skoroszycie.
Na stronie Krok 1 kreatora wybierz pozycję Wiele zakresów konsolidacji, a następnie wybierz przycisk Dalej.
Na stronie Krok 2a kreatora wybierz pozycję Utworzę pola stron, a następnie wybierz przycisk Dalej.
Na stronie kreatora Krok 2b wykonaj następujące czynności:
Przejdź wstecz do skoroszytu i zaznacz zakres komórek, a następnie z powrotem 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.
W obszarze Ile pól stron potrzebujesz? wybierz liczbę pól stron, których chcesz użyć.
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.
Wybierz pozycję Dalej.
Na stronie Krok 3 kreatora wybierz, czy tabela przestawna ma zostać dodana do nowego, czy do istniejącego arkusza, a następnie wybierz pozycję Zakończ.