Funkcja XLOOKUP

Funkcji XLOOKUPnależy używać, gdy trzeba znaleźć elementy w tabeli lub w zakresie według wiersza. Na przykład Sprawdź cenę części motoryzacyjnej przez numer części lub Znajdź nazwę pracownika na podstawie identyfikatora pracownika. Dzięki XLOOKUP możesz w jednej kolumnie wyszukać wyszukiwany termin i zwrócić wynik z tego samego wiersza w innej kolumnie, niezależnie od tego, z której strony zwracana jest kolumna zwrotna.

Przeglądarka nie obsługuje klipu wideo. Zainstaluj dodatek Microsoft Silverlight, Adobe Flash Player lub program Internet Explorer 9.

Uwaga: 28 sierpnia 2019: XLOOKUP jest obecnie funkcją wersji beta i jest w tej chwili dostępny tylko dla części niejawnych testerów pakietu Office . Będziemy w dalszym ciągu optymalizować w ciągu kilku kolejnych miesięcy. Gdy XLOOKUP jest już gotowy, udostępnimy go wszystkim niejawnym testerom pakietu Office i subskrybentom pakietu office 365.

Funkcja XLOOKUP przeszukuje zakres lub tablicę i zwraca element odpowiadający pierwszej znalezionej wartości. Jeśli dopasowanie nie istnieje, XLOOKUP może zwrócić najbliższy (przybliżony) odpowiednik. 

= XLOOKUP. Jeżeli (szukana_wartość; przeszukiwana_tab; return_array; [match_mode]; [search_mode]; [if_not_found]) 

Argument

Opis

odniesienie

Wymagane

Wartość odnośnika

tablicy

Wymagane

Tablica lub zakres do przeszukania

return_array

Wymagane

Tablica lub zakres do zwrócenia

[match_mode]

Opcjonalnie

Określ typ dopasowania:

0 — dokładne dopasowanie. Jeśli nie odnaleziono, zwróć #N/A. To jest domyślne ustawienie.

-1-dokładne dopasowanie. Jeśli niczego nie znaleziono, zwróć następny mniejszy element.

1 — dokładne dopasowanie. Jeśli niczego nie znaleziono, zwróć następny większy element.

2 — dopasowania z symbolami wieloznacznymi, gdzie *,?, i ~ mają specjalne znaczenie.

[search_mode]

Opcjonalnie

Określ tryb wyszukiwania, którego chcesz użyć:

1 — przeprowadź wyszukiwanie Zaczynając od pierwszego elementu. To jest domyślne ustawienie.

-1 — przeprowadź wyszukiwanie wsteczne Zaczynając od ostatniej pozycji.

2 — wykonywanie wyszukiwania binarnego, które polega na sortowaniu zawartości przeszukiwana_tab w kolejności rosnącej . Jeśli nie posortowano, zostaną zwrócone nieprawidłowe wyniki.

-2 — wykonywanie wyszukiwania binarnego, które polega na sortowaniu w formacie przeszukiwana_tab w kolejności malejącej . Jeśli nie posortowano, zostaną zwrócone nieprawidłowe wyniki.

[if_not_found]

Opcjonalnie

Jeśli nie odnaleziono prawidłowego dopasowania, zwróć podany tekst [if_not_found].

Jeśli nie znaleziono prawidłowego dopasowania, a brakuje [if_not_found], zostanie zwrócona wartość #N/A.

Przykłady

Przykład 1

Ten przykład pochodzi z powyższego klipu wideo i używa prostej XLOOKUP do wyszukania nazwy kraju, a następnie zwrotu numeru kierunkowego kraju. Obejmuje tylko argumenty szukana_wartość (komórka F2), przeszukiwana_tab (zakres B2: B11) oraz return_array (zakres D2: D11). Nie zawiera argumentu match_mode, ponieważ XLOOKUP domyślnie jest to dokładne dopasowanie.

Przykładowa funkcja XLOOKUP, używana do zwracania nazwy pracownika i działu na podstawie identyfikatora pracownika. Formuła to = XLOOKUP (B2; B5: B14; C5: C14).

Uwaga: XLOOKUP różni się od funkcji Wyszukaj. pionowo, ponieważ używa oddzielnych tablic odnośników i zwrotów, gdzie funkcja Wyszukaj. pionowo używa pojedynczej tablicy tabel, a następnie numeru indeksu kolumny. Odpowiednią formułę funkcji Wyszukaj. pionowo w takim przypadku: = Wyszukaj. pionowo (F2; B2: D11; 3; FAŁSZ)

Przykład 2

W tym przykładzie wyszukiwane są informacje o pracownikach na podstawie numeru IDENTYFIKACYJNego pracownika. W przeciwieństwie do funkcji Wyszukaj. pionowo XLOOKUP może zwrócić tablicę z wieloma elementami, co umożliwia pojedynczej formule Zwracanie nazwy pracownika i działu (komórki C5: D14).

Przykładowa funkcja XLOOKUP używana do zwracania nazwy pracownika i działu na podstawie IDt pracowników. Formuła: = XLOOKUP (B2; B5: B14; C5: D14; 0; 1)

Przykład 3

W tym przykładzie argument if_not_found zostanie dodany do powyższego przykładu.

Przykładowa funkcja XLOOKUP używana do zwracania nazwy pracownika i działu na podstawie identyfikatora pracownika z argumentem if_not_found. Formuła to = XLOOKUP (B2; B5: B14; C5: D14; 0; 1; "nie znaleziono pracownika")

Przykład 4

W poniższym przykładzie w kolumnie C jest wyświetlana wartość dopłata osobista wprowadzona w komórce E2, a w kolumnie B znajduje się pasująca stawka podatku. Używa argumentu match_mode o wartości 1, co oznacza, że funkcja będzie szukać dokładnego dopasowania, a jeśli nie może go znaleźć, zostanie zwrócony następny większy element.

Obraz funkcji XLOOKUP użytej do zwrócenia stawki podatkowej na podstawie maksymalnego dochodu. Jest to dokładne dopasowanie. Formuła: = XLOOKUP (E2; C2: C7; B2: B7; 1; 1)

Uwaga: W przeciwieństwie do funkcji Wyszukaj. pionowo kolumna przeszukiwana_tab znajduje się po prawej stronie kolumny return_array, gdzie funkcja Wyszukaj. pionowo może wyglądać tylko od lewej do prawej.

Przykład 5

Następnie użyjemy zagnieżdżonej funkcji XLOOKUP w celu wykonania zarówno dopasowania pionowego, jak i poziomego. W tym przypadku najpierw zapoznajemy zysk brutto w kolumnie B, a następnie poszukaj pozycji KW1 w pierwszym wierszu tabeli (zakres C5: F5) i zwróć wartość na przecięciu dwóch. Jest to podobne do korzystania z funkcji indeks i Podaj . pozycję. Funkcji Wyszukaj . pionowo można także użyć XLOOKUP.

Obraz funkcji XLOOKUP używanej do zwracania danych poziomych z tabeli przez zagnieżdżenie 2 XLOOKUPs. Formuła: = XLOOKUP (D2, $B 6: $B 17, XLOOKUP ($C 3, $C 5: $G 5, $C 6: $G 17))

Formuła w komórkach D3: = XLOOKUP (D2, $B 6: $B 17, XLOOKUP ($C 3, $C 5: $G 5, $C 6: $G 17)).

Przykład 6

W tym przykładzie użyto funkcji Sumai dwóch zagnieżdżonych funkcji XLOOKUP, aby zsumować wszystkie wartości z dwóch zakresów. W tym przypadku chcemy zsumować wartości dla winogron, bananów i uwzględnić gruszki, które są między nimi.

Używanie XLOOKUP z SUMĄ do zsumowania zakresu wartości, które należą do dwóch zaznaczeń

Formuła w komórce E3: = suma (XLOOKUP (B3; B6: B10; E6: E10): XLOOKUP (C3, B6: B10, E6: E10))

Jak to działa? Funkcja XLOOKUP zwraca zakres, więc po obliczeniu formuły kończy się w następujący sposób: = suma ($E $7: $E $9). Możesz zobaczyć, jak to działa, wybierając komórkę z formułą XLOOKUP podobną do tej, a następnie przejdź do pozycji formuły > inspekcji formuł > szacowanie formuły, a następnie naciśnij przycisk Oceń , aby przejść do kolejnych obliczeń.

Uwaga: Aby zasugerować ten przykład, Dziękujemy za Microsoft Excel MVP, Bill Jelena.

Potrzebujesz dodatkowej pomocy?

Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel, uzyskać pomoc techniczną w społeczności witryny Answers bądź zasugerować nową funkcję lub ulepszenie w witrynie UserVoice dotyczącej programu Excel.

Zobacz też

Funkcja XMATCH

Funkcje programu Excel (lista alfabetyczna)

Funkcje programu Excel (według kategorii)

Uwaga:  Ta strona została przetłumaczona automatycznie i może zawierać błędy gramatyczne lub nieścisłości. Chcemy, aby ta zawartość była dla Ciebie przydatna. Czy możesz dać nam znać, czy te informacje były pomocne? Oto angielskojęzyczny artykuł do wglądu.

Rozwijaj umiejętności związane z pakietem Office
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

Dziękujemy za opinię!

Dziękujemy za opinię! Wygląda na to, że połączenie Cię z jednym z naszych agentów pomocy technicznej pakietu Office może być pomocne.

×