ZhrnutieNa preklad textových hodnôt na čísla a súčet výsledku môžete použiť vzorec INDEX a MATCH a funkciu SUMA. V uvedenom príklade vzorec v H5 je:
{= 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 an maticový vzorec , a je potrebné ho zadať pomocou ovládača + shift + enter.
Vysvetlenie
Srdcom tohto vzorca je základný vzorec INDEX a MATCH, ktorý sa používa na preklad textových hodnôt na čísla definované vo vyhľadávacej tabuľke. Napríklad na preklad „EX“ na zodpovedajúce číslo by sme použili:
= INDEX (value, MATCH ('EX',code,0))
ktorý by vrátil 4.
Zvrat v tomto probléme je však v tom, že chceme preložiť a spočítať a rozsah textových hodnôt v stĺpcoch C až G na čísla. To znamená, že musíme zadať viac ako jednu vyhľadávaciu hodnotu a potrebujeme INDEX, aby sme vrátili viac ako jeden výsledok. Štandardný prístup je nasledujúci vzorec:
= 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 jeden výsledok SUM. Aby INDEX vrátil viacero výsledkov, musíme použiť radšej nejasný trik , a zabaľte MATCH do N a IF takto:
N ( IF (1, MATCH (C5:G5,code,0)))
To efektívne núti INDEX poskytovať viac ako jednu hodnotu pre funkciu SUMA. Po spustení INDEX máme:
vzorec programu Excel kombinujúci meno a priezvisko s čiarkou
= SUM ({3,3,2,3,-1})
A funkcia SUMA vracia súčet položiek v poli, 10. Informácie o správaní tohto správania nájdete v časti tento zaujímavý článok na webe EXCELXOR .
Autor Dave Bruns
