Trouver le rang directement supérieur ou inférieur

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


Sujets que vous pourriez aussi aimer :


Recherches approchantes avec RechercheX

Dans les deux précédents chapitres, nous avons découvert la fonction Excel RechercheX. Nous avons appris qu'elle pouvait réaliser des recherches verticales dans un tableau, à droite comme à gauche. Mais nous avons aussi compris qu'elle pouvait réaliser des recherches horizontales. Dans ce nouveau volet, nous allons voir qu'elle est aussi capable de se caler sur les valeurs les plus proches lorsque la donnée cherchée n'existe pas.



Sur l'exemple illustré par la capture, un premier tableau dresse les barèmes à attribuer en fonction des scores obtenus. Un second tableau énumère les résultats de certains candidats. Ces notes ne correspondent pratiquement jamais aux paliers des barèmes ainsi définis. Dans un premier cas, nous attribuons un grade lorsque le score est égal ou directement supérieur et dans un second cas, lorsqu'il est égal ou directement inférieur. Et le moins que l'on puisse dire est que la modification d'un simple paramètre conduit à des sentences très différentes.

Classeur Excel à télécharger
Pour comprendre la subtilité des recherches approchantes avec la fonction Excel RechercheX, nous suggérons d'appuyer l'étude sur un classeur hébergeant ce tableau des barèmes.

• Télécharger le classeur recherches-par-tranches.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é,

Sur l'unique feuille de ce classeur, nous retrouvons bien le tableau des barèmes accompagné du tableau des scores obtenus par certains candidats. En colonne H, sont attendues les évaluations les plus clémentes. Dès que la note s'approche d'un palier supérieur, l'évaluation positive correspondante lui est attribuée. En colonne I, ce sont les évaluations les plus sévères qui sont attendues. Si la note n'atteint pas le palier supérieur inscrit en colonne G, c'est l'évaluation la plus pessimiste du palier inférieur qui lui est octroyée.

Catégorie directement supérieure
La fonction Excel RechercheX peut accueillir six paramètres. Les trois premiers sont obligatoires tandis que les trois derniers sont facultatifs. Le cinquième permet de définir le mode de correspondance avant de réaliser l'extraction. Il peut s'agir d'une extraction basée sur l'élément directement supérieur ou sur l'élément directement inférieur.

• Cliquer sur la case H4 de la première évaluation optimiste à livrer,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : RechercheX(,
• Désigner la note à chercher dans le tableau des barèmes par ses coordonnées, soit : G4,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Désigner la plage des scores dans le tableau du barème, soit : C4:C8,
• Puis, enfoncer la touche F4 du clavier pour la figer, ce qui donne : $C$4:$C$8,

En effet, nous allons reproduire cette formule sur les lignes du dessous pour livrer toutes les évaluations en fonction des différentes notes obtenues. Ces dernières doivent toujours être comparées aux tranches de la plage des scores du barème, dont les bornes ne doivent donc pas bouger en même temps que la formule est répliquée sur les lignes suivantes.

• Taper un point-virgule (;) pour passer dans l'argument de la plage de retour,
• Désigner les évaluations correspondantes en sélectionnant la plage de cellules D4:D8,
• Comme précédemment enfoncer la touche F4 pour la figer, soit : $D$4:$D$8,
• Puis, taper deux points virgules successifs, soit : ;;,

De cette manière, nous ignorons le quatrième argument facultatif pour atteindre directement le cinquième. Celui-ci concerne le mode de correspondance pour réaliser l'extraction (Optimiste ou pessimiste).



C'est bien ce que confirme l'infobulle qui se déclenche automatiquement.

• Taper le chiffre 1 pour tendre vers l'élément supérieur en cas de non-correspondance,
• Fermer la parenthèse de la fonction RechercheX,
• Puis valider la formule par le raccourci clavier CTRL + Entrée,

Ainsi, nous conservons active la cellule du résultat. Et comme vous pouvez le voir, avec une note de 88, bien que le palier pour le grade A ne soit pas atteint, c'est bien cette évaluation optimiste qui est attribuée grâce à cette correspondance approximative basée sur l'élément directement supérieur.

• Double cliquer sur la poignée du résultat pour reproduire la logique sur les cellules du dessous,

Selon le même schéma, l'évaluation du palier directement supérieur est attribuée à chaque note.

Catégorie directement inférieure
Maintenant, nous allons voir comment une légère différence d'interprétation, par le biais de ce cinquième paramètre, peut considérablement influer sur les résultats et les issues attachées.

• En cellule I4, adapter la précédente syntaxe comme suit :

=RECHERCHEX(G4; $C$4:$C$8; $D$4:$D$8;; -1)

C'est simplement le cinquième paramètre que nous inversons pour réaliser une extraction exacte ou à défaut basée sur l'élément inférieur suivant.

• Valider la formue par le raccourci clavier CTRL + Entrée,
• Puis, double cliquer sur la poignée du résultat pour répliquer la logique,

Vous en conviendrez, la simple intervention sur ce cinquième paramètre conduit à des sentences bien différentes, la plupart du temps.