I Notions sur Excel

I
II - Le solveur
I
I
I )^' .ü t{*-
A) ExemPIe simPle :

T
Prenons un exemPle simPle :

Nous cherchons la solution de :

^r= h* *')= e
une valeur initiale à x et allons tâtonner
Pour résoudre ce problème, nous donnons
jusqu'à obtenir la relation cherchée'

(nous mettons 0'3, comme valeur de départ) ;

Nous allons placer x en A1, par exemple
-A1^2),dansA2; îJ l,' 'E' "'rr â-J^1,,".J-o *"
Nousécrivons::racine(1
demande :
Nous obtenons une fenêtre où l'on nous
Cellule à définir: mettons A2
(prenons ici, 0'25)
valeur à atteinclre: mettons la valeür de a
Cellule à modifier: mettons A1'
Cliquons sur OK.

Excel répond sur une nouvelle fenêtre

t fAU#= 4/ 99eto{02
Valeur cible : ^.
^0'25
Valeur actuelle :
A'250407215
Si nous regardons dans la feuille Excel
:

Dans A1, nous trouvons : a.9681406 2tç€?rYrç3

Dans A2, 0.2s040721

La solution est donc, x: 0'981406

Pourquoi Excel s'arrête-t-it à 0'250407215

?

C,estlaprécisionducalculquidécidedel,arrêtdesopérations;
i,,1,, -
tl
à lf1 h4fifiÀ è
Faisons 1 Outils-OPtions-Calcul Nl r'o" r'!v,".f
" L'
!"1sJr,l rq (ÿ
Ecart maximal : 0'001
Nous trouvons, dans le paragraphe " Itérations" "
Nous pouvons le changer : mettons.: 0'000001
et relançons le calcul :
 Notions sur Excel

nous trouvons : x: 0.968245065

valeur cible : 0'250002987

solution)

la parabole (P) d'équation : y x''r

:
Dans un repère orthonormal, on donne
( 4 0 )'
On donne le poinfA de coordonnées '
t, où t est un nombre reel positif'
On appelle fuf, f" poirit,aïüfuruUot.à'u6r"irr"
;;i;à.
, ,a distance AM est ro
minimale'
on se propo§e de cherôhprjpour q".[.
i
p"*-"ioË"a1",r d" t, quelie est la dîstance AM ?
:t4 + (t- 4)'
Il est facile de voir que AM2
Sa dérivée est : 4t3 + 2t - 8 t positif : la
est : 12ÿ + 2 etest donc positive qoY
Remarquons que la dérivée seconde (le t optimal
croissante dans la ,on" qui oorrr^inté[Link]
fonction dérivée est donc strictement
,gryIery:",I9sr1,I!)'Cettefonctiondérivéenes'annuleàoncqu'unefoispourt>0'
sera donc obtenu pour la dérivée
nulle'
Le minimum de AM'

DansAl,onmett;dansA2,mettonsunevaleurdedépartpourt'soit0
::A2^4 + (A2 - 4)"2
Dans 81, mettons AM2 ; dans B2'.mettons :
: Racine (82)
Dans Cl, mettons eM ;'du" C2"'mettons
i'éibit" dans D2' mettons i : 4* A2^3 + 2* Az - I
Dans Dl, mettons ;

Langons la ceûlule ciblè en mettatt

:
' Cellule à définir D2^ :
Valewàatteindre: 0
' i . ., : "''"''C"Ilule àmodifier : A2

Le résultat est : t:1.1281739 ', ,t* 1ù''^r

r'
D2:2.5825E-10
AM: 3.141233

Ilestdoncpossibledefairecalculerdesrésultatscomplémentaires(ici,AM),n'étant
de la cellule cible'
pu, Airectement inipiiques dans le calcul

I Exemple tiié de Excel-StarCalc de B' Egger'

 Notions sur Excel
60

r,l' Ur,ÿ,
fli ço(lrra, * !
.lÀ hr{ : ù,r!. \il
i|\
_ cq,""1dr"rtr,"t t7p.l*g;fi + a_ftU'
La méthode de la cellrule cible n'est qu,un cas particulier
de
ffi3H?iX*Rffi
le solveur Excel.
Nous allons voir maintenant comment se présente
cet outil.

A) Le calcul d'optimisation

Dans un problème d,optimisation, il y a :

une fonction que llon veut maximiser ou minimiser
; cette fonction est appelée,
en général, fonction objectif.

des uariabres de décision (que lon và faire varier)

des conditions que les variables de décision

doivent vérifier; ces conditions
sont appelées,, contraintes,, .

Le problème poséconsiste à "optimiser" (rendre maximum

ou minimum) la fonction
objectif, en faisant varier les variables àe décisioà r
celles-ci doivent vérifier les
contraintes. ""p""à*t

B) La nrogrammation linéaire

On appelle problème de programmation linéaire un problème

d,optimisation dans
lequel, la fonction objectif et les contraintes sont linéaires
par rapport aux variables de
décision.

Il existe trne méthode classique de résolution des problèmes

-- r-- de programmation linéaire
simples ; c'est la méthode du Simplôxe.

Dans les problèmes de progtammation non linéaire,

contraintes ou fonction objectif ou
les deux ne sont pas linéaires par rapport aux
àeié"irion. si la fonction objectif
n'est pas linéaire, il peut même arrivêr que les"[Link]
contraintes soient absentes.
Ce sera un de ces cas que nous [Link] expérimenter.
---- Notions sur Excel

ii;àH"â#';ffiïïJ":iîffi
ta'ectiiJn;; i" t'ti :|;
desceil
ffi g,:
' Ï*5[ti Ï,HË".J# x;;nt a
"nti",,'
îà',iur",'.nti:")tl'. de minimum) o,,
o"îoïà" 69* ï î:iiiiffi iîîilH"TJJH

*îh#tr;n*iil*:lixsH:'.'JËil:H'll,T,.rr,"u."
"*"|Ïil.Ë:i?î':*:i.5J;xi,ii:ï,?:#îiîï:ïh;î':i:ï'-
t^ Âaaqrt (rrn ieu de valeurs P

' - -':
res,aria-ùrl;S. glIi:i,, ,;;{.,îî*î'Ë,.î*l*. atÏ""àet'
",i;i'Ë[i;*" 1"-:1*imum
le programme
ne pei*"
lehaut, ,3.u**" î i" Ëà.*i"t
i;r,;;;, s,anête arr#,
àinimrm)' Le Pro^gr
ia rànction obiectir tr-:
iTÏ,#;ii"[' r :- du
r. point de départ est souvent
r,rcr'v "- - - '
ÊrI AnalYsl,*11:Ïîïro;;",litlloix
-
Vousÿellez'
Vous ÿeffez'en *
i-î l :;''--
obtentto'l-tt a* résultat'
i*p on# r;ryryy1r
"ii
|'

suivant :

à résoudre le système linéaire

Soit

+22 =14
lLx +3Y
i '-2Y+z=o
2z
[s' * 3Y + =17 de :
la recherche du minimum
le problème en
Nous allons transformer + +22-17)'
(5x 3V
(2x+ 3y + 2z'14)'+ (x - 2y + z- 0)'+
F(x, y, z)=

Laso,utiono::l:lîlf
:iii#îïJ*ï'*'etdon;
ton«
minimiser cette "Ti,'3,',ï,1::'#ffiil,,Tî:iJ:ff
:
Nous allons donc Poser

F(x.v z)::'"11::îïffiT:i[cision
X"! 'Z "-^ ^
'- ' ^-ô autre'
contrainte ^rrtfe
il nY a Pas de

A1' 81, Ct' mettont':1i'*::"'

B1'91'*"joli.â;"fu; soit: 1' 1' 1
Dans 41, --- départ'
(Jǧ vorv*" de
rnettons
Dans A2,Pi2,C2'
r" ca*é de E*:=
,î:i;:ii;il,--,'i.;,nr:Ë;tËi?B,"ir:ï1#:ï:,î:;:îJïË1
Dans A4 ro_go ?l;ffÏ:ïii1î;*i:Ë;$ill;gf1'.:HÏ:,ià"'
; en E4, calculurrD
membre tt:"i[Link],1i:;
1^ oê.^ôfld.e équation.
*êI: calcui Pour i;la troisième
8412 équation
85, F5, faisons 1" :il"; ;;;
*iîr +l '.7, F?
À0,86, D6' B?,
c6' B?. i?,'#:x fiffi:
 62
Notions sur Excel
:F4+F5+F6 : nous avons ici la valeur de la
F7, mettons la somme de F4' F5, F6 '
Dans
lonction objectif' t^nr,' -=' XQruu'rr
§ --) ["b+u, \\.-^.aS 1,t'l'**ts"^n*'
"/a(;t- lnw
Nous objectir: F7
^ff:;',,8:;ii;T*,u u définir,,menons ta fonction A21.C2
,oul"o.s des variables de décision..
Dals,,Cellules variablus,,, m"tto-,,s 1",
Nous t*"' Min'.''
"r,oi'i"onJuit"
Nous n'avons pas de contrainte à préciser'

de la
(notion analogue à l"'Ecart maximal:
*fm;,f'=lfmf:#*,* r---^ àÀ celui
"valeurcible"), ^o d"'Ecart
t"r r*iàut"s de décisio|,.^(terme analogue
une précision sur
"'*'"'-;;ma1" dans la "valeur cible")
ru âr"tio, objectif, qur
indique que si la fonction objectif
une [Link]
nevarieplusqued,unequantitéinfériàureàcettevaleur,lecalcul
s'arrête.-t,-^-^-2\-.st"atteinte.
Enfait,lecalculs'arrêtelorsquelapremièredecesconditionse cas ici)'
,,errr"i.î"îiîir"i i modèle linéaire (ce n'est pas notre
on peut ,r;;ïth"iri, r"
Conjugué'
Nt*tà";î; rnetnoa" du Gradient
la méthode a"
calcule pas à pas et
le.résultat des itérations"'le programme
En choisiss ant"Afficher le résultat de f itération
; il faut îu,n--"éon'inuet" poit "ii'
s,arrête apros ctraquel,iiiiurion"
suivante. r§ e= .-^.-lo \' L'?'
J"i f:]il,,l gu,,
Lançons le calcul
Noustrouvons
:
x:1gggg9::f i;tr]t9\;
l=,,,zzzz1l1t, Ê"t
.^AE f-t
oUje"tif pour valeur : l'7205
errarorrçrru,,,Ju1wv.r.
et la foiction a
ffi
\f.§7(;;\
.G 6\+ ,T)
14 solution du problème'
^^i^) S ^
L, E§L la ÿ-
C'est JvrsLrvrr r- n\r
*{no'-* {i**t}1ï
I F)Remarques: \,'"t^T;-
\**
i"= t
l a*,,,,,o;J:i;iï 1ffi'H,Xi,fi: ;â:îi";':§'q':r'1,"'Ë'îËiili{frl:{",.;;fl§i
l..ffiï:in otitis" r. *oàài"ttr.-.rrirr. pour repérer les y=g
ne fonctionn" qrr"
Le sorveur q^* tt '9o
l cellules.
Dans le sotveur Excel, on
a droit jus@?O-O ygt?P§: {*jgg§lol'

I
.-Ex-) 2-l:
I n"rfr"ttt des racines d'une équation'
-*---n""frercher
" entre 0 et 5 de l'équation :
la racine comprise
-L ,\- . .,. -'t ltçt'Lt'l
e' - 3. cos(x) .e-i = 0

I
fr ï,2) 6

! \
\\
 Notions sur Excel

J Z Z.
LX ^^1.
Piô6rc*" de PL
UncamelotvenddeschaussettessurlaPlaceduMarché'maisp'--
types de lots de
aguicher les acheteurs, il propose deux
chaussettes qu'il sait bien marcher
:

lot 1 : 4 Paires de chaussettes unies

2Paires de chaussettes à fleurs'
lor2: 8 Paires de chaussettes unies fleurs'
2Paires de chaussettes à
le lot 2 de 8 F' Son stock de
Le lot 1 lui laisse *ïenef'"t de 6 F'
chaussettes
j;là- tg4 paires de chaussettes unies et24 p,'-:=:'
de chaussettes à fleurs'
Ilsedemande,cedimanchematin'combiendelotsdetypeletde--:
à maximiser son bénéfic.'
de type 2 dJ-iütéparer [Link]çon
tous ses lots'
sachant, pu' t*pê'i"'ce, qu'il vendra
- Mettre ce Problème en équations:
sont évidemment le nombre de lots letdelots2à
Les inconnues
préParer, soit :
x: nombre de iots 1 à PréParer'
le bénéfice escomF:;
On écrira la fonction représentant
stock qui font que l'on ne peut
On écrit ensuite te, co"t'ait'tes de
1 et 100 lots 2'
PréParer 100 lots
2 -Résoudre avec le solveur'

Ex3]):
[e*t"tio" de sYstème linéaire
Résoudre le sYstème :
5.x + 2.Y + 3.2 + 5.u+ 2l = 7J
2.x - 3.Y + 6.2 -3'u + lZ'v = 49
6.x + 4.Y + l8.z - 5'u - v = -15 1
3.x+8'Y+6.2 + u- v =-14 Ir
7.x - 3.Y + 2.2 - 4'u + 6.v = 13
convient'
et vérifier que 1a solution trouvée
âJ::'ffi ,:# i: i: :ii#J ;';ff #ilàans 1' exerci ce Ex-3 - 1
1,_
2' La comparai sor
obtenues
portlïâ porter sur les écarts entre t",
.""o,iàt *t*Ut"t calculés avecles sotrutions
de ces écarts'
dans ces deux exercices. on
poltlÏa aussi étudier la somme des carrés

4:
-Ex3 2 de problème non linéaire
fe*f"iion :

,.\ tg U,o zî I
Rechercher x et Y e R tels que "

min {
aÿec i
x' + y' -z*Y }
l -: 4,ltta [ +
sY I
I xy <10

frr* 2y> 25
 Notions sur Excel 64
-
On prendra cofltme valeurs de départ : x:0.5, y:0.5.

Ex*3_2_5: -
t
-w\^t^r^ ilP u,[$n
Résolution de systèmes non linéaires
4- \
;) -x"§
1 - Résoudre le sYstème :
lZ.*.Y+3.:=13
I
T fl**,,
1r+y+y.z=9
I o^t a pSA"t
Ir.y*z=5 k
en prenant comme valeurs de départ : x:1, y-*1, z:I "QJ 1trvu,^'

2 - Refaire le calcul avec corlme valeurs de départ : x:10' y:10, z:l}' Au!.o*.L (

Ex_3_2_6: "J"rI
Inversion d'une matrice
La première colonne de la matrice inverse de A est la solution du
système : A . R: .{première colonne de I}
La deuxième colonne de la matrice inverse est la solution de
A . S : .{deuxième colonne de I}
etc...
On peut donc inverser une matrice en résolvant N (ordre de la matrice)
systèmes linéaires.
1 - Reprendre la matrice de I'exercice Ex 1 et f inverser avec le solveur.

5 3)
(T -t,+651 otL\{ 0,v0f \
A: 12 I 1l ci'{Ee'-0,qûSt -o,to61ll
[34 5) , rJ,I4 t3 \ . c, ,tqès
pour éviter certains problèmes d'effacement des formules, on écrira les 3 systèmes les
uns à la suite des autres et, ensuite, on appliquera 3 fois le solveur.
2 -Yénfier en comparantàlamatrice inverse déjà trouvée'
\
Ex_3 2J : , ')Lr
Ajustement de 'parametres {, i''\ t ti,-o
''{z pr't,u'L'L ç.'"''t'i ,)
Soit rn
I de points expérimentaux, donc efitachés d'erreurs de
".rr.Àble
mesure pour les valeurs Y.
(xr,yr) (xz,Yz) ...: (xN,Yrq)
i"r sont supposés exacts ou, du moins, beaucoup plus précis que les
",
ÿi'
Or rrit que les y sont reliés à x par une expression de la forme :

y:f(x)&,b-,c,.:.)
:
6ar exémple': y: ax3'+bxz + cx * d ou y ueo*+ cx -.. )
ùoor voulons déterrniner la valeur des coéfficients a, b, c, ... qui
donnent une courbe passant le plus près possible des points
expérimentaux.
f(x),
euelqueslois, on ne connaît pas, apriori, la forme de la fonction
mais I'examen du graphique des (x1, yi) condüt à faire une
hypothèse sur cette fonction : droite, exponentielle, " '
Pour détàrminer les paramètres a,b, c,.. ' nous dl6ns utiliser la
"méthode des moindres carrés", c'est à dire que I'on va
minimiser la somme des carrés des écarts entre la fonction
f(x1,a,b,c, ..) et les ÿi, soit :
r
Notions sur Excel

N. l
min Q =Zl,
i=1
- .f (*, , o, b,t, "')l

en faisant varier les a, b, c, "'

Le solveur va nous permettre de résoudre ca problème
:

la cible est la quantité Q, que I'on va minimiser ;

les cellules variu61"s sont les a,b, c, " '
Jr;;;;il"d;i;recherche des paramèties p et q de la fonction
y:p'x+q
poi"ï
"passant" le plus près possibb {"-: '
(0.2;r.010) io.i, '222) (1'2 ; 1's68)
r (1's ; 1'665) (1'8 ; 1'e61)
points donnés'
1
que les
des
- Repiésentation graphiquepoints sont presque alignés et que la
roncti":
recherche d'une fonctic:
rarecherche
2 -§:ï"::ffi:ïl;,3ffli3§i'î§:iir':ffilurig,,es
- Nous constatons
y:px*q
y--px+q I
est bien adaPtée.
Excer en réservant
construire votre ieuille Excel 2lignes pour les valeurs de p et q :
:
I
3 --.JJ*riJ#i:ti"iur. B2r
(A1 P 81 q Azt )
ùon r.r les noms p et q aux
..ffi:il:*îrJ,#;m**:'ii-*',iï'à"n
cellules A2 etBZ'
dans les
,,,,, au dessous, en laissànt la troisième ligne vide, mettre"dans
puis,
coronnes A et B les
res colonnes les I
aleurs de x et celles de Y'
valeurs
mefiôns les valeurs de la fonction y =
)ans la colonne C, mettons
Dans
px+qpx+q I
)ans la colonne D, mettons yi- Oxi
Dans O
+ q)
I
au catré'
Dans la colonne E, mettons ces valeurs
m;,*:rï';"î,îffîl:î:ti:$:1i::ii,"E:c',est,aquantitéQ
En E,3, faisons lu somme des valeurs de
4t - Appliquer le solveur pour déterminer
la colonne E : c'est la quantité Q.
p et q'
I
tenclanc"'.Jr-oî. de régression" obtenue
Excel'
dans Excel'
- ô;p;er
5; Comparer avec la courbe de tenclance^"droite
I

#ï:i,ï:îjr*,l3liffiiï.
lÏ,f**n*t;*î:"ffi
(déplacement maximum) d,une poutre encatrée
et [Link] upprryJ" a f'u*. Ëxtrémité

on mesure tes déplacemenrs .,

i::1# î : i:i,tr^
*
,Ià une extrémite

:ï,:îffi î)ii::,ï:,îî;ï:ffiîruneex'[rémi'leI
et soumise à un certain chargement'

I
larXrlru.î",, ,T:^."*T:u
transversaux de la poutre sont aonni;,'i'"- |
La théorie des poutres dit que les déplacements ,I

TI;3"ïLXTJ;*î*:"k f;poutre.
l#fJ,iî ; i,u,n"!" n**:;"*";; T", iu,*e;;.,'' l
2 -Endéduire la flèche de la la dérivée de y par rapport à x, 1
3_Uneétudeplusattentivenouspermetderemarquelqueladérivéedeypar,l
:
doit être nulle au point x 72 m' én,qrinn (comme contrainte cc,mplémentaire.
-^,,.,-ra équation
Utilisant le solveur et introduisant cette nouvelle
I
I

trouver la nouvelle équation du polynôme'

I

I
l

l
 .§
66
Notions sur Excel

Ex-3J-9 :

AjustementdeParamètres. , i a a

on veut trouver une expression analytique simple de la relation

:

o (Mpa) : f (e (%)) d'un barreau métallique'

Les mesures faites sont les suivantes :

11 58882 15550s5

une expression analytique Sous la forme

On pense que la meilleure solution serait d'avoir
suivante :
o: E. e sur un premier intervalle [ 0 ' cx ]
o: R € sur un deuxième intervalle [u,20 ]
1 - Représenter graphiquement les points
expérimentaux'
- rr -- ^ ^+ graphiquement, la
et --^,^Li^,,
2 -Détermrner les ;ô;i";r des 2 droite,
"r
rorr, fixant, à I'avance

valeur cx.