Jak korzystać z funkcji XLOOKUP w programie Excel 2016

Excel 2016 dla subskrybentów Office 365 w systemach Windows i Mac obsługuje teraz nową funkcję WYSZUKAJ.PIONOWO, reklamowaną jako znacznie prostszy i bardziej wszechstronny zamiennik bardzo popularnej (choć często oczernianej) funkcji wyszukiwania pionowego, WYSZUKAJ.PIONOWO (nie wiem, co X w funkcji WYSZUKAJ.XX) oznacza: być może rozbudowany?).

Dla tych, którzy nie znają jeszcze funkcji WYSZUKAJ.PIONOWO (uważanej za trzecią najczęściej używaną funkcję zaraz po SUMA i ŚREDNIA), ta funkcja przeszukuje pionowo według wiersza w skrajnej lewej kolumnie wyznaczonej tabeli wyszukiwania od góry do dołu, aż znajdzie wartość w kolumna wyszukiwania wyznaczona przez numer przesunięcia, który odpowiada lub przekracza ten, którego szukasz. Chociaż niezwykle przydatna do lokalizowania określonych elementów na długiej liście lub kolumnie tabeli danych w arkuszu, funkcja WYSZUKAJ.PIONOWO ma kilka ograniczeń, które nie są wspólne dla tej nowej funkcji wyszukiwania, na przykład XLOOKUP:

• Domyślne wyszukiwanie dokładnych odpowiedników dla Twojej wartości wyszukiwania w zakresie wyszukiwania
• Może wyszukiwać zarówno w pionie (według wiersza), jak i w poziomie (według kolumny) w tabeli, zastępując w ten sposób potrzebę korzystania z funkcji WYSZUKAJ.POZIOMO podczas wyszukiwania w poziomie według kolumny
• Może wyszukiwać w lewo lub w prawo, dzięki czemu zakres wyszukiwania w tabeli wyszukiwania nie musi byćumieszczony w kolumnie na lewo od kolumny wyznaczonej jako zakres zwracany, aby funkcja działała
• Gdy używana jest wartość domyślna dopasowania ścisłego, działa nawet wtedy, gdy wartości w zakresie wyszukiwania nie są posortowane w określonej kolejności
• Może wyszukiwać od dolnego wiersza do góry w zakresie tablicy wyszukiwania, używając opcjonalnego argumentu trybu wyszukiwania

Funkcja XLOOKUP ma pięć możliwych argumentów, z których pierwsze trzy są wymagane, a dwa ostatnie opcjonalne, przy użyciu następującej składni:

XLOOKUP ( szukana_wartość , tablica_wyszukiwania , return_array [ match_mode ] [ search_mode ])

Wymagany argument search_value określa wartość lub element, którego szukasz. Wymagany argument tablica look_up określa zakres komórek, które mają być przeszukane dla tej wartości wyszukiwania, a argument return_array określa zakres komórek zawierających wartość, która ma zostać zwrócona, gdy program Excel znajdzie dokładne dopasowanie.

* Podczas wyznaczania argumentów lookup_array i return_array w funkcji XLOOKUP należy pamiętać, że oba zakresy muszą mieć równą długość, w przeciwnym razie program Excel zwróci #VALUE! błąd w Twojej formule. Jest to tym bardziej powód, dla którego podczas definiowania tych argumentów należy używać nazw zakresów lub nazw kolumn w wyznaczonej tabeli danych, zamiast wskazywać je lub wpisywać odwołania do ich komórek .

Opcjonalny argument match_mode może zawierać dowolną z następujących czterech wartości:

• 0 dla dokładnego dopasowania (wartość domyślna, tak samo jak w przypadku braku argumentu match_mode )
• -1 dla dokładnego dopasowania lub następnej mniejszej wartości
• 1 dla dokładnego dopasowania lub następnej większej wartości
• 2 dla częściowego dopasowania przy użyciu symboli wieloznacznych połączonych z odwołaniem do komórki w argumencie szukana_wartość

Opcjonalny argument search_mode może zawierać dowolną z następujących czterech wartości:

