Poids fichier excel avec formules

[Florian53]

Bonjour,

je dispose d'un fichier excel avec beaucoup de formules et le poids du fichier est de 11Mo,

Pouvez vous me dire si c'est normal ? et si une solution existe pour diminué le poids de ce fichier ?

Merci à vous

 

[mutzik]

re,
dans les nouvelles versions de Excel, un xlsx est le format de fichier excel sans macros et sans l'interface VBA qui prend quelques Ko
xlsm : m pour maros donc fichier avec VBA et donc plus lourd

 

[eriiic]

pour compléter mutzik, oui un .xls est souvent considérablement plus lourd mais ... pour la taille du fichier, en mémoire c'est du pareil au même.
Je pensais surtout à ça car tu te prives de nouvelles fonctions qui peuvent raccourcir tes formules. D'ailleurs si tu répondais à la question ?
Si la structure de tes données est telle que tu peux les exploiter directement, le plus efficace serait une macro pour insérer tes dates absentes (je n'ai pas rouvert le fichier pour voir). Ca éviterait de les doubler, et plus de formules.

 

[Florian53]

Bonsoir à tous, et merci pour vos éclaircissements:

Eriiiic : j'ai fais quelques essais cet apres midi voici mes remarques

Re,

beaucoup de formules pourraient être allégées, à commencer par ton calcul de dates.

=SI(A3="";"";SI(A3>TEXTE(ANNEE((AUJOURDHUI()-7)-JOURSEM((AUJOURDHUI()-7);2)+4)&""&TEXTE(ENT(MOD(ENT(((AUJOURDHUI()-7)-2)/7)+3/5; 52+5/28))+1;"00");"0");"";(A3+7)))

ne peut pas se résumer à ça :

=SI(OU(A3+7>AUJOURDHUI();A3+7="");"";A3+7)

?

La formule (SI(OU(A3+7>AUJOURDHUI();A3="");"";A3+7)) fonctionne sauf quand la date arrive a AUJOURDHUI(), a la place de ne rien affiché j'ai #Valeur qui s'affiche, j'ai essayé plusieurs chose mais rien n'y fait . J'ai fait un enregistrement quand meme afin de voir la place gagné ==> 40Ko max

1) pourquoi un xls ? Compatibilité obligatoire ? Le fait de passer en format xlsm MAGIQUE le fichier ne fait plus que 1500Ko
tu te passes de sierreur() qui permettrait de supprimer un recherchev().
que tu peux sinon remplacer par un nb.si plus court et léger ==> J'ai pas encore essayé cette solution
2) tu remplacerais tes "-" par des 0 directement dans les données tu gagnes encore un autre recherchev()
Macro faire afin de remplacer directement les "-" par des "0" afin d'enlever une rechercheV ==> gain de 1,5 à 2 MO sur le fichier
3) on ne prépare pas des lignes en avance qui ne servent à rien d'autre qu'alourdir encore un peu plus
Du coup il faudrait dans la macro récupérer le nombre de ligne de la base de données afin de répéter les formules sur x lignes seulement. Cependant je ne vois pas comment faire cette macro.

4) et si ça reste encore trop lourd (sans doute que oui) garde tes formules uniquement sur la 1ère ligne.
Copier-collage spécial valeur sur les autres. En cas de mise à jour ou d'ajout de données tu as juste à copier-coller ta ligne de formules. Enfin ça, ça dépend du mode d'utilisation de ton classeur. Maj permanentes ou ponctuelles.

Ce fichier va etre utilisé par plusieurs utilisateurs dont je ne connais pas leur connaissances informatique donc il faudrait que ce fichier soit le plus automatisés possible.
Voilà déjà quelques pistes.

Pour ton dernier Post: effectivement une macro comme celle ci serait très intéressante et enlèverait une bonne partie des formules, mais c'est aussi une macro qui me parait compliqué pour moi.

 

[Florian53]

Voici le nouveau fichier.

 

[eriiic]

Les colonnes de A à I me permettent de créer un Top 5 fixe

C'est quoi un top 5 pour toi ?
Pour moi c'est les 5 plus grandes valeurs par exemple.

En imaginant qu'on t'ajoute les semaines manquantes dans AAA, qu'on te mette les colonnes
A6 A22 A23 A19 A21 Autres en tête, tu n'arriverais pas à faire tous tes graphiques dessus ?
En utilisant un nom dynamique pour celui avec inf et sup.
Parce que tes données ne sont pas seulement doublées, mais quadruplées pour celle-ci.

 

[Florian53]

Le top5 est bien les 5 plus grandes valeurs,

