Funkcja INDEKS i PODAJ.POZYCJĘ w Excelu

Masz cennik, w którym szukana wartość stoi po lewej stronie kolumny z nazwą, a WYSZUKAJ.PIONOWO (VLOOKUP) patrzy tylko w prawo. Albo ktoś dołożył kolumnę w środku tabeli i formuła zaczęła pokazywać cenę nie tego produktu, co trzeba. Albo po prostu masz dwa warunki naraz: średnicę i rodzaj opony, a jedna funkcja wyszukująca ich nie obsłuży.

W tym poradniku przechodzimy duet INDEKS (INDEX) i PODAJ.POZYCJĘ (MATCH) po kolei: najpierw każda z tych funkcji osobno, potem obie razem w jednej formule, a na końcu drugi wariant INDEKS, który sięga do kilku tabel jednocześnie. Każdy krok na tym samym cenniku opon, ze zrzutem z Excela i gotową formułą do skopiowania.

Z jednej rzeczy bierze się cała przewaga tego duetu: INDEKS nie szuka niczego. Dostaje numer wiersza i numer kolumny, i zwraca to, co stoi na ich przecięciu. Szukaniem zajmuje się PODAJ.POZYCJĘ, podając te dwa numery. Dlatego kierunek wyszukiwania przestaje mieć znaczenie.

W dalszej części poradnika:

1. Funkcja INDEKS - budowa i argumenty
2. Przykład zastosowania funkcji INDEKS
3. Funkcja PODAJ.POZYCJĘ - budowa i argumenty
4. Przykład zastosowania funkcji PODAJ.POZYCJĘ
5. PODAJ.POZYCJĘ z wyszukiwaniem najbliższej wartości
6. INDEKS i PODAJ.POZYCJĘ w jednej formule
7. Dlaczego INDEKS zamiast WYSZUKAJ.PIONOWO
8. INDEX i MATCH, czyli te same funkcje po angielsku
9. Drugi wariant funkcji INDEKS
10. Przykład zastosowania drugiego wariantu
11. Podsumowanie

Funkcja INDEKS - budowa i argumenty

Funkcja INDEKS zwraca wartość na przecięciu określonego wiersza i kolumny w podanym zakresie komórek. Funkcja składa się z trzech argumentów, przy czym dwa są wymagane.

INDEKS(tablica; numer_wiersza; [numer_kolumny])

• tablica (wymagana) - zakres komórek lub tablica.
• numer_wiersza (wymagany -  chyba, że podany jest argument numer_kolumny) - wybiera wiersz w tablicy, z którego ma zostać zwrócona wartość.
• numer_kolumny (wymagany gdy nie podano argumentu numer_wiersza) - wybiera kolumnę w tablicy, z której ma zostać zwrócona wartość.

Funkcja INDEKS występuje też w drugim wariancie o czym opowiem w dalszej części artykułu.

Przykład zastosowania funkcji INDEKS w Excelu

Działanie funkcji INDEKS przedstawię na poniższym przykładzie.

Po stronie lewej mamy białą tabelę z cenami różnych typów i wielkości opon samochodowych.

Po stronie prawej w tabeli niebieskiej chcielibyśmy uzyskać ceny dla produktów np. w komórce G2 dla opon zimowych o średnicy 17 cali

Wobec tego w komórce G2 wprowadzamy funkcję:

=INDEKS(A1:C6;4;3)

• pierwszym argumentem funkcji jest tablica czyli zakres komórek od A1 do C6
• drugi argument to numer wiersza - średnica 17 cali występuje w 4 wierszu zakresu A1:C6
• trzeci argument to numer kolumny - opony zimowe znajdują się w 3 kolumnie zakresu A1:C6

A wynikiem tej funkcji jest wartość 215 zł.

Wiemy już jak działa funkcja INDEKS, w dalszej części artykułu pokażę jak ją zmodyfikować aby automatycznie pobierała numer wiersza i kolumny z danych. Do tego potrzebujemy poznać funkcję PODAJ.POZYCJĘ.

