Resumé
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 det navngivne område 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 sammenfatte et interval af tekstværdier i kolonne C til G til tal. Dette 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 emner:
=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 ét resultat SUM. For at få INDEX til at returnere flere resultater, er vi nødt til at bruge et temmelig uklart trick og indpakke MATCH i N og IF sådan:
N(IF(1,MATCH(C5:G5,code,0)))
Dette tvinger effektivt INDEX til at give mere end en værdi til SUM-funktionen. Efter INDEX kører har vi:
=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.
