Wyszukaj wartość w jednej kolumnie i zwróć pasującą wartość z innej.
Wyszukiwanie · 5 krótkich ćwiczeń w żywym arkuszu: sam wpisujesz każdą formułę i odpowiadaj na pytania, które każą przewidywać wyniki lub wykrywać błędy. Każda odpowiedź jest sprawdzana od razu.
Co będziesz ćwiczyć
Wyszukiwanie wartości funkcją XLOOKUP
Zwracanie nazwy na podstawie kodu
Wyszukiwanie kodu na podstawie nazwy
Pokazywanie komunikatu, gdy nic nie zostanie znalezione
X.WYSZUKAJ (Angielski: XLOOKUP)(co szukać; gdzie szukać; co zwrócić) znajduje wartość w jednej kolumnie i zwraca wartość z tego samego wiersza z innej kolumny.
X.WYSZUKAJ (Angielski: XLOOKUP) może szukać w dowolnej kolumnie i zwracać wartość z dowolnej innej, nawet z kolumny znajdującej się po lewej stronie.
Jeśli nic nie pasuje, X.WYSZUKAJ (Angielski: XLOOKUP) zwraca #N/A. Jego 4. argument określa, co pokazać zamiast tego.
X.WYSZUKAJ (Angielski: XLOOKUP) znajduje wartość w jednym zakresie i zwraca pasującą wartość z innego zakresu. To nowoczesny następca WYSZUKAJ.PIONOWO (Angielski: VLOOKUP) i WYSZUKAJ.POZIOMO (Angielski: HLOOKUP): szuka w dowolnym kierunku, domyślnie dopasowuje dokładnie i ma gotową odpowiedź na „nie znaleziono”.
Podstawowa postać
Wyobraź sobie listę pracowników. A2:A5 zawiera identyfikatory, B2:B5 imiona, a C2:C5 działy. Pierwsze trzy argumenty to: co znaleźć, gdzie szukać i co zwrócić.
=X.WYSZUKAJ(F1;A2:A5;B2:B5)
Szuka identyfikatora wpisanego w F1 i zwraca imię z tej samej pozycji.
Ponieważ zakres wyszukiwania i zakres zwracany są osobne, klucz nie musi być skrajną lewą kolumną, a nic się nie psuje, gdy ktoś wstawi kolumnę pomiędzy.
Obsługa brakującej wartości
Czwarty argument to wartość zwracana, gdy nic nie pasuje, więc nie potrzebujesz dodatkowego JEŻELI.BŁĄD (Angielski: IFERROR).
=X.WYSZUKAJ(F1;A2:A5;B2:B5;"No such ID")
Zastępowany jest tylko przypadek „nie znaleziono”. Inne błędy, takie jak zakresy o różnych rozmiarach, nadal się pojawią, i tak właśnie ma być.
Tryby dopasowania i wyszukiwania
Piąty argument ustawia tryb dopasowania. 0 oznacza dokładne, czyli domyślne. -1 oznacza dokładne albo następną mniejszą wartość, a 1 oznacza dokładne albo następną większą, co przydaje się przy przedziałach, takich jak stawki podatkowe lub rabaty. 2 włącza symbole wieloznaczne.
=X.WYSZUKAJ(F1;A2:A5;B2:B5;"Below range";-1)
Przy progach w A2:A5 zwraca wpis z najwyższego progu, który nie przekracza F1. Progi nie muszą być posortowane.
Szósty argument ustawia kierunek wyszukiwania. 1 zaczyna od góry, co jest domyślne. -1 zaczyna od dołu, więc dostajesz ostatnie dopasowanie zamiast pierwszego, na przykład najnowsze zamówienie klienta.
=X.WYSZUKAJ(F1;A2:A5;B2:B5;"Not found";0;-1)
Dopasowanie dokładne, szukanie od dołu do góry.
Wyszukiwanie dwukierunkowe i symbole wieloznaczne
Ponieważ X.WYSZUKAJ (Angielski: XLOOKUP) zwraca zakres, można go zagnieździć. Załóżmy, że C1:E1 zawiera nagłówki Q1, Q2 i Q3, a C2:E5 sprzedaż czterech osób. Wewnętrzne wyszukiwanie wybiera kolumnę, a zewnętrzne wiersz:
=X.WYSZUKAJ(F1;A2:A5;X.WYSZUKAJ(F2;C1:E1;C2:E5))
F1 zawiera identyfikator, F2 kwartał.
Przy trybie dopasowania 2 wartość wyszukiwania może zawierać symbole wieloznaczne, więc =X.WYSZUKAJ("Ma*";B2:B5;A2:A5;"";2) zwraca identyfikator pierwszego imienia zaczynającego się od Ma.
Zwracanie więcej niż jednej wartości
Jeśli zakres zwracany ma kilka kolumn, X.WYSZUKAJ (Angielski: XLOOKUP) zwraca cały wiersz, a wynik rozlewa się na komórki obok formuły.
=X.WYSZUKAJ(F1;A2:A5;B2:C5)
Zwraca imię i dział danej osoby za jednym razem.
X.WYSZUKAJ (Angielski: XLOOKUP) wymaga Excela 2021, Excela 2024 lub Microsoft 365. Nie ma go w Excelu 2019 ani wcześniejszych, więc udostępniony plik może nadal potrzebować WYSZUKAJ.PIONOWO (Angielski: VLOOKUP).
Zakres wyszukiwania i zakres zwracany muszą mieć tyle samo wierszy albo, gdy szukasz w poziomie, tyle samo kolumn.
Dopasowanie tekstu ignoruje wielkość liter. Przypadkowe spacje nadal się liczą, więc uporządkuj zaimportowane dane funkcją USUŃ.ZBĘDNE.ODSTĘPY (Angielski: TRIM).