W pierwotnym zamyśle artykuł skierowany miał być do analityków, ale tak naprawdę rozwiązania a przede wszystkim świadomość ich istnienia, może być wykorzystana przez wszystkich użytkowników Excela. Niektóre zastępują powszechnie stosowane rozwiązania w doskonalszy sposób, inne to zupełnie nowa wartość.

INDEKS & PODAJ.POZYCJĘ czyli jak zastąpić WYSZUKAJ.PIONOWO

Odwiecznym utrudnieniem napotykanym przy stosowaniu funkcji Wyszukaj.pionowo jest kolejność kolumn w danych nad którymi pracuję. Wartość, „po której wyszukuję” musi się znajdować w pierwszej kolumnie danych, więc wartość która jest zwracana przez formułę musi pochodzić z kolumn po prawej stronie.

Dzięki połączeniu funkcji INDEKS i PODAJ.POZYCJĘ mogę szukać wartości po wierszach na lewo i prawo od kolumny „po której” wyszukuję.

 

PODAJ.POZYCJĘ

Funkcja zwraca względne położenie szukanej wartości w przeszukiwanej tablicy danych, zwraca pozycję szukanej wartości a nie jej wartość.

– szukana_wartość – argument wymagany, który będzie dopasowany do jednej z wartości w zakresie przeszukiwana_tab; szukana_wartość może być liczbą, tekstem, wartością logiczną lub odwołaniem do jednej z tych wartości

– przeszukiwana_tab – argument wymagany, zakres komórek przeszukiwany by znaleźć w nim szukaną_wartość

– typ_porównania – argument opcjonalny, może przyjąć wartość -1, 0 lub 1 (wartość 1 jest domyślna), argument ten określa w jaki sposób porównywana jest szukana_wartość do elementów w przeszukiwanej_tab

– 0 – fukcja znajduje pierwszą wartość, równą dokładnie szukanej_wartości (najczęśniej stosowane rozwiązanie)

 

INDEKS

Funkcja ta jest używana na dwa sposoby:

– może zwracać wartość określonej komórki lub tablicy (forma tablicowa)

– może zwracać odwołanie do określonych komórek (forma odwołaniowa)

W połączeniu z funkcją PODAJ.POZYCJĘ wykorzystywać będę formę tablicową, dlatego też omówię to zastosowanie.

W formie tablicowej funkcja zwraca wartość znajdującą się w podanej tablicy na przecięciu danego wiersza oraz kolumny.

– tablica – argument wymagany, jest to tablica lub stała tablicowa która będzie przeszukiwana

– nr_wiersza – argument wymagany , wskazuje wiersz w tablicy z którego zostanie pobrana wartość, jeżeli zostanie pominięty wówczas wymagany będzie argument nr_kolumny

– nr_kolumny – argument opcjonalny, podaje numer kolumny z tablicy, z której zostanie zwrócona wartość. Analogicznie, jeżeli argument nr_kolumny zostanie pominięty będzie wymagany nr_wiersza.

 

Efekt otrzymywany dzięki połączeniu funkcji INDEKS oraz PODAJ.POZYCJĘ

Połączenie polega na wykorzystaniu wersji tablicowej funkcji INDEKS i zagnieżdżenie w jednym z argumentów, bądź w obu funkcji PODAJ.POZYCJĘ, dzięki czemu można budować elastyczne, uniwersalne formuły, które pozwalają na dynamiczne wyszukanie właściwego (dopasowanego) wiersza jak i kolumny.

Zastosowanie

Oczywiście dzięki wspomnianej uniwersalności, opanowanie tych funkcji a dokładniej ich połączenia stanie się na tyle wygodne, że zapomnisz o funkcji wyszukaj pionowo, ponieważ bez problemu dane można wyszukać niezależnie od ich położenia w tablicy. Formuły są jak najbardziej do zastosowania podczas budowy raportów, czy wszelkiego typu analiz.

Przykład

Dane, które wykorzystuję znajdują się w arkuszu Dane. Numer seryjny jest drugą kolumną w tablicy danych.

Pierwsza część funkcji (czyli pierwsze PODAJ.POZYCJĘ – wskazanie wiersza)

Przeszukuję kolumnę B (numer seryjny) arkusza Dane w poszukiwaniu numeru seryjnego podanego w komórce B5 czyli PL/PRS444, z dokładnym typem porównania. Ta część funkcji daje wartość 8, co oznacza że w wierszu numer 8 zakresu B1:B209 znajduje się szukany numer seryjny.

Przeszukuję pierwszy wiersz danych (czyli nagłówki)  w arkuszu Dane w poszukiwaniu konkretnej nazwy nagłówka (w tym przypadku z komórki c4 czyli Dealer), z dokładnym typem dopasowania. Ta część formuły daje wynik 1, co oznacza że w arkuszu Dane kolumna o nazwie Dealer jest pierwszą kolumną.

