Excel et Power Query

Power Query, une version No Code pour remplacer VBA ?

☰

Revoir les bases d'Excel et maîtriser ses usages classiques. Partir à la découverte de Power Query et relier son usage à l'utilisation de VBA.

Contenu

Excel

J’ai commencé par revoir les bases d’Excel pour une utilisation classique, ce qui m’a permis de découvrir plusieurs astuces et fonctionnalités dont j’ignorais l’existence, mais que je vais désormais apprécier utiliser.

Pour cela, je me suis appuyé sur les recommandations de Khaoula, qui suggérait dans son MON cette série de vidéos abordant l’essentiel de manière efficace : Cours complet Microsoft EXCEL. J’ai regardé l’intégralité de ces vidéos et les ai trouvées particulièrement utiles.

Les points suivants sont abordés :

• Les POURCENTAGES, Fcts Max/Min
• Créer un GRAPHIQUE avec EXCEL et l'améliorer
• TRIER et FILTRER une LISTE (base de données)
• Utiliser la MISE EN FORME automatique CONDITIONNELLE
• NBVAL et NB.SI
• Les MINI-GRAPHIQUES d'une case (Graphiques Sparkline)
• Utiliser la FONCTION SI Condition à 2 possibilités, à 3 possibilités ou PLUS (si imbriqués)
• Les fonctions ET et OU + utilisation avec SI
• ALEA, ALEA.ENTRE.BORNES et TABLEAU.ALEA (Les nombres aléatoires (tirage au sort))
• Créer une LISTE DÉROULANTE
• La VALIDATION des DONNÉES (Créer un formulaire sans erreur d'encodage)
• Les fonctions RECHERCHEV et RECHERCHEH
• Créer un TABLEAU CROISÉ DYNAMIQUE + GRAPHIQUE
• La fonction RANG (établir un classement)
• Réaliser un CLASSEMENT AUTOMATIQUE
• Créer une CARTE GÉOGRAPHIQUE automatique (statistiques par pays, régions et villes)
• CONVERTIR des DEVISES (convertisseur de monnaie automatique)
• L'ANALYSEUR de DONNÉES (Tableaux et graphiques automatiques)

Power Query

Formation

En explorant le site de Do_It, j’ai remarqué que la plupart des MON sur Excel s’orientent vers VBA après avoir revu les bases. De mon côté, j’ai préféré me pencher sur Power Query, un outil intégré à l’onglet Données d’Excel depuis 2016.

Pour découvrir son utilisation, j’ai d’abord regardé cette vidéo, qui présente rapidement les manipulations possibles avec Power Query. Cependant, je ne la trouve pas très convaincante et ne la recommande pas.

En revanche, j’ai beaucoup apprécié cette série de quatre vidéos, que je recommande vivement : Formation Power Query

Exercice

Pour mettre en pratique ce que j’ai appris, j’ai choisi de reproduire exactement le même résultat que Charles dans son MON, mais en utilisant Power Query plutôt que VBA.

L’implémentation des deux requêtes suivantes dans Power Query n’a pris que 10-15 minutes, contre quelques heures en VBA :

• Création des adresses mail centraliennes
• Extraction des centraliens de Marseille

Formule Power Query pour générer les adresses mail centraliennes

Prise en compte de l'exception "centrale-med" pour Marseille dans la formule

Formule Power Query pour extraire les centraliens de Marseille

Les requêtes s'actualisent automatiquement en temps réel dès que de nouvelles lignes de données concernant les centraliens sont ajoutées au fichier Excel initial, comme illustré dans la vidéo ci-dessous. Ainsi, les tables manipulées par Power Query se mettent à jour sans intervention manuelle.

⚠️ Important : N'oubliez pas d' "Enregistrer" le fichier Excel avant de cliquer sur "Actualiser" dans Excel dans l'onglet Données, comme montré dans la vidéo. Sinon, l'actualisation ne fonctionnera pas. J'ai mis un certain temps à réaliser que j'avais commis cette erreur, ce qui expliquait pourquoi l'actualisation ne fonctionnait pas.

Bonus : Power Query remplace-t-il VBA ?

J’aurais aimé approfondir la question de savoir si Power Query peut réellement remplacer VBA. Faute de temps, je partage ici une requête rapide effectuée avec ChatGPT, car ce sujet m'intéresse particulièrement.