Didacticiel : analyse de données de tableau croisé dynamique à l'aide d'un modèle de données dans Excel 2013

En moins d’une heure, vous pouvez créer un rapport de tableau croisé dynamique dans Excel qui combine des données issues de plusieurs tables. La première partie de ce didacticiel vous guide pas-à-pas dans l’importation et l’exploration de données. Dans la deuxième partie, vous utiliserez le complément Power Pivot afin d’affiner le modèle de données qui se trouve derrière le rapport, et apprendrez ainsi à ajouter des calculs et des hiérarchies, tout en optimisant la création de rapports Power View.

Importer des données

Vous devez disposer de données avant de pouvoir créer un tableau croisé dynamique. Commençons par obtenir des données de ventes à partir de la base de données d’exemple que vous avez téléchargée.

  1. Téléchargez des exemples de données (ContosoV2) pour ce didacticiel. Pour plus d’informations, voir Obtenir des exemples de données pour les didacticiels du modèle de données et DAX. Extrayez et enregistrez les fichiers de données dans un emplacement facilement accessible, tel que Téléchargements ou Mes documents.

  2. Dans Excel, ouvrez un classeur vierge.

  3. Cliquez sur Données > Obtenir des données externes > À partir d’Access.

  4. Accédez au dossier contenant les fichiers d’exemples de données et sélectionnez ContosoSales.

  5. Cliquez sur Ouvrir. Comme vous vous connectez à un fichier de base de données qui contient plusieurs tables, la boîte de dialogue Sélectionner une table apparaît pour que vous puissiez choisir les tables à importer.

    Boîte de dialogue Sélectionner la table

  6. Dans Sélectionner une table, cochez Activer la sélection de plusieurs tables.

  7. Choisissez toutes les tables et cliquez sur OK.

  8. Dans Importer des données, cliquez sur Rapport de tableau croisé dynamique, puis cliquez sur OK.

Que s’est-il passé ?

Vous ne vous en êtes peut-être pas encore rendu compte, mais vous venez tout juste de créer un modèle de données. Les modèles de données se créent automatiquement lorsque vous importez ou travaillez en simultané avec plusieurs tables dans le même rapport de tableau croisé dynamique. Le modèle est presque totalement transparent dans Excel, mais vous pouvez l’afficher et le modifier directement à l’aide du complément Power Pivot. Dans Excel, la présence d’un modèle de données semble évident quand vous voyez un ensemble de tableaux dans la liste Champs de tableau croisé dynamique. Il existe plusieurs façons de créer un modèle. Pour plus d’informations, voir Créer un modèle de données dans Excel.

Explorer des données à l'aide d'un tableau croisé dynamique

L’exploration des données est simple quand vous faites glisser des champs vers les zones Valeurs, Colonnes et Lignes de la liste de champs du tableau croisé dynamique.

  1. Dans la liste de champs, faites défiler vers le bas jusqu’à ce que la table FactSales apparaisse.

  2. Cliquez sur SalesAmount. Ces données étant numériques, Excel place automatiquement SalesAmount dans la zone Valeurs.

  3. Dans DimDate, faites glisser CalendarYear vers Colonnes.

  4. Dans DimProductSubcategory, faites glisser ProductSubcategoryName vers Lignes.

  5. Dans DimProduct, faites glisser BrandName vers Lignes, et placez-le ainsi sous la sous-catégorie.

Votre tableau croisé dynamique doit ressembler à celui de l’écran ci-dessous.

Tableau croisé dynamique comprenant un exemple de données

En un minimum d'efforts, vous disposez maintenant d'un tableau croisé dynamique de base qui comprend des champs de quatre tables différentes. C’est grâce aux relations préexistantes entre les tables que cette tâche a été si simple. Comme les relations de table existaient dans la source et comme vous avez importé toutes les tables en une seule opération, Excel a pu recréer ces relations dans le modèle.

Que se passerait-il si les données provenaient de sources différentes ou si elles étaient importées ultérieurement ? En règle générale, vous pouvez incorporer de nouvelles données en créant des relations selon des colonnes correspondantes. Dans l’étape suivante, vous allez importer d’autres tables et découvrir les exigences et les étapes nécessaires pour créer des relations.

Ajouter des tables supplémentaires

Découvrir comment configurer des relations nécessite d’autres tables non connectées à utiliser. Dans cette étape, vous allez obtenir les données restantes utilisées dans ce didacticiel en important un autre fichier de base de données et en collant des données à partir de deux autres classeurs.

