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

Z lekcji dowiesz się:

• Jak utworzyć kolumnę warunkową w Power Query?

• Przy jakich zadaniach jej używać?

• Jak wyliczyć premię dla sprzedawców?

Zadanie 1

W pierwszym zadaniu sprawdzimy, czy student zdał egzamin, czy też potrzebuje dodatkowych testów żeby zdać, czy też nie zdał w ogóle. W przykładowych danych z rys. 1 przedstawione są numery identyfikacyjne studentów oraz ilość punktów, jakie zdobyli. Mamy również rozpisane warunki: jeśli student otrzymał powyżej 60 punktów, to zdał egzamin; jeśli otrzymał ilość punktów większą od 40, a mniejszą bądź równą 60, oznacza to, że musi przejść dodatkowy test. W trzecim przypadku, czyli jeśli student otrzymał mniej niż 40 punktów, oznacza to, że nie zdał egzaminu.

Rys. 1. Dane wyjściowe

Zadanie to wykonamy w Power Query, w tym celu ustawiamy aktywną komórkę w tabeli z liczbą punktów, a następnie wybieramy polecenie Z tabeli/ zakresu z karty Dane (rys. 2).

Rys. 2. Polecenie Z tabeli/ zakresu

Otworzy się Edytor zapytań Power Query z wczytaną tabelą tOceny. Przede wszystkim będzie nas interesowała kolumna Punkty. W pierwszym kroku dodamy nową kolumnę ze statusem, czyli informacją czy egzamin został zdany czy nie. W tym celu wybieramy polecenie Kolumna warunkowa z karty Dodaj kolumnę (rys. 3).

Rys. 3. Polecenie Kolumna warunkowa

Otworzy się okno Dodawania kolumny warunkowej, gdzie w polu Nazwa nowej kolumny wpisujemy "Status" (punkt 1 na rys. 4). W kolejnym etapie musimy określić, jakie warunki ma spełnić nasza nowa kolumna. W polu Nazwa kolumny wybieramy z listy rozwijanej kolumnę Punkty (punkt 2), w polu Operator wybieramy z listy "jest większe niż" (punkt 3), następnie w polu Wartość określamy próg, czyli podajemy ilość punktów jaka gwarantuje zdanie egzaminu (60 punktów) – punkt 4.

Jeśli warunek jest spełniony to chcemy otrzymać tekst "Zdał", czyli wpisujemy go w polu Wartość wyjściowa (punkt 5). W ten sposób otrzymaliśmy pierwszy warunek. Jeśli ilość punktów nie spełni naszego warunku to chcemy otrzymać tekst "Nie zdał", który wpisujemy w polu W przeciwnym razie (punkt 6).

Rys. 4. Określenie pierwszego warunku kolumny warunkowej

Zanim zatwierdzimy dodanie kolumny musimy dołożyć kolejny warunek opisujący sytuację, kiedy student musi przejść dodatkowy test (ilość punktów między 40 a 60). W tym celu klikamy dwukrotnie na przycisk Dodaj klauzulę. Dodaliśmy dwa warunki, żeby pokazać, jak łatwo możemy zmieniać kolejność warunków lub usunąć niepotrzebny, wystarczy kliknąć w wielokropek na końcu warunku (rys. 5).

Rys. 5. Przesuwanie i usuwanie warunków

Zajmiemy się teraz rozpisaniem drugiego warunku, czyli w polu Nazwa kolumny wybieramy z listy rozwijanej kolumnę Punkty (punkt 1 na rys. 6), następnie w polu Operator wybieramy "jest większe niż" (punkt 2), a w polu Wartość wpisujemy 40 (tyle punktów musi mieć student, żeby móc zdawać dodatkowe testy) – punkt 3.

Musimy pamiętać, że rozpatrujemy tylko wartości większe od 40, nie musimy rozpatrywać drugiego progu, czyli mniejsze bądź równe 60. Wynika to z tego, że jeśli pierwszy warunek (z rysunku 4) nie został spełniony, to tylko te wartości są sprawdzane w kolejnym warunku. Jeśli warunek jest spełniony to chcemy otrzymać tekst "Dodatkowe testy", który wpisujemy w polu Wartość wyjściowa (punkt 4). Tak przygotowane warunki kolumny warunkowej zatwierdzamy przyciskiem OK.

Rys. 6. Warunki kolumny warunkowej

Otrzymamy nową kolumnę ze statusem studenta (zdał, dodatkowe testy lub nie zdał) przedstawioną na rys. 7.

Rys. 7. Kolumna warunkowa z informacją o egzaminie

Możemy w łatwy sposób modyfikować warunki kolumny warunkowej, wystarczy kliknąć na koło zębate przy nazwie kroku Dodano kolumnę warunkową w Zastosowanych krokach (rys. 8).

Rys. 8. Edycja warunków kolumny warunkowej

Po kliknięciu koła zębatego otworzy się okno Dodawania kolumny warunkowej, gdzie można edytować wpisane warunki. Takie zmiany możemy również wprowadzać w kodzie języka M w pasku formuły (rys. 9).

Rys. 9. Kod M w pasku formuły

Tak przygotowane dane możemy zaczytać do Excela, w tym celu wybieramy polecenie Zamknij i załaduj do z karty Narzędzia główne (rys. 10).

Rys. 10. Polecenie Zamknij i załaduj do

W Excelu otworzy się okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako Tabela, następnie określamy miejsce wstawienia danych – Istniejący arkusz i wskazujemy konkretną komórkę. Tak ustawione parametry Importowania danych zatwierdzamy przyciskiem OK (rys. 11).

Rys. 11. Okno Importowania danych

Otrzymamy dane zaczytane do Excela przedstawione na rys. 12.

Rys. 12. Dane zaczytane do Excela

Zadanie 2

W drugim przykładzie będziemy chcieli wyznaczyć wysokość premii dla sprzedawców w zależności od wygenerowanego obrotu. Dane do tego zadania zostały przedstawione na rys. 13.

Rys. 13. Dane do drugiego przykładu

W tym wypadku mamy bardziej skomplikowaną sytuację, ponieważ wyniki będą zależały nie tylko od progów, ale też od wygenerowanego obrotu (2% lub 2,2% premii od obrotu). Dzięki temu pokażemy dodatkowe możliwości kolumny warunkowej, ale też pewne jej ograniczenia.

W pierwszym kroku zaczytamy dane do Power Query, czyli ustawiamy aktywną komórkę w kolumnie ze sprzedawcami i wybieramy polecenie Z tabeli/ zakresu z karty Dane (jak na rys. 2). Otworzy się Edytor zapytań Power Query z wczytaną tabelą tObroty2. Wybieramy polecenie Kolumna warunkowa z karty Dodaj kolumnę (jak na rys. 3). Otworzy się okno Dodawania kolumny warunkowej, gdzie w polu Nazwa nowej kolumny wpisujemy Premia.

Jako ciekawostkę pokażemy jak zmieni się lista rozwijana Operatorów, jeśli w polu Nazwa kolumny wybierzemy kolumnę Sprzedawca zawierającą tekst (rys. 14).

Rys. 14. Operatory dla wartości tekstowych

Teraz zajmiemy się określaniem warunków, czyli w polu Nazwa kolumny wybieramy kolumnę Wygenerowany Obrót (punkt 1 na rys. 15), następnie w polu Operator wybieramy "jest większe niż" (punkt 2). W polu Wartość wpisujemy 150 000, czyli górny próg premii (punkt 3), natomiast w polu Wartość wyjściowa wpisujemy 2,2% (punkt 4).

Rys. 15. Pierwszy warunek kolumny warunkowej

