Menu
Jest wolny
rejestracja
Dom  /  Programy/ Jak korzystać z funkcji vp w Excelu. Jak działa funkcja vpr

Jak korzystać z funkcji vpr w programie Excel. Jak działa funkcja vpr

WYSZUKAJ.PIONOWO to funkcja programu Excel, która umożliwia wyszukiwanie określonej kolumny przy użyciu danych z innej kolumny. Funkcja WYSZUKAJ.PIONOWO w programie Excel służy również do przesyłania danych z jednej tabeli do drugiej. Istnieją trzy warunki:

  1. Tabele muszą znajdować się w tym samym skoroszycie programu Excel.
  2. Możesz wyszukiwać tylko wśród danych statycznych (nie formuł).
  3. Wyszukiwane hasło musi pojawić się w pierwszej kolumnie użytych danych.

Formuła WYSZUKAJ.PIONOWO w programie Excel

Składnia funkcji WYSZUKAJ.PIONOWO w zrusyfikowanym programie Excel to:

WYSZUKAJ.PIONOWO (kryterium wyszukiwania; zakres danych; numer kolumny wyników; warunek wyszukiwania)

Argumenty w nawiasach są wymagane do znalezienia wyniku końcowego.

Kryteria wyszukiwania

Adres komórki Arkusz programu Excel, który wskazuje dane do przeprowadzenia wyszukiwania w tabeli.

Zakres danych

Wszystkie adresy, wśród których przeprowadzane jest wyszukiwanie. Pierwsza kolumna powinna być tą, w której znajduje się wyszukiwane hasło.

Numer kolumny dla sumy

Numer kolumny, z której zostanie pobrana wartość po znalezieniu dopasowania.

Warunek wyszukiwania

Wartość logiczna (prawda / 1 lub fałsz / 0), która wskazuje przybliżone dopasowanie do wyszukiwania (1) lub dokładne dopasowanie (0).

WYSZUKAJ.PIONOWO w Excelu: Przykłady funkcji

Zasada działania jest prosta. Pierwszy argument zawiera kryteria wyszukiwania. Gdy tylko w tabeli zostanie znalezione dopasowanie (drugi argument), informacja jest pobierana z wymaganej kolumny (trzeci argument) znalezionego wiersza i wstawiana do komórki z formułą.
Prostym zastosowaniem funkcji WYSZUKAJ.PIONOWO jest znajdowanie wartości w arkuszu kalkulacyjnym Excel. Ma to znaczenie przy dużych ilościach danych.

Znajdźmy liczbę faktycznie wydanych produktów według nazwy miesiąca.
Wynik jest wyświetlany po prawej stronie tabeli. W komórce z adresem H3 wprowadzimy żądaną wartość. W tym przykładzie byłaby to nazwa miesiąca.
W komórce H4 wprowadź samą funkcję. Można to zrobić ręcznie lub skorzystać z kreatora. Aby zadzwonić, umieść wskaźnik nad komórką H4 i kliknij ikonę Fx obok paska formuły.


Otworzy się okno Kreatora funkcji programu Excel. Konieczne jest znalezienie w nim funkcji WYSZUKAJ.PIONOWO. Wybierz „Pełny wykaz alfabetyczny” z listy rozwijanej i zacznij pisać WYSZUKAJ.PIONOWO. Zaznacz znalezioną funkcję i naciśnij "OK".


Pojawi się okno WYSZUKAJ.PIONOWO arkusza kalkulacyjnego Excel.


Aby określić pierwszy argument (kryterium), umieść kursor w pierwszej linii i kliknij komórkę H3. Jego adres pojawi się na linii. Aby wybrać zakres, umieść kursor na drugiej linii i zacznij wybierać za pomocą myszy. Okno zwinie się do rzędu. Odbywa się to tak, aby okno nie przeszkadzało w zobaczeniu całego zasięgu i nie przeszkadzało w wykonywaniu czynności.


Gdy skończysz z wyborem i zwolnieniem lewy przycisk myszy, okno powróci do normalnego stanu, a adres zakresu pojawi się w drugiej linii. Jest obliczany od lewej górnej komórki do prawej dolnej komórki. Ich adresy są oddzielone operatorem ":" - brane są wszystkie adresy między pierwszym a ostatnim.


Przesuń kursor do trzeciego wiersza i odczytaj, z której kolumny zostaną pobrane dane po znalezieniu dopasowania. W naszym przykładzie jest to 3.


Pozostaw ostatnią linię pustą. Domyślnie wartość będzie równa 1, zobaczmy jaką wartość wyświetla nasza funkcja. Kliknij OK.


Wynik jest zniechęcający. „N/A” oznacza nieprawidłowe dane dla funkcji. Nie określiliśmy wartości w komórce H3, a funkcja szuka pustej wartości.


Wpiszmy nazwę miesiąca, a wartość się zmieni.


Tylko to nie odpowiada rzeczywistości, bo faktyczna liczba produktów wydanych w styczniu to 2000.
Jest to efekt argumentu Warunek wyszukiwania. Zmień go na 0. Aby to zrobić, umieść wskaźnik nad komórką z formułą i ponownie naciśnij Fx. W oknie, które się otworzy, wpisz „0” w ostatnim wierszu.


Kliknij OK. Jak widać, wynik się zmienił.


Aby sprawdzić drugi warunek z początku naszego artykułu (funkcja nie przeszukuje formuł), zmienimy warunki dla funkcji. Zwiększmy zakres i spróbujmy wyprowadzić wartość z kolumny z obliczonymi wartościami. Określ wartości, jak pokazano na zrzucie ekranu.


Kliknij OK". Jak widać, wynik wyszukiwania okazał się być 0, chociaż tabela zawiera wartość 85%.


