P odstawowym zadaniem, które realizujefunkcja INDEKS, jest zwrócenie pojedyn-czej wartości (lub tablicy) z podanego

zbioru danych. Zbiorem może być w tym przy-padku zakres komórek, wpisana ręcznie tablicalub kilka niezależnych od siebie obszarów z da-nymi. INDEKS potrafi w wyniku zwrócić takżeadres, ale tę funkcjonalność omówimy w następ-nym opracowaniu.

Funkcja ta doskonale współpracuje z innąfunkcją wyszukiwania i adresu – PODAJ.PO-ZYCJĘ dlatego, że w pewnym sensie spełniaodwrotną rolę. PODAJ.POZYCJĘ, jak napisali-śmy w poprzednim artykule (nr 3/2010), okre-śla pozycję (kolejność) wystąpienia konkretnejwartości na jednowymiarowej tablicy danych; INDEKS natomiast zwraca w wyniku wartośćna podstawie jej pozycji (robi to za pomocądwóch argumentów Nr_wiersza i Nr_kolumny)– w przeciwieństwie do PODAJ.POZYCJĘ może operować na dwuwymiarowej tabeli danych.

Budowa funkcji – składnia tablicowa

Funkcja INDEKS posiada dwie składnie – ta-blicową (składającą się z trzech argumentów)i odwołaniową (zawierającą cztery parametry).Różnice dotyczą przede wszystkim pierwszegoargumentu funkcji – w wersji tablicowej może-my zaznaczyć (wpisać) tylko pojedynczą tablicędanych, w wersji odwołaniowej natomiast kilkaniepowiązanych ze sobą zakresów komórek.Składnia odwołaniowa zawiera także dodatkowyargument, który określa numer zakresu, do któ-

rego będziemy się odwoływać w formule. Zarów-no jedna jak i druga wersja może zwracać poje-dyncze wartości, fragmenty tablic, jak i całe ta-blice danych.

Prześledźmy budowę funkcji w wersji tablico-wej (z jedną tabelą danych). Tablica to po pro-stu tabela (zakres komórek w arkuszu) lub tabli-ca (wpisana ręcznie), z której chcemy pobrać da-ne. Nr_wiersza to numer wiersza tablicy. Nr_ko-lumny to argument opcjonalny (w sytuacji gdytabela zawiera więcej niż jedną kolumnę da-nych, należy podać konkretny numer kolumny,z której chcemy pobrać dane).

Jak widzimy na widoku 1:● funkcja działa prawidłowo zarówno dla tabli-cy jednowymiarowej (formuła 1, 2, 4 i 5) jaki dla tablicy dwuwymiarowej (formuła 3),● dla jednowymiarowej tablicy pionowej (for-muła 1) wymagany jest drugi argument funkcji(Nr_wiersza), natomiast trzeci argument(Nr_kolumny) jest opcjonalny (tablica zawieratylko jedną kolumnę – można wpisać także0 lub 1),● dla jednowymiarowej tablicy poziomej (for-muła 2) wymagany jest trzeci argument funkcji(Nr_kolumny), natomiast drugi argument(Nr_wiersza) jest opcjonalny (tablica zawieratylko jeden wiersz – można wpisać także 0lub 1),● funkcja działa prawidłowo dla tabeli dwuwy-miarowej, jeżeli poprawnie podane są argumen-ty: Nr_wiersza i Nr_kolumny (formuła 3),● funkcja generuje błąd #ADR! jeżeli argumen-ty Nr_wiersza lub Nr_kolumny przyjmują war-tości wyższe niż odpowiednio: liczba wierszy

26 www.controlling.infor.pl

Funkcja INDEKSNarzędzia informatyczne

Funkcje wyszukiwaniai adresu – INDEKS

Mariusz Jankowskiautor strony internetowej poświęconej Excelowi

i programowaniu w VBA;Bogdan Gilarski

właściciel firmy szkoleniowej Perfect And Practical;Pytania: czytelnicy.controlling@infor.pl

W tym opracowaniu kontynuujemy proble-matykę funkcji wyszukiwania i adresu. W nu-merze 3/2010 miesięcznika opisaliśmy zastosowanie PODAJ.POZYCJĘ. Tym razemzajmiemy się funkcją INDEKS.

