INDEKS i PODAJ.POZYCJĘ dobre połączenie do wyszukiwania

Myślę, ze kiedy pierwszy raz zobaczyłem połączenie funkcji INDEKS i PODAJ.POZYCJĘ to zareagowałem podobnie jak wtedy kiedy pierwszy raz zobaczyłem WYSZUKAJ.PIONOWO. Podejście jak pies do jeża – nie była to miłość od pierwszego użycia. Większość użytkowników, którzy znają i wykorzystują funkcję WYSZUKAJ.PIONOWO, gdy widzi połączenie funkcji INDEKS i PODAJ.POZYCJĘ najczęściej pyta: ale po co sobie komplikować życie, przecież to samo robi przecież jedna funkcja, a nie dwie – WYSZUKAJ.PIONOWO. I trudno się z nimi nie zgodzić, gdyż  po części mają rację. Jednak osoby, które najczęściej to mówią nie wiedzą, że te dwie funkcje pokonują ograniczenia, które posiada WYSZUKAJ.PIONOWO czy WYSZUKAJ.POZIOMO.

Ten zestaw funkcji daję radę z takimi ograniczeniami, jak:

• Może przeszukiwać dane zarówno w poziomie jak i w pionie,
• zwraca dane, które są po lewej stronie wyszukiwania,
• jeśli dodasz lub usuniesz kolumnę nie musisz poprawiać formuły,
• kopiowana formuła nie musi być edytowana,
• jest szybsza, ale widać to na dużych zestawach danych.

Zastąpmy więc funkcją INDEKS i PODAJ POZYCJĘ funkcję WYSZUKAJ.PIONOWO

Pierwsza niedoskonałość funkcji WYSZUKAJ PIONOWO – kopiowanie

W tabeli wyszukiwania mam wpisane nazwisko Leśny oraz markę samochodu, jeśli teraz będę chciał wykorzystać funkcje WYSZUKAJ PIONOWO to mogę ją napisać na nowo, bądź skopiować jak zazwyczaj robię, by sobie życie uprościć. Zaznaczam więc komórkę J10, następnie przesuwam kursor w prawy dolny róg komórki i kiedy kursor zmieni się w czarny krzyżyk, to naciskam lewy przycisk myszy i przeciągam na pole K10. Formuła została skopiowana i od razu widać niedoskonałość funkcji WYSZUKAJ PIONOWO, wynik jest dokładnie taki sam. Żeby wynik był prawidłowy należałoby teraz formułę poprawić. Zostawmy to tak jak jest. Zaznaczmy zakres I10:K10 i skopiujmy go w dół o jeden wiersz. W tym celu kursor przesuńmy w prawy dolny róg ostatniej prawej zaznaczonej komórki K10 i po zmianie kursora na czarny krzyżyk naciśnijmy lewy przycisk myszy i przeciągnijmy o jeden wiersz w dół. Usuńmy formuły z obszaru J11:K11.

INDEKS i PODAJ POZYCJĘ lepiej znoszą kopiowanie

Zacznijmy więc w komórce J11 wpisywać naszą formułę =INDEKS(C9:C13;PODAJ.POZYCJĘ($I$11;$B$9:$B$13;0))

W funkcji INDEKS podajemy w pierwszej kolejności zakres C9:C13, z którego chcemy otrzymać wynik, w naszym przypadku markę samochodu. Adresowanie względne, gdyż w kolejnym kroku będziemy kopiować formułę i chcemy, by dane były pobierane z kolumny obok. Drugi argument, który musimy podać to nr wiersza, z którego ma być podana wartość. W tym przypadku używamy funkcji PODAJ POZYCJĘ, która z kolei posiada trzy argumenty, najpierw podajemy czego szukamy, czyli danych z komórki $I$11 – nazwiska, w drugim argumencie podajemy zakres, który przeszukujemy pod katem pierwszego argumentu, czyli kolumnę z nazwiskami $B$9:$B$13. Pozostał trzeci argument, w którym podobnie jak w funkcji WYSZUKAJ PIONOWO wskazujemy czy wynik ma być dokładny, nasz ma być dokładny więc wpisujemy 0. Wspomnę tylko tutaj, że ten argument ma 3, a nie 2 możliwości jak to jest w przypadku WYSZUKAJ PIONOWO – 1, 0 lub -1, jeśli decydujemy się na pozycję 0 to przeszukiwany obszar nie musi być sortowany.

Teraz kopiujemy formułę do komórki obok i jak widzimy od razu mamy poprawne dane, czego nie udało się zrobić z  funkcja WYSZUKAJ PIONOWO.

Druga niedoskonałość funkcji WYSZUKAJ PIONOWO – nowa kolumna

Zróbmy psikusa funkcji WYSZUKAJ PIONOWO i zaznaczmy obszar C8:C13, następnie:

1. naciśnijmy prawy przycisk myszy i wybierzmy z menu kontekstowego Wstaw…
2. zaznacz Przesuń komórki w prawo i OK,
3. w nowej kolumnie dopiszmy imiona,
4. jak zobaczymy w wyszukiwarce, tam gdzie jest wykorzystywana funkcja WYSZUKAJ PIONOWO jest w tej chwili wpisane imię, a tam gdzie kombinacja funkcji INDEKS i PODAJ POZYCJĘ są prawidłowe dane

INDEKS wraz z PODAJ POZYCJĘ pokonują trzecią niedoskonałość funkcji WYSZUKAJ PIONOWO – przeszukiwanie po lewej stronie

To zróbmy jeszcze jednego psikusa i zacznijmy wyszukiwać po imieniu, chcielibyśmy, by po wpisaniu imienia, pokazywało się nazwisko i marka samochodu.

5. Zmieniamy więc na początku dane w naszej wyszukiwarce,
6. Następnie edytujemy formułę WYSZUKAJ PIONOWO: =WYSZUKAJ.PIONOWO($J$10;$C$9:$E$13;-1;0) zmieniamy zakres, w którym szukamy imienia oraz chcielibyśmy, by było pokazywane nazwisko, które jest po lewej stronie od przeszukiwanego zakresu,
7. niestety to nie zadziała,
8. zmieńmy teraz zakresy w funkcji INDEKS i PODAJ.POZYCJĘ na: =INDEKS(B9:B13;PODAJ.POZYCJĘ($I$11;$C$9:$C$13;0))
9. jak widać w tym przypadku tu to działa.

Mam nadzieję, że chociaż trochę zaciekawiłem tym zestawem funkcji. Na pewno ciężko będzie ją wdrożyć, jeśli pracujemy na arkuszach, z których korzystają inni, którzy nie znają i nie będą chcieli poznać tego zestawu. Jeśli jednak możemy sobie na to pozwolić to zachęcam do wysiłku, nie jest tak trudna do zrozumienia dla średnio-zaawansowanych użytkowników Excela, a miło się z nią pracuje.