Funkcja indeks to podobnie jak formatowanie warunkowe i tabele przestawne najbardziej przydatne (i niedoceniane zarazem) możliwości Excela. Wg mnie to niezbędnik każdego użytkownika Excela.
Wróćmy jednak do funkcji INDEKS. Omówimy ją na przykładzie z poniższego obrazka, który przygotowałem. Jest to raport sprzedaży z podziałem na kwartały.
Dzięki funkcji INDEKS nauczymy się, jak wyszukiwać poszczególne dane z raportu w Excelu. Funkcja INDEKS ma 2 różne wersje - formę tablicową i formę odwołaniową. Omówmy każdą z nich.
1. Forma tablicowa formuły INDEKS pozwala nam na wyszukanie w danej tabeli określonej wartości, którą znajdziemy w wybranym przez nas wierszu i kolumnie.
Funkcja INDEKS w tym wariancie ma postać:
=INDEKS(tablica; nr_wiersza; [nr_kolumny])
Można to wytłumaczyć w ten sposób:
- tablica to cały zakres wartości, które znajdują się w tabeli (np. na powyższym rysunku byłoby to B3:D6);
- nr_wiersza to po prostu liczba porządkowa wiersza w danej tabeli (na rysunku powyżej Adam to wiersz nr 1, Julia nr 2 itd.);
- nr_kolumny to analogicznie jak nr wiersza liczba porządkowa kolumny.
Zobaczmy teraz, jak wykorzystać funkcję indeks w formie tablicowej. Załóżmy, że chcemy wyszukać wartość sprzedaży dla Julii w lutym. Formuła będzie wyglądać następująco:
==INDEKS($B$3:D6;$G$4;$G$5)<
W formule najlepiej użyć adresów bezwzględnych (skrót klawiaturowy to F4). Wynik formuły jest prawidłowy.
2. Forma odwołaniowa funkcji INDEKS również umożliwia nam wyszukanie danych z tabel, jednak takich tabel (obszarów) możemy zdefiniować kilka, np. 4 różne kwartały jak w naszym przykładzie.
Formuła wygląda wtedy w ten sposób:
=INDEKS(odwołanie; nr_wiersza; [nr_kolumny]; [nr_obszaru])
Wytłumaczmy to sobie:
- odwołanie to obszar, gdzie funkcja ma szukać danych - w praktyce oznacza to obszar wszystkich naszych tablic;
- nr_wiersza to liczba porządkowa wiersza w tabeli;
- nr_kolumny to analogicznie liczba porządkowa kolumny;
- nr_obszaru to numer tabeli, którą mamy przeszukać.
Zobaczmy, jak skorzystać z formy odwołaniowej funkcji INDEKS. Wyszukajmy wartość sprzedaży Adama w czerwcu.
Znów używamy adresów bezwzględnych. Formuła w takim przypadku będzie wyglądać w ten sposób:
=INDEKS((D9;$B$9:$D$12;$B$15:$D$18;$B$21:$D$24);$G$10;$G$11;$G$12)
To tylko teoretyczne zastosowanie funkcji INDEKS. Przy użyciu tej funkcji Excel naprawdę wyczynia cuda. Jednym z zastosowań funkcji INDEKS jest stworzenie pełnego cennika w Excelu.
Zachęcam więc wszystkich do subskrypcji wpisów bloga Abc Excel i zadawania pytań w razie problemów. Służą temu komentarze pod wpisami i mail.
10 komentarze:
Z utęsknieniem czekam na szczegółowe opracowanie cennika. Chciałbym podane w komórkach nr wiersza itd zastąpić słownymi nazwami
Mam wrażenie że zakradł się mały błąd w ostatniej formule =INDEKS((D9;$B$9:$D$12;$B$15:$D$18;$B$21:$D$24);$G$10;$G$11;$G$12)
chyba powinno wyglądać
=INDEKS(($B$3:$D$6;$B$9:$D$12;$B$15:$D$18;$B$21:$D$24);$G$10;$G$11;$G$12)
ale może się mylę...
czy przy tej funkcji jest możliwe odwołanie się tylko do obszaru z pominięciem wiersza i kolumny?
@ Anonimowy
Jeśli dobrze rozumiem pytanie... w takim przypadku funkcja Indeks straciła by swoje zastosowanie. Co dokładnie chcesz osiągnąć?
w formatowaniu warunkowym chciałabym wprowadzić warunek zaznaczania na dany kolor komórek które zawierają konkretne dane. Chciałam zrobić to za pomocą tej funkcji. Odwołanie do każdego wiersza powoduje że muszę wprowadzać ten warunek w dla każdego wiersza. Zastanawiałam się czy nie można odwołać się do jakiegoś zakresu danych.
Aby lepiej to wyjaśnić:
Chciałabym trochę zmodyfikować szablon jaki znalazłam na http://office.microsoft.com/en-us/templates/results.aspx?qu=schedule&ck=1&av=zxl140#pg:3|ai:TC101863404|
Autor w swojej liście nadał każdemu zadaniu inny kolor. Ja chciałabym zrobić grupy zadań - kazda grupa miałaby ten sam kolor. Dlatego też chciałabym się odwoływać do jakiegoś zakresu danych a nie do jednego wiersza.
@ Anonimowy
Jakoś nie mogę tego skojarzyć z funkcją indeks.
A może wystarczy tylko formatowanie warunkowe? Jeśli jednak konieczna jest funkcja indeks, to załóż temat na forum i wklej przykładowy plik.
a jak zamienić podawane w komórkach nr wiersza, nr kolumny zastąpić słownymi nazwami? Chciałabym, żeby były one wybierane z listy rozwijanej...
No właśnie to będzie miało sens i jakieś praktyczne zastosowanie... Czy można zastąpić numery kolumn i wierszy ich opisami? Wtedy będzie to bardziej intuicyjne w późniejszej obsłudze. Oczywiście upraszczającym rozwiązaniem będzie zastosowanie listy rozwijanej do wyboru warunków.
Chyba można by to zrobić w ten sposób, że do np. nazwisk i miesiecy przypisane były numer funkcją jeżeli. Robimy w jednej komorce liste rozwijana z ktorej mozemy wybrac konkrentego człowieka, w komorce obok formułę jeżeli przypisującą numer wiersza, i analogicznie ze sprzedażą w poszczególnych miesiącach
z tym, że trzeba zmodyfikować tabelę tak, aby miesiące były w sasiadujących kolumach; bez podziału na kwartały
Prześlij komentarz
Uwaga: tylko uczestnik tego bloga może przesyłać komentarze.