Jak korzystać z funkcji XWYSZUKAJ w programie Excel

Co to jest funkcja Xlookup w programie Excel ? Jak korzystać z Xlookup w Excelu ? Przekonajmy się z LuckyTemplates.com!

Jeśli regularnie korzystasz z programu Excel, prawdopodobnie znasz funkcje wyszukiwania. Jeśli nie wiesz, wystarczy po prostu zrozumieć to jako funkcję Excela służącą do wyszukiwania określonych danych lub wartości w wierszach lub kolumnach w arkuszu kalkulacyjnym.

Funkcja ta jest szczególnie przydatna, gdy trzeba przetworzyć dużą ilość danych w arkuszu kalkulacyjnym Excel. W programie Excel dostępnych jest kilka typów funkcji WYSZUKAJ. XLOOKUP jest jednym z nich. Czym więc jest funkcja XLOOKUP, czym jest formuła XLOOKUP i jak z niej korzystać? Przekonajmy się z LuckyTemplates.com!

Rzeczy, które warto wiedzieć o funkcji XWYSZUKAJ w programie Excel

• Co to jest XLOOKUP?
  • Jak uzyskać dostęp do funkcji XLOOKUP
  • Składnia funkcji XLOOKUP
• Zalety i wady funkcji XLOOKUP w Excelu
  • Zalety funkcji XLOOKUP
  • Wady funkcji XLOOKUP
• Jak korzystać z funkcji XLOOKUP
  • Nie ma dodatkowych numerów indeksów kolumn
  • Domyślne dopasowanie dokładne
  • XLOOKUP można znaleźć po lewej stronie
  • Użyj XLOOKUP, aby wyszukać zakresy
  • XLOOKUP zastępuje funkcję WYSZUKAJ.POZIOMO
  • XLOOKUP można przeglądać od dołu do góry

Co to jest funkcja XWYSZUKAJ w programie Excel?

Zasadniczo XLOOKUP zastępuje stare funkcje, takie jak HLOOKUP, VLOOKUP i LOOKUP. Obsługuje wyszukiwanie danych zarówno w pionie, jak i w poziomie.

Innymi słowy funkcja XLOOKUP w programie Excel pomaga szybko znaleźć wartości w podanym zbiorze danych, zarówno w poziomie, jak i w pionie. Następnie zwraca odpowiednią wartość według różnych wierszy i kolumn.

Istnieje wiele sposobów maksymalnego wykorzystania funkcji XLOOKUP.

Jak uzyskać dostęp do funkcji XLOOKUP

Obecnie funkcja XLOOKUP jest dostępna tylko dla użytkowników programu Insiders. Każdy, kto dołączy do programu Insider, będzie mógł uzyskać dostęp do najnowszych funkcji programu Excel, gdy tylko staną się dostępne. Microsoft wkrótce udostępni tę funkcję wszystkim użytkownikom Office 365. Niestety funkcja XLOOKUP nie jest dostępna dla użytkowników korzystających z wersji Office 2010, 2013, 2016 i 2019.

Jeśli nie korzystasz z pakietu Microsoft 365 na swoim komputerze, może być konieczne uaktualnienie do Microsoft 365, aby uzyskać dostęp do funkcji XLOOKUP. A jeśli korzystasz z Microsoft 365, zobaczysz tę opcję w Excelu. Funkcja XLOOKUP jest również dostępna w usłudze Office 365 Online.

Składnia funkcji XLOOKUP

Składnia funkcji XLOOKUP jest podobna do składni funkcji WYSZUKAJ.PIONOWO i WYSZUKAJ.POZIOMO. Jeśli korzystałeś z nich wcześniej, wygodniejsze będzie korzystanie z XLOOKUP. Oto składnia funkcji XWYSZUKAJ w programie Excel:

= XLOOKUP ( wartość_wyszukiwana , tablica_wyszukiwana , tablica_powrotna , [jeśli_nieznaleziona], [tryb_dopasowania], [tryb_wyszukiwania])

Funkcja XLOOKUP obsługuje do 6 argumentów i oto ich wartości.

1. szukana wartość (wymagane): Wartość, którą chcesz znaleźć.

2. lookup_array (wymagane): Tablica, w której chcesz szukać szukanej wartości.

3. return_array (wymagane): Tablica, z której chcesz pobrać i zwrócić wartości, gdy zostanie znaleziona szukana wartość.

4. [if_not_found] (opcjonalnie): Jeśli nie znaleziono dopasowania, jest to zwracana wartość.

