Variables tableaux

[pascal82]

Bonjour à tous et toutes
J’ai besoin de votre compétence pour tenter de résoudre mon problème.
Je suis débutant en VBA et j’ai construis un petit programme certes avec quelques maladresses, mais il fonctionne. Cependant son temps de traitement est très long c’est pourquoi j’essaye en vain de passer en variables tableaux la partie la plus gourmande en temps. J’ai lu quelques tutos sur le sujet mais je n’ai pas trouvé de cas semblables que je pourrais adapter. J’ai lu une grande quantité de post et rien ne ressemble à mes besoins. Si quelqu’un pouvait regarder mon petit fichier je serais génial.
Un grand merci par avance.

 

[pascal82]

Re : Variables tableaux

Re,

Désolé David84, j’avais mal compris le message. J’ai donc intégré quelques résultats.
Lors du premier test sur la colonne A, c’est normal qu’il n’y ait rien en colonne B, il faut attendre que la formule soit appliquée avec le i diviseur qui a donné le résultat max pour coller la colonne C20 :C79 vers B20 :B79
A) La colonne eAW20:AW79 est collée en A20:A79
1) résultat du test suite a application de la formule B20+SIN(A20/$G$20) jusqu'en C79 : 0,0993220610601349. Ce résultat est copié en F20
2) résultat du test suite a application de la formule B20+SIN(A20/$G$21) jusqu'en C79 : 0,00334070774141697. Ce résultat est copié en F21
3) le test est réalisé jusqu’a $G$519
4) le max est de F20:F419 est 9,84 , la diviseur qui a permis ce resultat est 0,385, est copié en G1
5) application de la formule de C20 à C79 aavec B20+SIN(A20/$G$1)
6) La colonne C20:C79 est copiée en B20:B79
B) La colonne AX20 :AX79 est collée en A:A79
Cordialement

 

[david84]

Re : Variables tableaux

Re Pascal
Dernière relance : je ne te demande pas de m'expliquer les différentes étapes mais de le faire sur le fichier.
Etape 1 en colonne ? : telle action
Etape 2 en colonne ? : telle action
etc.
une fois que je comprendrai les différentes étapes et leur enchaînement, je pense être capable de comprendre les formules.
Je verrai alors si je peux t'aider.
A+

 

[Paritec]

Re : Variables tableaux

Bonjour David🙂, Pascal82, le forum
alors bon j'ai fait une version .xls pour notre ami Roger qui ne peut ouvrir les 2007, voilà pourquoi il ne s'était pas manifesté.
Maintenant comme dans le fichier initial il y avait 297 colonnes (va savoir pourquoi)
quand on sait que Pascal82 a commencé sa ligne 20 à la colonne 49 ????
Bref j'ai essayé de faire pour que Roger puisse prendre part à la fête!!!
bonne journée à tous
Papou🙂

 

[pascal82]

Re : Variables tableaux

Bonjour à tous,

Désolé pour hier soir, mais je n’avais plus le temps, travail oblige et ce matin un peu de sommeil.
J’ai tenté de répondre à David84, je joins je fichier avec les explications.
Je vous remercie pour votre patience et compréhension.

Cordialement

 

[david84]

Re : Variables tableaux

Re
J'ai regardé ton dernier fichier et j'ai 2 remarques :
- quand tu écris

Etape 2: Je réalise le test sur les colonnes C20:C79 =>58 * ((30 * (Moyx - Moyz) ^ 2) + (30 * (Moyy - Moyz) ^ 2)) / (DSx + DSy) et je colle le résultat du test en F20

, je ne vois pas dans ton fichier la formule Excel correspondant à cette étape. Où est-elle ? Quand je te dis que je pense pouvoir comprendre tes formules, je parle des formules Excel, pas des formules mathématiques et dans ton tableau, je ne vois pas à quoi correspond cette formule.
- si je comprends bien, tu as un code qui fonctionne mais qui te prend plusieurs heures.
L'intérêt de passer par des variables tableau est notamment d'éviter de travailler dans la feuille de calcul pour justement gagner du temps. Or le problème, c'est que tes explications ne prennent pas en compte cette notion : tu te crée des colonnes intermédiaires, tu copie telle colonne à tel endroit puis tu l'effaces et tu en recopies une autre,etc.
Ci-joint un code simple traitant jusqu'à l'étape 1 de la boucle 2 et donnant les même résultats que ceux de la colonne C de la feuille C avec toutes les étapes que tu utilises pour en arriver au même point (lancer la macro en feuille "test").

Sub test()
Dim tabl
tabl = [TRANSPOSE(SIN((AE20:AE79+AF20:AF79)/G20))]
Sheets("Test").[C20:C79] = Application.Transpose(tabl)
End Sub

Regarde les données placées dans cette feuille test : il ne reste que tes 2 plages et pourtant tu obtiens ce même résultat.
Après comme je ne vois pas ce que je peux faire de

C20:C79 =>58 * ((30 * (Moyx - Moyz) ^ 2) + (30 * (Moyy - Moyz) ^ 2)) / (DSx + DSy)

, je n'ai pas plus avancé.
Mais bon cela pourra peut-être te dépanner.
Bon courage.
A+

 

[pascal82]

Re : Variables tableaux

Bonjour David,

