Czyli must have każdego analityka sprzedaży

Ostatnio na blogu pojawił się pierwszy wpis o Power Query, w którym pokazywałam jak wybrać z listy klientów, którzy mają przypisane różne numery ID. Dziś drugi wpis o tym narzędziu, a z pewnością będzie pojawiało się ich więcej, ponieważ PQ jest przyszłością Excela. W wielu sytuacjach może zastąpić pisanie makr, a jest od nich zdecydowanie łatwiejsze i, aby go używać, nie trzeba mieć nie wiadomo jakich umiejętności. Wręcz powiedziałabym, że mnóstwo rzeczy da się „wyklikać” z menu. Jeden z takich przykładów prezentuję w dzisiejszym wpisie. A ponieważ zdaję sobie sprawę, że dla wielu z Was PQ jest to całkowicie nowym narzędziem – prezentowany case opisałam bardzo szczegółowo.

Mamy do dyspozycji dwa zakresy: Faktury i Regiony. Oba przedstawione na obrazku poniżej (Faktury mają oczywiście więcej danych):

Dane

Odpowiedź na pytanie Jaki jest średni obrót na klienta w regionie, wymaga zestawienia ze sobą tych dwóch tabel. Czyli chcemy „jakoś” uzyskać obrót per region i wiedzieć ile jest w tym regionie klientów. Jak już to będziemy mieli, to zostanie prosta matematyka: obrót regionu przez ilość jego klientów i z głowy. Podobny case już omawiałam (w tym filmie) i użyłam do tego tabel przestawnych, da się to zrobić inaczej tabelami (to tym jeszcze będę pisała) i da się też formułami. W dzisiejszym wpisie natomiast, pokażę Wam jak to zrobić za pomocą Power Query.

Aby zacząć pracę z Power Query, najpierw trzeba te zakresy przerobić na tabele (tabele w rozumieniu narzędzia Tabela). Aby to zrobić, wystarczy ustawić się w zakresie, z którego tabelę chcemy stworzyć i użyć skrótu klawiszowego Ctrl + t. Pojawi się taka tabliczka:

Tworzenie tabeli – okienko

Excel powinien wykryć, że dane mają nagłówki. Jeśli tego nie zrobi – trzeba to zaznaczyć ręcznie. Czynność tę trzeba powtórzyć dla każdej tworzonej tabeli.

Ja jeszcze lubię zmieniać nazwy utworzonych tabel na jakieś sensowne (Excel z kolei lubi Tabela1, Tabela2,… :)). Aby to zrobić, wystarczy ustawić się w dowolnej komórce interesującej nas tabeli i w menu Narzędzia tabel/ Projektowanie w sekcji Właściwości zmienić nazwę:

Zmiana nazwy tabeli

Teraz będziemy tabele wrzucać do Power Query…

Import do PQ: Faktury

Niezależnie od wersji Excela, najpierw należy się ustawić w tabeli (Faktury), którą chcemy wrzucić do Power Query. Dalej, jeśli masz Excela 2010 lub 2013 (ja mam 2013), to idź do menu Power Query (nie masz? Tutaj znajdziesz instrukcję jak zainstalować Power Query) i tam wciśnij przycisk From Table/Range (mam polską wersję, ale ten przycisk usilnie jest po angielsku :)). Jeśli natomiast masz 2016 – przycisk ten znajdziesz na karcie Dane.

Od razu po tej operacji Excel otworzy edytor zapytań Power Query, więc jesteśmy w domu. Zapytanie zostanie nazwane tak, jak importowana tabela (Faktury) i od razu PQ doda 2 kroki w sekcji Zastosowane kroki:

Po wrzuceniu danych…

I teraz przypominam, że naszym celem jest ustalenie średniego obrotu na klienta. Nie interesuje nas z jakich faktur ten obrót się składa. Faktury więc nie będą nam tutaj potrzebne. Chcemy więc się ich pozbyć, jednak przy okazji sumując obrót na klienta. W tym celu zastosujemy grupowanie danych według klienta.

Aby to zrobić – zaznacz nagłówek kolumny Klient i wybierz z menu Przekształć/ Grupowanie według. Wybierz i wpis następujące opcje:

Faktury: grupowanie klientów

Po naciśnięciu OK otrzymamy taki efekt:

Faktury: obroty klientów

I z fakturami to na razie tyle. Teraz tylko trzeba zapisać i załadować. Polecam zapisać tylko połączenie, a nie wrzucać ponownie tych danych do arkusza – nie jest nam to potrzebne. Aby to zrobić wejdź w edytorze PQ do menu Narzędzia główne/ Zamknij i załaduj/ Zamknij i załaduj do… (trzeba rozwinąć strzałkę pod przyciskiem) i wybierz: Utwórz tylko połączenie.

PS. Strasznie dużo screenów w tym wpisie 🙂

Import do PQ: Regiony i scalanie zapytań

To zabieramy się za regiony. Najpierw to samo, czyli wrzucenie do PQ (From Table/Range). Tutaj dane są ładnie oczyszczone, więc teraz zajmiemy się łączeniem dwóch zapytań (tabelek): Faktur i Regionów, gdyż chcemy wiedzieć ile klientów było w danym regionie i jaką miał on sprzedaż. Na razie wiemy jaką sprzedaż mieli klienci.

