Alléger un fichier

[Sarahlala]

Bonjour le forum,

J'aurai besoin de de vos avis et conseil sur ce point: je souhaiterai savoir s'il est possible de créer des liens entre plusieurs fichiers excel? Je vous explique mon cas de figure. J'avais posté il y a quelques semaines un message afin de savoir comment réduire mon fichier excel qui plante tout le temps tellement il est énorme, sans réel succès.

D'où mon idée: puis je avoir un fichier par mois puis un autre qui regrouperait toutes les données dans différents fichiers MOIS dans un TCD??? De cette façon, le fichier récapitulatif serait moins lourd et les données pourraient s'actualiser mois après mois lorsque mes collègues ajouteraient des mois (donc des fichiers) au dossier.

Vos avis donc donc les bienvenus!

Bonne journée!

 

[Misange]

Re : Lien entre différents fichiers

Bonjour

Ce que tu proposes est bien pire.
Quand tu dis que ton fichier excel est énorme, peux tu nous dire combien tu as de lignes et de colonnes dans ta base de données ?
Bien souvent quand un classeur est énorme, ce n'est pas tant le nombre de données qui l'alourdit mais la façon de les exploiter.
MAis là faute de plus de détails on ne peut donner qu'une impression très générale

 

[Sarahlala]

Re : Lien entre différents fichiers

Bonjour Misange,
Voici plus de détails sur mon méga fichier:
- J'ai 3 gros onglets de données qui me servent pour mes TCD: la premier contient 4876 lignes pour 49 colonnes (dont 5 de formules: CHOOSE, IF ou VLOOKUP). Le second contient 78065 lignes et 51 colonnes (dont les 7 dernière contiennent des formules: les mêmes que ci dessus et plus de COUNTIFS). Le troisiéme contient 31566 lignes pour 39 colonnes (dont les 8 dernières contiennent des formules, encore les mêmes que celles des autres onglets).

-En plus de ces 3 onglets de données extraites d'un logiciel, j'ai 5 onglets où sont indiquées les données servant pour les VLOOKUP (du type prix par trajet, produit par catégorie...)

-A partir de mes onglets de données j'ai fait 2 onglets avec 4 TCD (2 par onglet).
-Enfin mon 11éme onglet est pour l'actualisation du fichier où il y a 2 petites macro (dont une qui actualise les TCD par ex)


Bref, compliqué tout ça, je ne sais pas comment m'en sortir tellement ça plante
Je pensais que mon idée était plus simple, mais apparemment non ^^
Des idées?
Merci!

 

[Misange]

Re : Lien entre différents fichiers

Est-ce que tu es obligée de conserver tes formules dans tes bases de données ?
Autrement dit est-ce que tu pourrais faire les recherche nécessaires par VBA et ne mettre dans les cellules que les résultats en dur ce ces calculs ou bien faut-il que les données soient mises à jour en continu ? C'est rarement le cas : on peut bien souvent se contenter de ne déclencher la mise à jour des calculs que sur ordre, par exemple au moment où l'on met à jour la base de données.
En faisant celà tu allègerais considérablement ton fichier je pense.

 

[Sarahlala]

Re : Lien entre différents fichiers

Bonjour,
Effectivement je n'ai besoin de mettre à jour mes TCD qu'une fois par mois, en prenant en compte le mois écoulé. Par contre je n'ai aucune idée de comment utiliser VBA Je veux bien apprendre pour ce cas du moins: si tu as le temps de m'expliquer, je suis preneuse car mes collègues n'en peuvent plus de ce fichier!!!
Merci!

 

[titiborregan5]

Re : Lien entre différents fichiers

Bonjour Sarah, misange, le forum,

je me permets de me joindre à la conversation.

Il est fort probable que les résultats des formules en dur pèsent moins que les formules...

Du coup, on aurait besoin de connaître les formules que tu utilises pour pouvoir les adapter en VBA ou un mini bout de ton fichier exemple...ensuite ce n'est qu'une histoire de boucle à faire...

 

[Sarahlala]

Re : Lien entre différents fichiers

Bonjour,
Ok, voici les formules que j'utilise:

=CHOOSE(MATCH(MID(A2;5;2)*1;{1;2;3;4;5;6;7;8;9;10;11;12};0);"Janvier";"Février";"Mars";"Avril";"Mai";"Juin";"Juillet";"Août";"Septembre";"Octobre";"Novembre";"Décembre")

=IF(AV2=1;SUMIFS('€ usines T'!L:L;'€ usines T'!K:K;C2;'€ usines T'!I:I;G2);0)

=VLOOKUP(AF:AF;'€ usines T'!I:J;2;0)

=IF(COUNTIF(D$2:$D2;D2)>1;"";1)

=VLOOKUP($J:$J;'Catégories prod'!A:B;2;0)

=IFERROR(VLOOKUP(AX:AX;'€ usines T'!I:J;2;0);"T")

=N(COUNTIFS($E$2:E2;E2;$F$2:F2;F2)=1)

=VLOOKUP(AI:AI;'Clients'!B:E;4;0)

Voila mon panel de formule, est-ce que c'est ce que tu voulais dire titiborregan ?
Merci de votre aide,
S.

 

[Misange]

Re : Lien entre différents fichiers

Il faudrait que tu nous fasses un petit exemple (anonymisé !) de ton classeur :
laisse une dizaine de lignes significatives par feuille, avec les formules bien sur parce que là on ne peut pas faire grand chose.

Ce que je vois aussi immédiatement sur tes formules c'est que tu travailles sur des colonnes entières ce qui est une TRES mauvaise idée. Le nombre de lignes est très grand dans excel 2007 et + et il est inutile de demander à excel d'analyser 1 million de lignes quand il n'y en a que 30000 de significatives !
comme ici
=IF(AV2=1;SUMIFS('€ usines T'!L:L;'€ usines T'!K:K;C2;'€ usines T'!I:I;G2);0)
Commence par mettre tes tableaux sous forme de vrais tableaux excel, ce qui évite de devoir mettre des lignes vides destinées à accueillir d'éventuelles données.
Ensuite tu adapteras tes formules.
Plus d'infos ici
Ce lien n'existe plus

un tableau c'est beaucoup plus qu'une simple façon de présenter des données avec une alternance de bandes colorées...

 

[Sarahlala]

Re : Lien entre différents fichiers

Bonjour,
Désolé pour le retard, mais voici en PJ mon fichier allégé, avec toutes les formules que j'utilise. Merci des astuces Misange, je vais étudier la question des tableaux dés que j'en aurai le temps! Dans le fichier que j'ai mis là je n'ai pas mis les TCD par contre.

Merci de votre aide!
Sarah

 

[Misange]

Re : Lien entre différents fichiers

Bonjour
Avant de regarder le code VBA, je te propose déjà quelques modifs
j'ai commencé sur le premier onglet (et supprimé ceux auxquels je n'ai pas touché):
- mise sous forme de tableau de tout ce qui peut l'être
-remplacement des formules faisant appel à des colonnes entières aux colonnes des tableaux
(j'ai remis les tableaux de référence sur un seul onglet mais cela n'a pas d'incidence)

Dans ta première colonne tu as une date qui n'est pas sous format date. Est-ce-que cela provient d'une exportation depuis un autre logiciel ?
Pourquoi mets tu les données d'une année sur un onglet ? Si tu fais un TCD pour analyser l'ensemble de tes données, le fait de tout mettre dans une unique base de données, quitte à ajouter une colonne qui transforme ta fausse date en vraie date, tu peux sans difficulté analyser sur un seul TCD l'ensemble des données, juste en faisant varier le filtre de page année. De plus cela te permet de faire très aisément dans le même TCD des comparaisons d'une année sur l'autres (si c'est pertinent). Et tu allègeras d'autant ton classeur.
La colonne AS dans ce cas se contente de recopier la date et de l'afficher avec un format mmmm, ce qui est plus léger que de mettre la formule avec choisir.

Une fois qu'une année est terminée, que les données concernant cette année ne sont plus modifiables, tu as tout intérêt sans même faire de VBA à faire un simple copier des colonnes AS à AW et à faire un collage spécial valeur sur ces mêmes colonnes. De cette façon tu remplaces les formules par leur résultat et tu évites des recalculs qui n'ont plus lieu d'être.

