Sammendrag
For å oversette tekstverdier til tall og summere resultatet, kan du bruke en INDEX- og MATCH-formel og SUM-funksjonen. I eksemplet vist er formelen i H5:
(=SUM(INDEX(value,N(IF(1,MATCH(C5:G5,code,0))))))
der "kode" er det navngitte området K5: K9, og "verdi" er det navngitte området L5: L9.
Merk: dette er en matriseformel, og må angis med kontroll + skift + enter.
Forklaring
Hjertet i denne formelen er en grunnleggende INDEKS- og MATCH-formel, som brukes til å oversette tekstverdier til tall som definert i en oppslagstabell. For eksempel, for å oversette "EX" til det tilsvarende tallet, bruker vi:
=INDEX(value,MATCH("EX",code,0))
som ville komme tilbake 4.
Vridningen i dette problemet er imidlertid at vi ønsker å oversette og oppsummere et utvalg av tekstverdier i kolonne C til G til tall. Dette betyr at vi må gi mer enn en oppslagsverdi, og vi trenger INDEX for å returnere mer enn ett resultat. Standardtilnærmingen er en formel som denne:
=SUM(INDEX(value,MATCH(C5:G5,code,0)))
Etter at MATCH har kjørt, har vi en matrise med 5 elementer:
=SUM(INDEX(value,(2,2,3,2,5)))
Så det ser ut til at INDEX skal gi 5 resultater til SUM. Hvis du prøver dette, vil INDEX-funksjonen imidlertid bare returnere ett resultat SUM. For å få INDEX til å returnere flere resultater, må vi bruke et ganske uklart triks, og pakke MATCH i N og IF slik:
N(IF(1,MATCH(C5:G5,code,0)))
Dette tvinger INDEX effektivt til å gi mer enn en verdi til SUM-funksjonen. Etter at INDEX har kjørt, har vi:
=SUM((3,3,2,3,-1))
Og SUM-funksjonen returnerer summen av elementene i matrisen, 10. For en god beskrivelse av denne oppførselen, se denne interessante artikkelen på EXCELXOR-nettstedet.