w tabeli (formuła 4), liczba kolumn w tabeli(formuła 5).

Funkcja INDEKS pozwala nie tylko na pobie-ranie pojedynczych wartości, ale części lub na-wet całej tabeli źródłowej. Chcąc pobrać caływiersz, musimy ustawić wartość drugiego argu-mentu na 0, chcąc pobrać całą kolumnę – ana-logicznie musimy ustawić wartość trzeciego ar-gumentu na 0. Równoczesne ustawienie drugie-go i trzeciego argumentu funkcji na 0 pozwalazwrócić w wyniku całą tabelę źródłową. Ponie-waż w wyniku zwracana jest tablica wartości,formułę należy zatwierdzić w sposób tablicowy(Ctrl+Shift+Enter).

Widok 2 zawiera dwie formuły tablicowe:● pierwsza formuła (komórka D3) odwołuje siędo pierwszego wiersza z danymi; ustawienie ze-ra dla trzeciego argumentu powoduje, że w do-myśle mają być zwrócone wszystkie kolumnyz danymi; formuła w wyniku zwraca zatem dwiewartości (tablica pozioma),● druga formuła (komórka D6) w domyśl-ne zwraca całą tabelę, ale ponieważ umieścili-śmy ją tylko w czterech ko-mórkach, zwraca fragment tablicy.

Budowa funkcji – składnia odwołaniowa

W wersji odwołaniowejbudowa funkcji wyglądanieco inaczej. Pierwszy ar-gument Odwołanie to od-wołanie do pojedynczej ta-beli lub wielu tabel (listazakresów musi być ujętaw nawiasy). Drugi i trzeciargument wyglądają tak sa-mo jak w wersji tablicowej,czyli Nr_wiersza i Nr_ko-lumny. Składnia ta posiadatakże czwarty argumentNr_obszaru, który pozwalana wybór jednego spośródwybranych w pierwszymargumencie funkcji – ob-szarów.

Należy jednak zazna-czyć, że funkcja w tej for-mie posiada pewne ograni-czenia – obszary podawane

w pierwszym argumencie (nawet jeżeli są to na-zwane obszary) muszą być zlokalizowane w tymsamym arkuszu, w którym znajduje się funkcja.Z tego też powodu ta wersja zastosowania funk-cji INDEKS() jest mało popularna i raczej niepo-lecana w przypadku bardziej zaawansowanychprojektów.

Widok 3 zawiera cztery formuły (aby ułatwićich zrozumienie, wprowadziliśmy nazwy: Euro-pa – odnosi się do zakresu A3:B6, Azja – odno-si się do zakresu D3:D5, Afryka – odnosi się do zakresu A8:D8):● pierwsza formuła odwołuje się do nazwy Eu-ropa – pobiera wartość z czwartego wiersza,drugiej kolumny zakresu – czyli Hiszpanię,● druga formuła odwołuje się do wszystkichtrzech zakresów zawierających nazwy państw.Ostatni argument ustawiony na 2 wskazuje, żebierzemy pod uwagę tylko zakres Azja (D3:D5).Trzeci wiersz zakresu to Indie, zatem taka wartośćzostaje zwrócona w wyniku. Argument Nr_kolum-ny jest ustawiony na 1, ponieważ tablica składasię tylko z jednej kolumny, jednak pominięcie te-

Controlling nr 4 ● 1–30 kwietnia 2010 27

Funkcja INDEKS Narzędzia informatyczne

Widok 1

Wersja tablicowa funkcji INDEKS – pojedyncze wartości

Widok 2

Wersja tablicowa funkcji INDEKS – tablica wartości

go argumentu lub wpisanie zera daje dokładnie ta-ki sam efekt (pobierany jest wtedy cały wiersz),● trzecia formuła zwraca cały trzeci wiersz za-kresu Europa – formuła musi zostać zatwierdzo-na w sposób tablicowy,● czwarta formuła działa podobnie. Tym razemodwołujemy się do zakresu Afryka – pobieramycały pierwszy (jedyny) wiersz. Ponieważ na wy-nik zarezerwowaliśmy tylko dwie komórki,w wyniku otrzymujemy połowę tablicy.

Zastosowanie funkcji – wyszukiwanie w tablicy pionowej

