Création de solutions avec Microsoft PowerPivot pour Excel et SharePoint (traduction automatique)

Résumé : Apprenez à utiliser Microsoft PowerPivot pour créer, analyser et partager des classeurs Microsoft Excel 2010 de façon innovante en créant des solutions de bout en bout avec PowerPivot pour Excel et PowerPivot pour SharePoint.

Dernière modification : vendredi 24 avril 2015

S’applique à : Excel 2010 | Office 2007 | Office 2010 | Open XML | PowerPoint 2010 | SharePoint Foundation 2010 | SharePoint Server 2010 | VBA | Word 2010

Applies to:Microsoft Excel 2010 | Microsoft SharePoint Server 2010

Publication :   Avril 2011

Fournie par :Frank Rice, Microsoft Corporation

Sommaire

• Vue d'ensemble de Business Intelligence et Microsoft PowerPivot

• Scénario de PowerPivot

• La Solution pour le scénario

• Synopsis

• Ressources supplémentaires

Vue d'ensemble de Business Intelligence et Microsoft PowerPivot

Business intelligence (BI) fait référence à des techniques ordinateur sont utilisés pour analyser, de prévision et d'un rapport sur les données d'entreprise pour améliorer les décisions commerciales. BI permet de transformer les données brutes en informations utiles et pertinentes. Il condense des quantités énormes de données dans des structures multidimensionnelles que vous pouvez utiliser dans les tableaux croisés dynamiques et les graphiques croisés dynamiques dans Microsoft Excel.

L'un des inconvénients des précédentes versions de Excel est la limite du nombre de lignes que vous pouvez manipuler. Pour Microsoft Office Excel 2007 et versions ultérieures, cette limite est un million de lignes. En revanche, Microsoft SQL Server PowerPivot pour Excel vous permet de créer des applications BI qui peuvent contenir des dizaines, voire même des centaines de millions de lignes de données. PowerPivot pour Excel permet de créer et modifier des applications qui peuvent intégrer des données provenant de diverses sources de données externes, en améliorant ces données avec des calculs personnalisés et à l'aide de ces données pour l'analyse dans les tableaux croisés dynamiques et les graphiques croisés dynamiques.

PowerPivot pour Excel est gratuit et géré dans un complément qui étend les capacités de Excel 2010. Il permet aux utilisateurs de créer et modifier des applications de PowerPivot et d'utiliser l'interface Excel pour travailler avec des données de PowerPivot.

Cet article traite des, les applications PowerPivot ressemblent exactement à régulière Excel classeurs. Toutefois, ils sont beaucoup plus de données et métadonnées incorporées dans le classeur que dispose d'un classeur normal qui ne sont pas connectée à une source de données externe. En outre, vous pouvez vous connecter de nombreux autres types de sources de données externes de l'application PowerPivot. Pour plus d'informations sur PowerPivot pour Excel, consultez Ressources supplémentaires.

Dans les versions précédentes de Excel, il est difficile de partager des données, les calculs et les autres fonctionnalités de classeur avec d'autres utilisateurs. Dans de nombreux cas, les collaborateurs utilisé par e-mail ou le classeur vers un partage sur le réseau, habituellement produites de nombreuses versions du classeur dans une organisation. La synchronisation des données à partir de ces différentes versions a été très longue et difficile.

Avec PowerPivot pour SharePoint, vous pouvez collaborer, partager et créer des rapports sur les données dans des applications PowerPivot pour Excel. En d'autres termes, utilisez PowerPivot pour Excel pour créer des applications de PowerPivot et PowerPivot pour SharePoint pour partager ces applications. PowerPivot pour SharePoint peut également fournir une fonctionnalité de client léger pour applications de PowerPivot lorsqu'il est utilisé avec Excel Services.

Contrairement à PowerPivot pour Excel, PowerPivot pour SharePoint installe par-dessus SharePoint Server 2010. Elle nécessite que vous utilisez Windows Server 2008 R2 ou Windows Server 2008 Service Pack 2 sur un ordinateur 64 bits et que vous utilisez Microsoft SQL Server 2008 R2 Enterprise Edition.

