Automatyzacja pracy

Jak z 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 musimy znaleźć 3 zwycięzców konkursu. W tabeli, którą posiadamy mamy imiona i nazwiska oraz ilość punktów, które użytkownicy zdobyli. Naszym zadaniem jest wyszukać trzy pierwsze miejsca z największą ilością punktów, jeśli będą osoby z taka 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

Funkcją LICZ.JEŻELI sprawdzimy  ilości wystąpień  wartości

Jako, że zależy nam, aby wartości wyżej  miały większą wartość to funkcję LICZ JEŻELI zaczniemy wpisywać w ostatnim wierszu danych w tabeli. Tworzymy więc kolumnę pomocniczą, na początek niech będzie w kolumnie E, przechodzimy do komórki E32 i 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ęc 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 po skopiowaniu do do pozostałych komórek

No tak, ale my chcielibyśmy, żeby Exel od razu nam dał coś więcej niż tylko nr wystąpienia. Proponuje więc połączyć wartości z komórki D z E, w tym celu modyfikujemy w komórce E8 formułę, po modyfikacji powinna wyglądać tak: =D8&LICZ.JEŻELI($D8:D$32;D), efektem czego będzie wartość dwóch komórek, czyli 71. Znakiem, który łączy wartości z komórek (nie dodaje) jest &.

Licz JEŻELI gotowa formuła zliczająca ilość wystąpień

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

Połączone wartości komórki z licz.jeżeli

Funkcja MAX.K szukamy 3 największych wartości

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

Excel funkcja MAX.K

Następnie kopiujemy ją w dół po poniższych dwóch komórek oraz edytujemy każdą z tych formuł i zmieniamy drugi argument z 1 na właściwą kolejna liczbę, czyli w komórce J10 na 2, ponieważ szukamy drugiego wyniku, a w J11 na 3, gdyż szukamy 3 wyniku.

Kopiowanie komórki z funkcją MAX.K

Nadal widzimy błąd w tych komórkach, który wynika z tego, że szukamy liczby, a w szukanym zakresie mamy tekst. Najłatwiej to poznać po tym, że Excel liczby wyrównuje do prawej krawędzi, a tekst do lewej.

Jak zamienić format tekstowy w komórce na liczbowy

  1. przechodzimy do komórki E8 i dodajemy dwa znaki minus przed formułę ujętą 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 także dojdzie do zmiany i zobaczymy poprawne wyniki.

Zamiana formatowania tekstowego komórki na liczbowe wewnątrz formuły

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)), której zadaniem jest wyszukanie imienia, które znajduje się w wierszu z największą wartością, czyli 911. Jeśli nie rozumiesz jak działa ta funkcja to zapraszam do wpisu, który uzupełni tą wiedzę INDEKS i PODAJ.POZYCJĘ jak użyć zamiast funkcji WYSZUKAJ.PIONOWO

Excel funkcje INDEKS i PODAJ.POZYCJĘ

Zwróćmy tutaj uwagę na sposób adresowania, dzięki temu, że zablokujemy w funkcji INDEKS wiersze oraz kolumnę J to podczas kopiowania formuły w prawo, nie będzie trzeba zmieniać formuły.

  1. A skoro nie trzeba jej teraz zmieniać to skopiujmy zawartość komórki G9
  2. do komórek H9:I9,
  3. a następnie zaznaczmy cały wiersz,
  4. i skopiujmy do dwóch dolnych wierszy.

Kopiowanie gotowej formuły INDEKS i PODAJ POZYCJĘ

Sprawdźmy, 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ści – tym samy wszystko działa jak powinno.

Gotowy przykład

Ale po co mi te kolumny pomocnicze?

Dokładnie tak, nie zawsze chcemy, by nam się wyświetlały w arkuszu, jest kilka możliwych rozwiązań tego problemu, można komórki ukryć, albo przenieś poza obszar ekranu. Ja jestem zwolennikiem usuwania takich danych jak najdalej od użytkowników i zazwyczaj umieszczam w osobnym arkuszu, więcej na ten temat w Kalkulator gotówki w Excelu – Stan Kasy – część 6.

Newsletter

Zapisz się do Neslettera i bądź na bieżąco

Plik do pobrania

Plik do przykładu, w którym wyszukujemy 3 największych wartości na przykładzie znalezienia zwycięzców konkursu. Wykorzystujemy funkcje MAX.K, INDEKS, PODAJ.POZYCJĘ, LICZ.JEŻELI

Plik wyświetla błędy, jeśli wpisana funkcja w a komórkę jest inna niż zaplanowałem dla danego przykładu.

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

Zobacz inne wpisy

Funkcje logiczne w Excelu

No comments yet! You be the first to comment.

Dodaj komentarz

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.