COUNTIF - optimisation du code

Version imprimable

Bonjour,

Je cherche à savoir si mon code disponible ci-dessous peut être optimisé. Certes, je traite environ 40 000 lignes mais cela demande plus de 5 min de traitement sur mon PC qui ne me semble pas si mauvais. Je me dis du coup qu'il doit y avoir une meilleure syntaxe, plus efficace (voire plus propre).

Pour info, le tableau se décompose de la manière suivante:

Colonne C Colonne G Colonne K Colonne V

numéro de commande numéro de document code de validation du document résultat de la formule

Commande_1 doc_123 3 33,3%

Commande_1 doc_124 2 33,3%

Commande_1 doc_125 2 33,3%

Commande_2 doc_126 3 100%

L'idée est d'obtenir en colonne V le pourcentage de documents traités par commande (si traité alors code 3 dans la colonne K).
Je me suis dit qu'il fallait diviser le nombre de références par commande en code 3 PAR le nombre de références totale par commande.
Je suis même allé jusqu'à me dire qu'il fallait juste diviser le nombre d'occurences d'une commande en code 3 par le nombre total d'occurences de cette même commande (plus besoin d'utiliser le numéro de document).

Voici le code qui en découle :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Sub Test_countif() Application.ScreenUpdating = False Dim i As Long With Sheets("EntireList") i = .UsedRange.Rows.Count Range("V2:V" & i).Formula = "=COUNTIFS(C:C,C2,K:K,""3"")/COUNTIF(C:C,C2)" End With With Sheets("EntireList").Range("V:V") .Value = .Value End With ActiveSheet.Range("V:V").NumberFormat = "0.00%" Application.ScreenUpdating = True End Sub
Il fonctionne bien mais je me dis que cela peut être plus concis, plus efficace.

J'ai vu que certains utilisent l' Application.CountIf mais je n'arrive pas à savoir si une façon de faire est bien mieux conseillée que les autres.

Merci de votre attention.

Camzo

28/11/2017, 11h33
unparia

Bonjour

Citation:

J'ai vu que certains utilisent l' Application.CountIf mais je n'arrive pas à savoir si une façon de faire est bien mieux conseillée que les autres.

L'utilisation de Worksheetfunction.Countif en lieu et place d'une formule a au moins un avantage évident :
celui d'obtenir un résultat déjà constant et donc de ne pas avoir besoin de "figer" ensuite le résultat d'une formule. Non ?

PS : et surtout : par ton code, tu "inondes" de formules la totalité de ta colonne. (puis "figes" ensuite la totalité).

Merci pour ce retour. Voici mon nouveau code avec l'Application.Countifs qui permet notamment d'éviter l'inondation:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Dim i as Long, CIF1 as Variant, CIF2 as Variant
Lastrow = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Row
    With Sheets("EntireList")
       For i = Lastrow To 2 Step -1
            CIF1 = Application.CountIfs(Range("C:C"), Range("C" & i), Range("K:K"), "3")
            CIF2 = Application.CountIfs(Range("C:C"), Range("C" & i))
            If CIF2 <> 0 Then
            Range("V" & i) = CIF1 / CIF2
            Else
            Range("V" & i) = "0"
            End If
        Next i
    End With
 
    ActiveSheet.Range("V:V").NumberFormat = "0.00%"

Cela fonctionne bien, sans pour autant avoir l'impression que ce soit plus performant en terme de temps (reste à chronométrer). A peaufiner.

Cordialement,
Camzo

28/11/2017, 15h32
unparia

Citation:

reste à chronométrer

fais-le, donc.

Citation:

A peaufiner.

Oui.
Par exemple ainsi :
1) constituer un tableau dynamique (tu en connais les dimensions d'avance)
2) alimenter ce tableau plutôt que d'alimenter directement tes cellules
3) injecter ce tableau d'un seul coup suir ta feuille de calcul.

PS (et au passage) : prends la très bonne habitude de préciser la propriété utilisée de tes objets Excel, plutôt que de laisser VBA deviner de laquelle il s'agit. Monsieur VBA (et par voie de conséquence ton processeur t'en sauront gré).
28/11/2017, 17h33
Marc-L

Bonjour !

Citation:

Envoyé par camzo

Cela fonctionne bien, sans pour autant avoir l'impression que ce soit plus performant en terme de temps (reste à chronométrer). A peaufiner.

Cela ne sert à rien de calculer CIF1 sans savoir déjà si CIF2<>0
car dans le cas où il y a beaucoup de CIF2 égal à zéro alors il y a un gain de temps sur le non calcul de CIF1 !
Et dans le cas de CIF2 différent de zéro il n'y a même pas besoin de variable pour CIF1, calculer directement dans la division
car cela ne sert à rien d'affecter une variable pour ne l'utiliser qu'une seule fois, mis à part de perdre du temps !
Bref avec un peu de bon sens …

___________________________________________________________________________________________________________
Je suis Paris, Egypte, Nigeria, New-York, Mogadicio, Barcelone, London, Manchester, Stockholm, Istanbul, Berlin, Nice, Bruxelles, Charlie, …

Bonjour,

Effectivement, voici le code modifié tendant vers un peu plus de bon sens:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Sub Test()
 
    Lastrow = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Row
 
    Dim i As Long, CIF As Variant
 
    With Sheets("Test")
       For i = Lastrow To 2 Step -1
            CIF = Application.CountIfs(Range("C:C"), Range("C" & i))
            If CIF = 0 Then
            Range("V" & i).Value = "0"
            Else
            Range("V" & i).Value = Application.CountIfs(Range("C:C"), Range("C" & i), Range("K:K"), "3") / CIF
            End If
        Next i
    End With
    ActiveSheet.Range("V:V").NumberFormat = "0.00%"
End Sub

Merci également pour les pistes à creuser côté tableau dynamique. Je vais tenter de m'y mettre au plus vite.

Cordialement,
Camzo

Colonne C	Colonne G	Colonne K	Colonne V
numéro de commande	numéro de document	code de validation du document	résultat de la formule
Commande_1	doc_123	3	33,3%
Commande_1	doc_124	2	33,3%
Commande_1	doc_125	2	33,3%
Commande_2	doc_126	3	100%