Używanie funkcji WYSZUKAJ.PIONOWO do zastępowania wartości

Kto jest zbyt leniwy lub nie ma czasu na czytanie – obejrzyj wideo. Szczegóły i niuanse znajdują się w tekście poniżej.

Sformułowanie problemu

Mamy więc dwa stoły – zamów tabelę и cennik:

Zadaniem jest automatyczne podstawianie cen z cennika do tabeli zamówień, skupiając się na nazwie produktu, aby później można było obliczyć koszt.

Rozwiązanie

W zestawie funkcji Excel, w kategorii Referencje i tablice (Wyszukiwanie i odniesienie) jest funkcja VPR (WYSZUKAJ.PIONOWO).Ta funkcja wyszukuje daną wartość (w naszym przykładzie jest to słowo „Jabłka”) w skrajnej lewej kolumnie określonej tabeli (cennika) przesuwając się od góry do dołu i po jej znalezieniu wyświetla zawartość sąsiedniej komórki (23 ruble) .Schematycznie można przedstawić działanie tej funkcji Tak:

Dla ułatwienia dalszego korzystania z funkcji od razu zrób jedną rzecz – nadaj zakresowi komórek w cenniku swoją nazwę. W tym celu zaznacz wszystkie komórki cennika oprócz „nagłówka” (G3:H19), wybierz z menu Wstaw – Imię – Przypisz (Wstaw — Nazwa — Zdefiniuj) lub naciśnij CTRL + F3 i wprowadź dowolną nazwę (bez spacji) jak Cena… Teraz, w przyszłości, możesz użyć tej nazwy do linku do cennika.

Teraz używamy funkcji VPR… Wybierz komórkę, w której zostanie wpisana (D3) i otwórz zakładkę Wzory – wstawianie funkcji (Formuły — funkcja wstawiania)… W kategorii Referencje i tablice (Wyszukiwanie i odniesienie) znajdź funkcję VPR (WYSZUKAJ.PIONOWO) i naciśnij OK… Pojawi się okno do wpisania argumentów funkcji:

Używanie funkcji WYSZUKAJ.PIONOWO do zastępowania wartości

Wypełniamy je kolejno:

  • Pożądana wartość (Wartość wyszukiwania) – nazwę produktu, który funkcja powinna znaleźć w skrajnej lewej kolumnie cennika. W naszym przypadku słowo „Jabłka” z komórki B3.
  • Stół (Tablica tabeli) – tabela z której wzięto pożądane wartości uXNUMXbuXNUMXbare, czyli nasz cennik. Dla odniesienia posługujemy się podaną wcześniej własną nazwą „Cena”. Jeśli nie podałeś nazwy, możesz po prostu wybrać tabelę, ale nie zapomnij nacisnąć przycisku F4przypiąć link za pomocą znaków dolara, ponieważ w przeciwnym razie zsunie się w dół podczas kopiowania naszej formuły do ​​pozostałych komórek w kolumnie D3:D30.
  • Numer_kolumny (Numer indeksu kolumny) – numer seryjny (nie litera!) kolumny w cenniku, z której będziemy pobierać wartości cen. Pierwsza kolumna cennika z nazwami ma numer 1, dlatego potrzebna jest cena z kolumny o numerze 2.
  • wyszukiwanie_interwału (Zakres wyszukiwania) – w tym polu można wpisać tylko dwie wartości: FALSE lub TRUE:
      • Jeśli wprowadzono wartość 0 or KŁAMLIWY (FAŁSZYWE), to w rzeczywistości oznacza to, że dozwolone jest tylko wyszukiwanie dokładne dopasowanie, tzn. jeśli funkcja nie znajdzie niestandardowej pozycji określonej w tabeli zamówień w cenniku (jeśli np. wpisano „Kokos”), wygeneruje błąd #N/A (brak danych).
      • Jeśli wprowadzono wartość 1 or TRUE (PRAWDZIWE), oznacza to, że zezwalasz na wyszukiwanie nie dokładnego, ale przybliżony mecz, czyli w przypadku „kokos” funkcja będzie próbowała znaleźć produkt o nazwie jak najbardziej zbliżonej do „kokos” i zwróci cenę za tę nazwę. W większości przypadków takie przybliżone podstawienie może zmylić użytkownika, podstawiając wartość złego produktu, który faktycznie tam był! Dlatego w przypadku większości rzeczywistych problemów biznesowych najlepiej nie zezwalać na wyszukiwanie przybliżone. Wyjątkiem jest sytuacja, gdy szukamy liczb, a nie tekstu – na przykład przy obliczaniu rabatów krokowych.

