Le reporting Excel qui se met à jour seulLe reporting Excel qui se met à jour seul

L'automatisation d'un reporting Excel consiste à faire arriver les chiffres dans le classeur sans export, sans copier-coller et sans ressaisie. Les tableaux et les graphiques restent les mêmes. Le travail qui les précède à chaque édition disparaît, geste après geste.

Un reporting Excel est un classeur qui rassemble, à date fixe, les chiffres de l'entreprise : le chiffre d'affaires du mois, les heures facturées, les factures en retard. Dans une PME, ces chiffres viennent de plusieurs logiciels, et une personne les recopie à chaque fois. Cet article fait partie du dossier Tableaux de bord et pilotage.

Ce qu'est un reporting Excel, et où part son temps

Le temps d'un reporting part rarement dans le calcul, puisque les formules s'écrivent une fois. Il part dans 4 gestes qui précèdent chaque édition, et que l'automatisation retire un par un.

Prenons une scène type. Un reporting mensuel est alimenté par 4 exports, et chacun prend 20 minutes entre le téléchargement, le collage et la vérification. Cela fait 80 minutes par mois, soit 16 heures par an. Tenu chaque semaine, sur 47 semaines, soit l'année moins les 5 semaines de congés payés d'une année complète de travail, le même reporting demande près de 63 heures.

Préparer les données avant d'automatiser

Une automatisation rejoue les mêmes gestes sur des données qui changent. Elle tient si ces données gardent la même forme d'une édition à l'autre, et si chaque chiffre n'a qu'une source.

• Des tableaux Excel plutôt que des plages. La commande Mettre sous forme de tableau, dans l'onglet Accueil, transforme une plage en tableau. Une formule saisie dans une colonne s'applique alors aussitôt à toute la colonne, et les formules peuvent désigner le tableau par son nom.
• Des colonnes qui n'acceptent que le bon type. La validation des données limite une colonne aux nombres, ou aux dates. Une date saisie comme du texte ne passe plus.
• Une seule source par chiffre. Si le chiffre d'affaires existe dans 2 logiciels, décidez lequel fait foi, et ne lisez que celui-là.
• Des identifiants stables. Un client porte le même numéro dans tous vos outils. Sans cela, aucun rapprochement ne tient.

Les 2 premiers points se règlent dans le classeur. Le 3e demande parfois une décision de direction, parce qu'il touche à la façon dont l'information circule entre la vente, la facturation et la comptabilité.

Importer sans copier-coller avec Power Query

Power Query est l'outil d'Excel qui importe des données externes ou s'y connecte, puis les met en forme. Il se trouve dans l'onglet Données, groupe Obtenir & transformer les données, et lit des fichiers, des pages web, des bases de données ou d'autres tableaux du classeur.

Son éditeur enregistre chaque transformation comme une étape : retirer une colonne, corriger un format de date, réunir 2 tableaux. Lorsque vous actualisez la requête, chaque étape s'exécute à nouveau, sur les données du jour. La mise en forme ne se refait plus à la main.

Les exports du mois, rangés dans un dossier

Power Query sait aussi combiner plusieurs fichiers de même structure, rangés dans un même dossier, en une seule table. La commande se trouve dans Données, Obtenir les données, À partir d'un fichier, puis À partir d'un dossier. Chaque mois, l'export du mois rejoint le dossier, et une actualisation l'ajoute au reporting.

Le support de Microsoft pose 2 conditions. Le dossier ne contient que ces fichiers, puisque tout ce qu'il contient entre dans la combinaison. Les fichiers ont les mêmes en-têtes de colonnes, les mêmes types de données et le même nombre de colonnes. Leur ordre, lui, peut changer : la correspondance se fait par le nom des colonnes.

Power Query existe dans Excel pour Windows et dans Excel pour Mac, et Excel pour le web sait actualiser ses requêtes.

Laisser Excel actualiser les chiffres

Une fois les requêtes en place, le reporting ne se refait plus : il s'actualise. Excel propose 3 façons de le faire, du bouton à l'actualisation à intervalle fixe.

• Actualiser tout. Ce bouton de l'onglet Données relance toutes les connexions du classeur d'un coup.
• À l'ouverture du fichier. L'option Actualiser les données lors de l'ouverture du fichier met les chiffres à jour dès qu'on ouvre le classeur.
• À intervalle fixe. Une autre option relance la connexion toutes les n minutes, au nombre de minutes de votre choix.

Les tableaux croisés dynamiques bâtis sur ces données se mettent à jour par leur commande Actualiser. Ils peuvent aussi s'actualiser à l'ouverture du classeur : l'option se coche dans leurs options, onglet Données.

Le classeur garde sa forme, avec les mêmes onglets et les mêmes graphiques. Les personnes qui le lisent ne changent rien à leurs habitudes.

Les macros et les scripts Office, pour les gestes qui restent

Certains gestes ne relèvent pas de l'import : trier, mettre en forme, préparer un onglet avant l'envoi. Excel les rejoue avec une macro ou avec un script Office, enregistrés une fois.