WYSZUKAJ.PIONOWO w programie Excel „rozumie” tylko stałe wartości.

Porównanie danych dwóch tabel Excel

WYSZUKAJ.PIONOWO w programie Excel może służyć do porównywania danych z dwóch tabel. Załóżmy na przykład, że mamy dwa arkusze robocze z danymi dotyczącymi wyników dwóch warsztatów. Możemy dopasować rzeczywistą wersję dla obu. Przypomnij sobie, że aby przełączać się między arkuszami, użyj ich skrótów u dołu okna.

Na dwóch arkuszach mamy te same tabele z różnymi danymi.

Jak widać, ich plan wydania jest taki sam, ale rzeczywisty jest inny. Przełączanie i porównywanie linia po linii, nawet dla niewielkich ilości danych, jest bardzo niewygodne. Na trzecim arkuszu stworzymy tabelę z trzema kolumnami.

Wprowadźmy funkcję WYSZUKAJ.PIONOWO w komórce B2. Jako pierwszy argument wskażemy komórkę z miesiącem na bieżącym arkuszu i wybierzemy zakres z arkusza "Sklep1". Aby zapobiec przesuwaniu się zakresu podczas kopiowania, naciśnij klawisz F4 po wybraniu zakresu. To sprawi, że link będzie absolutny.


Rozciągnij formułę, aby wypełnić całą kolumnę.

Podobnie wprowadź formułę w następnej kolumnie, wybierz tylko zakres w arkuszu „Sklep2”.


Po skopiowaniu otrzymasz raport podsumowujący z dwóch arkuszy.

Podmiana danych z jednej tabeli Excela na drugą

Ta czynność jest wykonywana w ten sam sposób. Dla naszego przykładu nie możesz utworzyć oddzielnej tabeli, ale po prostu wpisać funkcję w kolumnie którejkolwiek z tabel. Pokażmy to na pierwszym przykładzie. Ustaw wskaźnik na ostatnią kolumnę.


A w komórce G3 umieść funkcję WYSZUKAJ.PIONOWO. Ponownie bierzemy zasięg z sąsiedniego arkusza.


W rezultacie kolumna drugiej tabeli zostanie skopiowana do pierwszej.


To wszystkie informacje o niepozornym, ale użyteczna funkcja WYSZUKAJ.PIONOWO w Excelu dla manekinów. Mamy nadzieję, że pomoże Ci w rozwiązywaniu problemów.

Miłego dnia!

Przykłady użycia formuły Excela z funkcją WYSZUKAJ.PIONOWO pozwalają poznać zasadę działania i uniknąć błędów:

  1. Funkcja uruchamia się ustaw parametry, posługując się kryterium z pierwszej tabeli, odnosi się do drugiego zakresu, z którego wartości będą „podciągane”.
  2. Z najwyższej komórki kolumny kryteriów przegląda wszystkie wartości.
  3. Po znalezieniu dopasowań z określonymi kryteriami, zlicza określoną liczbę kolumn po prawej stronie i kończy w komórce, w której znajduje się pożądana wartość, która jest „przeciągana” do komórki, w której określona jest formuła.

Przykład użycia funkcji WYSZUKAJ.PIONOWO

W podanym przykładzie chcesz poznać koszt sprzedanego towaru. Aby ją obliczyć, należy znaleźć iloczyn ilości i ceny (kolumny z danymi znajdują się w sąsiednich kolumnach). W pustej kolumnie obok wpisują wzór na iloczyn dwóch komórek i rozciągają go na koniec listy towarów.

Oryginalna informacja może być zawarta w różnych zakresach i w innej kolejności. Pierwsza tabela wskazuje ilość sprzedanego towaru:

Drugi dotyczy cen:

Jeśli towary w obu tabelach pokrywają się, używając kombinacji klawiszy Ctrl + C i Ctrl + V, wskaźniki ceny można łatwo zastąpić ilością. Ale tabele mają inną kolejność pozycji.

Informacje o wielu produktach nie odpowiadają sąsiednim wskaźnikom. W tym przypadku wykluczona jest możliwość zapisania wzoru na mnożenie i „ciągnięcie” w dół dla wszystkich pozycji.

Jak zastąpić ceny z drugiej tabeli odpowiednimi wskaźnikami ilości z pierwszej, czyli cenę pozycji A na ilość pozycji A, cenę B na ilość B.

Podczas korzystania z funkcji WYSZUKAJ.PIONOWO ceny z drugiej tabeli są „podciągane” do ilości z pierwszej tabeli, zgodnie z nazwami towarów.

Procedura:

W pierwszej tabeli musisz dodać nową kolumnę, w której ceny będą ustalane za pomocą formuły WYSZUKAJ.PIONOWO.

Funkcja WYSZUKAJ.PIONOWO jest wywoływana za pomocą Kreatora funkcji lub jest zapisywana ręcznie.

Wywołanie opcji przez Kreatora polega na aktywowaniu komórki, w której będzie wskazywana formuła, i kliknięciu przycisku f (x) na początku paska formuły. W wyświetlonym oknie dialogowym Kreatora musisz określić WYSZUKAJ.PIONOWO z proponowanej listy.

