Les Tableurs

1
Les Tableurs
2
1. Que fait un tableur ?

• organise les données
• les traite
• les présente de différentes façons
• peut être relié à des bases de données.

3
Les composants

• données stockées dans des cellules
• chaque cellule à l'intersection d'une ligne et
  d'une colonne dans une feuille de calcul.
• Un classeur est un ensemble de feuilles de calcul.

4
(No Transcript)
5
Les références

• Chaque élément est désigné par sa référence

abc est situé à la référence B1
6
Valeurs, formules, fonctions et formats

• chaque cellule contient une valeur

• saisie par l'utilisateur
• 17 3,14 bonjour VRAI
• résultat d'une formule
• 5cos(3)2
• A1cos(B1)C14

• une valeur possède un type et est affichée sous
  un format

7
Formats des cellules
8
Formules références et fonctions

• une formule commence toujours par
• syntaxe précise
• utiliser des
• opérateurs lt gt
• constantes 2 OUI VRAI
• références de cellules A2, A1A10

9
Les formules de calcul
10
Références à une cellule

• Chaque cellule est référencée par son adresse
  lettre/nombre
• Exemple A1 C3
• Après Z AA AB ACAZ BA...

abc est situé à la référence B1 les 5 premiers
mois à la référence A3E3
11
(No Transcript)
12
Référence relative
C1C2
Cette formule peut-être obtenue par copier/coller
A1A2
13
Problèmes liés aux références relatives
14
Référence absolue ()
15
Ici, une référence mixte aurait suffit, laquelle ?
16

• Adresses relatives
• A1 référence relative pour la ligne, absolue
  pour la colonne
• A1 référence relative pour la colonne,
  absolue pour la ligne

17
Bilan

• La bonne question
• Si l'on doit exécuter le même calcul à
  différents endroits de la page, puis-je déplacer
  telle quelle ma formule ?

18
Référence par nom

• Nommer une cellule on peut reprendre l'exemple
  précédent et nommer la cellule B1 multiplicateur.

19

• Pour nommer une cellule
• utiliser le menu insérer un nom
• Pour l'utiliser
• utiliser le nom !

20
Portée des noms

• Un nom peut être utilisé dans tout le classeur
• Exemple feuille3janv!observation

21
Référence à une plage de cellules

• Plage de cellules un rectangle

B1D2 contient les 6 cellules
22
Les fonctions

• Les fonctions texte
• Les fonctions mathématiques
• Les fonctions logiques
• etc.

23
Fonctions texte

• Majuscule( moi )---gtMOI
• CNUM(1000 F)---gt1000

24
Fonctions date et heure

• Aujourdhui() ---gt 22 Fév 2001
• Mois(42) ---gt 02

25
Fonctions mathématiques

• Sin, Cos, Ln,.
• Arrondi(valeurnombredechiffres)
• Arrondi(3,141592 3) ---gt 3,142
• Somme(arg1 arg2 arg3argn)
• Somme(plage)
• Somme(5 7 8) ----gt 23
• Somme(A1A4) ---gt 10

26
Fonctions logiques

• Et(5lt7 totogttot) ---gt VRAI
• OU(5gt7 3gtcos(pi())) --gt VRAI
• Non(VRAI) ---gt FAUX
• Si(test valsivrai valsifaux)
• Si(3gt2 Plus grand Plus petit)---gtPlus
  grand
• Si(5gt6  bonjour   au revoir ) ---gt
  au revoir

27
Fonctions statistiques

• Max(A1A4) ---gt 6
• Max(A1 4 A2 A3) ---gt 5
• Min (A1 4 A2 A3 A4) ---gt 1
• Moyenne(5 3 10) ---gt6

28
Fonctions représentation en arbre

• À la racine le nom de la fonction
• Aux feuilles les arguments
• Ex Racine(cos(pi() (3/2))2)

29
Fonctions représentation en arbre

• Moyenne( (35)1 65-2 6)

30
Fonctions représentation en arbre

• Exp( (1/2) ( cos(0.4)2 / 0.1 ) )

31
Les Graphiques

• Possibilité d'exploiter les données sous forme
  visuelle courbes, histogrammes, camemberts
  (statistiques descriptives).
• Utilisation du menu assistant graphique

Le type de graphique utilisé dépend des données
étudiées
32
Les histogrammes

• Adapté pour les variables quantitatives continues
• (Age, Salaire, Chiffre daffaire, etc.)