Funkcja PODAJ.POZYCJĘ - budowa i argumenty

Funkcja PODAJ.POZYCJĘ zwraca pozycję (numer) wyszukiwanego elementu w zakresie komórek.

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

• szukana_wartość (wymagany) - wartość, którą chcesz odnaleźć w przeszukiwanym zakresie danych
• przeszukiwana_tablica (wymagany) - zakres komórek, który zostanie przeszukiwany
• typ_porównania - jest liczbą -1, 0 lub 1. Argument określa sposób dopasowania do wartości w zakresie danych. Wartość domyślna tego argumentu to 1. Gdy typ porównania równy jest:
  • 1 -  funkcja PODAJ.POZYCJĘ znajdzie największą wartość, która jest mniejsza lub równa szukanej wartości. Wartości w przeszukiwanym zakresie danych muszą być uporządkowane w kolejności rosnącej.
  • 0 - funkcja PODAJ.POZYCJĘ znajdzie pierwszą wartość, która jest dokładnie równa szukanej wartości. W tym przypadku wartości w zakresie danych mogą być ułożone w dowolnej kolejności.
  • -1 - funkcja PODAJ.POZYCJĘ znajdzie najmniejszą wartość, która jest większa lub równa szukanej wartości. Wartości w przeszukiwanym zakresie danych muszą być uporządkowane w kolejności malejącej.

Jeśli wyszukiwanie za pomocą funkcji PODAJ.POZYCJĘ zakończy się niepowodzeniem, zwracana jest wartość błędu #N/D.

Przykład zastosowania funkcji PODAJ.POZYCJĘ

Na podstawie zakresu z datami A1:A12 chcemy podać pozycję, na której znajduje się data 21.06.2021r. Pozycję tą podamy w niebieskiej komórce E2

W komórce E2 wprowadzamy funkcję PODAJ.POZYCJĘ z trzema argumentami:

=PODAJ.POZYCJĘ(D2;A1:A12;0)

• D2 - wskazuje szukaną datę (21.06.2021)
• A1:A12 - jest przeszukiwanym zakresem danych
• 0 - oznacza, że szukamy w zakresie dokładnie takiej daty

Funkcja PODAJ.POZYCJĘ zwróciła nam wynik 10, który mówi o tym że szukana wartość występuje na 10 pozycji w całym zakresie danych.

Dla drugiej daty (23.07.2021) funkcja zwróci nam błąd, ponieważ taka data nie występuje. Ale możemy wyszukać datę najbardziej zbliżoną od góry lub od dołu. O tym jak to zrobić przeczytasz w kolejnym dziale.

TIP: Funkcję PODAJ.POZYCJĘ możesz zastosować też do wyszukiwania tekstu ale pamiętaj, że nie rozróżnia ona wielkich i małych liter.

PODAJ.POZYCJĘ z wyszukiwaniem najbliższej wartości

Funkcja PODAJ.POZYCJĘ pozwala też na wyszukiwanie najbardziej zbliżonej wartości. Przedstawię to na przykładzie:

Po lewej stronie ułożyłem daty w kolejności od najstarszej do najnowszej, (tym razem data 21.06.2021 znajduje się na 5 pozycji).

W niebieskiej komórce E3 (odpowiadającej dacie 23.07.2021) wprowadzam funkcję:

=PODAJ.POZYCJĘ(D3;A1:A12;1)

• D3 - wskazanie wartości tj. data 23.07.2021
• A1:A12 - przeszukiwany zakres dat ułożony w kolejności od najstarszej do najnowszej
• 1 - oznacza, że szukamy największej daty, która jest mniejsza lub równa 23.07.2021

TIP: Excel traktuje daty w Excelu jako liczby, w tym przykładzie - liczby ułożone są w kolejności od najmniejszej do największej. Jeśli chcesz dowiedzieć się więcej o datach w Excelu zobacz artykuł na Data w Excelu, Funkcja Dziś, Formatowanie Daty.

Wynikiem funkcji jest 7, jest to pozycja odpowiadająca dacie najbardziej zbliżonej ale mniejszej od daty 23.07.2021

Jeśli chcielibyśmy odnaleźć datę najbardziej zbliżoną do 23.07.2021, ale większą od niej, w trzecim argumencie funkcji PODAJ.POZYCJĘ wpisujemy -1 i funkcja przybiera postać:

=PODAJ.POZYCJĘ(D3;A1:A12;-1)

W tym przypadku zakres dat musi być ułożony w kolejności od najnowszych do najstarszych.

Funkcja zwróci wartość 6 - jest to pozycja najbardziej zbliżona większa od daty 23.07.2021

INDEKS i PODAJ.POZYCJĘ w jednej formule

Wracamy do naszego początkowego przykładu wyszukiwania rodzaju opon na podstawie cennika. Nauczyliśmy się jak działa funkcja INDEKS. Numer wiersza i kolumny wprowadziliśmy ręcznie - teraz zmodyfikujemy tę funkcję tak aby numer wiersza (średnica 17 cali - pozycja 4) i kolumny (opony zimowe - kolumna 3) wyszukać funkcją PODAJ.POZYCJĘ.

Komórkę G2 zmodyfikujemy do takiej postaci:

=INDEKS(A1:C6;PODAJ.POZYCJĘ(E2;A1:A6;0);PODAJ.POZYCJĘ(F2;A1:C1;0))

Funkcja INDEKS składa się z trzech argumentów:

• A1:C6 - Przeszukiwany zakres komórek danych (cennik opon)
• PODAJ.POZYCJĘ(E2;A1:A6;0) - wynik funkcji PODAJ.POZYCJĘ wynosi 4
  • Średnica 17 cali znajduje się na 4 pozycji zakresu komórek A1:A6
• PODAJ.POZYCJĘ(F2;A1:C1;0) - wynik funkcji PODAJ.POZYCJĘ wynosi 3
  • Słowo „zimowe” stoi na trzeciej pozycji w wierszu nagłówków A1:C1. ⚠ To musi być pojedynczy wiersz, a nie cały blok danych - PODAJ.POZYCJĘ przeszukuje wyłącznie jeden wiersz albo jedną kolumnę.

Żeby przekopiować formułę na pozostałe wiersze, musimy zablokować zakresy znakiem dolara (klawisz F4). Funkcja przybiera wtedy taką postać:

=INDEKS($A$1:$C$6;PODAJ.POZYCJĘ(E2;$A$1:$A$6;0);PODAJ.POZYCJĘ(F2;$A$1:$C$1;0))

Zablokowane zostały trzy zakresy: cały cennik oraz oba zakresy przeszukiwane przez PODAJ.POZYCJĘ. Komórki E2 i F2 zostają względne, bo przy kopiowaniu w dół mają wędrować razem z wierszem.

I wynik połączenia funkcji INDEKS i PODAJ.POZYCJĘ przedstawia się następująco:

Dlaczego INDEKS zamiast WYSZUKAJ.PIONOWO

Duet INDEKS i PODAJ.POZYCJĘ robi to samo co WYSZUKAJ.PIONOWO, tylko bez trzech jego ograniczeń.

• Szuka w obie strony. WYSZUKAJ.PIONOWO znajduje wartość w pierwszej kolumnie zakresu i zwraca coś z kolumny po prawej. Jeśli kolumna z wynikiem stoi na lewo od kolumny z kluczem, trzeba przestawiać dane. INDEKS nie ma pojęcia o kierunku - dostaje numer wiersza i numer kolumny.
• Nie psuje się po dodaniu kolumny. W WYSZUKAJ.PIONOWO numer kolumny wpisujesz na sztywno. Ktoś wstawia kolumnę w środku tabeli i formuła dalej liczy, tylko zwraca dane z sąsiedniej kolumny - bez żadnego błędu. Tutaj numer kolumny wylicza PODAJ.POZYCJĘ z nagłówka, więc przesunięcie nic nie zmienia.
• Czyta tylko dwie kolumny zamiast całego bloku. Przy dużych arkuszach to różnica w czasie przeliczania, bo WYSZUKAJ.PIONOWO trzyma w pamięci cały zakres.

