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