Caractères génériques Excel : Un guide avec des exemples

Les caractères génériques d'Excel sont des symboles spéciaux qui aident les utilisateurs à effectuer des correspondances partielles et des opérations de données flexibles sur de grands ensembles de données. Bien qu'Excel ne prenne pas en charge toutes les fonctionnalités des expressions rationnelles, vous pouvez utiliser des caractères génériques pour structurer les formules de manière créative (fonctions telles que SEARCH(), TEXT(), et ISNUMBER()).

Il est plus facile d'acquérir des compétences en matière de caractères génériques lorsque vous avez acquis de solides connaissances de base sur Excel. Notre cours d'introduction à Excel vous permet d'acquérir des compétences essentielles, notamment la navigation dans l'interface, les références de cellules et les formules de base, créant ainsi la base dont vous avez besoin pour mettre en œuvre efficacement les techniques de caractères génériques abordées dans ce guide.

Que sont les caractères génériques d'Excel ?

Les jokers sont des symboles spéciaux qui remplacent les caractères d'une chaîne de texte, ce qui vous permet de rechercher des motifs plutôt que des correspondances exactes. Excel reconnaît trois caractères génériques, chacun ayant une fonction distincte dans la recherche de motifs et les opérations sur le texte.

L'astérisque (*)

L'astérisque (*) correspond à n'importe quel nombre de caractères, y compris aucun. Par exemple, App* correspondrait à "Apple", "Application", "App" et "Approach".

Ce caractère générique est utile lorsque vous connaissez le début d'une chaîne de texte mais que vous souhaitez capturer toutes les variations qui suivent.

Le point d'interrogation ( ?)

Le point d'interrogation (?) correspond à un seul caractère. L'utilisation de B?t correspondrait à "Bat", "Bet", "Bit" et "But", mais pas à "Boat" ou "Boot".

Cette précision en fait un outil idéal pour les situations où vous connaissez la longueur et le nombre de caractères de votre texte cible.

Le tilde(~)

Le tilde (~) sert de caractère d'échappement, ce qui vous permet de rechercher des astérisques ou des points d'interrogation dans vos données. Lorsque vous devez rechercher un texte contenant littéralement les caractères * ou ?, le fait de les faire précéder d'un tilde indique à Excel de les traiter comme des caractères normaux plutôt que comme des caractères génériques.

Par exemple, ~*Sale correspondrait au texte littéral "*Sale" plutôt que de traiter l'astérisque comme un caractère générique.

Utilisation de caractères génériques dans les formules Excel

Plusieurs fonctions Excel prennent en charge les caractères génériques, ce qui les rend utiles pour les calculs conditionnels et les opérations de recherche. Les fonctions les plus couramment utilisées qui acceptent les caractères génériques sont COUNTIF(), COUNTIFS(), SUMIF(), SUMIFS(), AVERAGEIF(), AVERAGEIFS(), SEARCH(), VLOOKUP(), HLOOKUP(), XLOOKUP(), MATCH(), et XMATCH().

Chacune de ces fonctions peut utiliser des caractères génériques pour effectuer des recherches de motifs flexibles, ce qui vous permet de travailler avec des correspondances de données partielles et de gérer efficacement les variations dans vos ensembles de données.

COUNTIF() avec caractères génériques

La fonction COUNTIF() devient particulièrement utile lorsqu'elle est combinée à des caractères génériques pour compter les cellules qui correspondent à des modèles spécifiques. Voici un exemple simple :

=COUNTIF(A2:A12, "*AA*")

Cette formule compte toutes les cellules de la plage A2:A12 qui contiennent "AA" n'importe où dans le texte. Les astérisques avant et après "AA" indiquent à Excel de faire correspondre tous les caractères avant ou après cette séquence.

Pour les critères dynamiques, vous pouvez faire référence à une cellule contenant votre motif de joker :

=COUNTIF(A2:A12, E1)

Dans ce cas, la cellule E1 peut contenir "*AA*" ou tout autre motif joker. Cette approche vous permet de modifier vos critères de recherche sans modifier la formule elle-même, ce qui rend votre feuille de calcul plus flexible et plus conviviale.