Après l'installation de PowerPivot pour SharePoint, vos classeurs PowerPivot publiées se trouvent dans une galerie de PowerPivot vous permet d'afficher les classeurs dans différentes vues interactives. Vous pouvez également créer des rapports du classeur en cours de PowerPivot et gérer la planification d'actualisation des données à partir de la galerie.

Vous trouverez des liens vers plus d'informations sur PowerPivot pour SharePoint plus loin dans cet article. Vous trouverez plus d'informations, visitez la page d'accueil PowerPivot (éventuellement en anglais) .

Scénario de PowerPivot

Pour voir les avantages des applications de PowerPivot, envisagez le scénario suivant.

Imaginez que vous êtes un directeur des ventes et que vous souhaitez examiner les données de ventes pour chacun de vos canaux de vente entre les années 2007 et 2009. Vous souhaitez également partager facilement des données avec d'autres responsables.

Dans ce scénario, vous suivez ces étapes :

• Importer des données dans le classeur à partir d'une source de données externe.

• Appliquer un filtre à une table.

• Créer des relations entre les tables.

• Créer des calculs à l'aide d'Expressions d'analyse de données (DAX).

• Créer et manipuler un tableau croisé dynamique et graphique croisé dynamique pour afficher des données de vente.

• Partager le classeur à l'aide de PowerPivot pour SharePoint.

La Solution pour le scénario

Les sections suivantes fournissent la solution pour le scénario.

Importation de données à partir d'une base de données relationnel

Tout d'abord, vous devez obtenir les données dans la fenêtre de PowerPivot. Vous pouvez importer des données dans des classeurs de PowerPivot pour Excel à partir de différentes sources :

• Bases de données relationnelles

• SQL Server Analysis Services

• États Reporting Services

• fichiers texte ;

• tables du classeur Excel

• Le Presse-papiers

Dans cette section, vous importez des données à partir d'une base de données relationnelle. Dans ce cas, vous utilisez la base de données exemple de Northwind et importez les tables Employees et Orders dans Excel.

Pour importer des données à partir d'une base de données relationnel

1. Ouvrez le classeur PowerPivotTutorialSample.xlsx. Sous l'onglet PowerPivot , cliquez sur Fenêtre PowerPivot.

2. Dans la fenêtre PowerPivot, cliquez sur le bouton De la base de données et choisissez À partir d'Access.

3. Dans la boîte de dialogue Assistant Importation de table , cliquez sur le bouton Parcourir par un Nom de la base de donnéeset accédez à la base de données Northwind. Par défaut, il est nommé Database2.accdb après le téléchargement.

4. Cliquez sur Next.

5. Dans la page Choisir comment importer les données , utilisez Sélectionner dans une liste de tables et de vues à choisir les données à importer et puis cliquez sur Suivant.

6. Dans la colonne de la Données source , sélectionnez les Employés , puis sur Aperçu et filtrage.

7. Dans la page de la Aperçu de la table sélectionnée , faites défiler vers la droite jusqu'à la colonne Pièces jointes .

8. Désactivez la case à côté du titre de la colonne, puis sur OK pour que la colonne d'être importés dans Excel.

9. Notez que dans la page de Aperçu de la table sélectionnée dans la colonne Détails du filtre de la table Employés , Filtres appliqués est maintenant affiché pour vous informer qu'un filtre est appliqué à la table (voir Figure 1). Cliquez sur Terminer.

   La figure 1. Un filtre est appliqué à la table

   

    

10. Une fois que vous importez la table Employee dans la fenêtre PowerPivot, cliquez sur Fermer.

Importer des données à partir d'une Table liée

Dans cette section, vous importez des données à partir d'une table liée dans le classeur d'exemple. Tout d'abord, vous importez une table dans Excel à partir de la base de données Northwind. Ensuite, vous créez un lien vers la nouvelle feuille de calcul. Enfin, vous établissez une relation entre la nouvelle table et de la table Employee dans la fenêtre PowerPivot.