Formuła WYSZUKAJ.PIONOWO w programie Excel for Dummies wymaga prawidłowego wypełnienia pól w oknie dialogowym Kreator funkcji:

  1. Pierwsza kolumna „Wyszukaj wartość” pozwala ustawić kryteria dla komórki, w której zostanie zapisana formuła. W tym przykładzie komórka zawiera element „A”.
  2. Następna linia"Tabela". Wprowadzając zakres danych, możesz znaleźć żądane wartości. Na przykład użyto drugiej tabeli z cenami. Ponieważ ceny "podciągają" do ilości. Ważne jest, aby wziąć pod uwagę potrzebę, aby skrajna lewa (pierwsza lewa) kolumna wybranego zakresu zawierała podobne kryteria wyszukiwania. W przykładzie jest to kolumna z nazwą produktu. Następnie tabela jest podświetlana z prawej strony aż do kolumny zawierającej wymagane dane (ceny). Możesz rozszerzyć wybór w prawo, ale nie wpłynie to na nic, ponieważ kolumna z wymaganymi wskaźnikami zostanie jednoznacznie zidentyfikowana następny parametr... Ważne jest, aby wybrane tabele zaczynały się od kolumny kryterium i zawierały interesującą kolumnę danych.
  3. „Numer kolumny” – liczby, którymi oddzielane są kolumny z wymaganymi danymi (cenami) od kolumny z kryteriami (nazwą produktu). Odliczanie rozpoczyna się od samej kolumny kryteriów. Jeżeli w drugiej tabeli obie kolumny znajdują się obok siebie, należy wskazać cyfrę 2 (pierwsza to kryteria, druga to ceny). Możliwe jest umieszczenie danych w odniesieniu do kryteriów dla 10 lub 20 kolumn. To nie ma znaczenia, Excel wykona poprawne obliczenia.
  4. Ostatnia kolumna to „Widok interwałowy”, w którym wskazane są opcje wyszukiwania: dokładne (0) lub przybliżone (1) dopasowania kryteriów. Powinieneś teraz podać 0 (lub FALSE).

Pozostaje kliknąć przycisk OK lub Enter. Jeśli dane zostaną wprowadzone poprawnie i w obu tabelach jest kryterium, w miejscu podanej formuły zostanie wyświetlony odpowiedni wskaźnik. Wystarczy rozciągnąć (lub skopiować) formułę do ostatniego wiersza tabeli.

Dalsze obliczenia są dokonywane przez określenie iloczynu ilości i ceny, rozciągając formułę do końca tabeli, ponieważ pary cena-ilość są już takie same.

Alternatywną opcją jest zapisanie formuły WYSZUKAJ.PIONOWO w komórce przez dodanie znaku „;” między parametrami.

Dozwolone jest przepisywanie nazwy „vpr” małymi literami, rejestr nie ma znaczenia.

Jak korzystać z funkcji Wklej specjalnie?

W wyniku użycia funkcji WYSZUKAJ.PIONOWO obie tabele pozostają połączone. Gdy dokonasz zmian w cenniku, zmieni się również koszt towaru przyjmowanego do magazynu. Aby uniknąć tej sytuacji, powinieneś użyć opcji „Wklej specjalnie”.

  • Wybierz kolumnę z podanymi cenami.
  • Kliknij prawym przyciskiem myszy - "Kopiuj".
  • Bez usuwania zaznaczenia kliknij prawym przyciskiem myszy - „Wklej specjalnie”.
  • Ustaw pole wyboru na „Wartości”. OK.

W komórkach pozostaną tylko wartości, formuła zostanie anulowana.

Jak szybko porównać dwie tabele za pomocą funkcji WYSZUKAJ.PIONOWO?

Funkcja służy do mapowania wartości w dużych tabelach. Na przykład, jeśli nastąpią zmiany w cenniku. Chcesz porównać stare ceny z nowymi.

Procedura:

W starym cenniku musisz utworzyć kolumnę „Nowe ceny”.

Wybierz pierwszą komórkę i wybierz formułę WYSZUKAJ.PIONOWO. Ustaw argumenty (patrz wyżej). Na przykład:

Podana formuła informuje o konieczności wzięcia nazwy produktu z zakresu A2:A15, obejrzenia jej w „Nowej cenie” za pomocą kolumny A. Następnie skorzystaj z danych z drugiej kolumny nowego cennika (nowe ceny) i wprowadź je do komórki C2.

Wybór sposobu wyświetlania danych pozwala na ich porównanie, określenie różnicy liczbowej i procentowej.

Formuła WYSZUKAJ.PIONOWO w Excelu z wieloma warunkami

Powyżej przykłady analiz zostały rozpatrzone pod jednym warunkiem - nazwą produktu. W praktyce może okazać się konieczne porównanie kilku zakresów z danymi i dobranie wartości według 2, 3 kryteriów. Poniżej znajduje się tabela przedstawiająca przegląd wizualny:

Aby wyszukać cenę, po jakiej tektura falista została przywieziona z UAB „Wostok”, należy stworzyć 2 warunki: według nazwy materiału i dostawców.

Główna trudność polega na obecności kilku nazw produktów od jednego dostawcy.

Instrukcje krok po kroku obejmują szereg czynności:

Dodanie skrajnej lewej kolumny do tabeli (ważne!), Łączenie „Dostawcy” i „Materiały”.

Łącząc wymagane kryteria przez analogię:

Umieszczenie kursora w odpowiednim miejscu i ustawienie argumentów formuły:

Excel wyszukuje odpowiednią cenę.

Szczegółowe rozpatrzenie wzoru:

  1. Co szukać.
  2. Gdzie patrzeć.
  3. Jakie dane wziąć.

Formuły i listy rozwijane WYSZUKAJ.PIONOWO

Pozwól, aby niektóre dane były wprowadzane w formie listy rozwijanej. W tym przykładzie są to Materiały. Ważne jest, aby skonfigurować funkcję tak, aby po wybraniu nazwy wyświetlała się cena.

Aby utworzyć listę rozwijaną, musisz wykonać szereg czynności:

Umieść kursor w komórce E8, w której planujesz umieścić listę.

Otwórz zakładkę „Dane”. Sprawdź menu danych.

