funkcje wyszukiwania i odwołania

Wyszukiwanie w arkuszu Excela

Wyszukiwanie w arkuszu Excel, nawet w dużych tabelach wcale nie jest trudne i nie wymaga znajomości VBA – można to zrobić funkcjami wbudowanymi.

Najłatwiej pokazać to na konkretnym przykładzie:

wyszukiwanie w arkuszu Excela

Załóżmy, że z takiej tabeli jak na obrazku powyżej chcemy otrzymać informację jakie stanowisko zajmuje Maliniak? Wystarczy skorzystać z funkcji Wyszukaj.Pionowo:

=WYSZUKAJ.PIONOWO(„Maliniak”;C:D;2;FAŁSZ)

Oczywiście jako pierwszy argument zamiast przypisano na sztywno wartości „Maliniak” można użyć także odwołania do konkretnej komórki. Istotny jest tu natomiast zakres wyszukiwania – wyszukiwana wartość musi znajdować się w pierwszej kolumnie zakresu wyszukiwania, a wartość którą chcemy uzyskać w wyniku działania funkcji – na prawo od niej.
Oznacza to jednak, że z wykorzystaniem tej funkcji nie uda nam się wyszukać odwrotnego przypadku tzn. jak ma nazwisko kierownik?
Trzeba tu wykorzystać funkcję złożoną – połączenie funkcji Indeks oraz Podaj.Pozycję.

=INDEKS(C:C;PODAJ.POZYCJĘ(„kierownik”;D:D;0))

To najszybszy i najprostszy sposób na zwracanie wartości z kolumn tabeli położonych na lewo od kolumny wyszukiwania.

http://HELION.pl/plugins/new/ksiazkasm.phi?id=ex21fo&nr=4737c&double=double;

http://HELION.pl/plugins/new/kupowali.cgi?id=ex21fo&nr=4737c&double=double;

Funkcje wyszukiwania i odwołania Excela: Adr.Pośr()

Funkcja Excela Adr.Pośr() należy do Funkcji Wyszukiwania i Odwołania Excela. W wyniku jej działania otrzymujemy wartość komórki, której adres jest wskazany w argumencie funkcji.
Argumenty funkcji to:

Adres_tekst – komórka zawierająca adres odwołania

A1 – opcjonalny argument typu 1/0 (Prawda/Fałsz). Wartość domyślna Prawda zwraca styl odwołania adresu A1. Wstawienie Fałsz to wybranie stylu odwołania W1K1.
Dla wartości argumentu równego 0 parametry Nr_wiersza i Nr_kolumny mogą być wartościami ujemnymi.

Sama funkcja jest bardzo często wykorzystywana jako element funkcji zagnieżdżonej, np. w połączeniu z funkcją Adres czy Przesunięcie.

Funkcja Adr.Poś() nie ma swojego bezpośredniego odpowiednika w kodzie VBA. Odwołanie do wartości określonej komórki jest możliwe poprzez właściwość Value obiektu Range.

http://helion.pl/plugins/new/random-katalog.cgi?k=3,28&a=9&nr=4737c

http://helion.pl/plugins/new/random-katalog.cgi?k=3,28&a=10&nr=4737c

http://helion.pl/plugins/new/random-katalog.cgi?k=3,28&a=11&nr=4737c

http://helion.pl/plugins/new/random-katalog.cgi?k=3,28&a=12&nr=4737c

Funkcje wyszukiwania i odwołania Excela: Adres()

Funkcja Excela Adres() należy do Funkcji Wyszukiwania i Odwołania Excela. W wyniku jej działania otrzymujemy adres zdefiniowanej poprzez argumenty funkcji komórki.
Argumenty funkcji to:

Nr_wiersza – wartość liczbowa określająca numer wiersza

Nr_kolumny – wartość liczbowa określająca numer kolumny

Typ_adresu – wartość liczbowa określająca typ zwracanego adresu. Możliwe są tu opcje:

1 – (wartość domyślna) – zwraca adres bezwzględny
2 – zwraca adres mieszany – bezwzględne odwołanie do wiersza, względne do kolumny
3 – zwraca adres mieszany – względne odwołanie do wiersza, bezwzględne do kolumny
4 – zwraca adres względny
O adresach względnych i bezwzględnych przeczytaj tu:
Adres względny i bezwzględny

