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 2013: ExcelSzkolenie.pl Cwiczenia Excel 2013.zip

Konsoliduj to jedna z funkcjonalności Excela 2013, którą osobiście uważam za mało użyteczną.

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

Ta lekcja może być obejrzana lub przeczytana poniżej.

Film wygląda najlepiej jeśli będzie odtwarzany w rozdzielczości 720p HD, rozdzielczość można zmienić dopiero po uruchomieniu filmu klikając na ikonie trybika która pojawi się w prawym dolnym rogu poniższego ekranu. Po kilku sekundach od zmiany obraz wyostrzy się.

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.

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 na poniższym rysunku, po czym klikamy ‘Malarza formatów’ i przechodzimy do arkusza z danymi zsumowanymi.

Następnie klikamy w komórkę A1 przenosząc całe formatowanie do tego arkusza.

Opisy tabel z komórek B3, B11, B19 oraz nagłówki wierszy i kolumn dla średniej ceny należy przekopiować z tabel dla krajów.

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 ze średnich dla krajów.

Mając dane sprzedaży w tys. sztuk i tys. 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 opisane w lekcji ‘Operacje na wielu arkuszach’.

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