
Jak znaleźć 3 największe wartości
Jak znaleźć zwycięzcę w konkursie dzięki funkcjom MAX.K, INDEKS, PODAJ.POZYCJĘ, LICZ.JEŻELI
W dzisiejszym zadaniu dostaliśmy tabelę konkursową z wynikami uczestników, a naszym zadaniem jest znalezienie 3 zwycięzców.
Jak znaleźć 3 największe wartości w Excelu – film instruktażowy
Praca z przykładowym plikiem
Plik do przykładu można pobrać poniżej.
UWAGA! Plik wyświetla błędy, jeśli wpisana funkcja w komórkę jest inna niż zaplanowałem dla danego przykładu. Co nie oznacza, że koniecznie jest to błędem, ponieważ często pewne rzeczy w Excelu można zrobić na kilka / kilkanaście sposobów.

Tabela zawiera LP., Imiona, Nazwiska oraz Ilość punktów zdobytych przez uczestników. Naszym zadaniem jest wyszukać trzy pierwsze miejsca z największą ilością punktów, a jeśli będą osoby z taką samą ilością punktów to decydującym warunkiem będzie pozycja na liście – osoba wyżej wygrywa z osobą niżej.

Funkcja LICZ.JEŻELI – sprawdzamy ilość wystąpień Ilości punktów
Jako, że zależy nam, aby wartości o pozycji wyższej w tabeli miały większą wartość od tych niżej to wykorzystam funkcję LICZ.JEŻELI, to przeliczenia tych wartości. Tworzymy więc kolumnę pomocniczą i w kolumnie E, przechodzimy do komórki E32, gdzie wpisujemy:
=LICZ.JEŻELI($D$32:D32;D32)
Dzięki takiej konstrukcji, gdy skopiujemy formułę w górę funkcja licz jeżeli będzie zwiększała zakres, w którym będzie sprawdzać czy poszukiwana wartość już była i wypisze jej kolejne wystąpienie. Więcej na ten temat we wpisie LICZ.JEŻELI – jak dodać nr kolejny wystąpienia nazwy.

Po skopiowaniu funkcji do wyższych komórek pojawią się następujące wyniki.

No tak, ale my chcielibyśmy, żeby Excel od razu nam dał coś więcej niż tylko nr wystąpienia. Proponuję więc połączyć wartości z komórek D i E.
Uaktualnienie
Przemek słusznie w komentarzu zwrócił uwagę na błąd. Wcześneiej formuła wyglądała tak: =D8&LICZ.JEŻELI($D8:D$32;D8) i dopókiwyników o tej samej wartości punktów było 9, wynik był podawany poprawnie. Niestety w momecie pojawienia już 10 takich samych wyników – formuła przestała spełniać swoje zadanie.
Modyfikujemy w komórce E8 formułę, która po modyfikacji powinna wyglądać tak:
=D8&”,”&LICZ.JEŻELI($D8:D$32;D)
Dzięki temu zabiegowi każde kolejne wystąpienie tego samego wyniku, będzie podliczone i zapisane po przecinku. Znakiem, który łączy wartości z komórek (nie dodaje) jest &. W formule występuje 2 razy, gdyż łączy watość z komórki D8 z przecinkiem oraz liczbą wystąpienia wartości z D8.
Uaktualnienie 2
Przemek znalazł drugi problem, na który zwrócił uwagę.
Jak rozpoznać wynik, który jest wyższy, jeśli jeden będzie wynikiem 5,1 , a drugi 5,10.
Na pierwszy rzut oka wygląda tak samo, jednak sposób tworzenia cyfr po przecinku powoduje, że zarówno pierwsze wystąpienie jak i 10 daje ten sam wynik.
To co pozostaje to ubrać wynik formuły zliczającej w funkcję TEKST, która pozwoli na sformatowanie pierwszego wystąpienia wstawiając 0 przed jednościami.
Przemek dziękuję za zwrócenie uwagi 🙂
Cała formuła powinna więc wyglądać tak:
=D8&”,”&TEKST(LICZ.JEŻELI($D8:D$32;D);”00″))

Następnie pozostanie skopiować formułę do poniższych komórek.

Funkcja MAX.K szukamy 3 największych wartości
Żeby wyszukać trzech największych wartości użyjemy funkcji MAX.K. Funkcja MAX.K posiada dwa argumenty, w pierwszym należy wskazać przeszukiwany zakres, a w drugim pozycję, którą szukamy. Stwórzmy więc kolejną kolumnę pomocniczą, np. w kolumnie J. W komórce J9 wpisujemy formułę
=MAX.K($E$8:$E$32;1)

Następnie kopiujemy ją do poniższych dwóch komórek oraz edytujemy każdą z tych formuł tak, by drugi argument miał wartość odpowiadającą poszukiwanemu wynikowi. A więc drugi argument w komórkach powinien wynosić:
- 1 w J9,
- 2 w J10, gdyż szukamy drugiego wyniku,
- 3 w J11, gdyż szukamy trzeciego wyniku.

Jak zauważyliście zamiast wyników w funkcji MAX.K pojawił sie błąd #LICZBA!. Błąd ten oznacza, że cos jest nie tak z formatem liczbowym. Jeśli zerkniemy na arkusz zobaczymy, że w kolumnie E cyfry są wyrównane do lewej strony, a to zazwyczaj oznacza, że mają format tekstowy.
Jak zamienić w Excelu format tekstowy w komórce na liczbowy?
- Przechodzimy do komórki E8 i dodajemy dwa znaki minus przed formułę, którą ujmujemy w nawias. Formuła powinna wyglądać tak: =–(D8&LICZ.JEŻELI($D8:D$32;D8)),
- jak widzimy wpis w komórce zmienił położenie i teraz jest wyrównany do prawej strony,
- skopiujmy formułę do poniższych komórek, efektem czego wszystkie wpisy będą już liczbami,
- w komórkach J9:J11 znikną błędy i zobaczymy już prawidłowe wyniki.

