W poszukiwaniu wartości najbliższej

by Marcin

Dzisiaj będziemy poszukiwać komórki z wartością najbliższą jakiejś wybranej przez użytkownika liczbie (mniejszej, większej, równej – bez znaczenia. Ważne, aby była jak najbliższa wartości poszukiwanej).

Zaczniemy od prostej tabelki, w której mam listę imion oraz odpowiadających im punktów.

Excel - Poszukiwanie najbliższej wartości

Chciałbym, aby Excel zwrócił mi liczbę punktów najbliższą liczbie wpisanej do innej komórki skoroszytu oraz imię odpowiadające tej liczbie punktów.

Excel - Poszukiwanie najbliższej wartości

Rozwiązanie jest zupełnie proste.

Jeden z prostszych sposobów na rozwiązanie problemu to wykorzystanie kolumny pomocniczej. W komórkach tej kolumny znajdzie się wartość bezwzględna różnicy poszukiwanej wartości oraz liczby punktów z listy.

=MODUŁ.LICZBY($K$7-E19)

Rozwiązanie naszego problemu znajdzie się oczywiście w wierszu, w którym ta wartość jest najmniejsza.

Excel - Poszukiwanie najbliższej wartości

W celu wybranie odpowiedniej wartości oraz odpowiadającego jej imienia wystarczy użyć, odpowiednio, funkcji:

=INDEKS(E19:E37;PODAJ.POZYCJĘ(MIN(G19:G37);G19:G37;0))

oraz

=INDEKS(D19:D37;PODAJ.POZYCJĘ(MIN(G19:G37);G19:G37;0))

Gdzie kolumna “G” to nasza kolumna pomocnicza, a kolumna „D” to kolumna z imionami. Od razu dodam (dla porządku), że kolumna „E” to kolumna z liczbą punktów.

Rozwiązanie „hardcore” z wykorzystaniem formuł tablicowych (dla zapaleńców oraz tych, którzy chcą sobie po prostu przetrenować formuły tablicowe)

Wyszukanie odpowiedniej wartości

{=INDEKS(E19:E37;PODAJ.POZYCJĘ(PRAWDA;(MODUŁ.LICZBY($L$7-E19:E37)=MIN(MODUŁ.LICZBY($L$7-E19:E37)));0))}

oraz odpowiadającego jej imienia:

{=INDEKS(D19:D37;PODAJ.POZYCJĘ(PRAWDA;(MODUŁ.LICZBY($L$7-E19:E37)=MIN(MODUŁ.LICZBY($L$7-E19:E37)));0))}

Co zrobić, jeżeli liczba zdobytych punktów się powtarza?

Dwa sposoby, które pokazałem powyżej zwracają tylko jedną wartość. Kiedy więc kilku osobom przypisana jest ta sama liczba punktów formuły zwrócą tylko imię pierwszej osoby z listy.

W jaki więc sposób możesz zmusić Excela do zwrócenia listy wszystkich imion z interesująca nas ilością punktów ?

Mam dla Was dwa rozwiązania wykorzystujące kolumnę pomocniczą. Pierwsze bez, a drugie z wykorzystaniem formuł tablicowych.

Najpierw przygotujmy sobie kolumnę pomocniczą. W pierwszej komórce znajdzie się formuła:

=JEŻELI(MODUŁ.LICZBY($K$7-E19)=MIN($G$19:$G$37);WIERSZ();””)

którą następnie przeciągamy (kopiujemy) na inne komórki kolumny pomocniczej.
Formuła ma za zadanie zwrócić numer wiersza, w którym znajduje się wartość najbliższa poszukiwanej. W przeciwnym razie zwraca spację.

Wynik działania formuły widać na rysunku poniżej.

Excel - Poszukiwanie najbliższej wartości

Bez użycia formuł tablicowych

Kolumna pomocnicza jest już gotowa, możemy wrócić do naszych poszukiwań. W celu otrzymania najbliższej wartości użyjemy formuły:

=INDEKS(E19:E37;PODAJ.POZYCJĘ(MIN(G19:G37);G19:G37;0))

W pierwszej komórce zakresu, w których chcesz mieć listę wszystkich imion wprowadź następnie formułę:

=INDEKS(D:D;MIN.K(H19:H37;1))