Ajouter des catégories de produits

  1. Dans le classeur, ouvrez une nouvelle feuille. Vous l'utiliserez pour stocker des données supplémentaires.

  2. Cliquez sur Données > Obtenir des données externes > À partir d’Access.

  3. Accédez au dossier contenant les fichiers d’exemples de données et sélectionnez ProductCategories. Cliquez sur Ouvrir.

  4. Dans Importer des données, sélectionnez Table et cliquez sur OK.

Ajouter des données géographiques

  1. Insérez une autre feuille.

  2. Parmi les fichiers d’exemples de données, ouvrez Geography.xlsx, placez le curseur dans A1, puis appuyez sur Ctrl-Maj-Fin pour sélectionner toutes les données.

  3. Copiez les données dans le Presse-papiers.

  4. Collez des données dans la feuille vide que vous venez d'ajouter.

  5. Cliquez sur Mettre sous forme de tableau et choisissez un style. La mise en forme des données en tant que tableau permet de leur attribuer un nom, ce qui vous facilitera la tâche au moment de définir des relations dans une étape ultérieure.

  6. Dans Mettre sous forme de tableau, vérifiez que l'option Mon tableau comporte des en-têtes est sélectionnée. Cliquez sur OK.

  7. Nommez la table Geography. Dans Outils de tableau > Création, tapez Geography dans Nom du tableau.

  8. Fermez Geography.xlsx pour le faire disparaître de votre espace de travail.

Ajouter des données de magasin

  • Répétez les étapes précédentes pour le fichier Stores.xlsx, en collant son contenu dans une feuille vide. Nommez le tableau « Stores ».

Vous devez maintenant disposer de quatre feuilles. Feuil1 contient le tableau croisé dynamique, Feuil2 contient ProductCategories, Feuil3 contient Geography et Feuil4 Stores. Étant donné que vous avez pris le temps de nommer chaque table, l’étape suivante, la création de relations, sera beaucoup plus simple.

Utiliser des champs provenant des tables nouvellement importées

Vous pouvez commencer immédiatement à utiliser des champs des tables que vous venez d’importer. Si Excel ne peut pas déterminer comment incorporer un champ dans le rapport de tableau croisé dynamique, il vous sera demandé de créer une relation qui associe la nouvelle table avec une table qui appartient déjà au modèle.

  1. En haut de la liste Champs de tableau croisé dynamique, cliquez sur Tous pour afficher la liste complète des tables.

  2. Faites défiler la liste vers le bas où vous trouverez les nouvelles tables que vous venez d’ajouter.

  3. Développez Stores.

  4. Faites glisser StoreName vers la zone Filters.

  5. Notez qu’Excel vous invite à créer une relation. Cette notification s’affiche, car vous avez utilisé des champs d’une table qui n’est pas liée au modèle.

  6. Cliquez sur Créer pour ouvrir la boîte de dialogue Créer une relation.

  7. Dans Table, choisissez FactSales. Dans les exemples de données que vous utilisez, FactSales contient des informations détaillées de ventes et de coûts relatives à la société Contoso, ainsi que des clés à d’autres tables, notamment les codes de magasin qui sont également présents dans le fichier Stores.xlsx que vous avez importé à l’étape précédente.

  8. Dans Colonne (étrangère), choisissez StoreKey.

  9. Dans Table liée, choisissez Stores.

  10. Dans Colonne associée (primaire), choisissez StoreKey.

  11. Cliquez sur OK.

En arrière-plan, Excel génère un modèle de données qui peut être utilisé dans le classeur dans plusieurs tableaux croisés dynamiques, graphiques croisés dynamiques ou rapports Power View. Dans ce modèle, les relations qui découlent d'une table à l'autre sont fondamentales, du fait qu'elles déterminent les chemins de navigation et de calcul utilisés dans un rapport de tableau croisé dynamique. Dans la tâche suivante, vous allez créer des relations manuellement pour connecter les données que vous venez d'importer.

Ajouter des relations

Vous pouvez créer systématiquement des relations pour toutes les nouvelles tables importées. Si vous partagez le classeur avec des collègues, ces derniers seront ravis d’avoir des relations prédéfinies s’ils ne maîtrisent pas les données.

Lorsque vous créez des relations manuellement, vous travaillez avec deux tables à la fois. Pour chaque table, vous choisissez les colonnes qui indiquent à Excel comment rechercher les lignes associées dans une autre table.

