Formules de recherche complexes (Microsoft Excel)

Eddie a une série de références au format 123/45678 ou 011/00345.

Il doit rechercher le nom du numéro de pièce correspondant dans une table différente. Ce tableau se compose de trois colonnes. La première colonne contient la partie du numéro de pièce avant la barre oblique (telle que 123 ou 011) et la deuxième colonne contient la partie après la barre oblique (telle que 45678 ou 00345). La troisième colonne contient le nom qu’Eddie souhaite rechercher.

Une partie du problème d’Eddie est que dans le tableau, les deux colonnes qui contiennent chacune une partie du numéro de pièce sont des valeurs numériques. Ainsi, le numéro de pièce complet n’est pas 011 et 00345, mais 11 et 345.

Eddie se demande comment créer une formule de recherche pour les numéros de pièce (123/45678 ou 011/00345) pour renvoyer le nom de pièce correct à partir du tableau.

Pour commencer, vous devez apporter des modifications au tableau contenant les numéros de pièce. Les deux premières colonnes doivent être mises en forme sous forme de texte et non de nombres. Cela traitera les valeurs de ces colonnes comme du texte, de sorte que lorsque vous entrez « 011 » dans la première colonne, il conserve le zéro non significatif.

Si vous avez déjà entré la valeur 011 avant de formater la colonne sous forme de texte, elle sera toujours affichée comme 11 (sans le zéro non significatif). Vous devrez non seulement modifier le format de la colonne, mais également saisir à nouveau les fragments de numéro de pièce contenant des zéros non significatifs.

Ensuite, vous devez vous assurer que votre table de numéros de pièce comprend des en-têtes de colonne. Pour cet exemple, assurez-vous que les trois en-têtes de colonne sont Gauche, Droite et Nom. (Vous pouvez mettre ces en-têtes en gras et soulignés, ce qui permet de les distinguer des valeurs de chaque colonne.) Ensuite, créez des noms pour les colonnes individuelles en suivant ces étapes:

Une fois votre tableau des références préparé, vous êtes maintenant prêt à rechercher les références. Dans les cellules juste à droite de vos valeurs de recherche (123/45678 et 011/00345), vous allez entrer une formule matricielle. En supposant que le premier numéro de pièce se trouve dans la cellule A1, vous devez entrer la formule matricielle suivante dans la cellule B1:

N’oubliez pas que pour désigner cela comme une formule matricielle, vous la saisissez en appuyant sur Maj + Ctrl + Entrée. La formule apparaît alors dans la barre de formule avec \ {accolades} autour d’elle. Vous pouvez copier la formule dans les autres cellules de la colonne B, si nécessaire.

La formule fonctionne en tirant la valeur de la colonne Nom de la table lorsque la valeur correspondante de la colonne de gauche concaténée avec une barre oblique et la valeur de la colonne de droite correspond à ce qui se trouve dans la cellule A1. S’il n’y a pas de correspondance, la formule renvoie une valeur d’erreur # N / A, sinon elle renvoie le numéro de pièce souhaité.

Notez que cette approche ne fonctionnera que si vous formatez les deux premières colonnes de votre tableau des références sous forme de texte et assurez-vous que les différentes cellules contiennent des zéros non significatifs. Si, pour une raison quelconque, vous ne pouvez pas formater les deux premières colonnes du tableau de cette manière (peut-être qu’il y en a trop), vous devez alors changer la formule de recherche que vous utilisez:

Notez que cette version de la formule (qui doit toujours être saisie sous forme de formule matricielle) utilise la fonction TEXT à deux endroits, pour convertir les valeurs des colonnes Gauche et Droite afin qu’elles aient des zéros non significatifs.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (2787) s’applique à Microsoft Excel 97, 2000, 2002 et 2003.