Comment utiliser PowerPivot avec Microsoft Excel

■■ Comment puis-je charger des données dans PowerPivot?

■■ Comment puis-je utiliser PowerPivot pour créer un tableau croisé dynamique?

■■ Comment puis-je utiliser des trancheuses avec PowerPivot?

■■ Que sont les fonctions DAX?

Microsoft PowerPivot a été introduit pour la première fois en tant que complément téléchargeable pour Excel 2010. Si vous avez Excel  Pro Plus ou Office 365, PowerPivot pour Excel est à votre disposition. PowerPivot pour Microsoft Excel  est un complément qui vous permet de:

■■ Stockez et interrogez efficacement de gros volumes de données (pensez à des centaines de millions de lignes) que vous pouvez combiner à partir de plusieurs sources de données et de plusieurs formats de données. Par exemple, certaines de vos données peuvent provenir d’une base de données Microsoft Access, certaines d’un fichier texte, certaines de plusieurs fichiers Excel et certaines de données en direct importées d’un site Web.

■■ Créez des colonnes calculées (au-delà des définitions de champ). Par exemple, si chaque ligne de vos données source contient des revenus et des coûts, vous pouvez créer une colonne calculée pour calculer Profit = Revenue – Costs. PowerPivot contient de nombreuses fonctions DAX (Data Expression Language) qui facilitent la création de colonnes calculées.

■■ Créez des champs calculés qui regroupent les données de différentes lignes. Par exemple, un champ calculé pourrait calculer le bénéfice total comme la somme des bénéfices de chaque transaction.

■■ Créez des KPI (Key Performance Indicators) qui permettent à une organisation de suivre facilement les performances par rapport aux cibles. Par exemple, un indicateur de performance clé PowerPivot pourrait être utilisé pour déterminer pour chaque année et chaque vendeur comment ses ventes réelles par rapport à son quota de ventes.

■■ PowerPivot peut être utilisé comme source de données pour les rapports de classeurs (tableaux croisés dynamiques, graphiques, CUBE fonctions, Power View, etc.). Power View est traité dans le chapitre 46, «Power View».

■■ Les rapports PowerPivot peuvent être publiés sur Microsoft SharePoint pour permettre l’actualisation automatique des données, faciliter le partage et activer la surveillance informatique. Une fois publié, il peut être utilisé comme source de données pour d’autres expériences d’analyse et de génération de rapports (telles que la publication sur SharePoint, un processus de déploiement).

Cette article présente PowerPivot. Pour une explication plus complète du fonctionnement de PowerPivot, lisez Microsoft PowerPivot pour Excel : Donnez une signification à vos données (Microsoft Press 2013) de Marco Russo et Alberto Ferrari.

Pour activer le complément PowerPivot, sélectionnez Fichier dans le ruban. Sélectionnez Options puis Compléments. Au bas de la boîte de dialogue Compléments, sélectionnez Compléments COM, puis cliquez sur OK. Dans Compléments COM, sélectionnez Microsoft Office PowerPivot pour Excel et cliquez sur OK. Vous voyez maintenant un onglet PowerPivot sur le ruban.

 

Comment puis-je charger des données dans PowerPivot?

Après avoir activé PowerPivot, vous voyez un onglet PowerPivot sur le ruban. Cliquez sur l’onglet PowerPivot et cliquez sur le bouton Gérer pour afficher les options illustrées à la figure 1. Après avoir choisi Accueil, vous voyez l’onglet Accueil illustré à la figure 2.

FIGURE 1 Cette figure montre les options PowerPivot.

FIGURE 2 Cette figure montre l’onglet du ruban Accueil de la fenêtre PowerPivot.

Dans l’onglet Accueil, vous pouvez importer des données de plusieurs sources de données et formats, par exemple:

■■ Si vous sélectionnez Depuis la base de données, PowerPivot charge les données d’une base de données Access ou SQL Server.

■■ Si vous sélectionnez Depuis le rapport, PowerPivot charge les données d’un rapport SQL Server Reporting Services.

