Arkusze Google: jak korzystać z funkcji XLOOKUP?

Funkcja XLOOKUP w Arkuszach Google ułatwia i przyspiesza znajdowanie potrzebnych danych. WYSZUKAJ.PIONOWO nie ma takich samych ograniczeń jak WYSZUKAJ.PIONOWO i WYSZUKAJ.POZIOMO, co pozwala na wyszukiwanie w dowolnym kierunku.

Jeśli jesteś użytkownikiem programu Microsoft Excel, być może użyłeś funkcji XLOOKUP. Na szczęście działa to w ten sam sposób w Arkuszach Google. Niezależnie od tego, czy jesteś przyzwyczajony do tej funkcji w programie Excel, czy jesteś w niej zupełnie nowy, pokażemy Ci, jak używać XLOOKUP do wyszukiwania określonych wartości w zakresie komórek.

Informacje o XLOOKUP w Arkuszach Google

Za pomocą funkcji XLOOKUP i odpowiedniej formuły możesz wyszukiwać w jednym zakresie komórek i zwracać odpowiedni wynik z innego. Jest to przydatne w przypadku tabel zawierających dużo danych, gdzie używanie gałek ocznych jest czasochłonne.

Składnia funkcji: XLOOKUP(search_value, lookup_range, result_range, missing_value, match_mode, search_mode).wymagane są pierwsze trzy argumenty. Trzy pozostałe argumenty można wykorzystać do dostosowania wyszukiwania.

• search_value: wartość do wyszukania, która może być liczbą, tekstem lub odwołaniem do komórki. Tekst musi być ujęty w cudzysłów.
• Zakres_przeszukiwania: zakres komórek do wyszukania, search_valuektóry musi być pojedynczym wierszem lub kolumną.
• Zakres_wyniku: zakres komórek do wyszukania wyniku, search_valuektóry musi mieć taki sam rozmiar jak zakres_wyszukiwania.
• Missing_value: wartość zwracana w przypadku braku dopasowania dla search_value. Domyślnie formuła zwraca błąd #N/D.
• Match_mode: Jak znaleźć dopasowanie search_value. Wpisz 0 dla dokładnego dopasowania, 1 dla dokładnego dopasowania lub następnej wartości większej niż search_value, -1 dla dokładnego dopasowania lub następnej wartości mniejszej niż search_valuelub 2 dla dopasowania wieloznacznego. Wartość domyślna to 0.
• search_mode: Jak wyszukiwać lookup_range. Wprowadź 1, aby wyszukiwać od pierwszego do ostatniego wpisu, -1, aby wyszukiwać od ostatniego do pierwszego wpisu, 2, aby użyć wyszukiwania binarnego z wartościami w porządku rosnącym, lub -2, aby użyć wyszukiwania binarnego z wartościami w porządku malejącym zamówienie. Wartość domyślna to 1.

Jak korzystać z funkcji XLOOKUP w Arkuszach Google

Aby pokazać, jak działa funkcja, zaczniemy od prostego wyszukiwania z użyciem wymaganych argumentów, a następnie przejdziemy do większej liczby przykładów, w których użyto argumentów opcjonalnych.

Tutaj mamy arkusz zamówienia klienta, który zawiera dane kontaktowe i informacje o zamówieniu. W pierwszym przykładzie wykonamy proste wyszukiwanie numeru zamówienia, aby zwrócić nazwę klienta za pomocą tej formuły:

=XLOOKUP(123456,D2:D14,A2:A14)

Aby złamać formułę, 1234356 to search_valuenumer zamówienia lub D2:D14 to lookup_range, a A2:A14 to result_range. Jak widać, numer zamówienia 123456 należy do Marge Simpson.

Ponieważ XLOOKUP może działać zarówno od lewej do prawej, jak i od prawej do lewej, możemy zrobić odwrotnie. Tutaj poszukamy Marge Simpson w zakresie od A2 do A14, aby znaleźć jej numer zamówienia w zakresie od D2 do D14.

=XLOOKUP("Marge Simpson",A2:A14,D2:D14)

Notatka. W przeciwieństwie do funkcji WYSZUKAJ.PIONOWO, która działa w pionie, i funkcji WYSZUKAJ.POZIOMO, która działa w poziomie, funkcja QPR działa w obu kierunkach.

Brakująca wartość

W poniższym przykładzie włączymy „ZERO” dla missing_value. Tak więc, jeśli nasza wartość_wyszukiwania nie zostanie znaleziona, zobaczymy ZERO zamiast domyślnego #N/A.

=XLOOKUP("Homer Simpson",A2:A14,D2:D14,"ZERO")

Ponieważ nasze wyszukiwanie Homer Simpson nie znajduje się w zakresie od A2 do A14, nasz wynik to NULL.

Tryb meczu

Jako przykład, używając match_modeargumentu, użyjemy wartości search_value29 dla sumy z zakresu F2 do F14, aby znaleźć nazwę klienta z zakresu A2 do A14.

Uwzględnimy match_mode1 dla dokładnego dopasowania lub następnej wyższej wartości. missing_valueZauważ, że w formule nie ma argumentu.

=XLOOKUP(29,F2:F14,A2:A14,,1)

Możesz zobaczyć wynik Raja Koothrappali. Ponieważ nie ma dopasowania dla 29, formuła daje nam wynik dla następnej wyższej wartości, która wynosi 30.

Tryb szukania

Oto kolejny przykład użycia argumentów match_modez tą samą liczbą 29 w F2-F14. Ponownie szukamy nazwy klienta w zakresie od A2 do A14.search_mode search_value

Poszukamy dokładnego dopasowania lub następnej niższej wartości, przeszukując od ostatniego wpisu do pierwszego. Wpisujemy więc -1 dla match_modei -1 dla search_mode. Jak wyżej, missing_valuepominięto.

=XLOOKUP(29,F2:F14,A2:A14,,-1,-1)

Jak widać, wynikiem jest Michael Kelso. Ponieważ nie ma dopasowania dla 29, formuła daje nam następną niższą wartość, która wynosi 28. Chociaż Eric Foreman również pasuje do 28, szukaliśmy od ostatniego wpisu do pierwszego (od dołu do góry), więc Michael Kelso jest pierwszym wynikiem znaleziony.

Gdybyśmy szukali od pierwszego do ostatniego wpisu (od góry do dołu), używając search_mode1 zamiast -1, wynikiem byłby Eric Foreman.

Gdy masz arkusz kalkulacyjny pełen danych, wyszukiwanie wartości w celu znalezienia odpowiednich danych może trochę potrwać. Ale jeśli używasz XLOOKUP w Arkuszach Google, szybko znajdziesz to, czego potrzebujesz.

Aby uzyskać więcej informacji, zapoznaj się z najważniejszymi funkcjami Arkuszy Google, które możesz wypróbować.