VLOOKUP() et XLOOKUP() avec caractères génériques

Les fonctions de recherche peuvent utiliser des caractères génériques pour les correspondances partielles, ce qui est utile lorsque vos valeurs de recherche ne sont pas des correspondances exactes. Voici des exemples de ces deux approches :

=VLOOKUP("John*", A:C, 2, FALSE)

Cette formule VLOOKUP() recherche dans la colonne A toute entrée commençant par "John" et renvoie la valeur correspondante de la colonne 2. Le paramètre FALSE garantit que la fonction utilise le mode de correspondance exacte, ce qui est nécessaire pour la fonctionnalité des caractères génériques.

Pour XLOOKUP(), la syntaxe est légèrement différente :

=XLOOKUP("*Adams", A:A, C:C, "Not found", 2)

Le paramètre de mode de correspondance (2) indique à XLOOKUP() d'utiliser la correspondance par caractères génériques. Cette formule recherche toute entrée se terminant par "Adams" et renvoie la valeur correspondante de la colonne C. Elle renvoie "Non trouvé" s'il n'y a pas de correspondance. 

IF() avec contournement des caractères génériques

Bien que la fonction IF() ne prenne pas directement en charge les caractères génériques, vous pouvez obtenir une fonctionnalité similaire en combinant IF() avec COUNTIF():

=IF(COUNTIF(A1, "Amy*"), "Yes", "No")

Cette formule vérifie si la cellule A1 contient du texte commençant par "Amy" et renvoie "Oui" si c'est le cas, "Non" si ce n'est pas le cas. La fonction COUNTIF() se charge de la correspondance des caractères génériques, tandis que IF() traite le résultat.

SUMIF() avec caractères génériques

La fonction SUMIF() peut additionner des valeurs basées sur des critères génériques, ce qui la rend utile pour agréger des données présentant des schémas similaires :

=SUMIF(A:A, "*a*", C:C)

Cette formule additionne toutes les valeurs de la colonne C dont la cellule correspondante de la colonne A contient la lettre "a" n'importe où dans le texte. Le motif joker "a" capture tout texte contenant "a", quelle que soit sa position.

Utilisation de caractères génériques dans les outils Excel

Les caractères génériques vont au-delà des formules et peuvent être utilisés dans plusieurs outils Excel pour rechercher, faire correspondre et filtrer des données. Ces fonctions intégrées rendent les caractères génériques accessibles même lorsque vous n'écrivez pas de formules complexes.

Jokers dans Rechercher et remplacer

La boîte de dialogue Rechercher et remplacer d'Excel prend en charge les caractères génériques pour localiser des motifs variables dans vos données. Pour activer la fonctionnalité des caractères génériques, vous devez cocher l'option "Utiliser les caractères génériques" dans la boîte de dialogue Rechercher et remplacer.

Vous pouvez utiliser l'astérisque (*) pour trouver des motifs de longueur variable. Par exemple, la recherche de "Produit*X" permet de trouver "Produit-A-X", "Produit-Premium-X" et "Produit-X". De même, le point d'interrogation ( ?) permet de trouver des modèles avec des positions de caractères spécifiques, comme "20 ??" pour trouver n'importe quel nombre à quatre chiffres commençant par "20".

Lorsque vous devez rechercher des astérisques ou des points d'interrogation dans vos données, utilisez le tilde (~) comme caractère d'échappement. La recherche de "~*Vente" trouverait les cellules contenant le texte littéral "*Vente" au lieu de traiter l'astérisque comme un caractère générique.

Jokers dans les fonctions de filtrage et de recherche

Les fonctions de filtrage d'Excel prennent en charge les caractères génériques, particulièrement utiles dans les filtres de texte comportant des options telles que "contient", "commence par" et "se termine par". Ces filtres interprètent automatiquement les caractères génériques lorsque vous les incluez dans vos critères.

Par exemple, l'application d'un filtre avec "Jo*" dans l'option "commence par" affichera tous les noms commençant par "Jo" - y compris "John", "Joseph", "Joan" et "Jonathan". Cette approche permet d'explorer rapidement les données sans avoir à écrire de formules.

Toutefois, les fonctions de recherche intégrées présentent certaines limites par rapport aux options de filtrage avancées. Si les motifs de remplacement de base sont efficaces, le filtrage multicritère complexe peut nécessiter la combinaison de caractères de remplacement avec des formules personnalisées ou l'utilisation de la fonction de filtre avancé d'Excel pour une correspondance de motifs plus sophistiquée.

Travailler avec des nombres et des caractères génériques

L'utilisation de caractères génériques avec des nombres nécessite une attention particulière car Excel traite les opérations de caractères génériques comme des fonctions basées sur le texte. Lorsque vous appliquez des motifs de caractères génériques à des données numériques, Excel convertit automatiquement les nombres en texte, ce qui peut donner lieu à des correspondances inattendues.

Pour une correspondance efficace des nombres avec les caractères génériques, vous disposez de plusieurs solutions de contournement. Vous pouvez convertir des nombres en texte à l'aide de la fonction TEXT(), de la fonction Texte en colonnes ou de formules spécifiques conçues à cet effet.

Une approche pratique consiste à combiner les fonctions SEARCH(), ISNUMBER() et SUMPRODUCT():

=SUMPRODUCT(--(ISNUMBER(SEARCH("4", B2:B12))))

Cette formule compte combien de cellules de la plage B2:B12 contiennent le chiffre "4" n'importe où dans le nombre. Pour additionner des valeurs dont les nombres contiennent des motifs spécifiques, vous pouvez étendre cette approche :

=SUMPRODUCT(--(ISNUMBER(SEARCH("4*", TEXT(B2:B12,"0")))), C2:C12)

Cette formule additionne les valeurs de la colonne C lorsque les nombres correspondants de la colonne B contiennent "4" suivi d'un chiffre quelconque. 

Pour la recherche de dates, utilisez la fonction TEXT() pour convertir les dates en modèles de texte consultables :

=TEXT(C2:C12, "mmddyyyy")

Cela permet de convertir les dates en un format texte cohérent que les caractères génériques peuvent traiter efficacement. Vous pouvez ensuite appliquer des caractères génériques pour rechercher des composants de date spécifiques : 

=COUNTIF(TEXT(C2:C12,"mmddyyyy"),"*0525*")

