SVYHLEDAT

 

Pokud máte v tabulce známé informace, můžete pomocí funkce SVYHLEDAT vyhledat související informace po řádcích. Pokud si například chcete koupit pomeranč, můžete pomocí funkce SVYHLEDAT vyhledat cenu.

SVYHLEDAT pro BigQuery

Svislé vyhledání. Vrátí hodnoty ve sloupci dat na pozici, kde byla nalezena shoda ve sloupci k vyhledávání.

Příklady použití

SVYHLEDAT("Apple"; název_tabulky!fruit; název_tabulky!price)

Syntaxe

SVYHLEDAT(hledaný_klíč; rozsah; index; je_seřazeno)

• hledaný_klíč: Hodnota, která se má vyhledat ve sloupci vyhledávání.
• sloupec_vyhledávání: sloupec dat, ve kterém se má vyhledávat.
• sloupec_výsledku: Sloupec dat, který se má použít pro výsledek.
• je_seřazeno: [VOLITELNÉ] Způsob, jak najít shodu pro hledaný_klíč.
  • NEPRAVDA: Tato hodnota se doporučuje pro přesnou shodu.
  • PRAVDA: Tato hodnota je výchozí pro přibližnou shodu, pokud není parametr je_seřazeno zadán.
    Tip: Před použitím přibližné shody seřaďte hledaný klíč vzestupně. Jinak by vám mohla být vrácena nesprávná hodnota. Přečtěte si možné důvody vrácení nesprávné hodnoty.

Tip: Pro flexibilnější dotazy na databázi v BigQuery používejte funkci XLOOKUP.

Syntaxe

=SVYHLEDAT(hledaný_klíč; rozsah; index; [je_seřazeno])

Vstupy

1. hledaný_klíč: Hodnota, která se má vyhledat v prvním sloupci rozsahu.
2. rozsah: Horní a dolní hodnota, která se má použít pro vyhledávání.
3. index: Index sloupce s vrácenou hodnotou rozsahu. Index musí být kladné celé číslo.
4. je_seřazeno: Volitelný vstupní parametr. Vyberte některou z těchto možností:
   • NEPRAVDA = Přesná shoda. Toto je doporučená hodnota.
   • PRAVDA = Přibližná shoda. Toto je výchozí hodnota, pokud parametr je_seřazeno není zadaný.
     Důležité: Než použijete přibližnou shodu, seřaďte hledaný klíč vzestupně. Jinak by vám mohla být vrácena nesprávná hodnota. Přečtěte si možné důvody vrácení nesprávné hodnoty.

Vrácená hodnota

První odpovídající hodnota z vybraného rozsahu.

Technické podrobnosti:

Příklad:

=SVYHLEDAT(G9; B4:D8; 3; NEPRAVDA)

=SVYHLEDAT("Apple"; B4:D8; 3; PRAVDA)

Vstupy	Popis
hledaný_klíč	Toto je hodnota, kterou vyhledáváte v prvním sloupci rozsahu. Pokud očekáváte jinou než chybovou hodnotu, musí být hledaný klíč v prvním sloupci rozsahu. Je podporováno také odkazování na buňky. Jak provést jednoduchou kontrolu: Pokud se váš hledaný_klíč nachází v B3, měl by rozsah začínat sloupcem B.
rozsah	Jedná se o rozsah, kde: Funkce vyhledá zadaný hledaný klíč v prvním sloupci. Funkce SVYHLEDAT vrátí hodnotu ze sloupce určeného parametrem index. Můžete také použít pojmenovaný rozsah. Pokud chcete, aby se vrátila jiná než chybová hodnota, musí být hledaný klíč v prvním sloupci rozsahu. Jak provést jednoduchou kontrolu: Pokud se váš hledaný_klíč nachází v B3, měl by rozsah začínat sloupcem B.
index	Označuje se také jako „číslo sloupce“. Jedná se o index sloupce v rozsahu, který obsahuje vrácenou hodnotu. Nejmenší možný index je 1. Největší možný index je maximální počet sloupců v daném rozsahu. Po nastavení rozsahu hledá funkce SVYHLEDAT pouze ve sloupci hledaného klíče, když index = 1, nebo ve sloupcích, které jsou dále vpravo. Tip: Když používáte funkci SVYHLEDAT, představte si, že sloupce v rozsahu jsou číslovány zleva doprava a začínají číslem 1.
je_seřazeno	Jedná se o nepovinný vstupní parametr. K dispozici jsou dvě možnosti: PRAVDA a NEPRAVDA. Pokud má parametr je_seřazeno hodnotu PRAVDA, použije funkce SVYHLEDAT přibližnou shodu. Důležité: Než použijete přibližnou shodu, seřaďte hledaný klíč vzestupně. Jinak se může vrátit neočekávaná hodnota. Přečtěte si možné důvody vrácení nesprávné hodnoty. Pokud má parametr je_seřazeno hodnotu NEPRAVDA, použije funkce SVYHLEDAT přesnou shodu. Pokud parametr je_seřazeno není zadán, použije se ve výchozím nastavení hodnota PRAVDA. Důrazně doporučujeme, abyste: pro parametr je_seřazeno použili hodnotu NEPRAVDA, protože se pak funkce chová stejně bez ohledu na to, zda je sloupec hledaného klíče seřazen, kvůli lepší srozumitelnosti vstupní parametr je_seřazeno vždy zadávali, i když je volitelný.

 

