Alternatywa dla WYSZUKAJ.PIONOWO

Funkcja WYSZUKAJ.PIONOWO jest bardzo często wykorzystywaną funkcją, która wyszukuje zadaną wartość w pierwszej kolumnie zakresu i zwraca wartość z tego samego zakresu z kolumny o numerze określonym przez użytkownika. Alternatywą dla tej funkcji jest kombinacja funkcji INDEKS oraz PODAJ.POZYCJĘ o czym powie wam szkolenie Excel średniozaawansowany.

Funkcja INDEKS jest to funkcja, która zwraca wartość elementu w tablicy określonego przez odpowiedni numer wiersza i kolumny (jest też możliwość użycia funkcji INDEKS w wersji odwołaniowej, jednak w tym przypadku omówimy i wykorzystamy wersję tablicową tej funkcji). Składnia tej funkcji jest następująca:

=INDEKS(tablica;nr_wiersza;nr_kolumny)

tablica – zakres komórek, wśród których będziemy szukać konkretnego wiersza i konkretnej kolumny. Jeśli tablica ma tylko jeden wiersz lub tylko jedną kolumnę, to odpowiedni kolejny argument (nr_wiersza lub nr_kolumny) staje się argumentem opcjonalnym.

nr_wiersza – numer wiersza w tablicy, z którego ma być zwrócona wartość,

nr_kolumny – numer kolumny w tablicy, z której ma być zwrócona wartość.

Jeśli jeden z dwóch powyższych argumentów otrzyma wartość zero, to wówczas funkcja INDEKS zwróci nam tablicę składającą się z całego wiersza lub całej kolumny (wtedy jednak trzeba ją wywołać jako funkcję tablicową, o czym przeczytacie w osobnym artykule). Zobaczmy teraz na przykładzie, jak działa funkcja INDEKS.

W wyniku tak wprowadzonej formuły otrzymamy wartość z komórki C10 (dziesiąty wiersz i trzecia kolumna).

Funkcja PODAJ.POZYCJĘ jest to funkcja, która zwraca względną pozycję danego elementu w tablicy. Jej składnia jest następująca:

=PODAJ.POZYCJĘ(szukana_wartość;przeszukiwana_tab;[typ_porównania])

szukana_wartość – jest to wartość, którą będziemy wyszukiwać

przeszukiwana_tab – tablica, w której będziemy wyszukiwać konkretną wartość

typ_porównania – argument ten może przyjmować trzy wartości: 1 gdy szukamy największej wartości mniejszej lub równej argumentowi szukana_wartość, 0 dla dokładnego dopasowania, -1 gdy szukamy najmniejszej wartości większej lub równej argumentowi szukana_wartość. Gdy wybieramy typ porównania 1, wtedy tablica musi być posortowana rosnąco, natomiast gdy wybieramy typ -1 – tablica musi być posortowana malejąco.

Zobaczmy teraz przykład wykorzystania funkcji PODAJ.POZYCJĘ w praktyce.

W wyniku tak wprowadzonej formuły otrzymamy względny numer wiersza w zaznaczonym zakresie, w którym znajduje się wartość z komórki G4 (czyli otrzymamy 11).

Za pomocą kombinacji funkcji INDEKS oraz PODAJ.POZYCJĘ będziemy mogli wyszukiwać wartości znajdujące się w dowolnej kolumnie przeszukiwanego zakresu (a niekoniecznie w pierwszej jak to było w przypadku funkcji WYSZUKAJ.PIONOWO).

Działanie takich zagnieżdżonych funkcji zobaczymy na przykładzie.

Mając podaną nazwę wydatku (komórka H4) będziemy chcieli znaleźć kategorię, do jakiej został zaklasyfikowany (w kolumnie A).

Ponieważ wydatku będziemy szukać w kolumnie trzeciej przeszukiwanego zakresu, nie możemy wykorzystać do tego celu funkcji WYSZUKAJ.PIONOWO.

Najpierw więc za pomocą funkcji PODAJ.POZYCJĘ znajdziemy względną pozycję naszego wydatku w kolumnie „Wydatek”, a następnie za pomocą funkcji INDEKS znajdziemy wartość o identycznej względnej pozycji w kolumnie „Kategoria”.

Wpisana formuła będzie więc wyglądać następująco:

=INDEKS(A1:D14;PODAJ.POZYCJĘ(G4;C1:C14;0);1)

W wyniku jej działania otrzymamy kategorię wydatku znalezioną w pierwszej kolumnie rozważanego zakresu.

Mimo, że składnia tak zagnieżdżonych funkcji jest bardziej skomplikowana niż składnia funkcji WYSZUKAJ.PIONOWO, to jest to formuła całkowicie uniwersalna i nie wymaga ułożenia kolumn w określonej kolejności.