Excel dispose d’une panoplie de fonctions fort intéressantes, qui permettent de résoudre pratiquement n’importe quelle problématique. Nous vous en présentons régulièrement sur ce site. Toutefois, si les fonctions Excel sont une façon efficace d’obtenir des résultats à des problèmes mathématiques parfois complexes, il faut savoir bien les utiliser. Autrement, celles-ci pourraient introduire des problèmes de performance, liés au temps de calcul requis pour afficher les résultats. Voici donc quelques conseils pour tirer le maximum de vos fonctions.
Respecter une logique de haut en bas et de gauche à droite
Vos formules devraient toujours référer à des valeurs et/ou à des formules insérées dans des cellules en haut et/ou à gauche de leur emplacement. Autrement dit, vous devriez placer vos données et vos formules dans votre feuille, dans l’ordre où Excel fera les calculs.
Ici, la cellule D12, réfère à la cellule C12 à gauche et à la cellule D9 en haut.
Ici, la cellule C17 réfère à la cellule C5 en haut et à la cellule C12 en haut.
Ici, la cellule C27 réfère à la cellule C17 en haut et C22 en haut.
Ici, la cellule C33 réfèrent aux cellules B27 à B29 ainsi qu’aux cellules C26 à E29 et aux cellules C31 et C32, toutes en haut de la cellule C33.
En procédant de la sorte, non seulement vous augmenterez la vitesse de calcul de vos fichiers Excel mais en plus, il sera beaucoup plus facile pour un usager de suivre la logique de vos calculs.
Vous aimeriez créer des états financiers prévisionnels complets et dynamiques dans Excel, capables de se moduler à vos hypothèses, afin d’éclairer vos décisions d’affaires ? Apprenez les meilleures pratiques avec nos formations en modélisation financière. |
|---|
Éviter les références circulaires avec itérations dans vos fonctions
Je vous ai déjà parlé de mon appréhension à utiliser des références circulaires avec itérations, notamment à travers mon article Comment fonctionnent les calculs itératifs?. Je préfère de loin résoudre ces cas de façon algébrique pour éviter d’introduire de l’instabilité dans mes fichiers Excel. Qui plus est, les références circulaires avec itérations affectent la performance des fichiers Excel sous-jacents puisqu’Excel doit effectuer de très nombreux calculs avant de s’arrêter sur le résultat final et il doit également recalculer tout le fichier avant même de commencer ses calculs d’itérations.
Éviter les fonctions Excel volatiles
Les fonctions volatiles, comme Offset (Decaler), Indirect (Indirect), Rand() (Alea()) et Today (Aujourdhui), peuvent affecter la performance de vos fichiers Excel car elles augmentent significativement le nombre de formules qui doivent être recalculées à chaque changement apporté au fichier. Dans les petits fichiers, vous n’observerez pas de problèmes de performance mais dans des très gros fichiers complexes, vous serez confrontés à cette limite. Sachez que dans certains cas, il est possible de remplacer ces fonctions, tel qu’illustré ci-bas.
Manipuler judicieusement les fonctions Excel SI imbriquées
Lorsque vous utilisez une série de fonctions Si (If) imbriquées, vérifiez d’abord si vous ne pouvez pas remplacer votre formule par une autre formule impliquant notamment des Max, Min, Index, Et, Ou. Sinon, assurez-vous d’imbriquer vos Si par ordre de probabilité, en prenant soin d’insérer le Si le plus probable en premier et le Si le moins probable en dernier. Pourquoi? Parce qu’Excel cesse de lire la formule lorsqu’il trouve un résultat correspondant au Si sous-jacent. Ceci pourra donc diminuer le temps de calcul considérablement, dépendamment de la quantité de formules Si imbriquées utilisées.
Convertir les fonctions Excel inutilisées
Si votre fichier comprend des formules “intermédiaires” qui ont été créées simplement dans le but d’obtenir des données d’entrée qui ne changeront plus jamais, il est fortement recommandé de convertir les résultats en valeur. Mais avant toute chose, faites donc une copie de votre fichier juste au cas!
Éviter les fonctions Excel sur plusieurs lignes
Évidemment, la meilleure approche est de procéder par blocs de calculs et ne pas imbriquer trop de fonctions dans une même formule. On comprend facilement pourquoi.
Remplacer les fonctions Excel If(IsError) par IfError
Si vous avez accès à une version Excel qui dispose de la fonction Iferror (Sierreur), remplacez vos formules If (Iserror) / Si (Esterreur) par cette dernière, qui est beaucoup plus performante, surtout quand l’expression dans laquelle on doit vérifier l’erreur est une très grande formule, qui doit être répétée deux fois.
Dans l’exemple ci-dessous, on utilise une fonction Index, qui lit dans une autre feuille du même fichier Excel.
En supposant que D n’existe pas, la fonction ci-dessus ramènera un #N/A, tel qu’illustré ci-bas.
En supposant que vous utilisiez la fonction imbriquée If (Iserror) / Si (Esterreur), tel qu’illustré ci-bas…
… vous pourriez facilement la remplacer par une fonction IfError (SiErreur), tel qu’illustré ci-bas.
Utiliser efficacement les fonctions Excel de recherche (lookup)
Voir l’article: Excel: Utiliser les fonctions de recherche (lookup) adéquatement.
Éviter les fonctions Excel matricielles
Voir l’article: Excel: Quelques conseils concernant les fonctions matricielles (array formulas).
Éviter de faire référence à des plages de données plus grandes que nécessaire
Voir l’article: Excel: Évitez de faire référence à des plages de données plus grandes que nécessaire.
Fichier d’accompagnement
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
Formation complémentaire
Êtes-vous aussi bon que vous le pensez avec Excel ? Faites le test ! Vous pourrez ensuite suivre la formation Excel – Mise à niveau qui peut servir de mise à niveau pour les professionnels qui utilisent déjà Excel dans le cadre de leurs fonctions et qui désirent aller plus loin et elle est également un prérequis recommandé pour les formations Excel – Tableaux de bord (niveau 1), Excel O365 – Bienvenue dans l’ère moderne! et surtout, Excel – Modélisation financière (niveau 1).
Voici quelques commentaires d’apprenants ayant suivi la formation Excel – Mise à niveau :
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