Votre navigateur ne prend pas en charge la vidéo. Installez Microsoft Silverlight, Adobe Flash Player ou Internet Explorer 9.

Mise en relation de ProductSubcategory à ProductCategory

  1. Dans Excel, cliquez sur Données > Relations > Nouveau.

  2. Dans Table, choisissez DimProductSubcategory.

  3. Dans Colonne (étrangère), choisissez ProductCategoryKey.

  4. Dans Table liée, choisissez Table_ProductCategory.accdb.

  5. Dans Colonne associée (primaire), choisissez ProductCategoryKey.

  6. Cliquez sur OK.

  7. Fermez la boîte de dialogue Gérer les relations.

Ajouter des catégories au tableau croisé dynamique

Bien que le modèle de données ait été mis à jour afin d’inclure des tables et des relations supplémentaires, le tableau croisé dynamique ne les utilise pas encore. Dans cette tâche, vous allez ajouter ProductCategory à la liste des champs de tableau croisé dynamique.

  1. Dans les champs de tableau croisé dynamique, cliquez sur Tout pour afficher les tables qui existent dans le modèle de données.

  2. Faites défiler vers le bas de la liste.

  3. Dans la zone Lignes, supprimez BrandName.

  4. Développez Table_DimProductCategories.accdb.

  5. Faites glisser ProductCategoryName vers la zone Lignes, en le plaçant au-dessus de ProductSubcategory.

  6. Dans les champs de tableau croisé dynamique, cliquez sur Actif pour vérifier que les tables que vous venez d'utiliser sont maintenant connues du tableau croisé dynamique.

Point de contrôle : vérification des acquis

Vous disposez maintenant d'un tableau croisé dynamique qui comprend des données de plusieurs tables, plusieurs d'entre elles ayant été importées par vos soins à une autre étape. Pour effectuer ce travail, vous deviez créer des relations entre les tables afin qu'Excel les utilise pour corréler les lignes. Vous avez appris qu'il était indispensable d'avoir des colonnes qui fournissent des données correspondantes pour pouvoir rechercher des lignes connexes. Dans les fichiers d'exemples de données, toutes les tables contiennent une colonne pouvant être utilisée à cet effet.

Bien que le tableau croisé dynamique soit fonctionnel, vous avez peut-être remarqué plusieurs éléments susceptibles d'être améliorés. La liste de champs du tableau croisé dynamique semble comporter des tables supplémentaires (DimEntity) et des colonnes (ETLLoadID) qui ne sont pas liées à l'entreprise Contoso. Et, nous n'avons toujours pas intégré les données Geography.

À suivre : afficher et étendre votre modèle avec Power Pivot

Dans la prochaine série de tâches, vous allez utiliser le complément Microsoft Office Power Pivot dans Microsoft Excel 2013 afin d’étendre le modèle. Vous constaterez que vous pouvez créer des relations plus facilement à l’aide de la vue de diagramme fournie par le complément. Vous utiliserez également le complément pour créer des calculs et des hiérarchies, masquer des éléments qui ne doivent pas apparaître dans la liste de champs et optimiser les données en vue de la création de rapports supplémentaires.

Remarque : Le complément Power Pivot dans Microsoft Excel 2013 est disponible dans Office Professionnel Plus. Pour plus d’informations, voir Complément Power Pivot in Microsoft Excel 2013.

Ajoutez Power Pivot au ruban Excel en activant le complément Power Pivot.

  1. Accédez à Fichier > Options > Compléments.

  2. Dans la zone Gérer, cliquez sur Compléments COM> Atteindre.

  3. Activez la case à cocher Microsoft Office Power Pivot dans Microsoft Excel 2013, puis cliquez sur OK.

Le ruban comporte désormais un onglet Power Pivot.

