Fonctions Excel pour compter selon les couleurs de fond

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


Sujets que vous pourriez aussi aimer :


Créer des fonctions Excel pour calculer sur les couleurs

Dans cette formation VBA Excel, nous proposons de créer deux nouvelles fonctions, capables de réaliser des calculs conditionnels, en fonction de la couleur de remplissage des cellules. Excel propose déjà les fonctions permettant de réaliser des sommes et dénombrements conditionnels. Nous les avions d'ailleurs exploitées dans la formation pour classer les résultats de chiffres d'affaires. Mais ces calculs se font selon un critère numérique la plupart du temps.



C'est pourquoi nous souhaitons ajouter deux fonctions, sur le même principe que les fonctions Somme.Si et Nb.Si, afin de réaliser ces calculs, selon la couleur de fond des cellules. C'est d'ailleurs ce qu'illustre la capture ci-dessus pour l'une d'entre elles. La fonction nbCouleurs, créée par le code Visual Basic, permet de comptabiliser sur une plage de cellules, toutes les fois qu'un code couleur est répété. A l'instar de la fonction Nb.Si, elle requiert deux arguments. Il faut tout d'abord définir la plage de cellules sur laquelle elle doit opérer, puis il faut désigner la cellule de référence portant le code couleur à comparer.

Source et présentation des objectifs
Pour tester les fonctions que nous allons créer, nous avons besoin de données numériques appropriées et mises en forme avec des couleurs distinctes. C'est pourquoi nous proposons de récupérer un classeur source avant de débuter.

• Télécharger le fichier fonction-excel-compter-couleurs.xlsm en cliquant sur son lien,
• L'ouvrir dans Excel puis cliquer sur le bouton Activer la modification du bandeau de sécurité,

Comme vous le constatez, ce classeur est constitué de deux feuilles. Nous allons en effet bâtir une fonction de somme conditionnelle sur les couleurs. Et comme son homologue Somme.Si, elle proposera de renseigner trois arguments, le dernier devant être facultatif. Si le troisième argument n'est pas défini, cette somme cherchera le critère et effectuera la somme sur la même plage, celle passée en premier argument. S'il est défini, elle cherchera le critère sur la première plage et réalisera la somme correspondante sur la seconde.

La première feuille de ce classeur nommée classement, permettra de tester la fonction de dénombrement conditionnel ainsi que la somme dans sa plus simple expression, sans le troisième argument. La seconde feuille nommée Sommes_couleurs, permettra de tester cette fonction de somme conditionnelle dans sa version surchargée, soit avec tous les arguments renseignés.

Repérer les couleurs par le code VBA
Avant de nous lancer dans l'élaboration de ces fonctions, nous proposons de bâtir un tout petit code VBA. Celui-ci a pour mission d'apprendre à récupérer les indices numériques des couleurs de fond appliquées aux cellules. Ainsi, nous mettrons en application ces acquis pour l'élaboration des fonctions conditionnelles.

• Basculer dans l'éditeur de code VBA Excel à l'aide du raccourci ALT + F11 par exemple,
• Dans l'explorateur de projet sur la gauche, déployer l'affichage du dossier Modules,
• Puis, double cliquer sur l'élément Module1 pour afficher sa feuille de code,

Celle-ci est vide pour l'instant. Elle doit accueillir le code de nos fonctions.

• Créer la fonction numCouleur comme suit :

Function numCouleur(cellule As Range) As Integer

End Function

Comme nous l'avait appris la formation VBA Excel pour créer des fonctions, c'est le mot clé Function qui permet de déclarer une telle procédure de code. Contrairement à une procédure normale, une fonction doit retourner une valeur après son traitement. Et cette valeur est retournée par le nom même de la fonction à laquelle elle est affectée. Une fonction doit être typée. Nous la dimensionnons donc comme un entier (Integer), puisqu'elle doit retourner l'indice numérique de la couleur appliquée dans une cellule. Une fonction réalise un traitement selon des paramètres qui lui sont passés, comme les fonctions de calcul d'une feuille Excel. Entre ses parenthèses, nous déclarons donc le paramètre cellule, typé comme un objet Range. Un objet de type Range désigne une plage de cellules. Une seule cellule est bien une plage particulière. Cette fonction attend donc que l'utilisateur clique sur la cellule à partir de laquelle l'indice numérique de couleur doit être retourné.

• Entre les bornes de la fonction, ajouter les deux instructions suivantes :

Application.Volatile
numCouleur = cellule.Interior.ColorIndex

La méthode Volatile de l'objet VBA Application permet de forcer le recalcul automatique à chaque événement intercepté sur la feuille, comme pour toute fonction de calcul Excel. L'objet cellule passé en paramètre étant de type Range, sa propriété Interior permet de désigner son contenu. Et c'est alors la propriété dérivée ColorIndex qui permet de prélever l'indice de la couleur remplissant son contenu. Nous affectons ce résultat numérique au nom de la fonction. Elle se chargera ainsi de retourner la valeur en lieu et place, soit là où la formule est écrite.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Classement (ALT + F11),
• Sélectionner par exemple la cellule C18 pour un test temporaire,
• Taper le symbole = pour débuter le calcul,
• Saisir les premières lettres de la fonction numCouleur,

Comme vous le constatez, elle est reconnue par Excel puisqu'elle apparaît dans les propositions de fonctions, dont les noms commencent par les mêmes lettres.

• Double cliquer sur la fonction NumCouleur pour finir son écriture, soit numCouleur(,
• Sélectionner par exemple la dernière cellule de couleur de la colonne C, soit C16,
• Fermer la parenthèse de la fonction et valider le calcul,

Comme l'illustre la capture ci-dessus, notre fonction fraîchement créée, retourne la valeur 40. Il s'agit du code couleur correspondant au remplissage de la cellule désignée en paramètre. Si vous remplacez la cellule C16 par la cellule C15, dans le paramètre de la fonction, vous obtenez la valeur 19 après validation. Comme la couleur de fond change, son code s'ajuste. Nous sommes donc bien en mesure de reconnaître la différence entre les couleurs appliquées aux cellules. Il s'agit d'exploiter ces techniques VBA pour créer les deux fonctions conditionnelles.

Dénombrement selon les couleurs des cellules
La fonction Excel Nb.Si demande deux paramètres. Il s'agit tout d'abord de la plage de cellules sur laquelle doit être compté le critère et ensuite, le critère lui-même. Notre fonction nbCouleurs doit proposer la même structure. Elle doit attendre tout d'abord la plage de cellules sur laquelle la couleur doit être comptée, puis la cellule de référence à partir de laquelle la couleur doit être prélevée. Ses deux paramètres sont donc de type Range. La fonction elle-même doit retourner une valeur numérique. Nous devons la typer comme un Integer.

• Revenir dans l'éditeur de code VBA Excel (ALT + F11),
• Sous la fonction numCouleur, créer la fonction nbCouleurs, comme suit :

Function nbCouleurs(plage As Range, cellule As Range) As Integer

End Function

Nous créons la signature de la fonction avec ses deux paramètres attendus et son type. Il s'agit de parcourir l'ensemble des cellules contenues dans la plage passée en premier argument de la fonction. L'objectif est de comparer la couleur de chacune avec la couleur de la cellule de référence. Nous devons donc commencer par déclarer la variable permettant de désigner chaque cellule, au passage dans la boucle de traitement.

• Dans les bornes de la fonction, ajouter les deux instructions suivantes :

Application.Volatile
Dim chaqueCellule As Range

Tout d'abord et comme précédemment, nous exploitons la méthode Volatile de l'objet Application, afin de créer une fonction de calcul dynamique. Puis nous déclarons la variable chaqueCellule comme un objet de type Range. C'est elle qui va nous permettre de parcourir chaque cellule de la plage dans une boucle For Each.

• En conséquence, à la suite du code de la fonction, créer les bornes de la boucle, comme suit :

nbCouleurs = 0

For Each chaqueCellule In plage

Next chaqueCellule

Nous commençons par initialiser la valeur de départ que la fonction doit retourner si aucune cellule ne correspond à la couleur demandée. Puis, nous initialisons la boucle For Each qui littéralement permet de parcourir chaque cellule contenue dans la plage passée en paramètre, reconnue sous le nom de variable plage.

A l'intérieur de cette boucle, il s'agit d'incrémenter la valeur que la fonction nbCouleurs doit retourner, dans la mesure où les couleurs coïncident. Un test sur les propriétés ColorIndex de chacune est nécessaire. Et ce test peut être réalisé à chaque passage dans la boucle, donc pour chaque cellule analysée, grâce à une instruction conditionnelle VBA Excel.

• A l'intérieur de la boucle, ajouter les instructions suivantes :

If chaqueCellule.Interior.ColorIndex = cellule.Interior.ColorIndex Then
nbCouleurs = nbCouleurs + 1
End If

Nous comparons donc la couleur de fond de chaque cellule avec la couleur de fond de la cellule de référence, passée en deuxième argument de la fonction nbCouleurs. Si le test est validé par l'instruction If, donc si les couleurs correspondent, nous incrémentons la variable qui porte le nom de la fonction, afin de restituer ce résultat sur la feuille de calcul.

Le code VBA est déjà terminé et vous en conviendrez il est très simple. Pourtant, nous allons le constater, les résultats conditionnels qu'elle retourne sont fort précieux. Il s'agit de tester notre fonction nbCouleurs sur le premier petit tableau de la feuille Classement.

• Enregistrer les modifications et basculer sur la feuille Classement,

Dans le tableau situé entre les colonnes H et L et entre les lignes 5 et 9, il s'agit de compter chaque code couleur référencé en colonne H pour chaque vendeur identifié entre les colonnes C et F du tableau de synthèse.

• Sélectionner la première cellule du calcul, soit la cellule I6,
• Taper le symbole = pour débuter le calcul,
• Taper les premières lettres de la fonction et double cliquer sur la proposition dans la liste, ce qui donne : nbCouleurs(,
• Sélectionner la plage de cellules du premier vendeur, soit C5:C16,
• Enfoncer deux fois la touche F4 du clavier pour ne la figer qu'en ligne, ce qui donne : C$5:C$16,

En effet, le calcul doit être répliqué pour les autres vendeurs. La colonne du décompte doit s'adapter et donc se déplacer, elle ne doit pas être figée. En revanche, les plages sont nécessairement comprises entre les lignes 5 et 16. En répliquant le calcul vers le bas, pour les autres codes couleurs, ces bornes doivent rester fixes.

• Taper un point-virgule (;) pour passer dans l'argument de la cellule de couleur de référence,
• Saisir la référence H6 de la première cellule de couleur située en regard du calcul,
• Enfoncer trois fois de suite la touche F4 du clavier pour ne la figer qu'en colonne, soit $H6,

Inversement cette fois en effet, le code couleur doit changer lorsque nous répliquerons le calcul vers le bas. Mais, comme il est nécessairement situé en colonne H et seulement dans cette colonne, lorsque nous le répliquerons sur la droite pour les autres vendeurs, il ne doit pas bouger.

• Fermer la parenthèse de la fonction et valider le calcul par CTRL + Entrée,
• Puis tirer la poignée du calcul sur la droite jusqu'en colonne L,
• Tirer alors la poignée de la sélection vers le bas jusqu'en ligne 9,

La formule est parfaitement répliquée et fournit des résultats tout à fait cohérents. Notre fonction permettant de compter les cellules possédant la même couleur que celle de la cellule de référence passée en argument, fonctionne parfaitement. Nous avons donc créé une nouvelle fonction Excel pour le dénombrement conditionnel, selon la couleur.

Le code complet de la fonction nbCouleurs est le suivant :

Function nbCouleurs(plage As Range, cellule As Range) As Integer
Application.Volatile
Dim chaqueCellule As Range

nbCouleurs = 0

For Each chaqueCellule In plage
If chaqueCellule.Interior.ColorIndex = cellule.Interior.ColorIndex Then
nbCouleurs = nbCouleurs + 1
End If
Next chaqueCellule
End Function

Additionner selon la couleur des cellules
Il s'agit désormais de bâtir la fonction permettant de réaliser une somme conditionnelle, comme la fonction Somme.Si, mais encore une fois, sur un critère de couleur de cellule. Cette fonction doit proposer un argument facultatif, comme son homologue. Si une seule plage de cellules est définie, alors le critère et la somme sont appliqués au même endroit. Si deux plages de cellules sont passées en paramètre, le critère est vérifié sur la première tandis que l'addition est réalisée sur les cellules correspondantes de la seconde.

• Basculer dans l'éditeur de code Visual Basic Excel (ALT + F11),
• Sous la fonction nbCouleurs, créer la fonction sommeCouleurs, comme suit :

Function sommeCouleurs(plageC As Range, cellule As Range, Optional plageS As Variant) As Long

End Function

Nous déclarons trois paramètres. Comme vous le constatez, le dernier est défini comme facultatif grâce au mot clé Optional. Et pour qu'il puisse être traité comme tel par le code VBA Excel, il est nécessaire de le typer comme un Variant.

• Entre les bornes de la fonction, ajouter les instructions suivantes :

Application.Volatile
Dim chaqueCelluleC As Range: Dim chaqueCelluleS As Range

sommeCouleurs = 0

Comme toujours, nous exploitons la méthode Volatile de l'objet VBA Application afin de créer une fonction de calcul dynamique. Nous déclarons ensuite deux variables, typées comme des objets Range, afin d'être en mesure de parcourir chacune des cellules, pour chaque plage passée en argument. Puis, nous initialisons la variable de la fonction sommeCouleurs à zéro.

Pour savoir si le traitement doit se réaliser sur l'une des plages ou les deux, il s'agit d'ajouter un test, permettant de vérifier si la dernière plage de cellules a été passée en argument.

• A la suite du code, ajouter les bornes de l'instruction conditionnelle suivante :

If (IsMissing(plageS)) Then

Else

End If

Comme son nom l'indique, la fonction VBA Excel IsMissing permet de savoir si la variable qui lui est passée en paramètre existe. Si elle renvoie True, donc si le test est vérifié, cela signifie que le troisième argument, celui de la seconde plage de cellules, n'a pas été spécifié. Cette fonction IsMissing ne fonctionne que si l'argument est bien déclaré comme un Variant.

Dans le cas où la seconde plage n'est pas passée en troisième argument de la fonction, le traitement est quasiment identique au précédent. Il s'agit de réaliser une boucle parcourant l'ensemble des cellules de la première plage. Si la couleur de la cellule en cours correspond à la couleur de la cellule de référence, passée en deuxième argument, alors sa valeur doit être additionnée aux précédentes, dans la variable du nom de la fonction. C'est ainsi que la somme conditionnelle pourra être retournée à l'issue.

• Dans la branche If de l'instruction conditionnelle, ajouter les traitements suivants :

For Each chaqueCelluleC In plageC
If (chaqueCelluleC.Interior.ColorIndex = cellule.Interior.ColorIndex) Then
sommeCouleurs = sommeCouleurs + chaqueCelluleC.Value
End If
Next chaqueCelluleC

C'est la variable chaqueCelluleC, dans une boucle ForEach, qui permet de parcourir toutes les cellules de la première plage (plageC). L'instruction conditionnelle qui suit, permet pour chacune d'entre elles, de vérifier l'égalité des couleurs. Si le test est vérifié, c'est la somme des valeurs qui est réalisée cette fois-ci, grâce à la propriété Value d'un objet de type Range, soit d'une cellule.

Dans le cas contraire, donc si le troisième argument de la seconde plage de cellules est spécifié, il s'agit toujours de parcourir l'ensemble de cellules de la première plage, à la recherche des correspondances de couleur. Mais si cette correspondance est avérée, il s'agit de parcourir l'ensemble des cellules de la seconde plage, à la recherche de l'indice de ligne correspondant, pour y prélever la valeur et effectuer la somme. Une seconde boucle For Each doit donc être imbriquée dans la première.

• Dans la branche Else de l'instruction conditionnelle, ajouter les traitements suivants :

For Each chaqueCelluleC In plageC
If (chaqueCelluleC.Interior.ColorIndex = cellule.Interior.ColorIndex) Then
For Each chaqueCelluleS In plageS
If (chaqueCelluleS.Row = chaqueCelluleC.Row) Then
sommeCouleurs = sommeCouleurs + chaqueCelluleS.Value
End If
Next chaqueCelluleS
End If
Next chaqueCelluleC

Le début du code ne change pas. La première boucle et son test sont identiques au traitement précédent. Mais si les couleurs sont bien similaires, cette fois la somme n'est pas réalisée tout de suite, puisque l'opération doit se faire sur la seconde plage de cellules. C'est là qu'entre en scène notre seconde variable de type Range (chaqueCelluleS). Utilisée dans une nouvelle boucle For Each, elle permet de parcourir toutes les cellules de la seconde plage, pour chaque cellule elle-même parcourue dans la première. Un nouveau test réalisé sur les indices de ligne grâce à la propriété Row, permet de savoir s'il s'agit de la cellule correspondante, lorsque les couleurs sont identiques. Si tel est le cas, la somme est donc cette fois réalisée sur cette seconde plage. Et c'est toujours la propriété Value d'un objet de type cellule, qui permet de récupérer la valeur qu'elle contient.

Notre développement est terminé. Bien que le code soit légèrement plus conséquent que le précédent, il reste relativement léger et simple. De plus, cette fonction peut être surchargée par le jeu de ses arguments et délivre des résultats fort précieux. Il s'agit de la tester dans les deux contextes.

• Enregistrer les modifications et basculer sur la feuille Classement,
• Sélectionner la première cellule de calcul dans le deuxième tableau, soit I13,
• Taper le symbole = pour débuter le calcul,
• Saisir les premières lettres de la fonction et valider la proposition, soit =sommeCouleurs(,
• Sélectionner la plage de cellules du premier vendeur, soit C5:C16,
• La figer seulement en ligne pour les mêmes raisons que précédemment, ce qui donne : C$5:C$16,
• Taper un point-virgule (;) pour passer dans l'argument du critère de couleur,
• Saisir la référence de la cellule H13 située en regard,
• La figer seulement en colonne pour les mêmes raisons que précédemment, ce qui donne : $H13,
• Fermer la parenthèse de la fonction et valider le calcul par CTRL + Entrée,
• Comme précédemment, répliquer le calcul sur la largeur et la hauteur du tableau,

La formule que nous avons tapée est la suivante :

=sommeCouleurs(C$5:C$16;$H13)

Les résultats tombent instantanément comme l'illustre la capture ci-dessus. Ils sont tous cohérents. Il s'agit bien des sommes réalisées en fonction de la couleur de remplissage, repérée dans chacune des cellules de la plage désignée en premier argument. Nous devons maintenant tester la méthode surchargée de la fonction, en précisant la seconde plage de cellules pour y réaliser les additions.

• Cliquer sur l'onglet Sommes_couleurs en bas de la fenêtre Excel pour activer sa feuille,

Un petit tableau liste des articles avec des prix unitaires. Chacun de ces articles est associé à une catégorie. Cette catégorie est définie par un code couleur en colonne B. Entre les lignes 14 et 16, il s'agit de connaître la somme des prix par catégorie. Le critère de couleur doit donc être cherché sur la plage de cellules de la colonne B. S'il est trouvé, la somme doit être réalisée sur les cellules correspondantes de la plage située en colonne D.

• Sélectionner la première cellule du calcul, soit D14,
• Taper le symbole = pour commencer la formule,
• Saisir les premières lettres de la fonctions et valider la proposition, soit =sommeCouleurs(,
• Sélectionner la plage de cellules du critère, soit B5:B12,
• La figer complètement grâce à la touche F4 du clavier, ce qui donne : $B$5:$B$12,
• Taper un point-virgule (;) pour passer dans l'argument du critère,
• Saisir la référence de la première cellule de couleur en regard, soit C14,
• Taper un point-virgule (;) pour passer dans l'argument de la plage à sommer,
• Sélectionner tous les montants HT, soit la plage D5:D12,
• Figer cette dernière avec la touche F4, ce qui donne ; $D$5:$D$12,
• Fermer la parenthèse de la fonction,
• Valider le calcul par le raccourci clavier CTRL + Entrée,
• Puis, le répliquer sur les deux cellules du dessous à l'aide de la poignée de la cellule,

La formule que nous avons tapée est la suivante :

=sommeCouleurs($B$5:$B$12; C14; $D$5:$D$12)

Une fois de plus, les additions conditionnelles sont parfaitement réalisées. Cette fois, la somme a été effectuée sur une autre plage de cellules que celle qui a permis de vérifier le critère de couleur. Notre fonction sommeCouleurs, à l'instar de la fonction Somme.Si, peut donc être surchargée pour s'adapter au contexte. Elle livre ainsi une puissance et une efficacité très intéressantes.

Son code complet est le suivant :

Function sommeCouleurs(plageC As Range, cellule As Range, Optional plageS As Variant) As Long
Application.Volatile
Dim chaqueCelluleC As Range: Dim chaqueCelluleS As Range

sommeCouleurs = 0

If (IsMissing(plageS)) Then
For Each chaqueCelluleC In plageC
If (chaqueCelluleC.Interior.ColorIndex = cellule.Interior.ColorIndex) Then
sommeCouleurs = sommeCouleurs + chaqueCelluleC.Value
End If
Next chaqueCelluleC
Else
For Each chaqueCelluleC In plageC
If (chaqueCelluleC.Interior.ColorIndex = cellule.Interior.ColorIndex) Then
For Each chaqueCelluleS In plageS
If (chaqueCelluleS.Row = chaqueCelluleC.Row) Then
sommeCouleurs = sommeCouleurs + chaqueCelluleS.Value
End If
Next chaqueCelluleS
End If
Next chaqueCelluleC
End If
End Function

Fonction portable et décrite
A ce stade, un souci de taille persiste. Nos fonctions VBA ont été écrites dans un classeur spécifique. Elles ne sont donc reconnues que par ce dernier. Si vous créez un nouveau classeur et que vous tapez les premières lettres de l'une ou l'autre, après le symbole =, l'infobulle contextuelle ne les propose plus. De même, une autre amélioration est à envisager.

• Dans le classeur en cours, sélectionner une cellule vide,
• Puis, cliquer sur l'onglet Formules en haut de la fenêtre Excel pour activer son ruban,
• Tout à fait à gauche du ruban, cliquer sur le bouton Insérer une fonction,
• Dans la boîte de dialogue qui suit, choisir la catégorie Personnalisées,
• Sélectionner l'une des fonctions créées, comme SommeCouleurs,

Comme vous le remarquez, aucune aide, aucune description, n'est proposée à l'utilisateur. Si ce dernier n'est pas le concepteur, il aura sans doute les plus grandes peines à les mettre en application.

C'est la raison pour laquelle, dans la prochaine formation, nous proposerons de rendre ces fonctions portables pour toutes les utilisations à venir avec une description permettant de comprendre leur fonctionnement.