Funkcja Excel XLOOKUP: wszystko, co musisz wiedzieć (10 przykładów)

Obejrzyj wideo - funkcja Excel XLOOKUP (10 przykładów XLOOKUP)

Przewyższać Funkcja XWYSZUKAJ w końcu przybył.

Jeśli korzystasz z funkcji WYSZUKAJ.PIONOWO lub INDEX/MATCH, na pewno spodoba Ci się elastyczność, jaką zapewnia funkcja WYSZUKAJ.PIONOWO.

W tym samouczku omówię wszystko, co trzeba wiedzieć o funkcji XLOOKUP i kilka przykładów, które pomogą ci dowiedzieć się, jak najlepiej z niej korzystać.

Więc zacznijmy!

Co to jest XLOOKUP?

XLOOKUP to nowa funkcja w Office 365 i nowa, ulepszona wersja funkcji WYSZUKAJ.PIONOWO/WYSZUKAJ.POZIOMO.

Robi wszystko, co kiedyś robiła funkcja WYSZUKAJ.PIONOWO i wiele więcej.

XLOOKUP to funkcja, która pozwala szybko wyszukać wartość w zestawie danych (pionowym lub poziomym) i zwrócić odpowiednią wartość w innym wierszu/kolumnie.

Na przykład, jeśli masz wyniki dla uczniów na egzaminie, możesz użyć XLOOKUP, aby szybko sprawdzić, ile uczeń zdobył, używając imienia i nazwiska ucznia.

Moc tej funkcji stanie się jeszcze bardziej wyraźna, gdy zagłębię się w niektóre XLOOKUP przykłady w dalszej części tego samouczka.

Ale zanim przejdę do przykładów, jest duże pytanie - jak uzyskać dostęp do XLOOKUP?

Jak uzyskać dostęp do XLOOKUP?

Od teraz XLOOKUP jest dostępny tylko dla użytkowników Office 365.

Jeśli więc korzystasz z wcześniejszych wersji programu Excel (2010/2013/2016/2019), nie będziesz mógł korzystać z tej funkcji.

Nie jestem również pewien, czy kiedykolwiek zostanie to wydane dla wcześniejszych wersji, czy nie (być może Microsoft może utworzyć dodatek tak, jak zrobił to w przypadku Power Query). Ale od teraz możesz z niego korzystać tylko wtedy, gdy korzystasz z Office 365.

Kliknij tutaj, aby uaktualnić do Office 365

Jeśli korzystasz już z Office 365 (wersja Home, Personal lub University) i nie masz do niej dostępu, możesz przejść do karty Plik, a następnie kliknąć Konto.

Byłby program Office Insider i możesz kliknąć i dołączyć do programu Office Insider. Dzięki temu uzyskasz dostęp do funkcji XLOOKUP.

Spodziewam się, że XLOOKUP będzie wkrótce dostępny we wszystkich wersjach Office 365.

Uwaga: XLOOKUP jest również dostępny dla Office 365 dla komputerów Mac i Excel dla sieci Web (Excel online)

XLOOKUP Składnia funkcji

Poniżej znajduje się składnia funkcji XLOOKUP:

=XWYSZUKAJ(wyszukaj_wartość, wyszukana_tablica, zwrócona_tablica, [jeśli nie znaleziono], [tryb_dopasowania], [tryb_wyszukiwania])

Jeśli korzystałeś z funkcji WYSZUKAJ.PIONOWO, zauważysz, że składnia jest dość podobna, oczywiście z kilkoma niesamowitymi dodatkowymi funkcjami.

Nie martw się, jeśli składnia i argument wyglądają trochę za dużo. W dalszej części tego samouczka opiszę je kilkoma prostymi przykładami XLOOKUP, które sprawią, że będzie to krystalicznie jasne.

Funkcja XLOOKUP może podać 6 argumentów (3 obowiązkowe i 3 opcjonalne):

