LibreOffice Calc oferuje więcej niż jeden sposób wyszukiwania wartości w tabeli i zwracania powiązanych danych. Przez lata powszechnie stosowaną opcją była funkcja WYSZUKAJ.PIONOWO . Obecnie Calc zawiera również funkcję XLOOKUP , nowszą funkcję wyszukiwania, która eliminuje kilka ograniczeń funkcji WYSZUKAJ.PIONOWO i jest zazwyczaj łatwiejsza w obsłudze.
Ten przewodnik zaczyna się od podstawowego modelu mentalnego, a następnie omawia praktyczne formuły, które można skopiować do małego arkusza kalkulacyjnego. Wyjaśnia również, gdzie funkcja INDEKS/PODAJ.POZYCJĘ nadal pasuje, co może pójść nie tak i która funkcja ma sens w nowym skoroszycie.
Wyszukiwanie przeszukuje jeden zakres w poszukiwaniu klucza, takiego jak identyfikator produktu, a następnie zwraca powiązaną wartość, na przykład cenę produktu. Kluczem wyszukiwania może być tekst, liczba, data lub odwołanie do komórki.
W aktualnej Pomocy LibreOffice funkcja XLOOKUP jest udokumentowana jako nowoczesny zamiennik funkcji WYSZUKAJ.PIONOWO, WYSZUKAJ.POZIOMO i WYSZUKAJ. Funkcja XLOOKUP jest dostępna od wersji LibreOffice 24.8. Aktualna Pomoc LibreOffice 26.8 dokumentuje dopasowanie dokładne i przybliżone, dopasowanie z symbolami wieloznacznymi i wyrażeniami regularnymi, wyszukiwanie odwrotne oraz wyszukiwanie binarne. Zobacz oficjalną dokumentację XLOOKUP LibreOffice .
Jeden szczegół, który często zaskakuje osoby przechodzące z programu Microsoft Excel: przykłady formuł w LibreOffice często używają średników między argumentami. Na przykład: =XLOOKUP(F2;A2:A6;D2:D6). Calc może dostosować separatory do ustawień regionalnych, więc postępuj zgodnie z separatorami akceptowanymi przez instalację.
| Zdolność | WYSZUKAJ.PIONOWO | XLOOKUP |
|---|---|---|
| Dokładne dopasowanie | Tak, ale zazwyczaj określasz 0/FAŁSZ | Tak; dokładne dopasowanie jest ustawieniem domyślnym |
| Zwróć wartość po lewej stronie kolumny wyszukiwania | Nie, nie bezpośrednio | Tak |
| Oddzielne zakresy wyszukiwania i zwracania | Nie; używa jednej tablicy tabel i indeksu kolumn | Tak |
| Wbudowany wynik, gdy nie znaleziono dopasowania | Brak dedykowanego argumentu | Tak |
| Wyszukaj od ostatniego do pierwszego elementu | Brak bezpośredniego argumentu trybu wyszukiwania | Tak |
| Przybliżone dopasowanie | Tak | Tak, z wyraźnymi trybami dopasowania |
| Dostępne w LibreOffice w wersjach starszych niż 24.8 | Tak | NIE |
Utwórz kolumny od A do D z nagłówkami: ID produktu , Nazwa produktu , Kategoria i Cena . Dodaj kilka identyfikatorów, takich jak P001 do P005. Wpisz identyfikator, którego szukasz, w F2 i umieść formułę w G2.
Formuły w Calcu rozpoczynają się znakiem równości. Można je wpisać bezpośrednio do komórki lub na pasku formuł; oficjalny przewodnik po wprowadzaniu formuł LibreOffice opisuje obie metody.
Załóżmy, że P004 znajduje się w kolumnie F2 i chcesz uzyskać cenę z kolumny D. Wprowadź:
=VLOOKUP(F2;$A$2:$D$6;4;0)
Argumenty oznaczają: wyszukaj wartość w komórce F2, przeszukaj pierwszą kolumnę komórek A2:D6, zwróć wartość z czwartej kolumny tej tabeli i wymuś dokładne dopasowanie. Znaki dolara tworzą odwołania bezwzględne , które utrzymują stały zakres wyszukiwania po skopiowaniu formuły do innych komórek.
LibreOffice dokumentuje funkcję WYSZUKAJ.PIONOWO jako wyszukiwanie pionowe, w którym szukana wartość musi znajdować się w pierwszej kolumnie tablicy, a wartość zwracana pochodzi z kolumny po jej prawej stronie. Ostrzega również, że jeśli ostatni argument zostanie pominięty lub będzie miał wartość PRAWDA, funkcja zakłada posortowane dane i może zwrócić przybliżone dopasowanie. Przed skorzystaniem z przybliżonego działania funkcji WYSZUKAJ.PIONOWO zapoznaj się z oficjalnym dokumentem informacyjnym dotyczącym funkcji arkusza kalkulacyjnego LibreOffice .
Teraz przeorganizuj tabelę tak, aby nazwa produktu znajdowała się w kolumnie A, a identyfikator produktu w kolumnie C. W przypadku funkcji WYSZUKAJ.PIONOWO taki układ jest niewygodny, ponieważ funkcja przeszukuje tylko pierwszą kolumnę tablicy tabeli i zwraca wartości po prawej stronie.
Funkcja XLOOKUP oddziela tablicę wyszukiwania od tablicy wyników . Aby znaleźć P003 w kolumnie C i zwrócić nazwę produktu z kolumny A, użyj:
=XLOOKUP(F2;$C$2:$C$6;$A$2:$A$6;"Not found")
Czwartym argumentem jest wartość zwracana w przypadku braku dopasowania. Dzięki temu formuła jest łatwiejsza do odczytania niż umieszczanie wyszukiwania w osobnej funkcji obsługi błędów w tym typowym przypadku.
Jeśli użytkownik wprowadzi nieistniejący identyfikator, zwykłe wyszukiwanie dokładne zazwyczaj zwraca wynik #N/A. W funkcji XLOOKUP umieść przyjazny wynik w czwartym argumencie:
=XLOOKUP(F2;$A$2:$A$6;$D$2:$D$6;"Not found";0)
Tryb 0dopasowania wyraźnie żąda dokładnego dopasowania, chociaż dokładne dopasowanie jest już domyślne dla funkcji XLOOKUP. Wpisanie go może być jednak przydatne, gdy chcesz, aby cel formuły był oczywisty dla osoby przeglądającej arkusz.
Nie ukrywaj automatycznie każdego błędu. Jeśli identyfikator powinien zawsze istnieć, #N/Amoże to być cenne, ponieważ ujawnia błędne lub niekompletne dane źródłowe. Używaj wygodnego rozwiązania awaryjnego, gdy spodziewane są braki kluczy, a nie gdy wskazują one na problem z jakością danych.
Czasami klucz pojawia się więcej niż raz. Na przykład dziennik transakcji może zawierać kilka wierszy dla klienta C001. Funkcja XLOOKUP ma argument „Tryb wyszukiwania” . Jest to wartość -1wyszukiwania od ostatniego elementu do pierwszego.
Aby zwrócić kwotę z ostatniego wiersza zawierającego identyfikator klienta w F2, należy użyć:
=XLOOKUP(F2;$A$2:$A$8;$D$2:$D$8;"Not found";0;-1)
Oficjalny podręcznik XLOOKUP definiuje tryb wyszukiwania 1jako pierwszy do ostatniego i -1ostatni do pierwszego. Zawiera również tryby wyszukiwania binarnego 2i -2, ale wymagają one poprawnie posortowanych danych. Użycie wyszukiwania binarnego w niesortowanych danych może zwrócić nieprawidłowe wyniki.
Zanim funkcja XLOOKUP pojawiła się w Calc 24.8, powszechną i elastyczną alternatywą były funkcje INDEKS i PODAJ.POZYCJĘ . PODAJ.POZYCJĘ znajduje względną pozycję klucza; INDEKS zwraca wartość w tej pozycji. Na przykład:
=INDEX($D$2:$D$6;MATCH(F2;$A$2:$A$6;0))
Funkcja ta jest przydatna, gdy potrzebna jest zgodność ze starszymi instalacjami LibreOffice lub gdy istniejący skoroszyt korzysta już konsekwentnie z funkcji INDEKS/PODAJ. Oficjalny podręcznik funkcji arkusza kalkulacyjnego dokumentuje zarówno funkcję INDEKS, jak i PODAJ .
Calc oferuje również funkcję XMATCH od wersji LibreOffice 24.8. XMATCH dodaje tryby wyszukiwania i dopasowywania podobne do XLOOKUP, w tym wyszukiwanie wsteczne. Zobacz oficjalną dokumentację XMATCH dla LibreOffice .
W przypadku funkcji WYSZUKAJ.PIONOWO pominięcie ostatniego argumentu powoduje zachowanie sortowanego zakresu. W przypadku identyfikatorów produktów, numerów pracowników, kodów SKU i innych identyfikatorów dyskretnych należy użyć parametru 0lub FAŁSZ, aby uzyskać dokładne dopasowanie, chyba że dopasowanie przybliżone jest celowo uwzględnione w projekcie.
Indeks kolumny funkcji WYSZUKAJ.PIONOWO jest liczony od pierwszej kolumny tablicy tabeli, a nie od liter kolumn arkusza kalkulacyjnego. W komórkach A2:D6 kolumna D ma indeks 4. Jeśli później wstawisz kolumnę do tej tabeli, zakodowany na stałe indeks może już nie odnosić się do zamierzonego pola.
Tablica wyszukiwania i tablica wyników powinny być wyrównane wiersz po wierszu lub kolumna po kolumnie. Zakres wyszukiwania taki jak A2:A100 w połączeniu z D2:D90 jest błędem projektowym, ponieważ odpowiadające sobie pozycje nie obejmują tych samych rekordów.
Tryb wyszukiwania XLOOKUP 2 wymaga kolejności rosnącej, a tryb -2 – malejącej. Dokumentacja LibreOffice wyraźnie ostrzega, że niesortowane dane mogą generować nieprawidłowe wyniki. Jeśli nie kontrolujesz sortowania, użyj standardowych trybów wyszukiwania do przodu lub do tyłu.
Calc obsługuje dopasowywanie symboli wieloznacznych i wyrażeń regularnych, ale w funkcji XLOOKUP są to oddzielne tryby dopasowania. Tryb symboli wieloznacznych używa znaków takich jak *i ?; tryb wyrażeń regularnych korzysta z mechanizmu wyrażeń regularnych Calca. Wybierz tryb świadomie, zamiast zakładać, że wzorzec zostanie zinterpretowany automatycznie.
W przypadku nowego arkusza kalkulacyjnego działającego w LibreOffice 24.8 lub nowszym, należy zacząć od funkcji XLOOKUP. Oddzielne zakresy wyszukiwania i wyników są łatwiejsze do odczytania, domyślne jest dopasowanie dokładne, zwroty po lewej stronie są proste, a wbudowane opcje wyszukiwania „nie znaleziono” i wyszukiwania odwrotnego redukują potrzebę stosowania obejść formuł.
Użyj funkcji WYSZUKAJ.PIONOWO, gdy utrzymujesz istniejący skoroszyt zbudowany na jej podstawie, udostępniasz go środowiskom, w których obsługa funkcji WYSZUKAJ.PIONOWO jest niepewna, lub pracujesz z prostą, stabilną tabelą, której ograniczenia nie mają znaczenia. Użyj funkcji INDEKS/PODAJ.POZYCJĘ, gdy ważna jest zgodność ze starszym pakietem LibreOffice lub skoroszyt już stosuje ten wzorzec.
Najważniejszym nawykiem nie jest zapamiętywanie każdego argumentu. Zamiast tego, przed napisaniem formuły, określ cztery rzeczy: jakiej wartości szukam, gdzie szukam, jaki zakres powinien dostarczyć odpowiedź i co się stanie, jeśli klucz będzie brakujący? Gdy te kwestie będą jasne, wybór między funkcją WYSZUKAJ.PIONOWO, WYSZUKAJ.X i INDEKS/PODAJ.POZYCJĘ stanie się znacznie łatwiejszy.
Utwórz zaproszenie na spotkanie w programie Outlook, dodaj wymaganych lub opcjonalnych uczestników, wybierz godzinę, dodaj szczegóły dotyczące usługi Teams, ustaw cykliczność i sprawdź odpowiedzi w nowej lub klasycznej wersji programu Outlook.
Użyj ustawień sprawdzania poprawności danych programu Excel, aby określić maksymalną, minimalną, dokładną długość lub zakres znaków, a następnie dodaj przejrzyste monity i alerty o błędach.
Napraw błąd programu Outlook „Nie możemy zaplanować spotkania”, sprawdzając obsługę konta, odświeżając logowanie do usługi Teams i naprawiając klasyczny dodatek do programu Outlook.
Zmień menu pakietu Office 365, ustawienia pisowni i gramatyki lub wprowadzanie tekstu z klawiatury w aplikacjach komputerowych i usłudze Microsoft 365 dla sieci Web.
Dowiedz się, jak automatycznie przekazywać dalej wszystkie lub wybrane wiadomości e-mail w programie Outlook 365, korzystając z ustawień przekazywania dalej lub reguł, a także jak zatrzymać przekazywanie dalej i naprawić zablokowane przekazywanie wiadomości zewnętrznych.
Użyj Edytora Microsoft w programie Word dla usługi Microsoft 365, aby sprawdzić pisownię, gramatykę, przejrzystość i zwięzłość, przejrzeć sugestie i dostosować ustawienia sprawdzania poprawności.
Dowiedz się, jak dodać kanał RSS do programu Outlook 365 w klasycznej wersji programu Outlook dla systemu Windows, znaleźć prawidłowy adres URL kanału, zarządzać subskrypcjami i rozwiązywać typowe problemy z kanałami RSS.
Możesz przejść bezpośrednio do dowolnej strony w programie Word dla Microsoft 365 za pomocą kombinacji klawiszy Ctrl+G lub F5, względnych numerów stron lub panelu nawigacji, korzystając ze skrótów klawiaturowych systemu Windows i Mac.
Dowiedz się, jak dodawać kontakty do aplikacji Outlook w sieci Web, nowej aplikacji Outlook, klasycznej aplikacji Outlook i aplikacji mobilnej, a także jak działa synchronizacja kontaktów i importowanie plików CSV.
Zmień kolor strony w programie Word, dodaj obraz tła, usuń go i włącz drukowanie tła w systemie Windows lub Mac, korzystając z przejrzystych i aktualnych instrukcji.