Cena jest jedna: formuła jest dłuższa i trzeba pilnować, który zakres gdzie trafia. Jeśli dopiero zaczynasz, zacznij od funkcji WYSZUKAJ.PIONOWO, a po ten duet sięgnij wtedy, gdy WYSZUKAJ.PIONOWO przestanie wystarczać.

🚀 Następny poziom

Wyszukiwanie w Excelu bez ograniczeń WYSZUKAJ.PIONOWO

✓Lekcje wideo: 40

✓Ćwiczenia z plikami: 17

✓Dostęp bez terminu, także do aktualizacji

✓Gwarancja zwrotu: 30 dni

Zobacz program kursu

🛡 Gwarancja zwrotu 30 dni · ⚡ Dostęp natychmiastowy

INDEX i MATCH, czyli te same funkcje po angielsku

W anglojęzycznych poradnikach ten duet nazywa się INDEX MATCH. To dokładnie te same funkcje:

• INDEX to polskie INDEKS,
• MATCH to polskie PODAJ.POZYCJĘ.

Nazwy funkcji Excel tłumaczy sam, zależnie od języka interfejsu. Czego nie tłumaczy, to separator argumentów. Formuła przepisana z angielskiego poradnika wygląda tak: =INDEX(A1:C6,MATCH(E2,A1:A6,0),MATCH(F2,A1:C1,0)) i w polskim Excelu nie zadziała, bo polska wersja oczekuje średników: =INDEKS(A1:C6;PODAJ.POZYCJĘ(E2;A1:A6;0);PODAJ.POZYCJĘ(F2;A1:C1;0)).

Jeśli po wklejeniu formuły z internetu Excel pokazuje #NAZWA?, w dziewięciu przypadkach na dziesięć chodzi właśnie o angielską nazwę albo o przecinek w miejscu średnika.

Drugi wariant funkcji INDEKS w Excelu

Drugi wariant funkcji INDEKS zwraca odwołanie do komórki leżącej na przecięciu określonego wiersza i kolumny. Jeśli odwołanie zawiera nieprzylegające obszary, można wybrać obszar, który ma zostać uwzględniony. Funkcja składa się z czterech argumentów, przy czym trzy są wymagane.

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

• odwołanie (wymagane). Odwołanie do co najmniej jednego zakresu komórek.
  • Jeśli odwołanie dotyczy nieprzylegających zakresów, należy je ująć w nawiasy.
  • Jeśli każdy obszar w argumencie odwołanie zawiera tylko jeden wiersz lub kolumnę, to odpowiednio argumenty nr_wiersza lub nr_kolumny są opcjonalne.
• numer_wiersza (wymagany -  chyba, że podany jest argument numer_kolumny) - wybiera wiersz w tablicy, z którego ma zostać zwrócona wartość.
• numer_kolumny (wymagany gdy nie podano argumentu numer_wiersza) - wybiera kolumnę w tablicy, z której ma zostać zwrócona wartość.
• nr_obszaru - określa zakres w odwołaniu, z którego zostanie zwrócone przecięcie argumentów nr_wiersza i nr_kolumny. Pierwszy zaznaczony lub wprowadzony obszar ma numer 1, drugi obszar ma numer 2 itd.

Przykład zastosowania drugiego wariantu funkcji INDEKS

Nawiązując do przykładu z cennikiem opon. Tym razem dochodzi kolejny argument jakim jest producent. Mamy tutaj cennik dla opon fulida oraz kromoran o różnych średnicach i rodzajach.

W tym przypadku świetnie sprawdzi się drugi wariant funkcji INDEKS

Funkcję wprowadzamy w komórce H2 i chcemy poznać cenę opon zimowych marki „fulida” o średnicy 17 cali. Funkcja przybiera taką postać:

=INDEKS((A2:C7;A10:C15);PODAJ.POZYCJĘ(F2;A2:A7;0);PODAJ.POZYCJĘ(G2;A2:C2;0);JEŻELI(E2="fulida";1;2))

Funkcja INDEKS jest zbudowana z czterech argumentów:

• (A2:C7;A10:C15) - pierwszym argumentem są dwa zakresy danych pierwszy dla opon „fulida” a drugi dla „kromoran” zakresy muszą być ujęte we wspólny nawias i przedzielone średnikiem. W tym przykładzie istotne jest aby oba cenniki miały takie same wymiary, nazwy nagłówków i wierszy.
• PODAJ.POZYCJĘ(F2;A2:A7;0) - drugi argument czyli numer wiersza dla średnicy 17 cali - argument zwróci liczbę 4 ponieważ ta liczba jest na czwartej pozycji zakresu A2:A7.
• PODAJ.POZYCJĘ(G2;A2:C2;0) - trzeci argument przeszukuje zakres A2:C2 w poszukiwaniu słowa „zimowe” i zwróci liczbę 3
• JEŻELI(E2="fulida";1;2) - czwarty argument funkcja JEŻELI (IF) sprawdza z którego obszaru chcemy pobrać cenę. W naszym przykładzie poszukujemy marki „fulida” więc test logiczny funkcji JEŻELI jest prawdziwy i zwrócona zostanie wartość 1. Więcej o funkcji JEŻELI dowiesz się z artykułu Funkcja JEŻELI w Excelu - JEŻELI z funkcjami ORAZ i LUB

Po zablokowaniu zakresów danych, możemy przekopiować funkcję dla innych pozycji i otrzymamy poniższy wynik.

Na czerwono zaznaczyłem inną pozycję do weryfikacji poprawności

UWAGA! W poniższym pliku dołączyłem ciekawe zadanie do zastosowania funkcji INDEKS i PODAJ.POZYCJĘ do samodzielnego wykonania.

W pliku dołączyłem zadanie do samodzielnego wykonania. Napisz w komentarzu wynik.

Podsumowanie

Cały duet sprowadza się do podziału pracy: PODAJ.POZYCJĘ ustala numery, INDEKS podaje wartość z ich przecięcia. Co warto zapamiętać:

• Trzeci argument PODAJ.POZYCJĘ nie jest ozdobą. Zero oznacza dopasowanie dokładne i działa na nieposortowanych danych. Jedynka i minus jedynka wymagają posortowanego zakresu - rosnąco przy 1, malejąco przy -1. Pominięcie tego argumentu daje domyślną jedynkę, czyli cichy zły wynik na nieposortowanej liście.
• PODAJ.POZYCJĘ przeszukuje jeden wiersz albo jedną kolumnę. Podanie bloku komórek (na przykład A1:C3 zamiast A1:C1) kończy się błędem #N/D.
• Zakresy blokuj przed kopiowaniem. Zakres przeszukiwany ma być bezwzględny, a komórka z szukaną wartością względna. Pomyłka tutaj też nie daje błędu, tylko wyniki przesunięte o kilka wierszy.
• Numery wierszy liczą się od początku zakresu, nie od początku arkusza. W zakresie A2:C7 wiersz pierwszy to wiersz drugi arkusza. Stąd biorą się wyniki przesunięte dokładnie o jeden.
• Drugi wariant INDEKS przyda się tylko wtedy, gdy masz kilka tabel o identycznym układzie. Przy jednej tabeli nie ma po co go używać.

🚀 Następny poziom

Przerobiłeś INDEKS i PODAJ.POZYCJĘ. Co dalej?

✓Lekcje wideo: 40

✓Ćwiczenia z plikami do każdego działu

✓Certyfikat po ukończeniu

✓Kursantów: ponad 9 000

Zobacz kurs Excel Średniozaawansowany

🛡 Gwarancja zwrotu 30 dni · ⚡ Dostęp natychmiastowy