Ajouter une relation à l'aide de la vue de diagramme dans Power Pivot

  1. Dans Excel, cliquez sur Feuil3 pour en faire la feuille active. Feuil3 contient la table Geography que vous avez importée précédemment.

  2. Sur le ruban, cliquez sur Power Pivot > Ajouter au modèle de données. Cette étape ajoute la table Geography au modèle. Elle ouvre également le complément Power Pivot que vous utiliserez pour effectuer les étapes restantes de cette tâche.

  3. Vous pouvez remarquer que la fenêtre Power Pivot affiche toutes les tables du modèle, notamment Geography. Cliquez sur plusieurs tables. Dans le complément, vous pouvez afficher toutes les données contenues dans votre modèle.

  4. Dans la fenêtre Power Pivot, dans la section Vue, cliquez sur Vue de diagramme.

  5. Utilisez la barre coulissante pour redimensionner le diagramme afin que vous puissiez voir tous les objets qu’il contient. Notez que deux tables ne sont pas liées au reste du diagramme : DimEntity et Geography.

  6. Cliquez avec le bouton droit sur DimEntity, puis sélectionnez Supprimer. Cette table est un artefact de la base de données d’origine. Elle n’est pas utile dans le modèle.

  7. Effectuez un zoom avant sur Geography afin que vous puissiez voir tous les champs de cette table. Vous pouvez utiliser le curseur pour agrandir le diagramme de la table.

  8. Notez que la table Geography comporte une colonne GeographyKey. Cette colonne contient des valeurs qui identifient de manière unique chaque ligne dans la table Geography. Regardons si d’autres tables du modèle utilisent également cette clé. Dans l’affirmative, nous pouvons créer une relation permettant de connecter la table Geography au reste du modèle.

  9. Cliquez sur Rechercher.

  10. Dans Rechercher les métadonnées, tapez GeographyKey.

  11. Cliquez plusieurs fois sur Suivant. Vous remarquerez que GeographyKey apparaît dans la table Geography et dans la table Stores.

  12. Repositionnez la table Geography de façon à la placer en regard de Stores.

  13. Faites glisser la colonne GeographyKey de la table Stores vers la colonne GeographyKey dans Geography. Power Pivot trace une ligne entre les deux colonnes pour montrer la relation.

Dans cette tâche, vous avez appris une nouvelle technique pour ajouter des tables et créer des relations. Vous disposez désormais d'un modèle entièrement intégré, dont toutes les tables sont connectées et qui est disponible dans le tableau croisé dynamique dans Feuil1.

Conseil :  Dans la vue de diagramme, plusieurs diagrammes de table sont entièrement étendus, affichant des colonnes telles que ETLLoadID, LoadDate et UpdateDate. Ces champs spécifiques sont les artefacts de l’entrepôt de données Contoso d'origine, ajoutés pour prendre en charge les opérations d'extraction et de chargement de données. Vous n'en avez pas besoin dans votre modèle. Pour les supprimer, mettez le champ en surbrillance et cliquez avec le bouton droit, puis sélectionnez Supprimer.

Créer une colonne calculée

Dans Power Pivot, vous pouvez utiliser DAX (Data Analysis Expressions) pour ajouter des calculs. Dans cette tâche, vous allez calculer le bénéfice total, puis vous allez ajouter une colonne calculée qui référence les valeurs des données d’autres tables. Plus loin, vous découvrirez comment utiliser les colonnes référencées pour simplifier votre modèle.

  1. Dans la fenêtre Power Pivot, revenez en vue de données.

  2. Attribuez un nom plus convivial à la table Table_ProductCategories accdb. Vous la référencerez dans les étapes suivantes et un nom plus court facilitera la lecture des calculs. Cliquez avec le bouton droit sur le nom de la table, cliquez sur Renommer, tapez ProductCategories, puis appuyez sur Entrée.

  3. Sélectionnez la table FactSales.

  4. Cliquez sur Conception > Colonnes > Ajouter.

  5. Dans la barre de formule au-dessus de la table, tapez la formule suivante. La saisie semi-automatique vous aide à taper les noms complets de colonnes et de tables et répertorie les fonctions disponibles. Vous pouvez également cliquer sur la colonne pour que Power Pivot ajoute son nom à la formule.

    = [SalesAmount] - [TotalCost] - [ReturnAmount]

  6. Lorsque vous avez terminé de générer la formule, appuyez sur Entrée pour accepter la formule.

    Des valeurs remplissent toutes les lignes de la colonne calculée. Si vous faites défiler la table vers le bas, vous remarquez que les lignes peuvent avoir des valeurs différentes pour cette colonne, en fonction des données figurant dans chaque ligne.

  7. Renommez la colonne en cliquant avec le bouton droit sur CalculatedColumn1 et en sélectionnant Renommer la colonne. Tapez Profit, puis appuyez sur Entrée.

  8. Sélectionnez maintenant la table DimProduct.

  9. Cliquez sur Conception > Colonnes > Ajouter.

  10. Dans la barre de formule au-dessus de la table, tapez la formule suivante.

    = RELATED(ProductCategories[ProductCategoryName])

    La fonction RELATED retourne une valeur à partir d’une table associée. Dans ce cas, la table ProductCategories inclut les noms des catégories de produits, ce qui s’avère utile dans la table DimProduct lorsque vous générez une hiérarchie qui inclut des informations de catégorie. Pour plus d’informations sur cette fonction, voir Fonction RELATED (DAX).

  11. Lorsque vous avez terminé de générer la formule, appuyez sur Entrée pour accepter la formule.

    Des valeurs remplissent toutes les lignes de la colonne calculée. Si vous faites défiler la table vers le bas, vous pouvez constater que chaque ligne comporte maintenant un nom de catégorie de produit.

  12. Renommez la colonne en cliquant avec le bouton droit sur CalculatedColumn1 et en sélectionnant Renommer la colonne. Tapez ProductCategory, puis appuyez sur Entrée.

  13. Cliquez sur Conception > Colonnes > Ajouter.

  14. Dans la barre de formule au-dessus de la table, tapez la formule suivante, puis appuyez sur Entrée pour accepter la formule.

    = RELATED(DimProductSubcategory[ProductSubcategoryName])

  15. Renommez la colonne en cliquant avec le bouton droit sur CalculatedColumn1 et en sélectionnant Renommer la colonne. Tapez ProductSubcategory, puis appuyez sur Entrée.