Wszystko! Pozostaje nacisnąć OK i skopiuj wprowadzoną funkcję do całej kolumny.

# Nie dotyczy błędów i ich tłumienia

Funkcjonować VPR (WYSZUKAJ.PIONOWO) zwraca błąd #N/D (#Nie dotyczy) Jeśli:

  • Dokładne wyszukiwanie włączone (argument Widok interwałowy = 0), a żądana nazwa nie jest w Stół.
  • Uwzględniono wyszukiwanie zgrubne (Widok interwałowy = 1), ale Stół, w którym odbywa się wyszukiwanie, nie jest sortowane rosnąco według nazw.
  • Format komórki, z której pochodzi wymagana wartość nazwy (na przykład B3 w naszym przypadku) i format komórek pierwszej kolumny (F3: F19) tabeli są różne (na przykład numeryczne i tekstowe ). Ten przypadek jest szczególnie typowy, gdy zamiast nazw tekstowych stosuje się kody numeryczne (numery kont, identyfikatory, daty itp.). W takim przypadku możesz skorzystać z funkcji Ч и TEKST konwertować formaty danych. Będzie to wyglądać mniej więcej tak:

    =WYSZUKAJ.PIONOWO(TEKST(B3);cena;0)

    Więcej na ten temat przeczytasz tutaj.

  • Funkcja nie może znaleźć wymaganej wartości, ponieważ kod zawiera spacje lub niewidoczne znaki niedrukowalne (podziały wierszy itp.). W takim przypadku możesz użyć funkcji tekstowych TRIM (PRZYCINAĆ) и WYDRUKOWAĆ(CZYSTY) aby je usunąć:

    =WYSZUKAJ.PIONOWO(PRZESTRZENI PRZYCINANIA(CZYŚĆ(B3));cena;0)

    =WYSZUKAJ.PIONOWO(PRZYC(CZYŚĆ(B3));cena;0)

Aby pominąć komunikat o błędzie # N / A (#Nie dotyczy) w przypadkach, gdy funkcja nie może znaleźć dokładnego dopasowania, możesz użyć funkcji JEŻELI BŁĄD (JEŻELI BŁĄD)… Czyli na przykład ta konstrukcja przechwytuje wszelkie błędy generowane przez funkcję WYSZUKAJ.PIONOWO i zastępuje je zerami:

= JEŻELI BŁĄD (WYSZUKAJ.PIONOWO (B3, cena, 2, 0), 0)

= JEŻELI BŁĄD (WYSZUKAJ.PIONOWO (B3; cena; 2; 0); 0)

PS

Jeśli chcesz wyodrębnić nie jedną wartość, ale cały zestaw naraz (jeśli jest kilka różnych), będziesz musiał zszamanizować za pomocą formuły tablicowej. lub skorzystaj z nowej funkcji XLOOKUP z Office 365.

 

  • Ulepszona wersja funkcji WYSZUKAJ.PIONOWO (WYSZUKAJ.PIONOWO 2).
  • Szybkie obliczanie rabatów krokowych (zakresowych) za pomocą funkcji WYSZUKAJ.PIONOWO.
  • Jak zrobić „lewe WYSZUKAJ.PIONOWO” za pomocą funkcji INDEKS i PODAJ.POZYCJĘ
  • Jak korzystać z funkcji WYSZUKAJ.PIONOWO do wypełniania formularzy danymi z listy?
  • Jak wyciągnąć z tabeli nie pierwszą, a wszystkie wartości na raz?
  • Funkcje VLOOKUP2 i VLOOKUP3 z dodatku PLEX

 

Dodaj komentarz