Obliczanie wartości funkcji VLOOKUP i XLOOKUP: Wybierz odpowiednią formułę wyszukiwania

Użyj funkcji XLOOKUP do większości nowych wyszukiwań, jeśli skoroszyt będzie otwierany w LibreOffice 24.8 lub nowszym. Domyślnie jest to dopasowanie dokładne i pozwala na wybór oddzielnych zakresów wyszukiwania i wyników. Użyj funkcji WYSZUKAJ.PIONOWO, gdy potrzebujesz zgodności ze starszymi wersjami Calca lub gdy wyszukiwana wartość znajduje się w pierwszej kolumnie tabeli kompaktowej. Jeśli zachowasz funkcję WYSZUKAJ.PIONOWO, podaj jej czwarty argument jako 0 lub FAŁSZ, aby uzyskać dopasowanie dokładne.

Funkcje te rozwiązują ten sam podstawowy problem: znajdują klucz, taki jak kod produktu, i zwracają powiązane informacje z innej komórki. Istotne różnice dotyczą sposobu, w jaki każda formuła definiuje swoje zakresy, założeń przyjmowanych w przypadku pominięcia opcji oraz tego, czy plik musi działać w starszym oprogramowaniu.

Jaka jest praktyczna różnica?

Funkcja WYSZUKAJ.PIONOWO przeszukuje pierwszą kolumnę tabeli prostokątnej i zwraca wartość z ponumerowanej kolumny po jej prawej stronie. Funkcja WYSZUKAJ.PIONOWO oddziela zakres wyszukiwania od zakresu zwrotów, dzięki czemu wartości zwrotne mogą znajdować się po obu stronach kluczy. Funkcja WYSZUKAJ.PIONOWO akceptuje również niestandardową wartość dla brakującego dopasowania i może wyszukiwać w odwrotnej kolejności.

Załóżmy na przykład, że komórki A2:A5 zawierają identyfikatory produktów, a komórki B2:B5 zawierają ceny. Komórka F2 zawiera identyfikator do znalezienia. W programie Calc formuła dokładnego dopasowania pionowego wygląda następująco:

=VLOOKUP(F2;A2:B5;2;0)

Równoważna formuła XLOOKUP wygląda następująco:

=XLOOKUP(F2;A2:A5;B2:B5;"Not found")

Oba zwracają pasującą cenę. Calc zazwyczaj wyświetla średniki między argumentami funkcji, zgodnie z ustawieniami regionalnymi; jeśli formuły w instalacji używają przecinków, należy użyć separatora oczekiwanego przez Calc. W przykładach wykorzystano wyniki jednokomórkowe i jednowymiarowe zakresy wyszukiwania.

Kiedy warto wybrać XLOOKUP?

Wybierz funkcję XLOOKUP dla nowego skoroszytu, jeśli wiesz, że odbiorcy korzystają z LibreOffice 24.8 lub nowszego. Funkcja ta jest dostępna w LibreOffice 24.8, zgodnie z oficjalną stroną pomocy XLOOKUP . Domyślnym trybem dopasowania jest dopasowanie dokładne, więc brakujący klucz dokładny nie zwraca automatycznie wartości zbliżonej.

Wzorzec funkcji to =XLOOKUP(search_criterion;search_array;result_array;[if_not_found];[match_mode];[search_mode]). Pierwsze trzy argumenty określają klucz, zakres jednowymiarowy do przeszukania oraz zakres, z którego ma zostać zwrócony wynik. Czwarty argument, taki jak , "Not found"zastępuje standardowy wynik #N/A, gdy nie ma dopasowania.

Zwróć wartość z kolumny po lewej stronie

Funkcja WYSZUKAJ.PIONOWO nie może zwrócić wartości po lewej stronie pierwszej kolumny tabeli. Funkcja WYSZUKAJ.PIONOWO może, ponieważ zakresy wyszukiwania i wyników są niezależne. Na przykład, jeśli identyfikatory znajdują się w komórkach C2:C100, a nazwy działów w komórkach A2:A100, użyj:

