Konsolidowanie danych z wielu arkuszy w Excelu

Konsolidowanie danych w Excelu

W jednym skoroszycie mamy kilka arkuszy z danymi budżetu dla poszczególnych działów. Budżety poszczególnych działów znajdują się w osobnych arkuszach i są rozdzielone na poszczególne miesiące. Jak najprościej utworzyć zbiorczy arkusz, w którym będą zestawione wszystkie budżety?

Chcemy mieć w osobnym arkuszu podsumowanie kwot rocznych wszystkich działów. Arkusz, który ma zawierać podsumowanie, przedstawiony jest na rysunku 2.

Rys. 1. Jeden z arkuszy zawierający budżet działu

Rys. 2. Arkusz z podsumowaniem budżetów wszystkich działów

Jednym z narzędzi, które można wykorzystać do takiego podsumowania w Excelu, jest konsolidowanie danych. Aby wstawić sumaryczną wartość poszczególnych pozycji za pomocą tego narzędzia:

1. Zaznaczamy w arkuszu z podsumowanie komórki B2:B13, w których chcemy wstawić skonsolidowane dane, i na karcie Dane w grupie poleceń Narzędzia danych wybieramy polecenie Konsoliduj (w Excel 2003: wybieramy polecenie menu Dane/Konsoliduj).

2. W oknie dialogowym Konsolidowanie najpierw w polu Funkcja ustawiamy funkcję, którą chcemy użyć do agregacji danych. Ponieważ chcemy podsumować dane, a domyślnie jest ustawiona tam funkcja Suma, to przechodzimy do kolejnego kroku.

3. Klikamy w polu Odwołanie i na arkuszu zaznaczamy zakres komórek z pierwszego arkusza, który chcemy skonsolidować. W polu Odwołanie zostanie wpisane odwołanie do zaznaczonego zakresu komórek, tak jak to przedstawiono na rysunku 3.

Rys. 3. Okno Konsolidowanie po zaznaczeniu pierwszego zakresu danych do konsolidacji

Jeśli okno dialogowe Konsolidowanie przeszkadza nam w prawidłowym zaznaczeniu zakresu, to możemy je przesunąć w inne miejsce, chwytając myszą za pasek tytułu okna lub nacisnąć ikonę z czerwoną strzałką znajdującą na końcu pola Odwołanie, aby ukryć okno dialogowe i zostawić tylko pole Odwołanie, a później w ten sam sposób powrócić do okna dialogowego.

Do konsolidacji możemy użyć dane zawarte w innych skoroszytach, w tym celu trzeba nacisnąć przycisk Przeglądaj, wybrać skoroszyt, z którego chcemy pobrać dane i dopisać prawidłowe odwołanie do komórek z wybranego skoroszytu.

4. Naciskamy przycisk Dodaj, aby dodać zaznaczony zakres komórek do konsolidacji. Odwołanie do zaznaczonego zakresu komórek pojawi się na liście Wszystkie odwołania.

5. Zaznaczamy kolejny arkusz w skoroszycie. W polu odwołanie zostanie automatycznie zmienione odwołanie. Jeśli komórki w tym arkuszu znajdują się dokładnie w tym samym miejscu arkusza, to nie musimy już zmieniać tego odwołania. Naciskamy przycisk Dodaj, aby dodać ten zakres komórek do konsolidacji.

6. W ten sam sposób dodajemy kolejne odwołania do zakresów w pozostałych arkuszach zawierających kwoty budżetów dla poszczególnych działów.

Jeśli pomylimy się przy jakimś zakresie, to zawsze możemy go zaznaczyć na liście Wszystkie odwołania i nacisnąć przycisk Usuń, aby go usunąć z listy danych do konsolidacji.

Pamiętajmy, że przedstawiony powyżej sposób konsolidacji może być zastosowany tylko wtedy, gdy dane we wszystkich arkuszach mają dokładnie taki sam układ, tj. gdy poszczególne pozycje budżetu występują w takiej samej kolejności – w poszczególnych arkuszach dane mogą być w innej kolumnie czy innych wierszach.

Rys. 4. Okno Konsolidowanie po dodaniu wszystkich zakresów komórek do konsolidacji

7. Naciskamy przycisk OK, aby skonsolidować dane.

Jeśli wykorzystujemy dane z innego skoroszytu, to możemy dodatkowo w oknie dialogowym Konsolidowanie zaznaczyć opcję Utwórz łącze z danymi źródłowymi, aby podsumowania były zawsze aktualne.

W arkuszu z podsumowaniem zostały wstawione sumy kwot budżetów wszystkich działów dla poszczególnych pozycji budżetu, tak jak przedstawiono na rysunku 5.

Rys. 5. Arkusz podsumowania po wstawieniu skonsolidowanych danych