Excel: Tirez le maximum de vos fonctions

Publié le 18 mai 2015
par Sophie Marchand M.Sc., CPA, CGA, MVP
Ordre des calculs Excel

Excel: Tirez le maximum de vos fonctions

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.

Ordre des calculs Excel

.

Ici, la cellule C17 réfère à la cellule C5 en haut et à la cellule C12 en haut.

Ordre des calculs Excel

.

Ici, la cellule C27 réfère à la cellule C17 en haut et C22 en haut.

Ordre des calculs Excel

.

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.

Ordre des calculs Excel

 

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

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.

Offset (Decaler) vs Index

 

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-dessous, on utilise une fonction Index, qui lit dans une autre feuille du même fichier Excel.

Iferror / Sierreur

.

En supposant que D n’existe pas, la fonction ci-dessus ramènera un #N/A, tel qu’illustré ci-bas.

Iferror / Sierreur

.

En supposant que vous utilisiez la fonction imbriquée If (Iserror) / Si (Esterreur), tel qu’illustré ci-bas…

Iferror / Sierreur

.

… vous pourriez facilement la remplacer par une fonction IfError (SiErreur), tel qu’illustré ci-bas.

Iferror / Sierreur

 

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.

 


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 :

François Le May
Écrit il y a 5 mois
Il était grandement temps que je la suive!

Mon efficacité au travail et ma capacité à faire des analyses rapidement vont être grandement améliorées.

Mélanie Bonneau
Écrit il y a 6 mois
Adaptée pour le monde des CPA

Je trouve que cette formation est la plus adaptée pour le monde des CPA. En d'autres mots, j'apprends davantage ici que dans une formation générale de type "débutant - intermédaire - avancé" et je trouve que c'est plus concret pour mes activités quotidiennes. Merci!

Isabelle Madé
Écrit il y a 8 mois
Excellente formation!

Le découpage des sections est parfait, on peut prendre le tout à petites bouchées à notre rythme. J'utilise Excel depuis très longtemps et comme bien des gens j'ai appris sur le tas. Cette mise à niveau m'a permis d'apprendre ou mieux comprendre certaines formules et fonctionnalités que j'aurais dû maîtriser bien avant! Je suis prête pour la suite...

Melissa Forgues
Écrit il y a 9 mois
J'ai adoré!

Je croyais faire cette formation pour rien puisque je travaille avec Excel et prit quelques cours dans le passé. Je croyais que je maîtrisais toute la base! Ayayaye! Je me suis trompé! La formatrice donne énormément de trucs et des informations jusque là inconnue pour moi, jamais montré en salle de formation avec d'Autres compagnies... Déjà là, je vais sauvé beaucoup de temps. OMG! Bravo!

Sylvie Giroux
Écrit il y a 2 ans
Captivant

La formatrice expliquait très clairement chaque exemple et avait un ton de voix dynamique. En suivant une formation en ligne, je craignais de m'endormir, mais non! J'ai été captivée et intéressée tout au long de ma formation.


CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top