Funkcja INDEKS jest na tyle elastyczną funk-cją, że pozwala na pobieranie danych nawet

z takich tabel, których układ nie jest do końcaprawidłowy. Widok 4 zawiera listę kwartałów(wprowadzonych do pierwszej kolumny arkusza)i listę miesięcy (wpisanych aż do trzech różnychkolumn) – zadanie polega na przyporządkowa-niu kwartału z kolumny A, konkretnemu miesią-cowi.

Aby ułatwić zrozumienie zadania, wprowadzi-liśmy dwie nazwy:Kwartały – odnosi się do zakresu A3:A6,Miesiące – odnosi się do zakresu B3:D6.

Jak widać, formuła tablicowa (Ctrl+Shift+En-ter) działa prawidłowo zarówno dla maja jak idla grudnia. Przestudiujmy formułę dla maja:= INDEKS(kwar ta ły ;MAX( JEŻELI (mies ią -

ce=$A8;WIERSZ(miesią-ce);0))-2)● J E Ż E L I ( m i e s i ą -ce=$A8;WIERSZ(miesią-ce);0) – najpierw porównu-jemy dane z zakresu Mie-siące ze słowem „maj”. Każ-dy miesiąc znajduje się naliście tylko raz, pewne jestzatem, że formuła zwróciwartość PRAWDA dokład-nie w jednym przypadku.Chcemy, aby funkcja JE-ŻELI zwracała w wyniku numer wiersza komórki(dla PRAWDA) lub 0 (dlaFAŁSZ). Funkcja tworzy ta-blicę wartości składającąsię z zer i jednej czwórki(numer wiersza komórkiz tekstem „maj”).● MAX(JEŻELI (mies ią -ce=$A8;WIERSZ(miesią-ce);0))-2 – funkcja MAX po-biera największą wartośćz tablicy – czyli 4. Ponie-waż zakres komórek Mie-siące zaczyna się od trze-ciego wiersza, musimy od-jąć 2 – chcemy, aby w wy-niku podany został numerwiersza zakresu, w którymznajduje się komórka z tek-stem „maj”. W wynikuotrzymujemy 2.● Następnie za pomocąfunkcji INDEKS pobieramy

28 www.controlling.infor.pl

Funkcja INDEKSNarzędzia informatyczne

Widok 3

Wersja odwołaniowa funkcji INDEKS

Widok 4

Wyszukiwanie w tablicy pionowej

drugi wiersz zakresu Kwar-tały (tablica jednokolumno-wa). Wynikiem jest tekstKwartał 2.

Zastosowaniefunkcji– wyszukiwaniew tablicy poziomej

Widok 5 przedstawia po-dobny przypadek, z tą róż-nicą, że tym razem chcemypobrać dane z tablicy po-ziomej. Tak jak poprzednio,wprowadziliśmy dwie na-zwy, które pozwalają na ła-twiejsze zrozumienie sposo-bu działania formuł tablico-wych:Quarters – odnosi się do za-kresu A3:D3,Months – odnosi się do za-kresu A4:D6.

Jak widać, formuła tablico-wa (Ctrl+Shift+Enter) działaprawidłowo zarówno dla lu-tego jak i dla lipca. Przestu-diujmy formułę dla lipca:INDEKS(quarters;;MAX(JE-ŻELI(months=$A11;NR.KOLUMNY(months);0)))● JEŻELI(months=$A11;NR.KOLUMNY(mon-ths);0) – najpierw porównujemy dane z zakresuMonths ze słowem „lipiec”. Każdy miesiąc znaj-duje się na liście tylko raz, tak więc podobnie jakw poprzednim przykładzie, formuła zwróci war-tość PRAWDA dokładnie w jednym przypadku.Chcemy, aby funkcja JEŻELI zwracała w wynikunumer kolumny komórki (dla PRAWDA) lub 0(dla FAŁSZ). W rezultacie funkcja tworzy tablicęwartości składającą się z zer i jednej trójki.● MAX(JEŻELI(months=$A11;NR.KOLUM-NY(months);0)) – funkcja MAX pobiera najwięk-szą wartość z tablicy – czyli 3.● Następnie za pomocą funkcji INDEKS pobie-ramy trzecią kolumnę zakresu Quarters (tablicajednowierszowa). Wynikiem jest tekst Kwartał 3.● W formule został pominięty drugi argument,czyli Nr_wiersza, dlatego, że zakres Quartersto czteroelementowa tablica pozioma. Ustawie-nie tego argumentu na 0 (cały wiersz) lub na 1(pierwszy wiersz) daje identyczne rezultaty.