Výstupy

Popis

Vrácená hodnota

Toto je hodnota, kterou funkce SVYHLEDAT vrátí na základě vašich vstupních parametrů. Z každé funkce SVYHLEDAT je pouze jedna vrácená hodnota. Pokud zadání odpovídá několik hodnot hledaného klíče, je ve sloupci vrácené hodnoty vrácena ta hodnota, jejíž související hledaný klíč ve sloupci hledaného klíče odpovídá jako první. Pokud je vrácena chybová hodnota #N/A, nebyla nalezena žádná hodnota. Pokud narazíte na neočekávanou hodnotu nebo chybu, jako je #N/A nebo #VALUE!, zkuste odstranit problémy. Pokud chcete nahradit hodnotu #N/A jinou hodnotou, přečtěte si, jak použít funkci IFNA() na funkci SVYHLEDAT().

Základní příklady funkce SVYHLEDAT:

SVYHLEDAT s různými hledanými klíči

Zjistěte cenu pomeranče a jablka pomocí funkce SVYHLEDAT.

Vyzkoušet

Vysvětlení:

Pokud použijete funkci SVYHLEDAT, můžete použít různé hledané klíče , například „Apple“ (jablko) nebo „Orange“ (pomeranč).

Pokud chcete, aby se vrátila jiná než chybová hodnota, musí být tyto hledané klíče v prvním sloupci rozsahu. Pokud nechcete vyplňovat hodnotu hledaných klíčů, můžete také použít odkaz na buňku, například „G9“.

hledaný_klíč je „Orange“ (pomeranč)	=SVYHLEDAT("Orange"; B4:D8; 3; NEPRAVDA) Vrácená hodnota = 1,01 $
hledaný_klíč je „Apple“ (jablko)	=SVYHLEDAT("Apple"; B4:D8; 3; NEPRAVDA) Vrácená hodnota = 1,50 $
hledaný_klíč, který používá odkaz na buňku „Jablko“ v G9	=SVYHLEDAT(G9; B4:D8; 3; NEPRAVDA) Vrácená hodnota = 1,50 $

SVYHLEDAT s různými indexy sloupců

Zjistěte počet pomerančů ve druhém sloupci indexu pomocí funkce SVYHLEDAT.

Vyzkoušet

Vysvětlení:

Když používáte funkci SVYHLEDAT, představte si, že sloupce v rozsahu jsou číslovány zleva doprava a začínají od 1. Pokud chcete najít cílové informace, musíte zadat index sloupce. Například sloupec 2 pro počet.

Index = 2 Zjistěte počet pomerančů, což je druhý sloupec rozsahu.

=SVYHLEDAT(G3; B4:D8; 2; NEPRAVDA) Vrácená hodnota = 5

SVYHLEDAT s přesnou shodou nebo přibližnou shodou

• Zjistěte přesné ID pomocí přesné shody funkce SVYHLEDAT.
• Zjistěte přibližné ID pomocí přibližné shody funkce SVYHLEDAT.

Vyzkoušet

Vysvětlení:

Když vyhledáváte nejlepší shodu, ale ne přesnou shodu, použijte přibližnou shodu nebo parametr je_seřazeno = PRAVDA.