5. [tryb_dopasowania] (opcjonalnie): Ten argument pozwala określić rodzaj dopasowania, którego szukasz. Istnieją różne wartości, aby to określić:

• 0 — szuka dokładnego dopasowania i wartość musi dokładnie odpowiadać wartości w tablicy wyszukiwania. Ta wartość jest również ustawiana jako domyślna, jeśli nie jest wymieniona.
• -1 - Wyszukuje dokładne dopasowanie i po znalezieniu powraca do następnej mniejszej wartości.
• 1 — szuka dokładnego dopasowania i po znalezieniu zwraca następną większą wartość.
• 2 - Wykonaj częściowe dopasowanie przy użyciu symboli wieloznacznych, gdzie *, ? i ~ ma specjalne znaczenie.

6. [tryb_wyszukiwania] (opcjonalnie): Używany do określenia trybu wyszukiwania XLOOKUP w tablicy wyszukiwania. Istnieją różne wartości, aby określić to samo:

• 1 – Przeprowadź wyszukiwanie zaczynając od pierwszego elementu. Ta wartość jest ustawiana jako domyślna, jeśli nic nie jest określone.
• -1 - Wykonuje wyszukiwanie wsteczne, zaczynając od ostatniego elementu.
• 2 – Wykonaj wyszukiwanie binarne w tablicy lookup_array , w której dane muszą zostać posortowane w kolejności rosnącej. Jeśli dane nie zostaną posortowane, może to spowodować błędy lub błędne wyniki.
• -2 - Wykonaj wyszukiwanie binarne w tablicy wyszukiwania , w której dane muszą zostać posortowane w kolejności malejącej. Jeśli dane nie zostaną posortowane, może to spowodować błędy lub błędne wyniki.

Zalety i wady funkcji XLOOKUP w Excelu

XLOOKUP jest nadal korzystną funkcją w porównaniu z VLOOKUP i INDEX/ATCH. Ale ma to również swoje wady.

Zalety funkcji XLOOKUP

• Szczęka pracuje zarówno w pionie, jak i w poziomie.
• Potrzebne są tylko 3 argumenty zamiast 4, jak w przypadku WYSZUKAJ.PIONOWO i DOPASUJ INDEKS.
• Zawsze domyślnie jest to dokładne dopasowanie.
• Częściowe wyszukiwania można przeprowadzić przy użyciu symboli wieloznacznych.
• Wyszukiwania można przeprowadzać w kolejności malejącej.
• Użyj jednej funkcji zamiast dwóch w INDEX MATCH.

Wady funkcji XLOOKUP

• Opcjonalne argumenty mogą wydawać się skomplikowane dla początkujących.
• Wybranie dwóch zakresów i zbyt duża liczba komórek w arkuszu kalkulacyjnym może zająć więcej czasu.
• Zwraca błąd podczas wyszukiwania i zwracania tablicy o różnej długości.
• Zapamiętaj zarówno zakres wyszukiwania, jak i zakres zwracany.

Korzyści ze stosowania funkcji XLOOKUP Excel

• Funkcja XLOOKUP sprawia, że ​​najczęściej używane formuły w programie Excel są prostsze i mniej podatne na błędy . Wystarczy wpisać =XLOOKUP (dane do znalezienia, lista, lista wyników), a otrzymasz odpowiedź lub #N/A, jeśli wartość nie zostanie znaleziona.
• Domyślne wyniki wyszukiwania są dokładne . Jedną z wad WYSZUKAJ.PIONOWO jest to, że musisz podać FALSE jako ostatni parametr, aby uzyskać poprawny wynik. XLOOKUP rozwiązuje ten problem, domyślnie wykonując dokładne dopasowanie. Jeśli chcesz, możesz użyć parametru trybu dopasowania, aby zmienić zachowanie wyszukiwania.
• Czwarty parametr obsługuje przypadek, gdy wartość nie została znaleziona . W większości sytuacji biznesowych będziesz zmuszony „zawinąć” formułę wyszukiwania za pomocą IFERROR lub IFNA, aby wyeliminować błędy. Funkcja XLOOKUP udostępnia czwarty parametr, dzięki czemu można poznać pożądany wynik domyślny, jeśli wartość nie zostanie znaleziona.
• XLOOKUP udostępnia parametry wyboru umożliwiające znalezienie specjalnych sytuacji . Możesz wyszukiwać od góry lub od dołu, według znaków specjalnych i szybszych opcji, aby znaleźć przefiltrowane dane.

Jak korzystać z funkcji XLOOKUP

