Depuis des années, je forme des utilisateurs d’Excel à la programmation avec le langage Visual Basic for Application (VBA). Ce langage permet de faire ce dont toute personne rêve : automatiser les tâches répétitives, pour :

  1. gagner du temps
  2. limiter les erreurs
  3. limiter les tâches sans intérêt

Le problème est que cette automatisation est déjà possible SANS PROGRAMMATION, et depuis longtemps. Le VBA n’est PAS nécessaire dans 90% des projets que les personnes m’apportent en stage.

L’exemple le plus emblématique est la fusion de fichiers : en début de chaque mois, j’ai un fichier Excel (ou CSV) qui est produit par mon CRM, mon ERP, ma GPAO, mon logiciel comptable, etc. ; je dois agréger ces données pour produire des statistiques. Depuis les années 90, il n’est pas nécessaire de recourir à la programmation Excel pour réaliser cela. Des outils plus récents viennent d’arriver, et permettent de rendre la chose encore plus facile.

Entendons-nous : il peut toujours être intéressant d’apprendre à programmer, et la programmation peut être nécessaire dans certaines situations. J’aime programmer, et j’aime former au VBA. Mais il y aura toujours 2 problèmes :

  1. Créer un « bon » programme en VBA sous Excel demande du temps. Au terme d’une formation de 3, 4 ou même 5 jours, les personnes ne sont pas devenues développeurs, et n’ont surtout pas de temps à consacrer à la programmation.
  2. Comme tout débutant, le programme produit sera celui d’un débutant, donc pas nécessairement bien structuré, donc source de bugs. Au début, appliquer des bordures autour d’une cellule Excel prend 28 lignes de codes…

Et surtout il existe des outils qui font déjà le travail.

Ces 3 outils sont :

  1. Access (depuis 1992)
  2. Power Query/Power Pivot pour Excel (depuis 2010)
  3. Power BI (depuis 2010), qui contient aussi Power Query.

Rapidement, c’est quoi ?

  1. Access est une base de données, ce qu’Excel (version de base) n’est pas.
  2. Power Query est un ETL (Extract, Transform and Load), ce qu’Excel (version de base) n’est pas non plus.

Et ça date de quand tout ça ?

  1. Access 1.0 est sortie en 1992, donc il y a 27 ans…
  2. Power Query est sortie la première fois en 2010, donc il y a donc 9 ans.

Vous avez besoin de ces outils, mais vous ne le savez pas.

Car ça ne s’invente pas. Ça s’apprend.

Après leur utilisation, vous aurez normalement l’une ces pensées (ou les 2):

  1. Pourquoi on ne m’en a pas parlé avant (si j’avais su) ?
  2. Quel temps perdu à bricoler dans Excel !

Comment obtenir ces outils ?

  1. Access est inclus dans Office 365. Je ferais un article sur le sujet.
  2. Power Query est un module d’Excel :
    1. à installer comme un complément dans Excel 2010/2013.
    2. déjà installé dans Excel à partir d’Excel 2016, onglet Données, groupe d’options Récupérer et transformer.
  3. Power BI Desktop est un logiciel gratuit qui s’installe à côté d’Excel.

Ces outils sont simples à utiliser, mais il faut disposer de la bonne méthode d’organisation et de normalisation des données pour cela. Ce n’est pas compliqué, il fait juste prendre le temps de se former sur le sujet. Quelques heures suffisent pour démarrer.

Voici quelques cas d’usage que Access + Power Query + Power BI peuvent réaliser pour vous, sans programmation (inspiré de faits réels) :

  • John a besoin d’importer des fichiers Excel tous les mois. Les 12 fichiers d’une année sont copiés-collés chacun dans un fichier annuel. John trouve ça fastidieux, et risqué, et il a raison. Comme il a 30 000 lignes par mois, le fichier Excel met plusieurs minutes à s’ouvrir. En attendant, John boit du café, et pense que ce n’est pas bon pour sa santé. John a raison.
    Avec Access ou Power Query, quelques secondes suffisent pour brasser les 360 000 lignes, et faire des statistiques sur plusieurs années.
  • Jonathan collecte toutes les semaines, auprès de ses 25 collègues, les heures qu’ils ont consacré à différents projets. Jonathan fait des copier-coller de 25 fichiers dans un seul. Jonathan pourrait créer une table dans Access (1 heure), créer un formulaire de saisie dans Access avec des listes déroulantes et une vérification de la saisie (15 min) et placer le tout sur le réseau (2 min). Ensuite, chaque utilisateur ouvre la même base Access, et saisit ses données qui alimentent une seule table.
    Depuis Excel, Jonathan se connecte à la table Access depuis Excel et réalise de jolis Tableaux croisés et de beaux graphiques.
    Leur actualisation avec les données saisies sur la semaine prendra environ 2 secondes.
  • Jonas doit suivre l’évolution du budget prévisionnel par rapport au consommé réel. D’un côté, le budget prévisionnel (1 classeur par agence, 1 feuille par produit, 1 colonne par mois) se trouve dans des fichiers Excel gérés par la compta. D’un autre côté, les données réelles se trouvent dans des exports de l’ERP (ventes par client et par agence). Grâce à Power Query, Jonas transforme automatiquement les données budgétaires avec Power Query, en les présentant sous forme d’un fichier plat (1 ligne par agence/produit/année/mois). Jonas procède aussi aux transformations des données réels avec Power Query, en les agrégeant par mois/année/somme des ventes. Les 2 fichiers (budget & réalisé) sont regroupés par une requête Power Query. Le tout permet de réaliser des Tableaux croisés et des graphiques.
    Temps d’actualisation pour l’année 2019 : 2 secondes, pour l’année 2020 : 2 secondes, etc.
  • Johnny reçoit des fichiers de 4 fournisseurs différents. Il ne peut leur demander de remplir le même fichier, chacun a son format. Johnny trouve ça pénible, et il a raison. Mais Power Query va régler le problème : chaque fichier se voit appliqué des règles de transformation avec Power Query. Au final, Johnny a un seul fichier qui est la fusion des 4 fichiers, grâce à une requête Power Query. Tous les mois, Johnny place les fichiers dans un dossier puis clic sur Actualiser.
    Temps de travail : environ 2 minutes.

Enfin, 2 choses à savoir :

  1. Il y a BEAUCOUP de documentation sur ces sujets, et la documentation de Microsoft est très bien faite : voir l’intro du DAX, le référentiel DAX, Power Query et le langage M de Power Query.
  2. Beaucoup de choses sont en anglais, précisément en anglais informatique, soit 200 mots au grand max. L’extension de navigateur Mate Translate (Chrome | Opera | Firefox) permet de traduire en français littéral toute ou partie des pages.

Se former sur Power Query pour Excel | Se former sur Power BI | Si vous tenez vraiment Excel VBA.