Excel : LES TABLEAUX CROISÉS DYNAMIQUES, l'énoncé
Exercices EXCEL, corrections & plus...
Jean-Marc Stoeffler © maj : 8 mai 2007

 

introduction

Le Tableau Croisé Dynamique (TCD) est l'outil d'Excel le plus impressionnant mais également un des plus méconnus car il est délicat à mettre en oeuvre.
Lorsqu'on se risque - par hasard - à utiliser l'assistant Données> rapport de TCD...., on échoue généralement car cet outil est prévu uniquement pour la création de tableaux de synthèse à partir d'une base de données, c'est-à-dire à partir d'une feuille avec des lignes d'enregistrements successifs et une première ligne d'intitulés de colonne (sans fusion).Un seul type de données par colonne est nécessaire.

Dans ces exercices nous allons partir d'une base de données et en faire différentes analyses :
- soit la base utilisée ici (2004)->
- soit la nouvelle base, qui sera bientôt la nouvelle référence en 2007-> et qui servira pour les nouvelles vidéos.

Cette base (fictive) représente une entreprise de 284 personnes réparties sur 4 sites. Les âges s'étendent de 18 à 62 ans. (ici l'ancien lien de téléchargement)

remarque : la nouvelle base comprend 285 salariés.

1ère partie : mise en forme et filtres

a

mettre en forme ce fichier :
- première ligne et deux premières colonnes figées (Fenêtre> figer les volets)
- première colonne en gras
- fond des deux premières colonnes en Vert clair
- intitulés des colonnes (A à J) en gras, fond en Bleu clair

(vous cliquerez {précédent}, pour revenir à cette page) fichier (corrigé) >>

b

en utilisant Données>filtre>filtre automatique et la calculatrice cachée (voir trucs et astuces N°19) montrer combien de femmes travaillent à Strasbourg

idem>>
c idem : montrer tous les salaires de l'entreprise supérieurs à 3000 euros, et les mettre en gras/rouge
d

préparer l'aperçu avant impression, de telle sorte que :
- l'impression en mode portrait ne dépasse pas la largeur d'une page A4
- le nombre de pages en hauteur soit automatiquement calculé par Excel
- que la date d'impression apparaisse en bas de page, ainsi que le numéro de la page courante / nombre total de pages
- la première ligne soit répetée en haut de chaque page imprimée.
Pour les exercices qui suivent, nous ferons apparaître toutes les données : Données>filtre>afficher tout

2ème partie : les TCD

Nous créerons, avec TCD, des tranches d'âges de 5 années de 20 ans à 60 ans.A partir de cette base de données, avec l'assistant Données> Rapport de tableau croisé dynamique" faire, dans l'ordre, et dans des feuilles séparées, qui se généreront automatiquement, et qui seront nommées A, B, C, etc...):

1 Etablir la répartition du personnel par site
2 Etablir la répartition du personnel par site avec la distinction homme/femme
3 Etablir la répartition de la somme des rémunérations par site (tableau, par site, des virements mensuels que doit effectuer le comptable).
4 Etablir la répartition des rémunérations par site avec leur somme et leur moyenne avec distinction homme/femme.
5 

Calculer la moyenne des rémunérations par tranches d'âges avec distinction homme/femme
(utiliser la fonction "grouper" sur les âges : de 20 à 60 ans par pas de 5 années
6  à venir...
7  à venir...
8  pour chaque sexe et par site trouver : le nombre de personnes, l'âge minimum, la moyenne des âges, l'âge maximum.
9 établir le tableau de données qui permettra ensuite d'établir la pyramide d'âges sous forme graphique. Créer cette pyramide. 
10  calculer le nombre de personnes par pièce pour chaque site - en déduire par site, la moyenne des personnes par pièce et le nombre de pièces utilisées.
11  montrer qu'au niveau de la numérotation téléphonique il existe un certain nombre d'incohérences.
12  créer sous Word un document récapitulatif avec les tableaux des exercices 2, 4, 5, 8 et 9. Les tableaux devront être liés avec les données d'Excel.
13 tester cette liaison en ajoutant une personne à Lille et en vérifiant que les modifications se répercutent bien dans le document Word : par exemple MICHEL Louise; CFS FSC; pièce 50 ; 1 225  ;18/4/52

voir le résultat expliqué(corrigé)
il n'y a pas qu'Excel dans la vie... (cliquez ici)Créé par Jean-Marc Stoeffler pour le DTS-RH98 en 1998  - remanié profondément en 2004 -La Sorbonne - http://perso.wanadoo.fr/jeanmarc.stoeffler/excel pour m'écrire par mail... depuis le 5/11/2006->