1. szukana_wartość - wartość, której szukasz
2. lookup_array - tablica, w której szukasz wartości wyszukiwania
3. return_array - tablica, z której chcesz pobrać i zwrócić wartość (odpowiadającą pozycji, w której znajduje się szukana wartość)
4. [jeśli nie znaleziono] - wartość do zwrócenia w przypadku, gdy wartość wyszukiwania nie zostanie znaleziona. Jeśli nie podasz tego argumentu, zostanie zwrócony błąd #N/D
5. [tryb dopasowania] - Tutaj możesz określić rodzaj dopasowania, które chcesz:
   • 0 - Dokładne dopasowanie, gdzie szukana_wartość powinna dokładnie odpowiadać wartości w szukanej_tablicy. Jest to opcja domyślna.
   • -1 - Szuka dokładnego dopasowania, ale jeśli zostanie znalezione, zwraca następny mniejszy element/wartość
   • 1 - Szuka dokładnego dopasowania, ale jeśli zostanie znalezione, zwraca następny większy element/wartość
   • 2 - Aby wykonać częściowe dopasowanie za pomocą symboli wieloznacznych (* lub ~)
6. [tryb szukania] - Tutaj określasz, w jaki sposób funkcja XLOOKUP powinna przeszukiwać wyszukiwaną_tablicę
   • 1 - Jest to domyślna opcja, w której funkcja zaczyna szukać szukanej_wartości od góry (pierwszy element) do dołu (ostatni element) w lookup_array
   • -1 - Czy wyszukiwanie od dołu do góry. Przydatne, gdy chcesz znaleźć ostatnią pasującą wartość w lookup_array
   • 2 - Wykonuje wyszukiwanie binarne, w którym dane muszą być posortowane w kolejności rosnącej. Jeśli nie zostanie posortowany, może to dać błąd lub błędne wyniki
   • -2 - Wykonuje wyszukiwanie binarne, w którym dane należy posortować w kolejności malejącej. Jeśli nie zostanie posortowany, może to dać błąd lub błędne wyniki

XLOOKUP Przykłady funkcji

Przejdźmy teraz do interesującej części - kilku praktycznych przykładów XLOOKUP.

Te przykłady pomogą Ci lepiej zrozumieć, jak działa WYSZUKAJ.PIONOWO, czym różni się od funkcji WYSZUKAJ.PIONOWO i INDEKS/DOPASUJ, a także niektóre ulepszenia i ograniczenia tej funkcji.

Kliknij tutaj, aby pobrać przykładowy plik i podążaj dalej

Przykład 1: Pobierz wartość wyszukiwania

Załóżmy, że masz następujący zestaw danych i chcesz pobrać wynik matematyczny dla Grega (wartość wyszukiwania).

Poniżej znajduje się formuła, która to robi:

=XWYSZUKAJ(F2,A2:A15;B2:B15)

W powyższym wzorze właśnie użyłem obowiązkowych argumentów, w których szuka nazwy (od góry do dołu), znajduje dokładne dopasowanie i zwraca odpowiednią wartość z B2: B15.

Jedną z oczywistych różnic między funkcjami XLOOKUP i VLOOKUP jest sposób, w jaki obsługują tablicę wyszukiwania. W funkcji WYSZUKAJ.PIONOWO masz całą tablicę, w której wartość wyszukiwania znajduje się w skrajnej lewej kolumnie, a następnie określasz numer kolumny, z której chcesz pobrać wynik. Z drugiej strony XLOOKUP pozwala wybrać osobno lookup_array i return_array

Jedna natychmiastowa korzyść z posiadania osobnych argumentów lookup_array i return_array oznacza, że ​​teraz możesz spójrz w lewo. WYSZUKAJ.PIONOWO miał to ograniczenie, w którym można tylko wyszukać i znaleźć wartość po prawej stronie. Ale dzięki XLOOKUP to ograniczenie zniknęło.

Oto przykład. Mam ten sam zestaw danych, w którym nazwa jest po prawej stronie, a zakres_zwrotów po lewej.

Poniżej znajduje się formuła, której mogę użyć, aby uzyskać wynik dla Grega w matematyce (co oznacza patrzenie na lewo od szukanej_wartości)

=XWYSZUKAJ(F2,D2:D15;A2:A15)

XLOOKUP rozwiązuje inny poważny problem - w przypadku wstawienia nowej kolumny lub przeniesienia kolumn, wynikowe dane będą nadal poprawne. Funkcja WYSZUKAJ.PIONOWO prawdopodobnie zepsuje lub da niepoprawny wynik w takich przypadkach, ponieważ w większości przypadków wartość indeksu kolumny jest zakodowana na stałe.

Przykład 2: Wyszukaj i pobierz cały rekord

Weźmy te same dane jako przykład.

W tym przypadku nie chcę po prostu pobrać wyniku Grega z matematyki. Chcę uzyskać wyniki ze wszystkich przedmiotów.

W takim przypadku mogę skorzystać z poniższej formuły:

=XWYSZUKAJ(F2,A2:A15;B2:D15)

Powyższa formuła używa zakresu return_array, który jest większy niż kolumna (B2:D15). Tak więc, gdy wartość wyszukiwania zostanie znaleziona w A2:A15, formuła zwraca cały wiersz z tablicy_powrotów.

Ponadto nie można usunąć tylko komórek, które są częścią tablicy, która została automatycznie wypełniona. W tym przykładzie nie można usunąć H2 ani I2. Jeśli spróbujesz, nic się nie stanie. Jeśli wybierzesz te komórki, formuła na pasku formuły będzie wyszarzona (co oznacza, że ​​nie można jej zmienić)

Możesz usunąć formułę w komórce G2 (gdzie pierwotnie ją wprowadziliśmy), spowoduje to usunięcie całego wyniku.

Jest to przydatne ulepszenie, ponieważ wcześniej w przypadku funkcji WYSZUKAJ.PIONOWO będziesz musiał określić numer kolumny osobno dla każdej formuły.

Przykład 3: Dwukierunkowe wyszukiwanie za pomocą XLOOKUP (wyszukiwanie w poziomie i w pionie)

Poniżej znajduje się zestaw danych, w którym chcę poznać wynik Grega w matematyce (temat w komórce G2).

Można to zrobić za pomocą dwukierunkowego wyszukiwania, w którym szukam nazwy w kolumnie A i nazwy przedmiotu w wierszu 1. Zaletą tego dwukierunkowego wyszukiwania jest to, że wynik jest niezależny od nazwy ucznia i nazwy przedmiotu. Jeśli zmienię nazwę przedmiotu na Chemia, ta dwukierunkowa formuła XLOOKUP nadal będzie działać i da mi poprawny wynik.

Poniżej znajduje się formuła, która wykona dwukierunkowe wyszukiwanie i da poprawny wynik:

=XWYSZUKAJ(G1,B1:D1,XWYSZUKAJ(F2,A2:A15;B2:D15))

Ta formuła używa zagnieżdżonej funkcji XLOOKUP, w której najpierw używam jej do pobrania wszystkich znaków ucznia z komórki F2.

Tak więc wynik XLOOKUP(F2,A2:A15,B2:D15) to {21,94,81}, co w tym przypadku jest tablicą ocen uzyskanych przez Grega.

Jest on następnie używany ponownie w zewnętrznej formule XLOOKUP jako tablica zwracana. W zewnętrznej formule XLOOKUP szukam nazwy podmiotu (która znajduje się w komórce G1), a tablica wyszukiwania to B1:D1.

Jeśli nazwa podmiotu to Math, ta zewnętrzna formuła XLOOKUP pobiera pierwszą wartość z tablicy zwracanej - czyli {21,94,81} w tym przykładzie.

Robi to samo, co do tej pory osiągano za pomocą kombinacji INDEKS i DOPASUJ

Kliknij tutaj, aby pobrać przykładowy plik i podążaj dalej

Przykład 4: Gdy nie można znaleźć wartości wyszukiwania (obsługa błędów)

Obsługa błędów została teraz dodana do formuły XLOOKUP.

Czwartym argumentem w funkcji XLOOKUP jest [if_not_found], gdzie możesz określić, co chcesz na wypadek, gdyby wyszukiwanie nie zostało znalezione.

Załóżmy, że masz zestaw danych, jak pokazano poniżej, w którym chcesz uzyskać wynik matematyczny w przypadku dopasowania, a w przypadku, gdy nazwa nie zostanie znaleziona, chcesz zwrócić - „Nie pojawił się”

Zrobi to poniższa formuła:

=XWYSZUKAJ(F2,A2:A15;B2:B15;"Nie pojawiła się")

W tym przypadku na stałe zakodowałem to, co chcę uzyskać na wypadek braku dopasowania. Możesz także użyć odwołania do komórki lub formuły.

Przykład 5: Zagnieżdżone XLOOKUP (wyszukiwanie w wielu zakresach)

Geniusz posiadania argumentu [jeśli nie znaleziono] polega na tym, że pozwala on na użycie zagnieżdżona formuła XLOOKUP.

Załóżmy na przykład, że masz dwie oddzielne listy, jak pokazano poniżej. Chociaż mam te dwie tabele na tym samym arkuszu, możesz mieć je w osobnych arkuszach lub nawet zeszytach.

Poniżej znajduje się zagnieżdżona formuła XLOOKUP, która sprawdzi nazwę w obu tabelach i zwróci odpowiednią wartość z określonej kolumny.

=XWYSZUKAJ(A12;A2:A8;B2:B8;XWYSZUKAJ(A12;F2:F8;G2:G8))

W powyższej formule użyłem argumentu [if_not_found], aby użyć innej formuły XLOOKUP. Pozwala to dodać drugą XLOOKUP w tej samej formule i zeskanować dwie tabele za pomocą jednej formuły.

Nie jestem pewien, ile zagnieżdżonych wyszukiwań XLOOKUP możesz użyć w formule. Próbowałem do 10 i zadziałało, potem się poddałem 🙂

Przykład 6: Znajdź ostatnią pasującą wartość

Ten był bardzo potrzebny, a funkcja XLOOKUP to umożliwiła. Teraz nie musisz szukać zawiłych sposobów, aby uzyskać ostatnią pasującą wartość w zakresie.

Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz sprawdzić, kiedy była ostatnia osoba zatrudniona w każdym dziale i jaka była data zatrudnienia.

Poniższa formuła wyszuka ostatnią wartość dla każdego działu i poda nazwę ostatniego pracownika:

=XWYSZUKAJ (F1, $ B 2: $ B 15 $, $ A $ 2: $ A $ 15,,,-1)

A poniższy wzór poda datę ostatniego zatrudnienia dla każdego działu:

=XWYSZUKAJ(F1,$B2:$B15$,$C$2:$C$15,,,-1)

Ponieważ XLOOKUP ma wbudowaną funkcję określania kierunku wyszukiwania (od pierwszego do ostatniego lub od ostatniego do pierwszego), odbywa się to za pomocą prostej formuły. W przypadku danych pionowych WYSZUKAJ.PIONOWO i INDEKS/DOPASUJ zawsze patrzą od góry do dołu, ale w przypadku funkcji WYSZUKAJ.PIONOWO można również określić kierunek jako od dołu do góry.

Przykład 7: Przybliżone dopasowanie z XLOOKUP (Znajdź stawkę podatku)

Innym znaczącym ulepszeniem XLOOKUP jest to, że teraz dostępne są cztery tryby dopasowania (VLOOKUP ma 2, a MATCH ma 3).

Możesz określić dowolny z czterech argumentów, aby zdecydować, jak wyszukiwana wartość powinna być dopasowana:

• 0 - Dokładne dopasowanie, gdzie szukana_wartość powinna dokładnie odpowiadać wartości w szukanej_tablicy. Jest to opcja domyślna.
• -1 - Szuka dokładnego dopasowania, ale jeśli zostanie znalezione, zwraca następny mniejszy element/wartość
• 1 - Szuka dokładnego dopasowania, ale jeśli zostanie znalezione, zwraca następny większy element/wartość
• 2 - Aby wykonać częściowe dopasowanie za pomocą symboli wieloznacznych (* lub ~)

Ale najlepsze jest to, że nie musisz się martwić, czy Twoje dane są sortowane w porządku rosnącym czy malejącym. Nawet jeśli dane nie są posortowane, XLOOKUP zajmie się tym.

Poniżej mam zbiór danych, w którym chcę znaleźć prowizję każdej osoby - a prowizję należy obliczyć korzystając z tabeli po prawej stronie.

Poniżej znajduje się formuła, która to zrobi:

=XWYSZUKAJ(B2,$E$2:$E$6,$F$2:$F$6,0,-1)*B2

To po prostu używa wartości sprzedaży jako odnośnika i przegląda tabelę odnośników po prawej stronie. W tej formule użyłem -1 jako piątego argumentu ([match_mode]), co oznacza, że ​​będzie szukał dokładnego dopasowania, a gdy go nie znajdzie, zwróci wartość tylko mniejszą niż wartość wyszukiwania .

I jak powiedziałem, nie musisz się martwić, czy twoje dane są sortowane według nie.

Kliknij tutaj, aby pobrać przykładowy plik i podążaj dalej

Przykład 8: Wyszukiwanie poziome

XLOOKUP może wykonywać wyszukiwanie pionowe i poziome.

Poniżej mam zestaw danych, w którym mam nazwiska uczniów i ich wyniki w wierszach, i chcę pobrać wynik dla nazwiska w komórce B7.

Zrobi to poniższa formuła:

=XWYSZUKAJ(B7;B1:O1;B2:O2)

To nic innego jak proste wyszukiwanie (podobne do tego, co widzieliśmy w przykładzie 1), ale poziome.

Wszystkie opisane przeze mnie przykłady dotyczące wyszukiwania pionowego można również wykonać za pomocą wyszukiwania poziomego przy użyciu funkcji WYSZUKAJ.PIONOWO (pożegnanie z funkcją WYSZUKAJ.PIONOWO i WYSZUKAJ.POZIOMO).

Przykład 9: Wyszukiwanie warunkowe (używanie XLOOKUP z innymi formułami)

Ten jest nieco zaawansowanym przykładem, a także pokazuje moc XLOOKUP, gdy musisz wykonać złożone wyszukiwania.

Poniżej znajduje się zestaw danych, w którym mam nazwiska uczniów i ich wyniki, a także chcę znać imię i nazwisko ucznia, który uzyskał maksymalną liczbę punktów z każdego przedmiotu, oraz liczbę uczniów, którzy uzyskali więcej niż 80 punktów z każdego przedmiotu.

Poniżej formuła, która poda nazwisko ucznia z najwyższymi ocenami z każdego przedmiotu:

=XLOOKUP(MAX(XLOOKUP(G1,$B$1:$D$1,$B$2:$D$15)),XLOOKUP(G1,$B$1:$D$1,$B$2:$D$15),$A 2 USD: 15 USD)

Ponieważ XLOOKUP może być użyty do zwrócenia całej tablicy, użyłem go, aby najpierw uzyskać wszystkie znaki dla wymaganego tematu.

Na przykład w przypadku matematyki, gdy używam XLOOKUP(G1,$B$1:$D$1,$B$2:$D$15), daje mi to wszystkie wyniki z matematyki. Następnie mogę użyć funkcji MAX, aby znaleźć maksymalny wynik w tym zakresie.

Ten maksymalny wynik staje się następnie moją wartością wyszukiwania, a zakresem wyszukiwania będzie tablica zwrócona przez XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)

Używam tego w innej formule XLOOKUP, aby pobrać imię i nazwisko ucznia, który uzyskał maksymalną liczbę punktów.

Aby policzyć uczniów, którzy zdobyli więcej niż 80 punktów, użyj poniższego wzoru:

=LICZ.JEŻELI(XWYSZUKAJ(G1,$B$1:$D$1,$B$2:$D$15);">80")

Ten po prostu używa formuły XLOOKUP, aby uzyskać zakres wszystkich wartości dla danego tematu. Następnie zawija go w funkcję LICZ.JEŻELI, aby uzyskać liczbę wyników przekraczających 80.

Przykład 10: Używanie Wildcard w XLOOKUP

Tak jak możesz używać symboli wieloznacznych w WYSZUKAJ.PIONOWO i DOPASUJ, możesz to również zrobić za pomocą WYSZUKAJ.PIONOWO.

Ale jest różnica.

W XLOOKUP musisz określić, że używasz znaków wieloznacznych (w piątym argumencie). Jeśli tego nie określisz, XLOOKUP wyświetli błąd.

Poniżej znajduje się zbiór danych, w którym mam nazwy firm i ich kapitalizację rynkową.

Chcę wyszukać nazwę firmy w kolumnie D i pobrać kapitalizację rynkową z tabeli po lewej stronie. A ponieważ imiona w kolumnie D nie są dokładnymi odpowiednikami, będę musiał użyć symboli wieloznacznych.

Poniżej znajduje się formuła, która to zrobi:

=XWYSZUKAJ("*"&D2&"*",$A$2:$A$11,$B$2:$B$11,,2)

W powyższym wzorze użyłem symbolu wieloznacznego gwiazdki (*) przed i po D2 (musi być w cudzysłowie i połączony z D2 za pomocą znaku handlowego „ampersand”).

Dzięki temu formuła ma przejrzeć wszystkie komórki, a jeśli zawiera słowo w komórce D2 (czyli Apple), należy je uznać za dokładne dopasowanie. Bez względu na to, ile i jakie znaki znajdują się przed i po tekście w komórce D2.

Aby upewnić się, że XLOOKUP akceptuje znaki wieloznaczne, piąty argument został ustawiony na 2 (dopasowanie symboli wieloznacznych).

Przykład 11: Znajdź ostatnią wartość w kolumnie

Ponieważ XLOOKUP umożliwia wyszukiwanie od dołu do góry, możesz łatwo znaleźć ostatnią wartość na liście, a także pobrać odpowiednią wartość z kolumny.

Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz wiedzieć, jaka jest ostatnia firma i jaka jest kapitalizacja rynkowa tej ostatniej firmy.

Poniższy wzór poda nazwę ostatniej firmy:

=XWYSZUKAJ("*",A2:A11;A2:A11,,2,-1)

A poniższa formuła poda kapitalizację rynkową ostatniej firmy z listy:

=XWYSZUKAJ("*",A2:A11;B2:B11,,2,-1)

Te formuły ponownie używają symboli wieloznacznych. W nich użyłem gwiazdki (*) jako wartości wyszukiwania, co oznacza, że ​​pierwsza napotkana komórka zostanie uznana za dokładne dopasowanie (gwiazdka może być dowolnym znakiem i dowolną liczbą znaków).

A ponieważ kierunek jest od dołu do góry (dla danych ułożonych pionowo), zwróci ostatnią wartość na liście.

A druga formuła, ponieważ używa oddzielnego zakresu_zwrotów, aby uzyskać kapitalizację rynkową nazwiska z listy.

Kliknij tutaj, aby pobrać przykładowy plik i podążaj dalej

Co jeśli nie masz funkcji XLOOKUP?

Ponieważ funkcja XLOOKUP będzie prawdopodobnie dostępna tylko dla użytkowników Office 365, jednym ze sposobów na jej uzyskanie jest uaktualnienie do Office 365.

Jeśli masz już wersję Office 365 Home, Personal lub University, masz już dostęp do XLOOKUP. Wszystko, co musisz zrobić, to dołączyć do programu Office Insider.

Aby to zrobić, przejdź do zakładki Plik, kliknij Konto, a następnie kliknij opcję Insider pakietu Office. Byłaby opcja dołączenia do programu insider.

Jeśli masz inne subskrypcje Office 365 (takie jak Enterprise), jestem pewien, że XLOOKUP i inne niesamowite funkcje (takie jak tablice dynamiczne, formuły, takie jak SORTOWANIE i FILTROWANIE) będą wkrótce dostępne.

Jeśli korzystasz z programu Excel 2010/2013/2016/2019, nie będziesz mieć funkcji WYSZUKAJ.X i będziesz musiał nadal używać kombinacji WYSZUKAJ.PIONOWO, WYSZUKAJ.POZIOMO i INDEKS/PODEJMOWANIE, aby jak najlepiej wykorzystać formuły wyszukiwania.

X Kompatybilność wsteczna WYSZUKAJ

Jest to jedna rzecz, na którą musisz uważać - XLOOKUP to NIE jest kompatybilny wstecznie.

Oznacza to, że jeśli utworzysz plik i użyjesz formuły XLOOKUP, a następnie otworzysz go w wersji, która nie ma funkcji XLOOKUP, wyświetli błędy.

Ponieważ XLOOKUP to ogromny krok naprzód we właściwym kierunku, wierzę, że stanie się to domyślną formułą wyszukiwania, ale z pewnością minie kilka lat, zanim zostanie powszechnie przyjęta. Przecież nadal widuję kilka osób korzystających z Excela 2003.

Oto 11 przykładów XLOOKUP, które mogą pomóc szybciej wykonać wszystkie wyszukiwania i odniesienia, a także ułatwić korzystanie.

Mam nadzieję, że ten samouczek okazał się przydatny!