Excel - Podaj_Pozycje.pdf - Excel - rrichor58

Narzędzia informatyczne

Funkcja PODAJ.POZYCJĘ

Funkcje wyszukiwania

i adresu – PODAJ.POZYCJĘ

Mariusz Jankowski

autor 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 problematykę funk-

cji wyszukiwania i adresu. W numerze 2/2010 mie-

sięcznika opisaliśmy zastosowanie WYSZUKAJ.PIONO-

WO. Tym razem napiszemy o jednej z najszybszych,

a także najbardziej użytecznych i wszechstronnych

funkcji arkuszowych – PODAJ.POZYCJĘ.

Przeznaczenie funkcji PODAJ.POZYCJĘ za-

warte jest niejako w jej nazwie. Jej głównym za-

daniem jest zlokalizowanie w wyznaczonym

obszarze poszukiwanej wartości lub – mówiąc

prościej, określenie, którą pozycję na liście zaj-

muje szukana wartość. Listą w tym przypadku

jest zawsze jednowymiarowa tablica (pionowa

lub pozioma). Algorytmy przeszukiwania nie

rozróżniają wielkości liter, pomijają puste ko-

mórki, a także umożliwiają korzystanie ze zna-

ków specjalnych przy definiowaniu kryteriów

dla szukanej wartości.

Funkcja ta działa na łączach międzyskoroszy-

towych (osobnych plikach Excela). Po określeniu

takiego łącza źródło danych (skoroszyt zawiera-

jący tabelę, z której pobieramy informacje) mo-

że zostać zamknięte, czyli de facto możemy wy-

ciągać dane np. z cennika umieszczonego w in-

nym, aktualnie zamkniętym skoroszycie.

Funkcja w wyniku zwraca zawsze liczbę na-

turalną (mniejszą lub równą od pozycji ostatniej

wartości na liście) lub błąd (w sytuacji gdy nie

można określić pozycji dla szukanej wartości).

W przeciwieństwie do WYSZUKAJ.PIONO-

WO funkcja PODAJ.POZYCJĘ zwraca pozy-

cję/miejsce wartości na liście, a nie samą war-

tość, co sprawia, że jest ona często zagnieżdża-

na w innych funkcjach wyszukiwania i adresu,

takich jak: INDEKS, ADR.POŚR, PRZESUNIĘ-

CIE oraz funkcji z grupy WYSZUKAJ… Innymi

słowy, można powiedzieć, że funkcja PODAJ.

POZYCJĘ służy przeważnie do dostarczania pa-

rametrów (argumentów) dla innych funkcji.

Omówienie aspektów współpracy funkcji PO-

DAJ.POZYCJĘ z poszczególnymi, wymienionymi

powyżej funkcjami będzie przedmiotem kolej-

nych, odrębnych artykułów.

Widok 1

Uwarunkowania funkcji PODAJ.POZYCJĘ()

Jak widać na widoku 1, funkcja:

działa prawidłowo zarówno dla tabeli piono-

wej, jak i poziomej (formuła 1 i 2),

●

nie rozróżnia wielkości liter zarówno dla szu-

kanej wartości, jak i dla poszczególnych elemen-

tów tablicy (formuła 3 i 4),

● umożliwia korzystanie ze znaków specjalnych

(formuła 4 i 5).

Budowa funkcji

Budowa funkcji jest stosunkowo prosta i wy-

maga podania następujących trzech argumen-

tów:

1. Szukana wartość to po prostu wartość, którą

chcemy odszukać wewnątrz jednowymiarowej

tabeli/tablicy. To, czego szukamy, przeważnie

jest tekstem lub liczbą, ale może to być także

wartość logiczna typu PRAWDA, FAŁSZ.

38 www.controlling.infor.pl

●

Funkcja PODAJ.POZYCJĘ Narzędzia informatyczne

2. Przeszukiwana_tab to tablica (wpisana ręcz-

nie) lub tabela (ciągły zakres komórek).

3. Typ_porównania (domyślnie 1) może przybie-

rać wartości 0, –1 lub 1.

Wartość 0 oznacza, że funkcja stosuje algo-

rytm dokładnego przypisania, czyli zwraca pozy-

cję na liście, wartości określonej w pierwszym

argumencie ( Szukana wartość ). Dane w tablicy

mogą być uporządkowane w dowolny sposób.

Wartość 1 oznacza, że dane muszą być posor-