Tekst_arkusz – argument opcjonalny. Pominięty – zwraca adres odnoszący się do tego samego arkusza. Wstawienie tam wartości tekstowej spowoduje odwołanie do arkusza o nazwie tej wartości.

Sama funkcja jest bardzo często wykorzystywana jako element funkcji zagnieżdżonej.

Odpowiednikiem funkcji w kodzie VBA jest właściwość Address obiektu Range.

http://helion.pl/plugins/new/random-katalog.cgi?k=3,28&a=5&nr=4737c

http://helion.pl/plugins/new/random-katalog.cgi?k=3,28&a=6&nr=4737c

http://helion.pl/plugins/new/random-katalog.cgi?k=3,28&a=7&nr=4737c

http://helion.pl/plugins/new/random-katalog.cgi?k=3,28&a=8&nr=4737c

Funkcje wyszukiwania i odwołania VBA: Hlookup

Funkcja arkuszowa VBA Worksheet.Hlookup() należy do Funkcji Wyszukiwania i Odwołania w kodzie VBA. Wyszukuje określoną wartość tablicy lub zakresie komórek i zwraca wartość z innego wiersza w tej samej kolumnie.
Argumenty wejściowe WorksheetFunction.Hlookup() to:

Arg1 – szukana wartość

Arg2 – zakres, tablica lub zaznaczony obszar komórek, do którego się odwołujemy

zakres musi być tak określony, aby wiersz, w której szukamy wartości była pierwszym górnym wierszem tego zakresu.

Arg3 – numer kolumny, z której ma zostać zwrócona wartość

argument ten musi być liczba naturalną. Przeszukiwana kolumna ma numer 1, kolejne wiersze poniżej – to kolejne liczby naturalne. Nie da się zwrócić wartości z kolumn położonych z lewej strony przeszukiwanej kolumny. Wartość argumentu nie może także wychodzić poza tabelę czyli argument ten nie może być większy niż liczba wierszy zaznaczonego zakresu.

Arg4 – argument opcjonalny czyli nieobowiązkowy, typu logicznego. Wskazuje sposób wyszukiwania.

Wartością domyślną (czyli z pominięciem argumentu) jest tu False – znajdowana jest wartość przybliżona. W takim przypadku, jeżeli szukamy wartości tekstowej, możliwe jest także wstawienie znaków typu:
* – zastępująca dowolny znak lub ciąg znaków
? – zastepujący dowolny pojedynczy znak
Wstawienie tu wartości True – spowoduje wyszukanie dokładnie takiej samej wartości.

Jeżeli funkcja nie znajdzie szukanej wartości – zwracana jest wartość błędu #N/A!

Odpowiednikiem funkcji w Excelu jest funkcja Wyszukaj.Poziomo

Polecam też swoje kursy e-learningowe z VBA dla Excela:

VBA w Excelu – pierwsze kroki

VBA w Excelu, dla tych, którzy już coś wiedzą

http://helion.pl/plugins/new/random-katalog.cgi?k=3,34&a=1&nr=4737c

http://helion.pl/plugins/new/random-katalog.cgi?k=3,34&a=2&nr=4737c

http://helion.pl/plugins/new/random-katalog.cgi?k=3,34&a=3&nr=4737c

http://helion.pl/plugins/new/random-katalog.cgi?k=3,34&a=4&nr=4737c

Funkcje wyszukiwania i odwołania Excela: Wyszukaj.Poziomo

Funkcja Excela Wyszukaj.Poziomo() należy do Funkcji Wyszukiwania i Odwołania Excela. Działa analogicznie jak funkcja Wyszukaj.Pionowo, ale wyszukuje wartość w wierszu, zwracając wartość z innego wiersza tej samej kolumny.
Argumenty funkcji to:

Odniesienie– czyli ta wartość, którą chcemy odszukać w tabeli lub zakresie komórek.

Tablica – zakres komórek zawierających dane. Można tu odwołać się do zdefiniowanej nazwy tabeli lub zakresu lub wpisać cały zakres komórek.

Numer_wiersza – numer wiersza w zakresie. Szukana wartość musi się znajdować w pierwszym górnym wierszu. Numer wiersza, z którego chcemy pobrac wartość, musi być liczbą dodatnią czyli znajdować się poniżej wiersza z szukanymi wartościami.