=XLOOKUP(F2;C2:C100;A2:A100;"ID not found")

Taki układ pozwala również uniknąć niestabilnego indeksu kolumn. Jeśli ktoś wstawi kolumnę do tabeli VLOOKUP, zakodowany na stałe indeks może wskazywać na inne pole. Jawny zakres wyników funkcji XLOOKUP ułatwia inspekcję zamierzonej kolumny zwrotnej.

Zwróć najnowszy pasujący wpis

Gdy występują zduplikowane klucze i chcesz znaleźć ostatnie dopasowanie, funkcja XLOOKUP obsługuje wyszukiwanie odwrotne w swoim trybie wyszukiwania. W przypadku zakresu pionowego, ostatni argument -1przeszukuje od ostatniego do pierwszego:

=XLOOKUP(F2;A2:A100;B2:B100;"Not found";0;-1)

Zwraca wynik powiązany z ostatnim dokładnym wystąpieniem. Należy go używać tylko wtedy, gdy kolejność wierszy ma zdefiniowane znaczenie, na przykład najnowsze rekordy pojawiają się na końcu. Jeśli duplikaty mają być traktowane jako błąd, należy wyczyścić lub zwalidować dane zamiast domyślnie wybierać jedno wystąpienie.

Kiedy funkcja WYSZUKAJ.PIONOWO jest nadal lepszym wyborem?

Zachowaj funkcję WYSZUKAJ.PIONOWO, jeśli plik musi zostać otwarty w starszej wersji LibreOffice, która nie zawiera funkcji WYSZUKAJ.PIONOWO, lub gdy aplikacja docelowa może nie rozpoznać funkcji WYSZUKAJ.PIONOWO. Oficjalna dokumentacja Calca opisuje argumenty tabeli i indeksu funkcji WYSZUKAJ.PIONOWO oraz jej opcjonalny argument sortowania zakresu; zapoznaj się z oficjalnym dokumentem referencyjnym funkcji arkusza kalkulacyjnego .

W przypadku dokładnego dopasowania wzorzec to =VLOOKUP(key;table;column_index;0). Indeks liczy kolumny od lewej krawędzi wybranej tabeli: 1 oznacza pierwszą kolumnę, 2 oznacza drugą. Klucz wyszukiwania musi znajdować się w pierwszej kolumnie, a zwrócone pole musi znajdować się po jej prawej stronie.

Czwarty argument ma znaczenie. Jeśli go pominiesz lub ustawisz na PRAWDA, funkcja WYSZUKAJ.PIONOWO użyje dopasowania przybliżonego i oczekuje, że pierwsza kolumna będzie posortowana rosnąco. Takie zachowanie może być przydatne w przypadku zakresów, takich jak przedziały podatkowe, gdzie każdy wiersz oznacza dolną granicę. Jest to ryzykowne w przypadku identyfikatorów, nazw i niesortowanych list. W takich przypadkach należy podać 0 lub FAŁSZ.

Jak wypadają w porównaniu zachowania dopasowania i błędu?

• Dokładne dopasowanie: Funkcja XLOOKUP domyślnie używa dokładnego dopasowania. Funkcja VLOOKUP wymaga ustawienia czwartego argumentu na 0 lub FAŁSZ, aby zagwarantować dokładne wyszukiwanie.
• Brak klucza: Funkcja XLOOKUP może zwrócić wybrany komunikat lub wartość w czwartym argumencie. Funkcja VLOOKUP zazwyczaj zwraca #N/A; jeśli potrzebujesz niestandardowego komunikatu, zamknij go za pomocą funkcji IFNA.
• Dopasowanie przybliżone: Oba obsługują dopasowanie oparte na zakresie, ale dane i wybrany tryb muszą być zgodne z zamierzoną regułą. Posortuj dane zgodnie z wymaganiami i zweryfikuj przypadki graniczne.
• Kierunek wyszukiwania: Funkcja XLOOKUP może wyszukiwać od dołu z argumentem „tryb wyszukiwania”. Funkcja VLOOKUP zwraca pierwszy pasujący wiersz w standardowym wyszukiwaniu z góry na dół.

