Nous ne pouvons pas additionner les valeurs textuelles dans Google Sheets ou dans toute autre application de tableur sans attribuer des valeurs numériques aux textes.
Par exemple, j’ai un tableau avec des évaluations d’employés en texte. Les textes utilisés pour les évaluations sont « Exceptionnelle », « Très bien », « Bien », « Correcte » et « Très mauvaise ».
Chacune de ces valeurs textuelles a des scores respectifs de 5, 4, 3, 2 et 1.
Tableau #1:
Nom | Test 1 | Test 2 | Test 3Voyons comment attribuer des scores aux 5 types de valeurs textuelles ci-dessus et additionner les valeurs textuelles dans Google Sheets. J’utilise les fonctions Vlookup et Sum pour cela.
Si vous souhaitez additionner les valeurs textuelles comme des nombres dans chaque ligne de Google Sheets, vous pouvez utiliser la formule de copier-coller Vlookup + Sum ou une formule de tableau Vlookup + Mmult.
La méthode la plus simple pour additionner des valeurs textuelles basées sur des chiffres attribués dans Google Sheets est d’utiliser la formule Vlookup + Sum sans tableau. Je vais donc commencer par cela.
Logique de la formule et préparation de la feuille de calcul
Il y a deux étapes dans la formule. La compréhension de celles-ci vous aidera à écrire la formule très facilement. Elles sont :
- Attribution de scores/numéros aux textes – Vlookup
- Addition de valeurs textuelles – Sum(Vlookup)
Pour additionner des valeurs textuelles, nous devons d’abord attribuer des scores aux textes. Comment faire cela ?
Le Vlookup se charge d’attribuer les chiffres/scores aux valeurs textuelles. Pour cela, vous devez d’abord créer un tableau (appelé « range » dans le Vlookup).
Ce tableau contient les valeurs textuelles dans une colonne et leurs scores correspondants. Consultez ce tableau qui servira de « range » dans le Vlookup.
Tableau #2:
Description | ScoreNotre tâche est d’attribuer les scores du Tableau #2 aux valeurs correspondantes du Tableau #1 et d’obtenir la somme dans la colonne Total du Tableau #1.
Formule non-matrice pour additionner des valeurs textuelles dans Google Sheets
Vlookup pour attribuer des scores aux valeurs textuelles correspondantes
Comme je l’ai mentionné, la première étape pour additionner des valeurs textuelles de la même manière que des nombres est d’attribuer des nombres aux valeurs textuelles.
Le Vlookup suivant le fera.
=ArrayFormula(vlookup(D3:F3,$A$3:$B$7,2,0))Le Vlookup prend plusieurs clés de recherche de D3:F3, c’est pourquoi j’ai utilisé la fonction ArrayFormula avec Vlookup.
La « range » dans Vlookup est $A$3:$B$7 et la colonne d’index (sortie) est la colonne 2 dans $A$3:$B$7.
Le Vlookup ci-dessus attribue les scores 5 au texte « Exceptionnelle », 3 à « Bien » et encore 5 à « Exceptionnelle ».
Somme des scores attribués par Vlookup
Le Vlookup se trouve dans la cellule G3. Utilisez simplement la fonction Sum pour additionner les nombres retournés par Vlookup.
Ensuite, faites glisser la formule vers le bas.
=ArrayFormula(SUM(vlookup(D3:F3,$A$3:$B$7,2,0)))De cette façon, vous pouvez additionner des valeurs textuelles après leur avoir attribué des scores dans Google Sheets.
Remarque : Il peut y avoir une chance d’erreur #N/A! avec la formule ci-dessus en cas de faute de frappe/cellule vide dans le Tableau #1.
Pour éviter cela, incluez IFNA avec la formule ci-dessus. Vous devez insérer IFNA avant le SUM.
=ArrayFormula(SUM(IFNA(vlookup(D3:F3,$A$3:$B$7,2,0))))Formule matricielle pour additionner des valeurs textuelles dans Google Sheets
Avec quelques ajustements, nous pouvons transformer la formule ci-dessus en une formule matricielle.
Le premier changement concerne le Vlookup. Dans l’exemple ci-dessus, la clé de recherche provient du tableau D3:F3. Modifiez-le en D3:F12.
=ArrayFormula(vlookup(D3:F12,$A$3:$B$7,2,0))Ici aussi, vous devez envelopper la sortie de la formule Vlookup avec IFNA pour retourner 0 en cas d’erreur #N/A! Ceci est nécessaire pour que MMULT fonctionne correctement.
=ArrayFormula(ifna(vlookup(D3:F12,$A$3:$B$7,2,0),0))MMULT est utilisée pour retourner le produit matriciel de deux matrices, n’est-ce pas ?
Syntaxe : MMULT(matrice1, matrice2)
La formule ci-dessus est « matrice1 ». Nous ne voulons pas calculer le produit de deux matrices et nous n’avons pas réellement de « matrice2 ».
Générons donc « matrice2 » en utilisant la formule Sequence suivante.
=ArrayFormula(sign(sequence(3,1)))Cette formule renverrait le nombre 1 trois fois dans une colonne (sur 3 lignes). Vous pouvez le tester sur votre feuille.
Le nombre de colonnes dans « matrice1 » est de 3. Donc en standard, « matrice2 » doit contenir un nombre équivalent de lignes. Nous avons maintenant des tailles de matrices compatibles dans MMULT.
Si vous avez plus de colonnes dans votre Tableau #1, modifiez la formule Sequence en conséquence.
Voici la formule matricielle dans la cellule G2 pour additionner des valeurs textuelles comme des nombres dans Google Sheets.
=ArrayFormula(mmult(ifna(vlookup(D3:F12,$A$3:$B$7,2,0),0),sequence(3,1)^0))Vous voulez utiliser une plage ouverte, comme D3:F au lieu de D3:F12 ?
Lisez ce guide – Utilisation appropriée de MMULT avec des lignes infinies dans Google Sheets.
Merci de nous avoir accompagnés. Profitez-en bien!