■■ Si vous sélectionnez À partir des flux de données, vous pouvez lire les données d’un site Web doté d’un flux OData.

■■ Pour plus d’informations sur les flux ODATA, voir http://mdn.microsoft.com/en-us/magazine

/ff714561.aspx.

■■ Choisissez Autres sources pour charger les données de toutes les sources de données prises en charge, y compris les fichiers Excel, les fichiers texte et de nombreux autres types de bases de données telles qu’Oracle et Teradata.

■■ Après avoir transformé les données Excel en tableau, vous pouvez sélectionner Coller pour lier les données à PowerPivot.

Pour illustrer comment télécharger des données de plusieurs sources dans PowerPivot, utilisez les magasins

Fichier , dans lequel sont répertoriées les transactions de vente de 20 magasins. Un sous-ensemble des données est présenté dans Figure 3.

Vous pouvez voir que pour chaque transaction, vous recevez le numéro de magasin, le produit vendu, la date de vente, les unités vendues et les revenus. Vous souhaitez résumer ces données par état, mais l’état de chaque magasin est répertorié dans un fichier différent,  L’emplacement de chaque magasin est illustré à la figure 4.

FIGURE 3 Cette figure montre les données de vente à charger dans PowerPivot.

FIGURE : 4 Cette figure montre l’emplacement de chaque magasin par état.

Vous souhaitez créer un tableau croisé dynamique qui vous permet de découper et de découper vos données afin que vous puissiez voir comment vous avez vendu chaque produit dans chaque état. Pour commencer, cliquez sur l’onglet PowerPivot, puis sur Gérer pour ouvrir la fenêtre PowerPivot. Voir l’onglet Accueil illustré à la figure 2. Étant donné que vous souhaitez importer un fichier texte, sélectionnez À partir du texte (c’est le dernier choix) dans À partir d’autres sources.

Comme le montre la figure 5, vous pouvez ensuite parcourir dans le fichier . Vous n’avez rien à saisir dans la section Nom de connexion convivial de la boîte de dialogue, mais le nom du fichier texte PowerPivot est choisi ici. Étant donné que la première ligne de données contient des en-têtes de colonne, sélectionnez Utiliser la première ligne comme en-têtes de colonne. Dans la liste Séparateur de colonnes, sélectionnez Tab, car les champs de données du fichier texte sont séparés par des tabulations. Cliquez sur Terminer, puis sur Fermer pour terminer le processus d’importation des données du fichier texte dans PowerPivot.

FIGURE 5 Configurer une importation de fichier texte pour PowerPivot.

La figure 6 montre le résultat après l’importation des données de texte dans PowerPivot. Un sous-ensemble des données est montré. En bas, vous pouvez voir un onglet indiquant que la source des données est Storesales.txt. L’onglet est le nom de la table et vous auriez pu créer un nom pendant le processus d’importation.

FIGURE 6 : Il s’agit d’un sous-ensemble des données importées .

Ensuite, vous souhaitez importer le fichier  afin de pouvoir vous associer aux données de vente. Pour importer , revenez à Excel en cliquant sur l’icône Basculer vers le classeur Excel dans le coin supérieur gauche (deuxième icône avec un X) du ruban PowerPivot. Ouvrez le fichier  et transformez les données en tableau. Copiez les données dont vous avez besoin. Sélectionnez Coller dans l’onglet Accueil de PowerPivot (voir la figure 2). La boîte de dialogue Coller l’aperçu illustrée à la figure 7 s’ouvre.

FIGURE : 7 Il s’agit de la boîte de dialogue Coller l’aperçu.

Sélectionnez Utiliser la première ligne comme en-têtes de colonne et modifiez le nom de la table en States. Après avoir cliqué sur OK, les données de States.xlsx sont importées dans PowerPivot, comme le montre la figure 8. Notez qu’en bas de la fenêtre, un onglet apparaît pour chaque tableau qui peut être utilisé pour créer des rapports.

