Table des matières
Ce petit tutoriel explique comment faire une fonction VPR (VLOOKUP) sensible à la casse, montre plusieurs autres formules qu'Excel peut rechercher de manière sensible à la casse, et souligne les forces et les faiblesses de chaque fonction.
Je suppose que chaque utilisateur d'Excel sait quelle fonction effectue la recherche verticale. C'est vrai, c'est une fonction VPR. Cependant, peu de gens savent que VPR n'est pas sensible à la casse, c'est-à-dire que les caractères minuscules et majuscules sont identiques pour lui.
Voici un exemple rapide démontrant l'incapacité VPR reconnaître le registre. Supposons dans une cellule A1 contient la valeur "facture" et la cellule A2 – « Facture », formule :
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… arrêtera sa recherche sur "facture" puisque cette valeur vient en premier dans la liste, et extraira la valeur de la cellule B1.
Plus loin dans cet article, je vous montrerai comment faire VPR sensible aux majuscules et minuscules. De plus, nous apprendrons quelques fonctions supplémentaires qui peuvent effectuer des recherches sensibles à la casse dans Excel.
Nous allons commencer par le plus simple - VOIR (RECHERCHE) et SUMPRODUCT (SUMPRODUCT), qui, malheureusement, ont plusieurs limitations importantes. Ensuite, nous examinerons de plus près la formule un peu plus complexe INDEX + CORRESPONDANCE (INDEX+MATCH), qui fonctionne parfaitement dans n'importe quelle situation et avec n'importe quel ensemble de données.
La fonction VLOOKUP est sensible à la casse
Comme vous le savez déjà, la fonction habituelle VPR est insensible à la casse. Cependant, il existe un moyen de le rendre sensible à la casse. Pour ce faire, vous devez ajouter une colonne auxiliaire à la table, comme illustré dans l'exemple suivant.
Supposons dans une colonne B il y a des identifiants produit (Item) et vous voulez extraire le prix du produit et le commentaire correspondant des colonnes C и D. Le problème est que les identifiants contiennent à la fois des caractères minuscules et majuscules. Par exemple, les valeurs de cellule B4 (001Tvci3u) et B5 (001Tvci3U) ne diffèrent que dans le cas du dernier caractère, u и U respectivement.
Comme vous pouvez l'imaginer, la formule de recherche habituelle
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
reviendra 90 $, puisque la valeur 001Tvci3u se trouve dans la plage de recherche avant 001Tvci3U. Mais ce n'est pas ce dont nous avons besoin, n'est-ce pas ?
Pour rechercher avec une fonction VPR dans Excel sensible à la casse, vous devrez ajouter une colonne d'assistance et remplir ses cellules avec la formule suivante (où B est la colonne de recherche) :
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
Cette formule décompose la valeur souhaitée en caractères séparés, remplace chaque caractère par son code (par exemple, au lieu de A à 65 ans, au lieu a code 97) puis combine ces codes en une chaîne unique de nombres.
Après cela, nous utilisons une fonction simple VPR pour la recherche sensible à la casse :
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Bon fonctionnement de la fonction VPR la sensibilité à la casse dépend de deux facteurs :
- La colonne d'assistance doit être la colonne la plus à gauche dans la plage visible.
- La valeur que vous recherchez doit contenir un code de caractère au lieu de la valeur réelle.
Comment utiliser correctement la fonction CODE
La formule insérée dans les cellules de la colonne auxiliaire suppose que toutes vos valeurs de recherche ont le même nombre de caractères. Si ce n'est pas le cas, vous devez connaître les nombres les plus petits et les plus grands et ajouter autant de fonctionnalités IFERREUR (IFERREUR) combien de caractères est la différence entre la valeur recherchée la plus courte et la plus longue.
Par exemple, si la valeur de recherche la plus courte est de 3 caractères et la plus longue de 5 caractères, utilisez cette formule :
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
Pour la fonction PSTR (MID) Vous fournissez les arguments suivants :
- 1er argument - texte (texte) est le texte ou la référence de cellule contenant les caractères à extraire (dans notre cas c'est B2)
- 2er argument - num_début (start_position) est la position du premier de ces caractères à extraire. tu entres 1 dans la première fonction PSTR, 2 – dans la deuxième fonction PSTR et ainsi de suite
- 3er argument - nombre_caractères (number_of_characters) – Spécifie le nombre de caractères à extraire du texte. Comme nous n'avons besoin que d'un seul caractère tout le temps, dans toutes les fonctions, nous écrivons 1.
LIMITATIONS: Fonction VPR n'est pas la meilleure solution pour les recherches sensibles à la casse dans Excel. Tout d'abord, l'ajout d'une colonne auxiliaire est nécessaire. Deuxièmement, la formule ne fait du bon travail que si les données sont homogènes ou si le nombre exact de caractères dans les valeurs recherchées est connu. Si ce n'est pas votre cas, il est préférable d'utiliser une des solutions que nous montrons ci-dessous.
Fonction LOOKUP pour une recherche sensible à la casse
Fonction VOIR (RECHERCHE) liés VPR, cependant sa syntaxe permet des recherches sensibles à la casse sans ajouter de colonne auxiliaire. Pour ce faire, utilisez VOIR combiné avec la fonction EXACT (EXACT).
Si nous prenons les données de l'exemple précédent (sans colonne auxiliaire), la formule suivante fera l'affaire :
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Recherches de formules dans la plage A2: A7 correspondance exacte avec la valeur de la cellule F2 respecte la casse et renvoie la valeur de la colonne B de la même ligne.
Comme VPRfonction VOIR fonctionne de la même manière avec des valeurs textuelles et numériques, comme vous pouvez le voir dans la capture d'écran ci-dessous :
Important! Pour la fonction VOIR a fonctionné correctement, les valeurs de la colonne de recherche doivent être triées par ordre croissant, c'est-à-dire de la plus petite à la plus grande.
Permettez-moi de vous expliquer brièvement comment fonctionne la fonction EXACT dans la formule ci-dessus, car c'est le point clé.
Fonction EXACT compare les deux valeurs de texte dans les 1er et 2ème arguments et renvoie TRUE si elles sont exactement identiques, ou FALSE si elles ne le sont pas. Il est important pour nous que la fonction EXACT sensible aux majuscules et minuscules.
Voyons comment fonctionne notre formule VOIR+EXACT:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- Fonction EXACT compare la valeur de la cellule F2 avec tous les éléments dans une colonne A (A2:A7). Renvoie TRUE si une correspondance exacte est trouvée, sinon FALSE.
- Puisque vous donnez le premier argument de la fonction VOIR valeur TRUE, il extrait la valeur correspondante de la colonne spécifiée (dans notre cas, la colonne B) uniquement si une correspondance exacte est trouvée, sensible à la casse.
J'espère que cette explication était claire et que vous comprenez maintenant l'idée principale. Si tel est le cas, vous n'aurez aucune difficulté avec d'autres fonctions que nous analyserons plus en détail, car. ils fonctionnent tous sur le même principe.
LIMITATIONS: Les données de la colonne de recherche doivent être triées par ordre croissant.
SUMPRODUCT - trouve des valeurs de texte, sensibles à la casse, mais ne renvoie que des nombres
Comme vous l'avez déjà compris dans le titre, SUMPRODUCT (SUMPRODUCT) est une autre fonction Excel qui vous aidera à effectuer une recherche sensible à la casse, mais ne renverra que des valeurs numériques. Si cette option ne vous convient pas, vous pouvez immédiatement passer au forfait INDEX + CORRESPONDANCE, qui donne une solution pour tous les cas et pour tous les types de données.
Tout d'abord, laissez-moi vous expliquer brièvement la syntaxe de cette fonction, cela vous aidera à mieux comprendre la formule sensible à la casse qui suit.
Fonction SUMPRODUCT multiplie les éléments des tableaux donnés et renvoie la somme des résultats. La syntaxe ressemble à ceci :
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Puisque nous avons besoin d'une recherche sensible à la casse, nous utilisons la fonction EXACT (EXACT) de l'exemple précédent comme l'un des multiplicateurs :
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Comme vous vous en souvenez, EXACT compare la valeur de la cellule F2 avec tous les éléments dans une colonne A. Renvoie TRUE si une correspondance exacte est trouvée, sinon FALSE. Dans les opérations mathématiques, Excel prend TRUE comme 1, et FAUX pour 0Plus loin SUMPRODUCT multiplie ces nombres et additionne les résultats.
Les zéros ne sont pas comptés car, multipliés, ils donnent toujours 0. Examinons de plus près ce qui se passe lorsqu'une correspondance exacte dans une colonne A trouvé et rendu 1… Fonction SUMPRODUCT multiplie le nombre dans la colonne B on 1 et renvoie le résultat – exactement le même nombre ! En effet, les résultats des autres produits sont nuls et n'affectent pas la somme résultante.
Malheureusement la fonction SUMPRODUCT ne peut pas fonctionner avec des valeurs de texte et des dates car elles ne peuvent pas être multipliées. Dans ce cas, vous recevrez un message d'erreur #VALEUR! (#VALEUR !) comme dans une cellule F4 dans l'image ci-dessous :
LIMITATIONS: Renvoie uniquement des valeurs numériques.
INDEX + MATCH - recherche sensible à la casse pour tout type de données
Enfin, nous sommes proches d'une formule de recherche illimitée et sensible à la casse qui fonctionne avec n'importe quel ensemble de données.
Cet exemple vient en dernier, non pas parce que le meilleur est laissé pour le dessert, mais parce que les connaissances acquises dans les exemples précédents vous aideront à comprendre mieux et plus rapidement la formule sensible à la casse. INDEX + CORRESPONDANCE (INDEX + CORRESPONDANCE).
Comme vous l'avez probablement deviné, la combinaison de fonctions PLUS EXPOSÉ и INDEX utilisé dans Excel comme une alternative plus flexible et puissante pour VPR. L'article Utiliser INDEX et MATCH au lieu de RECHERCHEV expliquera parfaitement comment ces fonctions fonctionnent ensemble.
Je vais juste rappeler les points clés :
- Fonction PLUS EXPOSÉ (MATCH) recherche une valeur dans une plage donnée et renvoie sa position relative, c'est-à-dire le numéro de ligne et/ou de colonne ;
- Ensuite, la fonction INDEX (INDEX) renvoie une valeur d'une colonne et/ou d'une ligne spécifiée.
Formuler INDEX + CORRESPONDANCE peut effectuer une recherche sensible à la casse, il vous suffit d'y ajouter une fonction. Ce n'est pas difficile de deviner ce que c'est encore EXACT (EXACT):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
Dans cette formule EXACT fonctionne de la même manière qu'en conjonction avec la fonction VOIR, et donne le même résultat :
A noter que la formule INDEX + CORRESPONDANCE entre accolades se trouve une formule matricielle et vous devez la compléter en appuyant sur Ctrl + Maj + Entrée.
Pourquoi INDEX+MATCH est-il la meilleure solution pour la recherche sensible à la casse ?
Les principaux avantages du forfait INDEX и PLUS EXPOSÉ:
- Ne nécessite pas l'ajout d'une colonne auxiliaire, contrairement à VPR.
- Ne nécessite pas que la colonne de recherche soit triée, contrairement à VOIR.
- Fonctionne avec tous les types de données - nombres, texte et dates.
Cette formule semble parfaite, n'est-ce pas ? En fait, ce n'est pas le cas. Et c'est pourquoi.
Supposons que la cellule de la colonne de valeur de retour associée à la valeur de recherche est vide. Quel résultat la formule renverra-t-elle ? Non? Voyons ce que la formule renvoie réellement :
Oups, la formule renvoie zéro ! Cela peut ne pas être un gros problème si vous travaillez avec des valeurs de texte pur. Cependant, si le tableau contient des nombres, y compris des zéros "réels", cela devient un problème.
En fait, toutes les autres formules de recherche (VLOOKUP, LOOKUP et SUMPRODUCT) dont nous avons parlé précédemment se comportent de la même manière. Mais vous voulez la formule parfaite, n'est-ce pas ?
Pour rendre une formule sensible à la casse INDEX + CORRESPONDANCE parfait, mettez-le dans une fonction IF (IF) qui testera une cellule avec une valeur de retour et retournera un résultat vide si elle est vide :
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
Dans cette formule :
- B est une colonne avec des valeurs de retour
- 1+ est un nombre qui transforme la position relative de la cellule retournée par la fonction PLUS EXPOSÉ, à l'adresse réelle de la cellule. Par exemple, dans notre fonction PLUS EXPOSÉ tableau de recherche donné A2: A7, c'est-à-dire la position relative de la cellule A2 sera 1, car c'est le premier du tableau. Mais la position réelle de la cellule A2 dans la colonne est 2, donc on ajoute 1combler la différence et avoir la fonction INDIRECT (INDIRECT) a récupéré la valeur de la cellule souhaitée.
Les images ci-dessous montrent la formule corrigée sensible à la casse INDEX + CORRESPONDANCE En action. Elle renvoie un résultat vide si la cellule renvoyée est vide.
J'ai réécrit la formule en colonnes B : Dpour ajuster la barre de formule sur la capture d'écran.
Retourne la formule 0si la cellule renvoyée contient zéro.
Si tu veux le lien INDEX и PLUS EXPOSÉ affiche un message lorsque la valeur de retour est vide, vous pouvez l'écrire entre les derniers guillemets ("") de la formule, par exemple, comme ceci :
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
