Comment compter les valeurs uniques basées sur plusieurs critères dans Excel ?
Dans de nombreux cas pratiques, il est souvent nécessaire non seulement de compter des valeurs, mais aussi de déterminer combien d'éléments uniques répondent à certaines conditions dans vos données. Par exemple, vous pourriez vouloir connaître le nombre de produits différents vendus par un vendeur particulier ou combien de commandes uniques ont été passées dans une période donnée. Gérer efficacement ces tâches dans Excel nécessite de se familiariser avec des formules appropriées, des fonctionnalités avancées comme les tableaux croisés dynamiques, voire des solutions VBA personnalisées. Dans cet article, nous explorerons plusieurs méthodes pratiques pour compter les valeurs uniques en fonction d'un ou plusieurs critères, avec des instructions étape par étape et des conseils.
Compter les valeurs uniques basées sur un critère
Compter les valeurs uniques basées sur deux dates données
Compter les valeurs uniques basées sur deux critères
Compter les valeurs uniques basées sur trois critères
Compter les valeurs uniques avec un tableau croisé dynamique (Distinct Count, Excel 2013+)
Compter les valeurs uniques avec du code VBA (pour des cas complexes/automatisés)
Compter les valeurs uniques basées sur un critère
Prenons un cas courant : vous souhaitez compter combien de produits différents ont été vendus par Tom. Cette méthode convient lorsque vous avez un ensemble de données simple et que vous visez à évaluer l'unicité basée sur une seule condition, comme les records de ventes d'une seule personne. C'est simple, mais cela nécessite une utilisation minutieuse des formules matricielles.
Pour ce scénario, entrez la formule suivante dans une cellule vide (par exemple, cellule G2) :
=SOMME(SI("Tom"=$C$2:$C$20;1/(NB.SI.ENS($C$2:$C$20; "Tom"; $A$2:$A$20; $A$2:$A$20));0))
Après avoir tapé la formule, appuyez sur Ctrl + Maj + Entrée (et non simplement Entrée) pour la confirmer en tant que formule matricielle. Les accolades apparaîtront autour de la formule dans la barre de formule, et vous verrez immédiatement le résultat comme indiqué ci-dessous :
Remarque :
- “Tom” est le critère que vous souhaitez utiliser pour filtrer les résultats. Vous pouvez remplacer "Tom" par une référence à une autre cellule (par exemple, $F$2) si vous souhaitez plus de flexibilité.
- $C$2:$C$20 contient les noms des vendeurs à évaluer.
- $A$2:$A$20 est la colonne de produits pour laquelle vous souhaitez des comptes uniques.
- Si votre plage de données change, n'oubliez pas d'ajuster les références en conséquence.
Astuce : Si vous utilisez Excel 365 ou Excel 2019 et versions ultérieures, vous pouvez essayer d'utiliser les fonctions UNIQUE et FILTER pour des formules plus simples.
Si vous rencontrez des erreurs #DIV/0 !, revérifiez les critères et assurez-vous que vos plages sont de longueur égale.
Compter les valeurs uniques basées sur deux dates données
Lorsque vous devez trouver le nombre d'éléments uniques dans une plage de dates spécifique, par exemple, tous les produits uniques vendus entre le 01/09/2016 et le 30/09/2016, vous pouvez appliquer cette approche. Cela est particulièrement utile lors de l'analyse des tendances de données entre certaines périodes, comme mensuelles, trimestrielles, ou plages de dates personnalisées. Cependant, soyez prudent avec le formatage des dates ; il doit correspondre aux valeurs de date de votre feuille de calcul.
Placez la formule suivante dans une cellule vide où vous souhaitez afficher le résultat :
=SOMME(SI($D$2:$D$20<=DATE(2016;9;30)*($D$2:$D$20>=DATE(2016;9;1));1/NB.SI.ENS( $A$2:$A$20; $A$2:$A$20; $D$2:$D$20; "<="&DATE(2016;9;30);$D$2:$D$20; ">="&DATE(2016;9;1)));0)
Appuyez sur Ctrl + Maj + Entrée après avoir entré la formule pour l'exécuter en tant que formule matricielle. La capture d'écran ci-dessous montre le résultat :
Remarque :
- 2016,9,1 et 2016,9,30 sont les critères de date de début et de fin. Vous pouvez les modifier selon vos besoins, ou même utiliser des références de cellules pour des filtres de dates dynamiques.
- $D$2:$D$20 contient les entrées de date à vérifier.
- $A$2:$A$20 est encore la colonne d’articles ou de produits que vous souhaitez compter de manière unique.
- Assurez-vous que vos dates sont stockées sous forme de dates Excel valides, et non de chaînes de texte. Si votre résultat ne s'affiche pas comme prévu, vérifiez le formatage des dates et les plages.
Astuce : Utilisez DATE(année, mois, jour) pour éviter les problèmes liés au formatage régional des dates. Lors de l’utilisation de plages dynamiques, envisagez d’utiliser des plages nommées pour plus de clarté.
Compter les valeurs uniques basées sur deux critères
Supposons que vous souhaitiez analyser uniquement les produits que Tom a vendus en septembre, combinant son nom et une plage de dates dans votre compte unique. Ce scénario est courant dans les analyses de performance basées sur des périodes ou des analyses segmentées. À mesure que vos critères s'étendent, la formule devient plus complexe, et l'attention portée à l'exactitude des données devient encore plus importante.
Entrez la formule ci-dessous dans n'importe quelle cellule vide, comme H2 :
=SOMME(SI(("Tom"=$C$2:$C$20)*($D$2:$D$20<=DATE(2016;9;30)*($D$2:$D$20>=DATE(2016;9;1)));1/NB.SI.ENS($C$2:$C$20; "Tom"; $A$2:$A$20; $A$2:$A$20; $D$2:$D$20; "<="&DATE(2016;9;30);$D$2:$D$20; ">="&DATE(2016;9;1)));0)
Après avoir tapé la formule, confirmez-la avec Ctrl + Maj + Entrée. Vous devriez voir immédiatement le compte unique ; consultez l'illustration suivante :
Remarques :
- “Tom” est le critère de nom, tandis que “2016,9,1” et “2016,9,30” sont vos limites de plage de dates. Ajustez-les selon vos besoins ou rendez-les dynamiques avec des références de cellules.
- $C$2:$C$20 est la colonne du personnel (ou un autre premier critère) ; $D$2:$D$20 est la colonne des dates ; $A$2:$A$20 contient les éléments uniques à compter.
- Les plages doivent toutes être de longueur égale pour éviter les erreurs.
Si vous souhaitez utiliser des conditions « OU », telles que le comptage des produits uniques vendus par Tom ou dans la région Sud, vous pouvez utiliser la formule suivante. Cela permet des conditions de recherche plus larges, bien que les résultats puissent se chevaucher si les données correspondent à deux critères :
=SOMME(--(FREQUENCE(SI(("Tom"=$C$2:$C$20)+("South"=$B$2:$B$20); NB.SI($A$2:$A$20; "<"&$A$2:$A$20); ""); NB.SI($A$2:$A$20; "<"&$A$2:$A$20))>0))
N’oubliez pas d’appuyer sur Ctrl + Maj + Entrée. Vous verrez les résultats comme indiqué ci-dessous :
Astuce : Lors de l'application des critères OU, soyez conscient(e) des doubles comptages possibles si le même enregistrement satisfait aux deux conditions. Pour de grands ensembles de données, les performances peuvent être affectées.
Compter les valeurs uniques basées sur trois critères
Parfois, votre analyse peut nécessiter trois critères ou plus, comme déterminer les produits uniques vendus par Tom en septembre uniquement dans la région Nord. Cela est courant dans l'analyse de données multidimensionnelles pour des rapports ou des informations commerciales ciblées. Une gestion attentive des références est essentielle pour gérer une telle logique composée.
Placez cette formule matricielle dans une cellule vide (par exemple, I2) :
=SOMME(SI(("Tom"=$C$2:$C$20)*($D$2:$D$20<=DATE(2016;9;30))*($D$2:$D$20>=DATE(2016;9;1))*("North"=$B$2:$B$20);1/NB.SI.ENS($C$2:$C$20; "Tom"; $A$2:$A$20; $A$2:$A$20; $D$2:$D$20; "<="&DATE(2016;9;30); $D$2:$D$20; ">="&DATE(2016;9;1); $B$2:$B$20; "North"));0)
Appuyez sur Ctrl + Maj + Entrée pour terminer. Voici un exemple de résultat pour référence :
Pour des conditions avancées, revérifiez que toutes les plages sont cohérentes et que les types de données (par exemple, date et texte) sont corrects. Les désalignements peuvent provoquer des erreurs ou des résultats trompeurs.
Astuce :
- Si vous rencontrez des problèmes de performance sur de grands ensembles de données, envisagez de décomposer la formule ou d'utiliser la solution des tableaux croisés dynamiques d'Excel.
- Les plages nommées ou les références de cellules pour tous les critères améliorent la lisibilité et réduisent les erreurs de formule.
- Pour une utilisation fréquente, envisagez d'enregistrer ces formules dans des références de cellules nommées ou des fonctions personnalisées.
Compter les valeurs uniques avec un tableau croisé dynamique (Distinct Count, Excel 2013+)
Pour les utilisateurs d'Excel 2013 ou version ultérieure, les tableaux croisés dynamiques offrent une alternative interactive et sans formule pour compter les valeurs uniques sur un ou plusieurs critères. La fonction Distinct Count vous aide à résumer et filtrer de grands ensembles de données efficacement, rendant cette méthode particulièrement adaptée aux environnements dynamiques basés sur des rapports. Cependant, notez que les versions antérieures d'Excel ne prennent pas en charge la fonction Distinct Count dans les tableaux croisés dynamiques.
Comment utiliser cette méthode :
- Sélectionnez votre jeu de données et allez dans Insertion > Tableau croisé dynamique.
- Dans la boîte de dialogue Créer un tableau croisé dynamique, choisissez où placer le tableau croisé dynamique, cochez la case "Ajouter ces données au modèle de données", puis cliquez sur OK.
- Glissez le champ que vous souhaitez compter de manière unique (par exemple, Produit) dans la zone Valeurs. Par défaut, il s'affichera sous la forme "Nombre de...".
- Cliquez sur le champ dans la zone Valeurs et sélectionnez Paramètres de champ de valeur.
- Dans la fenêtre contextuelle, faites défiler vers le bas et sélectionnez Distinct Count (cette option n'est disponible qu'à partir d'Excel 2013 ou version ultérieure, et apparaît lorsque le tableau croisé dynamique est créé avec l'option "Ajouter ces données au modèle de données" activée).
- Ajoutez vos champs de critères (par exemple, vendeur, région, date) dans la zone Filtres ou Lignes/Colonnes pour appliquer des conditions simples ou multiples.
- Votre tableau croisé dynamique affichera maintenant le compte unique des valeurs filtrées par vos critères choisis.
Avantages : Très visuel, facile à ajuster les filtres sans éditer les formules, et adapté aux rapports interactifs.
Limitations : Non disponible dans Excel 2010 ou versions antérieures ; l'ajout de nouvelles données nécessite un rafraîchissement manuel du tableau croisé dynamique.
Astuce pratique : Assurez-vous toujours que les données sources ne contiennent pas de doublons dans le même enregistrement s'ils ne sont pas intentionnels. Si vous constatez que l'option Distinct Count est manquante, recréez le tableau croisé dynamique et cochez l’option “Ajouter ces données au modèle de données”.
Compter les valeurs uniques avec du code VBA (pour des cas complexes/automatisés)
Parfois, vous devrez peut-être compter les valeurs uniques automatiquement en fonction de divers critères, surtout lors de la manipulation de très grands ensembles de données ou lorsque l'analyse est répétée fréquemment. Une macro VBA est idéale pour de telles situations, car elle peut rapidement traiter différentes logiques – y compris le filtrage multi-critères – sans intervention manuelle après configuration. Cependant, VBA est plus avancé que les fonctionnalités régulières d'Excel, donc il est préférable de l’utiliser pour des utilisateurs à l'aise avec les macros ou ceux ayant des besoins analytiques continus.
Étapes de l'opération :
- Appuyez sur Alt + F11 pour ouvrir l'éditeur VBA. Dans l'éditeur, sélectionnez Insertion > Module pour créer un nouveau module.
- Copiez et collez le code VBA suivant dans le module :
Sub CountUniqueWithCriteria()
Dim DataRange As Range
Dim CriteriaRange As Range
Dim CriteriaValue As Variant
Dim Dict As Object
Dim i As Long
Dim UniqueCount As Long
Dim ResultCell As Range
Set Dict = CreateObject("Scripting.Dictionary")
' Prompt for range settings
Set DataRange = Application.InputBox("Select data range (items to count):", "KutoolsforExcel", Type:=8)
Set CriteriaRange = Application.InputBox("Select criteria range (e.g. Salesperson):", "KutoolsforExcel", Type:=8)
CriteriaValue = Application.InputBox("Enter criteria value:", "KutoolsforExcel", "", Type:=2)
Set ResultCell = Application.InputBox("Select cell for result output:", "KutoolsforExcel", Type:=8)
On Error Resume Next
For i = 1 To DataRange.Rows.Count
If CriteriaRange.Cells(i, 1).Value = CriteriaValue Then
If Not Dict.Exists(DataRange.Cells(i, 1).Value) Then
Dict.Add DataRange.Cells(i, 1).Value, 1
End If
End If
Next i
UniqueCount = Dict.Count
ResultCell.Value = UniqueCount
MsgBox "Unique count for '" & CriteriaValue & "': " & UniqueCount, vbInformation, "KutoolsforExcel"
End Sub- Fermez l'éditeur VBA et retournez à votre feuille de calcul. Appuyez sur Alt + F8, sélectionnez CountUniqueWithCriteria, et exécutez la macro.
- Suivez les invites d'entrée pour spécifier les plages et critères en fonction de vos données. Le résultat apparaîtra dans la cellule que vous choisissez et également sous forme de boîte de message.
Explication des paramètres et notes :
- Cette macro est actuellement configurée pour un seul critère. Pour l’étendre à plusieurs critères, modifiez la logique
Si ... Alorsà l’intérieur de la boucle. - Enregistrez toujours votre classeur avant d’exécuter des macros, car les modifications ne peuvent pas être annulées.
- Activez les macros dans les paramètres d'Excel si vous rencontrez des erreurs d'exécution.
- Cette méthode fonctionne bien pour des données plus grandes ou fréquemment mises à jour où les formules manuelles seraient encombrantes.
Avantages : Hautement personnalisable et automatisable, gère efficacement les ensembles de données volumineux et changeants. Adapté aux besoins de flux de travail avancés ou répétés.
Inconvénients : Nécessite des permissions pour les macros, et les débutants auront besoin de temps pour se familiariser avec les opérations VBA.
Lorsque vous travaillez avec des comptes de valeurs uniques basés sur des critères, confirmez toujours vos références de plage et assurez-vous que toutes les colonnes de critères sont alignées en taille. Les plages inadaptées sont une source commune d'erreurs ou de résultats incorrects. Si les formules renvoient des résultats inattendus, vérifiez les problèmes de formatage cachés ou les cellules vides. Pour les scénarios critiques en termes de performance, les tableaux croisés dynamiques et VBA offrent des alternatives robustes aux formules matricielles. Choisissez la solution qui correspond le mieux à votre niveau de confort et à la complexité de votre ensemble de données. Souvenez-vous, Kutools for Excel fournit des utilitaires et des raccourcis supplémentaires qui peuvent rationaliser de nombreuses de ces tâches pour encore plus d'efficacité dans des classeurs complexes.
Meilleurs outils de productivité pour Office
Améliorez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité incomparable. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte l’interface par onglets à Office, simplifiant considérablement votre travail.
- Activez la modification et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de50 % et réduit des centaines de clics de souris chaque jour !
Tous les modules complémentaires Kutools. Une seule installation
La suite Kutools for Office regroupe les modules complémentaires pour Excel, Word, Outlook & PowerPoint ainsi qu’Office Tab Pro, idéal pour les équipes travaillant sur plusieurs applications Office.
- Suite tout-en-un — modules complémentaires Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un installateur, une licence — installation en quelques minutes (compatible MSI)
- Une synergie optimale — productivité accélérée sur l’ensemble des applications Office
- Essai complet30 jours — sans inscription, ni carte bancaire
- Meilleure valeur — économisez par rapport à l’achat d’add-ins individuels