Máme seznam objednávek s čísly a názvy zboží. Rád bych např. vytáhl ze stolu podle čísla objednávky všechno zboží, které je v něm obsaženo. Víceméně takto:
Úžasná vlastnost SVYHLEDAT (VLOOKUP) v takové situaci pomůže jen částečně, protože je schopen extrahovat data pouze podle první nalezené shody, tj. dá nám pouze jablka. Chcete-li najít a extrahovat všechny položky z tabulky, je lepší použít maticový vzorec. Jako tento:
=INDEX($B$2:$B$16;NEJMÉNĚ(IF($ E $ 2=A2: A16;LINE(B2:B16)-1;»»);LINE()-5))
Musí se zadat následovně:
- vyberte buňky, kde se mají výsledky zobrazit (v našem příkladu je to rozsah D6:D20)
- zadejte (zkopírujte vzorec do první buňky) rozsahu
- lis Ctrl + směna + vstoupit
Odečítání jednotek ve fragmentu STRING(B2:B16)-1 se provádí kvůli záhlaví tabulky. Ze stejného důvodu, aby se kompenzoval posun ve výsledném rozsahu vzhledem k původnímu, je číslo pět ve fragmentu odečteno STRING()-5
Chcete-li skrýt #NUM! chyba, která se objeví v prázdných buňkách ve výsledném rozsahu D6:D20, můžete použít funkce kontroly chyb IF a EOSH, které nahradí náš vzorec o něco složitějším:
=IF(EOSH(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)));»»;ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)))
V Excelu 2007 se objevila pohodlnější funkce IFERROR – umožňuje vám vyřešit problém kompaktněji:
=IFERROR(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5));»»)
PS
V anglické verzi Excelu budou tyto funkce vypadat takto:
=INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))
=IF(ISERR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))),»»,INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)))
=IFERROR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)),»»)
- Použití funkce VLOOKUP k vyhledání dat v tabulce
- Vylepšená verze funkce SVYHLEDAT2, která dokáže vyhledávat v libovolném sloupci, nejen v první hodnotě
- Funkce VLOOKUP2 a VLOOKUP3 z doplňku PLEX
- Co jsou maticové vzorce a k čemu se používají?