Trouver l'écart le plus faible sur des données Excel

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


Sujets que vous pourriez aussi aimer :


Extraire les valeurs numériques voisines

Nous avions déjà appris à isoler les valeurs numériques les plus proches grâce à des techniques matricielles. Pour cela, nous avions engagé les fonctions Petite.Valeur et Grande.Valeur. Nous proposons de traiter de nouveau ce sujet mais avec une approche différente et plus simple. Elle consiste à exploiter cette fois les fonctions Max et Min.

Classeur Excel à télécharger
Pour appuyer les démonstrations, nous suggérons de récupérer un classeur offrant des données numériques à analyser.

• Télécharger le classeur plus-proches.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 le tableau des valeurs numériques entre les colonnes B et G et entre les lignes 5 et 14. Sur la droite, deux calculs sont attendus en fonction de la valeur renseignée en cellule I6. Il est tout d'abord question d'extraire le nombre le plus proche de cette valeur en cellule I9. Puis, il est question d'extraire la donnée la plus grande directement inférieure, en cellule I12.

Trouver la valeur la plus proche
Pour trouver la donnée la plus proche, nous allons engager un raisonnement matriciel conditionnel. Pour chaque cellule du tableau, nous devons vérifier si la valeur absolue de la différence entre la cellule en cours et la valeur cherchée est égale à la plus petite de ces différences dans le tableau.

• En cellule I6, remplacer le nombre 116 par le nombre 113,

Sa valeur la plus proche est le nombre 112 placé en début de tableau. En exploitant l'assistant fonction, nous verrons ainsi les données placées au début des matrices réagir en cohérence, au fur et à mesure que nous bâtirons la formule.

• Cliquer désormais sur la cellule I9 pour la sélectionner,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Inscrire la fonction rendant le plus petit nombre, suivie d'une parenthèse, soit : Min(,
• Inscrire alors la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Cliquer sur le petit bouton de l'assistant fonction à gauche de la barre de formule,

• Inscrire alors la fonction pour la valeur absolue suivie d'une parenthèse, soit : Abs(,
• Taper ensuite le nom du tableau, soit : tab,
• Inscrire le symbole de soustraction (-),
• Puis, cliquer sur la cellule I6 de la valeur tapée par l'utilisateur,
• Fermer alors la parenthèse de la fonction Abs,

De cette manière et dans ce raisonnement matriciel, nous calculons la valeur absolue de toutes les différences entre chaque cellule du tableau et la cellule I6. Dans cette matrice résultante, nous souhaitons déceler la différence la plus petite de toutes. C'est donc maintenant qu'intervient la condition à honorer.

• Taper le symbole égal (=) pour annoncer le critère à suivre,
• Inscrire la fonction Min suivie d'une parenthèse, pour trouver la plus petite différence,
• Reproduire exactement le même calcul de différence que précédemment : abs(tab-I6),
• Puis, fermer la parenthèse de la fonction Min,

Grâce à cette égalité, notre critère consiste bien à déceler la différence la plus petite de toutes. Sur la droite de la zone Test_logique, vous voyez apparaître une matrice de booléens. Elle livre son verdict. Le nombre le plus proche (Vrai) est placé en première position du tableau.

• Dans la zone Valeur_si_vrai inscrire de nouveau le nom du tableau, soit : tab,

Désormais, grâce à la matrice relatant le tableau de données, la fonction Si livre son verdict en bas de la boîte de dialogue, sous forme de matrice bien entendu. Comme seul le premier nombre répond favorablement au critère, il est accompagné d'une ribambelle de booléens Faux. Et comme ce calcul conditionnel est embarqué dans la fonction Min, cette dernière va en extraire la valeur la plus petite en ignorant tout ce qui n'est pas numérique. Il va donc en résulter le nombre 112, soit la valeur la plus proche du nombre tapé par l'utilisateur.

• Valider la boîte de dialogue par le raccourci clavier CTRL + MAJ + Entrée,
• Puis, valider la correction demandée par Excel en cliquant sur Oui,

C'est ainsi qu'il ajoute la parenthèse fermante de la fonction Min qui englobe le calcul. Et c'est bien le nombre 112 qui est extrait.

Maintenant, vous avez tout le loisir de modifier à de multiples reprises la valeur en cellule I6, pour constater que c'est à chaque reprise la donnée la plus proche qui est effectivement extraite. Et dans le même temps, vous notez qu'une mise en forme conditionnelle prédéfinie fait surgir cette valeur en vert dans le tableau, pour un repérage instantané.



Valeur directement inférieure
Maintenant, en cellule I12, nous devons extraire la valeur la plus proche mais directement inférieure. Il est donc question d'isoler la plus grande valeur en dessous du nombre tapé. Grâce à un nouveau raisonnement matriciel conditionnel embarqué dans la fonction Max, la mise en oeuvre est un jeu d'enfant.

• Cliquer sur la cellule I12 pour la sélectionner,
• Taper le symbole égal (=) pour débuter la syntaxe de la formule matricielle,
• Inscrire la fonction rendant le plus grand nombre, suivie d'une parenthèse, soit : Max(,
• Dès lors, inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Puis, cliquer sur le bouton de l'assistant fonction à gauche de la barre de formule,
• Dans la zone Test_logique, taper le critère suivant : tab<I6,

Une matrice de booléens apparaît. Elle indique les positions des données conformes au critère.

• Dans la zone Valeur_si_vrai, inscrire le nom du tableau, soit : tab,

Comme vous pouvez le voir en bas de la boîte de dialogue, nous obtenons une matrice des nombres effectivement inférieurs à la valeur tapée, accompagnés de booléens Faux. Une fois encore, comme ce calcul est embarqué dans la fonction Max, il va en résulter la valeur la plus grande directement inférieure à la donnée saisie par l'utilisateur.

• Valider la boîte de dialogue par le raccourci clavier CTRL + MAJ + Entrée,
• Puis, accepter la correction proposée par Excel pour la parenthèse manquante,

Désormais et comme vous pouvez l'apprécier, dès que vous changez de valeur en cellule I6, la donnée numérique la plus grande et directement inférieure est automatiquement extraite.