Excel SVERWEIS – jak to działa

Excel oferuje funkcję SVerweis, która przeszukuje pierwszy kolumnę zakresu wyszukiwania.

Excel SVERWEIS – celowe stosowanie, czysta praca

Funkcja SVERWEIS w programie Excel należy do najczęściej używanych funkcji, gdy z obszernych tabel należy wyciągnąć pojedyncze wartości. Aby uzyskać niezawodny wynik, potrzeba czegoś więcej niż tylko właściwego wzoru – liczy się struktura danych i dokładne sprawdzenie źródeł błędów.

• Macierz musi zaczynać się po lewej stronie: funkcja WYSZUKAJ.PRAWIDŁOWĄ WYŚWIETLA tylko w pierwszej kolumnie wybranego zakresu. Jeśli na przykład numer klienta znajduje się w kolumnie D, obszar wyszukiwania również musi zaczynać się w tym miejscu – czyli nie „A:F”, ale „D:F”. Pierwsza kolumna jest zawsze używana jako podstawa porównania. Zignorowanie tej zasady spowoduje uzyskanie błędnych wyników lub brak wyników.

• Pewniejsza praca z obiektami tabel: Po sformatowaniu danych jako „tabelę” (skrót klawiszowy: Ctrl + T) program Excel automatycznie reaguje na późniejsze rozszerzenia. Formuła WYSZUKAJ.PRAWDA pozostaje ważna, nawet jeśli dodasz nowe wiersze. Formuła =WYSZUKAJ.PRAWDA(A2;Tabela1;3;FAŁSZ) uzyska wtedy dostęp do zaktualizowanych danych – idealne rozwiązanie dla rosnących list.

• FAŁSZ zamiast PRAWDA – precyzyjne sterowanie wyszukiwaniem: Bez dokładnego dopasowania ryzykujesz uzyskanie niedokładnych wyników. Czwarta pozycja formuły – tzw. odwołanie do zakresu – powinna prawie zawsze być ustawiona na FAŁSZ. Tylko wtedy Excel sprawdza dokładną zgodność. PRAWDA nadaje się tylko w przypadku posortowanych danych – co w codziennej pracy rzadko jest możliwe do zapewnienia.

• Sprawdzanie i ujednolicanie formatów danych: Liczba w formacie tekstowym lub liczba w formacie liczbowym – obie wyglądają tak samo, ale zachowują się inaczej. Jeśli funkcja WYSZUKAJ.PRAWIDŁOWE nie zwraca żadnego wyniku, mimo że wartość znajduje się na liście, prawie zawsze występuje konflikt formatów. Za pomocą funkcji =ISTTEXT(A2) lub =ISTZAHL(A2) można szybko zidentyfikować, gdzie leży problem. Narzędzia takie jak =GLÄTTEN() lub =WERT() automatycznie porządkują dane.

• Celowe stosowanie odwołań do komórek bezwzględnych i względnych: podczas kopiowania formuł odwołania do komórek również się dostosowują. W przypadku danych macierzowych może to mieć fatalne konsekwencje. Dlatego należy używać bezwzględnych odwołań do komórek z symbolem $ (np. $A$2:$D$100), aby niezawodnie przeciągać formuły od góry do dołu lub w poprzek tabeli – bez „przesuwania” zakresu.

• Eleganckie wychwytywanie błędów: Nikt nie chce widzieć „NV” w listach seryjnych lub pulpitach nawigacyjnych. Połącz funkcję WYSZUKAJ.PRAWIDŁOWĄ z funkcją JEŻEŚLI, aby wygenerować alternatywny wynik: =JEŻEŚLI(WYSZUKAJ.PRAWIDŁOWĄ(A2;Tabela1;3;FAŁSZ); „Nie znaleziono”)

• Nie jest to kwestia estetyki, ale funkcjonalności – na przykład podczas eksportu lub automatycznego przetwarzania danych.

