Problème de lenteur

[JORDAN]

Bonjour à toutes et à tous

J'ai un projet qui contient des listes de valdation, des formules et un USF et une vingtaine de pages.
Lorque je lance mon USF, il vient écrire des données dans une feuille de calcul, ces données sont récuprées pour les listes de validation, donc qui dit modification de cellules dit recalcul des formules.
j'ai 12 formules identiques à celle-ci

=SOMMEPROD((INDIRECT("'"&$A$2&"'!$B$3:$B$65536";VRAI)= $B$2)*(MOIS(INDIRECT("'"&$A$2&"'!$A$3:$A$65536";VRAI ))=MOIS(A4))*(ANNEE(INDIRECT("'"&$A$2&"'!$A$3:$A$65536 ";VRAI))=ANNEE(A4));INDIRECT("'"&$A$2&"'!$D$3:$D$65536 ";VRAI))

Actuellement dans les formules, les recherches sont effectués sur 65536 lignes et ce 4 fois par formule, ce qui rend un calcul assez long à chaque validation de l'USF.
Existerait-il un moyen de détecter la dernière cellule pleine et de remplacer 65536 par cette valeur ce qui accélèrerait le calcul des formules, actuellement j'ai environ 12000 lignes de remplies et en augmentation d'une centaine de lignes par jour, saisies via l'USF.
J'ai bien essayé le Calcul sur ordre mais ce n'est pas très pratique, les données sont aussi transcrites en graphiques.
Merci

 

[Tibo]

Re : Problème de lenteur

Bonjour,

Effectivement, un SOMMEPROD sur 65000 lignes peut prendre un peu ..., beaucoup de temps.

Sur les 12000 lignes que tu évoques, y-a-t-il des lignes vides ?

Si non, on peut définir une plage nommée dynamique avec la fonction DECALER.

ex :

Insertion - Nom - Définir - Nom : plage_cellules - Fait référence à :

=DECALER(Feuil1!$A$1;0;0;NBVAL(Feuil1!$A:$A))

le nom plage_cellules définit ainsi la plage de cellules qui va de A1 à la dernière cellule.

Il faut alors utiliser plage_cellules à la place de $B$3:$B$65536

L'exemple ci-dessus ne correspond pas à ton cas, il faut l'adapter.

N'hésite pas à revenir avec un petit bout de ton fichier (anonymisé) si problème persiste.

Cela dit, sur 12000 lignes, ça mettra quand même un certain temps.


@+

 

[Pierrot93]

Re : Problème de lenteur

Bonjour Jordan

essaye peut être ainsi, code à placer dans le module de ton USF :

devant toutes les autres procédures :

Dim s As Long

dans l'événement "Initialize" de l'usf :

With aplication
    s = .Calculation
    .Calculation = xlCalculationManual
End With

lorsque tu le quitte :

Application.Calculation = s

bon après midi
@+

Edition :bonjour Tibo, Jean-Marcel

 

[JORDAN]

Re : Problème de lenteur

Bonjour Tibo, Jean-Marcel, Pierrot

Merci pour vos solutions, je fais quelques tests et vous tiens au courant des résultats
bonne journée

 

[JORDAN]

Re : Problème de lenteur

re,
J'ai oublié de préciser,
non il n y a pas de ligne vide

 

[JORDAN]

Re : Problème de lenteur

Re,

ça y est j'ai enfin eu le temps de tester vos solutions

Pour Tibot et Jean-Marcel :
La lenteur se fait toujours trop ressentir, ça ne va pas, mais je garde sous le coude cette solution.

Pour Pierrot :
ta solution est très convenable, l'USF n'est plus du tout ralentit

Reste juste à faire attention de ne pas cliquer sur les cellules une fois que l'on a quitté l'USF, mais ça me va très bien

Merci à vous tous pour votre aide
bonne fin de journée

 

[JeanMarie]

Re : Problème de lenteur

Bonjour à tous

Jordan, ta base est-elle triée sur un des critères que tu utilises dans SOMMEPROD ?

Si c'est le cas regarde ce lien https://www.excel-downloads.com/threads/remplacer-sommeprod.110406/

@+Jean-Marie

 

[JORDAN]

Re : Problème de lenteur

Bonjour Jean-Marie,
non ma base n'est pas triée
ce sont les listes de validation qui extraient les données voulues
je regarde ton lien
Merci

 

[JORDAN]

Re : Problème de lenteur

Re,
j'avais déjà lu ce lien en faisant des recherches
mais peut on avoir des graphiques évolutifs avec des données extraites par TCD ??

 

[JeanMarie]

Re : Problème de lenteur

Re...

Oui évidement, soit on gère automatiquement un graphe au lieu du TCD, ou on génère un graphe après avoir fait le TCD.

@+Jean-Marie

 

[JORDAN]

Re : Problème de lenteur

re,

Ok merci de l'info, je vais étudier cette solution
Bonne soirée