Opakovane použiteľné VLOOKUP (VLOOKUP)

Obsah

Máme zoznam objednávok s číslami a názvami tovaru. Chcel by som napríklad vytiahnuť zo stola podľa čísla objednávky všetok tovar, ktorý je v ňom zahrnutý. Viac-menej takto:

 

Úžasná vlastnosť VLOOKUP (VLOOKUP) v takejto situácii pomôže len čiastočne, pretože dokáže extrahovať dáta len podľa prvej nájdenej zhody, teda dá nám len jablká. Ak chcete nájsť a extrahovať všetky položky z tabuľky, je lepšie použiť vzorec poľa. Ako tento:

=INDEX($B$2:$B$16;ASPOŇ(IF(2 $ E$=A2: A16;LINE(B2:B16)-1;»»);LINE()-5))

Musí sa zadať takto:

  1. vyberte bunky, v ktorých sa majú zobraziť výsledky (v našom príklade je to rozsah D6:D20)
  2. zadajte (skopírujte vzorec do prvej bunky) rozsahu
  3. lis ctrl + smena + vstúpiť

Odčítanie jednotiek vo fragmente STRING(B2:B16)-1 sa robí kvôli hlavičke tabuľky. Z rovnakého dôvodu, aby sa kompenzoval posun vo výslednom rozsahu v porovnaní s pôvodným, sa číslo päť vo fragmente odpočíta STRING()-5

Ak chcete skryť #NUM! chyba, ktorá sa objaví v prázdnych bunkách vo výslednom rozsahu D6:D20, môžete použiť funkcie na kontrolu chýb IF a EOSH a nahradiť náš vzorec trochu zložitejší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 Exceli 2007 sa objavila pohodlnejšia funkcia IFERROR – umožňuje vám kompaktnejšie vyriešiť problém:

=IFERROR(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5));»»)

PS

V anglickej verzii Excelu budú tieto funkcie vyzerať 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žitie funkcie VLOOKUP na vyhľadanie údajov v tabuľke
  • Vylepšená verzia funkcie VLOOKUP2, ktorá dokáže vyhľadávať v ľubovoľnom stĺpci, nielen v prvej hodnote
  • Funkcie VLOOKUP2 a VLOOKUP3 z doplnku PLEX
  • Čo sú vzorce poľa a na čo sa používajú?

 

Nechaj odpoveď