Pour importer des données à partir d'une table liée

1. Si vous êtes toujours dans la fenêtre PowerPivot, cliquez sur l'icône Excel dans le coin supérieur gauche pour basculer vers le classeur Excel.

2. Dans Excel, cliquez sur l'onglet Données .

3. Dans le groupe de Données externes , cliquez sur À partir d'Access.

4. Dans la boîte de dialogue Sélectionner la source de données , accédez à la base de données Northwind (Database2.accdb) et puis cliquez sur Ouvrir.

   Dans la boîte de dialogue Sélectionner le tableau vers le bas, sélectionnez la table Commandes et puis cliquez sur OK.

   Dans la boîte de dialogue Importer des données dans la section Insérer les données dans , sélectionnez Nouvelle feuille de calcul (voir Figure 2), puis cliquez sur OK pour importer de la table dans une feuille de calcul.

   La figure 2. Boîte de dialogue Importer données

   

    

5. Cliquez droit sur l'onglet de la nouvelle feuille de calcul et renommez-le Orders.

6. Ensuite, vous importer des données à partir de la feuille de calcul que vous venez de créer dans la fenêtre PowerPivot. Cliquez sur l'onglet Commandes et cliquez sur l'onglet PowerPivot , l'icône Créer une table liée .

7. PowerPivot crée une nouvelle table liée avec les données source à partir de la feuille de calcul de Commandes . Dans la fenêtre PowerPivot, vous voyez une nouvelle table appelée Table_Database2.accdb et un symbole de lien affiché sous l'onglet.

8. Droit du nouvel onglet de feuille de calcul, puis renommez-le Orders.

9. Créez maintenant une relation entre la table employés et de la table Orders dans la fenêtre PowerPivot. Sur le ruban, cliquez sur l'onglet Création et puis cliquez sur Créer une relation.

10. Dans la boîte de dialogue Créer une relation de recherche entre deux tables sous la liste déroulante Tables supérieure, sélectionnez la table Commandes et puis, dans la liste déroulante de la Colonne , sélectionnez EmployeesID comme illustré à la Figure 3.

11. Dans la liste déroulante de Table de recherche associée , sélectionnez la table Employés et dans la liste déroulante de la Colonne de recherche associée , sélectionnez l' ID comme illustré à la Figure 3.

    La figure 3. Créer une relation entre la boîte de dialogue de deux tables de recherche

    

     

12. Cliquez sur Créer pour créer la relation.

    Pour afficher cette relation ou toutes les relations entre les tables, cliquez sur Gérer les relations dans l'onglet Création .

Créer des calculs à l'aide d'Expressions de l'analyse des données

Dans cette section, vous créez des calculs à l'aide d'Expressions d'analyse de données (DAX). DAX fournissent un ensemble de fonctions et opérateurs de vous aideront à écrire des expressions pour les calculs que vous souhaitez appliquer à vos données PowerPivot. Les fonctions DAX ont une inclinaison de base de données relationnelle (un focus sur les tables et colonnes) qui les rend plus facile pour les utilisateurs Excel à appliquer. En outre, après avoir créé un calcul DAX, vous pouvez créer et graphiques croisés qui sembleront familiers aux utilisateurs de Excel.

Vous pouvez regrouper les fonctions DAX dans les principaux suivants :

• Maths et trigonométrie

• Date et heure

• Statistiques

• Logique

• Text

• Filtre

• Assistant Time Intelligence

Vous pouvez regrouper les calculs DAX dans les catégories suivantes :

• Créer des colonnes calculées en utilisant des fonctions simples tels que des sommes et leur nombre ou en créant des calculs plus complexes à l'aide de plusieurs tables et les agrégations.

• À l'aide de mesures, qui sont agrégées des valeurs qui sont généralement ajoutés à la zone de dépôt Valeurs sur une liste de champs de tableau croisé dynamique.