• Ograniczenia funkcji WYSZUKAJ.POWYŻEJ: Gdy kolumny zostaną przesunięte lub zmieni się struktura tabeli, funkcja WYSZUKAJ.POWYŻEJ zwraca nieprawidłowe wyniki. Nie jest również możliwe wyszukiwanie wsteczne, czyli od prawej do lewej. W takich przypadkach zaleca się użycie funkcji XVERWEIS lub INDEX/VERGLEICH.

XVERWEIS, INDEX & Co. – alternatywy zapewniające większą kontrolę

Od czasu wprowadzenia Microsoft 365 i Excel 2021 dostępna jest bardziej niezawodna wersja funkcji XVERWEIS, która eliminuje wiele słabych punktów funkcji SVERWEIS. Również kombinacja INDEX/VERGLEICH jest uważana za elastyczną – zwłaszcza w przypadku zmiany wersji programu Excel lub wymagań.

• XVERWEIS: wyszukiwanie wstecz bez zbędnych kroków: Największa różnica: XVERWEIS pozwala również na zwracanie wyników z kolumn po lewej stronie wyszukiwanego hasła. Przykład: =XVERWEIS(B2;Tabela1[Nazwa];Tabela1[Numer klienta];„Brak wyników”). Dzięki temu można przeszukiwać również tabele, których struktura nie jest optymalna. Funkcja SVERWEIS nie zadziałałaby w tym przypadku.

• Indeks kolumn nie jest już potrzebny – mniej błędów: W funkcji XVERWEIS należy podać dwa konkretne zakresy komórek: zakres wyszukiwania i zakres zwracanych wartości. Nie trzeba pamiętać o numeracji, jak w przypadku funkcji SVERWEIS (3. kolumna, 4. kolumna itd.). Zmniejsza to liczbę błędów i zwiększa przejrzystość.

• Precyzyjne sterowanie komunikatami o błędach: Czwarty argument „jeśli_nie_znaleziono” zastępuje w funkcji XVERWEIS obejście za pomocą WENNFEHLER. Łatwa czytelność, mniej błędów w pisaniu, bezpośredni dostęp – funkcja reaguje sprawniej na puste komórki lub nieprawidłowe wartości.

• Sterowanie kierunkiem wyszukiwania i zachowaniem porównawczym: XVERWEIS oferuje dwa dodatkowe parametry: „Tryb porównywania” (0, -1, 1) i „Tryb wyszukiwania” (1 od góry, -1 od dołu). Dzięki temu można np. wyszukiwać konkretne przedziały cenowe lub duplikaty wpisów, co jest niemożliwe w przypadku funkcji SVERWEIS.

• XVERWEIS na urządzeniach mobilnych i w Internecie: W programie Microsoft Excel Mobile funkcja XVERWEIS działa zasadniczo poprawnie, ale złożone formuły dynamiczne są ograniczone. W programie Excel Online zarówno funkcja SVERWEIS, jak i XVERWEIS działają stabilnie, o ile formaty komórek są poprawne. Na urządzeniach mobilnych zaleca się stosowanie prostych struktur, ponieważ brakuje niektórych elementów sterujących.

• INDEX/VERGLEICH: Wszechstronne i niezależne od wersji: INDEX pobiera wartość, VERGLEICH wyszukuje odpowiednią lokalizację. Przykład formuły: =INDEX(D2:D100;VERGLEICH(A2;B2:B100;0))

W przeciwieństwie do funkcji WYSZUKAJ.POWYŻEJ, ta kombinacja nie przerywa działania po zmianie kolejności kolumn. Nadaje się do złożonych obliczeń lub gdy funkcja XWYSZUKAJ nie jest dostępna (np. w programie Excel 2016).
Wydajność przy dużych ilościach danych: osoby pracujące z tysiącami rekordów danych szybciej osiągną swój cel za pomocą funkcji INDEX/VERGLEICH lub XWYSZUKAJ niż za pomocą funkcji WYSZUKAJ.POWYŻEJ. Obie funkcje przetwarzają większe ilości danych w bardziej efektywny sposób – szczególnie w dynamicznych pulpitach nawigacyjnych lub podczas eksportu z systemów zewnętrznych.

Przykład praktyczny – SVERWEIS krok po kroku

Wiele osób korzysta z programu Excel na co dzień, ale funkcja WYSZUKAJ.POWIĄZANE często powoduje nieporozumienia – zwłaszcza jeśli nie pracuje się regularnie z formułami. Poniższy przykład pokazuje, jak za pomocą przejrzystej struktury i kilku kroków utworzyć działającą funkcję WYSZUKAJ.POWIĄZANE. Celem jest automatyczne pobranie odpowiedniego numeru telefonu z listy klientów.

• Struktura tabeli wyjściowej: Utwórz tabelę z trzema kolumnami: „Numer klienta”, „Nazwa” i „Numer telefonu”. Każdy wiersz zawiera dokładnie jeden rekord. Ważne: Kolumna z poszukiwaną wartością – w tym przypadku numer klienta – znajduje się po lewej stronie. Funkcja SVERWEIS porównuje zawsze tylko pierwszą kolumnę w obszarze wyszukiwania. Przykład: „1002 Langer 0157 9876543”

• Ustaw drugą tabelę dla zapytania: Po prawej stronie – lub w innym arkuszu – utwórz małą listę z dwiema kolumnami: „Numer klienta” i „Numer telefonu”. W kolumnie „Numer klienta” wprowadź wartość, która ma być wyszukana – na przykład „1002”. Druga kolumna pozostaje na razie pusta i zostanie automatycznie wypełniona później.

• Prawidłowe skonfigurowanie funkcji WYSZUKAJ.PRAW: Kliknij komórkę, w której ma pojawić się wynik – na przykład komórkę F2. Teraz wprowadź następującą formułę: =WYSZUKAJ.PRAW(E2;A2:C100;3;FAŁSZ)

• Ta formuła oznacza: wyszukaj wartość z komórki E2 (w tym przypadku „1002”) w pierwszym kolumnie zakresu od A2 do C100. Następnie zwróć wartość z trzeciej kolumny (C) tego samego wiersza. „FALSCH” na końcu zapewnia, że akceptowane są tylko dokładne dopasowania.

• Sprawdź formułę i wynik: Po naciśnięciu klawisza Enter w komórce F2 automatycznie pojawi się „0157 9876543” – numer telefonu poszukiwanego klienta. Zmiana wartości w komórce E2 (na przykład na „1003”) spowoduje automatyczną zmianę wyniku w komórce F2. Formuła dostosowuje się dynamicznie do wartości wprowadzonej.

• Unikaj typowych źródeł błędów: Sprawdź, czy w obszarze wyszukiwania liczby są rzeczywiście sformatowane jako liczby. Często przyczyną błędu „NV” jest to, że wartość w komórce E2 jest sformatowana jako liczba, ale w tabeli źródłowej jako tekst. Jest to prawie niewidoczne, ale ma konsekwencje techniczne. Można to sprawdzić za pomocą „=ISTTEXT(A2)”. W razie potrzeby „=WERT()” pomoże skorygować zawartość komórki.

• Ustal odniesienia do komórek, jeśli używanych jest kilka wierszy: Przeciągnij formułę w dół, aby wyszukać kolejnych klientów, a następnie ustal zakres za pomocą znaku dolara: =SVERWEIS(E2;$A$2:$C$100;3;FALSCH)

W ten sposób zakres wyszukiwania pozostaje niezmieniony, nawet jeśli skopiujesz formułę lub przeciągniesz ją w dół.
Przechwytywanie komunikatów o błędach, poprawa wyników

Aby zamiast „NV” wyświetlić zrozumiały komunikat, np. „Klient nie został znaleziony”, uzupełnij formułę w następujący sposób: =WENNFEHLER(SVERWEIS(E2;$A$2:$C$100;3;FALSCH); „Klient nie został znaleziony”)

Dzięki temu tabela wygląda bardziej przejrzyście, a użytkownik od razu widzi, że nie ma żadnych wyników.