1. Oprogramowanie Microsoft OfficeExcelJak korzystać z funkcji XLOOKUP w programie Excel 2016

Greg Harvey

Excel 2016 dla subskrybentów Office 365 w systemach Windows i Mac obsługuje teraz nową funkcję XLOOKUP, reklamowaną jako znacznie prostszy i bardziej wszechstronny zamiennik bardzo popularnej (choć często złośliwej) funkcji wyszukiwania pionowego, VLOOKUP (nie wiem, co X w XLOOKUP oznacza; może eXtensive?).

Dla tych z Was, którzy nie znają jeszcze funkcji WYSZUKAJ.PIONOWO (uważanej za trzecią najczęściej używaną funkcję zaraz po SUMIE i ŚREDNIA), funkcja ta wyszukuje pionowo po rzędzie w kolumnie po lewej stronie wyznaczonej tabeli odnośników od góry do dołu, aż znajdzie wartość w kolumna odnośnika oznaczona liczbą przesunięcia, która odpowiada lub przekracza tę, na którą patrzysz. Chociaż funkcja VLOOKUP jest niezwykle przydatna do lokalizowania poszczególnych pozycji na długiej liście lub kolumnie tabeli danych w arkuszu, ma kilka ograniczeń, które nie są współdzielone przez tę nową funkcję wyszukiwania, jak XLOOKUP:

  • Domyślnie znajduje się dokładne dopasowanie wartości wyszukiwania w zakresie wyszukiwania Może wyszukiwać zarówno w pionie (według wiersza), jak i poziomo (według kolumny) w tabeli, zastępując w ten sposób potrzebę korzystania z funkcji WYSZUKAJ.PIONOWO podczas wyszukiwania w poziomie według kolumny Może wyszukiwać w lewo lub w prawo, aby zakres wyszukiwania w tabeli odnośników nie musiał znajdować się w kolumnie po lewej stronie tej oznaczonej jako zakres powrotu, aby funkcja działała Gdy używane jest domyślne dopasowanie dokładne, działa nawet wtedy, gdy wartości w zakresie wyszukiwania nie są sortowane w określonej kolejności Może wyszukiwać od dolnego wiersza do góry w zakresie tablicy odnośników, używając opcjonalnego argumentu trybu wyszukiwania

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

XLOOKUP (wartość_wyszukiwania, tablica_wyszukiwania, tablica_powrotu, [tryb_pasowania], [tryb_wyszukiwania])

Wymagany argument lookup_value określa wartość lub element, którego szukasz. Wymagany argument tablicy look_up określa zakres komórek, które mają być wyszukiwane dla tej wartości wyszukiwania, a argument return_array określa zakres komórek zawierający wartość, którą chcesz zwrócić, gdy Excel znajdzie dokładne dopasowanie.

