EXCEL : Utilisez les caractères de remplacement [ ? * ~ ] dans vos fonctions de recherche

Excel offre la possibilité d’utiliser des caractères de remplacement dans plusieurs de ses fonctions. Par exemple, dans notre article 3 trucs pour augmenter la performance de vos critères dans vos fonctions Excel, nous démontrons comment utiliser efficacement les * et les ? dans des fonctions Somme.si (Sumif). Cela permet d’aller un peu plus loin que les options de base offertes par cette fonction. Excel dispose d’ailleurs d’une panoplie de conventions à cet effet. Cet article vise à présenter les principaux caractères de remplacement utilisés par Excel et les fonctions qui acceptent ces caractères de remplacement, en plus de fournir quelques exemple concrets de l’usage des caractères de remplacement dans les fonctions de recherche.

Les caractères de remplacement dans Excel

Voici la liste des principaux caractères de remplacement utilisés par Excel :

• Point d’interrogation (?)
  • Représente un caractère unique (n’importe lequel)
  • Par exemple, a?b recherche des expressions qui commencent par a et qui se terminent par b et cela, peu importe le deuxième caractère

• Astérisque (*)
  • Représente n’importe quel nombre de caractères
  • Par exemple, 100* recherche tous les nombres débutants par 100, que ce soit 100, 1000, 1001 ou autre
  • Ou encore *abd* recherche les expressions qui contiennent la sous-expression abd

• Tilde (~)
  • Utilisé, suivi par ? ou * ou ~
  • Recherche vraiment l’expression ?, ou * ou ~
  • Par exemple, pour trouver abd*, il faut utiliser abd~*
  • Ou pour trouver abd?, il faut utiliser abd~?

Vous aimeriez créer des états financiers prévisionnels complets et dynamiques dans Excel, capables de se moduler à vos hypothèses, afin d’éclairer vos décisions d’affaires ? Apprenez les meilleures pratiques avec nos formations en modélisation financière.

Les fonctions qui utilisent les caractères de remplacement

Plusieurs fonctions Excel permettent l’usage des caractères de remplacement. En voici une liste:

• Fonctions conditionnelles
  • Averageif (Moyenne.si) et Averageifs (Moyenne.si.ens)
  • Countif (Nb.si) et Countifs (Nb.si.ens)
  • Sumif (Somme.si) et Sumifs (Somme.si.ens)

• Fonctions de bases de données

• Fonctions de recherche
  • Match (Equiv)
  • Vlookup (Recherchev)
  • Hlookup (Rechercheh)
  • Search (Cherche) et SearchB (Chercheb)

Exemple avec les fonctions de recherche

L’exemple suivant plaira sans doute aux comptables et aux professionnels qui doivent souvent aller chercher des informations complémentaires dans un autre onglet d’un même fichier ou dans un fichier externe. Par exemple, dans l’exercice suivant, dans les colonnes B et C, nous avons une première section de données qui représente les numéros de comptes de l’entreprise et les montants correspondants. Dans la colonne E, nous avons une autre section de données qui exige que l’on ramène les valeurs de la première section de données. En utilisant un Vlookup (Recherchev) conventionnel, on se retrouvera avec une série de #N/A puisque la valeur recherchée (soit 1001 sur la première ligne) n’appartient pas à la liste de valeurs dans la colonne B. Pour effectuer une recherche efficace, nous pouvons donc utiliser le symbole * pour spécifier à Excel que nous recherchons les comptes qui commencent par les chiffres indiqués dans la colonne E. Évidemment, cela suppose que tous les numéros de comptes soient uniques.

Dans l’exemple, suivant, nous utilisons plutôt les symboles ? pour spécifier un nombre de caractères précis suivant le numéro de compte. Donc, si nous avons des données avec plusieurs comptes qui débutent par les mêmes chiffres, il faudra plutôt ensuite étudier la structure de ce que l’on recherche, un compte ou un sous-compte (et le nombre de caractères sous-jacents). Cela suppose que vous savez combien de caractères suivent le numéro de compte ou de sous-compte que vous cherchez.

Si vous ne connaissez pas d’avance ce nombre de caractères, vous pouvez toujours procéder comme dans l’exemple suivant. Voici ce qui se passe sur la ligne 4. D’abord, nous utilisons la fonction Find (Trouve) pour trouver la position du symbole -. Une fois que nous avons récupéré la position de ce symbole, nous utilisons la fonction Len (Nbcar) pour trouver le nombre de caractères total dans l’expression de la colonne B. Nous retranchons ensuite de ce résultat la position du symbole – et nous ajoutons 2. Pourquoi? Parce que ce que nous souhaitons obtenir, c’est le nombre de ? à insérer dans notre fonction Vlookup (Recherchev). Nous devons donc obtenir au final, le nombre total de caractères, suivant l’expression en E, soit 1001 (il faut donc considérer l’espace et le symbole – ). Ensuite, nous utilisons la fonction Rept (Rept) pour répéter le symbole ? le nombre de fois nécessaire. Finalement, nous insérons le résultat de notre fonction Rept (Rept) dans notre fonction Vlookup (Recherchev). À noter que nous aurions pu écrire une seule formule avec l’ensemble de ces fonctions.

Fichier d’accompagnement

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

Formation complémentaire

Êtes-vous aussi bon que vous le pensez avec Excel ? Faites le test ! Vous pourrez ensuite suivre la formation Excel – Mise à niveau qui peut servir de mise à niveau pour les professionnels qui utilisent déjà Excel dans le cadre de leurs fonctions et qui désirent aller plus loin et elle est également un prérequis recommandé pour les formations Excel – Tableaux de bord (niveau 1), Excel O365 – Bienvenue dans l’ère moderne! et surtout, Excel – Modélisation financière (niveau 1).

Voici quelques commentaires d’apprenants ayant suivi la formation Excel – Mise à niveau :

La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.