Aby stworzyć drugi warunek musimy kliknąć w przycisk Dodaj klauzulę. Analogicznie jak w pierwszym warunku określamy dwie pierwsze wartości (Nazwa kolumny i Operator). W polu Wartość wpisujemy 100 000 (kolejny próg dochodowy). Teraz dla pola Wartość wyjściowa zmieniamy typ danych, rozwijamy ikonkę obok tego pola i z listy rozwijanej wybieramy Wybierz kolumnę (rys. 16).

Rys. 16. Zmiana typu danych w polu Wartość wyjściowa

Następnie w polu Wartość wyjściowa wybieramy kolumnę Wygenerowany obrót. Istotne jest, że w celu uzyskania wysokości premii musimy przemnożyć procenty przez wartość wygenerowanego obrotu, ale tego niestety nie możemy zrobić w kolumnie warunkowej.

Warto sprawdzić co się stanie, jeśli w polu Wartość wyjściowa wpiszemy mnożenie, czyli 2,2%*[Wygenerowany obrót]. Jeśli żaden z warunków nie zostanie spełniony to chcemy otrzymać wartość 0, więc wpisujemy 0 w polu W przeciwnym razie (rys. 17).

Rys. 17. Warunki kolumny warunkowej

Otrzymamy dane z nową kolumną warunkową przedstawione na rys. 18.

Rys. 18. Nowa kolumna warunkowa

Możemy zauważyć, że zapis mnożenia w warunkach nie zadziałał prawidłowo, Power Query potraktował go jako tekst i w pasku formuły zapisał go w podwójnych cudzysłowach oraz kolorem czerwonym (rys. 19).

Rys. 19. Zapis mnożenia traktowany jako tekst w formule

Wynika z tego, że musimy zmodyfikować kod. W tym celu klikamy na koło zębate obok kroku Dodano kolumnę warunkową w Zastosowanych krokach (jak na rys. 8). Otworzy się okno Dodawania kolumny warunkowej, gdzie w polu Wartość wyjściowa zmieniamy zapis i zostawiamy tylko 2,2% (rys. 20). Tak zmodyfikowane warunki kolumny zatwierdzamy przyciskiem OK.

Rys. 20. Modyfikacja warunków kolumny warunkowej

Otrzymamy zmodyfikowaną kolumnę warunkową przedstawioną na rys. 21.

Rys. 21. Kolumna warunkowa po zmianach warunków

Możemy zauważyć, że wartość 0,022 (2,2%) została zapisana jako liczba kolorem zielonym (rys. 22).

Rys. 22. Zapis zmienionego warunku w pasku formuły

Jak widać na rys. 21, w danych przypisane są różne wartości w zależności od wygenerowanych obrotów. Mianowicie dla sprzedawców, którzy wygenerowali obroty mniejsze od 100 000, jest przypisana wartość 0. Dla sprzedawców z obrotem powyżej 150 000 jest wartość 0,022 odpowiadającą 2,2% premii. Natomiast dla tych, którzy wygenerowali obroty między 100 a 150 tysięcy, przypisana jest wartość ich obrotów.

Pamiętamy, że zapis mnożenia wpisany w okno Dodawania kolumny warunkowej nie działał prawidłowo. A co się stanie, gdy spróbujemy użyć mnożenia w pasku formuły? Wprowadzimy teraz zmiany w formule kolumny warunkowej. Pierwotny zapis wyglądał następująco:

=Table.AddColumn(#"Zmieniono typ","Premia", each if [Wygenerowany obrót] > 150000 then 0,022 else if [Wygenerowany obrót] > 100000 then [Wygenerowany obrót] else 0

Dla spełnionego warunku, gdzie obrót jest większy od 150000 pomnożymy obrót przez 0,022, natomiast dla kolejnego warunku pomnożymy przez 0,02. Zapis będzie wyglądał następująco:

=Table.AddColumn(#"Zmieniono typ","Premia", each if [Wygenerowany obrót] > 150000 then 0,022*[ Wygenerowany obrót] else if [Wygenerowany obrót] > 100000 then 0,02*[Wygenerowany obrót] else 0

Po zatwierdzeniu powyższej formuły przyciskiem Enter otrzymamy zmienioną kolumnę warunkową (rys. 23).

Rys. 23. Zmieniona kolumna warunkowa

Po dokonaniu takich zmian w kodzie formuły okazuje się, że wykraczają one poza możliwości standardowego dodawania kolumny warunkowej. Dlatego jeśli teraz klikniemy koło zębate obok nazwy kroku Dodano kolumnę warunkową, otworzy się okno Kolumny niestandardowej, a nie kolumny warunkowej (rys. 24).

Rys. 24. Okno Kolumny niestandardowej

Warto w tym oknie podzielić formułę funkcji if w odpowiednich miejscach na osobne wiersze, żeby nie popełnić błędów przy określaniu wysokości premii. Zapis w polu Formuły kolumny niestandardowej powinien wyglądać następująco:

= if [Wygenerowany obrót] > 150000

then 0,022*[ Wygenerowany obrót]

else if [Wygenerowany obrót] > 100000

then 0,02*[Wygenerowany obrót]

else 0

Ponadto możemy użyć dodatkowej funkcji Number.Round, która zaokrągli przyznaną premię do określonej liczby miejsc po przecinku (0). Funkcję tą musimy wstawić w powyższy zapis po słowach "then". Ponadto musimy pamiętać, że Power Query jest Case sensitive, czyli zwraca uwagę na wielkość liter. Zapis formuły powinien wyglądać następująco:

= if [Wygenerowany obrót] > 150000

then Number.Round (0,022*[ Wygenerowany obrót], 0)

else if [Wygenerowany obrót] > 100000

then Number.Round (0,02*[Wygenerowany obrót], 0)

else 0

Powyższą formułę zatwierdzamy przyciskiem OK (rys. 25).

Rys. 25. Formuła kolumny niestandardowej z funkcją zaokrąglenia

Otrzymamy zaokrąglone wyniki (wartości premii sprzedawców) przedstawione na rys. 26.

Rys. 26. Zaokrąglone wartości premii dla sprzedawców w zależności od wysokości obrotów

Tak przygotowane dane można załadować do Excela. W tym celu wybieramy polecenie Zamknij i załaduj do z karty Narzędzia główne (jak na rys. 10). W Excelu otworzy się okno Importowania danych, gdzie musimy określić sposób wyświetlania danych jako Tabela, następnie wybrać miejsce wstawienia danych – Istniejący arkusz oraz wskazać konkretną komórkę. Tak przygotowane parametry Importowania danych zatwierdzamy przyciskiem OK (rys. 27).

Rys. 27. Okno Importowania danych

Otrzymamy dane wstawione do Excela przedstawione na rys. 28.

Rys. 28. Dane wstawione do Excela

Dodatkowo możemy skopiować formatowanie z tabeli bazowej, aby wyświetlały się znaki $. Ustawiamy aktywną komórkę w kolumnie B, następnie klikamy ikonkę Malarza formatów na karcie Narzędzia główne (rys. 29) i zaznaczamy obszar w danych zaimportowanych z Power Query (kolumny H i I).

Rys. 29. Polecenie Malarz formatów

Otrzymamy sformatowane dane ze znaczkami $ przy wygenerowanym obrocie oraz wysokości premii (rys. 30).

Rys. 30. Sformatowane dane

Musimy pamiętać, że Power Query nigdy nie formatuje danych, a jedynie przypisuje danym typ (np. liczbowy, tekstowy, data itp.).

Przeczytaj także:

• Jak przedstawić wyniki sprzedaży na wykresie panelowym?
• Jak przygotować trójwymiarową mapę w Excelu?

Adam Kopeć