Pour voir DAX en action, effectuez les opérations dans les procédures qui suivent. Pour l'instant, considérez que la feuille de calcul dbo_FactSales , il y a des colonnes TotalSales et PrixTotal . Pour calculer la marge, vous venez de créer l'expression Profit = TotalSales – CoûtTotal et PowerPivot applique automatiquement le calcul pour chaque ligne dans la colonne. Pour faire la même chose dans Excel, vous devez créer l'expression et puis collez l'expression dans chaque ligne.

Pour calculer le balisage à l'aide de DAX

1. Dans la feuille de calcul dbo_FactSales , faites défiler jusqu'à la dernière colonne et sélectionnez-le.

2. Dans l'expression de fenêtre tapez =, puis faites défiler vers la gauche et sélectionnez la colonne PrixUnitaire . DboFactSales [PrixUnitaire] sont ajoutés à l'expression.

3. Tapez le symbole moins – , puis sélectionnez la colonne PrixUnitaire .

4. Appuyez sur la touche ENTRÉE. Une fois que vous appuyez sur la touche entrée, PowerPivot calcule le balisage pour chaque ligne et enregistre les résultats dans la nouvelle colonne.

5. Cliquez sur l'en-tête de colonne CalculateColumn1 et renommez-le balisage comme illustré à la Figure 4.

   La figure 4. Colonne de balisage

   

    

Examinez à présent un calcul différent entre les tables connexes. Ici, vous souhaitez rechercher la marge totale pour les ventes d'un groupe de produits. Vous pouvez utiliser DAX dans les procédures suivantes pour calculer le bénéfice total des ventes du produit.

Pour calculer les ventes de produits à l'aide de DAX

1. Dans la table dbo_DimProduct , accédez à la dernière colonne et tapez = SUMX(RELATEDTABLE(dbo_FactSales), dbo_FactSales[TotalProfit]).

2. Appuyez sur la touche ENTRÉE. Le bénéfice total pour chaque produit s'affiche dans la ligne de ce produit, comme illustré à la Figure 5.

3. Cliquez sur l'en-tête de colonne et nommez-le TotalProfit.

   La figure 5. Colonne de TotalProfit

   

    

Il existe une relation un-à-plusieurs entre les tables dbo_DimProduct et dbo_FactSales , où un produit peut contenir plusieurs entrées dans la table de données de vente. La fonction RELATEDTABLE permet de récupérer les données de la table source (dbo_FactSales) dans la table en cours (dbo_DimProduct). Lorsque les données sont retournées à partir de la table source, la fonction SUMX regroupe les données pour chaque ligne de la table de destination. Et étant donné que chaque ligne de la dbo_DimProduct spécifie un produit unique, les données à partir de la table connexe sont le total des bénéfices pour chaque produit.

Créer un tableau et graphique croisé dynamique

Si vous souhaitez partager votre analyse avec vos collègues ou de gestion, vous devez le placer dans un formulaire qui est facile à manipuler et examiner. Dans cette section, vous créez un tableau croisé dynamique avec les données dans la fenêtre PowerPivot. Notez que si vous avez déjà le fichier Excel, le tableau croisé dynamique et graphique croisé dynamique existe déjà dans la feuille de calcul intitulée Ventes par canal; Toutefois, vous pouvez créer un tableau croisé dynamique et un graphique croisé dynamique similaire dans les procédures suivantes.

La figure 6 illustre le résultat de deux procédures suivantes.

La figure 6. Tableau croisé dynamique terminé

Pour créer le tableau croisé dynamique

1. Sous l'onglet Home de la fenêtre PowerPivot, cliquez sur le menu déroulant Tableau croisé dynamique et sélectionnez le Graphique et tableau (à l'horizontale).

