Pro informace o výuce Excelu klikněte zde

Máme tabulku, ve které jsou v prvním sloupci názvy firem a v dalších sloupcích čísla faktur. Potřebujeme vyhledat k odpovídajícímu číslu konkrétní firmu.

Tabulka:

V tomto případě nemůžeme použít klasické vyhledávácí funkce.

Řešení:

Do buňky B8 můžeme zadat tyto vzorce založené na funkci SOUČIN.SKALÁRNÍ:

=NEPŘÍMÝ.ODKAZ(ODKAZ(SOUČIN.SKALÁRNÍ((B2:F4=B7)*ŘÁDEK(B2:B4));1))

=INDEX(A2:A4;SOUČIN.SKALÁRNÍ((B2:F4=B7)*ŘÁDEK(B2:B4))-1)

případně maticový:

{=INDEX(A2:A4;SUMA((B2:F4=B7)*ŘÁDEK(B2:B4))-1)}

Výše uvedené vzorce nám budou vracet špatný výsledek, pokud bude seznam obsahovat duplicitní hodnoty. V tom případě můžeme použít tento maticový vzorec:

{=INDEX(A2:A4;MAX((B2:F4=B7)*ŘÁDEK(B2:B4))-1)}

který nám vrátí název firmy, u které je číslo faktury s nejvyšším číslem řádku v seznamu.

Pokud požadujeme vrátit název firmy s nejmenším číslem řádku v seznamu, musíme do vzorce vložit ještě logikou funkci KDYŽ, jinak by mám vzorec vždy vracel hodnotu 0.

{=INDEX(A2:A4;MIN(KDYŽ((B2:F4=B7);ŘÁDEK(B2:B4)))-1)}

Pozn.: Závorky {} na začátku a na konci se u maticových vzorců nepíšou, ale zadávají se pomocí kl. zkratky CTRL+SHIFT+ENTER

Výsledek:

Analýza vzorců:

Excel převede v paměti část vzorce (B2:F4=B7)*ŘÁDEK(B2:B4) na tuto matici:

Kde každé porovnání vyhodnotí a převede na logickou hodnotu PRAVDA nebo NEPRAVDA tj. 1 nebo 0 a vynásobí číslem řádku.

Dostaneme takovou matici:

Jejíž součet s pomocí funkce SOUČIN.SKALÁRNÍ nebo pomocí maticového vzorce s funkcí SUMA je 3, v posledních dvou vzorcích nám maticový vzorec zjistí pomocí funkce MAX (MIN) maximální (minimální) číslo řádku.

Pokud už víme číslo řádku ve kterém se hledaná hodnota nachází můžeme vrátit hodnotu z prvního sloupce např.:

1. Pomocí funkci NEPŘÍMÝ.ODKAZ(ODKAZ, kde funkce ODKAZ nám vrátí textový odkaz na buňku z čísla řádku a sloupce tj. ODKAZ(3,1) nám vrátí adresu buňky A3 a funkce NEPŘÍMÝ.ODKAZ vrátí odkaz na tuto buňku.

2. Pomocí funkce INDEX která nám vrátí hodnotu v určitém řádku danou indexem oblasti tj. INDEX(A2:A4;3-1) nám vrátí hodnotu z druhého indexu oblasti tj. odkaz na buňku A3.