Commencer par une question de correspondance

Une recherche Excel sert à relier deux informations, pas à deviner une identité. Un magasin de fournitures à Rabat reçoit une liste de commandes avec des références d’articles et doit retrouver les prix du catalogue. Le catalogue de démonstration comporte A101, écran 24 pouces, 1 450 MAD ; A102, clavier, 180 MAD ; A103, souris, 95 MAD. La commande de deux écrans et trois claviers doit donc représenter 3 440 MAD avant toute remise.

Le premier travail consiste à choisir une clé stable. Le libellé « écran » est insuffisant lorsque plusieurs modèles existent. Utilisez la référence de produit et conservez le nom comme information descriptive. Vérifiez également la date du catalogue : retrouver un prix actuel ne reconstitue pas automatiquement le prix applicable à une commande ancienne. Une formule correcte peut répondre à une mauvaise question si le référentiel utilisé ne correspond pas à la période commerciale recherchée.

Vérifier la disponibilité avant de choisir la formule

RECHERCHEX est disponible dans plusieurs éditions récentes d’Excel, notamment Microsoft 365 et Excel 2021 ou 2024, mais Microsoft précise son absence dans Excel 2016 et 2019. Ne déduisez donc pas sa disponibilité de la seule extension .xlsx. Demandez la version effectivement utilisée par le destinataire et testez une formule simple sur son installation. Un résultat enregistré par l’auteur n’est pas une preuve que le lecteur pourra recalculer le classeur.

Dans un environnement hétérogène, livrer un modèle INDEX et EQUIV peut être plus pratique qu’introduire une dépendance à une fonction absente. Le choix dépend du parc de travail, de la facilité de maintenance et du besoin réel. N’utilisez pas une fonction récente uniquement pour moderniser une feuille déjà fiable. À l’inverse, ne conservez pas une formule difficile à lire lorsque tous les utilisateurs disposent d’une version adaptée et qu’une recherche explicite améliore le contrôle du modèle.

Construire une première RECHERCHEX exacte

Transformez le catalogue en tableau nommé tblCatalogue avec les colonnes Reference, Article et PrixMAD. Dans un tableau de commandes possédant une colonne Reference, saisissez =RECHERCHEX([@Reference];tblCatalogue[Reference];tblCatalogue[PrixMAD];"Référence absente"). La recherche exacte est le comportement par défaut. Pour A102, la valeur attendue est 180 ; pour A999, le message signale une absence plutôt qu’un prix nul.

L’intérêt est de séparer clairement la colonne interrogée de la colonne renvoyée. La disposition du catalogue peut changer sans imposer un numéro de colonne écrit en dur. Cependant, les deux tableaux de recherche et de résultat doivent représenter les mêmes lignes dans le même ordre. Ne triez jamais une colonne isolément pour embellir la liste. Les exemples suivent la notation française avec point-virgule ; une installation différente peut afficher les noms anglais et utiliser un autre séparateur. Adaptez l’écriture sans modifier la logique de correspondance.

Traiter l’absence sans cacher les incidents

Le quatrième argument de RECHERCHEX permet de définir le résultat lorsqu’aucune référence n’est trouvée. « Référence absente » est utile dans une colonne de contrôle, mais ce texte ne doit pas devenir un montant dans un calcul de facturation. Séparez au besoin PrixRecherche et StatutControle. La validation de la commande peut alors exiger zéro ligne non reconnue avant de calculer ou transmettre le total.

Évitez de remplacer toute anomalie par zéro avec SIERREUR autour du modèle entier. Un zéro peut être un prix commercial réel, une absence ou une panne de référence ; ces situations ne sont pas équivalentes. Une cellule qui affiche une erreur attire l’attention, tandis qu’un total plausible mais incomplet peut partir au client. Pour notre commande, retirer A102 du catalogue ferait disparaître 540 MAD si ses trois claviers étaient transformés silencieusement en prix nul. Le contrôle métier doit donc compter les références absentes et bloquer le total présenté comme définitif.

Distinguer première occurrence et unicité réelle

RECHERCHEX renvoie normalement la première correspondance rencontrée. Si A102 existe deux fois, à 180 et 190 MAD, la fonction ne résout pas le conflit ; elle choisit une occurrence selon l’ordre de recherche. La possibilité de chercher depuis la fin ne transforme pas davantage le dernier enregistrement en tarif valide. Il faut d’abord définir ce que signifie une ligne du catalogue : produit unique, tarif par agence ou historique daté.

Utilisez =NB.SI(tblCatalogue[Reference];[@Reference]) dans un contrôle adapté pour vérifier l’unicité. Un résultat supérieur à un doit être expliqué. Si les tarifs diffèrent selon Casablanca et Rabat, la vraie clé est peut-être le couple Reference et Agence. Construisez alors un identifiant composite documenté ou une recherche à critères multiples adaptée au niveau des utilisateurs. Ne fusionnez pas des champs sans séparateur : A1 associé à 23 et A12 associé à 3 formeraient tous deux A123 et créeraient une correspondance artificielle.

Reconnaître les différences invisibles de type

La référence numérique 102 et la chaîne de texte « 102 » peuvent ne pas se comporter comme attendu dans un rapprochement. Une espace finale peut également distinguer A102 de A102 suivi d’un caractère invisible. Avant de compliquer la formule, inspectez la longueur du texte, le type des cellules et le fichier source. Nettoyez les données dans une colonne de préparation ou dans Power Query afin de conserver la valeur originale pour l’audit.

