Excel qui "rame"
Bonjour à tous,
Je m'adresse à la communauté de cet excellent site car je fais fasse à un souci de lenteur sur un fichier excel assez conséquent. Le fichier xlsx fais 16 Mo. Mes données sont dans un onglet et occupe environs 100.000 lignes sur les 1.000.000 exploitables. Je travaille essentiellement avec les fonctions SOMME.SI.ENS et RECHERCHEV.
Si je comprends qu'Excel doivent "travailler" pour mettre à jour tous mes tableaux imbriqués quand je modifie les données ou les formules citées ci dessus, je fais également face à de nombreux ralentissements que je ne m'explique pas. Par exemple, le simple fait de déplacer une cellule vide, occupe le processeur à 100 % pendant quasiment 10 secondes.
Est ce du aux formules utilisées ? Ou dois je optimiser mes formules en les modifiant ?
Bonsoir,
ça se discute... 100 000 lignes c'est pas la mer à boire ! Si tu les multiplies par 65 colonnes sur plusieurs feuilles ça fait tout de suite plus sérieux et si en plus tes SOMME.SI.ENS sont rédigés sur toutes les colonnes (au lieu d'être rédigé sur la zone précise de tes données) , là tu pousses peut-être le bouchon un peu loin...
Mais YFO voir de plus près en particulier le nombre (et l'étendue) des formats et d'objets graphiques...
Tu pourrais déjà commencer par faire un test de compression : Si ton fichier une fois zippé ne fait plus que 2 ou 3 Mo on peut parier qu'il est un peu pollué par des scories de construction.
Un deuxième test est d'essayer de supprimer 20 000 lignes par exemple (puis tu sauvegardes sous un autre nom). Si ton fichier augmente au fur et à mesure des suppressions, tu passes à la solution suivante :
Si tu n'as qu'une seule feuille et quelques colonnes. la meilleure solution consiste à faire un Copier/Coller de ta zone de travail vers un nouveau classeur : 100 000 lignes multiplié par x colonnes.
Faire un Copier/Collage Spécial Données + Formules dans un premier temps + Format ensuite si tout se passe bien.
Si le Copier/Coller divise ton poids par 4 ou 5 voire plus,tu auras compris que c'est un problème de pollution...
Il existe de nombreuses macros de dégraissage sur le Net et sur ce forum mais quand c'est possible rien ne vaut une bonne remise à plat par Copier/Coller. En tout cas ne jamais procéder par Export ou par l'option de copie de feuille quand tu fais un clic Droit sur l'onglet... car tu exporterais par la même occasion tous tes résidus de construction.
Si tu as vraiment beaucoup de formules SOMME.SI.ENS il est probable qu'une batterie de formules BDSOMME adossé ou non à un peu de VBA résoudrait ton affaire... (à condition que tes données soit organisées de manière tabulaire)
Quand même je suis sceptique : j'ai pas encore vu beaucoup de tableaux Excel montés de manière orthodoxe qui pèsent plus de 10 Mégas.
A+
Bonjour,
merci pour votre réponse.
J'ai tenté de compressé le fichier et celui ci fait environs 13 Mo, pas 2 ou 3 comme vous l'aviez suggéré. J'essayerais les autres méthodes dés que je peux. Je penses avoir par contre du mal à mettre en oeuvre celle du copié collé compte tenu de la difficulté que j'ai eu à réalisé le fichier. J'ai par ailleurs nommé certaines zones et je ne crois pas que le copié collé en tienne compte.
D'autre part, est ce que l'utilisation de filtres sur les colonnes de mes données peut jouer sur la performance du fichier ?
Bonjour,
Non. De plus si la compression ne donne rien c'est que ton fichier est clean. Il est alors inutile de chercher à faire du Copier/COller sur un nouveau fichier.
Dans l'immédiat tu peu déjà gagner un peu de temps en faisant du calcul "sur ordre"
Il faut chercher à supprimer des formules obsolètes. Très souvent on laisse subsister des formules dont les valeurs ne changeront plus. Dans ce cas il faut figer les valeurs...
Supprimer les objets graphiques et formats non indispensables. Boutons, images, graphiques et TCD idem.
Pour les formules et les plages nommées veiller à ce qu'aucune ne déborde sur une zone vide (pas de plages définies sur toute une colonne !) Mais utiliser des plages dynamiques en utilisant DECALER()
Si possible supprimer les formules complexes et les remplacer par des formules de base de données (BD*) ou mieux encore par des valeurs calculées par macros...
Bon courage !
A+
J'ai tenté de supprimer des lignes, par étapes, dans l'onglet où sont enregistré mes données. Le fichier réduit en poids et retrouve de la réactivité.
Je recensé 3250 formules SOMME.SI.ENS dans mon fichier, certaines avec une fonction de rechercheV ou si imbriquée dans la même cellule. Le problème, c'est qu'à priori Excel recalcule tout des que je modifie une cellule dans un des onglets concernés par ces formules, quand bien même la cellule est vide et non concerné par mes calculs.
Le problème doit venir de là......
Je ne connais pas les formules BD, je vais regarder. Le VBA, j'ai regardé les premiers cours mais c'est trop complexe pour le moment, je ne vois pas comment le mettre en oeuvre dans mon fichier.
Tu peux me mettre un bon aperçu de ton fichier mais quand même un peu réduit (1 Mo maxi) ?
Je te mets mon mail en MP
A+
bonjour,
je suis pas très calé mais j'ai eu une macro sur le net qui m'a aisé dans des fichiers pareils pour nettoyé la macro ( sans précision , il faut faire un essaie sur un double du fichier)
Sub Nettoie()
Dim Sht As Worksheet, DCell As Range, Calc As Long, Rien As String, Avant As Double, plage As Range
On Error Resume Next
Calc = Application.Calculation ' ---- mémorisation de l'état de recalcul
'------------------------------------------------------------
MsgBox "Pour le classeur actif : " _
& Chr(10) & ActiveWorkbook.FullName _
& Chr(10) & "dans chaque feuille de calcul" _
& Chr(10) & "recherche la zone contenant des données," _
& Chr(10) & "réinitialise la dernière cellule utilisée" _
& Chr(10) & "et optimise la taille du fichier Excel", _
vbInformation, _
"d'après LL par GeeDee@m6net.fr"
'-------------------------------------------------------------
MsgBox "Taille initiale de ce classeur en octets" _
& Chr(10) & FileLen(ActiveWorkbook.FullName), _
vbInformation, ActiveWorkbook.FullName
'------------------------------------------------------------
With Application
.Calculation = xlCalculationManual
.StatusBar = "Nettoyage en cours..."
.EnableCancelKey = xlErrorHandler
.ScreenUpdating = True
End With
'-------------------- le traitement
For Each Sht In Worksheets
Avant = Sht.UsedRange.Cells.Count
Application.StatusBar = Sht.Name & "-" & Sht.UsedRange.Address
'-------------------Traitement de la zone trouvée
If Sht.UsedRange.Address <> "$A$1" Or Not IsEmpty(Sht.[A1]) Then
Set DCell = Sht.Cells.Find("*", , , , xlByRows, xlPrevious)(2)
'----------------Suppression des lignes inutilisées
If Not DCell Is Nothing Then
Sht.Range(DCell, Sht.Cells([A:A].Count, 1)).EntireRow.Delete
Set DCell = Nothing
Set DCell = Sht.Cells.Find("*", , , , xlByColumns, xlPrevious)(, 2)
'----------------Suppression des colonnes inutilisées
If Not DCell Is Nothing Then Sht.Range(DCell, Sht.[IV1]).EntireColumn.Delete
End If
Rien = Sht.UsedRange.Address
End If
ActiveWorkbook.Save
'---------------------Message pour la feuille traitée
MsgBox "Nom de la feuille de calcul :" & Chr(10) & Sht.Name & Chr(10) & Format(Sht.UsedRange.Cells.Count / Avant, "0.00%") & " de la taille initiale", vbInformation, ActiveWorkbook.FullName
Next Sht
'--------------------Message fin de traitement
MsgBox "Taille optimisée de ce classeur en octets " & Chr(10) & FileLen(ActiveWorkbook.FullName), _
vbInformation, _
ActiveWorkbook.FullNameActive
'--------------------
Application.StatusBar = False
Application.Calculation = Calc
End Sub
Bonjour,
une méthode simple et rapide à mettre en oeuvre :
garder 1 ligne avec tes formules (sauf celles susceptibles d'évoluer en cours d'utilisation) , sélectionner toute la plage en-dessous et copier-collage spécial valeur.
Le jour ou tu as une mise à jour à faire tu recopies les formules vers le bas et ensuite tu refais la manip.
eric
galopin01 a écrit :Tu peux me mettre un bon aperçu de ton fichier mais quand même un peu réduit (1 Mo maxi) ?
Je te mets mon mail en MP
A+
Je n'y tiens pas trop, c'est un fichier professionnel
Je crois que tu as raté ma proposition...
Oui, je l'ai lu depuis.
J'en prends bonne note, ça peut être une alternative si je ne trouve pas d'autre solutions.
L'autre solution est de faire la même chose en vba.
SOMME.SI.ENS est très gourmand en ressource.
eric
Je vais regarder si je trouve une solution mais le VBA et moi, ça fait plus que 2.