Excel : LES TABLEAUX CROISÉS DYNAMIQUES, corrigé 2/2
Exercices EXCEL, corrections & plus...
Jean-Marc Stoeffler © maj :2013
la vidéo ici ! ... énoncé ...corrigé page1 base de test 2007->


liens commerciaux :


à importerFfichier de départ (àimporter et à enrergistrer avec F12) ou [bouton droit] >"enregistrer la cible"  [comment faire [1] corrigé 1 Fcorrigé 1ancien - (sans la pyramide)  [2 la pyramide d'âges] corrigé 2 (revu ) + nouveauFfichier corrigé avec mode d'emploi et une belle pyramide pyramide d'âges de Sucy>F
voir plus d'explications revu en juillet 2006 avec en +
pour éviter le plantage de mozilla ->corrigé 1ancien - (sans la pyramide)

Mise à jour le 11 novembre 2013 :

La pyramide d'âges avec Excel 2007-2010-2013


Excel 2007-2010 - Pyramide d'âge par jeanmarc_stoeffler



nouveau la pyramide d'âges : le film !!! (new : juillet 2006) (6 Mo, patience pour le chargement !)

fichier avant le film le film (lecteur Windows Média 9 mn 6Mo) le fichier après


mai 2005 - ce fichier Excel va contenir toutes les explications pour créer une pyramide d'âges :
fichier corrigé avec mode d'emploi et une belle pyramide (ébauche du 22 mai 2005) (à suivre...)


-9-

étape 1 : se placer dans n'importe quelle cellule de la base de donnée et lancer "tableau croisé" pour connaître le nombre de salariés par tranche d'âges.

[schéma étape 3/4]

résultat :

NB NOM sexe

 

 

tranche d'âges femme homme Total

1

2

1

3

2

9

10

19

3

32

17

49

4

14

12

26

5

9

5

14

6

39

19

58

7

35

23

58

8

21

9

30

9

16

8

24

10

3

 

3

Total

180

104

284

Remarque : ce tableau ne doit pas être modifié car il est "vivant" (dynamique) :  il doit se recalculer à chaque mise à jour de la base de données. Par contre, il n'est pas très esthétique, et les tranche d'âges ne sont pas parlantes.

Remarque 2 : les tranches d'âges peuvent être calculées avec cette formule :  =ENT(J2/5)-2 où J2 représente l'âge mais désormais:
il n'est plus utile d'avoir une colonne tranche d'âge. Il suffit de grouper les âges du tableau dynamique :
bouton droit sur les âges > grouper

étape 2 : pour créer un tableau utilisable pour la pyramide, avec des ordonnées littérales en colonne F il faut transposer les données sur un tableau parallèle, avec une liaison simple (c-a-d, sans utiliser le copier-coller) car, à la mise-à-jour du tableau croisé, les données doivent se déverser automatiquement dans notre tableau (colonnes G et H).
Noter le signe "-" de la formule en colonneG qui permettra aux données des femmes d'aller sur la gauche de notre graphique, alors que les données des hommes iront sur la partie droite du graphique (toute l'astuce de la pyramide d'âge avec Excel est dans ce signe "-" !!)

cliquez ici pour voir plus d'explications (revu le 6 juillet 2006) (mozilla s'abstenir...)
.
...dans ce deuxième tableau, les données des femmes sont positives à l'écran, mais négatives pour les besoins de notre graphique (les femmes seront à gauche de la pyramide, et les hommes à droite). L'artifice pour faire disparaître le signe "-" est le format spécial qu'il faut créer de la façon suivantes :
Format de cellule-> Nombre -> personnalisé puis 0;0 (voir le dessin)

remarque : le format personnalisé permet de donner des formats différents pour les nombres positifs et les nombres négatifs. Et éventuellement pour les nombre nuls...
"
positif ; négatif ; nul" voir dans l'exo 3, les nombres positifs d'une certaine couleur, et les nombres négatifs d'une autre)

> > > > > >cliquez ici pour voir plus d'explications (navigateur Internet Explorer recommandé...) < < < < < <

étape 3 : le graphique.

a) sélection des données : bien incorporer les valeurs littérales de la colonne 6.

b) lancer l'assistant graphique :

Suivant, Suivant, Suivant ... résultat brut :

c) la mise au point :

- pour équilibrer les deux courbes hommes/femmes : bouton droit de la souris sur l'axe du bas (axe des ordonnées, dans ce type de graphique et non pas abscisse car le graphique a pivoté de 90°)

