Jak korzystać z XLookup w programie Excel

Praca z dużymi zbiorami danych jest zawsze trudna. Excel to jedno z najpotężniejszych narzędzi do manipulowania, formatowania i analizowania dużych zestawów informacji.

Istnieje kilka dobrze znanych funkcji ułatwiających wyszukiwanie określonych dopasowań w zbiorze danych, takich jak VLookup.

Ale funkcje takie jak VLookup miały pewne ograniczenia, więc Microsoft niedawno wprowadził funkcję XLookup do Excela. Zapewnia bardziej niezawodną metodę znajdowania określonych wartości w zbiorze danych.

Wiedza o tym, jak skutecznie go używać, może drastycznie skrócić czas poświęcony na analizę danych w Excelu.

Co robi XLookup w Excelu?

Korzyści z XLookup w Excelu

XLookup Wymagane argumenty

XLookup Argumenty opcjonalne

Jak korzystać z XLookup w programie Excel

Najlepsze praktyki dotyczące XLookup w programie Excel

Co robi XLookup w Excelu?

Podstawową funkcjonalnością XLookup jest możliwość wyszukiwania określonych wartości w zakresie lub tablicy danych. Po uruchomieniu funkcji zwracana jest odpowiednia wartość z innego zakresu lub tablicy.

Ostatecznie jest to narzędzie do wyszukiwania zestawów danych programu Excel.

W zależności od twoich argumentów, XLookup pokaże ci pierwszą lub ostatnią wartość w instancjach z więcej niż jednym pasującym wynikiem.

Inne funkcje wyszukiwania, takie jak VLookup (wyszukiwanie pionowe) lub HLookup (wyszukiwanie poziome), mają ograniczenia, takie jak wyszukiwanie tylko od prawej do lewej. Użytkownicy muszą albo zmienić kolejność danych, albo znaleźć złożone obejścia, aby temu zaradzić.

XLookup jest o wiele bardziej elastycznym rozwiązaniem, pozwalającym uzyskać częściowe dopasowania, użyć więcej niż jednego kryterium wyszukiwania i użyć zagnieżdżonych zapytań.

Sprawdź samouczek XLookup Excel, aby zobaczyć go w akcji:

Korzyści z XLookup w Excelu

Główną zaletą XLookup w programie Excel jest oszczędność czasu, jaką zapewnia. Wynik wyszukiwania można uzyskać na przykład bez manipulowania pozycjonowaniem danych.

Podobnie jak inne formuły wyszukiwania programu Excel, oszczędza ogromną ilość czasu, który w innym przypadku musiałby zostać poświęcony na ręczne przewijanie wierszy i wierszy danych.

Jeśli chodzi o porównania z innymi funkcjami wyszukiwania, elastyczność XLookup jest kluczowa. Na przykład możesz użyć symboli wieloznacznych do wyszukiwania częściowych dopasowań.

Pomaga to ogólnie zmniejszyć liczbę błędów, ponieważ można go użyć do znalezienia danych, które mogą być błędnie napisane lub wprowadzone niepoprawnie.

Argumenty dla XLookup są również prostsze niż VLookup, co czyni go szybszym i łatwiejszym w użyciu. Na przykład XLookup domyślnie odpowiada dokładnemu dopasowaniu, podczas gdy musiałbyś to określić w argumencie VLookup.

Ogólna funkcjonalność XLookup jest lepsza, ponieważ może zwracać wiele wyników jednocześnie. Jednym z przykładów tego przypadku użycia byłoby wyszukiwanie pięciu pierwszych wartości w określonym zbiorze danych.

XLookup Wymagane argumenty

Twoja funkcja XLookup wymaga trzech argumentów:

• Lookup_value: To jest wartość, której szukasz w tablicy.
• Lookup_array: Jest to zakres komórek, w których ma być wyświetlana zwracana wartość.
• Return_array: Jest to zakres komórek, w których funkcja ma szukać wartości.

Tak więc prosta wersja XLookup wyglądałaby tak:

=XLOOKUP(przeszukiwana_wartość; szukana_tablica; zwracana_tablica)

XLookup Argumenty opcjonalne

Funkcja XLookup ma również kilka opcjonalnych argumentów, których można użyć w przypadku bardziej złożonych scenariuszy lub w celu zawężenia wyszukiwania:

• Match_mode: Określa typ dopasowania do użycia, na przykład dopasowanie ścisłe lub dopasowanie wieloznaczne w celu uzyskania dopasowań częściowych.
• Search_mode: określa, czy funkcja ma wyszukiwać od lewej do prawej, czy odwrotnie.
• If_not_found: Ten argument mówi funkcji, jaka wartość powinna zostać zwrócona, jeśli w ogóle nie ma dopasowania.

Z dołączonymi opcjonalnymi argumentami funkcja XLookup wygląda następująco:

