Nb élmts sans doublons avec conditions

[jl.delor]

Bonjour le forum,

Qui dispose d'une formule optimisée en temps de calcul pour compter un nombre d'éléments sans doublons avec conditions ?

Ma situation : J'ai 20 vendeurs, leurs dossiers (20.000) et le nombre de visites (117.000).
Le but est de calculer le nombre de dossiers par vendeur.
J'ai bien 2 formules possibles qui fontionnent sur un petit échantillon (SOMMEPROD ou SOMME(SI).

Quand le PC ne plante pas, c'est au minimum 20 minutes de calcul pour 1 seul vendeur.

Merci à tous de vos idées.

 

[R@chid]

Re : Nb élmts sans doublons avec conditions

Bonjour,
Une formule qui est plus rapide,
sélectionner F2:F3
copier/coller cette formule dans la barre de formules, puis valider par Ctrl+Maj+Entree

=FREQUENCE(SI(FREQUENCE(EQUIV(B2:B19;B2:B19;0);LIGNE(B2:B19)-1);EQUIV(A2:A19;E2:E3;0));LIGNE(E2:E3)-1)

@ adapter à ton fichier original.

@ + +

 

[BOISGONTIER]

Re : Nb élmts sans doublons avec conditions

Bonsoir,

Avec une fonction perso

=ItemsDifferentsCritere($B$2:$B$19;$A$2:$A$19;E2)

Sous forme matricielle (si plusieurs critères)

-sélectionner h2:h3
=ItemsDifferentsCritereMat(B2:B19;A2:A19;E2:E3)
valider avec maj+ctrl+entrée

Pour 120000 lignes et 20 critères : 20 s

Function ItemsDifferentsCritere(champ, champcritere, critere)
  Set MonDico = CreateObject("Scripting.Dictionary")
  a = champ
  b = champcritere
  For i = 1 To champ.Count
    If b(i, 1) = critere And a(i, 1) <> "" Then
       temp = a(i, 1)
       MonDico(temp) = temp
    End If
  Next i
  ItemsDifferentsCritere = MonDico.Count
End Function

JB

 

[R@chid]

Re : Nb élmts sans doublons avec conditions

Bonjour @ tous,
Même avec 120000 lignes, la formule avec FREQUENCE() reste la plus rapide..
essayer sur le fichier joint avec,

=FREQUENCE(SI(FREQUENCE(EQUIV(B2:B121697;B2:B121697;0);LIGNE(B2:B121697)-1);EQUIV(A2:A121697;E2:E21;0));LIGNE(1:20))

En sélectionnant la plage rose et collant la formule dans la barre de formules tout en validant en matricielle..

Voir PJ

@ + +

 

[MJ13]

Re : Nb élmts sans doublons avec conditions

Bonjour à tous

J'aurais bien vu un TCD avec un soupçon de VBA .

 

[R@chid]

Re : Nb élmts sans doublons avec conditions

Bonsoir @ tous,
Salut MJ13,

Bonjour à tous

J'aurais bien vu un TCD avec un soupçon de VBA .

Du fait que notre ami travaille sur XL2010, il peut profiter de PowerPivot
Regarde la pièce jointe 907072
@ + +

 

[jl.delor]

Re : Nb élmts sans doublons avec conditions

C'est Noël ; Que de réponses, merci à chacun d'entre vous.
R@chid : J'ai essayé la formule matricielle : il m'a quand même fallu 5 minutes de calculs.
MJ13 : c'est vrai un TCD avec un NB.SI ça marche aussi (mais nécessite une manipulation de plus).
Pour simplifier d'avantage, je vois qu'il faut passer par macro (suis débutant en VBA) : merci pour vos contributions, vous m'avez donné pleins d'exercices pour apprendre.
Cordialement
Jean-Luc

 

[R@chid]

Re : Nb élmts sans doublons avec conditions

Bonsoir @ tous,

R@chid : J'ai essayé la formule matricielle : il m'a quand même fallu 5 minutes de calculs.

Peut-être que ton fichier original contient des autres formules qui ralentissent le calcul ou bien d'autres fichiers contenant des formules matricielles sont ouverts, je voie les résultats apparaitre avant de taper la touche Entrée
Tu peux faire avec PowertPivot sans TCD avec NB.SI()

@ + +

 

[gosselien]

Re : Nb élmts sans doublons avec conditions

et c'est là qu'on se dit qu'avec un Excel MAC ça ne fonctionne pas...
"Set MonDico = CreateObject("Scripting.Dictionary")"
donc on essaye sur pc 🙂
merdouille 😀

 

[mapomme]

Re : Nb élmts sans doublons avec conditions

Bonjour à tous,

Pour le fun, une version par VBA avec un Dictionary qui prend moins de 0,4 s sur ma bécane.

Sub ListeSansDoublons()
Dim tablo, i&, i0&, i1&, dico As New Dictionary, s$, xkey, t As Single

  t = Timer()
  Application.ScreenUpdating = False
  tablo = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row).Value
  Range("e2:f" & Rows.Count).ClearContents
  i0 = LBound(tablo): i1 = UBound(tablo)
  For i = i0 To i1
    s = tablo(i, 2) & "}"
    If dico.Exists(tablo(i, 1)) Then
      If InStr(dico(tablo(i, 1)), s) = 0 Then dico(tablo(i, 1)) = dico(tablo(i, 1)) & s
    Else
      dico(tablo(i, 1)) = s
    End If
  Next i
  For Each xkey In dico.Keys
    dico(xkey) = UBound(Split(dico(xkey), "}"))
  Next xkey
  
  Range("e2").Resize(dico.Count) = Application.Transpose(dico.Keys)
  Range("f2").Resize(dico.Count) = Application.Transpose(dico.Items)
  Application.ScreenUpdating = True
  MsgBox Format(Timer() - t, "0.000") & " secondes"
End Sub

 

[jl.delor]

Re : Nb élmts sans doublons avec conditions

Encore merci pour toutes ces pistes.
Le VBA va être de l'exercice.
R@chid, j'ai demandé l'installation à mon pilote bureautique de PowerPivot, cette piste est aussi intéressante ; il est vrai que mon fichier comporte d'autres calculs complémentaires (117.000 L x 118C de données source et 240 C de résultat calculé - les formules restent sur un ligne dédiée pour ne pas alourdir le tout !🙄) ce qui explique la lenteur de ta formule.
Cordialement
Jean-Luc