Compter le nombre de cellules sans les doublons

Moonshine33

XLDnaute Nouveau
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.
 

Fichiers joints

job75

XLDnaute Barbatruc
Bonjour Moonshine33, Pierre,

Formule matricielle en F15 :
Code:
=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+
 

Fichiers joints

job75

XLDnaute Barbatruc
Re,

Il est plus classique de mettre des dates en ligne 14, la formule est un peu plus simple :
Code:
=NB(EQUIV({"A"."B"."C"."D"};REPT($B2:$B33;(MOIS($A2:$A33)=MOIS(F14))*(ANNEE($A2:$A33)=ANNEE(F14)));0))
Fichier (2).

A+
 

Fichiers joints

KIM

XLDnaute Accro
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
 

Fichiers joints

hbenalia

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

Fichiers joints

job75

XLDnaute Barbatruc
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

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

Code:
=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
 
Dernière édition:

job75

XLDnaute Barbatruc
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 :
Code:
=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+
 

Fichiers joints

job75

XLDnaute Barbatruc
Re,

Si l'on tient au VBA une solution consiste à entrer la formule précédente dans G4:J6 :
Code:
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+
 

Fichiers joints

job75

XLDnaute Barbatruc
Re,

Des solutions plus élaborées avec une feuille "Produits" et des tableaux Excel :
Code:
=NB(EQUIV(Tableau1[Produit];REPT(Tableau2[NOM];Tableau2[DP]&Tableau2[Type]=$F4&G$3);0))
Code:
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+
 

Fichiers joints

job75

XLDnaute Barbatruc
Re,

Dans la foulée retour sur le fichier de Moonshine33 avec 2 tableaux Excel :
Code:
=NB(EQUIV(Tableau2[Produit];REPT(Tableau1[NOM];(MOIS(Tableau1[Date])=MOIS(G14))*(ANNEE(Tableau1[Date])=ANNEE(G14)));0))
Fichier (3).

A+
 

Fichiers joints

zebanx

XLDnaute Accro
Bonjour à tous,

@job75 quel acharnement - merci !:)

J'ai tenté de repatir du (très bon) code VBA de Pierre-Jean pour KIM (#5)
Ca donnerait ça en prenant une correction sur le "-1" et réglé par une formule car je n'arrive pas à coller une instruction dans la fonction pour la faire passer de -1 à 0 s'il n'y a pas d'occurences.

Cdlt
zebanx

------
Function nb_sp2(zone, ref, ref2)
tablo = zone
For n = LBound(tablo) To UBound(tablo)
If CStr((tablo(n, 1)) & "-" & (tablo(n, 2))) = CStr((ref) & "-" & (ref2)) Then
If InStr(dif, tablo(n, 3)) = 0 Then dif = dif & tablo(n, 3) & ";"
End If
Next
nb_sp2 = UBound(Split(dif, ";"))
If np_sp2 < 0 Then
np_sp2 = 0
End If
End Function
 

Fichiers joints

KIM

XLDnaute Accro
Bonjour le fil,
Je salue les amis Pierrejean, job75 et vous remercie de votre participation à ce forum.
Merci Zebanx pour ta fonction.
Amitiés
KIM
 

job75

XLDnaute Barbatruc
Bonjour le fil, bonjour Nicole,

Pour le problème de KIM voici 2 fonctions VBA utilisant le Dictionary.

1) Sans feuille "Produits", fichier (3) :
Code:
Dim d As Object 'mémorise pour gagner du temps sur la création

Function nb_sp1&(zone, texte$)
Dim n&
zone = zone 'matrice, plus rapide
If d Is Nothing Then Set d = CreateObject("Scripting.Dictionary") Else d.RemoveAll
For n = 1 To UBound(zone)
  If zone(n, 1) & zone(n, 2) = texte Then d(zone(n, 3)) = ""
Next
nb_sp1 = d.Count
End Function
2) Avec feuille "Produits", fichier (3 bis) :
Code:
Dim d As Object 'mémorise pour gagner du temps sur la création

Function nb_sp2&(zone1, zone2, texte$)
Dim n&
zone1 = zone1.Resize(, 2): zone2 = zone2 'matrices, plus rapides (au moins 2 éléments)
If d Is Nothing Then Set d = CreateObject("Scripting.Dictionary") Else d.RemoveAll
For n = 1 To UBound(zone1): d(zone1(n, 1)) = "": Next
For n = 1 To UBound(zone2)
  If zone2(n, 1) & zone2(n, 2) = texte Then _
    If d.exists(zone2(n, 3)) Then nb_sp2 = nb_sp2 + 1: d.Remove zone2(n, 3)
Next
End Function
Durées des recalculs chez moi :

- fichier Compter sans doublons(2).xlsx => 0,29 [Edit : corrigé 0,19] millième de seconde

- fichier Compter sans doublons(3).xlsm => 1,02 [Edit : corrigé 0,92] millième de seconde

- fichier Compter sans doublons(3 bis).xlsm => 1,15 [Edit : corrigé 1,05] millième de seconde

A+
 

Fichiers joints

Dernière édition:

KIM

XLDnaute Accro
Bonjour le fil,
@job75, Merci pour ces 2 macros, une autre façon de déclarer les données. J'en profite pour apprendre à optimiser le code utilisé et à résoudre ses problèmes.
Surpris de remarquer qu'une formule est plus rapide qu'un tableau Dictionnary!

Merci encore
Amitiés
KIM
 
Dernière édition:

Moonshine33

XLDnaute Nouveau
Bonjour à tous,
Merci pour vos nombreuses réponses, je vais tester ça. Juste une précision, mais je vois que quelqu'un a soulevé le point, sur mon exemple A-B-C-D ne seront pas connus, je souhaite uniquement que les cellules différentes soient comptées dans un intervalle de temps donné, mais je ne pourrais pas rentrer leur nom dans la formule / macro.
Merci encore,
Moonshine
 

Discussions similaires


Haut Bas