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.
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ł).
=WYSZUKAJ.PIONOWO(E3;B3:C13;2;PRAWDA)
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.
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ą.
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.
=WYSZUKAJ.PIONOWO(E3;B2:C22;2;FAŁSZ)
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:
Jasno i w prosty sposób pokazujesz rozwiązania, które wydają się być bardzo skomplikowane.
Bardzo dziękuję za pomoc:)
Proszę bardzo :)
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??
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ź...
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
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ć ?
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.
WIELKIE Dzięki!!! tego właśnie szukałem!!! Porada jasna i konktertna!
Mam pytanie jak zrobić wykres funkcji kwadratowej w Exelu
Z problemami w Excelu proszę zgłaszać się na forum Abc Excel. Pozdrawiam
Świetnie że tu trafiłam, w końcu jasno wytłumaczone o co chodzi :)
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
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 ???
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
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 :)
Dzięki
Dzięki
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
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
nie wytlumaczone za dobrze:/
Czy funkcja wyszukaj pionowo może zwracać liste rozwijaną ?
Prześlij komentarz
Uwaga: tylko uczestnik tego bloga może przesyłać komentarze.