Créer une hiérarchie

La plupart des modèles incluent des données qui sont hiérarchiques de manière intrinsèque. Parmi les exemples classiques, citons les données de calendrier, les données géographiques et les catégories de produits. La création de hiérarchies est utile car vous pouvez faire glisser un élément (la hiérarchie) vers un rapport au lieu de devoir sans cesse assembler et classer ces mêmes champs.

  1. Dans Power Pivot, basculez vers la vue de diagramme. Développez la table DimDate afin de voir plus facilement l’ensemble de ses champs.

  2. Tout en maintenant enfoncée la touche Ctrl, cliquez sur les colonnes CalendarYear, CalendarQuarter et CalendarMonth (vous devez faire défiler la table).

  3. Les trois colonnes étant sélectionnées, cliquez avec le bouton droit sur l'une d’entre elles, puis cliquez sur Créer une hiérarchie. Un nœud de hiérarchie parent, « Hierarchy 1 », est créé en bas du tableau et les colonnes sélectionnées sont copiées sous la hiérarchie en tant que nœuds enfants.

  4. Tapez « Dates » pour nommer la nouvelle hiérarchie.

  5. Ajoutez la colonne FullDateLabel à la hiérarchie. Cliquez avec le bouton droit sur FullDateLabel et sélectionnez Ajouter à la hiérarchie. Choisissez Date. FullDateLabel contient une date complète (jour, mois et année). Vérifiez que FullDateLabel apparaît en dernier dans la hiérarchie. Vous disposez maintenant d’une hiérarchie à plusieurs niveaux qui comprend année, trimestre, mois et des jours de calendrier.

  6. Toujours dans la vue de diagramme, pointez sur la table DimProduct, puis cliquez sur le bouton Créer une hiérarchie dans l’en-tête de table. Un nœud de hiérarchie parent vide apparaît en bas de la table.

  7. Tapez Product Categories pour nommer la nouvelle hiérarchie.

  8. Pour créer des nœuds de hiérarchie enfants, faites glisser les colonnes ProductCategory et ProductSubcategory vers la hiérarchie.

  9. Cliquez avec le bouton droit sur ProductName et sélectionnez Ajouter à la hiérarchie. Choisissez Product Categories.

Maintenant que vous connaissez différentes manières de créer une hiérarchie, nous allons nous en servir dans le tableau croisé dynamique.

  1. Retournez dans Excel.

  2. Dans Feuil1 (feuille contenant le tableau croisé dynamique), supprimez les champs dans la zone Lignes.

  3. Remplacez-les par la nouvelle hiérarchie Product Categories dans DimProduct.

  4. De même, remplacez CalendarYear dans la zone Colonnes par la hiérarchie Dates dans DimDate.

À présent, lorsque vous explorez les données, il est facile de constater les avantages de l’utilisation des hiérarchies. Vous pouvez développer et fermer des zones du tableau croisé dynamique de manière indépendante, ce qui permet de disposer d’un meilleur contrôle sur la façon dont l’espace disponible est utilisé. En outre, lorsque vous ajoutez une hiérarchie unique à Lignes et à Colonnes, vous bénéficiez d’une fonctionnalité d’exploration complète et immédiate, sans avoir à empiler plusieurs champs pour obtenir un résultat similaire.

Masquer des colonnes

