1
TD EXCEL
Jeremy ROY –sept. 2005Support TD
Click
to bu
y NOW
!PD
F-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
2
Généralités
•Qu’est-ce qu’un tableur ?•Les feuilles de calcul•Les fonctions•Les graphiques•Utiliser Excel comme une base de
données•Démarche globale d’un logiciel basé
sur le calcul matriciel
‡ Lancer le logiciel
Feuille de calculMicrosoft Excel
Click
to bu
y NOW
!PD
F-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
3
Interface logicielCl
ick to
buy N
OW!
PDF-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
4
Entrer et modifier des données
‡ Essai navigation et barre d’outils
Click
to bu
y NOW
!PD
F-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
5
Saisie de texte
‡ Essais de frappes de texte
Click
to bu
y NOW
!PD
F-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
6
Saisie de données numériques
‡ Dimensionnement des lignes/colonnes
Click
to bu
y NOW
!PD
F-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
7
Dates et heures(format/cellule)
‡ Essais « Format de cellule »
Click
to bu
y NOW
!PD
F-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
8
Cellules et références
F4:F5;G6:G7
Click
to bu
y NOW
!PD
F-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
9
Référence de cellules et deplages
NB : Il est possible de faire référence à des cellules de fichiers, classeurs ou de feuilleparticuliersEx : =[nomdoc]Feuil1!B4*Feuille3!C5(on multiplie la cellule B4 de la feuille 1 du fichier « nomdoc »avec la cellule C5 de la feuille 3)
Click
to bu
y NOW
!PD
F-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
10
Les formulesCl
ick to
buy N
OW!
PDF-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
11
Les opérateursCl
ick to
buy N
OW!
PDF-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
12
Ordre de traitement desopérations
Click
to bu
y NOW
!PD
F-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
13
Ajouter des formules de calcul
Exercice 1 (page 6 fascicule sur « feuille1 ») puis sauver
Click
to bu
y NOW
!PD
F-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
14
Copies incrémentées
Exercice 2 : faites ces 2 tableaux sur les feuilles 2 et 3
Exercice 3 : faites l’exercice du fascicule dans une quatrième feuille
Click
to bu
y NOW
!PD
F-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
15
Copies incrémentées avecréférence relative
Exercice 4 : faites cet exercice dans une cinquième feuille
Click
to bu
y NOW
!PD
F-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
16
Copies avec référence absolueCl
ick to
buy N
OW!
PDF-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
17
Fonction SICl
ick to
buy N
OW!
PDF-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
18
Exemple SI (1)Cl
ick to
buy N
OW!
PDF-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
19
Exemple SI (2)
Réelles Prévues
Click
to bu
y NOW
!PD
F-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
20
Fonctions logiquesCl
ick to
buy N
OW!
PDF-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
21
SOMME.SI
=SOMME.SI(A2:A5;"160 000";B2:B5)Somme des commissions relatives aux valeurs de propriétés
excédant 160 000 (63 000)
__> voir aussi NB.SI , mise en forme conditionnelle etc.
Description (résultat)Formule
28 000400 000
21 000300 000
14 000200 000
7 000100 000
CommissionValeur de propriété
BA
Click
to bu
y NOW
!PD
F-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
22
RECHERCHEVCl
ick to
buy N
OW!
PDF-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
23
Exercice ACl
ick to
buy N
OW!
PDF-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
24
Exercice BOffrir le cadeau A à tous les clients rapportant plus de 500000F de CA etle cadeau B à ceux qui représentent au moins 10000F de CA.Ne pas envoyer de cadeaux aux autres « petits clients ».
Click
to bu
y NOW
!PD
F-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
25
ETUDE de DOC (1/2)Cl
ick to
buy N
OW!
PDF-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
26
ETUDE de DOC (2/2)Cl
ick to
buy N
OW!
PDF-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
27
Exercices polycop
Feuille de calculMicrosoft Excel
Click
to bu
y NOW
!PD
F-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38
28
Formules complexessites web de référence
• http://excel.1.free.fr/Trucs%20&%20Astuces1.htm• http://ericrenaud.free.fr/droiteformules.htm• http://mapage.noos.fr/pnoss/trucsetastuces.html• Autres sites :• http://www.excel-vba-francais.com/formule-excel-
exemple.htm• http://www.admexcel.com/formules.htm (exemples)• Pour poser une question particulière et trouver une
réponse dans un forum de discussion :• http://www.commentcamarche.net/forum
Click
to bu
y NOW
!PD
F-XCHANGE
www.docu-track
.com C
lick t
o buy
NOW
!PD
F-XCHANGE
www.docu-track
.com
http://excel.1.free.fr/Trucs%20http://ericrenaud.free.fr/droiteformules.htmhttp://mapage.noos.fr/pnoss/trucsetastuces.htmlhttp://www.excel-vba-francais.com/formule-excel-http://www.admexcel.com/formules.htmhttp://www.commentcamarche.net/forumhttp://www.docu-track.com/index.php?page=38http://www.docu-track.com/index.php?page=38