M2104 Environnement Informatique

DUT GEA 1ère année
M2104
Environnement
Informatique
Partie 2 : Listes et tableaux croisés dynamiques
Lionel Paris ([email protected]) (d’après M. Viguier)
2013-2014
M2104 Environnement Informatique
2013-2014
LES LISTES DE DONNEES SOUS EXCEL
1 TABLE DES MATIERES
2
Préambule ........................................................................................................................... 2
3
Le Filtrage des données ...................................................................................................... 4
3.1
Filtre automatique ........................................................................................................ 4
3.2
Filtre Automatique Personnalisé ................................................................................. 4
3.3
Filtres élaborés ............................................................................................................. 5
3.3.1
Le critère de comparaison ............................................................................................... 7
3.3.2
Le critère calculé.............................................................................................................. 8
3.3.3
Zone de critères correspondant à une combinaison de critères .................................... 9
4
Les fonctions Excel de Bases de Données ........................................................................ 10
5
Utilisation des fonctions catégorie Math & Trigo ............................................................ 11
6
Les tableaux croisés dynamiques ...................................................................................... 14
6.1
La création d’un tableau croisé dynamique ............................................................... 14
6.2
La personnalisation d’un tableau croisé dynamique .................................................. 19
6.2.1
La création d'intervalles sur un champ. ........................................................................ 19
6.2.2
L'ajout d'un deuxième champ de ligne.......................................................................... 19
6.2.3
Affichage des sous-totaux ............................................................................................. 20
6.2.4
Affichage des totaux et sous-totaux .............................................................................. 20
6.2.5
Utilisation d'un champ Filtre du rapport ....................................................................... 21
2 PREAMBULE
L’objectif du travail qui va vous être demandé est de vous faire découvrir des fonctionnalités
poussées d’Excel, mais surtout, de vous inculquer une certaine autonomie. Ces deux critères
seront évalués pendant ce module : votre aptitude à découvrir, comprendre et mettre en œuvre
des fonctionnalités du logiciel en autonomie et votre capacité à respecter et suivre une consigne.
En effet, après avoir récupérer le fichier qui servira de base sur le serveur ftp étudiant, vous
devrez produire un document final, selon des règles qui seront précisées au fur et à mesure de
l’énoncé, et qui fera l’objet d’une évaluation. Il faudra donc rendre le fichier Excel terminé pour
qu’il soit noté (1/2 de la note finale). Ce travail sera fait par binôme (c’est-à-dire 2 personnes
maxi).
La seconde moitié de la note sera attribuée suite à une évaluation pratique individuelle, dans
laquelle vous devrez faire la preuve de votre maitrise de l’outil.
2
M 2104
DUT GEA 1ère année | [email protected]
M2104 Environnement Informatique
2013-2014
Les listes de données sous Excel
Une liste est une suite de lignes contenant des ensembles de données similaires.
La première ligne d'une liste contient les étiquettes de colonne.
Les éléments d'une colonne représentent la même information dans toutes les lignes de la
liste.
Les éléments d'une liste sont saisis ou évalués par la mise en place de formules.
Une liste peut être vue et utilisée comme une base de données avec les correspondances
suivantes :
- une ligne de la liste est un enregistrement,
- une colonne de la liste est un champ,
- la première ligne contient les noms des champs.
Remarques
- Pour différencier les étiquettes de colonne des éléments de la liste, ne pas insérer de lignes
vides mais choisir un élément de mise en forme (police, alignement, mise en gras ...)
différent de celui des lignes.
- Attribuer un nom à la liste permet de faciliter son utilisation.
ATTENTION !! Il faut nommer une plage intégrant la 1ère ligne des étiquettes de colonne.
Exemple de liste
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
A
B
NOM
PRENOM
ABENHAÏM
ABSCHEN
ADAMO
AGAPOF
ALEMBERT
AMARA
AMELLAL
AMELLAL
AMELLAL
ANGONIN
AZOURA
AZRIA
BACH
BAH
BARNAUD
BARRACHINA
BARRANDON
BASS
BAUDET
BAUDET
BEAUDEAU
BEAUMIER
BEDO
BEETHOVEN
BENHAMOU
BENSIMHON
BENSIMON
BÉRAUD
Myriam
Paul
Stéphane
Brigitte
Jean
Nicolas
Jean-Marc
Viviane
Henri
Jean-Pierre
Marie-France
Maryse
Ginette
Paule
Janine
Monique
Margaret
Thierry
Arlette
Michele
Gérard
Isabelle
Jean
Michele
Jeanine
Pascal
Elisabeth
Giséle
C
D
TELEPHONE DIRECTION
3091
3186
3055
3033
3408
3098
3766
3421
3132
3419
3127
3060
3147
3795
3725
3070
3280
3090
3632
3880
3541
3595
3008
3013
3486
3636
3287
3141
CCS DXO
CCS AGL
CCS OGT
CFS CO
CCS DXO
CCS AGL
CFS CO
CCS DPO
CFS CO
CFS FSC
CCS DXO
SNPO
CFS CO
CCS AGL
CFS ONF
SNPO
CFS FSC
CCS DPO
CFS FSC
CCS DXO
CFS AG
CFS AG
CFS ONF
SNPO
CFS CO
CCS AGL
CFS FSC
CFS CO
E
F
G
H
I
J
K
SITE
PIECE
SALAIRE
sexe
date de
naisssance
AGE
Catégor
ie
65
44
55
42
44
64
46
56
55
60
57
58
59
59
56
51
33
54
59
51
40
47
40
61
48
62
56
43
8
3
6
3
4
8
4
6
9
7
6
6
7
6
9
5
1
6
6
5
3
4
3
7
4
7
9
3
Paris
Marseille
Paris
Nice
Paris
Paris
Nice
Strasbourg
Nice
Marseille
Marseille
Paris
Nice
Paris
Nice
Paris
Nice
Strasbourg
Nice
Paris
Nice
Nice
Nice
Paris
Nice
Paris
Nice
Marseille
pièce 58
pièce 74
pièce 73
pièce 109
pièce 134
pièce 80
pièce 232
pièce 80
inconnu
pièce 70
inconnu
pièce 233
pièce 90
pièce 131
pièce 58
pièce 232
pièce 34
pièce 35
pièce 91
pièce 96
pièce 212
pièce 17
pièce 219
pièce 131
pièce 58
pièce 73
pièce 58
pièce 245
2 011,94 €
2 474,29 €
2 444,82 €
2 158,25 €
1 183,90 €
4 151,95 €
1 899,00 €
1 200,00 €
3 201,14 €
1 658,25 €
3 145,80 €
1 650,21 €
2 885,09 €
1 108,71 €
2 014,09 €
1 606,47 €
1 848,08 €
2 400,00 €
2 887,84 €
2 983,95 €
1 426,95 €
1 316,45 €
1 825,26 €
1 130,92 €
2 017,23 €
2 845,50 €
1 986,96 €
1 736,92 €
femme
30-oct-47
homme 11-nov-68
homme 16-déc-57
femme 24-mars-70
homme 13-janv-68
homme
21-oct-48
homme
07-oct-66
femme
20-mai-56
homme 25-déc-57
homme 19-sept-52
femme
13-avr-55
femme 22-janv-54
femme 20-janv-53
femme
29-nov-53
femme
23-oct-56
femme
04-juin-61
femme 26-janv-79
homme 04-févr-58
femme
11-déc-53
femme
04-avr-61
homme
04-avr-72
femme
09-déc-65
homme 01-sept-72
femme
23-févr-51
femme
24-juil-64
homme 29-août-50
femme
22-avr-56
femme
11-juil-69
3
M 2104
DUT GEA 1ère année | [email protected]
M2104 Environnement Informatique
2013-2014
3 LE FILTRAGE DES DONNEES
L'utilisation de filtres est l'outil le plus simple d'exploitation des listes de données sous Excel.
Les filtres d'Excel permettent d'extraire d'un tableau toutes les lignes répondant à certaines
conditions (critères).
3.1 FILTRE AUTOMATIQUE
Le plus simple des filtres est le Filtre Automatique.
Positionner le curseur dans une cellule quelconque du tableau (dont la première ligne contient
les intitulés des colonnes). La commande Données / Filtrer fait apparaître un petit triangle sur
chaque en-tête de colonne.
Il suffit de cliquer sur ce triangle pour afficher une liste déroulante proposant les différentes
valeurs de cette colonne.
Les lignes ne correspondant pas aux critères de sélection retenus sont masquées. Elles ne sont
pas supprimées et sont prises en compte dans les calculs mettant en jeu des plages de cellules.
En filtrant sur plusieurs colonnes à la fois, on peut affiner la sélection.
On peut limiter le filtrage à quelques colonnes en sélectionnant les en-têtes correspondants
avant de filtrer. Pour limiter à une seule colonne, il suffit de sélectionner l'en-tête de la
colonne et quelques cellules de cette colonne.
Pour supprimer le filtre, il faut activer la commande Données / Filtrer à nouveau.
Travail à faire
Sur la liste de données du classeur ListeExemple appliquer un filtre automatique pour obtenir :
-
la liste des salariés rattachés au site de Paris, puis de Marseille, puis de Nice ;
la liste des salariés rattachés à la direction du SNPO ;
la liste des salariés rattachés au site de Paris et de sexe féminin ;
la liste des salariés rattachés au site de Marseille et appartenant à la catégorie 3.
3.2 FILTRE AUTOMATIQUE PERSONNALISE
Pour filtrer une colonne, on a vu qu'on pouvait, dans la liste déroulante attachée à cette
colonne, sélectionner un élément de la liste.
Il est possible de réaliser un filtre un peu plus fin en cliquant sur la valeur Filtres Textuels /
Filtre personnalisé.
Une boîte de dialogue permet de saisir un ou deux critères sur la colonne :
4
M 2104
DUT GEA 1ère année | [email protected]
M2104 Environnement Informatique
2013-2014
Travail à faire
Sur la liste de données du classeur ListeExemple appliquer un filtre automatique personnalisé:
-
pour obtenir la liste des salariés rattachés au site de Marseille et ayant entre 50 et 65 ans ;
pour obtenir la liste des salariés rattachés au site de Paris, ayant entre 30 et 45 ans et
appartenant à la catégorie 3 ou 5 ou 7.
pour obtenir la liste des salariés rattachés à la direction CFS CO ou à CCS DXO,
dépendant du site de Nice et dont le salaire est compris entre 2500 € et 3500 €.
3.3 FILTRES ELABORES
Le filtrage automatique, facile à mettre en œuvre, possède certaines restrictions :
- il donne une liste résultat affichée au même emplacement,
- tous les champs de la liste source sont réaffichés,
- le nombre de conditions sur un même champ est limité à 2.
Le filtrage élaboré permet de s’affranchir de toutes ces restrictions :
- la liste résultat peut être affichée à un emplacement choisi par l’utilisateur (dans la même
ou dans une autre feuille de calcul),
- l’utilisateur peut choisir les champs à afficher dans la liste résultat,
- il n’y a pas de limite sur les conditions définies dans la zone de critères.
Le filtrage se réalise en saisissant les critères de filtrage, non plus dans des listes déroulantes,
mais dans une plage de cellules appelée Zone de critères. Sélectionner une cellule de la liste
et choisir la commande Données /Trier et Filtrer / Avancé.
5
M 2104
DUT GEA 1ère année | [email protected]
M2104 Environnement Informatique
2013-2014
Les critères de filtrage doivent être définis dans une zone de critères, plage de cellules
reprenant le nom exact de la ou des colonnes servant de filtre.
Il est possible de :
filtrer les données sur place (ne pas oublier d’afficher toutes les données avant de filtrer à
nouveau)
ou
copier le résultat du filtrage en un autre emplacement de la feuille de calcul : par activation de
l'option Copier vers un autre emplacement en fixant une plage dans la zone Copier dans.
Suivant la nature de la zone Copier dans, les données affichées après filtrage varient :
Nature de la zone Copier dans ...
Une cellule vide unique
Une plage constituée d'un sousensemble d'étiquettes de colonne de
la liste
 Excel copie toutes les lignes filtrées et toutes les