Pokud chcete vyhledat ID = 102, které v tabulce neexistuje, přibližná shoda se vrátí o krok zpět a výsledkem bude ID = 101. Ve sloupci hledaného klíče je totiž nejbližší hodnota 101, která je také menší než 102.

Přibližná shoda prohledává sloupec hledaného klíče, dokud nenajde hodnotu, která je větší než hledaný klíč. Pak se zastaví na řádku před větší hodnotou a vrátí hodnotu ze sloupce vrácené hodnoty na daném řádku. To znamená, že pokud není sloupec hledaného klíče seřazen vzestupně, nejspíš dostanete nesprávnou vrácenou hodnotu.

Důležité: Než použijete přibližnou shodu, seřaďte hledaný klíč vzestupně, aby se vrátila správná hodnota. Jinak se může vrátit neočekávaná hodnota.

Když vyhledáte přesnou shodu, například je_seřazeno = NEPRAVDA, vrátí se přesná shoda. Například název ovoce pro ID = 103 je „Banán“. Pokud žádná přesná shoda neexistuje, zobrazí se chyba #N/A. Přesnou shodu doporučujeme kvůli větší předvídatelnosti chování.

Přesná shoda	=SVYHLEDAT(G6; A4:D8; 2; NEPRAVDA) Vrácená hodnota = „Apple“ (jablko)
Přibližná shoda	=SVYHLEDAT(G3; A4:D8; 2; PRAVDA) NEBO =SVYHLEDAT(G3; A4:D8; 2) Vrácená hodnota = „Banán“

Běžná použití funkce SVYHLEDAT

Nahrazení chybové hodnoty z funkce SVYHLEDAT

Pokud váš hledaný klíč neexistuje, můžete nahradit chybovou hodnotu, kterou funkce SVYHLEDAT vrací. Pokud v tomto případě nechcete hodnotu #N/A, můžete ji nahradit pomocí funkcí IFNA(). Přečtěte si další informace o IFNA().

Vyzkoušet

Původně funkce SVYHLEDAT vrací výsledek #N/A, protože ve sloupci „Ovoce“ neexistuje hledaný klíč „Tužka“. Funkce IFNA() nahradí chybovou hodnotu #N/A druhým vstupním parametrem zadaným ve funkci. V našem případě to bude „NENALEZENO“.

=IFNA(SVYHLEDAT(G3; B4:D8; 3; NEPRAVDA); "NENALEZENO") Vrácená hodnota = „NENALEZENO“

Tip: Pokud chcete nahradit jiné chyby, například #REF!, přečtěte si další informace o funkci IFERROR().

SVYHLEDAT s více kritérii

SVYHLEDAT nelze přímo použít na více kritérií. Místo toho vytvořte nový pomocný sloupec, abyste funkci SVYHLEDAT použili přímo na více kritérií při sloučení více stávajících sloupců.

Vyzkoušet

1. Pomocný sloupec Helper můžete vytvořit tak, že použijete znak „&“ ke sloučení sloupců First Name Jméno) a Last Name (Příjmení).	=C4&D4 a přetažením dolů z B4 na B8 získáte pomocný sloupec.
2. Jako hledaný klíč použijte odkaz na buňku B7, JohnLee.	=SVYHLEDAT(B7; B4:E8; 4; NEPRAVDA) Vrácená hodnota = „Support“ (Podpora)

SVYHLEDAT se zástupnými znaky nebo částečnými shodami

Ve funkci SVYHLEDAT můžete také použít zástupné znaky nebo částečné shody. Můžete použít tyto zástupné znaky:

• Otazník „?“ odpovídá libovolnému jednomu znaku.
• Hvězdička „*“ odpovídá libovolnému pořadí znaků.

Pokud chcete ve funkci SVYHLEDAT použít zástupné znaky, je nutné použít přesnou shodu: „je_seřazeno = NEPRAVDA“.

Vyzkoušet

„St*“ slouží nalezení čehokoli, co začíná na „St“ bez ohledu na počet znaků, například „Steve“, „St1“, „Stock“ nebo „Steeeeeeve“.

=SVYHLEDAT("St*"; B4:D8; 3; NEPRAVDA) Vrácená hodnota = „Marketing“

Odstraňování problémů a doporučené postupy:

Nesprávná vrácená hodnota

• Vrátí se neočekávaná hodnota: Pokud nastavíte parametr je_seřazeno na hodnotu PRAVDA, ale první sloupec v rozsahu nebude číselně ani abecedně seřazen vzestupně, změňte hodnotu je_seřazeno na NEPRAVDA.

