Dans les rapports financiers, il est très courant de souhaiter un affichage mensuel du réalisé et du prévisionnel sur l'ensemble de l'année. La plupart du temps, l'on souhaite afficher le réalisé pour les mois passés et compléter les mois futurs avec les données du prévisionnel, afin que la précision du total annuel s'affine au fur et à mesure de l'avancement dans l'année.
Comme Excel est un environnement très flexible, ce type de rapport est souvent construit en utilisant un mélange de copier-coller et de formules. Bien que cette approche permette d'atteindre l'objectif, cette façon de faire nécessite un grand nombre de manipulations à chaque mise à jour mensuelle.
Le défi
Pour construire ce type de rapport, le plus grand défi réside souvent dans le fait que les données du réalisé et du prévisionnel sont stockées dans deux mesures différentes ou dans deux membres d'une hiérarchie de type "scénarios". Avec la plupart des outils clients, le réalisé et le prévisionnel seront tous deux affichés pour chacun des mois pour lesquels des données existent. Ce que l'on cherche à afficher est cependant différent : on souhaite voir le réalisé existant et compléter les mois restants avec le prévisionnel.
Ce que l'on souhaite réellement en réalité c'est un rapport dynamique qu'il ne soit pas nécéssaire de modifier ou de recommencer chaque mois. Dans un article de blog précédent, il y a quelques années, nous avions montré comment créer ce genre de rapport en s'appuyant sur la fonctionnalité "Garder / Exclure de l'affichage". Bien que cette approche soit toujours valide, il existe désormais une façon plus intuitive d'obtenir le même résultat grâce à la fonctionnalité "Conserver - Conserver les membres et créer un jeu dynamique".
La méthode
(Notez qu'il y a plusieurs façons pour paramétrer, directement dans le cube, quel est le dernier mois avec des données disponibles et complètes. Si ce paramétrage existe dans le cube, vous pouvez en tirer parti en utilisant une formule XL3Member(), et vous pouvez, dans ce cas, sauter l'étape 1 ci-dessous).
1. Segment / Filtre XLCubed pour choisir le dernier mois complet
Insérez un segment / filtre XLCubed pour les mois de votre cube. Ensuite, paramétrez le segment / filtre afin que l'élément sélectionné soit automatiquement copié dans une cellule ($B$1 dans ce cas). Pour cela, dans la configuration du segment / filtre, cochez "Mettre à jour la plage avec la sélection"("Update range with selection") dans l'onglet "Comportement" et saisissez $B$1 et ""Libellé" (Caption) comme type de sortie.
2. Plage Excel pour déterminer les mois disponibles
A l'aide de l'assistant "Insérer une formule / Membres (XL3Member) pour générer horizontalement tous les mois de l'année, puis attribuez à chacun un numéro de 1 à 12. Utilisez ensuite une formule RechercheH (HLookUp) pour récupérer le numéro du mois affiché en $B$1.
We now have to determine which months we want to assign for Actuals and which ones for the Forecast. For this we will use an if statement so that if the month is before or equal to the last complete month, it will be reported as Actuals otherwise as Forecast. So, for Actuals, an example of the formula would be =if (B4<=$C$1, B3, “”) and for forecast, = if(B4>$C$1, B3, “”)
Une fois ces formules définies, on peut constater que si le dernier mois complet est juillet 2019, les mois disponibles pour les données de réalisé sont janvier 2019 - juillet 2019 et que les mois pour le prévisionnel sont août 2019 à décembre 2019.
3. Rendre le tableau dynamique en utilisant "Garder les membres et créer un jeu dynamique".
Maintenant que nous avons choisi les mois dont nous avons besoin pour chaque mesure, nous avons besoin de paramétrer le tableau pour utiliser ces plages.
Sélectionner les colonnes correspondant à un mois particulier (pour l'instant, le mois et le scénario choisis n'ont pas d'importance) puis créez un 'jeu nommé' depuis le ruban XLCubed Tableau > Garder> "Garder les membres et créer un ensemble dynamique". Une boîte de dialogue vous demandera d'insérer un nom pour l'ensemble nommé.
Le tableau n'affiche désormais que les données de janvier 2019.
To edit this, go to the grid designer and under columns, click on to ‘{…} 2019 – Actual and Forecast’. This opens the set designer as below:
Pour transformer ce jeu nommé fixe en un jeu dynamique, il suffit de sélectionner les périodes affichées par la plage $B$:M$6 pour le réalisé et $B$:M$7 pour le prévisionnel.
Après avoir validé, nous obtenons le tableau souhaité.
Dès que l'on modifier le mois sélectionné dans le segment, les plages utilisées pour le jeu dynamique sont mises à jours grâce aux formules Excel, ce qui permet d'actualiser le tableau pour n'afficher que les données de réalisé et de prévisionnel pour les mois concernés.
Formatage du tableau
Nous pouvons également formater le tableau de manière à distinguer rapidement le réalisé du prévisionnel. Dans l'exemple ci-dessous, j'ai choisi de mettre le prévisionnel en italique et en gris.
Pour plus d'informations sur le formatage des tableaux, lisez cette page.