Maintenant que vous avez créé une hiérarchie Product Categories et l’avez placée dans DimProduct, vous n’avez plus besoin de DimProductCategory ni de DimProductSubcategory dans la liste de champs de tableau croisé dynamique. Dans cette tâche, vous allez apprendre à masquer des tables et des colonnes étrangères qui prennent de la place dans la liste de champs du tableau croisé dynamique. En masquant les tables et les colonnes, vous améliorez l’expérience de création de rapports sans affecter le modèle qui fournit des relations et des calculs de données.

Votre navigateur ne prend pas en charge la vidéo. Installez Microsoft Silverlight, Adobe Flash Player ou Internet Explorer 9.

Vous pouvez masquer des colonnes individuelles, une plage de colonnes ou toute la table. Les noms de table et de colonne apparaissent en grisé pour signifier qu'elles sont masquées aux clients de création de rapports qui utilisent ce modèle. Afin d'indiquer l'état des colonnes masquées, celles-ci apparaissent en grisé dans le modèle mais demeurent visibles dans l'affichage Données. Vous pouvez ainsi continuer à les utiliser.

  1. Dans Power Pivot, vérifiez que la vue de données est sélectionnée.

  2. Dans les onglets situés en bas, cliquez avec le bouton droit sur DimProductSubcategory et sélectionnez Masquer dans les outils clients.

  3. Répétez ces étapes pour ProductCategories.

  4. Ouvrez DimProduct.

  5. Cliquez avec le bouton droit sur les colonnes suivantes, puis sélectionnez Masquer dans les outils clients :

    • ProductKey

    • ProductLabel

    • ProductSubcategory

  6. Sélectionnez plusieurs colonnes adjacentes en même temps. Démarrez avec ClassID et poursuivez avec ProductSubcategory jusqu’à la fin. Cliquez avec le bouton droit pour les masquer.

  7. Recommencez pour les autres tables, en supprimant les ID, les clés, ou d’autres informations que vous n’utiliserez pas dans ce rapport.

Revenez à la Feuil1 dans Excel avec la liste de champs du tableau croisé dynamique pour voir la différence. Le nombre de tables est réduit et DimProduct inclut uniquement les éléments que vous êtes le plus susceptible d’utiliser lors de l’analyse des ventes.

Créer un rapport Power View

Un rapport de tableau croisé dynamique n'est pas le seul type de rapport qui bénéficie des avantages d'un modèle de données. Grâce au même modèle que vous venez de créer, vous pouvez ajouter une feuille Power View afin d'essayer certaines des dispositions qui y sont proposées.

  1. Dans Excel, cliquez sur Insertion > Power View.

    Remarque :  Si vous utilisez Power View pour la première fois sur cet ordinateur, vous êtes d’abord invité à activer le complément et à installer Silverlight.

  2. Dans les champs Power View, cliquez sur la flèche en regard de la table FactSales, puis cliquez sur SalesAmount.

  3. Développez la table Geography, puis cliquez sur RegionCountryName.

  4. Dans le ruban, cliquez sur Carte.

  5. Un rapport cartographique apparaît. Faites glisser un angle pour le redimensionner. Sur la carte, des cercles bleus de tailles variées illustrent les résultats des ventes pour les différents pays/régions.

Optimisation de la création de rapports Power View

Si vous apportez quelques petites modifications à votre modèle, vous obtiendrez des réponses plus intuitives lors de la création d’un rapport Power View. Dans cette tâche, vous allez ajouter des URL de site web pour plusieurs fabricants, puis vous les classerez comme URL web de sorte qu’elles s’affichent sous la forme d’un lien.

Tout d’abord, ajoutez des URL à votre classeur.

  1. Dans Excel, ouvrez une nouvelle feuille et copiez ces valeurs :

ManufacturerURL

ManufacturerID

http://www.contoso.com

Contoso, LTD

http://www.adventure-works.com

Adventure Works

http://www.fabrikam.com

Fabrikam, Inc.

  1. Mettez les cellules en forme de table, puis appelez cette dernière URL.

  2. Créez une relation entre la table URL et celle qui contient les noms de fabricant DimProduct :

    1. Cliquez sur Données > Relations. La boîte de dialogue Créer une relation s’affiche.

    2. Cliquez sur Créer.

    3. Dans Table, sélectionnez DimProduct.

    4. Dans Colonne, sélectionnez Manufacturer.

    5. Dans Table liée, sélectionnez URL.

    6. Dans Colonne associée (primaire), sélectionnez ManufacturerID.

