Modélisation financière dans Excel: que faire si la taille du fichier est hors de contrôle


Posted on 2017-09-08 12:41:28 category:Logiciel


Lorsque vous commencez à obtenir une bonne quantité de données dans votre modèle financier, il est assez facile de se retrouver avec un énorme fichier Excel qui prend beaucoup de temps à calculer, ce qui rend difficile l'examen ou le partage avec d'autres.

Si la grande taille du fichier est due à un grand nombre de lignes (par exemple, plus de 100 000), envisagez d'utiliser Power Pivot pour stocker les données à la place.

Si vous trouvez que votre modèle devient incontrôlable (et que vous utilisez Excel standard, sans l'aide de Power Pivot ou de tout autre complément), voici quelques conseils éprouvés que vous pouvez utiliser pour conservercette taille de fichier vers le bas:

  • Supprimez toute mise en forme inutile. Les couleurs et la mise en forme ne doivent s'appliquer qu'à la plage nécessaire, et non à une ligne ou une colonne entière.Évitez le formatage manuel et utilisez plutôt des styles.

    Lorsque vous effacez des cellules que vous n'utilisez plus, vous le faites probablement en sélectionnant la cellule et en appuyant sur la touche Suppr.Cette action efface le contenu et les formules, mais n'efface pas la mise en forme.Si vous pensez qu'il s'agit d'un problème, vous pouvez appuyer sur Ctrl + A pour sélectionner toutes les cellules;puis sur l'onglet Accueil du ruban, dans la section Édition, cliquez sur la liste déroulante Effacer et sélectionnez Effacer les formats.

  • Assurez-vous que les formules ne font référence qu'à la plage dont elles ont besoin (sans sélectionner la ligne ou la colonne entière). Si vos formules référencent plus de cellules que nécessaire, cela utilisera plus de mémoire.Par exemple, utilisez la formule= SUM (A1: A1000) pour autoriser des lignes supplémentaires au lieu de= SUM (A: A).Vous pouvez également mettre en forme les données sous forme de tableau et faire référence aux plages de tableau à expansion automatique dans votre formule.
  • Supprimez (ou au moins vérifiez la taille de) tous les logos ou images que vous utilisez. Un seul fichier JPG inséré dans un modèle peut facilement ajouter 10 Mo à une taille de fichier Excel.
  • Évitez les tableaux croisés dynamiques. Les tableaux croisés dynamiques grugent vraiment la mémoire.Si vous avez plusieurs tableaux croisés dynamiques, assurez-vous qu'ils utilisent la même source de données et ne créent pas une toute nouvelle source de données.
  • Supprimez les liens externes vers d'autres fichiers. Pour vérifier s'il existe des liens externes dans votre modèle, accédez à l'onglet Données du ruban et cliquez sur Modifier les liens dans la section Connexions.Si le bouton Modifier les liens est grisé, cela signifie qu'il n'y a pas de liens externes.S'ils existent, cliquez sur le bouton et lorsque la boîte de dialogue Modifier le lien apparaît, cliquez sur Casser les liens pour coller les données de tous les liens externes en tant que valeurs, et les liens externes seront supprimés.Si vous devez absolument utiliser des liens externes, ouvrez le fichier source en même temps;cela accélérera les choses.
  • Vérifiez les redondances dans les entrées et les calculs. Parfois, un modèle évolue, et il peut y avoir des pièces qui ne sont plus utilisées ou des pièces dont les informations ont changé.
  • Évitez les fonctions volatiles. Certaines des fonctions volatiles les plus couramment utilisées sont OFFSET, INDIRECT, RAND, NOW, TODAY, ROW et COLUMN.La surutilisation de ces fonctions particulières dans votre modèle peut vraiment ralentir vos calculs.Si vous devez les utiliser, essayez de limiter leur apparence dans le modèle.Par exemple,= AUJOURD'HUI () est très utile pour indiquer la date d'aujourd'hui, mais au lieu de l'utiliser plusieurs fois dans une formule, placez-la dans une seule cellule et renvoyez continuellement à cette cellule pour la date d'aujourd'hui.
  • Assurez-vous que vous n'utilisez pas le type de fichier XLS. XLSX est un type de fichier beaucoup plus compact, et vous verrez une énorme différence de vitesse et de taille de fichier si vous l'utilisez.XLSB est un type de fichier de classeur binaire Excel qui est encore plus compact que XLSX.

Si vous avez essayé tous ces conseils et que vous rencontrez toujours des problèmes, envisagez de passer le calcul en manuel (vous pouvez le faire en cliquant sur l'onglet Formules du ruban, en allant dans la section Calcul et en sélectionnant le calcul manueloption).Appuyez ensuite sur F9 uniquement lorsque vous devez recalculer.Vous saurez si quelque chose doit être calculé, car vous verrez Calculer dans la barre d'état.

Enfin, en dernier recours, une petite astuce consiste à laisser une cellule en haut de la colonne avec le lien en direct et à coller toutes les autres cellules en tant que valeurs.Copiez la cellule vers le bas et recalculez lorsque vous devez actualiser.Ce n'est certainement pas une option privilégiée, car elle prend du temps et est sujette à des erreurs, mais c'est pourquoi il s'agit d'un dernier recours.

À propos de l'auteur du livre

Danielle Stein Fairhurst est la principale modélisatrice financière de Plum Solutions avec de nombreuses années d'expérience dans le domaine.Son groupe LinkedIn «Modélisation financière dans Excel» compte plus de 40 000 abonnés.Elle est également l'auteur de plusieurs articles et autres livres ainsi que d'un bulletin d'information sur la modélisation financière.

82241