étiquettes de colonne de la liste.
 Excel copie uniquement les colonnes correspondantes.
Exemple :
Pour obtenir une liste filtrée affichant uniquement les colonnes NOM, PRENOM et
SALAIRE de la liste de données ListeExemple :
- copier ces noms de champs dans des cellules (en M6, N6 et O6 par exemple),
- dans la boîte de dialogue Filtre élaboré, cliquer sur le bouton d’option Copier vers un
autre emplacement, puis donner la référence de la plage M6:O6 dans la zone Copier dans.
6
M 2104
DUT GEA 1ère année | [email protected]
M2104 Environnement Informatique
2013-2014
ATTENTION
Pour obtenir les données filtrées dans une autre feuille de calcul, lancer la commande
Données Filtre élaboré en se plaçant dans la feuille destination.
La zone de critères peut être indifféremment dans la feuille contenant la liste initiale ou
dans la feuille destination.
La condition de filtrage est saisie par l’utilisateur dans une plage de cellules appelée la zone
de critères. L’écriture de la zone de critères doit permettre à Excel d’identifier les colonnes à
tester et de fixer les conditions d’extraction.
Il existe deux sortes de critères :
- le critère de comparaison dans lequel la condition est basée sur une comparaison avec une
valeur constante.
- le critère calculé où la condition est basée sur une comparaison avec une valeur calculée.
3.3.1 LE CRITERE DE COMPARAISON
Il s’agit d’une plage de deux cellules :
-
la 1ère cellule contient une étiquette de critère, exactement le nom du champ de la
liste sur lequel va être vérifiée la condition.
la 2ème cellule (positionnée en dessous de l'étiquette de critère) contient la condition
qui s’écrit avec un opérateur de comparaison et une valeur constante de type texte,
date ou numérique.
Exemple :
A partir de ListeExemple : afficher pour les salariés habitant Marseille, leurs noms, prénoms
et salaires.
N
O
La zone de critères est constituée d’un critère :
2 SITE
- l’étiquette de critères : SITE
3 Marseille
- la condition : Marseille
4
La boîte de dialogue Filtre élaboré va être complétée en donnant la référence N2:N3 dans la
zone de critères.
7
M 2104
DUT GEA 1ère année | [email protected]
M2104 Environnement Informatique
2013-2014
3.3.2 LE CRITERE CALCULE
Il s’agit d’une plage de deux cellules :
la 1ère cellule contient une étiquette de critère, obligatoirement différente du nom du champ
de la liste sur lequel va être vérifiée la condition.
la 2ème cellule (positionnée en dessous de l'étiquette) contient une expression logique
composée de trois termes :
- la référence relative du premier élément de la colonne à tester (l’étiquette de critère étant
différente du nom du champ, c’est l’élément qui permet à Excel d’identifier la colonne à
tester) ;
- un opérateur de comparaison ;
- une formule qui détermine la valeur de référence.
Exemple
Afficher les salariés de la liste dont le salaire est supérieur au salaire moyen de l’ensemble du
personnel.
La formule à établir dans la zone de critères (en N3) est composée :
- du premier élément de la colonne à tester c’est à dire G2 (première valeur du salaire)
- de l’opérateur de comparaison >
- de la formule permettant le calcul du prix moyen des téléviseurs
=MOYENNE($G$2:$G$285)
Le résultat affiché en N3 est une valeur logique (VRAI ou FAUX) correspondant au résultat
de l’évaluation logique pour le premier élément de la colonne ici G2.
Lors du filtrage Excel va balayer les différentes lignes, modifiant automatiquement la formule
de manière à comparer successivement G3, G4 … à la moyenne des salaires.
8
M 2104
DUT GEA 1ère année | [email protected]
M2104 Environnement Informatique
2013-2014
3.3.3 ZONE DE CRITERES CORRESPONDANT A UNE COMBINAISON DE CRITERES
Exemple :
A partir de ListeExemple, afficher les salariés dépendant de Nice ou de Marseille, dont l’age
est compris entre 28 et 37 ans, et dont le salaire est inférieur au salaire moyen de l’ensemble
du personnel.
En respectant la syntaxe d’écriture :
- critères rassemblés dans un ET sont saisis sur une même ligne (avec un critère par
colonne)
- critères rassemblés dans un OU sont saisis sur une1ère, 2ème, 3ème ligne ….
L’écriture de la zone de critères est la suivante
N
2 SITE
3 Nice
4 Marseille
O
AGE
>=28
>=28
P
Q
Salaire moyen
VRAI
VRAI
AGE
<=37
<=37
La boîte de dialogue Filtre élaboré est
remplie de la manière suivante :
Le résultat de ce filtrage donne la liste
suivante :
NOM
AMELLAL
BEAUDEAU
BEDO
BÉRAUD
BOLLO
CUCIT
DELAMARRE
DOUCOURE
DURAND
FERRAND
FRETTE
FRISA
GUITTON
JUDITH
KARSENTY
KONGOLO
LANLO
LEMARIÉ
LY
MARTEL
PARINET
PERRUCHON
ROULET
SAILLANT
TAMBURRINI
TAN
Travail à faire
-
-
PRENOM
Jean-Marc
Gérard
Jean
Giséle
René
Marie-Louise
Jean-Luc
Jean-Jacques
Jean-Pierre
Danielle
Daniel
Brigitte
Francis
Marie-Hélène
Christian
Bernard
Catherine
Hervé
Jean-Claude
Jean-Claude
Jean-Louis
Fabrice
Christiane
Marie-Claude
Marie-Claire
Joelle
SALAIRE
1 899,00 €
1 426,95 €
1 825,26 €
1 736,92 €
1 958,25 €
1 860,95 €
1 045,76 €
1 043,02 €
1 764,30 €
2 083,23 €
1 147,03 €
1 008,65 €
1 430,29 €
1 883,55 €
974,73 €
1 016,06 €
1 227,68 €
1 666,43 €
2 109,08 €
1 970,73 €
1 992,00 €
1 102,81 €
1 422,32 €
1 917,85 €
1 683,60 €
1 545,23 €
Mettre en place dans une feuille de calcul différente de celle contenant la base, les
exercices proposés dans le paragraphe sur les filtres élaborés (1 seul onglet, nommé
filtres élaborés, sur lequel doit figurer les zones de critères en haut et le résultat de
l’extraction juste en dessous).
Dans un autre onglet nommé filtres supplémentaires :
o Réaliser l’extraction de la liste des hommes de catégorie comprise entre 5 et 7
(inclus) et ne travaillant PAS à Paris. (Nom, Prénom, Site, âge)
o Réaliser l’extraction de la liste des femmes, dont le numéro de téléphone
commence par 30 ou 31 et gagnant plus de 2000 € de salaire. (Nom, Prénom,
Téléphone et salaire).
M 2104
DUT GEA 1ère année | [email protected]
9
M2104 Environnement Informatique
2013-2014
4 LES FONCTIONS EXCEL DE BASES DE DONNEES
Microsoft Excel fournit des fonctions de feuille de calcul pour analyser des données stockées
dans des listes. Chacune de ces fonctions, regroupées sous l'appellation fonctions de base de
données, utilise trois arguments : liste de données, colonne et critères. Ces arguments font
référence aux plages de feuille de calcul utilisées par la fonction :
- la plage contenant la liste de données (y compris la ligne des étiquettes).
- l’étiquette de la colonne (ou la référence de la cellule contenant cette étiquette) sur laquelle
le calcul correspondant à la fonction choisie sera exécuté (une moyenne pour
BDMOYENNE, un comptage pour BDNB ….).
- la zone de critères ou plage contenant les conditions que doivent vérifier les éléments de la
liste.
Les fonctions disponibles sont les suivantes :
BDMOYENNE(plage;etiquette;plage_critères) Calcule la moyenne des valeurs d’une colonne de la liste pour
les éléments qui vérifient les critères.
Compte le nombre d’éléments de la liste qui vérifient les
BDNB(plage;etiquette;plage_critères)
critères.
Donne la valeur la plus élevée d’une colonne de la liste pour
BDMAX(plage;etiquette;plage_critères)
les éléments qui vérifient les critères.
Donne la valeur la plus petite d’une colonne de la liste pour
BDMIN(plage;etiquette;plage_critères)
les éléments qui vérifient les critères.
Calcule la somme des valeurs d’une colonne de la liste pour
BDSOMME(plage;etiquette;plage_critères)
les éléments qui vérifient les critères.
ATTENTION
Dans le cas d'utilisation de la fonction BDNB, le deuxième argument est inutile car la
fonction gère un compteur indépendant augmenté de 1 chaque fois que le critère est
vérifié.
La zone de critères
Elle se construit dans une feuille de calcul, en suivant les principes donnés dans le paragraphe
sur le filtrage élaboré.
Pour faciliter l'écriture des formules, pensez à nommer la plage de cellules ainsi obtenue.
Pour éviter les erreurs de saisie dans l'écriture des étiquettes de colonnes, pensez à effectuer
des copier/coller.
Travail à faire
Dans un onglet appelé "Base de données" différent de celui contenant la liste des données
en utilisant les fonctions de base de données :
- donner le salaire le plus bas de l’ensemble du personnel.
- calculer le nombre total de salariés habitant Marseille dont le montant du salaire est
compris entre 1250 € et 23000 €.
- donner le nombre de salariés rattachés à la direction CCS DXO.
- calculer la masse salariale pour le site de Strasbourg.
- calculer le salaire moyen du site Marseille.
M 2104
DUT GEA 1ère année | [email protected]
10
M2104 Environnement Informatique
2013-2014
5 UTILISATION DES FONCTIONS CATEGORIE MATH & TRIGO
Dans le cas d'opérations de comptage ou addition d'éléments répondant à un critère unique, il
est possible d'utiliser les fonctions NB.SI et SOMME.SI
Fonction NB.SI(plage;critère)
Pour chaque élément de la plage de cellules vérifiant une certaine condition ou critère, Excel
incrémente de 1 le résultat renvoyé par la fonction.
- L’argument plage est la plage des éléments à tester.
- L’argument critère est la condition devant être vérifiée par les cellules de l'argument plage.
En fonction de la complexité de la condition à tester, le critère adopte différentes formes
d’écriture :
être égal à une valeur
numérique
être égal à une chaîne de
caractères
être égal au contenu d’une
cellule
La condition
s'écrit en donnant la valeur
numérique
s'écrit en donnant le texte entre
guillemets
s'écrit en donnant la référence
de la cellule
être supérieur, inférieur à une
valeur numérique ….
être supérieur, inférieur … à
une valeur contenue dans une
cellule
s'écrit en utilisant les
opérateurs de comparaison
s'écrit en concaténant
l'opérateur de comparaison et
une chaîne de caractères
Exemple
être égal à 200
NB.SI(plage;200)
être égal à oui
NB.SI(plage; "oui")
être égal au contenu de la
cellule B1
NB.SI(plage;B1)
être supérieur à 200
NB.SI(plage; ">200")
être supérieur au contenu de la
cellule B3
NB.SI(plage; ">"&B3)
D’une manière similaire, la fonction SOMME.SI est capable sur une plage de cellules donnée
de n’additionner que les éléments vérifiant une même condition.
Fonction SOMME.SI(test_plage;critère;somme_plage)
Elle permet de calculer la somme de valeurs qui répondent à un critère (ou condition) unique.
- L’argument test_plage est la plage des éléments à tester ;
- L’argument critère est la condition devant être vérifiée par les cellules de l'argument
plage ;
- L’argument somme_plage est la plage des cellules à ajouter.
Exemple 1
Le nombre de salariés rattachés au site de Nice s'obtient par la formule suivante :
=NB.SI(E2:E285;"Nice")
Le résultat est 153.
Exemple 2
Le montant total des salaires des employés rattachés au site de Marseille s'obtient par la
formule suivante :
=SOMME.SI(E2:E285;"Marseille";G2:G285)
Le résultat est 46 184,276 €.
M 2104
DUT GEA 1ère année | [email protected]
11
M2104 Environnement Informatique
2013-2014
Travail à faire
Dans un onglet appelé "Math & trigo" différent de celui contenant la liste des données :
- Compter le nombre de salariés de la direction CFS FSC ; (réponse : 57) ;
- Compter le nombre de salariés dont le salaire est supérieur ou égal à 1500 € (réponse :
216) ;
- Compter le nombre de salarié dont le n° de pièce est inconnu (réponse : 13) ;
- Calculer le montant total des salaires versés aux salariés de moins de 25 ans (réponse :
8686,17 €) ;
- Calculer le montant total des salaires versés aux salariés de plus de 50 ans (réponse :
265119,40 €) ;
- Calculer le salaire moyen versé aux salariés femmes (réponse : 2 100,69 €) ;
- Calculer le salaire moyen versé aux salariés hommes (réponse : 2 151,43 €).
Complément sur le dépouillement d'un questionnaire
Le dépouillement d'un questionnaire est facilement réalisable sous Excel en utilisant la notion
de liste et les différentes techniques d'analyse des données de la liste (les questions étant
représentées en colonne, chaque ligne de la liste représentant les réponses à un
questionnaire). Le dépouillement d'une question à réponse unique ne pose pas de problème
(utilisation des fonctions de comptage et/ou des tableaux croisés dynamiques). Le
dépouillement d'une question à réponses multiples est plus complexe :
- soit la question est éclatée en autant de colonnes qu'il y a de types de réponses possibles (ce
qui peut devenir très lourd à gérer) ;
- soit le codage des réponses à la question permet d'utiliser la fonction NB.SI en utilisant un
masque d’interrogation.
Exemple
A la question suivante :
"Vous utilisez votre vélo en  Promenade
 Déplacement professionnel
 Sport loisir
 Sport compétition
 Autre"
