Excel

Summen af ​​tekstværdier som tal

Sum Text Values Like Numbers

Excel-formel: Summen af ​​tekstværdier som talResumé

For at oversætte tekstværdier til tal og opsummere resultatet kan du bruge en INDEX- og MATCH-formel og SUM-funktionen. I det viste eksempel er formlen i H5:

 
{= SUM ( INDEX (value, N ( IF (1, MATCH (C5:G5,code,0)))))}

hvor 'kode' er navngivet rækkevidde K5: K9, og 'værdi' er det navngivne område L5: L9.



Bemærk: dette er en matrixformel , og skal indtastes med kontrol + skift + enter.



Forklaring

Hjertet i denne formel er en grundlæggende INDEX- og MATCH-formel, der bruges til at oversætte tekstværdier til tal som defineret i en opslagstabel. For eksempel, for at oversætte 'EX' til det tilsvarende nummer, bruger vi:

 
= INDEX (value, MATCH ('EX',code,0))

som ville vende tilbage 4.



Twist i dette problem er dog, at vi ønsker at oversætte og summere a række af tekstværdier i kolonne C til G til tal. Det betyder, at vi skal give mere end en opslagsværdi, og vi har brug for INDEX for at returnere mere end et resultat. Standardmetoden er en formel som denne:

 
= SUM ( INDEX (value, MATCH (C5:G5,code,0)))

Efter MATCH-kørsler har vi en matrix med 5 elementer:

 
= SUM ( INDEX (value,{2,2,3,2,5}))

Så det ser ud til, at INDEX skal returnere 5 resultater til SUM. Men hvis du prøver dette, returnerer INDEX-funktionen kun et resultat SUM. For at få INDEX til at returnere flere resultater, skal vi bruge en snarere uklart trick , og pakk MATCH i N og IF sådan:



 
 N ( IF (1, MATCH (C5:G5,code,0)))

Dette tvinger INDEX effektivt til at give mere end en værdi til SUM-funktionen. Efter INDEX kører har vi:

gradient udfylde blå databjælker udmærker sig
 
= SUM ({3,3,2,3,-1})

Og SUM-funktionen returnerer summen af ​​elementer i arrayet, 10. For en god skrivning af denne adfærd, se denne interessante artikel på EXCELXOR-webstedet .

Forfatter Dave Bruns


^