Sommaire
Pour traduire des valeurs de texte en nombres et additionner le résultat, vous pouvez utiliser une formule INDEX et MATCH et la fonction SOMME. Dans l'exemple illustré, la formule en H5 est:
(=SUM(INDEX(value,N(IF(1,MATCH(C5:G5,code,0))))))
où "code" est la plage nommée K5: K9 et "valeur" est la plage nommée L5: L9.
Remarque: il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée.
Explication
Le cœur de cette formule est une formule de base INDEX et MATCH, utilisée pour traduire des valeurs de texte en nombres tels que définis dans une table de recherche. Par exemple, pour traduire "EX" en nombre correspondant, nous utiliserions:
=INDEX(value,MATCH("EX",code,0))
ce qui reviendrait 4.
La torsion de ce problème est cependant que nous voulons traduire et additionner une plage de valeurs de texte dans les colonnes C à G en nombres. Cela signifie que nous devons fournir plus d'une valeur de recherche et que INDEX renvoie plus d'un résultat. L'approche standard est une formule comme celle-ci:
=SUM(INDEX(value,MATCH(C5:G5,code,0)))
Après l'exécution de MATCH, nous avons un tableau avec 5 éléments:
=SUM(INDEX(value,(2,2,3,2,5)))
Il semble donc que INDEX devrait renvoyer 5 résultats à SUM. Toutefois, si vous essayez cela, la fonction INDEX renverra un seul résultat SUM. Pour que INDEX renvoie plusieurs résultats, nous devons utiliser une astuce plutôt obscure et envelopper MATCH en N et IF comme ceci:
N(IF(1,MATCH(C5:G5,code,0)))
Cela force effectivement INDEX à fournir plusieurs valeurs à la fonction SOMME. Après l'exécution d'INDEX, nous avons:
=SUM((3,3,2,3,-1))
Et la fonction SOMME renvoie la somme des éléments du tableau, 10. Pour un bon article sur ce comportement, consultez cet article intéressant sur le site Web EXCELXOR.
