Zaawansowana analiza danych - konsolidacja danych

Mogłeś spotkać się z różnymi sytuacjami, w których musisz przedstawić skonsolidowane dane. Źródłem danych może być jedno lub kilka miejsc. Innym wyzwaniem może być to, że dane mogą być od czasu do czasu aktualizowane przez inne osoby.

Musisz wiedzieć, jak skonfigurować arkusz podsumowujący, który konsoliduje dane ze skonfigurowanych źródeł, kiedy tylko chcesz. W programie Excel możesz łatwo wykonać to zadanie w kilku krokach za pomocąData Tool – Consolidate.

Przygotowanie danych do konsolidacji

Zanim zaczniesz konsolidować dane, upewnij się, że istnieje spójność między źródłami danych. Oznacza to, że dane są uporządkowane w następujący sposób -

Każdy zakres danych znajduje się w osobnym arkuszu.
Każdy zakres danych ma format listy z etykietami w pierwszym wierszu.
Dodatkowo możesz mieć etykiety dla kategorii, jeśli ma to zastosowanie, w pierwszej kolumnie.
Wszystkie zakresy danych mają ten sam układ.
Wszystkie zakresy danych zawierają podobne fakty.
W każdym zakresie nie ma pustych wierszy ani kolumn.

W przypadku zewnętrznych źródeł danych należy zapewnić użycie predefiniowanego układu w postaci szablonu Excel.

Załóżmy, że dysponujesz danymi dotyczącymi sprzedaży różnych towarów z każdego regionu - wschodniego, północnego, południowego i zachodniego. Od czasu do czasu może zajść potrzeba skonsolidowania tych danych i przedstawienia mądrego podsumowania sprzedaży. Przygotowanie obejmuje:

Jeden arkusz roboczy na region - tj. Cztery arkusze z nazwami Wschód, Północ, Południe i Zachód. Mogą one znajdować się w tym samym lub różnych skoroszytach.
Każdy arkusz ma taki sam układ, przedstawiający szczegóły produktu, liczbę jednostek i ilość.
Musisz mądrze skonsolidować produkt danych. Dlatego upewnij się, że kolumna z etykietą Produkt jest pierwszą kolumną i zawiera etykiety Produktu.

Konsolidowanie danych w tym samym skoroszycie

Jeśli masz wszystkie dane, które musisz skonsolidować, w tym samym skoroszycie, wykonaj następujące czynności -

Step 1 - Upewnij się, że dane każdego regionu znajdują się w osobnym arkuszu.

Step 2 - Dodaj nowy arkusz roboczy i nazwij go Podsumowanie.

Step 3 - Kliknij arkusz Podsumowanie.

Step 4 - Kliknij komórkę, w której chcesz umieścić wyniki podsumowania.

Step 5 - Kliknij DATA na Wstążce.

Step 6 - Kliknij Consolidate przycisk w Data Tools Grupa.

Plik Consolidate pojawi się okno dialogowe.

Step 7 - Wybierz Sum z listy rozwijanej pod Function.

Step 8 - Wybierz dane z każdego arkusza w następujący sposób.

Kliknij ikonę w polu pod Odniesieniem.
Wybierz arkusz roboczy - Wschód.
Wybierz zakres danych.
Ponownie kliknij ikonę w polu pod Odniesieniem.

Wybrany zakres pojawi się w polu odniesienia -

Step 9 - Kliknij Addpo prawej stronie pola. Wybrany zakres danych pojawi się w polu poniżejAll References.

Step 10- Powtórz kroki 1-5 dla pozostałych arkuszy danych - północ, południe i zachód. Okno dialogowe Konsolidacja wygląda następująco.

Możesz zobaczyć, że zakresy danych są wyświetlane według arkusza w porządku alfabetycznym w polu poniżej All references.

Step 11 - Zaznacz pola Top row i Left column pod Use labels in. Kliknij OK.

Twoje dane są podsumowane produktowo dla regionów - wschodniego, północnego, południowego i zachodniego.

Możesz powtórzyć powyższe kroki, aby ręcznie odświeżyć podsumowanie wyników, kiedy tylko ich potrzebujesz.

Automatyczna konsolidacja danych

Załóżmy, że chcesz, aby arkusz podsumowania był aktualizowany automatycznie, gdy tylko nastąpią zmiany w danych. Aby to osiągnąć, musisz mieć łącza do danych źródłowych.

Step 1 - Zaznacz pole - Create links to source data w oknie dialogowym Konsoliduj i kliknij OK.

Twoje wyniki podsumowujące pojawią się z następującym konspektem -

Zauważysz, że nowa kolumna jest wstawiana po prawej stronie kolumny o nazwie Produkt.

Step 2- Kliknij znak + na konturze w wierszu zawierającym wartość produktu o nazwie Soap. Jak widać, nowa kolumna zawiera skonsolidowaną wartość dla każdego zestawu wartości produktów według regionu.

Konsolidowanie danych z różnych skoroszytów

W poprzednim przykładzie wszystkie dane, które chcesz podsumować, znajdują się w tym samym skoroszycie. Jednak jest prawdopodobne, że dane są przechowywane oddzielnie dla każdego regionu i są aktualizowane według regionu. W takim przypadku możesz skonsolidować dane w następujący sposób -

Step 1 - Otwórz skoroszyty zawierające dane, na przykład skoroszyty - Sprzedaż wschodnia, Sprzedaż północna, Sprzedaż południowa i Sprzedaż zachodnia.

Step 2 - Otwórz nowy skoroszyt.

Step 3 - W nowym arkuszu kliknij komórkę, w której ma się pojawić podsumowanie.

Step 4 - Kliknij kartę DANE na Wstążce.

Step 5 - Kliknij Konsoliduj w polu Narzędzia danych.

ZA Consolidatepojawi się okno dialogowe. W oknie dialogowym Konsolidacja -