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?

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