Nbre d'occurences identiques dans une col en fonction d'un critère d'une autre col

[JohnBill]

Bonjour à tout le forum et à ceux qui y oeuvrent.
Bien qu'ayant fouillé tout le forum, je n'arrive pas à résoudre mes problèmes de comptage.
J'explique. Mon fichier gére ma base de CDs et est assez volumineux (+ de 100000 lignes), c'est peut ètre de là que vient le pb.
Dans une colonne se trouve les noms des artistes, dans une autre celui des albums , une autre les titres des chansons et une dernière avec le genre musical.
Je voudrait comptabiliser le nombre de chanteurs et le nombre d'albums de chaque genre.
Autre question : puis je avoir la base dans un fichier et les statistiques dans un autre (Question temps à l'ouverture, à l'enregistrement et à la fermeture du fichier stats).
Merci à tous ceux qui pourrons m'aider. Je joins un extrait de mon fichier pour plus de compréhension.

 

[Tibo]

Re : Nbre d'occurences identiques dans une col en fonction d'un critère d'une autre c

Bonjour JohnBill,

Un essai avec une formule matricielle en C4 :

=SOMME(SI(DisquesSurCD!$E$2:$E$38=$B4;(1/NB.SI(DisquesSurCD!$B$2:$B$38;DisquesSurCD!$B$2:$B$38))))

Formule matricielle à valider par CTRL + MAJ + ENTREE

Plage de cellules à adapter.

Cela dit, sur plus de 100 000 lignes, ça va mettre un certain temps, voire un temps certain en temps de calcul.

Je te laisse essayer

@+

 

[BOISGONTIER]

Re : Nbre d'occurences identiques dans une col en fonction d'un critère d'une autre c

Bonjour,

Avec fonction perso (+rapide)

Les formules matricielles deviennent lentes pour un nombre d'éléments important (>1sec pour 4000 items).
La fonction personnalisée donne 0,015sec pour 4000 items (dans un module :Alt+F11 puis insertion/module)

http://boisgontierjacques.free.fr/fichiers/Matriciel/MatOccurDiff.xls

=ItemsDifferentsCritere(chanteurs;genre;B4)

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

 

[Monique]

Re : Nbre d'occurences identiques dans une col en fonction d'un critère d'une autre c

Bonjour,
Avec Frequence()
Pas plus rapide mais plages nommées qui accélèrent le recalcul

 

[JohnBill]

Re : Nbre d'occurences identiques dans une col en fonction d'un critère d'une autre c

Un grand merci à Tibo, Monique et Boisgontier. Les formules SOMME et FREQUENCE fonctionnent très bien sur le fichier d'exemples mais je ne sais pas si cela fonctionne sur mon fichier car des heures après avoir validé une seule formule, Excel s'est bloqué. Je suppose que la base est trop importante pour lui (J'ai effectivement 160504 lignes. Faudra qu'un jour je me mette à Access.
Par contre, la fonction perso de Boigontier n'a mis que deux minutes après l'avoir appliqué à une vingtaine de catégories. Question rapidité, y a pas mieux. L'efficacité est aussi au rendez vous. Tout fonctione bien.
Mon problème est résolu et je vous remercie encore pour vos réponses rapides.
A +

 

[BOISGONTIER]

Re : Nbre d'occurences identiques dans une col en fonction d'un critère d'une autre c

Essai avec SQL

Sub groupe()
   'Outils/Références Microsoft ActiveX Data Object 2.8
  ActiveWorkbook.Names.Add Name:="MaBd", RefersTo:=Sheets(1).[A1].CurrentRegion
  Dim rs As ADODB.Recordset
  Set cnn = New ADODB.Connection
  cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name
     Sql = "select genre,count(*) as ttal from (SELECT  distinct artiste,genre From MaBD) group by genre"
     Set rs = cnn.Execute(Sql)
     [F4].CopyFromRecordset rs
     rs.Close
     cnn.Close
     Set rs = Nothing
End Sub

JB

 

[JohnBill]

Re : Nbre d'occurences identiques dans une col en fonction d'un critère d'une autre c

Bonjour Boisgontier et merci encore pour l'amélioration que tu essaie d'apporter à mon Pb.
Mais, car il y a un "mais", j'ai ouvert le fichier que tu m'a renvoyé avec ta macro à base de Sql, je n'y ai rien touché, j'ai ouvert Visual Basic, j'ai fait un pas à pas détaillé pour comprendre comment elle fonctionne et j'ai une erreur Visual Basic qui m'amène sur le débogage lorsque j'arrive sur la ligne :
"Set rs = cnn.Execute(Sql)"
Texte du message d'erreur : [Microsoft Jet][Pilote ODBC Excel) le moteur de base de données Microsoft n'a pas pu trouver l'objet 'MaBd'. Assurez vous que l'objet existe, etc, etc...
Faut il installer un complément quelconque ?
PS : 'MaBd' est bien présente dans le gestionnaire des noms.

 

[BOISGONTIER]

Re : Nbre d'occurences identiques dans une col en fonction d'un critère d'une autre c

Bonjour,

Le fichier a t-il été enregistré?

JB
Formation Excel VBA JB

 

[JohnBill]

Re : Nbre d'occurences identiques dans une col en fonction d'un critère d'une autre c

Non. Je l'ai enregistré et cela fonctionne et c'est même plus rapide (Du moins, il me semble).
Une dernière question car tu es très aimable et aussi beaucoup plus calé que moi sur le sujet.
Que faut il modifier à ton code si la base de données se trouve dans un fichier excel différent de celui qui gère les statistiques ? Exemple : Une fichier nommé "CD2010Base" sur une feuille nommée "DisquesSurCD"

Merci une nouvelle fois.

 

[BOISGONTIER]

Re : Nbre d'occurences identiques dans une col en fonction d'un critère d'une autre c

Sub groupe()
  'Outils/Références Microsoft ActiveX Data Object 2.8
  'ActiveWorkbook.Names.Add Name:="MaBd", RefersTo:=Sheets(1).[A1].CurrentRegion
  'répertoire = ThisWorkbook.Path
  'fichier = ThisWorkbook.Name
  répertoire = "c:\mesdoc\excelmacronouveau\1001exemples"
  fichier = "xxxx.xls"
  Dim rs As ADODB.Recordset
  Set cnn = New ADODB.Connection
  cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" & répertoire & "\" & fichier
     Sql = "select genre,count(*) as ttal from (SELECT  distinct artiste,genre From MaBD) group by genre"
     Set rs = cnn.Execute(Sql)
     [F4].CopyFromRecordset rs
     rs.Close
     cnn.Close
     Set rs = Nothing
End Sub

JB

 

[JohnBill]

Re : Nbre d'occurences identiques dans une col en fonction d'un critère d'une autre c

Merci. C'est parfait.
Je suppose que pour comptabiliser les albums il suffit de changer "artiste" par "albums" dans l'instruction :
(SELECT distinct artiste,genre from MaBD) et [F4] par une autre cellule de destination.