Table Of ContentTravaux pratiques
avec
Excel 2007 et 2010
Saisie et mise en forme, formules
et exploitation des données,
courbes et graphiques
Fabrice Lemainque
Auteur et traducteur
Toutes les marques citées dans cet ouvrage sont des
marques déposées par leurs propriétaires respectifs
Mise en pages réalisée par ARCLEMAX
© Dunod, Paris, 2011
ISBN 978-2-10-056262-6
TABLE DES MATIÈRES
Avant-propos ........................................................................................................................................................................ 1
INTRODUCTION
Présentation des exercices ............................................................................................................................................... 2
L’interface d’Excel ............................................................................................................................................................... 4
Terminologie ......................................................................................................................................................................... 5
PRISE EN MAIN
T1P Lancement d’Excel ............................................................................................................................................................. 8
T2P Ouverture de fichier .......................................................................................................................................................... 9
T3P Enregistrement d’un fichier .......................................................................................................................................... 10
T4P Gestion d’un classeur ...................................................................................................................................................... 11
T5P Déplacement et copie de feuille de calcul .............................................................................................................. 13
T6P Navigation dans une feuille de calcul ....................................................................................................................... 15
T7P Sélection de cellules ........................................................................................................................................................ 16
SAISIE ET IMPORTATION DE DONNÉES
T8P Saisie de données ............................................................................................................................................................ 18
T9P Listes de données ............................................................................................................................................................ 20
1T0P Liste personnalisée .......................................................................................................................................................... 21
1T1P Styles de données ............................................................................................................................................................ 22
1T2P Travail sur les lignes et les colonnes ........................................................................................................................ 25
1T3P Validation de la saisie ...................................................................................................................................................... 27
1T4P Importation de données ................................................................................................................................................ 28
1T5P Importation de données depuis le Web .................................................................................................................. 31
FORMULES ET FONCTIONS
1T6P Saisie de formules ............................................................................................................................................................ 34
1T7P Opérateurs ......................................................................................................................................................................... 36
1T8P Référencement d’une cellule ...................................................................................................................................... 37
1T9P Cellules et plages nommées ........................................................................................................................................ 39
2TP0 Copier une formule ........................................................................................................................................................ 41
2T1P Copie de formule et type de référence .................................................................................................................. 42
2TP2 Saisie simultanée de plusieurs formules ................................................................................................................. 44
2TP3 Fonctions ............................................................................................................................................................................. 45
@
III
2TP4 Utilisation d’une fonction .............................................................................................................................................. 46
2TP5 Référencement d’une plage de cellules .................................................................................................................. 48
2TP6 Formules matricielles ...................................................................................................................................................... 50
2TP7 Correction des erreurs de formules ........................................................................................................................ 51
MISE EN FORME DES FEUILLES DE CALCUL
2TP8 Mise en forme d’une feuille de calcul (1re partie) ................................................................................................. 54
2TP9 Mise en forme d’une feuille de calcul (2e partie) ................................................................................................. 57
3TP0 Styles .................................................................................................................................................................................... 61
3T1P Style personnalisé ............................................................................................................................................................ 63
3TP2 Modification d’un style existant ................................................................................................................................ 64
3T3P Mise en forme conditionnelle ..................................................................................................................................... 65
3TP4 Tableaux Excel .................................................................................................................................................................. 69
3T5P Insertion d’autres objets ................................................................................................................................................. 72
3TP6 Commentaire .................................................................................................................................................................... 76
GRAPHIQUES
3T7P Création d’un graphique ............................................................................................................................................... 80
3TP8 Modification d’un graphique (onglet Création) .................................................................................................... 82
3TP9 Modification d’un graphique (onglet Disposition) ............................................................................................... 84
4TP0 Modification d’un graphique (onglet Mise en forme) ....................................................................................... 87
4T1P Modification des données source d’un graphique .............................................................................................. 90
4TP2 Courbes de tendance ................................................................................................................................................... 92
4TP3 Graphiques de tendance (sparkline) ....................................................................................................................... 95
TRAVAIL AVEC LES DONNÉES
4TP4 Tri de données ................................................................................................................................................................ 100
4TP5 Filtre de données ........................................................................................................................................................... 102
4TP6 Tableaux croisés dynamiques .................................................................................................................................... 104
4T7P Personnalisation d’un tableau croisé dynamique ............................................................................................... 108
4TP8 Graphique croisé dynamique ..................................................................................................................................... 111
4TP9 Segments ......................................................................................................................................................................... 114
5TP0 Partage de segments entre tableaux croisés dynamiques ............................................................................ 116
5T1P Impression d’un document (1re partie) ................................................................................................................. 118
5TP2 Impression d’un document (2e partie) .................................................................................................................. 120
Index ................................................................................................................................................................................... 123
@
IV
Avant-propos
S i, de tous les programmes bureautiques, c’est sans aucun doute le traitement de texte (Word) que j’emploie le plus, le
tableur (Excel) est pour moi une boîte à outils presque universelle à laquelle j’ai recours dans de très nombreuses
circonstances. Il peut s’agir d’emploi ponctuel pour effectuer une suite de calculs un peu complexes ou visualiser sous
forme de graphiques des données numériques, ou bien une façon de stocker des données probablement plus simple et
rapide qu’avec une base de données.
Mon père, appartenant à une génération venue aussi tardivement que courageusement à l’informatique, utilise pratiquement
chaque jour ce logiciel depuis plusieurs années pour gérer divers éléments de sa vie quotidienne. Bien que globalement très S
satisfait du produit, il me pose souvent des questions sur des points qui, bien que pouvant paraître simples pour un utilisateur O
averti, lui posent manifestement problème. C’est donc notamment en pensant à lui que j’ai conçu ce livre comme un manuel
P
pratique, qui revient dans un premier temps sur les aspects fondamentaux du logiciel, avant d’aborder des aspects plus
O
avancés.
R
Tout au long de cet ouvrage, nous supposons : P
-
T
• que vous maîtrisez déjà les techniques de base de l’emploi de Windows : réduction et agrandissement de fenêtre, emploi
N
de la barre des tâches Windows, clic, double-clic, clic droit, glisser-déposer, etc. Si tel n’est pas le cas, reportez-vous à l’aide
en ligne de Windows ou consultez un ouvrage dédié au système d’exploitation dont vous disposez ; A
V
• qu’une version d’Excel 2007 ou 2010 est déjà installée sur votre ordinateur. A
Vous trouverez sur le site Dunod, www.dunod.com, les fichiers source d’exercices employés dans ce livre, que
vous devrez télécharger et installer sur votre ordinateur. Je vous recommande de le faire dans un dossier dédié
dans Documents, sous le nom TPExcel.
@
1
Présentation des exercices
EXCEL 2010
Les fonctionnalités spécifiques à Windows
2010 seront précisées comme telles.
BLOC-NOTES
À chaque début d’exer-
cice un bloc-notes vous
indique le numéro du TP. Segments
Ce numéro est utile car il
TP
sert de référence pour 49
d’autres exercices.
Lecsr osiesgéms deyntnsa smoniqt uuense d dee fsa nçoounv teraèus tvéiss supeellcet.aculaires d’Excel 2010, qui permettent de fi ltrer les données des tableaux
INTRODUCTION PFTriecémhreipeqsru deiesm :rp éTlaoPlyi 4séa 6:t iToPn4 :9 _10d émbuint.uxtlessx
L’introduction procure des
indications sur l’exercice à
venir et contient un encadré
précisant les TP éventuelle-
ment prérequis et les fichiers
dctctndiihooooe’emannrmx r speg)b .era ercsrDreLi t c e euedsxu nu eetneore r emmdcml ie,c piepn elsssuwo.t iiyttwmeedé sewsa d àt.(ider oàéasu n acnt lécéiosoddléadrui----. fOeiDcd1thp eafetisnli t oscrl neehla srsaOl,d e mbecOe utnol i pvquCîottsrauteme ribAl eézsdl za e nTel dtaensiP oued uéf4 n iireactc9 .a(l hIronsD.boix)ges lialseéerusné artxeT es uT d.PrI lyn C4’yocups9nrlnnéieo_aqg r.im dsueslCeeééreigt l qbzi dqdmO uuesuyetisep n.e nxntszasiléetuos m.csgxnrue imsqelsra, s tueO daeecnia,renKt neslra.l,s eu vd cglleaeeoicn s c gAtshrsr 1eeeloe.zzs u - Crllpoaeeeepn sls apgoTc olarueairsestfte-s r s aLtcvi3eoha nnatmant bédplse eD ,ts Caiurqtaueaacun lntcâescéedc ,rg sholiaseosie pi srvqréips eeue lu.amdg’yu ytmeenonnzeuea t cnme ihctts iôeuqeT nrnuCy efepTo l eeRdnnLs, ec’ .aéc féflefiiliql éctlurhamae egtez oenp u stlucasu hpsr epIqnl a’CutéreealTréRî ntmlL ee,à set p nctueytô itpst Bé ecR usldéir qesesuéae luesaeozu-cn -
cceo2zlo cnonteeUàsd ànaDc n ôcs seteo étlgte eEmd r,ul ueel ’basnt uaatd nbtere lseelu’t oa xsanuu fg sfric elclereghotsm éic s coépeon noldtotueysnrx,n ntclaueehm esap liqF qrO ueueemute t,Gcii elhts.ar a snmàed gpcismh sqeeévunlea’tacl. ptDsipouéanrpr nallaeéît-s LtpiRde4otao eenrtum aanscîbétéah lslàre ee Dqa lap cc’puCéautoôipelnT oéutzcéuRsnmr r L y q lod)leeede.uie zsns’es éé ste a leové2gdntonm 0mynu en0céesenha5e nnafmas totva sm nsei vAqéczcapnuloé ne enenAectm é ttndinilegopane’a(un l ésofrtsn)feeoy ,i éc((élucâslhe acl)ciise.c qhh.t i o ueepUla uerln u Mzctlares has A etuqidocJ ,ruiôM ut ieplnoc’ AéuhenlJle ieén)sds me eMectlt lyel efiAdqipn lJttiuet.ers asejc2o gzh0si nené0s tliaeus0q pcru,( --lea
@
114
@
2
Introduction
•
Introduction Présentation des exercices
TP 49 • Segments
CAPTURES
s Les copies d’écran présen-
e
é tées dans ce livre sont réali-
n sées avec Excel 2010 sur un
n
ordinateur exécutant Win-
o N
5fgraar6comi luCeprè péélàtiaerqr eipc uCefgSsoopue ooerndazrrmgrr de iigasmedsemuie rs seerorssee nuemil: tge nvtr s p modtébdl eluuldoeoee s ya cunc lsénha’tttoeios vsaongd. nemnlgmaez nEnpl etdeefs atfTnz û ablOy t cell lpeeee Tp taer syart u eilepbeoe gmetlc ne fmr nsiaaol: te ureidrsq ta neoércuget ur edOefTotlr yèeyi,sus ntspiléette aeui ll mesp dsée esay sit agqs neccu mugalla eimemqcte é.onuniegqioentno uzm Ttrs e yidus e.p Cua-sCen ra ’stePsé esla-et - Uenbms8xnmnaclemqeinimre sEbgaausoatnênlmeauniielemqtsirngs i séeèeunévct mCe tg rnseoerspip etooil.dtc.e uysio .osqnri ntyLlssoupEérseus néteesnrenr , ce asè Séczrrtd d zm lrda étcempvaeé yypieesssoniatlénuqzep eosss fua r su l ded idd pmenolqllereaieeeefgrb mu.n ii os scqgmslaststi peaePgyurhe sntezorguoltageèiteey onu mntu mlm usolentppea r sn iers seensreo,cat ne n alpysdpddavSmtdtltle esoopeéee tCecsrys er ugso sel mtial naedusreodacut titusébieonrie r fsp drrg fl und3eeéTéàseoeola ee.ar pmeuP ercle gutiueivs4nseaneem rereelb c,9tncsu gzrfsrpl euot. ie omx asnutradflàiétplma’stiaueys us,eéip s xnllr d esaed pecltt d.i esaarq, dea cyo bncspcualprnaiéosltnésréep ,a léér malse aomr’ do tàcveudlirêenoé tosit yiqm éfcpunogufniurenoo nnlseoeasedri n n smti m msu t deappOdaé iuzraoaeqb ise pst dnureull p tees-dyivagedèoo a-ue- -renu-z e - d sel ceva liavarT frdao2vtéaiouu0oerilttz we0lrrqe nee7rs u e ,c d7seemy.pi os fvsSfa.ruotéoirè ruqvgmessourin aeueptm rlesoad ,cps m’eesvoféxreeostdp nsuaeélêisoenzdt isre tepEetas zo x t diucuoddierfnnee--l INTROducTIO
ZOOM
Lorsque cela est né-
7 Dnooapnmtiso nalacs t fupeernolê pTtroyeps éeqe upsi a psra’ oCru acvterétegt,e or rfeeiemn. êpRtlreaemc.e Cazr lqisqouuueeszz L leeésng sdeuinvitdeere ss eluesr scdue’ésr sclaraai rnze o,s nolene st n réecccaeapsdtsuraérieeress
OLmddeKeie a .snlleo’otg gnmmugoeele dnPti tfa Oieraafrpfm iltceioèh ntnero, e dmmsé dsadoeiasrs nm css’e éalagtisa mz iCto elan’noteétc sgLc.aéosgriioeen.n Vd deo’ eudxsu aa mgurrionieuezpr pela uS besoigmîmtepe lden-et à la compréhension.
@
115
PUCE
Les puces des pas à pas
vous aident à suivre
l’ordre de lecture de
l’exercice.
@
3
L’interface d’Excel
D epuis Excel 2007, l’interface d’Excel se présente sous forme d’un Ruban. Les fonctions qui permettent d’agir sur les éléments
d’un document sont rassemblées dans des onglets, tandis que les fonctions de gestion générale du document se trouvent
en un emplacement unique situé à l’extrémité gauche du Ruban : le bouton Office pour Excel 2007 ou l’onglet Fichier
pour Excel 2010. Le bouton Office ou l’onglet Fichier permettent de passer en mode Backstage. Ce mode rassemble toutes les
fonctions générales de gestion d’un document ou classeur Excel, dont l’ouverture, l’enregistrement ou l’impression d’un classeur. Dans
ce livre, nous emploierons Excel 2010 : si vous possédez Excel 2007, cliquez sur le bouton Office chaque fois que nous demanderons
de cliquer sur l’onglet Fichier.
Barre de
contrôle
Galerie Barre de titre de la fenêtre
Onglet
Ruban
Barre de formule Groupe Bouton réduire
le Ruban
Bouton d’Aide
Bouton de contrôle
de la fenêtre
Feuille de calcul
Barre d’état
@
4
•
Introduction Terminologie
Terminologie
A vant de pouvoir employer efficacement Excel, il est indispensable de maîtriser certains termes :
• Un fichier ou document Excel est nommé classeur. Il contient une ou plusieurs feuilles de calcul (trois par défaut,
nommées Feuil1, Feuil2 et Feuil3), présentées sous formes d’onglets en bas de page.
• Une feuille de calcul est constituée de lignes (numérotées à l’aide de chiffres) et de colonnes (étiquetées à l’aide de
lettres).
N
• Une cellule est l’intersection entre une ligne (horizontale) et une colonne (verticale) de la feuille de calcul. Elle est identifiée
O
par la combinaison de la lettre de sa colonne et du numéro de sa ligne. Ainsi, la première cellule en haut à gauche de la
feuille de calcul est la cellule A1. De même, C4 désigne la cellule à l’intersection de la 3e colonne et la 4e ligne. I
T
• Vide par défaut, la cellule d’une feuille de calcul peut recevoir un contenu, classiquement nommé valeur. Une valeur c
peut être une valeur littérale numérique (3,1415927, 58 %, ou 1984) ou alphanumérique (Tableau2, Titre ou Nom) une u
formule (une expression représentant un calcul en fonction de données) ou même d’autres objets (images, sons, vidéos,
d
etc.). Excel propose un grand nombre d’opérateurs et de fonctions intégrées afin d’effectuer des calculs mathématiques,
O
statistiques, financiers, etc.
R
• Une cellule possède également un style, qui se décompose en style de valeur (dépendant du type de données considérées)
T
et style de cellule (indépendant du contenu). Par exemple, s’il s’agit d’un nombre, il est possible de choisir si Excel l’affiche
N
en tant qu’entier, en tant que nombre à virgule (et le nombre de chiffres après la virgule qui vont être affichés), avec
un symbole monétaire, comme pourcentage, etc. Une date peut également être affichée de diverses façons. Le style I
de cellule est indépendant de la valeur qu’elle contient : c’est une apparence visuelle particulière (police, taille, couleur,
bordure, image de fond, etc.).
• Une cellule Excel peut renfermer jusqu’à 65 000 caractères. La quantité de texte affichable dans une cellule dépend de
la largeur de la colonne qui contient la cellule, de la mise en forme de la cellule et de son contenu.
• Lorsqu’une feuille de calcul est active, une cellule au moins est active et porte donc le nom de cellule active. Elle est
généralement représentée avec une bordure plus épaisse, le plus souvent noire. Le contenu d’une cellule active est affiché
dans la barre de formule et son adresse (ou son nom si vous lui en avez attribué un) dans la zone d’adresse située en
haut à gauche.
@
5
