Et si 1 cellule vous permettait d’avoir des fichiers Excel toujours à jour ?

Combien de temps passez-vous chaque mois à mettre à jour vos fichiers Excel ? A changer le nom du mois dans les titres, à modifier des formules calculant les cumuls à date ou à ajuster les plages de données des graphiques pour faire apparaître un nouveau mois?… 

Si une chose est certaine, c’est bien que
tous les mois, on change de mois… Et que malheureusement ce principe s’applique aussi aux années. Partant de cette lapalissade, vous pourriez vous dire que c’est la fatalité et que ça fait partie du job… Resterait alors à espérer qu’on ne vous demande pas des analyses plus compliquées qui démultiplieraient le temps passé à ces tâches sans valeur ajoutée.

Et si on automatisait tout ça pour se débarrasser une bonne fois pour toute de ces tâches routinières ?

Envie de gagner du temps pour vous pencher davantage sur vos commentaires ? Ne plus subir la pression des dates de diffusion de vos analyses ? Alors rappelez-vous que tout est possible sous Excel, à condition de se remettre (un peu) en question et de se former !

C’est parti !

Une date de référence comme paramètre principal de vos fichiers

Pour commencer, détendez-vous : Excel n’a pas besoin de vous pour déterminer la date de référence de vos analyses. Il suffit juste de la définir.

Nous allons donc la définir dans une cellule que nous nommerons « date_ref ». En effet, nommer cette cellule permettra d’en récupérer facilement la valeur quel que soit notre emplacement dans le fichier. Autant se simplifier la vie jusqu’au bout.

En finance, la période de base des analyses est le mois dans au moins 95% des cas. Notre date de référence sera donc déterminée par une année, un mois et un jour qui sera toujours le 1^er du mois par défaut.

Les fonctions utiles

Nous allons utiliser les fonctions suivantes :

• MOIS() : qui nous permettra de restituer la valeur d’un mois (nombre de 1 à 12) à partir d’une date
• ANNEE() : qui nous permettre d’isoler l’année d’une date donnée
• AUJOURDHUI() : qui nous donnera la date du jour
• MOIS.DECALER() : qui nous permettra de décaler une date de x mois, en avant ou en arrière
• et enfin la fonction DATE() qui nous permettra de construire une date en ayant un jour, un mois et une année

La combinaison de fonctions pour une date de référence dynamique

En combinant les 5 fonctions que nous venons de voir précédemment, la formule suivante permet d’obtenir :

• La date du 1^er jour du mois actuel
  • Formule : =DATE(ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI());1)
  • Exemple : 01/11/2018 si nous sommes le 23/11/2018
• La date du 1^er jour du mois précédent (-1 mois):
  • =MOIS.DECALER(DATE(ANNEE(AUJOURDHUI());MOIS(AUJOURDHUI());1);-1)
  • Exemple : 01/10/2018 si nous sommes le 23/11/2018
• Enfin si vous avez besoin de 2 jours en début de mois pour avoir toutes vos données disponibles, et que vous souhaitez que le changement de date de cette cellules ne s’opère qu’une fois ce délais passé, ajustez la date d’aujourd’hui de -2 jours :
  • =MOIS.DECALER(DATE(ANNEE(AUJOURDHUI()-2);MOIS(AUJOURDHUI()-2);1);-1)
  • Exemple : donne le 01/09/2018 si nous sommes le 02/11/2018 et donne le 01/10/2018 si nous sommes le 23/11/2018.

Cette date de référence va ainsi devenir un point d’appui central dans votre fichier. Elle commandera par la suite le calcul des montants cumulés (YTD), des reste-à-faire (year-to-go YTG), de l’affichage des données dans vos graphiques etc…

La mise à jour des dates dans les titres

En pointant sur votre cellule « date_ref » telle que vue à l’instant, vous pourrez alors facilement récupérer le mois en toute lettre. Pour cela, il vous suffit :

• D’avoir une cellule récupérant le mois de la valeur de la cellule « date_ref »
• D’avoir une petite table de correspondance entre les mois en chiffre et en toute lettre (dans la langue que vous voulez)
• De récupérer le mois en toute lettre grâce à la fonction RECHERCHEV().
• Et pourquoi pas de l’intégrer dans un texte pour le rendre automatique comme dans l’exemple ci-dessous.

En voici une illustration avec les formules visibles dans les commentaires :

Ce texte automatisé peut ainsi servir de titre de tableau, d’entête de colonne ou dans un graphique.

Des données toujours à jours avec la fonction INDEX()

La fonction INDEX() permet pointer sur une cellule au sein d’une plage de données. Elle peut aussi être utilisée dans de très nombreuses occasions, que nous détaillerons ultérieurement dans d’autres articles.

Premier exemple :

Dans cet exemple, nous souhaitons extraire le chiffre du dernier mois disponible en partant d’une base simple, présentant les données mensuelles en colonne :

La fonction INDEX() cherche une donnée :

• dans une plage de données (premier argument),
• en fonction du numéro de ligne (2^e argument)
• et du numéro de colonne (3^e argument).

Pour récupérer la valeur du dernier mois dans notre exemple, la fonction INDEX() cherche donc :

• Dans une plage de données reprenant les 12 mois.
• Dans  la première ligne… c’est évident dans cet exemple, mais il faut tout de même le préciser !…
• Dans la 10^e colonne, correspondant à notre mois de référence. Il faudrait bien sûr ajuster notre formule si notre premier mois n’était pas janvier, mais faisons simple à ce stade.

Deuxième exemple :

La fonction INDEX() est également très utile pour calculer des cumuls qui restent à jour.

Intégrée dans une somme, la formule  que nous venons de voir permet de rendre dynamique la plage à sommer, toujours en se basant sur notre mois de référence :

Les données sélectionnées pour vos calculs sont ainsi directement définies par la date du jour. Il n’est plus nécessaire de modifier manuellement les formules une par une !

La fonction NA() et son effet sur les graphiques

Il n’est jamais très heureux de voir ses fichiers contenir des erreurs du type « #DIV/0! » ou « #N/A »… Pourtant, cette dernière signifie Not Available (non disponible). Et elle peut être très utile pour vous faciliter la mise à jour permanente d’un graphique !

En effet, les cellules revoyant cette valeur ne seront pas affichées dans le graphique. Finis les valeurs à zéro ou les redimensionnements manuels des plages de données reprises dans le graphiques !

Pour cela, il suffit d’inclure dans une condition testant la date pour savoir si la cellule doit donner un résultat ou renvoyer « #N/A » (qui s’obtient avec la fonction « NA() »).

Exemple

Dans l’exemple ci-dessous, les lignes 10 et 11 récupèrent les valeurs mensuelles depuis un tableau ou un système source. La date de référence étant le 01/10/2018, il n’est pas surprenant que nous récupérions des valeurs nulles au réel de novembre et décembre. Le Budget quant à lui est bien complété jusqu’à la fin de l’année.

A partir de là, nous allons chercher à faire un graphique présentant ces données en cumul.

• Pour la ligne Budget, nous allons faire une simple somme avec une référence semi-relative. L’extrémité de la plage est fixe à gauche ($D10), alors que la cellule de droite de la plage de décale à chaque fois pour intégrer un mois de plus.
• Pour la ligne Réel, la formule est la même, mais insérée dans une condition (en gras ci-dessous)
  • Si le numéro du mois (ligne 8) est inférieur ou égal au mois de référence (C6), le cumul est calculé, sinon la fonction NA()renvoie « #N/A »
  • Formule : =SI(O$8<=mois_ref;SOMME($D11:O11);NA())
  • Aucune valeur ne s’affichera pour le Réel des mois postérieurs à la date de référence.

Avantages

Un gain de temps énorme !

Cette solution permet enfin de construire vos graphiques une bonne fois pour toute.

J’ai trop vu de contrôleurs de gestion ajuster manuellement la plage des données du réel à afficher dans le graphique. Il faut alors cliquer sur l’histogramme bleu clair. Puis ajuster à la souris la plage en tirant d’une cellule vers la droite. Et enfin taper « entrée »…. D’accord, on l’a tousfait à nos débuts, mais de là à continuer… Franchement quelle perte de temps et de valeur ajoutée ! Surtout quand c’est à multiplier par le nombre de graphique et de séries de données (plus ou moins évidentes à sélectionner) et par les 12 mois de l’année !…

Un préalable pour des analyses plus poussées

Cette absence de mise à jour vous permet surtout d’envisager des graphiques plus sophistiqués pour une meilleure analyse de la situation. Cumul mobiles, tendances, reste-à-faire, courbe de Pareto… vous trouverez plein d’inspiration dans nos tutos. La liste des possibles ne dépendra alors que de la  pertinence des indicateurs pour mettre en lumière les enjeux de votre business.

En vous libérant de toute mise à jour des graphiques, vous ne toucherez plus à leurs paramètres. Ils ne seront alors qu’une restitution transparente de vos données sources.

Inconvénients (mineurs)

Esthétiquement, un tableau truffé de « #N/A » n’est pas très beau à présenter sur un slide. Cela peut même attirer la suspicion d’interlocuteurs qui, ne maîtrisant pas toujours Excel, penseront qu’il s’agit d’erreur de formule. Dans ce cas, il vaut mieux que ces tableaux ne servent que comme source de données à des graphiques…

De plus, tout calcul incluant une cellule renvoyant« #N/A » renvoie « #N/A »… Si vous voulez réaliser des calculs sur ces plages de données, vous pouvez contourner le problème avec une condition basée sur le numéro du mois, comme vu précédemment. Plus simplement ,vous pouvez doublonner la plage, en remplaçant « NA() » par un « 0 »dans la formule, ce qui permettra tous les calculs.

Pour aller plus loin…

En poussant plus loin l’utilisation des principes vus dans cet article et en y ajoutant l’usage des tableaux, il est possible de rendre un fichier totalement automatisé, du branchement sur la source des données à une restitution graphique la plus élaborée… voire à sa restitution dans Powerpoint ! Cela représente certes un peu de travail de mise en place, mais si vous suivez ce site, vous apprendrez tout cela très vite ! Et quel temps gagné par la suite !

Vous pourrez alors dire adieu aux manipulations fastidieuses, sans intérêts et qui risquent d’introduire des erreurs…

Parmi nos « incontournables de l’analyse », vous trouverez un suivi budgétaire graphique offrant en un coup d’œil toutes les informations nécessaires à une parfaite analyse de la situation. Et comme pour tous nos produits, vous êtes satisfait ou remboursé pendant 1 an sur simple demande ! (cf. nos CGV)

Eh bien ce graphique se remet à jour automatiquement tous les mois, y compris pour les changements d’année ! En effet, il repose entièrement sur une date de référence qui détermine l’ensemble des calculs. Ainsi, une fois connecté à la source de données, le seul travail est de sélectionner les données à afficher, de le lire et d’en tirer les conclusions !

Zéro maintenance, valeur ajoutée maximisée, CQFD !

J’espère que cet article contribuera à vous faire gagner du temps et de la sérénité dans vos réalisations quotidiennes ! Si vous avez des questions ou des remarques, laissez-moi un commentaire. Les questions comme les bonnes pratiques se partagent !