Strona główna komputer Jak łatwo skonsolidować dane w Excelu?

Jak łatwo skonsolidować dane w Excelu?

Przez

25 kwietnia 2019

2124

Arkusz kalkulacyjny to jedno z podstawowych narzędzi pracy w każdej firmie, które wykorzystujemy do gromadzenia i analizowania różnych danych liczbowych i tekstowych. Czasami skoroszyty składają się z wielu arkuszy zawierających bardziej lub mniej rozbudowane zestawienia. Jakkolwiek może wówczas zajść potrzeba zsumowania tych danych w postaci jednego „raportu”. W tym celu wykorzystamy wbudowaną w Excelu funkcję konsolidacji. Gdzie znajduje się w programie? Jak działa? O tym w dzisiejszym artykule. Postępuj zgodnie z krótką instrukcją krok po kroku. Zaczynamy!

Zrzuty ekranu w tym artykule zostały wykonane w programie Excel dla Office 365. Jeżeli masz inną wersję programu, wygląd może się nieco różnić ale nie martw się funkcje pozostają takie same.

Otwieramy arkusz kalkulacyjny z danymi, które chcemy skonsolidować. Dysponujemy zestawieniami przedstawiającymi zarobki, premie oraz bony pracowników firmy X w pierwszym kwartale 2019 roku (styczeń, luty, marzec). Listy z danymi nie muszą być tych samych rozmiarów. Zwróć uwagę, że w naszym przypadku zestawienia w poszczególnych miesiącach różnią się ilością kolumn i wierszy.
Umieszczamy kursor w dowolnej komórce arkusza, w których chcemy wykonać konsolidację. U nas jest to arkusz zatytułowany „RAZEM”. Następnie w górnym pasku nawigacji przechodzimy do zakładki „Dane” i w sekcji „Narzędzia danych” klikamy na opcję „Konsoliduj”.
Pojawi się nowe, małe okno dialogowe „Konsolidowanie”. Z listy wybieramy funkcję, której chcemy użyć na naszych skonsolidowanych danych. My użyjemy „SUMY”, która jest najczęściej wykorzystywana w pracy.
Ustawiamy kursor myszy komputerowej w polu „Odwołanie” i zaznaczamy po kolei zakresy danych, które będą tworzyć zbiorcze zestawienie. W tym celu przechodzimy do pierwszego arkusza „styczeń”, zaznaczamy całą listę i zatwierdzamy przyciskiem „Dodaj”. Następnie klikamy na kolejny arkusz „luty” i powtarzamy czynność. Tak samo postępujemy z zakładką „marzec”.
Każda z tych naszych list posiada nagłówki kolumn (pracownik, zarobki, premia, bon) i wierszy (poszczególni pracownicy) dlatego też musimy pamiętać o etykietach i zaznaczyć opcje „górny wiersz” i „lewa kolumna”. Zatwierdzamy przyciskiem „OK”.
Pojawiła się nam skonsolidowana lista. Puste miejsce w zestawieniu oznacza, że dla pracownika 6 nie odnotowano pozycji „BON” w przeciągu 3 miesięcy.
Niestety jest jeden minus konsolidowania danych w taki sposób. A mianowicie dane nie uaktualniają się w momencie naniesienia zmiany w arkuszu źródłowym. Spójrz, pracownik 1 zarobił łącznie w I kwartale 6990 zł. Jeśli w styczniu dodamy do jego zarobków jeszcze 1000 zł to ogólnie wartość powinna wzrosnąć do 7990 zł ale niestety pozostaje bez zmian.
Aby odświeżyć skonsolidowane zestawienie, wystarczy stanąć kursorem myszy w dowolnej komórce i kliknąć na opcję „konsoliduj” (w górnym pasku nawigacji). Jeśli nie zmieniły się zakresy danych z arkuszy, po otwarciu okienka dialogowego wciskamy przycisk „OK” i gotowe. Widzimy teraz, że wartość uaktualniła się i zarobki pierwszego pracownika wzrosły o 1000 zł.
Jest jeszcze jedna bardzo ciekawa według nas opcja. Można wykasować dane i utworzyć po raz kolejny listę lub też pracować na wcześniej utworzonym, skonsolidowanym zestawieniu. W górnym pasku nawigacji przechodzimy do zakładki „Dane” i w sekcji „Narzędzia danych” klikamy na opcję „Konsoliduj”. Po otwarciu okna dialogowego zaznaczamy opcję „utwórz łącze z danymi źródłowymi”.
Mamy takie samo zestawienie z tymi samymi wynikami ale przed nagłówkami wierszy pojawiły się przyciski „+” pokazujące nam grupowanie. Po naciśnięciu takiego plusa możemy zobaczyć na jakie pozycje składa się konkretna wartość. Dla przykładu na wynagrodzenie dla Pracownika 1 składają się pensje zaciągnięte z kolejno trzech arkuszy.
W tej sytuacji gdybyśmy chcieli zwiększyć wartość wynagrodzenia dla Pracownika 1 o 1000 zł to od razu w tej skonsolidowanej liście pojawi się prawidłowa wartość.