Excel - Power Query - Power Pivot - VBA

Excel - Power Query - Power Pivot - VBA

Quel stage Excel choisir ?

Excel, VBA ou Power Pivot / Query / BI ?

C’est quoi le VBA ?

Le VBA (Visual Basic pour Application) est un langage informatique qui permet d’automatiser des traitements répétitifs, appliqués à des classeurs, des feuilles, des cellules, etc. Dans Excel, mais aussi dans Word, Outlook et PowerPoint, ce sont des macros. Dans Access, ce sont des modules.

C’est quoi les “outils Power” ?

Depuis 2010, Microsoft a progressivement développé d’autres solutions, pour arriver au même résultat que le VBA/les macros, tout en évitant le recourt à la programmation (tendance « low code »). Ces solutions sont sous la forme :

de 2 modules d’Excel (appelé “Power Tools”), Power Pivot et Power Query.

d’1 logiciel indépendant d’Excel (et gratuit), Power BI Desktop (qui contient les 2 modules précédents).

Quand passer aux outils Power ?

Regardez cette liste de tâches et cochez si vous les réalisez actuellement (le résultat n’est pas mémorisé).

Les tâches que je fais régulièrement sur Excel

Les tâches que je fais régulièrement sur Excel

Les tâches

Oui je fais cette tâche régulièrement !

Traiter les mêmes fichiers Excel ou CSV régulièrement (export de janvier, de février...)

Ouvrir des fichiers, copier les données qui sont dedans, et les coller dans un autre fichier

Relier des fichiers Excel entre eux (le fameux message à l’ouverture “voulez-vous mettre à jour le lien...”)

Utiliser les fonctions de recherche, comme la plus connue RECHERCHEV

Avoir des classeurs dont le nom contient l’année (SuiviProduction_2020.xlsx) ou 1 fichier par mois (SuiviVente_Janvier.xlsx)

Nettoyer des fichiers : appliquer des mises en forme, supprimes les lignes vides, supprimer telles colonnes, éliminer les doublons, vérifier la cohérence, etc.

There are no rows in this table

⁠

Vous avez coché au moins une case ? Utilisez les “outils Power” !

Power Query pour Excel et Power BI

Excel et Power BI contiennent un complément gratuit, Power Query, qui permet 2 choses :

Des requêtes vers des sources : dans un classeur Excel A, je crée une requête vers un fichier Excel B. Je crée des calculs et des TCD basés sur cette requête dans le classeur A. Quand le classeur B est modifié, je mets à jour le classeur A et les TCD en 1 clic.

Une source peut être un dossier : tous les fichiers du dossier sont fusionnés en un seul fichier. Dès que j’ajoute un fichier, il est pris en compte par une simple actualisation (1 seul clic).

Autres sources possibles : fichiers CSV/TXT, SQL Serveur, Access, etc.

Des transformations de sources, pour les normaliser (= qui peuvent être exploitées dans un TCD). Ce sont ces transformations qui sont souvent faites avec le VBA, et que l’on peut donc désormais faire en cliquant simplement sur des boutons (”low code”).

Exemple de transformations : rechercher/remplacer, supprimer les lignes qui contiennent…, fusionner 2 colonnes, scinder 2 colonnes, ajouter les données de Janvier à celle de Février, regrouper tous les fichiers Excel d’un dossier.

Dès que la source est modifiée (ajout ou modifications de données), les transformations sont de nouveau appliquées en 1 clic.

Les transformations utilisent le langage M. Il n’est pas nécessaire de taper au clavier ce code, qui est automatiquement généré à partir de commande (boutons, listes...).

Installation dans Excel : ce module (gratuit) doit être installé jusqu’à la version 2016 d’Excel. A partir de la version 2019, le module est fusionné dans Excel (onglet Données > Groupe d’options Obtenir et Transformer).

Installation dans Power BI : ce module est déjà dans Power BI. Cf.

Installation des outils⁠

Power Pivot pour Excel

Excel reçoit un autre complément gratuit Power Pivot, qui permet :

De relier des tables entre elles.

Par exemple, on relie la table Client et la table Commande sur un champ commun, par exemple CodeClient. On peut ensuite calculer la somme du montant des commandes (table Commandes) par département (table Clients qui contient un champ Département).

On évite ainsi le recours aux fonctions de recherche : RECHERCHEV, EQUIV, RECHECHERX.

De créer des calculs avancés (qu’Excel ne sait pas faire) avec le langage fonctionnel DAX.

On utilise des fonctions déjà créées, sur le même principe que les fonctions Excel, mais en plus puissant. Par exemple, on peut retourner les doublons d’une liste avec une seule fonction ou calculer des cumuls depuis le début de l’année. Le tout dans des TCD.

Ces calculs en DAX tiennent toujours compte des filtres appliqués lors de leur utilisation, à la différence de la fonction SOMME qui ne tient jamais compte des filtres.

Ce module existe dans Excel (déjà intégré à partir de la 2013 sinon à installer) et dans Power BI Desktop ( cf. infra).

Power BI Desktop

Microsoft a développé un logiciel, gratuit dans sa version Bureau, nommé Power BI Desktop.

Vous pouvez l’

installer facilement ici⁠

Ce logiciel est indépendant d’Excel, et peut même le remplacer à l’usage.

Il contient :

les fonctionnalités de Power Pivot (relations et DAX, plus simple à utiliser)

le module Power Query, celui que l’on trouve dans Excel (transformations)

la création de rapports, composés de pages, pages qui contiennent des représentations graphiques et en tableaux (tableaux croisés), le tout de façon plus simple, dynamiques, esthétiques et modernes. Les rapports peuvent être filtrés facilement.

Power BI Service est un service en ligne, qui pemet de publier ses rapports (graphiques et TCD) sur le Web et mobile.

Power BI Pro est une licence payante (environ 10 $ / mois / utilisateur), qui autorise le partage de rapports publié sur Power BI Service. Chaque personne qui consulte les rapports publiés sur Power BI Service doit aussi avoir une licence payante. Sans cette licence, vous pouvez tout de même publier sur Power BI Pro, mais vous ne pourrez pas partager ces rapports avec d’autres.

Voir notre

support⁠

Excel “Power Tools” ou Power BI Desktop

Power BI Desktop est gratuit, donc profitez-en ! La limite de l’outil est que la diffusion des rapports Power BI Desktop est facilité en prenant un abonnement payant (Power BI Pro).

Tout ce que l’on apprend sur Power Query peut s’appliquer à Excel ET à Power BI Desktop, qui possède chacun ce module.

[Résumé] A-t-on encore besoin du VBA (un peu) ?

La plupart des manipulations que l’on doit programmer en VBA peuvent être faites avec les “outils Power”.

Quand on programme en VBA, on part d’une page blanche (ou d’un “mauvais” code avec l’Enregistreur de macro).

Automatiser la fusion de fichiers : avec Power Query

Automatiser la suppression de colonnes et des lignes : avec Power Query

Automatiser l’ajustement de contenu (suppression, remplacement...) : avec Power Query

Relier 2 fichiers entre eux : avec Power Query ou Power Pivot

Créer des interfaces de saisie : avec VBA ou

PowerApps⁠

Conclusion

Apprenez d’abord à utiliser les “outils Power”, qui devraient couvrir 95% de vos besoins.

Si seulement ensuite vous constatez que certaines choses que vous voulez faire ne sont toujours pas possibles, alors le VBA peut aider.

95% de ce que l'on fait en VBA peut être fait avec les “outils Power” (et Access au besoin), plus rapidement et plus sûrement.