Wiersz – opcjonalny argument typu Prawda/Fałsz. Wartość domyślna Prawda wyszukuje Szukaną wartość w przybliżeniu. Chcąc odszukać wartość dokładnie – musimy wstawić wartość Fałsz.

W przypadku, gdy szukana wartość nie zostanie odnaleziona, funkcja zwróci wartość błędu #N/D!.

Odpowiednikiem funkcji w kodzie VBA jest funkcja Hlookup.

http://helion.pl/plugins/new/random-katalog.cgi?k=3,28&a=1&nr=4737c

http://helion.pl/plugins/new/random-katalog.cgi?k=3,28&a=2&nr=4737c

http://helion.pl/plugins/new/random-katalog.cgi?k=3,28&a=3&nr=4737c

http://helion.pl/plugins/new/random-katalog.cgi?k=3,28&a=4&nr=4737c

Funkcje wyszukiwania i odwołania VBA: Vlookup

Funkcja arkuszowa VBA Worksheet.Vlookup() należy do Funkcji Wyszukiwania i Odwołania w kodzie VBA. Wyszukuje określoną wartość tablicy lub zakresie komórek i zwraca wartość z innej kolumny w tym samym wierszu.
Argumenty wejściowe WorksheetFunction.Vlookup() to:

Arg1 – szukana wartość

Arg2 – zakres, tablica lub zaznaczony obszar komórek, do którego się odwołujemy

zakres musi być tak określony, aby kolumna, w której szukamy wartości była pierwszą kolumną tego zakresu.

Arg3 – numer kolumny, z której ma zostać zwrócona wartość

argument ten musi być liczba naturalną. Przeszukiwana kolumna ma numer 1, kolejne z prawej strony – to kolejne liczby naturalne. Nie da się zwrócić wartości z kolumn położonych z lewej strony przeszukiwanej kolumny. Wartość argumentu nie może także wychodzić poza tabelę. Jeżeli w zakresie np. 3-kolumnowym wstawimy tu wartość 4 – funkcja zwróci błąd.

Arg4 – argument opcjonalny czyli nieobowiązkowy, typu logicznego. Wskazuje sposób wyszukiwania.

Jeżeli funkcja nie znajdzie szukanej wartości – zwracana jest wartość błędu #N/A!

Odpowiednikiem funkcji w Excelu jest funkcja Wyszukaj.Pionowo.

http://HELION.pl/plugins/new/ksiazkasm.phi?id=vb27pp&nr=4737c&double=double;

http://HELION.pl/plugins/new/kupowali.cgi?id=vb27pp&nr=4737c&double=double;

A każdego kto ma ochotę nauczyć się VBA zapraszam tu:

VBA w Excelu – pierwsze kroki

VBA w Excelu dla tych, którzy już coś wiedzą

Funkcje wyszukiwania i odwołania Excela: Wyszukaj.Pionowo

Funkcja Excela Wyszukaj.Pionowo() należy do Funkcji Wyszukiwania i Odwołania Excela. Wyszukuje wartość w kolumnie i zwraca wartość z wyznaczonej kolumny tego samego wiersza.
Argumenty funkcji to:

Szukana_wartość – czyli ta wartość, którą chcemy odszukać w tabeli lub zakresie komórek.

Tabela/tablica – zakres komórek zawierających dane. Można tu odwołać się do zdefiniowanej nazwy tabeli lub zakresu lub wpisać cały zakres komórek.

Numer_indeksu_kolumny – numer kolumny w zakresie. Szukana wartość musi się znajdować w pierwszej kolumnie z lewej. Numer indeksu kolumny, z której chcemy pobrac wartość, musi być liczbą dodatnią czyli znajdować się na prawo od kolumny z szukanymi wartościami.

Przeszukiwany_zakres – opcjonalny argument typu Prawda/Fałsz. Wartość domyślna Prawda wyszukuje Szukaną wartość w przybliżeniu. Chcąc odszukać wartość dokładnie – musimy wstawić wartość Fałsz.

W przypadku, gdy szukana wartość nie zostanie odnaleziona, funkcja zwróci wartość błędu #N/D!.

Odpowiednikiem funkcji w kodzie VBA jest funkcja Vlookup.

http://helion.pl/plugins/new/random-katalog.cgi?k=3,28&a=1&nr=4737c

