Chapitre 5:

Utilisation
d'EXCEL
pour
résoudre des
problèmes de
programma-
tion
linéaire Utilisation d'EXCEL pour résoudre des problè
Abdelaziz
CHE-
mes de programmation linéaire
TOUANI

Sommaire

Introduction
Abdelaziz CHETOUANI
Applications

École Nationale de Commerce et de Gestion - Oujda

Département de commerce
Recherche opérationnelle

7 décembre 2020

1/17
 Sommaire

Chapitre 5:
Utilisation
d'EXCEL
pour
résoudre des
problèmes de
programma-
tion
linéaire

Introduction
Abdelaziz
CHE- 1
TOUANI

Sommaire

Introduction

Applications
2 Applications

2/17
 Introduction

Chapitre 5: • Le solveur d'EXCEL est un outil puissance d'optimisation

Utilisation
d'EXCEL et d'allocation de ressources.
pour
résoudre des • Il peut aider à déterminer comment utiliser au mieux des
ressources limitées pour maximiser les objectifs souhaités
problèmes de
programma-

(telle la réalisation de bénéces) et minimiser une perte

tion
linéaire

Abdelaziz donnée (tel un coût de production). En résumé, il permet

de trouver le minimum, le maximum ou la valeur au plus
CHE-
TOUANI

