alphorm.com formation excel 2013 tcd

Présentation de la formation

Microsoft Excel 2013

Excel 2013, Les tableaux croisés dynamiques alphorm.com™©

Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum

Vincent ISOZFormateur et Consultant indépendant Ingénierie statistique et financière

Microsoft Excel 2013Tableaux croisés dynamiques

Plan• Introduction• Présentation du concept et préparation des données• Manipulation des champs• Filtres et Segments• Groupements• Totaux et sous-totaux


• Totaux et sous-totaux• Champs et éléments calculés• Analyses comparatives• Analyses particulières et divers• Options d'actualisation• VBA et les tableaux croisés dynamiques• Conclusion

Présentation du formateur• Vincent ISOZ

• [email protected]

• Consultant scientifique

• Mission conseil, migration et formation

• Bsc. Physique


• Mes références :

� Mon profil Alphorm : http://www.alphorm.com/auteur/vincent-isoz

� Mon site perso: http://www.sciences.ch

Mes formations Scientifiques sur alphorm• Formation Les fondamentaux de Matlab 2013A

• L'objectif de cette formation sur les fondamentaux, qui s'adresse principalement à des ingénieurs juniors est d'introduire l'environnement, la philosophie et les commandes du noyau de base de MATLAB – donc sans aucune Toolbox! - pour que le praticien puisse développer par la suite des scripts spécialisés et efficients par rapport à son domaine d'activité (physique des matériaux, thermodynamique, mécanique, finance quantitative,


matériaux, thermodynamique, mécanique, finance quantitative, statistique, biostatistique, fouille de données, simulations, etc.).

• Une fois les notions de cette formation sur les fondamentaux acquises et pratiquées, l'ingénieur pourra faire l'acquisition éventuelle de Toolbox supplémentaires et suivre les formations spécialisées y relatives, qui apparaîtront sur Alphorm.

Mes formations Scientifiques sur alphorm• Édition Scientifique avec LaTeX

• L'objectif de cette formation, s'adressant principalement à des étudiants ou des ingénieurs R&D en emploi, est d'introduire: l'environnement, la philosophie et les commandes majeures de LaTeX pour que le praticien puisse rédiger par la suite des articles, mémoires ou thèses par rapport à son domaine d'activité (mathématique, physique, sciences sociales, finance, etc.).


Remarques• Objectifs (pourquoi cette formation?)

• Prérequis (matériel et connaissances)

• Audience (public cible)

• Références bibliographiques + Liens Internet

• Hypothèses


• Hypothèses

• Validation des acquis (certifications)

• Support post formation

• Avertissements

• Et après…?

Références bibliographiques


Liens internet• Google/YouTube

• http://www.excel-downloads.com

• http://www.mrexcel.com


Are you ready?


Présentation du concept et préparation des données

Définition et contexte des




Définition et contexte des tableaux croisés dynamiques

Plan• Utilité des TCD

• Types de TCD

• Limites des TCD

• Limites des listes et tables Excel


• Ne pas penser toujours que TCD!

• TCD vs PowerPivot

Ce qu’on a couvert• Quelques définitions pour la culture générale

• Biais d'utilisation des TCD



Préparation d'une liste




Préparation d'une liste ou d'une table interne

Plan• Format de fichier

• Nettoyage et contrôle du contenu d'une liste

� Retours à la ligne

� Caractères spéciaux

Nombres en tant que textes


� Nombres en tant que textes

� Cellules fusionnées

� Lignes vides

• Conversion d'une liste en une table et avantages y relatifs

• Nommer la table correctement

Ce qu’on a couvertQuelques bonne pratiques quant à la préparation des données à analyser



Découverte de l'assistant




Découverte de l'assistant TCD

Plan• Vérifier le type de fichier (*.xls, *.xlsb ou *.xlsx)

• Découvrir l'assistant TCD

• Déplacer le TCD dans un fichier autre que celui contenant la source

• Apprendre à renommer la source ou la changer à tout moment


• Nommer correctement son TCD

Ce qu’on a couvertL'assistant tableau croisé dynamique et quelques paramétrages fondamentaux correspondants.



Récupérer l'ancien




Récupérer l'ancien assistant TCD

Plan• Ajout de l'ancien assistant TCD à la barre d'accès rapide

• Définir les options importantes de l'ancien assistant qui seront utiles plus tard


Ce qu’on a couvertQu'il existe un autre assistant TCD qui est "caché" et comportant des options majeures!


Remettre l'ancien visuel





Remettre l'ancien visuel des TCD

Plan• Remettre l'ancien visuel des TCD pour ceux qui préféraient la façon de

travailler d'avant MS Excel 2007.


Ce qu’on a couvertUne option très personnelle et donc subjective quant à la méthode de travail.


Fonctionnalités réservées





Fonctionnalités réservées pour les cubes OLAP et

PowerPivot

Plan• Passage en revue des fonctionnalités grisées car fonctionnelles

uniquement pour les sources OLAP ou les PowerPivot

� Monter/Descendre hiérarchie

� Options "avancées"

� Jeux


� Jeux

� Outils OLAP

Ce qu’on a couvertLes fonctionnalités grisées, la raison de leur désactivation et la définition théorique de leur utilité.


Activation et désactivation

Manipulation des champs




Activation et désactivation du panneau des champs

Plan• Comment récupérer le panneau (volet) des champs

• Comment personnaliser le visuel

• Définition des 4 zones

• Explication de la mise à jour différée


Ce qu’on a couvertUne compréhension simple et théorique du panneau des champs pour son usage approfondi par la suite.


Utilisation de la zone





Utilisation de la zone Étiquettes de lignes

Plan• Démonstration de l'effet de la zone d'Étiquettes de lignes

• Renommer l'intitulé de la zone de lignes

• Utilité de cette zone seule pour des listing d'unicité à un niveau

• Utilité de cette zone seule pour des listing d'unicité à plusieurs niveaux (la gestion du tri, des groupements et filtres étant traitée plus tard)


(la gestion du tri, des groupements et filtres étant traitée plus tard)

• Choisir l'aspect visuel des lignes

• Effet du double clic à un ou plusieurs niveaux

• Homogénéiser l'aspect des groupes

Ce qu’on a couvertL'utilité que peut avoir la zone d'Étiquettes de lignes lorsque utilisée seule.







Utilisation de la zone Étiquettes de colonnes

Plan• Démonstration de l'effet de la zone d'étiquettes de colonnes seule

• Renommer l'intitulé de la zone de colonnes

• Utilité de cette zone conjointement avec cette de lignes pour faire des matrice d'entrée

• Construction d'un listing (agrégation) de colonnes multiniveaux


• Construction d'un listing (agrégation) de colonnes multiniveaux

• Choisir l'aspect visuel des colonnes

• Effet du double clic à un ou plusieurs niveaux

• Homogénéiser l'aspect des groupes

Ce qu’on a couvertL'utilité que peut avoir la zone d'Étiquettes de colonnes conjointement avec la zone de lignes.





Utilisation de la zone de valeurs



Plan• Découverte de la zone de valeurs et limites calculatoires

• Renommer l'intitulé de la statistique choisie

• Ajout de statistiques multiples

• Gérer les erreurs et valeurs vides


• Méthode recommandée de formatage des nombres

• Découverte de l'effet au double clic sur une valeur

Ce qu’on a couvertUne première approche ("mise en bouche") de la zone de valeur des TCD


Options de formatage





Options de formatage automatique des TCD

Plan• Options de disposition

• Options de grilles des TCD

• Options d'alignement des champs

• Options de styles de couleurs automatiques


Ce qu’on a couvertUne fois que nous avons des étiquettes de lignes, de colonnes et des valeurs il peut être pertinent d'utiliser les formatages rapides (styles).


Utilisation de la zone Filtre





Utilisation de la zone Filtre du rapport

Plan• Découverte de la zone Filtre du rapport

• Définir l'option de choix multiple

• Contrôler la disposition des Filtres du rapport


Ce qu’on a couvertUn premier aspect des Filtres du rapport qui seront utilisés plus tard pour générer automatiquement une série de rapports de TCD.


Simuler une requête





Simuler une requête de sélection

PlanJouer avec les TCD pour obtenir un résultat similaire à une requête de sélection


Ce qu’on a couvertUne technique souvent utilisée par les fans du RECHERCHEV et qui ne connaissent pas les PowerPivot.


Tris élémentaires et

Tris et filtres




Tris élémentaires et options de tris avec listes

personnalisées

Plan• Rappel sur le tri des champs

• Ordonner manuellement les éléments

• Tris simples sur étiquettes de lignes ou colonnes de niveau supérieur

• Tris sur étiquettes de lignes ou colonnes avec listes personnalisées


• Définir dans les options des TCD la détection automatique des listes personnalisées

Ce qu’on a couvertLe fait que nous retrouvons dans un premier temps les techniques de tri de listes classiques


Tris sur sous-ensembles

