Nombre d'occurences avec deux critères variables

Version imprimable

31/01/2013, 17h02
bigor44

Nombre d'occurences avec deux critères variables

Bonjour,

je suis complètement perdu pour un projet:
J'ai un tableau avec pleins de colonnes mais seules deux m'intéressent:
- Numéro de contrat (Alphanumérique)
- Résumé (Alphanumérique)

Il y a environ 6000 entrées,
J'ai besoin de savoir cb de fois un contrat a été saisi avec le même résumé
Le résultat devrait donné
Colonne A: Contrat
Colonne B: Résumé
Colonne C: Nombre d'occurences.
Contraintes:
Je ne peux pas avoir la liste complete des numéros de contrat ou des résumés

Auriez vous une piste ou une fonction que je ne connais pas ?

Bonjour,

Avec les contrats en colonne A, les résumés en colonne B et les résultats en colonne F:H :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub test()
    Dim Ctr As Long, Dico As Object, C As Range, Ligne As Long
    Set Dico = CreateObject("Scripting.Dictionary")
    Ligne = 0
    For Each C In Range([A1], Cells(Rows.Count, 1).End(xlUp))
        If Not Dico.exists(C.Value) Then
            Dico.Add C.Value, C.Value
            Ctr = Evaluate("COUNTIFS(A:A,""" & C.Value & """,B:B,""" & C.Offset(, 1).Value & """)")
            Ligne = Ligne + 1
            Cells(Ligne, 6) = C.Value
            Cells(Ligne, 7) = C.Offset(, 1).Value
            Cells(Ligne, 8) = Ctr
        End If
    Next C
End Sub

Dis-moi si tu n'arrives pas à adapter la macro ou si tu ne la comprends pas.

Effectivement, je ne comprends pas son fonctionnement.

Alors, j'ai testé, et je n'obtiens pas tout à fait le résultat attendu mais je me suis peut être mal exprimé et vu que je ne comprends pas le fonctionnement de ta macro, je reviens demander de l'aide
Alors, j'ai créé un tableau de test, et voici le résultat

"A"   "B"   "C" "D" "E" "F"  "G"   "H'
A	titi                     A    titi    3
A	toto                   B    toto   2
B	toto
B	titi
A	titi
A	toto
A	titi
B	toto
B      titi

J'ai fait une petite modif pour ne pas afficher les cas ou Ctr=1

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub test()
    Dim Ctr As Long, Dico As Object, C As Range, Ligne As Long
    Set Dico = CreateObject("Scripting.Dictionary")
    Ligne = 0
    For Each C In Range([A1], Cells(Rows.Count, 1).End(xlUp))
        If Not Dico.exists(C.Value) Then
            Dico.Add C.Value, C.Value
            Ctr = Evaluate("COUNTIFS(A:A,""" & C.Value & """,B:B,""" & C.Offset(, 1).Value & """)")
            Ligne = Ligne + 1
            If Not Ctr = 1 Then
                Cells(Ligne, 6) = C.Value
                Cells(Ligne, 7) = C.Offset(, 1).Value
                Cells(Ligne, 8) = Ctr
            End If
        End If
    Next C
End Sub

Le résultat que j'attends ce serait:
A titi 3
A toto 2
B toto 2
B titi 2

Merci d'avance

Au temps pour moi (ce code n'est pas valable pour les versions antérieures à XL2007) :

Code:

1
2
3
4
5
6
7
8
9
10
Sub test1()
[A:B].Copy [F1]
Range([F1], [F1].End(xlDown)).Resize(, 2).RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo
    For Each C In Range([F1], Cells(Rows.Count, 6).End(xlUp))
        Ctr = Evaluate("COUNTIFS(A:A,""" & C.Value & """,B:B,""" & C.Offset(, 1).Value & """)")
        If Ctr > 1 Then
            Cells(C.Row, 8) = Ctr
        End If
    Next C
End Sub

01/02/2013, 18h21
bigor44

C'est parfait à un poil près mais je m'en arrangerais
Si je me mets un jour à la sculpture, j'érigerais une statue en ton honneur ;)

PAr contre, si tu peux m'expliquer comment elle fonctionne, ce serait sympa:
Qu'est ce que [F1] ?
Pourrais tu "traduire" tes lignes 3,4 et 5 ?

Merci d'avance
01/02/2013, 20h39
Daniel.C
Citation:

Qu'est ce que [F1] ?

C'est la cellule F1, une autre façon d'écrire "Range("F1")" (sauf que c'est plus court, et comme je suis feignant...). Par contre, si tu écris "Range("F1")" et que tu ajoutes un point, l'éditeur VB va te proposer les propriétés et les méthodes de l'objet Range, ce qui n'est pas le cas avec "[F1]". A toi de choisir, donc.

Code:

Range([F1], [F1].End(xlDown))

représente la plage de cellules entre F1 et la première cellule précédent la première cellule vide suivant F1 (donc toutes les cellules non vides partant de F1). "Resize" redimensionne la plage de cellules. Le premier paramètre est le nombre de lignes, le second, celui des colonnes. Donc "Resize(,2)" redimensionne la plage sur le même nombre de lignes (omis) et sur 2 colonnes.

"RemoveDuplicates" cherche et supprime les lignes de la plage indiquée. "Columns:=Array(1, 2)" indique que la recherche des doublons se fait suer les deux premières colonnes. "Header:=xlNo" signifie qu'il n'y a pas d'entêtes.

Code:

Range([F1], Cells(Rows.Count, 6).End(xlUp))

est une méthode proche de la précédente pour déterminer la plage de cellules utilisées. La première cellule est F1 et la dernière est la première cellule non vide en partant de la dernière cellule de la colonne. Les deux méthodes sont très voisines (sauf s'il y a une plage de cellules vides au milieu des cellules non vides).
Code:

1 2 3 4 For Each C In Range([F1], Cells(Rows.Count, 6).End(xlUp)) '... '... Next C
Note qu'on utilise "Rows.Count" qui est le nombre de lignes de la feuille. Avant Excel 2007, on pouvait mettre "65536" sans risque d'erreur, mais depuis, "Rows.Count" donne le numéro de la dernière ligne, qu'il s'agisse d'un classeur ancien ou nouveau format.

Une fois qu'on a défini la plage, on boucle sur chaque cellule (C représente cette cellule). On récupère dans la variable "Ctr" le résultat de la formule "Countifs" qui est l'équivalent de la formule Excel "NB.SI.ENS" qui permet de faire ce type de comptage.