=XLOOKUP(przeszukiwana_wartość, szukana_tablica, zwracana_tablica, [jeśli_nie_znaleziono], [tryb_dopasowania], [tryb_wyszukiwania])

Jak korzystać z XLookup w programie Excel

1. Otwórz Excel i arkusz danych, na którym chcesz użyć funkcji XLookup.

2. Wybierz komórkę, w której chcesz umieścić formułę XLookup Excel.

3. Wpisz „=” w tej komórce, a następnie wpisz „XLookup”. Kliknij opcję „XLookup”, która pojawi się na liście rozwijanej.

4. Nawias otwierający zostanie wygenerowany automatycznie. Po nawiasie otwierającym wprowadź wymagane argumenty we właściwej kolejności: szukana_wartość, szukana_tablica i zwracana_tablica.

Oznacza to wybranie zakresu komórek dla każdego argumentu i umieszczenie przecinka przed przejściem do następnego argumentu.

5. Jeśli używasz opcjonalnych argumentów, wprowadź je po wymaganych argumentach.

6. Gdy argumenty XLookup są kompletne, dodaj nawias zamykający przed naciśnięciem klawisza Enter.

7. Wyniki Twojego XLookup powinny zostać wyświetlone w komórce, w której wprowadziłeś funkcję.

W tym przykładzie Excel XLookup użyliśmy XLookup, aby znaleźć ocenę dla konkretnego ucznia. Tak więc lookup_value było imieniem i nazwiskiem ucznia („Ruben Pugh”).

Lookup_array była listą nazwisk uczniów w kolumnie A. Wreszcie lookup_return była listą ocen uczniów w kolumnie C. Dzięki tej formule funkcja zwróciła wartość oceny studenta: C-.

Ale jak używać XLookup z opcjonalnymi argumentami?

Korzystając z tego samego zestawu danych co powyżej, powiedzmy, że chcemy znaleźć wskaźnik obecności ucznia o nazwisku „Smith”.

W tym przypadku mówimy programowi Excel, aby zwracał komunikat „Nie znaleziono”, jeśli nie może pobrać wartości, wskazując, że studenta „Smith” nie ma na tej liście:

Załóżmy teraz, że chcemy ustalić, czy któryś z uczniów miał frekwencję na poziomie 70%. Ale chcemy również poznać następny najbliższy wskaźnik frekwencji, jeśli w zbiorze danych nie ma 70%.

Użyjemy wartości „1” w argumencie match_mode, aby znaleźć następny największy element, jeśli 70 nie istnieje:

Ponieważ żaden uczeń nie ma 70% frekwencji, XLookup zwrócił „75”, kolejną największą wartość.

Najlepsze praktyki dotyczące XLookup w programie Excel

Użyj odniesień opisowych

Lepiej jest używać opisowych odwołań do używanych komórek niż ogólnych zakresów, takich jak A1:A12.

Kiedy przychodzi czas na dostosowanie formuły (co często robisz za pomocą funkcji takiej jak XLookup), odniesienia opisowe ułatwiają zrozumienie, do czego pierwotnie używałeś formuły.

Jest to również przydatne, jeśli przekazujesz arkusz kalkulacyjny nowemu użytkownikowi, który musi szybko zrozumieć odwołania do formuł.

Używaj dopasowania ścisłego tam, gdzie to możliwe

Użycie domyślnego dopasowania dokładnego w formule (w przeciwieństwie do symboli wieloznacznych) pomaga upewnić się, że zwrot nie zawiera niezamierzonych wartości.

Znaki wieloznaczne są bardziej przydatne do identyfikowania dopasowań częściowych, ale dopasowanie dokładne to najlepszy sposób, aby zapewnić prawidłowe działanie formuły.

Zachowaj ten sam rozmiar zakresów argumentów

Łącząc swoje argumenty, upewnij się, że używasz tej samej liczby komórek dla tablicy_powrotu i tablicy wyszukiwania. Jeśli nie, pojawi się błąd, a Excel zwróci tylko #ARG w komórce.

Przetestuj swoją formułę

Naprawienie problemów za pomocą prostego XLookup nie jest trudne. Ale jeśli używasz funkcji zagnieżdżonych lub XLookup w połączeniu z innymi formułami, upewnij się, że testujesz wszystko po drodze.

Jeśli tego nie zrobisz i pojawi się błąd, cofnięcie się i zidentyfikowanie miejsca, w którym funkcja działa nieprawidłowo, może być trudne.

Rozpoczęcie pracy

Jako nowy i ulepszony sposób korzystania z funkcji wyszukiwania w programie Excel, XLookup przewyższa klasyczne VLookup na wiele sposobów.

Chociaż podstawy funkcji są łatwe do zrozumienia, korzystanie z XLookup w bardziej złożony sposób może wymagać pewnej praktyki. Ale mając trochę praktycznych danych i scenariuszy testowych do pracy, opanujesz XLookup w mgnieniu oka.