Aller au contenu principal

16 septembre 2013

16

8 TRUCS POUR RÉDUIRE LA TAILLE D’UN FICHIER EXCEL

par Sophie Marchand
Ciseaux

Il arrive fréquemment que des clients me fassent auditer leurs fichiers Excel. Très souvent, ces fichiers sont extrêmement volumineux. Ils sont donc non seulement lourds à manipuler mais également très instables. Comment faire donc pour réduire la taille d’un fichier Excel? C’est à cette question que je vais répondre dans l’article suivant.

.

Ciseaux

.

1. Version Excel

D’abord, il faut savoir que la version Excel aura un impact sur la taille de votre fichier. Faites le test. Si vous sauvegardez un fichier sous Excel 2003 et que vous sauvegardez ensuite le même fichier sous Excel 2007, vous verrez sa taille diminuer de moitié, voire même plus encore.

.

2. Longueur des champs texte

Si vous utilisez des noms de cellules, des noms de champs, des noms d’onglets et des noms de fichiers très longs, vous allez assurément allourdir votre fichier inutilement. Pensez plutôt à utiliser des abbréviations claires. Vous observerez une différence nette. Par exemple, la formule suivante est simplement l’addition de deux cellules… Vous voyez qu’elle utilise beaucoup trop de caractères pour une formule aussi simple…

.

Cliquez sur l’image pour l’agrandir

Formule longue

.

3. Dernière cellule d’un onglet

Il arrive qu’en travaillant dans Excel, on élabore et on supprime ensuite certaines sections. Utilisez la touche CTRL+End (ou Fin en français) pour voir quelle est la dernière cellule active dans chaque onglet. Souvent, vous remarquerez que votre dernière cellule réellement utilisée est V50 et que votre dernière cellule active est CD30000. Ceci alourdira incontestablement votre fichier. Pensez donc à réduire toutes les "extras" lignes et colonnes.

.

4. Références circulaires

Les références circulaires sont sans contredit des éléments d’instabilité dans un fichier Excel. La plupart du temps, lorsque vous avez des références circulaires, c’est réellement que vous avez effectué un mauvais calcul (une cellule qui réfère à elle-même dans un calcul). D’autres fois, la référence circulaire est souhaitée. Il est par contre toujours possible de contourner les références circulaires par calculs algébriques. Tentez donc d’éviter les références circulaires.

.

5. Quantité de formules complexes

Lorsque vous utilisez des formules complexes, comme par exemple RechercheV/Vlookup, RechercheH/Hlookup, Index/Index, Equiv/Match, Decaler/Offset, somme.si.ens/sumifs, et que vous appliquez ces formules sur un très grand nombre de lignes, vous alourdirez beaucoup votre fichier Excel. Si cette situation est inévitable, pensez à utiliser des macros (qui s’assureront d’effectuer les bons calculs et de copier coller ensuite les résultats en valeurs) ou encore à utiliser des outils comme PowerPivot (add-in Excel pour construire des cubes de données).

.

6. Cache

Si votre fichier Excel est excessivement lourd, il est possible que ce soit lié aussi à des problèmes de cache. Par exemple, si vous avez une base de données et que vous créez 10 tableaux croisés dynamiques à partir de cette base de données, si vous recréez chaque fois le tableau croisé dynamique à partir de la base de données, vous allez remplir la cache et alourdir votre fichier. Pour contourner ce problème, il faudrait plutôt copier le premier tableau croisé dynamique créé 9 fois et ajuster ensuite les paramètres de chacun selon ce que l’on veut présenter.

.

7. Items Excédentaires

Pensez à utiliser la fonction F5 pour retracer et supprimer tous les items non nécessaires (objets, commentaires, formats, etc.). Vérifiez notamment les formats conditionnels. J’ai audité des fichiers Excel où le format conditionnel avait été très malmené! Je ne sais trop comment mais une même cellule pouvait se retrouver avec 20 fois le même format conditionnel (appliqués les uns par-dessus les autres). Une fois le nettoyage effectué, le fichier roulait comme sur des roulettes!

.

8. Format XLSB

Dans les cas extrêmes, vous pouvez également penser à enregistrer vos fichiers Excel volumieux dans le mode binaire XLSB. Personnellement, je n’ai jamais eu à me rendre jusque-là mais ça demeure une option.

.

Et vous, que faites-vous pour réduire la taille de vos fichiers Excel?

.

Vous aimez cet article?

Je vous invite à le partager dans vos différents réseaux sociaux et également à laisser un commentaire plus bas.

.

Sophie Marchand, M.Sc., CPA, CGA

514-605-7112

 

