Les termes « Power Query », « Power Pivot », « Power BI » et d'autres « pouvoirs » apparaissent de plus en plus dans les articles et les documents sur Microsoft Excel. D'après mon expérience, tout le monde ne comprend pas clairement ce qui se cache derrière ces concepts, comment ils sont interconnectés et comment ils peuvent aider un simple utilisateur d'Excel.
Clarifions la situation.
Requête d'alimentation
En 2013, un groupe de développeurs spécialement créé au sein de Microsoft a publié un complément gratuit pour Excel. Requête d'alimentation (d'autres noms sont Data Explorer, Get & Transform), qui peuvent faire beaucoup de choses utiles pour le travail quotidien :
- Téléchargement des données dans Excel à partir de près de 40 sources différentes, y compris des bases de données (SQL, Oracle, Access, Teradata…), des systèmes ERP d'entreprise (SAP, Microsoft Dynamics, 1C…), des services Internet (Facebook, Google Analytics, presque tous les sites Web).
- Recueillir des données de Fichiers tous les principaux types de données (XLSX, TXT, CSV, JSON, HTML, XML…), à la fois individuellement et en masse - à partir de tous les fichiers du dossier spécifié. À partir des classeurs Excel, vous pouvez télécharger automatiquement les données de toutes les feuilles à la fois.
- Nettoyer données reçues du « garbage » : colonnes ou lignes supplémentaires, répétitions, informations de service dans le « header », espaces supplémentaires ou caractères non imprimables, etc.
- Intégrez les données de commander: corrigez la casse, les nombres en tant que texte, remplissez les lacunes, ajoutez le bon "bouchon" du tableau, analysez le texte "collant" en colonnes et recollez-le, divisez la date en composants, etc.
- de toutes les manières possibles transform tableaux, en les mettant sous la forme souhaitée (filtrer, trier, modifier l'ordre des colonnes, transposer, additionner des totaux, développer des tableaux croisés à plat et réduire en arrière).
- Remplacez les données d'une table à une autre en faisant correspondre un ou plusieurs paramètres, c'est-à-dire une fonction de remplacement agréable VPR (VLOOKUP) et ses analogues.
Power Query se trouve en deux versions : en tant que complément distinct pour Excel 2010-2013, qui peut être téléchargé à partir du site Web officiel de Microsoft, et dans le cadre d'Excel 2016. Dans le premier cas, après l'installation, un onglet séparé apparaît dans Exceller:
Dans Excel 2016, toutes les fonctionnalités de Power Query sont déjà intégrées par défaut et se trouvent dans l'onglet Données (Date) en tant que groupe Obtenir et convertir (Obtenir et transformer):
Les possibilités de ces options sont totalement identiques.
La caractéristique fondamentale de Power Query est que toutes les actions d'importation et de transformation des données sont stockées sous la forme d'une requête - une séquence d'étapes dans le langage de programmation interne de Power Query, qui est succinctement appelé "M". Les étapes peuvent toujours être modifiées et rejouées un nombre quelconque de fois (requête d'actualisation).
La fenêtre principale de Power Query ressemble généralement à ceci :
À mon avis, il s'agit du module complémentaire le plus utile répertorié dans cet article pour un large éventail d'utilisateurs. De nombreuses tâches pour lesquelles vous deviez terriblement déformer avec des formules ou écrire des macros sont désormais facilement et magnifiquement effectuées dans Power Query. Oui, et avec mise à jour automatique ultérieure des résultats. Et étant donné qu'il est gratuit, en termes de rapport qualité-prix, Power Query est tout simplement hors compétition et un incontournable absolu pour tout utilisateur Excel intermédiaire-avancé de nos jours.
PowerPivot
Power Pivot est également un complément pour Microsoft Excel, mais conçu pour des tâches légèrement différentes. Si Power Query se concentre sur l'importation et le traitement, Power Pivot est principalement nécessaire pour l'analyse complexe de grandes quantités de données. En première approximation, vous pouvez considérer Power Pivot comme un tableau croisé dynamique sophistiqué.
Les principes généraux de travail dans Power Pivot sont les suivants :
- Nous sommes le premier chargement des données dans Power Pivot – 15 sources différentes sont prises en charge : bases de données communes (SQL, Oracle, Access…), fichiers Excel, fichiers texte, flux de données. De plus, vous pouvez utiliser Power Query comme source de données, ce qui rend l'analyse presque omnivore.
- Puis entre les tables chargées les connexions sont configurées ou, comme on dit, est créé Modèle de données. Cela permettra à l'avenir de créer des rapports sur tous les champs des tables existantes comme s'il s'agissait d'une seule table. Et pas de VPR à nouveau.
- Si nécessaire, des calculs supplémentaires sont ajoutés au modèle de données à l'aide de colonnes calculées (similaire à une colonne avec des formules dans un "tableau intelligent") et les mesures (un analogue du champ calculé dans le résumé). Tout cela est écrit dans un langage interne spécial de Power Pivot appelé DAX (Data Analysis eXpressions).
- Sur la feuille Excel, selon le Data Model, les rapports qui nous intéressent sont construits sous la forme tables pivotantes et schémas.
La fenêtre principale de Power Pivot ressemble à ceci :
Et voici à quoi ressemble le modèle de données, c'est-à-dire toutes les tables chargées avec des relations créées :
Power Pivot possède un certain nombre de fonctionnalités qui en font un outil unique pour certaines tâches :
- Dans PowerPivot pas de limite de ligne (comme dans Excel). Vous pouvez charger des tableaux de n'importe quelle taille et travailler facilement avec eux.
- Power Pivot est très bon pour compresser les données lors de leur chargement dans le modèle. Un fichier texte original de 50 Mo peut facilement se transformer en 3 à 5 Mo après le téléchargement.
- Étant donné que "sous le capot" Power Pivot dispose en fait d'un moteur de base de données à part entière, il gère de grandes quantités d'informations très vite. Besoin d'analyser 10 à 15 millions d'enregistrements et de créer un résumé ? Et tout ça sur un vieux portable ? Aucun problème!
Malheureusement, Power Pivot n'est pas encore inclus dans toutes les versions d'Excel. Si vous avez Excel 2010, vous pouvez le télécharger gratuitement sur le site Web de Microsoft. Mais si vous avez Excel 2013-2016, tout dépend de votre licence, car. dans certaines versions, il est inclus (Office Pro Plus, par exemple), et dans d'autres non (Office 365 Famille, Office 365 Personnel, etc.). Vous pouvez en savoir plus à ce sujet ici.
Cartes de puissance
Ce module complémentaire est apparu pour la première fois en 2013 et s'appelait à l'origine GeoFlow. Il est destiné à la visualisation de géo-données, c'est-à-dire d'informations numériques sur des cartes géographiques. Les données initiales à afficher sont extraites du même modèle de données Power Pivot (voir le paragraphe précédent).
La version de démonstration de Power Map (presque pas différente de la version complète, soit dit en passant) peut être téléchargée à nouveau gratuitement sur le site Web de Microsoft. La version complète est incluse dans certains packages Microsoft Office 2013-2016 avec Power Pivot - sous la forme d'un bouton Carte 3D languette insérer (Insérer — carte 3D):
Fonctionnalités clés de Power Map :
- Map peut être à la fois plat et volumineux (globe).
- Vous pouvez utiliser plusieurs types de visualisation (histogrammes, graphiques à bulles, cartes thermiques, zones de remplissage).
- Vous pouvez ajouter mesure du temps, c'est-à-dire animer le processus et le voir évoluer.
- Les cartes sont chargées à partir du service Bing Maps, c'est-à-dire que vous avez besoin d'une connexion Internet très rapide pour voir. Parfois, il y a des difficultés avec la reconnaissance correcte des adresses, car. les noms dans les données ne correspondent pas toujours à ceux de Bing Maps.
- Dans la version complète (non démo) de Power Map, vous pouvez utiliser votre propre cartes téléchargeables, par exemple, pour visualiser les visiteurs d'un centre commercial ou les prix des appartements dans un immeuble résidentiel directement sur le plan de l'immeuble.
- Sur la base des géo-visualisations créées, vous pouvez créer des vidéos directement dans Power Map (exemple) pour les partager ultérieurement avec ceux qui n'ont pas installé le complément ou les inclure dans une présentation Power Point.
vue de puissance
Introduit pour la première fois dans Excel 2013, ce complément est conçu pour donner vie à vos données avec des graphiques, des diagrammes, des cartes et des tableaux interactifs. Parfois, les termes sont utilisés pour cela. tableau de bord (tableau de bord) or tableau de bord (fiche d'évaluation). L'essentiel est que vous pouvez insérer une feuille spéciale sans cellules dans votre fichier Excel - une diapositive Power View, où vous pouvez ajouter du texte, des images et de nombreux types de visualisations différents en fonction de vos données du modèle de données Power Pivot.
Cela ressemblera à ceci:
Les nuances ici sont:
- Les données initiales sont extraites du même endroit - du modèle de données Power Pivot.
- Pour travailler avec Power View, vous devez installer Silverlight sur votre ordinateur – l'analogue Microsoft de Flash (gratuit).
Soit dit en passant, sur le site Web de Microsoft, il existe un cours de formation très correct sur Power View en .
Power BI
Contrairement aux précédents, Power BI n'est pas un complément pour Excel, mais un produit distinct, qui est un ensemble d'outils d'analyse et de visualisation d'entreprise. Il se compose de trois éléments clés :
1. Bureau Power BI – un programme d'analyse et de visualisation des données, qui comprend, entre autres, toutes les fonctionnalités des modules complémentaires Power Query et Power Pivot + des mécanismes de visualisation améliorés de Power View et Power Map. Vous pouvez le télécharger et l'installer gratuitement à partir du site Web de Microsoft.
Dans Power BI Desktop, vous pouvez :
- Charger des données à partir de 70 sources différentes (comme dans Power Query + connecteurs supplémentaires).
- lier tables à modéliser (comme dans Power Pivot)
- Ajoutez des calculs supplémentaires aux données avec les mesures и colonnes calculées sur DAX (comme dans PowerPivot)
- Créez de belles données basées sur rapports interactifs avec différents types de visualisations (très similaire à Power View, mais encore meilleur et plus puissant).
- Publier créer des rapports sur le site Power BI Service (voir point suivant) et les partager avec des collègues. De plus, il est possible de donner différents droits (lecture, édition) à différentes personnes.
2. Service en ligne Power BI - pour faire simple, il s'agit d'un site où vous et chaque utilisateur de votre entreprise aurez leur propre "sandbox" (espace de travail) où vous pourrez télécharger des rapports créés dans Power BI Desktop. En plus de les visualiser, il vous permet même de les modifier, reproduisant presque toutes les fonctionnalités de Power BI Desktop en ligne. Vous pouvez également emprunter des visualisations individuelles à partir des rapports d'autres personnes ici, en collectant les tableaux de bord de votre propre auteur à partir d'eux.
Cela ressemble à ceci:
3. Power BI Mobile est une application pour iOS / Android / Windows permettant de se connecter au service Power BI et de visualiser (et non de modifier) facilement les rapports et tableaux de bord créés directement sur l'écran de votre téléphone ou de votre tablette. Vous pouvez le télécharger (entièrement gratuit) ici.
Sur un iPhone, par exemple, le rapport généré ci-dessus ressemble à ceci :
Et tout cela en conservant une interactivité et une animation + emprisonnée pour le toucher et le dessin sur l'écran avec un stylet. Très confortablement. Ainsi, l'intelligence d'affaires devient accessible à toutes les personnes clés de l'entreprise à tout moment et en tout lieu – seul l'accès à Internet est nécessaire.
Plans tarifaires Power BI. Power BI Desktop et Mobile sont gratuits prêts à l'emploi, et la plupart des fonctionnalités du service Power BI sont également gratuites. Donc, pour un usage personnel ou au sein d'une petite entreprise, vous n'avez pas besoin de payer un centime pour tout ce qui précède et vous pouvez rester sur le plan en toute sécurité. Gratuit. Si vous souhaitez partager des rapports avec des collègues et administrer leurs droits d'accès, vous devrez vous rendre sur BESS (10 $ par mois par utilisateur). Y en a-t-il d'autres Premium – pour les grandes entreprises (> 500 utilisateurs) qui nécessitent des capacités de stockage et de serveur séparées pour les données.
- Diagramme de Gantt de projet dans Excel avec Power Query
- Comment créer une base de données dans Excel à l'aide de Power Pivot
- Visualisation du mouvement le long de l'itinéraire sur la carte dans Power Map