Compter le nombre de cellules sans les doublons

[Moonshine33]

Bonjour,
Dans une base de données, je souhaite compter le nombre de cellules d'une colonne sans compter les doublons, sous condition d'une date située dans une autre colonne. Ci-joint un fichier exemple pour mieux comprendre ma demande.
Ma demande est de calculer par exemple pour le mois de janvier 2017 situé en colonne A, le nombre de cellules différentes situées en colonne B, mais sans compter les récurrences.
En gros, je souhaite pouvoir compléter le tableau que j'ai mis sur la page (où ma valeur sera toujours 4 pour cet exemple, car je n'ai que 4 produits A-B-C-D), mais avec une formule excel ou une macro.
J'ai vu la formule : {=SOMME(SI(PLAGE<>"";1/NB.SI(PLAGE;PLAGE))), mais ça ne fonctionne pas comme je souhaite...
Merci d'avance à ceux qui prendront le temps de m'aider !
Moonshine.

 

[pierrejean]

Bonjour Mooshine33

Un essai avec une fonction personnalisée

 

[job75]

Bonjour Moonshine33, Pierre,

Formule matricielle en F15 :

=NB(EQUIV({"A"."B"."C"."D"};REPT($B2:$B33;(MOIS($A2:$A33)=--GAUCHE(F14;2))*(ANNEE($A2:$A33)=--DROITE(F14;4)));0))

A valider par Ctrl+Maj+Entrée et tirer vers la droite.

Fichier joint.

A+

 

[job75]

Re,

Il est plus classique de mettre des dates en ligne 14, la formule est un peu plus simple :

=NB(EQUIV({"A"."B"."C"."D"};REPT($B2:$B33;(MOIS($A2:$A33)=MOIS(F14))*(ANNEE($A2:$A33)=ANNEE(F14)));0))

Fichier (2).

A+

 

[KIM]

Bonjour le fil, job75, Moonshine33,
Bonjour pierrejean,
@pierrejean,
Ta fonction m'a intéressé, j'ai modifié la 1è colonne en texte et supprimé le test du format dans la fonctionne.
Est-il possible de rajouter un 2è critère dans ta fonction pour le calcul du nombre sans doublons car mes données sont sur 3 col et non sur 2 ? voir onglet type.
Merci d'avance
KIM

 

[hbenalia]

Bonjour à tous,
Un essai dans le fichier attaché avec une formule matricielle contenant deux plages dynamiques...
Cordialement

 

[job75]

Re, salut hbenalia,

Par curiosité j'ai testé les diverses solutions (en ajoutant 08/2017 chez pierrejean et hbenalia) :

- pierrejean (post #2) recalcul en 1,22 millième de seconde

- job75 (posts #3 ou 4) recalcul en 0,29 millième de seconde

- hbenalia (post #6) recalcul en 0,38 millième de seconde - mais l'année n'est pas précisée...

A+

 

[zebanx]

Re, salut hbenalia,
- job75 (posts #3 ou 4) recalcul en 0,29 millième de seconde

=NB(EQUIV({"A"."B"."C"."D"};REPT($B2:$B33;(MOIS($A2:$A33)=MOIS(F14))*(ANNEE($A2:$A33)=ANNEE(F14)));0))

Bonsoir JOB75 , hbnelia, Pierre-Jean, Kim, Moonshine et bonsoir à tous.

@job75
Ta formule est très bien et la plus rapide mais peut-on rendre l'array "A,B,C,D" non limité stp ?

@ Pierre-Jean :
Bonsoir,
superfonction (!), comme souligné par Kim, mais j'ai pas compris , en cas de non "occurence", pourquoi la fonction retourne -1 et non 0 ?

Merci par avance messieurs pour vos réponses et pour les formules / fonctions que vous avez proposées.

bonne soirée
zebanx

 

[job75]

Bonsoir zebanx,

peut-on rendre l'array "A,B,C,D" non limité stp ?

On peut remplacer l'array par une plage de cellules, mais comme les autres plages elle doit être limitée.

Bonne nuit.

 

[bcharef]

Bonjour à toutes et à tous,
Bonjour Moonshine33, pierrejean, job75,Ce lien n'existe plus, hbenalia et zebanx.

Voici un essai avec un Tableau Croisé Dynamique.

Salutations
.

 

[job75]

Bonjour à tous,

Juste pour saluer bcharef, très heureux de te revoir cher ami

A+

 

[job75]

Re,

Je reviens pour le problème de l'ami KIM, que je salue.

Si vous avez compris mes formules des posts #3 et 4 il n'est guère difficile d'adapter, en G4 :

=NB(EQUIV({"A"."B"."C"."D"};REPT($C$8:$C$39;$A$8:$A$39&$B$8:$B$39=$F4&G$3);0))

C'est bien sûr ici aussi une formule matricielle à valider par Ctrl+Maj+Entrée.

Fichier joint.

A+

 

[job75]

Re,

Si l'on tient au VBA une solution consiste à entrer la formule précédente dans G4:J6 :

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With [A7].CurrentRegion
  .Columns(1).Name = "DP"
  .Columns(2).Name = "Type"
  .Columns(3).Name = "NOM"
End With
With [G4:J6]
  .Cells(1).FormulaArray = "=COUNT(MATCH({""A"",""B"",""C"",""D""},REPT(NOM,DP&Type=$F4&G$3),0))"
  .Cells(1).AutoFill .Rows(1)
  .Rows(1).AutoFill .Rows
  .Value = .Value
End With
Application.EnableEvents = True
End Sub

Fichier .xlsm joint.

A+

 

[job75]

Re,

Des solutions plus élaborées avec une feuille "Produits" et des tableaux Excel :

=NB(EQUIV(Tableau1[Produit];REPT(Tableau2[NOM];Tableau2[DP]&Tableau2[Type]=$F4&G$3);0))

Private Sub Worksheet_Activate()
Worksheet_Change [A1]
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With [G4:J6]
  .Cells(1).FormulaArray = "=COUNT(MATCH(Tableau1[Produit],REPT(Tableau2[NOM],Tableau2[DP]&Tableau2[Type]=$F4&G$3),0))"
  .Cells(1).AutoFill .Rows(1)
  .Rows(1).AutoFill .Rows
  .Value = .Value
End With
Application.EnableEvents = True
End Sub

Fichiers (2).

A+

 

[job75]

Re,

Dans la foulée retour sur le fichier de Moonshine33 avec 2 tableaux Excel :

=NB(EQUIV(Tableau2[Produit];REPT(Tableau1[NOM];(MOIS(Tableau1[Date])=MOIS(G14))*(ANNEE(Tableau1[Date])=ANNEE(G14)));0))

Fichier (3).

A+