Lorsque je fais l’audit de certains fichiers Excel, que ce soit des modèles financiers, des tableaux de bord ou d’autres types d’outils de gestion, je constate que beaucoup d’utilisateurs font référence à des plages de données plus grandes que nécessaire. Dans un petit fichier Excel, avec un niveau de complexité sommaire, cette pratique n’est pas nocive en soi. Par contre, dans des fichiers d’envergure, cette pratique peut entraîner des problèmes de performance importants. Cet article vise donc à présenter les cas populaires où les usagers d’Excel font référence à des plages de données plus grandes que nécessaire, à préciser dans quels cas il faudrait proscrire cette pratique et à fournir des façons alternatives d’accomplir la même tâche.
Quand et pourquoi fait-on référence à des plages de données plus grandes que nécessaire?
Il arrive fréquemment que des usagers d’Excel rédigent des formules qui réfèrent à des plages de données plus grandes que nécessaire, lorsque ces plages de données évoluent au fil du temps (des lignes et/ou des colonnes s’ajoutent). Au lieu de modifier manuellement chaque fois la plage de référence, au prix d’efforts laborieux, ces usagers font référence à une plage de données plus grande que nécessaire, parfois même toute la colonne, la ligne ou même la feuille Excel.
- Référer à une plage de données plus grande que nécessaire sans pour autant faire référence à une colonne, une ligne ou une feuille entière est plus performant que d’y référer en entier mais cette approche comporte un niveau de risque d’erreurs supplémentaire. En effet, si les données sous-jacentes finissent par transcender la plage de données de référence, les formules continueront de fournir des résultats mais ces résultats seront erronés. Le problème, c’est que ce type d’erreur est souvent difficile à percevoir à l’oeil nu et les gens continuent donc de travailler avec les mauvaises données.
- Référer à une colonne, une ligne ou une feuille entière empêche les risques d’erreurs de calculs et cette technique est donc plus populaire que la première. Toutefois, des problèmes de performance sont à prévoir dans un fichier le moindrement complexe et/ou volumineux.
Donc, dans les deux cas, on peut rencontrer un problème important, soit un problème d’inexactitude dans le premier cas ou un problème de performance dans le deuxième cas.
Qu’entend-on par une référence à une plage de données plus grande que nécessaire?
On parle de plage de données plus grande que nécessaire lorsque l’on fait référence à un plus grand ensemble de données que nécessaire, dans le but d’englober éventuellement les nouvelles données qui seront ajoutées dans la plage de données de référence. Dans l’exemple ci-bas, on cherche à obtenir les ventes par #sku. Pour ce faire, on utilise la fonction Sumif (Somme.si) et on cherche le critère (#sku) dans la colonne D au complet et on demande à Excel de faire la somme des ventes correspondantes dans la colonne H au complet. On agit de la sorte car on sait pertinemment que la plage de données sous-jacente, qui se termine en ce moment à la ligne 21 et à la colonne H, évoluera au fil des jours pour comprendre davantage de lignes (nouvelles transactions de ventes) et possiblement de nouvelles colonnes (ajout de nouvelles dimensions ou de nouvelles mesures). Ainsi, la plage de données de référence peut évoluer sans problème car la formule référera toujours à l’ensemble des données sous-jacentes et fournira donc toujours le bon résultat. Toutefois, tel que mentionné en introduction, ce fichier pourra un jour être confronté à un problème de performance.
Quand est-ce que ça devient problématique?
Bien sûr, pour des questions d’exactitude, faire référence à une plage de données plus grande que nécessaire, surtout s’il s’agit de la plage de données maximale, ça peut être rassurant pour un usager d’Excel. Mais cette pratique compte aussi des désavantages importants. Voici ce qu’il faut savoir:
- Plusieurs fonctions Excel, comme la fonction Sumif (Somme.si) que nous avons présentée au point précédent, calcule efficacement les références aux plages de données plus grandes que nécessaire car elles reconnaissent automatiquement la dernière cellule utilisée dans la colonne.
- D’autres fonctions Excel, comme la fonction Sumproduct (Sommeprod) ne reconnaissent pas automatiquement la dernière cellule utilisée dans la colonne et de ce fait, recalcule chacune des cellules de la colonne, même les cellules vides (donc pour les versions d’Excel à 1 million de lignes, ça peut dramatiquement augmenter les temps de calcul).
- Nous avons vu dans un article précédent que le même sort était réservé aux fonctions matricielles (voir article Excel: Quelques conseils concernant les fonctions matricielles (Array formulas)).
- Et c’est la même chose également pour les fonctions définies par les usagers (consulter l’article Excel: Créez vos propres fonctions pour savoir comment écrire une fonction personnalisée), mais on pourrait par contre, programmer ces fonctions afin qu’elles reconnaissent la dernière cellule active
Quelles sont les alternatives?
Option mettre sous forme de tableaux
Depuis la version 2007 d’Excel, les usagers ont accès à la l’option « Mettre sous forme de tableau« , ce qui permet aux plages de données mises sous forme de tableaux, de prendre de l’expansion en termes de lignes et de colonnes au fil du temps sans entraîner de problèmes de références dans d’éventuelles formules en lien avec ces plages de données. L’option « Mettre sous forme de tableau » est une de mes fonctionnalités préférées des dernières versions d’Excel (j’en ai parlé à plusieurs reprises sur ce blogue) et comporte plusieurs avantages:
- Plus performant qu’une référence à une plage de données plus grande que nécessaire
- Pas d’erreurs de rérérencement
- Référence à des noms de colonnes ou de tables significatifs donc plus grande facilité à comprendre le sens des formules rattachées
- Les formules incluses dans le tableau, soit la plage de données de référence, sont automatiquement appliquées aux nouvelles données
Plage de données dynamiques
Nous avons vu dans l’article Excel: Créer une base de données dynamique sans l’option Mettre sous forme de tableau, comment créer une base de données dynamique, sans faire intervenir l’option Mettre sous forme de tableau, en utilisant les fonctions Offset (Decaler) et Counta (Nbval) pour définir un nom de plage de données. Cette approche est acceptable, mais utilise une fonction volatile, soit Offset (Decaler). Il faut donc être prudent également avec ce type de formule, qui peut elle aussi connaître des problématiques de performance. De plus, le Counta (Nbval) doit examiner toutes les lignes, donc cette portion de la formule peut également introduire des problématiques de performance. Il est possible de minimiser ces problèmes de performance en isolant la portion de la formule composée par le Counta (Nval) dans une cellule séparée et ensuite référer à cette cellule dans la formule de la plage de données dynamique. Cette portion ne serait ainsi calculée qu’une seule fois.
Power Query, Power Pivot et VBA
Bien entendu, vous pouvez également créer des plages de données dynamiques à l’aide de VBA ou encore utiliser les add-in Power Query et Power Pivot qui définissent vos données sous forme de tableau par défaut.
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 info@lecfomasque.com