Wybierz typ danych - "Listy". Źródła - zakres z nazwami materiałów.

Po kliknięciu OK zostanie utworzona lista rozwijana.

Pozostaje ustawić funkcję, która przy wyborze materiałów pozwoli wyświetlić odpowiednie wartości w kolumnie ceny. Umieść kursor w komórce E9 (gdzie będzie wyświetlana cena).

Otwórz „Kreator funkcji” i wybierz WYSZUKAJ.PIONOWO.

Pierwszy argument to „Wyszukaj wartość” — komórki z listami rozwijanymi. Tabela - asortyment z nazwą materiału i cenami. Kolumna - 2. Funkcja zostanie wyświetlona w następującym formacie:

Pozostaje nacisnąć ENTER i cieszyć się wynikiem.

Po zmianie materiału cena zmienia się:

Tak działa lista rozwijana w programie Excel z funkcją WYSZUKAJ.PIONOWO. Wszystko odbywa się w trybie automatycznym w ciągu kilku sekund.

Funkcje pracy z formułą WYSZUKAJ.PIONOWO

Przed użyciem funkcji WYSZUKAJ.PIONOWO w programie Excel należy zapoznać się z jej funkcjami:

  1. Korzystając z opcji dla wielu komórek, określając formułę w jednej z nich i kopiując do pozostałych, ważne jest kontrolowanie względności i bezwzględności odwołań. W WYSZUKAJ.PIONOWO kryteria (pierwsze pola) muszą mieć odniesienia względne (bez $), co jest określane przez obecność własnych kryteriów dla każdej komórki. Zakresy muszą mieć odniesienia bezwzględne (adresy zakresów są oznaczone znakiem $). W przeciwnym razie podczas kopiowania formuł zakres „spłynie” w dół, a wiele wskaźników nie zostanie wyświetlonych w wyszukiwaniu, ponieważ nie będzie gdzie szukać.
  2. Numery kolumn określone w trzecim polu „Numer kolumny” podczas korzystania z Kreatora funkcji muszą być liczone z kolumny kryteriów.
  3. W przypadku braku kryteriów w tabelach, w których wyszukiwane są dane, pojawia się błąd #N/A, co powoduje trudności w obliczeniu sum (suma, średnia itp.). Aby rozwiązać problem, możesz użyć funkcji SUMA.JEŻELI (zamiast WYSZUKAJ.PIONOWO) lub ESLIOSHIBKA (umieść przed WYSZUKAJ.PIONOWO).
  4. W przypadku używania wartości liczbowych zamiast kryteriów (kody, numery artykułów) formuła WYSZUKAJ.PIONOWO ma zwiększoną wrażliwość na formaty komórek. Jeśli w jednej tabeli znajdują się kryteria w formacie liczbowym, a w innej w formacie tekstowym, to jeśli wskaźniki są całkowicie zgodne, zostanie wyświetlony błąd # N / A. Wystarczy sprawdzić, czy format pola pasuje do kryteriów i ujednolicić je lub skorzystać z funkcji SUMA.JEŻELI (format nie ma dla tego znaczenia).
  5. Nie zaleca się stosowania długich kryteriów w celu zmniejszenia prawdopodobieństwa „losowych” różnic. Na przykład obecność dodatkowej spacji między słowami lub jednej błędnej litery spowoduje brak porównywalności tych samych wartości kryteriów. Numery pozycji lub kody kreskowe produktów są w porządku, ale nazwy wielowyrazowe nie są zalecane jako kryteria.
  6. Funkcje WYSZUKAJ.PIONOWO z tabeli z wymaganymi danymi podają pierwszy wskaźnik od góry. Jeśli w drugiej tabeli, z której dane są „wyciągane”, znajduje się liczba komórek z tymi samymi kryteriami, to pierwszy wskaźnik od góry jest przechwytywany w wybranym zakresie WYSZUKAJ.PIONOWO. Jest to ważne do rozważenia. Na przykład, jeśli konieczne jest zawężenie ilości z innego zakresu do ceny towaru, a tam ten produkt powtarza się w kilku kolumnach, to pierwsze wskaźniki (ilość) od góry dojdą do ceny, inne pozostaną ignorowane.
  7. Wymagana jest obecność ostatniego parametru w postaci liczby 0 (zero). W przeciwnym razie formuła działa krzywo.
  8. Po użyciu funkcji WYSZUKAJ.PIONOWO zaleca się natychmiastowe usunięcie formuły, pozostawiając tylko uzyskane wyniki. Procedura wymaga podświetlenia zakresu z uzyskanymi wynikami za pomocą przycisku „kopiuj” i ustawienia wartości w tym miejscu za pomocą specjalna wkładka... Umieszczając tabele w różnych skoroszytach Excela, wygodniej będzie je rozbić stosunki zewnętrzne(pozostawiając dane) za pomocą narzędzia, pod Dane → Edytuj linki.

Po wywołaniu funkcji zrywania linków zewnętrznych pojawi się okno dialogowe, w którym pozostaje aktywować przycisk „Zerwij link” i „Zamknij”.

Spowoduje to jednoczesne usunięcie wszystkich linków zewnętrznych.

  1. Alternatywną opcją WYSZUKAJ.PIONOWO jest opcja WYSZUKAJ.POZIOMO. Różnica polega na poziomym widoku listy danych.

