Excel a trois caractères génériques pour les critères et les recherches : * correspond à un nombre quelconque de caractères (zéro compris), ? correspond à exactement un caractère, et ~ retransforme le * ou le ? suivant en caractère ordinaire. =NB.SI(A2:A7;"*apple*") compte les cellules qui contiennent apple n'importe où. Le tableau affiche les formules sous leur forme anglaise (COUNTIF pour NB.SI), avec des virgules, mais vous pouvez aussi les taper comme dans un Excel français, avec des points-virgules.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Pattern | Count | |
| 2 | Apple juice | *apple* | 4 | |
| 3 | Green apple | apple* | 2 | |
| 4 | Pineapple | *juice | 2 | |
| 5 | Orange juice | ????? | 0 | |
| 6 | Pear | *e | 4 | |
| 7 | Apples |
=NB.SI($A$2:$A$7;C2)*apple*contientapple: 4 correspondances, carPineapplecompte aussi.apple*commence parapple: seulementApple juiceetApples. NB.SI ignore la casse.*juicese termine parjuice.?????fait exactement cinq caractères. Aucun de ces produits n'a cinq caractères, donc 0 ; tapezPeachen A6 et le compte passe à 1.*ese termine pare.
Tapez votre propre modèle dans la colonne C, comme *an* ou P*, et le compte se met à jour.
Correspondance partielle dans RECHERCHEV et RECHERCHEX
RECHERCHEV (VLOOKUP en anglais) accepte les caractères génériques en mode correspondance exacte (FAUX comme dernier argument). Collez le caractère générique à la valeur dans la formule, pour que seules les premières lettres aillent en D2 :
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Price | Starts with | Price | |
| 2 | Apple juice | 3.5 | Pin | 4 | |
| 3 | Green apple | 1.2 | 4 | ||
| 4 | Pineapple | 4 | |||
| 5 | Orange juice | 3.2 | |||
| 6 | Pear | 0.9 |
=RECHERCHEV(D2&"*";A2:B6;2;FAUX)Les deux formules trouvent Pineapple. Remplacez D2 par juice : le modèle juice* de RECHERCHEV exige que le texte commence par juice et renvoie #N/A, tandis que le modèle *juice* de RECHERCHEX trouve le premier produit qui le contient, Apple juice. Comme toute correspondance exacte, une recherche avec caractère générique renvoie la première ligne qui convient, alors rendez le modèle assez précis.
RECHERCHEX ne traite * et ? comme des caractères génériques que si son cinquième argument, match_mode, vaut 2. Sans lui, elle cherche l'astérisque littéralement. EQUIV accepte les caractères génériques avec un type de correspondance 0, et EQUIVX avec match_mode 2, comme RECHERCHEX. Voir RECHERCHEV pour ses autres arguments.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Phone products | ||
| 2 | Smartphone | |||
| 3 | Headphones | |||
| 4 | Phone stand | |||
| 5 | Laptop bag | |||
| 6 | Charger |
À vous : En D2, comptez les produits dont le nom contient phone n'importe où.
Somme et moyenne avec un caractère générique
Toutes les fonctions qui prennent des critères les lisent de la même façon, donc les mêmes modèles fonctionnent dans SOMME.SI, SOMME.SI.ENS, MOYENNE.SI, MOYENNE.SI.ENS, MAX.SI.ENS et MIN.SI.ENS :
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | Pattern | Total | |
| 2 | North-East | 120 | North* | 285 | |
| 3 | North-West | 95 | *West | 155 | |
| 4 | South | 80 | ????? | 150 | |
| 5 | South-West | 60 | |||
| 6 | East | 110 | |||
| 7 | North | 70 |
=SOMME.SI(A2:A7;D2;B2:B7)North* additionne chaque région qui commence par North, y compris North lui-même, car * correspond aussi à rien. ????? additionne les régions d'exactement cinq caractères : South et North.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | West total | ||
| 2 | North-East | 120 | |||
| 3 | North-West | 95 | |||
| 4 | South | 80 | |||
| 5 | South-West | 60 | |||
| 6 | East | 110 | |||
| 7 | North | 70 |
À vous : En E2, totalisez les ventes de chaque région dont le nom se termine par West.
Trouver un vrai astérisque ou point d'interrogation avec ~
Pour compter un texte qui contient un vrai * ou ?, placez un tilde devant. Le tilde lui-même s'écrit ~~.
| A | B | C | |
|---|---|---|---|
| 1 | Note | Count | |
| 2 | Rated 5* | 1 | |
| 3 | Why? | 1 | |
| 4 | Done | 4 | |
| 5 | 5 stars |
=NB.SI(A2:A5;"*~**")C2 compte les cellules qui contiennent un astérisque littéral (A2 seulement), C3 celles qui contiennent un point d'interrogation. C4 montre l'autre face : "*" seul correspond à n'importe quel texte, donc il compte chaque cellule de texte, 4 ici. Il ignore les nombres et les cellules vides, ce qui fait de NB.SI(plage;"*") la méthode habituelle pour compter les cellules de texte.
Quelles fonctions acceptent les caractères génériques
Acceptent *, ?, ~ | N'acceptent pas |
|---|---|
| NB.SI, NB.SI.ENS, SOMME.SI, SOMME.SI.ENS, MOYENNE.SI, MOYENNE.SI.ENS, MAX.SI.ENS, MIN.SI.ENS | =, <> et les autres comparaisons |
| RECHERCHEV et RECHERCHEH avec FAUX | SI seule |
| EQUIV avec 0 | TROUVE |
| RECHERCHEX et EQUIVX avec match_mode 2 | FILTRE, UNIQUE, TRIER |
| CHERCHE | SUBSTITUE, TEXTE.AVANT, TEXTE.APRES |
| Rechercher et remplacer (Ctrl+H), zones de filtre |
CHERCHE accepte les caractères génériques dans une formule : =CHERCHE("b?d";"a bad day") renvoie 3 (TROUVE et CHERCHE donne les détails). Pour FILTRE, utilisez ESTNUM(CHERCHE(...)) comme condition au lieu d'un modèle.
Erreur fréquente : un caractère générique après =
L'opérateur = ne lit jamais les caractères génériques : =A2="*apple*" demande si A2 contient les sept caractères *apple*. Dans Excel, avec Green apple en A2 :
=A2="*apple*" FALSE
=IF(A2="*apple*","yes","no") no
Placez plutôt le test dans NB.SI, qui renvoie 1 ou 0 pour une seule cellule, ou utilisez CHERCHE :
| A | B | C | |
|---|---|---|---|
| 1 | Product | COUNTIF test | SEARCH test |
| 2 | Green apple | contains apple | contains apple |
| 3 | Pear | no | no |
=SI(NB.SI(A2;"*apple*");"contains apple";"no")SI traite le 1 renvoyé par NB.SI comme VRAI et le 0 comme FAUX. La version avec CHERCHE n'a besoin d'aucun caractère générique, car CHERCHE cherche déjà le texte n'importe où dans la cellule.
Questions fréquentes
Quels sont les caractères génériques dans Excel ?
* correspond à un nombre quelconque de caractères, zéro compris ; ? correspond à exactement un caractère ; ~ devant *, ? ou ~ en fait un caractère ordinaire. "*apple*" signifie contient apple, "A*" commence par A, "???" exactement trois caractères.
Comment utiliser un caractère générique dans RECHERCHEV ?
Collez le caractère générique à la valeur cherchée et utilisez la correspondance exacte : =RECHERCHEV(E2&"*";A2:B6;2;FAUX) trouve la première entrée qui commence par E2. Dans RECHERCHEX, mettez match_mode à 2 : =RECHERCHEX("*"&E2&"*";A2:A6;B2:B6;"none";2).
Pourquoi un caractère générique ne fonctionne-t-il pas dans ma formule SI ?
La comparaison = ne comprend pas les caractères génériques, donc =SI(A2="*apple*";...) ne trouve que le texte littéral *apple*. Utilisez =SI(NB.SI(A2;"*apple*");"Yes";"No") ou =SI(ESTNUM(CHERCHE("apple";A2));"Yes";"No").
Comment compter les cellules qui contiennent un astérisque ?
Placez un tilde devant : =NB.SI(A2:A10;"*~**"). Le premier et le dernier * sont des caractères génériques, et ~* est un astérisque littéral.