towane rosnąco (od najmniejszej do najwięk-

szej). Funkcja znajduje pierwszą większą war-

tość od szukanej i w wyniku zwraca pozycję

wartości umieszczonej powyżej (tablica jednoko-

lumnowa) lub po lewej (tablica jednowierszowa).

Wartość –1 oznacza, że dane muszą być po-

sortowane malejąco (od największej do najmniej-

szej, tak jak to ma miejsce w tabeli powyżej).

Funkcja znajduje pierwszą mniejszą wartość od

szukanej i w wyniku zwraca pozycję wartości

umieszczonej powyżej (tablica jednokolumnowa)

lub po lewej (tablica jednowierszowa).

● algorytm generuje błąd, gdy szukana wartość

nie znajduje się na liście (formuła 4),

● algorytm pozwala na stosowanie znaków spe-

cjalnych, ale tylko dla wyrażeń tekstowych (for-

muła 5), w przeciwnym razie generowany jest

błąd #N/D! (formuła 2).

Algorytm „pierwsze większe”

Trzeci argument funkcji PODAJ.POZYCJĘ do-

myślnie przyjmuje wartość 1. Oznacza to, że aby

funkcja zwracała prawidłowe wyniki, dane w tabe-

li muszą zostać wcześniej posortowane rosnąco.

Algorytm znajduje pierwszą wartość większą od szu-

kanej i przyporządkowuje jej pozycję wartości powy-

żej (w przypadku tablicy pionowej) lub pozycję war-

tości po lewej (w przypadku tablicy poziomej).

Warto podkreślić, że algorytmu tego używamy

niemal wyłącznie dla danych typu liczbowego

– po prostu stosowanie porównań matematycz-

nych (>, <) dla danych typu tekstowego jest bar-

dzo rzadko spotykane, a często wręcz niepożą-

dane (z reguły stosuje się w takich przypadkach

algorytm dokładnego przypisania).

Algorytm „dokładne dopasowanie”

Algorytm dokładnego dopasowania (ostatni ar-

gument ustawiony na 0) w praktyce jest wyko-

rzystywany najczęściej. Jego działanie polega na

wyszukaniu w tablicy/tabeli danych dokładnie

takiej samej wartości, jaka jest określona

w pierwszym argumencie funkcji (jak wspomnie-

liśmy, wielkość liter nie jest rozróżniana). Jeżeli

szukana wartość znajduje się na jednowymiaro-

wej tablicy, wówczas zwracana jest jej pozycja;

w przeciwnym razie funkcja generuje błąd

#N/D!

Widok 3

Algorytm „pierwsze większe”

Widok 2

Algorytm „dokładne dopasowanie”

Jak pokazuje widok 3:

● liczby w tablicy poziomej są posortowane ro-

snąco, wartość ostatniego argumentu ustawiona

jest na 1 (jest to wartość domyślna, możemy za-

tem także pominąć ten argument),

● algorytm szuka pierwszej większej wartości,

na prawo od szukanej – następnie w wyniku

zwraca pozycję pierwszej wartości po lewej,

●

algorytm zwraca błąd #N/D! w sytuacji, gdy

szukana wartość jest mniejsza od pierwszej war-

tości na liście (formuła 1). Funkcja znajduje

pierwszą wartość większą od szukanej (0>–5),

ale nie jest w stanie pobrać pozycji wartości

znajdującej się po lewej (bo wartość mniejsza od

0 nie istnieje w naszej tablicy).

Jak pokazuje widok 2:

● algorytm zwraca prawidłowy wynik, gdy szuka-

na wartość znajduje się na liście (formuła 1 i 3),

Controlling nr 3 ● 1–31 marca 2010 39

Narzędzia informatyczne

Funkcja PODAJ.POZYCJĘ

Algorytm „pierwsze mniejsze”

Analogicznie jak w przypadku algorytmu

„pierwsze większe”, zakres zastosowania tego

algorytmu to praktycznie analiza porównawcza

dla danych liczbowych. W tym przypadku da-

ne w tabeli danych muszą być posortowane ma-

lejąco, a wartość ostatniego argumentu

( Typ_porównania ) ustawiona na –1. Algorytm

znajduje pierwszą wartość mniejszą od szuka-

nej i przyporządkowuje jej pozycję wartości po-

wyżej (w przypadku tablicy pionowej) lub po-

zycję wartości po lewej (w przypadku tablicy

poziomej).

