Trouver et extraire les données les plus proches avec Excel

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


Sujets que vous pourriez aussi aimer :


Trouver les données les plus proches

Cette nouvelle astuce Excel prouve comment faire ressortir instantanément les valeurs les plus proches d'une saisie grâce à des techniques matricielles.



Sur l'exemple finalisé illustré par la capture, nous travaillons sur un tableau d'un parc automobile. Sur la droite de ce dernier, l'utilisateur inscrit le budget du client. Aussitôt, les prix les plus proches des véhicules directement inférieur et supérieur sont extraits. Et dans le même temps, une règle de mise en forme conditionnelle fait surgir ces deux enregistrements dans la base de données.

Classeur source
Pour réaliser ces travaux, nous avons besoin de ce tableau des véhicules avec leurs prix.

• Télécharger le classeur trouver-donnee-proche.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ébouchons bien sur un tableau recensant des véhicules. Les prix respectifs sont inscrits en colonne G. Le budget du client doit être saisi en cellule I4. Et selon cette information, les deux prix les plus proches doivent être extraits respectivement en cellules I7 et I10.

Extraire la valeur numérique la plus proche
Pour connaître le prix directement inférieur au budget saisi, l'astuce consiste à trouver le montant le plus grand juste en-dessous de cette barre. C'est pourquoi la fonction Excel Grande.Valeur est nécessaire. Mais son champ d'action doit être limité aux prix effectivement en-dessous du budget. Nous allons donc exploiter la fonction conditionnelle Si pour restreindre la plage de l'étude aux valeurs nécessaires. Dès lors, nous pourrons extraire la plus grande des données restantes.