Funkcją INDEKS oraz PODAJ.POZYCJĘ szukamy pozostałych wartości
Przechodzimy do komórki G9 i wpisujemy następującą formułę:
=INDEKS(B$8:B$32;PODAJ.POZYCJĘ($J9;$E$8:$E$32;0))
Zadaniem tej formuły jest wyszukanie imienia, które znajduje się w wierszu z największą wartością, czyli 911. Jeśli nie rozumiesz jak działa powyższa formuła to zapraszam do wpisu, który uzupełni tą wiedzę INDEKS i PODAJ.POZYCJĘ jak użyć zamiast funkcji WYSZUKAJ.PIONOWO

Adresowanie względne i bezwzględne – pomoc przy kopiowaniu komórek
Zwróćmy tutaj uwagę na sposób adresowania komórek. Odpowiednio blokowanie adresów poprzez adresowanie bezwględne np. $A1, $A$1, A$1 lub względne A1 znacząco ułatwia pracę wExcelu, ponieważ wystarczy wtedy skopiować formułę do innych komórek bez jej edycji.
INDEKS(B$8:B$32
Zablokowane są wiersze 8 do 32, natomiast kolumna B nie. To spowoduje podczas kopiowania w prawo, że kolumna zacznie się automatycznie zmieniać.
Podobnie w funkcji
PODAJ.POZYCJĘ($J9
gdy zaczniemy kopiować formułę w dół wiersz zacznie się automatycznie zmieniać.
Więc dalej do wykonania mamy:
- A skoro nie trzeba jej teraz zmieniać to skopiujmy zawartość komórki G9
- do komórek H9:I9,
- następnie zaznaczmy cały wiersz i kopiujmy do dwóch dolnych wierszy.

Sprawdźmy teraz, czy dwie osoby o tej samej ilości punktów będą prawidłowo wyświetlane. Na przykład w komórce D15 wpiszmy 91. Jak widzimy pani Stalińska nadal jest na pierwszym miejscu, gdyż jest pozycję wyżej na liście – tym samym wszystko działa jak powinno.

Ale po co mi te kolumny pomocnicze?
Dokładnie tak, nie zawsze chcemy, by nam się wyświetlały w arkuszu dodatkowe kolumny. Jest kilka możliwych rozwiązań tego problemu, można np.:
- komórki ukryć,
- przenieś komórki poza obszar ekranu,
- można także czego jestem zwolennikiem usuwania takich danych jak najdalej od użytkowników i osobiście zazwyczaj umieszczam je w osobnym arkuszu, więcej na ten temat w Kalkulator gotówki w Excelu – Stan Kasy – część 6.
Poczytaj także
- Jak zrobić Listę Rozwijaną w Excelu?Wysyłasz arkusz do uzupełnienia i mimo informacji, co powinno być wpisane w komórki to jednak użytkownicy uzupełniają zupełnie nie tak. Często przez brak wiedzy, może coś źle zostało to zakomunikowane? Albo po prostu wiedzą lepiej. Chcesz to zmienić? Zobacz jak zrobić listę rozwijaną?
- Dlaczego Excel nie oblicza formuł?Otwierasz plik Excela i okazuje się, że Excel nie chce liczyc formuł? Czyzby Excel zepsuł się? Nie sądzę…
- Jak wyodrębnić inicjały cz.2W drugiej części przedstawiam jak sprawdzić, czy początkowe dwie litery to Ch, Rz, Cz, Sz i jeśli tak to wyświetlić dwie, a nie jedną literę
- Jak wyodrębnić inicjałyPraca z tekstem w Excelu potrafi frustrować. Przynajmniej do momentu, w którym poznamy kilka funkcji tekstowych i sposobów pracy z nimi. Wierzcie mi, tu jedna funkcja nie wystarczy.
- Funkcja WYSZUKAJ.POZIOMOJedna z bardziej popularnych funkcji w Excelu, która służy do wyszukiwania danych. Z artykułu dowiesz sie jak z niej korzystać w podstawowym zakresie.
- Funkcja X.WYSZUKAJPrzedstawiam funkcję przewyższającą WYSZUKAJ.PIONOWO i WYSZUKAJ.POZIOMO. Funkcja ta potrafi to wszystko z czym nie dawały sobie rady wcześniejsze.
7 komentarzy
cześć. metoda może niezadziałać w przypadku kiedy będzie np 10 dublujacych się wartości. wówczas nawet wartość dużo niższa wraz z dwucyfrowym powieleniem zafałszuje wynik
Cześć 🙂 dziękuję za aktywność. Faktycznie ta metoda posiada błąd, o którym piszesz. Myślę, że dobrym rozwiązaniem tego będzie podliczone wystapienia dać po przecinku. pozdrawiam serdecznie 🙂
Należy w takim razie zmienić formułe w komórce E8 na
=--(D8&","&LICZ.JEŻELI($D$8:$D8;D8))
dokładnie tak. dzięki za reakcję
Dzisiaj poprawiłem artykuł, jeszcze raz dziękuję
pozdrawaim
to jeszcze jedna zagwózdka, jak rozróżnić wartości dające po sumowaniu (D8&”,”&LICZ.JEŻELI($D$8:$D8;D8))) wartość np 5,1 oraz 5,10 – tekstowo różne jednak liczbowo nie bardzo 🙂
Widzę, że moge na Ciebie liczyć, niby szczegół, a jednak ?
TEKST(LICZ.JEŻELI($D30:D$32;D30);”00″
pozdrawiam ?
dzięki, polecam się.