Sammanfattning
För att översätta textvärden till siffror och summera resultatet kan du använda en INDEX- och MATCH-formel och SUM-funktionen. I exemplet som visas är formeln i H5:
(=SUM(INDEX(value,N(IF(1,MATCH(C5:G5,code,0))))))
där "kod" är det namngivna området K5: K9, och "värde" är det namngivna området L5: L9.
Obs: detta är en matrisformel och måste anges med kontroll + skift + enter.
Förklaring
Kärnan i denna formel är en grundläggande INDEX- och MATCH-formel, som används för att översätta textvärden till siffror som definieras i en uppslagstabell. För att till exempel översätta "EX" till motsvarande nummer använder vi:
=INDEX(value,MATCH("EX",code,0))
som skulle återvända 4.
Vridningen i detta problem är dock att vi vill översätta och summera ett intervall av textvärden i kolumnerna C till G till siffror. Det betyder att vi måste tillhandahålla mer än ett uppslagsvärde, och vi behöver INDEX för att returnera mer än ett resultat. Standardmetoden är en sådan formel:
=SUM(INDEX(value,MATCH(C5:G5,code,0)))
Efter MATCH-körningar har vi en matris med 5 objekt:
=SUM(INDEX(value,(2,2,3,2,5)))
Så det verkar som att INDEX ska returnera 5 resultat till SUM. Om du försöker dock kommer INDEX-funktionen att returnera endast ett resultat SUM. För att få INDEX att returnera flera resultat måste vi använda ett ganska dunkelt trick och slå in MATCH i N och IF så här:
N(IF(1,MATCH(C5:G5,code,0)))
Detta tvingar effektivt INDEX att tillhandahålla mer än ett värde till SUM-funktionen. Efter INDEX-körningar har vi:
=SUM((3,3,2,3,-1))
Och SUM-funktionen returnerar summan av objekt i matrisen, 10. För en bra skrivning av detta beteende, se den här intressanta artikeln på EXCELXOR-webbplatsen.