Z pewnością wiele aktywni użytkownicy edytor arkuszy kalkulacyjnych Excel okresowo musiał radzić sobie z sytuacjami, w których konieczne stało się podstawianie wartości z jednej tabeli do drugiej. Wyobraź sobie, że pewien produkt trafił do Twojego magazynu. Do dyspozycji mamy dwa pliki: jeden z listą nazw otrzymanych towarów, drugi - cennik tego właśnie produktu. Po otwarciu cennika stwierdzamy, że jest w nim więcej pozycji i nie są one ułożone w takiej samej kolejności jak w pliku z listą nazw. Mało kto z nas spodoba się pomysłowi ręcznego sprawdzania obu plików i przenoszenia cen z jednego dokumentu na drugi. Oczywiście w przypadku, gdy mówimy o 5-10 pozycjach, mechaniczne wprowadzanie danych jest całkiem możliwe, ale co jeśli ilość sztuk przekroczy 1000? W tym przypadku Excel i jego magiczna funkcja WYSZUKAJ.PIONOWO (lub vlookup, jeśli mówimy o angielskiej wersji programu) pomogą nam poradzić sobie z monotonną pracą.


Tak więc na początku naszej pracy nad przekształcaniem danych z jednej tabeli do drugiej należy zrobić mały przegląd funkcji WYSZUKAJ.PIONOWO. Jak zapewne już zrozumiałeś, funkcja vlookup umożliwia przesyłanie danych z jednej tabeli do drugiej, a tym samym automatyczne wypełnianie potrzebnych nam komórek. Aby funkcja WYSZUKAJ.PIONOWO działała poprawnie, zwróć uwagę na obecność scalonych komórek w nagłówkach tabeli. Jeśli są, będziesz musiał je rozbić.

Powiedzmy, że musimy wypełnić „Tabelę zamówień” danymi z „Cennika”

Stajemy więc przed zadaniem przeniesienia cen istniejących towarów do tabeli z ich nazwami i obliczenia całkowitego kosztu każdego produktu. W tym celu musimy wykonać następujący algorytm:

  1. Najpierw przynieś Excel arkusz kalkulacyjny w potrzebnej formie. Do przygotowanej macierzy danych dodaj dwie kolumny o nazwach „Cena” i „Koszt”. Wybierz format waluty dla komórek w nowo utworzonym zakresie kolumn.
  2. Teraz aktywuj pierwszą komórkę w bloku „Cena” i wywołaj „Kreatora funkcji”. Można to zrobić, klikając przycisk „fx” znajdujący się przed paskiem formuły lub przytrzymując kombinację klawiszy „Shift + F3”. W otwartym oknie dialogowym znajdź kategorię „Referencje i tablice”. Tutaj nie interesuje nas nic innego niż funkcja WYSZUKAJ.PIONOWO. Wybierz i kliknij "OK". Przy okazji należy powiedzieć, że funkcję WYSZUKAJ.PIONOWO można wywołać za pośrednictwem zakładki Formuły, której rozwijana lista zawiera również kategorię Referencje i tablice.
  3. Po uruchomieniu WYSZUKAJ.PIONOWO otworzy się przed Tobą okno z listą argumentów wybranej funkcji. W polu „Wyszukaj wartość” należy wpisać zakres danych zawarty w pierwszej kolumnie tabeli z listą otrzymanych towarów i ich ilości. Oznacza to, że musisz powiedzieć programowi Excel, co dokładnie powinien znaleźć w drugiej tabeli i przenieść to do pierwszej.
  4. Po wskazaniu pierwszego argumentu możesz przejść do drugiego. W naszym przypadku drugim argumentem jest tabela z cennikiem. Umieść kursor myszy w polu argumentu i przejdź do arkusza z listą cen. Wybierz ręcznie asortyment z komórkami znajdującymi się w obszarze kolumn z nazwami produktów handlowych i ich cenami. Powiedz Excelowi, które wartości zmapować do funkcji WYSZUKAJ.PIONOWO.
  5. Aby Excel nie pomylił się i nie odwoływał do potrzebnych danych, ważne jest, aby naprawić podany do niego link. W tym celu wybierz żądane wartości w polu „Tabela” i naciśnij klawisz F4. Jeśli wszystko zostało wykonane poprawnie, na ekranie powinien pojawić się znak $.
  6. Teraz przechodzimy do pola argumentu „Numer strony” i nadajemy mu wartość „2”. Blok ten zawiera wszystkie dane, które należy przesłać do naszego arkusza roboczego, dlatego ważne jest, aby do „Widoku interwałowego” przypisać fałszywą wartość (ustawić pozycję na „FALSE”). Jest to konieczne, aby funkcja WYSZUKAJ.PIONOWO działała tylko z dokładnymi wartościami i nie zaokrąglała ich.

Teraz, gdy wszystkie niezbędne czynności zostały wykonane, wystarczy je potwierdzić, naciskając przycisk „OK”. Gdy tylko zmienią się dane w pierwszej komórce, będziemy musieli zastosować funkcję WYSZUKAJ.PIONOWO do całego dokumentu Excel. Aby to zrobić, wystarczy pomnożyć WYSZUKAJ.PIONOWO przez całą kolumnę „Cena”. Można to zrobić, przeciągając prawy dolny róg komórki ze zmienioną wartością na sam dół kolumny. Jeśli wszystko się ułożyło, a dane zmieniły się zgodnie z potrzebami, możemy przystąpić do obliczania całkowitego kosztu naszych towarów. Aby wykonać tę akcję, musimy znaleźć iloczyn dwóch kolumn - "Ilości" i "Ceny". Ponieważ Excel zawiera wszystkie formuły matematyczne, obliczenia można wprowadzić do „paska formuł” za pomocą znanej już ikony „fx”.

Ważny punkt

Wydawałoby się, że wszystko jest gotowe i VLOOKUP poradził sobie z naszym zadaniem, ale tak nie było. Faktem jest, że funkcja WYSZUKAJ.PIONOWO jest nadal aktywna w kolumnie „Cena”, o czym świadczy wyświetlenie tej ostatniej na pasku formuły. Oznacza to, że oba nasze stoły pozostają ze sobą połączone. Taki tandem może doprowadzić do tego, że gdy zmienią się dane w tabeli z cennikiem, zmienią się również informacje zawarte w naszym pliku roboczym z wykazem towarów.

Najlepiej tego uniknąć, dzieląc dwie tabele. Aby to zrobić, musimy zaznaczyć komórki znajdujące się w zakresie kolumny „Cena” i kliknąć je prawym przyciskiem myszy. W oknie, które się otworzy, wybierz i aktywuj opcję „Kopiuj”. Następnie, bez usuwania zaznaczenia z wybranego obszaru komórek, ponownie naciśnij prawy przycisk myszy i wybierz opcję „Wklej specjalnie”.

Aktywacja tej opcji spowoduje otwarcie okna dialogowego na ekranie, w którym należy zaznaczyć pole obok kategorii „Wartość”. Potwierdź podjęte działanie, klikając przycisk „OK”.

Wróć do naszego paska formuły i sprawdź, czy funkcja WYSZUKAJ.PIONOWO jest aktywna w kolumnie Cena. Jeśli widzisz tylko wartości liczbowe zamiast formuły, wszystko się udało, a funkcja WYSZUKAJ.PIONOWO jest wyłączona. To znaczy, związek między nimi Pliki Excel uszkodzony i nie ma zagrożenia nieplanowanych zmian lub usunięcia danych dołączonych z tabeli z ceną. Teraz możesz bezpiecznie korzystać z dokumentu arkusza kalkulacyjnego i nie martwić się o to, co się stanie, jeśli „Cennik” zostanie zamknięty lub przeniesiony w inne miejsce.

Jak porównać dwie tabele w programie Excel?

Dzięki funkcji WYSZUKAJ.PIONOWO możesz dopasować wiele różne znaczenia aby na przykład porównać, jak zmieniły się ceny istniejącego produktu. Aby to zrobić, musisz napisać VLOOKUP w pustej kolumnie i odnieść funkcję do zmienionych wartości, które znajdują się w innej tabeli. Najlepiej, jeśli kolumna „Nowa cena” znajduje się bezpośrednio po kolumnie „Cena”. Dzięki temu rozwiązaniu zmiany cen będą bardziej wizualne dla porównania.

Umiejętność pracy w wielu warunkach

Kolejną niewątpliwą zaletą funkcji WYSZUKAJ.PIONOWO jest możliwość pracy z kilkoma parametrami charakterystycznymi dla Twojego produktu. Aby znaleźć produkt według dwóch lub więcej cech, musisz:

  1. Utwórz dwa (lub, jeśli to konieczne, więcej) wyszukiwane hasła.
  2. Dodaj nową kolumnę, w której podczas działania funkcji zostaną dodane wszystkie pozostałe kolumny, według których wyszukiwany jest produkt.
  3. W wynikowej kolumnie, zgodnie z powyższym algorytmem, wprowadzamy znaną już formułę funkcji WYSZUKAJ.PIONOWO.

Podsumowując, należy powiedzieć, że wsparcie programu Excel dla funkcji takiej jak WYSZUKAJ.PIONOWO znacznie ułatwia pracę z informacjami tabelarycznymi. Zapraszam do korzystania z funkcji WYSZUKAJ.PIONOWO z ogromne ilości dane, ponieważ bez względu na to, jak są sformatowane, zasada działania jest zawsze taka sama. Wszystko, co musisz zrobić, to poprawnie zdefiniować jego argumenty.

Korzystanie z funkcji WYSZUKAJ.PIONOWO podczas pracy w Excelu możesz wyodrębnić wymagane informacje z arkuszy kalkulacyjnych. Excel oferuje kilka funkcji do tych celów, ale WYSZUKAJ.PIONOWO najczęstszy z nich. W tym samouczku zapoznamy się z funkcją WYSZUKAJ.PIONOWO, a także rozważ jego możliwości na prostym przykładzie.

Funkcjonować WYSZUKAJ.PIONOWO(skanowanie w pionie) szuka wartości w skrajnej lewej kolumnie badanego zakresu, a następnie zwraca wynik z komórki, która znajduje się na przecięciu znalezionego wiersza i określonej kolumny.

Przykład 1

Na przykład poniższy obrazek pokazuje listę 10 nazwisk, każde nazwisko ma swój numer. Pod podanym numerem wymagane jest wyodrębnienie nazwiska.

Korzystanie z funkcji WYSZUKAJ.PIONOWO jest to dość proste:

Ze wzoru wynika, że ​​pierwszy argument funkcji WYSZUKAJ.PIONOWO to komórka C1, w której wskazujemy żądaną liczbę. Druga to gama A1:B10, która pokazuje, gdzie szukać. A ostatnim argumentem jest numer kolumny, z której ma zostać zwrócony wynik. W naszym przykładzie jest to druga kolumna. Klikając Wejść, otrzymamy pożądany rezultat:

Przykład 2

Weźmy inny przykład. Poniższy obrazek pokazuje te same 10 nazwisk, co poprzednio, tylko liczby pochodzą z przepustkami.

Jeśli spróbujemy znaleźć nazwisko dla nieistniejącego numeru (np. 007), to formuła zamiast podać błąd, bezpiecznie zwróci nam wynik. Jak to może być?

Chodzi o to, że funkcja WYSZUKAJ.PIONOWO posiada również czwarty argument, który pozwala ustawić tzw. skanowanie interwałowe. Może mieć dwa znaczenia: PRAWDA i FAŁSZ. Co więcej, pominięcie argumentu jest równoznaczne z prawdą.

