Fichier très lent à l'utilisation
Bonjour,
Je travail sur un fichier Excel qui perd de sa vivacité,
Je me suis renseigné et ai tenté de supprimer les colonnes et lignes inutiles sans succès,
J'ai aussi essayer plusieurs macro de nettoyage en vain,
Je pense que soit je m'y prends mal soit... je m'y prends mal...
Pour info j'ai tendance à faire des recherchev sur des colonnes entières (A:A par exemple), est-ce que cela peut m'empêcher de supprimer les lignes en fin de colonne A ?
J'ai aussi une formule matricielle dans toute la colonne B de l'onglet DVR
Est-ce que quelqu'un pourrais de l'alléger de chez lui ?
Vous trouverez le fichier ici :
Merci d'avance,
Julien
bonjour
Je suis vraiment trés loin d'etre un as de EXCEL, mais le seul conseil que je peu te donner
aujourd'hui tu a un fichier avec une formule
=SI(ESTERR(CHERCHE("Lahti Scala";L4));SI(ESTERR(CHERCHE("OmniLink";L4));SI(ESTERR(CHERCHE("Jonckheere Transit";L4));SI(ESTERR(CHERCHE("Aabenraa";L4));SI(ESTERR(CHERCHE("Säffle";L4));SI(ESTERR(CHERCHE("Carrus ";L4));SI(ESTERR(CHERCHE("Urbino";L4));SI(ESTERR(CHERCHE("Vest Center";L4));SI(ESTERR(CHERCHE("Wroclaw ";L4));SI(ESTERR(CHERCHE("Lahti";L4));SI(ESTERR(CHERCHE("Crossway";L4));SI(ESTERR(CHERCHE("8700 Carrus";L4));SI(ESTERR(CHERCHE("Vest";L4));SI(ESTERR(CHERCHE("Carrus";L4));SI(ESTERR(CHERCHE("Sprinter City";L4));SI(ESTERR(CHERCHE("Car.Ind Urban 40 Elettrica";L4));SI(ESTERR(CHERCHE("AutoCuby CityLine";L4));SI(ESTERR(CHERCHE("Wroclaw";L4));U4;SUBSTITUE(U4;"Wroclaw";"W"));SUBSTITUE(U4;"AutoCuby CityLine";"AcCl"));SUBSTITUE(U4;"Car.Ind Urban 40 Elettrica";"CI Urban 40E"));SUBSTITUE(U4;"Sprinter City";"Sptr Cty"));SUBSTITUE(U4;"Carrus";"C"));SUBSTITUE(U4;"Vest";"V"));SUBSTITUE(U4;"8700 Carrus";"C8700"));SUBSTITUE(U4;"Crossway";"Crsw"));SUBSTITUE(U4;"Lahti ";"L"));SUBSTITUE(U4;"Wroclaw ";"W"));SUBSTITUE(U4;"Vest Center";"VC"));SUBSTITUE(U4;"Urbino";"Urbino"));SUBSTITUE(U4;"Carrus ";"C"));SUBSTITUE(U4;"Säffle ";"S"));SUBSTITUE(U4;"Aabenraa ";"A"));SUBSTITUE(U4;"Jonckheere Transit ";"J T"));SUBSTITUE(U4;"OmniLink";"O"));SUBSTITUE(U4;"Lahti Scala";"LS"))comme cella
demain tu auras un fichier trop trop lourd
je pense que l'ideal cc'est de le realiser en VBA et la tes feuilles ne contiendrons que du texte!!!
enfin c'est mon avis
cordialement
Salut iliyes,
C'est vrai que j'ai pas mal de formules en tout genre mais ça marchait plutôt pas mal depuis plusieurs mois
Et la depuis hier c'est la cata,
J'ai utilisé les macro de https://forum.excel-pratique.com/applications/degraissez-le-mammouth-t4627.html pour nettoyer mon fichier.
Il m'affiche que sur l'onglet DVR, la Adresse DerCel (Dernière cellule utile) est AE1048576, c'est juste énorme car je n'ai que 171 lignes utiles.
Je pense que c'est ça qui ralenti mon fichier, malheureusement je n'arrive pas a supprimer les cellules en excès...
Julien
Salut Amadéus,
Merci beaucoup pour ton fichier, c'est beaucoup mieux !
J'ai essayé de voir ce que tu avais changé, j'ai identifié :
Onglet DVR:
Tout le tableau de gauche, j'ai un peu de mal à comprendre comment tu appelles la colonne garage de l'onglet weekly report, sans faire appel à l'autre onglet et juste le terme "Garage" et idem pour "System_setup", j'ai regardé si tu ne les avais pas recopiés/cachés dans l'onglet DVR mais non
Tu m'as aussi remplacé les termes FAUX par *vide*
et enfin tu m'as mis des couleurs un peu plus funky mais je doute que ce soit cela qui change la donne
En bref, je dirais que c'était la formule matricielle qui mettait le souque, est-ce bien ça ?
Je serais aussi intéressé de savoir,
Comment appelles-tu un autre onglet sans le nommer
Faut-il éviter de faire des tableaux ("mettre sous forme de tableau")
Ma dernière question, en lien avec la réponse de iliyes, faut-il passé au VBA même si c'est possible de le faire sans ?
Merci d'avance,
Julien
bonjour
bravo a amadeus!!! chapeau
le fichier d'origine pesais 2Mo et celui la 420 Ko!!!
comment!!!!
je suis époustouflé
merci a excel pratique
Bonjour.
Je n'ai pas eu le temps de donner les explications et de me connecter au Forum depuis hier.
Ce n'est pas tant le poids du fichier qui importe, mais la limitation des calculs.
Les modifications apportées sont les suivantes.
Les formules matricielles sont de grosses consommatrice de mémoire. Il faut donc éviter de leur demander sur les colonnes entières si on peut l'éviter.
Pour cela, il faut définir des plages dites dynamiques parce qu'elles s'adaptent automatiquement au nombre de lignes à traiter lorsqu'on ajoute ou supprime des lignes.
La majorité des formules matricielles de la page DVR traitent les données de la Feuille Weekly report
Au lieu d'utiliser dans la formule le champ 'Weekly report'!E:E on préférera nommer Garage la formule
=DECALER('Weekly report'!$E$2;;;NBVAL('Weekly report'!$B:$B)-1)qui représente en colonne E, le nombre de lignes utilisées en colonne B
Dans le cas général, on écrit
=DECALER('Weekly report'!$E$2;;;NBVAL('Weekly report'!$E:$E)-1)mais ici, on constate que en colonne E, il existe des lignes vides entre les données.
En utilisant la colonne B qui ne présente pas de vides entre les données, on obtient en colonne E un champ identique à celui de la colonne B
=DECALER('Weekly report'!$E$2;;;NBVAL('Weekly report'!$B:$B)-1)Une fois ce Nom garage défini,
La formule
=LIGNES(Garage)donne le nombre de lignes utiles de la colonne E
A partir de là, pour définir des champs identiques sur les autres colonnes de calculs de de la formule
On nomme DVR_state la formule correspondant au champ de cette colonne DVR state
=DECALER('Weekly report'!$AE$2;;;LIGNES(Garage))On nomme Garage2, la formule correspondant à seconde colonne Garage
=DECALER('Weekly report'!$AF$2;;;LIGNES(Garage))et System_Setup la formule correspondant au champ de la colonne System Setup
=DECALER('Weekly report'!$T$2;;;LIGNES(Garage))
Et dans la formule matricielle, on remplace toutes les colonnes concernées par les noms des champs dynamiques de toutes ces colonnes pour obtenir au final une formule matricielle pour laquelle Excel limitera les calculs aux plages de données nommées et non aux colonnes entières concernant ces données.
ex en B2 de la Feuille DVR
=SOMME(SI((Garage=DVR!$A2)*(System_Setup="YES");1;0))+DVR!$C2J'espère cette explication assez claire, sinon, je reste à disposition si besoin.
Cordialement
Salut Amadéus,
Merci pour tes explications,
Je ne connaissais pas la formule "Décaler" ni la fonctionnalité "Gestionnaire de noms",
Je t'avoues qu'en lisant tes explications, je n'ai pas compris grand chose, puis en me documentant j'ai compris le mécanisme,
Je n'avais pas encore été confronté a ces problèmes de mémoires sous excel,
Mais ce duo de fonction va m'être très utile car me servant de plages qui évoluent, j'avais tendance à les nommer du type "=A:A" avec le souci qu'on connait maintenant sur les formules matricielles, et c'est aussi plus propre car on ne calcul que la zone utile,
Encore merci et à bientôt pour de nouvelles astuces !
Julien
Bon c'est encore moi...
J'ai finalement une dernière question, j'ai voulu mettre en pratique toute cette belle théorie (c'est comme ça qu'on apprend
J'ai essayé d'améliorer ma formule dans l'onglet DVR colonne E :
=RECHERCHEV(DVR!$A2;'Weekly report'!$E$2:$I$184;5;FAUX)
La plage (table_matrice) de ma fonction recherchev n'est pas dynamique, j'ai donc voulu y remédier en employant la fonction DECALER,
J'ai donc créer un nom qui fait référence à ma fonction décaler et qui compte le nombre de lignes successives de ma plage (table_matrice) que j'ai nommé NB_VAL_BUS
=LIGNES(DECALER('Weekly report'!$B$1;;;NBVAL('Weekly report'!C65352:C65534;)))
Ensuite pour moi je devrais entrer ce genre de recherchev
=RECHERCHEV(DVR!$A2;'Weekly report'!$E$2:$I$NB_VAL_BUS;5;FAUX)
Évidemment sans succès, qu'elle doit être la mise en forme à adopter ?
Y-a-t-il un intérêt à faire ce que j'essai de faire ou comme la fonction recherchev n'est pas une fonction matricielle, la perte en temps de calcul sera nulle ?
Merci d'avance,
Julien
Bonjour
Tu as le nombre de lignes de ton tableau dans les précédents champs nommés.
Si tu veux nommer ta plage E2:E184 en champ dynamique, utilise le même principe.
Par exemple pour déterminer la plage Garage2, tu as utiliser la fonction DECALER.
=DECALER(Ref;Lignes;Colonnes;hauteur;largeur)
=DECALER('Weekly report'!$AF$2;;;LIGNES(Garage))soit un champ partant de AF2 avec la même hauteur que Garage.
Maintenant, pour définir à partir de 'Weekly report'!$E$2:$I$2 un champ de même hauteur, tu fais tout pareil:
exemple: Tu nommes Bus (Plus le nom est court et plus le nombre total de caractères utilisés dans un grand nombre de formules est faible, donc, gain de mémoire également)
=DECALER('Weekly report'!$E$2:$I$2;;;LIGNES(Garage))Ta formule RECHERCHEV en E2 devient
=RECHERCHEV(DVR!$A2;Bus;5;FAUX)Sans vouloir trop charger, je t'invite à regarder de plus près la Fonction DECALER parce que si tu suis bien les divers arguments de cette fonction, la formule précédente nommée Bus
=DECALER('Weekly report'!$E$2:$I$2;;;LIGNES(Garage))peut aussi s'écrire
=DECALER('Weekly report'!$E$2;;;LIGNES(Garage);5)Décaler(E2; de 0 ligne; 0 colonne;même hauteur que le champ Garage; largeur de 5 colonnes)
Ceci dit, de manière générale limiter les calculs aux plages concernées est toujours un gain de mémoire, donc de temps de calcul
Cordialement
Hello Amadéus,
Merci pour ce complément,
Je trouve cette fonction un peu dure à appréhender,
Je vais tacher de l'utiliser pour la maîtriser,
Une autre question me vient, est-on limité en nombre de nom défini ?
Car dans certains fichiers, je pourrais nommer beaucoup beaucoup de mes plages...
Julien
Salut Amadéus,
Comme demandé ci-dessus, j'ai une autre question : est-on limité en nombre de nom défini ?
Car dans certains fichiers, je pourrais nommer beaucoup beaucoup de mes plages...
Merci de ta réponse et bonne journée !
Julien
Bonjour
Non, limités seulement par la mémoire disponible.
Cordialement