FIGURE 8 Les données de deux sources sont maintenant importées dans PowerPivot.

Sinon, les données du fichier  auraient pu être importées dans Excel en sélectionnant Fichier Excel à partir d’autres sources, puis en accédant au fichier  et en sélectionnant la ou les feuilles de calcul que vous souhaitez importer.

Rappelez-vous que vous souhaitez analyser les ventes dans différents états. Le problème est qu’à l’heure actuelle, PowerPivot ne sait pas que la liste des emplacements de magasin à partir de States.xlsx correspond aux magasins répertoriés dans le fichier texte. Pour résoudre ce problème, vous devez créer une relation entre les deux sources de données. Pour créer cette relation, vous pouvez cliquer sur l’onglet du ruban Conception dans la fenêtre PowerPivot (voir Figure 2), choisissez Créer une relation et procédez comme indiqué dans le chapitre précédent, «Le modèle de données». Il est cependant beaucoup plus facile de cliquer sur Vue Diagramme dans l’onglet Accueil et de définir la relation requise en faisant glisser (comme illustré à la figure 9) une colonne au-dessus d’une autre du côté plusieurs vers le côté.

FIGURE 9 Le diagramme de relations vous permet de lier des magasins entre des sources de données.

Pour revenir à la vue des données, cliquez sur l’icône de la vue des données illustrée à la figure 2.

 

Comment puis-je utiliser PowerPivot pour créer un tableau croisé dynamique?

Vous êtes maintenant prêt à utiliser PowerPivot pour résumer les données de vente de votre entreprise via un tableau croisé dynamique. Pour une discussion sur les autres types de rapports pris en charge par PowerPivot, reportez-vous à nouveau à Ferrari et Russo (2013). Sélectionnez l’onglet Accueil dans la fenêtre PowerPivot, puis sélectionnez Tableau croisé dynamique. La boîte de dialogue Créer un tableau croisé dynamique s’ouvre et vous invite à choisir une nouvelle feuille de calcul ou un emplacement dans la feuille de calcul actuelle. Choisissez une nouvelle feuille de calcul. La boîte de dialogue Champs PowerPivot s’ouvre, illustrée à la figure 10.

FIGURE 10 Cette figure montre la boîte de dialogue Champs PowerPivot.

En cliquant sur les triangles à gauche des tableaux États et Magasins, vous avez accès à toutes les colonnes (désormais appelées champs) des données importées. L’objectif est d’obtenir une ventilation par état et par produit du revenu total et des unités vendues. Pour résumer les revenus et les unités vendues, faites glisser les champs Revenus et unités vers la zone Valeurs. Faites glisser State vers la zone Rows et Product vers la zone Columns pour organiser les champs de tableau croisé dynamique comme illustré à la figure 11. Notez que les champs utilisés dans le tableau croisé dynamique sont sélectionnés.

FIGURE 11 Cette figure montre l’affectation des champs pour créer un rapport de tableau croisé dynamique.

La partie du tableau croisé dynamique impliquant des CD, des DVD et des aliments est illustrée à la figure 12. Comme vous pouvez le voir, 3 881 DVD ont été vendus en Illinois (ILL) pour un revenu total de 2 295,76 $.

FIGURE 12 Cette figure montre le tableau croisé dynamique ventilant les ventes de produits par état.

Comment puis-je utiliser des trancheuses avec PowerPivot?