W przypadku, gdy czwarty argument ma wartość TRUE, funkcja najpierw szuka dokładnego dopasowania, a jeśli takiego dopasowania nie ma, to najbliższego, czyli mniejszego od podanego. Dlatego funkcja WYSZUKAJ.PIONOWO zwrócił nazwisko „Panczenko”. Gdybyśmy dali „008”, to formuła zwróciłaby również „Panczenko”.

W przypadku, gdy czwarty argument funkcji WYSZUKAJ.PIONOWO ma wartość logiczną FALSE, funkcja wyszukuje dokładne dopasowanie. Na przykład na poniższym rysunku formuła zwróci błąd, ponieważ nie znaleziono dokładnego dopasowania.

Jeśli czwarty argument funkcji WYSZUKAJ.PIONOWO ma wartość TRUE lub jest pominięta, kolumna znajdująca się najbardziej po lewej stronie musi być posortowana w porządku rosnącym. Jeśli tego nie zrobisz, funkcja WYSZUKAJ.PIONOWO może zwrócić zły wynik.

Dla tych, którzy lubią tworzyć nie pionowe, ale poziome stoły, istnieje odpowiednik w Excelu WYSZUKAJ.PIONOWO ale do wyszukiwania poziomego.

WYSZUKAJ.PIONOWO w programie Excel

Microsoft Excel ma funkcję GPR(widok poziomy), który jest bardzo podobny do WYSZUKAJ.PIONOWO, jedyną różnicą jest to, że zasięg jest oglądany nie w pionie, ale w poziomie. GPR szukam ustalić wartość v Górna linia zakres i zwraca wynik z komórki, która znajduje się na przecięciu znalezionej kolumny i określonego wiersza.

Jeśli przedstawisz powyższy przykład w formie poziomej, formuła będzie wyglądać tak:

Jak widać, wszystko jest dość proste!

To kończy naszą lekcję. Dzisiaj spotkaliśmy się chyba z najpopularniejszym narzędziem Microsoft Excel - Funkcja WYSZUKAJ.PIONOWO i przeanalizowałem jego możliwości przez kilka proste przykłady... Mam nadzieję, że ten samouczek był pomocny. Wszystkiego najlepszego i sukcesu w nauce Excela.

Funkcja WYSZUKAJ.PIONOWO () , angielska wersja funkcji WYSZUKAJ.PIONOWO (), szuka wartości w pierwszej (najbardziej od lewej) kolumnie tabeli i zwraca wartość z tego samego wiersza, ale z innej kolumny w tabeli.

Funkcja WYSZUKAJ.PIONOWO() jest jedną z najczęściej używanych w programie EXCEL, więc przyjrzyjmy się jej szczegółowo.

W tym artykule przyjęto niestandardowe podejście: nacisk nie kładzie się na samą funkcję, ale na te zadania, które można rozwiązać za jej pomocą.

Składnia funkcji

WYSZUKAJ.PIONOWO (wyszukaj_wartość; tabela; numer_kolumny; widok_zakresu)

Wyszukaj_wartość to wartość, którą próbujesz znaleźć w kolumnie danych. Wyszukaj_wartość może być liczbą lub tekstem, ale najczęściej jest to liczba, która jest wyszukiwana. Szukana wartość musi znajdować się w pierwszej (najbardziej od lewej) kolumnie zakresu komórek określonego w Tabela .

Tabela - odwołanie do zakresu komórek. Wyszukiwana jest lewa kolumna tabeli Wyszukaj_wartość, a z kolumn znajdujących się po prawej stronie wyświetlany jest odpowiedni wynik (chociaż w zasadzie można wyprowadzić wartość z lewej kolumny (w tym przypadku będzie to szukana_wartość)). Często lewa kolumna nazywa się klucz... Jeśli pierwsza kolumna nie zawiera szukana_wartość , # Nie dotyczy.

Numer_kolumny- numer kolumny Stoły z którego wyprowadzić wynik. Skrajna lewa kolumna (klucz) ma numer 1 (jest wyszukiwana).

Parametr interwał_widok może przyjmować 2 wartości: TRUE (wyszukiwana jest wartość najbliższa kryterium lub z nim pokrywająca się) i FALSE (wyszukiwana jest wartość dokładnie pokrywająca się z kryterium). TRUE zakłada, że ​​pierwsza kolumna w Tabela posortowane alfabetycznie lub rosnąco. Ta metoda jest używana w funkcji domyślnej, chyba że określono inaczej.

W poniższym artykule omówiono popularne problemy, które można rozwiązać za pomocą funkcji WYSZUKAJ.PIONOWO().

Zadanie 1. Katalog towarów

Niech zostanie podana oryginalna tabela (patrz. przykładowe odniesienie do arkusza pliku).

Zadanie polega na wybraniu żądanego kod sprzedawcy produkt, wycofaj go Nazwa oraz Cena.

Notatka... Jest to „klasyczny” problem przy korzystaniu z funkcji WYSZUKAJ.PIONOWO() (patrz artykuł).

Do wypłaty Nazwy użyj formuły = WYSZUKAJ.PIONOWO ($E9, $A$13:$C$19,2,FAŁSZ) lub = WYSZUKAJ.PIONOWO ($ E9; $ A $ 13: $ C $ 19; 2; PRAWDA) lub = WYSZUKAJ.PIONOWO ($E9;$A$13:$C$19;2) (tj. wartość parametru Interwał_widok można ustawić na FALSE lub TRUE lub całkowicie pominąć). Wartość parametru numer_kolumny musisz ustawić = 2, ponieważ numer kolumny Nazwa jest równa 2 (kolumna kluczowa ma zawsze numer 1).

Do wypłaty Ceny użyj podobnej formuły = WYSZUKAJ.PIONOWO ($ E9; $ A $ 13: $ C $ 19; 3; FAŁSZ)(wartość parametru numer_kolumny musi być ustawiony = 3).

