Contact: 514-605-7112 / info@lecfomasque.com

Excel : Évitez de faire référence à des plages de données plus grandes que nécessaire

Publié le : 11 mai 2015

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.

Plage données excédentaires

 

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
Pour tout savoir sur les tableaux Excel, rendez-vous sur le site exceltables.com et jetez un coup d’oeil à leur guide.
Excel tables

 

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.

Plage données dynamique

 

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


Microsoft Most Valuable Professional

Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Finance corporative et Compléments avancés. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et ils 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.

 

Découvrez quelles formations vous conviennent

Pour info: 514-605-7112 ou info@lecfomasque.com

 

Sophie Marchand

Sophie Marchand, M.Sc., CPA, CGA, MVP, est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA. Elle est également MVP Office Apps and Services (Excel) et MVP Data Platform (Power BI). Le titre de MVP est un titre honorifique remis par Microsoft et signifie « Most Valuable Professional ». Il est attribué à des experts de solutions Microsoft qui se distinguent non seulement par leurs compétences mais également par leur implication dans la communauté. Seuls 4 professionnels possèdent cette double-qualification dans le monde. Mme Marchand cumule de nombreuses années d’expérience dans le milieu des affaires et se spécialise en modélisation financière et en intelligence d’affaires avec Excel et Power BI. Après avoir longtemps offert ses services comme consultante, elle se consacre désormais à la formation, sous toutes ses formes (formations en classe, formations en ligne, formations en entreprise, webinaires, animation de groupe d'usagers, rédaction d'articles de blogue, animation de forum en ligne et plus encore). Vous pouvez d'ailleurs la retrouver à la tête du groupe d'usagers Montreal Modern Excel and Power BI.

Laisser un commentaire