Automatiser un tableau Excel : le guide ultime.

AUTOMATISER-EXCEL-800x450

Vous voulez automatiser un tableau Excel ? Vous devez mettre à jour votre reporting Excel ? Vous effectuez cette tâche tous les mois et ça commence à sérieusement vous lasser. Vous avez plusieurs solutions qui s’offrent à vous :

  • Donner le bébé à un collègue ;
  • Faire le mort ;
  • Plaider la panne machine…
Un reporting ? Quel reporting ?

Ces solutions ne sont pas viables à long terme malheureusement. Lorsque l’on travaille au sein des services supports (comptabilité, ressources humaines, achats,…) ou que l’on est manager d’un service, il arrive régulièrement que l’on doive créer ou mettre à jour des tableaux de bord dans Excel.

Plus communément appelés reportings, ces tableaux ont pour objectif de présenter des données chiffrées afin de pouvoir prendre des décisions éclairées. La première problématique est de savoir comment faire un reporting dans Excel orienté vers la future prise de décision. Une fois mis en place, on se rend vite compte que le processus de mise à jour est chronophage.

Pour certains métiers comme celui de contrôleur de gestion, ces tâches représentent 70 à 90% du poste. Si vous êtes dans cette situation et que vous souhaitez remédier à cet état de fait, il existe une solution : l’automatisation des tableaux sur Excel. Dans cet article, je vous donne toutes les astuces et techniques qui m’ont permis d’automatiser la quasi totalité de mes reportings récurrents.

Pourquoi automatiser un reporting Excel ?

Saisir des données actualisées chaque mois dans Excel afin de mettre à jour tableaux de bord et reportings est une tâche longue et fastidieuse. La charge de travail générée peut mener au burn out. C’est d’autant plus vrai lorsque la personne a une maitrise limitée d’Excel.

A l’inverse, pour une personne maitrisant Excel, mettre à jour les reportings est souvent une tâche provoquant l’ennui et l’absence de challenge. Pour ma part, j’ai dû faire face au bore out. Il s’agit d’une forme de dépression par l’ennui à cause du manque de challenge.

Automatiser un tableau Excel permet pour l’entreprise de libérer les salariés de tâches sans valeur ajoutée. Le temps ainsi libéré peut être consacré à d’autres activités à plus haute valeur ajoutée comme l’analyse et la mise en place de plans correctifs.

Un reporting automatisé a l’avantage d’être évolutif et de s’améliorer. Lors de la production des tableaux, en cas d’erreur, on peut modifier le programme de production. Il est amélioré. Lorsque le reporting est fait manuellement, on risque toujours l’erreur humaine.

A celles et ceux qui me répondraient qu’un humain a le grand avantage de se rendre compte d’aberrations par rapport à un programme, je vous répondrais deux choses :

  • Votre programme a été mal paramétré ;
  • Un humain n’alloue pas plus de 2% de son QI à une tâche qui l’ennuie.

Quels sont les tableaux pouvant être automatisés dans Excel ?

Si vous produisez vos tableaux de bord dans Excel, il est fort à parier que tout ou partie est automatisable. Suivant votre secteur d’activité et votre service, les tableaux de bord et reportings à produire dans Excel seront différents. En voici quelques uns qui sont automatisables :

  • Compte de résultat (analytique)
  • Bilan
  • Soldes intermédiaires de gestion
  • Suivi de la trésorerie
  • Budget
  • Comparaison budget vs réalisé
  • Suivi des projets
  • Plan de charge de la production
  • Suivi du personnel
  • Suivi de la masse salariale
  • Plan de formation

Excel est un formidable outil de traitement et de représentation des données. On peut autant automatiser la saisie des données dans Excel, que la production des graphiques ou que le partage des tableaux.

Aujourd’hui, grâce à des technologies connexes et accessibles, on peut récupérer des données directement sur internet. Automatiser un tableau Excel est de plus en plus facile et requiert des compétences limitées. Je vous explique ça dans la section suivante.

Comment automatiser un tableau dans Excel ?

Pour automatiser un tableau Excel, il existe différentes techniques :

  • Choix de l’architecture du fichier
  • Utilisation des formules
  • Création de macros VBA
  • Power Query et Power Pivot
  • Python

Ces méthodes sont complémentaires et chacune a ses forces et son domaine de prédilection.

Architecture du fichier

Il existe plusieurs bonnes pratiques qui simplifieront grandement vos efforts pour automatiser un tableau Excel. En effet, pour exploiter au mieux les données, se doter d’un import brut au format base de données est le top ! On évitera les cellules fusionnées ainsi que de placer plusieurs tableaux source dans le même onglet.

S’il vous faut avoir des taux de référence, des listes de choix afin de mettre en place des calculs et de simplifier des saisies, on s’orientera fortement vers la mise en place d’un onglet paramètres dans lequel nous nommerons plages et cellules. Ainsi, nous faciliterons l’exploitation future.

Pour la présentation des données et la production des tableaux, on choisira soigneusement nos représentations visuelles et on s’orientera vers des tableaux au carré pour valider la qualité des éléments transmis.

Il a reçu un fichier mal structuré.

Utilisation des formules

Pour automatiser un tableau Excel, la première étape est aussi l’une des plus accessibles : l’utilisation des fonctions. Si vous travaillez votre fichier afin d’avoir un ou plusieurs onglets avec des données brutes, vous pouvez créer un ou plusieurs onglets pour effectuer des calculs grâce aux fonctions Excel. Il vous suffira alors de remplacer vos données brutes chaque mois pour que l’ensemble du fichier se recalcule.

C’est intéressant si le fichier Excel est propre et a une architecture cohérente. Vous pouvez également créer vos propres fonctions pour des calculs plus complexes ou spécifiques à votre activté.

Macro Excel

Les macros sont des mini programmes directement stockés dans vos fichiers. Il s’agit d’un langage interne à la suite Microsoft Office. Ils sont rédigés en VBA (Visual Basic).

Les macros sont très accessibles grâce à l’enregistreur automatique qui permet de générer du code suite à des actions que nous montrons à Excel. Ensuite, charge à nous de chercher à le comprendre et à le modifier.

Grâce au VBA, vous pouvez récupérer en automatique des données, fusionner des fichiers, produire des graphiques, envoyer les tableaux par mail,…

Power Query et Power Pivot

Power Query est un ETL (Extract, Transform and Load). Une grande partie de notre travail de reporting réside dans la récupération des données et leur nettoyage. L’exploitation vient après. Power Query répond à ce besoin.

Power Query répond à notre effort pour automatiser un tableau Excel en nous permettant de nous connecter à des données, qu’elles soient dans un autre fichier Excel, dans une base de données, dans un fichier brut. Une fois fait, on peut supprimer, ajouter ou modifier des colonnes sans modifier le fichier de base. Il s’agit alors de créer un processus de transformation des données qui sera applicable à chaque mise à jour des données. Puissant !

Automatiser Excel avec Python

Python est un langage de programmation doté d’une communauté de data scientists très active. Ce langage s’est doté d’ajouts (appelés bibliothèques) qui dope ses fonctionnalités et ses possibilités.

Python est capable de récupérer des données dans Excel et de les réinjecter transformées. Il peut générer des graphiques, mises en forme,… Grâce à Python, on peut automatiser un tableau Excel avec des besoins plus complexes de récupération des données sur internet ou la mise en place de calculs statistiques complexes ou de modèles prédictifs.

A présent, je vais vous expliquer comment vous pouvez mettre en oeuvre ces différents outils et ainsi aisément automatiser un tableau Excel.

Comment mettre en oeuvre ?

L’automatisation est souvent vu comme un domaine intimidant. Au début on ne se sent pas capable de le faire ou on ne sait pas comment se faire aider. Il y a pourtant des méthodes simples pour mener à bien un projet d’automatisation des tableaux de bord dans Excel.

Se former et mettre en oeuvre seul

« Google est ton ami ». C’est d’autant plus vrai lorsqu’on comprend le vocabulaire à utiliser et que l’on utilise les bons mots clés. Internet est truffé de tutoriels sur Excel, VBA, Python. Commencez petit. Faites vos armes sur de petites fonctionnalités et par améliorations sporadiques. Vous constaterez que vous garderez en tête cette logique d’améliorations successives.

Si vous faites de la programmation via VBA ou Python pour automatiser un tableau Excel, commentez votre code. C’est une excellente habitude qui vous permettra de revenir plus tard sur votre code sans vous prendre la tête pour comprendre ce que vous vouliez faire à l’époque.

Lui va devoir trouver une autre solution

Passer par un prestataire

Lorsque le projet est d’une taille significative, passer par un prestataire est une bonne solution. Des plateformes de mise en relation existent comme Malt ou Fiverr. Il vous faudra définir le périmètre du projet.

De préférence, sélectionnez un prestataire dont le parcours est similaire à vos attentes. En effet, si vous souhaitez automatiser un tableau Excel de type finance, un ancien contrôleur de gestion ou comptable comprendra plus facilement vos attentes et saura vous orienter.

N’hésitez pas à demander à ce que des écrits soient produits sur la construction du fichier. Si un jour vous voulez reprendre en main la maintenance, ça vous simplifiera grandement la tâche.

Passer par une solution clé en main

Elles sont rares. Très rares. Il existe plusieurs solutions pour automatiser un reporting mais ça vous demandera un peu de formation et de changer vos habitudes de travail.

Conclusion

Automatiser un tableau Excel n’a jamais été aussi accessible. Les techniques et outils sont à votre portée.

Je vous invite à commencer petit. Visez un fichier Excel modeste ou une fonctionnalité bien particulière. Ensuite, vous aurez toujours la possibilité de le faire évoluer successivement à mesure que vos compétences se développeront.

Si vous avez besoin de conseils sur une solution d’automatisation, n’hésitez pas à me contacter et à consulter les autres articles du blog. A bientôt !

Articles en lien

4.9/5 - (37 votes)

Vous avez apprécié cet article ?

Pour me soutenir, cliquez sur cette publicité. Pas besoin d’acheter, un clic suffit.

Merci. 🤩

Annonces

Modèles Excel

+

Livret 

7 méthodes pour gagner 2H par semaine sur Excel

C’est gratuit 🤩

Formation Excel (100% finançable)

Un programme personnalisé, avec un formateur expert attitré et selon vos disponibilités.

Formations Excel

Parce que chacun devrait avoir les moyens de faire son travail sur Excel

Découvrez des formations accessibles, rapides et directement applicables.