• SVYHLEDAT vrátí první shodu: SVYHLEDAT vrací pouze první shodu. Pokud máte více odpovídajících hledaných klíčů, bude vrácena hodnota, která ale nemusí být očekávanou hodnotou.
• Nečistá data: Hodnoty s mezerami za nebo před hodnotou někdy můžou vypadat podobně, ale funkce SVYHLEDAT s nimi nakládá jinak. Následující možnosti se například pro funkci SVYHLEDAT liší:
  • „ Jablko“
  • „Jablko “
  • „Jablko“

Pokud chcete získat očekávané výsledky, před použitím funkce SVYHLEDAT odstraňte mezery.

Další informace najdete v sekci doporučených postupů.

#N/A

• Pokud použijete přibližnou shodu nebo parametr je_seřazeno = PRAVDA a hledaný klíč ve funkci SVYHLEDAT je menší než nejmenší hodnota v prvním sloupci, pak funkce SVYHLEDAT vrátí chybovou hodnotu #N/A.
• Pokud použijete přesnou shodu nebo parametr je_seřazeno = NEPRAVDA, přesná shoda hledaného klíče ve funkci SVYHLEDAT se v prvním sloupci nenajde. Pokud nechcete hodnotu #N/A, když se hledaný klíč v prvním sloupci nenajde, můžete použít funkci IFNA().

#REF!

Možná jste omylem zadali rozsah s číslem větším než maximální počet sloupců v rozsahu. Abyste tomu zabránili, postupujte takto:

• Počítejte sloupce z vybraného rozsahu, ne z celé tabulky.
• Začněte počítat od 1 místo od 0.

#VALUE!

Pokud se zobrazí chyba #VALUE!, tady jsou možné příčiny:

• Jako index jste nesprávně zadali text nebo název sloupce.
• Pro index jste zadali číslo menší než 1. Index musí být aspoň 1 a menší než maximální počet sloupců rozsahu. Funkce SVYHLEDAT může vyhledávat pouze ve sloupci hledaného klíče, když je index = 1, nebo ve sloupcích, které jsou dále vpravo.

Důležité: index může být pouze číslo.

#NAME?

• Pokud váš hledaný_klíč obsahuje textová data, možná jste v něm vynechali uvozovky.

Rady a tipy

 

Správný postup	Důvod
Pro rozsah používejte absolutní odkazy	Měli byste používat: Absolutní odkaz pro rozsah ve funkci SVYHLEDAT SVYHLEDAT(G3; $B$3:$D$7; 3; NEPRAVDA) Neměli byste používat: SVYHLEDAT(G3; B3:D7; 3; NEPRAVDA) Předchází se tím nepředvídatelným změnám v rozsahu při kopírování nebo přetažení dolů.
Pokud používáte přibližnou shodu, seřaďte první sloupec vzestupně, například je_seřazeno = PRAVDA.	Pokud používáte přibližnou shodu nebo parametr je_seřazeno = PRAVDA, musíte první sloupec seřadit vzestupně. V opačném případě bude pravděpodobně vrácena nesprávná hodnota. Přečtěte si další informace o řazení
Před použitím funkce SVYHLEDAT vyčistěte data	Před použitím funkce SVYHLEDAT nezapomeňte data vyčistit. Nevyčištěná data můžou způsobit, že funkce SVYHLEDAT vrátí nepředvídatelnou hodnotu. Tady jsou některé běžné příklady nevyčištěných dat: Mezery před hodnotou: „ Jablko“ Mezery za hodnotou: „Jablko “ Prázdná místa nebo mezery: „“ a „ “ nejsou ekvivalentní K oříznutí prázdných znaků před nebo za hodnotou můžete použít Data Vyčištění dat Oříznout prázdné znaky.
Neukládejte hodnoty čísel a kalendářních dat jako text	Zkontrolujte, zda hodnoty kalendářních dat nebo čísel v prvním sloupci rozsahu funkce SVYHLEDAT, například ve sloupci hledaného klíče, nejsou uložené jako textové hodnoty. Mohla by se vám vrátit neočekávaná hodnota. V horní části Tabulek vyberte sloupec hledaného klíče. Klepněte na nabídku Formát Číslo. Vyberte možnost podle požadovaného typu dat: Datum Číslo