XL 2013 Conversion sous VBA

momo

XLDnaute Occasionnel
Bonjour à tous

Je voudrais solliciter votre aide.

J'ai un fichier d'analyse que j'ai automatisé à l'aide de formules. Ma préoccupation est que lorsque mes données portent sur plus de 20 mille lignes, le temps de traitement du fichier s'allonge considérablement pouvant aller à plus de 15 min et parfois plus. Et ceci bloque de faire autre chose sur Excel

Je voudrais donc que vous m'aidiez à mettre ce fichier sous VBA afin de gagner du temps dans le traitement.

Je joins un exemple du fichier tel qu'il se présente avec les formules de traitement.

En vous remerciant par avance
 

Pièces jointes

  • Fichier Modèl.xlsx
    19.3 KB · Affichages: 30
C

Compte Supprimé 979

Guest
Bonjour Momo,

Sans faire quoique ce soit dans ton fichier, vu les innombrables formules :eek:

Je ne sais pas ce que tu penses pouvoir faire sous VBA !?
tu peux éventuellement parcourir tes lignes, inscrire la formule, puis inscrire la valeur retournée

Pas certain que ça te fasse gagner du temps

@+
 

momo

XLDnaute Occasionnel
Bonjour Momo,

Sans faire quoique ce soit dans ton fichier, vu les innombrables formules :eek:

Je ne sais pas ce que tu penses pouvoir faire sous VBA !?
tu peux éventuellement parcourir tes lignes, inscrire la formule, puis inscrire la valeur retournée

Pas certain que ça te fasse gagner du temps

@+
J’avais pensé que avec VBA la résolution des formules se fassent plus vite
 
C

Compte Supprimé 979

Guest
Re,

Je ne suis pas certain, en passant peut-être pas un EVALUATE() mais reste à voir

Pour que ce soit très rapide, il faut utiliser la mémoire vive et donc passer par des variables tableaux, mais ce n'est pas trop mon domaine, désolé ;)
 

momo

XLDnaute Occasionnel
J’avais pensé que avec VBA la résolution des formules se fassent plus vite
Re,

Je ne suis pas certain, en passant peut-être pas un EVALUATE() mais reste à voir

Pour que ce soit très rapide, il faut utiliser la mémoire vive et donc passer par des variables tableaux, mais ce n'est pas trop mon domaine, désolé ;)

Re,

Je vois. Merci Bruno
 

eriiic

XLDnaute Barbatruc
Bonjour à tous,

Dans un 1er temps certaines formules peuvent être allégées en s'appuyant sur un TCD.
La macro se limitant à l'actualisation du TCD.
Là je l'ai mise à l'ouverture du fichier, et à l'activation de la feuille TCD. Tu peux compléter/remplacer par l'action d'un bouton.

Ce sont les formules du type :
=SOMME.SI.ENS(Source!$C:$C;Source!$A:$A;$A5;Source!$D:$D;S$2) en A!S:S
et
=SOMME(SOMME.SI.ENS(Source!C:C;Source!A:A;A5;Source!D:D;{"DA";"HY";"CE"})) en C!S:S
qui deviennent :
VB:
=LIREDONNEESTABCROISDYNAMIQUE("Montant";TCD!$A3;"Clé Commune";$A3;"Type";S$2)
et
=SOMMEPROD(LIREDONNEESTABCROISDYNAMIQUE("Montant";TCD!$A$3;"Clé Commune";$A3;"Type";{"DA";"HY";"CE"}))
Le sommeprod() étant sur de petites matrices, il n'est pas pénalisant.
Je t'ai mis ces formules en fond vert dans A et C. Elles peuvent tirer à droite et vers le bas.
A toi de tester le gain sur un gros fichier en ayant modifié toutes ces formules.

L'autre amélioration éventuelle serait de ne garder les formules qu'en 1ère ligne (collé valeur pour les suivantes), à recopier-coller valeur à chaque actualisation.
Ca te ferait gagner du temps à l'ouverture si besoin, pas vraiment sur les actualisations les calculs étant à faire de toute façon.
eric
 

Pièces jointes

  • Fichier Modèl.xlsm
    33.9 KB · Affichages: 4

cp4

XLDnaute Barbatruc
B
Bonjour à tous

Je voudrais solliciter votre aide.

J'ai un fichier d'analyse que j'ai automatisé à l'aide de formules. Ma préoccupation est que lorsque mes données portent sur plus de 20 mille lignes, le temps de traitement du fichier s'allonge considérablement pouvant aller à plus de 15 min et parfois plus. Et ceci bloque de faire autre chose sur Excel

Je voudrais donc que vous m'aidiez à mettre ce fichier sous VBA afin de gagner du temps dans le traitement.

Je joins un exemple du fichier tel qu'il se présente avec les formules de traitement.

En vous remerciant par avance
Bonjour Momo:), BrunoM45;), Patrice33740 ;), eriiiic ;),

@momo : Personnellement, je trouve que tu n'as expliqué clairement les tenants et les aboutissants de ton problème (mis à part la lenteur de recalcul vu le nombre de formules).