Sommaire
près d'une donnée tout en respectant les contraintes qu'on
Introduction
lui soumet.
Applications
• Plutôt que de se contenter d'approximations , on peut faire
appel au solveur pour trouver la meilleur solution.
Quand utiliser le solveur
On utilise le solveur lorsqu 'on recherche la valeur optimale
d'une cellule donnée (la fonction économique) par ajustement
des valeurs de plusieurs autres cellules (les variables) respectant
des conditions limitées supérieurement ou inférieurement par
des valeurs numériques (c'est à dire les contraintes).
3/17
 Introduction

Chapitre 5:
Utilisation
d'EXCEL
pour
résoudre des

Exemple
problèmes de
programma-
tion
linéaire
Etudions un exemple avec le chier [Link]
Abdelaziz

Max f (x1 , x2 , x3 ) = 10x1 + 15x2 + 25x3

CHE-
TOUANI

sous les contraintes :

Sommaire

Introduction

x1 + 2x2 + 4x3 ≤ 20000


Applications

x1 + x2 + 3x3 ≤ 16000


3x + 5x2 + 3x3 ≤ 48000
 1

x1 , x2 , x3 ≥ 0


4/17
 Introduction

Chapitre 5:
Utilisation
Exemple
d'EXCEL
pour
Le problème peut être synthétisé sur cette feuille de calcul EXCEL :
résoudre des
problèmes de
programma-
tion
linéaire

Abdelaziz
CHE-
TOUANI

Sommaire

Introduction

Applications

• Les variables sont les quantités respectives des diérents investissements

(cellules jaunes).
• Les contraintes sont les valeurs imposées dans la donnée (cellules rouges).
• La cellule cible est celle contenant la formule exprimant la valeur à
optimiser (cellules bleues). An d'optimiser la fonction économique, nous
allons utiliser la commande Solveur... du menu Outil
5/17
 Introduction

Chapitre 5:
Utilisation
d'EXCEL
pour
résoudre des
problèmes de
Première étape : Congurer l'outil Solveur
programma-
tion
Si les commandes du solveur n'apparaissent pas encore dans le
linéaire
menu Outils, on déroule le menu Outils puis :
Abdelaziz
CHE-
TOUANI

Sommaire

Introduction

Applications

6/17
 Introduction

Deuxième étape : Spécications de la cellule cible

Chapitre 5:
Utilisation
d'EXCEL
pour
résoudre des
Dans la zone Cellule cible à dénir, on tape la référence de la
problèmes de
programma-
cellule que vous voulez minimiser, maximiser (c'est à dire la
tion fonction économique).
linéaire

Abdelaziz
CHE-
TOUANI

Sommaire

Introduction • Si on désire maximiser la cellule cible, choisir le bouton Max.

Applications • Si on désire minimiser la cellule cible, choisir le bouton Min.
• Si on désire que la cellule cible se rapproche d'une valeur donnée, choisir le
bouton Valeur et indique la valeur souhaitée dans la zone à droite du
bouton.
Remarques

• Aller plus vite en cliquant directement sur la cellule à spécier plutôt que
de taper sa référence au clavier.
• La cellule cible doit contenir une formule dépendant directement ou
indirectement des cellules variables spéciées dans la zone Cellules
variables.

7/17
 Introduction

Chapitre 5:
Utilisation
d'EXCEL
pour Troisième étape : Spécications des cellules variables
résoudre des
problèmes de On tape dans la zone Cellule variables les références des cellules
programma-
tion
devant être modiées par le solveur jusqu'à ce que les
linéaire
contraintes du problème soient respectées et que la cellule cible
Abdelaziz atteigne le résultat recherché.
CHE-
TOUANI

Sommaire

Introduction

Applications Remarques

• Aller plus vite en cliquant-glissant directement sur les cellules à spécier

plutôt que de taper leurs références au clavier.
• Il est probable que le solveur propose automatiquement les cellules variables
en fonction de la cellule cible. Controler que sa proposition n'est pas trop
exotique.
• On peut spécier jusqu'à 200 cellules variables.
• Dans le programme initial, on dénit les cellules variables par des zéros.

8/17
 Introduction

Chapitre 5:
Utilisation
d'EXCEL
pour
résoudre des
problèmes de
programma-
tion
Quatrième étape : Spécications des contraintes
linéaire

Abdelaziz
CHE-
TOUANI

Sommaire

Introduction

Applications

9/17
 Introduction

Chapitre 5:
Utilisation
Quatrième étape : Spécications des contraintes
d'EXCEL
pour
A l'aide des boutons Ajouter, Modier et Supprimer de la boîte
résoudre des
problèmes de
de dialogue, établissez votre liste de contraintes dans la zone
programma-
tion
Contraintes.
linéaire

Abdelaziz
CHE-
TOUANI

Sommaire

Introduction

Remarques
Applications

• Après avoir cliqué dans chaque case à compléter, il sut

de cliquer dans les cellules correspondantes directement sur
la feuille Excel. Puis pour conrmer par OK
• Une contrainte peut être une limité inférieurement (≤),
supérieurement (≥) ou limité aux nombres entiers
(opérateur ent ).
10/17
• La cellule à laquelle l'étiquette Cellule fait référence
 Introduction

Chapitre 5:
Utilisation
d'EXCEL
pour
résoudre des
Cinquième étape : Les options du solveur
problèmes de
programma-
tion
linéaire

Abdelaziz
CHE-
TOUANI

Sommaire

Introduction

Applications

Cette boîte de dialogue permet de contrôler les caractéristique

avancées de résolution et de précision du résultat. En général, la
plupart des paramètres par défaut sont adaptés à la majorité
des problèmes d'optimisation.

11/17
 Introduction

Chapitre 5:
Utilisation
d'EXCEL
pour
résoudre des
problèmes de
Cinquième étape : Les options du solveur
programma-
tion
• Modèle supposé linéaire : A cocher seulement si le système d'équations est
linéaire linéaire. Si la case est activée alors que le problème n'est pas linéaire,
Abdelaziz
EXCEL achera un message d'erreur pendant la résolution. En revanche, si
CHE- le problème est linéaire et que la case est activée, la résolution est plus
TOUANI rapide.
• Diérence entre problème linéaire et non linéaire : Sur un graphe, un
problème linéaire serait représenté par une droite. On trouve donc dans un
Sommaire

Introduction
problème linéaire des opérations arithmétiques simples comme : l'addition
Applications et la soustraction. Sur un graphe, un problème non linéaire serait représenté
par une courbe, traduisant une relation non proportionnelle entre les
variables du système. Le cas le plus courant est quand 2 variables du
système sont multipliées l'une avec l'autre.
• Acher le résultat des itérations : Interrompt le solveur et ache les
résultats produits par chaque itération. Cette option permet de suivre étape
après étape les diérents programmes de base.

12/17
 Introduction

Chapitre 5:
Utilisation
d'EXCEL
pour
Sixième étape : Résolution et résultat
Une fois tous les paramètres du problème mis en place, le choix
résoudre des
problèmes de

du bouton Résoudre amorce le processus de résolution du

programma-
tion

problème. On obtient alors une de ces réponses :

linéaire

Abdelaziz
CHE-
TOUANI

Sommaire

Introduction

Applications

Que faire des résultats du solveur

• Garder la solution trouvée par le solveur ou rétablir les
valeurs d'origine dans votre feuille de calcul.
• Créer un des rapports intégrés du solveur en sélectionnant
celui qui nous concernera.

13/17
 Introduction

Chapitre 5:
Utilisation
d'EXCEL
pour
résoudre des
problèmes de
Cinquième étape : Rapport des réponses
programma-
tion Au bas de l'écran, vous pouvez obtenir le rapport des réponses
linéaire
en sélectionnant la feuille correspondante :
Abdelaziz
CHE-
TOUANI

Sommaire

Introduction

Applications

14/17
 Introduction

Chapitre 5:
Utilisation
d'EXCEL
pour Cinquième étape : Rapport des réponses
Ce rapport donne l'évolution des cellules variables et de la cellule cible. On
résoudre des
problèmes de
programma- remarque donc bien qu'il y a eu une maximisation du bénéce.
tion
linéaire

Abdelaziz
CHE-
TOUANI

Sommaire

Introduction

Applications
Le rapport rappelle les diérentes valeurs des contraintes, leurs formules, et dans
quelle mesure elles ont été respectées.
• Lié : La valeur nale de la cellule contenant une contrainte atteint
eectivement la valeur maximum. Exemple : $B$10 devait-être <=16000
et vaut bien nalement 16000. La Marge (que l'on appelle temps mort) est
donc égale à 0.
• Non lié : La contrainte est respectée mais la valeur nale de la cellule n'est
pas égale à la valeur maximum ou minimum de la contrainte.
La Marge (temps mort) valent ici 0 pour les 3 contraintes.

15/17
 Applications

Chapitre 5:
Utilisation

Max f (x1 , x2 , x3 ) = 10x1 + 8x2 + 7x3

d'EXCEL
pour
résoudre des
problèmes de
programma- sous les contraintes :
tion

x1 + 2x2 + x3 ≤ 1000
linéaire


16x1 + 19x2 + 18x3 ≤ 18000
Abdelaziz


CHE-

x1 − x2 − x3 ≤ 100
TOUANI

x1 , x2 , x3 ≥ 0


Sommaire

Introduction

Applications

Dans sa basse-cour, un fermier peut tenir 600 volatiles : oies,

canard et poules. Il veut avoir au moins 20 canards et 20 oies,
mais pas plus de 100 canards, ni plus de 80 oies, ni plus de 140
des deux. Acheter et élever une poule coûte Dh. 3.-, un canard
Dh. 6.- et une oie Dh. 8.-. Ils peuvent être vendus Dh. 8.-, Dh.
13.- et Dh. 20.- respectivement. Comment ce fermier peut-il
réaliser un bénéce maximum ?

16/17
 Applications

Chapitre 5:
Utilisation
d'EXCEL
pour
résoudre des Dans une entreprise de nettoyage, chaque personne travaille cinq jours consécutifs
problèmes de
suivis de deux jours de congé. Il existe 4 catégories d'employés selon leurs jours de
programma-
tion
congé. Le salaire d'un employé varie selon la catégorie à laquelle il appartient :
linéaire

Abdelaziz
CHE-
Catégorie 1 2 3 4
TOUANI Congé Vendredi,samedi samedi,dimanche dimanche,lundi lundi,mardi
Salaire Dh. 5200 4800 5200 5600
Sommaire

Introduction
Les demandes quotidiennes en employés dépendent du jour de la semaine, suivant
le tableau ci-dessous :
Applications

Jour Lundi Mardi Mercredi Jeudi Vendredi Samedi Dimanche

Demande 25 18 41 41 30 18 24
Combien de personnes de chaque catégorie doit-on faire travailler de façon à
satisfaire la demande et à minimiser le coût du personnel ?

17/17