Funkcje INDEKS oraz PODAJ.POZYCJĘ, czyli wyszukiwanie po dowolnej kolumnie

Pracując w Excelu często przychodzi potrzeba przeprowadzenia wyszukiwania na określonym zakresie. Pierwsze rozwiązanie, które przychodzi do głowy to funkcja WYSZUKAJ.PIONOWO oraz WYSZUKAJ.POZIOMO. Świetnie! Ale co w momencie, w którym wyszukiwana wartość nie jest w pierwszej kolumnie bądź w pierwszym wierszu? Pojawia się problem. No oczywiście, można zmodyfikować tabelę i korzystać z powyższych funkcji, ale nie zawsze jest taka możliwość. Są sytuacje, w których tabela pod żadnym pozorem nie może być edytowana. I co wtedy? Z pomocą przychodzą dwie funkcje, które użyte razem są niesamowicie potężnym narzędziem. Mianowicie chodzi o funkcję INDEX() oraz PODAJ.POZYCJĘ(). Ale po kolei.

Funkcja INDEKS()

Funkcja INDEKS() zwraca wartość znajdująca się na przecięciu wiersza i kolumny w tablicy. Innymi słowy, należy w niej podać numer wiersza i kolumny, z której chcemy odczytać dane. Składnia tej funkcji jest następująca:

INDEKS(tablica; nr_wiersza; [nr_kolumny])

gdzie:

• tablica – to obszar, który ma zostać przeszukany.
• nr_wiersza – numer wiersza, z którego ma zostać zwrócona wartość.
• nr_kolumny – argument opcjonalny, odpowiadający za numer kolumny tablicy. W przypadku tablic jednowymiarowych, może on zostać pominięty.

Inną postacią funkcji INDEKS(), która ma zastosowanie podczas przeszukiwania wielu tablic jest następująca:

INDEKS(odwołanie; nr_wiersza; [nr_kolumny]; [nr_obszaru])

• odwołanie – argument odwołanie zwiera wszystkie obszary, które mają być brane pod uwagę podczas wyszukiwania; kolejne obszary musimy oddzielać od siebie średnikami, jednak aby Excel nie traktował ich jako kolejne argumenty funkcji INDEKS, musimy je ująć w nawias,
• nr_wiersza – numer wiersza, który ma być przeszukany.
• nr_kolumny – argument opcjonalny, odpowiadający za numer kolumny tablicy. W przypadku tablic jednowymiarowych, może on zostać pominięty.
• nr_obszaru – argument opcjonalny – przy pomocy liczby całkowitej określamy, który z obszarów ujętych w argumencie odwołanie ma zostać przeszukany; kolejność występowania obszarów w argumencie odwołanie wyznacza jego liczbę porządkową.

Przykładowo, chcąc odczytać, z tabeli poniżej, zysk z maja możemy wykorzystać funkcję INDEKS(), w której pierwszym argumentem jest cała tabela zawierająca kwoty, nr_wiersza przyjmuje wartość 3, ponieważ w trzeci wiersz odpowiada za zysk. Trzecim argumentem jest 5, ponieważ piąta kolumna odpowiada za miesiąc maj.

Przecięcie 3 wiersza i 5 kolumny wskazuje zysk z maja.

Funkcja PODAJ.POZYCJĘ()

Funkcja PODAJ.POZYCJĘ zwraca pozycję komórki w określonej tablicy, w której znajduje się dany ciąg znaków. Jej składnia jest następująca:

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

gdzie:

• szukana_wartość – wartość, która ma zostać odnaleziona w tablicy,
• przeszukiwana_tablica – obszar, w którym wyszukiwana jest wartość z argumentu szukana_wartość,
• typ_porównania – argument opcjonalny; określamy w jaki sposób ma być wyszukiwana wartość na tablicy; do wyboru mamy :
  • 1 mniejsze niż – wyszukana zostanie wartość dokładna lub w przypadku niepowodzenia pierwsza wartość, która jest mniejsza od szukanej; tak jak w innych przypadkach wyszukiwania wartości przybliżonej dobrze jest posortować dane w kolejności rosnącej przed wykorzystaniem funkcji, w przeciwnym przypadku wynik będzie nieprawidłowy; jeżeli argument typ_porównania zostanie pominięty jego wartość jest ustawiana jako 1,
  • 0 dokładne dopasowanie – wyszukiwana jest dokładna wartość, w przypadku niepowodzenia wyświetlany jest błąd,
  • -1 większe niż – w pierwszej kolejności wyszukiwana jest dokładna szukana_wartość, a następnie dopiero pod uwagę brana jest pierwsza wartość, która jest większa od szukanej; w tym przypadku również należy posortować dane w kolejności rosnącej przed przestąpieniem do wyszukiwania.

Przykładem użycia może być konieczność znalezienia komórki, w której jest zapisany styczeń.

Jak widać powyżej, argumentem szukana_wartość jest “styczeń”, przeszukiwaną_tablicą jest wiersz, w którym są zapisane miesiące, natomiast ostatnim argumentem jest 0, ponieważ interesuje nas dokładne dopasowanie.

Dobrana para

Funkcja INDEKS potrzebuje argumentów w postaci numeru wiersza i numeru kolumny. Jednakże, nie zawsze jest możliwość podania go wprost, jako konkretną wartość. Tutaj z pomocą może przyjść funkcja PODAJ.POZYCJĘ, które jest w stanie zwrócić wartość mogącą być argumentem funkcji INDEKS.

Ale jak to wygląda w praktyce? Otóż, jeśli mamy tabelę, w której każdy wiersz oznacza kolejno przychód, koszt przychodu i zysk. Kolumny natomiast reprezentują kolejne miesiące,  naszym zadaniem jest automatyczne wyświetlanie tych wartości w zależności od wybranego miesiąca w tabeli poniżej, to funkcje WYSZUKAJ.PIONOWO oraz WYSZUKAJ.POZIOMO w żaden sposób nie zdadzą egzaminu, ponieważ kolumny i wiersze nagłówkowe nie są przedstawione jako pierwsze!

Tutaj najwygodniejszym rozwiązaniem będzie zagnieżdżenie funkcji PODAJ.POZYCJĘ  w funkcji INDEKS.

Najbardziej zewnętrzną funkcją w powyższym przykładzie jest funkcja INDEKS, która jako pierwszy argument, przyjmuje obszar z kwotami. Został on zablokowany, aby można było przeciągnąć funkcję do kolejnych komórek w kolumnie. Drugi i trzeci argument funkcji INDEKS, to funkcja PODAJ.POZYCJĘ szukająca odpowiednio miesiąca w wierszu 4  i rodzaju kwoty w kolumnie N. Taka forma wykorzystania funkcji INDEKS i PODAJ.POZYCJE jest bardzo elastyczna i daje naprawdę szereg różnych możliwości.

PODSUMOWANIE

Funkcja WYSZUKAJ.PIONOWO i WYSZUKAJ.POZIOMO są dwiema bardzo dobrze znanymi funkcjami, jednakże funkcje INDEKS i PODAJ.POZYCJE mają jedną bardzo dużą przewagę nad funkcjami WYSZUKAJ. Jest to mianowicie ogromna elastyczność, która nie wymusza konkretnego ułożenia tabeli. Ponadto funkcje INDEKS i PODAJ.POZYCJE działają szybciej.

Niestety, takie zagnieżdżanie często wywołuje strach wśród użytkowników Excela, ale naprawdę warto go przezwyciężyć, ponieważ korzyści płynące z takiego użycia są potężne..

Chcesz zdobyć wiedzę z Excela? Sprawdź nasze kursy informatyczne, oferujemy w nich między innymi kurs Excel oraz Kurs Excel z tabeli przestawnych. Sprawdź również poradnik o wybieraniu elementu z listy – funkcja WYBIERZ

Zobacz również:

Szkolenia: