Excel avancé

Manuele Kirsch Pinheiro

[Link]-Pinheiro@[Link]
 Rappel des objectifs
• Importance de la maîtrise de l’ou3l Excel en entreprise
– Facile à u3liser et puissant
– Largement u3lisé pour
l’analyse de données
– Business Intelligence
des PMEs

⬇‍‍
– Très demandé en
entreprise
 Bibliographie
• Niveau débutant
– Corinne HERVO (Ed.), « Microsoft Excel 2016 », Editions ENI, 2015, ISBN
978-2-7460-9743-8, p. 516.
• Niveau moyen
– Bernard MINOT, Jean-Michel LÉRY, « Excel 2010 et VBA », collection
Synthex Informatique, Pearson, 2ème édition, 2012, ISBN 978-2-7440-
7598-8, p. 231.
• Niveau avancé
– Pierre RIGOLLET, « Excel 2010 : Tableaux croisés dynamiques », collection
Objectif Solutions, Editions ENI, 2010, ISBN 978-2-7460-5928-3, p. 250.
– Rémy LENTZNER, « Excel, les macros, initiation à VBA », collection
Informatique du quotidien, Editions Remylent, 2016, ISBN 978-2-
9557694-1-6, p. 134.
– Pierre RIGOLLET, « Analyser efficacement vos données à l’aide des
tableaux croisés dynamiques », collection Solutions Business, Editions
ENI, 3ème édition, 2016, ISBN 978-2-409-00361-5, p. 303.
 Objectifs de la journée

• Objectifs de la journée
– Savoir concevoir et utiliser les tableaux croisés
dynamiques
– Savoir filtrer des données sur un tableur
Des suggestions
sont toujours
bienvenues !
 Filtres & Tableaux

• Filtres et tableaux croisés

– Filtres : synthèses sur un seul paramètre
– Tableaux : synthèses sur plusieurs paramètres
• Tableaux croisés
– Synthèse (fonction au choix) croisant 2 ou plus
paramètres (attributs)

Traiter une quan7té Obtenir synthèse / Visualiser chiffes clés,

importante de sta@s@ques variées faires des graphiques de
données sur 1 ou + critères synthèse
 Tableaux croisés dynamiques
Attention : lorsque les données
sources sont mises à jour, les tableaux
Structure & ne les sont pas forcément.
Concep?on
(choix des champs
et des fonc<ons)
t) Champs perme@ant
b u
attri p de filtrer les données
( am Rapport
Ch

1er ligne Filtre rapport

A1 A2 A3
entêtes
xx xx xx Étiquettes des
enregistrement Valeurs cols
colonnes
yy yy yy (n-uplet) Valeurs lignes

Synthèse selon la
de s èse fonc<on choisie
Source des données e s th
u e@ s n
(base de données) q
É< ligne Sy On croise
normalisée lignes x colonnes
 Tableaux croisés dynamiques
• La fiabilité de l’analyse dépend de la
qualité des données sources
– Si source sur Excel, mettre en place des contrôles de validation

• Recommandations pour les sources de données

– Chaque colonne représente un champs (attribut)
– 1ère ligne contient les noms de champs
– Ne pas fusionner les cellules des titres
– Pas deux attributs avec le même nom (ligne titres)
– Pas de nom d’attribut vide (ligne titres)
– Éviter les lignes et les colonnes vides
– Pas de sous-totaux dans les données sources
– Préférer les zéro aux cellules vides pour les chiffres
– 1 enregistrement par ligne
(ex. ttes données d’un client sur une même ligne)
 Tableaux croisés dynamiques

e f e u ille
Ce#
e ca l cul
d le ?
nt - e l
convie
 Tableaux croisés dynamiques
Chaque colonne a un nom ≠

1ère ligne avec les

noms des colonnes

Pas de cellule vide

Pas de totaux / sous-

totaux dans les données
 Tableaux croisés dynamiques
On ré
fl
avant échit
• Déroulement de l’analyse lance
de se
r!
1) Identifier les sources de données
• Choisir les sources à utiliser : plages sur Excel, source
externe (MS Access…)
• Les analyses qu’on pourra effectuer dépendent des
données choisies et de leur format
2) Choisir l’emplacement du futur rapport
• Nouvelle feuille dans l’Excel ou feuille externe
3) Placer les champs dans les lignes / colonnes
• On regroupe les données par ligne/colonne (group by)
4) Choisir la fonction à appliquer
• Somme, nombre, moyenne…
 Tableaux croisés dynamiques
Créa5on du tableau croisé
Source interne
Ou
Ficher externe

Zone du
rapport
Données sources Rapport à
concevoir Configuration
du rapport
 Tableaux croisés dynamiques

Les calculs sont mis

à jour en fonction Attributs disponibles
des filtres

A/ributs (champs)
Attributs (champs) définissant les valeurs
permettant de qui seront dans les
filtrer les données colonnes
(synthèse d’une
partie des données)
Attributs et calculs qui
A/ributs (champs) seront réalisés en
définissant les croisant
valeurs qui seront lignes x colonnes
dans les lignes (fonctions de synthèse)
 Tableaux croisés dynamiques
Les valeurs de l’a9ribut stage se
retrouvent dans les colonnes.

Calcul réalisé
sur les valeurs
choisies

Les valeurs de l’a9ribut stage se

retrouvent dans les lignes. Ici, on croise les
valeurs de stage et
période en calculant
le nombre de stages

Exemple : on veut connaître le

Données sources nombre de stages par période.
 Tableaux croisés dynamiques
On peut ajouter plusieurs calculs (Nombre,
Somme, moyenne…) sur plusieurs attributs.

Synthèse
de la ligne

Synthèse de la
colonne
 Exercices

• A par&r du fichier « [Link] » :

– Construire un tableau croisé perme>ant
d’analyser
• Le nombre de stages (ligne) et par période (colonne)
• Placer ces valeurs dans les lignes
 Tableaux croisés dynamiques
Des multiples fonctions
sont disponibles pour
l’analyse des données.
 Exercices

• A partir du fichier « [Link] » :

– Modifier le tableau croisé précédant
• Ajouter à la durée moyenne de ces stages
• Modifier le tableau afin d’afficher les durées min et
max des stages par périodes
 Tableaux croisés dynamiques
On peut aussi filtrer les
données.

Prise en compte d’un sous-

ensemble des données

La synthèse est mise à jour

automatiquement.
 Exercices

• A partir du fichier « Garanties [Link] » :

– Construire un tableau croisé permettant
d’analyser
• Les montants total des contrats par vendeur (ligne) et
par secteur (colonne)
• Modifier le tableau pour avoir les montant des contrats
par vendeur et type de contrat (lignes) par rapport aux
secteurs (colonne)
• Ajouter à l’analyse le nombre de contrats par vendeur
• Ajouter un filtre par option
 Tableaux croisés dynamiques
• Bon à savoir :
– Un doute sur les données ? On peut revenir sur les
données utilisées pour une synthèse
• Afficher détails
 Tableaux croisés dynamiques

• Bon à savoir :
– Attention aux totaux généraux
avec les fonctions min, max, moyenne, etc.
• C’est la même fonction qui est appliquée (e.g. « totaux
min » = min global)
 Tableaux croisés dynamiques

• Bon à savoir :
– A-en/on aux labels qui se répètent (homonymes)
• Exemple : module « Informa/que » en L3 et en M1
L’étiquette « Informatique »
correspond à deux matières
distinctes. Or dans la synthèse,
on ne peut plus les distinguer.

Attention à la saisie :
Sécurité ≠ Securite
 Exercices
• A partir du fichier « [Link] » :
– Construire un tableau croisé permettant
d’analyser
• Volume total par UFR (colonne) et par année (ligne)
• Ajouter la moyenne du volume
– Revenir sur les données pour vérifier la moyenne
de 2008
– Modifier le tableau pour lui ajouter la matière
(ligne)
• Comment peut-on distinguer « Informatique » L3 et
« Informatique » M1 ? Que faire pour les distinguer ?
– Filtrer par filière
 Tableaux croisés dynamiques
• Bon à savoir :
– On peut inclure dans le tableaux des données
« calculées » (issues des formules)

Données sources calculées

avant la synthèse
 Tableaux croisés dynamiques
• Bon à savoir :
– On peut ajouter dans le tableaux des nouveaux
champs calculés (après la synthèse)

On peut construire des formules sur les champs de

synthèse. Le nouvel attribut devient alors disponible pour
être utilisé sur le rapport.
 Tableaux croisés dynamiques
• Bon à savoir :
– Les paramètres
des champs
fonctionnent
aussi pour les
champs
calculés
 Exercices
• A partir du fichier « [Link] » :
– Ajouter sur les données une colonne eqTD
• 1h CM = 1,5h réalisée
– Construire un tableau croisé permettant
d’analyser
• Total eqTD par UFR (colonne) et par année (ligne)
– Ajouter un champs calculé heureSup
• heureSup = eqTD – 192
– Ajouter au tableau précédent
• heureSup (colonne) par année
• Différence en % des heureSup par rapport à 2008
 Tableaux croisés dynamiques

• Mise en forme
– Comme dans le mode plan, on peut
développer/réduire les lignes avec les + / -
• On peut désac;ver
ce comportement
 Tableaux croisés dynamiques
• Mise en forme
– La mise en forme de cellules est toujours possible
– Mise en forme du tableau sur
les Options
(onglet « Analyse tableau… »)

– On peut utiliser/définir des styles de mise en forme pour

les tableaux (onglet « création »)
 Exercices
• A partir du fichier « [Link] » :
– Revenir sur le 1er tableau construit
• Mettre en forme le tableau et les cellules contenant la
moyenne (limité à 1 seule case décimal)
– Revenir sur le 2ème construit
• Ajouter une mise en forme conditionnelle pour afficher
en rouge si heureSup ≥ 50
 Tableaux croisés dynamiques
• Grouper les données
– On peut créer des tranches de valeur
à partir des valeurs sur les lignes/colonnes
– Affichage et synthèse par tranche
 Tableaux croisés dynamiques
• Grouper les données
– Groupement par date (jour, mois, année)
 Exercices
• A partir du fichier « [Link] » :
– Créer un nouveau tableau croisé
• Moyenne des salaires par sexe des employés
(valeurs des salaires sur les lignes, sexe sur les colonnes)
• Grouper les valeurs des salaires par tranche de 1000€
– Créer un nouveau tableau croisé
• Moyenne des salaires par date de naissance et sexe des
employés
• Grouper les dates de naissance par année et trimestre

• A partir du fichier « [Link] »

– Construire un tableau croisé de la durée moyenne des
pannes par type de panne et mois (date)
• Afficher correctement les données de durée (format heure)
• Grouper les dates par mois et trimestre
 Tableaux croisés dynamiques

• Graphiques
– On peut faire des graphiques croisés (i.e. des
graphiques sur les tableaux croisés)
Onglet Inser;on

Onglet Analyse tableau croisé…

 Tableaux croisés dynamiques
• Segments
– Il s’agit de filtres applicables sur le
tableau croisé
– Filtrage rapide
 Exercices
• A partir du fichier « [Link] »
– Ajouter un graphique à partir du tableau précédemment créé

• A partir du fichier « [Link] » :

– Construire un nouveau tableau croisé représentant :
• Le nombre de matières enseignées par volume (ligne) et par niveau et
modalité (colonne)
• Grouper le volume par tranche de 18h
– Ajouter dans la même feuille un tableau + graphique croisé :
• La somme eqTD par niveau (colonne) et année (ligne)
• Ajouter au tableau un filtre par UFR
• Ajouter une segment par année pour filtrer les données
• A l’aide du segment, afficher uniquement une année sur deux
– Toujours à la même feuille, ajouter un tableau + graphique croisé
• La somme de Heure Sup. et eqTD par année (ligne)
• Grouper les années 2 par 2
 Tableaux croisés dynamiques

• Mise à jour des tableaux croisés

– Si les données changent, le tableau n’est pas mise
à jour automatiquement

– Si des nouvelles lignes sont

ajoutées aux données sources, la mise à jour ne
les prendra pas en compte (idem pour les
colonnes)
• Solution : utiliser les plages dynamiques
 Tableaux croisés dynamiques
• Plages dynamiques
– On peut utiliser une fonction pour définir la plage
de données sources
– Fonction DECALER permet d’obtenir une plage
dynamique (sans réellement décaler les données)
• Cette fonction renvoie les coordonnées d’une plage par
rapport à une autre
Nb lignes et nb colonnes
Début de la plage (0 car on ne décale pas le début, juste la fin)
On se limite à 1000
=DECALER( Feuille!$A$1 ; 0 ; 0 ; Limite Excel 2007
Nombre de NBVAL( Feuille!$A$1:$A$1000) ; ±10000 lignes
valeurs NBVAL( Feuille!$A$1:$ZZ$1) ) On se limite à 52 col max
Limite Excel ±16000 col
 Tableaux croisés dynamiques

• Plages dynamiques
– Mais si on essaye d’indiquer directement la
formule lors
de la création
du tableau…
 Tableaux croisés dynamiques
• Plages dynamiques
– Enregistrer la formule en lui a4ribuant un nom

– U7liser ce nom en
tant que plage pour
le tableau

La mise à jour du
tableau prendra alors en
compte les nouvelles
lignes
 Exercices
• A partir du fichier « [Link] » :
– Construire un nouveau tableau croisé représentant :
• Le volume enseigné par voie (colonne) et par niveau
(ligne)
• Ajouter une nouvelle ligne aux données initiales (feuille
« services »
• Mettre à jour le tableau. Va-t-il prendre en compte la
nouvelle ligne ?
• Modifier le tableau pour qu’il utilise
une plage dynamique des données
(formule DECALER)
 Exercices
• A par&r du fichier « [Link] » :
– Construire un nouveau tableau croisé représentant :
• Le volume enseigné par voie (colonne) et par niveau
(ligne)
• Ajouter une nouvelle ligne aux données ini&ales (feuille
« services »
• MeEre à jour le tableau. Va-t-il prendre en compte la
nouvelle ligne ?
• Modifier le tableau pour qu’il u&lise
une plage dynamique des données
(formule DECALER)
 Tableaux croisés dynamiques
• Plusieurs sources dans le même Excel
– Données organisées en plusieurs feuilles à tableaux
– Prise en compte de données de ≠ tableaux
• Créa9on d’un modèle de données

Données organisées sur

plusieurs tableaux sur
différentes feuilles

Références entre les données

Code à Code Personne
 Tableaux croisés dynamiques
• Plusieurs sources dans le même Excel
– Lors de la création du TCD,
on l’associe à un
modèle de données

On utilise les
tableaux
 Tableaux croisés dynamiques
• Plusieurs sources dans le même Excel
– Lors de la création du TCD,
on l’associe à un
modèle de données

On u7lise les
tableaux

Les données des

autres tableaux
sont accessibles
Tableaux croisés dynamiques
Uniquement
sur Windows

Modèle de données :
on établit les liens (relations) entre
les attributs (colonnes)
 Tableaux croisés dynamiques
Tableau « Agents » Sur MacOS
« réunir » les
données

Avec « RECHERCHEV », on va pouvoir

récupérer les données des autres tableaux et
Tableau tout réunir sur un seul tableau.
« Heures »
Tableaux croisés dynamiques
Usage d’un tableau comme page
pour la prise en compte des
nouvelles valeurs
 Exercices

• A partir du fichier « [Link] »

– Créer deux tableaux (« agents » et « heures ») à partir des
données des feuilles « Agents » et « ReleveHeures ».
– Ajouter au tableau « heures » une colonne « durée »
• Durée = heure sortie – heure entrée
• Formater la colonne comme « Heure » (hh:mm:ss)
• Si vous utilisez une machine Windows
– Créer un TCD contenant les noms des agents (tableau « agents »)
et la somme de la durée (tableau « heures »)
• La colonne « code-personne » du tableau « heures » correspond à la
colonne « code » du tableau « agents »
• Si vous utilisez une machine MacOS
– Ajouter une colonne « nom » au tableau « heures ».
• Utiliser la formule RECHERCHEV pour trouver le nom de l’employé
– Créer un TCD contenant les nom des agents et la somme de la
durée
 Tableaux croisés dynamiques
• Sources extérieurs sous MS Access
– Utiliser les données qui sont sur une BdD Access
– Utiliser les tables ou les requêtes enregistrées
sous Access
 Tableaux croisés dynamiques
• Sources extérieurs sous MS Access
– On peut ajouter une nouvelle source ou dans la
créa7on du tableau proprement parlé
 Tableaux croisés dynamiques
• Sources extérieurs sous MS Access
– On peut choisir une requête ou une relation
définie dans la BdD
 Tableaux croisés dynamiques
• Sources extérieurs sous MS Access

Une fois la source extérieur connectée,

on dispose de tous les champs pour
réaliser le tableau croisé.
 Exercices

• Créer un nouveau fichier « [Link] »

– Connecter ce fichier à la BdD
« [Link] », à la requête
« RequêteVentes »
– Créer un tableau croisé à parDr de ces données
représentant le montant des ventes par type de
client et année.
– Ajouter un filtre par secteur et un filtre par
commercial.
 Contenu prévisionnel

• Contenu prévisionnel
üTableaux croisés dynamiques

• Extras
üFiltres et mode plan
 Filtres & Mode plan

• Filtres
– Les filtres servent à filtrer les données (affichant certains
et en cachant d’autres) sur place ou faisant une copie des
données selon un critère.
– Fonction de synthèse au choix (somme, nombre…)
• Différents types de filtres
– Par valeur de la liste
– Par mise en forme (couleur)
– Personnalisé
• Mode plan
– On regroupe les données de manière hiérarchique
– Des fonctions (sous-totaux…) calculées par niveau
Filtres par valeur de la liste
Onglet « Données »
Puis « Filtrer »

On clique sur le(s)

champ(s) sur lequel
on souhaite filtrer les
données.

Puis, on choisit les

valeurs qui nous
intéressent.
Filtres par valeur de la liste
On peut appliquer plusieurs filtres.
Sémantique : ET
Produit = « assurance vie »
ET
Conseiller = « Cornu »
 Filtres

Filtres personnalisés
(idéales pour les
données numériques)
 Filtres

Op*ons avancées
Possibilité de copier les données dans la même feuille
filtre ( plage données ; zone critère, copier dans )

Pas de mise à jour

automatique
 Mode plan
1° Trier les données 2° Définir les sous-totaux

Les données sont regroupées et

les sous-totaux calculés
 Exercices
• A partir du fichier « [Link] » :
– Extraire les données
• Contrats de « BOULANGER » vers la zone d’extraction
• Changer la valeur sur la Zone de critères.
Les données sont-elles mises à jour ?
– Filtrer les données
• Par produit (assurance et assurance vie)
• Par produit et par conseiller (Cornu)
• Par montant entre 300€ et 400€
• Par montant supérieur à la moyenne
– Filtrer les données par couleur
• Afficher uniquement les contrat en rouge
– Effacer tous les filtres
– Faire des sous-totaux avec le mode plan
• Somme des montants par produit
• Nombre des contrats par conseiller