Excel SVERWEIS – jak na to

Excel nabízí funkci SVerweis, která prohledává první sloupec vyhledávací oblasti.

Excel SVERWEIS – cílené použití, čistá práce

Funkce SVERWEIS v Excelu patří k nejčastěji používaným funkcím, pokud je třeba z rozsáhlých tabulek vytáhnout jednotlivé hodnoty. Aby to spolehlivě fungovalo, nestačí jen správný vzorec – důležité je také uspořádání dat a přesný pohled na zdroje chyb.

• Matice musí začínat vlevo: Funkce SVERWEIS prohledává pouze první sloupec vybrané oblasti. Pokud máte například číslo zákazníka ve sloupci D, musíte vyhledávací oblast také zahájit tam – tedy ne „A:F“, ale „D:F“. První sloupec se vždy používá jako srovnávací základna. Pokud to ignorujete, získáte nesprávné výsledky nebo žádné výsledky.

• Stabilnější práce s tabulkovými objekty: Jakmile formátujete data jako „tabulku“ (klávesová zkratka: Ctrl + T), Excel automaticky reaguje na pozdější rozšíření. Vzorec SVERWEIS zůstane platný, i když přidáte nové řádky. Vzorec =SVERWEIS(A2;Tabelle1;3;FALSCH) pak čistě přistupuje k aktualizovaným datům – ideální pro rostoucí seznamy.

• FALSCH místo WAHR – cílené řízení vyhledávání: Bez přesného porovnání riskujete nepřesné výsledky. Čtvrté místo vzorce – tzv. oblast_odkaz – by mělo být téměř vždy nastaveno na FALEŠNÉ. Pouze v takovém případě Excel zkontroluje přesnou shodu. PRAVDA je vhodná pouze pro seřazená data, což v běžné praxi málokdy lze zaručit.

• Kontrola a sjednocení formátů dat: Číslo v textovém formátu nebo číslo v číselném formátu – oba vypadají stejně, ale chovají se odlišně. Pokud funkce VLOŽIT_JAKO_NEJLEPŠÍ vůbec nevrátí žádný výsledek, přestože hodnota je v seznamu, téměř vždy se jedná o konflikt formátů. Pomocí =ISTTEXT(A2) nebo =ISTZAHL(A2) rychle zjistíte, kde je problém. Nástroje jako =GLÄTTEN() nebo =WERT() automaticky uklidí.

• Cílené použití absolutních a relativních odkazů na buňky: Při kopírování vzorců se přizpůsobí i odkazy na buňky. U maticových údajů to může mít fatální následky. Proto používejte absolutní odkazy na buňky s $ (např. $A$2:$D$100), abyste mohli vzorce spolehlivě přetahovat shora dolů nebo napříč tabulkou, aniž by se oblast „posunula“.

• Elegantní zachycení chyb: Nikdo nechce v sériových dopisech nebo na dashboardu vidět „NV“. Kombinujte funkci VLOŽIT_JAKO s funkcí POKUD_CHYBA, abyste vytvořili alternativní výstup: =POKUD_CHYBA(VLOŽIT_JAKO(A2;Tabulka1;3;FALSE); „Nenalezeno“)

• Nejde o kosmetickou úpravu, ale o funkční vylepšení – například při exportu nebo automatizovaných datových procesech.

• Znát omezení funkce SVERWEIS: Jakmile dojde k posunutí sloupců nebo změně struktury tabulky, funkce SVERWEIS vrátí nesprávné výsledky. Rovněž není možné zpětné vyhledávání, tedy zprava doleva. V takových případech se doporučuje použít XVERWEIS nebo INDEX/VERGLEICH.

XVERWEIS, INDEX & Co. – Alternativy s větší kontrolou

Od Microsoft 365 a Excel 2021 je k dispozici robustnější varianta XVERWEIS, která překonává mnoho slabých stránek SVERWEIS. Kombinace INDEX/VERGLEICH je také považována za flexibilní – zejména při změně verzí Excelu nebo požadavků.

• XVERWEIS: Zpětné vyhledávání bez oklik: Největší rozdíl: XVERWEIS umožňuje také vrácení výsledků ze sloupců vlevo od hledaného výrazu. Příklad: =XVERWEIS(B2;Tabelle1[Name];Tabelle1[Kundennummer];„Kein Treffer“). Tímto způsobem lze prohledávat i tabulky, jejichž struktura není optimální. Funkce SVERWEIS by zde selhala.

• Index sloupce odpadá – méně náchylné k chybám: U funkce XVERWEIS zadáte dvě konkrétní oblasti buněk: oblast vyhledávání a oblast vrácení. Nemusíte počítat jako u funkce SVERWEIS (3. sloupec, 4. sloupec atd.). To snižuje počet chyb a zvyšuje přehlednost.

• Cílené řízení chybových hlášení: Čtvrtý argument „pokud_nenalezeno“ nahrazuje v XVERWEIS obcházení pomocí WENNFEHLER. Snadná čitelnost, méně překlepů, přímý přístup – funkce reaguje lépe na prázdné buňky nebo nesprávné hodnoty.

