Udostępnij teraz w social media:

Nowy XLOOKUP programu Excel zastąpi WYSZUKAJ.PIONOWO, stanowiąc potężny zamiennik jednej z najpopularniejszych funkcji programu Excel. Ta nowa funkcja rozwiązuje niektóre ograniczenia funkcji WYSZUKAJ.PIONOWO i ma dodatkową funkcjonalność. Oto, co musisz wiedzieć.

Co to jest XLOOKUP?

Nowa funkcja XLOOKUP rozwiązuje niektóre z największych ograniczeń funkcji WYSZUKAJ.PIONOWO. Ponadto zastępuje również WYSZUKAJ.POZIOMO. Na przykład XLOOKUP może wyglądać w lewo, domyślnie określa dokładne dopasowanie i umożliwia określenie zakresu komórek zamiast numeru kolumny. WYSZUKAJ.PIONOWO nie jest tak łatwe w użyciu ani tak wszechstronne. Pokażemy Ci, jak to wszystko działa.

W tej chwili XLOOKUP jest dostępny tylko dla użytkowników programu Insiders. Każdy może dołączyć do programu Insiders aby uzyskać dostęp do najnowszych funkcji programu Excel, gdy tylko staną się dostępne. Firma Microsoft wkrótce zacznie udostępniać ją wszystkim użytkownikom Office 365.

Jak korzystać z funkcji XLOOKUP

Przejdźmy od razu do przykładu XLOOKUP w akcji. Weź przykładowe dane poniżej. Chcemy zwrócić dział z kolumny F dla każdego identyfikatora w kolumnie A.

To jest klasyczny przykład wyszukiwania w dopasowaniu ścisłym. Funkcja XLOOKUP wymaga tylko trzech informacji.

Poniższy obrazek przedstawia XLOOKUP z sześcioma argumentami, ale tylko pierwsze trzy są potrzebne do dokładnego dopasowania. Skoncentrujmy się więc na nich:

Lookup_value: czego szukasz.
Lookup_array: Gdzie szukać.
Return_array: zakres zawierający wartość do zwrócenia.

Informacje wymagane przez funkcję XLOOKUP

W tym przykładzie będzie działać następująca formuła: = XLOOKUP (A2, $ E $ 2: $ E $ 8, $ F $ 2: $ F $ 8)

XLOOKUP dla dokładnego dopasowania

Przyjrzyjmy się teraz kilku zaletom XLOOKUP w porównaniu z VLOOKUP.

Nie więcej numeru indeksu kolumny

Niesławnym trzecim argumentem funkcji WYSZUKAJ.PIONOWO było określenie numeru kolumny informacji do zwrócenia z tablicy tabeli. Nie stanowi to już problemu, ponieważ XLOOKUP umożliwia wybranie zakresu do powrotu (w tym przykładzie kolumna F).

Sprawdź -   Jak zaplanować usuwanie plików w Dropbox

Argument numeru indeksu kolumny funkcji WYSZUKAJ.PIONOWO

I nie zapominaj, że XLOOKUP może wyświetlać dane po lewej stronie wybranej komórki, w przeciwieństwie do WYSZUKAJ.PIONOWO. Więcej na ten temat poniżej.

Nie masz również problemu z nieprawidłową formułą po wstawieniu nowych kolumn. Gdyby tak się stało w Twoim arkuszu kalkulacyjnym, zakres zwrotów dostosowałby się automatycznie.

Wstawiona kolumna nie przerywa XLOOKUP

Dokładne dopasowanie jest ustawieniem domyślnym

Podczas uczenia się funkcji WYSZUKAJ.PIONOWO zawsze było mylące, dlaczego konieczne było określenie dokładnego dopasowania.

Na szczęście XLOOKUP domyślnie ustawia dokładne dopasowanie – jest to znacznie częstszy powód używania formuły wyszukiwania). Zmniejsza to potrzebę odpowiedzi na ten piąty argument i zapewnia mniejszą liczbę błędów popełnianych przez nowych użytkowników formuły.

Krótko mówiąc, XLOOKUP zadaje mniej pytań niż WYSZUKAJ.PIONOWO, jest bardziej przyjazny dla użytkownika, a także trwalszy.

