Dans ce tutoriel, nous découvrirons comment effectuer une recherche verticale qui est sensible à la casse dans une feuille de calcul Excel.
Dans un ancien tutoriel, nous avons abordé la fonction qui permet de faire une recherche verticale : “la fonction RECHERCHEV“. Nous pouvons dire que chaque utilisateur Excel connait cette fonction, cependant, la plupart d’eux ne savent pas que RECHRCHEV est insensible à la casse. En effet, par défaut, la valeur de recherche dans la fonction RECHERCHEV ne respecte pas la casse. Cela signifie qu’elle traitera les deux mots, « pomme » qui est en minuscule et « POMME » qui est en majuscule, d’une manière identique.
Nous vous expliquerons comment rendre la fonction RECHERCHEV sensible à la casse, et vous montrerons également d’autres fonctions à utiliser comme solutions alternatives.
Comment rendre la fonction RECHERCHEV sensible à la casse
Rendre RECHERCHEV sensible à la casse : utilisation de la colonne d'assistance
Rendre RECHERCHEV sensible à la casse : sans la colonne d'aide
Comment effectuer une recherche de correspondance exacte avec SOMMEPROD
EQUIV/INDEX - recherche sensible à la casse pour tous les types de données
Comme vous le savez déjà, une formule RECHERCHEV habituelle est insensible à la casse. Cependant, il existe des techniques pour la rendre ainsi.
Dans l’ensemble des données indiqué ci-dessous, nous avons une liste des noms des élèves (avec différentes casses) et leurs notes en mathématique :
Comme vous pouvez le voir, il y a trois cellules avec le même nom (A2, A4 et A6) mais avec une casse différente. Sur la droite (en D3:E5), nous avons les trois noms : « Amine », « AMINE » et « amine », ainsi que leurs notes en mathématiques.
Comme vous pouvez le deviner, une formule RECHERCHEV habituelle =RECHERCHEV("AMINE"; $A$2:$B$9;2; FAUX) récupérera la valeur 10 qui est associée à "Amine" car il vient avant "AMINE" dans le tableau de recherche. Mais ce n'est pas ce que vous souhaitez, non ?
Pour pouvoir effectuer une RECHERCHEV sensible à la casse dans Excel, vous devez utiliser l'une des méthodes suivantes :
Vous pouvez utiliser une colonne d'assistance pour obtenir une valeur de recherche unique pour chaque élément du tableau de recherche. En effet, cela aide à différencier les noms avec une casse différente.
Pour ce faire, suivez les étapes ci-dessous :
=RECHERCHEV(MAX(EXACT(E3; $A$2:$A$9) * (LIGNE($A$2:$A$9))); $B$2:$C$9; 2; 0)
Remarque : puisqu'il s'agit d'une formule matricielle, utilisez le raccourci Ctrl + Shift + Entrée au lieu de simplement “Entrée“.
Comment fonctionne cette formule ?
Décomposons la formule en parties pour comprendre son fonctionnement :
{VRAI; FAUX; FAUX; FAUX; FAUX; FAUX; FAUX; FAUX}
{2; 0; 0; 0; 0; 0; 0; 0}
Les deux notes suivantes sont essentielles pour le bon fonctionnement de votre formule RECHERCHEV sensible à la casse :
Vous pouvez également remplir votre colonne d'assistance avec la formule ci-dessous (où B est votre colonne de recherche) :
=CODE(STXT(A2; 1; 1))&CODE(STXT(A2; 2; 1))&CODE(STXT(A2; 3; 1))&CODE(STXT(A2; 4; 1))&SIERREUR(CODE(STXT(A2; 5; 1)); "")
La formule ci-dessus analyse la valeur de recherche en caractères individuels, convertit chaque caractère en son code (par exemple, «A» est 65 et «a» est 97), puis concatène ces codes en une chaîne numérique unique :
Après avoir rempli votre colonne d’assistance, vous pouvez utiliser une formule RECHERCHEV simple sensible à la casse :
=RECHERCHEV(F3; $B$2:$C$9; 2; FAUX)
La formule CODE qui est copiée dans la colonne d'assistance implique que toutes vos valeurs de recherche ont le même nombre de caractères. Sinon, vous devez connaître les nombres minimum et maximum et ajouter autant de fonctions SIERREUR que le nombre de caractères faisant la différence entre les valeurs de recherche les plus petites et les plus grandes.
Par exemple, si la plus petite valeur de recherche a 3 caractères et la plus grande dispose de 5 caractères, vous utiliserez cette formule :
=CODE(STXT(A2; 1; 1))&CODE(STXT(A2; 2; 1))&CODE(STXT(A2; 3; 1))&SIERREUR(CODE(STXT(A2; 3; 1)); "")&SIERREUR(CODE(STXT(A2; 4; 1)); "")
Dans la fonction STXT, vous devez spécifier les arguments suivants :
Désormais, si vous n'aimez pas l’utilisation d’une colonne d'assistance, vous pouvez également effectuer une recherche sensible à la casse en utilisant la méthode suivante.
Dans cette méthode vous n’avez pas à vous soucier d’insérer une colonne d’assistance. Cependant, vous devez toujours disposer d'une colonne d'assistance virtuelle ! Cette colonne virtuelle ne fait pas partie de la feuille de calcul mais elle est construite dans la formule.
Voici la formule qui vous donnera le résultat sans la colonne d'assistance :
=RECHERCHEV(MAX(EXACT(D3; $A$2:$A$9) * (LIGNE($A$2:$A$9))); CHOISIR({1.2}; LIGNE($A$2:$A$9); $B$2:$B$9); 2; 0)
Remarque : N’oubliez pas qu'il s'agit d'une formule matricielle, utilisez Ctrl + Shift + Entrée au lieu d’utiliser simplement Entrée.
Comment fonctionne cette formule ?
Cette formule se base sur le concept de colonne d'assistance. La différence est qu'au lieu de créer la colonne d'assistance dans la feuille de calcul, considérez-la comme une donnée d'assistance virtuelle faisant partie de la formule globale.
Voici la partie qui fonctionne comme les données d'assistance dans la formule :
CHOISIR({1.2}; LIGNE($A$2:$A$9); $B$2:$B$9)
Pour comprendre ce que nous voulons dire par des données d'assistance virtuelle, jetez un coup d’œil sur la capture ci-dessous :
Dans l'illustration ci-dessus, lorsqu’on sélectionne la partie CHOISIR de la formule et qu’on clique sur F9, cela montre le résultat que la formule CHOISIR renvoi. Le résultat dans ce cas est le suivant :
{2.10; 3.15; 4.15; 5.12; 6.14; 7.12; 8.10; 9.17}
Il s’agit d’un tableau dans lequel un point représente la cellule suivante de la même ligne et un point-virgule indique que les données suivantes se trouvent dans la ligne suivante. Par conséquent, cette formule crée 2 colonnes de données : une colonne qui contient les numéros de lignes et une autre qui contient les notes de mathématique.
Maintenant, lorsque vous utilisez la fonction RECHERCHEV, elle recherche simplement la valeur de recherche dans la première colonne (de ces données virtuelles à 2 colonnes) et renvoie la note correspondante. La valeur de recherche ici est un nombre que nous obtenons de la combinaison des fonctions MAX et EXACT.
Comme vous l'avez déjà compris à partir du sous-titre, SOMMEPROD est encore une autre fonction Excel qui peut effectuer une recherche sensible à la casse. Cependant, elle ne peut renvoyer que des valeurs numériques.
La fonction SOMMEPROD multiplie les composantes dans les tableaux spécifiés en argument et renvoie la somme des produits. Sa syntaxe est la suivante :
SOMMEPROD(tableau1; tableau2 ; tableau3 ; ...)
Puisque nous souhaitons effectuer une recherche sensible à la casse, nous allons utiliser la fonction EXACT de l'exemple précédent comme l'un des multiplicateurs.
La fonction EXACT fonctionne comme un outil de recherche. Cependant, elle est sensible à la casse. Cette fonction compare deux chaînes de texte et renvoie VRAI si elles sont exactement identiques, FAUX sinon.
Bien que SOMMEPROD fonctionne avec des tableaux, elle ne nécessite pas l'utilisation de raccourci de formule matricielle (CTRL + Shift + Enter). Vous pouvez compléter une formule SOMMEPROD en appuyant simplement sur « Enter » après avoir tapé la formule.
En considérant l'exemple précédent, la formule en E4 est la suivante :
=SOMMEPROD((EXACT($A$2:$A$9; D4) * ($B$2:$B$9)))
Dans ce cas, les recherches standard comme RECHERCHEH de RECHERCHEV ignoreront la casse et renverront la première correspondance, qui est « Amine ». Cependant, la fonction EXACT correspondra aux cas exacts dans « tableau_recherche ». Le résultat sera donc 14 :
Comment fonctionne cette formule ?
Comme vous pouvez le deviner, la fonction EXACT compare la valeur de la cellule D4 à tous les éléments de la colonne A et si une correspondance exacte sensible à la casse est trouvée, elle retourne « VRAI », sinon elle retourne « FAUX ». Dans les opérations mathématiques, Excel traite « VRAI » comme « 1 » et FAUX comme « 0 », de sorte que la fonction SOMMEPROD multiplie les nombres renvoyés et additionne les produits.
N’oubliez pas que les zéros ne comptent pas ici, car un zéro produit toujours "0" quel que soit le nombre par lequel il est multiplié. Alors, expliquons ce qui se passe lorsqu'une correspondance exacte (sensible à la casse) est trouvée dans la colonne A et que "1" est renvoyé : la fonction SOMMEPROD multiplie 1 par le nombre correspondant dans la colonne B (de la même ligne) et renvoie exactement ce nombre ! En effet, les produits des autres multiplications sont des zéros, ce qui n'aura aucun effet sur la valeur renvoyée.
Enfin, dans cette section nous allons aborder une formule de recherche sensible à la casse sans limitation qui fonctionne sur tous les types de données.
Par défaut, les recherches standard avec RECHERCHEV ou INDEX + EQUIV ne sont pas sensibles à la casse. Les fonctions RECHERCHEV et EQUIV renverront simplement la première correspondance, en ignorant la casse.
La fonction EQUIV recherche une valeur, spécifiée en argument, dans une plage spécifiée (Tableau_recherche) et renvoie sa position relative dans le tableau. Ensuite, cette position relative de la valeur de recherche va directement à l'argument « no_lig » de la fonction INDEX lui demandant de renvoyer une valeur à partir de cette ligne. Toutefois, si vous devez faire une recherche qui sera sensible à la casse, vous pouvez le faire avec une formule matricielle qui utilise INDEX, EQUIV et la fonction EXACT.
Dans notre exemple, nous allons utiliser la formule suivante :
=INDEX($B$2:$B$9; EQUIV(VRAI; EXACT(D4; $A$2:$A$9); 0))
Cette formule est une formule matricielle et doit être saisie avec le raccourcie Ctrl + Shift + Enter.
Puisque la fonction EQUIV seule n'est pas sensible à la casse, nous avons besoin d'un moyen pour vérifier la correspondance de la casse. C’est ainsi que nous avons besoin de la fonction EXACT, mais la façon dont nous l'utilisons est un peu inhabituelle, car nous devons comparer une cellule à une plage de cellules. Expliquons cette formule de l'intérieur vers l'extérieur :
{FAUX; FAUX; FAUX; FAUX; VRAI; FAUX; FAUX; FAUX}
EQUIV(VRAI; EXACT(D4; $A$2:$A$9); 0)
Remarque : cette formule récupérera à la fois le texte et les valeurs numériques. Si vous souhaitez récupérer uniquement des nombres, vous pouvez utiliser une formule basée sur SOMMEPROD expliquée ci-dessus.
Supposons maintenant qu'une cellule de la colonne de retour correspondant à une valeur de recherche soit vide. Que retournera la formule ? Rien. Et maintenant, voyons ce qu'elle renvoie réellement :
Comme vous pouvez le voir, la formule renvoie un zéro. Peut-être ce n'est pas une grande chose si vous travaillez uniquement avec des valeurs textuelles. Cependant, si votre ensemble de données contient des nombres et que certains d'entre eux sont de vrais zéros, cela vous causera un problème.
Pour rendre la formule INDEX/EQUIV sensible à la casse absolument parfaite, vous devez l'envelopper dans la fonction SI qui vérifiera si une cellule de retour est vide et ne renverra rien dans ce cas :
=SI(INDIRECT("B" & (1 + EQUIV(VRAI; EXACT(D4; $A$2:$A$9); 0))) <> ""; INDEX($B$2:$B$9; EQUIV(VRAI; EXACT(D4; $A$2:$A$9); 0)); "")
Dans cette formule, « B » est la colonne de retour. Le « 1+ » est utilisé pour transformer la position relative d'une cellule renvoyée par la fonction EQUIV en une adresse de cellule réelle. Par exemple, le tableau de recherche dans notre fonction EQUIV est A2:A9, alors la position relative de la cellule A2 est « 1 », car il s'agit de la première cellule du tableau. Mais la position réelle de la cellule A2 dans la colonne est « 2 », donc nous ajoutons 1 pour compenser la différence, pour que la fonction INDIRECT renvoie une valeur de la cellule de droite.
Comme illustré ci-dessous, la formule renvoie une chaine vide si la cellule de retour contient un zéro :