Pour comparer les résultats avant et après, commencez un nouveau rapport Power View et ajoutez FactSales | SalesAmount, dimProduct | Manufacturer et URL | ManufacturerURL à un rapport. Notez que les URL s’affichent comme du texte statique.

Le rendu d’une URL sous la forme d’un lien hypertexte actif demande un classement. Pour classer une colonne, vous utilisez Power Pivot.

  1. Dans Power Pivot, ouvrez URL.

  2. Sélectionnez ManufacturerURL.

  3. Cliquez sur Avancé > Propriétés de la création de rapports > Catégorie de données : Sans catégorie.

  4. Cliquez sur la flèche vers le bas.

  5. Sélectionnez Web URL.

  6. Dans Excel, cliquez sur Insertion > Power View.

  7. Dans la liste Champs de Power View, sélectionnez FactSales | SalesAmount, dimProduct | Manufacturer et URL | ManufacturerURL. Cette fois, les URL s’affichent sous la forme de lien hypertexte.

D’autres optimisations Power View incluent la définition d’un ensemble de champs par défaut pour chaque table et la définition de propriétés qui déterminent si les lignes de données répétitives sont agrégées ou répertoriées indépendamment. Pour plus d’informations, voir Configurer l’ensemble de champs par défaut pour les rapports Power View et Configurer les propriétés de comportement de table pour les rapports Power View.

Créer des champs calculés

Dans la seconde tâche, Explorer des données à l'aide d'un tableau croisé dynamique, vous avez cliqué sur le champ SalesAmount dans la liste Champs de tableau croisé dynamique. Comme SalesAmount est une colonne numérique, elle a été automatiquement placée dans la zone Valeurs du tableau croisé dynamique. La somme de la colonne SalesAmount pouvait alors être calculée quel que soit le filtre appliqué. Dans ce cas, aucun filtre en premier lieu, puis CalendarYear, ProductSubcategoryName et BrandName.

Vous avez créé un champ calculé implicite, ce qui a simplifié l’analyse des montants des ventes de la table FactSales par rapport à d’autres champs comme la catégorie de produit, la région et les dates. Des champs calculés implicites sont créés par Excel quand vous faites glisser un champ vers la zone Valeurs ou quand vous cliquez sur un champ numérique, comme vous l’avez fait pour SalesAmount. Les champs calculés implicites sont des formules qui utilisent des fonctions d’agrégation standard comme SOMME, NB et MOYENNE, créées automatiquement.

Il existe d’autres types de champs calculés. Vous pouvez créer des champs calculés explicites dans Power Pivot. Contrairement à un champ calculé implicite, qui ne peut être utilisé que dans le tableau croisé dynamique dans lequel il a été créé, un champ calculé explicite peut être utilisé dans n’importe quel tableau croisé dynamique du classeur ou dans n’importe quel rapport utilisant le modèle de données comme source de données. Avec les champs calculés explicites créés dans Power Pivot, vous pouvez utiliser la somme automatique pour créer automatiquement des champs calculés utilisant des agrégations standard ou vous pouvez créer les vôtres à l’aide d’une formule créée avec DAX (Data Analysis Expressions).

Comme vous pouvez l’imaginer, créer des champs calculés peut vous aider à analyser vos données de façon puissante. Découvrons à présent comment les créer.

Créer des champs calculés dans Power Pivot est très simple quand vous utilisez Somme automatique.

  1. Dans la table FactSales, cliquez sur la colonne Profit.

  2. Cliquez sur Calculs > Somme automatique. Notez qu’un nouveau champ calculé appelé Somme de Profit a été automatiquement créé dans la cellule de la zone de calcul directement sous la colonne Profit.

  3. Dans Excel, dans Feuil1, dans la liste des champs, cliquez sur Somme de Profit, click FactSales.

Voilà ! Créer un champ calculé à l’aide d’une agrégation standard dans Power Pivot est aussi simple que ça. Comme vous pouvez le voir, en quelques minutes, vous avez créé un champ calculé SOMME de Profit et vous l’avez ajouté au tableau croisé dynamique, ce qui simplifie l’analyse des bénéfices selon les filtres appliqués. Dans le cas présent, vous pouvez voir Somme de Profit filtré par les hiérarchies Product Category et Dates.