Tris et filtres




Tris sur sous-ensembles

Plan• Tris sur des champs de lignes ou colonnes de niveau inférieur

• Tris sur les champs de valeurs


Ce qu’on a couvertUne aspect des tris qui n'existe pas dans les tables et listes classiques de Microsoft Excel.


Filtres par/sur étiquettes

Tris et filtres




Filtres par/sur étiquettes

Plan• Filtre sur les étiquettes de lignes ou colonnes de premier niveau

• Filtre sur les étiquettes de lignes ou colonnes de sous niveaux

• Particularité des filtres de données de types dates

• Effacer rapidement tous les filtres


• Paramétrages particuliers des filtres des TCD

• Piège des filtres lors de la mise à jour des TCD

Ce qu’on a couvertUne aspect des filtres qui existe aussi dans les listes et tables classiques de Microsoft Excel mais avec quelques options particulières propres aux TCD.


Filtres par valeurs

Tris et filtres




Filtres par valeurs

Plan• Filtrer par Valeur (type de statistique)

• Effacer rapidement tous les filtres

• Rappel sur le piège des filtres multiples


Ce qu’on a couvertS'il était possible de filtrer aussi par valeur et non que pour les champs et… le petit piège habituel…


Filtres du rapport

Tris et filtres




Filtres du rapport

Plan• Rappels sur les Filtres de rapport

• Utilisation des Filtres de rapport pour générer une série de TCD


Ce qu’on a couvertQue les Filtres du rapport ne servent pas uniquement à filtrer!


Segments

Tris et filtres




Segments

Plan• Utilité des segments

• Insertion d'un segment d'un champ ordinal/nominal

• Insertion d'un segment d'un champ temporel (chronologique)

• Liaison de plusieurs TCD à un segment


• Personnalisation visuelle des segments

• Limite des segments au niveau de la protection des feuilles

Ce qu’on a couvertUn outil de filtrage de plus en plus utilisé et particulièrement dans les tableaux de bord SharePoint.


Création de groupes

Groupements




Création de groupes manuels sur catégories

Plan• Création/Destruction de groupes créés manuellement sur les étiquettes

de ligne ou colonnes (non temporels!)

• Changer les appartenances à des groupes

• Groupes et filtres/segments?


Ce qu’on a couvertLe groupement de données nominales/ordinales tout simplement…


Création de groupes Groupements




Création de groupes sur le temps

Plan• Création/Destruction de groupes créés sur les étiquettes de ligne ou

colonnes temporelles

• Problèmes des semestres

• Ouvrir/Fermer rapidement les groupes


Ce qu’on a couvertLe groupement de données temporelles avec découverte des limites inhérentes.


Conflits de groupements

Groupements




Conflits de groupements entre TCD

Plan• Découverte du problème de groupement générant des conflits entre

TCD

• Résolution du problème de conflits de groupements


Ce qu’on a couvertUne message d'erreur classique très rarement traité dans la littérature spécialisée.


Développer/Réduire Groupements




Développer/Réduire des groupes

Plan• Afficher/Masquer les symboles de groupes

• Comprendre l'effet du double clic sur les groupes

• Contrôler les groupements globalement

• Rappel sur le blocage du double clic sur les valeurs


Ce qu’on a couvertLe contrôle rapide de l'affichage visuel des groupements


Gérer les grands Totaux et Sous-Totaux




Gérer les grands totaux

Plan• Afficher/Masquer les grands totaux

• Liaison avec les sous-totaux?

• Choix du type de grand total?


Ce qu’on a couvertLes options et limites relatives aux grands totaux


Totaux et Sous-Totaux

Gérer les sous-totaux




Gérer les sous-totaux

Plan• Afficher/Masquer les sous-totaux

• Choisir la position des sous-totaux

• Particularité des sous-totaux des colonnes par rapport aux lignes

• Biais d'interprétation des sous-totaux


Ce qu’on a couvertLes options et limites relatives aux sous-totaux


Champs et éléments calculés

Créer des champs




Créer des champs calculés

Plan• Utilité/Limites des champs calculés

• Création d'un champ calculé

• Mise à jour d'un champ calculé

• Masquer un champ calculé


• Suppression d'un champ calculé

Ce qu’on a couvertLa création de champs calculés dans le but de ne pas avoir à modifier la source.


Champs et éléments calculés

Créer des éléments




Créer des éléments calculés

Plan• Élément calculé VS champ calculé et limites

• Création/Mise à jour/Suppression d'un élément calculé

• Contrôler l'ordre de calcul des éléments calculés

