Compter les valeurs uniques en fonction de deux critères

**Lilie01** · 29/06/2022, 16h27

Bonjour,

Je souhaiterai compter un nombre de valeur unique en fonction de 2 critères.
Exemple : colonne A - Numéro de convention
Colonne B : numero de département
colonne C : un type de logement
colonne D : commune

Imaginons que j'ai :

N° convention	Num département	Type logement	commune
12	69	T1	lyon
12	69	T1	vénissieux
12	69	T1	st germain
12	83	T1	brignais
13	69	T1	villefranche
13	69	T2	genas

Je voudrai compter le nombre de convention unique pour le département 69 et pour le type de logement T1 soit dans mon exemple : 2

Si j'utilise un NB.SI.ENS classique, cela va me sortir 4.

Comment écrire la formule pour obtenir 2?

Merci d'avance pour votre aide.

**Raccourcix** · 29/06/2022, 16h54

Bonjour
quelle est votre version d'Excel ?
si 365 vous avez les fonctions UNIQUE et FILTRE

également possible via un TCD en chargeant votre table dans le modèle de données

en créant le TCD vous cochez, "ajouter ces données au modèle de données"
vous avez alors la possibilité de compter les valeurs distinctes et donc d'obtenir 2 et non 4 pour 69/T1

Stéphane

**Lilie01** · 29/06/2022, 17h41

Pour compléter mon propos : la base de départ est déjà un tcd transformé en format tableau. Il doit alimenter une page synthétique et dynamique avec pleins de chiffres clés (dont mon chiffre "2" que je souhaiterai calculer). Cette page est dynamique car on peut choisir le département et le type via un menu déroulant.
Je veux seulement modifier la formule pour bien compter mes conventions (unique) en fonction de ces 2 critères.

La formule unique ok mais comment préciser mes 2 critères "département" et "type de logement". Je ne vois pas comment l'écrire... Désolée...

Et oui 365.

Merci d'avance!

**Raccourcix** · 29/06/2022, 18h39

Un exemple de formule

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=NBVAL(UNIQUE(FILTRE(Tableau1[N° convention];(Tableau1[Num département]=69)*(Tableau1[Type logement]="T1"))))

la fonction FILTRE pour réduire le tableau au département 69 et type T1
puis UNIQUE pour conserver une fois chaque convention
et NBVAL pour les compter.

Stéphane

**Pierre Dumas** · 29/06/2022, 21h01

Bonsoir

Envoyé par Lilie01

Pour compléter mon propos : la base de départ est déjà un tcd transformé en format tableau. [...]

Si je puis me permettre, à priori (je ne connais pas vos données), cela ressemble à une erreur de conception (ma stagiaire de la semaine dernière travaillait aussi de cette façon).
On ne transforme pas un TCD en un tableau structuré.
Les données sont dans un Tableau au départ. Puis on va chercher dedans les informations qui nous intéressent via des TCD ou des formules en XXX.SI.ENS par exemple.

Il peut arriver qu'il soit nécessaire de rajouter des colonnes de calcul dans le tableau de départ. Tant que le nombre de lignes est peu conséquent, cela ne pose pas de problème. Si cela se compte en plusieurs (dizaine/centaines de) milliers, il devient urgent de passer à PowerQuery et/ou PowerPivot.

Pour calculer des valeurs uniques, la solution de Raccourcix (que je salue

) est excellente.

En espérant que cela aide

Bonne soirée

Pierre Dumas

**jurassic pork** · 30/06/2022, 07h39

Hello,
et pour ceux qui comme moi n'ont pas Excel 365 ou supérieur voici deux fonctions personnalisées matricielles pour remplacer l'absence des fonctions UNIQUE et FILTER.
La première fonction personnalisée est UFILTRE, Elle accepte jusqu'à deux critères de filtre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
Function UFILTRE(ByVal TDonn, Optional ByVal TCond1, _
                Optional ByVal TCond2, Optional ByVal TCondU)
   Dim LE&, LS&, C&
   Dim Cond1 As Boolean, Cond2 As Boolean, CondU As Boolean
   Dim DisCond1 As Boolean, DisCond2 As Boolean, DisCondU As Boolean
   Dim arrUnique As Object
   Set arrUnique = CreateObject("System.Collections.ArrayList")
   If TypeOf TDonn Is Range Then TDonn = TDonn.Value
   If TypeOf TCond1 Is Range Then TCond1 = TCond1.Value
   If TypeOf TCond2 Is Range Then TCond2 = TCond2.Value
   If TypeOf TCondU Is Range Then TCondU = TCondU.Value
   For LE = 1 To UBound(TDonn, 1)
      If IsMissing(TCond1) Then
           Cond1 = True
      Else
          If TypeOf TCond1 Is Range Then TCond1 = TCond1.Value
          Cond1 = TCond1(LE, 1)
      End If
      If IsMissing(TCond2) Then
           Cond2 = True
      Else
          If TypeOf TCond2 Is Range Then TCond2 = TCond2.Value
          Cond2 = TCond2(LE, 1)
      End If
      If IsMissing(TCondU) Then
           CondU = True
      Else
          If TypeOf TCondU Is Range Then TCondU = TCondU.Value
          If Not arrUnique.Contains(TCondU(LE, 1)) Then
            arrUnique.Add TCondU(LE, 1)
            CondU = True
          Else
            CondU = False
          End If
      End If
      If Cond1 And Cond2 And CondU Then
         LS = LS + 1
         For C = 1 To UBound(TDonn, 2)
            TDonn(LS, C) = TDonn(LE, C)
            Next C: End If: Next LE
   Do While LS < UBound(TDonn, 1)
      LS = LS + 1
      For C = 1 To UBound(TDonn, 2)
         TDonn(LS, C) = ""
         Next C: Loop
   UFILTRE = TDonn
   Set arrUnique = Nothing
End Function

Le premier argument est la plage sur laquelle va se faire le filtre.
Le deuxième argument est la première condition sur laquelle va se faire le filtre (La plage doit correspondre à une colonne avec le même nombre de lignes que la plage de départ)
Le troisième argument est la deuxième condition sur laquelle va se faire le filtre (La plage doit correspondre à une colonne avec le même nombre de lignes que la plage de départ)
Le quatrième argument est la plage sur laquelle on va chercher les valeurs uniques sur la plage filtrée (La plage doit correspondre à une colonne avec le même nombre de lignes que la plage de départ).
Il faut rentrer la formule avec un CTRL MAJ ENT après avoir sélectionné une plage de la taille des données initiales.
La fonction renvoie un tableau qui correspond au filtre demandé.

La deuxième fonction personnalisée est NBUFILTRE :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
Function NBUFILTRE(ByVal TDonn, Optional ByVal TCond1, _
                Optional ByVal TCond2, Optional ByVal TCondU) As Integer
   Dim LE&, LS&, C&
   Dim Cond1 As Boolean, Cond2 As Boolean, CondU As Boolean
   Dim DisCond1 As Boolean, DisCond2 As Boolean, DisCondU As Boolean
   Dim arrUnique As Object
   Set arrUnique = CreateObject("System.Collections.ArrayList")
   NBUFILTRE = 0
   If TypeOf TDonn Is Range Then TDonn = TDonn.Value
   If TypeOf TCond1 Is Range Then TCond1 = TCond1.Value
   If TypeOf TCond2 Is Range Then TCond2 = TCond2.Value
   If TypeOf TCondU Is Range Then TCondU = TCondU.Value
   For LE = 1 To UBound(TDonn, 1)
      If IsMissing(TCond1) Then
           Cond1 = True
      Else
          If TypeOf TCond1 Is Range Then TCond1 = TCond1.Value
          Cond1 = TCond1(LE, 1)
      End If
      If IsMissing(TCond2) Then
           Cond2 = True
      Else
          If TypeOf TCond2 Is Range Then TCond2 = TCond2.Value
          Cond2 = TCond2(LE, 1)
      End If
      If IsMissing(TCondU) Then
           CondU = True
      Else
          If TypeOf TCondU Is Range Then TCondU = TCondU.Value
          If Not arrUnique.Contains(TCondU(LE, 1)) Then
            arrUnique.Add TCondU(LE, 1)
            CondU = True
          Else
            CondU = False
          End If
      End If
      If Cond1 And Cond2 And CondU Then
        NBUFILTRE = NBUFILTRE + 1
      End If
   Next LE
   Set arrUnique = Nothing
End Function

C'est pour ainsi dire la même chose que la première fonction sauf qu'elle renvoie uniquement le nombre de lignes filtrées.
Il faut aussi la rentrer comme une formule matricielle mais cette fois-ci en ne sélectionnant qu'une cellule.

Les fonctions sont certainement buggées et à optimiser mais cela semble fonctionner avec les données de cette discussion :

Nom : UFILTREXL.gif
Affichages : 3856
Taille : 111,4 Ko

Nom : UFILTREXL.gif
Affichages : 3856
Taille : 111,4 Ko

Ceci a été testé sous Excel 2016 Windows 10.

Ami calmant, J.P

