Jak korzystać z funkcji XLOOKUP programu Excel -

Podsumowanie

Funkcja XLOOKUP programu Excel to nowoczesny i elastyczny zamiennik starszych funkcji, takich jak WYSZUKAJ.PIONOWO, WYSZUKAJ.PIONOWO i WYSZUKAJ. XLOOKUP obsługuje przybliżone i dokładne dopasowanie, symbole wieloznaczne (*?) Dla częściowych dopasowań oraz wyszukiwania w zakresach pionowych lub poziomych.

Cel, powód

Wyszukaj wartości w zakresie lub tablicy

Wartość zwracana

Dopasowanie wartości z tablicy zwracanej

Składnia

= XLOOKUP (wyszukiwanie, tablica_wyszukiwania, tablica_powrotu, (not_found), (tryb_dopasowania), (tryb_wyszukiwania))

Argumenty

  • lookup - wartość wyszukiwania.
  • lookup_array - tablica lub zakres do przeszukania.
  • tablica_powrotu - tablica lub zakres do zwrócenia.
  • not_found - (opcjonalne) Wartość do zwrócenia, jeśli nie znaleziono dopasowania.
  • match_mode - (opcjonalne) 0 = dokładne dopasowanie (domyślne), -1 = dokładne dopasowanie lub następna najmniejsza, 1 = dokładne dopasowanie lub następne większe, 2 = dopasowanie wieloznaczne.
  • tryb_wyszukiwania - (opcjonalny) 1 = wyszukiwanie od pierwszego (domyślne), -1 = wyszukiwanie od ostatniego, 2 = wyszukiwanie binarne rosnąco, -2 = wyszukiwanie binarne malejąco.

Wersja

Excel 365

Uwagi dotyczące użytkowania

XLOOKUP to nowoczesny zamiennik funkcji WYSZUKAJ.PIONOWO. Jest to elastyczna i wszechstronna funkcja, której można używać w wielu różnych sytuacjach.

XLOOKUP może znajdować wartości w zakresach pionowych lub poziomych, może wykonywać przybliżone i dokładne dopasowania oraz obsługuje symbole wieloznaczne (*?) Dla częściowych dopasowań. Ponadto XLOOKUP może przeszukiwać dane, zaczynając od pierwszej lub ostatniej wartości (zobacz szczegóły typu dopasowania i trybu wyszukiwania poniżej). W porównaniu ze starszymi funkcjami, takimi jak WYSZUKAJ.PIONOWO, WYSZUKAJ.PIONOWO i WYSZUKAJ, XLOOKUP oferuje kilka kluczowych zalet.

Nie znaleziono wiadomości

Gdy XLOOKUP nie może znaleźć dopasowania, zwraca błąd # N / D, podobnie jak inne funkcje dopasowywania w programie Excel. W przeciwieństwie do innych funkcji dopasowujących, XLOOKUP obsługuje opcjonalny argument o nazwie not_found, którego można użyć do przesłonięcia błędu # N / D, gdy w przeciwnym razie pojawiłby się. Typowymi wartościami not_found mogą być „Nie znaleziono”, „Brak dopasowania”, „Brak wyniku” itp. Podając wartość not_found, umieść tekst w podwójnych cudzysłowach („”).

Uwaga: zachowaj ostrożność, jeśli podasz pusty ciąg („”) dla not_found. Jeśli nie zostanie znalezione żadne dopasowanie, XLOOKUP nie wyświetli nic zamiast # N / A. Jeśli chcesz zobaczyć błąd # N / D, gdy dopasowanie nie zostanie znalezione, całkowicie pomiń argument.

Typ dopasowania

Domyślnie XLOOKUP wykona dokładne dopasowanie. Zachowanie dopasowania jest kontrolowane przez opcjonalny argument o nazwie match_type, który ma następujące opcje:

Typ dopasowania Zachowanie
0 (domyślnie) Dokładne dopasowanie. Jeśli nie pasuje, zwróci # N / A.
-1 Dokładne dopasowanie lub następny mniejszy element.
1 Dokładne dopasowanie lub następny większy element.
2 Dopasowanie wieloznaczne (*,?, ~)

Tryb szukania

Domyślnie XLOOKUP rozpocznie dopasowywanie od pierwszej wartości danych. Zachowanie wyszukiwania jest kontrolowane przez opcjonalny argument o nazwie tryb_wyszukiwania , który zapewnia następujące opcje:

Tryb szukania Zachowanie
1 (domyślnie) Szukaj od pierwszej wartości
-1 Szukaj od ostatniej wartości (wstecz)
2 Binarne wartości wyszukiwania posortowane w kolejności rosnącej
-2 Binarne wartości wyszukiwania posortowane w porządku malejącym

Wyszukiwania binarne są bardzo szybkie, ale dane muszą być sortowane zgodnie z wymaganiami. Jeśli dane nie są poprawnie posortowane, wyszukiwanie binarne może zwrócić nieprawidłowe wyniki, które wyglądają zupełnie normalnie.

Przykład 1 - podstawowe dopasowanie ścisłe

Domyślnie XLOOKUP wykona dokładne dopasowanie. W poniższym przykładzie XLOOKUP służy do pobierania Sales na podstawie dokładnego dopasowania w filmie. Wzór w H5 to:

=XLOOKUP(H4,B5:B9,E5:E9)

Bardziej szczegółowe wyjaśnienie tutaj.

Przykład # 2 - podstawowe przybliżone dopasowanie

Aby włączyć przybliżone dopasowanie, podaj wartość argumentu „match_mode”. W poniższym przykładzie XLOOKUP służy do obliczania rabatu na podstawie ilości, która wymaga przybliżonego dopasowania. Formuła w F5 podaje wartość -1 dla match_mode, aby umożliwić przybliżone dopasowanie z zachowaniem „dopasowanie ścisłe lub następny najmniejszy”:

=XLOOKUP(E5,B5:B9,C5:C9,,-1)

Bardziej szczegółowe wyjaśnienie tutaj.

Przykład 3 - wiele wartości

XLOOKUP może zwrócić więcej niż jedną wartość w tym samym czasie dla tego samego dopasowania. Poniższy przykład pokazuje, jak można skonfigurować XLOOKUP, aby zwracał trzy pasujące wartości za pomocą jednej formuły. Wzór w C5 to:

=XLOOKUP(B5,B8:B15,C8:E15)

Zwróć uwagę, że tablica zwrotów (C8: E15) zawiera 3 kolumny: pierwsza, ostatnia, dział. Wszystkie trzy wartości są zwracane i przenoszone do zakresu C5: E5.

Przykład 4 - wyszukiwanie dwukierunkowe

XLOOKUP może być używany do dwukierunkowego wyszukiwania, zagnieżdżając jeden XLOOKUP w drugim. W poniższym przykładzie „wewnętrzna” XLOOKUP pobiera cały wiersz (wszystkie wartości dla Glass), który jest przekazywany do „zewnętrznej” XLOOKUP jako tablica zwracana. Zewnętrzny XLOOKUP znajduje odpowiednią grupę (B) i zwraca odpowiednią wartość (17,25) jako wynik końcowy.

=XLOOKUP(I6,C4:F4,XLOOKUP(I5,B5:B9,C5:F9))

Więcej szczegółów tutaj.

Przykład # 5 - nie znaleziono wiadomości

Podobnie jak inne funkcje wyszukiwania, jeśli XLOOKUP nie znajdzie wartości, zwraca błąd # N / A. Aby wyświetlić niestandardową wiadomość zamiast # N / A, podaj wartość opcjonalnego argumentu „nie znaleziono”, ujęty w podwójne cudzysłowy („”). Na przykład, aby wyświetlić „Nie znaleziono”, gdy nie zostanie znaleziony pasujący film, na podstawie poniższego arkusza, użyj:

=XLOOKUP(H4,B5:B9,E5:E9,"Not found")

Możesz dowolnie dostosować tę wiadomość: „Brak dopasowania”, „Nie znaleziono filmu” itp.

Przykład # 6 - złożone kryteria

Dzięki możliwości natywnej obsługi tablic XLOOKUP może być używany ze złożonymi kryteriami. W poniższym przykładzie XLOOKUP pasuje do pierwszego rekordu, gdzie: konto zaczyna się od „x”, a region to „wschód”, a miesiąc to nie kwiecień:

=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)

Szczegóły: (1) prosty przykład, (2) bardziej złożony przykład.

Zalety XLOOKUP

XLOOKUP oferuje kilka ważnych zalet, zwłaszcza w porównaniu z VLOOKUP:

  • XLOOKUP może wyszukiwać dane po prawej lub lewej stronie wartości wyszukiwania
  • XLOOKUP może zwrócić wiele wyników (przykład nr 3 powyżej)
  • XLOOKUP domyślnie odpowiada dokładnemu dopasowaniu (WYSZUKAJ.PIONOWO domyślnie przybliżone)
  • XLOOKUP może pracować z danymi pionowymi i poziomymi
  • XLOOKUP może przeprowadzić wyszukiwanie wsteczne (od ostatniego do pierwszego)
  • XLOOKUP może zwrócić całe wiersze lub kolumny, a nie tylko jedną wartość
  • XLOOKUP może natywnie pracować z tablicami, aby stosować złożone kryteria

Uwagi

  1. XLOOKUP może działać zarówno z tablicami pionowymi, jak i poziomymi.
  2. XLOOKUP zwróci # N / A, jeśli wartość wyszukiwania nie zostanie znaleziona.
  3. Tablica_wyszukiwania musi mieć wymiar zgodny z return_array argumentu, inaczej XLOOKUP powróci #ARG!
  4. Jeśli XLOOKUP jest używany między skoroszytami, oba skoroszyty muszą być otwarte, w przeciwnym razie XLOOKUP zwróci #REF !.
  5. Podobnie jak funkcja INDEKS, XLOOKUP zwraca jako wynik odwołanie.

Powiązane wideo

Podstawowy przykład XLOOKUP W tym nagraniu wideo skonfigurujemy funkcję XLOOKUP na podstawowym przykładzie. Dopasowując nazwę miasta, odzyskamy kraj i populację. Podstawowe przybliżone dopasowanie XLOOKUP W tym filmie skonfigurujemy funkcję XLOOKUP, aby wykonać przybliżone dopasowanie w celu obliczenia rabatu opartego na ilości. XLOOKUP z logiką boolowską W tym nagraniu wideo przyjrzymy się, jak używać funkcji XLOOKUP z logiką Boole'a w celu zastosowania wielu kryteriów. XLOOKUP z wieloma wartościami wyszukiwania W tym nagraniu wideo skonfigurujemy XLOOKUP tak, aby zwracał wiele wartości w tablicy dynamicznej, podając zakres wartości wyszukiwania zamiast pojedynczej wartości wyszukiwania.

Interesujące artykuły...