Teraz do każdego klienta w regionie (zapytanie Regiony) przyporządkujemy jego obrót z zapytania Faktury (coś jak WYSZUKAJ.PIONOWO w Excelu. Żeby tego dokonać skorzystamy z przycisku Scal zapytania na karcie Narzędzia główne w sekcji Połącz:

Scalanie zapytań

W tym okienku ważne jest, aby kliknąć na kolumny, według których ma nastąpić połączenie. W naszym przykładzie są to kolumny Klient. Wtedy PQ będzie wiedziało, że po nich ma dopasowywać.

Jako rodzaj sprzężenia zostawmy domyślną opcję, czyli Lewe zewnętrzne. Otrzymamy taki efekt:

Po scaleniu

Dwie pierwsze kolumny pochodzą z zapytania Regiony, natomiast trzecia – Nowa kolumna – jest wynikiem połączenia i zawiera ona w sobie tabele, w których znajdują się pasujące rekordy z zapytania Faktury. Tak, w PQ możemy w jedną „komórkę” wpakować całą tabelę. W Excelu co najwyżej jedną wartość. Natomiast my chcemy wyodrębnić z tych tabel wartości. Będzie to już prościutkie, ponieważ mamy tylko jeden rekord w każdej z nich, gdyż wcześniej dokonaliśmy grupowania i pozbyliśmy się nadmiarowych rekordów. Aby to zrobić, należy kliknąć na przycisk ze strzałkami obok nagłówka Nowa kolumna (zaznaczony na żółto na obrazkach). Polecam tylko odznaczyć kolumnę Klient (nic nam nie wniesie, a tylko zdubluje dane) i opcję Użyj oryginalnej nazwy kolumny jako prefiksu:

Rozwijanie

W wyniku dołączymy kolumnę Obrót do klienta i regionu:

Doklejenie obrotu

Widać z obrazka powyżej, że nie potrzebujemy tutaj klientów, a jedynie ich ilość w regionie. Znowu zatem dokonamy grupowania, tym razem według regionu i jednocześnie policzymy klientów i zsumujemy obroty w regionach. Ustawmy się więc na kolumnie Region i wybierzmy Przekształć/ Grupuj według:

Ostatnie grupowanie

I w wyniku tego grupowania dostaniemy prawie to, co chcieliśmy na początku.

Wynik grupowania

Teraz tylko dorzućmy kolumnę, o którą pytaliśmy na początku, czyli średni obrót na klienta. Aby to zrobić, ustawmy się w kolumnie Obrót (tę kolumnę chcemy dzielić) i skorzystajmy z menu Dodaj kolumnę, przycisk Standardowe, opcja Podziel.

Nowa kolumna – dzielenie

Pojawi się następująca tabliczka, w której należy zaznaczyć, że chcemy dzielić przez wartość z innej kolumny:

Dzielenie

Wynik otrzymamy taki, jak na obrazku poniżej. Oczywiście wynik jeszcze nie ostateczny 🙂

Wynik dzielenia

Wypada jeszcze zmienić nazwę kolumny i oczywiście posortować dane. Nazwę kolumny polecam zmienić w pasku formuły (nie ma potrzeby dodawania kolejnego kroku). Po prostu zmieńmy Wstawiono dzielenie na np. Średni obrót. I po tym średnim obrocie teraz posortujmy, czyli przycisk sortowania malejącego na karcie Narzędzia główne (po uprzednim zaznaczeniu kolumny Obrót).

Wynik teraz możemy załadować do Excela, np. do nowego arkusza (czyli Narzędzia główne/ Zamknij i załaduj). Po sformatowaniu danych wynik mamy następujący:

Wynik

I voila! O to chodziło.

Na koniec możecie zapytać, czemu akurat Power Query, skoro nawet prostymi formułami da się powiedzieć jaki był średni obrót na klienta w regionie. Moja odpowiedź jest taka, że oczywiście, że się da, natomiast dzięki PQ dostajemy tutaj niesamowicie elastyczny mechanizm. Wystarczy, że do naszych tabel dojdą nowe faktury i klienci (a z pewnością tak się stanie!), zapytania PQ wystarczy odświeżyć i wynik uaktualni się sam. Dodam, że Power Query może te dane czerpać z bazy danych i nawet nie ma konieczności umieszczania w arkuszach danych źródłowych (Faktury i Regiony), co dla formuł jest koniecznością. Oczywiście nie twierdzę, że formuły są be – sama je kocham! Natomiast chodzi mi o przyszłościowe zastosowanie. Uważam, że akurat do tego formuły po prostu są bardziej uciążliwe. Coś jak formuły vs tabele przestawne 🙂

Wow, to chyba wpis z największą liczbą screenów na moim blogu i przy okazji chyba najdłuższy! Przyznam, że sporo pracy mnie to kosztowało. Mam nadzieję, że Wam się podoba chociaż!

A na koniec tradycyjnie plik do pobrania:

Powiązane produkty

• Power Query – wstęp dla laików – Celem tej lekcji jest wprowadzenie w magiczny świat Power Query wszystkich tych, którzy jeszcze nie mieli do czynienia z tym cudownym narzędziem. Coś tam kiedyś słyszeli, coś widzieli, ale jeszcze nic samodzielnie nie robili.

I na koniec powiązany film, o którym wspominałam na początku, czyli rozwiązanie z tabelami przestawnymi podobnego problemu (dane inaczej wyglądają):