REMY LENTZNER
LES TABLEAUX CROIS S DYNAMIQUES AVEC EXCEL
Collection : Informatique du quotidien
EDITIONS REMYLENT, Paris, 1 re dition, 2016.
R.C.S. 399 397 892 Paris
25 rue de la Tour dAuvergne - 75009 Paris [email protected]
WWW.REMYLENT.FR
Excel est une marque d pos e de la soci t Microsoft
ISBN EPUB : 978-2-955-7694-23
Le Code de la propri t intellectuelle interdit les copies ou reproductions destin es une utilisation
collective. Toute repr sentation ou reproduction int grale ou partielle faite par quelque proc d
que ce soit, sans le consentement de lauteur ou de ses ayant droit ou ayant cause, est illicite et
constitue une contrefa on, aux termes des articles L.335-2 et suivants du Code de la propri t
intellectuelle.
Aux personnes qui aiment jongler avec les d tails
INTRODUCTION
Gr ce Excel, vous pouvez tout instant calculer, ltrer et organiser
vos informations de mille mani res. Le tableau crois dynamique
(nous lappellerons TCD) permet de produire des statistiques et
danalyser plus nement des donn es.
Toute personne qui manipule Excel est capable de cr er des tableaux
crois s dynamiques : un(e) commercial(e) peut analyser des donn es
informations de
ing nieur(e) peut g rer des
marketing, un(e)
r sistance de mat riaux. Il ny pas de limite dans le choix des
informations analyser.
Excel est un produit populaire qui a fait lobjet de plusieurs versions
(2007, 2010, 2013 et 2016). Chaque version change environ tous les
deux ans et demi et apporte son lot dam liorations et de nouveaut s.
Ces changements interviennent aussi au sein des TCD et cette
fr n sie de nouvelles versions est une des raisons pour lesquelles
Microsoft propose maintenant dacheter le logiciel lann e. Au
moment de l criture de ce livre, la derni re version est celle de 2016
et permet plus facilement de croiser des informations en provenance
de sources externes diversi es gr ce un nouveau concept appel
mod le de donn es. Ce livre est issu dune longue exp rience de
cours en entreprise sur le sujet des tableaux crois s dynamiques. Il
sint resse aux d tails, aux options diverses et aux dispositifs plus ou
moins cach s qui facilitent le travail quotidien.
La plupart des exemples de cet ouvrage sont reproductibles avec
toutes les versions dExcel en fran ais, mais partir dExcel 2007. Ce
livre est structur en deux parties.
La partie 1 sint resse la mise en place et la manipulation du
tableau crois dynamique cr partir de donn es issues dune seule
feuille de calcul. Vous y apprendrez comment faire des calculs
simples et complexes, la mani re de r aliser des pourcentages
automatiques, les techniques de comptage de valeurs identiques et la
la plus pr cise possible. De
fa on de pr senter
les r sultats
nombreux param tres vous aideront mieux organiser les r sultats.
Les options g n rales et locales seront toutes tudi es ainsi que la
fonction sp cique qui permet de
r cup rer des informations partir du tableau crois . Pour terminer
cette premi re partie, vous ferez un petit tour au cSur dun chier
Excel pour d couvrir comment sont structur es en interne les
donn es gr ce au langage XML.
La Partie 2 porte sur lorigine des donn es. Vous y d couvrirez le
dispositif Mise sous forme de tableau qui vite les probl mes dajout
de nouvelles lignes ou colonnes dans le tableau source. Vous
tudierez la cr ation de tableaux crois s partir dinformations
issues de plusieurs feuilles de calcul gr ce aux fonctions RechercheV,
Index/Equiv et par linterm diaire du dispositif Query. Puisque chaque
version dExcel apporte son lot de nouveaut s, vous examinerez le
dispositif Mod le de donn es qui facilite la relation entre les feuilles.
Jesp re que la lecture de ce livre vous permettra de progresser dans
la ma trise de cet outil statistique. N'h sitez pas me contacter
ladresse [email protected] si vous avez des remarques sur
ce livre ou bien des questions sur les tableaux crois s dynamiques.
Je ne manquerai pas de vous r pondre.
Bonne lecture.
Lauteur
SOMMAIRE
Partie 1
Les fondamentaux
Chapitre 1
Cr er un tableau crois dynamique
1.1 Un TCD, quoi a sert ?
1.2 Structure du tableau source
1.3 Cr er un tableau crois dynamique
1.4 Mise jour du tableau crois dynamique
1.5 Voir les lignes la source du r sultat
1.6 Un exemple horizontal
1.7 Le filtre du rapport
1.8 Le menu Cr ation
Chapitre 2
Param trer les calculs
2.1 Utiliser des fonctions de calculs
2.2 Compter des acronymes
2.3 D nombrer partir dun groupe de dates
2.3.1 Regroupement par ann e
2.3.2 Regroupement multiple
2.4 Segments et chronologie
2.4.1 Les segments
2.4.2 Une chronologie visuelle
2.5 Calculs automatiques avec des %
2.5.1 Faire appara tre le % du total g n ral
2.5.2 Apparition des totaux en plus du % du total g n ral
2.5.3 % du total g n ral avec regroupement
2.5.4 Calcul du % interm diaire
2.6 Autres calculs de pourcentage
2.6.1 Affichage du % du total de la colonne
2.6.2 Affichage du % du total de la ligne
2.6.3 Affichage dun calcul % de
2.6.4 Affichage dun % du total de la ligne parente
2.6.5 Affichage dun % du total de la colonne parente
2.6.6 Affichage dun % du total du parent
2.7 Calculs de diff rence entre plusieurs valeurs
2.7.1 Affichage dune diff rence par rapport
2.7.2 Affichage dune diff rence en pourcentage par rapport
2.8 Calculs de cumuls
2.8.1 Affichage dun r sultat cumul
2.8.2 Affichage dun r sultat cumul en pourcentage
2.9 Classer des valeurs
2.10 Indexer des valeurs dans un TCD
Chapitre 3
Manipuler plusieurs champs
3.1 Cr ation dun champ calcul
3.2 Suppression dun champ calcul
3.3 Un calcul de variation pour la force de vente
3.4 Une fonction SI dans un champ calcul
3.5 Deux fonctions SI dans un champ calcul
3.6 Ajouter un l ment calcul
3.7 Afficher une liste des formules
Chapitre 4
Aller plus loin avec les options
4.1 Les options g n rales
4.1.1 La fonction LIREDONNEESTABCROISDYNAMIQUE
4.1.2 D sactivation de LIREDONNEESTABCROISDYNAMIQUE
4.1.3 Actualiser un tableau louverture
4.2 Les autres options locales
4.2.1 Les options de disposition et mise en forme
4.2.2 Les options Totaux et Filtres
4.2.3 Filtrer en enlevant les valeurs nulles dans une colonne
Advertisement
4.2.4 Les options daffichage
4.2.5 Les options dimpression
4.2.6 Les options de donn es et la m moire cache
4.3 Au cSur dun tableau crois dynamique
Partie 2
Les sources de donn es
Chapitre 5
La mise sous forme de tableau
5.1 Un probl me de lignes et de colonnes
5.2 La mise sous forme de tableau
5.3 Une ligne de formule automatique
5.4 Mise sous forme de tableau et rapport crois
5.5 Un tableau crois consolid
5.5.1 Le dispositif Consolider
5.5.2 Consolidation avec lAssistant tableau crois dynamique
Chapitre 6
Fonctions de recherche et
mod le relationnel
6.1 La fonction RECHERCHEV
6.2 Les fonctions INDEX/EQUIV
6.3 Lutilitaire QUERY facilite la relation
6.4 Le mod le de donn es
Partie 1
Les fondamentaux
Cette premi re partie sint resse la mise en place du tableau crois
dynamique partir de donn es issues dune seule feuille de calcul.
Vous d couvrirez ici la mani re de r aliser toutes sortes de calculs
statistiques ainsi que les techniques de pr sentation dinformations.
Vous tudierez galement les param tres des options qui peuvent
am liorer le travail quotidien.
CHAPITRE 1 : Cr er un tableau crois dynamique CHAPITRE 2 :
Param trer les calculs CHAPITRE 3 : Manipuler plusieurs champs
CHAPITRE 4 : Aller plus loin avec les options
Chapitre 1
Cr er un tableau crois dynamique
Tout en rappelant les obligations li es la pr sentation des donn es,
ce chapitre expose les principes de base qui r gissent la cr ation dun
tableau crois dynamique.
1.1 Un TCD, quoi a sert ?
D s que vous employez la notion de cat gorie ou de groupe
dinformations, vous pouvez utiliser ce type de dispositif pour
regrouper, calculer ou synth tiser des informations.
On parle de tableau crois dynamique lorsquon vise la production
dun rapport. Les informations sont issues dune liste de donn es
organis es en colonnes puis crois es. Les l ments de base peuvent
tre stock s dans une ou plusieurs feuilles de calcul, provenir de
plusieurs classeurs ou dune source externe.
La partie 2 de cet ouvrage pr sentera plus en d tail le mod le de
donn es qui permet d tablir des relations entre les tableaux.
La gure 1.1 est un exemple de tableau Excel quon appelle Base de
donn es. Chaque colonne
indique clairement des groupes de
donn es identiques (mois, num ro, r gion) et la colonne quantit sert
au calcul. Les versions d'Excel mettent votre disposition 1 048 576
lignes et 16384 colonnes.
Vous pouvez ainsi manipuler de grandes quantit s dinformations
mais
dans la pratique, il est assez rare demployer toutes les lignes et
toutes les colonnes. Pour bien travailler avec Excel et ses tableaux
crois s, privil giez un ordinateur rapide avec beaucoup de m moire.
Figure 1.1 : Un tableau Excel sous la forme d'une base de donn es.
La structure de ce tableau implique le respect de certaines r gles :
Dans la colonne A ne mettez que des mois.
Dans la colonne C ne mettez que des r gions.
Dans la colonne D ne mettez que des valeurs num riques.
Nins rez ni lignes vides, ni colonnes vides lint rieur du
tableau.
Nommez chaque en-t te de colonne.
Lorsque les donn es sont bien enregistr es, vous pouvez cr er tr s
facilement des statistiques. Par exemple, vous cherchez le total des
quantit s vendues (colonne D) par mois (colonne B ou A). Pour
janvier, le total correspondrait 22, pour f vrier 7, et pour mars 13.
Ici, le calcul reste facile puisquil ny a que trois mois. Mais imaginez
un tableau avec des centaines de lignes. Excel et son tableau crois
dynamique r aliseront les calculs ais ment.
Dans ce tableau, la colonne B indique le mois en chiffres pour faciliter
les tris ult rieurs. Quand il sagit de regrouper des donn es et de
calculer, Excel est excellent. Il sait regrouper des informations puis
calculer des totaux en relation avec ces groupes. Le but dun tableau
crois dynamique est donc de regrouper des valeurs communes puis
de proposer un r sultat num rique. Excel se base sur les donn es en
colonnes et les croise.
En changeant la position des lignes et des colonnes, les calculs
changent dynamiquement, do le nom de tableau crois dynamique.
Vous verrez plus loin dans ce chapitre quil est tout fait possible de
choisir plusieurs regroupements, tout en changeant le mode de calcul
du r sultat par lutilisation dautres fonctions. Ainsi vous pourrez
choisir de calculer la moyenne des quantit s gr ce la fonction
Moyenne, la quantit la plus grande gr ce la fonction Max ou la plus
petite quantit gr ce la fonction Min.
Ces fonctions sont mises en place au moment de la cr ation du
rapport de tableau crois dynamique. La gure 1-2 montre un r sultat
possible.
Figure 1-2 : Un tableau crois dynamique.
La colonne A contient les mois et les autres colonnes les r sultats
statistiques. Au centre du tableau se placent les calculs trouv s. Une
ligne Total g n ral effectue la somme des valeurs. Les donn es sont
dynamiques parce quelles changent dans le cas o les colonnes
seraient diff rentes. Les sections suivantes d tailleront la m thode
utiliser pour cette op ration.
Revenons une derni re fois sur les obligations li es la structure du
tableau source.
1.2 Structure du tableau source
Pour r ussir votre tableau crois et ses statistiques, respectez les
consignes suivantes en ce qui concerne la structure des donn es
dorigine.
Chaque colonne du tableau source poss de un nom unique.
Vous pouvez indiquer celui-ci sur plusieurs lignes dans une
m me cellule (avec les touches Alt Entr e). Pr f rez malgr
tout un nom simple.
Ne fusionnez pas les cellules o se trouvent les noms des
colonnes.
Nins rez ni lignes ni colonnes vierges dans le tableau. La
derni re ligne vide termine le tableau. De m me pour la
derni re colonne.
Des cellules vierges sont possibles dans le tableau, elles ne
nuisent en aucune mani re la structure.
Restez coh rent. Dans une colonne num rique, nintroduisez que des
valeurs avec des nombres. Dans une colonne de dates, ne faites
gurer que des dates. Dans une colonne texte, tout est permis.
Contrairement une base de donn es comme Microsoft Access, SQL
Server, FileMakerPro sur Mac ou encore MySQL sur Internet, Excel
vous permet de saisir ce que vous souhaitez dans nimporte quelle
colonne. Si vous le d sirez, vous pouvez entrer du texte l o il ne
devrait y avoir que des dates ou des nombres.
Cette permissivit dans la saisie des informations ne fait cependant
pas bon m nage avec la r alisation dun tableau crois dynamique.
Les donn es doivent absolument tre coh rentes. Si une colonne
regroupe des noms de villes, ny mettez pas des codes postaux. Excel
regroupera toujours les informations en fonction de ce que vous lui
demandez, et gr ce cette bonne organisation, vous r ussirez
toujours vos objectifs.
Ces restrictions peuvent vous sembler tonnantes mais elles vous
permettront, plus tard, de r aliser toutes vos statistiques sans
surprise. Voyons maintenant la mani re de cr er un tableau crois .
Advertisement
1.3 Cr er un tableau crois dynamique
Une fois le tableau source pr cis ment d ni et correctement rempli,
vous pouvez commencer la mise en place du TCD. Respectez
simplement les tapes suivantes.
Placez votre pointeur dans le tableau source. Il nest pas
n cessaire de s lectionner tout le tableau.
Cliquez sur le menu Insertion / Tableau Crois Dynamique.
Terminez par le bouton OK.
Excel afche alors une bo te de dialogue (gure 1.3) qui montre, dune
part, la zone compl te du tableau source dans la zone Tableau/Plage
et dautre part, la possibilit de cr er le tableau crois dans une
nouvelle feuille ou dans une feuille existante.
Figure 1.3 : Choix de lemplacement du TCD.
Si vos donn es sont issues dune source externe, vous devrez cocher
Utiliser une source de donn es externes puis renseigner la connexion.
La derni re option Ajouter ces donn es au mod le de donn es permet
dajouter des informations un dispositif qui facilite la mise en place
dun mod le relationnel entre des tableaux. Des outils sont
maintenant disponibles ( partir de la version Excel 2013) pour mieux
g rer les relations entre les donn es. D'une part, la mise sous forme
de tableaux, et d'autre part un compl ment appel PowerPivot. La
partie 2 du livre portera sur ce sujet.
La gure 1.4 ci-dessous montre l tape la plus int ressante dans la
r alisation de statistiques imm diates. La partie gauche est la partie
qui montre le r sultat du TCD. La partie droite est celle qui permet de
s lectionner les champs, cest- -dire les noms de colonnes du tableau
source.
Figure 1.4 : Les deux parties importantes du tableau crois dynamique.
Dune mani re g n rale, lorsque vous cochez nimporte quel champ
num rique, ce dernier se place automatiquement dans la case
Valeurs qui se trouve en bas droite des zones.
Vous pouvez n anmoins d placer ce champ num rique en le faisant
glisser avec la souris. Dans certains calculs, il est parfois n cessaire
de placer deux fois le m me champ num rique, une fois dans la zone
tiquettes de lignes et une autre fois dans la zone Valeurs, surtout
lorsquun calcul de d nombrement est n cessaire. Vous avez le droit
de placer autant de fois que n cessaire un champ num rique dans la
zone Valeurs. Lorsque vous cochez un champ texte, ce dernier se
place automatiquement dans la zone Etiquettes de lignes. Vous voyez
alors imm diatement le r sultat obtenu dans la partie gauche.
Comme le montre la gure 1.5, le r sultat est un regroupement par
mois et par r gion en fonction de lordre du placement des champs
dans la zone Lignes. Vous pouvez toujours modier la position des
champs laide de la souris.
Figure 1.5 : R sultat du tableau crois dynamique.
Ici,
le calcul correspond
la somme des quantit s vendues,
regroup es par mois et par r gion. Le tableau crois , dans cet
exemple, effectue deux regroupements dinformations et le total
g n ral est la somme des sous-totaux.
Il est tout fait possible de positionner autrement les mois ou les
r gions. Il suft de glisser lun ou lautre champ dans la zone
Colonnes.
Dans lexemple ci-dessus, les mois et les r gions sont plac s
verticalement. Le r sultat montre des r gions l g rement d cal es
vers la droite.
Cette organisation visuelle est param trable, et une fois les lignes ou
les colonnes d nies, les calculs sont r actualis s automatiquement.
On dit quils sont dynamiques.
Puisquil est possible de glisser plusieurs fois un m me champ
num rique dans la zone Valeurs (en bas droite de la partie r serv e
aux champs), vous pouvez indiquer Excel de changer la fonction
utilis e pour le calcul de chaque champ num rique. Ainsi on pourrait
calculer une somme dans une colonne, une moyenne dans une autre,
un cart dans une troisi me et ainsi de suite.
Pour r aliser ce genre de calculs multiples, vous utiliserez une bo te
de dialogue particuli re appel e Param tres des champs de valeurs ou
bien en cliquant directement avec le bouton droit de la souris, en
ayant pris la pr caution de placer le pointeur sur une valeur
num rique.
Une fois le rapport r alis , les donn es sources sont susceptibles
d' tre modi es. En effet, un tableau est "vivant" et souvent partag
entre plusieurs personnes. Dans ce cas, vous devrez actualiser le
tableau crois , car il ne se met pas jour automatiquement.
1.4 Mise jour du tableau crois dynamique
Lorsque vous modiez les donn es d'origine du tableau crois
dynamique, ce dernier ne se met pas jour automatiquement et cest
vous de le faire.
Pour effectuer cette op ration, suivez les instructions ci-dessous.
Placez le pointeur dans nimporte quelle partie du tableau
crois dynamique.
Donn es / Actualiser tout.
Cette technique permet de r actualiser tr s facilement tous les
tableaux crois s en une seule op ration. N anmoins, il est possible
de mettre jour un seul rapport en cliquant avec le bouton droit de la
souris dans le tableau, puis en choisissant loption Actualiser dans le
menu contextuel.
Attention : si votre pointeur sort du rapport de tableau crois
dynamique, la partie droite de l cran dispara tra. Replacez alors le
pointeur dans le r sultat du rapport pour faire r appara tre les
champs.
1.5 Voir les lignes la source du r sultat
Lorsquun tableau crois dynamique est r alis , vous pouvez afcher
rapidement les lignes d tails qui fabriquent le r sultat. Double-cliquez
sur nimporte quel r sultat num rique pour faire appara tre ces lignes.
Une nouvelle feuille sera cr e automatiquement pr sentant toutes
les informations qui ont servi au calcul. Celle-ci peut tre supprim e,
si vous le souhaitez, car elle est totalement ind pendante du tableau
crois .
Vous pouvez aussi d sactiver ce dispositif par loption Activer
lafchage des d tails. Excel vous pr viendra que vous ne pouvez pas
voir les lignes lorigine du calcul.
1.6 Un exemple horizontal
Dans la partie droite r serv e aux champs du tableau crois
dynamique, vous avez pu remarquer lexistence dune zone appel e
Etiquettes de colonnes dans laquelle il est galement possible de
glisser des champs.
Si vous placez un champ dans cette zone,
les r sultats se
propageront alors horizontalement, contrairement la zone Etiquette
de lignes o les valeurs sont plut t orient es verticalement.
La gure 1.6 montre un autre exemple de tableau crois dynamique
avec une pr sentation diff rente sous la forme horizontale. Jai ajout
les encadrements et le centrage des valeurs, qui ne seffectuent pas
automatiquement.
Vous pourrez remarquer sur la ligne 3 lintitul Etiquettes de colonnes
qui nest pas particuli rement explicite. Vous pouvez toujours
modier " la main" un en-t te pour plus de clart . Par exemple, on
pourrait mettre le mot R gions. Une fois le rapport cr , vous pouvez
modier sa pr sentation volont en fonction de vos besoins.
Observez aussi les petites fl ches dirig es vers le bas sur le rapport
de tableau crois . Elles vous permettent de ltrer et de trier les
donn es. Les ltres peuvent ainsi sappliquer aux valeurs, aux textes
ou encore aux dates. Plusieurs options concernant les tris sont
disponibles. Il suft de les s lectionner.
Figure 1.6 : Des donn es afch es horizontalement.
Lorsque vous choisissez de d nir un ltre, une case cocher
S lectionner tout propose de d cocher (ou non) automatiquement
toutes les autres valeurs existantes dans la liste. Gardez cependant
lesprit quil est plus efcace dutiliser
chronologiques ou textuels pour ltrer les informations.
les ltres num riques,
1.7 Le filtre du rapport
Vous pouvez constater lexistence dune autre zone appel e Filtre ou
Advertisement
Filtre du rapport, qui facilite la mise en place de ltres lint rieur du
rapport crois . En glissant par exemple le champ R gions dans cette
zone Filtre, Excel va ajouter une ligne dans la partie haute du r sultat
en vous laissant la possibilit de choisir une ou plusieurs r gions.
La gure 1.7 montre ce dispositif qui peut tre int ressant dans
certains cas de ltrage personnalis .
Figure 1.7 : Le ltre de rapport.
Noubliez pas de cocher la case S lectionner plusieurs l ments si vous
d sirez distinguer plusieurs informations. Remarquez galement la
disparition du champ R gion dans la zone Etiquettes de colonnes.
Pour supprimer le ltre de rapport, glissez-le de nouveau dans la zone
sup rieure ou bien dans la zone Etiquette de colonnes. Une fois le
tableau r alis vous pouvez modier volont sa pr sentation gr ce
au menu Cr ation.
1.8 Le menu Cr ation
Ce menu pr sente les possibilit s dafchage une fois le tableau
crois dynamique r alis . Trois groupes sont disponibles :
Disposition.
Options de styles de tableau crois dynamique.
Styles de tableau crois dynamique.
Ces dispositifs permettent dam liorer la pr sentation du rapport, par
exemple en ins rant des lignes vides par groupe de valeurs r p t es,
ou bien en d calant lafchage. La gure 1.8 montre le menu Cr ation
et diff rentes mani res dafcher
le rapport avec des styles
pr existants.
Figure 1.8 : Le menu CR ATION am liore la pr sentation.
En d pla ant simplement la souris au-dessus des diff rents styles, le
rapport prend la forme choisie. Rien nemp che par la suite de
formater les cellules en y pla ant un signe mon taire, un alignement
particulier ou un encadrement personnalis . Vous retrouverez bien
entendu tous ces dispositifs de formatage dans le menu Accueil.
Essayez de cocher les param tres Listes bande, Colonnes bande,
En-t tes de ligne et En-t tes de colonne. Ces options rajoutent des
bandes de couleurs dans le rapport. Il ne sagit ici que de param tres
dafchage. Loption Lignes vides propose dins rer ou de supprimer
des lignes vierges apr s chaque l ment, cest- -dire apr s chaque
rupture.
Les param tres du groupe Disposition sont int ressants pour modier
la pr sentation du rapport de tableau crois dynamique et sont au
nombre de quatre : lignes vides, disposition du rapport, totaux g n raux
et sous-totaux.
La gure 1.9 montre le rapport pr c dent avec des lignes vierges
ins r es. Vous pouvez galement disposer votre rapport de tableau
crois dynamique de plusieurs mani res : sous forme compact e, en
mode plan ou bien sous forme tabulaire.
Figure 1.9 : Insertion de lignes vides apr s chaque l ment.
Il est possible de r p ter plusieurs fois certains l ments de groupe.
Par exemple, en r p tant le nom du mois sur toutes les lignes c t
de la colonne des r gions (gure 1.10).
Figure 1.10 : Afchage des l ments de groupe en mode plan.
Pour effectuer cette op ration, s lectionnez Cr ation / Disposition du
rapport puis choisissez lafchage en mode Plan et enn indiquez le
param tre R p ter toutes les tiquettes d l ments.
Une autre mani re de pr senter les donn es est de modier la
position verticale des sous-totaux ou des totaux g n raux dans le
tableau crois dynamique.
Lorsque vous r alisez pour la premi re fois un rapport de tableau
crois , le total des groupes de donn es est plac dans la partie
sup rieure, au-dessus des items. Si vous d sirez placer ce sous-total
en dessous du groupe, employez le param tre Cr ation / Sous-totaux /
Afchez tous les sous-totaux en bas du groupe.
Vous pouvez voir le r sultat sur la gure 1.11. Constatez que le total
de janvier est plac sur la ligne 7, le total de f vrier sur la ligne 12 et le
total de mars sur la ligne 17.
Figure 1.11 : Les sous-totaux sont plac s en dessous du groupe des donn es.
Tous ces totaux de groupes sont afch s sous les d tails du groupe.
Une derni re possibilit est lafchage ou non du total g n ral. Il
repr sente la somme des totaux interm diaires et peut tre plac soit
verticalement, soit horizontalement en
fonction des donn es
pr sent es. Gardez lesprit que le nom des colonnes peut toujours
tre modi si cela sav re n cessaire.
En r sum
Dans ce chapitre, vous avez tudi la mani re de cr er des rapports
de tableaux crois s dynamiques et vous avez pu constater la facilit
avec laquelle des statistiques peuvent tre r alis es. Gr ce au menu
Cr ation, vous pouvez am liorer la pr sentation des donn es et des
calculs.
Le chapitre 2 sint ressera aux param trage des calculs et en
particulier aux param tres des champs de valeurs. Vous y
d couvrirez la mani re de r aliser des statistiques plus labor es et
de mani re automatique.
Chapitre 2
Param trer les calculs
Ce chapitre expose la mani re de r aliser des calculs lint rieur des
tableaux crois s dynamiques et dutiliser des fonctions de calculs
labor es. Vous tudierez galement la fa on de compter du texte qui
se r p te dans une ou plusieurs colonnes.
se
ce
dernier
2.1 Utiliser des fonctions de calculs Lorsque
vous s lectionnez un champ num rique au
moment de la cr ation dun tableau crois
dynamique,
place
automatiquement dans la zone Valeurs. Excel
calcule alors imm diatement la somme de ce
champ. Pour calculer autre chose quune
somme (par exemple une moyenne, un cart
type, un maximum ou un minimum) vous devrez
faire appara tre une bo te de dialogue appel e
Param tres des champs de valeurs. Cest dans
cette bo te de dialogue que vous choisirez la
fonction qui vous int resse.
Voici diff rentes m thodes que vous pouvez appliquer pour faire
appara tre cette fen tre.
En bas droite de la zone Valeurs se trouve une liste
d roulante qui vous permet dacc der cette bo te de dialogue
Param tres des champs de valeurs.
Vous pouvez aussi double cliquer sur len-t te de la colonne qui
afche la somme.
Une autre m thode est de placer le pointeur sur le r sultat
dune somme, de cliquer sur le bouton droit de la souris, et
enn de choisir loption Synth tiser les valeurs par. Diff rents
calculs sont alors possibles.
Remarquez le param tre Autres options qui vous renvoie vers la bo te
de dialogue Param tres des champs de valeurs. La gure 2.1 montre
ces deux fa ons rapides dacc der aux param tres des champs de
valeurs. Cette bo te de dialogue est une des mani res de r aliser des
calculs automatiques.
Figure 2.1 : Acc s aux param tres de champs de valeur.
Pour calculer des op rations en utilisant plusieurs colonnes, vous
emploierez loption de menu Champs, l ments et jeux. Cette option se
trouve dans le menu Analyse partir dExcel 2013. La liste suivante
montre les fonctions disponibles dans cette bo te de dialogue
Param tres des champs de valeurs (Figure 2.2).
Somme. Effectuer la somme dun groupe de donn es.
la moyenne dun groupe de valeurs
Nombre. Compter (d nombrer) le nombre de fois quune cha ne
de caract res identiques appara t au sein dun groupe de
donn es.
Moyenne. R aliser
num riques.
Advertisement
Max. Trouver la valeur la plus grande dans un groupe de
valeurs num riques.
Min. Trouver la valeur la plus petite dans un groupe de valeurs
num riques.
Produit. Effectuer la multiplication de toutes les valeurs au sein
dun groupe de valeurs.
Chiffres. Compter le nombre de fois quun chiffre appara t dans
une liste.
le diviseur est un nombre
Ecartype. Dans
s lectionn de mesures, cest dire un chantillon. Le diviseur
est n-1.
Ecartypep. Dans la formule, le diviseur est le nombre complet
de mesures, cest dire la population enti re. Le diviseur est n.
Var. La variance dun chantillon est une mesure qui
caract rise la dispersion dun chantillon ou dune distribution.
Une variance de z ro indique que toutes les valeurs sont
identiques. Une faible variance montre que les valeurs sont
proches les unes des autres. Une variance lev e indique que
les valeurs sont tr s cart es.
Varp. Variance dune population.
la formule,
Figure 2.2 : Les fonctions disponibles dans les param tres des champs de valeurs.
Il existe dautres possibilit s de calcul dans la bo te de dialogue
Param tres des champs de valeurs au niveau de longlet Afcher les
valeurs. Vous pouvez y calculer des pourcentages par ligne ou par
colonne, des pourcentages par groupe, des diff rences entre
plusieurs donn es ou des r sultats cumul s ainsi que des valeurs
dindex.
Ces techniques seront tudi es plus loin avec des exemples pr cis.
Enn, pour terminer avec les calculs, loption de menu Analyse /
Champs l ments et jeux facilite la cr ation de nouvelles colonnes
bas es sur des champs existants avec des formules personnalis es.
Il faudra faire attention la notion d l ments de calculs et de
champs calcul s.
2.2 Compter des acronymes
Un acronyme est un sigle, une abr viation, un item qui peut se r p ter
dans une colonne dune feuille de calcul. Avec Excel vous pouvez
compter tr s facilement des items.
Pour d nombrer du texte, employez la fonction Nombre.
Pour d nombrer des chiffres employez la fonction Chiffres.
La plupart du temps, on cherche compter des mots sous
format texte.
Le tableau 2.1 montre une
liste daffectation dagents dune
administration dont les informations renseignent sur le nom de la
r gion, la direction, une date daffectation, le nom de lagent et son
grade.
On constate que les informations sont coh rentes et que des
statistiques sont r alisables.
Voici quelques questions qui trouvent toutes une r ponse gr ce
lapplication dun tableau crois dynamique.
Question 1 : combien y-a-t-il dinspecteurs et de contr leurs ?
Dans cet exemple, vous souhaitez compter le nombre de fois
quappara t le mot inspecteur et contr leur dans la colonne Grade.
Dans le tableau crois dynamique, vous devez placer ce champ Grade
dans la zone Lignes puis glisser ce m me champ Grade dans la zone
Valeurs.
Tableau 2.1: Les donn es source pour d nombrer des choses.
R gion
Direction
Date
Agent
Grade
AQUITAINE
DRH 22/03/2015 PAULE inspecteur
AQUITAINE
DRH 01/03/2015 JEANNE inspecteur
AQUITAINE COMPTA 13/04/2015 ROSETTE contr leur
AQUITAINE COMPTA 14/06/2016 AHMED inspecteur
IDF
IDF
IDF
IDF
DRH 07/03/2015 PIERROT contr leur
COMPTA 01/01/2016 ALAIN inspecteur
DRH 02/06/2015 ADELE contr leur
DRH 22/07/2015 CORINNE inspecteur
PACA
FINANCE 01/08/2015
JOE
contr leur
PACA
FINANCE 10/11/2015 RENE
inspecteur
La gure 2.3 montre la mani re utilis e par Excel pour calculer
imm diatement le nombre de mots demand s. Le champ Grade est
par cons quent plac deux fois.
Figure 2.3 : Le champ grade se trouve deux endroits.
Remarquez au bas de la fen tre lexistence dun bouton METTRE A
JOUR pr sent uniquement partir de la version Excel 2013. Ce
bouton nest actif que si vous avez coch le lien Diff rer la mise jour
de la disposition. Avec ce dispositif, le r sultat du tableau crois
dynamique ne se met jour automatiquement que lorsque vous
rajoutez des champs dans les zones. Il faut alors cliquer sur ce
bouton pour voir ces modications. Ce dispositif ressemble au calcul
manuel dans Excel.
Le d placement dun champ de type texte, directement dans la zone
Valeurs, entra ne automatiquement un d nombrement (un comptage)
du texte. Au contraire, en pla ant un champ num rique dans la zone
Valeurs, Excel calcule imm diatement sa somme.
Question 2 : combien y-a-t-il dinspecteurs et de contr leurs par
r gion et par direction dans le tableau 2.1 ?
Dans cet exemple, vous comptez des grades en fonction de deux
autres crit res. La gure 2.4 montre une disposition deux entr es.
Les champs R gion et Direction sont plac s dans la zone Lignes,
tandis que le champ Grade se place dans la zone Colonnes. Enn le
champ Grade est d plac dans la zone Valeurs. Comme ce dernier est
de type alphab tique, Excel y applique imm diatement une fonction
de calcul Nombre.
Figure 2.4 : D nombrement avec plusieurs crit res.
Comme vous pouvez le constater, Excel effectue facilement des
comptages sur des textes. Etudions pr sent la mani re de compter
des valeurs de type date.
la mani re de
2.3 D nombrer partir dun groupe de dates Ici,
regrouper des
tudions
informations en fonction dune ou plusieurs
parties de date. Par exemple, en reprenant le
tableau 2.1, vous cherchez compter
les
personnes affect es une direction particuli re
par ann e, cest- -dire en 2015 et 2016. On
pourrait affiner la recherche en calculant le
nombre dinspecteurs ou de contr leurs
affect s par r gion et par direction, par ann e
et par mois, ou encore par ann e et par
trimestre.
Excel sait r pondre toutes ces questions laide dun tableau crois
dynamique qui regroupe des parties de date. Le point suivant va
d tailler la fa on de regrouper automatiquement des dates par ann e
au sein dun tableau crois dynamique.
2.3.1 Regroupement par ann e
Dans cet exemple, vous souhaitez compter le nombre de personnes
Advertisement
affect es par ann e. Suivez pas pas les instructions.