Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Apprentissage d’Excel 2013-2016 Olivier BOCQUET DUOcfi Page 1 sur 6 Les tableau

Apprentissage d’Excel 2013-2016 Olivier BOCQUET DUOcfi Page 1 sur 6 Les tableaux croisés dynamiques Dans ce cours, nous allons voir : 1. OBJECTIFS 2. QU’EST-CE QU’UN TABLEAU CROISE DYNAMIQUE 3. REPERES ET VOCABULAIRE LIES AUX TABLEAUX CROISES DYNAMIQUES DANS EXCEL 4. INSERTION D’UN TABLEAU CROISE DYNAMIQUE 5. DISPOSITION DU RAPPORT 6. FILTRER LES CHAMPS 7. TRIS DES DONNEES ET TOP 10 8. REGROUPEMENTS DE DONNEES 9. AUTRES FONCTIONS DE SYNTHESE 10. AUTRE VUE DES DONNEES A L'AIDE D'UN CALCUL PERSONNALISE 1. Objectifs Les tableaux croisés dynamiques sont des outils d'analyse très performants. Ils permettent de comparer et de calculer par des simples clics toutes sortes d'éléments issus d’une base de données. Apprentissage d’Excel 2013-2016 Olivier BOCQUET DUOcfi Page 2 sur 6 2. Qu’est-ce qu’un tableau croisé dynamique C’est un tableau à double entrée, dont chaque ligne et chaque colonne représente une information homogène. A l’intersection d’une colonne et d’une ligne se trouve une valeur significative. Il est dynamique car il est élaboré à partir d’informations présentées sous forme de liste et les résultats présents dans le tableau évoluent en même temps que la liste. Voici des contre-exemples : Certaines lignes et colonnes n’ont pas d’en-tête. A l’intersection des lignes et des colonnes se trouvent des valeurs fusionnées. 3. Repères et vocabulaire liés aux tableaux croisés dynamiques dans Excel Nous allons nous intéresser aux tableaux croisés dynamiques construits à partir d’une base de données présentée sous forme de liste à entête. Le vocabulaire relatif à la feuille de calcul : ▪ Champ : Correspond aux informations contenues en colonne Apprentissage d’Excel 2013 2016 Page 3 sur 6 ▪ Enregistrement : Correspond aux informations contenues en ligne La base de données doit respecter quelques recommandations pour pouvoir être exploitée au sein d’un tableau croisé dynamique : ▪ La première ligne doit contenir les étiquettes des colonnes : ces en-têtes de colonnes seront convertis en noms de champs ▪ Chaque colonne ne doit contenir qu'un seul type de données homogènes ▪ Éviter d’utiliser des listes contenant déjà des sous-totaux automatiques. ▪ La plage de données utilisée dans le rapport ne doit contenir aucune ligne ou colonne vide Toute élaboration d’un tableau croisé dynamique commence par la conception qui nécessite de se poser les bonnes questions : ▪ Que souhaite-t-on analyser ? o Quels champs vais-je poser en ligne ? o Quels champs vais-je poser en colonne ? o Quels champs vais-je poser en page ? o Quelles valeurs vais-je analyser ? 4. Insertion d’un tableau croisé dynamique Pour insérer un tableau croisé dynamique, il faut se positionner dans la base de données et activer le menu Insertion/Tableau croisé dynamique La fenêtre suivante s’ouvre. L'Assistant génère alors le tableau croisé dynamique dans une nouvelle feuille de calcul, et affiche la liste des champs disponibles dans Liste de champs de tableau croisé dynamique. Apprentissage d’Excel 2013 2016 Page 4 sur 6 5. Disposition du rapport La disposition est une zone divisée en zones de dépôt distinctes, dans lesquelles nous déposerons les champs que nous ferons glisser à partir de la liste de champs. La disposition finale constituera le rapport de tableau croisé dynamique. Les quatre zones de dépôt de la disposition sont : ▪ La zone de dépôt des champs de ligne : reçoit les données verticalement, un élément par ligne ▪ La zone de dépôt des champs de colonne : reçoit les données horizontalement, un élément par colonne ▪ La zone de dépôt des champs de page regroupe et affiche les données comme si elles se trouvaient sur des pages distinctes, de façon à pouvoir isoler (filtrer) les informations à visualiser ▪ La zone de dépôt des données reçoit et synthétise les données, qui sont le plus souvent de type numérique Il n'est pas obligatoire d'utiliser toutes les zones de dépôt. Chaque zone de dépôt peut accueillir plusieurs champs. Chaque colonne des données source est convertie en un champ portant le même nom. Pour créer une vue de vos données en forme de tableau croisé dynamique, il suffit de faire glisser les champs de votre choix de la liste de champs et de les déposer dans une des zones de dépôt. Pour choisir dans quel sens déposer les champs dans les zones de dépôt des champs de ligne et champs de colonne, nous positionnerons en ligne le champ qui contient le plus d’occurrences et en colonne le champ qui en contient le moins. Cette logique suit le dimensionnement des feuilles Excel 2003. (256 colonnes / 65 536 lignes). Dès la création d’un tableau croisé dynamique, on dispose d’un menu contextuel qui permet d’accéder à des actions de paramétrage du tableau croisé dynamique. Apprentissage d’Excel 2013 2016 Page 5 sur 6 Entrainement : Construire les 5 premiers tableaux croisés dynamiques de l’exercice TCD_sites. A chaque nouvelle consigne, vous disposerez le tableau croisé dynamique dans une nouvelle feuille Excel que vous renommerez par le numéro de la consigne. 6. Filtrer les champs Derrière la flèche qui suit chaque étiquette de champ, nous avons la possibilité de filtrer les données pour ne voir que certaines occurrences. 7. Tris des données et TOP 10 Le tri des données peut se faire sur les champs de lignes, de colonnes ou de données, en ordre croissant ou décroissant. Il est aussi possible de n’afficher que les n premières occurrences de la sélection grâce à l’option TOP 10. Cette option est accessible par un clic droit sur le champ concerné puis « Trier » ou « Filtrer ». 8. Regroupements de données Les regroupements permettent d'afficher des données récapitulatives avec un niveau de détail moindre (par exemple, des données trimestrielles, et non plus journalières). Le regroupement se fera par tranche (à définir) lorsque les données sur lesquelles sera basé le regroupement sont numériques. Le regroupement se fera par période (à définir) lorsque les données sur lesquelles sera basé le regroupement sont des dates. ▪ Cliquer avec le bouton droit sur le champ dont on veut regrouper les données. ▪ Cliquer sur Grouper et afficher le détail / Grouper. Pour dissocier des données, cliquer avec le bouton droit sur le champ, puis pointer sur Grouper et afficher le détail / Dissocier. 9. Autres fonctions de synthèse Il est possible de déposer plusieurs champs dans la zone de dépôt Champ de données. Le plus souvent la première synthèse sera une somme (option par défaut retenue pour les champs numériques) ou un dénombrement (option par défaut retenue pour les champs texte). L’ajout d’un champ de données répond au besoin d’une autre fonction de synthèse à choisir parmi les fonctions suivantes : Somme Affiche la somme de toutes les valeurs de ce champ. Nbval Affiche le nombre d'enregistrements dans cette catégorie. Moyenne Affiche la moyenne de toutes les valeurs de ce champ. Max Affiche la plus grande valeur du champ. Min Affiche la plus petite valeur du champ. Produit Affiche la multiplication de toutes les valeurs du champ. Apprentissage d’Excel 2013 2016 Page 6 sur 6 Nb Affiche le nombre de valeur numériques dans cette catégorie. Si l’on souhaite une synthèse différente sur un même champ (somme et moyenne par exemple), il suffit de le déposer deux fois dans la zone de dépôt Champ de données puis de modifier les paramètres de champs d’un des deux champs. Ces options sont accessibles par un clic droit sur la valeur à modifier puis « Synthèse des valeurs par ». Entrainement : Dérouler les consignes 6 à 9 de l’exercice TCD_sites. A chaque nouvelle consigne, vous disposerez le tableau croisé dynamique dans une nouvelle feuille Excel. 10. Autre vue des données à l'aide d'un calcul personnalisé Il est possible d‘afficher les données de synthèse non pas sous la forme d’une fonction simple mais sous la forme d’une vue de synthèse telle que : ▪ Résultats cumulés ▪ % par ligne ▪ % par colonne ▪ % du total Ces options sont accessibles par un clic droit sur la valeur à modifier puis « Afficher les valeurs ». Entrainement : Dérouler les consignes 10 à 12 de l’exercice TCD_sites dans une nouvelle feuille Excel. uploads/Management/ 42-excel-tcd-support.pdf