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:
- vyberte bunky, v ktorých sa majú zobraziť výsledky (v našom príklade je to rozsah D6:D20)
- zadajte (skopírujte vzorec do prvej bunky) rozsahu
- 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ú?