Ranking za pomocą RANK i PERCENTILE

Często nie chodzi tylko o samą wartość, lecz także o jej pozycję w grupie. Czy ten sprzedawca zajmuje 1. czy 12. miejsce? Czy ten wynik mieści się w najlepszych 10%?

Odpowiadają na to dwie rodziny funkcji: RANK podaje pozycję porządkową, a PERCENTILE — wartość dla określonego punktu odcięcia. Razem przekształcają surowe liczby w rankingi i wartości referencyjne.

RANK.EQ podaje pozycję wartości w zakresie. Należy przekazać wartość, zakres i parametr określający kolejność.

• Kolejność 0 (lub pominięta) szereguje od największej wartości (malejąco).
• Kolejność 1 szereguje od najmniejszej wartości (rosnąco).

W tym przykładzie najwyższa wartość sprzedaży otrzymuje rangę 1.

Zwróć uwagę na znaki dolara: $B$2:$B$20. Zakres musi pozostać stały podczas kopiowania formuły w dół, natomiast odwołanie do wartości B2 powinno zmieniać się na B3, B4 itd.

Jeśli pominiesz znaki dolara, zakres będzie przesuwał się przy każdym wierszu, generując bezsensowne rangi. Odwołania bezwzględne w zakresie są tutaj niezbędne.

RANK.EQ nadaje wartościom ex aequo tę samą rangę, a następnie pomija kolejną. Jeśli dwie wartości zajmują ex aequo 2. miejsce, obie otrzymają rangę 2, a następna wartość — 4 (bez 3. miejsca).

Jeśli zamiast tego chce Pan/Pani uzyskać średnią rangę dla remisów, należy użyć funkcji RANK.AVG. Dwie wartości zajmujące ex aequo 2. i 3. miejsce otrzymają rangę 2,5.

Aby wymusić unikatowe rangi mimo remisów, dodaj funkcję COUNTIF jako mechanizm rozstrzygający remis. Zlicza ona, ile równych wartości znajduje się powyżej bieżącego wiersza, i dodaje to przesunięcie.

Gwarantuje to uporządkowaną sekwencję 1, 2, 3, 4 bez powtórzeń, co jest przydatne w rankingach.

PERCENTILE.INC odpowiada na pytanie: jaka wartość znajduje się w określonym punkcie procentowym? Należy przekazać zakres i ułamek od 0 do 1.

Poniższa formuła zwraca 90. percentyl, czyli wartość, poniżej której znajduje się 90% danych. Doskonale nadaje się do ustalania progów, takich jak „osoby osiągające wyniki w najlepszych 10%”.

Kwartyle dzielą dane na cztery części. Funkcja QUARTILE.INC to skrócona forma: kwartyl 1 odpowiada 25. percentylowi, kwartyl 2 — 50. percentylowi (medianie), a kwartyl 3 — 75. percentylowi.

Poniższa formuła zwraca trzeci kwartyl, identycznie jak PERCENTILE.INC(A2:A101,0.75).

Istnieją dwa warianty: PERCENTILE.INC (włączający, akceptuje wartości 0 i 1) oraz PERCENTILE.EXC (wyłączający, przyjmuje tylko wartości między 0 a 1, nigdy wartości skrajne).

W większości codziennych raportów standardowym wyborem jest .INC. Należy użyć .EXC, gdy stosują Państwo konwencje statystyczne wykluczające punkty graniczne.

RANK podaje pozycję, a PERCENTILE — wartość dla określonego progu. PERCENTRANK.INC działa odwrotnie niż percentyl: mając daną wartość, określa, któremu percentylowi ona odpowiada.

Jeśli student uzyskał wynik 88, a PERCENTRANK zwraca 0.92, oznacza to, że student osiągnął wynik lepszy niż 92% grupy. Należy pomnożyć tę wartość przez 100, aby wyświetlić ją jako procent.

RANK i PERCENTILE działają wyłącznie na liczbach. Etykiety tekstowe i puste komórki znajdujące się w zakresie są pomijane, więc przypadkowy nagłówek nie zakłóci rankingu.

Należy jednak uważać na liczby zapisane jako tekst: są one całkowicie pomijane, co przesuwa każdą rangę i wartość percentylową. Jeśli ranga wygląda nieprawidłowo, należy sprawdzić, czy wszystkie wartości są rzeczywistymi liczbami, a nie tekstem wyrównanym do lewej.

Mają Państwo 20 przedstawicieli handlowych, których wyniki sprzedaży znajdują się w kolumnie B. Chcą Państwo określić rangę każdego przedstawiciela oraz sprawdzić, czy należy on do górnego kwartylu.

• Ranga: =RANK.EQ(B2,$B$2:$B$21,0)
• Wskaźnik górnego kwartylu porównuje wynik sprzedaży z 75. percentylem.

Poniższa formuła zwraca dla każdego przedstawiciela wartość Top lub Standard na podstawie ustalonego progu.

Sprawdźcie Państwo swoją znajomość funkcji rangowania.

Nauczyli się Państwo określać pozycję wartości w grupie:

• RANK.EQ zwraca pozycję porządkową; kolejność 0 oznacza wartości od największej, a 1 — od najmniejszej. Należy zablokować zakres za pomocą $.
• RANK.AVG uśrednia rangi dla jednakowych wartości; aby uzyskać unikalne rangi, należy dodać COUNTIF.
• PERCENTILE.INC zwraca wartość dla określonego progu; QUARTILE.INC to skrót dla 25., 50. i 75. percentyla.
• PERCENTRANK.INC działa odwrotnie: zwraca percentyl dla podanej wartości.