Konsoliduj

Przykład opisany w tej lekcji dostępny jest w arkuszu Excela: Konsoliduj.xls, tylko ich samodzielne przerobienie daje gwarancję zapamiętania tej lekcji.

Przykłady dla wszystkich lekcji szkolenia Excel 2003: ExcelSzkolenie.pl Cwiczenia Excel 2003.zip

Przykład 1.

(Arkusze: ‘Konsoliduj’, ‘37a’, ‘37b’, ‘37c’)

Firma sprzedaje swoje produkty w pięciu kategoriach na 3 rynkach, w Polsce, Rosji i na Ukrainie.

Poniżej dane ze sprzedażą w tyś USD oraz sztukach a także średnia cena dla lat 2004-2009.

Naszym zadaniem będzie konsolidacja tych 3 arkuszy z danymi tak aby uzyskać takie tabele dla sumy PL+RU+UA.

Ustawiamy aktywną komórkę w arkuszu, w którym chcielibyśmy aby znalazło się podsumowanie.

Z Menu ‘Dane’ wybieramy polecenie ‘Konsoliduj’

Okno ‘Konsolidowanie’ umożliwia wybranie sposobu zliczania danych, oprócz sumy którą my użyjemy może to być także np. licznik czy średnia.

Po wybraniu funkcji ‘Suma’ ustawiamy kursor w okienku ‘Odwołanie:’.

Przechodzimy do pierwszego arkusza z danymi wejściowymi i zaznaczamy obszar danych który ma być konsolidowany wraz z nagłówkami zarówno wierszy, jak i kolumn.

Klikamy przycisk ‘Dodaj’..

Efektem wyżej opisanej operacji powinno być pojawienie się odwołania w okienku ‘Wszystkie odwołania:’

Zaznaczamy opcję ‘Górny wiersz’ i ‘Lewa kolumna’, aby nagłówki kolumn i wierszy zostały skopiowane do arkusza wynikowego.

Na tym ekranie dostępna jest także opcja ‘Utwórz łącze z danymi źródłowymi’, która powoduje dodanie linków do danych źródłowych i ich podsumowania w arkuszu wynikowym. Moim zdaniem nie jest to korzystne rozwiązanie ponieważ dobry przegląd danych źródłowym mamy na arkuszach poświęconych krajom, a arkusz skonsolidowany powinien służyć jedynie do przeglądu wyników podsumowanych.

Przechodzimy do kolejnego arkusza z danymi.

Odwołanie zaznaczone na zielono, zostało zmienione na aktywny arkusz. Jeżeli dane w każdym z arkuszy krajowych są w tych samych komórkach, możemy nie zmieniając odwołania kliknąć przycisk ‘Dodaj’.

Analogicznie dodajemy odwołanie do arkusza z danymi dla Ukrainy i klikamy przycisk ‘OK.’.

W arkuszu podsumowującym zostają umieszczone skonsolidowane dane sprzedaży.

Tą sama operację powtórzymy dla danych sprzedaży wyrażonych w sztukach. Zanim będziemy mogli skonsolidować te dane, musimy usunąć poprzednio używane odwołania, użyjemy przycisku ‘Usuń’.

Aby zaznaczyć nowe odwołanie należy ustawić kursor w okienku ‘Odwołanie:’

Przechodzimy do pierwszego arkusza z danymi i postępujemy dokładnie tak samo jak w powyżej opisanym przypadku.

Po zakończeniu konsolidowania wszystkich danych źródłowych uzyskujemy zsumowaną sprzedaż w sztukach.

Prostym sposobem skopiowania formatowania z arkuszy źródłowych do arkusza z danymi skonsolidowanymi będzie użycie ‘Malarza formatów’ dla całego arkusza.

Zaznaczamy cały arkusz z danymi dla Polski klikając w miejsce zaznaczone zielonym kółkiem, po czym klikamy ‘Malarza formatów’ i przechodzimy do arkusza z danymi zsumowanymi.

Klikamy w górnym lewym rogu poniższego obrazka (powyżej 1, na lewo od A) przenosząc całe formatowanie do tego arkusza.

Nie wszystkie rodzaje danych mogą być konsolidowane za pomocą tego polecenia. Ostatnia z tabel przedstawiająca średnią cenę w dolarach nie może zostać skonsolidowana przy użyciu funkcji średnia, ponieważ powinna zostać obliczona jako średnia ważona wielkością sprzedaży a nie jako zwykła średnia. Mając dane sprzedaży w tyś sztuk i tyś usd, możemy po prostu podzielić sprzedaż przez sztuki uzyskując średnią cenę.

Alternatywnym i preferowanym przeze mnie sposobem rozwiązania takiego problemu, byłoby skorzystanie z funkcji suma i zaznaczenie na raz wielu arkuszy, tak jak jest to pokazane poniżej.

Należy pamiętać że każda metoda ma swoich zwolenników.

Szkolenie Excel 2003 Szkolenie Excel 2007