2. Dans la boîte de dialogue Créer un graphique croisé dynamique et un tableau croisé dynamique (à l'horizontale) , sélectionnez l'option pour la Nouvelle feuille de calculet puis cliquez sur OK. Une nouvelle feuille de calcul intitulée Feuil1 est affiché avec les mises en page pour un tableau croisé dynamique et un graphique croisé dynamique-côte comme illustré à la Figure 6. Notez également qu'il existe une liste de champs de PowerPivot est similaire à la liste de champs pour les tableaux croisés dynamiques régulières avec les ajouts de zones de dépôt pour Découpages verticaux et Découpages horizontaux. TRANCHEURS vous aident à filtrer et analyser des données sur les tableaux croisés dynamiques et les graphiques croisés dynamiques associés. La liste de champs contient toutes les tables dans la fenêtre de PowerPivot avec la première table développée.

   La figure 7. Feuille de tableau croisé dynamique et graphique croisé dynamique

   

    

3. Ensuite, développez la table dbo_FactSales et puis faites glisser le champ TotalSales vers la zone de dépôt de Valeurs . Colonnes dans la zone de dépôt de Valeurs sont des mesures, qui signifie que vous pouvez regrouper le contenu de la colonne. Avec le bouton droit de la mesure TotalSales et cliquez sur Modifier la mesure, puis dans la zone Nom personnalisé , modifiez le nom de ventes par canal. Notez également que vous avez la possibilité de modifier le type d'agrégation pour les valeurs de cette colonne à Somme, Nombre, Min, **Max.**ou Moyenne , comme illustré à la Figure 8.

   La figure 8. Modification d'un nom de mesure

   

    

4. Dans la liste des tables, développez la table dbo_DimChannel et puis faites glisser la colonne ChannelName vers la zone de dépôt Étiquettes de lignes pour afficher les circuits de vente de CataloguesEn ligne, Revendeur, les étiquettes de ligne de Banque du tableau croisé dynamique.

5. Développez la table dbo_DimDate et faites-le glisser vers le CalendarQuarter à la zone de dépôt Étiquettes de colonnes pour ajouter les étiquettes de trimestre et un an ajouté comme en-têtes de colonnes du tableau croisé dynamique.

6. Ensuite, à partir de la table dboDimDate , faites glisser les champs de Année, Trimestreet Mois pour la zone de dépôt Découpages verticaux . Ces TRANCHEURS sont affichées sur le côté gauche du tableau croisé dynamique dans la Figure 6.

Vous venez de créer une feuille de données que vous pouvez analyser. TRANCHEURS peuvent filtrer les données du tableau croisé dynamique par année, mois et trimestre.

Pour créer le graphique croisé dynamique

1. Cliquez sur 2008 dans le découpage de Année .

2. Sélectionnez le graphique croisé dynamique.

3. La table dbo_FactSales , faites glisser la colonne TotalSales sur la zone de dépôt de Valeurs .

4. Développez la table dbo_DimChannel et faites glisser la colonne ChannelName sur la zone de dépôt des Champs Axe .

5. À partir de la table dbo_DimDate , faites glisser la colonne CalendarQuarter dans la zone de dépôt du Champ Légende . Le graphique croisé dynamique reflète désormais les ventes pour chaque canal et le trimestre en 2008 comme illustré à la Figure 9. Notez également que le tableau croisé dynamique modifie les données affichées pour les TRANCHEURS sélectionnés.

   La figure 9. Graphique croisé dynamique reflète les données de ventes pour chaque canal chaque trimestre 2008

   

    

Partage des données avec d'autres personnes

Il existe de nombreuses façons de partager un classeur Excel dans toute l'organisation. Une traditionnelle consiste à créer un partage de fichiers sur un serveur et télécharger le rapport de ventes pour le partage. Cette solution vous oblige à donner à chaque utilisateur les autorisations pour afficher et télécharger le rapport. Une autre manière traditionnelle pour partager un classeur est d'envoyer une version à chaque gestionnaire. Le plus grand inconvénient de cette solution est le contrôle de version ; Si un seul gestionnaire modifie une copie locale du classeur, vous devez synchroniser une certaine manière de chaque modification avec un classeur maître et redistribuez ensuite le classeur mis à jour pour chacun des gestionnaires.

PowerPivot offre des fonctionnalités qui sont intégrés avec SharePoint Server 2010 et qui permettent de partager et de gérer les données de PowerPivot dans des classeurs. PowerPivot for SharePoint vous permet également d'interagir avec les classeurs PowerPivot publiés.

Pour installer et configurer le PowerPivot pour SharePoint sur votre ordinateur Windows Server 2008 R2, voir d'Installation (PowerPivot pour SharePoint) sur MSDN. Vous trouverez également des liens vers plus de ressources à la fin de cet article.

Pour vérifier que PowerPivot pour SharePoint est installé et configuré correctement, ouvrez un navigateur web en mode administrateur et accédez à https://servername, où NomServeur est le nom de l'ordinateur sur lequel vous avez installé PowerPivot pour SharePoint. Vous devez voir un lien vers la galerie de PowerPivot sur le côté gauche de la page, comme illustré à la Figure 10.

La figure 10. Galerie de PowerPivot

Vous êtes maintenant prêt à publier votre classeur.

Pour publier le classeur

1. Ouvrez le classeur PowerPivotTutorialSample.xlsx.

2. Cliquez sur l'onglet Fichier , cliquez sur Enregistrer et envoyer, cliquez sur Enregistrer dans SharePointet puis cliquez sur Enregistrer sous.

3. Tapez https://servername/PowerPivot galerie dans le chemin du dossier de la boîte de dialogue Enregistrer sous et puis cliquez sur Enregistrer. Excel publie le classeur à votre serveur SharePoint Server 2010 et l'ouvre dans le navigateur web comme le montre la Figure 11. Excel Services est responsable du rendu du classeur dans le navigateur web. Vous pouvez maintenant interagir avec le classeur exactement comme vous le feriez si vous l'aviez ouvert dans Excel.

   La figure 11. Classeur est restitué par Excel Services

   

    

Une fois que vous publiez votre classeur PowerPivot à la galerie de PowerPivot, PowerPivot affiche le classeur en utilisant différentes vues, comme illustré à la Figure 12. Ces vues permettent de travailler de manière interactive avec le classeur. Survolez chaque vue miniature et de voir l'agrandir sur le côté gauche de la fenêtre.

La figure 12. Vues de la galerie de PowerPivot

La galerie de PowerPivot vous permet également de créer des rapports de Excel ou Reporting Services et actualiser les données de PowerPivot à intervalles définis. Dans la Figure 12, ils sont les deux icônes dans le coin supérieur droit de la fenêtre.

Synopsis

Cet article vous présente Microsoft PowerPivot. Vous avez appris comment intégrer des données provenant de diverses sources externes PowerPivot pour Excel, puis d'analyser ces données à l'aide de DAX. Vous avez créé un tableau et graphique croisé dynamique que vous pouvez utiliser pour consulter les données dans de nombreux affichages. Vous avez vu comment faire pour télécharger le classeur à PowerPivot pour SharePoint afin qu'elles puissent être partagées avec d'autres utilisateurs. Cet article qu'effleurer tout qui est possible avec Microsoft PowerPivot. Expérimenter avec vos propres classeurs PowerPivot et utiliser ces techniques à votre propre avantage !

Ressources supplémentaires

Pour plus d’informations sur les sujets traités dans cet article, voir les ressources suivantes.

• Site Web de Microsoft PowerPivot (éventuellement en anglais)

• PowerPivot Excel (éventuellement en anglais)

• PowerPivot Overview

• guide étape par étape sur l'installation de PowerPivot pour Excel (éventuellement en anglais)

• Guide étape par étape : PowerPivot pour l'installation de SharePoint sur un seul ordinateur (éventuellement en anglais)

• Installation (PowerPivot for SharePoint)

• SQL Server PowerPivot pour SharePoint (éventuellement en anglais)