L'enregistreur de macros note vos actions dans le langage des macros, VBA, sans qu'il faille savoir programmer tant que l'enregistrement fait ce que vous voulez. Il capte presque tout, erreurs comprises. Il se trouve dans l'onglet Développeur, masqué par défaut. Une macro enregistrée sur une plage ne traite pas une ligne ajoutée ensuite, et son exécution ne s'annule pas : la première fois, travaillez sur une copie du classeur.

Les scripts Office font le même travail dans Excel pour le web, pour Windows et pour Mac, depuis l'onglet Automatiser. Leur enregistreur d'actions ne demande aucune connaissance en code, et un script se partage dans l'organisation. Leur présence dépend de la licence : si l'onglet Automatiser n'apparaît pas, le support de Microsoft conseille d'abord de vérifier que la vôtre les inclut.

Relier vos logiciels au classeur

Power Query retire le collage. Il laisse l'export, quand le logiciel source ne se laisse pas lire directement : quelqu'un doit encore télécharger le fichier et le déposer dans le dossier. Le dernier geste à supprimer est celui-là.

Une liaison va chercher les données dans le logiciel et les dépose là où Excel les lit : chaque nuit, le fichier des ventes de la veille rejoint le dossier du reporting. Elle passe par un connecteur du logiciel lui-même, par un outil d'automatisation sans code, ou par un petit développement quand le logiciel n'offre ni l'un ni l'autre. L'article qui rassemble des exemples concrets d'automatisation en donne d'autres.

Avant d'engager une liaison, faites le calcul : ce que coûte l'export à la main sur un an, face au prix de la liaison. L'article qui explique comment baisser ses coûts avec l'automatisation en donne la méthode.

Regardez aussi ce que contient chaque onglet. Un onglet qui sert à suivre les relances de prospects a plutôt sa place dans un CRM qui relance tout seul, une fois réglé. Le reporting n'en reprend alors que les totaux.

Les pannes d'un reporting automatisé, et leurs parades

Un reporting automatisé casse rarement d'un coup. Une colonne change de nom, un fichier de trop arrive dans le dossier, et les totaux glissent sans alerte. 5 pannes sont à prévoir.

La parade commune tient en une ligne de contrôle : le total du reporting comparé, à chaque édition, au total du logiciel source. Placez-la en haut de la première feuille, et désignez la personne qui la regarde.

Quand le reporting doit sortir d'Excel

Excel suffit quand le reporting est mensuel, lu par une ou 2 personnes et alimenté par des exports propres. Il atteint sa limite quand plusieurs personnes ont besoin des mêmes chiffres à jour, chaque jour.

• chacun reçoit sa copie du fichier, et plusieurs versions circulent ;
• un logiciel ne sait rien exporter, et quelqu'un recopie ses chiffres ;
• la personne qui a construit le classeur est la seule à savoir le réparer ;
• le reporting sert des décisions de la semaine, et il arrive une fois par mois.

Dès 2 de ces signes, la question de l'outil se pose. Pour placer un tableau de reporting ailleurs que dans un classeur, un article du dossier compare les familles d'outils, du tableur au tableau de bord. Un autre aide à décider par quoi remplacer Excel, usage par usage.

Si la question ne porte que sur le tableur, regardez les alternatives gratuites à Excel, en vérifiant qu'elles savent importer et actualiser des données de la même façon. Et quand aucun outil du marché ne lit vos logiciels, il reste à faire construire son tableau de bord.

Par où commencer

Commencez par mesurer, puis par Power Query, sur un seul export. Les liaisons viennent ensuite, quand les exports pèsent plus que tout le reste.

1. Mesurez. Chronométrez la prochaine édition, geste par geste. Le calculateur du coût administratif convertit ces minutes en heures par semaine et en coût sur l'année.
2. Remplacez un premier onglet. Celui que vous remplissez à chaque édition : une requête à la place du copier-coller, puis un mois d'essai en comparant chaque total au logiciel source.
3. Passez aux suivants. Un onglet après l'autre, jusqu'à ce qu'il ne reste que l'export à supprimer.

Pour ce dernier geste, regardez du côté de l'automatisation. La nôtre commence à 1 200 € HT : nous relions les logiciels que vous utilisez déjà pour qu'une tâche répétée chaque semaine se fasse seule. Notre maintenance Veille, à 49 € HT par mois, surveille ensuite qu'elle tourne, et la corrige quand un logiciel relié change. Le détail est sur la page de nos prestations.

Si vos sources se contredisent, le chantier commence avant le classeur. Notre diagnostic d'organisation regarde comment l'entreprise tourne au quotidien : d'où part l'information, combien de fois elle est recopiée. Un rapport écrit dit ensuite ce qui bloque, ce que vous pouvez corriger dès la semaine suivante, et ce qu'il faudrait construire, chiffré. Le diagnostic court coûte 650 € HT, pour les entreprises de 1 à 10 salariés, et le diagnostic complet 1 650 € HT, de 10 à 50 salariés.

Vous avez un reporting à automatiser, ou besoin d'un diagnostic ? Contactez-nous directement.