Kluczowa kolumna w naszym przypadku zawiera liczby i musi zawierać żądaną wartość (warunek problemu). Jeśli pierwsza kolumna nie zawiera pozycji, której szukasz , wtedy funkcja zwraca wartość błędu # Nie dotyczy. Może się to zdarzyć na przykład z literówką podczas wpisywania numeru artykułu. Aby nie popełnić błędu podczas wpisywania żądanego artykułu, możesz użyć (patrz komórka E9 ).

Oczywiste jest, że w naszym zadaniu kolumna kluczowa nie powinna zawierać duplikatów (takie znaczenie ma artykuł, który jednoznacznie identyfikuje produkt). W przeciwnym razie zostanie wyświetlona najwyższa wartość.

Przy rozwiązywaniu takich problemów kolumna kluczowa jest lepsza wcześniej (pomoże to również zrobić) Lista rozwijana jaśniej). Również w przypadku listy nieposortowanej, WYSZUKAJ.PIONOWO() z parametrem Interwał_widok TRUE (lub pominięte) nie będzie działać.

Rozważane są również alternatywne formuły (otrzymamy ten sam wynik) za pomocą funkcji INDEX (), SEARCH () i WYSZUKAJ (). Jeśli kolumna klucza (kolumna z jednostkami SKU) nie znajduje się na skrajnej lewej stronie tabeli, funkcja WYSZUKAJ.PIONOWO () nie ma zastosowania. W takim przypadku musisz użyć alternatywnych formuł. Kilka funkcji INDEX (), SEARCH () tworzy tak zwaną „prawą funkcję WYSZUKAJ.PIONOWO”: = INDEKS (B13: B19; SZUKAJ ($ E $ 9; $ A $ 13: $ A $ 19,0); 1)

V przykładowe odniesienie do arkusza pliku widać, że formuły mają zastosowanie do kolumn kluczowych zawierających wartości tekstowe, ponieważ artykuł jest często wartością tekstową. Również problem został rozwiązany dla nieposortowanej kolumny klucza.

Notatka... Nigdy nie używaj funkcji WYSZUKAJ.PIONOWO() z parametrem Interwał_widok TRUE (lub pominięte), jeśli kolumna klucza nie jest posortowana w kolejności rosnącej. wynik formuły jest nieprzewidywalny (jeśli funkcja WYSZUKAJ.PIONOWO() znajdzie wartość większą niż pożądana, to wyprowadza wartość znajdującą się o jeden wiersz powyżej niej).

Zadanie 2. Znajdowanie najbliższego numeru

Załóżmy, że chcesz znaleźć produkt, którego cena jest równa lub najbliższa żądanej.

Aby użyć funkcji WYSZUKAJ.PIONOWO() do rozwiązania tego problemu, musisz spełnić kilka warunków:

  1. Przeszukiwana kolumna klucza musi znajdować się w skrajnej lewej kolumnie tabeli;
  2. Kolumna kluczowa musi być posortowana w porządku rosnącym;
  3. Wartość parametru Interwał_widok musi być ustawiony na PRAWDA lub całkowicie pominięty.

Do wypłaty Nazwa przedmiotu użyj formuły = WYSZUKAJ.PIONOWO ($A7;$A$11:$B$17;2;PRAWDA)

Aby wyświetlić znalezioną cenę (niekoniecznie będzie pokrywać się z podaną) użyj wzoru: = WYSZUKAJ.PIONOWO ($A7,$A$11:$B$17,1,PRAWDA)

Jak widać na powyższym obrazku, znaleziono VLOOKUP () najwyższa cena, który jest mniejszy lub równy podanemu (patrz. przykładowy arkusz pliku "Znajdź najbliższy numer"). Wynika to ze sposobu, w jaki funkcja wyszukuje: jeśli funkcja WYSZUKAJ.PIONOWO() znajdzie wartość większą niż żądana wartość, to wyprowadza wartość znajdującą się o jeden wiersz powyżej tej wartości. W konsekwencji, jeśli żądana wartość jest mniejsza niż minimum w kolumnie klucza, funkcja zwróci błąd # Nie dotyczy.

Znaleziona wartość może nie być najbliższa. Na przykład, jeśli spróbujesz znaleźć najbliższą cenę za 199, funkcja zwróci 150 (chociaż najbliższa to 200). Jest to znowu konsekwencją faktu, że funkcja znajduje największą liczbę, która jest mniejsza lub równa podanej.

Jeśli chcesz znaleźć to, co jest naprawdę najbliższe żądanej wartości, funkcja WYSZUKAJ.PIONOWO () tutaj nie pomoże. Ten rodzaj problemu został rozwiązany w sekcji. Można tam również znaleźć rozwiązanie problemu znalezienia najbliższego za pomocą nieposortowanej kolumny klucza.

Notatka... Dla wygody podświetlony jest wiersz tabeli zawierający znalezione rozwiązanie. Można to zrobić za pomocą wzoru = SZUKAJ (7 $ A $ 7; $ A $ 11: $ A $ 17; 1) = LINE () - LINE ($ A $ 10).

Notatka: Jeśli kolumna klucza ma wartość, która pasuje do żądanej, to funkcja z parametrem Interwał_widok= FALSE zwróci pierwszą znalezioną wartość równą żądanej, a przy parametrze = TRUE - ostatnią (patrz rysunek poniżej).

Jeśli przeszukiwana kolumna nie jest pierwszą od lewej, funkcja WYSZUKAJ.PIONOWO () nie pomoże. W takim przypadku należy skorzystać z funkcji SZUKAJ () + INDEKS () lub WYŚWIETL ().