Existuje hledaná hodnota ve sloupci - Excel

Úvodem do vyhledání hodnoty ve sloupci

Možná pro hledaní zda požadovaná hodnota v dané oblasti (sloupci) se nachází využívate funkci SVYHLEDAT (VLOOKUP), ale co když budete chtít použít INDEX (INDEX), POZVYHLEDAT (MATCH), nejde řešit jinak?

Jaké máme další možností pro vyřešení máme, jaké mohou být úskalí daných řešení? V tomto článku se na to podíváme.

Mějme tabulku například vydaných faktur a ve druhém sloupečku pozici této faktutry. Například:

článek je rozdělen an kapitoly, kdy se podíváme na 10 + 1 možných řešení:

• SVYHLEDAT (VLOOKUP) - bez posledního argumentu
• SVYHLEDAT (VLOOKUP) - vrací hledanou "stejnou" hodnotu
• SVYHLEDAT (VLOOKUP) - vrací hodnotu z jiného sloupce
• INDEX (INDEX), POZVYHLEDAT (MATCH)
• INDEX (INDEX), POZVYHLEDAT (MATCH) - přesná shoda
• COUNTIFS (COUNTIFS)
• KDYŽ (IF), COUNTIFS (COUNTIFS)
• SUMIFS (SUMIFS)
• VYHLEDAT (LOOKUP) - jak využít, komplikovaněji
• VYHLEDAT (LOOKUP) - jak využít, jednodušeji
• FILTER (FILTER)
• Řešení - lepší rozlišení
• Související články
• Ke stažení

Kdo nechce dále číst, tak rovnou řešení:

V lepším rozlišení niže.

SVYHLEDAT (VLOOKUP) - bez posledního argumentu

Klasické použití funkce SVYHLEDAT (VLOOKUP), kdy se předpokládá, že hledaná hodnota se ve sloupci nachází a sloupec je seřazen od nejmenšího. Co ale když sloupec nebude seřazen, případně se bude hledat hodnota, která neexistuje, najde se nejbližší nižší, neboli chybná.

Využijeme-li funkci:

=SVYHLEDAT(E6;$B$6:$C$9;2)

Budeme-li hledat A127, která se ve sloupci nenachází, obdržíte hodnothu A126 coř je chybně.

SVYHLEDAT (VLOOKUP) - vrací hledanou "stejnou" hodnotu

Klasické použití funkce SVYHLEDAT (VLOOKUP), kdy vyplníte poslední argument na přesnou shodu. Pokud hledaná hodnota nebude nalezena, obdržíte chybovou hodnotu #NENÍ_K_DISPOZICI

=SVYHLEDAT(E6;$B$6:$C$9;1;NEPRAVDA)

SVYHLEDAT (VLOOKUP) - vrací hodnotu z jiného sloupce

Klasické použití funkce SVYHLEDAT (VLOOKUP), kdy vyplníte poslední argument na přesnou shodu, ale nebudete chtít vratít hledanou hodnotu, ale například hodnotu z vedlejšího sloupce.

=SVYHLEDAT(E6;$B$6:$C$9;2;NEPRAVDA)

INDEX (INDEX), POZVYHLEDAT (MATCH)

Nalezení hledané hodnoty, pokud existuje za využití INDEX (INDEX), POZVYHLEDAT (MATCH), nerozlišuje velikost písmen A124 je to stejné jako a124:

=INDEX($C$6:$C$9;POZVYHLEDAT(E6;$B$6:$B$9;0))

Místo INDEX (INDEX) lze využít funkci POSUN (OFFSET).

INDEX (INDEX), POZVYHLEDAT (MATCH) - přesná shoda

Nalezení hledané hodnoty, pokud existuje za využití INDEX (INDEX), POZVYHLEDAT (MATCH), navíc s požadavkem na přesnou shodu. V prohledavaném sloupci máte hodnotu A124, pokud hledáte A124 bude nalezeno, pokud hledáte a124 už nalezeno nebude:

=INDEX($C$6:$C$9;POZVYHLEDAT(PRAVDA;STEJNÉ($B$6:$B$9;E6);0))

COUNTIFS (COUNTIFS)

Zajímavou záležitosti je využít pro zjištění, zda hledaná hodnota existuje funkci COUNTIFS (COUNTIFS), kdy pokud hledaná hodnota existuje obdržíte hodnotu 1, pokud se ve sloupci nachazí více hledaných hodnot, budete mít ve výsledku počet těchto nalezených hodnot v oblasti (sloupců):

=COUNTIFS($B$6:$B$9;E6)

Tuto funkci můžete využít i ke kontrole, zda se daná hodnota ve sloupci nenachází dva krát (například, že vám daná faktura byla proplacena vícekrát, možná vám to vadit nebude, ale někomu by mohlo vadit ;) .

KDYŽ (IF), COUNTIFS (COUNTIFS)

Pokud předchozí hodnotu doplníte o funkci KDYŽ (IF) můžete pak do příslušných argumentů vložit co chcete mít v případě, že se hodnota v oblasti nachází (já vložil "Je" - ANO nemám bytostně rád), pokud se nenachází (já vložil "Ne")

=KDYŽ(COUNTIFS($B$6:$B$9;E6);"Je";"Není")

SUMIFS (SUMIFS)

Podobně jako COUNTIFS (COUNTIFS), můžete využít i funkci SUMIFS (SUMIFS), provede součet hodnot z dalšího sloupce, které jsou na řádku (řádcích). Pokud teda půjde o pořadové číslo a hledaná hodnota bude pouze jednou, můžete také využít:

=SUMIFS(C6:C9;B6:B9;E6)

VYHLEDAT (LOOKUP) - jak využít, komplikovaněji

Pro hledání, zda se daná hodnota v tabulce nachází, lze využít i funkci VYHLEDAT, ale problém nastane, pokud hledaná hodnota se ve sloupci nacházet nebude!

=VYHLEDAT(E6;$B$6:$B$9;$B$6:$B$9)

VYHLEDAT (LOOKUP) - jak využít, jednodušeji

Jak využít funkci VYHLEDAT (LOOKUP) jednodušeji, ale pozor, pokud se hledaná hodnota v tabulce nacházet nebude můžete obdržet chybovou hodnotu

=VYHLEDAT(E6;$B$6:$B$9)

FILTER (FILTER)

K řešení lze využít i funkci FILTER (FILTER), ale pozor pokud bude ve sloupci více hledaných hodnot, může doít k chybě #PŘESAH!.

=FILTER($B$6:$B$9;$B$6:$B$9=E6;"")

Případně pokud hodnotu bude více a vy chcete obejít problém s chybovou hodnotou #PŘESAH!

=UNIQUE(FILTER($B$6:$B$9;$B$6:$B$9=E6;""))

Řešení

V lepším rozlišení.

Související články

Několik souvisejicích článku:

• SVYHLEDAT
• SUMIFS
• INDEX
• POSUN
• ...

Závěrem

Narazili jste na nějaký problém/nejasnost? Máte tip na doplnění, můžete to zmínit v komentářích.

Článek byl aktualizován: 17.06.2022 13:54

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.