Discussion :

[shadown08]

Bonjour à tous,

Suite à une modification de formule et après avoir tirer mes formules sur 45000 lignes (contre 15000 initialement), mon fichier est passé de 4 Mo à 36 Mo ! Je ne peux pas réduire le nombre de lignes et il est même possible que je sois obligé de tirer encore ma formule ...

Bien évidemment il est maintenant bien trop lourd et très très lent : l'enregistrement de la dernière version a pris près de 55 minutes !

Je pense que l'idée serait de remplacer ma formule (incrémentée sur 45 000 lignes) par une macro mais je ne vois pas comment faire.

Voilà la formule en question :

M24=SI(I24=0;0;SI(ET(D24="AE";NON(ESTNA(RECHERCHEV(A24&" "&"AS";'DE'!$E$9:$L$45000;4;FAUX))));J24+F24*RECHERCHEV(A24&" "&"AS";'DE'!$E$9:$L$45000;6;FAUX)/RECHERCHEV(A24&" "&"AS";'DE'!$E$9:$L$45000;4;FAUX);SI(A24="";"";SI(OU(D24="S1";D24="S2";D24="S3";D24="S4");J24+F24*J24/H24;SI(OU(D24="V";D24="";D24="AE");SI(C24="P1";(J24+F24);J24);(J24+F24*J24/H24)))))).

Si quelqu'un a une idée ?

Merci.

[Menhir]

http://excel.developpez.com/cours/?page=prog#prog

[shadown08]

http://excel.developpez.com/cours/?page=prog#prog

Bonjour,

Merci pour le lien vers les cours mais ma connaissance de VBA ne me permet pas de mettre une telle formule sous forme de programme :-(
Je n'arrive pour le moment qu'à faire de petit programme assez simple (duplication d'onglet, récupération de résultats, ...)

Et là vraiment je ne vois pas par où commencer ...

[Menhir]

ma connaissance de VBA ne me permet pas de mettre une telle formule sous forme de programme :-(

C'est bien pour ça que je t'ai fourni des éléments qui permettent d'améliorer ta connaissance du sujet.

Désolé mais, personnellement, décortiquer une formule de cette taille sans connaitre le contexte ni même savoir ce qu'elle est sensée faire, ça ne me tente pas vraiment.

Analyse ta formule.
Fais un algorithme pour le traduire en éléments de base.
Transforme cet algorithme en code.

[shadown08]

Je vas voir pour traduire ma formule en algorithme.

Néanmoins j'ai vu sur internet que l'on peut traduire les formule excel directement sous VBA. Bien spur les exemple que j'ai vu sont simples.
Pourquoi n'est-il pas possible de faire la même chose avec ma formule ? J'ai bien sûr essayé avec l'enregistrement de macro mais ça ne fonctionne pas "Erreur de compilation - Erreur de syntaxe"

Je vais essayer de faire un algorithme détaillé mais j'aurai aimé comprendre pourquoi cette méthode n'est pas applicable ?

Merci.

For i = 1 To 44990
Range("M10").Offset(i, 0).Select


ActiveCell.FormulaR1C1 = "IF(RC[-4]=0,0,IF(AND(RC[-9]=""AE"",NOT(ISNA(VLOOKUP(RC[-12]&"" ""&""AS"",'DE'!R9C5:R45000C8,4,FALSE)))),RC[-3]+RC[-7]*VLOOKUP(RC[-12]&"" ""&""AS"",'DE'!R9C5:R45000C12,6,FALSE)/VLOOKUP(RC[-12]&"" ""&""AS"",'DE'!R9C5:R45000C8,4,FALSE),IF(RC[-12]="""","""",IF(OR(RC[-9]=""S1"",RC[-9]=""S2""& _
"]+RC[-7]*RC[-3]/RC[-5],IF(OR(RC[-9]=""V"",RC[-9]=""""),IF(RC[-10]=""P1"",(RC[-3]+RC[-7])*(1+RC[-6])^((RC[-12]-R[-1]C[-12])/(DATE(YEAR(RC[-12]),12,31)-DATE(YEAR(RC[-12])-1,12,31))),RC[-3]*(1+RC[-6])^((RC[-12]-R[-1]C[-12])/(DATE(YEAR(RC[-12]),12,31)-DATE(YEAR(RC[-12])-1,12,31)))),(RC[-3]+RC[-7]*RC[-3]/RC[-5])*(1+RC[-6])^((RC[-12]-R[-1]C[-12])/(DATE(YEAR"& _
",12,31)-DATE(YEAR(RC[-12])-1,12,31))))))))"


Next

End Sub

[Menhir]

Néanmoins j'ai vu sur internet que l'on peut traduire les formule excel directement sous VBA.

Je n'ai jamais entendu parler de ça.
A moins que tu parles de la propriété Formula (ou ses cousines) de l'objet Range. Mais ça permet simplement de mettre une formule dans une cellule. Ca ne fait rien de plus que ce que fait ta formule actuelle.
En tout cas, ça n'évite pas à Excel de faire les calculs.

ActiveCell.FormulaR1C1 =

Cette propriété va juste placer dans ta formule dans une cellule.
D'ailleurs, si tu remplace FormulaR1C1 par FormulaLocal, tu peux directement faire un copier/coller de ta formule à la suite (sans avoir à la transcrire en fonctions anglaises et références LC).

Mais; comme je le disais, ça ne règlera pas ton problème.
Ce n'est pas la macro qui va faire les calculs. Elle se contente de placer la formule dans les cellules. Rien de plus que ce que tu pourrais faire avec un copier/coller à la main. Les calculs restent dans tes cellules.

Donc, en terme de temps de calcul et de taille de fichier, ça ne règlera rien.

[Namica]

...après avoir tirer mes formules sur 45000 lignes (contre 15000 initialement)...

Outre les remarques de Menhir, il faut se demander si Excel est l'outil adéquat, vu le nombre de lignes de données...
Commence par exposer un échantillon de tes données et le traitement que tu veux exécuter, plutôt qu'une formule imbuvable que personne, sans autres explications, n'a envie de décortiquer.
Peut-être qu'une petite application de base de données serait plus indiquée.

[Pierre Fauconnier]

Salut.

Des RECHERCHEV séquentiels (quatrième paramètre à FAUX) sur 45000 lignes, cela impose une moyenne de 22500,5 recherches pour UN RECHERCHEV... Tu ne dis pas sur combien de lignes tu tires ta formule, mais tu peux faire la multiplication par toi-même.

Pour accélérer le processus, pour autant que tu sois certaine que la donnée recherchée par RECHERCHEV est présente dans la première colonne du tableau de recherche, tu pourrais trier celui-ci par ordre croissant sur la première colonne et supprimer le ;FAUX de tes RECHERCHEV. Le nombre maximum de recherches passera alors à 16 par RECHERCHEV, ce qui sera un gain appréciable.

Cela étant, il devrait y avoir moyen d'améliorer la conception de ton classeur, mais il serait alors utile que tu le postes.

En fonction de la finalité, tu pourrais effectivement t'orienter vers Access pour la gestion des données (et pour la préparation éventuelle de données à traiter dans Excel).

Sinon, tu pourrais aussi t'orienter vers du VBA, notamment en utilisant des tableaux, avec des algorithmes qui peuvent s'avérer bien plus rapides que des formules Excel, mais certainement pas en saisissant des formules par VBA (gain nul) ou en utilisant des trucs style EVALUATE ou WORSHEETFUNCTIONS qui ne feront que transmettre le calcul à Excel.