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
{ 8 comments… read them below or add one }
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 🙂
podziwiam & dzięki za wysiłek! blog WYJĄTKOWO pożyteczny!
pozdro
krzysiek
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)
@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
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
A jak zrobić żeby podawał pierwszą większą liczbę od wskazanej?
nie umiem rozwiązać takiego problemu suma komórek najbliższa zadanej wartości?
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?