Ranking bez remisów w Excelu: kolejność wierszy

Dwie osoby mają po 90 punktów. Excel przyznaje obu pierwsze miejsce, a następnej osobie trzecie. Przy wynikach sportowych taki remis może być dokładnie tym, czego potrzebujesz. Przy przydzielaniu kolejnych pozycji w tabeli chcesz czasem dostać osobne numery: 1, 2, 3 i tak dalej.

Pokażę prosty sposób na taki ranking: przy tej samej liczbie punktów wyżej znajdzie się wcześniejszy wiersz. To jawna zasada rozstrzygania remisu. Formuła nie odczyta z samych punktów, kto zdobył je wcześniej ani kto bardziej zasłużył na wyższe miejsce.

Najpierw ustalam regułę remisu

Ranking to wynik i reguła porządkowania. Jeśli wyższa liczba punktów jest lepsza, ustawiam kolejność malejącą. Dla czasu wykonania zadania lepsza może być niższa wartość. Przed wpisaniem formuły zapisuję ten kierunek.

Potem ustalam, co robię przy równych wynikach. W tym poradniku liczy się kolejność wierszy od góry. Wcześniejszy wiersz dostaje lepszą pozycję w obrębie remisu. Ta zasada jest prosta i nadaje się do przygotowania uporządkowanej listy, o ile osoby korzystające z wyniku ją akceptują.

Jeśli regulamin mówi o wcześniejszym czasie zgłoszenia, potrzebuję kolumny z tym czasem. Samo położenie wiersza nie jest dowodem chronologii. Mogę wcześniej uporządkować cały zbiór według czasu, ale przy identycznych czasach nadal potrzebuję dodatkowej zasady, np. unikalnego identyfikatora. Nie obiecuję uczciwości decyzji tylko dlatego, że numery są unikalne.

Co robi zwykła funkcja rankingowa

W polskim Excelu używam POZYCJA.NAJW, czyli angielskiego RANK.EQ. Według dokumentacji Microsoft równe wartości otrzymują tę samą pozycję, a kolejne pozycje uwzględniają liczbę wcześniejszych elementów. Przy dwóch najlepszych wynikach jest więc 1, 1, 3.

Innym narzędziem jest RANK.AVG, które przy remisie przyznaje średnią zajmowanych pozycji. Dla miejsc 1 i 2 byłoby to 1,5. Opis RANK.AVG potwierdza tę zasadę. Ona również nie tworzy osobnej całkowitej pozycji dla każdej osoby.

Nie trzeba uznawać remisu za błąd Excela. Funkcja poprawnie opisuje miejsce wyniku w zbiorze. Dopiero dodatkowy warunek pozwala utworzyć listę, na której żadne dwa wiersze nie dostaną tego samego numeru.

Pięć syntetycznych wyników

Wpisz nazwy do kolumny A, a punkty do kolumny B. Nagłówki są w pierwszym wierszu, dane w A2:B6. Nazwy i liczby są wymyślone na potrzeby przykładu.

Ada i Celina mają tyle samo punktów. Ada występuje wcześniej, więc dostaje 1, a Celina 2. Ben i Ewa także remisują. Oboje są za dwiema osobami z wynikiem 90, więc rozdzielam między nich miejsca 3 i 4. Dan pozostaje na miejscu 5.

Tabela źródłowa nie musi być posortowana malejąco po punktach. Widzisz to po Benie, który jest nad Celiną, chociaż ma mniej punktów. Jego pozycja nadal wynosi 3. Kolejność wierszy rozstrzyga wyłącznie remis, a nie całą klasyfikację.

Formuła do skopiowania w dół

W C2 wpisuję i kopiuję do C6:

=POZYCJA.NAJW(B2;$B$2:$B$6;0)+LICZ.JEŻELI($B$2:B2;B2)-1

W angielskim Excelu odpowiednik wygląda tak:

=RANK.EQ(B2,$B$2:$B$6,0)+COUNTIF($B$2:B2,B2)-1

Polskie przykłady używają średników, angielskie przecinków. Separator argumentów zależy od ustawień regionalnych. Funkcje te są opisane przez Microsoft dla m.in. Microsoft 365, Excel 2024 i Excel 2021. Nie wymagają budowania tablicy rozlanej.

Pierwsza część daje zwykłą pozycję punktów. Druga zlicza wystąpienia aktualnego wyniku od początku danych do bieżącego wiersza. Funkcję zliczającą opisuje instrukcja LICZ.JEŻELI. Odejmuję jeden, ponieważ pierwsze wystąpienie ma pozostać na swojej zwykłej pozycji.

Dlaczego zakres raz stoi, a raz rośnie

