Comment compter le nombre de valeurs uniques dans une plage selon plusieurs critères sous Excel ?
Dans de nombreux cas concrets, il est souvent nécessaire non seulement de compter des valeurs, mais aussi de déterminer combien d’éléments uniques répondent à certains critères au sein de vos données. Par exemple, vous souhaiterez peut-être connaître le nombre de produits distincts vendus par un commercial donné ou le nombre de commandes uniques passées sur une période précise. Pour traiter efficacement ce type de tâches dans Excel, il est essentiel de maîtriser les formules adaptées, les fonctionnalités avancées telles que les tableaux croisés dynamiques, voire des solutions VBA personnalisées. Dans cet article, nous explorerons plusieurs méthodes pratiques pour compter le nombre de valeurs uniques dans une plage selon un ou plusieurs critères, accompagnées d’instructions détaillées et de conseils utiles.
Compter le nombre de valeurs uniques dans une plage selon un critère
Compter le nombre de valeurs uniques dans une plage selon deux dates données
Compter le nombre de valeurs uniques dans une plage selon deux critères
Compter le nombre de valeurs uniques dans une plage selon trois critères
Compter le nombre de valeurs uniques dans une plage selon un critère
Prenons un cas courant : vous souhaitez savoir combien de produits différents Tom a vendus. Cette méthode est idéale pour les jeux de données simples, lorsque vous devez évaluer l’unicité selon une seule condition — par exemple, les ventes attribuées à une personne précise. Elle est directe, mais exige une utilisation rigoureuse des formules matricielles.
Pour ce scénario, saisissez la formule suivante dans une cellule vide (par exemple, la cellule G2) :
=SUM(IF(«Tom»=$C$2:$C$20,1/(COUNTIFS($C$2:$C$20, «Tom», $A$2:$A$20, $A$2:$A$20)),0))
Après avoir saisi la formule, appuyez sur Ctrl + Maj + Entrée (et non simplement sur Entrée) pour la valider en tant que formule matricielle. Des accolades apparaîtront automatiquement autour de la formule dans la barre de formule, et vous obtiendrez immédiatement le résultat, comme illustré ci-dessous :
Remarque:
- « Tom » est la condition que vous souhaitez appliquer pour filtrer les résultats. Pour plus de flexibilité, vous pouvez remplacer « Tom » par une référence à une autre cellule (par exemple, $F$2).
- La plage $C$2:$C$20 contient les noms des commerciaux à évaluer.
- $A$2:$A$20 correspond à la colonne des produits pour lesquels vous souhaitez obtenir des décomptes uniques.
- Si votre plage de données évolue, pensez à ajuster les références en conséquence.
Conseil : Si vous utilisez Excel 365 ou Excel 2019 et les versions ultérieures, essayez les fonctions UNIQUE et FILTRE pour simplifier vos formules.
Si vous obtenez des erreurs #DIV/0!, vérifiez soigneusement vos critères et assurez-vous que vos plages sont de même longueur.
Compter le nombre de valeurs uniques dans une plage selon deux dates données
Lorsque vous devez déterminer le nombre d’éléments uniques dans une plage de dates spécifique — par exemple, tous les produits uniques vendus entre le 1er septembre 2016 et le 30 septembre 2016 — cette approche s’avère particulièrement efficace. Elle est idéale pour analyser des tendances sur des périodes précises, qu’elles soient mensuelles, trimestrielles ou personnalisées (plage de dates). Assurez-vous toutefois que le format des dates corresponde exactement à celui utilisé dans votre feuille de calcul.
Saisissez la formule suivante dans une cellule vide où vous souhaitez afficher le résultat :
=SUM(IF($D$2:$D$20=DATE(2016,9,1)),1/COUNTIFS( $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, «=»&DATE(2016,9,1))),0)
Après avoir saisi la formule, appuyez sur Ctrl + Maj + Entrée pour l’exécuter en tant que formule matricielle. La capture d’écran ci-dessous illustre le résultat obtenu :
Remarque:
- 2016,9,1 et 2016,9,30 sont les dates de début et de fin. Vous pouvez les modifier selon vos besoins ou même utiliser des références de cellules pour créer des filtres dynamiques basés sur des dates.
- La plage $D$2:$D$20 contient les dates à vérifier.
- $A$2:$A$20 correspond désormais à la colonne contenant les articles ou 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 pas comme des chaînes de texte. Si votre résultat ne s’affiche pas comme prévu, vérifiez le formatage de vos dates ainsi que vos plages.
Conseil : utilisez DATE(année, mois, jour) pour éviter les problèmes liés au formatage régional des dates. Lorsque vous travaillez avec des plages dynamiques, privilégiez les plages nommées pour plus de clarté.
Compter le nombre de valeurs uniques dans une plage selon deux critères
Supposons que vous souhaitiez analyser uniquement les produits vendus par Tom en septembre, en combinant un nom et une plage de dates dans votre comptage unique. Ce scénario est courant lors d’évaluations de performance périodiques ou d’analyses segmentées. À mesure que vos critères se multiplient, la formule gagne en complexité, et la précision des données devient encore plus cruciale.
Saisissez la formule ci-dessous dans une cellule vide, par exemple H2 :
=SUM(IF((«Tom»=$C$2:$C$20)*($D$2:$D$20=DATE(2016,9,1))),1/COUNTIFS($C$2:$C$20, «Tom», $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, «=»&DATE(2016,9,1))),0)
Après avoir saisi la formule, validez-la avec Ctrl + Maj + Entrée. Le comptage unique s’affichera immédiatement ; consultez l’illustration suivante :
Remarques :
- « Tom » constitue votre critère de nom, tandis que « 2016,9,1 » et « 2016,9,30 » définissent les bornes de votre plage de dates. Ajustez-les selon vos besoins ou rendez-les dynamiques en utilisant des références de cellules.
- $C$2:$C$20 correspond à la colonne du personnel (ou d’un autre critère principal) ; $D$2:$D$20 représente la colonne des dates ; $A$2:$A$20 contient les éléments uniques à comptabiliser.
- Toutes les plages doivent avoir la même longueur pour éviter toute erreur.
Si vous souhaitez utiliser des conditions « ou », par exemple compter les produits uniques vendus par Tom ou dans la région Sud, vous pouvez utiliser la formule suivante. Cela permet d’élargir les conditions de recherche, bien que les résultats puissent se chevaucher si des données correspondent aux deux critères :
=SOMME(--(FREQUENCE(SI((«Tom»=$C$2:$C$20)+(«South»=$B$2:$B$20), COUNTIF($A$2:$A$20, "0))
N’oubliez pas d’appuyer sur Ctrl + Maj + Entrée. Vous obtiendrez les résultats illustrés ci-dessous :
Conseil : Lorsque vous appliquez des critères « OU », attention au risque de double comptage si un même enregistrement remplit les deux conditions. Sur de grands jeux de données, cela peut impacter les performances.
Compter le nombre de valeurs uniques dans une plage selon trois critères
Il arrive parfois que votre analyse nécessite trois conditions ou plus, par exemple déterminer les produits uniques vendus par Tom en septembre uniquement dans la région Nord. Cela est courant dans les analyses multidimensionnelles Analyse des données destinées à la création de rapports ou à l’obtention d’informations commerciales ciblées. Une gestion rigoureuse des références est essentielle pour gérer ce type de logique composée.
Saisissez cette formule matricielle dans une cellule vide (par exemple, I2) :
=SUM(IF((«Tom»=$C$2:$C$20)*($D$2:$D$20=DATE(2016,9,1))*(«North»=$B$2:$B$20),1/COUNTIFS($C$2:$C$20, «Tom», $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, «=»&DATE(2016,9,1), $B$2:$B$20, "North")),0)
Appuyez sur Ctrl + Maj + Entrée pour valider. Voici un exemple de résultat à titre indicatif :
Pour les conditions avancées, vérifiez toujours que toutes les plages sont cohérentes et que les types de données (par exemple, date et texte) sont corrects. Les incohérences peuvent entraîner des erreurs ou des résultats trompeurs.
Conseils :
- Si vous rencontrez des problèmes de performance avec de grands jeux de données, envisagez de fractionner votre formule ou d’utiliser la solution Tableau croisé dynamique d’Excel.
- Utiliser des plages nommées ou des références de cellules pour tous les critères améliore la lisibilité des formules et réduit les erreurs.
- Pour une utilisation fréquente, envisagez d’enregistrer ces formules dans des cellules nommées ou des fonctions personnalisées.
Compter le nombre de valeurs uniques dans une plage avec Tableau croisé dynamique (comptage distinct, Excel 2013+)
Pour les utilisateurs d’Excel 2013 ou d’une version ultérieure, les tableaux croisés dynamiques offrent une alternative interactive et sans formule pour compter le nombre de valeurs uniques dans une plage selon un ou plusieurs critères. La fonctionnalité **Comptage distinct** permet de résumer et de filtrer efficacement de grands ensembles de données, ce qui rend cette méthode particulièrement adaptée aux environnements dynamiques axés sur la création de rapports. Notez toutefois que les versions antérieures d’Excel ne prennent pas en charge la fonction **Comptage distinct** dans les tableaux croisés dynamiques.
Comment utiliser cette méthode :
- Sélectionnez votre jeu de données, puis accédez à Insertion > Tableau croisé dynamique.
- Dans la boîte de dialogue « Créer un tableau croisé dynamique », choisissez l’emplacement du tableau croisé dynamique, cochez la case « Ajouter ces données au modèle de données », puis cliquez sur OK.
- Faites glisser le champ dont vous souhaitez compter les valeurs uniques (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, puis sélectionnez Paramètres des valeurs.
- Dans la boîte de dialogue qui s’ouvre, faites défiler vers le bas et sélectionnez Nombre sans doublon. (Cette option n’est disponible qu’à partir d’Excel 2013 et apparaît uniquement 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 : Commercial, Région, Date) aux zones Filtres ou Lignes/Colonnes pour appliquer une ou plusieurs conditions.
- Votre tableau croisé dynamique affichera désormais le nombre unique de valeurs filtrées selon vos critères.
Avantages : Très visuel, facile à ajuster sans modifier les formules et parfaitement adapté aux rapports interactifs.
Limites : Non disponible dans Excel 2010 ni dans les versions antérieures ; l’ajout de nouvelles données nécessite une actualisation manuelle du tableau croisé dynamique.
Conseil pratique : Assurez-vous toujours que la plage de données source ne contient pas de doublons au sein d’un même enregistrement, sauf si ceux-ci sont intentionnels. Si l’option « Comptage distinct » n’apparaît pas, recréez le tableau croisé dynamique en cochant l’option « Ajouter ces données au modèle de données ».
Compter le nombre de valeurs uniques dans une plage avec code VBA (pour les cas complexes ou automatisés)
Il peut arriver que vous ayez besoin de compter automatiquement le nombre de valeurs uniques dans une plage selon divers critères — notamment lors de la manipulation de très grands jeux de données ou lorsque l’analyse est effectuée fréquemment. Une macro VBA s’impose alors comme la solution idéale, car elle permet de traiter rapidement des logiques complexes, y compris le filtrage multicritères, sans aucune intervention manuelle après sa configuration initiale. Toutefois, le VBA étant plus avancé que les fonctionnalités classiques d’Excel, il convient surtout aux utilisateurs à l’aise avec les macros ou disposant de besoins analytiques réguliers.
Étapes opératoires :
- Appuyez sur Alt + F11 pour ouvrir l’éditeur VBA. Dans l’éditeur, sélectionnez Insertion > Module afin de 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 revenez à votre feuille de calcul. Appuyez sur Alt + F8, sélectionnez CountUniqueWithCriteria, puis exécutez la macro.
- Suivez les invites pour définir les plages et les critères adaptés à vos données. Le résultat s’affichera dans la cellule sélectionnée ainsi que dans une boîte de message.
Explication des paramètres et remarques :
- Cette macro est actuellement configurée pour un seul critère. Pour l’adapter à plusieurs critères, modifiez la logique
If ... Thenà l’intérieur de la boucle. - Enregistrez toujours votre classeur avant d’exécuter des macros, car les modifications effectuées 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 est particulièrement adaptée aux jeux de données volumineux ou fréquemment mis à jour, où les formules manuelles deviendraient rapidement fastidieuses.
Avantages : Hautement personnalisable et automatisable, il gère efficacement les grands jeux de données en évolution — idéal pour les flux de travail avancés ou récurrents.
Inconvénients : Nécessite l’autorisation d’exécuter des macros, et les débutants peuvent avoir besoin de temps pour se familiariser avec les opérations VBA.
Lorsque vous comptez des valeurs uniques selon des critères, vérifiez toujours vos références de plage et assurez-vous que toutes les colonnes de critères ont la même taille. Les plages incohérentes sont une source fréquente d’erreurs ou de résultats incorrects. Si les formules renvoient des résultats inattendus, recherchez d’éventuels problèmes de mise en forme masqués ou des cellules vides. Dans les scénarios sensibles aux performances, Tableau croisé dynamique et le VBA constituent des alternatives robustes aux formules matricielles. Choisissez la solution la mieux adaptée à votre niveau de maîtrise et à la complexité de votre jeu de données. N’oubliez pas que Kutools pour Excel propose des utilitaires et des raccourcis supplémentaires pouvant simplifier bon nombre de ces tâches, améliorant ainsi encore davantage l’efficacité dans les classeurs complexes.
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile
- Activez l’édition 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é de 50 % et vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels