Funkcja WYSZUKAJ.PIONOWO Excela służy do wyszukiwania i zwracania informacji z tabeli danych w oparciu o wybraną wartość wyszukiwania.
Zwykle funkcja WYSZUKAJ.PIONOWO wymaga, aby wartość odnośnika znajdowała się w lewej kolumnie tabeli danych, a funkcja zwraca inne pole danych znajdujące się w tym samym wierszu po prawej stronie tej wartości.
Jednakże, łącząc funkcję WYSZUKAJ.PIONOWO z funkcją WYBIERZ, można utworzyć formułę lewego wyszukiwania, która:
- Zezwala na wartość wyszukiwania z dowolnej kolumny w tabeli danych
- Zwraca informacje znajdujące się w dowolnej kolumnie po lewej stronie wartości odnośnika
Uwaga: Te instrukcje dotyczą wersji Excel 2019, 2016, 2013, 2010 i Excel dla Office 365.
01 z 05Samouczek: Korzystanie z funkcji WYSZUKAJ.PIONOWO i WYBIERZ Funkcje w formule Left Lookup
Aby utworzyć formułę left lookup widoczną na przykładowym obrazie, użyj następującej formuły:
= WYSZUKAJ.PIONOWO ($ D $ 2, WYBIERZ ({1,2}, $ F: $ F, $ D: $ D), 2, FAŁSZ)
W tym przykładzie formuła umożliwia znalezienie części dostarczonej przez różne firmy wymienione w kolumnie 3 tabeli danych.
Zadaniem funkcji WYBIERZ w formule jest przekonanie VLOOKUP do przekonania, że kolumna 3 to kolumna 1. W rezultacie nazwa firmy może być używana jako wartość wyszukiwania, aby znaleźć nazwę części dostarczonej przez każdą firmę.
Wprowadź dane samouczka
- Wprowadź nagłówek Dostawca do komórki D1.
- Wprowadź nagłówek Część w komórce E1.
- Wprowadź tabelę danych widoczną na obrazku powyżej do komórek D4 do F9
- Wydziwianie 2 i 3 są puste, aby uwzględnić kryteria wyszukiwania i formułę lewego wyszukiwania utworzoną podczas tego samouczka
Otwórz okno dialogowe WYSZUKAJ.PIONOWO
Chociaż możliwe jest wpisanie formuły bezpośrednio w komórce F1 w arkuszu, wiele osób ma problemy ze składnią formuły.
W takim przypadku bezpieczniej jest skorzystać z okna dialogowego WYSZUKAJ.PIONOWO. Prawie wszystkie funkcje Excela mają okno dialogowe, które pozwala na wprowadzenie każdego z argumentów funkcji w osobnym wierszu.
- Kliknij komórkę E2 arkusza roboczego. E2 to lokalizacja, w której będą wyświetlane wyniki formuły wyszukiwania lewostronnego.
- Kliknij na Formuły zakładka wstążki.
- Kliknij na Wyszukiwanie i referencje opcję na wstążce, aby otworzyć rozwijaną listę funkcji.
- Kliknij WYSZUKAJ.PIONOWO na liście, aby wywołać okno dialogowe funkcji.
Wprowadzanie argumentów do okna dialogowego WYSZUKAJ.PIONOWO
Argumenty funkcji są wartościami używanymi przez funkcję do obliczania wyniku.
W oknie dialogowym funkcji nazwa każdego argumentu znajduje się w oddzielnym wierszu, po którym następuje pole do wprowadzenia wartości.
Wprowadź następujące wartości dla każdego z argumentów VLOOKUP w poprawnym wierszu okna dialogowego, jak pokazano na załączonym obrazku.
Wartość wyszukiwania
Wartość wyszukiwania jest polem informacji używanym do przeszukiwania tablicy tablicowej. VLOOKUP zwraca inne pole danych z tego samego wiersza, co wartość odnośnika.
W tym przykładzie użyto odwołania do komórki do lokalizacji, w której nazwa firmy zostanie wprowadzona do arkusza roboczego. Zaletą tego jest to, że ułatwia zmianę nazwy firmy bez edytowania formuły.
- Kliknij na lookup_value linia w oknie dialogowym.
- Kliknij komórkę D2 aby dodać to odwołanie do komórki do lookup_value linia.
- wciśnij F4 klawisz na klawiaturze, aby odwołanie do komórki było absolutne - $ D 2 $.
Bezwzględne odwołania do komórek
Bezwzględne odwołania do komórek są używane dla wartości wyszukiwania i argumentów tablicy tablic, aby zapobiec błędom, jeśli formuła odnośnika zostanie skopiowana do innych komórek w arkuszu.
Wprowadzanie funkcji WYBIERZ
Argument tablicy tablicowej jest blokiem sąsiednich danych, z których pobierane są określone informacje.
Zwykle funkcja WYSZUKAJ.PIONOWO patrzy tylko na prawo od argumentu wartości odnośnika, aby znaleźć dane w tablicy tablicowej. Aby uzyskać to, aby wyglądał w lewo, należy oszukać VLOOKUP, zmieniając kolejność kolumn w tablicy tabeli za pomocą funkcji CHOOSE.
W tej formule funkcja WYBIERZ realizuje dwa zadania:
- Tworzy tablicę tablicową, która ma tylko dwie kolumny szerokości (kolumny D i F).
- Zmienia kolejność kolumn od prawej do lewej, tak aby kolumna F była pierwsza, a kolumna D - druga.
Wprowadzanie funkcji
Podczas ręcznego wprowadzania funkcji, każda z argumentów funkcji musi być oddzielona przecinkiem.
- W oknie dialogowym funkcji VLOOKUP kliknij przycisk Table_array linia.
- Wprowadź następujące informacje WYBIERAĆ funkcjonować:WYBIERZ ({1,2}, $ F: $ F, $ D: $ D)
Numer indeksu kolumny
Zwykle numer indeksu kolumny wskazuje, która kolumna tablicy tablic zawiera dane, których szukasz. Jednak w tym wzorze odnosi się do kolejności kolumn ustawionych za pomocą funkcji WYBIERZ.
Funkcja WYBIERZ tworzy tablicę składającą się z dwóch kolumn z kolumną F, po której następuje kolumna D. Ponieważ poszukiwana informacja - nazwa części - znajduje się w kolumnie D, wartość argumentu indeksu kolumny musi wynosić 2.
- Kliknij na Col_index_num linia w oknie dialogowym.
- Wpisz a 2 w tej linii.
Zakres wyszukiwania
VLOOKUP's argument Range_lookup jest wartością logiczną (tylko PRAWDA lub FAŁSZ), która wskazuje, czy chcesz, aby WYSZUKAJ.PIONOWO znalazł dokładne lub przybliżone dopasowanie do wartości odnośnika.
- Jeśli PRAWDA lub jeśli ten argument zostanie pominięty, funkcja WYSZUKAJ.PIONOWO zwraca dokładne dopasowanie do wartości szukanej lub, jeśli nie zostanie znalezione dokładne dopasowanie, funkcja WYSZUKAJ.PIONOWO zwraca następną największą wartość. Aby to zrobić, dane w pierwszej kolumnie tablicy Tabela muszą być posortowane w porządku rosnącym.
- Jeśli FALSE, VLOOKUP używa tylko dopasowania ścisłego do wartości Lookup_v. Jeśli są dwie lub więcej wartości w pierwszej kolumnie tabeli Tabela, która jest zgodna z wartością wyszukiwania, zostanie użyta pierwsza znaleziona wartość. Jeśli nie znaleziono dokładnego dopasowania, zwracany jest błąd # N / A.
W tym samouczku, ponieważ szukamy konkretnej nazwy części, parametr Range_lookup jest ustawiony na Fałszywy tak, że tylko dokładne dopasowania są zwracane przez formułę.
- Kliknij na Zakres wyszukiwania linia w oknie dialogowym.
- Wpisz słowo Fałszywy w tym wierszu, aby wskazać, że chcemy, aby funkcja WYSZUKAJ.PIONOWO zwróciła dokładne dopasowanie do danych, których szukamy.
- Kliknij dobrze aby ukończyć formułę lewego wyszukiwania i zamknąć okno dialogowe.
- Ponieważ nie wprowadziliśmy jeszcze nazwy firmy do komórki D2, w komórce E2 pojawia się błąd # N / A.
Zwracanie danych za pomocą formuły Left Lookup
Aby znaleźć firmy, które dostarczają części, wpisz nazwę firmy w komórce D2 i naciśnij WCHODZIĆ klawisz na klawiaturze.
Nazwa części jest wyświetlana w komórce E2.
- Kliknij komórkę D2 w arkuszu roboczym.
- Rodzaj Gadżety Plus do komórki D2 i naciśnij WCHODZIĆ klawisz na klawiaturze.
- Tekst "Gadżety" - część dostarczona przez firmę Gadgets Plus - powinien być wyświetlany w komórce E2.
Przetestuj dalej formułę wyszukiwania, wpisując inne nazwy firm w komórce D2, a odpowiednia nazwa części powinna pojawić się w komórce E2.
Jeśli w komórce E2 pojawi się komunikat o błędzie, taki jak # N / A, sprawdź błędy pisowni w komórce D2.
04 z 05Tworzenie tablicy tablic dwukolumnowych
Składnia funkcji CHOOSE to:
= WYBIERZ (Index_number, Value1, Value2, … Value254)
Funkcja WYBIERZ zwykle zwraca jedną wartość z listy wartości (wartość 1 do wartości 254) na podstawie wprowadzonego numeru indeksu.
Jeśli numer indeksu wynosi 1, funkcja zwraca wartość 1 z listy; jeśli numer indeksu wynosi 2, funkcja zwraca wartość 2 z listy i tak dalej.
Po wprowadzeniu wielu numerów indeksów funkcja zwraca wiele wartości w dowolnej kolejności. Wybranie opcji WYBIERZ, aby zwrócić wiele wartości, jest tworzone przez utworzenie tablicy.
Aby wprowadzić tablicę, otocz cyfry wprowadzone za pomocą nawiasów klamrowych lub nawiasów. Dla numeru indeksu wprowadzono dwie liczby:{ 1,2 }.
Należy zauważyć, że WYBÓR nie ogranicza się do tworzenia tabeli dwukolumnowej. Dodając dodatkową liczbę w tablicy - na przykład {1,2,3} - i dodatkowy zakres w argumencie wartości, CHOOSE tworzy tabelę z trzema kolumnami.
Dodatkowe kolumny umożliwiają zwracanie różnych informacji za pomocą formuły wyszukiwania lewostronnego przez zmianę wartości argumentu indeksowego kolumny VLOOKUP na numer kolumny zawierającej żądane informacje.
05 z 05Zmiana kolejności kolumn za pomocą funkcji WYBIERZ
W funkcji WYBIERZ używanej w tym wzorze:
WYBIERZ ({1,2}, $ F: $ F, $ D: $ D)
zakres dla kolumny F jest wymieniony przed kolumną D.
Ponieważ funkcja WYBIERZ ustawia tablicę tablicy VLOOKUP (źródło danych dla tej funkcji) przełączanie kolejności kolumn w funkcji WYBIERZ jest przekazywane do funkcji WYSZUKAJ.PIONOWO.
Jeśli chodzi o VLOOKUP, tablica ma tylko dwie kolumny z kolumną F po lewej i kolumną D po prawej. Ponieważ kolumna F zawiera nazwę firmy, którą chcemy wyszukać, a ponieważ kolumna D zawiera nazwy części, funkcja VLOOKUP może wykonywać swoje regularne obowiązki wyszukiwania w wyszukiwaniu danych znajdujących się po lewej stronie wartości odnośnika.
W wyniku tego funkcja VLOOKUP może użyć nazwy firmy, aby znaleźć dostarczoną część.