Spójrz na poniższy przykład, aby zrozumieć, jak działa XLOOKUP. W tym przykładzie musimy zwrócić część z kolumny F dla każdego identyfikatora w kolumnie A.

To jest przykład szukania dokładnych wyników, ale funkcja XLOOKUP wymaga tylko trzech typów informacji.

Poniższy obraz przedstawia funkcję XLOOKUP z 5 argumentami, ale do uzyskania poprawnych wyników potrzebne są pierwsze trzy argumenty. Skupmy się więc na nich:

• Lookup_value : Wartość wyszukiwania
• Lookup_array : zakres wyszukiwania
• Return_array : Zakres zawierający wartość do zwrócenia

W tym przykładzie użyjemy wzoru:

=XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

Przyjrzyjmy się teraz niektórym zaletom funkcji XLOOKUP w porównaniu z funkcją VLOOKUP.

Nie ma dodatkowych numerów indeksów kolumn

Trzecim argumentem funkcji WYSZUKAJ.PIONOWO jest określenie liczby kolumn informacji, które mają zostać zwrócone z zakresu tabeli. Nie stanowi to już problemu w przypadku XLOOKUP, ponieważ funkcja pozwala użytkownikowi wybrać zakres zwracanych wartości. (w tym przykładzie kolumna F).

I nie zapominaj, że XLOOKUP może zobaczyć pozostałe dane wybranej komórki, w przeciwieństwie do VLOOKUP.

Nie napotkasz również błędów formuł podczas dodawania nowych kolumn. Jeśli zdarzy się to w arkuszu kalkulacyjnym, zakres zwrotu zostanie dostosowany automatycznie.

Domyślne dopasowanie dokładne

Korzystając z funkcji WYSZUKAJ.PIONOWO, użytkownik musi w razie potrzeby określić dokładne dopasowanie. Ale w przypadku XLOOKUP domyślnym ustawieniem jest dokładne dopasowanie. Zmniejsza to czwarty argument i zapewnia mniej błędów nowym użytkownikom. Krótko mówiąc, XLOOKUP wymaga mniej pytań niż VLOOKUP i jest przyjazny dla użytkownika.

XLOOKUP można znaleźć po lewej stronie

Możliwość wyboru zakresów wyszukiwania zapewnia większą elastyczność funkcji WYSZUKAJ.PIONOWO. Dzięki XLOOKUP kolejność kolumn w tabeli nie ma znaczenia.

WYSZUKAJ.PIONOWO jest ograniczone poprzez przeszukiwanie 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).

Skorzystaj z poniższej formuły:

=XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

Użyj XLOOKUP, aby wyszukać zakresy

Chociaż nie jest to tak powszechne jak dopasowanie dokładne, możesz użyć formuły wyszukiwania, aby znaleźć wartość w zakresie. Przykładowo chcemy zwrócić rabat w zależności od wydanej kwoty.

Tym razem nie szukamy konkretnej wartości i musimy wiedzieć w jakim przedziale mieszczą się wartości w kolumnie B w kolumnie E, aby określić rabat.

XLOOKUP ma opcjonalny czwarty argument (pamiętaj, że domyślnie jest to dopasowanie bezwzględne) o nazwie match_mode.

Możesz zobaczyć, że XLOOKUP może znaleźć przybliżone dopasowania lepiej niż VLOOKUP.

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

Formuła w tym przykładzie zwraca najbliższą wartość mniejszą niż wartość wyszukiwana, jeśli nie zostanie znalezione dokładne dopasowanie:

=XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,-1)

Wystąpił jednak błąd w komórce C7, należało zwrócić rabat 0%, ponieważ wydało tylko 64, co nie spełniło kryteriów otrzymania rabatu.

Kolejną zaletą funkcji XLOOKUP jest to, że nie wymaga ona zakresu wyszukiwania w kolejności rosnącej, jak w przypadku funkcji WYSZUKAJ.PIONOWO.

Wprowadź nowy wiersz na dole tabeli przeglądowej, a następnie otwórz formułę. Rozszerz zakres zastosowania, klikając i przeciągając rogi.

Formuła natychmiast koryguje błąd. Nie ma problemu z posiadaniem 0 na końcu tabeli zakresów.

XLOOKUP zastępuje funkcję WYSZUKAJ.POZIOMO

Jak wspomniano, funkcja XLOOKUP może również zastąpić funkcję HLOOKUP. Jedna funkcja może zastąpić dwie funkcje, co może być lepszego?

Funkcja WYSZUKAJ.POZIOMO wyszukuje w poziomie i służy do wyszukiwania według wierszy.

