Funkcja INDEKS podstawy

Funkcja INDEKS zwraca wartość na przecięciu określonego wiersza i kolumny w podanym zakresie komórek. Funkcja składa się z 3 argumentów przy czym 2 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’’

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’’ 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Ę podstawy

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 wartościami 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 trzeba 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.0221) wprowadzam funkcję:

=PODAJ.POZYCJĘ(D3;A1:12;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 daty mniejszej niż 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 a funkcja przybiera postać:

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

Zastosowanie funkcji Indeks i Podaj Pozycję w Excelu

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’’ – pozycja 4) i kolumny (opony zimowe – kolumna 3) wyszukać funkcją PODAJ.POZYCJĘ.

Komórkę G2 zmodyfikujemy do takiej postaci:

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’’ znajduje się na 4 pozycji zakresu komórek A1:A6
• PODAJ.POZYCJĘ(F2;A1:C3;0) – wynik funkcji PODAJ.POZYCJĘ wynosi 3
  • Słowo „zimowe” znajduje się na 3 pozycji zakresu A1:C3

Aby przekopiować komórki dla pozostałych wartości musimy zablokować zakresy danych i funkcja przybiera poniższą postać:

Więcej o tym jak działa blokowanie komórek znajdziesz w artykule Jak zablokować komórkę w Excelu. Znak $ w Formułach

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

Drugi wariant funkcji INDEKS w Excelu

Drugi wariant funkcji INDEKS zwraca odwołanie do komórki lezą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 4 argumentów przy czym 3 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 wariant funkcji INDEKS w Excelu

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’’. Funkcja przybiera taką postać:

Funkcja INDEKS jest zbudowana z 4 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 w 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’’ – 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 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 Excel – 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.

Tutaj pobierz plik do tego ćwiczenia:

W pliku dołączyłem zadanie do samodzielnego wykonania.

Kurs Excel

Chcesz dobrze nauczyć się Excela ? Nie trać więcej czasu na rzeczy które możesz wykonać jednym kliknięciem. Prawie 10 godzin praktycznej wiedzy, w której przedstawiam najprzydatniejsze możliwości Excela. Wszystkie 72 lekcje wideo oparte są na przykładach podsumowanych w ebooku. Dodatkowo 62 zadania do samodzielnego wykonania.

Zobacz mój kurs Excel