- pour donner de l'epaissseur et mettre les données face à face, bouton droit de la souris :

(attention sur Excel 5 c'est "Format de groupe barre" qu'il faut trouver...)

- superposition 100% - largeur = 0.

- résultat final :

et après 10 minutes d'un effort gratifiant ...(voir la vidéo pour tout comprendre !) :


remarque
: la tendance actuelle, pour les graphiques en pyramide, est de placer les hommes à gauche et les femmes à droite (inversion par rapport à mon graphique).
Dans ce cas, c'est à la colonne des hommes qu'il conviendra de mettre un signe négatif et c'est à toutes les valeurs du tableau qu'il faudra appliquer le format 0;0 car le format des nombres de l'axe du bas de ce graphique est pris dans la colonne des femmes... 


liens commerciaux :


Illustration de cette technique : la pyramide des âges de la ville de Sucy-en-Brie (94) en 1999 que j'ai réalisée pour le livre : Histoire de Sucy Tome IV de la S.H.A.S. :


ouvrir le fichier Excel correspondant 


étape 4 : améliorations (mais attention, "le mieux est l'ennemi du bien..." => faites des enregistrements réguliers de votre travail)

attention : 
cette étape ayant besoin d'être expliquée de vive voix, je recommande de passer directement à l'exercice 10.

ajout d'un "tampon" qui se met en superposition sur le tableau pour lancer une alerte visuelle lorsque les données de la base de données ont été modifiées !

le tampon est constitué d'un objet texte qui n'a plus de bordure et plus de couleur de fond. Par contre la couleur d'écriture est rouge avec du "gras 20". Au lieu du texte, il y a une formule (que l'on saisit dans la barre de formule) de type "= E17" (ici = L17C5 car en est en style de référence L1C1 et non pas A1 qui s'obtient avec outil-option-général-L1C1)

revenir à la fenêtre précédente :
retour à la page précedemment visitée

la zone texte "tampon incrusté" créé avec l'outil texte de la barre d'outil dessin, a des propriétés particulières de transparence. L'astuce est de créer un lien vers le contenu d'une cellule: au lieu d'écrire dans la zone texte, il faut écrire dans la Barre de formule "= <référence>". (J'ai mis des mois à mettre au point cette astuce !) et ensuite avec format cellule, on agrandit la taille des caractères.

résultat de l'incrustation :

tableau croisé synchrone avec la base de données tableau croisé non synchrone avec la base de données (nb de fiches différent de 284)

- voir le fichier Excel (97) que j'ai incorporé dans mon ZIP [exemplePyramide.xls]


- un bouton, couplé à une macro, dont l'apparence se modifie lorsque la base de donnée est modifiée et qui invite à mettre à jour le tableau croisé ( à venir)


- exo 10 -

personne SITE        
PIECE Lille Nice Paris Strasbourg Total
inconnu   7 5 1 13
pièce 104     2   2
pièce 105     1   1
pièce 107   2 1   3
pièce 109   3     3
pièce 110   2 1   3
pièce 115   4     4
pièce 118   2 2   4

etc... etc...

pièce 69     1   1
pièce 70 1 3     4
pièce S R   1     1
pièce S/S     1 1 2
pièce SEC     1   1
Total 3 171 98 12 284
           
  Lille Nice Paris Strasbourg  
moyenne= 1,00 2,67 1,92 1,09  
attention : la moyenne ne peut se calculer facilement qu'à partir d'excel 97 car les cellules avec la valeur "0" sont vides. La formule "moyenne(...)" fonctionne bien car elle ne prend donc en compte que les valeurs différentes de zéro.


- exo 11 -

Trier par numéro de téléphone et regarder ceux qui sont attribués à plusieurs personnes dans le même site mais dans des pièces différentes : il doit y avoir une erreur ! (exemple, le 3733)


- exo 12 -

Les tableaux qui doivent apparaître dans word doivent être copiés dans Excel et collés "avec liaison" dans le document Word


- exo 13 -

Il faut placer une ligne au milieu de la base de données, par insertion, avant la dernière ligne.
Attention, ne pas rajouter à la fin, car la base de données a été séléctionnée au lancement de la procédure avec 285 lignes.


accueil

énoncé- corrigé page1

depuis le 5/11/2006->
Créé par Jean-Marc Stoeffler pour le DTS-RH98 - La Sorbonne