W poszukiwaniu wartości najbliższej

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.

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.

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.

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.

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

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

Tagged as: Formuła tablicowa, Indeks, Licz.Jeżeli, Moduł.Liczby, Podaj.Pozycję, Wiersz