Ne convertissez pas tous les identifiants en nombres : 00102 peut être une référence distincte dont les zéros initiaux ont un sens. Pour des codes de produits, une convention textuelle explicite est souvent préférable. Documentez aussi la gestion des tirets, des accents et des espaces. Deux articles dont les noms se ressemblent ne doivent pas être rapprochés automatiquement sans règle approuvée. Une correspondance exacte sur des clés normalisées est plus facile à expliquer qu’une succession de remplacements manuels intégrés dans chaque formule de vente.

Préparer une alternative INDEX et EQUIV

Pour le même catalogue, une solution classique est =INDEX(tblCatalogue[PrixMAD];EQUIV([@Reference];tblCatalogue[Reference];0)). EQUIV recherche la position de la référence avec une correspondance exacte imposée par zéro ; INDEX renvoie le prix situé à cette position. A103 doit produire 95. Cette décomposition aide à diagnostiquer le problème : testez EQUIV seul pour savoir si la référence est trouvée, puis contrôlez la colonne renvoyée par INDEX.

Si l’on préfère RECHERCHEV, la référence doit se trouver dans la première colonne de la plage et l’argument FAUX impose une recherche exacte. Un index de colonne écrit en dur peut devenir fragile si quelqu’un insère une colonne dans la plage. Ce défaut ne signifie pas que toute RECHERCHEV est mauvaise ; il oblige à tester le scénario de modification du référentiel. Choisissez une convention dans l’équipe et accompagnez-la d’exemples de contrôle, plutôt que de mélanger trois styles au hasard dans un même rapport.

Réserver l’approximation aux barèmes explicitement définis

Une recherche approximative est adaptée à certains barèmes, par exemple une remise commerciale de démonstration selon la quantité. Supposons les seuils minimaux 1, 10 et 50, associés à 0 %, 3 % et 7 %. Une commande de 12 unités doit utiliser le palier 10 et recevoir 3 %. Ce comportement doit être demandé explicitement, puis testé sur les bornes : 9, 10, 49 et 50.

Ne réutilisez pas ce principe pour un identifiant client ou une référence de produit. Le voisin le plus proche de A102 n’est pas un substitut autorisé. Les modes de recherche binaire imposent en outre un ordre de tri approprié ; les utiliser sur une liste non triée peut produire des résultats faux. Dans un fichier transmis à plusieurs personnes, privilégiez la lisibilité tant qu’aucun besoin de performance mesuré ne justifie ces réglages. Un modèle de quelques centaines de références gagne davantage à être contrôlé qu’à utiliser un mode avancé difficile à maintenir.

Exercice corrigé : une commande et trois anomalies

Créez une commande avec A101, quantité 2 ; A102, quantité 3 ; A999, quantité 1. La recherche doit reconnaître deux références et signaler la troisième. Le montant des lignes reconnues vaut 3 440 MAD, mais il s’agit d’un sous-total contrôlé, pas du total complet de la commande. Remplacez A999 par A103 : le montant final attendu devient 3 535 MAD.

Ajoutez ensuite une seconde ligne A102 à 190 MAD dans le catalogue. Le contrôle d’unicité doit détecter le problème avant que l’équipe valide le tarif. Supprimez cette copie après vérification, puis saisissez A103 avec une espace finale dans la commande. Analysez l’absence, normalisez la clé dans une colonne prévue à cet effet et retrouvez 95 MAD. Les trois essais vérifient des risques différents : référence inexistante, référentiel ambigu et donnée mal normalisée. Une seule formule qui affiche un nombre sur le cas heureux ne suffit pas à valider un processus de rapprochement.

Constituer une recette de partage du classeur

Avant diffusion, préparez cinq références de test : une connue, une absente, une dupliquée, une avec zéros initiaux et une contenant une espace parasite. Notez pour chacune le statut attendu et la décision opérationnelle. Ouvrez le fichier sur la version cible, provoquez un recalcul et modifiez un prix du catalogue pour vérifier que le résultat se met réellement à jour. Évitez de considérer une capture d’écran comme une preuve de compatibilité fonctionnelle.

Conservez une version du référentiel et expliquez si les commandes doivent garder leur prix historique ou suivre les évolutions du catalogue. Pour un devis validé, écraser rétrospectivement le montant par une actualisation peut être indésirable. Une recherche est alors un outil de proposition ou de contrôle, tandis que la transaction validée conserve son tarif. Cette distinction permet de construire un classeur exploitable par les ventes, le support et l’analyse sans confondre vérité actuelle du catalogue et réalité des opérations passées.

Lorsque les fichiers grossissent, examinez aussi le périmètre interrogé. Utiliser des tableaux structurés permet de cibler les lignes utiles sans transformer chaque recherche en comparaison sur des colonnes entières. Mesurez le temps d’ouverture et de recalcul avec un échantillon représentatif avant de multiplier les formules sur cent mille transactions. Si le même rapprochement est répété à chaque import mensuel, une fusion Power Query peut devenir plus claire qu’une colonne de recherches copiées. La migration doit néanmoins conserver les mêmes tests d’absence et d’unicité : déplacer une opération vers un autre outil ne corrige pas une clé métier incorrecte et ne justifie pas d’abandonner les contrôles existants.

Références

Retour au blog →