Ranking sprzedawców w Excelu
W życiu często chcemy wiedzieć, kto jest najlepszy. Nam zależy na znalezieniu najlepszego sprzedawcy, a dokładniej –na wyznaczeniu rankingów sprzedawców od najlepszego do najgorszego. Excel zawiera kilka narzędzi ułatwiających to zadanie. Zaczniemy od tabeli przestawnych.
Tabele przestawne
Nasze dane sprzedażowe obejmują okres od 2018 do 2020 roku (Rysunek 1).
Na ich podstawie potrzebujemy stworzyć tabelę przestawną. W tym celu zaznaczamy pojedynczą komórkę w danych (np. A1), a następnie klikamy w polecenia Tabela przestawna na karcie Wstawianie (Rysunek 2). Otworzy się okno wstawiania tabeli przestawnej (Rysunek 3), w którym możemy się upewnić, czy Excel wykrył poprawnie zakres danych (pole Tabela/zakres), oraz wybrać, czy nową tabelę przestawną wstawiamy na Nowym arkuszu, czy na Istniejącym arkuszu. Na potrzeby wyznaczenia rankingu sprzedawców chcemy stworzyć nową tabelę przestawną na nowym arkuszu.
W utworzonej tabeli przestawnej przeciągamy pole (kolumnę) Data do obszaru etykiet kolumn i grupujemy je po latach. Następnie do obszaru etykiet wierszy przeciągamy pole Sprzedawca. Kolejnym krokiem będzie dwukrotne przeciągnięcie pola Sprzedaż do obszaru wartości. Warto jeszcze, dla pierwszego przeciągnięcia pola Sprzedaż, zmienić format liczby na walutę. Po tych operacjach uzyskamy odpowiednią tabelę przestawną (Rysunek 4).
Teraz możemy przejść do wyznaczenia rankingu sprzedawców. W kolumnie C (Rysunek 4) klikamy na dowolną komórkę z podsumowaniem wartości prawym przyciskiem myszy, w podręcznym menu rozwijamy opcję Pokaż wartości jako i z listy możliwości wybieramy Porządkuj od największych do najmniejszych (Rysunek 5). Oznacza ona, że największej wartości sprzedaży zostanie przypisane pierwszej miejsce (1), a najmniejszej – miejsce ostatnie. Warto jeszcze nadpisać nagłówki w tabeli przestawnej – w komórkę C5 wpisać słowo Ranking, a w komórkę B5 Przychód (Rysunek 6).
Jeśli zależałoby nam na rankingu, gdzie pierwsze miejsce zajmuje osoba z najmniejszą wartością (np. najkrótszym czasem), należałoby wybrać możliwość Porządkuj od najmniejszych do największych (pamiętajmy, że Excel tak stworzony ranking wciąż będzie sortował na podstawie podsumowań – Rysunek 4).
Wyznaczanie rankingu jest prostą sprawą, jeśli nie mamy remisów (np. 2019 rok). Wtedy każdemu sprzedawcy jest przypisane określone miejsce (od 1 do 10). Jeżeli pojawiają się remisy (2018 rok), sytuacja się skomplikuje. Można przyjąć różne sposoby rozwiązania takiego problemu. Rozwiązanie z tabeli przestawnej, którego użyliśmy, przydziela to samo miejsce dwóm sprzedawcom (miejsce 6. zostało przypisane zarówno Achillesowi, jak i Agamemnonowi). Kolejny sprzedawca uzyskuje kolejne miejsce (Leokrates – 7.). Takie podejście powoduje, że w 2018 roku nie mamy sprzedawcy, któremu przydzielone zostałoby 10. miejsce, tak samo jak w 2019 roku. Inne rozwiązania remisów prezentują formuły Excela.
Formuły
Aby wyznaczyć ranking sprzedawców za pomocą formuł, w pierwszej kolejności musimy obliczyć wartość sprzedaży. Chcemy się skupić na 2018 roku. Ponieważ nie mamy osobnej kolumny z rokiem, nie zadziała funkcja SUMA.WARUNKÓW, by wyciągnąć wartość sprzedaży poszczególnych sprzedawców w wybranym roku. Musimy nasze warunki odpowiednio zapisać w funkcji SUMA.ILOCZYNÓW (Rysunek 7).
B2: =SUMA.ILOCZYNÓW(Dane!$F$2:$F$100000* (ROK(Dane!$A$2:$A$100000)= B$1)*(Dane!$B$2:$B$100000=$A2))
Gdy już mamy obliczone interesujące nas wartości, możemy zacząć wyznaczać ranking. Od Excela 2010 możemy do tego wykorzystywać funkcje POZYCJA.NAJW i POZYCJA.ŚR. Została również zachowana funkcja POZYCJA dla zgodności z Excelem 2007. Wszystkie te funkcje mają dokładnie taką samą składnię:
=POZYCJA.ŚR(liczba;odwołanie;[lp])
gdzie:
- liczba (argument wymagany) – to liczba, której pozycję/ranking będziemy wyznaczać,
- odwołanie (argument wymagany) – zakres komórek z liczbami, na podstawie których będziemy określić pozycję podanej przez nas liczby,
- lp (argument opcjonalny) – ten argument określa, czy porządkujemy liczby rosnąco (liczba 1), czy malejąco (liczba 0). Gdy nie zostanie on podany, Excel uporządkuje liczby malejąco. Oznacza to, że największej liczbie ze zbioru (odwołanie) zostanie przypisana pozycja 1. Mniejsze liczby będą uzyskiwały odpowiednie kolejne miejsce, aż do ostatniego (w naszym przykładzie 10).
Gdyby w naszych danych nie było remisów, wszystkie funkcje zadziałałyby identycznie, przydzielając pozycję analogicznie do tabeli przestawnej. Nas jednak interesuje omówienie różnic przy remisach.
Zacznijmy od funkcji POZYCJA i POZYCJA.NAJW. Te funkcje wyznaczają remisy tak samo, czyli wartości z remisami uzyskują najwyższe możliwe miejsce (w naszym przykładzie to miejsce 6. – Rysunek 6). Kolejny sprzedawca (Leokrates) nie uzyska miejsca 7., jak w tabelach przestawnych, lecz 8. Excel oblicza pozycję na zasadzie policzenia wartości, które są mniejsze bądź równe w podanym zakresie do badanej liczby. Oznacza to, że jeśli trzech sprzedawców uzyskałoby 6. pozycję/miejsce, to kolejny zająłby 9. pozycję.
Trochę inaczej jest liczona pozycja przy remisach dla funkcji POZYCJA.ŚR. Pozycja przy remisach jest liczona na zasadzie średniej. W naszym przykładzie oznacza to, że Achillesowi i Agamemnonowi powinny zostać przyznane 6. i 7. miejsce, więc miejsce im przypisane =(6+7)/2=6,5. Gdyby trzy osoby uzyskały ten sam wynik, czyli miejsca 6., 7. i 8., ich średnia pozycja byłaby wyliczona jako =(6+7+8)/3=7.
Podsumowując wszystkie funkcje, otrzymalibyśmy odpowiednie wyniki (Rysunek 8):
C2: =POZYCJA.NAJW(B2;$B$2:$B$11)
D2: =POZYCJA.ŚR(B2;$B$2:$B$11)
E2: =POZYCJA(B2;$B$2:$B$11)
Co możemy zrobić, gdy zależy nam na zachowaniu ciągłości w przyznawanych pozycjach, czyli chcemy, żeby któremuś ze sprzedawców została przydzielona 7. pozycja? W takiej sytuacji należy wykonać bardziej skomplikowane obliczenia, ale przede wszystkim określić, kto uzyska lepszą pozycję. W większości sytuacji przyjmuje się, że lepsza pozycja przyznawana jest wcześniejszemu sprzedawcy. Przeważnie jest to rozróżniane na podstawie kolejności alfabetycznej, czyli w naszym przykładzie Achillesowi powinna zostać przyznana 6. pozycja, a Agamemnonowi 7. Aby uzyskać taki wynik, w pierwszej kolejności musimy zliczyć liczbę większych wartości od aktualnie sprawdzanej. Można do tego wykorzystać funkcję LICZ.JEŻELI (Rysunek 9):
F2: =LICZ.JEŻELI($B$2:$B$11;”>”&B2)
Następnie należy dodać liczbę takich samych liczb do danego wiersza, czyli znów możemy do tego wykorzystać funkcję LICZ.JEŻELI z rozszerzającym się zakresem (Rysunek 10):
F2: =LICZ.JEŻELI ($B$2:$B$11;”>”&B2)+LICZ.JEŻELI($B$2:B2;B2)
W zależności od potrzeb możemy wybrać odpowiednią formułę.
Power Query
Na potrzeby wyznaczania rankingu w Power Query przygotowaliśmy uproszczony zestaw danych, który zawiera tylko kolumny Rok, Sprzedawca i Sprzedaż (Rysunek 11). Dane te pobieramy do Power Query.
Pierwszym krokiem, jaki dodamy w zapytaniu Power Query, jest odpowiednie grupowanie. Zaznaczamy kolumny Rok i Sprzedawca, a następnie klikamy w polecenie Grupowanie według na karcie Narzędzia główne (Rysunek 12).
Power Query otworzy okno grupowania zaawansowanego, w którym zaznaczamy, że chcemy dokonać operacji sumowania na kolumnie Sprzedaż, a utworzoną kolumnę z sumą nazwać Sprzedaż (Rysunek 13).
Po zgrupowaniu danych kolejnym krokiem będzie posortowanie utworzonej kolumny Sprzedaż od największych do najmniejszych wartości. Do tak posortowanych danych dodamy kolumnę indeksu liczącego od 1 (polecenie Kolumna indeksu na karcie Dodaj kolumnę – Rysunek 14).
Aktualnie przygotowany zestaw danych (Rysunek 15) znów musimy zgrupować – tym razem tylko po kolumnie Sprzedaż, ale potrzebujemy wykonać aż trzy różne operacje. Pierwszą będzie obliczenie minimalnej wartości z kolumny Indeks (Ranking1), drugą – obliczenie średniej również z kolumny Indeks (Ranking2). Trzecia to wykonanie pomocniczej operacji wyciągnięcia wszystkich wierszy, dlatego stworzoną kolumnę nazwiemy Pomoc (Rysunek 16).
W ten sposób uzyskaliśmy dwa pierwsze sposoby wyznaczania pozycji w rankingu. Problemem jest tylko to, że informacja o sprzedawcach jest ukryta w kolumnie Pomoc. Możemy zobaczyć, że ukryte są w niej całe wiersze wcześniejszych danych. Dla szóstego wiersza są to aż dwa wiersze (Rysunek 17).
Teraz możemy znów dodać kolumnę indeksu, by uzyskać trzeci ranking (pamiętajmy o tym, by odpowiednio nazwać kolumnę). Następnie możemy rozwinąć kolumnę Pomoc, klikając w strzałki w nagłówku kolumny. Przy rozwijaniu zależy nam tylko na kolumnie Sprzedawca. Nie potrzebujemy też prefików (Rysunek 18).
Po rozwinięciu kolumny Pomoc wartości z pozostałych kolumn zostaną zduplikowane dla szóstego wiersza, ponieważ kryje się pod nim dwóch sprzedawców. Dane powinny być posortowane po kolumnie Sprzedaż, a następnie – po kolumnie Sprzedawca. Jeśli tak się nie stało, musimy ponownie wykonać odpowiednie sortowania. Na koniec możemy dodać jeszcze raz kolumnę z indeksem, by uzyskać czwarty sposób wyznaczania rankingu. Pozostaje co najwyżej zmiana kolejności kolumn i utworzone dane (Rysunek 19) możemy załadować do Excela.
W Excelu możemy wyznaczać ranking lub pozycję liczb na liście na różne sposoby. Gdy nie ma remisów, najprościej skorzystać z tabeli przestawnej albo prostej funkcji POZYCJA. Gdy w danych trafią się remisy, musimy zastanowić się, jak mają być przydzielane miejsca w takiej sytuacji, i dobrać odpowiedni sposób do tej decyzji.