a w komórce poniżej, zwracająca kolejne imię, formułę:

=INDEKS(D:D;MIN.K($H$19:$H$37;(1+LICZ.JEŻELI($M$11:M11;”>=”&” „))))

którą następnie skopiuj w dół. W rezultacie wszystko wygląda mniej więcej tak, jak na rysunku poniżej. Jak widzisz, w wyniku działania pokazanych formuł, otrzymujesz listę wszystkich imion, spełniających poszukiwane kryterium

Excel - Poszukiwanie najbliższej wartości

Na koniec to samo, ale przy wykorzystaniu formuł tablicowych (korzystamy z opisanej wcześniej kolumny pomocniczej)

Zaznaczamy zakres komórek, w których chcemy mieć listę imion i stosujemy formułę:

{=INDEKS(D:D;MIN.K($H$19:$H$37;WIERSZ($A$1:INDEKS($A:$A;LICZ.JEŻELI(H19:H37;”>0″)))))}

Formuła zwróci dokładnie to, czego oczekujesz. We wszystkich „nadwymiarowych” komórkach formuła zwróci kod błędu. Łatwo to w razie potrzeby usunąć. Miałem to nawet zrobić, ale jestem prawie pewien, że podpowiedź jak to zrobić znajdziesz na moim blogu. Wszyscy zainteresowani na pewno sobie odszukają.

Jeżeli ktoś ma inne pomysły na rozwiązanie problemu – jak zwykle zapraszam. Czekam na maile i komentarze

Marcin

Download

{ 8 comments… read them below or add one }

eizo

Marcin jesteś WIELKI!
Chciałbym kiedyś zaznajomić się z Excelem tak jak Ty, mam nadzieję że niedługo znajdę trochę czasu i zgłębię Twoje wszystkie wpisy, bo naprawdę warto. Lubię Excela to bardzo przydatny program 🙂

krzysiek

podziwiam & dzięki za wysiłek! blog WYJĄTKOWO pożyteczny!

pozdro
krzysiek

m00ka

A mając poszukiwaną wartość 9? a w wynikach jest 8 i 11. To jak zrobić żeby zwracało tą większą wartość od zadanej (czyli 11)

Marcin

@m00ka
W takim przypadku nie wiem czy nie najprościej byłoby posortować sobie rosnąco tabelę z danymi, które chcesz przeszukiwać i skorzystać z funkcji PODAJ.POZYCJĘ z parametrem -1 ustawionym jako typ porównania. Jeżeli połączysz to sobie z funkcją INKEKS to w rezultacie otrzymasz zawsze wartośc większą od zadanej

Marcin

aorat

Witam serdecznie
A jeżeli chciałbym uzyskać taki efekt: przypuśćmy że imiona powtarzają się w kolumnie D i przypisane są im różne wartości w kolumnie E, chciałbym wiedzieć jakie są najbliższe wartości dla poszczególnych imion, przy zadaniu określonej wartości. Czyli podaję liczbę np. 100, w tabeli otrzymuję np wynik że: Marcin ma 101, Kasia ma 99, Zosia 120 itd….Jeżeli podam 10 to otrzymuję: Marcin ma 2, Kasia ma 10, Zosia ma 11..
Pozdrawiam Pana Marcina

wicked

A jak zrobić żeby podawał pierwszą większą liczbę od wskazanej?

voyager

nie umiem rozwiązać takiego problemu suma komórek najbliższa zadanej wartości?

Wojtek

Witam, Analizuję przypadek wyszukiwania więcej, niż jednego wyniku i zauważyłem pewien dziwny przypadek. Mianowicie liczba szukana to np. 100,51 podczas gdy dwie osoby wskazują wartości odpowiednio 100,55 oraz 100,47. Różnica w obu przypadkach wynosi tyle samo, tj. 0,04. Mimo to wynik pokazuje wyłącznie wyższą wartość (100,51), jako prawidłową. Dlaczego tak jest? Żeby było śmieszniej, jeśli poszukiwać będziemy wartości np. 100,50, a dwie osoby wskażą np. 100,55 oraz 100,45 (równa różnica 0,05) jest wszystko OK i wynik wskaże prawidłowo dwie osoby. Czy ktoś ma pomysły, dlaczego tak jest?

Leave a Comment

Previous post:

Next post: