MAX.K w Excelu: druga największa wartość i remisy

Jeśli potrzebuję drugiej albo trzeciej największej wartości, używam MAX.K. To polska nazwa angielskiej funkcji LARGE, potwierdzona w dokumentacji Microsoft. Dawna wersja tej strony używała nazwy DUŻA, która była błędna. Adres strony pozostaje ten sam, ale formuły zapisuję już poprawnie.

Najważniejsze pytanie brzmi: szukasz kolejnej obserwacji po uporządkowaniu czy kolejnej różnej liczby? Przy powtórzeniach odpowiedzi mogą się rozjechać. Ja wyjaśniam tę różnicę od razu, zanim wynik trafi do rankingu osób albo produktów.

Składnia i wersja Excela

Składnia to MAX.K(tablica;k), w EN LARGE(array,k). W przykładach k jest dodatnią liczbą całkowitą: 1 dla największej obserwacji, 2 dla drugiej, 3 dla trzeciej. Nie jest numerem wiersza arkusza ani identyfikatorem rekordu.

Microsoft opisuje funkcję dla Excel Microsoft 365, 2024, 2021, 2019 i 2016. Tutaj używam średników w PL, przecinków w EN oraz zwykłego zakresu z liczbami. Separator zależy od ustawień regionalnych. Wszystkie poniższe identyfikatory i wyniki są danymi syntetycznymi.

Pięć rekordów, w tym remis

Wpisz do A1:B6 poniższą tabelę. Kolumna A zawiera identyfikatory, kolumna B wyniki. Obliczenia umieść poza tabelą, na przykład w D2:D4. Dzięki temu nie nadpiszesz źródła przy budowaniu listy największych wartości.

=MAX.K($B$2:$B$6;2)

Wynik to 600. Aby to prześledzić, uporządkuj w myślach obserwacje malejąco: 600, 600, 450, 300, 100. Druga pozycja nadal zawiera 600. Funkcja nie usuwa powtórzeń przed wyborem k.

Lista trzech największych obserwacji

W D2 wpisz formułę dla k równego 1, w D3 dla 2, a w D4 dla 3. Dolary utrzymują ten sam zakres źródłowy. Każda formuła jest samodzielna i można ją wkleić bez dodatkowych komentarzy w kodzie.

=MAX.K($B$2:$B$6;1)
=MAX.K($B$2:$B$6;2)
=MAX.K($B$2:$B$6;3)

Kolejne wyniki to 600, 600 i 450. W angielskim Excelu druga pozycja ma zapis =LARGE($B$2:$B$6,2). Nie musisz sortować tabeli źródłowej, żeby otrzymać taką wartość. Jednocześnie wynik nie przenosi całego wiersza ani jego identyfikatora.

Jeśli pytanie brzmi „jaka jest druga różna wartość?”, w tym zbiorze odpowiedzią jest 450. To wymaga wcześniejszego zdefiniowania zbioru różnych wartości. Nie usuwaj jednak duplikatów z danych źródłowych bez namysłu: dwie osoby z takim samym wynikiem mogą być dwiema prawidłowymi obserwacjami.

Granice k i błędy

Dokumentacja LARGE podaje błąd #NUM! dla pustej tablicy, k nie większego od zera albo k przekraczającego liczbę punktów danych. W polskim Excelu jest to #LICZBA!. Dla pięciu liczb w B2:B6 pozycja 6 nie istnieje.

W tym ćwiczeniu k równe 5 zwraca 100, czyli najmniejszą z pięciu obserwacji. K równe 0 i 6 prowadzi do błędu. To proste przypadki graniczne do sprawdzenia przed użyciem przełącznika pozycji w raporcie.

Nie wpisuję automatycznie JEŻELI.BŁĄD(...;"za mało danych"). JEŻELI.BŁĄD / IFERROR przechwytuje różne błędy, więc taki podpis może wskazywać niewłaściwą przyczynę. Najpierw sprawdź k, zakres i błędy źródła. Dopiero później dobierz uczciwy komunikat.

Wartość a identyfikator rekordu

Chcesz dopisać ID do drugiej największej obserwacji? Poniższe połączenie INDEKS i PODAJ.POZYCJĘ pokaże ważne ograniczenie. Używa tego samego źródła A1:B6, a wynik umieść na przykład w F2.

=INDEKS(A2:A6;PODAJ.POZYCJĘ(MAX.K(B2:B6;2);B2:B6;0))

MAX.K zwraca 600. PODAJ.POZYCJĘ z argumentem 0 szuka dokładnie i zgodnie z dokumentacją MATCH zwraca pierwsze dopasowanie. INDEKS wybiera więc A02, a nie automatycznie A04 jako „drugą osobę”. Powtórzenie formuły dla k równego 1 i 2 może zwrócić to samo ID.

Jeśli ranking ma zawierać unikalne rekordy, ustal sposób rozstrzygania remisów, na przykład wynik malejąco i ID rosnąco. To już porządkowanie całych rekordów. MAX.K dostarcza wartość progową, ale sama nie realizuje reguły wyboru wszystkich nazw ani rozdzielania remisów.

Ćwiczenie i następny krok

Zmień wynik A04 z 600 na 590. Przewidź trzy największe wartości: 600, 590, 450. Potem przywróć remis i zapisz różnicę między „drugą obserwacją”, „drugą różną wartością” oraz „wszystkimi rekordami z najwyższym wynikiem”. Każde z tych zdań opisuje inne wymaganie.

Do wybierania najmniejszych obserwacji służy MIN.K / SMALL. Jeśli pozostajesz przy największych, zacznij od jasnego opisu remisu i liczby oczekiwanych wierszy. Wtedy czytelna lista top wyników nie będzie przypadkowym skutkiem kolejności źródła.