Un grand merci pour ce que tu as réalisé et surtout pour ton immense patience.
Je comprends que ma demande ne soit pas suffisamment clair, mais j’arrête ce post même s’il n’est pas résolu. Je me rends compte que j’ai un grand besoin de bosser le VBA avant de poster.

Cordialement

 

[Paritec]

Re : Variables tableaux

Re Pascal 82,
non pas que tu bosses le vba , seulement que tu expliques simplement ce qu'il faut faire et clairement, car comme t'a dit David, on peut tout faire mais encore faut-il comprendre ta demande pour le faire sans passer par tes multiples copiés collés. et tes 297 colonnes car je pense que là aussi on pourrait avoir une colonne comme ta colonne g je crois
Si tu as la patience de partir de ton tableau et d'expliquer pour 1 boucle complète et après on te fais cela avec des tableaux et là tu verras la différence de temps c'est impressionnant.
a+
papou🙂

 

[ROGER2327]

Re : Variables tableaux

Bonjour à tous

Paritec (que je salue au passage) a attiré mon attention sur cette discussion. Ne disposant pas de version d'Excel ultérieure à Excel2003, je n'ai consulté que la version .xls fournie par Paritec. Je n'ai pas plus que les autres compris l'ensemble du projet, mais j'ai débarrassé le code des scories les plus évidentes et j'obtiens ceci :

Sub TestFeuilleCOpt()
Dim A&, decal&, yM!, RyM&
Dim Deb! '***
   Deb = Timer '***
   With Application: .ScreenUpdating = 0: .Calculation = -4135: End With
  With Sheets("C")
    .Range("A20:F79").ClearContents
    .Range("H1").FormulaArray = "=AVERAGE(R[19]C[-5]:R[48]C[-5]-R[49]C[-5]:R[78]C[-5])^2/(DEVSQ(R[19]C[-5]:R[48]C[-5])+DEVSQ(R[49]C[-5]:R[78]C[-5]))"
    For decal = 1 To 100
      yM = 0
      .Range("A20:A79").Formula = .Range("AV20").Offset(0, decal).Formula
      For A = 20 To 519
        .Range("C20:C79").FormulaR1C1 = "=RC[-1]+SIN(RC[-2]/R" & A & "C7)"
        .Range("H1").Calculate
        If .Range("H1").Value > yM Then yM = .Range("H1").Value: RyM = A
      Next A
      Union(.Range("G1"), .Cells(decal, 9)).Value = (RyM - 19) / 1000
      .Range("C20:C79").FormulaR1C1 = "=RC[-1]+SIN(RC[-2]/R1C7)"
      .Range("B20:B79").Value = .Range("C20:C79").Value
    Next decal
  End With
  With Application: .Calculation = -4105: .ScreenUpdating = 1: End With
  ActiveWorkbook.Save
  MsgBox "J'ai travaillé " & Timer - Deb & " secondes." '***
End Sub

Dans le classeur .xls de Paritec, l'exécution de cette procédure est de l'ordre de quatorze fois plus rapide que la procédure originale (environ 17 s contre 3 mn 55 s sur ma machine).

Je n'ai pas cherché à construire une solution à base de tableaux faute, d'une part, d'avoir une vue claire du problème, d'autre part, d'avoir suffisamment de temps.

Si cette première proposition retient l'attention de notre ami et s'il veut bien donner une description complète de son projet (entre autre, la logique qui commande les formules de la ligne 20) il sera temps d'envisager d'autres améliorations.​

ROGER2327
#5231

Vendredi 27 Palotin 138 (Saint Foin, coryphée, SQ)
27 Floréal An CCXIX
2011-W20-1T22:56:55Z

 

[pascal82]

Re : Variables tableaux

Bonjour Roger,

Je suis confus, je viens de retourner ce weekend sur le post et j’ai découvert ta réponse. Je manque à touts mes devoirs de politesse et te présente mes excuses.
J’ai testé ta proposition, je suis déjà impressionné par la rapidité de ton code, en effet je passe de 43000 s à 17000 s (le fichier de travail fait 8000 lignes et 700 colonnes). Comme quoi la programmation est un véritable métier.
J’en profite également pour remercier Paritec et David.

Cordialement

Petite explication pour répondre à Roger:
Etape1 : sélection de la 1° colonne (AW20 :AW79), sur les données de la colonne j’applique la formule B20+sin(AW20/H), cette formule est glissée jusqu’en 79 : H étant un nombre allant de 0.001 à 0.5 avec un pas de 0.001(500 valeurs ).
Etape 2 : Pour les 500 valeurs de H j’applique le test =>58 * ((30 * (Moyx - Moyz) ^ 2) + (30 * (Moyy - Moyz) ^ 2)) / (DSx + DSy).
Attendu 0,0993220610601349 avec H=0.001, 0,00334070774141697 avec H=0.002
Etape3 : Le résultat du test est gardé en mémoire et après les 500 tests, je relance la formule B20+sin(AW20/H) toujours sur la première colonne avec pour valeur de H celle qui a donné le résultat max du test. Cette valeur de H sera copiée en H1
Attendu : 9,84014284555573 pour H=0.385
Etape 4 : Le résultat de la formule est copié en B20 :B79 dans le but d’incrémenter le test pour les prochaines colonnes
Retour a l’étape 1 avec passage sur la colonne AX20 :AW79