• 1, aby wyszukiwać od pierwszego do ostatniego, czyli od góry do dołu (domyślnie, tak samo jak w przypadku braku argumentu search_mode )
• -1 do wyszukiwania od ostatniego do pierwszego, czyli od dołu do góry
• 2 dla wyszukiwania binarnego w porządku rosnącym
• -2 dla wyszukiwania binarnego w porządku malejącym

Najlepszym sposobem na zrozumienie mocy i wszechstronności nowej funkcji XLOOKUP jest zobaczenie jej w działaniu w arkuszu programu Excel. Na poniższym rysunku mam arkusz z prostą tabelą danych sprzedaży 2019 uporządkowaną według kraju. Aby użyć funkcji XLOOKUP do zwrócenia łącznej sprzedaży z tej tabeli w komórce E4 na podstawie kraju wprowadzonego w komórce D4 arkusza, wykonaj następujące czynności:

Umieść kursor komórki w komórce E4 arkusza roboczego

Kliknij opcję Wyszukaj i odwołanie na karcie Formuły, a następnie XLOOKUP u dołu menu rozwijanego, aby otworzyć okno dialogowe Argumenty funkcji.

Kliknij komórkę D4 w arkuszu, aby wprowadzić jej odwołanie do komórki w polu tekstowym argument Wartość_szukana.

Naciśnij klawisz Tab, aby zaznaczyć pole tekstowe argumentu Wyszukaj_tablica, a następnie kliknij komórkę A4 i przytrzymaj klawisz Shift, naciskając klawisz Ctrl-strzałka w dół, aby wybrać A4:A8 jako zakres do przeszukania (ponieważ zakres A3:B8 jest zdefiniowany jako tabela danych programu Excel, Tabela1[Kraj] pojawia się w polu tekstowym w miejscu zakresu A4:A8).

Naciśnij klawisz Tab, aby zaznaczyć pole tekstowe argumentu Return_array, a następnie kliknij komórkę B4 i przytrzymaj klawisz Shift, naciskając klawisz Ctrl-strzałka w dół, aby wybrać B4:B8 jako zakres zawierający wartości do zwrócenia na podstawie wyników wyszukiwania (wyświetlane jako Tabela1[Całkowita sprzedaż] w polu tekstowym).

Kliknij OK, aby wprowadzić formułę XLOOKUP w komórce E4.

Tworzenie formuły z funkcją XLOOKUP w komórce E4, która zwraca sprzedaż na podstawie kraju wprowadzonego w komórce D4.

Excel wprowadza formułę XLOOKUP do komórki E4 arkusza i zwraca jako wynik 4900, ponieważ Kostaryka jest obecnie wpisana do komórki wyszukiwania D4 i jak widać w tabeli sprzedaży 2019, jest to rzeczywiście całkowita sprzedaż dokonana w tym kraju.

Ponieważ XLOOKUP działa od prawej do lewej tak samo dobrze, jak od lewej do prawej, możesz użyć tej funkcji równie dobrze, aby zwrócić kraj z tej tabeli sprzedaży na podstawie określonej liczby sprzedaży. Poniższy rysunek pokazuje, jak to zrobić. Tym razem tworzysz formułę WYSZUKAJ.X w komórce D4 i wyznaczasz wartość wprowadzoną w komórce E4 (w tym przypadku 11 000) jako argument wartość_szukana.

Ponadto wpisujesz -1 jako argument match_mode, aby zastąpić domyślne dopasowanie dokładne funkcji, tak aby program Excel zwracał kraj z dokładnym dopasowaniem do wartości sprzedaży wprowadzonej w komórce wyszukiwania E4 lub kraj z następną niższą sprzedażą całkowitą (Meksyk z W tym przypadku 10 000 USD, ponieważ w tej tabeli nie ma kraju z 11 000 USD całkowitej sprzedaży). Bez określenia argumentu tryb_dopasowania dla tej formuły program Excel zwróci #NA jako wynik, ponieważ w tej tabeli sprzedaży nie ma dokładnego dopasowania do 11 000 USD.

Tworzenie formuły z XLOOKUP w komórce D4, która zwraca kraj na podstawie sprzedaży wprowadzonej w komórce E4