Lire plus de Excel
16 Commentaires Poster un commentaire
  1. Pascal PERRIN
    sept 16 2013

    Je fais presque tout cela également !

    J’ajouterais que je déconseille de formater les cellules en sélectionnant toute la ligne ou toute la colonne ce qui évite de formater le maximum de lignes et de colonnes possibles avec Excel et de se retrouver au point 3 !…

    Je formate toutes les cellules de la feuille voire du classeur avec la même police et la même taille … Eh oui !…
    Je privilégie des noms d’onglets courts (2 ou 3 lettres suffisent! ) pour les formules ce qui revient au point 2 ! Et en plus les formules sont plus intelligibles…

    Je dimensionne automatiquement et au plus juste mes tableaux croisés dynamiques en agrandissant mes bases de données entre la premiere ligne et l’entête des colonnes, c’est très fiable en plus.

    Par contre j’évite les macros à tout prix, car leur maintenance est parfois délicate et leur portabilité assez faible d’une version ou d’un environnement à l’autre. De plus, la plupart des macros ignorent une fonction souvent déjà présente dans Excel.

    Voila comment "j’ai la ligne" avec Excel !

    Réponse
  2. Serge Turgeon
    sept 16 2013

    Bonjour Mme Marchand,
    Au point 3 lorsque vous dites <> comment fait-on? Est-ce simplement en les supprimant?
    Merci
    Serge Turgeon

    Réponse
    • sept 18 2013

      Bonjour Serge,

      C’est bien exact. Il s’agit de les supprimer.

      Au plaisir,

      Sophie

      Réponse
      • oct 30 2013

        Bonjour,

        Je ne suis pas sûr d’avoir compris cette méthode, j’ai un onglet de base que je copie et rajoute à chaque nouvelle entrée (fiche client).

        Cet onglet de base doit être volumineux car à chaque ajout de nouvelle fiche, mon Excel s’allourdit…j’en suis à 13 MO aujourd’hui (11 hier!)

        Merci

      • oct 31 2013

        Bonjour Charles,

        Je ne suis pas certaine de bien saisir votre question. Pourriez-vous être plus précis?

        Merci,

        Sophie

  3. lily
    sept 17 2013

    très intéressant merci

    Réponse
  4. sept 19 2013

    Bonjour,
    J’utilise des noms (Onglet Formules/Gestionnaire de noms) pour identifier des plages de cellules ou des tableaux. Ça me permet de garder des onglets avec des noms un peu plus parlant et simplifie le libellé de mes formules.

    Certaines données que je reçois sont des valeurs numériques qui correspondent à des textes (choices 0= USA, 1=Europe, etc.). Avant, j’importais la valeur numérique et j’utilisais recherchev (vlookup) pour faire la correspondance avec le texte. Maintenant, je travaille un peu plus les query d’extraction et j’importe directement la valeur en texte.

    Je révise annuellement mes plus gros fichiers. Je reçois souvent des demandes pour avoir des informations plus détaillées ou particulières. Je fais un tableau croisé supplémentaire, une ou deux formules de plus. Finalement, cette information n’est plus réutilisée, mais le tableau reste dans le fichier. Il faut s’assurer de ne pas avoir de colonnes inutiles, d’onglets inutilisés. Ménage annuel!
    Joanne

    Réponse
  5. Jenjacks KANGA
    sept 19 2013

    Merci pour ces infos bien utiles.

    Réponse
  6. David & Pascale
    oct 23 2013

    Merci pour l’astuce du ctrl+end ; Nous venons de gagner 1 bon méga sur un fichier qui vient de passer à 43Ko. Assurément une bonne opération, merci encore ;)

    Réponse
    • oct 23 2013

      Wow, génial!

      Très contente d’avoir pu vous diriger dans la bonne direction!

      Au plaisir,

      Sophie

      Réponse
  7. pouetre
    oct 31 2013

    Lorsque il y a plusieurs milliers de lignes à supprimer, comment faire pour faire la manip en seule fois?

    Réponse
  8. nov 27 2013

    comment faire ctrl+end ? end je clik sur les trois lettre ou il y a une touche pour faire cela?

    Réponse
    • nov 27 2013

      Bonjour Frédérique,

      Il y a une touche sur votre clavier sur laquelle il devrait être inscrit "end".

      Merci,

      Sophie

      Réponse
  9. nov 28 2013

    Bonjour ,

    J’ai enfin trouvé la touche "fin" et je me pose la question comment faire pour maintenant que je sais que ma dernière cellule active est la ligne "1048576" alors que je n’utilise que 20 lignes…
    Comment remonté à la 20 ligne…

    Réponse
    • nov 29 2013

      Bonjour Frédérique,

      Vous devez sélectionner toutes les lignes excédentaires et les supprimer et recommencer la même chose avec les colonnes.

      Ensuite, vous devez enregistrer le fichier.

      La dernière cellule active deviendra la dernière cellule utilisée.

      Au plaisir,

      Sophie

      Réponse

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s

Remarque : Le HTML est autorisé. Votre adresse email ne sera jamais publiée.

S'abonner aux commentaires

%d bloggers like this: