W ramach optymalizacji procesu obsługi posprzedażowej dyrekcja firmy X postanowiła swoich klientów uporządkować alfabetycznie, a następnie przydzielić ich dedykowanym opiekunom. Wszystko po to, aby jakość obsługi i zadowolenie klientów były na jak najwyższym poziomie. Teraz dyrekcja oczekuje cotygodniowych raportów, gdzie wyniki będą prezentowane między innymi pod kątem działań każdego z opiekunów.
Konsultant odpowiedzialny za opracowanie danych źródłowych zwrócił się do mnie z pytaniem: jak mogę w miarę szybko i „bezboleśnie” przypisać klientów (na podstawie pierwszej litery ich nazwy) do konkretnych opiekunów?
Jeden problem, wiele rozwiązań
Na szkoleniach i kursach bardzo często powtarzam, że zdecydowaną większość zadań w Excelu można wykonać na wiele sposobów. Oczywiście to, który zostanie użyty zależy m.in. od stopnia zaawansowania użytkownika, jak skomplikowany jest problem oraz ile czasu chcemy zaoszczędzić. Zawsze myślę o optymalizacji procesu, aby pracę wykonać raz a porządnie, a później tylko czerpać korzyści z dobrze opracowanego rozwiązania.
Również w prezentowanym przykładzie mógłbym pokusić się o przedstawienie różnych wariantów, ale postanowiłem, że pokażę Ci jak w prosty i szybki sposób możesz przypisać opiekunów za pomocą Power Query.
Jeśli chcesz poznać MEGA TRIKI w Power Query, to pobierz darmowy przewodnik.
Rozwiąż to z Power Query
Dlaczego akurat stawiam na to narzędzie? Bo jest funkcjonalne, łatwe w nauce i późniejszym używaniu, no i oszczędza czas. Pobierasz dane źródłowe z systemu transakcyjnego, wykonujesz szereg przekształceń (oczywiście tylko te, które są niezbędne), a efekt końcowy przedstawiasz w postaci raportu – zwykłej tabeli lub tabeli przestawnej.
A jak będę mieć nowe dane źródłowe? Nic prostszego. Zapisujesz je w ustalonym miejscu, a potem odświeżasz raport i gotowe! Brzmi cudownie i takie jest. 😉 Zresztą zobacz sam.
Power Query – pomysł na rozwiązanie
Wspomniany wcześniej konsultant przekazał informację, że firma zatrudnia czterech opiekunów i każdy z nich ma przypisaną grupę klientów, których nazwy zaczynają się odpowiednimi literami. I tak: Szczepański Piotr obsługuje klientów A-F, Nowicka Ewa obsługuje klientów G-M, Grabarz Jan obsługuje klientów N-T, a Witek Iwona obsługuje klientów U-Z.
W prezentowanym przykładzie wszystkie dane transakcyjne są już umieszczone w arkuszu, więc dane pobierzesz z zakresu/tabeli.
Skoro opiekunów będziesz przypisywać na podstawie pierwszej litery nazwy klienta, to należy ją wyodrębnić. W ten sposób otrzymasz kolumnę z pojedynczymi literami. Nie będziesz jednak rozpisywać zbyt długiej formuły, żeby opisać każdą literę alfabetu. Tym razem do rozwiązania użyjesz tzw. kodu znaku, czyli numery pod którym kryje się każda litera alfabetu.
Być może wiesz, a być może nie, ale każdy znak wprowadzany z klawiatury ma swój kod. Dla przykładu duża litera A oznaczona jest kodem 65, a duża litera Z to kod 90.
Zasadniczo możesz poruszać się w zakresie od 65 do 90. Skoro Szczepański Piotr obsługuje klientów A-F, to dla niego sprawdzisz zakres kodów od 65 do 70. W tym miejscu zmuszony jestem zauważyć, że polskie znaki jak np. litera Ł, Ś czy Ż również mają swoje kody, ale te wykraczają wspomniany wcześniej zakres. Nie stanowi to jednak przeszkody w rozwiązaniu tego zadania, o czym przekonasz się już za chwilę.
Kiedy już poznasz kody pierwszej litery wyodrębnionej z nazwy klienta, to za pomocą funkcji logicznej IF będziesz mógł przypisać właściwego opiekuna. A później pozostanie Ci już tylko załadowanie gotowego rozwiązania do arkusza.
To tyle tytułem wstępu. Zaczynamy!
Power Query – działaj krok po kroku
KROK 1: Najpierw pobierzesz dane źródłowe, które w tym przypadku są umieszczone w arkuszu bieżącego skoroszytu. Zaznacz ten zakres, a następnie na karcie Dane, w grupie Pobieranie i przekształcanie danych kliknij polecenie Z tabeli/zakresu.
Excel wymusi utworzenie tabeli. Upewnij się, że zakres obejmuje wszystkie dane źródłowe oraz, że opcja Moja tabela ma nagłówki jest zaznaczona.
Kliknij przycisk OK, aby edytor Power Query wyświetlił zaimportowane dane.
KROK 2: Zmień nazwę zapytania na Transakcje i naciśnij klawisz Enter. Jeśli nie wiesz gdzie to zrobić, to szukaj (po prawej stronie okna edytora w sekcji Ustawienia zapytania) pola Nazwa i tam wprowadź nową nazwę.
KROK 3: Zaznacz kolumnę Klient klikając w jej nagłówek. Teraz na karcie Dodaj kolumnę w grupie Z pliku tekstowego kliknij polecenie Wyodrębnij, a następnie z listy rozwijanej wybierz opcję Pierwsze znaki.
W oknie dialogowym Wstawianie pierwszych znaków w polu Liczba wprowadź wartość 1 i kliknij OK.
Na końcu zapytania powstała nowa kolumna o nazwie Pierwsze znaki zawierająca pierwsze litery z nazw klientów.
KROK 4: Może zdarzyć się, że nazwa klienta została wprowadzona małymi literami. Power Query rozróżnia je, a poza tym mała litera “a” i duża litera “A” mają odmienne kody. Dlatego na tym etapie musisz wszystkie litery zestandaryzować, np. zamieniając je na wielkie litery.
W tym celu zaznacz kolumnę Pierwsze znaki. Następnie na karcie Przekształć w grupie Kolumna tekstu kliknij polecenie Format i wybierz opcję Wielkie litery. Jeżeli w kolumnie znajdowałyby się małe litery, to teraz zostałyby zmienione na wielkie litery.
KROK 5: Jak już wiesz każdy znak posiada swój indywidualny kod. Teraz używając dedykowanej funkcji uzyskasz kody dla każdej litery, która znajduje się w kolumnie Pierwsze znaki. Przejdź na kartę Dodaj kolumnę, a potem w grupie Ogólne kliknij polecenie Kolumna niestandardowa.
W oknie dialogowym Kolumna niestandardowa w polu Nazwa nowej kolumny wprowadź tekst Kod, a w polu Formuła kolumny niestandardowej wprowadź formułę =Character.ToNumber([Pierwsze znaki]), gdzie [Pierwsze znaki] oznacza kolumnę zawierającą wyodrębnione w kroku 3 pierwsze litery nazw klientów.
Uwaga! Pamiętaj, aby w polu Formuła kolumny niestandardowej na początku znajdował się tylko jeden znak równości (=). W przeciwnym razie ten krok nie zadziała prawidłowo.
Kliknij przycisk OK.
Super! Otrzymałeś nową kolumnę o nazwie Kod, w której umieszczone są wartości liczbowe odpowiadające kodom poszczególnych liter. Poza standardowymi kodami w zakresie od 65 do 90, dla liter od A do Z, znajdziesz również kody 321 (litera Ł), 346 (litera Ś) oraz 379 (litera Ż).
KROK 6: Skoro znasz już wszystkie kody oraz z opisu zadania wiesz, która osoba jakich klientów ma obsługiwać, to możesz teraz stworzyć kolumnę prezentującą takie przyporządkowanie.
Przypominam tylko, że litera Ł będzie przypisana do Nowickiej Ewy, litera Ś do Grabarz Jana, a litera Ż znajdzie się u Witek Iwony.
Przejdź na kartę Dodaj kolumnę i w grupie Ogólne kliknij przycisk Kolumna niestandardowa.
W polu Nazwa nowej kolumny wprowadź tekst Opiekun, a w polu Formuła kolumny niestandardowej wpisz następującą formułę:
=if [Kod]>=65 and [Kod]<=70 then "Szczepański Piotr" else if ([Kod]>=71 and [Kod]<=77) or [Kod]=321 then "Nowicka Ewa" else if ([Kod]>=78 and [Kod]<=84) or [Kod]=346 then "Grabarz Jan" else if ([Kod]>=85 and [Kod]<=90) or [Kod]=379 then "Witek Iwona" else "bez opiekuna"
Na koniec kliknij przycisk OK.
Kilka słów wyjaśniających do formuły if. Otóż formuła ta bada czy wartość liczbowa umieszczona w kolumnie Kod znajduje się w przedziałach, np. między 65 a 70, stąd użyto łącznika and (oraz). Jeśli tak jest to wtedy w cudzysłowie umieszczony został tekst do wyświetlenia. Jeżeli weryfikowany kod ma znajdować się w przedziale, np. między 71 a 77 lub ma być równy 321 wtedy użyto również określenia or (lub). Na samym końcu formuły wprowadzono opcję dla znaków, których kody nie są dużymi literami alfabetu i w takim przypadku jako rozwiązanie wyświetlać będzie się tekst „bez opiekuna”.
KROK 7: Czas na porządki. Zawsze, kiedy pracujesz w Power Query pamiętaj, aby posprzątać po swojej pracy. Mam tu na myśli usuwanie wszystkich zbędnych kolumn, które były potrzebne na etapie opracowywania rozwiązania, ale teraz ich wyświetlanie w raporcie końcowym nie ma najmniejszego sensu.
Kliknij w nagłówek kolumny Opiekun klienta, wciśnij i przytrzymaj klawisz Shift, a następnie kliknij w nagłówek kolumny Kod. Po zaznaczeniu wszystkich trzech kolumn, kliknij prawym przyciskiem myszy na nagłówku jednej z tych kolumn i z menu podręcznego wybierz polecenie Usuń kolumny.
Teraz zmień nazwę kolumny z Opiekun na Opiekun klienta. W tym celu możesz albo dwukrotnie kliknąć w nagłówek kolumny, albo użyć klawisza funkcyjnego F2, a następnie wprowadzić nową nazwę Opiekun klienta i nacisnąć klawisz Enter.
KROK 8: Ostatnim etapem będzie umieszczenie raportu z przypisanymi opiekunami w nowym arkuszu. W tym celu na karcie Strona główna kliknij polecenie Zamknij i załaduj, a następnie jeszcze raz Zamknij i załaduj.
Edytor Power Query zostanie zamknięty, a w bieżącym skoroszycie Excela utworzony zostanie nowy arkusz, w którym umieszczone zostanie gotowe rozwiązanie.
Teraz po każdej zmianie danych źródłowych wystarczy odświeżyć tabelę klikając prawym przyciskiem myszy i wybierając polecenie Odśwież.
Podsumowanie
Jak widzisz na pozór trudne zadanie, można w bardzo prosty sposób rozwiązać. Oczywiście jeszcze raz przypominam, że nie jest to jedyny sposób na uzyskanie zamierzonego efektu końcowego. Jeśli chcesz, to zachęcam Cię do poszukiwania innych sposobów i dzielenia się wnioskami w komentarzach pod tym wpisem.
Mam nadzieję, że ten przykład będzie stanowił dla Ciebie inspirację do odkrywania kolejnych możliwości jakie oferuje użytkownikom genialne narzędzie, czyli Power Query.