Összegzés
A szöveges értékek számokká történő lefordításához és az eredmény összegzéséhez használhatja az INDEX és MATCH képletet, valamint a SUM függvényt. A bemutatott példában a H5 képlete a következő:
(=SUM(INDEX(value,N(IF(1,MATCH(C5:G5,code,0))))))
ahol a "kód" a megnevezett tartomány K5: K9, és az "érték" az elnevezett tartomány L5: L9.
Megjegyzés: ez egy tömbképlet, amelyet a control + shift + enter billentyűkombinációval kell megadni.
Magyarázat
Ennek a képletnek a szíve egy alap INDEX és MATCH képlet, amelyet a szöveges értékek számokká történő fordítására használnak, a keresési táblázatban meghatározottak szerint. Például az "EX" lefordításához a megfelelő számra a következőket használjuk:
=INDEX(value,MATCH("EX",code,0))
ami 4-et adna vissza.
Ennek a problémának az a csavarja, hogy a C – G oszlopokban lévő szövegértékek tartományát számokká akarjuk fordítani és összegezni. Ez azt jelenti, hogy egynél több keresési értéket kell megadnunk, és az INDEX-re is szükségünk van, ha egynél több eredményt adunk vissza. A standard megközelítés egy ilyen képlet:
=SUM(INDEX(value,MATCH(C5:G5,code,0)))
A MATCH futtatása után van egy tömbünk 5 elemmel:
=SUM(INDEX(value,(2,2,3,2,5)))
Úgy tűnik tehát, hogy az INDEX-nek 5 eredményt kell visszaadnia a SUM-nak. Ha azonban megpróbálja ezt, az INDEX függvény csak egy eredményt ad vissza. Ahhoz, hogy az INDEX több eredményt adjon vissza, meglehetősen homályos trükköt kell használnunk, és a MATCH-ot N-be és IF-be kell burkolnunk:
N(IF(1,MATCH(C5:G5,code,0)))
Ez gyakorlatilag arra kényszeríti az INDEX-et, hogy egynél több értéket adjon meg a SUM függvénynek. Az INDEX futása után:
=SUM((3,3,2,3,-1))
A SUM függvény pedig a tömbben szereplő elemek összegét adja vissza, 10. A viselkedés megfelelő írásához olvassa el ezt az érdekes cikket az EXCELXOR webhelyen.