**Raccourcix** · 30/06/2022, 12h03

Envoyé par Pierre Dumas

Si je puis me permettre, à priori (je ne connais pas vos données), cela ressemble à une erreur de conception (ma stagiaire de la semaine dernière travaillait aussi de cette façon).
On ne transforme pas un TCD en un tableau structuré.
Les données sont dans un Tableau au départ. Puis on va chercher dedans les informations qui nous intéressent via des TCD ou des formules en XXX.SI.ENS par exemple.

Il peut arriver qu'il soit nécessaire de rajouter des colonnes de calcul dans le tableau de départ. Tant que le nombre de lignes est peu conséquent, cela ne pose pas de problème. Si cela se compte en plusieurs (dizaine/centaines de) milliers, il devient urgent de passer à PowerQuery et/ou PowerPivot.

...

Je suis d'accord avec Pierre (

) bien que ce matin même j'ai réalisé un tel copier/coller d'un TCD dans un tableau. Les données source du TCD provenaient de Power BI (cube OLAP) et non d'un tableau, et sauf peut-être via une requête MDX assez complexe à mettre en œuvre, pas moyen d'utiliser les données du TCD autrement.

Comme je l'indiquais dans mon premier post, le "compte distinct" est disponible facilement dans les TCD basé sur le modèle de données (sans avoir besoin de créer une mesure DAX dans Power Pivot)

Enfin, voici une formule matricielle qui doit être compatible Excel 2016 (je suis allé à la ligne pour faciliter la lecture)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
=SOMMEPROD(
(Tableau[Num département]=69)*
(Tableau[Type logement]="T1")/
NB.SI.ENS(Tableau[N° convention];Tableau[N° convention];Tableau[Num département];Tableau[Num département];Tableau[Type logement];Tableau[Type logement])

Stéphane

**Lilie01** · 30/06/2022, 16h05

Déjà merci à tous car vraiment c'est sympa de prendre le temps de répondre et de chercher!

Je suis d'accord avec vous pour le tableau de départ. Mais là je reprend le travail d'un presta extérieur et la consigne c'est de ne rien modifier. Juste de mettre à jour le tableau de données pour que les formules que ce presta a mis en place recalculent tout correctement.

Or, il y a une erreur. Leur clé unique utilisée dans leurs formules pour compter n'est en fait pas une clé unique.
Pour rectifier il faut que je fasse ce que je vous demande à savoir une formule qui me retire ces doublons.

La formule suivant fonctionne parfaitement!!!!! Merci Stephane!

=SOMMEPROD((Base_Conventions[Département]="69")*(Base_Conventions[Type de réservataire]="Commune")/NB.SI.ENS(Base_Conventions[Numéro convention];Base_Conventions[Numéro convention];Base_Conventions[Département];Base_Conventions[Département];Base_Conventions[Type de réservataire];Base_Conventions[Type de réservataire]))

Pour l'autre formule avec unique et filtre, je n'ai pas le bon résultat. J'avoue ne pas savoir pourquoi...

Merci encore!!!

**Lilie01** · 30/06/2022, 16h18

Le seul truc c'est que la formule fonctionne mais que je ne la comprends pas bien. Donc oui je vais la conserver bien au chaud mais j'aimerai vraiment la comprendre... Si tu as quelques explications en me la décortiquant... Ce serait top

**Raccourcix** · 30/06/2022, 16h20

pour vous aider il faudrait un extrait de votre fichier avec des données anonymisées
car c'est difficile de savoir pourquoi votre formule ne renvoie pas la valeur attendue
Stéphane

**jurassic pork** · 01/07/2022, 07h05

Hello,

Envoyé par Lilie01

Le seul truc c'est que la formule fonctionne mais que je ne la comprends pas bien. Donc oui je vais la conserver bien au chaud mais j'aimerai vraiment la comprendre... Si tu as quelques explications en me la décortiquant... Ce serait top

Le meilleur moyen pour comprendre une formule c'est de la déboguer. Dans Excel on peut faire cela grâce à la boîte de dialogue d'évaluation de formule (Menu Formules/Evaluer la formule) :

Il est parfois difficile à comprendre comment une formule imbriquée calcule un résultat final, étant donné la nécessité de plusieurs calculs intermédiaires et de tests logiques. Toutefois, vous pouvez utiliser la boîte de dialogue Évaluation de formule pour visualiser les différentes parties d’une formule imbriquée évaluée dans l’ordre de calcul de la formule.

Voici ce que cela donne dans le cas de la formule distillée par Raccourcix :

Nom : DebugFormule.gif
Affichages : 3801
Taille : 344,6 Ko