Il existe une version révisée de ce poste ici.
Excel est un excellent outil pour la conception et la fourniture de tableaux de bord/rapports (c'est pourquoi nous avons créé notre addin en premier lieu), mais il y a un piège caché au niveau des performances :
DECALER, MAINTENANT, AUJOURDHUI, CELLULE, INDIRECT, INFO, ALEA
Si vous avez déjà utilisé l'une de ces formules, vous avez peut-être remarqué qu'à chaque fois que vous modifiez une cellule, ou que vous pliez ou dépliez un tableau, Excel recalcule. Cela est dû au fait que toutes ces formules sont VOLATILES. Dès vous utilisez l'une d'entre elles, Excel est contraint de tout recalculer, et il y a une explication à cela.
Comme Decaler et Maintenant sont des formules fréquemment utilisées, examinons quelques approches alternatives permettant d'optimiser les performances.
La fonction Decaler
C'est de loin, parmi les formules dangereuses, celle que nous rencontrons le plus fréquemment. Voici sa syntaxe:
=DECALER (référence; lignes; colonnes; hauteur; largeur)
Renvoie une référence à une plage qui est un nombre donné de lignes et de colonnes
à partir d'une référence donnée.
Nous les considérons généralement comme faisant partie d'une définition de plage nommée pour les données sources des graphiques - elle permet de modifier automatiquement le nombre de lignes/colonnes des données des graphiques, ce qui n'est pas inhabituel lorsqu'il s'agit de créer des rapports (en particulier lorsqu'un rapport contient des filtres ou des tranches définis par l'utilisateur). En voici un exemple :
Dans cet exemple simple, on peut saisir le nombre de mois à afficher dans le graphique. En réalité, le nombre de mois à afficher sera probablement déterminé par les données disponibles pour les critères sélectionnés. La capture d'écran montre déjà le problème : le graphique est configuré pour afficher un maximum de 12 mois, mais il n'y a que 3 mois de données disponibles.
Une approche possible est d'utiliser la formule 'DECALER' pour définir automatiquement la zone que le graphique doit utiliser. Nous pourrions ainsi créer une plage nommée 'myMonths' telle que...
...puis remplacer la plage de données source du graphique par cette zone nommée.
Le graphique représente maintenant 3 mois, mais il sera automatiquement mis à jour pour indiquer le nombre de mois requis :
Cependant nous avons maintenant utilisé une formule VOLATILE. Bien que ce soit un classeur simple, nous avons placé Excel dans dans position qui l'oblige à tout recalculer tout le temps.
Analysons pourquoi Excel doit se comporter ainsi en examinant une formule très simple qui illustre la façon dont Excel gère les calculs.
Considérons la formule :
C1 =A1 + B1
Nous pouvons voir que C1 dépend de A1 et B1 - donc à chaque fois que la valeur de l'une de ces cellules change, C1 devra être recalculé afin d'afficher la la valeur correcte. Excel connaît cette dépendance car il maintient un arbre des dépendances ; il sait quelles cellules doivent être recalculées chaque fois qu'une autre cellule change. C'est une méthode de travail très efficace, car si un classeur comporte des milliers de formules, mais qu'une seule valeur change, et que seulement 10 formules dépendent de cette valeur, alors seulement ces 10 là seront calculées.
De même, si C1 contenait: C1 = SOMME(A1:A20)
C1 =Somme(A1:A20)
Nous saurions que que C1 dépend de l'une des cellules de A1:A20, tout comme Excel.
Mais que se passerait-il si C1 contenait : C1 = SOMME(DECALER(A1;0;0;B1;1)
C1 =Somme(Compensation(A1,0,0,B1,1))
De quelles cellules le C1 dépend-il ? En un coup d'œil, on pourrait dire A1 et B1.
mais B1 contient le nombre 20, donc en fait C1 dépend de A1:A20 et B1 (j'ai mis en évidence les cellules supplémentaires qui sont dépendantes) :
Tout comme nous ne pouvons pas voir d'un seul coup d'œil de quelles cellules C1 a besoin - Excel ne peut pas non plus en décider facilement. Par conséquent, la compensation est volatile car, si elle ne l'était pas, Excel risquerait de mettre tellement de temps à déterminer si elle doit être calculée qu'il pourrait tout aussi bien toujours la calculer.
Dans cet exemple, il existe une solution simple pour éviter ce problème, via la fonction INDEX. Voici sa syntaxe (attention, il y a 2 façons d'utiliser cette fonction, dans notre cas nous utiliserons la syntaxe par référence) :
=Index(référence,numéro_rangée,numéro_colonne,numéro_zone)
Renvoie une valeur de référence de la cellule à l'intersection d'un
en particulier ligne et colonne, dans une fourchette donnée
La grande différence, par rapport à l'utilisation de la fonction Decaler, est que comme la fonction Index renvoie une seule référence de cellule, vous devez l'utiliser au sein d'une plage de cellules de type 'A1:Index(...)'. Voici donc la fonction somme, utilisant la fonction 'INDEX' au lieu de la fonction 'DECALER': C1 = SOMME(A1:INDEX(A1:A20;B1;0))
C1 = SOMME(A1:INDEX(A1:A20,B1,0))
La formule indique simplement que la plage que nous voulons commence en A1 et s'arrête au nombre de lignes défini dans B1. La différence essentielle est qu'avec la fonction 'index', la plage A1:A20 est connue d'Excel, ce qui peut se vérifier visuellement lors de l'édition de la formule.
Nous pouvons maintenant mettre à jour la zone nommée 'myMonths' pour utiliser la fonction 'INDEX' au lieu de 'DECALER':
=Feuille1!$C$6:INDEX(Feuille1!$C$6:$C$17,Feuille1!$D$2,0)
Les fonctions 'Maintenant' et 'Aujourdhui'
Les fonctions "Maintenant" et "Aujourd'hui" renvoient toutes deux la date du jour dans une cellule. La date du jour est souvent utilisée pour piloter / automatiser le rafraîchissement du rapport dès l'ouverture en fonction de la date. Bien que ce besoin semble légitime, ce que la plupart des gens souhaitent en réalité, c'est que le rapport soit exécuté pour les dernières données disponibles, ce qui peut signifier des choses différentes en fonction de l'activité ou du contexte :
- Hier (si les données sont chargées toutes les nuits)
- Le dernier jour ouvrable (si les données sources ne sont chargées que pendant la semaine de travail et pas le week-end)
- Le mois en cours, etc.
La solution la plus simple est de laisser les données déterminer la date à utiliser - si nous utilisons une grille ou une table de requête XLCubed pour récupérer les données, nous pouvons simplement mettre en place une grille pour récupérer les jours/mois où il y a des données :
Utilisation de l'option de tri pour Inverser l'affichage des données, avec les périodes les plus récentes en premier
Une fois l'option 'Actualiser à l'ouverture' cochée dans les propriétés du tableau, la cellule A6 affichera toujous la période disponible la plus plus récente et les autres périodes pourront découler de celle là.
Soit dit en passant, la version 6.2 de XLCubed introduit une nouvelle option dans les Slicers pour sélectionner automatiquement la date la plus récente du membre lorsque le rapport est chargé :