• Générer un rapport des champs et éléments calculés


Ce qu’on a couvertLa création d'éléments calculés ne nécessitant pas de modifier la source.


Formules avec cellules Champs et éléments calculés




Formules avec cellules de TCD

Plan• Découverte du comportement par défaut du tableur lorsqu'une formule

se base sur la cellule d'un TCD

• Choix du comportement par défaut

• Avantages/Désavantages de chacune des méthodes

• Dangers lors de la mise à jour du TCD


• Dangers lors de la mise à jour du TCD

Ce qu’on a couvertComment choisir au mieux le type de référence aux cellules d'un TCD pour des formules et danger y relatif.


Options d'analyses comparativesExcel 95 à 2007

Analyses comparatives





PlanDécouverte des options d'analyse comparatives existantes depuis Microsoft Excel 95:

• %Column

• %Ligne

• %Total


• %Total

• Différence

• %Différence de

• %De

• …

Ce qu’on a couvertLes options d'analyse comparatives existantes depuis Microsoft Excel 95…


Analyses comparativesOptions d'analyses comparatives

Excel 2010 à 2013





PlanDécouverte des options d'analyse comparatives existantes depuis Microsoft Excel 2010:

• % Du total de la ligne parente

• % Du total de la colonne parente

• % Du total parent


• % Du total parent

• % résultat cumulé par

• Rang…

Ce qu’on a couvertLes options d'analyse comparatives existantes depuis Microsoft Excel 2010…


Analyses particulières et divers

Particularités du formatage




Particularités du formatage conditionnel sur les TCD

Plan• Application du formatage conditionnel avant l'ajout de niveaux

multiples

• Application du formatage conditionnel après l'ajout de niveaux multiples

• … et les sparklines???


Ce qu’on a couvertDifférentes manières d'appliquer le formatage conditionnel et constat que l'assistant correspondant détecte correctement la présence d'un TCD. Par contre pour les sparklines ce n'est pas encore cela…



Présentation des graphiques




Présentation des graphiques croisés dynamiques

Plan• Création d'un GCD

• Comprendre la liaison entre un GCD et son TCD

• Segments et GCD

• Options visuelles importantes des GCD (pour l'impression)


• Problème des GCD avec les filtres et couleurs

• Un GCD seul sans TCD?

Ce qu’on a couvertLes particularités des graphiques MS Excel lorsque liés à des TCD



Consolidation de plages de feuilles de calcul avec étiquettes




Consolidation de plages de feuilles de calcul avec étiquettes

Plan• Distinguer la notion de consolidation de celle de liaison multitables des

PowerPivot

• Découverte de l'option de consolidation de l'ancien assistant tableau croisé dynamique


Ce qu’on a couvertLa consolidation de plages de feuilles de calcul avec étiquettes et sa contrainte majeure (relativement aux attentes des utilisateurs qui confondent avec ce que font les PowerPivot).



Options d'impression




Options d'impression

Plan• Ajouter des lignes vides automatiquement

• Ajouter des sauts de page automatiquement

• Faire en sorte que la zone de titre se retrouve sur chaque page imprimée

• Rappels sur les rapports des champs de page (filtres)


• Rappels sur les rapports des champs de page (filtres)

Ce qu’on a couvertQuelques options d'impression souvent demandées par ceux qui impriment au format papier ou électronique.


Options de protectionAnalyses particulières et divers




Options de protection

Plan• Options de protection des TCD et questions fréquentes y relatives.


Ce qu’on a couvertOptions de protection des TCD et questions fréquentes y relatives et un rappel sur les segments.


Options d'actualisation





Options d'actualisation avec une table locale

Plan• Actualisation manuelle d'un unique TCD avec source locale

• Actualisation manuelle de multiples TCD avec source locale

• Automatisation temporelle automatique de TCD avec source locale?

• Forcer l'actualisation à l'ouverture du fichier


Ce qu’on a couvertLes options d'actualisation (mise à jour) des TCD/GCD lorsque les données sont localement stockées.







Options d'actualisation avec un SGBDR

Plan• Actualisation manuelle d'un unique TCD avec source SGBDR

• Actualisation manuelle de multiples TCD avec source SGBDR

• Automatisation temporelle automatique de TCD avec source SGBDR?

• Forcer l'actualisation à l'ouverture du fichier


Ce qu’on a couvertLes options d'actualisation (mise à jour) des TCD/GCD lorsque les données sont stockées dans une SGBDR.


alphorm.com formation excel 2013 tcd

Technology