Sur le tableau de gauche (A1:I943) en gros c'est un top 5 fixe qui va prendre les 5 plus grandes valeurs de la période mini "200101" à la plus grande "201729".

Pour les 2 autres tableaux, c'est pour récupérer le Top5 ( Dynamique) en fonction des bornes inf et Sup .

Si une macro peux rajouter les lignes manquantes et récupérer le top5 ( fixe) et top5 (dynamique), j'arriverais a faire mes graphiques et sa éviterais de quadruplé les formules. Et la je pense que la taille de mon fichier sera peanuts .

Encore faut 'il réussir a faire cette macro, je pense qu'il faudrait :

- Pouvoir rajouter les dates manquantes en insérant des lignes et en prenant en compte qu'à chaque "XXXX52" on change d'année.

- Récupérer le nombre de ligne de la base de données pour pouvoir minimiser le nombre de ligne à traiter lors de l'insertion des formules.

- Pouvoir récupérer un Top 5 fixe qui se recopie automatiquement en (A1:I & lignes)

Je ne sais pas si c'est possible.

Merci à vous

 

[eriiic]

Bonjour,

je n'ai toujours pas compris ton top5
Tu sembles plutôt récupérer toutes les valeurs des 5 champs précis
Prend juste les 10 premières lignes de ta table. Quel serait ton (ou tes ?) top 5 en indiquant les cellules ?
eric

 

[Florian53]

Bonjour,

Je prends les grandes valeurs de la selection C3:AC3:

Donc le TOP 5 de la ligne 3 est :

-1er :" H3" (A6)
-2eme : "X3" (A22)
-3eme : "Y3" ( A23)
- 4eme : "U3" ( A19)
- 5eme : "W3" (A21)

J'ai un nom : DecalSct = =DECALER(Sct;2;;

et Sct = =AAA!$C$1:$AC$1

le Nom DecalSct me permet de trouver la grande valeur de celui ci

 

[eriiic]

Ah ok.
1) donc le top 5 de la ligne Total, et tu veux toutes les lignes de ces colonnes. On est d'accord ?
2) du coup, les autres colonnes tu t'en fous et on peut les supprimer pour alléger encore plus ?
3) dans l'hypothèse, improbable mais non nulle, où le 5ème total est présent 2 fois ou plus, on prend le premier qui tombe sous la main ou tu passes à un top 6 voire plus ?
4) les graphiques n'étant pas ma tasse de thé j'aurais aimé que tu répondes à ma question : pour le top 5 inf/sup tu ne peux pas définir la plage des données avec un nom dynamique sur la totalité de la table ? Une 2nde extraction est-elle vraiment nécessaire ?

 

[Florian53]

Ah ok.
1) donc le top 5 de la ligne Total, et tu veux toutes les lignes de ces colonnes. On est d'accord ?
Oui c'est bien ça
Je veux récupérer l'information par ex : Combien de A1 à la date du 201343 ? Rep : 3

2) du coup, les autres colonnes tu t'en fous et on peut les supprimer pour alléger encore plus ?
Il 'y a que le colonne " B" qui ne sert à rien

3) dans l'hypothèse, improbable mais non nulle, où le 5ème total est présent 2 fois ou plus, on prend le premier qui tombe sous la main ou tu passes à un top 6 voire plus ?
Effectivement j'y est pensé mais comme c'est peu probable je prendrais le 1er qui me vient sous la main

4) les graphiques n'étant pas ma tasse de thé j'aurais aimé que tu répondes à ma question : pour le top 5 inf/sup tu ne peux pas définir la plage des données avec un nom dynamique sur la totalité de la table ?

Si tu souhaite avoir un nom, qui prends de la 1er ligne ( A4 : A & dernierligne ), oui c'est possible de créer un nom de ce genre. Est ce ceci ta demande?

Ou

Tu souhaite avoir un nom qui sélectionne ce qui se trouve entre la borne inf incluse et la borne sup incluse.

Auquel cas je pense que ce nom exite déjà " Graph".

Une 2nde extraction est-elle vraiment nécessaire ?

Je me sers de cette 2nd extraction pour mon top5 dynamique mais si une autre façon existe notamment avec l'aide d'une macro, elle n'a pu d'utilité .

Merci de prendre de ton temps pour m'aider

 

[eriiic]

