Téléchargez l'exemple de classeur
Ce didacticiel montrera comment trouver la somme des valeurs de texte où un code unique est attribué à chacune de ces valeurs de texte dans Excel et Google Sheets.
Numéros SUM stockés sous forme de texte
Tout d'abord, nous verrons comment additionner des nombres stockés ou formatés sous forme de texte.
Dans l'exemple suivant, la colonne Salaire est stockée sous forme de texte. Si vous essayez d'additionner les valeurs, Excel affichera un zéro.
| 1 | =SOMME(C3:C7) |
Au lieu de cela, pour effectuer l'opération SUM sur des nombres stockés sous forme de texte, vous pouvez utiliser une formule matricielle avec les fonctions SUM et VALUE comme ceci :
| 1 | =SOMME(VALEUR(C3:C7)) |
La fonction VALUE convertit un texte représentant un nombre en un nombre. La fonction SUM additionne ces nombres.
Dans Excel 365 et la version d'Excel plus récente que 2022, vous pouvez simplement entrer la formule comme d'habitude. Cependant, lorsque vous utilisez Excel 2022 et versions antérieures, vous devez entrer la formule matricielle en appuyant sur CTRL + MAJ + ENTRÉE (à la place de ENTRER), indiquant à Excel que la formule est une formule matricielle. Vous saurez que c'est une formule matricielle par les accolades qui apparaissent autour de la formule (voir l'image du haut). Dans les versions ultérieures d'Excel et d'Excel 365, vous pouvez simplement appuyer sur ENTRER au lieu.
Examinons l'explication suivante pour mieux comprendre la formule.
Fonction VALEUR
Utilisée comme formule matricielle, la fonction VALUE convertit toute la plage de nombres stockés sous forme de texte en un tableau de nombres et la renvoie comme entrée pour la fonction SUM.
| 1 | =SOMME({10000;6500;7500;15000;8000}) |
Pour voir ce que renvoie la fonction VALUE, sélectionnez la fonction requise et appuyez sur F9.
SOMME des valeurs de texte
Pour SOMME une plage de valeurs de texte où un code unique est attribué à chacune de ces valeurs de texte, une formule matricielle peut être utilisée.
Le tableau suivant enregistre ce que les gens sélectionnent pour trois questions données. Le tableau de droite répertorie les quatre réponses possibles auxquelles chacune a été attribuée une valeur de code. Nous devons additionner les codes pour chaque personne.
| 1 | =SOMME(INDICE(I$3:I$6,N(SI(1,MATCH(C4:E4,H$3:H$6,0)))))) |
Nous allons parcourir la formule ci-dessous.
Fonction MATCH
La fonction MATCH recherche un élément spécifié dans une plage et renvoie sa position relative dans cette plage. Sa syntaxe est :
Étant une formule matricielle dans cet exemple, voyons ce que renvoie la fonction MATCH.
| 1 | =SOMME(INDICE(I$3:I$6,N(SI(1,{1,3,2}))))) |
Pour une personne donnée, la fonction MATCH trouve la position relative de chaque réponse dans la plage H3:H6. Le résultat est un tableau de positions.
Noter: Dans une formule matricielle, pour voir ce qu'une fonction renvoie, sélectionnez la fonction requise et appuyez sur F9.
Fonction SI & N
La fonction IF et la fonction N utilisées ensemble renvoient le tableau suivant comme entrée pour la fonction INDEX.
| 1 | =SOMME(INDICE(I$3:I$6,{1,3,2})) |
Ici, les deux fonctions renvoient un tableau de position relative des réponses dans la plage H3:H6. Le but de l'utilisation des fonctions IF et N est d'effectuer un processus appelé déréférencement. En termes simples, les deux fonctions forcent la fonction INDEX à transmettre l'ensemble des valeurs de code à la fonction SUM.
Nous expliquons cela dans la section suivante.
Fonction INDEX
La fonction INDEX renvoie la valeur positionnée à l'intersection d'une ligne et d'une colonne spécifiées dans une plage. Sa syntaxe est :
Voyons comment cela fonctionne sous forme de formule matricielle :
| 1 | =SOMME({10,5,8}) |
La fonction INDEX trouve les valeurs de code dans la plage I3:I6 selon les numéros de position donnés. Il renvoie ensuite un tableau de valeurs, c'est-à-dire le code respectif pour chaque réponse, à la fonction SUM pour effectuer des opérations.
Assurez-vous que le nombre de lignes et de colonnes dans les deux Réponse et le Code la colonne est la même.
Fonction SOMME
La fonction SUM additionnera les valeurs de code renvoyées par la fonction INDEX.
| 1 | =SOMME({10,5,8}) |
Tout cela mis ensemble donne notre formule initiale :
| 1 | {=SOMME(INDICE(I$3:I$6,N(SI(1,MATCH(C4:E4,H$3:H$6,0))))))} |
SOMME des valeurs de texte - Sans fonctions IF & N
Cette section explique comment Excel répond si nous n'utilisons pas les fonctions SI et N dans la formule mentionnée ci-dessus.
Le même exemple est utilisé avec les mêmes codes et réponses.
| 1 | {=SOMME(INDICE(I$3:I$6,MATCH(C4:E4,H$3:H$6,0)))} |
Comme vous pouvez le voir, la fonction INDEX ne transmet que le code de la première réponse à la fonction SOMME. Si vous scrutez sur la fonction INDEX en appuyant sur F9 vous obtiendrez les éléments suivants :
La valeur! Une erreur est renvoyée car la fonction INDEX ne peut pas lire le tableau de numéros de ligne sous forme de tableau. Par conséquent, l'utilisation de la fonction SI et N fait l'affaire.
Noter: Dans Excel 365, vous pouvez ignorer complètement l'utilisation des fonctions IF et N.
Somme du texte - Google Sheets
Ces formules fonctionnent de la même manière dans Google Sheets que dans Excel.
