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.
Dowiedz się, jak przeciągać obraz w programie Word, wybierać opcje zawijania tekstu, zachowywać go w akapicie lub mocować na stronie oraz rozwiązywać problemy z obrazami, których nie można przenieść.
Dowiedz się, jak wstawić pole wyboru w programie Excel za pomocą narzędzia w komórce, zliczyć zaznaczone elementy za pomocą formuł lub dodać powiązany formant formularza w starszych wersjach programu na komputery stacjonarne.
Generuj litery od A do Z, małe litery lub etykiety powyżej Z w programie Excel za pomocą formuły z uchwytem wypełniania, SEKWENCJI lub wielokrotnego użytku listy niestandardowej.
Dowiedz się, jak przekreślać wiersze w programie Excel w systemie Windows, na komputerze Mac i w przeglądarce, formatować część komórki i usuwać wiersze bez usuwania ich wartości.
Microsoft Access to aplikacja bazy danych systemu Windows do przechowywania powiązanych rekordów, tworzenia zapytań, tworzenia formularzy wprowadzania danych i generowania raportów. Dowiedz się, jak działa i kiedy się sprawdza.
Dowiedz się, jak zaimportować plik PST do programu Outlook 2010 lub wyeksportować pocztę, kontakty, kalendarz i zadania. Postępuj zgodnie z odpowiednimi menu i unikaj pominiętych folderów lub duplikatów.
Dowiedz się, jak dostosować wysokość i szerokość linii siatki w programie Word 2010, zmienić widoczną częstotliwość linii oraz ustawić odstępy między liniami siatki w pionie i w poziomie.
Za pomocą dodatku LiveWeb możesz umieścić witrynę internetową na slajdzie programu PowerPoint, ustawić jej rozmiar i sposób odświeżania oraz sprawdzić zgodność przed prezentacją.
Odzyskaj zapomniane hasło PST programu Outlook, korzystając z bieżących instrukcji Kernela, obejmujących m.in. identyfikację właściwego pliku, wykonanie kopii zapasowej i sprawdzenie ograniczeń narzędzia.
Dodaj święta krajowe w nowym Outlooku, Outlooku w przeglądarce lub klasycznej wersji Outlooka. Dowiedz się, jak unikać duplikatów wydarzeń i sprawdzać, czy nie brakuje dat z lat 2026–2027.