33
Un graphique doit contenir
Un titre explicite
Graphique en secteurs
34
Les surfaces 3D
Représentation dune fonction à partir dun
échantillon de points
f(x)100.(x²-y)²(1-x)²
35
Les macro-commandes
' ' mise_en_forme Macro ' Macro enregistrée le
09/10/01' ' Sub mise_en_forme()
ActiveCell.Range("A1A4").Select With
Selection .HorizontalAlignment
xlCenter .VerticalAlignment xlBottom
.WrapText False .Orientation
xlHorizontal End With ActiveCell.Select
Selection.Font.Bold True
ActiveCell.Offset(1, 0).Range("A1A3").Select
Selection.Font.Italic True
ActiveCell.Offset(-1, 0).Range("A1A4").Select
With Selection.Borders(xlLeft) .Weight
xlThin .ColorIndex xlAutomatic End
With With Selection.Borders(xlRight)
.Weight xlThin .ColorIndex
xlAutomatic End With With
Selection.Borders(xlTop) .Weight
xlThin .ColorIndex xlAutomatic End
With With Selection.Borders(xlBottom)
.Weight xlThin .ColorIndex
xlAutomatic End With Selection.BorderAroun
d WeightxlThin, ColorIndexxlAutomatic End Sub
36
Les macro-commandes
37
Programmation
On peut créer des programmes via un langage de
programmation nommé VisualBasic (VB)
38
Le Solveur
Permet de répondre à la question  Que faut-il
faire pour que ? 
Pour cela on lui indique la cellule contenant le
résultat à obtenir, et les cellules intervenant
dans le résultat, qu'Excel aura le droit de
modifier.
Il adapte les valeurs des cellules que vous
souhaitez modifier, pour fournir le résultat
optimal spécifié à partir de la formule de la
cellule cible. On peut de plus spécifier des
contraintes pour limiter les valeurs
susceptibles d'être utilisées par le solveur.
39
Solveur exemple
Le solveur peut modifier les bugets trimestriels
consacrés à la publicité, dans la limite d'un
contrainte budgétaire de 40000, jusqu'à ce que
la valeur des bénéfices atteigne une valeur
maximale
40
Après exécution
41
Exemple
Réalisation de 2 gâteaux en déterminant les
ingrédients, le prix de revient et en optimisant
le bénéfice maximum.
Les oeufs coûtent 15 centimes, la farine 1.5
euroe le kilo et le lait 1euro le litre. Le
bénéfice est égal à deux fois le prix de
revient. Contraintes on dispose de 50 oeufs,
5kg de farine, et 5 litres de lait
Bénéfice maximal à partir de ces données ?
42
Activation du solveur
Dans Outils/Solveur ou Outils/Macros
complémentaires/Solveur
Cellule cible F6 , on veut le bénéfice maximal,
on coche max
Cellules variables on sélectionne le nombre
d'oeufs B4 et B5
Ici on suppose que la quantité des autres
ingrédients dépend du nombre d'oeufs
43
Contraintes
Dans la zone Contraintes, on clique sur Ajouter
On fixe le nombre total d'oeufs (B6) à 50
On effectue la même procédure pour les
contraintes sur la farine et le lait.
44
Solveur
On obtient finalement
On clique sur résoudre pour obtenir le résultat.
45
Résultat du solveur
Si les valeurs ne vous conviennent pas Cliquez
sur Annuler ou Rétablir les valeurs d'origine
Si les valeurs vous conviennent, vous pouvez
sélectionner des rapports (Réponses,
Sensibilité, Limites), cliquez sur OK
46
Résultat du solveur
Le résultat de l'optimisation est reporté dans la
feuille de calcul
47
Exemple de rapport
48
Modification de contraintes
Le résultat précédent vous propose une solution à
base de décimale d'oeufs, ce qui n'est pas facile
à mettre en oeuvre. Il est donc possible de
mettre en place des contraintes de type  telle
valeur est entière .
49
Exercices
50
Exercice 1
On veut la formule de C2 qui calcule F(X,Y)XAYB
On veut pouvoir copier la formule pour calculer C3


C2
A
2
E
2
B
2
F
2









Pour C3 on a besoin de A3,B3, E2 et F2, d où les

Note la formule  A2E2B2F2  marche aussi
car on ne change pas de colonne, mais c est
moins rigoureux!!
51
Exercice 2
On considère les colonnes A, B ,C, D, tel que
pour chaque ligne
Si A vaut 1 on veut 1 en B et 0 en C et D
Si A vaut 2 on veut 1 en C et 0 en B et D
Si A vaut 3 on veut 1 en D et 0 en B et C
Formule B1
SI(A111,0)
Formule C1
SI(A121,0)
SI(A131,0)
Formule D1
52
On veut pouvoir copier les 3 formules sur les
autres lignes.
On copie les formules vers le bas, donc celles de
la page précédente marchent.
Mais les colonnes ne changent pas, donc pour être
rigoureux
Formule B1
SI(A1110)
SI(A1210)
Formule C1
SI(A1310)
Formule D1
53
(No Transcript)