http://helion.pl/plugins/new/random-katalog.cgi?k=3,28&a=2&nr=4737c

http://helion.pl/plugins/new/random-katalog.cgi?k=3,28&a=3&nr=4737c

http://helion.pl/plugins/new/random-katalog.cgi?k=3,28&a=4&nr=4737c

Funkcje wyszukiwania i odwołania VBA: Offset

Offset to właściwość obiektu Range, odwołująca się do komórki przesuniętej o określoną liczbę wierszy i/lub kolumn.
Sama konstrukcja jest zapisywana w formie:

Range(Adres_komórki_początkowej).Offset(RowIndex,ColumnIndex)

Parametr RowIndex wyznacza ilość wierszy przesunięcia

Parametr ColumnIndex wyznacza ilość kolumn przesunięcia

Argumenty te są opcjonalne – wartością domyślną jest zero czyli wyznaczeniu komórki bez przesunięcia.
Sama właściwość Offset tylko wyznacza komórkę. Kolejnym krokiem jest odniesienie się do własciwości komórki przesuniętej:

np. Range(Adres_komórki_początkowej).Offset(RowIndex,ColumnIndex).Value

odnosi się do wartości komórki.

Odpowiednikiem Offset w funkcjach Excela jest funkcja Przesunięcie

http://HELION.pl/plugins/new/ksiazkasm.phi?id=cwvba&nr=4737c&double=double;

http://HELION.pl/plugins/new/kupowali.cgi?id=cwvba&nr=4737c&double=double;

Funkcje wyszukiwania i odwołania Excela: Przesunięcie

Funkcja Excela Przesunięcie() należy do Funkcji Wyszukiwania i Odwołania Excela. Wyszukuje i zwraca wartość komórki przesuniętej o określoną ilość wierszy i kolumn od danej komórki.
Argumenty funkcji to:

Odwołanie – adres komórki w stosunku od której wyznaczamy przesunięcie

Wiersze – liczba wierszy przesunięcia od komórki odwołania. Wartości dodatnie oznaczają przesunięcie w dół, wartości ujemne – w górę. Zero oznacza ten sam wiersz.

Kolumny – liczba kolumn przesunięcia od komórki odwołania. Wartości dodatnie oznaczają przesunięcie w prawo, wartości ujemne – w lewo. Zero oznacza tę samą kolumnę.

W przypadku, gdy przesunięcie przekracza arkusz, funkcja zwróci wartość błędu #ADR!.

Odpowiednikiem funkcji w kodzie VBA jest funkcja Offset.

http://HELION.pl/plugins/new/ksiazkasm.phi?id=ex21vw&nr=4737c&double=double;

http://HELION.pl/plugins/new/kupowali.cgi?id=ex21vw&nr=4737c&double=double;

Funkcje wyszukiwania i odwołania Excela: Podaj.Pozycję

Funkcja Excela Podaj.Pozycję() należy do Funkcji Wyszukiwania i Odwołania Excela. Wyszukuje i zwraca pozycję szukanej wartości w podanym zakresie lub tablicy.
Argumenty funkcji to:

Szukana_wartość – ta wartość, którą chcemy odnaleźć w danym zakresie

Przeszukiwana_tab – przeszukiwana tabela lub zakres komórek.

Typ_porównania – typ porównania wartości. Jest to argument opcjonalny czyli nieobowiązkowy.
Wartością domyślną jest tu 1 – Znajdowana jest największa wartość z wartości niewiększych niż Szukana_wartość. Parametr -1 znajduje najmniejszą wartość z wartości niemniejszych niż Szukana_wartość. Dokładna wartość jest wyszukiwana przy wstawieniu jako Typ_porównania wartości 0.

Jeżeli wyszukiwanie nie zakończyło się powodzeniem – zwracana jest wartość błędu #N/D!.

Funkcja jest bardzo przydatna do porównywania i aktualizacji danych, szczególnie w połączeniu z funkcją Indeks().
Odpowiednikiem funkcji w kodzie VBA jest funkcja arkuszowa Match.

http://HELION.pl/plugins/new/ksiazkasm.phi?id=zaexan&nr=4737c&double=double;

http://HELION.pl/plugins/new/kupowali.cgi?id=zaexan&nr=4737c&double=double;