* Podczas wyznaczania argumentów lookup_array i return_array w funkcji XLOOKUP należy pamiętać o tym, że oba zakresy muszą być równej długości, w przeciwnym razie program Excel zwróci wartość # WARTOŚĆ! błąd w formule. Jest to tym bardziej powód, dla którego należy używać nazw zakresów lub nazw kolumn wyznaczonej tabeli danych podczas definiowania tych argumentów zamiast wskazywania ich lub wpisywania odwołań do komórek.

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

  • 0 dla dokładnego dopasowania (domyślnie, 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 wartość_wyszukiwania

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

  • 1, aby wyszukiwać od początku do końca, to znaczy od góry do dołu (domyślnie, tak samo, jak gdy nie jest określony żaden argument search_mode) -1, aby wyszukiwać od początku do pierwszego, czyli od dołu do góry 2 dla wyszukiwania binarnego w porządku rosnącym -2 dla wyszukiwania binarnego w kolejności malejącej

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 roboczy z prostą tabelą danych sprzedaży 2019 uporządkowaną według kraju. Aby użyć XLOOKUP do zwrócenia całkowitej sprzedaży z tej tabeli w komórce E4 w oparciu o kraj wprowadzony w komórce D4 arkusza, wykonaj następujące czynności:

  1. Ustaw kursor komórki w komórce E4 arkusza roboczego Kliknij opcję Wyszukiwanie 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ć odwołanie do komórki w polu tekstowym Argument wartość wyszukiwania. Naciśnij klawisz Tab, aby zaznaczyć pole tekstowe Argument Lookup_array, a następnie kliknij komórkę A4 i przytrzymaj klawisz Shift podczas naciskania strzałki Ctrl w dół, aby wybrać zakres A4: A8 jako zakres wyszukiwania (ponieważ zakres A3: B8 jest zdefiniowany jako tabela danych Excel, Tabela 1 [Kraj] pojawia się w polu tekstowym zamiast zakresu A4: A8). Naciśnij klawisz Tab, aby zaznaczyć pole tekstowe Argument Return_array, a następnie kliknij komórkę B4 i przytrzymaj klawisz Shift podczas naciskania strzałki Ctrl w dół, aby wybrać B4: B8 jako zakres zawierający wartości do zwrócenia na podstawie wyników wyszukiwania (który pojawia się jako Tabela 1 [Całkowita sprzedaż] w polu tekstowym).

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

Tworzenie formuły XLOOKUP

Excel wprowadza formułę XLOOKUP do komórki E4 arkusza roboczego i zwraca w rezultacie 4900, ponieważ Kostaryka jest obecnie wprowadzana do komórki wyszukiwania D4 i jak widać w tabeli sprzedaży w 2019 r., Jest to rzeczywiście całkowita sprzedaż zrealizowana dla tego kraju.

Ponieważ XLOOKUP działa zarówno od prawej do lewej, jak i od lewej do prawej, możesz również użyć tej funkcji, aby zwrócić kraj z tej tabeli sprzedaży na podstawie konkretnej wielkości sprzedaży. Poniższy rysunek pokazuje, jak to zrobić. Tym razem tworzysz formułę XLOOKUP w komórce D4 i wyznaczasz wartość wpisaną w komórce E4 (w tym przypadku 11 000) jako argument wartość_wyszukiwania.

Ponadto wprowadź -1 jako argument match_mode, aby zastąpić domyślną funkcję dopasowania ścisłego funkcji, dzięki czemu program Excel zwróci kraj z dokładnym dopasowaniem do wartości sprzedaży wprowadzonej w komórce odnośnika E4 lub w kraju o następnej niższej całkowitej sprzedaży (Meksyk z W tym przypadku 10 000 USD, ponieważ w tabeli nie ma kraju z całkowitą sprzedażą w wysokości 11 000 USD). Bez wyznaczenia argumentu match_mode dla tej formuły program Excel zwróci wynik #NA, ponieważ w tej tabeli sprzedaży nie ma dokładnego dopasowania do 11 000 USD.

Formuła XLOOKUP w D4

Ponieważ funkcja XLOOKUP jest równie wygodna w wyszukiwaniu poziomym według kolumn, jak i w pionie według wiersza, można jej użyć do utworzenia formuły, która wykonuje dwukierunkowe wyszukiwanie (zastępując potrzebę utworzenia formuły, która łączy funkcje INDEKSU i PODAJNIKA 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.

zagnieżdżone funkcje XLOOKUP

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

= XLOOKUP (część_wyszukiwania, 3 A $ 3 USD: 6 A $, XLOOKUP (data_wyszukiwania, 2 B $ 2: 2 J $, 3 B $: 6 USD))

Ta formuła zaczyna się od zdefiniowania funkcji XLOOKUP, która pionowo wyszukuje według wiersza dokładnego dopasowania 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 . Zauważ jednak, że argument return_array dla tej oryginalnej funkcji WYSZUKAJ jest sam w sobie 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 dokonanego w komórce o nazwie data_pogląd (w tym przypadku komórka B11). Argument return_array dla tej drugiej zagnieżdżonej funkcji XLOOKUP to $ 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 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 z czerwca 19 (z wartościami: 438, 153, 306 i 779) jako wynik. Ten wynik z kolei staje się argumentem return_array dla oryginalnej funkcji XLOOKUP, która wykonuje wyszukiwanie pionowe po wierszu w celu dokładnego dopasowania do wpisu numeru części dokonanego w komórce B11 (o nazwie przegląd_partii). Ponieważ w tym przykładzie ta komórka part_lookup zawiera AB-102, formuła zwraca tylko wartość produkcyjną Jun-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 VLOOKUP i HLOOKUP, ale także dwustronne wyszukiwanie wartości wykonywane przez połączenie Funkcje INDEX i MATCH.

* Niestety, funkcja XLOOKUP nie jest wstecznie kompatybilna z wcześniejszymi wersjami Microsoft Excel, które obsługują tylko funkcje VLOOKUP i HLOOKUP lub kompatybilne z aktualnymi wersjami, które nie uwzględniają jej jeszcze jako jednej z 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, gdy otwierają arkusz roboczy.