XLOOKUP może patrzeć w lewo

Możliwość wyboru zakresu wyszukiwania sprawia, że ​​XLOOKUP jest bardziej wszechstronny niż WYSZUKAJ.PIONOWO. W przypadku XLOOKUP kolejność kolumn tabeli nie ma znaczenia.

Funkcja WYSZUKAJ.PIONOWO została ograniczona przez przeszukanie skrajnej lewej kolumny tabeli, a następnie powrót z określonej liczby kolumn po prawej stronie.

W poniższym przykładzie musimy wyszukać identyfikator (kolumna E) i zwrócić imię i nazwisko osoby (kolumna D).

Przykładowe dane dla formuły wyszukiwania po lewej stronie

Można to osiągnąć następującą formułą: = XLOOKUP (A2, $ E $ 2: $ E $ 8, $ D $ 2: $ D 8 $)

Funkcja XLOOKUP zwraca wartość po lewej stronie

Co zrobić, jeśli nie znaleziono

Użytkownicy funkcji wyszukiwania dobrze znają komunikat o błędzie # N / D, który wita ich, gdy funkcja WYSZUKAJ.PIONOWO lub funkcja PODAJ.POZYCJ. Nie mogą znaleźć tego, czego potrzebują. Często jest to logiczne.

Dlatego użytkownicy szybko badają, jak ukryć ten błąd, ponieważ nie jest on poprawny ani przydatny. Oczywiście są na to sposoby.

XLOOKUP ma własny wbudowany argument „jeśli nie znaleziono” do obsługi takich błędów. Zobaczmy to w akcji z poprzednim przykładem, ale z błędnie wpisanym identyfikatorem.

Poniższa formuła wyświetli tekst „Nieprawidłowy identyfikator” zamiast komunikatu o błędzie: = XLOOKUP (A2, $ E $ 2: $ E $ 8, $ D $ 2: $ D $ 8, „Niepoprawny identyfikator”)

Sprawdź -   Jak działa rozpoznawanie twarzy?

Alternatywny tekst, jeśli nie zostanie znaleziony w XLOOKUP

Używanie XLOOKUP do wyszukiwania zakresu

Chociaż nie jest to tak powszechne, jak dopasowanie ścisłe, bardzo skutecznym zastosowaniem formuły wyszukiwania jest szukanie wartości w zakresach. Weźmy następujący przykład. Chcemy zwrócić rabat w zależności od wydanej kwoty.

Tym razem nie szukamy konkretnej wartości. Musimy wiedzieć, gdzie wartości w kolumnie B mieszczą się w zakresach w kolumnie E. To określi uzyskany rabat.

Dane tabeli do wyszukiwania zakresu

XLOOKUP ma opcjonalny piąty argument (pamiętaj, że domyślnie jest to dokładne dopasowanie) o nazwie tryb dopasowania.

Argument trybu dopasowania dla wyszukiwania zakresu

Jak widać, XLOOKUP ma większe możliwości z przybliżonymi dopasowaniami niż funkcja WYSZUKAJ.PIONOWO.

Istnieje możliwość znalezienia najbliższego dopasowania mniejszego niż (-1) lub najbliższego większego niż (1) szukanej wartości. Istnieje również opcja użycia symboli wieloznacznych (2), takich jak? albo *. To ustawienie nie jest domyślnie włączone, tak jak w przypadku WYSZUKAJ.PIONOWO.

Formuła w tym przykładzie zwraca najbliższą mniejszą niż szukana wartość, jeśli dokładne dopasowanie nie zostanie znalezione: = XLOOKUP (B2, $ E $ 3: $ E $ 7, $ F $ 3: $ F $ 7 ,, – 1)

Wyszukiwanie zakresu z błędem

Jednak w komórce C7 występuje błąd, w którym zwracany jest błąd # N / D (argument „jeśli nie znaleziono” nie został użyty). Powinno to zwrócić 0% rabatu, ponieważ wydanie 64 nie spełnia kryteriów żadnego rabatu.

Kolejną zaletą funkcji XLOOKUP jest to, że nie wymaga ona, aby zakres wyszukiwania był w porządku rosnącym, tak jak funkcja WYSZUKAJ.PIONOWO.

