Discussion :

[pascal4782]

Bonjour à tous,

Je viens de réaliser mon premier programme en variable tableau pour un traitement normalement plus rapide. J'ai donc respecté les règles du ScreenUpdating sur False, le calcul manuel, de limiter les Range et cells (chargement du tableau complet en 1 seul) et pourtant il continu de ramer !!!.
Le but du programme est d'additionner plusieurs colonnes (5 dans ce cas) d'un tableau qui en comporte 125 et de faire la somme. Pour tenter d'aller plus vite j'additionne colonne par colonne mais je pense que le problème vient de la boucle For A parce que le calcul est réalisé par ligne, enfin pure hypothèse, cependant plus le nombre de lignes est important plus le temps de traitement s'allonge.
Si quelqu'un pouvait me renseigner sur mes erreurs !!!

Cordialement

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
Sub tableauV2()
Dim T As Variant, A%
Dim a1%, a2%, a3%, a4%, a5%
Dim b1%, b2%, b3%, b4%, b5%
Dim c1%, c2%, c3%, c4%, c5%
Dim x%, y%
Dim tStart As Double, tEnd As Double
 
tStart = Time
Application.ScreenUpdating = False
Application.Calculation = xlManual
 
With Worksheets("A") 'feuille active
'Remplir le tableau des données de la plage
T = .Range("A5:DX" & Cells(Rows.Count, 1).End(xlUp).Row).Value
End With
 
'boucle sur colonne
For a1 = 1 To 121
    For a2 = a1 + 1 To 122
        For a3 = a2 + 1 To 123
            For a4 = a3 + 1 To 124
                For a5 = a4 + 1 To 125
'Boucle sur le tableau
                    For A = LBound(T, 1) To UBound(T, 1)
 
                    T(A, 127) = T(A, a1) + T(A, a2) + T(A, a3) + T(A, a4) + T(A, a5) 'addition des colonnes
                    If T(A, 127) > 0 Then T(A, 128) = 1 Else: T(A, 128) = 0 'test si element colonne 127>0 alors 1 sinon 0 en colonne 128
 
                    Next A
        'x = x + 1 'compte le nombre de boucles
        x = Application.Sum(Application.Index(T, , 128)) 'compte le nombre de données en colonne 128
 
        If x = y Then
        c1 = a1
        c2 = a2
        c3 = a3
        c4 = a4
        c5 = a5
        End If
 
        If x > y Then 'test pour garder la combinaison la plus élevée
        y = x
        b1 = a1
        b2 = a2
        b3 = a3
        b4 = a4
        b5 = a5
        End If
 
 
'MsgBox x
                Next
            Next
        Next
    Next
Next
 
'copie du tableau
With Worksheets("A")
'.Range("A4").Resize(UBound(T, 1), UBound(T, 2)) = T
.Range("EA1") = y
.Range("EB1") = b1
.Range("EC1") = b2
.Range("ED1") = b3
.Range("EE1") = b4
.Range("EF1") = b5
.Range("EB2") = c1
.Range("EC2") = c2
.Range("ED2") = c3
.Range("EE2") = c4
.Range("EF2") = c5
'.Range("EG1") = x
End With
 
tEnd = Time
Range("EJ1") = Format(tEnd - tStart, "HH:MM:SS")
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
ActiveWorkbook.Save
End Sub

[mercatog]

Quelque chose ne va pas dans tes boucles

Enfin de compte tu auras

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

T(i, 127) = T(i, 121) + T(i, 122) + T(i, 123) + T(i, 124) + T(i, 125)

[pascal4782]

Bonsoir mercatog,

En effet c'est le but recherché, je réalise la somme de toutes les combinaisons possibles de 5 colonnes parmi les 125, et la dernière combinaison sera les 5 dernières colonnes du tableau.
Cela représente environ plus de 16 M de cas possible s'est pourquoi je désire passer par des variables tableaux. Avec 10 lignes le temps de traitement est de 6 minutes mais j'ai 5000 lignes à traiter et c'est pourquoi je cherche une astuce pour ne pas boucler sur chaque ligne mais traiter directement par colonne.

Cordialement.

[Pierre Fauconnier]

Salut.

Pourrais-tu expliquer, avec une copie écran de ta feuille, ce que tu souhaites obtenir?

Avant toute chose, c'est un bon énoncé du problème qui permettra probablement de trouver une solution correcte.

Sinon, on peut seulement tenter d'améliorer ton code, alors que d'autres solutions, qui nous sont fermées faute d'explications, sont peut-être plus rapides.

[el_slapper]

+1 avec les autres, dans ce cas, c'est l'algorithme qui est couteux. Je ne vois pas d'erreur technique susceptible de plomber les performances. La quantité brute de traitements explique, à elle seule, tes lenteurs.

Donc il faut optimiser l'algorithme. Là, tu as 6 boucles imbriquées. Mon record en production est à 4, et je me le suis permis parceque la limite des boucles était très faible. Ce n'est pas seulement la boucle A : les 5 premières boucles sont déjà fort couteuses. J'ai fait(en vitesse) le code suivant pour compter :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Option Explicit
 
