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 à 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.
Éviter les références circulaires avec itérations
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 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 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 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 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 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-bas, 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 de recherche (lookup)
Voir l’article: Excel: Utiliser les fonctions de recherche (lookup) adéquatement.
Éviter les fonctions 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.
NOTRE OFFRE DE FORMATIONS
Le CFO masqué vous offre 13 formations, réparties dans 4 catégories: Tableaux de bord, Modélisation financière, Finance corporative et Analyse et modélisation de données. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.
Consulter la liste des formations offertes
Pour info: 514-605-7112 ou [email protected]
Jeu de briques
Snake
Pacman
Bubble