V tomto podrobném návodu si ukážeme, jak v Excelu vyhledávat nadřazenou kategorii podle položky pomocí kombinace funkcí INDEX a POZVYHLEDAT. Tento postup je užitečný zejména tehdy, pokud máte pomocnou tabulku strukturovanou do sloupců, kde jsou jednotlivé produkty uvedeny v řádcích pod sebou podle toho, do které nadřazené kategorie patří.

Tento článek vás provede krok za krokem celým procesem, včetně správného nastavení a vyřešení častých problémů.

1. Problém s funkcí POZVYHLEDAT

Než začneme s řešením, podíváme se na to, proč POZVYHLEDAT (anglicky MATCH) nemůže být v tomto případě použita.

• Funkce POZVYHLEDAT vyžaduje, aby bylo prohledávané pole uspořádáno v jednom sloupci nebo v jednom řádku.
• Pokud bychom označili celou oblast s produkty v pomocné tabulce, Excel vrátí chybu, protože neví, ve kterém sloupci se hledaný produkt nachází.

Proto je nutné použít jiný přístup – kombinaci INDEX a skalárního součinu.

2. Ověření pozice produktu v pomocné tabulce

Začneme vytvořením podmínky, která nám zjistí, kde se produkt nachází:

1. Označíme celou pomocnou tabulku včetně prázdných buněk.
2. Porovnáme ji s produktem ve zdrojové tabulce.
3. Použijeme klávesu F9, abychom si zobrazili výsledek podmínky.

Tento postup vrátí sérii hodnot PRAVDA a NEPRAVDA, kde PRAVDA označuje buňku, ve které se produkt nachází.

Tip: Oddělovač ** znamená oddělení do sloupců, zatímco ; odděluje řádky.

3. Zjištění pořadového čísla sloupce

Abychom mohli použít funkci INDEX, potřebujeme získat číslo sloupce, ve kterém se produkt nachází.

Postup:

1. Vynásobíme podmínku funkcí SLOUPEC: excelCopy= (pomocná tabulka = hledaný produkt) * SLOUPEC(záhlaví pomocné tabulky) - SLOUPEC(první buňky před záhlavím)
2. Použití F9 pro kontrolu vrácených hodnot:
   • NEPRAVDA se změní na 0.
   • Pouze v jedné buňce se vrátí číslo sloupce.

4. Odstranění nul pomocí funkce SOUČIN.SKALÁRNÍ

Abychom se zbavili nepotřebných nul, zabalíme celou podmínku do funkce SOUČIN.SKALÁRNÍ:

= SOUČIN.SKALÁRNÍ((pomocná tabulka = hledaný produkt) * (SLOUPEC(záhlaví pomocné tabulky) - SLOUPEC(první buňky před záhlavím)))

Tímto způsobem získáme jediné číslo sloupce, kde se produkt nachází.

5. Použití funkce INDEX pro vyhledání kategorie

Nyní máme číslo sloupce, takže můžeme použít funkci INDEX k získání nadřazené kategorie:

= INDEX(záhlaví pomocné tabulky; ; SOUČIN.SKALÁRNÍ((pomocná tabulka = hledaný produkt) * (SLOUPEC(záhlaví pomocné tabulky) - SLOUPEC(první buňky před záhlavím))))

Co znamenají jednotlivé části vzorce:

• INDEX(záhlaví pomocné tabulky; ; sloupec) – vyhledává hodnotu v daném sloupci.
• SOUČIN.SKALÁRNÍ(…) – vrací číslo sloupce, ve kterém se produkt nachází.

Po potvrzení vzorce ENTER a roztažení dolů se vrátí správné nadřazené kategorie pro všechny produkty.

6. Časté dotazy

Co dělat, když Excel vrací chybu #N/A?

Zkontrolujte, zda:

• Produkt ve zdrojové tabulce přesně odpovídá produktu v pomocné tabulce (bez překlepů).
• Oblast pomocné tabulky je správně označená a zafixovaná pomocí F4.

Jak zajistit, aby vzorec fungoval i po roztažení dolů?

• Fixujte oblasti pomocné tabulky pomocí F4, aby se nepřesouvaly při kopírování vzorce.
• Buňka s hledaným produktem by měla zůstávat relativní, aby se mohla měnit při kopírování.

Funguje tento postup i v Google Sheets?

Ano, ale Google Sheets nepodporují funkci SOUČIN.SKALÁRNÍ úplně stejně jako Excel. V takovém případě lze použít SUMPRODUCT nebo pole vzorců.