Sub calcul()
Dim x As Long
Dim a1 As Integer, a2 As Integer, a3 As Integer, a4 As Integer, a5 As Integer
x = 0
For a1 = 1 To 121
    For a2 = a1 + 1 To 122
        For a3 = a2 + 1 To 123
            For a4 = a3 + 1 To 124
                For a5 = a4 + 1 To 125
                    x = x + 1
                Next
            Next
        Next
    Next
Next
MsgBox (x)
End Sub

la msgbox indique 234 531 275 combinaisons.

Certes toute optimisation de cette boucle A sera positive, mais comme tu la fais 234 millions de fois, fatalement, ça sera couteux en termes de temps de calcul. C'est pour celà qu'il faut repenser ton algorithme. Et pour celà, il faut savoir EXACTEMENT quel maximum tu cherches. Il y a forcément des astuces pour faire ça mieux.

[Pierre Fauconnier]

Personnellement, je ne suis pas certain qu'on ait besoin d'itérer sur les boucles.

Il peut être intéressant d'utiliser Excel, lorsque l'on "fait" du VBA pour Excel. Mais il faut pour cela savoir ce qui doit être réalisé, indépendamment de la technique (qui sera) mise en place.

[pascal4782]

Bonjour à tous,

Merci à vous de regarder ma problématique.
@Pierre Fauconnier: Une copie écran n'apportera pas grand chose, par contre je suis d'accord qu'une bonne explication du problème est nécessaire
@el slapper: Le +1 doit probablement signifier que la demande initiale n'est pas suffisamment claire, désolé.

Je vais donc reprendre mon explication de texte avec un petit exemple. J'ai des données binaires (0-1) sur 125 colonnes et 5000 lignes et je dois déterminer la combinaison qui me permet d'obtenir le maximum de "1" avec la somme des 5 colonnes sélectionnées, exemple:
1) Sélection des colonnes A,B,C,D et E
Calcul ligne 1: a1+b1+c1+d1+e1, si la somme > 0 alors 1 sinon 0 en colonne 128
Calcul ligne 2: a2+b2+c2+d2+e2, si la somme > 0 alors 1 sinon 0 en colonne 128
Et cela jusqu'a la dernière ligne ensuite je fais le total de la colonne 128 et le stocke dans la variable "x petit indice 1"
2) Sélection des colonne A,B,C,D et F
a1+b1+c1+d1+f1, si la somme > 0 alors 1 sinon 0 en colonne 128
a2+b2+c2+d2+f2, si la somme > 0 alors 1 sinon 0 en colonne 128
Et cela jusqu'a la dernière ligne ensuite je fais le total de la colonne 128 et le stocke dans la variable "x petit indice 2"
si "x petit indice 2" > "x petit indice 1" alors on garde en mémoire les colonnes qui ont données ce résultat maximum "x2"
Le "x" est le "Maximum" que je cherche.

Encore un grand merci pour votre patience

[el_slapper]

Avant de répondre des bêtises, je veux être sur de bien comprendre :

(1)tu as 125 colonnes de données.
(2)Est-ce que
___(2a)pour chaque ligne, tu cherches la combinaison de colonnes qui donne le total le plus élevé
___(2b)pour chaque combinaison de 5 colonnes, tu cherches la ligne qui te proposes le total le plus elevé

[pascal4782]

Bonjour el slapper,

Je me suis encore mal exprimé, je vais donc reprendre pas à pas.
1) j'ai bien 125 colonnes de données
2) je cherche la combinaison de 5 colonnes qui me donne le "total" le plus élevés.
a) Boucles sur les colonnes.
Sélection de 5 colonnes
b) Boucle sur les lignes
Pour le calcul, par ligne je somme les 5 éléments des colonnes sélectionnées, si cette somme est > 0 alors le résultat de cette ligne est 1
Exemple a1=0, b1=1, c1=0, d1=0, e1=0 => Somme 1>0 alors 1
a2=0, b2=0, c2=0, d2=0, e2=0 => Somme 2=0 alors 0
a3=1, b3=1, c3=0, d3=1, e3=1 => Somme 3>0 alors 1
..... Fin boucle sur les lignes
c) Détermination du "total":
Le total est la somme des résultats de toutes les lignes. Si on reprend l'exemple du dessus alors nous aurons Total =1+0+1......
Ce résultat est stocké en mémoire pour comparaison lorsque 5 autres colonnes seront sélectionnées. On garde en memoire le resultat maximum
Sélection de 5 autres colonnes et on boucle sur les lignes

En fin de traitement j'ai besoin du total maximum et des 5 colonnes qui permettent d'obtenir ce résultat (exemple colonne 10, 70, 98, 102 et 124 donne un résultat maximum de 4500)

J'espère avoir été plus clair

Cordialement

[Pierre Fauconnier]

Wouah !

[yizashi]

Je ne suis pas un spécialiste du VBA, mais je pense qu'il doit être possible de supprimer certaines colonnes avant de commencer ton traitement. Bien sûr ça demande du temps de calcul, mais je pense que ça peut t'en faire gagner au final.

A titre d'exemple, si tu supprimes 16 colonnes (environ 10%) avec une sorte de filtre, tu diminues de moitié (50%) le nombre de combinaison/opérations (116 mio au lieu de 234 mio).

Mon idée c'est de se dire qu'une colonne ayant bcp de 1 "inclus" probablement des colonnes qui en ont peu, donc on peut les supprimer sans soucis.

Je procèderais ainsi:
1) faire la somme de chacune des 125 colonnes pour identifier celles ayant beaucoup et peu de 1
2) prendre la colonne qui possède le plus de 1 (colonne x) et celle ayant le moins de 1 (colonne y)
3) faire une boucle sur les 5000 lignes et dès que colonne y > colonne x, cela signifie qu'on doit garder y, évidemment le but c'est d'arriver à la ligne 5000 sans avoir eu ce cas

Tu peux répéter cela en prenant par exemple les 5 colonnes ayant le plus de 1 et les 20 colonnes en ayant le moins et augmenter/diminuer ces valeurs selon le succès de la méthode. La réussite dépend beaucoup des données... si t'as rempli ton tableau avec des 0/1 aléatoire, ce sera certainement inutile.

Sinon j'ai testé ton code (enfin en limitant les bornes max des For) et j'ai pu trouver deux améliorations:
1) convertir ton array de variant en booléen (15 sec au lieu de 18 sec lors de mes tests)
2) supprimer l'appel à application.sum, très gourmand. Je suis passé de 15 sec à 3 sec pour le même test.

Voilà le code modifié:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
Option Explicit

Sub tableauV2()
Dim T1 As Variant, A%
Dim a1%, a2%, a3%, a4%, a5%
Dim b1%, b2%, b3%, b4%, b5%
Dim c1%, c2%, c3%, c4%, c5%
Dim l As Integer, c As Integer
Dim x%, y%
Dim tStart As Double, tEnd As Double
 
tStart = Time
Application.ScreenUpdating = False
Application.Calculation = xlManual
 
With Worksheets("A") 'feuille active
'Remplir le tableau des données de la plage
T1 = .Range("A1:DX5000")

Dim T(1 To 5000, 1 To 128) As Boolean
    For c = 1 To 128
        For l = 1 To 5000
            T(l, c) = CBool(T1(l, c))
        Next l
    Next c
End With


'boucle sur colonne
For a1 = 1 To 1
    For a2 = a1 + 1 To 2
        For a3 = a2 + 1 To 123
            For a4 = a3 + 1 To 124
                For a5 = a4 + 1 To 125
'Boucle sur le tableau
                  x = 0
                    For A = LBound(T, 1) To UBound(T, 1)
                        x = x + (T(A, a1) Or T(A, a2) Or T(A, a3) Or T(A, a4) Or T(A, a5))
                    Next A
 
        If x = y Then
        c1 = a1
        c2 = a2
        c3 = a3
        c4 = a4
        c5 = a5
        End If
 
        If x < y Then 'test pour garder la combinaison la plus élevée
        y = x
        b1 = a1
        b2 = a2
        b3 = a3
        b4 = a4
        b5 = a5
        End If
 
 
'MsgBox x
                Next
            Next
        Next
    Next
Next
 
'copie du tableau
With Worksheets("A")
'.Range("A4").Resize(UBound(T, 1), UBound(T, 2)) = T
.Range("EA1") = y
.Range("EB1") = b1
.Range("EC1") = b2
.Range("ED1") = b3
.Range("EE1") = b4
.Range("EF1") = b5
.Range("EB2") = c1
.Range("EC2") = c2
.Range("ED2") = c3
.Range("EE2") = c4
.Range("EF2") = c5
'.Range("EG1") = x
End With
 
tEnd = Time
Range("EJ1") = Format(tEnd - tStart, "HH:MM:SS")
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
ActiveWorkbook.Save
End Sub

A noter que TRUE = -1 (dans mes "or") donc la variable x est négative, d'où le x < y et plus x > y pour tester le max.

Voilà, ça restera très long... mais probablement 4-5 fois mois si je me suis pas planté dans mes tests...

[pascal4782]

Bonjour yisashi,

Je viens de découvrir ton post ce jour. Suite a l'épisode Vent et pluie, weekend sans téléphone et internet.
Je te remercie pour ta proposition, je teste ce soir et te tiens au courant, mais globalement je vais devoir suivre tes conseils et supprimer quelques colonnes pour rester dans des temps de traitements raisonnables.

Encore un grand merci

[pascal4782]

Bonsoir,

Je viens de terminer les tests. C'est formidable le temps de traitement est divisé par 20 en passant par le booleen.
Je vais donc suivre tes conseils et supprimer quelques lignes pour garder un temps de traitement raisonnable, cependant il me reste quelques tests a réaliser avant.
Encore un grand merci yizashi

Cordialement

[yizashi]

Heureux que ça fonctionne. Sinon il doit être possible d'optimiser l'utilisation du processeur par Excel mais j'ignore comment, certains sites disent que dans la liste des tâches Windows on peut donner une priorité élevée à Excel, mais ça n'a rien changé chez moi...

Bonne continuation!