A. POWER PIVOT

A.                   POWER PIVOT

Il fait l'objet d'un complément à Excel.

1.            ACTIVATION POWER PIVOT

Comme tous les compléments, il doit être activé dans les options d'Excel.

MENU FICHIER
<clic g> sur
volet gauche - <clic g> sur
en bas - <clic g> sur de
<clic g> sur
cocher les compléments en particulier
pour valider

 

 

Dans les options, la liste des compléments affiche :

 

Dans le menu, l'onglet "Power Pivot" est présent.

 

Si les onglets ne sont toujours pas affichés dans le ruban, aller dans la personnalisation du ruban puis cocher pour ajouter l'onglet "power pivot"

 

Vérifier que Power Pivot est activé dans Excel – Sinon, l'activer

 

Il faut cependant savoir que Microsoft a mis en pause le développement de Power Pivot et Power view au sein de Excel au bénéfice de Power Bi, plus puissant et externe à Excel.

2.            UTILISATION POWER PIVOT

Power Pivot permet d'effectuer une puissante analyse de grandes quantités données, de créer des modèles de données élaborés et de les partager éventuellement dans le cloud. Il peut intégrer des données d'Excel ou de tout autre logiciel dans lequel les données sont organisées en tables (Access, SqlServer…). Il est possible de faire des allers/retours entre Excel et Power pivot.

Pour afficher la fenêtre Power Pivot :

  POWERPIVOT
groupe "modèle de données (1er bloc)
feuille de données affichée
une cellule de données sélectionnée
<clic g> sur

 

 

ONGLET DONNÉES
groupe "outils de données" (5ème bloc)
feuille de données affichée
une cellule de données sélectionnée
<clic g> sur
la fenêtre Power Pivot s'affiche

 

 

Pour fermer la fenêtre Power Pivot :

BARRE DE TITRE
<clic g> sur

 

où

 

MENU FICHIER
<clic g> sur

 

Ouvrir "tables_powerpivot"
Afficher la fenêtre Power Pivot

3.            AJOUTER AU MODÈLE

La première étape consiste à ajouter les données à "power pivot".

ONGLET "POWERPIVOT"
groupe "tables" (3ème bloc)
feuille de travail affichée et une cellule de données sélectionnée
<clic g> sur  
pour valider
la fenêtre Power Pivot s'affiche avec les données de la feuille Excel

Dans le classeur "tables_powerpivot"
Ajouter les données de la feuille de travail "Ventes" au modèle

4.            MODIFIER LE MODÈLE

Les données vont être traitées au sein de Power Pivot avant de créer des mesures, des indicateurs de performance ou de gérer les dates. Ce traitement peut être du type :

Ajout de données Tri / filtre Création de colonnes calculées Insertion d'une fonction

Actualisation Attribution de format Masquage de colonnes Définition du comportement des tables…

a)            AJOUTER DES DONNÉES

D'autres données Excel peuvent être ajoutées à celles existantes. Elles peuvent être ajoutées avec le menu en revenant dans Excel ou par un copier/Coller.

  POWERPIVOT
groupe "modèle de données (1er bloc)
<clic g> sur
pour valider
la fenêtre Power Pivot s'affiche avec les données de la feuille Excel – les données sont liées

 

 

PRESSE-PAPIERS
données sélectionnée dans Excel
<clic g> sur  
<clic g> sur affiche Power pivot
<clic g> sur
les données ne sont pas liées

Nommer les onglets de table pour les identifier facilement

 

Dans le classeur "tables_powerpivot"
Ajouter au modèle les données de la feuille de travail "Représentants" en utilisant et les données de la feuille de travail "Outils" avec le presse-papier
Renommer les onglets des tables Power pivot comme ceux des feuilles de travail du classeur

b)            ACTUALISER DES DONNÉES

Il est important de pouvoir actualiser les données de Power Pivot. Le terme de "modèle" est employé car les données traitées par Power Pivot peuvent évoluer en valeur et en quantité.

ONGLET ACCUEIL
groupe "obtenir des données externes" (2ème bloc)
une cellule de la table à actualiser étant sélectionnée
<clic g> sur
<clic g> sur

Dans le classeur "tables_powerpivot", dans la feuille de travail Excel "Ventes"
Modifier les ventes de Mr Martin en "marteaux" à 75 k€, Actualiser les données de la table "ventes dans Power Pivot " et vérifier la mise à jour
Annuler la modification et Actualiser la table "ventes"

c)             TRIER LES DONNÉES

Les données importées dans Power Pivot peuvent être triées par colonne, un tri remplaçant le précédent.

ONGLET ACCUEIL
groupe "trier et filtrer" (4ème bloc)
une cellule de la colonne de tri étant sélectionnée
<clic g> sur ou

 

 

Le tri peut être effectué directement dans la table.

BOUTON GAUCHE
<clic g> sur d'une tête de colonne
<clic g> sur l'une des options
 

 

Trier la table "ventes" par nom

Une colonne de tri affiche au lieu de
Pour effacer le tri, <clic g> sur du menu ou   de la liste

 

 

Il est possible de faire un tri par colonne, c’est-à-dire trier une colonne d'après les valeurs d'une autre colonne.

ONGLET ACCUEIL
groupe "trier et filtrer" (4ème bloc)
une cellule de la colonne de tri étant sélectionnée
<clic g> sur
renseigner la colonne qui doit être triée
renseigner la colonne par laquelle elle doit être triée
pour valider

 

 

La colonne triée doit n'avoir qu'une seule valeur pour la colonne de tri

 

Dans le classeur "tables_powerpivot", dans la table "représentants"
Trier les représentants par région puis effacer le tri

d)            FILTRER LES DONNÉES

Le filtre va permettre de n'afficher que les valeurs répondant à certains critères.

BOUTON GAUCHE
pointer sur le triangle bas à droite du titre de colonne
<clic g> pour dérouler la zone de liste
<clic g> pour décocher les valeur à ne pas prendre en compte
seuls les enregistrements répondant aux critères restent alors affichés ; les autres sont masqués.

 

Une colonne filtrée affiche au lieu de
Pour effacer le filtre, <clic g> sur du menu ou   de la liste

 

Dans le classeur "tables_powerpivot", dans la table "ventes"
n'afficher les données que pour les outils "scies" et "marteaux"
Effacer le filtre

Des critères plus complexes peuvent être appliqués à des valeurs numériques.

BOUTON GAUCHE
pointer sur le triangle bas à droite du titre de colonne de valeurs
<clic g> sur
<clic g> sur le critère de filtre
le renseigner
pour valider

N'afficher que les ventes supérieures ou égales à 50 k€ puis Effacer le filtre

 

 

Des opérateurs logiques peuvent être utilisés.

OPERATEURS

• et   intersection - les deux conditions doivent être simultanées
• ou   union - l'une ou l'autre des conditions est nécessaire et suffisante

N'afficher que les ventes supérieures à 100 k€ et les ventes inférieures à 50 k€
Effacer le filtre

 

 

Des critères peuvent être appliqués à des valeurs alphanumériques.

BOUTON GAUCHE
pointer sur le triangle bas à droite du titre de colonne de texte
<clic g> sur
<clic g> sur le critère de filtre
le renseigner
pour valider

N'afficher que les ventes de scies et de marteaux
Effacer le filtre

Les critères de plusieurs colonnes peuvent être combinés

 

N'afficher que les ventes supérieures à 100 k€ et les ventes inférieures à 30 k€ et n'afficher que les ventes de scies et de marteaux

e)            FORMATER LES DONNÉES

Les données importées n'ont pas toujours le bon format :

• Chiffres considérés comme texte
• Valeurs monétaires considérées comme chiffres bruts
• Dates considérées comme texte…

 

 

Power Pivot permet de rectifier cela

ONGLET ACCUEIL
groupe "mise en forme" (3ème bloc)
une cellule de la colonne étant sélectionnée
<clic g> sur de
sélectionner le type de données
sélectionner le format
toutes les données de la colonne adoptent le format choisi

 

Dans le classeur "tables_powerpivot", dans la table "ventes", assigner à la colonne "ventes" le format "devise  - euro"

f)              AJOUTER UNE COLONNE CALCULÉE (formules)

Il est possible de faire des calculs avec des formules dans Power Pivot, comme dans Excel.

 

