Pracujesz w bankowości, ubezpieczeniach, księgowości, jesteś przedstawicielem handlowym albo prowadzisz działalność gospodarczą? Bez względu na wykonywaną profesję jednym z podstawowych narzędzi z których korzystasz na co dzień jest arkusz kalkulacyjny. Gromadzisz w nim dane, analizujesz je, tworzysz tabele przestawne, scenariusze albo rozbudowane wykresy chcąc uzyskać odpowiedzi na konkretne pytania. Czasami pierwszym etapem na drodze do rozwiązania zadania jest odpowiednie zsumowanie wszystkich dostępnych informacji poprzez konsolidację danych. Nie dość, że musisz to zrobić prawidłowo to dodatkową przeszkodą może być niewielka ilość czasu jaką możesz poświęcić na tę czynność. W tym artykule zaprezentuję Ci w jaki sposób właściwie skonsolidować arkusze zawierające dane a przy tym jak to zrobić szybko i bezbłędnie.
Czym jest konsolidacja?
Konsolidacja to nic innego jak podsumowanie danych zawartych w oddzielnych arkuszach i przedstawienie ich w postaci raportu umieszczonego w głównym arkuszu. Dane podlegające konsolidacji mogą znajdować się zarówno w skoroszycie zawierającym arkusz główny jak również w innych skoroszytach.
Korzystając z Excela dysponujesz trzema sposobami sumowania danych z arkuszy, którymi są:
- konsolidacja za pośrednictwem polecenia Wklejanie specjalne,
- konsolidacja wymagająca zastosowania formuł oraz
- konsolidacja z użyciem polecenia Konsoliduj.
Pierwszy z nich możliwy jest do zastosowania tylko i wyłącznie, kiedy wszystkie konsolidowane arkusze są otwarte. Niestety jego główną wadą jest fakt, iż wynik podsumowania nie podlega aktualizacji co oznacza, że w przypadku zmiany danych źródłowych raport końcowy nie pokaże nowych rezultatów.
Drugi sposób wymaga użycia formuł odwołujących się do innych arkuszy lub skoroszytów. Jeśli dane w nich zawarte ulegną zmianie to formuły zaktualizują się automatycznie. Ponadto nie ma potrzeby otwierania wszystkich skoroszytów w trakcie tworzenia formuł konsolidacyjnych, przy czym należy zwrócić szczególną uwagę, aby wszystkie sumowane arkusze źródłowe miały ten sam układ danych. W przeciwnym razie musisz wykonać dodatkową pracę, aby upewnić się, że formuły odwołują się do właściwej komórki w każdym arkuszu danych.
Trzeci sposób jest najbardziej elastyczny i pozwala pracować na arkuszach, które w pewnym stopniu mogą różnić się od siebie. Co ważne stosując polecenie Konsoliduj możesz tworzyć raporty statyczne (bez formuł) oraz dynamiczne (z formułami).
Konsolidacja krok po kroku
W celu podsumowania danych z osobnych zakresów i zebrania wyników w jednym zakresie wyjściowym wykorzystaj polecenie Konsoliduj w grupie Narzędzia danych na karcie Dane, które pozwala skonsolidować maksymalnie 255 arkuszy w jednym głównym arkuszu.
Informacje źródłowe mogą znajdować się w tym samym skoroszycie co arkusz główny lub w innych skoroszytach. Ponadto wszystkie ustawienia konsolidacji są zarejestrowane i możesz do nich wrócić w każdym momencie.
Konsolidując dane możesz wykorzystać jedną z dwóch metod, a mianowicie:
- konsolidację według pozycji – wybierz tę metodę tylko w przypadku, kiedy zakresy komórek w arkuszach mają taki sam rozmiar, a etykiety wierszy i kolumn są identycznie rozmieszczone;
- konsolidację według kategorii – wybierz tę metodę jeśli zakresy komórek w arkuszach różnią się rozmiarem, a występujące etykiety wierszy i kolumn są identyczne bez względu na ich położenie w zakresie.
Aby wytłumaczyć zasady obowiązujące przy konsolidacji obiema metodami posłużę się pewnym przykładem.
Konsolidacja według pozycji
Załóżmy, że dane dotyczące miesięcznej sprzedaży wygenerowanej przez przedstawicieli handlowych zatrudnionych w naszej firmie gromadzimy w następujących arkuszach – Q4-Październik, Q4-Listopad oraz Q4-Grudzień.
W celu porównania wyników za poszczególne kwartały roku musimy skonsolidować sprzedaż za IV kwartał. Wszystkie trzy arkusze posiadają identyczny układ oraz etykiety wierszy i kolumn.
Mając do dyspozycji otwarte wszystkie skoroszyty lub arkusze z danymi źródłowymi możemy przystąpić do konsolidacji zaczynając od zaznaczenia pustego zakresu komórek (bez etykiet wierszy i kolumn) w arkuszu podsumowującym, gdzie skonsolidowane dane zostaną umieszczone.
Korzystając z konsolidacji według pozycji nie musisz wskazywać komórek za każdym razem. Wystarczy tylko kliknąć na kartę z nazwą arkusza, ponieważ Excel zaznaczy ten sam zakres komórek określony w pierwszym odwołaniu.
Możesz dodawać arkusze do konsolidacji z innych skoroszytów, lecz jeśli te nie są otwarte to będziesz musiał ręcznie dopisać nazwę właściwego arkusza oraz podać zakres komórek.
Konsolidując według pozycji należy:
KROK 1: Otworzyć skoroszyt lub skoroszyty do skonsolidowania.
KROK 2: W arkuszu podsumowującym, zaznaczyć zakres, który będzie zawierał podsumowane dane. Pamiętaj, że ten obszar oraz wszystkie zakresy źródłowe muszą mieć ten sam rozmiar oraz identyczne etykiety wierszy i kolumn.
KROK 3: Na karcie Dane, w grupie Narzędzia danych, kliknij polecenie Konsoliduj.
KROK 4: Wybierz funkcję, której chcesz użyć a następnie kliknij pole Odwołanie.
KROK 5: Teraz aktywuj pierwszy arkusz, który chcesz dodać i zaznacz dane do skonsolidowania a następnie kliknij przycisk Dodaj. Powtórz tę czynność dla każdego arkusza, który ma być brany pod uwagę przy konsolidacji.
KROK 6: Zaznacz opcje dla etykiet jeśli wymaga tego sytuacja.
KROK 7: Zaznacz opcję Utwórz łącze z danymi źródłowymi jeśli chcesz utworzyć raport dynamiczny, który samoczynnie zaktualizuje się w przypadku zmiany danych źródłowych. Kliknięcie tej opcji spowoduje wstawienie do arkusza głównego formuł odwołujących się do arkuszy źródłowych.
KROK 8: Kliknij przycisk OK i gotowe!
Konsolidacja według kategorii
Załóżmy teraz, że dane dotyczące miesięcznej sprzedaży wygenerowanej przez przedstawicieli handlowych zatrudnionych w naszej firmie gromadzimy w następujących arkuszach – Q4-Październik, Q4-Listopad oraz Q4-Grudzień, przy czym każdy z nich zawiera inną ilość wierszy i/lub kolumn.
Tym razem w arkuszu podsumowującym zaznacz komórkę w lewym górnym rogu zakresu wyniku. Konsolidacja wykorzysta etykiety z każdego arkusza źródłowego by właściwie zorganizować dane wynikowe. Jeśli wiersz lub kolumna zgadzają się, to Excel dodaje wartości do siebie, jeśli nie, Excel dodaje kolejny wiersz lub kolumnę w skonsolidowanym raporcie.
Konsolidacja według kategorii umożliwia podsumowywanie zakresów danych o różnych rozmiarach. Stąd rzeczą niezmiernie istotną jest ręczne zaznaczenie każdego z nich, aby nie pominąć żadnej istotnej informacji umieszczonej w arkuszach. Pamiętaj również o zaznaczeniu opcji dla etykiet – dzięki temu Excel poprawnie przyporządkuje dane do właściwych etykiet wierszy i kolumn.
Konsolidując według kategorii należy:
KROK 1: Otworzyć skoroszyt lub skoroszyty do skonsolidowania.
KROK 2: W arkuszu podsumowującym, zaznaczyć komórkę w lewym górnym rogu zakresu, który będzie zawierał podsumowane dane.
KROK 3: Na karcie Dane, w grupie Narzędzia danych, kliknij polecenie Konsoliduj.
KROK 4: Wybierz funkcję, której chcesz użyć a następnie kliknij pole Odwołanie.
KROK 5: Aktywuj pierwszy arkusz, który chcesz dodać i zaznacz dane do skonsolidowania a następnie kliknij przycisk Dodaj. Powtórz tę czynność dla każdego arkusza, który ma być brany pod uwagę przy konsolidacji. Pamiętaj o każdorazowym zaznaczeniu pełnego zakresu danych.
KROK 6: Zaznacz opcje dla etykiet jeśli wymaga tego sytuacja.
KROK 7: Zaznacz opcję Utwórz łącze z danymi źródłowymi jeśli chcesz utworzyć raport dynamiczny, który samoczynnie zaktualizuje się w przypadku zmiany danych źródłowych. Kliknięcie tej opcji spowoduje wstawienie do arkusza głównego formuł odwołujących się do arkuszy źródłowych.
KROK 8: Kliknij przycisk OK i gotowe!
Konsolidowanie arkuszy wywołuje u niektórych gęsią skórkę i przerażenie. Jak widać niepotrzebnie, ponieważ proces podsumowania zgromadzonych danych jest prosty w obsłudze i nie wymaga od użytkownika zaawansowanej wiedzy. Oczywiście jeśli do tej pory nie korzystałeś z tej funkcji to warto przećwiczyć ją kilka razy korzystając z różnych scenariuszy. Aby Ci to ułatwić poniżej zamieszczam dwa skoroszyty Excela, w których znajdziesz przykładowe dane i na nich będziesz mógł potrenować konsolidację.
Pobierz plik: Konsolidacja według pozycji
Pobierz plik: Konsolidacja według kategorii
I na koniec mam do Ciebie serdeczną prośbę. Jeśli uważasz ten artykuł za wartościowy to proszę podziel się informacją o nim ze swoimi znajomymi, polub na Facebooku, ćwierknij na Twitterze lub skomentuj poniżej.
Dziękuję!