Warunkowe sumowanie danych z wielu tabel w PowerQuery

Dziś w nowym wydaniu „zadanie tak stare jak sam Excel” Mamy kilka oddzielnych tabel i potrzebujemy uzyskać sumę danych z nich. Jak się zaraz okaże – potrzebujemy jedynie danych dla określonego warunku, co nie skomplikuje nam zbytnio rozwiązanie. Z PowerQuery wszystko jest prostsze.

Scenariusz:

Wyobraź sobie, że masz plik o takim układzie jak poniżej:

Każdy arkusz przechowuje dane o sprzedaży danego handlowca w danym regionie (np. GD_Nowak to sprzedaż Nowaka w Gdańsku). Dane w każdej zakładce przechowywane są w następujący sposób i mogą różnić się liczbą wierszy:

A dla ST_Zieliński (sprzedaż Zielińskiego w Sopocie):

Interesuje nas utworzenie mechanizmu, który pokaże nam sumę sprzedaży produktów z kategorii AGD ale tylko w regionie ST (Sopot). Mamy zatem dwa warunki:

1. Interesują nas jedynie arkusze, które posiadają wyróżnik ST (prefiks),

2. Interesują nas jedynie te wiersze, które w grupie produktowej posiadają wpis „AGD”.

Jak zatem warunkowo zsumować dane z wielu tabel na podstawie 2 kryteriów? Idealnym rozwiązaniem będzie PowerQuery, ponieważ łatwo pozwoli nam „dobrać się” do nazwy arkusza i potraktować ją jako kryterium.

PowerQuery jest narzędziem z grupy ETL (Extract, Transform, Load). Poniżej w 3 krokach pokazujemy czego dotyczą poszczególne kroki.

Aby wysłać wszystkie arkusze (a tym samym dane) do dodatku PowerQuery klikamy opcję „Ze skoroszytu” znajdującą się na wstążce Dane (w starszych wersjach Excela może być konieczne zainstalowanie PowerQuery, które pojawi się na oddzielnej karcie).

Wskazujemy następnie plik, z którego pobieramy dane (tak, to ten sam plik co jest otwarty)

Na ekranie pojawi się następujące okno:

Wskazujemy w nim kontener o nazwie Regiony.xlsx i przechodzimy do PowerQuery klikając Edytuj.

Na ekranie pojawi się następujące okno:

Pozostawiamy jedynie 2 pierwsze kolumny. Zaznaczamy je trzymając wciśnięty Shift a następnie wybieramy opcję „Usuń inne kolumny”. Rezultatem naszego działania będą 2 kolumny dostępne w PowerQuery:

Pamiętajmy, że interesuje nas jedynie rejon Sopot (rozpoczynający się literami ST). Dlatego też odfiltrujmy kolumnę Name korzystając z rozwijanej strzałki i wybierzmy opcję Zaczyna się od. PowerQuery jest dodatkiem bardzo wrażliwym na wielkość liter. W tym przykładzie zakładamy, że wyznacznik regionu w nazwie arkusza zawsze zaczyna wpisany jest wielkimi literami. Wpisz ST zgodnie z poniższym rysunkiem:

Po zatwierdzeniu przyciskiem OK zobaczysz na ekranie:

Dokonaliśmy zatem ograniczenia danych dotyczących regionu biorąc za kryterium fragment nazwy arkusza. Pozostaje nam teraz poproszenie PowerQuery o pokazanie danych znajdujących się w arkuszach. Robimy to za pomocą strzałki wskazanej na poniższym rysunku:

W dodatkowym oknie odznaczamy dla zachowania czytelności opcję Użyj oryginalnej nazwy kolumny jako prefiksu i potwierdzamy przyciskiem OK.

Rezultatem będzie widok scalonych z dwóch arkuszy danych:

Jest kilka elementów, którymi musimy się zaopiekować w tych danych. W pierwszej kolejności potraktujmy pierwszy wiersz z danymi jako wiersz, który zawiera nagłówki. Wystarczy, że wciśniemy przycisk Użyj pierwszego wiersza jako nagłówków na karcie Narzędzia główne

Rezultat działania powinien być następujący:

Pozostaje nam pozbycie się wiersza nagłówkowego z drugiego (i ew. kolejnych) arkuszy. Moglibyśmy odfiltrować oczywiście wpisy dot. nagłówków, jednak możemy upiec dwie pieczenie na jednym ogniu. Założyliśmy, że chcemy sumować jedynie te dane, które dotyczą grupy produktowej AGD. Wystarczy zatem, że w edytorze PowerQuery w kolumnie Grupa produktowa wybierzemy jedynie AGD.

Oczom naszym ukażą się jedynie dane zawierające wpis AGD w kolumnie Grupa produktowa. Należy pamiętać o odpowiedniej wielkości liter. Warto docelowo zabezpieczyć się przed różnymi wielkościami liter przez wcześniejsze sprowadzenie ich do jednego typu (małe lub wielkie litery).

Zabezpieczmy się jeszcze przed potencjalnie niepoprawnym przypisaniem formatów (typów danych) w PowerQuery. Klikajmy kolejno na kolumny prawym przyciskiem myszy ustalając dla:

1. Data zamówienia? Zmień typ Data
2. Wartość zamówienia? Zmień typ Liczba dziesiętna

Aby zsumować kwoty zamówień mamy 2 opcje:

1. Możemy w PowerQuery pogrupować dane wg Grupy produktowej i poprosić PowerQuery o zsumowanie wartości zamówień LUB
2. Możemy załadować dane do nowego arkusza i utworzyć na ich podstawie tabelę przestawną.

Wybierzemy drugą opcję ze względu na niewielką ilość danych oraz dalszą elastyczność tabel przestawnych. Wybieramy zatem w oknie PowerQuery opcję Zamknij i załaduj. PowerQuery przeniesie do Excela dane w następujący sposób:

W celu utworzenia żądanego raportu – utwórzmy następującą tabelę przestawną:

Udało się! Osiągnęliśmy raport na podstawie warunkowego sumowania danych z wielu tabel. Pamiętajcie, że PowerQuery (jak sama nazwa wskazuje) tworzy kwerendę, a więc mechanizm, który jest odświeżalny. Jeśli zatem w Waszym pliku pojawi się kolejny arkusz zaczynający się literami ST – PowerQuery po odświeżeniu uwzględni go w danych! Jest to magiczne działanie genialnego dodatku PowerQuery. Jeśli chcesz – obejrzyj film na youtube pokazujący jak wykonać powyższe działania krok po kroku, a jeśli chcesz dowiedzieć się więcej o PowerQuery i zautomatyzować swoje arkusze – sprawdź nasze szkolenia.