Essaie déjà de mettre ton classeur sous cette forme allégée avec des tableaux partout et en utilisant des formules adaptées et dis nous ce que ça donne. J'ai remplacé tes recherchev par des index+equiv. C'est une question d'habitude, je n'utilise absolument jamais rechercheV mais tu peux choisir ce que tu préfères.

 

[Sarahlala]

Re : Lien entre différents fichiers

Merci beaucoup pour le retour, je regarde ça attentivement et une fois les modifications opérées je reviendrai sur le forum
Thanks a lot!!

 

[Sarahlala]

Re : Lien entre différents fichiers

Aprés modifications le fichier marche déjà mieux, il est beaucoup plus fluide! J'ai mis les données de l'année dernière sur un onglet (sans formule et avec forme tableau pour les dernières colonnes). Sur un autre onglet les données de cette année, avec toutes les formules mais en ne prenant pas les colonnes entières.
Sinon oui mes données proviennent d'un autre logiciel donc les dates arrivent comme ça. J'ai par ailleurs laissé mes VLOOKUP car je ne sais pas comment utiliser index+equiv..! Est ce que les VLOOKUP alourdissent le fichier?
Mon fichier est quand même lourd (je passe de 58 300 KB à 53000 KB), même après tout ça. Mais je ne sais pas s'il y a possibilité d'alléger encore? Est ce que les TCD sont gourmands en mémoire?

Merci beaucoup des astuces!!
Sarah

 

[titiborregan5]

Re : Alléger un fichier

Bonjour,

de mémoire les TCD sont très gourmands... mais à vérifier auprès d'une source sûre!
Je ne sais pas si les index+equiv sont plus légers que les recherchev, mais leur utilisation n'est pas hyper compliquée, faut juste s'y mettre (j'ai comme toi longtemps repoussé mais au final tu peux faire une recherche double critère qui est impossible avec un recherchev simple!).

 

[Misange]

Re : Alléger un fichier

Le poids d'un fichier fermé n'a pas grand chose à voir avec le poids de son utilisation.
Multiplier les TCD, les graphiques, surtout les formules matricielles peut ralentir considérablement l'utilisation d'un fichier par ailleurs pas si gros que ça. C'est pour ça qu'il vaut mieux faire un seul TCD qui analyse toutes les données plutot que 4 analysant par exemple chacun une année différente.

Ce qui compte c'est la fluidité d'utilisation. Si le temps d'ouverture, de changement de page, de rafraichissement de l'écran après avoir modifié une cellule quelque part est épouvantable, il faut réfléchir autrement.

Si tes dates arrivent comme ça alors regarde la solution que je t'ai proposée pour les transformer en vraies dates excel et pouvoir travailler avec plus facilement dans le TCD.
vlookup ou index+equi (match si tu es en anglais) n'est pas en soi une formule monstrueuse pour ce qui est de la consommation de ressources. MAis si tu en as sur des milliers de lignes, oui ça peut le devenir.
Quelle est ta fréquence de mise à jour de ces tableaux ?

 

[Sarahlala]

Re : Alléger un fichier

Bonsoir,
En faite je fais 4 TCD car j'ai 2 types de livraisons. Donc pour la livraison A j'ai un TCD de l'année dernière et un de cette année car visuellement c'est simple à comparer. Pour le type de livraison B j'ai un autre TCD pour l'année dernière et un autre pour cette année. Le TCD de l'année dernière ne bouge pas mais je veux pouvoir choisir quel(s) mois je compare donc j'ai laissé le TCD et non le tableau.
Aprés je ne sais pas si je peux fusionner les deux TCD à chaque fois car se sont deux sources différentes.
Si j'ai du temps la semaine prochaine j'essayerai de comprendre comment fonctionne cette autre formule ;-)

Pour répondre à la question de la fréquence, des nouvelles données vont être ajoutées chaque mois. Le fichier peut être utilisé plusieurs fois par mois suivant les besoin d'analyse.

Bonne soirée!