Excel

Sčítajte textové hodnoty ako čísla

Sum Text Values Like Numbers

Vzorec programu Excel: Sčítajte textové hodnoty ako číslaZhrnutie

Na 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


^