Optimisaion d'un fichier Excel
Bonjour,
Je suis déjà venu ici pour un problème similaire il y a plusieurs mois. Le fichier est toujours le même d'ailleurs ^^'
Le problème est simple:
Le fichier comprend plusieurs onglets. SUIVIPROD, ligne 41, ligne 31....
Lorsqu'on entre une donnée dans SUIVIPROD et en particulier dans les colonnes E à N, le fichier et grandement ralenti.
C'est lié au fait que dans chaque onglet Ligne ## les calcules répétés ralentissent le fichier.
J'ai bien tenté quelques solution. Notamment avec "=SOMMEPROD((P2="En cours")*" devant chaque cellule afin que le calcul se fasse seulement si une cellule contient un teste précis. Mais je ne suis pas certains que cela fonctionne vraiment :/
N'étant pas un expert en la matière, je m'en remet à vous
Merci
Bonjour Pounch,
Ton fichier Excel est assez lourd (899 Ko) ; même si c'est un .xlsm il ne contient aucun code VBA.
J'avais eu l'idée, par VBA, de passer en calcul manuel avant le traitement puis de repasser en calcul automatique après, mais c'est bien sûr devenu sans objet ; peut-être peux-tu essayer de voir ce que ça donne en faisant cela manuellement, via l'option avancée d'Excel correspondante ? à part ça, pas d'autre idée, donc je laisse la suite à un autre intervenant.
Cordialement,
dhany
Bonjour,
Merci de ton retour
De quelle option avancée parles tu ?
Pour ce qui est du VBA avez vous une idée pour m'orienter voir même un tuto ? Même si j'imagine qu'il faut de bonne notion
Bonsoir Pounch,
Désolé : c'était bien une option, mais pas avancée : bouton Office (= menu fichier), bouton « Options Excel », choisir « Formules », 1ère rubrique « Mode de calcul », c'est là !
Pour apprendre le VBA :
Tu peux aussi faire une recherche Google « apprendre VBA » ou « cours VBA ».
Et bien sûr y'a aussi l'aide VBA déjà intégrée dans Excel.
Bonne chance !
dhany
Bonsoir,
en "plus directe" :
@ bientôt
LouReeD
Re
Pour ceci :
Notamment avec "=SOMMEPROD((P2="En cours")*" devant chaque cellule afin que le calcul se fasse seulement si une cellule contient un teste précis. Mais je ne suis pas certains que cela fonctionne vraiment :/
En fait cela n'empêche pas les calculs des SOMMEPROD à mon avis...
En effet, les calculs s'ils sont en "automatique" sont lancées sur tout le classeur dès qu'il y a changement d'une valeur dans une cellule, voir même si ce n'est qu'une validation d'une valeur déjà existante...
Donc ce stratagème ne fonctionne pas... Je pense.
@ bientôt (dans l'attente de la fin de téléchargement de votre fichier...)
LouReeD
Vos formules :
=SOMMEPROD((P2="En cours")*NB.SI.ENS(SUIVIPROD!$E$2:$E$29781;'Ligne 41'!$A2;SUIVIPROD!$J$2:$J$29781;'Ligne 41'!$P$1))
29781 lignes de SOMMEPROD multiplier par x colonnes sur Y feuilles !!!!! on arrive facilement à des milliards de calculs !!! Non ?
Pas étonnant que cela mette du temps
En plus la feuille SUIVIPROD ne comporte "que" 3777 lignes donc il y a 29781-3777 = 26004 lignes de calculs pour rien !
Je vois aussi que la feuille SUIVIPROD qui sert de base de données "garde" en mémoire les années 2016 - 2017...
Est-ce bien nécessaire ?
N'y a t il pas possibilité de faire un fichier par année, cela ferait pour 2018 3249 lignes de calculs en moins...
Je retourne sur le fichier
@ bientôt
LouReeD
Bonsoir LouReeD,
Réponse à ton message de 23 h 03 : oui, effectivement, j'l'avais pas remarqué !
c'est pa'c'que c'est une option qu'j'utilise jamais en manuel :
j'le fais via VBA, avec Application.Calculation = -4135
... suivi en fin de traitement par Application.Calculation = -4105
j'préfère souvent utiliser les constantes numériques, plus courtes à écrire et à lire que les noms à rallonge !
faut juste avoir un peu d'mémoire pour s'en rappeler, mais à force d'utiliser...
dhany
Ligne 41 :
le tableau est quasi vide, mais il demande tout de même de faire des SOMMEPROD sur 37 lignes vides...
Une solution simple de réduire les formules qui "se répète" à l'identique d'une ligne à l'autre mis à par la référence à la ligne où se trouve la formule est de transformer la plage de cellule de la feuille en tableau Excel par l'outil insertion tableau.
L'avantage ? Le tableau a la taille des données qu'il contient, lors d'ajout de données les formules et mise en forme se recopie d'elles-mêmes, plus besoin de la ligne "ligne modèle pour les formules"
En somme c'est la structure même du fichier qu'il faut revoir, non ?
@ bientôt
LouReeD
dhany, moi la mémoire c'est pas mon truc...
Toujours obligé de revenir "en arrière" sur ce que j'ai déjà fait pour pouvoir le refaire !!!!
D'où la lenteur de mes proposition de code VBA, trop de recherches !!!!
Mais je m'en sort !
@ bientôt
LouReeD
Bonsoir tous,
moi je me demande pourquoi il y a des lignes vides en plein tableau dans l'onglet "suiviprod" ?
P.
Bonsoir,
voilà sans rien comprendre au classeur, j'en ai fait du ménage (Il faudrait que je fasse du ménage dans mon Français !!!
Le fichier contient toutes les feuilles lignes avec un tableau Excel, suppression des lignes inutiles.
Suppression des validation de données qui allaient jusqu'à la ligne 1 000 000 (pas précis, je sais, je sais...)
Suppression de la mise en couleur de colonne sur toute la colonne !!!!
Modification des 23460 formules restantes avec une taille des SOMMEPROD à 3777...
Le fichier :
A vous de me dire s'il calcul plus vite....
Reste à voir s'il est encore fonctionnel, mais même si ce n'est pas les cas mais qu'il est plus rapide, alors cela montre que d'ajuster à la juste taille les calculs cela va plus vite, non ?
@ bientôt
LouReeD
Ah oui une chose en plus :
Comme je l'ai entendu (lu) il y a peu de temps, les SOMMEPROD sont souvent utilisés pour synthétiser des données venant d'un ou plusieurs tableaux, hors sur ce fichier j'ai l'impression que c'est l'inverse....
@ bientôt
LouReeD
Hello,
je viens d'ajouter des lignes dans l'onglet "suiviprod" des 2 fichiers avant et après tes modif (Loureed) et le tien va plus vite en effet;
PC : Win 10 24 G de Ram Interl 4. Ghz / Excel 2010 pour le tien et XL 2016 pour l'ancien qui met quelque secondes à donner un résultat à la dernière ligne du même onglet.
P.
Bonjour!
Merci LoorReeD ! Le fichier est effectivement plus rapide. Alors pour la petite histoire, le fichier a été créé en 2015 par un stagiaire en charge de la mise en place d'un suivi des formation sur un site de production de mise en bouteille. Ce fichier est gérer et actualisé par différent service et donc sur plusieurs postes. Suite aux demandes de chacun, il lui a été difficile de structurer le fichier à son goût...
N'étant plus présent, personne ne veut prendre la responsabilité de l'entretien du fichier ^^' alors qu'il est devenu presque indispensable dans la gestion du site de production....
J'avais envisage le VBA mais je manque de notion dans le domaine xD
En tout cas merci pour vos retours
Bonjour !
Alors pour commencer c'est OU et non pas OO et en plus je n'ai qu'un seul R, bien que souvent j'ai l'air méchant en plus....
Vérifiez bien le fonctionnement du fichier allégé, car j'ai coupé dans le "vif".
Le mieux est que vous fassiez vous même le "régime"....
@ bientôt
LouReeD
Merci pour votre temps.
J'envisage sérieusement de revoir totalement la structure du fichier afin de limiter les calculs
Bon courage @ vous !
@ bientôt
LouReeD