Povzetek
Če želite besedilne vrednosti prevesti v številke in sešteti rezultat, lahko uporabite formulo INDEX in MATCH ter funkcijo SUM. V prikazanem primeru je formula v H5:
(=SUM(INDEX(value,N(IF(1,MATCH(C5:G5,code,0))))))
kjer je "koda" imenovani obseg K5: K9, "vrednost" pa imenovani obseg L5: L9.
Opomba: to je matrična formula, ki jo je treba vnesti s tipko control + shift + enter.
Pojasnilo
Srce te formule je osnovna formula INDEX in MATCH, ki se uporablja za prevajanje besedilnih vrednosti v številke, kot je določeno v iskalni tabeli. Na primer, za prevod "EX" v ustrezno številko bi uporabili:
=INDEX(value,MATCH("EX",code,0))
ki bi se vrnil 4.
Zasuk v tej težavi je, da želimo prevesti in sešteti obseg besedilnih vrednosti v stolpcih C do G v številke. To pomeni, da moramo navesti več kot eno iskalno vrednost, za vrnitev več kot enega rezultata pa potrebujemo INDEX. Standardni pristop je formula, kot je ta:
=SUM(INDEX(value,MATCH(C5:G5,code,0)))
Po zagonu MATCH imamo matriko s 5 elementi:
=SUM(INDEX(value,(2,2,3,2,5)))
Zdi se torej, da bi INDEX moral vrniti 5 rezultatov v SUM. Če poskusite to, bo funkcija INDEX vrnila samo en rezultat SUM. Če želimo, da INDEX vrne več rezultatov, moramo uporabiti precej nejasen trik in MATCH zaviti v N in IF tako:
N(IF(1,MATCH(C5:G5,code,0)))
To učinkovito prisili INDEX, da funkciji SUM posreduje več kot eno vrednost. Po zagonu INDEX imamo:
=SUM((3,3,2,3,-1))
In funkcija SUM vrne vsoto elementov v matriki, 10. Za dober opis tega vedenja si oglejte ta zanimiv članek na spletnem mestu EXCELXOR.
