Kokkuvõte
Tekstiväärtuste numbriteks teisendamiseks ja tulemuse summeerimiseks võite kasutada valemit INDEX ja MATCH ning funktsiooni SUM. Näidatud näites on H5 valem:
(=SUM(INDEX(value,N(IF(1,MATCH(C5:G5,code,0))))))
kus "kood" on nimega vahemik K5: K9 ja "väärtus" on nimega vahemik L5: L9.
Märkus: see on massiivi valem ja see tuleb sisestada juhtklahviga + shift + enter.
Selgitus
Selle valemi keskmes on põhivalem INDEX ja MATCH, mida kasutatakse tekstiväärtuste teisendamiseks numbriteks, nagu on määratletud otsingutabelis. Näiteks "EX" tõlkimiseks vastavale numbrile kasutaksime järgmist:
=INDEX(value,MATCH("EX",code,0))
mis tagastaks 4.
Selle probleemi keerdkäik on aga see, et me tahame veergudes C – G olevate tekstiväärtuste vahemiku arvudeks ümber arvutada. See tähendab, et peame esitama mitu otsinguväärtust ja rohkem kui ühe tulemuse tagastamiseks vajame INDEX-i. Standardne lähenemisviis on selline valem:
=SUM(INDEX(value,MATCH(C5:G5,code,0)))
Pärast MATCHi käivitamist on meil massiiv 5 üksusega:
=SUM(INDEX(value,(2,2,3,2,5)))
Seega näib, et INDEX peaks tagastama SUM-ile 5 tulemust. Kui proovite seda, tagastab funktsioon INDEX ainult ühe tulemuse SUM. Kui soovite, et INDEX tagastaks mitu tulemust, peame kasutama üsna ebaselget trikki ja pakkima MATCH N-i ja IF-i järgmiselt:
N(IF(1,MATCH(C5:G5,code,0)))
See sunnib INDEXi pakkuma funktsioonile SUM rohkem kui ühte väärtust. Pärast INDEXi käivitamist on meil:
=SUM((3,3,2,3,-1))
Ja funktsioon SUM tagastab massiivi üksuste summa 10. Selle käitumise hea kirjutamiseks vaadake seda huvitavat artiklit EXCELXORi veebisaidil.