(„żżż”), który na 100% jest wartością większą od

wszystkich występujących w przeszukiwanym

obszarze.

Widok 5

Ostatnia niepusta komórka

Widok 4

Algorytm „pierwsze mniejsze”

Jak widać na widoku 5, w przypadku pierw-

szej formuły funkcja pomija ostatnią komórkę

zakresu (ponieważ A5 jest pusta) i zwraca pozy-

cję 4, czyli pozycję ostatniej niepustej komórki

w zakresie. Należy jednak być bardzo uważnym,

zarówno spacja jak i pusty ciąg zwracany przez

formułę są przez funkcję PODAJ.POZYCJĘ trak-

towane jako komórki niepuste (odpowiednio for-

muła 2 i 3).

algorytm szuka pierwszej mniejszej wartości,

na prawo od szukanej – następnie w wyniku

zwraca pozycję pierwszej wartości po lewej,

● algorytm zwraca błąd #N/D! w sytuacji, gdy

szukana wartość jest większa od pierwszej war-

tości na liście (formuła 1). Funkcja znajduje

pierwszą wartość mniejszą od szukanej (40<45),

ale nie jest w stanie pobrać pozycji wartości

znajdującej się po lewej (bo 40 jest pierwszą

wartością na liście).

Ostatnie wystąpienie z danych

powtarzalnych

Wiemy już, że ustawiając wartość ostatniego ar-

gumentu Typ_porównania na 0, otrzymujemy

w wyniku pierwszą pozycję wystąpienia danej war-

tości (nawet gdy występuje ona na liście wiele

razy). Algorytm „pierwsza większa” natomiast

zwraca w wyniku pozycję pierwszej komórki

mniejszej lub równej od szukanej. Fakt ten można

czasami wykorzystać do wyszukania pozycji nie

pierwszego, ale ostatniego wystąpienia danej war-

tości na liście. Warunkiem koniecznym w tym

przypadku jest jednak, aby dane były posortowane.

Na widoku 6 widzimy powtarzające się wystą-

pienia niektórych dat (21-gru-2009 pojawia się

na liście trzykrotnie), niektórych dat natomiast

w tabeli nie ma (np. 22-gru-2009). W związku

z tym pojawiają się dwa pytania:

●

Ostatnia niepusta komórka

Funkcję PODAJ.POZYCJĘ możemy także wy-

korzystać w niestandardowy sposób, aby np. po-

brać pozycję ostatniej wartości na liście lub zlo-

kalizować wiersz z ostatnią niepustą komórką

w kolumnie. Jeżeli w pewnym zakresie danych

występują puste komórki, możemy łatwo zlokali-

zować ostatnią niepustą. Wykorzystujemy w tym

celu algorytm „pierwsze większe”, a jednocze-

śnie jako szukaną podajemy ciąg tekstowy

Jak określić ostatnie wystąpienie daty

21-gru-2009?

●

Jak zabezpieczyć się przed błędem dla niewy-

stępującej daty 22-gru-2009?

Po pierwsze należy bezwzględnie sprawdzić,

