VBA : Compter les couleurs

Un de mes amis me demande, comment on pourrait cumuler le contenu de plusieurs cellules en fonction d'une couleur de fond appliquée à ces cellules ? Voici deux petites procédures VBA qui permettrons de résoudre facilement ce problème.

- 1 – Cette première procédure cumule simplement dans les cellules C1 à c5 les valeurs contenues dans des cellules de même couleur entre A1 et A10.

 Sub Cumul_couleur()
'déclaration des variables
Dim CouleurFond As Long
Dim LigneCumul As Long
Dim CouleurCumul As Long
Dim ligne As Long
'remise à zéro de cumuls précédent sans effacement de couleur
Range("c1:c5").ClearContents
For LigneCumul = 1 To 5
    Cells(LigneCumul, 3).Select
'récupération de la 1er couleur de cumul
    CouleurCumul = Selection.Interior.ColorIndex
    For ligne = 1 To 10
        Cells(ligne, 1).Select
        CouleurFond = Selection.Interior.ColorIndex
'récupération de la 1er couleur de fond
        If CouleurFond = CouleurCumul Then
'après comparaison des 2 couleurs on cumul
            Cells(LigneCumul, 3).Value = Cells(LigneCumul, 3).Value _
            + Cells(ligne, 1).Value
        End If
    Next ligne
Next LigneCumul
'Et on recommence
End Sub

- 2 – Dans ce second exemple nous appliquons une méthode identique mais à l'ensemble d'un tableau en comptant en plus le nombre de valeur par couleur.

Sub Cumul_Comptage_couleur()
'déclaration des variables
Dim CouleurFond As Long
Dim LigneCumul As Long
Dim CouleurCumul As Long
Dim ligne As Long
Dim col As Long
'remise à zéro de cumuls précédent sans effacement de couleur
Range("f1:f5").ClearContents
Range("h1:h5").ClearContents
For LigneCumul = 1 To 5
    Cells(LigneCumul, 6).Select
'récupération de la 1er couleur de cumul
    CouleurCumul = Selection.Interior.ColorIndex
    For ligne = 1 To 10
        For col = 1 To 4
            Cells(ligne, col).Select
            CouleurFond = Selection.Interior.ColorIndex
'récupération de la 1er couleur de fond
            If CouleurFond = CouleurCumul Then
'après comparaison des 2 couleurs on cumul
               Cells(LigneCumul, 6).Value = Cells(LigneCumul, 6).Value _
               + Cells(ligne, col).Value
' et maintenant on compte
               Cells(LigneCumul, 8).Value = Cells(LigneCumul, 8).Value + 1
              End If
        Next col
    Next ligne
Next LigneCumul
'Et on recommence
End Sub

Ces deux exemples seront aisément adaptables à vos tableaux…

 

Partager |

Petite et grande valeur

Comment déterminer la plus grande et la plus petite valeur d'une plage de cellules contenant des données numériques ? Facile me direz-vous il suffit d'utiliser les fonctions = max() et = min(), ces deux fonctions permettant effectivement de déterminer les maximas et le minimas de votre plage.
Mais comment faire si l'on veut connaître non pas la première grande valeur ou la première petite valeur mais la Kiéme valeur grande ou petite d'une plage ? Permettez moi de vous présentez la fonction :

=GRANDE.VALEUR( plage ; K )

Ou

=PETITE.VALEUR( plage ; K )

- 1 – Regardons la syntaxe de la fonction grande valeur par exemple les cellules A1:B10 contiennent les données et la fonction =max(A1:B10) saisie en E1 nous affiche la plus grande valeur de la plage (ici 1 9120,00). Utilisons maintenant la fonction :
=GRANDE.VALEUR (A1:B10;2) qui retournera la 2ème grande valeur de la plage dans la cellule A3.

- 2 – Recommençons avec la fonction petite valeur, la fonction =min(A1:B10) saisie en E3 nous affiche la plus petite valeur de la plage (ici -265,00). Utilisons maintenant la fonction :
=PETITE.VALEUR (A1:B10;2) qui retournera la 2ème petite valeur de la plage dans la cellule E4.