Aby zapewnić niestandardową odpowiedź na brakujący klucz za pomocą funkcji WYSZUKAJ.PIONOWO, użyj =IFNA(VLOOKUP(F2;A2:B5;2;0);"Not found"). Dzięki temu ustawienie dokładnego dopasowania pozostanie widoczne i zostanie obsłużony tylko błąd niedostępny. Unikaj umieszczania formuły w szerokiej procedurze obsługi błędów, chyba że chcesz również ukryć problemy niezwiązane z funkcją, takie jak nieprawidłowe odwołanie do zakresu.

Co należy sprawdzić przed udostępnieniem skoroszytu?

W pomocy LibreOffice podano, że funkcja XLOOKUP jest dostępna od wersji 24.8. Zauważono również, że funkcja XLOOKUP nie jest częścią standardu OpenFormula pakietu OpenDocument w wersji 1.3 i identyfikuje przestrzeń nazw funkcji firmy Microsoft w celu zapewnienia interoperacyjności. Oznacza to, że sam format pliku nie gwarantuje, że każda starsza lub zewnętrzna aplikacja arkusza kalkulacyjnego będzie w stanie obliczyć tę funkcję. W środowiskach z mieszanym oprogramowaniem należy przetestować skoroszyt w najstarszej aplikacji docelowej lub użyć sprawdzonej formuły, takiej jak funkcja WYSZUKAJ.PIONOWO z dokładnym dopasowaniem, tam gdzie pasuje jej układ.

Zwróć również uwagę na udokumentowany warunek formuły tablicowej: gdy tablica wyników funkcji XLOOKUP jest zakresem komórek, funkcja musi zostać wprowadzona jako formuła tablicowa. Powyższe proste przykłady zwracają pojedynczą, odpowiadającą wartość. Jeśli chcesz, aby formuła zwracała wiele komórek, zapoznaj się z pomocą dla danej wersji i sprawdź, jak aplikacja docelowa obsługuje wynik tablicowy.

Jak można zweryfikować wynik?

1. Przetestuj istniejący klucz i porównaj zwróconą wartość z wierszem źródłowym.
2. Przetestuj klucz, który nie istnieje. Sprawdź, czy funkcja XLOOKUP wyświetla wybrany kod zapasowy lub czy funkcja VLOOKUP zwraca oczekiwany komunikat #N/A lub IFNA.
3. W przypadku dopasowania dokładnego funkcji WYSZUKAJ.PIONOWO upewnij się, że ostatni argument to 0 lub FAŁSZ. Jeśli celowo używasz dopasowania przybliżonego, sprawdź kolejność sortowania oraz wartości tuż nad, na i pod granicą.
4. W przypadku funkcji XLOOKUP z kolumną zwracaną po lewej stronie lub wyszukiwania odwrotnego należy przetestować klucz ze znanym wynikiem i w przypadku występowania duplikatów potwierdzić, które wystąpienie powinno zostać zwrócone.
5. Zapisz i ponownie otwórz skoroszyt w najstarszym programie Calc lub arkuszu kalkulacyjnym, z którego korzystają Twoi odbiorcy. Prawidłowy wynik na Twoim komputerze nie dowodzi, że starsza wersja obsługuje tę samą funkcję.

W przypadku większości nowych arkuszy Calc funkcja XLOOKUP oferuje bardziej przejrzyste zakresy i bezpieczniejsze domyślne ustawienia dokładnego dopasowania. Użyj funkcji WYSZUKAJ.PIONOWO, gdy wymagana jest wsteczna kompatybilność lub gdy układ pierwszej kolumny po prawej stronie jest już odpowiedni, i określ jawnie tryb dopasowania.