Santrauka
Norėdami išversti teksto reikšmes į skaičius ir susumuoti rezultatą, galite naudoti INDEX ir MATCH formules bei SUM funkciją. Parodytame pavyzdyje formulė H5 yra:
(=SUM(INDEX(value,N(IF(1,MATCH(C5:G5,code,0))))))
kur „kodas“ yra pavadintas diapazonas K5: K9, o „reikšmė“ - pavadintas diapazonas L5: L9.
Pastaba: tai yra masyvo formulė, kurią reikia įvesti su „Control“ + „Shift“ + „Enter“.
Paaiškinimas
Šios formulės esmė yra pagrindinė „INDEX“ ir „MATCH“ formulė, naudojama teksto vertėms paversti skaičiais, kaip apibrėžta paieškos lentelėje. Pavyzdžiui, norėdami išversti „EX“ į atitinkamą skaičių, mes naudotume:
=INDEX(value,MATCH("EX",code,0))
kuris grąžintų 4.
Tačiau šios problemos posūkis yra tas, kad mes norime išversti ir suskaičiuoti teksto reikšmių diapazoną nuo C iki G stulpelių į skaičius. Tai reiškia, kad turime pateikti daugiau nei vieną paieškos vertę, o norint rodyti daugiau nei vieną rezultatą, reikia INDEX. Standartinis metodas yra tokia formulė:
=SUM(INDEX(value,MATCH(C5:G5,code,0)))
Pasibaigus MATCH, turime masyvą su 5 elementais:
=SUM(INDEX(value,(2,2,3,2,5)))
Taigi panašu, kad INDEX turėtų grąžinti 5 rezultatus į SUM. Tačiau jei tai bandysite, funkcija INDEX pateiks tik vieną rezultatą SUM. Norėdami, kad INDEX grąžintų kelis rezultatus, turime naudoti gana neaiškų triuką ir apvynioti MATCH N ir IF taip:
N(IF(1,MATCH(C5:G5,code,0)))
Tai iš tikrųjų priverčia INDEX teikti daugiau nei vieną reikšmę SUM funkcijai. Paleidus INDEX, turime:
=SUM((3,3,2,3,-1))
Funkcija SUM pateikia masyvo elementų sumą 10. Norėdami gerai parašyti apie šį elgesį, skaitykite šį įdomų straipsnį EXCELXOR svetainėje.