Współpraca z funkcją PODAJ.POZYCJĘ

Zagnieżdżenie funkcji PODAJ.POZYCJĘ we-wnątrz INDEKS pozwala na tworzenie bardzoszybkich, elastycznych i użytecznych formułzwracających potrzebne wartości z tabeli źródło-wej. Kombinacja tych funkcji pozwala pobieraćdane z tabel zorganizowanych w różny sposób.Dlatego też często z powodzeniem bywa wyko-rzystywana jako alternatywa wobec funkcji WY-SZUKAJ.PIONOWO. INDEKS + PODAJ.POZY-CJĘ pozwala m.in. na: wyszukiwanie wartości napodstawie kilku różnych kryteriów, wyszukiwa-nie krzyżowe (na podstawie kryteriów znajdują-cych się w pierwszym wierszu i pierwszej ko-lumnie), a także na pobranie wartości znajdują-cej się w kolumnie po lewej stronie względemszukanej. I temu ostatniemu przypadkowi chcie-libyśmy się przyjrzeć dokładniej, ponieważ jestto jeden z najczęściej spotykanych problemówdo rozwiązania.

Controlling nr 4 ● 1–30 kwietnia 2010 29

Funkcja INDEKS Narzędzia informatyczne

Widok 5

Wyszukiwanie w tablicy poziomej

Widok 6

Pobranie danych znajdujących się po lewej stronie

Pobranie danychznajdujących się po lewej stronie

Funkcja WYSZUKAJ.PIONOWO umożliwiapobranie danych znajdujących się po prawejstronie od kolumny, w której znajduje się szuka-na wartość. Nie ma natomiast możliwości, abypobrać wartość, która znajduje się po lewej stro-nie. W takiej sytuacji z pomocą przychodzi kom-binacja funkcji INDEKS + PODAJ.POZYCJĘ.

W powyższym przykładzie chcemy pobraćwartość z kolumny A (Rok), na podstawie war-tości z kolumny B (Sprzedaż). Znając wysokośćsprzedaży (4.01), chcemy pobrać rok. Rozwiąza-niem jest formuła:=INDEKS(rok;PODAJ.POZYCJĘ($E4;sprzedaż;0))● Najpierw pobierana jest pozycja liczby 4.01.Ostatni argument ustawiony na 0 wskazuje, żeszukamy dokładnego przypisania. 4.01 znajdujesię na czwartej pozycji zakresu Sprzedaż (na-główki nie są brane pod uwagę), zatem drugi ar-gument funkcji INDEKS przyjmuje wartość 4.● Wartością, która znajduje się w czwartymwierszu tabeli Rok, jest 2008, i rzeczywiście jestto prawidłowy wynik.

Wnioski

Jak wspomnieliśmy na wstępie, zdecydowa-na większość zastosowań funkcji INDEKS pole-ga na ściągnięciu pojedynczej wartości z obsza-ru (tabeli) poprzez wskazanie punktu przecię-cia (za pomocą dwóch argumentów funkcji:Nr_wiersza, i Nr_kolumny). Funkcja posiadadwie składnie:● tablicową, która pozwala pobrać pojedyncząwartość lub tablicę wartości, z określonegow pierwszym argumencie funkcji zakresu,● odwołaniową (bardzo rzadko stosowanąw praktyce), która umożliwia wybór obszaru,z którego ma zostać pobrana wartość, pod wa-runkiem że wszystkie zakresy znajdują sięw tym samym arkuszu, co funkcja INDEKS.

Uniwersalność tej funkcji polega na tym, żedoskonale sprawdza się w przypadkach pobie-rania informacji z tabel zorganizowanychw różny sposób. Trudno jest sobie wyobrazićszybkie i niezawodne przetwarzanie danych zapomocą formuł bez wykorzystywania funkcjiINDEKS.

■

30 www.controlling.infor.pl

Funkcja INDEKSNarzędzia informatyczne