La formule compte les dates qui contiennent "0525" (25 mai de n'importe quelle année). 

Certains scénarios impliquent des nombres stockés sous forme de texte, tels que les codes postaux, les identifiants de produits ou les numéros de référence. Dans ce cas, les caractères génériques peuvent les faire correspondre directement à des chaînes de texte. Par exemple, le filtrage de codes avec des motifs tels que "123*" correspondrait à "12345", "123-AB" et "123XYZ", tandis que "??-45" correspondrait à "AB-45", "12-45" et "XY-45".

Limites et surprises des jokers

Toutes les fonctions Excel ne prennent pas en charge les caractères génériques, ce qui peut créer une certaine confusion lorsque vous vous attendez à ce que certaines formules fonctionnent avec une correspondance de motifs. La fonction IF(), par exemple, n'accepte pas directement les caractères génériques, ce qui oblige à contourner le problème en la combinant avec COUNTIF(), comme nous l'avons vu plus haut.

Les caractères génériques ne fonctionnent qu'avec du texte dans les formules, ce qui signifie que les valeurs numériques doivent être converties en texte avant que les opérations de caractères génériques puissent les traiter efficacement. Cette exigence de conversion de texte peut conduire à des résultats inattendus si vous n'êtes pas conscient de cette limitation.

Soyez prudent avec les faux positifs dans les formules de recherche lorsque vous utilisez des motifs génériques larges. Par exemple, VLOOKUP("*Smith*", ...) peut renvoyer des résultats inattendus tels que "Smithson", "Blacksmith" ou "Goldsmith" alors que vous ne vouliez que "Smith". Ce comportement nécessite une conception minutieuse des modèles afin d'éviter de capturer des résultats non désirés.

Une autre surprise fréquente se produit lorsque vos données contiennent des espaces en début ou en fin de ligne. Les caractères génériques considèrent les espaces comme des caractères, de sorte que "John*" ne correspondra pas à " John" (avec un espace en tête). Le nettoyage des données devient important lorsque vous travaillez avec des caractères génériques afin de garantir des résultats cohérents.

En outre, la correspondance des caractères génériques est insensible à la casse par défaut. Cela signifie que des motifs tels que "apple*" correspondront à "Apple", "APPLE" et "ApPlE". Ce comportement est pratique pour les recherches générales, mais peut entraîner des correspondances involontaires si vous exigez une correspondance sensible à la casse. Pour obtenir des correspondances sensibles à la casse, envisagez d'utiliser des fonctions telles que FIND() combiné à ISNUMBER(). 

Conseils pour l'utilisation des caractères génériques dans Excel

Les caractères génériques permettent de fusionner des données dont la correspondance est floue, ce qui est particulièrement utile en cas d'orthographe incohérente ou de variations dans la saisie des données. Par exemple, vous pouvez avoir "Homel" et "Gomel" dans votre ensemble de données représentant le même lieu. L'utilisation de caractères génériques tels que "*omel" permet d'identifier et de consolider ces entrées similaires.

Le formatage des numéros de téléphone varie souvent d'un ensemble de données à l'autre, avec des préfixes ou des codes pays différents. Vous pouvez utiliser des caractères génériques pour normaliser ces incohérences. Par exemple, vous pouvez utiliser la fonction Rechercher et remplacer pour convertir des numéros au format international britannique en numéros au format national :

Find: 44??????????
Replace with: 0

Ce modèle correspond à tout numéro britannique commençant par "44" suivi de dix chiffres, convertissant ainsi des numéros comme "447123456789" en "07123456789" pour un formatage national cohérent.

Lorsque vous devez exclure des résultats spécifiques de vos correspondances de caractères génériques, combinez plusieurs fonctions SUMIF() ou COUNTIF(). Cette technique fonctionne bien pour soustraire les correspondances non désirées :

=SUMIF(..., "00123H*") - SUMIF(..., "00123H*-SP")

Cette formule additionne toutes les valeurs correspondant à "00123H*", mais exclut celles qui se terminent par "-SP", ce qui vous permet de contrôler plus précisément la correspondance des caractères génériques.

Les changements de format fonctionnent efficacement avec des caractères génériques utilisant des fonctions telles que REPLACE(), SUBSTITUTE(), ou TEXT(). Ces fonctions peuvent nettoyer et normaliser vos données avant ou après l'application d'opérations de caractères génériques, garantissant ainsi des résultats cohérents pour l'ensemble de votre analyse.

Lorsque vous travaillez avec des codes de produits ou des numéros de référence, les caractères génériques permettent d'identifier les modèles et les exceptions. Vous pouvez utiliser *-TEMP pour trouver tous les codes temporaires ou ??-??-???? pour localiser les entrées qui suivent une structure de format spécifique, ce qui rend la validation et le nettoyage des données plus efficaces.

Conclusion

Qu'il s'agisse de consolider des entrées similaires, de normaliser des formats de numéros de téléphone ou de filtrer de vastes ensembles de données à la recherche de motifs spécifiques, les caractères génériques constituent une alternative pratique à la correspondance exacte. Ils comblent le fossé entre les recherches textuelles de base et les fonctionnalités regex plus complexes, en rendant la recherche de motifs accessible dans l'environnement familier d'Excel.

Pour étendre vos capacités Excel au-delà des opérations de joker, notre cursus de compétences Excel Fundamentals offre un parcours complet couvrant la préparation des données, l'analyse et les techniques de visualisation. Pour ceux qui sont prêts à appliquer ces compétences dans des contextes analytiques, notre cours Analyse de données dans Excel offre une expérience pratique des tableaux croisés dynamiques et des fonctions logiques avancées qui complètent les compétences en matière de correspondance de modèles que vous avez acquises ici.