Piloter le graphique Excel Min/Max de la gamme

Comment déterminer les valeurs min et max d'un axe à partir d'une plage Excel ? C'est l'une des questions les plus fréquemment posées à propos d'Excel et, à chaque nouvelle version, je suis toujours étonné que cette fonctionnalité n'ait pas été ajoutée au produit de base.

C'est un scénario très courant, vous construisez un graphique linéaire et tout semble correct jusqu'à ce qu'Excel décide soudainement de mettre la valeur minimale à 0, tous les détails sont perdus et vous êtes passé d'un bel ensemble de lignes détaillées à un mélange de couleurs de quelques pixels de haut.

Il existe des techniques assez sophistiquées qu'Excel utilise pour déterminer le minimum et le maximum à utiliser, mais parfois nous voulons simplement les régler sur une valeur particulière (normalement autre chose que 0 !).

Voici un ensemble de chiffres assez simple et le graphique qui en résulte que nous obtenons à partir d'Excel (avec toutes les valeurs par défaut).

 

 

 

 

 

 

 

 

 

 

Tout cela a l'air bien, mais changeons la valeur de "C" de lundi à 86, et regardez ce qui se passe maintenant :

 

 

 

 

 

 

 

 

Excel a appliqué ses règles et a décidé que le 0 est un bon point de départ pour le graphique, mais dans ce cas, je perds beaucoup de détails et je me retrouve avec toutes les lignes regroupées.

Nous pourrions, bien sûr, changer la valeur minimale de l'axe pour quelque chose d'un peu plus raisonnable, donc nous utiliserons l'option Format Axis pour fixer une valeur minimale de 84 :

 

 

 

 

 

 

 

 

 

 

 

 

Ça a l'air mieux !

 

 

 

 

 

 

 

Les numéros de base avaient été saisis manuellement, donc pouvoir taper une valeur fixe dans l'axe minimum est bien, mais que faire si les numéros proviennent d'une base de données cube ou Sql ? Ne serait-il pas vraiment utile de pouvoir piloter la valeur minimale à partir d'une plage ; je peux changer à peu près tout ce qui concerne le graphique mais après tant d'années et tant de versions différentes, je ne peux toujours pas le faire.

Heureusement pour moi (et pour nos clients !), nous disposons déjà d'un complément Excel, de sorte que nous pouvons simplement ajouter la fonctionnalité nécessaire à cette fin en utilisant l'une des nouvelles formules de la version 6.5 :

XL3SetProperty( ObjectType, ObjectName, Property, Arg1, [Arg2],..., [Arg27] )

La formule pour faire varier l'axe du graphique est simple :

=XL3SetProperty("Chart", "Chart 1", "YMin",$C$1)

Les autres options sont :

PropriétéDescriptionValeur
"YMin" ou "YMax"Fixe les limites de l'axe Y.Numérique
"Y2Min" ou "Y2Max"Fixe les limites de l'axe Y2.Numérique
"XMin" ou "XMax".Fixe les limites de l'axe X.Numérique
"X2Min" ou "X2MaxFixe les limites de l'axe X2.Numérique

Nous pouvons enfin établir des rapports (et les publier sur le web), confiants que, quels que soient les données ou les critères choisis, nous n'allons pas nous retrouver avec un graphique linéaire commençant à 0 et regroupant toutes les lignes.

Cette formule peut également être utilisée pour modifier divers aspects de nos propres grilles, trancheurs et petits multiples basés sur les valeurs des cellules d'excel. Le genre de choses que nos clients et nous-mêmes voulions réaliser étaient des choses comme

  • Déplacer les dimensions entre les axes
  • Modifier les types de sélection des membres
  • Modifier diverses propriétés de la grille en fonction de différentes formules

Voyons comment la formule fonctionne pour réaliser certaines de ces choses :

=XL3SetProperty("Grid", "My Grid", "HierarchiesOnColumns", "[Products]", "[Regions]", $a$1)

Déplacer le produit, la région et toute autre hiérarchie dans $a$1 vers les colonnes (je pourrais utiliser un slicer ou un drop down pour mettre à jour $a$1 afin de permettre à l'utilisateur de passer d'une hiérarchie à l'autre)

=XL3SetProperty("Grid", "My Grid", "RemoveEmptyRows",$b$1)

Permet d'afficher ou non les lignes sans données sur la base de la valeur de $b$1

Si vous pensez qu'il serait utile de piloter certains aspects d'Excel à partir d'une gamme, faites-le nous savoir !