- Somme si le texte contient
- En utilisant le ? Caractère générique
- Utilisation du caractère ~ (Tilde)
- Combinaison de caractères génériques SUMIFS avec des références de cellule
- Verrouillage des références de cellule
- Somme si la cellule contient du texte spécifique à l'aide de caractères génériques dans Google Sheets
Téléchargez l'exemple de classeur
Ce didacticiel montrera comment utiliser la fonction SUMIFS et les caractères génériques pour additionner les données correspondant aux cellules contenant du texte spécifique dans Excel et Google Sheets.
Somme si le texte contient
Tout d'abord, nous montrerons comment additionner les données liées aux cellules contenant du texte spécifique à l'aide de la fonction SUMIFS.
La fonction SUMIFS additionne les lignes de données qui répondent à certains critères. Sa syntaxe est :
Cet exemple résumera tout Notes avec un Nom d'état qui contient "Dakota" en utilisant la fonction SUMIFS et le caractère générique *.
| 1 | =SUMIFS(C3:C9,B3:B9,"*Dakota*") |
Le caractère * permet à n'importe quel nombre (y compris zéro) d'autres caractères de prendre sa place.
Dans cet exemple, il est utilisé pour rechercher toutes les cellules contenant le texte "Dakota". Cette recherche n'est pas sensible à la casse, donc « dakota » est considéré comme identique à « Dakota » ou « DAKOTA ». Dakota du nord et Dakota du Sud contiennent « Dakota » et sont donc inclus dans la somme.
Somme si le texte commence par
Le caractère * peut également être utilisé pour rechercher des cellules commençant par le texte spécifié :
| 1 | =SUMIFS(C3:C9,B3:B9,"Nouveau*") |
New York, New Jersey, et Nouveau Mexique commencent par « Nouveau » et sont donc inclus dans la somme. Notez qu'avec le terme de recherche « Nouveau* », la cellule de texte doit commencer par « Nouveau »; le simple fait de contenir ces caractères ne suffit pas.
Somme si le texte se termine par
De même, on peut additionner tous Notes pour États se terminant par « o » en utilisant :
| 1 | =SUMIFS(C3:C9,B3:B9,"*o") |
Nouveau Mexique et Ohio se terminent par « o » et sont donc inclus dans la somme.
En utilisant le ? Caractère générique
Les ? peut être utilisé pour représenter n'importe quel caractère dans une chaîne de texte.
Cet exemple trouve tout Noms d'état commençant par « Nouveau », suivi d'exactement 7 caractères (espaces compris).
| 1 | =SUMIFS(C3:C9,B3:B9,"Nouveau???????") |
New Jersey et Nouveau Mexique répondre à ces critères, mais New York pas, car il n'y a que 5 caractères après "Nouveau" dans New York.
Notez que * et ? les caractères génériques peuvent être combinés si nécessaire pour effectuer des commandes de recherche très spécifiques. Cet exemple suivant trouve Noms d'état commençant par « N » et contenant un « o » avant le dernier caractère de la chaîne. Cela exclut Nouveau Mexique; il commence par "N" mais n'a pas de "o" avant le dernier caractère.
| 1 | =SUMIFS(C3:C9,B3:B9,"N*o?*") |
Cette fonctionnalité est particulièrement utile lors de la recherche de codes de produits, de codes postaux ou de numéros de série où la position de chaque caractère a une signification spécifique.
Utilisation du caractère ~ (Tilde)
Le caractère spécial ~ (appelé tilde) nous permet de traiter le * ou ? comme s'il s'agissait de simples valeurs de texte et ne se comportent pas comme des caractères génériques.
Dans l'exemple ci-dessous, nous devons additionner les Niveau de stock quand le Nom du produit correspond spécifiquement au texte « Produit ? » :
| 1 | =SUMIFS(C3:C8,B3:B8,"Produit ~?") |
Le ~ immédiatement avant un * ou un ? caractère le retransforme en valeur de texte, donc le terme de recherche « Produit ~ ? » est utilisé pour trouver une correspondance de texte exacte avec "Produit ?".
Combinaison de caractères génériques SUMIFS avec des références de cellule
Habituellement, il n'est pas recommandé de coder en dur des valeurs dans des formules. Au lieu de cela, il est plus flexible d'utiliser des cellules séparées pour spécifier des valeurs pour nos termes de recherche.
Pour rechercher si les cellules contiennent le texte contenu dans la cellule E3, nous pouvons utiliser la fonction SUMIFS avec une référence de cellule et des caractères génériques * :
| 1 | =SUMIFS(C3:C9,B3:B9,"*"&E3&"*") |
Notez que le texte "Dakota" a été remplacé par la référence de cellule &E3& et les caractères * ont été mis entre guillemets ("").
Plusieurs références de cellules et caractères génériques peuvent également être combinés. Trouver Noms d'état qui commencent par le texte de la cellule E3 et contiennent le texte de la cellule F3 suivi d'au moins 1 caractère supplémentaire, la formule suivante peut être utilisée :
| 1 | =SUMIFS(C3:C9,B3:B9,E3&"*"&F3&"?*") |
Verrouillage des références de cellule
Pour rendre nos formules plus faciles à lire, nous avons montré les formules sans références de cellules verrouillées :
| 1 | =SUMIFS(C3:C9,B3:B9,"*"&E3&"*") |
Mais ces formules ne fonctionneront pas correctement si elles sont copiées et collées ailleurs dans votre fichier. Au lieu de cela, vous devez utiliser des références de cellules verrouillées comme ceci :
| 1 | =SUMIFS($C$3:$C$9,$B$3:$B$9,"*"&E3&"*") |
Lisez notre article sur le verrouillage des références de cellules pour en savoir plus.
Somme si la cellule contient du texte spécifique à l'aide de caractères génériques dans Google Sheets
Ces formules fonctionnent exactement de la même manière dans Google Sheets que dans Excel.
