Fonction VBA Excel d'évaluation par paliers

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


Sujets que vous pourriez aussi aimer :


Fonction VBA Excel d'évaluation

C'est une nouvelle fonction VBA que nous proposons d'ajouter à Excel, telle une nouvelle corde à son arc. Son rôle est de permettre une évaluation automatisée par tranches de critères numériques.



Sur l'exemple illustré par la capture, en fonction de notes obtenues par des candidats en avant-dernière colonne d'un premier tableau, ce sont des appréciations correspondantes qui sont distillées en dernière colonne. Cette évaluation par paliers numériques est réalisée en fonction d'une grille de critères implantée dans un second tableau sur la droite du premier. Par exemple, ce sont les félicitations qui sont automatiquement retournées lorsque le score dépasse la note de 18 et les encouragements entre 15 et 18. Cinq autres paliers sont encore observés jusqu'à atteindre le score le plus bas possible.

Classeur Excel à télécharger
Pour créer cette fonction, nous suggérons d'appuyer l'étude sur un classeur offrant ce tableau et cette grille de conditions réparties par tranches.

• Télécharger le classeur fonction-criteres-multiples.xlsm 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 retrouvons effectivement le tableau des résultats entre les colonnes B et E et la grille des critères entre les colonnes G et H.

Créer la fonction d'évaluation
Nous l'avons dit, cette fonction doit évaluer les scores obtenus en tenant bien sûr compte de la note mais aussi des tranches numériques du second tableau. Elle doit donc être déclarée avec deux paramètres en attente. Le premier doit être une cellule et le second, une plage de cellules. Il est intéressant de comprendre que nous allons créer une fonction capable de s'adapter si d'aventure le nombre de paliers numériques augmentait ou diminuait.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• En haut de l'éditeur, cliquer sur le menu Insertion et choisir l'option Module,

Ainsi, nous créons un nouveau module dans ce projet. Sa feuille vierge apparaît au centre de l'écran. Cela signifie que cette fonction ne sera pas disponible pour toutes les utilisations d'Excel mais seulement pour ce classeur. Mais nous savons créer des fonctions VBA disponibles tout le temps. Il faut simplement créer un complément Excel comme le rappelle la formation du lien hypertexte. Donc, si elle vous intéresse pour des utilisations régulières, vous saurez comment faire. D'ailleurs, vous pouvez même documenter ces nouvelles fonctions comme le rappelle cette formation pour les enregistrer dans la bibliothèque Excel.

• Dans la feuille de code, créer la fonction suivante :

Function notation(cel As Range, cond As Range) As String

End Function

Nous créons donc une fonction de type texte (As String). En effet, elle doit retourner des observations ou plutôt évaluations textuelles. En premier paramètre, nous déclarons l'objet cel (As Range) pour réceptionner la cellule de la note à évaluer. En second paramètre, nous déclarons la plage cond, celle des tranches numériques de critères.

Initialiser les variables objets
Pour prendre possession de ces paramètres, nous devons maintenant déclarer des variables de mêmes types et les affecter afin de les représenter.

• Dans les bornes de la fonction, ajouter les déclarations et affectations suivantes :

...
Dim celluleN As Range
Dim cellule As Range: Dim plage As Range

Set celluleN = cel
Set plage = cond
...

Il s'agit de trois objets représentant des plages ou à défaut une cellule unique. celluleN doit représenter la cellule de la note à évaluer. plage doit représenter la plage des conditions numériques par paliers. cellule est une variable plus générale représentant une cellule au sens large, pour passer en revue toutes celles de la plage des tranches numériques. Et pour cela, nous l'exploiterons dans une boucle For Each.

Parcourir les tranches de critères
Maintenant, nous devons analyser chaque condition émise par la grille de requêtes en passant en revue chacune de ses valeurs numériques avec une boucle For Each, comme nous l'avons annoncé. Grâce à elle, nous pourrons confronter la note de la cellule passée en premier argument pour savoir si elle entre dans la catégorie conditionnelle. C'est ainsi que nous pourrons retourner l'évaluation correspondante.