Que se passe-t-il si vous devez effectuer une analyse plus détaillée (nombre de ventes par canal, produit ou catégorie spécifique par exemple) ? Dans ce cas, vous devez créer un autre champ calculé qui compte le nombre de lignes, une pour chaque vente dans la table FactSales, selon les filtres appliqués.

  1. Dans la table FactSales, cliquez sur la colonne SalesKey.

  2. Dans Calculs, cliquez sur la flèche vers le bas de Somme automatique > Nombre.

  3. Renommez le nouveau champ calculé en cliquant sur Nombre de SalesKey dans la zone de calcul, puis dans la barre de formule, changez Nombre de SalesKey en Count, puis appuyez sur Entrée. Contrairement aux colonnes calculées, les noms des champs calculés sont inclus dans la formule DAX.

  4. Dans Excel, dans Feuil1, dans la liste des champs, cliquez sur Count dans FactSales.

Notez qu’une nouvelle colonne Count est ajoutée au tableau croisé dynamique. Elle montre le nombre de ventes selon les filtres appliqués. Tout comme le champ calculé Somme de Profit, vous pouvez voir Count filtré selon les hiérarchies Product Category et Dates.

Créons un autre champ calculé. Cette fois, vous allez créer un champ calculé qui calcule le pourcentage des ventes totales pour un contexte ou un filtre spécifique. Toutefois, contrairement aux champs calculés précédents que vous avez créés à l’aide de Somme automatique, vous allez cette fois entrer une formule manuellement.

  1. Dans la table FactSales, dans la zone de calcul, cliquez sur une cellule vide. Conseil : la cellule située en haut à gauche est l’emplacement idéal pour commencer vos champs calculés. Elle permet de les retrouver facilement. Vous pouvez déplacer n’importe quel champ calculé dans la zone de calcul.

  2. Dans la barre de formule, tapez et utilisez IntelliSense pour créer la formule suivante : Pourcentage de All products:=[Count]/CALCULATE([Count], ALL(DimProduct))

  3. Appuyez sur Entrée pour accepter la formule.

  4. Dans Excel, dans Feuil1, dans la liste des champs, cliquez sur Pourcentage de All products dans FactSales.

  5. Dans le tableau croisé dynamique, sélectionnez les colonnes Pourcentage de All Products.

  6. Sous l’onglet Accueil, cliquez sur Nombre > Pourcentage. Utilisez deux décimales pour mettre en forme chaque nouvelle colonne.

Ce nouveau champ calculé calcule le pourcentage des ventes totales pour un contexte de filtre donné. Dans le cas présent, le contexte de filtre est toujours les hiérarchies Product Category et Dates. Vous pouvez voir par exemple que les ventes d’ordinateurs ont augmenté au fil des ans.

Créer des formules pour les colonnes calculées et les champs calculés est relativement simple si vous maîtrisez la création de formules Excel. Que vous connaissiez bien ou non les formules Excel, les leçons de Démarrage rapide : découvrir les fondamentaux de DAX en 30 minutes vous permettent de découvrir les notions fondamentales des formules DAX.

Enregistrer votre travail

Enregistrez le classeur pour l’utiliser avec d’autres didacticiels ou pour une exploration plus approfondie.

Étapes suivantes

Bien que vous puissiez facilement importer des données depuis Excel, il est souvent plus rapide et plus efficace de les importer à l’aide du complément Power Pivot. Vous pouvez filtrer les données que vous importez, en excluant les colonnes dont vous n’avez pas besoin. Vous pouvez également choisir d’utiliser un générateur de requêtes ou une commande de requête pour récupérer les données. Dans la prochaine étape, vous allez en savoir plus sur ces autres approches possibles : Obtenir des données à partir d’un flux dans Power Pivot et Importer des données à partir d’Analysis Services ou de Power Pivot.

La création de rapports Power View est conçue pour utiliser des modèles de données similaires à celui que vous venez de générer. Pour en savoir plus sur les visualisations optimales des données qu’apporte Power View dans Excel, consultez : Démarrer Power View dans Excel 2013 et Power View : explorer, visualiser et présenter vos données.

Essayez d’améliorer votre modèle de données pour créer des rapports Power View encore plus efficaces en suivant ce Didacticiel : optimiser votre modèle de données pour la création de rapports Power View

Développez vos compétences
Découvrez des formations
Accédez aux nouvelles fonctionnalités en avant-première
Rejoignez le programme Office Insider

Ces informations vous ont-elles été utiles ?

Nous vous remercions pour vos commentaires.

Merci pour vos commentaires. Il serait vraisemblablement utile pour vous de contacter l’un de nos agents du support Office.

×