Le code VBA qui suit vous permet de compter facilement le nombre de valeurs différentes (= uniques) dans une plage de cellules (Range
), ce qui peut être très pratique lors de l'analyse des données. Je vous propose cette fonctionnalité sous forme d'une fonction facilement intégrable dans votre projet VBA.
Fonction VBA pour compter les valeurs différentes
Cette fonction (appelée ValeursUniquesDansPlage
) utilise un seul argument (de type Range) qui représente la plage dont les valeurs différentes doivent être comptées. Ces plages peuvent se présenter sous la forme "A1:B100
" ou avec les "plages nommées" (Named Ranges).
L'avantage de cette fonction est qu'elle peut être utilisée pour compter les valeurs uniques de tout type – chaînes de caractères, nombres, dates,…
Attention: La valeur vide n'est pas incluse dans le résultat.
En pratique: la fonction appliquée à la plage de cellules suivante (A1:A7
) aura pour résultat "5" car elle contient 5 valeurs différentes ("123", "test", "lundi", "mardi" et "11/16/2015") sans compter la cellule vide.
Code VBA de la Fonction de comptage des valeurs différentes
C'est ce code-ci que vous devez copier dans votre projet VBA pour pouvoir utiliser la fonction qui compte les valeurs différentes.
Public Function ValeursUniquesDansPlage(MaPlage As Range) 'par: https://excel-malin.com On Error GoTo ValeursUniquesDansPlageErreur 'définition de variables Dim N As Long Dim Compteur As Long Dim Ligne As String Dim Valeur As String Dim NombreDeValeursUniques As Long Dim ValeursUniques() As String 'valeurs par défaut N = 0 Compteur = 0 Ligne = "" Valeur = "" NombreDeValeursUniques = 0 N = MaPlage.Count ReDim ValeursUniques(0 To N) 'itération dans la Plage If (N > 0) Then For Compteur = 1 To N Valeur = CStr(MaPlage.Cells(Compteur).Value) If Valeur <> "" Then If (Not (InStr(1, Ligne, Valeur, vbBinaryCompare) > 0)) Then Ligne = Ligne & ("[" & Valeur & "]") NombreDeValeursUniques = NombreDeValeursUniques + 1 ValeursUniques(NombreDeValeursUniques) = Valeur Else End If Else End If Next Compteur End If 'résultat final ValeursUniquesDansPlage = NombreDeValeursUniques Exit Function ValeursUniquesDansPlageErreur: 'si une erreur s'est produite... ValeursUniquesDansPlage = CVErr(xlErrValue) End Function
Exemple d’utilisation de la fonction ValeursUniquesDansPlage()
Passons à la pratique… Dans l'exemple qui suit, on utilise la fonction ValeursUniquesDansPlage
pour compter les valeurs différentes dans la plage de cellules A1:B30
(donc une plage de 2 colonnes * 30 lignes). Le résultat est affiché dans une fenêtre pop-up (MsgBox
).
Sub ExempleValeursUniques() 'par: https://excel-malin.com On Error GoTo TestErreur Dim MaPlage As Range Dim ValeursUniques As Double Set MaPlage = ActiveSheet.Range("A1:B30") 'sélection de la plage de cellules - attention à ne pas oublier le "Set" ValeursUniques = ValeursUniquesDansPlage(MaPlage) 'appel de la fonction "ValeursUniquesDansPlage" MsgBox ValeursUniques 'affiche le nombre des valeurs uniques dans la plage des cellules Exit Sub TestErreur: MsgBox "Une erreur s'est produite..." End Sub
Calculer les valeurs différentes dans une colonne entière
Un cas plus particulier est le calcul des valeurs uniques (différentes) dans une colonne entière. Rien de plus simple! La fonction ValeursUniquesDansPlage
fera l'affaire sans problème. Il suffit d'adapter la valeur de la plage de cellules. Donc par exemple: Pour compter les valeurs différentes dans la colonne B, l'argument à utiliser sera "B:B". L'exemple complet se trouve dans le code suivant.
Remarque: sachez que l'utilisation de cette formule avec des colonnes entières peut être gourmande en ressources – rien d'étonnant, vu qu'une colonne dans Excel 2007 et suivants contient 1.048.576 de cellules à vérifier…
Sub ExempleValeursUniquesDansColonne() 'par: https://excel-malin.com On Error GoTo TestErreur Dim MaPlage As Range Dim ValeursUniques As Double Set MaPlage = ActiveSheet.Range("B:B") 'le calcul se fera dans toute la colonne "B" - attention à ne pas oublier le "Set" ValeursUniques = ValeursUniquesDansPlage(MaPlage) 'appel de la fonction "ValeursUniquesDansPlage" MsgBox "La colonne ""B"" contient: " & ValeursUniques & " valeurs différentes" 'affiche le nombre des valeurs uniques dans la plage des cellules Exit Sub TestErreur: MsgBox "Une erreur s'est produite..." End Sub
J'espère que ces deux exemples illustrent bien comment utiliser la fonction VBA que je vous propose. Vous voici donc équipés pour une analyse de données rapide et efficace…
Pour aller plus loin en VBA
Et pour finir, quelques liens utiles qui pourraient vous servir dans votre apprentissage/utilisation de VBA. N'hésitez pas à explorer tout le site Excel-Malin.com car il y 'en a d'autres. Beaucoup d'autres…
- VBA: le nom et le numéro de la colonne (tutoriel)
- Comment vérifier si une chaîne de caractères représente un nombre?
- VBA: trouver la dernière cellule utilisée (tutoriel)
- Liste de toutes les fonctions disponibles en VBA
- Gestion des fichiers en VBA – travailler avec des fichiers dans vos macros
- Débloquer les fichiers avec des macros en provenance d'Internet
- RECHERCHEV en VBA – simple et pratique!
4 commentaires sur “VBA: Compter les valeurs différentes”
bonsoir lorsque j'exécute le programme il me met "Erreur de compilation: variable non définie"
Bonjour Fabien,
c'est parce que j'ai oublié de déclarer la variable
ValeursUniques
et comme vous utilisez (probablement)Option Explicit
dans votre code, ce qui est bien 🙂 , vous avez une erreur.Je viens de corriger le code. Merci de l'avoir signalé.
Cordialement, Martin
j'ai un petit soucis dans l'utilisation de cette fonction :
quand je défini maplage, je choisi que les cellules visibles :
Set maplage = Sheets("Frais").Range("N2:N200").Cells.SpecialCells(xlCellTypeVisible)
pour voir ma plage : Debug.Print maplage.Address
résultat : $N$80:$N$95,$N$104:$N$113
le souci, quand j'exécute la fonction :
For Compteur = 1 To N
Valeur = CStr(maplage.Cells(Compteur).Value)
au compteur 17, la valeur que j'obtiens est N96, qui n'est pas dans ma plage 😉
pour résoudre mon problème, je pense a trier les données avant de les filtrer, ainsi toutes les cellules vides seront groupés et maplage sera d'un seul bloque, mais cela ne m'arrange pas.
une autre idée pour résoudre ce problème ???
Bonjour,
Merci Beaucoup pour votre aide
Vos exemples de Macros m'ont bien aidé a faire fonctionné
la mienne surtout cette syntaxe
N = MaPlage.Count
Valeur = CStr(MaPlage.Cells(Compteur).Value) a utilisé avec une boucle for next
pour tester toutes les cellules d'une plage
Encore Merci
Cordialement
Marc