• Řízení směru vyhledávání a porovnávání: Funkce XVERWEIS nabízí dva další parametry: „Režim porovnání“ (0, -1, 1) a „Režim vyhledávání“ (1 shora, -1 zdola). To umožňuje například cílené vyhledávání cenových stupnic nebo duplicitních záznamů – což je u funkce SVERWEIS nemožné.

• XVERWEIS na mobilních zařízeních a na webu: V aplikaci Microsoft Excel Mobile funguje XVERWEIS v zásadě bez problémů, ale komplexní dynamické vzorce jsou omezené. V aplikaci Excel Online fungují funkce SVERWEIS i XVERWEIS stabilně, pokud jsou formáty buněk správné. Na mobilních zařízeních se doporučuje používat jednoduché struktury, protože některé ovládací prvky chybí.

• INDEX/VERGLEICH: Univerzální a nezávislé na verzi: INDEX vyhledá hodnotu, VERGLEICH vyhledá odpovídající místo. Příklad vzorce: =INDEX(D2:D100;VERGLEICH(A2;B2:B100;0))

Na rozdíl od SVERWEIS se tato kombinace nerozbije, pokud změníte pořadí sloupců. Hodí se pro složité výpočty nebo v případě, že XVERWEIS není k dispozici (např. Excel 2016).
Výkon při velkých objemech dat: Pokud pracujete s tisíci datovými záznamy, s INDEX/VERGLEICH nebo XVERWEIS dosáhnete cíle rychleji než s SVERWEIS. Obě funkce zpracovávají větší objemy dat efektivněji – zejména v dynamických dashboardech nebo při exportu z třetích systémů.

Praktický příklad – SVERWEIS krok za krokem

Mnoho lidí používá Excel každý den, ale při použití funkce SVERWEIS často dochází k nedorozuměním – zejména pokud s vzorci nepracujete pravidelně. Následující příklad ukazuje, jak pomocí přehledné struktury a několika kroků vytvořit funkční funkci SVERWEIS. Cílem je automaticky načíst příslušné telefonní číslo ze seznamu zákazníků.

• Struktura výchozí tabulky: Vytvořte tabulku se třemi sloupci: „Číslo zákazníka“, „Jméno“ a „Telefonní číslo“. Každý řádek obsahuje přesně jeden záznam. Důležité: Sloupec s hledanou hodnotou – v tomto případě číslo zákazníka – je zcela vlevo. Funkce SVERWEIS vždy porovnává pouze první sloupec v oblasti vyhledávání. Příklad: „1002 Langer 0157 9876543“

• Vytvořte druhou tabulku pro dotaz: Vpravo – nebo v jiném listu tabulky – vytvořte malý seznam se dvěma sloupci: „Číslo zákazníka“ a „Telefonní číslo“. Do sloupce „Číslo zákazníka“ zadejte hodnotu, kterou chcete vyhledat – například „1002“. Druhý sloupec zatím nechte prázdný, vyplní se automaticky později.

• Správné sestavení funkce VLOŽENÍ: Klikněte do buňky, ve které se má výsledek zobrazit – například buňka F2. Nyní zadejte následující vzorec: =VLOŽENÍ(E2;A2:C100;3;FALŠ)

• Tento vzorec znamená: Vyhledej hodnotu z buňky E2 (v tomto případě „1002“) v prvním sloupci oblasti A2 až C100. Poté vrať hodnotu ze třetího sloupce (C) stejného řádku. „FALŠ“ na konci zajišťuje, že budou akceptovány pouze přesné shody.

• Otestujte vzorec a zkontrolujte výsledek: Jakmile stisknete klávesu Enter, Excel automaticky zobrazí v buňce F2 „0157 9876543“ – telefonní číslo hledaného zákazníka. Pokud změníte hodnotu v E2 (například na „1003“), výsledek v F2 se automaticky změní. Vzorec se dynamicky přizpůsobí zadané hodnotě.

• Vyhněte se typickým zdrojům chyb: Zkontrolujte, zda jsou čísla v oblasti vyhledávání skutečně formátována jako čísla. Častou příčinou chyby „NV“ je to, že hodnota v E2 je formátována jako číslo, ale v zdrojové tabulce jako text. To je vizuálně téměř nepostřehnutelné, ale má to technické důsledky. Pomocí „=ISTTEXT(A2)“ to můžete zkontrolovat. V případě potřeby pomůže „=WERT()“ k opravě obsahu buňky.

• Při použití více řádků zafixujte odkazy na buňky: Chcete-li vyhledat další zákazníky, přetáhněte vzorec dolů a zafixujte oblast pomocí znaku dolaru: =SVERWEIS(E2;$A$2:$C$100;3;FALSCH)

Tím zůstane vyhledávací oblast nezměněna, i když vzorec zkopírujete nebo přetáhnete dolů.
Zachycení chybových hlášení, vylepšení výstupu

Chcete-li místo „NV“ zobrazit srozumitelné hlášení, jako například „Zákazník nebyl nalezen“, doplňte vzorec takto: =WENNFEHLER(SVERWEIS(E2;$A$2:$C$100;3;FALSCH); „Zákazník nebyl nalezen“)

Tabulka tak vypadá přehledněji a uživatel okamžitě rozpozná, že není k dispozici žádný výsledek.