czy szukana wartość znajduje się na liście (np.

za pomocą formuły =JEŻELI(LICZ.JEŻELI(A3:

40 www.controlling.infor.pl

Na podstawie widoku 4 widzimy, że:

● liczby w tablicy poziomej są posortowane ma-

lejąco (od największej do najmniejszej), wartość

ostatniego argumentu ustawiona jest na –1,

●

Funkcja PODAJ.POZYCJĘ Narzędzia informatyczne

A9;C4)>0; PODAJ.POZYCJĘ(C4;A3:A9;1); ”brak”).

Jeżeli data nie może zostać zlokalizowana, funk-

cja generuje stosowny komunikat tekstowy;

w przeciwnym razie zwraca pozycję 4, czyli po-

zycję nie pierwszego, lecz ostatniego wystąpie-

nia wartości (21-gru-2009) na liście.

ciąg tekstowy, „?” natomiast zastępuje jeden do-

wolny znak.

Już sam fakt zastosowania znaku specjalnego

powoduje, że Excel w kryteriach porównania

danych z szukanymi (=, <, >) traktuje zmienne

jako typ tekstowy. Może to mieć znaczenie w sy-

tuacji, gdy będziemy chcieli zastosować znaki

specjalne do pozycjonowania zmiennych typu

liczbowego.

Jak wynika z widoku 7, pierwsza formuła szu-

ka pozycji tekstu zaczynającego się na „3”,

druga formuła szuka w tablicy tekstu zawierają-

cego słowo „zakup”. Następne formuły korzysta-

ją równocześnie z oby-

dwu znaków specjalnych.

Trzecia formuła zwraca

pozycję tekstu, którego

drugim znakiem jest „0”,

czwarta zaś pozycję tek-

stu, który zaczyna się

znakiem „0”, zawiera spa-

cję we właściwym miej-

scu, a kończy sylabą „je”.

Piąta formuła zwraca

błąd, ponieważ na liście

nie istnieje tekst, którego

pierwszy znak to „5”,

a trzeci to „1”.

Znaki specjalne w algorytmach

wyszukiwania

Istnieje możliwość korzystania ze znaków spe-

cjalnych przy definiowaniu kryteriów dla szuka-

nej wartości. Do najpopularniejszych można za-

liczyć dwa takie symbole: „*” zastępuje dowolny

Widok 6

Ostatnie wystąpienie z danych powtarzalnych

Widok 7

Znaki specjalne w algorytmach wyszukiwania

Pozycjonowanie

wyników działań

na tablicach

Funkcja PODAJ.POZY-

CJĘ() jest bardzo przydat-

na do szybkiego określe-

nia pozycji, jaką zajmuje

szukana wartość (spełnia-

jąca równocześnie kilka

kryteriów, a nie jak to

ma miejsce zazwyczaj,

spełniająca tylko jeden

warunek) na dynamicz-

nej tablicy (utworzonej

poprzez działanie formu-

ły tablicowej). Widok 8

ilustruje taką zależność.

W tabeli mamy zesta-

wienie sprzedaży dokona-

nej przez pewne osoby.

Zadanie polega na tym,

aby pobrać pozycję pierw-

Widok 8

Pozycjonowanie wyników działań na tablicach

Controlling nr 3 ● 1–31 marca 2010 41

Narzędzia informatyczne

Funkcja PODAJ.POZYCJĘ

szej transakcji Jana (pierwszy warunek), dla

sprzedaży przekraczającej kwotę 35 jednostek

(drugi warunek).

Przykładowa formuła (zatwierdzamy Ctrl+Shift+

+Enter) rozwiązująca ten problem to: =PO-

DAJ.POZYCJĘ(1;($A$4:$A$8>$E$3)*($B$4:

$B$8=$E$4);0)

gdzie właściwe zapytanie tablicowe o wyżej opi-

sane warunki to formuła tablicowa:

=($A$4:$A$8>$E$3)*($B$4:$B$8=$E$4);

dla danych E3 = 35 oraz E4 = „Jan” otrzymuje-

my tablicę wynikową: {0\0\0\1\0}, gdzie 1 i 0 to

matematyczne wyniki działań na zmiennych ty-

pu logicznego (PRAWDA, FAŁSZ).

Jak łatwo sprawdzić, czwarty wiersz tabeli speł-

nia warunki zapytania.

Nałożenie na tę tablicę wynikową funkcji PO-

DAJ.POZYCJĘ(), czyli:

=PODAJ.POZYCJĘ(1; {0\0\0\1\0}; 0)

określa nam dokładną pozycję jedynki w tabeli

wynikowej i jednocześnie wskazuje numer jej

wiersza.

Wnioski

Przeznaczenie funkcji PODAJ.POZYCJĘ za-

warte jest w jej nazwie. Służy ona do określenia

pozycji (pozycjonowania), którą zajmuje szukana

wartość na jednowymiarowej tablicy z danymi.

Funkcja jest bardzo szybka i ma uniwersalne za-

stosowanie – bywa często wykorzystywana jako

argument dla innych funkcji wyszukiwania i ad-

resu. Nie rozróżnia ona wielkości liter, pozwala

natomiast na stosowanie znaków specjalnych.

Oprócz standardowych właściwości funkcję

PODAJ.POZYCJĘ można także wykorzystać do

pobrania pozycji ostatniego wystąpienia danej

liczby (daty) na liście bądź też określenia pozy-

cji ostatniej niepustej komórki na liście.

■

OD REDAKCJI

Wszystkie omawiane w tym opracowaniu przykłady

(widoki) można również pobrać ze strony interneto-

wej www.isc.infor.pl (zakładka „Excel”).

42 www.controlling.infor.pl

Excel - Podaj_Pozycje.pdf

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: