Ceci est une version révisée de l'article : Attention : Excel peut devenir volatil
Excel est un outil excellent pour la conception et distribution 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)
Cette formule retourne une référence à une cellule, décalée d'un nombre de lignes et de colonnes, à partir d'une cellule d'origine. La référence renvoyée peut être une seule cellule ou une série de cellules. Vous pouvez spécifier le nombre de lignes et le nombre de colonnes à renvoyer.
Microsoft, Fonction DECALER
Exemple - Plage de cellules dynamique
Nous observons souvent l'utilisation de cette formule pour la définition de la plage des cellules source d'un graphique. Elle permet, dans ce cas, de contrôler automatiquement le nombre de lignes ou de colonnes à afficher dans le graphique, ce qui est un besoin légitime dès qu'il s'agit de créer des rapports, en particulier lors de l'utilisation de filtres interactifs. 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.
Que se passe-t-il ?
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 = SUM(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 = SUM(OFFSET(A1,0,0,B1,1))
Dans ce cas, de quelles cellules dépend C1 ? Au premier coup d'œil, on pourrait dire de A1 et B1.
Mais, B1 contenant le chiffre 10, C1 dépend en fait des cellules de la plage A1:A20 et de B1 (les cellules concernées sont illustrées en jaune) :
Puisque nous nous ne pouvons pas visualiser, en éditant la formule, les cellules donc C1 a besoin pour se calculer, Excel non plus ! Cela siginifie que la formule DECALER est volatile. Etant donné qu'Excel risquerait de mettre beaucoup trop de temps à déterminer, dès qu'une modification a lieu sur la feuille,si C1 a besoin d'être recalculée, Excel recalcule systématiquement tout afin d'assurer la validité de tous les calculs.
La solution
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; no_ligne; no_colonne; [no_zone])
Renvoie la référence de la cellule située à l’intersection d’une ligne et d’une colonne déterminées. Si la référence est composée de sélections non adjacentes, vous pouvez sélectionner la sélection à consulter.
Microsoft, Fonction INDEX
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 = SUM(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':
=Sheet1!$D$5:INDEX(Sheet1!$D$5:$D$16,Sheet1!$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 et la plus sûre est le plus souvent de s'appuyer sur les données existantes pour déterminer la période à utiliser. Celle-ci peut être récupérée via un tableau, un filtre ou une formule.
Dans un tableau - Inverser l'ordre des membres
Si l'on utilise un tableau ou une table XLCubed pour récupérer les données, il est possible d'utiliser un filtre simple permettant de récupérer tous les jours/mois où il y a des données, puis de les trier dans l'ordre inverse, de sorte à ce que la période la plus récente s'affiche en premier.
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à.
Filtre / Segment - Afficher le dernier membre disponible
Les segments / filtres peuvent être configurés de manière à ce la première valeur affichée soit le dernier membre disponible dans une dimension, par exemple la dernière date ou le dernier mois dans une hiérarchie contenant des périodes.
XL3MemberNavigate
La formule Xl3MemberNavigate peut être utilisée pour récupérer le dernier membre d'une hiérarchie à un niveau donné. Dans le ruban XLCubed, un assistant est disponible pour faciliter la généreration de la bonne syntaxe.
Cette formule permet notamment de préciser un décalage. Il est par exemple possible de récupérer le dernier membre disponible, par exemple, la date du jour, puis d'appliquer un décalage de 1 afin d'obtenir la veille.
Autres options
Dans certains cas, la récupération du dernier membre d'une hiérarchie ne permet pas d'obtenir la date du jour, par exemple lorsque la hiérarchie contient des dates futures. Dans ce cas, il est généralement préférable de créer l'ensemble des jours actuels/précédents dans le cube en tant que 'Jeu nommé' ('Named Set'). DansXLCubed, un 'jeu nommé' du cube est exploitable dans un tableau ou un segment / filtre . Si un tel 'jeu nommé' ne peut être créé dans le cube, il est également possible de s'appuyer sur un calcul personnalisé pour obetnir le même résultat.
