Konsolidacja danych: jak sprytnie połączyć różniące się raporty?

Z lekcji dowiesz się:

• Jak łączyć raporty z różną ilością etykiet, kolumn i wierszy?

• Przy jakich zadaniach używać konsolidacji?

• Co zrobić z różnymi nagłówkami podsumowań?

Naszym zadaniem jest połączenie kilku różniących się raportów. Ponieważ podsumowania sprzedaży w przedstawionych przykładach różnią się ilością etykiet wierszy i kolumn nie możemy użyć odwołań 3D, ale na szczęście możemy skorzystać z Konsolidacji danych.

Konsolidacja danych połączy dane i wyciągnie odpowiednie obliczenia. Przykładowe dane do tego zadania przedstawione zostały na rys. 1.

Rys. 1. Przykładowe dane

Najpierw zajmiemy się łączeniem danych z jednego pliku, z różnych arkuszy. Jak widać na rysunku powyżej mamy dane umieszczone w trzech arkuszach (Polska, Czechy i Słowacja). Najlepiej zacząć konsolidowanie danych w docelowym arkuszu, więc wykonamy zadanie w arkuszu Razem. Wybieramy ikonkę polecenia Konsoliduj z karty Dane (rys. 2).

Rys. 2. Polecenie Konsoliduj

Otworzy się okno Konsolidowania, gdzie w pierwszym etapie wybieramy z listy rozwijanej funkcję, na zasadzie której, chcemy zrobić podsumowanie (punkt 1 na rys. 3 - pole Funkcja).

W tym przykładzie wybierzemy Sumę. W kolejnym kroku, w polu Odwołanie wybieramy zakresy (punkt 2). Opcja Przeglądaj jest mało użyteczna, prościej jest otworzyć sobie konkretny plik i zaznaczać interesujące dane. Żeby dodać do opcji konsolidacji kolejne dane, najpierw klikamy na nazwę arkusza Polska, a następnie zaznaczamy zakres, najlepiej szerszy od tego wypełnionego danymi.

Po zaznaczeniu zakresu, klikamy przycisk Dodaj (punkt 3). Zakres zostanie dodany do obszaru wszystkich odwołań, które będą wykorzystywane przy konsolidacji.

Rys. 3. Okno Konsolidowania (zaznaczanie zakresów)

Następnie przechodzimy na następny arkusz (Czechy), gdzie automatycznie powinien być zapamiętany zakres, który zaznaczyliśmy w arkuszu Polska, ale powinna zmienić się nazwa arkusza:
Polska!$A$1:$G$14 -> Czechy!$A$1:$G$14

Ponownie klikamy przycisk Dodaj. Analogicznie postępujemy dla trzeciego arkusza (Słowacja), podany zakres zatwierdzamy przyciskiem Dodaj. Dodane zakresy są wyświetlone w polu Wszystkie odwołania (punkt 1 na rys. 4).

Istotne jest, że chcemy użyć górnego wiersza i lewej kolumny jako etykiet, więc musimy zaznaczyć odpowiednie checkboxy Górny wiersz i Lewa kolumna. Ponadto jeszcze nie chcemy tworzyć łącza z naszymi danymi źródłowymi, więc nie zaznaczamy checkbox Utwórz łącze z danymi źródłowymi (punkt 2). Tak ustawione parametry konsolidowania zatwierdzamy przyciskiem OK.

Rys. 4. Parametry konsolidowania danych

Ponadto, gdybyśmy przestali potrzebować któregoś z tych zakresów, wystarczy go zaznaczyć i skasować za pomocą przycisku Usuń.

Otrzymamy skonsolidowane dane przedstawione na rys. 5.

Rys. 5. Skonsolidowane dane

W powyższych danych Excel zebrał wszystkie nagłówki z lewej kolumny i pierwszego wiersza. Sytuacje, w których dane były puste, takie pozostały, np. nigdy nie sprzedawaliśmy żelków w styczniu, czy chrupek w kwietniu. Powyższe dane są podsumowaniem, czyli konkretna komórką jest sumą wszystkich wartości z komórek spełniających dane warunki.

Istotne jest, że funkcja Konsoliduj pamięta zakresy. Jeśli ustawimy aktywną komórkę w dowolnym miejscu i klikniemy ikonkę polecenia Konsoliduj to, gdy otworzy się okno Konsolidowania, będziemy mieć zapamiętane odpowiednie zakresy.

W oknie w polu Funkcja wybieramy Średnia, a następnie wybór ten zatwierdzamy przyciskiem OK (rys. 6).

Rys. 6. W ybór funkcji Średnia w oknie konsolidowania

Otrzymamy dane ze średnią z wyników sprzedaży przedstawione na rys. 7.

Rys. 7. Konsolidacja z funkcją Średnia

W komórce A1 wpisujemy, że dane zawierają Sumę, a w komórce H1 – Średnią (rys. 8). Konsolidacja nie jest w stanie skopiować formatowania komórek, więc musimy je odpowiednio sformatować.

Rys. 8. Połączone dane (suma i średnia)

Dane uzyskane za pomocą polecenia Konsoliduj, są obliczone w danym momencie. Jeśli zmienimy je w arkuszu źródłowym, skonsolidowane dane się nie zmienią. Dzieje się tak dlatego, że odznaczyliśmy checkbox przy poleceniu Utwórz łącze z danymi źródłowymi.

Konsolidacja z trzech arkuszy i pliku

W tym przykładzie potrzebujemy skonsolidować dane z tych trzech arkuszy i dodatkowo z innego pliku. Ponadto utworzymy łącze z danymi źródłowymi. Ustawiamy aktywną komórkę w miejscu, gdzie chcemy otrzymać skonsolidowane dane (komórka A13) i wybieramy ikonkę polecenia Konsoliduj z karty Dane.

Otworzy się okno Konsolidowania, gdzie są już zaznaczone poprzednie 3 arkusze. Otwieramy dodatkowy plik, z którego chcemy dodać dane i zaznaczamy zakres (może być większy od wypełnionych danych, bo puste komórki nie wpływają na wyniki).

Zaznaczony zakres zatwierdzamy przyciskiem Dodaj (punkt 1 na rys. 9). W polu Funkcja wybieramy Sumę (punkt 2) i zaznaczamy checkbox przy Utwórz łącze z danymi źródłowymi (punkt 3). Tak ustawione parametry konsolidacji zatwierdzamy przyciskiem OK.

Rys. 9. Konsolidowanie danych (z różnych arkuszy i plików)

Otrzymamy skonsolidowane dane przedstawione na rys. 10.

Rys. 10. Skonsolidowane dane z różnych plików

Dane zostały zgrupowane, np. dla batoników po rozwinięciu ikony + widnieje informacja, że pochodzą tylko z pliku Pierwsza Konsolidacja (rys. 11).

Rys. 11. Dane z jednego pliku (Pierwsza konsolidacja)

Po rozwinięciu danych dla chrupek, widzimy, że dane pochodzą z dwóch plików, zarówno z pliku Druga Konsolidacja jak i Pierwsza Konsolidacja. Niestety nie mamy informacji, z którego arkusza pochodzą dane (dodaje się tylko nazwa pliku) – rys. 12.

Rys. 12. Dane z dwóch plików (pierwsza i druga konsolidacja)

Nazwa arkusza jest zapisana w formułach dla poszczególnych wyników, funkcja Konsoliduj stworzyła odwołania do konkretnych arkuszy/plików, co widać na rys. 13.

Rys. 13. Odwołania do konkretnych arkuszy w formułach

Dzięki utworzeniu łącza z danymi źródłowymi, jeśli zmienimy dane w danych źródłowych np. w arkuszu Czechy w komórce B2, to skonsolidowane dane również ulegną zmianie (rys. 14).

Rys. 14. Zmienione dane (zmiana w danych źródłowych)

Podsumowując, konsolidacja pomaga nam w łączeniu raportów i/lub podsumowań różnych danych z kilku arkuszy, a nawet plików. Dane mogą się różnić szczegółami w nagłówkach wierszy i kolumn, ponieważ konsolidacja łączy ze sobą te same nagłówki (gdy się powtarzają). Jednak te same nagłówki nie muszą występować w każdym zakresie danych uwzględnianych podczas konsolidacji.

Przeczytaj także:

Jak radzić sobie z błędami w Power Query?

Jak wyznaczyć wysokość premii w zależności od obrotu?

Adam Kopeć

ekspert arkusza Excel