Konsoliduj

Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: Konsoliduj.xlsx tylko ich samodzielne przerobienie daje gwarancję zapamiętania tej lekcji

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

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

Poniżej dane ze sprzedażą w tysiącach dolarów oraz sztuk a także średnia cena w USD dla lat 2004-2009.

Naszym zadaniem będzie konsolidacja tych 3 arkuszy z danymi tak aby uzyskać takie same tabele dla sumy tych trzech krajów w arkuszu ‘Konsoliduj’.

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

Z karty ‘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:’, umożliwi to nam zmianę arkusza.

Przechodzimy do arkusza ‘PL’ 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 czerwonym prostokątem, 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ąć wszystkie 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.

Na poniższym rysunku pokazuje wszystkie odwołania.

Po kliknięciu OK uzyskujemy zsumowaną sprzedaż w sztukach w arkuszu ‘Konsoliduj’.

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. Metoda ta ma tą przewagę, że w przypadku zmiany danych w arkuszach PL, RU lub UA dane dla sumy są uaktualniane a polecenie konsoliduj wkleja wartości.

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