Correspondances approximatives sur critères recoupés

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Correspondances approximatives et critères

Nous savons déjà réaliser des extractions sur la base de recherches approximatives, notamment grâce aux fonctions Index et Equiv. Mais lorsqu'il s'agit d'effectuer ces recherches en considérant des critères, la situation se complique. Nous devons être en mesure d'analyser la ou les conditions sur l'ensemble des lignes du tableau de données. Et dans ce contexte, c'est forcément un raisonnement matriciel qui permet de résoudre le cas.



Dans l'exemple illustré par la capture, nous travaillons sur une petite base de données de véhicules décrits par leur marque, leur modèle et leur prix. Sur la droite de ce tableau, l'utilisateur peut définir une marque et un prix plafond. En fonction de ces deux critères, le modèle correspondant est extrait avec son prix.

Source et présentation
Pour aboutir cette solution, nous devons tout d'abord récupérer ce tableau de données.

• Télécharger le classeur correspondances-approximatives.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous découvrons donc le petit tableau de données situé entre les colonnes B et D. Sur sa droite, un tableau de bord permet d'actionner deux leviers.



L'utilisateur peut tout d'abord définir la marque en cellule G4 à l'aide d'une liste déroulante. Il peut ensuite spécifier le prix à ne pas dépasser en cellule G5. En fonction de ces deux conditions, le nom et le prix du modèle le plus proche doivent être extraits en cellules G6 et G7.



Si vous déployez la liste déroulante de la zone Nom, en haut à gauche de la feuille Excel, vous notez que chaque colonne est intitulée en fonction de son titre de champ. Ces noms sont précieux pour simplifier la syntaxe de l'expression matricielle à construire.

Extraction approchante
Pour effectuer l'extraction la plus proche en fonction des deux conditions recoupées, nous devons observer deux règles. Tout d'abord, le dernier paramètre de la fonction Equiv doit être ajusté pour ne pas réaliser une recherche exacte. Et puis, la matrice de recherche de cette même fonction Equiv doit être contrainte par le critère sur la marque.

• Sélectionner la cellule du modèle à extraire, soit G6,
• Taper le symbole égal (=) pour démarrer la construction de la formule matricielle,
• Inscrire la fonction de gestion des anomalies suivie d'une parenthèse, soit : SiErreur(,

En effet, en cas de prix trop bas, aucune correspondance ne pouvant être trouvée, les fonctions d'extraction répondront par un message d'erreur. C'est ainsi que nous choisissons de le neutraliser.

• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner la colonne des modèles par son nom, soit : Modèle,

C'est en effet le modèle de la marque au prix le plus proche que nous souhaitons extraire de cette colonne.

• Taper un point-virgule (;) pour passer dans l'argument de la ligne de la donnée cherchée,

Cette position doit être trouvée grâce à la fonction de recherche Equiv.

• Inscrire la fonction de recherche suivie d'une parenthèse, soit : Equiv(,
• Désigner le prix proche cherché en cliquant sur sa cellule G5,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,

Cette colonne est bien entendu celle des prix. Mais tous ne doivent pas être considérés. Seuls doivent subsister ceux pour lesquels la marque concorde avec la demande formulée par le biais de la liste déroulante en cellule G4. Nous devons donc émettre une condition sur cette matrice.

• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Désigner la matrice des marques par son nom, soit : Marque,
• Taper le symbole égal (=) pour annoncer la condition à honorer,
• Cliquer sur la cellule de la marque choisie, soit la cellule G4,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Puis, désigner la matrice des prix par son nom, soit : Prix,

Grâce à la condition posée en Amont, seuls les prix correspondant à la marque seront sollicités.

• Fermer la parenthèse de la fonction Si,

Il n'est en effet pas nécessaire de renseigner la branche Sinon de la fonction Si. Lorsque la marque ne coïncide pas, les prix doivent être exclus de la recherche. De plus, toutes les erreurs vont être neutralisées par la fonction SiErreur.

• Taper un point-virgule (;) pour passer dans le dernier argument de la fonction Equiv,
• Choisir la première proposition matérialisée par le chiffre 1,

De cette manière, nous cherchons à extraire le modèle correspondant à la marque choisie et pour lequel le prix le plus proche est directement inférieur au plafond mentionné.

• Fermer la parenthèse de la fonction Equiv,
• Puis, fermer la parenthèse de la fonction Index,
• Taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,
• Inscrire le texte Aucun résultat entre guillemets, soit : 'Aucun Résultat',
• Fermer la parenthèse de la fonction SiErreur,
• Enfin, valider nécessairement la formule matricielle par le raccourci CTRL + MAJ + Entrée,

Le premier modèle apparaît aussitôt. Et si vous consultez le prix le plus proche pour la marque choisie, vous constatez que l'extraction approchante est parfaitement cohérente. Bien sûr, si vous modifiez le plafond, à 10 500 Euros par exemple, le modèle extrait s'adapte. Et si vous changez de marque pour Renault par exemple, le modèle s'ajuste une fois de plus parfaitement.



La syntaxe de la formule matricielle que nous avons construite est la suivante :

{=SIERREUR(INDEX(Modèle; EQUIV(G5; SI(Marque=G4; Prix); 1)); 'Aucun Résultat')}

Pour un résultat plus pertinent, il est opportun d'associer le prix exact du modèle extrait. Il suffit simplement d'adapter la précédente syntaxe en déplaçant l'extraction sur la matrice des prix en premier paramètre de la fonction Index.

• En cellule G7, adapter la syntaxe matricielle comme suit :

{=SIERREUR(INDEX(Prix; EQUIV(G5; SI(Marque=G4; Prix); 1)); 'Aucun Résultat')}

• Puis valider la formule par le raccourci clavier CTRL + MAJ + Entrée,

Désormais, le client intéressé peut directement être renseigné par rapport à son choix de marque et à sa limitation budgétaire.

Repérer le véhicule extrait
Pour un résultat encore plus pertinent et évident, nous proposons désormais de surligner dynamiquement la ligne du véhicule extrait dans la base de données. Cette alerte visuelle permettra de recouper efficacement la proposition émise par l'extraction sur les critères recoupés. Pour cela, nous devons bâtir une règle de mise en forme conditionnelle vérifiant l'égalité sur le prix et le modèle extraits.

• Sélectionner toutes les données du tableau, soit la plage de cellules B4:D19,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir la commande Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Dans la zone de saisie du dessous, construire la règle suivante :

=ET($C4=$G$6; $D4=$G$7)

C'est grâce à la fonction Et que nous pouvons vérifier ensemble les deux conditions. L'analyse d'une mise en forme conditionnelle est chronologique. C'est la raison pour laquelle nous initions les critères sur le premier modèle et le premier prix. Tous deux sont seulement figés en colonne ($C4 et $D4). C'est ainsi que les lignes du dessous seront analysées à leur tour à la recherche de la correspondance. Les cellules de références issues du tableau de bord sont quant à elles totalement figées ($G$6 et $G$7). Elles ne doivent en effet pas bouger en même temps que l'analyse progresse. Nous devons maintenant appliquer des attributs de format explicites lorsque la double condition est honorée.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un gris foncé,
• Activer ensuite l'onglet Police de la boîte de dialogue,
• Avec la liste déroulante, choisir un vert assez vif pour le texte,
• Puis, choisir le Style gras et valider ces attributs par le bouton Ok,
• De retour sur la première boîte de dialogue, valider la création de la règle avec le bouton Ok,

Désormais et comme vous pouvez le voir, à chaque modification de contrainte, en même temps que le véhicule le plus proche est extrait avec son prix, il est mis en évidence dynamiquement dans le tableau de données.