Opakovaně použitelné VLOOKUP (VLOOKUP)

Obsah

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ě:

  1. vyberte buňky, kde se mají výsledky zobrazit (v našem příkladu je to rozsah D6:D20)
  2. zadejte (zkopírujte vzorec do první buňky) rozsahu
  3. 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í?

 

Napsat komentář