Powyższe dwie funkcje dają, więc miejsce przecięcia w tablicy: wiersz 8 w kolumnie 1, co daje wynik „PL018”.

Oczywiście nie zawsze w przypadku połączenia funkcji INDEKS oraz PODAJ.POZYCJĘ muszę stosować tę ostatnią dwukrotnie. Gdy wiem, w którym wierszu poszukuję wartości lub w której kolumnie zawsze mogę podać ją na sztywno. Poniżej przykład wyszukuję ceny zakupu – dla jakiego numeru seryjnego wystąpiła, więc wiem z której kolumny w arkuszu Dane pobiorę informację o numerze seryjnym (kolumna numer 2).

 

ADRES & ADR.POŚR

Funkcje czy łącznie czy pojedynczo są rzadko używane i mało znane. Funkcja ADRES pozwala na podstawie podanego numeru wiersza i kolumny, zwrócić adres bezwzględny komórki.

ADRES

 

– nr_wiersza – jest to argument wymagany w funkcji, numer wiersza komórki, który ma być użyty w odwołaniu do komórki, może być podany np. poprzez funkcję WIERSZ

– nr_kolumny – jest to argument wymagany w funkcji, numer kolumny, który ma być użyty w odwołaniu do komórki, może być podany np. poprzez funkcję NR.KOLUMNY

– [typ_adresu] – jest to argument opcjonalny (nie muszę go podawać), podaję tu wartość liczbową na podstawie, której powstanie odwołanie określonego typu. Gdy typ_adresu przyjmie wartość:

– 1 lub zostanie pominięty – daje odwołanie bezwzględne

– 2 – bezwzględne wiersze, względne dla kolumny

– 3 – względne wiersza, bezwzględne dla kolumny

– 4 – względne

– [a1] – jest to argument opcjonalny, w zależności od podanej tu wartości PRAWDA/FAŁSZ, tworzone odwołalnie ma postać A1 lub W1K1, pierwsze (A1) ma formę kolumny alfabetyczną, zaś nazwa wiersza jest już wartością gdzie wiersz i kolumna podawane są numerycznie , jeżeli argument [a1] zostanie pominięty lub przyjmie wartość PRAWDA wówczas funkcja poda adres w stylu A1, jeżeli przyjmie wartość FAŁSZ odwołanie będzie miało postać W1K1

– [tekst_arkusz] – argument opcjonalny, gdzie mogę podać nazwę arkusza komórki, do której chcę stworzyć odwołanie. Jeżeli zostanie pominięty wówczas odwołanie zostanie stworzone do Arkusza w którym budowana jest funkcja

 

ADR.POŚR

 

W skrócie  funkcja ADR.POŚR zwraca odwołanie wskazane przez wartość tekstową.

– adres_tekst – to argument wymagany w funkcji. Może być to odwołanie do komórki jako ciągu tekstowego, nazwa zdefiniowana jako adres lub adres komórki zawierającej adres w trybie adresowania A1 lub W1K1.

– [a1] – jest to argument opcjonalny, podaje jakiego typu odwołanie daje argument adres_tekst, jeżeli jest to typ W1K1 wówczas typ [a1] powinien przyjąć wartość FAŁSZ, zaś gdy jest to typ A1 wówczas argument [a1] powinien przyjąć wartość PRAWDA lub może być pominięty.

 

Efekt otrzymywany w wyniku połączenia obu funkcji ADRES i ADR.POŚR

Efekt magiczny otrzymuje się poprzez zagnieżdżenie funkcji ADRES wewnątrz ADR.POŚR. Funkcja ADRES tworzy odwołanie do komórki w postaci tekstu (typu A1 lub W1K1) następnie funkcja ADR.POŚR. odczytuje „zawartość” podanego (przez funkcje ADRES) odwołania.

Zastosowanie

Zastosować obie funkcje możemy oczywiście budując rożnego typu raporty np. wiedząc lub też nie z jakich kolumn czy wierszy powinny być pobrane dane, w sytuacji gdy co miesiąc aktualizujemy dane np. pobieramy je dla kolejnego miesiąca. Tak naprawdę połączenie obu funkcji daje nieograniczone możliwości. W załączonym pliku kilka opcji zastosowania obu funkcji.

Przykład

Poniżej zastosowanie funkcji ADRES, bezwzględne odwołanie do komórki E2 w arkuszu Dane.

 

Kolejny przykład, gdzie funkcja ADR.POŚR uzyskuje wartość z komórki na podstawie odwołania stworzonego w innej komórce (C18).

 

Ostatni przykład czyli efekt połączenia obu funkcji. Odwołanie stworzone przez funkcję ADRES. Wartość (XYZ)  uzyskana przez funkcję ADR. POŚR, widoczna w komórce C11.

 

Pobierz plik Excel

Pobierz plik  Funkcje Excel nie tylko dla analityka cz1 i umieść własne dane.