Jak znaleźć 3 największe wartości

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

Jak z znaleźć 3 największe wartości – plik

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.

Jak znaleźć 3 największe wartości

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.

Excel funkcja LICZ JEŻELI

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

LICZ.JEŻELI - podliczenie ilości wystąpień wyniku

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.

Zapisz się do newslettera

Zapisz się do newslettera lubieExcela.pl
bądź zawsze na bieżąco

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)

Funkcja MAX.K - budowa

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.
Kopiowanie funkcji MAX.K do poniższych komórek oraz edycja 2 argumentu

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?

  1. 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)),
  2. jak widzimy wpis w komórce zmienił położenie i teraz jest wyrównany do prawej strony,
  3. skopiujmy formułę do poniższych komórek, efektem czego wszystkie wpisy będą już liczbami,
  4. w komórkach J9:J11 znikną błędy i zobaczymy już prawidłowe wyniki.
Zamiana formatu tekstowego na liczbowy

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

INDEKS oraz PODAJ.POZYCJĘ wyszukiwanie zwycięzców

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:

  1. A skoro nie trzeba jej teraz zmieniać to skopiujmy zawartość komórki G9
  2. do komórek H9:I9,
  3. następnie zaznaczmy cały wiersz i kopiujmy do dwóch dolnych wierszy.
Adresowanie względne i bezwzględne - kopiowanie wartość pomiędzy komórkami

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.

Sprawdzenie poprawności działania

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

  • WARUNKI – podstawowe wykorzystanie
    Funkcja WARUNKI – budowa? Funkcja WARUNKI do działania potrzebuje tylko jednego argumentu, a w zasadzie pary warunków: testu logicznego i wartości, jeśli test logiczny jest […]
  • Tabela przestawna – podstawy
    Tabela przestawna – film instruktażowy Tabela przestawna – na czym polega zadanie? Posiadamy tabelę z danymi sprzedażowymi pięciu regionów sprzedażowych: południowy północny zachodni wsochdni centralny […]
  • Mediana
    Funkcja MEDIANA – film instruktażowy Funkcja MEDIANA – budowa W funkcji MEDIANA jako argumenty podajemy liczby lub zakres komórek, z których następnie Excel wskaże MEDIANĘ, np: =MEDIANA(1,2,3,4,5) […]
  • Funkcja ŚREDNIA
    Z artykułu dowiesz się jak korzystać z funkcji statystycznej ŚREDNIA. Nauczysz się wykorzystywać funkzję ŚREDNIA, a także kopiowanie jej do komórek obok oraz włączanie jej […]
  • Funkcja PRAWY
    Funkcja PRAWY Excel – film instruktażowy Funkcja PRAWY – budowa Funkcja PRAWY posiada dwa argumenty, pierwszy obowiązkowy to tekst, a drugi opcjonalny to liczba znaków. […]
  • Excel – największa wartość – formatowanie warunkowe
    Jak wyszukać największą wartość za pomocą formatowania warunkowego? Pobierz plik do przykładu poniżej jest link lub użyj swojego. Zaznacz obszar danych, które chcesz przeszukać. W […]

7 thoughts on “Jak znaleźć 3 największe wartości

  1. 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

    1. 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))

  2. 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 🙂

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *

Witryna wykorzystuje Akismet, aby ograniczyć spam. Dowiedz się więcej jak przetwarzane są dane komentarzy.