Dans l’article 43, «Utilisation de tableaux croisés dynamiques et de segments pour décrire les données», vous avez appris à utiliser des segments pour révéler des détails et différentes perspectives dans vos analyses de tableau croisé dynamique. Ici, vous créez des trancheuses qui résument  Et redimensionne les données pour tout sous-ensemble de produits et de magasins. Pour ce faire, vous pouvez cliquer avec le bouton droit n’importe où dans votre tableau croisé dynamique et sélectionner Trancheurs. Après avoir choisi Magasins et produits, vous voyez les segments résultants illustrés dans la figure 13.  Dans Slicer Tools, vous pouvez modifier l’apparence des slicers. Par exemple, vous pouvez modifier le segment Magasins pour qu’il comporte quatre colonnes. Comme décrit au chapitre 43, vous pouvez maintenir la touche Ctrl enfoncée  vous cliquez pour sélectionner plusieurs produits, magasins ou les deux. De plus, la touche Maj peut être utilisée pour sélectionner n’importe quelle plage contiguë dans une tranche. Le tableau croisé dynamique illustré dans la figure 13 donne le revenu total et les unités vendues de livres et de nourriture dans les magasins 7 à 11. Parce que les magasins 7 à 11 se trouvent tous dans l’Illinois ou le Michigan, ce sont les seuls états indiqués dans le tableau croisé dynamique résultant.

FIGURE 13 Cette figure montre les trancheuses de produits et de magasins.

 

Quelles sont les fonctions DAX?

Rappelez-vous de l’article  43 que vous pouvez générer de nouvelles formules dans un tableau croisé dynamique en utilisant des éléments calculés ou des champs calculés. Une fois vos données importées dans PowerPivot, vous pouvez utiliser le langage de formule DAX pour créer de nouvelles colonnes calculées qui rendent le modèle de données beaucoup plus significatif.

        Remarque Une discussion complète de DAX dépasse le cadre de ce chapitre. Microsoft PowerPivot pour Excel : Donner du sens à vos données comprend une excellente et complète discussion du Langage DAX.

Pour illustrer certaines formules DAX, voyez comment créer des colonnes calculées pour permettre l’analyse de l’année, du mois et du jour du mois. Pour commencer, cliquez sur l’onglet Storessales dans la fenêtre PowerPivot et sélectionnez la première colonne vide. Cliquer sur le bouton fx sous le ruban PowerPivot affiche une liste

des fonctions DAX. Beaucoup d’entre eux (tels que ANNEE, MOIS et JOUR) sont basés sur des fonctions Excel. La sélection de la catégorie Date et heure affiche la liste des fonctions DAX illustrées à la figure 14. Le langage DAX comprend de nombreuses autres fonctions puissantes. Par exemple, la fonction DISTINCT peut renvoyer un tableau de valeurs distinctes trouvées dans une colonne.

FIGURE14 Voici une liste des fonctions DAX.

Pour placer l’année, le mois et le jour du mois pour chaque transaction dans une colonne distincte, passez à la première cellule de la première colonne vide et tapez = ANNEE (st. Ensuite, vous êtes invité avec les colonnes des tables du modèle de données qui vous pouvez passer à la fonction YEAR. Sélectionnez la colonne Date et complétez la formule = ANNEE (storesales [Date]). La colonne est maintenant remplie avec l’année de chaque transaction de vente. En cliquant avec le bouton droit sur l’en-tête de la colonne, vous pouvez renommer le dans l’année. Dans la colonne suivante, calculez le mois de l’année en entrant la formule = MOIS (storesales [Date]). Dans la colonne suivante, calculez le jour du mois avec la formule = JOUR(storesales [Date]) . En cliquant avec le bouton droit sur chaque en-tête de colonne, vous pouvez renommer ces colonnes en Mois et Jour du mois. Le tableau avec les colonnes calculées est illustré à la figure 15.

FIGURE 15 Les colonnes calculées pour l’année, le mois et le jour du mois sont créées avec des formules DAX.

Vous pouvez maintenant créer une variété de tableaux croisés dynamiques informatifs. Par exemple, vous pouvez résumer les ventes dans chaque état par année. (Voir problème 1.)

S’abonner
Notifier de
0 Commentaires
Inline Feedbacks
Voir tous les commentaires

Initiation à Excel

Fonctions Excel

Excel VBA

Macros VBA Utiles

Plus d'outils

Sur Facebook

Sur YouTube

0
Nous aimerions avoir votre avis, veuillez laisser un commentaire.x