Tableaux croisés dynamiques et graphiques croisés dynamiques

Lorsque vos ensembles de données sont volumineux, vous pouvez utiliser Excel Power Pivot qui peut gérer des centaines de millions de lignes de données. Les données peuvent se trouver dans des sources de données externes et Excel Power Pivot crée un modèle de données qui fonctionne sur un mode d'optimisation de la mémoire. Vous pouvez effectuer les calculs, analyser les données et arriver à un rapport pour tirer des conclusions et des décisions. Le rapport peut être sous la forme d'un tableau croisé dynamique ou d'un graphique croisé dynamique ou d'une combinaison des deux.

Vous pouvez utiliser Power Pivot en tant que solution de reporting et d'analyse ad hoc. Ainsi, il serait possible pour une personne ayant une expérience pratique avec Excel d'effectuer l'analyse de données et la prise de décision haut de gamme en quelques minutes et sont un atout précieux à inclure dans les tableaux de bord.

Utilisations de Power Pivot

Vous pouvez utiliser Power Pivot pour ce qui suit -

• Pour effectuer une analyse de données puissante et créer des modèles de données sophistiqués.
• Pour combiner rapidement de gros volumes de données provenant de plusieurs sources différentes.
• Pour effectuer une analyse des informations et partager les informations de manière interactive.
• Pour créer des indicateurs clés de performance (KPI).
• Pour créer des tableaux croisés dynamiques dynamiques.
• Pour créer des graphiques croisés dynamiques.

Différences entre le tableau croisé dynamique et le tableau croisé dynamique Power

Power PivotTable ressemble à PivotTable dans sa disposition, avec les différences suivantes -

• Le tableau croisé dynamique est basé sur des tableaux Excel, tandis que le tableau croisé dynamique Power est basé sur des tableaux de données qui font partie du modèle de données.

• Le tableau croisé dynamique est basé sur une seule table Excel ou plage de données, tandis que le tableau croisé dynamique Power peut être basé sur plusieurs tables de données, à condition qu'elles soient ajoutées au modèle de données.

• Le tableau croisé dynamique est créé à partir de la fenêtre Excel, tandis que le tableau croisé dynamique Power est créé à partir de la fenêtre PowerPivot.

Création d'un tableau croisé dynamique dynamique

Supposons que vous ayez deux tables de données - Vendeur et Ventes dans le modèle de données. Pour créer un Power PivotTable à partir de ces deux tables de données, procédez comme suit:

• Cliquez sur l'onglet Accueil du ruban dans la fenêtre PowerPivot.

• Cliquez sur Tableau croisé dynamique sur le ruban.

• Cliquez sur Tableau croisé dynamique dans la liste déroulante.

La boîte de dialogue Créer un tableau croisé dynamique s'affiche. Cliquez sur Nouvelle feuille de travail.

Cliquez sur le bouton OK. Une nouvelle feuille de calcul est créée dans la fenêtre Excel et un tableau croisé dynamique vide apparaît.

Comme vous pouvez le constater, la disposition du Power PivotTable est similaire à celle du PivotTable.

La liste des champs de tableau croisé dynamique apparaît sur le côté droit de la feuille de calcul. Ici, vous trouverez quelques différences par rapport au tableau croisé dynamique. La liste Champs du tableau croisé dynamique Power comporte deux onglets - ACTIF et TOUS, qui apparaissent sous le titre et au-dessus de la liste des champs. L'onglet TOUT est mis en surbrillance. L'onglet TOUT affiche toutes les tables de données dans le modèle de données et l'onglet ACTIVE affiche toutes les tables de données qui sont choisies pour le tableau croisé dynamique actuel.

• Cliquez sur les noms de table dans la liste Champs de tableau croisé dynamique sous TOUT.

Les champs correspondants avec des cases à cocher apparaîtront.

• Chaque nom de table aura le symbole

  
  sur le côté gauche.

• Si vous placez le curseur sur ce symbole, la source de données et le nom de la table modèle de cette table de données seront affichés.

• Faites glisser le vendeur de la table des vendeurs vers la zone ROWS.
• Cliquez sur l'onglet ACTIVE.

Le champ Vendeur apparaît dans le tableau croisé dynamique Power et le tableau Vendeur apparaît sous l'onglet ACTIF.

• Cliquez sur l'onglet TOUS.
• Cliquez sur Mois et montant de la commande dans le tableau Ventes.
• Cliquez sur l'onglet ACTIVE.

Les deux tableaux - Ventes et Vendeur apparaissent sous l'onglet ACTIF.

• Faites glisser le mois vers la zone COLONNES.
• Faites glisser la région vers la zone FILTRES.

• Cliquez sur la flèche à côté de TOUT dans la zone de filtre Région.
• Cliquez sur Sélectionner plusieurs éléments.
• Cliquez sur Nord et Sud.

• Cliquez sur le bouton OK. Triez les étiquettes de colonne dans l'ordre croissant.

Power PivotTable peut être modifié dynamiquement pour explorer et rapporter des données.

Création d'un graphique croisé dynamique

Un graphique croisé dynamique est un graphique croisé dynamique basé sur un modèle de données et créé à partir de la fenêtre Power Pivot. Bien qu'il présente certaines fonctionnalités similaires à Excel PivotChart, il existe d'autres fonctionnalités qui le rendent plus puissant.

Supposons que vous souhaitiez créer un Power PivotChart basé sur le modèle de données suivant.

• Cliquez sur l'onglet Accueil sur le ruban dans la fenêtre Power Pivot.
• Cliquez sur Tableau croisé dynamique.
• Cliquez sur Graphique croisé dynamique dans la liste déroulante.

La boîte de dialogue Créer un graphique croisé dynamique s'affiche. Cliquez sur Nouvelle feuille de calcul.

• Cliquez sur le bouton OK. Un graphique croisé dynamique vide est créé sur une nouvelle feuille de calcul dans la fenêtre Excel. Dans ce chapitre, lorsque nous parlons de graphique croisé dynamique, nous faisons référence à Power PivotChart.

Comme vous pouvez le constater, toutes les tables du modèle de données sont affichées dans la liste Champs de graphique croisé dynamique.

• Cliquez sur le tableau Vendeur dans la liste Champs de graphique croisé dynamique.
• Faites glisser les champs - Vendeur et Région vers la zone AXIS.

Deux boutons de champ pour les deux champs sélectionnés apparaissent sur le graphique croisé dynamique. Ce sont les boutons de champ Axis. L'utilisation des boutons de champ consiste à filtrer les données affichées sur le graphique croisé dynamique.

• Faites glisser TotalSalesAmount de chacune des 4 tables - East_Sales, North_Sales, South_Sales et West_Sales vers la zone ∑ VALUES.

Comme vous pouvez le constater, les éléments suivants apparaissent sur la feuille de calcul -

• Dans le graphique croisé dynamique, le graphique à colonnes est affiché par défaut.
• Dans la zone LÉGENDE, ∑ VALUES est ajouté.
• Les valeurs apparaissent dans la légende du graphique croisé dynamique, avec les valeurs de titre.
• Les boutons de champ de valeur apparaissent sur le graphique croisé dynamique.

Vous pouvez supprimer la légende et les boutons de champ de valeur pour une apparence plus nette du graphique croisé dynamique.

• Cliquez sur le

  
  bouton dans le coin supérieur droit du graphique croisé dynamique.

• Désélectionnez Légende dans les éléments du graphique.

• Cliquez avec le bouton droit sur les boutons du champ de valeur.

• Cliquez sur Masquer les boutons de champ de valeur sur le graphique dans la liste déroulante.

Les boutons de champ de valeur sur le graphique seront masqués.

Notez que l'affichage des boutons de champ et / ou de la légende dépend du contexte du graphique croisé dynamique. Vous devez décider de ce qui doit être affiché.

Comme dans le cas de Power PivotTable, la liste des champs de Power PivotChart contient également deux onglets - ACTIVE et TOUS. De plus, il y a 4 domaines -

• AXE (Catégories)
• LÉGENDE (Série)
• ∑ VALEURS
• FILTERS

Comme vous pouvez le constater, la légende est remplie avec des valeurs ∑. En outre, les boutons de champ sont ajoutés au graphique croisé dynamique pour faciliter le filtrage des données affichées. Vous pouvez cliquer sur la flèche d'un bouton de champ et sélectionner / désélectionner les valeurs à afficher dans le Power PivotChart.

Combinaisons de tableaux et de graphiques

Power Pivot vous propose différentes combinaisons de Power PivotTable et Power PivotChart pour l'exploration, la visualisation et la création de rapports de données.

Considérez le modèle de données suivant dans Power Pivot que nous utiliserons pour les illustrations -

Vous pouvez avoir les combinaisons de tableaux et de graphiques suivantes dans Power Pivot.

• Graphique et tableau (horizontal) - vous pouvez créer un Power PivotChart et un Power PivotTable, l'un à côté de l'autre horizontalement dans la même feuille de calcul.

Graphique et tableau (vertical) - vous pouvez créer un Power PivotChart et un Power PivotTable, l'un sous l'autre verticalement dans la même feuille de calcul.

Ces combinaisons et bien d'autres sont disponibles dans la liste déroulante qui apparaît lorsque vous cliquez sur Tableau croisé dynamique sur le ruban dans la fenêtre Power Pivot.

Hiérarchies dans Power Pivot

Vous pouvez utiliser les hiérarchies dans Power Pivot pour effectuer des calculs et pour explorer vers le haut et vers le bas les données imbriquées.

Considérez le modèle de données suivant pour les illustrations de ce chapitre.

Vous pouvez créer des hiérarchies dans la vue de diagramme du modèle de données, mais basées sur une seule table de données.

• Cliquez sur les colonnes - Sport, DisciplineID et Event dans le tableau de données Medal dans cet ordre. N'oubliez pas que l'ordre est important pour créer une hiérarchie significative.

• Faites un clic droit sur la sélection.

• Cliquez sur Créer une hiérarchie dans la liste déroulante.

Le champ de hiérarchie avec les trois champs sélectionnés lors de la création des niveaux enfants.

• Cliquez avec le bouton droit sur le nom de la hiérarchie.
• Cliquez sur Renommer dans la liste déroulante.
• Tapez un nom significatif, par exemple EventHierarchy.

Vous pouvez créer un tableau croisé dynamique puissant à l'aide de la hiérarchie que vous avez créée dans le modèle de données.

• Créez un tableau croisé dynamique puissant.

Comme vous pouvez le constater, dans la liste Champs de tableau croisé dynamique, EventHierarchy apparaît sous la forme d'un champ dans la table des médailles. Les autres champs de la table des médailles sont réduits et affichés en tant que champs supplémentaires.

• Cliquez sur la flèche
  
  devant EventHierarchy.
• Cliquez sur la flèche
  
  devant Plus de champs.

Les champs sous EventHierarchy seront affichés. Tous les champs du tableau Médailles seront affichés sous Plus de champs.

Ajoutez des champs au Power PivotTable comme suit -

• Faites glisser EventHierarchy vers la zone ROWS.
• Faites glisser Medal vers la zone ∑ VALEURS.

Comme vous pouvez le constater, les valeurs du champ Sport apparaissent dans le Power PivotTable avec un signe + devant elles. Le nombre de médailles pour chaque sport est affiché.

• Cliquez sur le signe + avant Aquatics. Les valeurs du champ DisciplineID sous Aquatiques seront affichées.

• Cliquez sur l'enfant D22 qui apparaît. Les valeurs du champ Événement sous D22 seront affichées.

Comme vous pouvez le constater, le nombre de médailles est donné pour les épreuves, qui sont résumées au niveau des parents - DisciplineID, qui se résument davantage au niveau des parents - Sport.

Calculs à l'aide de la hiérarchie dans les tableaux croisés dynamiques Power

Vous pouvez créer des calculs à l'aide d'une hiérarchie dans un tableau croisé dynamique Power. Par exemple, dans la hiérarchie des événements, vous pouvez afficher le no. de médailles au niveau des enfants en pourcentage du non. de médailles au niveau de ses parents comme suit -

• Cliquez avec le bouton droit de la souris sur la valeur du nombre de médailles d'un événement.
• Cliquez sur Paramètres du champ de valeur dans la liste déroulante.

La boîte de dialogue Paramètres du champ de valeur s'affiche.

• Cliquez sur l'onglet Afficher les valeurs sous.
• Cliquez sur la case Afficher les valeurs sous.
• Cliquez sur% du total de la ligne parent.

• Cliquez sur le bouton OK.

Comme vous pouvez le constater, les niveaux enfants sont affichés sous forme de pourcentage des totaux parents. Vous pouvez le vérifier en additionnant les valeurs en pourcentage du niveau enfant d'un parent. La somme serait de 100%.

Exploration et exploration d'une hiérarchie

Vous pouvez rapidement explorer les niveaux d'une hiérarchie vers le haut et vers le bas dans un tableau croisé dynamique puissant à l'aide de l'outil d'exploration rapide.

• Cliquez sur une valeur du champ Événement dans le tableau croisé dynamique Power.

• Cliquez sur l'outil Exploration rapide -

  
  qui apparaît dans le coin inférieur droit de la cellule contenant la valeur sélectionnée.

La boîte EXPLORER avec l'option Explorer vers le haut apparaît. En effet, à partir de l'événement, vous ne pouvez explorer que vers le haut car il n'y a pas de niveaux enfants en dessous.

• Cliquez sur Drill Up. Les données du tableau croisé dynamique puissant sont analysées jusqu'au niveau de la discipline.

• Cliquez sur l'outil Exploration rapide -

  
  qui apparaît dans le coin inférieur droit de la cellule contenant une valeur.

La boîte EXPLORER apparaît avec les options d'exploration vers le haut et vers le bas. En effet, à partir de la discipline, vous pouvez accéder au sport ou descendre aux niveaux d'événement.

De cette façon, vous pouvez rapidement monter et descendre la hiérarchie dans un tableau croisé dynamique.

Utilisation d'un segment commun

Vous pouvez insérer des segments et les partager entre les Power PivotTables et Power PivotCharts.

• Créez un Power PivotChart et un Power PivotTable côte à côte horizontalement.

• Cliquez sur Power PivotChart.

• Faites glisser Discipline du tableau Disciplines vers la zone AXIS.

• Faites glisser Médaille du tableau Médailles vers la zone ∑ VALEURS.

• Cliquez sur Power PivotTable.

• Faites glisser Discipline du tableau Disciplines vers la zone ROWS.

• Faites glisser Médaille du tableau Médailles vers la zone ∑ VALEURS.

• Cliquez sur l'onglet ANALYSER dans OUTILS PIVOTABLES sur le ruban.
• Cliquez sur Insérer un segment.

La boîte de dialogue Insérer des segments s'affiche.

• Cliquez sur NOC_CountryRegion et Sport dans le tableau des médailles.
• Cliquez sur OK.

Deux slicers - NOC_CountryRegion et Sport apparaissent.

• Disposez-les et redimensionnez-les pour les aligner correctement à côté du tableau croisé dynamique dynamique comme illustré ci-dessous.

• Cliquez sur USA dans le NOC_CountryRegion Slicer.
• Cliquez sur Aquatics dans le Sport Slicer.

Le Power PivotTable est filtré sur les valeurs sélectionnées.

Comme vous pouvez le constater, le Power PivotChart n'est pas filtré. Pour filtrer Power PivotChart avec les mêmes filtres, vous pouvez utiliser les mêmes segments que vous avez utilisés pour le Power PivotTable.

• Cliquez sur NOC_CountryRegion Slicer.
• Cliquez sur l'onglet OPTIONS dans SLICER TOOLS sur le ruban.
• Cliquez sur Report Connections dans le groupe Slicer.

La boîte de dialogue Connexions de rapport s'affiche pour le segment NOC_CountryRegion.

Comme vous pouvez le constater, tous les Power PivotTables et Power PivotCharts du classeur sont répertoriés dans la boîte de dialogue.

• Cliquez sur le Power PivotChart qui se trouve dans la même feuille de calcul que le Power PivotTable sélectionné.

• Cliquez sur le bouton OK.

• Répétez pour Sport Slicer.

Le Power PivotChart est également filtré sur les valeurs sélectionnées dans les deux segments.

Ensuite, vous pouvez ajouter plus de détails au Power PivotChart et au Power PivotTable.

• Cliquez sur le Power PivotChart.
• Faites glisser le sexe vers la zone LÉGENDE.
• Cliquez avec le bouton droit sur le Power PivotChart.
• Cliquez sur Modifier le type de graphique.
• Sélectionnez Colonne empilée dans la boîte de dialogue Modifier le type de graphique.
• Cliquez sur le Power PivotTable.
• Faites glisser l'événement vers la zone ROWS.
• Cliquez sur l'onglet CONCEPTION dans OUTILS PIVOTABLES sur le ruban.
• Cliquez sur Présentation du rapport.
• Cliquez sur Formulaire de plan dans la liste déroulante.

Rapports esthétiques pour les tableaux de bord

Vous pouvez créer des rapports esthétiques avec Power PivotTables et Power PivotCharts et les inclure dans des tableaux de bord. Comme vous l'avez vu dans la section précédente, vous pouvez utiliser les options de mise en page du rapport pour choisir l'aspect et la convivialité des rapports. Par exemple, avec l'option - Afficher sous forme de contour et avec les lignes en bandes sélectionnées, vous obtiendrez le rapport comme indiqué ci-dessous.

Comme vous pouvez le constater, les noms de champ apparaissent à la place des libellés de ligne et des libellés de colonne et le rapport semble explicite.

Vous pouvez sélectionner les objets que vous souhaitez afficher dans le rapport final dans le volet Sélection. Par exemple, si vous ne souhaitez pas afficher les segments que vous avez créés et utilisés, vous pouvez simplement les masquer en les désélectionnant dans le volet Sélection.