• A la suite du code VBA, créer la boucle For Each comme suit :

...
For Each cellule In plage

Next cellule
...

Notre objet cellule est du même type que l'objet plage et c'est important. C'est grâce à lui que nous pouvons parcourir toutes les cellules de la zone de critères numériques, passée en second paramètre.

Trouver la tranche du score
A chaque passage dans cette boucle désormais, nous devons vérifier un critère. Il consiste à comparer la valeur de la note du candidat avec le seuil de la tranche en cours d'analyse. Si cette note est supérieure, nous saurons que le bon palier est trouvé. Donc, nous pourrons retourner l'appréciation correspondante. Pour émettre une condition, nous devons déployer une instruction conditionnelle.

• Dans les bornes de la boucle, ajouter les lignes VBA suivantes :

...
If (cel.Value > cellule.Value) Then
notation = Cells(cellule.Row, cellule.Column - 1).Value
Exit For
End If
...

Lorsque la note (cel.Value) est supérieure au critère numérique en cours d'analyse (cellule.Value), nous retournons l'information de la colonne précédente (cellule.Column - 1) sur la même ligne (cellule.Row). Il s'agit de l'appréciation correspondant à la tranche en cours. Et comme vous le savez en VBA, ce retour se fait par le nom même de la fonction (notation). Enfin et surtout, nous sortons de la boucle (Exit For). En effet, si une note est élevée, elle va correspondre à toutes les tranches. Nous devons donc nous arrêter sur la première trouvée, la plus élevée.

Tester la fonction d'évaluation
Il est temps d'utiliser cette nouvelle fonction pour vérifier les résultats qu'elle retourne.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille du classeur (ALT + Tab),
• Sélectionner la cellule E4 pour désigner l'emplacement de la première appréciation à livrer,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire le nom de la fonction suivi d'une parenthèse, soit : notation(,
• Désigner la première note à évaluer en cliquant sur sa cellule D4,
• Taper un point-virgule (;) pour passer dans l'argument de la plage de critères,
• Sélectionner tous les critères numériques, soit la plage de cellules H4:H9,
• Enfoncer la touche F4 du clavier pour figer cette plage, ce qui donne : $H$4:$H$9,

En effet, cette formule est ensuite destinée à être répliquée sur les lignes du dessous. En suivant le mouvement, ce sont bien les notes du dessous qui devront être confrontées tour à tour à la plage de critères. Et précisément cette plage de critères elle, ne doit pas bouger pendant la réplication.

• Fermer la parenthèse de la fonction notation,
• Puis, valider la formule avec le raccourci clavier CTRL + Entrée,

Grâce à lui et comme vous le savez, nous gardons active la cellule du résultat pour pouvoir l'exploiter dans l'enchaînement sans devoir la resélectionner. Quoiqu'il en soit et comme vous pouvez le voir, la première sentence tombe. Il s'agit de l'appréciation Assez bien qui correspond effectivement à la fourchette de critères 12 à 15.

• Double cliquer sur la poignée du résultat pour répliquer la logique sur la hauteur du tableau,

Toutes les appréciations sont désormais livrées et elles sont toutes parfaitement cohérentes avec la grille de critères.



Bien sûr, si vous changez l'une ou l'autre note, vous voyez que l'appréciation s'actualise automatiquement à validation. Cette fonction d'analyse multicritère est particulièrement intéressante dans la mesure où elle accepte une plage de cellules non figée pour gérer ses conditions évolutives. Par exemple, vous pouvez très bien ajouter une nouvelle ligne dans cette grille de critères. Dès lors, si vous adaptez la formule déjà en place en agrandissant la plage de son second argument, vous remarquez qu'elle considère instantanément cette nouvelle tranche.