2) tu ne réponds pas à ma question.
L'idée est de travailler sur la feuille AAA, ajouter tes semaines, supprimer le superflu et limiter au maximum la duplication de données.
Si tu peux faire tes graphiques directement avec AAA c'est toujours ça de gagné...
Peut-on supprimer les colonnes hors top 5 sur AAA ?
4) je ne vais pas te faire le graphique ni le nom dynamique qu'apparemment tu sais faire.
La question est : peux-tu faire un tel graphique avec un nom dynamique sur AAA (plage des données variable grâce au nom, plage de titres fixe) ou non ?
Depuis 2010 je galère pour imposer des noms pour un graphique, sans doute maîtrises-tu mieux cet aspect.
Sinon on reste sur une 2nde extraction pour la période voulue inf/sup

 

[Florian53]

2) tu ne réponds pas à ma question.
L'idée est de travailler sur la feuille AAA, ajouter tes semaines, supprimer le superflu et limiter au maximum la duplication de données.
Si tu peux faire tes graphiques directement avec AAA c'est toujours ça de gagné...
Peut-on supprimer les colonnes hors top 5 sur AAA ?

Non, je voudrais garder les colonnes hors TOP5 (fixe) car le Top5(fixe) peut être different du Top5(dynamique)

Donc sa m'oblige a créer une autre feuille afin de faire les calculs intermédiaire.


4) je ne vais pas te faire le graphique ni le nom dynamique qu'apparemment tu sais faire.
La question est : peux-tu faire un tel graphique avec un nom dynamique sur AAA (plage des données variable grâce au nom, plage de titres fixe) ou non ?

Oui, je peux m'occuper de la gestion des noms et des graphiques et créer un nom dynamique sur la feuille AAA

Depuis 2010 je galère pour imposer des noms pour un graphique, sans doute maîtrises-tu mieux cet aspect.
Sinon on reste sur une 2nde extraction pour la période voulue inf/sup

 

[eriiic]

2) Non, je voudrais garder les colonnes hors TOP5 (fixe) car le Top5(fixe) peut être different du Top5(dynamique)
Ah oui, je n'avais pas vu cet aspect.
D'autant plus que tu as mis les mêmes colonnes pour les 2 dans Feuil1, ça ne pouvait pas sauter aux yeux.
Ce qui sous-entend qu'il faut refaire les totaux pour la sélection...
Il y a d'autres 'détails' oubliés ?

Une question est restée en suspend :
Est-ce qu'on peut modifier l'ordre des colonnes sur AAA (mettre celles du top 5 en premier et insérer devant ou derrière la colonne Autres)) pour éviter de dupliquer les données ou tu tiens absolument à avoir une copie sur Feuil1 ?

 

[Florian53]

2) Non, je voudrais garder les colonnes hors TOP5 (fixe) car le Top5(fixe) peut être different du Top5(dynamique)
Ah oui, je n'avais pas vu cet aspect.
D'autant plus que tu as mis les mêmes colonnes pour les 2 dans Feuil1, ça ne pouvait pas sauter aux yeux.
Ce qui sous-entend qu'il faut refaire les totaux pour la sélection...
Il y a d'autres 'détails' oubliés ?
Non je pense que nous avons fait le tour des détails

Une question est restée en suspend :
Est-ce qu'on peut modifier l'ordre des colonnes sur AAA (mettre celles du top 5 en premier et insérer devant ou derrière la colonne Autres)) pour éviter de dupliquer les données ou tu tiens absolument à avoir une copie sur Feuil1 ?
Oui on peut mettre le Top5 en premier sa fera gagner uneétape mais comme le top5 dynamique qui ne sera certainement pas le meme que le fixe, je pense que je n'aurais pas le choix repasser par la feuil1 pour ces étapes de calculs.

 

[Florian53]

Bonjour,

J'ai essayé de faire un premier jet pour la macro, peux tu me dire ce que tu en penses:

Sub Macro4()
'
' Macro4 Macro

    Sheets("AAA").Activate
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.NumberFormat = "dd/mm/yyyy"
    Range("A4").FormulaR1C1 = "=(DATE(LEFT(RC2,4),1,1))"
    Range("A5:A" & [B65536].End(xlUp).Row).FormulaR1C1 = "=IF(R[-1]C="""","""",IF(R[-1]C[1]>TEXT(YEAR((TODAY()-7)-WEEKDAY((TODAY()-7),2)+4)&""""&TEXT(INT(MOD(INT(((TODAY()-7)-2)/7)+3/5, 52+5/28))+1,""00""),""0""),"""",(R[-1]C+7)))"
   
    For i = Range("A65536").End(xlUp).Row To 4 Step -1
   
    If i - (i - 1) <> 7 Then
    Rows(i).Insert Shift:=xlUp
    Range("A" & i).FormulaR1C1 = "=R[-1]C1-7"
    Range("B" & i).FormulaR1C1 = "=CNUM(TEXTE(R[-1]C[2];""000000"")+1)"
    End If
    Next

End sub