Ta funkcja nie jest tak znana jak VLOOKUP, ale jest przydatna w przypadkach, gdy tytuł znajduje się w kolumnie A, a dane znajdują się w wierszach 4 i 5, jak w przykładzie poniżej.

Funkcja XLOOKUP umożliwia wyszukiwanie w obu kierunkach, w dół kolumn i wzdłuż wierszy.

W tym przykładzie użyto formuły do ​​zwrócenia wartości sprzedaży powiązanej z nazwą w kolumnie A2. Sprawdza nazwę w wierszu 4 i zwraca wartość z wiersza 5:

=XLOOKUP(A2,B4:E4,B5:E5)

XLOOKUP można przeglądać od dołu do góry

Zwykle trzeba przeszukać listę od góry do dołu, aby znaleźć pierwsze wystąpienie wartości. XLOOKUP ma piąty argument o nazwie search_mode. Argument ten umożliwia przekierowanie wyszukiwania tak, aby rozpoczynało się od dołu listy w celu znalezienia ostatniej wartości.

W poniższym przykładzie musimy znaleźć poziom zapasów każdego produktu w kolumnie A.

Tabela przeglądowa jest ułożona w kolejności dat. Chcemy zwrócić stan magazynowy z ostatniego sprawdzenia (ostatnie wystąpienie Product ID).

Piąty argument funkcji XLOOKUP udostępnia cztery opcje. Tutaj skorzystamy z opcji Szukaj od ostatniego do pierwszego .

Formuła użyta w tym przykładzie:

=XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,-1)

We wzorze tym pominięto czwarty argument. Jest to opcjonalne i chcemy, aby ustawienie domyślne było dokładne.

Funkcja XLOOKUP jest oczekiwanym następcą funkcji WYSZUKAJ.PIONOWO i WYSZUKAJ.POZIOMO. W tym artykule wykorzystano szereg przykładów, aby zademonstrować zalety funkcji XLOOKUP.

XLOOKUP to duże ulepszenie, jeśli chodzi o funkcje wyszukiwania. Jedynym ograniczeniem funkcji XLOOKUP jest to, że nie jest ona kompatybilna wstecz. Jeśli udostępniasz arkusze kalkulacyjne starszym wersjom pakietu Microsoft Office Suite, mogą pojawić się błędy.

Dostęp do funkcji XLOOKUP można jednak uzyskać z usługi Office 365 Online. XLOOKUP jest wciąż w fazie rozwoju, więc przyjęcie go przez użytkowników Excela zajmie trochę czasu.

Życzę powodzenia!

Różnica między XLOOKUP i VLOOKUP

Dlaczego XLOOKUP jest lepszy od VLOOKUP?

Chociaż WYSZUKAJ.PIONOWO ma swoje własne zastosowania, ma bardziej ograniczone funkcje niż WYSZUKAJ.PIONOWO. To sprawia, że ​​XLOOKUP jest najpopularniejszym wyborem w przypadku konieczności przetwarzania dużych ilości danych. Dzięki temu działa szybciej i ma mniej błędów.

• XLOOKUP nie ogranicza zakresu danych : XLOOKUP jest bardziej elastyczny niż VLOOKUP. WYSZUKAJ.PIONOWO może wyszukiwać wartości znajdujące się tylko w skrajnej lewej kolumnie tabeli i zwracać wartości z odpowiedniej kolumny po prawej stronie. Tymczasem XLOOKUP wymaga jedynie prostszych kroków i może zwracać wartości dla dowolnej kolumny w dowolnym kierunku.
• Domyślnym wynikiem XLOOKUP jest najdokładniejsza wartość .
• Domyślnie funkcja WYSZUKAJ.PIONOWO w programie Excel zawsze znajduje wyniki przybliżone . Jeśli jednak potrzebujesz dokładnych wyników, powinieneś wpisać FAŁSZ w argumencie wyszukiwania_zakresu.
• Funkcja XLOOKUP wyszukuje wartość wyszukiwania w określonym zakresie i zawsze domyślnie podaje dokładną wartość. Jeśli nie możesz go znaleźć, możesz edytować argument, aby znaleźć przybliżone dopasowanie, które jest mniejsze lub większe niż wartość wyszukiwania.

Inne powody:

• XLOOKUP może obsłużyć wstawianie i usuwanie kolumn.
• XLOOKUP implementuje wyszukiwania pionowe i poziome.
• XLOOKUP zapewnia inteligentniejsze przybliżone dopasowania.
• Funkcja XLOOKUP wymaga mniejszej liczby odwołań do komórek.