fbpx
Minuty czytania: 5

Funkcja INDEKS i PODAJ.POZYCJĘ – udane połączenie

Jako użytkownik Excela na pewno przynajmniej słyszałeś o WYSZUKAJ.PIONOWO. Ale czy znasz funkcje: INDEKS i PODAJ.POZYCJĘ? Jeżeli nie, złap za kubek z kawą, rozsiądź się wygodnie i poczytaj.

Funkcje INDEKS i PODAJ.POZYCJĘ (uścisk dłoni)

Każda z tych funkcji spełnia oczywiście niezależnie jakąś rolę. Ale żeby w pełni skorzystać z ich dobrodziejstw – najlepiej połączyć działanie obu.

Funkcja PODAJ.POZYCJĘ

Funkcja PODAJ.POZYCJĘ ma podawać względną pozycję szukanej wartości na liście. Co to znaczy?

Masz listę: Audi, BMW, Mercedes. Jeżeli szukasz BMW, to funkcja PODAJ.POZYCJĘ zwróci wartość 2 (bo BMW jest drugie na liście) Jasne, prawda?

Składnia funkcji wygląda tak:

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

czyli:

=PODAJ.POZYCJĘ(tu podajesz czego szukasz; tu podajesz gdzie szukasz; tu podajemy parametr: -1,0 albo 1, które mówi o sposobie porównania szukanej wartości z wartościami na liście, gdzie szukasz)

Po wyjaśnienie jak działają parametry w typie porównania odsyłam Cię do systemu pomocy Excela. W tym artykule skupimy się na użyciu parametru 0, czyli tzw. dokładnego dopasowania (szukana wartość ma być identyczna jak ta występująca na przeszukiwanej liście)

Funkcja INDEKS

Funkcja INDEKS zwraca wartość, która znajduje się w jakimś zakresie na przecięciu określonego wiersza i kolumny. Może mieć dwie postacie:

  • Możesz szukać wartości w jednym, konkretnym zakresie (forma tablicowa)
  • Możesz szukać wartości w jednym z wielu zakresów (forma odwołaniowa)

Wygląda to tak:

=INDEKS(tablica; nr_wiersza; nr_kolumny)

gdzie:

=INDEKS(tu zakres / lista, która będzie przeszukiwana; tu musi znaleźć się numer wiersza; w którym występuje szukana przez nas wartość; tu natomiast numer kolumny)

W przypadku, formy odwołaniowej:

=INDEKS(odwołanie; nr_wiersza; nr_kolumny; numer obszaru)

czyli

=INDEKS(tu podaj zakresy / listy, które będą przeszukiwane; tu numer wiersza, w którym występuje szukana przez nas wartość; wpisz numer kolumny; a tu numer obszaru, który przeszukamy)

Wszystko stanie się jaśniejsze, jeżeli spojrzysz na poniższy przykład. Stąd możesz pobrać plik początkowy.

Chciałbym wyszukać jaka była wartość sprzedaży w drugim kwartale, dla regionu wschód. Oczywiście w tej prostej tabeli widzę to bez żadnej dodatkowej funkcji. Chodzi jednak o przykład.

  • W pierwszym argumencie zaznaczyłem cały zakres tabeli z danymi.
  • Argument drugi to numer wiersza – stąd liczba 3, bo drugi kwartał (K2) znajduje się w 3 wierszu zaznaczonej tabeli
  • Argument trzeci to numer kolumny – stąd liczba 4, bo region wschód znajduje się w 4 kolumnie zaznaczonej tabeli

Funkcja INDEKS

Pewnie sobie teraz myślisz: „Faktycznie atrakcja…. Żeby znaleźć wartość, której szukam muszę sam najpierw zobaczyć w którym wierszu i kolumnie się znajduje…” No właśnie 😉 Dlatego teraz połączymy działanie obu funkcji.

Dobrana para

Zawsze powtarzam, że Excela uczą się lenie 😉 Bo w gruncie rzeczy chodzi o to, żeby automatyzować co się da i unikać ręcznej, mozolnej pracy. Dlatego funkcji INDEKS, przydałaby się towarzyszka, która wyeliminuje potrzebę ręcznego wpisywania numeru wiersza i kolumny.

Funkcja PODAJ.POZYCJĘ doskonale zrealizuje to zadanie. Aby znaleźć wiersz, w którym mamy szukany przez nas kwartał (K2) wpiszemy:

 Funkcja PODAJ.POZYCJĘ - jako numer wiersza

  • Pierwszy argument to szukany przez nas kwartał K2 – wpisany w cudzysłowie, ponieważ to tekst (oczywiście może to być także, odwołanie do jakieś komórki, o czym dalej)
  • W argumencie drugim zaznaczyłem kolumnę, w której mam szansę znaleźć szukaną wartość
  • Trzeci argument wskazuje, że zależy mi na dokładnym dopasowaniu (szukam dokładnie wartości K2)

A teraz numer kolumny.

Funkcja PODAJ.POZYCJĘ - jako numer kolumny

  • Pierwszy argument to szukany przez nas region wschodni – ponownie wpisany w cudzysłowie, ponieważ to tekst
  • W argumencie drugim zaznaczyłem wiersz, w której mamy regiony
  • Trzeci argument to dokładne dopasowanie

Jak widzisz, funkcja PODAJ.POZYCJĘ skutecznie rozwiążę nam problem wyszukiwania odpowiedniego wiersza i kolumny. Docelowo, wszystko możemy zrobić w ramach jednej, nieco rozbudowanej formuły.

Funkcje: Indeks i Podaj.Pozycję - połączenie

Funkcja INDEKS w formie odwołaniowej

No dobrze, ale co jeżeli zechcę wyszukać wartość dla innego kwartału czy regionu? Nie wiem jak Ty, ale ja nie chcę za każdym razem „grzebać” w formule i wpisywać kolejne wartości. Zautomatyzujmy działanie, przez odwołanie do komórek, w których będziemy mogli zmieniać: kwartały i regiony. Stwórzmy listy rozwijane:

Lista rozwijana

I zmodyfikujmy naszą formułę, tak aby zamiast ręcznie wpisywanych wartości odwoływała się do naszych komórek z listami. Dzięki temu zmieniając region czy kwartał, od razu otrzymam odpowiedni wynik.

Funkcje: Indeks i Podaj.Pozycję - warunki na podstawie list rozwijanych

Super, prawda? 😉

Na początku artykułu napisałem, że funkcja INDEKS występuje w dwóch formach. Do tej pory odwoływaliśmy się do jednego zakresu. A co jeżeli mamy dane z 3 lat i chcielibyśmy mieć podobny mechanizm wyszukiwania, w zależności od wybranego roku?

Funkcje: Indeks i Podaj.Pozycję - warunki na podstawie list rozwijanych (forma odwołaniowa)

Rozbijmy naszą formułę:

=INDEKS((B10:F14;B18:F22;B26:F30);PODAJ.POZYCJĘ(C3;B10:B14;0);PODAJ.POZYCJĘ(C4;B10:F10;0);PODAJ.POZYCJĘ(C2;{2016;2017;2018};0))

na części pierwsze:

  • Pierwszy argument – (B10:F14;B18:F22;B26:F30) – to adresy zakresów dla poszczególnych lat; umieszczamy je w nawiasie
  • W drugim i trzecim argumencie – PODAJ.POZYCJĘ(C3;B10:B14;0);PODAJ.POZYCJĘ(C4;B10:F10;0) – mamy PODAJ.POZYCJĘ, która szuka odpowiedniego kwartału i regionu. Ponieważ dla każdej tabeli mamy taki sam układ – możemy się odwołać tylko do jednej z nich.
  • Czwarty argument – PODAJ.POZYCJĘ(C2;{2016;2017;2018};0)) – to numer obszaru, który ma być przeszukiwany. Ponieważ nie chcemy wpisywać za każdym razem 1 dla roku 2016, 2 dla 2017 czy 3 dla 2018 – wprowadzamy kolejną PODAJ.POZYCJĘ, która podaje nam pozycję szukanej wartości na liście {2016;2017;2018} Nawiasy klamrowe są konieczne, aby wpisane wartości były traktowane jako jedna przeszukiwana tablica.

Finalny plik ściągniesz stąd.

Zalety używania funkcji INDEKS i PODAJ.POZYCJĘ

Na co dzień WYSZUKAJ.PIONOWO użytkujemy dużo częściej. Im bardziej zaawansowany jednak jesteś w Excelu, tym większa pokusa żeby poznać funkcję INDEKS i PODAJ.POZYCJĘ. Dwie największe przewagi to:

  • Większa elastyczność niż w przypadku WYSZUKAJ.PIONOWO
  • Szybsze działanie, w szczególności gdzie mamy dużo formuł wyszukiwania
 

 

Podstawowa wada – ciężej nauczyć się wykorzystywać INDEKS i PODAJ.POZYCJĘ. Mam nadzieję, że po tym artykule uczynisz duży krok w tym kierunku. Gdybyś potrzebował dodatkowych wyjaśnień napisz w komentarzu. A może znasz inne, ciekawe zastosowania tych funkcji?


Masz inne problemy z Excelem? Zapraszam Cię na grupę – Keep Calm And Use Excel na Facebooku. To miejsce, w którym pomagamy sobie nawzajem w codziennych wyzwaniach z Excelem 😉



6 Komentarzy

Krzysztof · 10 marca 2019 o 17:35

Witam, mam pytanie odnośnie funkcji INDEKS i odwołania. W przykładzie odwołania są w tym samym arkuszu: rok 2018,2017 i 2016. Czy obszary odwołania mogą znajdować się w innych arkuszach lub plikach ?

    Łukasz Pietrzak · 10 marca 2019 o 17:56

    Cześć Krzysztof. Dziękuję za komentarz. Niestety, w tej formie funkcji INDEKS – odwołania muszą znajdować się w jednym arkuszu. Pomoc dla tej funkcji sugeruje inne rozwiązanie: z formułą tablicową i funkcją WYBIERZ. Sprawdziłem. Przeniosłem rok 2017 do innego arkusza i przy zastosowaniu formuły w takiej postaci: =INDEKS(WYBIERZ(PODAJ.POZYCJĘ(C2;{2016;2017;2018};0);B10:F14;’INNY ARKUSZ’!A3:E7;B26:F30);PODAJ.POZYCJĘ(C3;B10:B14;0);PODAJ.POZYCJĘ(C4;B10:F10;0)) – działa 😉 Formułę trzeba zatwierdzić kombinacją klawiszy CTRL + SHIFT + ENTER, bo to formuła tablicowa. Daj znać czy pomogło. Pozdrawiam!

Krzysztof · 10 marca 2019 o 19:18

Świetne rozwiązanie ! Działa idealnie 🙂

Łukasz Pietrzak · 14 marca 2019 o 19:13

Super. Cieszę się, że mogłem pomóc 😉

Aga · 25 listopada 2019 o 14:05

Hej, mam pytanie odnośnie czwartego argmentu. Sklad w tym przykladzie excel wie, ze nasze kolejne tabele nazywaja się 2016.2017 i 2018 ? (sprawdzilam menadzerem nazw i nie ma)

    Łukasz Pietrzak · 25 listopada 2019 o 14:56

    Hej. Trafne pytanie 🙂 Excel nie wie, że nazywają się 2016,2017,2018 ale w tym miejscu: „PODAJ.POZYCJĘ(C2;{2016;2017;2018};0)” potrafi wybrać, która z wartości z listy rozwijanej została wybrana – 1,2 czy 3. Dzięki temu, w pierwszy argumencie, gdzie mamy tablice „=INDEKS((B10:F14;B18:F22;B26:F30); ” czyli zakresy z danymi dla lat 2016-2018 – potrafi zdecydować, z którego z obszarów ma pobierać dane. Czy to wyjaśnienie Ci pomogło?

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *