« Tableur EXCEL/PowerPivot » : différence entre les versions

Navigation interactive dans l’historique

← Modification précédente Modification suivante →

Contenu supprimé Contenu ajouté

Intégrés

Version du 14 juin 2014 à 21:01

’

**PowerPivot**
Leçon : Tableur EXCEL

Chapitre n^o 10
Chap. préc. :	Décisionnel
Chap. suiv. :	Conclusion

En raison de limitations techniques, la typographie souhaitable du titre, « Tableur EXCEL : PowerPivot
Tableur EXCEL/PowerPivot », n'a pu être restituée correctement ci-dessus.

Cette leçon à pour but de présenter les fonctions de PowerPivot.

Présentation

Le nom de cet outil est bien évocateur: "Power" pour "Pouvoir" et "Pivot" pour "Tableaux croisés dynamiques".

PowerPivot est un add-in d’EXCEL c'est-à-dire un module d’extension, il se trouve dans les outils de Business Intelligence (BI) simple, aussi nommée « Self-BI » et est fondé sur le principe des cubes de données.

PowerPivot existait dans le système SQL Server-Sharepoint utilisé par de nombreuses entreprises, il a été dans un premier temps proposé en tant que module d'extension gratuit pour la version EXCEL 2010. La version 2013 d'EXCEL comporte maintenant PowerPivot en standard.

PowerPivot est utilisé pour insérer et intégrer de grandes quantités de données dans des classeurs EXCEL. Les données ajoutées au classeur sont stockées en interne, sous la forme d'une base de données PowerPivot incorporée dans le fichier.xlsx qui peut être utilisée dans les rapports EXCEL ou Power View.

Dans EXCEL, le classeur offre toutes les fonctionnalités de visualisation des données et d'interaction avec celles-ci. Vous pouvez générer des classeurs sophistiqués basés sur des données analytiques performantes que vous générez facilement grâce à la création de tableau croisé dynamique.

PowerPivot est un logiciel intuitif et évolutif qui donne la possibilité aux utilisateurs de créer des rapports ou solution de REPORTING. Il permet également d'étendre les possibilités d’analyse de données d’EXCEL mais aussi d’obtenir rapidement des informations agrégées à partir d'un grand nombre de données.

Intérêt de l'outil

Les avantages de ce module d'extension sont nombreux, PowerPivot permet de:

Multiplier la puissance des TCD dans Excel et permet des calculs impressionnants, qui peuvent traiter des millions de lignes comme s’il s’agissait de quelques milliers
Travailler directement avec les données dans l'espace de travail EXCEL, l'onglet menu PowerPivot est intégré au ruban EXCEL.
Ajouter et consolider des données émanant de plusieurs sources tels qu' EXCEL, ACCESS, SQL, HTML, ... Il suffit simplement d'associer les différentes bases de données entre elles avec un champ commun, par exemple l'identifiant d'un client
Intégrer des données qui évoluent sans cesse aussi facilement qu’un flux RSS, la mise à jour se fait instantanément sans alourdir les fichiers Excel
Utiliser Sharepoint et permettre ainsi de publier, partager et collaborer facilement
Traiter les données avec un moteur d'analyse local (en mémoire xVelocity Analysis Services VertiPaq); ce dernier compacte, charge les données et les met à disposition d'objets de visualisation, comme des tableaux croisés dynamiques, dans une feuille de travail.
Eviter des tâches d'administration ou de configuration, le moteur s'exécute dans le processus EXCEL(en tant que composant interne du complément PowerPivot à EXCEL).

En Conclusion, PowerPivot permet à EXCEL de devenir un outil de décisionnel (Business Intelligence) très puissant et en même temps simple d’utilisation.

Ajout de données

A partir de cet add-in, il est possible d'ajouter des données de différentes manières :

à partir d’une liste de données (sous forme de plage ou de tableau) lors de la demande d’insertion d'un TCD => cocher "ajouter ces données au modèle de données". Dans ce cas, le TCD utilisera automatiquement le modèle de données. Il sera possible dans le volet de construction de ce TCD, de sélectionner "Actif" ou "Tous" pour afficher les champs du tableau ou les champs de l’ensemble des tableaux en relation.

à partir d’un tableau, il faudra pour cela ajouter automatiquement le tableau au modèle de données en établissant une relation avec un autre tableau : nouvelle fonctionnalité accessible depuis l’onglet "Données". Dans ce cas il faut préciser, lors de la création d’un tableau croisé, ce que l’on souhaite faire. Si on ne précise rien, c’est le tableau qui est utilisé pour la création du TCD. De ce fait, on peut ensuite préciser si l’on souhaite utiliser le modèle en sélectionnant "Plus de tables" dans la liste des champs du volet de construction du TCD. Sinon, on peut, comme dans le cas « d’ajout à partir d’une liste de données », cocher "Ajouter ces données au modèles de données". Ceci parait étonnant du fait que les données soient déjà dans le modèle.

à partir de données externes, pour la création du TCD, on devra cocher "Utiliser une source de données externes", puis on sélectionnera la connexion qui s'est créée dans le classeur, si on souhaite utiliser des données externes pour une relation, on peut :

- Établir une connexion à ces données en cochant dans la fenêtre d'importation
- "Tableau", ou si on ne souhaite pas les importer dans un onglet, "Ne créer que la connexion"
- "Ajouter ces données au modèle de données"

Intérêt des relations PowerPivot

Sur le même principe qu'une base de données, les relations permettent de tirer parti des liens existants entre les données en évitant la répétition. La relation est toujours une relation de 1 à N, contrairement à EXCEL qui n'impose pas de relation naturelle.

Les relations PowerPivot évitent cependant des formules comme RECHERCHEV pour croiser les données de plusieurs tables si l'objectif de la formule est l'analyse par TCD. Par exemple avec :

un tableau de factures où seul l'identifiant client apparait.
un tableau de clients, où figurent l'adresse du client et l'identifiant du commercial chargé du client

La relation entre les deux tableaux permet une analyse du chiffre d'affaire par département ou par ville ou par commercial.

PowerPivot version ProPlus

Dans la version pro plus, on bénéficie de tous les avantages de l’add-in tels que :

un onglet PowerPivot
une interface spécifique permettant de visualiser et optimiser le modèle de données
l'autorisation des calculs en langage DAX permettent de tirer toute la puissance possible des relations

Pour ajouter des données au modèle, en plus des trois possibilités citées pour toutes les versions, PowerPivot ProPlus permet :

L’utilisation de la commande Ajouter au modèle de données de l'onglet PowerPivot
Obtenir des données externes dans l'interface PowerPivot qui permet de :
- n'importer que certains champs d'une table ou vue.
- n'importer que certains enregistrements d'une table ou vue.
créer une requête pour définir les données à importer.

Dans les 3 cas on obtient une requête, implicite ou explicite pouvant être modifiée à tout moment.

Langage DAX

Le langage DAX permet de réaliser des calculs pouvant être réalisés soit dans l’interface spécifique, soit depuis l’onglet PowerPivot. Cela permet d’obtenir des résultats non disponibles dans les tableaux croisés dynamiques classiques.

Les formules DAX sont utilisées dans des tables PowerPivot ou dans des tableaux croisés dynamiques Excel. DAX représente un ensemble de fonctions, d'opérateurs et de constantes qui peuvent être utilisés dans une formule ou une expression, afin de calculer une ou plusieurs valeurs. DAX vous aide à créer de nouvelles informations à partir de données figurant déjà dans votre modèle.

Les formules DAX sont très semblables aux formules Excel. Pour en créer une formule DAX, tapez sur le signe égal, suivi d'un nom de fonction ou d'une expression, et de toutes les valeurs requises.

Pour mieux comprendre prenons un exemple simple :

A ==> l'opérateur de signe égal (=) indique le début de la formule
B ==> la colonne [MontantVentes], les crochets indiquent une référence de colonne dans une formule, contrairement aux formules Excel qui font référence à une cellule, une formule DAX fait toujours référence à une colonne
C ==> Opérateur mathématique de soustraction (-).
D ==> la colonne référencée [CoutTotl]

Tableur EXCEL

Décisionnel

Conclusion

@@ Ligne 10 : / Ligne 10 : @@
 == Présentation ==
 Le nom de cet outil est bien évocateur: "Power" pour "Pouvoir" et "Pivot" pour "Tableaux croisés dynamiques".
-PowerPivot est un add-in d’Excel c'est-à-dire un module d’extension, il se trouve dans les outils de Business Intelligence (BI) simple, aussi nommée  « Self-BI » et est fondé sur le principe des cubes de données.
+PowerPivot est un add-in d’EXCEL c'est-à-dire un module d’extension, il se trouve dans les outils de Business Intelligence (BI) simple, aussi nommée  « Self-BI » et est fondé sur le principe des cubes de données.
-PowerPivot existait dans le système SQL Server-Sharepoint utilisé par de nombreuses entreprises, il a été dans un premier temps proposé en tant que module d'extension gratuit pour la version 2010. La version 2013 d'excel comporte maintenant PowerPivot.
+PowerPivot existait dans le système SQL Server-Sharepoint utilisé par de nombreuses entreprises, il a été dans un premier temps proposé en tant que module d'extension gratuit pour la version EXCEL 2010. La version 2013 d'EXCEL comporte maintenant PowerPivot en standard.
-PowerPivot est utilisée pour insérer et intégrer de grandes quantités de données dans des classeurs Excel, PowerPivot est donc un complément d'Excel 2010. Les données ajoutées au classeur sont stockées en interne, sous la forme d'une base de données PowerPivot incorporée dans le fichier.xlsx qui peut être utilisée dans les rapports Excel ou Power View.
+PowerPivot est utilisé pour insérer et intégrer de grandes quantités de données dans des classeurs EXCEL. Les données ajoutées au classeur sont stockées en interne, sous la forme d'une base de données PowerPivot incorporée dans le fichier.xlsx qui peut être utilisée dans les rapports EXCEL ou Power View.
-Dans Excel, le classeur offre toutes les fonctionnalités de visualisation des données et d'interaction avec celles-ci. Vous pouvez générer des classeurs sophistiqués basés sur des données analytiques performantes que vous générez facilement dans Excel grâce à vos compétences en matière de création de tableau croisé dynamique.
+Dans EXCEL, le classeur offre toutes les fonctionnalités de visualisation des données et d'interaction avec celles-ci. Vous pouvez générer des classeurs sophistiqués basés sur des données analytiques performantes que vous générez facilement grâce à la création de tableau croisé dynamique.
-PowerPivot est un logiciel intuitif et évolutif qui donne la possibilité aux utilisateurs de créer des rapports ou solution de reporting. Il permet également d'étendre les possibilités d’analyse de données d’Excel mais aussi d’obtenir rapidement des informations utiles à partir d'un grand nombre de données.
+PowerPivot est un logiciel intuitif et évolutif qui donne la possibilité aux utilisateurs de créer des rapports ou solution de REPORTING. Il permet également d'étendre les possibilités d’analyse de données d’EXCEL mais aussi d’obtenir rapidement des informations agrégées à partir d'un grand nombre de données.
 == Intérêt de l'outil ==
+Les avantages de ce module d'extension sont nombreux, PowerPivot permet de:
+* Multiplier la puissance des TCD dans Excel et permet des calculs impressionnants, qui peuvent traiter des millions de lignes comme s’il s’agissait de quelques milliers
+* Travailler directement avec les données dans l'espace de travail EXCEL, l'onglet menu PowerPivot est intégré au ruban EXCEL.
+* Ajouter et consolider des données émanant de plusieurs sources tels qu' EXCEL, ACCESS, SQL, HTML, ... Il suffit simplement d'associer les différentes bases de données entre elles avec un champ commun, par exemple l'identifiant d'un client
+* Intégrer des données qui évoluent sans cesse aussi facilement qu’un flux RSS, la mise à jour se fait instantanément sans alourdir les fichiers Excel
+* Utiliser Sharepoint et permettre ainsi de publier, partager et collaborer facilement
+* Traiter les données avec un moteur d'analyse local (en mémoire xVelocity Analysis Services VertiPaq); ce dernier compacte, charge les données et les met à disposition d'objets de visualisation, comme des tableaux croisés dynamiques, dans une feuille de travail.
+* Eviter des tâches d'administration ou de configuration, le moteur s'exécute dans le processus EXCEL(en tant que composant interne du complément PowerPivot à EXCEL).
-Les avantages de ce module d'extension sont nombreux. PowerPivot permet de:
-*Multiplier la puissance des TCD dans Excel et permet des calculs impressionnants. On peut traiter des millions de lignes comme s’il s’agissait d’additionner quelques milliers de lignes.
-*Pour pouvoir travailler avec les données dans l'espace de travail Excel un menu PowerPivot est intégré au ruban Excel.
-*D’ajouter et consolider des données émanant de plusieurs sources tels qu' Excel, Access, SQL, Web, etc. Il suffit simplement d'associer les différentes bases de données entre elles avec un champ commun. Par exemple :l'identifiant d'un client.
-*Intégrer des données qui évoluent sans cesse aussi facilement qu’un flux RSS. La mise à jour se fait instantanément sans alourdir les fichiers Excel.
-*PowerPivot demande de maîtriser un "nouveau" langage nommé "DAX" (Data Analysis Expressions). Le langage "DAX" est une extension des fonctions traditionnelles d'Excel. Il sert entre autre a effectuer des calculs qui pouvaient être difficiles à faire auparavant, comme créer des formules qui réfèrent à des tables ou des colonnes associées.
-*Il peut être utilisé via Sharepoint et permet ainsi de publier, partager et collaborer facilement.
-*Le traitement des données s'effectue avec un moteur d'analyse en mémoire xVelocity Analysis Services (VertiPaq) local. Ce dernier compacte, charge les données et les met à disposition d'objets de visualisation, comme des tableaux croisés dynamiques, dans une feuille de travail. Le moteur s'exécute dans le processus Excel. Il n'est pas nécessaire d'effectuer des tâches d'administration ou de configuration. Le moteur est un composant interne du complément PowerPivot à Excel.
+En Conclusion, PowerPivot permet à EXCEL de devenir un outil de décisionnel (Business Intelligence) très puissant et en même temps simple d’utilisation.
-En Conclusion, PowerPivot permet à Excel de devenir un outil "BI" ("Business Intelligence") encore plus intéressant et simple d’utilisation. En effet, il permet de transformer de très grandes quantités de données en informations utiles à la prise de décision.
 == Ajout de données ==
 A partir de cet add-in, il est possible d'ajouter des données de différentes manières :
-Tout dabord, à partir d’une liste de données (sous forme de plage ou de tableau) lors de la demande d’insertion d'un TCD => cocher "ajouter ces données au modèles de données"
+* à partir d’une liste de données (sous forme de plage ou de tableau) lors de la demande d’insertion d'un TCD => cocher "ajouter ces données au modèle de données". Dans ce cas, le TCD utilisera automatiquement le modèle de données. Il sera possible dans le volet de construction de ce TCD, de sélectionner "Actif" ou "Tous" pour afficher les champs du tableau ou les champs de l’ensemble des tableaux en relation.
 [[File:Powerpivot1.png|center|Powerpivot]]
+* à partir d’un tableau, il faudra pour cela ajouter automatiquement le tableau au modèle de données en établissant une relation avec un autre tableau : nouvelle fonctionnalité accessible depuis l’onglet "Données". Dans ce cas il faut préciser, lors de la création d’un tableau croisé, ce que l’on souhaite faire. Si on ne précise rien, c’est le tableau qui est utilisé pour la création du TCD. De ce fait, on peut ensuite préciser si l’on souhaite utiliser le modèle en sélectionnant "Plus de tables" dans la liste des champs du volet de construction du TCD. Sinon, on peut, comme dans le cas « d’ajout à partir d’une liste de données », cocher "Ajouter ces données au modèles de données". Ceci parait étonnant du fait que les données soient déjà dans le modèle.
-Dans ce cas, le TCD utilisera automatiquement le modèle de données. Il sera possible dans le volet de construction de ce TCD, de sélectionner "Actif" ou "Tous" pour afficher les champs du tableau ou les champs de l’ensemble des tableaux en relation.
-On peut aussi ajouter des données à partir d’un tableau.
-Il faudra pour cela qu'on ajoute automatiquement le tableau au modèle de données en établissant une relation avec un autre tableau : nouvelle fonctionnalité accessible depuis l’onglet "Données". Dans ce cas il faut préciser, lors de la création d’un tableau croisé, ce que l’on souhaite faire :
-* Si on ne précise rien, c’est le tableau qui est utilisé pour la création du TCD. De ce fait ,on peut ensuite préciser si l’on souhaite utiliser le modèle en sélectionnant "Plus de tables" dans la liste des champs du volet de construction du TCD.
 [[File:Powerpivot2.png|center|Powerpivot]]
+* à partir de données externes, pour la création du TCD, on devra cocher "Utiliser une source de données externes", puis on sélectionnera la connexion qui s'est créée dans le classeur, si on souhaite utiliser des données externes pour une relation, on peut :
+** Établir une connexion à ces données en cochant dans la fenêtre d'importation
+** "Tableau", ou si on ne souhaite pas les importer dans un onglet, "Ne créer que la connexion"
-* Sinon, on peut, comme dans le cas « d’ajout à partir d’une liste de données », cocher "Ajouter ces données au modèles de données". Ceci parait étonnant du fait que les données soient déjà dans le modèle.
+** "Ajouter ces données au modèle de données"
-Enfin, PowerPivot permet d'ajouter à partir de données externes.
-Si on souhaite utiliser des données externes pour une relation, on peut :
-* Établir une connexion à ces données en cochant dans la fenêtre d'importation
-**"Tableau", ou si on ne souhaite pas les importer dans un onglet, "Ne créer que la connexion"
-**"Ajouter ces données au modèle de données"
 [[File:Powerpivot3.png|center|Powerpivot]]
-* Pour la création du TCD, on devra cocher "Utiliser une source de données externes", puis on sélectionnera la connexion qui s'est créée dans le classeur.
 [[File:Powerpivot4.png|center|Powerpivot]]
@@ Ligne 86 : / Ligne 61 : @@
 == Intérêt des relations PowerPivot  ==
-Sur le même principe qu'une base de données, les relations permettent de tirer parti des liens existants entre les données en évitant la répétition. La relation est toujours une relation de 1 à N, contrairement à Excel où l'on utilise le sens N à 1.
+Sur le même principe qu'une base de données, les relations permettent de tirer parti des liens existants entre les données en évitant la répétition. La relation est toujours une relation de 1 à N, contrairement à EXCEL qui n'impose pas de relation naturelle.
+Les relations PowerPivot évitent cependant des formules comme RECHERCHEV pour croiser les données de plusieurs tables si l'objectif de la formule est l'analyse par TCD. Par exemple avec :
-Dans les versions « classiques », l'intérêt est limité car :
-* le modèle de données n'est pas accessible.
-* Impossibilité d'effectuer des calculs en langage DAX.
-Les relations PowerPivot évitent cependant des formules comme RECHERCHEV pour croiser les données de plusieurs tables si l'objectif de la formule est l'analyse par TCD.
-Par exemple avec :
 * un tableau de factures où seul l'identifiant client apparait.
+* un tableau de clients, où figurent l'adresse du client et l'identifiant du commercial chargé du client
+La relation entre les deux tableaux permet une analyse du chiffre d'affaire par département ou par ville ou par commercial.
-* un tableau de clients, où figurent l'adresse du client et l'identifiant du commercial chargé du client.<br>
-La relation entre les deux tableaux permet une analyse du chiffre d'affaire par département ou ville ou commercial.
 == PowerPivot version ProPlus   ==
 Dans la version pro plus, on bénéficie de tous les avantages de l’add-in tels que :
 * un onglet PowerPivot
+* une interface spécifique permettant de visualiser et optimiser le modèle de données
+* l'autorisation des calculs en langage DAX permettent de tirer toute la puissance possible des relations
-* une interface spécifique permettant de visualiser et optimiser le modèle
 Pour ajouter des données au modèle, en plus des trois possibilités citées pour toutes les versions, PowerPivot ProPlus permet :
 * L’utilisation de la commande ''Ajouter au modèle de données'' de l'onglet PowerPivot
 * ''Obtenir des données externes'' dans l'interface PowerPivot qui permet de :
 ** n'importer que certains champs d'une table ou vue.
 ** n'importer que certains enregistrements d'une table ou vue.
 * créer une requête pour définir les données à importer.
 Dans les 3 cas on obtient une requête, implicite ou explicite pouvant être modifiée à tout moment.
-Les relations dans cette version Pro Plus sont bien plus puissantes que dans la version « Classique ».
-En effet, l'optimisation possible du modèle de données et le langage DAX permettent de tirer tout le parti possible des relations.
-Le langage DAX permet de réaliser des calculs pouvant être réalisés soit dans l’interface spécifique, soit depuis l’onglet PowerPivot. Cela permet d’obtenir des résultats non disponibles dans les tableaux croisés dynamiques classiques.
 == Langage DAX ==
+Le langage DAX permet de réaliser des calculs pouvant être réalisés soit dans l’interface spécifique, soit depuis l’onglet PowerPivot. Cela permet d’obtenir des résultats non disponibles dans les tableaux croisés dynamiques classiques.
-Les formules DAX sont utilisées dans des tables PowerPivot ou dans des tableaux croisés dynamiques Excel. DAX est un ensemble de fonctions, d'opérateurs et de constantes qui peuvent être utilisés dans une formule, ou expression, afin de calculer une ou plusieurs valeurs. DAX vous aide à créer de nouvelles informations à partir de données figurant déjà dans votre modèle.
-Excel permet de créer un classeur et d'y importer des données, on peut même créer des tableaux croisés dynamiques ou des graphiques croisés dynamiques sans utiliser de formules DAX.
-Mais DAX permet d’analyser des données de ventes stratégiques concernant plusieurs catégories de clients et s'étalant sur des plages de dates différentes par exemple.
+Les formules DAX sont utilisées dans des tables PowerPivot ou dans des tableaux croisés dynamiques Excel. DAX représente un ensemble de fonctions, d'opérateurs et de constantes qui peuvent être utilisés dans une formule ou une expression, afin de calculer une ou plusieurs valeurs. DAX vous aide à créer de nouvelles informations à partir de données figurant déjà dans votre modèle.
 Les formules DAX sont très semblables aux formules Excel. Pour en créer une formule DAX, tapez
 sur le signe égal, suivi d'un nom de fonction ou d'une expression, et de toutes les valeurs requises.
 Pour mieux comprendre prenons un exemple simple :
@@ Ligne 150 : / Ligne 101 : @@
 [[File:DAXlangage.jpg|center|DAX langage]]
-A - L'opérateur de signe égal (=) indique le début de la formule.
+* A ==> l'opérateur de signe égal (=) indique le début de la formule
+* B ==> la colonne [MontantVentes], les crochets indiquent une référence de colonne dans une formule, contrairement aux formules Excel qui font référence à une cellule, une formule DAX fait toujours référence à une colonne
+* C ==> Opérateur mathématique de soustraction (-).
-B – La colonne [MontantVentes] Une référence de colonne dans une formule est toujours entourée par des crochets [].
+* D ==> la colonne référencée [CoutTotl]
-Contrairement aux formules Excel qui font référence à une cellule, une formule DAX fait toujours référence à une colonne.
-C Opérateur mathématique de soustraction (-).
-D La colonne référencée [CoutTotl]
 {{Bas de page