Jak zsumować dane z kilku arkuszy (konsolidacja)?

Problem:
Jak zsumować dane z kilku arkuszy (konsolidacja)?
 
Konsolidacja – czyli możliwość stosowania funkcji podsumowujących na danych z kilku arkuszy jest najbardziej przydatna przy pracy z powtarzającymi się danymi. W tym przykładzie pokażę jak zsumować dane o sprzedaży z kilku kolejnych miesięcy. Żeby zastosować tę metodę dane w poszczególnych arkuszach muszą mieć taki sam układ.
 
Rozwiązanie:
Na przykładzie prostego pliku z danymi z 3 miesięcy pokażemy jak zsumować dane za pomocą Konsolidacji.
 
Nasze dane wyglądają w następujący sposób:
 
 
Jak widać we wszystkich trzech arkuszach mamy dokładnie takie same kolumny i formuły. Takie rozwiązanie jest często wykorzystywane przy powtarzających się obliczeniach.
 
Konsolidację można przeprowadzić na dwa sposoby. Tutaj omówimy ten prostszy – konsolidację całych zakresów danych naraz.
 
Taką metodę możemy zastosować tylko w przypadku jeśli wszystkie dane w zakresach mają ten sam układ i na wszystkich chcemy użyć tej samej funkcji (u nas: Sumowanie).
 
Otwieramy pusty arkusz (może być w tym samym dokumencie). Przechodzimy na kartę Dane. Z sekcji Narzędzia danych wybieramy formant Konsoliduj.
 
Pojawia się okno dialogowe Konsolidowanie. W polu Funkcja wybieramy odpowiednią funkcję podsumowującą z listy, u nas będzie to Suma.
 
Następnie klikamy na krateczkę obok pola edycji Odwołanie.
 
Teraz musimy kolejno pododawać wszystkie zakresy, które chcemy podsumować – u nas będą to zakresy z arkuszy Styczeń, Luty i Marzec.
Klikamy na zakładkę Styczeń. Zaznaczamy cały zakres z danymi. Nasze okno dialogowe zmniejszyło się tylko do paska na adres zakresu. Klikamy na kratkę, żeby pokazać całe okno. Naciskamy przycisk Dodaj.
W tym momencie zakres Styczeń został dodany. Teraz wystarczy tylko na dole kliknąć zakładkę Luty(zmiana arkusza) i Excel automatycznie ustawi zakres w tym samym miejscu co poprzednio.
Ponownie naciskamy Dodaj i analogicznie dodajemy zakres z arkusza Marzec (klikamy na zakładkę z nazwą arkusza i przycisk Dodaj).
 
 
Jeśli wszystkie zakresy do konsolidacji są już uwzględnione zaznaczamy wszystkie pola na dole:
  • Użyj etykiet w: Górny wiersz i Lewa kolumna sprawi, że dane po konsolidacji będą podpisane naszymi nagłówkami (oczywiście można nie zaznaczać tej opcji jeśli chcemy nadać inne nazwy).
  • Dzięki opcji Utwórz łącze z danymi źródłowymi możliwa będzie aktualizacja, jeśli dane źródłowe ulegną zmianie.
Naszym ostatnim ruchem jest naturalnie kliknięcie OK 🙂
 
Dane zostają zsumowane w jednym arkuszu. Jak widać mają postać listy rozwijalnej tak, że oprócz przejrzystego efektu końcowego możemy także prześledzić wyniki dla konkretnych miesięcy.
 
 
Oczywiście to było jednynie przykładowe wykorzystanie Konsolidacji – możliwe jest także konsolidowanie jedynie wybranych nazwanych zakresów oraz używanie innych funkcji (np. Średnia), ale to omówimy szczegółowo w innym odcinku.
Janusz Dreziński

Od ponad 20 lat związany z branżą IT. Pasjonat technologii Microsoft z naciskiem na pakiet Office. Od kilku lat pomaga innym prowadząc portal Office.info.pl oraz rozwiązując ich codzienne problemy dotyczące przede wszystkim programów Microsoft Excel i Word. Jego hobby to żeglarstwo oraz siatkówka, a ulubione miejsce to... Mazury.

Pokaż pozostałe moje porady