W POZYCJA.NAJW cały zbiór punktów jest zablokowany: $B$2:$B$6. Po skopiowaniu formuły każdy wiersz ma być porównywany z tymi samymi pięcioma wynikami. Gdyby zakres przesuwał się razem z formułą, poszczególne osoby rywalizowałyby z różnymi zbiorami.

W LICZ.JEŻELI początek jest zablokowany, a koniec rośnie: $B$2:B2, następnie $B$2:B3, a potem $B$2:B4. Ten rosnący fragment nadaje kolejnym identycznym wynikom korektę 0, 1, 2 i tak dalej.

W opisie odwołań względnych i bezwzględnych Microsoft wyjaśnia, które adresy zmieniają się przy kopiowaniu. Warto obejrzeć pasek formuły w pierwszym, środkowym i ostatnim wierszu. Brak jednego znaku $ może zmienić sens całego rankingu.

Rozpisuję wynik Celiny krok po kroku

Celina ma 90 punktów w B4. Zwykła pozycja 90 w całym zbiorze to 1. W zakresie od B2 do B4 liczba 90 występuje dwa razy: u Ady i Celiny. Korekta wynosi więc 2 - 1 = 1. Po dodaniu do pozycji podstawowej otrzymuję 2.

U Ewy wynik 80 ma zwykłą pozycję 3. W zakresie B2:B6 są dwa takie wyniki. Dodaję 2 - 1, czyli 1, i dostaję 4. U Bena ta sama liczba pojawia się po raz pierwszy, więc korekta wynosi zero.

To wyjaśnia, dlaczego pozycje nie zderzają się z następną grupą. Dwa wyniki 90 zajmują miejsca 1 i 2, a zwykła pozycja następnej wartości zaczyna się od 3. Gdyby najlepszych wyników było trzy, zajęłyby trzy kolejne miejsca.

Co może zmienić ranking

Zmiana kolejności równych wyników może zmienić ich ostateczne pozycje, bo właśnie kolejność wierszy jest tu kryterium pomocniczym. Jeśli potrzebujesz wyniku niezależnego od układu arkusza, wybierz trwałe kryterium, np. identyfikator, i przygotuj osobne rozwiązanie oparte na tej regule.

Filtr ukrywający wiersze nie zmienia zakresu formuły na listę wyłącznie widocznych pozycji. Ranking w tym przykładzie obejmuje całe B2:B6. Jeśli interesuje Cię ranking w jednej kategorii albo tylko aktywnych osób, najpierw trzeba zdefiniować taki zbiór. Samo schowanie pozostałych nie wystarcza.

Dopisanie kolejnej osoby pod wierszem 6 także wymaga aktualizacji końca zakresu. Formuła ze stałym $B$6 nie wie, że nowy wiersz ma wejść do rywalizacji. Przy większej liście pilnuję tego szczególnie po imporcie kolejnego pliku.

Dane liczbowe i pozorne remisy

Przykład zakłada kompletne wyniki liczbowe, bez pustych komórek i błędów. Jeśli import daje tekst, najpierw sprawdzam typ danych. Nie mieszam w rankingu poprawnych wyników z opisem „brak pomiaru” i nie nadaję brakowi automatycznie zera.

Zwracam też uwagę na precyzję. Dwie komórki mogą wyświetlać 90, chociaż jedna przechowuje 89,999, a druga 90. Format wyświetlania nie ustanawia równości danych. Jeżeli zasady klasyfikacji wymagają porównywania wartości zaokrąglonych, zapisuję tę regułę i przygotowuję kolumnę punktów używanych do rankingu.

Dla mniejszej wartości jako lepszej zmieniam ostatni argument funkcji rankingowej z 0 na 1. Rosnący licznik identycznych wartości nadal rozstrzyga remis kolejnością wierszy. Trzeba tylko jasno opisać kierunek, żeby pierwsze miejsce oznaczało to, co zamierzałeś.

Ćwiczenie i przekazanie wyniku

Zamień miejscami pełne wiersze Ady i Celiny w kopii danych, a następnie odtwórz formuły według ich bieżących pozycji. Zastanów się, kto powinien dostać 1 przy zasadzie „wcześniejszy wiersz wygrywa remis”. Potem nadaj Benowi 90 punktów i rozpisz trzy wystąpienia tego wyniku od góry.

Do raportu dopisuję jedno zdanie: „Ranking malejąco według punktów; przy remisie decyduje kolejność wierszy”. Taka informacja jest bardziej przydatna niż zapewnienie, że formuła zawsze wybiera sprawiedliwie. Jeśli zamiast porządkowania wyników potrzebujesz połączyć oceny o różnej wadze, zajrzyj do średniej ważonej.