Plusieurs réponses étant possible, le dépouillement de la question doit en prendre en compte
les différentes combinaisons fournies. En codant les réponses sous la forme :
Promenade
1
Déplacement professionnel
2
Sport loisir
3
Sport compétition
4
Autre
5
La valeur à saisir dans la colonne représentant la question se compose d'une suite de
caractères (chiffres) :
Par exemple 14 ou 3 ou 34 ou 134, …
Le même caractère (chiffre) peut apparaître dans plusieurs réponses avec une position
différente.
Le comptage des réponses peut s'effectuer en utilisant la fonction NB.SI avec un masque
d'interrogation :
M 2104
DUT GEA 1ère année | [email protected]
12
M2104 Environnement Informatique
A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
B
Question 4
34
12
4
5
234
12
24
5
1
12
34
1234
13
24
C
D
E
F
2013-2014
G
H
I
Cette plage de cellules est définie avec un
Format Cellule Nombre de type Texte
(avant la saisie des valeurs)
Compter le nombre de personnes qui utilisent le vélo pour le
sport de loisir nécessite la prise en compte de toutes les réponses
qui contiennent la valeur 3 (quelle que soit sa position dans la
suite de chiffres). La fonction NB.SI s'utilise avec un masque
d'interrogation :
=NB.SI(B5:B18;"*3*")
fournit le nombre de fois où la valeur 3 apparait (quel que soit sa
position)
Nb de "Sport loisir"
5
Autres exemples :
=NB.SI(B5:B18;"*2*4*") (valeur obtenue : 4)
(repésente le nb de couples 2 et 4, quelles que soient leurs
positions dans la suite de caractères).
=NB.SI(B5:B18;"*234*") (valeur obtenue : 2 )
(repésente le nb de triplets 2, 3 et 4, quelle soit le chiffre avant et
après le triplet).
Travail à faire
Toujours dans le même onglet appelé "Math & trigo" :
- Compter le nombre de salariés travaillant dans une ville commençant par la lettre "S"
(réponse : 12) ;
- Compter le nombre de salariés dont le nom de la direction contient deux fois la lettre "S"
(réponse : 23) ;
13
M 2104
DUT GEA 1ère année | [email protected]
M2104 Environnement Informatique
2013-2014
6 LES TABLEAUX CROISES DYNAMIQUES
Les tableaux croisés permettent d'afficher et d'analyser des résumés sur des données déjà
créées, sous Excel ou d'autres applications (Access - FoxPro ...).
Ces tableaux sont dits dynamiques car il est possible d'obtenir différentes vues des données
sources en faisant pivoter les titres de colonnes et de lignes.
6.1 LA CREATION D ’UN TABLEAU CROISE DYNAMIQUE
Il existe un assistant pour créer des tableaux croisés dynamiques, pour le lancer, il faut
rajouter un accès dans la barre d’outils Accès rapide. Pour ce faire, aller dans
Fichier/Options/Barre d’outils Accès rapide
Puis choisir Commandes non présentes dans le ruban et ajouter l’Assistant de tableau croisé
dynamique. Un nouveau bouton apparaît en haut à gauche d’Excel :
Etape 1 / identification du type de données à analyser
Sélectionnez l'option Rapport de tableau croisé dynamique.
Excel demande où est située la source des données qui vont servir à composer le tableau
croisé dynamique. Ces données peuvent provenir de quatre sources différentes.
14
M 2104
DUT GEA 1ère année | [email protected]
M2104 Environnement Informatique
Liste ou base de
données Microsoft
Excel.
Source de données
externes
Plage de feuilles de
calcul avec étiquette.
Autre tableau ou
graphique croisé
dynamique
2013-2014
Les données proviennent d'une liste de données Excel ou d'une série
de cellules située sur une feuille de calcul d'Excel.
Les données proviennent d'autres logiciels tels qu'Access, FoxPro, …
Créer automatiquement un tableau après avoir déterminé la plage de
cellules à utiliser. Il utilise le contenu de la première ligne et de la
première colonne pour déterminer le nom des champs du tableau.
Permet d'approfondir des analyses sur des tableaux et graphiques
dynamiques qui ont déjà été conçus.
Etape 2 / identification des données à analyser
Etape 3 / Création d'une présentation du tableau
La boîte de dialogue affichée est la suivante :
Vous devez tout d’abord choisir l’emplacement du futur tableau : dans une nouvelle feuille ou
une existante à sélectionner.
15
M 2104
DUT GEA 1ère année | [email protected]
M2104 Environnement Informatique
2013-2014
Il faut ensuite cliquer sur terminer pour faire apparaître un tableau vierge :
Régler la Disposition
Les tableaux croisés dynamiques permettent de présenter tout ou partie des informations de la
liste de données avec la disposition désirée. Pour cela il n’y a qu’à faire glisser les champs
disponibles (correspondants aux entêtes du tableau source) dans les cases adéquats.
Si l’on veut que le tableau visualise le nombre de salariés dans les différents sites en fonction
de leur sexe :
- dans la zone Etiquettes de lignes faire
glisser le champ sexe,
- dans la zone Etiquettes de colonnes faire
glisser le champ SITE,
- dans la zone Valeurs faire glisser le champ
SITE ou SEXE.
Automatiquement Excel propose de calculer
le nombre d’éléments pour chaque couple
de valeurs (ligne, colonne).
M 2104
DUT GEA 1ère année | [email protected]
16
M2104 Environnement Informatique
2013-2014
Remarques
- Il est possible de créer un tableau récapitulatif à une variable, le champ choisi est
positionné dans la zone Etiquettes de lignes ou Etiquettes de colonnes.
- Le champ Filtre du rapport sert à filtrer
l'affichage des données. Il sera utilisé plus loin
dans le TD.
- D'autres fonctions peuvent être utilisées
Moyenne, Somme…, pour les afficher cliquer
sur la petite flèche à droite du bouton Nombre
de SITE et choisir Paramètres des champs de
valeurs, la boîte de dialogue suivante est
obtenue :
Le champ SALAIRE est ajouté à la zone Valeurs pour connaître le montant total des salaires
par site et par sexe :
Sa sélection entraîne la création du tableau suivant (après avoir appliqué le style monétaire et
quelques renommages) :
A
3
4 SEXE
5
femme
6
Nombre de SITE
7
Somme de SALAIRE
8
homme
9
Nombre de SITE
10
Somme de SALAIRE
11 Total Nombre de SITE
12 Total Somme de SALAIRE
B
C
SITE
Lille
Marseille
D
Nice
E
Paris
F
G
Strasbourg Total général
1
15
101
58
5
1 922,48 € 32 499,02 € 212 951,63 € 120 350,06 € 10 401,22 €
180
378 124,41 €
2
8
52
35
7
5 250,60 € 13 685,25 € 109 792,80 € 79 207,50 € 15 812,48 €
3
23
153
93
12
7 173,09 € 46 184,27 € 322 744,43 € 199 557,56 € 26 213,70 €
104
223 748,64 €
284
601 873,05 €
17
M 2104
DUT GEA 1ère année | [email protected]
M2104 Environnement Informatique
2013-2014
Les Options
Il va notamment permettre de fixer les options de mise en forme du tableau : totaux, soustotaux… Pour faire apparaître la fenêtre des options, cliquer sur le bouton Options du tableau
croisé dynamique après avoir sélectionné Option dans le ruban :
Il est possible de personnaliser le tableau dynamique créé, en :
- insérant, supprimant ou réorganisant les champs du tableau,
- modifiant le mode de calcul des données,
- modifiant la mise en forme du tableau.
18
M 2104
DUT GEA 1ère année | [email protected]
M2104 Environnement Informatique
2013-2014
6.2 LA PERSONNALISATION D ’UN TABLEAU CROISE DYNAMIQUE
6.2.1 LA CREATION D 'INTERVALLES SUR UN CHAMP.
On souhaite maintenant visualiser la répartition des salariés de chaque site en fonction de
leurs salaires.
Très peu de salariés ayant exactement le même salaire, le tableau obtenu comporte beaucoup
trop de colonnes pour être exploitable. Il faut alors faut définir des intervalles de salaires pour
réduire sa dimension.
3
4
5
6
7
8
9
10
11
A
Nombre de SALAIRE
SITE
Lille
Marseille
Nice
Paris
Strasbourg
Total général
B
C
D
E
F
G
H
I
J
K
L
M
N
SALAIRE
939,76 € 948,04 € 964,26 € 973,50 € 974,73 € 988,35 € 1 001,81 € 1 008,65 € 1 015,22 € 1 016,04 € 1 016,06 € 1 042,47 € 1 043,02 €
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
Extrait du tableau obtenu
Sélectionner une valeur de champ SALAIRE, avec le
bouton droit de la souris, choisir l’élément Grouper.
Il est alors possible de définir les zones d’intervalles
voulues :
En choisissant par exemple une valeur de début de 900 et une valeur de fin de 4500 avec un
pas de 500, le tableau suivant est obtenu :
3
4
5
6
7
8
9
10
A
Nombre de SALAIRE
SITE
Lille
Marseille
Nice
Paris
Strasbourg
Total général
B
SALAIRE
900-1400
C
5
28
21
3
57
D
E
F
G
H
I
1400-1900 1900-2400 2400-2900 2900-3400 3400-3900 3900-4400 Total général
2
1
3
6
4
5
3
23
33
38
36
16
2
153
20
12
19
20
1
93
1
2
3
3
12
60
58
64
42
2
1
284
L'opération inverse de Grouper est obtenue avec l’élément Dissocier.
6.2.2 L'AJOUT D 'UN DEUXIEME CHAMP DE LIGNE
A partir du tableau précédent on souhaite afficher par Site et par sexe la répartition des
salaires.
Cliquer dans une cellule du tableau croisé, choisir le champ SEXE et le faire glisser sur le
tableau existant à côté du champ SITE :
19
M 2104
DUT GEA 1ère année | [email protected]
M2104 Environnement Informatique
A
3 Nombre de SALAIRE
4 SITE
5
Lille
6
femme
7
homme
8
Marseille
9
femme
10
homme
11
Nice
12
femme
13
homme
14
Paris
15
femme
16
homme
17
Strasbourg
18
femme
19
homme
20 Total général
B
SALAIRE
900-1400
C
5
2
3
28
18
10
21
14
7
3
1
2
57
D
E
F
G
2013-2014
H
I
1400-1900 1900-2400 2400-2900 2900-3400 3400-3900 3900-4400 Total général
2
1
3
1
1
1
1
2
6
4
5
3
23
4
3
3
3
15
2
1
2
8
33
38
36
16
2
153
21
28
21
12
1
101
12
10
15
4
1
52
20
12
19
20
1
93
15
7
6
16
58
5
5
13
4
1
35
1
2
3
3
12
1
1
1
1
5
1
2
2
7
60
58
64
42
2
1
284
6.2.3 AFFICHAGE DES SOUS -TOTAUX
Sélectionner le tableau, choisir dans le ruban l’onglet Création et faire afficher les sous-totaux
Le tableau suivant est obtenu
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
A
Nombre de SALAIRE
SITE
Lille
femme
homme
Total Lille
Marseille
femme
homme
Total Marseille
Nice
femme
homme
Total Nice
Paris
femme
homme
Total Paris
Strasbourg
femme
homme
Total Strasbourg
Total général
B
SALAIRE
900-1400
C
D
E
F
G
H
I
1400-1900 1900-2400 2400-2900 2900-3400 3400-3900 3900-4400 Total général
1
1
2
1
1
1
2
3
2
3
5
4
2
6
3
1
4
3
2
5
3
18
10
28
21
12
33
28
10
38
21
15
36
12
4
16
14
7
21
15
5
20
7
5
12
6
13
19
16
4
20
1
2
3
57
1
1
1
2
58
1
2
3
64
1
2
3
42
1
60
15
8
23
3
1
1
2
2
101
52
153
1
1
58
35
93
1
5
7
12
284
6.2.4 AFFICHAGE DES TOTAUX ET SOUS -TOTAUX
En utilisant les options (voir page 17) ou l’onglet Création, faire disparaître les totaux
généraux
Le tableau suivant est obtenu
20
M 2104
DUT GEA 1ère année | [email protected]
M2104 Environnement Informatique
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
A
Nombre de SALAIRE
SITE
Lille
femme
homme
Total Lille
Marseille
femme
homme
Total Marseille
Nice
femme
homme
Total Nice
Paris
femme
homme
Total Paris
Strasbourg
femme
homme
Total Strasbourg
B
SALAIRE
900-1400
C
D
E
2013-2014
F
G
H
1400-1900 1900-2400 2400-2900 2900-3400 3400-3900 3900-4400
1
1
2
1
1
2
3
5
4
2
6
3
1
4
3
2
5
3
18
10
28
21
12
33
28
10
38
21
15
36
12
4
16
14
7
21
15
5
20
7
5
12
6
13
19
16
4
20
1
2
3
1
1
1
2
1
2
3
1
2
3
1
3
1
1
2
1
1
6.2.5 UTILISATION D 'UN CHAMP FILTRE DU RAPPORT
Un champ de filtre permet de filtrer les données affichées dans le tableau croisé. Pour afficher
par Site la répartition des salaires :
Pour définir un champ de filtre à partir du tableau précédent, faire glisser avec la souris le
champ SITE dans la zone Filtre du rapport. Il est alors possible de choisir la valeur du site
dans la liste déroulante (les données affichées ne concernent alors que le site retenu) :
A
1
2
3
4
5
6
7
B
SITE
Nice
Nombre de SALAIRE
SITE
femme
homme
Total général
SALAIRE
900-1400
C
18
10
28
D
E
F
G
H
1400-1900 1900-2400 2400-2900 2900-3400 3400-3900 Total général
21
28
21
12
1
101
12
10
15
4
1
52
33
38
36
16
2
153
L’opération inverse s’obtient en ramenant le champ de page dans le tableau.
21
M 2104
DUT GEA 1ère année | [email protected]
M2104 Environnement Informatique
2013-2014
Travail à faire
Dans des onglets nommés TBCD X, où X représente le numéro de la question :
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
4. Calculer la moyenne des rémunérations par tranches d'âges avec distinction
homme/femme (utiliser la fonction "grouper" sur les âges : de 20 à 70 ans par pas de 5
années)
5. Afficher par site et par homme/femme : le nombre de personnes, l'âge minimum, la
moyenne des âges, l'âge maximum.
6. 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.
7. Créer sous Word un document récapitulatif avec les tableaux des exercices 2, 4 et 5. Les
tableaux devront être liés avec les données d'Excel. 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/72.
8. En utilisant les outils de représentation graphique du bandeau insertion, obtenez le
graphique suivant :
Moyenne de SALAIRE
3500
3000
SITE
2500
Lille
2000
Marseille
1500
Nice
Paris
1000
Strasbourg
500
Lille
0
1
2
3
4
5
6
7
8
9
10
femme
sexe
1
2
3
4
5
6
7
8
9
homme
Catégorie
22
M 2104
DUT GEA 1ère année | [email protected]