sobota, 29 maja 2010

Funkcja WYSZUKAJ.PIONOWO

Czołem! Przedstawiam kolejny wpis o możliwościach programu Excel.

Dziś nauczymy się kolejnej funkcji, którą zawiera Excel. Jest to funkcja WYSZUKAJ.PIONOWO. Funkcja WYSZUKAJ.PIONOWO jest funkcją wyszukiwania i odwołań. Za jej pomocą Excel w bardzo prosty sposób wyszuka i dopasuje do siebie dane, które znajdują się w dwóch osobnych tabelach.

Struktura funkcji wygląda następująco:

=WYSZUKAJ.PIONOWO(szukana wartość; tabela tablica; numer indeksu kolumny; przeszukiwany zakres)

W uproszczeniu można przyjąć, że poszczególne składniki funkcji to:

=WYSZUKAJ.PIONOWO(co; gdzie; w której kolumnie; prawda/fałsz)

Ostatnia część formuły jest bardzo ważna:

  • prawda to przybliżone dopasowanie;
  • fałsz to dokładna wartość.

Zobaczmy, w jaki sposób używać tych dwóch wariantów funkcji WYSZUKAJ.PIONOWO.

Wariant 1 - PRAWDA.

Przyjrzyjmy się wariantowi numer 1 funkcji WYSZUKAJ.PIONOWO. Dzięki niemu Excel przyporządkuje nam przybliżoną wartość.

Żeby zobrazować ten wariant potrzebna jest nam tabela posortowanych rosnąco danych. Dla przykładu stworzyłem tabelę zawierającą zależność przychodu firmy do jej wyniku netto.

przychód i wynik netto

Dzięki funkcji WYSZUKAJ.PIONOWO chcemy, żeby Excel automatycznie przyporządkował nam spodziewany wynik netto spółki po wpisaniu przez nas dowolnie wpisanej wysokości przychodu.

W tym wariancie funkcja WYSZUKAJ.PIONOWO będzie wyglądać następująco:

  • szukana wartość to przychód - u mnie to komórka E2;
  • tabela tablica to tabela z danymi - u mnie to zakres B2:C13;
  • numer indeksu kolumny to 2 - wynik netto jest wpisany się w drugiej kolumnie tabeli z danymi;
  • przeszukiwany zakres to PRAWDA lub 1 - dzięki temu funkcja przyporządkuje przybliżoną wartość wyniku netto dla dowolnie wpisanej wielkości przychodu (dla przykładu dla dowolnego przychodu z zakresu od 0 zł do 9 999,99 zł wynik netto będzie wynosił zawsze - 50 000 zł).
W naszym przypadku ten wariant funkcji WYSZUKAJ.PIONOWO będzie miał postać:

=WYSZUKAJ.PIONOWO(E3;B3:C13;2;PRAWDA)


Funkcja Wyszukaj Pionowo


Wariant 2 - FAŁSZ.

Dla zobrazowania wariantu nr 2 funkcji WYSZUKAJ.PIONOWO przygotowałem tabelkę, która zawiera nazwy spółek z indeksu giełdowego Wig20 i wartość kursu każdej ze spółek.

Wig20 w Excelu

Chcemy, żeby po wpisaniu nazwy spółki, Excel automatycznie przyporządkował do tej spółki jej kurs.

Najpierw w polu wyboru spółki stworzymy listę rozwijaną.

Lista wyboru w Excelu

Następnie przechodzimy do tworzenia funkcji WYSZUKAJ.PIONOWO. Klikamy w pole kurs i wpisujemy (zaczynając od znaku równości) funkcję. W poszczególnych elementach wpisujemy:

  • szukana wartość to nazwa spółki - u mnie to komórka E2;
  • tabela tablica to tabela z danymi - u mnie to zakres B2:C22;
  • numer indeksu kolumny to 2 - kurs znajduje się w drugiej kolumnie tabeli z danymi;
  • przeszukiwany zakres to FAŁSZ lub 0, bo szukamy dokładnej wartości z tabeli.
Funkcja WYSZUKAJ.PIONOWO w naszym przypadku ma postać:

=WYSZUKAJ.PIONOWO(E3;B2:C22;2;FAŁSZ)

Funkcja Wyszukaj Pionowo Excel


Po sprawdzeniu widzimy, że funkcja WYSZUKAJ.PIONOWO działa. Wybierając nazwę spółki funkcja automatycznie przyporządkowuje jej kurs.

Jak widzimy funkcja WYSZUKAJ.PIONOWO daje użytkownikom Excela bardzo dużo możliwości. Jest ona dość często używana szczególnie, gdy mamy do czynienia z dużą ilością danych, gdzie wypisanie dużej ilości danych jest zbyt uciążliwe.

Zachęcam też do subskrypcji bloga Abc Excel. Już wkrótce pojawi się tu znacznie więcej przydatnych funkcji Excela.

21 komentarze:

Anonimowy pisze...

Jasno i w prosty sposób pokazujesz rozwiązania, które wydają się być bardzo skomplikowane.
Bardzo dziękuję za pomoc:)

Ynwestor pisze...

Proszę bardzo :)

stufka pisze...

Staram się zrobić przykład drugi z tym, że u mnie dane muszą być na oddzielnej karcie. Niestety dostaję error #n/a i nie mam pojecia jak się z tym uporać.
W komórce G27 na drugiej karcie mam rozwijaną tabelę tak jak w przykładzie. Dane do dabeli są na trzeciej karcie. Również na karcie mam tabelę z danymi.

W komórce gdzie chcę aby dane pojawiały się automatycznie mam:

=VLOOKUP(G27,Sheet3!AI48:AY145,9,FALSE)

To zwraca mi niestety #n/a.

PO skopiowaniu tabeli danych na tę samą kartę wszystko działa jak w przykładzie.
Czy masz może pomysł jak mogę przekształcic twój przykład aby działał tak jak opisałem powyżej??

Ynwestor pisze...

Cześć stufka!

Błąd #n/d! ewidentnie świadczy o tym, że funkcja vlookup nie może znaleźć wyniku. Wynik jest niedostępny, bo najprawdopodobniej szukana wartość funkcji została błędnie wybrana.

Trochę ciężko rozwiązywać błąd zdalnie bez wglądu do arkusza, ale mam pewien pomysł. Myślę, że błąd nie leży w samej funkcji tylko błędna jest lista rozwijana. Sprawdź, czy lista rozwijana działa prawidłowo, gdy masz dane w innym arkuszu.

Podejrzewam, że przyczyną błędu jest to, że zrobiłeś listę rozwijaną, gdy dane były w tym samym arkuszu, a potem skopiowałeś dane do innego arkusza. Efekt tego jest taki, że nie działa lista rozwijana i funkcja głupieje.

Czekam na odpowiedź...

stufka pisze...

Znalazłem rozwiązanie :):)

Po prostu zaznaczyłem wszystkie dane z karty z tabelą i nadałem im nazwę "tabela_danych". Potem na karcie docelowej gdzie wykorzystuję funkcję wyszukaj.pionowo (vlookup) zamiast podawać zakres Ax:Zy użyłem tej nazwy "tabela_danych".

Wyślę ci zaraz mój plik i bedziesz mógł kolejny przykład na zastosowanie funkcji wyszukaj.pionowo.

pozdrawiam
stufka

Anonimowy pisze...

A jeżlei mam do kolumny z nazwamu firm przypożądkowane dwie kolumny z danymi, i chciałbym aby pokazywało wartoś z kolumny o danej zmiennej np: w zależności o zysku firmy.
czy jest możliwe to zrobić ?

Ynwestor pisze...

Stworzyłem specjalne forum, które będzie służyć do rozwiązywania tego rodzaju problemów. Link to: http://abc-excel.foreo.pl/ Stwórz tam temat, najlepiej wrzuć przykładowy arkusz, a ja postaram się pomóc.

Anonimowy pisze...

WIELKIE Dzięki!!! tego właśnie szukałem!!! Porada jasna i konktertna!

Anonimowy pisze...

Mam pytanie jak zrobić wykres funkcji kwadratowej w Exelu

Ynwestor pisze...

Z problemami w Excelu proszę zgłaszać się na forum Abc Excel. Pozdrawiam

Anonimowy pisze...

Świetnie że tu trafiłam, w końcu jasno wytłumaczone o co chodzi :)

Anonimowy pisze...

Wielkie dzięki! Dokładnie tego szukłem! Lekką modyfikację zrobiłem:
=WYSZUKAJ.PIONOWO(D4;Portale!A1:B5;2;FAŁSZ)
D4 - komórka listy wyboru
Portale! - nazwa arkuszu z tabelą danych
A1 - górny lewy róg tabeli danych
B5 - dolny prawy róg tabeli danych

Anonimowy pisze...

mam problem
prawidłowo użyłam funkcji wyszukaj pionowo
ale niestety wyrzuca mi "błąd"
dwa pliki z zewnątrz : tak jakby dane nie były takie same a w podglądzie jest ok
np. w jednym i drugim arkuszu ma 82008765
ale przy wyszukaj pionowo nie widzi ???
jak zmienię ręcznie w pierwszym arkuszu cyfry to jest ok
czy jest jakiś sposób aby cyfry w 1 arkuszu były widoczne dla funkcji ???

Anonimowy pisze...

Niech mi ktoś powie jak zrobić, by tę funkcję odwrócić. Czyli, by szukała po kolumnie z prawej i podawała wynik z kolumny z lewej :) Używam tego na codzień kilkanaście razy i już mnie męczy ciągłe przestawianie kolumn :P

Anonimowy pisze...

Poprzednikowi doradzę jedynie by doprawodził przeszukiwane dane do wspólnego formatu. Oczywiście nie formatowaniem komórki :P Tekst na liczbę przez "konwetuj na liczbę" - w lewym roku rekortu taki mały kwadracik ma listę wyboru (niestety jest wada - kasuje pierwsze zera). Można też odwrotnie (liczbę na tekst) wykorzystać należy banalną formułkę (dla liczby w A1) =tekst(A1;0) i wkleić wynik do A1 - POZDRÓWKI :)

Anonimowy pisze...

Dzięki

Anonimowy pisze...

Dzięki

Anonimowy pisze...

Uwielbiam program excel i naukę poprzez zabawę z nim, Twoje opisy są klarowne i bardzo pomocne przy poszukiwaniu czegoś "nowego". Gratuluję pomysłu i realizacji! :-)
preety_aga

Anonimowy pisze...

Niech mi ktoś powie jak zrobić, by tę funkcję odwrócić. Czyli, by szukała po kolumnie z prawej i podawała wynik z kolumny z lewej :) Używam tego na codzień kilkanaście razy i już mnie męczy ciągłe przestawianie kolumn :P

Anonimowy pisze...

nie wytlumaczone za dobrze:/

Anonimowy pisze...

Czy funkcja wyszukaj pionowo może zwracać liste rozwijaną ?

Prześlij komentarz

Uwaga: tylko uczestnik tego bloga może przesyłać komentarze.