Funkcja WYSZUKAJ. PIONOWO zawsze była jedną z najpotężniejszych funkcji programu Excel. Pozwala wyszukiwać wartości w pierwszej kolumnie tabeli i zwracać wartości z pól po prawej stronie. Ale Excel ma również funkcję o nazwie XLOOKUP, która umożliwia wyszukiwanie wartości w dowolnej kolumnie lub wierszu i zwracanie danych z dowolnej innej kolumny.
Jak działa XLOOKUP
Funkcja WYSZUKAJ. X jest znacznie łatwiejsza w użyciu niż funkcja WYSZUKAJ. PIONOWO, ponieważ zamiast określać wartość dla kolumny wyników, można określić cały zakres.
Funkcja umożliwia również przeszukiwanie zarówno kolumny, jak i wiersza, lokalizując wartość w przecinającej się komórce.
Parametry funkcji XLOOKUP są następujące:
=XLOOKUP (wyszukaj_wartość, wyszukaj_tablicę, zwróconą_tablicę, [tryb_dopasowania], [tryb_wyszukiwania])
- lookup_value: Wartość, którą chcesz wyszukać
- lookup_array: Tablica (kolumna), którą chcesz przeszukać
- return_array: Wynik (kolumna), z którego chcesz pobrać wartość
- match_mode (opcjonalne): Wybierz dokładne dopasowanie (0), dokładne dopasowanie lub następną najmniejszą wartość (-1) lub dopasowanie wieloznaczne (2).
- search_mode (opcjonalne): Wybierz, czy wyszukiwanie zaczyna się od pierwszego elementu w kolumnie (1), ostatniego elementu w kolumnie (-1), wyszukiwanie binarne rosnąco (2) lub wyszukiwanie binarne malejąco (-2).
Poniżej znajduje się kilka najczęstszych wyszukiwań, które można wykonać za pomocą funkcji XLOOKUP.
Jak wyszukać pojedynczy wynik za pomocą funkcji XLOOKUP
Najłatwiejszym sposobem użycia funkcji XLOOKUP jest wyszukanie pojedynczego wyniku przy użyciu punktu danych z jednej kolumny.
-
Ten przykładowy arkusz kalkulacyjny to lista zamówień złożonych przez przedstawicieli handlowych, zawierająca pozycję, liczbę jednostek, koszt i całkowitą sprzedaż.
Image -
Jeśli chcesz znaleźć pierwszą sprzedaż na liście przesłanej przez określonego przedstawiciela handlowego, możesz utworzyć funkcję XLOOKUP, która wyszukuje nazwę w kolumnie Przedstawiciel. Funkcja zwróci wynik z kolumny Suma. Funkcja XLOOKUP do tego celu to:
=XWYSZUKAJ(I2, C2:C44, G2:G44, 0, 1)
- I2: Wskazuje na Nazwa przedstawiciela komórkę wyszukiwania
- C2:C44: To jest kolumna Rep, która jest tablicą wyszukiwania
- G2:G33: To jest kolumna Suma, która jest tablicą zwrotów
- 0: Wybiera dokładne dopasowanie
- 1: Wybiera pierwszy mecz w wynikach
-
Kiedy naciśniesz Enter i wpiszesz nazwę przedstawiciela handlowego, komórka Wynik całkowity pokaże pierwszy wynik w tabeli dla tego przedstawiciela handlowego.
Image -
Jeśli chcesz wyszukać ostatnią sprzedaż (ponieważ tabela jest uporządkowana według daty w odwrotnej kolejności), zmień ostatni argument XLOOKUP na - 1, który zostanie uruchomiony wyszukiwanie z ostatniej komórki w tablicy wyszukiwania i zamiast tego dostarcza ten wynik.
Image -
Ten przykład przedstawia podobne wyszukiwanie, które można przeprowadzić za pomocą funkcji WYSZUKAJ. PIONOWO, używając kolumny Rep jako pierwszej kolumny tabeli wyszukiwania. Jednak XLOOKUP umożliwia wyszukiwanie dowolnej kolumny w dowolnym kierunku. Na przykład, jeśli chcesz znaleźć przedstawiciela handlowego, który sprzedał pierwsze zamówienie Binder w roku, użyj następującej funkcji XLOOKUP:
=XWYSZUKAJ(I2, D2:D44, C2:C44, 0, 1)
- D2: Wskazuje na komórkę wyszukiwania pozycji
- D2:D44: To jest kolumna Pozycja, która jest tablicą wyszukiwania
- C2:C44: To jest kolumna Rep, która jest tablicą zwrotów po lewej stronie tablicy wyszukiwania
- 0: Wybiera dokładne dopasowanie
- 1: Wybiera pierwszy mecz w wynikach
-
Tym razem wynikiem będzie nazwisko przedstawiciela handlowego, który sprzedał pierwsze w roku zamówienie segregatorów.
Image
Wykonaj dopasowanie pionowe i poziome za pomocą funkcji XLOOKUP
Kolejną funkcją funkcji WYSZUKAJ. PIONOWĄ, której nie obsługuje WYSZUKAJ. PIONOWO, jest możliwość wykonywania zarówno wyszukiwania pionowego, jak i poziomego, co oznacza, że można wyszukiwać element w dół kolumny, a także w rzędzie.
Ta funkcja podwójnego wyszukiwania skutecznie zastępuje inne funkcje programu Excel, takie jak INDEKS, PODAJ. POZYCJĘ lub WYSZUKAJ. POZIOMO.
-
W poniższym przykładowym arkuszu kalkulacyjnym sprzedaż każdego przedstawiciela handlowego jest podzielona na kwartały. Jeśli chciałbyś zobaczyć sprzedaż w trzecim kwartale dla konkretnego przedstawiciela handlowego, bez funkcji XLOOKUP, tego rodzaju wyszukiwanie byłoby trudne.
Image -
Dzięki funkcji XLOOKUP ten rodzaj wyszukiwania jest łatwy. Korzystając z następującej funkcji XLOOKUP, możesz wyszukać sprzedaż w trzecim kwartale dla określonego przedstawiciela handlowego:
=XWYSZUKAJ(J2, B2:B42, XWYSZUKAJ(K2, C1:H1, C2:H42))
- J2: Wskazuje komórkę wyszukiwania przedstawiciela
- B2:B42: To jest kolumna Pozycja, która jest tablicą wyszukiwania kolumn
- K2: Wskazuje na komórkę wyszukiwania kwartału
- C1:H1: To jest tablica wyszukiwania wierszy
- C2:H42: To jest tablica wyszukiwania kwot w dolarach w każdym kwartale
Ta zagnieżdżona funkcja XLOOKUP najpierw identyfikuje przedstawiciela handlowego, a następna funkcja XLOOKUP identyfikuje żądany kwartał. Zwróconą wartością będzie komórka, w której te dwa się przecinają.
-
Wynikiem dla tej formuły są zarobki w pierwszym kwartale dla przedstawiciela o imieniu Thompson.
Image
Korzystanie z funkcji WYSZUKAJ
Funkcja XLOOKUP jest dostępna tylko dla subskrybentów pakietu Office Insider, ale wkrótce zostanie udostępniona wszystkim subskrybentom usługi Microsoft 365.
Jeśli chcesz samodzielnie przetestować tę funkcję, możesz zostać niejawnym testerem pakietu Office. Wybierz File > Konto, a następnie wybierz menu rozwijane Office Insider, aby zasubskrybować.
Po dołączeniu do programu Office Insider zainstalowana wersja programu Excel będzie otrzymywać wszystkie najnowsze aktualizacje i możesz zacząć korzystać z funkcji XLOOKUP.