Je suppose que tu exploites la feuille 'source'. Mais tu ne nous donnes aucune explication à propos des feuilles (A,B,C et D). Il y a des formules certes mais aucune indication des feuilles dans la source.
Merci d'être plus explicite.

Bon dimanche.
 

momo

XLDnaute Occasionnel
Bonjour à tous,

Dans un 1er temps certaines formules peuvent être allégées en s'appuyant sur un TCD.
La macro se limitant à l'actualisation du TCD.
Là je l'ai mise à l'ouverture du fichier, et à l'activation de la feuille TCD. Tu peux compléter/remplacer par l'action d'un bouton.

Ce sont les formules du type :
=SOMME.SI.ENS(Source!$C:$C;Source!$A:$A;$A5;Source!$D:$D;S$2) en A!S:S
et
=SOMME(SOMME.SI.ENS(Source!C:C;Source!A:A;A5;Source!D:D;{"DA";"HY";"CE"})) en C!S:S
qui deviennent :
VB:
=LIREDONNEESTABCROISDYNAMIQUE("Montant";TCD!$A3;"Clé Commune";$A3;"Type";S$2)
et
=SOMMEPROD(LIREDONNEESTABCROISDYNAMIQUE("Montant";TCD!$A$3;"Clé Commune";$A3;"Type";{"DA";"HY";"CE"}))
Le sommeprod() étant sur de petites matrices, il n'est pas pénalisant.
Je t'ai mis ces formules en fond vert dans A et C. Elles peuvent tirer à droite et vers le bas.
A toi de tester le gain sur un gros fichier en ayant modifié toutes ces formules.

L'autre amélioration éventuelle serait de ne garder les formules qu'en 1ère ligne (collé valeur pour les suivantes), à recopier-coller valeur à chaque actualisation.
Ca te ferait gagner du temps à l'ouverture si besoin, pas vraiment sur les actualisations les calculs étant à faire de toute façon.
eric
Merciiii Eric
Je fais un essai et vous revient
 

momo

XLDnaute Occasionnel
B

Bonjour Momo:), BrunoM45;), Patrice33740 ;), eriiiic ;),

@momo : Personnellement, je trouve que tu n'as expliqué clairement les tenants et les aboutissants de ton problème (mis à part la lenteur de recalcul vu le nombre de formules).

Je suppose que tu exploites la feuille 'source'. Mais tu ne nous donnes aucune explication à propos des feuilles (A,B,C et D). Il y a des formules certes mais aucune indication des feuilles dans la source.
Merci d'être plus explicite.

Bon dimanche.
Bonjour Cp4
Merci pour ton retour ..
Je vais dans le post suivant détailler un peu plus
Momo
 

momo

XLDnaute Occasionnel
Personnellement, je trouve que tu n'as expliqué clairement les tenants et les aboutissants de ton problème (mis à part la lenteur de recalcul vu le nombre de formules).
Voila un peu pour les détails

C'est un fichier d'analyse comportant 4 onglets quasi indépendants.
Le fichier source sert a remplir les colonnes nommés ET à JT
En fait l'analyse se fait sous des conditions que j'ai intégré dans chaque cellule ou on a une formule.
Le challenge c'est de pouvoir optimiser ces formules vu effectivement qu'elles sont nombreuses
 

cp4

XLDnaute Barbatruc
Voila un peu pour les détails

C'est un fichier d'analyse comportant 4 onglets quasi indépendants.
Le fichier source sert a remplir les colonnes nommés ET à JT
En fait l'analyse se fait sous des conditions que j'ai intégré dans chaque cellule ou on a une formule.
Le challenge c'est de pouvoir optimiser ces formules vu effectivement qu'elles sont nombreuses
Pas clair du tout.
Mis à part les formules. Ensuite, récupérer les données à partir de la feuille source (pour la feuille C, il n'y a pas d’entêtes de colonne ET à JT).
Tu n'expliques pas comment tu obtiens le début des tableaux
momo1.JPG
 

momo

XLDnaute Occasionnel
Pas clair du tout.
Mis à part les formules. Ensuite, récupérer les données à partir de la feuille source (pour la feuille C, il n'y a pas d’entêtes de colonne ET à JT).
Tu n'expliques pas comment tu obtiens le début des tableauxRegarde la pièce jointe 1082715
Bonjour Cp4

JE te remercie pour la suite que tu donne à ma préoccupation.

En fait partout ou il n'y a pas de formule, ce sont des données qui doivent être ajoutées manuellement.
Dans la fichier originel, les cases sans formules sont remplis sur la base d'une extraction faite d'une base de donnée que j'ajoute dans ces colonnes du fichier pour compléter ainsi l'analyse.

Donc la clé commune et les différents montants issus de l'onglet source sont ajoutés manuellement
 

Discussions similaires

Statistiques des forums

Discussions
311 725
Messages
2 081 943
Membres
101 849
dernier inscrit
florentMIG