Le traitement de mes formules ds ma matrice prend 30 min...Comment l'accélérer ? VB ?

[benni.mvr]

Bonjour tout le Forum,

Avec l'aide de Cisco dont je remercie encore, j'ai pu développer ma matrice jointe allégée .

Ma matrice possède une feuille "BRUT" et une autre "Résultat"

Tout fonctionne très bien...

Cependant, dans "BRUT", je renseigne environs 3000 lignes sur 150 colonnes environs.

Alors forcément, une fois les lignes descendus dans "Résultat", j'en ai pour 30 minutes d'attentes avec mon processeur qui tourne à 100%

Je pense que le traitement sera 100 foix plus rapide avec VB...

Je me demandais donc comment migrer cette matrice en un scrip VB, que je pourrais rattacher à un bouton ?

Est ce possible ?

Merci d'avance de votre aide précieuse.

 

[Misange]

Re : Le traitement de mes formules ds ma matrice prend 30 min...Comment l'accélérer ?

Bonjour

j'ai juste très vite regardé mais est-ce que c'est indispensable de tout recalculer à chaque fois ? ou est-ce qu'une fois mises sur ta feuille résultats certaines valeurs ne bougeront plus ? Si c'est le cas, sur ces valeurs là fais un copier/coller spécial valeur sur toutes ces lignes. Ca peut tout à fait suffir et tu t'économises alors bien tu travail.

Le fait de passer en VBA écrira tes valeurs en dur dans la feuille (sans formule) mais cependant le calcul devra quand même être fait pour chaque ligne de ton tableau.... Dont le problème est un peu le même que ci-dessus. Si tu peux en VBA dire qu'il ne faut traiter les données qu'à partir de la ligne X et ne pas retraiter les lignes précédentes ça vaut la peine. Sinon ce n'est absolument pas garanti que tu sois gagnant.

Si tu dois impérativement tout recalculer à chaque fois que tu entres une valeur dans la feuille brut, alors pour accélérer le boulot, je te conseille vivement de passer par des arrays dans lesquels tu stockes les données à traiter et tu fais les calculs en mémoire vive d'excel puis tu réinjectes en une seule fois un array contenant les résultats dans la feuille ad hoc.

 

[benni.mvr]

Re : Le traitement de mes formules ds ma matrice prend 30 min...Comment l'accélérer ?

Bonjour Misange,

Suite test, et plus précisément, c'est ma formule Matricielle de Résultat!B qui prend toute la ressource du processeur.

Les valeurs que je renseigne dans BRUT, sont un copier/coller de mon extraction SQL à partir de mon serveur via Microsoft Query.

Le collage dans BRUT est instantané.

Mais quand je descend la formule de Résultat!B il y en à pour 30min de traitement &2000.

Alors, j'ai fais comme tu ma conseillé:

Copier/coller de "Brut!AS" en "Résultat!B" puis suppression des doublons de "Résultat!B" et là, miracle !!!

-> 10 Secondes de traitement !!

J'aurais juste à répéter cette opération pour mes autres extractions mais c'est mieux que d'attendre 1H.

Cette solution répond très bien à mes attentes.

Grand merci à toi Misange, pour cette idée.

 

[Misange]

Re : Le traitement de mes formules ds ma matrice prend 30 min...Comment l'accélérer ?

Tant mieux si cela te va mais ce n'est pas exactement ce que je t'avais conseillé (relis)
En tous cas parfois mieux vaut une solution "manuelle" qui prend 10 secondes mais qu'on comprend et qu'on maitrise et donc qu'on peut débuger que de passer des heures à développer une usine à gaz et autant à comprendre pourquoi ça ne marche pas

[edit] quand même je ne comprends pas trop : pourquoi tu as une formule aussi compliquée dans ta feuille résultat si un simple copié collé de la feuille brut fait la même chose ???

 

[benni.mvr]

Re : Le traitement de mes formules ds ma matrice prend 30 min...Comment l'accélérer ?

Bonjour tout le forum,

Il y a peut etre plus simple comme formule...

Ce que je souhaitais c'est simplement ramener les valeurs de BRUT!AS en Résultat!B mais sans doublons et sans "blanc"

Dans ma colonne BRUT!AS on y trouve des valeurs numérique et alpha et pour certaines en doublons, exmple :

AAAAA
00000
00000
00000
A0000
A0000
A-000
A,000
A 00A
A 00A

Nota: je n'ai pas encore trouvé comment virer les blanc mais c'est un détail.

Actuellement, je les supprime en manuel avec Ctrl+F.

Si y a plus simple en formule, je suis preneur et je pense que le traitement devrait en être amélioré.

Bien à toi,

 

[Misange]

Re : Le traitement de mes formules ds ma matrice prend 30 min...Comment l'accélérer ?

Bonjour

On peut facilement par formule extraire de la colonne AS la liste des éléments uniques.
En nommant colAS de façon dynamique pour que le nom s'adapte en fonction du nombre d'éléments extraits ce jour là.
=DECALER(BRUT!$AS$1;1;0;NBVAL(BRUT!$AS:$AS)-1)

en mettant alors en B6 la formule suivante
=INDEX(colAS;EQUIV(0;NB.SI($B$5:B5;colAS);0))
que tu valides de façon matricielle PUIS que tu tires vers le bas. Cette formule est beaucoup plus simple que celle que tu avais utilisée


Tu as de toutes façons absolument intérêt à nommer comme je l'ai fait toutes tes colonnes de la feuille brut de façon à ne pas travailler sur un nombre de lignes trop petit ou trop grand quand tu fais tes calculs. C'est particulièrement important quand il y a des formules sommeprod qui sont terriblement gourmandes en ressources.

fais un essai et reviens mais c'est sur que des formules matricielles sur un grand nombre de lignes réclament un PC musclé.

 

[MichD]

Re : Le traitement de mes formules ds ma matrice prend 30 min...Comment l'accélérer ?

Bonjour,

Si tes formules font référence à des plages de cellules de la feuille où elles sont écrites, chaque fois que tu modifies le contenu d'une cellule, seules les formules affectées pourraient être calculées. Pour ce faire, tu passes en mode de calcul manuel et utilises un bout de code comme suit dans le module feuille de la feuille :


'---------------------------
Private Sub Worksheet_Activate()
Application.Calculation = xlCalculationManual
End Sub
'---------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Target.DirectDependents.Calculate
End Sub
'---------------------------
Private Sub Worksheet_Deactivate()
Application.Calculation = xlCalculationAutomatic
End Sub
'---------------------------