Yhteenveto
Voit kääntää tekstiarvoja numeroiksi ja summata tuloksen käyttämällä INDEX- ja MATCH-kaavaa sekä SUM-funktiota. Esitetyssä esimerkissä kaava H5: ssä on:
(=SUM(INDEX(value,N(IF(1,MATCH(C5:G5,code,0))))))
missä "koodi" on nimetty alue K5: K9 ja "arvo" on nimetty alue L5: L9.
Huomaa: tämä on matriisikaava, ja se on syötettävä painamalla ohjainta + vaihto + enter.
Selitys
Tämän kaavan ydin on INDEX- ja MATCH-peruskaava, joita käytetään tekstiarvojen muuntamiseen numeroiksi hakutaulukossa määritetyllä tavalla. Esimerkiksi, jos haluat kääntää "EX" vastaavaksi numeroksi, käytämme:
=INDEX(value,MATCH("EX",code,0))
joka palauttaisi 4.
Tämän ongelman käänne on kuitenkin se, että haluamme kääntää ja summata sarakkeiden C - G tekstiarvojen alueen numeroiksi. Tämä tarkoittaa sitä, että meidän on annettava useampi kuin yksi hakuarvo, ja tarvitsemme INDEX-arvon palauttaaksesi useamman kuin yhden tuloksen. Tavallinen lähestymistapa on tällainen kaava:
=SUM(INDEX(value,MATCH(C5:G5,code,0)))
Kun MATCH on suoritettu, meillä on taulukko, jossa on 5 kohdetta:
=SUM(INDEX(value,(2,2,3,2,5)))
Joten näyttää siltä, että indeksin pitäisi palauttaa 5 tulosta SUM: lle. Jos kuitenkin yrität tätä, INDEX-toiminto palauttaa vain yhden tuloksen SUM. Jotta INDEX palauttaisi useita tuloksia, meidän on käytettävä melko hämärää temppua ja käärittävä MATCH N: ään ja IF: ään näin:
N(IF(1,MATCH(C5:G5,code,0)))
Tämä pakottaa INDEXin tarjoamaan enemmän kuin yhden arvon SUM-funktiolle. Indeksin suorittamisen jälkeen meillä on:
=SUM((3,3,2,3,-1))
Ja SUM-funktio palauttaa matriisin kohteiden summan, 10. Jos haluat kirjoittaa tämän käyttäytymisen hyvin, katso tämä mielenkiintoinen artikkeli EXCELXOR-verkkosivustolta.
