Fichier de 50 MO - calcul et enregistrement extrêmement lent

[randle88]

Bonsoir à tous,

Je viens pour une petite demande concernant un fichier excel d'environ 50 MO.

Ce fichier comporte :

-des exportations d'un ERP (type csv) environ 150 000 ligne pour la plus grosse BDD;
-des formules (rechercheV, Sommeprod, Somme.Si) sur là encore plus de 100 000 ligne;
-pas de TCD pour l'instant (ni de graph);
-a noter que c'est un fichier xlsx.

Ensuite je précise aussi que j'ai déjà tenté par mal de manip (suppression lignes en trop, copier coller dans une feuille vierge). Mais rien n'y fait.

Ce fichier fait donc toujours environ 50 mega et (etant donnée que je suis en calcul manuel) lorsque je lance le calcul ou lorsque je souhaite enregistrer, cela prend environ...15 min (pas très pratique et productif).

Avez vous quelques pistes pour solutionner ce problème?

Merci d'avance à tous

 

[Misange]

Re : Fichier de 50 MO - calcul et enregistrement extrêmement lent

Bonjour
des conseils classiques :
Séparer les données d'un côté et l'interprétation des données dans un autre est peut être une première piste.
Limiter ++++ les plages sur lesquelles tu travailles, en particulier en usant et abusant des tableaux (accueil/style/...)
Si nécessaire décortiquer les sommeprod par morceaux dans des colonnes (masquées) pour éviter de travailler en matricielle
Ne jamais formater une ligne ou une colonne entière
tiens nous au courant.

 

[randle88]

Re : Fichier de 50 MO - calcul et enregistrement extrêmement lent

Tout d'abord merci pour ces pistes.

Je vais tenter de les appliquer, concernant l'utilisation de tableau, en quoi cela peut il optimiser le fichier?

Sinon je pensais à autre chose mais je ne sais pas si cela va optimiser ce fichier:

Utiliser des macros à la place des formules ?

Merci d'avance

 

[Misange]

Re : Fichier de 50 MO - calcul et enregistrement extrêmement lent

L'utilisation des tableaux permet de ne travailler que sur les plages réellement utiles. On peut également le faire en définissant les noms avec la fonction decaler mais les tableaux font cela bien plus facilement, et présentent des tas d'autres avantages : Ce lien n'existe plus

Si tes formules doivent être recalculées à chaque changement sur la page, une macro ne sera pas plus rapide qu'une formule, au contraire. En revanche si tu n'as besoin de mettre à jour tes tableaux qu'une fois de temps en temps (par exemple à chaque nouvelle importation de la base de données) alors oui ça peut être le cas.
Mais quand je lis sommeprod sur plus de 100000 lignes, je me dis que de toutes façons, macro ou pas ça ne PEUT pas être rapide, même avec une machine de course, et surtout si tes sommeprod ont plein de conditions.
Le véritable gain dans ce cas vient plus souvent du fait de rajouter des colonnes qui testent chacune des conditions du sommeprod séparément plutôt que de passer par du VBA.
autrement dit : avec une formule du type
=sommeprod((mois(colonneA)="mars")*(colonneB="truc")*(colonneC))
tu crées une colonne (masquée si tu veux) qui teste si la date de la colonne A est en mars, une autre pour tester si colonne B = truc et dans une troisième tu fais le produit des vrais faux obtenus dans ces deux colonnes avec la valeur de la colonne C. Et ce n'est qu'ensuite que tu fais le total de cette dernière colonne pour avoir l'équivalent du sommeprod. Le gain est considérable sur de très grandes bases de données car le calcul matriciel est extrêmement gourmand en ressources.
C'est sur que 50MO, c'est beaucoup pour excel. Ca dépend bien sur de ta machine (pour le coup, mieux vaut avec des bases de ce type travailler avec office en 64bits). Mais ça peut très bien passer si tu n'as que des calculs assez simples à faire. Avec des calculs matriciels c'est plus que normal que ça coince et tu risques fort un jour que ce classeur attrape la vérole... Je te conseille en tous cas d'avoir toujours 1 ou même plutot 2 versions de sauvegarde, dont une sans données histoire de pouvoir repartir à blanc en cas de pb de microcoupure de courant et fichier flingué.

 

[Sheldor]

Re : Fichier de 50 MO - calcul et enregistrement extrêmement lent

bonjour Misange,

cherchant aussi les raisons d'enflement des fichiers, pourrais tu nous expliquer "Ne jamais formater une ligne ou une colonne entière"
faire des formats de cellule?

en cherchant j'ai constaté qu'un fichier vierge avec une bordure complète sur une colonne entière pèse 14Ko alors que si je fais cette même bordure sur 65535 cellules de la colonne (une cellule sans format donc) le fichier fait 2101 Ko

merci de ton aide
amicalement
nico

 

[Misange]

Re : Fichier de 50 MO - calcul et enregistrement extrêmement lent

Bonjour
A partir du moment où une cellule contient une information, quelle quelle soit : une valeur, un format de police, une bordure, un fond ou autre, excel doit garder ces infos en mémoire. Si c'est la cellule A1, il garde les infos d'une cellule. Si c'est la cellule B4 il garde obligatoirement les infos du "used range", c'est à dire de toutes les cellules qui vont de la cellule A1 jusqu'à B4, même si seule la cellule B4 est utilisée.
Mais si tu formates toute une ligne et toute une colonne, excel garde les infos de l'intégralité des cellules de la feuille. Déjà sur excel 2003 ça fait beaucoup de cellules (16 777 216) mais sur excel 2007 et + ça devient considérable : 1.7 e10 cellules !!!
et comme tu l'as constaté toi même ton classeur prends du poids très vite.

 

[Sheldor]

Re : Fichier de 50 MO - calcul et enregistrement extrêmement lent

j'étais juste surpris que 65536 cellules avec bordures "pèsent" 20 ko
et 65535 celules par contre 2100 ko


merci pour ces explications Misange