Sažetak
Za prevođenje tekstualnih vrijednosti u brojeve i zbrajanje rezultata možete upotrijebiti formulu INDEX i MATCH i funkciju SUM. U prikazanom primjeru formula u H5 je:
(=SUM(INDEX(value,N(IF(1,MATCH(C5:G5,code,0))))))
gdje je "kod" imenovani raspon K5: K9, a "vrijednost" imenovani raspon L5: L9.
Napomena: ovo je formula niza i mora se unijeti sa control + shift + enter.
Obrazloženje
Srce ove formule je osnovna formula INDEX i MATCH, koja se koristi za prevođenje tekstualnih vrijednosti u brojeve kako je definirano u tablici pretraživanja. Na primjer, za prijevod "EX" na odgovarajući broj koristili bismo:
=INDEX(value,MATCH("EX",code,0))
koja bi se vratila 4.
Zaokret u ovom problemu je u tome što želimo prevesti i zbrojiti niz tekstnih vrijednosti u stupcima C do G u brojeve. To znači da trebamo pružiti više od jedne vrijednosti pretraživanja, a INDEX nam treba za vraćanje više rezultata. Standardni pristup je formula poput ove:
=SUM(INDEX(value,MATCH(C5:G5,code,0)))
Nakon pokretanja MATCH-a imamo niz s 5 predmeta:
=SUM(INDEX(value,(2,2,3,2,5)))
Stoga se čini da bi INDEX trebao vratiti 5 rezultata u ZUM. Međutim, ako pokušate ovo, funkcija INDEX vratit će samo jedan rezultat SUM. Da bi INDEX vratio više rezultata, moramo se poslužiti prilično nejasnim trikom i umotati MATCH u N i IF ovako:
N(IF(1,MATCH(C5:G5,code,0)))
To učinkovito prisiljava INDEX da pruži više od jedne vrijednosti funkciji SUM. Nakon pokretanja INDEX-a imamo:
=SUM((3,3,2,3,-1))
A funkcija SUM vraća zbroj stavki u polju 10. Za dobar opis ovog ponašanja pogledajte ovaj zanimljiv članak na web mjestu EXCELXOR.