BOUTON GAUCHE 
<clic g> sur
saisir la formule comme dans Excel en commençant par un signe égal "="
valider avec <entrée> ou
la formule est insérée dans la nouvelle colonne

 

 

BOUTON DROIT
<clic d> sur la tête de colonne
saisir la formule comme dans Excel en commençant par un signe égal "="
valider avec <entrée> ou
la formule est insérée dans la nouvelle colonne

 

Par exemple, Il faut parfois modifier les données pour qu'elles s'adaptent au format. Dans la colonne "ventes", nous avons des k€ mais la devise n'existe pas, il va donc falloir multiplier par 1000 les valeurs en saisissant dans la colonne :
je clique sur
je saisis le signe "égal"
je clique sur la première valeur de "ventes" soit "37,000 €'
j'appuie sur la touche "*", je saisis "1000" puis je clique sur

 

Dans le classeur "tables_powerpivot", dans la table "ventes", créer une colonne de ventes en k€

 

 

Le nombre de décimales peut être géré.

ONGLET ACCUEIL
groupe "mise en forme" (3ème bloc)
<clic g> sur une valeur
<clic g> sur pour ajouter et sur pour supprimer une décimale
Toute la colonne prend la mise en forme

 

Dans le classeur "tables_powerpivot", dans la table "ventes", dans la colonne calculée, supprimer les décimales en trop

 

La gestion des colonnes est proche de celle d'Excel et s'effectue dans le menu "conception" onglet "colonnes"

g)            NOMMER UNE COLONNE

Les colonnes peuvent être nommées

BOUTON GAUCHE
<double clic> sur le nom de la colonne
saisir le nom de la colonne
valider avec <entrée>
la colonne est renommée

 

 

BOUTON DROIT
<clic d> sur la tête de colonne
saisir le nom de la colonne
valider avec <entrée>
la colonne est renommée

Dans le classeur "tables_powerpivot", dans la table "ventes", renommer la colonne calculée "VENTES EN K€"

h)            AJOUTER UNE COLONNE CALCULÉE (fonctions)

Les fonctions peuvent être utilisées dans une nouvelle colonne.

CONCEPTION
groupe "calcul" (2ème bloc)
<clic g> sur
<clic g> sur
sélectionner la fonction
pour valider

 

 

BOUTON DROIT
<clic d> sur la tête de colonne
<clic g> sur
sélectionner la fonction
pour valider

 

Comme dans Excel, une fonction peut être insérée directement à partir de la barre de formule avec

 

Pour créer une formule comme ci-dessous :
je clique dans la colonne "ajouter une colonne"
je clique sur de la barre de formule, choisis dans la liste et clique sur
je choisis par un <double  clic>  dans la liste des colonnes proposées et tape parenthèse fermante ")" pour fermer la fonction
Je tape le signe moins "-",  clique dans la colonne "ventes en k€" et clique sur pour valider
je clique 2 fois sur de l'onglet "accueil" pour enlever les décimales

 

Dans le classeur "tables_powerpivot", dans la table "ventes",
Créer une colonne affichant l'écart à la moyenne des ventes :

i)              MASQUER UNE COLONNE

Les colonnes inutiles peuvent être masquées pour les utilisateurs.

BOUTON DROIT
<clic d> sur la tête de colonne
<clic g> sur
la colonne est grisée et ne s'affichera plus pour les utilisateurs
 

 

 

Les colonnes masquées peuvent ne pas être affichées du tout.

ONGLET ACCUEIL
groupe "mise en affichage" (dernier bloc)
<clic g>
idem pour réafficher la colonne masquée

Dans le classeur "tables_powerpivot", dans la table "ventes",
Masquer pour les utilisateurs la colonne "ventes" d'origine et ne pas l'afficher

j)              CRÉER UNE DESCRIPTION

Une colonne ou une fonction peuvent recevoir une description.

BOUTON DROIT
sélectionner la tête de colonne ou la fonction
<clic d>
saisir la description
pour valider

 

Dans le classeur "tables_powerpivot", dans la table "ventes",
Saisir la description de la colonne "ventes en k€" : "ventes recalculées en euros" et de la fonction "moyenne" : "moyenne calculée des ventes"

 

5.            CRÉER UNE MESURE

La mesure, qui qualifie les données existantes, peut être créée dans Power Pivot ou dans Excel.

a)            CRÉER UNE MESURE DANS POWER PIVOT

Elle s'effectue avec les outils de fonction et qualifie une colonne de données, calculée ou non.

ONGLET CONCEPTION
groupe "calcul" (2ème bloc)
<clic g> sur une donnée de la colonne
<clic g> sur
ou sur   de , sélectionner la fonction et pour valider
agrandir éventuellement la colonne comme dans Excel
la fonction s'affiche dans le volet bas

Dans le classeur "tables_powerpivot", dans la table "ventes",
Créer une mesure calculant la somme des ventes en K€ dans Power Pivot

b)            CRÉER UNE MESURE DANS EXCEL

Un outil spécifique permet de créer ces mesures.

ONGLET "POWERPIVOT"
groupe "calculs" (2ème bloc)
<clic g> sur
<clic g> sur
saisir le nom du champ et sa description
<clic g> sur et choisir une fonction (noms en anglais)
saisir le début du nom de champ puis <double clic> dans la liste pour choisir
la liste des champs comprend les champs calculés de Power Pivot
fermer la parenthèse
<clic g> sur  pour valider

Ne pas oublier d'actualiser les données dans Power Pivot pour voir la nouvelle mesure

 

Créer une mesure calculant la moyenne des ventes en K€ dans Excel

c)             GÉRER LES MESURES

Cette gestion est possible dans Power Pivot.

ONGLET ACCUEIL
groupe "affichage" (dernier bloc)
<clic g> sur
la zone de calcul affiche les mesures dans un volet en bas de la table – idem pour ne plus les afficher
modifier dans la barre de formule – touche "suppr" pour supprimer

 

 

Ou dans Excel.

ONGLET "POWERPIVOT"
groupe "calculs" (2ème bloc)
<clic g> sur
<clic g> sur
sélectionner la mesure
<clic g> sur  
pour modifier les mesures existantes dans Excel quelle que soit leur origine
<clic g> sur
pour la supprimer

Afficher les mesures dans Excel et dans Power Pivot

6.            CRÉER UN INDICATEUR DE PERFORMANCE CLÉ

L'indicateur de performance clé ou KPI est basé sur une mesure et peut être créé dans Power Pivot ou dans Excel.

a)            CRÉER UN KPI DANS POWER PIVOT

ONGLET CONCEPTION
groupe "calcul" (2ème bloc)
<clic g> sur la mesure
<clic g> sur
choisir la mesure
indiquer si elle est basée sur une valeur absolue ou relative
choisir la modélisation et la représentation
pour valider

 

Créer une mesure "moyenne des écarts à la moyenne" et créer un KPI comme ci-avant dans Power Pivot

 

Un petit graphique vient enrichir la mesure

b)            CRÉER UN KPI DANS EXCEL

ONGLET "POWERPIVOT"
groupe "calculs" (2ème bloc)
<clic g> sur
<clic g> sur
choisir la mesure
indiquer si elle est basée sur une valeur absolue ou relative
choisir la modélisation et la représentation
pour valider

 

Créer un KPI comme ci-avant dans Excel

7.            CRÉER UN TABLEAU CROISÉ DYNAMIQUE

La procédure est ici semblable à Excel.

ONGLET ACCUEIL
groupe "affichage" (dernier bloc)
<clic g> sur une donnée
<clic g> sur
sélectionner
pour valider
le volet "tableau croisé dynamique" s'affiche
<clic g> sur pour dérouler une table et afficher les champs
<faire glisser> les champs à leur position
le tableau croisé dynamique s'actualise en temps réel

Dans "tables_powerpivot", dans la table "ventes",
Créer un tableau croisé dynamique simple comme ci-dessous

           

8.            GÉRER LES DATES

Power Pivot permet de créer un calendrier ou d'utiliser un champ "date" pour considérer la table comme "table de dates". Ces dernières doivent cependant être uniques.

ONGLET CONCEPTION
groupe "calendriers" (4ème bloc)
<clic g> sur pour créer un calendrier
ou <clic g> sur pour marquer une table comme table de dates