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

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 le rend difficile à réviser ou à partager avec d'autres personnes.

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

Si vous trouvez que votre modèle est hors de contrôle (et que vous utilisez le standard Excel, sans l'aide de Power Pivot ou de tout autre add-in), voici quelques-uns de ceux qui ont été testés Astuces que vous pouvez utiliser pour réduire la taille du fichier:

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

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

• Assurez-vous que les formules ne référencent que la plage dont elles ont besoin (ne pas sélectionner la ligne ou la colonne entière). Si vos formules font référence à plus de cellules qu'elles n'en ont besoin, cela nécessitera 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 la forme d'une table et faire référence aux plages de tables qui se développent automatiquement dans votre formule.
• Supprimez (ou au moins vérifiez la taille de) les logos ou les 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 mâchent vraiment de la mémoire. Si vous avez plusieurs tableaux croisés dynamiques, assurez-vous qu'ils utilisent la même source de données et n'en créez pas une nouvelle.
• Supprimer 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 Interrompre 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 va accélérer les choses.
• Vérifier les redondances dans les entrées et les calculs. Parfois, un modèle évolue, et il peut y avoir des parties qui ne sont plus utilisées ou des parties où l'information a 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, = TODAY () est très utile pour donner la date d'aujourd'hui, mais au lieu de l'utiliser plusieurs fois dans une formule, placez-la dans une seule cellule et faites référence 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 encore plus compact que XLSX.

Si vous avez essayé toutes ces astuces et que vous rencontrez toujours des problèmes, envisagez de passer en mode manuel (vous pouvez le faire en cliquant sur l'onglet Formules du ruban, en allant à la section Calcul) et en sélectionnant l'option de calcul manuel). 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, un petit truc consiste à laisser une cellule en haut de la colonne avec le lien direct et à coller toutes les autres cellules comme valeurs. Copiez la cellule et recalculez lorsque vous avez besoin d'actualiser. Ce n'est certainement pas une option privilégiée, car elle prend beaucoup de temps et est sujette aux erreurs, mais c'est pourquoi c'est un dernier recours.