Partager |

Statistiques sur les filtres automatiques

Comment appliquer des fonctions statistiques aux résultats affichés par un filtre automatique ? Effectivement les fonctions classiques (SOMME(), MOYENNE(), MAX(), MIN()…) ne conviennent pas dans un tel cas puisqu'elles retournent un résultat sur l'ensemble des données et pas sur le résultat du filtre.
La solution passe par la maîtrise de la fonction =SOUS.TOTAL()

La syntaxe de la fonction sous.total() est :

=SOUS.TOTAL( fonction ; plage )

fonction désigne alors la fonction statistique que vous souhaitez appliquer aux résultats de votre filtre, elle est indiquée par un numéro
 compris entre 1 et 11, comme l'indique le tableau ci-dessous.
plage désigne l'adresse contenant les données à analyser.

Ainsi dans la seconde image ci contre :
La fonction :

=SOUS.TOTAL( 9 ; b2:b11 )

saisie en B13, Nous permettra d'obtenir la somme des prix uniquement pour la région "Aquitaine"

Moyenne	1	101
Nb	2	102
Nbval	3	103
Max	4	104
Min	5	105
Produit	6	106
Ecartype	7	107
EcartypeP	8	108
Somme	9	109
Var	10	110
Var.P	11	111

Si vous indiquez le code sur 3 chiffres par exemple 109 pour somme et non 9 alors Excel comprendra qu'il doit ignorer d'éventuelles lignes masquées dans votre liste pour la réalisation de la statistique.

Partager |

Compter et compter distinctement !

Lorsque vous travaillez sur une liste de données Excel, comment connaître le nombre de valeurs distinctes contenu dans un champ ?
Par exemple dans le champ "Pays" ci-joint à combien de pays différents fait on référence ?

La fonction de comptage =NBVAL( plage ) peut vous donner le nombre total de lignes (ici 10), pour la formule =NBVAL(B2:B11).
La fonction =NB.SI( plage ; critère ) peut vous retourner le nombre de fois où un pays est présent, (ici 4) pour l'expression = NB.SI(B2:B11;"Espagne"), c'est justement à partir de cette fonction que se trouve une solution possible.

La formule  =SOMME( 1 / NB.SI(B2:B11;B2:B11) ) répond à cette question, la fonction =NB.SI( ) comptant ici les valeurs de sa propre matrice. Attention toutefois la valeur retournée étant une matrice, vous devez valider la formule par SHIFT + CTRL + Entrée.
On voit alors qu'il est fait référence à 5 pays différents dans ce champ, et la formule étant matriciel elle apparaît entre { }.
Merci à Michel, un anonyme internaute pour m'avoir suggéré la réponse à ce problème, sur laquelle je butais depuis longtemps.

Partager |

VBA : Les différents types de données

Comment choisir le bon type de données lors de la création de vos variables ? Voici un tableau rappelant toutes les informations techniques nécessaires à un choix éclairé

Type	contenu	Plage de valeurs
Byte	Valeurs binaires	0 à 255 Taille en octets : 1
Boolean	Valeurs logiques	True ou False Taille en octets : 2
Currency	Utiliser pour les valeurs monétaires. 4 chiffres à droite du séparateur décimale et 15 à gauche maximum	+ / - 9E14 Taille en octets : 8
Date	Informations de date et heure	1er janvier 100 au 31 décembre 9999 Taille en octets : 8
Double	Nombre en virgule flottante (double précision)	+ / - 5E-324 à 1,8 Taille en octets : 8
Integer	Entiers	-32 768 à 32 767 Taille en octets : 2
Long	Entiers	+ / - 2E09 Taille en octets : 4
Object	N'importe quelle référence d'objet
Single	Nombre en virgule flottante (simple précision)	+ / - 1E-45 à 3E38 Taille en octets : 4
String	Texte	Longueur fixe : 1 à 65 400 Longueur variable : 0 à E09
Variant	N'importe lequel des types précédents	Nombres : cf double Caractères : cf texte à longueur variables

Partager |