• Sélectionner le premier prix proche à trouver en cliquant sur sa cellule I7,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Inscrire la fonction de gestion d'erreur suivie d'une parenthèse, soit : SiErreur(,

Nous souhaitons en effet gérer les anomalies de calcul lorsqu'aucune correspondance n'est possible parce que le budget est trop bas par exemple.

• Inscrire ensuite la fonction des grandes valeurs suivie d'une parenthèse : Grande.Valeur(,

La colonne des prix est reconnue par son titre. Le nom Prix lui a été attribué. Mais comme nous l'avons dit précédemment, nous devons limiter cette matrice des prix à ceux situés sous le budget pour pouvoir extraire le plus grand d'entre eux.

• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Ouvrir une nouvelle parenthèse pour accueillir la matrice conditionnelle,
• Désigner la matrice des prix par son nom, soit : Prix,
• Inscrire le symbole moins (-) pour annoncer la soustraction à suivre,
• Puis, désigner le budget en cliquant sur la cellule I4,
• Fermer la parenthèse de la matrice conditionnelle,
• Inscrire alors l'inégalité suivante : <0,

Dans ce raisonnement matriciel, ce sont tous les prix qui vont être considérés par une seule et même formule. Chaque prix soustrait au budget conduisant à un résultat négatif sera retenu puisqu'inférieur à ce même budget. Pour cela, nous devons encore le mentionner.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Désigner de nouveau la matrice des prix par son nom, soit : Prix,

Du fait de la condition posée en amont, cette matrice correspond à une rangée filtrée sur les valeurs nécessairement inférieures au budget émis.

• Fermer ensuite la parenthèse de la fonction Si,

Le cas échéant de la branche Sinon ne nous intéresse pas. C'est la raison pour laquelle nous l'ignorons.

• Taper un point-virgule (;) pour passer dans l'argument du rang de la fonction Grande.Valeur,
• Saisir le chiffre 1 pour ne retenir que le prix le plus grand directement inférieur au budget,
• Fermer la parenthèse de la fonction Grande.Valeur,
• Taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,
• Inscrire deux guillemets ("") pour garder la cellule vide en cas d'anomalie,
• Fermer la parenthèse de la fonction SiErreur,
• Enfin, valider nécessairement la formule par le raccourci clavier CTRL + MAJ + Entrée,

La syntaxe complète et transformée avec les accolades est donc la suivante :

{=SIERREUR(GRANDE.VALEUR(SI((Prix-I4)<0; Prix); 1); "")}

Le premier prix surgit. Il s'agit effectivement du véhicule situé directement en-dessous du budget mentionné. Et si vous modifiez cette prétention en cellule I4, à validation vous constatez que l'extraction s'actualise parfaitement et en cohérence.

Maintenant, pour extraire le prix directement supérieur, la technique est identique. Mais dans la matrice filtrée par la condition, ce n'est plus le prix le plus grand qui nous intéresse, mais le prix le plus petit. Donc la fonction Grande.Valeur doit être remplacée par la fonction Petite.Valeur. Et bien entendu, l'inégalité du filtre doit être inversée pour ne considérer que les prix au-dessus du budget. Pour procéder, il est tout d'abord préférable de copier la précédente syntaxe du calcul.

• Sélectionner la cellule I10 puis cliquer dans sa barre de formule pour l'activer,
• Coller la précédente syntaxe (CTRL + V),
• Remplacer la fonction Grande.Valeur par la fonction Petite.Valeur,
• Remplacer l'inégalité : <0 par l'inégalité : >0,
• Puis, valider la formule matricielle par le raccourci clavier CTRL + MAJ + Entrée,

Là encore le résultat surgit et nous obtenons bien le prix directement supérieur au budget. En modifiant ce dernier, vous avez le plaisir de constater l'extraction des valeurs en parfaite cohérence avec la demande.

Surligner les enregistrements proches
Pour une application encore plus efficace et intéressante, nous proposons de mettre automatiquement en valeur les lignes des véhicules dont les prix ont été extraits. Le client pourra consulter avec beaucoup d'aisance le détail des informations qui les concernent. Pour cela, une simple règle de mise en forme conditionnelle faisant la correspondance avec les prix est nécessaire.

• Sélectionner toutes les données du tableau, soit la plage de cellules B4:G24,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour...,
• Puis, cliquer dans la zone de saisie du dessous pour l'activer,
• Taper le symbole égal (=) pour initier la syntaxe de la règle,
• Inscrire la fonction pour énumérer des conditions à empiler suivie d'une parenthèse, soit : Ou(,

En effet, nous devons repérer en même temps les deux enregistrements des prix extraits. Le critère doit pouvoir vérifier l'un comme l'autre, et non l'un avec l'autre.

• Désigner le premier prix en cliquant sur sa cellule G4,
• Enfoncer deux fois la touche F4 du clavier, ce qui donne : $G4,

De cette manière, nous libérons la ligne et figeons la colonne. En effet, pour chaque enregistrement, le critère sur le prix doit nécessairement être vérifié dans cette colonne G. Et comme ces prix doivent tous être passés en revue par l'analyse, nous laissons la ligne se déplacer.

• Taper le symbole égal (=) pour annoncer le critère à honorer,
• Sélectionner le premier des deux prix en cliquant sur sa cellule I7, soit : $I$7,

Cette fois, nous conservons la cellule totalement figée. Chaque prix du tableau doit être comparé à cette première valeur qui ne doit pas suivre le déplacement chronologique de l'analyse.

• Taper un point-virgule (;) et ajouter le critère suivant : $G4=$I$10,

Le principe est le même mais cette fois nous comparons chaque prix avec la borne supérieure extraite.

• Fermer la parenthèse de la fonction Ou,

Désormais, lorsque l'un ou l'autre prix est décelé, nous devons faire ressortir explicitement la ligne complète du véhicule concerné.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Police,
• Choisir un style gras et une couleur vert clair pour le texte avec la liste déroulante,
• Puis, cliquer sur le bouton Ok pour valider ces attributs,

Nous sommes de retour sur la première boîte de dialogue. Elle rappelle l'apparence que doivent prendre les lignes sélectionnées lorsqu'elles honorent l'un ou l'autre critère émis.

• Cliquer sur le bouton Ok pour valider la création de la règle,

De retour sur la feuille, vous notez que deux lignes surgissent instantanément. Et si vous modifiez le budget, en même temps que les prix sont extraits, les deux véhicules concernés ressortent de façon très explicite.