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ć.
Spis treści:
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.
W tym przykładzie będzie działać następująca formuła: = XLOOKUP (A2, $ E $ 2: $ E $ 8, $ F $ 2: $ F $ 8)
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).
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.
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).
Można to osiągnąć następującą formułą: = XLOOKUP (A2, $ E $ 2: $ E $ 8, $ D $ 2: $ D 8 $)
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”)
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.
XLOOKUP ma opcjonalny piąty argument (pamiętaj, że domyślnie jest to dokładne dopasowanie) o nazwie tryb dopasowania.
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)
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.
Formuła natychmiast koryguje błąd. Nie ma problemu z „0” na dole zakresu.
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.
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 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).
Szósty argument funkcji XLOOKUP zapewnia cztery opcje. Jesteśmy zainteresowani skorzystaniem z opcji „Wyszukaj od ostatniego do pierwszego”.
Wypełniona formuła jest pokazana tutaj: = XLOOKUP (A2, $ E $ 2: $ E $ 9, $ F $ 2: $ F $ 9 ,,, – 1)
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.