Wprowadź nowy wiersz u dołu tabeli przeglądowej, a następnie otwórz formułę. Rozszerz używany zakres, klikając i przeciągając rogi.

Napraw błąd, rozszerzając używany zakres

Formuła natychmiast koryguje błąd. Nie ma problemu z „0” na dole zakresu.

Błąd naprawiony przez rozwinięcie tabeli przeglądowej

Osobiście nadal sortowałbym tabelę według kolumny odnośnika. Posiadanie „0” na dole doprowadzałoby mnie do szału. Ale fakt, że formuła się nie zepsuł, jest genialny.

XLOOKUP Zastępuje również funkcję HLOOKUP

Jak wspomniano, funkcja XLOOKUP jest również tutaj, aby zastąpić HLOOKUP. Jedna funkcja zamiast dwóch. Świetny!

Funkcja WYSZUKAJ.POZIOMO jest wyszukiwaniem poziomym używanym do wyszukiwania wzdłuż wierszy.

Sprawdź -   Co to jest LiDAR i jak będzie działać na iPhonie?

Nie jest tak dobrze znany, jak jego siostrzane WYSZUKAJ.PIONOWO, ale przydatne w przykładach takich jak poniżej, gdzie nagłówki znajdują się w kolumnie A, a dane znajdują się w wierszach 4 i 5.

XLOOKUP może patrzeć w obu kierunkach – w dół kolumny, a także wzdłuż rzędów. Nie potrzebujemy już dwóch różnych funkcji.

W tym przykładzie formuła służy do zwracania wartości sprzedaży związanej z nazwą w komórce A2. Przeszukuje wiersz 4, aby znaleźć nazwę, i zwraca wartość z wiersza 5: = XLOOKUP (A2, B4: E4, B5: E5)

XLOOKUP jako zamiennik funkcji HLOOKUP

XLOOKUP może patrzeć od dołu do góry

Zazwyczaj trzeba przeszukać listę, aby znaleźć pierwsze (często jedyne) wystąpienie wartości. XLOOKUP ma szósty argument o nazwie tryb wyszukiwania. Dzięki temu możemy przełączyć wyszukiwanie, aby zaczęło się od dołu i przeszukać listę, aby zamiast tego znaleźć ostatnie wystąpienie wartości.

W poniższym przykładzie chcielibyśmy znaleźć stan zapasów dla każdego produktu w kolumnie A.

Tabela przeglądowa jest uporządkowana według dat i istnieje wiele sprawdzeń zapasów dla każdego produktu. Chcemy zwrócić stan magazynowy z ostatniego razu, gdy był sprawdzany (ostatnie wystąpienie ID produktu).

Przykładowe dane do wyszukiwania wstecznego

Szósty argument funkcji XLOOKUP zapewnia cztery opcje. Jesteśmy zainteresowani skorzystaniem z opcji „Wyszukaj od ostatniego do pierwszego”.

Opcje trybu wyszukiwania z XLOOKUP

Wypełniona formuła jest pokazana tutaj: = XLOOKUP (A2, $ E $ 2: $ E $ 9, $ F $ 2: $ F $ 9 ,,, – 1)

XLOOKUP patrząc od dołu do góry listę wartości

W tej formule czwarty i piąty argument zostały zignorowane. Jest to opcjonalne, a chcieliśmy ustawić domyślne dopasowanie ścisłe.

Podsumowanie

Funkcja XLOOKUP to z niecierpliwością oczekiwany następca do funkcji WYSZUKAJ.PIONOWO i WYSZUKAJ.PIONOWO.

W tym artykule wykorzystano różne przykłady, aby zademonstrować zalety XLOOKUP. Jednym z nich jest to, że XLOOKUP może być używany w różnych arkuszach, skoroszytach, a także w tabelach. Przykłady w artykule były proste, aby pomóc w zrozumieniu.

Spowodowany tablice dynamiczne wprowadzane do programu Excel wkrótce może również zwrócić zakres wartości. Jest to zdecydowanie coś, co warto zbadać dalej.

Dni WYSZUKAJ.PIONOWO są numerowane. XLOOKUP jest tutaj i wkrótce będzie de facto formułą wyszukiwania.