Publié par 2 commentaires

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

date excel automatisation fichiers graphique

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 1er 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 1er 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 1er 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 :

calcul date référence Excel mise à jour automatique fichier

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 :

base fichier mise à jour excel date référence

La fonction INDEX() cherche une donnée :

  • dans une plage de données (premier argument),
  • en fonction du numéro de ligne (2e argument)
  • et du numéro de colonne (3e 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 10e 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.
INDEX() mise à jour fichier Excel date fonction

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 :

INDEX() somme dynamique Excel mise à jour fichier date

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.

données toujours à jours excel NA() grahique cumul

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.
graphique excel toujours à jour fonction NA() date 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)

graphique suivi budgetaire budget mise à jour automatique dynamique
Un fichier de suivi budgétaire ZERO MAINTENANCE :
tout se met à jour et s’affiche en fonction de la date de référence !

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 !

2 réflexions au sujet de « Et si 1 cellule vous permettait d’avoir des fichiers Excel toujours à jour ? »

  1. Bonjour,
    J’aurai deux petites remarques concernant vos propositions. La première concerne l’utilisation de la fonction AUJOURDHUI() qui a un gros défaut lié au fait qu’elle s’actualise en temps réel. En effet, il arrive plus que fréquemment que vous manipuliez votre fichier autour de cette fameuse date (pour faire des analyse vs budget) et que vos cumuls vont donc changer du jour…au lendemain, venant perturber votre analyse (comment revenir en arrière dans ce cas ?).
    La seconde concerne l’utilisation d’un RECHERCHEV() pour trouver le nom du mois. Vous préconisez l’utilisation d’INDEX(), eh bien autant le conserver. En effet, si vos mois sont classés dans l’ordre, alors l’utilisation d’INDEX(numéro du mois;plage des mois) vous donnera le nom du mois 😉
    Petite précision: vous n’expliquez pas comment nommer une cellule, ce que bon nombre ignore. Enfin, je conseille pour ma part d’utiliser des noms mixant Majuscules et minuscules, car si un nom est mal orthographié dans une formule, ceci se verra immédiatement, car Excel modifie automatiquement la casse si le nom est correctement orthographié.

    Cdt,
    TJ

    1. Bonjour Thierry,
      Merci pour ces remarques! voici quelques éléments de réponses :
      – Pour l’essentiel des analyses financières, et spécialement en contrôle de gestion, la volatilité de la fonction AUJOURDHUI() ne pose pas de problème. En effet, on résonne sur une base mensuelle et presque exclusivement 1/ en année pleine (sans utiliser cette date), 2/ en YTD (en se basant sur cette date de référence), et 3/ un peu plus rarement en YTG (Year to Go ou reste à faire), le nombre de mois restant étant alors égal à 12-‘date_de_référence’… Malgré tout, pour faire varier cette date de référence plus facilement, rien n’empêche de la saisir « en dur » provisoirement, et de remettre la formule ensuite. L’avantage d’un mois de référence unique dans le fichier et que toutes les analyses repose sur ce paramètre. Tout est cohérent et plus simple à maintenir, quelque soit sa valeur, dynamique ou provisoirement figée.

      – Bonne remarque pour l’usage de la fonction INDEX() pour récupérer le nom des mois… On voit le poids des habitudes et les réflexes conditionnés ! 😉

      – Enfin pour les noms de cellules, c’est une bonne idée pour un prochain post sur les noms de cellule… mais aussi de tableaux (déjà en partie abordés), de graphiques ou d’autres objets… à suivre donc !
      Alexandre

Partagez vos questions, vos connaissances et vos expériences !

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.