Ponieważ funkcja XLOOKUP jest równie wygodna w przeszukiwaniu w poziomie według kolumny, jak w pionie według wiersza, można jej użyć do utworzenia formuły, która wykonuje wyszukiwanie dwukierunkowe (zastępując potrzebę tworzenia formuły, która łączy funkcje INDEKS i PODAJ.POZYCJĘ jako w przeszłości). Poniższy rysunek, zawierający tabelę harmonogramu produkcji 2019 dla numerów części, od AB-100 do AB-103 dla miesięcy od kwietnia do grudnia, pokazuje, jak to się robi.

Tworzenie formuły z zagnieżdżonymi funkcjami XLOOKUP w celu zwrócenia liczby jednostek wyprodukowanych dla części w danym miesiącu

W komórce B12 utworzyłem następującą formułę:

=XLOOKUP(part_lookup,$A$3:$A$6,XLOOKUP(data_lookup,$B$2:$J$2,$B$3:$J$6))

Ta formuła zaczyna się od zdefiniowania funkcji XLOOKUP, która wyszukuje w pionie według wiersza dokładne dopasowanie do wpisu części dokonanego w komórce o nazwie part_lookup (w tym przypadku komórka B10) w zakresie komórek $ A $ 3: $ A $ 6 tabeli produkcyjnej . Należy jednak zauważyć, że argument return_array dla tej oryginalnej funkcji WYSZUKAJ jest drugą funkcją XLOOKUP.

Ta druga, zagnieżdżona funkcja XLOOKUP przeszukuje zakres komórek $B$2:$J$2 poziomo według kolumny w celu dokładnego dopasowania do wpisu daty wprowadzonego w komórce o nazwie data_wyszukaj (w tym przypadku komórka B11). Argumentem return_array dla tej drugiej, zagnieżdżonej funkcji XLOOKUP jest $B$3:$J$6, czyli zakres komórek wszystkich wartości produkcyjnych w tabeli.

Sposób działania tej formuły polega na tym, że program Excel najpierw oblicza wynik drugiej, zagnieżdżonej funkcji XLOOKUP, wykonując wyszukiwanie poziome, które w tym przypadku zwraca tablicę w zakresie komórek D3: D6 kolumny cze-19 (z wartościami: 438, 153, 306 i 779) w wyniku. Ten wynik z kolei staje się argumentem return_array oryginalnej funkcji XLOOKUP, która wykonuje pionowe wyszukiwanie według wiersza w celu dokładnego dopasowania do wpisu numeru części dokonanego w komórce B11 (o nazwie part_lookup). Ponieważ w tym przykładzie ta komórka part_lookup zawiera AB-102, formuła zwraca tylko wartość produkcji cze-19, 306, z wyniku drugiej, następnej funkcji XLOOKUP.

Masz to! Pierwsze spojrzenie na XLOOKUP, potężną, wszechstronną i dość łatwą w użyciu nową funkcję wyszukiwania, która może nie tylko wykonywać wyszukiwania pojedynczej wartości wykonywane przez funkcje WYSZUKAJ.PIONOWO i WYSZUKAJ.POZIOMO, ale także dwukierunkowe wyszukiwania wartości wykonywane przez połączenie Funkcje INDEKS i PODAJ.POZYCJĘ.

* Niestety funkcja WYSZUKAJ.PIONOWO nie jest wstecznie zgodna z wcześniejszymi wersjami programu Microsoft Excel, które obsługują tylko funkcje WYSZUKAJ.PIONOWO i WYSZUKAJ.POZIOMO lub jest zgodna z bieżącymi wersjami, które nie zawierają jej jeszcze jako jednej ze swoich funkcji wyszukiwania, takich jak Excel 2019 i Excel Online . Oznacza to, że jeśli udostępnisz skoroszyt zawierający formuły XLOOKUP współpracownikom lub klientom korzystającym z wersji programu Excel, która nie zawiera tej nowej funkcji wyszukiwania, wszystkie te formuły zwrócą #NAZWA? wartości błędów po otwarciu arkusza.