Zhrnutie
Ak chcete preložiť textové hodnoty na čísla a výsledok sčítať, môžete použiť vzorec INDEX a MATCH a funkciu SUMA. V zobrazenom príklade je vzorec v H5:
(=SUM(INDEX(value,N(IF(1,MATCH(C5:G5,code,0))))))
kde „kód“ je pomenovaný rozsah K5: K9 a „hodnota“ je pomenovaný rozsah L5: L9.
Poznámka: toto je vzorec poľa a musí sa zadávať pomocou klávesov Control + Shift + Enter.
Vysvetlenie
Srdcom tohto vzorca je základný vzorec INDEX a MATCH, ktorý sa používa na preklad textových hodnôt do čísel definovaných vo vyhľadávacej tabuľke. Napríklad na preloženie výrazu „EX“ na zodpovedajúce číslo by sme použili:
=INDEX(value,MATCH("EX",code,0))
ktorý by sa vrátil 4.
Zvratom v tomto probléme je však to, že chceme preložiť a sčítať rozsah textových hodnôt v stĺpcoch C až G do čísel. To znamená, že musíme poskytnúť viac ako jednu vyhľadávaciu hodnotu a na vrátenie viac ako jedného výsledku potrebujeme INDEX. Štandardným prístupom je vzorec ako tento:
=SUM(INDEX(value,MATCH(C5:G5,code,0)))
Po spustení MATCH máme pole s 5 položkami:
=SUM(INDEX(value,(2,2,3,2,5)))
Zdá sa teda, že INDEX by mal vrátiť 5 výsledkov do SUM. Ak to však vyskúšate, funkcia INDEX vráti iba SUM výsledkov. Ak chcete, aby INDEX vrátil viac výsledkov, musíme použiť dosť nejasný trik a zabaliť MATCH do N a IF takto:
N(IF(1,MATCH(C5:G5,code,0)))
To účinne núti INDEX poskytovať funkcii SUM viac ako jednu hodnotu. Po spustení indexu INDEX máme:
=SUM((3,3,2,3,-1))
A funkcia SUM vráti súčet položiek v poli, 10. Pre dobré zapísanie do tohto správania si pozrite tento zaujímavý článok na webe EXCELXOR.
