rezumat
Pentru a traduce valorile textului în cifre și a rezuma rezultatul, puteți utiliza o formulă INDEX și MATCH și funcția SUM. În exemplul prezentat, formula din H5 este:
(=SUM(INDEX(value,N(IF(1,MATCH(C5:G5,code,0))))))
unde „cod” este intervalul numit K5: K9, iar „valoare” este intervalul numit L5: L9.
Notă: aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter.
Explicaţie
Inima acestei formule este o formulă de bază INDEX și MATCH, utilizată pentru a traduce valorile textului în numere definite într-un tabel de căutare. De exemplu, pentru a traduce „EX” la numărul corespunzător, am folosi:
=INDEX(value,MATCH("EX",code,0))
care ar reveni 4.
Cu toate acestea, răsucirea acestei probleme este că dorim să traducem și să însumăm o serie de valori de text din coloanele C până la G la numere. Aceasta înseamnă că trebuie să furnizăm mai multe valori de căutare și avem nevoie de INDEX pentru a returna mai multe rezultate. Abordarea standard este o formulă ca aceasta:
=SUM(INDEX(value,MATCH(C5:G5,code,0)))
După executarea MATCH, avem o matrice cu 5 articole:
=SUM(INDEX(value,(2,2,3,2,5)))
Deci, se pare că INDEX ar trebui să returneze 5 rezultate la SUM. Cu toate acestea, dacă încercați acest lucru, funcția INDEX va returna un singur rezultat SUM. Pentru a obține INDEX să returneze mai multe rezultate, trebuie să folosim un truc destul de obscur și să înfășurăm MATCH în N și IF așa:
N(IF(1,MATCH(C5:G5,code,0)))
Acest lucru forțează efectiv INDEX să furnizeze mai mult de o valoare funcției SUM. După executarea INDEX, avem:
=SUM((3,3,2,3,-1))
Iar funcția SUM returnează suma articolelor din matrice, 10. Pentru o scriere bună despre acest comportament, consultați acest articol interesant de pe site-ul EXCELXOR.
