Manuel de Travaux Dirigés

Cours :"Informatique I"

Excel outil d'aide à la décision

L3 GET
L3 ES

2024-2025

TD du cours de Sophie Martinez

A partir des supports réalisés par Maryse Salles, David Simoncini
 SOMMAIRE

MODE D'EMPLOI DE CE DOCUMENT .................................................................................. 3

PLANNING ........................................................................................................................ 4

LISTE DES BONNES PRATIQUES.......................................................................................... 4

LISTE DES EXERCICES ......................................................................................................... 5

CHAPITRE I INTRODUCTION A EXCEL FORMULES - FONCTIONS ....................................... 7

CHAPITRE II SIMULATIONS – FONCTIONS CONDITIONNELLES ET DATES ...................... 16

CHAPITRE III GESTION DE LISTES : FILTRES – RECHERCHES DE VALEURS ........................ 31

CHAPITRE IV TABLEAUX CROISES DYNAMIQUES .......................................................... 39

CHAPITRE V EXERCICES DE CAPITALISATION ............................................................... 43

CHAPITRE VI (FACULTATIF) SOLVEUR - FORMULAIRE ................................................... 46

CHAPITRE VII PROGRAMMATION VBA EXCEL ................................................................ 53

ANNEXE LOGICIELS LIBRES.............................................................................................. 64

Excel – Aide à la décision Page 2 UT1-Capitole, L3 GET & L3 ES

 Mode d'emploi de ce document

Ce document représente le support que vous devez utiliser pour faire

les TD du cours.
Il est constitué de plusieurs types d'éléments, présentés sur des fonds
de couleurs différentes :

- des apports (ou des rappels) sur les fonctionnalités d'Excel à savoir
mettre en œuvre : fond blanc

- des "bonnes pratiques" que nous vous conseillons dans l'usage d'Excel
ou d'autres tableurs : dans des ovales, fond vert

- des exercices :

❖ exercices d'application des apports à réaliser obligatoirement :

fond turquoise

❖ exercices dits de "capitalisation", à réaliser obligatoirement,

pour lesquels vous devrez vous-même identifier la ou les
fonctions que vous devrez utiliser : fond rose, précédés de
l'icône

❖ exercices facultatifs, que vous ne devez faire que si vous êtes

en avance sur le reste du groupe : fond gris, précédés de
l'icône

Excel – Aide à la décision Page 3 UT1-Capitole, L3 GET & L3 ES

 Planning

Séances de TD Contenu

1ère à 6ème Exercices Excel : 1 à 44 (certains facultatifs)

7ème Evaluation Excel

8ème Exercices VBA : 45 à 54 (certains facultatifs)

9ème Projet

10ème Evaluation projet

Liste des bonnes pratiques

Bonne pratique 1 : Pas de valeurs dans les cellules calculables

Bonne pratique 2 : Nommer les cellules de données
Bonne pratique 3 : Pas de valeurs dans les formules
Bonne pratique 4 : Vérifier que les résultats ont un sens
Bonne pratique 5 : Vérifier les valeurs avec le tri
Bonne pratique 6 : Pas plus de deux "SI" imbriqués
Bonne pratique 7 : Indenter les différents niveaux d'instruction
Bonne pratique 8 : Réaliser des jeux de tests complets
Bonne pratique 9 : Séparer les données des traitements

Excel – Aide à la décision Page 4 UT1-Capitole, L3 GET & L3 ES

 Liste des exercices

Exercice 1 : Manipulation préalable - Personnalisation de barres d’outils .................................................. 9

Exercice 2 : Création d’un tableau des ventes .......................................................................................................... 9

Exercice 3 : Mise en évidence de valeurs particulières ..................................................................................... 10

Exercice 4 : Compléments au tableau des ventes ................................................................................................. 14

Exercice 5 : Calcul de prix avec rabais...................................................................................................................... 15

Exercice 6 : Tableau de remboursement d'un prêt ............................................................................................. 16

Exercice 7 : Titre du tableau de prêt variable ....................................................................................................... 18

Exercice 8 : Visualisation graphique des données ............................................................................................... 18

Exercice 9 : Simulation par variation des données ............................................................................................. 19

Exercice 10 (Facultatif) : Visualisation des dépendances entre cellules .................................................... 19

Exercice 11 : Valeur cible : le montant de remboursement mensuel ........................................................... 20

Exercice 12 (Facultatif) : Protection d’une feuille ............................................................................................... 21

Exercice 13 : Résultat d'exploitation d'une soirée étudiante (table à simple entrée) ........................... 22

Exercice 14 : Table de donnée sur les montants de remboursement d’un prêt ....................................... 23

Exercice 15 : Résultat d'exploitation d'une soirée étudiante (table à double entrée) ........................... 23

Exercice 16 : Remboursement de l’emprunt (simulation sur durée et montant du prêt) .................... 24

Exercice 17 : Alerte quand la mensualité dépasse une valeur limite ........................................................... 25

Exercice 18 : Modification du tableau pour ne faire apparaître que les lignes utiles ............................ 25

Exercice 19 : Résultats d'examens (colonnes complémentaires) .................................................................. 26

Exercice 20 : Analyse de résultats d'examens ....................................................................................................... 28

Exercice 21 : Calcul de l'âge et de l'ancienneté ..................................................................................................... 29

Exercice 22 (Facultatif) : Mise en page et impression ........................................................................................ 30

Exercice 23 : Utilisation du filtre automatique ..................................................................................................... 31

Exercice 24 (Facultatif) : Tester le filtrage avec différents critères ............................................................. 33

Exercice 25 (Facultatif) : Filtre des ventes ............................................................................................................. 34

Exercice 26 (Facultatif) : Filtre des ventes vers un autre emplacement ..................................................... 34

Exercice 27 (Facultatif) : Filtre en affichant seulement certaines zones .................................................... 34

Exercice 28 : Tri des ventes .......................................................................................................................................... 35

Excel – Aide à la décision Page 5 UT1-Capitole, L3 GET & L3 ES

 Exercice 29 : Recherche des données Salariés ...................................................................................................... 36

Exercice 30 : Détermination de la tranche de salaire ........................................................................................ 36

Exercice 31 (Facultatif) : Recherche des résultats d'un concours ................................................................. 38

Exercice 32 (Facultatif) : Insertion de commentaires ........................................................................................ 38

Exercice 33 : Création d'un TCD ................................................................................................................................. 39

Exercice 34 : Groupage des données sur les champs de date .......................................................................... 41

Exercice 35 (FACULTATIF) : Groupage sur les données sur un autre champ ............................................ 41

Exercice 36 : Données agrégées par une autre fonction que la somme (ici : moyenne)........................ 42

Exercice 37 : Affichage des données en pourcentages ....................................................................................... 42

Exercice 38 : Actualisation d'un TCD (après modification des données d'origine) ................................ 42

Exercice 39 (exercice de capitalisation) : Achats ................................................................................................. 43

Exercice 40 (exercice de capitalisation) : Gestion d’hôtel ................................................................................ 44

Exercice 41 (exercice de capitalisation) : Etat des stocks................................................................................. 45

Exercice 42 (Facultatif) : Découvrir le SOLVEUR.................................................................................................. 47

Exercice 43 (Facultatif): Optimisation d'allocation de ressources ............................................................... 49

Exercice 44 (Facultatif): Réalisation d'un formulaire ........................................................................................ 51

Exercice 45 : Affectations de valeurs à des cellules ............................................................................................ 56

Exercice 46 : Utilisation de variables ....................................................................................................................... 56

Exercice 47 : Saisie d’une valeur par l’utilisateur ................................................................................................ 57

Exercice 48 : Que fait la macro suivante ? ............................................................................................................... 58

Exercice 49 : Structures conditionnelles multiples ............................................................................................ 58

Exercice 50 (facultatif) : Structures conditionnelles sur intervalles ............................................................ 59

Exercice 51 : Un feu d’alerte......................................................................................................................................... 59

Exercice 52 : La boucle For et les petites marionnettes .................................................................................... 61

Exercice 53 : Question pour un champion .............................................................................................................. 62

Exercice 54 : Liste de mots ........................................................................................................................................... 63

Excel – Aide à la décision Page 6 UT1-Capitole, L3 GET & L3 ES

 Chapitre I
INTRODUCTION A EXCEL
FORMULES - FONCTIONS

1 PRESENTATION D'EXCEL

1.1 Fenêtre EXCEL

Barre d’outils
1.1 Ruban
Accès rapide "Accueil"
Barre de menu
"Mise enOnglet
forme""Accueil" Groupe "Style"
1.2 "Mise en forme"
"Mise en forme" Barre de titre
Aide Excel

Barre de Numéro de
formule colonne

Curseur de
Zone de nom Cellule active fractionnement

Numéro de ligne

Boutons de Onglet de Ascenseurs Aperçu des

feuille Barre d’état
défilement page sauts de page
des onglets Mise en page
de feuille

1.2 Quelques icônes utiles

Attention : Ces icônes seront à utiliser dans les futurs exercices sans qu’on vous précise de le faire.

1.2.1 Barre d’outils "Accès rapide"

ICÔNE LEGENDE EXPLICATION
Permet d’enregistrer le classeur en cours. Au premier
enregistrement, choisir le dossier de sauvegarde (dans votre
Enregistrer
espace personnel) et un nom de fichier explicite.
Raccourci CTRL + S

Excel – Aide à la décision Page 7 UT1-Capitole, L3 GET & L3 ES

 Annule la dernière action réalisée (certaines actions ne
peuvent être annulées). Ce bouton permet de "remonter dans
Annuler
le temps" en annulant plusieurs actions précédentes.
Raccourci Ctrl+Z
Annule l'effet de la commande annuler. Utilisable sur
plusieurs actions annulées.
Rétablir
Permet également de reproduire une action (ex. : mise en
forme, insertion de lignes…). Raccourci Ctrl +Y

1.2.2 Onglet "Accueil"

Reproduit la mise en forme de la cellule ou de la plage
Reproduire la mise
sélectionnée (il faut d'abord sélectionner la cellule
en forme
"modèle", cliquer sur l'icône, puis cliquer la cellule "cible")
Renvoyer à la ligne Rend tout le contenu d’une cellule visible en affichant sur
automatiquement plusieurs lignes
Fusionner et Fusionne, en une seule cellule, la plage sélectionnée et
Centrer centre le contenu sur la cellule obtenue.
Format nombre Permet de choisir la devise (caractère €) le nombre est
comptabilité présenté avec deux décimales.
Style de Le contenu des cellules sélectionnées est multiplié par
pourcentage 100, le symbole % est ajouté.

Séparateur de Sépare les milliers et les millions avec un espace, rajoute

milliers 2 chiffres après la virgule.

Ajouter une
Ajoute une ou plusieurs décimales au nombre affiché.
décimale
Réduire les
Supprime une ou plusieurs décimales au nombre affiché.
décimales
Permet de mettre en évidence des cellules ou des plages
de cellules qui présentent un intérêt, en soulignant les
Mise en forme
valeurs inhabituelles. Permet de visualiser les données à
conditionnelle
l’aide de barres de données, de nuances de couleurs et de
jeux d’icônes.
Accès à la fonction SOMME (Elle somme par défaut des
cellules situées sur la même ligne, dans les colonnes
adjacentes à gauche, sinon dans la même colonne dans les
Somme lignes adjacentes supérieures.
La flèche permet l'accès :
* aux fonctions de base (MOYENNE,MAX, MIN …)
* à toutes les fonctions : choix "Autres fonctions…"
Permet de copier le contenu de la cellule en bas, à droite,
Remplissage
en haut à gauche ou de créer des séries
Supprime tous les éléments de la cellule ou uniquement la
Effacer
mise en forme, le contenu ou les commentaires.
Dispose les données afin de faciliter leur analyse (Trier
des données par ordre croissant ou décroissant ou
Trier et filtrer
appliquer un filtre temporaire (sélection des données à
afficher).

Excel – Aide à la décision Page 8 UT1-Capitole, L3 GET & L3 ES

 1.2.3 Onglet "Insertion"

Permet de choisir le type de graphique à utiliser

pour représenter la plage sélectionnée.

Exercice 1 : Manipulation préalable - Personnalisation de barres d’outils

L’objectif est ici de personnaliser la Barre d’outils d’accès rapide (tout en haut à gauche de
la fenêtre Excel – cf page 8) qui permet de garder affiché les boutons de commandes
courantes.
• Cliquer sur l’icône Personnaliser la barre d’outils d’Accès rapide

• Rajouter les commandes Nouveau, Impression rapide ceci vous permettra d’accéder
directement à l’impression.
Attention, sur le réseau de l'université l'impression par défaut est en fait la création
d'un fichier au format PDF.
Si vous ne trouvez pas la commande cherchée, pensez à regarder dans la catégorie
Autres commandes.

Exercice 2 : Création d’un tableau des ventes

Vous allez devoir réaliser le tableau suivant

1. Nommer
• Enregistrer le classeur Excel sous le nom Chapitre [Link]
• Renommer la feuille Ventes premier semestre (double-cliquer sur Feuil1 et modifier
le nom ou cliquer sur l'onglet avec le bouton droit, choisir Renommer).
2. Saisir les données :
• Le titre général
• Utiliser la recopie incrémentielle pour créer les noms de mois par recopie de la
première valeur : Écrire "janvier", puis sélectionner la cellule contenant "janvier",
placer la souris sur le coin droit bas de la cellule, le pointeur prend la forme d'une croix
noire dite "poignée de recopie", tirer sur 5 cellules.

Excel – Aide à la décision Page 9 UT1-Capitole, L3 GET & L3 ES

 • Les données du tableau dans les cellules sur fond blanc et le titre « Total semestre ».
3. Mettre des formules dans les cellules calculées automatiquement (cellules violettes)
• Utiliser dans l’onglet Accueil le bouton Somme en cellule H4 et B7 et la recopie
incrémentielle pour réaliser les totaux mensuels sur la dernière ligne et les totaux du
semestre sur la dernière colonne.
4. Mettre en forme
• Fusionner et centrer le titre général.
• Pour obtenir "Jean-Christophe" sur deux lignes : sélectionner la cellule, choisir dans
l’onglet Accueil l’outil Renvoyer à la ligne automatiquement. (faire de même pour la
cellule contenant "Total du mois".)
• Utiliser dans l’onglet Accueil / Nombre le Format nombre comptabilité pour les
cellules contenant les montants.
• Utiliser dans l’onglet Accueil / Police (ou dans le menu contextuel Format de cellule)
l’outil Bordures pour encadrer les cellules.
• Utiliser dans l’onglet Accueil / Police (ou dans le menu contextuel Format de cellule)
l’outil Couleur de remplissage pour les différentes cellules. Vous pouvez aussi choisir
des motifs et textures.
5. Réalisation d’un graphique
• Réalisez un graphique en tant qu’objet sur la feuille ‘Ventes premier semestre’ pour
les ventes mensuelles de mars, en respectant le modèle ci-dessous.
Attention : avant d’insérer le type de graphique voulu, vous devez sélectionner les 3
cellules contenant les noms des vendeurs puis, en gardant la touche CTRL appuyée
(sélection disjointe), sélectionner les cellules contenant les trois chiffres d'affaires
correspondants au mois de Mars.

Une fois le graphique inséré, utilisez :

- Le bouton pour respecter le

modèle ci-contre
- Changer la couleur des pourcentages en
cliquant dessus

Exercice 3 : Mise en évidence de valeurs particulières

Sélectionner les données numériques du tableau que vous avez réalisé (de B4 à G6)

Excel – Aide à la décision Page 10 UT1-Capitole, L3 GET & L3 ES

 • Utiliser dans l’onglet Accueil la Mise en forme conditionnelle et Règles de mise en
surbrillance des cellules.
Mettre les cellules <20000 € en remplissage rouge clair avec texte en rouge foncé
• Utiliser dans l’onglet Accueil la Mise en forme conditionnelle et Règles des valeurs de
plage haute/basse.
Mettre les cellules supérieures à la moyenne en remplissage vert clair et texte en vert
foncé (Vous pouvez vérifier en calculant la moyenne de la plage)
Attention : Les règles peuvent être ajoutées individuellement et leur effet se cumule alors.
Pour visualiser/modifier/créer un ensemble de règles il faut utiliser l’Option Mise en forme
conditionnelle/ Gérer les règles

2 NOTION DE REFERENCE
Les formules de calcul utilisent les références de cellules.

2.1 Références relatives

Elles s'ajustent lors de la recopie ou du déplacement des formules dans d'autres cellules.
Elles utilisent la lettre de la colonne et le numéro de la ligne.
ex : A1 (cellule à l'intersection de la colonne A et de la ligne 1)

2.2 Références absolues

Elles ne s'ajustent pas lors de la recopie ou du déplacement des formules dans d'autres
cellules. Le symbole $ sert à figer la valeur de la colonne et de la ligne.
ex: $B$4 (cellule à l'intersection de la colonne B et de la ligne 4).
Cette référence ne changera pas, même si on déplace la formule.

2.3 Références mixtes

Elles ne s'ajustent que partiellement lors de la recopie ou du déplacement des formules

dans d'autres cellules. Le symbole $ fige la valeur de la colonne ou de la ligne.
ex : $C4 (le numéro de ligne sera modifié lors de la recopie verticale mais le
n° de colonne ne sera pas modifié lors d'une recopie horizontale)
C$6 (seul le numéro de colonne sera modifié lors de la recopie)

2.4 Référence à une plage de cellules

Une plage est un ensemble de cellules : portion de ligne, portion de colonne, ensemble
rectangulaire de cellules.
• " : " est l'opérateur de plage
ex : Dans l’exercice 2, l’utilisation du bouton Somme en cellule H4 a inséré automatiquement la
formule =SOMME(B4:G4) c’est-à-dire la somme des valeurs de la cellules B4 à la cellule G4
• Références de plages abrégées
ex : A:A renvoie à la colonne A entière

Excel – Aide à la décision Page 11 UT1-Capitole, L3 GET & L3 ES

 2.5 Référence à d'autres feuilles

Syntaxe générale :
− pour les cellules d'une feuille du même classeur : Nom de feuille!référence
− pour les cellules d'une feuille d'un autre classeur : '[nom du classeur]nom de la
feuille'!référence

2.6 Référence par nom (nommer des cellules)

Il est possible de donner un nom à une cellule ou une plage de cellules (ensemble de
cellules) pour simplifier l’écriture des formules et les rendre plus compréhensible.
Pour une cellule : on clique sur la cellule et on inscrit le nom dans la "zone de nom" (voir
figure en page 8).
Pour une cellule ou un ensemble de cellules : les sélectionner et utiliser dans l’onglet
Formules la commande Définir un nom, ou la commande Depuis sélection (dans ce cas il
est proposé d’utiliser les libellés situés dans la ligne du haut ou dans la colonne de gauche
de la plage sélectionnée, la sélection doit donc inclure les libellés).

3 FORMULES ET FONCTIONS

L’automatisation des calculs est le principe de base d’un tableur comme Excel. Pour cela,
les cellules calculées doivent contenir des formules utilisant éventuellement des fonctions.

Les formules commencent par = et peuvent contenir :

− des valeurs constantes
− des références ou des noms de plages ou de cellules
− des opérateurs :
• arithmétiques + - * / % ^
• de comparaison = < > <= >= <>
• de texte & (concaténation)
• de référence ; (union de plages) ou caractère espace (intersection)
− des noms de fonctions EXCEL, suivis de leurs paramètres entre () et séparés par des ;
− des textes (commencent par des lettres ou par le caractère ' ) ; si par exemple on
souhaite écrire l'expression + de 55 ans dans une cellule, il faut écrire
'+ de 55 ans sinon Excel envoie un message d'erreur car sans le signe ' en début
d'expression, Excel traite + de 55 ans comme une formule dont il cherche à calculer la
valeur.

L'assistant fonction

Pour utiliser des fonctions que vous ne connaissez pas, ou que vous connaissez mal, utilisez
l'assistant fonction. Il est accessible depuis la barre de formules (bouton fx).

Excel – Aide à la décision Page 12 UT1-Capitole, L3 GET & L3 ES

 Avant d'appeler l'assistant fonction, on se positionne dans la cellule dans laquelle on veut
utiliser la fonction. Ici, nous allons utiliser une formule pour calculer le montant de
remboursement mensuel d'un prêt (fonction VPM).
L'assistant fonction a deux fenêtres.
La première fenêtre permet de sélectionner une fonction, en choisissant auparavant la
catégorie.

Une fois la fonction choisie, l'assistant donne une brève explication du résultat qu'elle donne, et de
sa syntaxe.
La deuxième fenêtre permet d'entrer les valeurs des paramètres, c'est-à-dire des références à des
cellules contenant ces valeurs. Sur la figure ci-dessous, on a déjà renseigné le premier paramètre
(Taux) en cliquant sur E1, et l'on se prépare à renseigner le deuxième (Npm). L’assistant indique
qu'un paramètre est obligatoire en le mettant en gras (ici : Taux, Nmp, Va), et donne un descriptif de
son contenu. Quand tous les paramètres obligatoires seront renseignés, l'assistant affichera le
résultat de la fonction dans cette zone

Si vous ne comprenez pas bien le fonctionnement de la fonction, allez voir l'aide associée, elle
comprend toujours des exemples qui pourront vous éclairer.

Excel – Aide à la décision Page 13 UT1-Capitole, L3 GET & L3 ES

 Exercice 4 : Compléments au tableau des ventes

Pour cet exercice à l'aide de l'assistant fonction, vous utiliserez cinq fonctions : SOMME, MIN,
MAX, MOYENNE puis ARRONDI et le nommage de cellule (point 2.6 page 13)

1) Placez-vous sur la feuille 2 et renommez-la Paramètre pour y mettre le taux de TVA (à savoir
20%) dans la cellule A1 et nommez la cellule TauxTVA

2) Complétez la feuille de l'exercice précédent en rajoutant la colonne Total TTC. Lors de

l’écriture de la formule, utilisez le nom de la cellule (TauxTVA) au lieu de sa référence.
Vous n'écrirez la formule du total TTC qu'une seule fois (pour la ligne de Pierre) puis vous la
recopierez en la tirant avec la poignée de recopie.
3) Rajouter les moyennes (sauf ligne 10), min, et max.

4) Calculez l'arrondi des moyennes du mois avec la fonction ARRONDI, de façon à obtenir la
ligne "Moyenne arrondie". Vous pouvez trouver cette fonction en utilisant le bouton Somme
puis Autres fonctions ou bien l’Onglet Formules puis choisir la catégorie Maths et
trigonométrie. Vous utiliserez l'aide sur la fonction pour connaître ses paramètres.

Bonne pratique 1

Ne mettez JAMAIS une valeur dans une cellule si celle-ci peut être calculée
par une formule.

Excel – Aide à la décision Page 14 UT1-Capitole, L3 GET & L3 ES

 Exercice 5 : Calcul de prix avec rabais

Question 1)
Dans une nouvelle feuille nommée Calcul de prix avec rabais, faire un tableau comme montré
sur la figure suivante :

A) Nommez les données à fixer en ligne 4.

B) Calculer les prix hors rabais pour les trois clients en écrivant une seule formule (dans la
cellule C10) que vous recopiez ensuite vers le bas.
Vous devez obtenir les résultats donnés sur la figure plus bas.
C) Calculer les prix avec rabais pour les trois clients en utilisant les divers taux de rabais. Pour
chaque colonne de prix avec rabais, vous écrirez une seule formule (en D10, E10, et F10) et
vous les recopierez vers le bas.

Question 2) Facultative
A) Faire un deuxième tableau sous le premier comme suit

B) Refaire le calcul des prix avec les taux de rabais 1, 2 et 3 en écrivant une seule formule
dans la cellule D17. Vous recopierez ensuite la cellule D17 vers la droite puis vers le bas.
Aide : Vous devez utiliser cette fois ci les références mixtes (point 2.3 page 11)

Excel – Aide à la décision Page 15 UT1-Capitole, L3 GET & L3 ES

 Chapitre II
Simulations – Fonctions conditionnelles et dates

1 SIMULATIONS
Excel est aussi un outil de simulation. Il existe différents niveaux dans Excel :
1) Modifier la valeur d'une donnée qui influence le résultat d'une formule et constater le
nouveau résultat
2) Rechercher la valeur cible
3) Rechercher la valeur cible dans une table de données (à 1 ou 2 entrées)
4) Au Chapitre VI (facultatif) est abordé le solveur : à utiliser quand on recherche la
valeur optimale pour une cellule par ajustement de plusieurs autres cellules qui
influencent directement ou indirectement le contenu de la première cellule.

1.1 Simulation par modification de valeur

Exercice 6 : Tableau de remboursement d'un prêt

Dans un nouveau classeur que vous enregistrerez et nommerez « Tableau [Link] »,

créer :
A) Une feuille Données contenant les données suivantes

- Donner un nom aux cellules contenant : le montant du prêt (B1), le taux annuel (B2),
le nombre de mensualités (B3), le taux mensuel (E1) et le remboursement mensuel
(E2).
ATTENTION : Seules les cellules blanches contiennent des données ou des libellés à saisir
tels quels.
Toutes les cellules en jaune contiennent des formules !

Formules et fonction à utiliser :

1) Taux mensuel = taux annuel /12
(Nota : formule simplifiée, il ne s’agit pas ici de la formule du «taux mensuel
équivalent»)
2) Remboursement (mensuel) = VPM(Taux mensuel ; Durée en mois ; - Montant du
prêt)
Nota : VPM est une FONCTION

B) Une feuille Tableau remboursement contenant le tableau de remboursement du prêt (page

suivante)

Excel – Aide à la décision Page 16 UT1-Capitole, L3 GET & L3 ES

Formule différente
dans la 1ère cellule
de cette colonne Formules à étirer
pour une recopie
incrémentielle dans
ATTENTION :
la colonne
Mettre la formule
= cellule
précédente +1

Formules et fonction à utiliser :

3) Montant départ (pour le premier paiement) = Montant du prêt
4) Montant départ (pour les autres paiements) = Reste dû du mois précédent
5) Part des intérêts = Montant départ * Taux mensuel
6) Part du capital = Remboursement mensuel - Part d'intérêt
7) Reste dû = Montant départ - Pa rt du capital

Bonne pratique 2
Prenez l'habitude de nommer les cellules notamment celles
contenant des données primaires (c'est-à-dire des données
non calculées).

Bonne pratique 3
Ne mettez JAMAIS une valeur dans une formule, mais toujours
des références à des cellules.
Les seules exceptions acceptables sont les valeurs liées à la mesure du
temps, qui ne sont pas destinées à changer (il y a 7 jours dans une
semaine, 52 semaines ou 12 mois dans une année, etc.).

Excel – Aide à la décision Page 17 UT1-Capitole, L3 GET & L3 ES

 Texte variable
Une cellule peut contenir un texte qui varie automatiquement en fonction du contenu
d’une autre cellule.
Dans l’exemple ci-dessous, le titre change en fonction du contenu de la cellule B1:

Pour cela, on utilise le caractère & (appelé "et commercial"), qui permet de concaténer
(coller) deux chaînes de caractères, ou bien des caractères et des chiffres.
Dans la cellule B3 la formule est : ="les " & A1 & " travaux d'Hercule"

Exercice 7 : Titre du tableau de prêt variable

Insérez deux lignes en haut de la feuille contenant le tableau de prêt.

Dans la première ligne, saisissez le titre ci-dessous, en faisant apparaître le nombre de mois
qui correspond à la durée en mois. Attention, ce nombre de mois varie en fonction du contenu
de la cellule contenant le nombre de mensualités
Ainsi le titre qui va s’afficher est
Mais, si vous modifier le contenu de la cellule de la durée en mois à 36 (feuille Données) par
exemple, il affiche automatiquement

Exercice 8 : Visualisation graphique des données

Réaliser dans une nouvelle feuille Visualisation, un graphique permettant de visualiser la part
de capital et la part des intérêts pour chaque mensualité de remboursement du prêt.

Utilisez les colonnes du tableau de remboursement du prêt comme source de données.

Type de graphique : Histogramme empilé 100%
Nota : Pour mettre un graphique dans une nouvelle feuille, vous devez le créer sur la feuille
des données puis avec un clic droit dans le graphique, faire apparaitre le menu contextuel et
choisir Déplacer le graphique/Sur une nouvelle feuille.

Excel – Aide à la décision Page 18 UT1-Capitole, L3 GET & L3 ES

 Exercice 9 : Simulation par variation des données

Pour simuler différentes condition d‘emprunt, nous allons faire varier les données de calcul.
Revenir sur la feuille contenant le tableau de prêt.
1) Remplacer le montant du prêt par 5000€. Observer le changement automatique
2) Remplacer la durée en mois par :
– la valeur 24. Que se passe-t-il quand la durée est plus longue ?
– la valeur 8. Que se passe-t-il quand la durée est plus courte ?
Revenir à la valeur 12

Bonne pratique 4
Ne faites pas une confiance aveugle aux résultats qui sont affichés dans
les cellules calculées.
Vérifiez toujours que ces résultats :
• sont cohérents avec le contexte
• ont un sens
Si un résultat vous paraît étrange, n'accusez pas Excel de faire une erreur. Sauf
exception, l'erreur vient de vous-même. Analysez votre formule pour
comprendre d'où vient l'erreur.

AUDIT (FACULTATIF)
Lorsque vous réalisez des formules, Excel crée un graphe de dépendances entre la
cellule qui contient la formule et les cellules utilisées pour effectuer le calcul.
Les fonctions d’audit permettent de repérer les dépendances entre cellules et de
retrouver les erreurs dans une feuille de calcul.
Ces fonctions se trouvent dans l’Onglet Formules, zone Vérification des Formules.

Exercice 10 (Facultatif) : Visualisation des dépendances entre cellules

Visualiser les dépendants et les antécédents de la cellule contenant le calcul de

remboursement mensuel

Excel – Aide à la décision Page 19 UT1-Capitole, L3 GET & L3 ES

 1.2 Utilisation de la valeur cible

La simulation est ici inverse du cas précédent. Il s'agit de spécifier, pour une formule
donnée, le résultat que l'on souhaite obtenir, Excel va aider à trouver la valeur d’une
donnée qui participe au calcul de la formule (paramètre).

Exercice 11 : Valeur cible : le montant de remboursement mensuel

Objectif : On veut qu’Excel nous indique ce que l’on peut rembourser avec une mensualité de
remboursement de 100€.
1) Paramètre (Cellule à modifier) : la durée du prêt.
Après avoir sélectionné la cellule contenant la formule de calcul de la mensualité de
remboursement (dans la figure ci-dessus, cellule E2), sous l’onglet Données, dans le groupe
Outils de données, cliquez sur Analyse de scénarios, puis sur Valeur cible.
Compléter la boite de dialogue (voir figure suivante) selon la variable choisie (valeur qui sera
calculée par l’outil de simulation alors que les autres cellules de la formule ont des valeurs
fixes).

Permet de trouver la valeur pour le

nombre de mois (B3) nécessaire pour
que le remboursement (E2) soit de 100€
(les autres paramètres ne changent pas)

Attention : après avoir utilisé valeur cible et vu le résultat, revenir à la valeur d'origine (12
mois) en annulant la simulation.

Refaire deux autres simulations pour la mensualité de remboursement de 100€. Mais en

faisant varier d’autres paramètres.
2) Paramètre (Cellule à modifier) : le montant du prêt.
Après avoir utilisé valeur cible et vu le résultat, revenir à la valeur d'origine (4000 euros).

3) Paramètre (Cellule à modifier) : le taux annuel. Comment expliquez-vous le résultat (taux

négatif) ?

PROTECTION D’UNE FEUILLE DE CALCUL (FACULTATIF)

La protection d’une feuille de calcul d’un classeur EXCEL permet de choisir les
cellules qui pourront être modifiées par l’utilisateur lors de l’ouverture du fichier,
toutes les autres cellules de la feuille ne pourront plus être modifiées.
L’avantage de cette protection est qu’elle empêche toute modification accidentelle
ou non d’un « modèle de traitement » réalisé sous EXCEL, notamment les cellules
contenant les libellés et les formules. Généralement le concepteur du modèle à
intérêt à protéger les cellules contenant les libellés utilisés pour une meilleure
compréhension du modèle et les formules.

Excel – Aide à la décision Page 20 UT1-Capitole, L3 GET & L3 ES

 Exercice 12 (Facultatif) : Protection d’une feuille

1) Sélectionner toute la feuille contenant le tableau de remboursement. Utiliser

les commandes suivantes de l’onglet Accueil :
a. Cellules, Format, Protection, Format de cellules
b. Dans l’onglet Protection cocher la case Verrouillée
2) Sélectionner les cellules B1, B2 et B3. Utiliser les commandes suivantes de l’onglet
Accueil.
a. Cellules, Format, Protection, Format de cellules
b. Dans l’onglet Protection décocher la case Verrouillée
3) Utiliser la commande Protéger la feuille (Dans l’Onglet Accueil – Commandes Cellules,
Format, Protection)
4) Vérifier que les seules cellules que vous pouvez modifier sont celles qui contiennent les
données, c'est-à-dire la plage B1 :B3.
Remarque : La protection de la feuille peut se faire en utilisant un mot de passe .

Excel – Aide à la décision Page 21 UT1-Capitole, L3 GET & L3 ES

 1.3 UTILISATION DES TABLES DE DONNEES

Les tables de données permettent de simuler le résultat d'un calcul avec un ensemble
d'hypothèses sur 1 ou 2 paramètres (rappel : pour la valeur cible, 1 maximum). Les
tableaux obtenus permettent au décideur de comparer les impacts des différentes
hypothèses, en ayant sous les yeux l'ensemble des résultats.
1.3.1 Table de données à simple entrée

Exercice 13 : Résultat d'exploitation d'une soirée étudiante (table à simple

entrée)

Dans un nouveau classeur [Link], renommez la feuille Soirée étudiante.

Les données de départ sont :
- le nombre de participants attendus
- le prix du ticket d'entrée
- le coût total de la soirée (la location de la salle, sono, réalisation des affiches, etc.)
Calculez le résultat de la soirée dans la cellule B7

Vous allez ensuite simuler plusieurs résultats de la soirée (donnée à recalculée par simulation)
en faisant varier le prix du ticket (paramètre qui va varier) :
- Selon le modèle présenté sur la figure ci-dessous, saisissez une suite de prix différents pour
le ticket d'entrée (vous pouvez bien entendu utiliser la recopie incrémentielle).
- En B11, faire un lien avec le résultat d'exploitation avec la formule =B7
- Sélectionnez la plage A11:B20, puis sous l’onglet Données, dans le groupe Outils de données,
cliquez sur Analyse de scénarios, puis sur table de données.
Attention : L’ordre de présentation des données dans
une table de données est fixe :
- La formule de la donnée à calculer en haut à droite
(ici une référence à la cellule soit =B11)
- Les différentes valeurs du paramètre qui varie en
colonne à gauche de la colonne de la formule

La boîte de dialogue suivante apparaît :

Dans notre cas, il faut choisir la zone d'entrée "Cellule d'entrée en colonne" car nous avons
mis nos différentes hypothèses de prix en colonne.
On saisit dans cette zone la référence de la cellule (en cliquant sur la cellule) contenant le prix
du ticket d'entrée dans la formule (donne $B$5). Faites OK, et observez le résultat.

Excel – Aide à la décision Page 22 UT1-Capitole, L3 GET & L3 ES

 Exercice 14 : Table de donnée sur les montants de remboursement d’un prêt

Reprendre le classeur Tableau [Link] et positionnez-vous sur la feuille Données

1) réalisez une table à simple entrée pour simuler différents montants de remboursement du
prêt selon différentes hypothèses de durée de prêt (de 10 à 24).
2) réalisez une nouvelle table à simple entrée pour simuler différents montants de
remboursement du prêt selon différentes hypothèses de montant de prêt (de 2000 à 6000
avec un pas de +500).

3) Utilisez l’outil Table de données pour générer les simulations et obtenir les résultats
suivants

1.3.2 Table de données à double entrée

Le principe général est le même, mais ce sont deux paramètres que l'on va pouvoir faire
varier dans la même opération : les différentes valeurs d’un paramètre seront sur la
colonne tandis que celles du second seront sur la ligne adjacente à la formule.

Exercice 15 : Résultat d'exploitation d'une soirée étudiante (table à double

entrée)

Reprendre la feuille Soirée Etudiante du classeur [Link]. L’objectif est de simuler le

résultat de la soirée en faisant varier le prix du ticket (colonne) et le nombre de participants
à la soirée (ligne).
Nota : vous ferez la table à double entrée à côté de la première, en laissant au moins une
colonne de séparation vide (voir modèle à la figure suivante).
Dans le coin haut gauche de la table (en E11 sur la figure suivante), mettre une formule vers
celle calculant le résultat.

Excel – Aide à la décision Page 23 UT1-Capitole, L3 GET & L3 ES

 Sélectionner la table, puis sous l’onglet Données, dans le groupe Outils de données, cliquez
sur Analyse de scénarios, puis sur Table de données …, etc en précisant les deux entrées.

Exercice 16 : Remboursement de l’emprunt (simulation sur durée et montant du prêt)

Reprendre le classeur Tableau [Link], en colonne G de la feuille Données, réaliser

une table à double entrée en faisant varier :
- la durée en mois (en colonne)
- et le montant du prêt (en ligne)
pour simuler les montants de remboursement de prêt (formule à mettre dans le coin haut
gauche)

2 LES FONCTIONS CONDITIONNELLES

2.1 La fonction SI

La fonction si permet d’afficher 2 valeurs différentes dans une même cellule.

= SI(Test_logique; Valeur1_si_test_vrai ; valeur2_si_test_faux)
où :
• Test_logique (ou critère) : représente le critère, exprimé sous forme d'expression
logique (comparaison).
Quelques exemples de Tests logiques:
E1 < B2
C1 = A2 * 4
B3 >= B2
• Valeur_si_vrai : l'expression que EXCEL calculera et dont il affichera la valeur dans
la cellule si le critère est évalué VRAI
• Valeur_si_faux : l'expression que EXCEL calculera et dont il affichera la valeur dans
la cellule si le critère est évalué FAUX.

Exemple :
La cellule A1 contient le nombre 1200, B1 contient le nombre 1550.
La cellule C1 contient la formule suivante =SI(B1>A1; "En hausse" ; "En baisse").
Dans C1, il est affiché "En hausse".

Excel – Aide à la décision Page 24 UT1-Capitole, L3 GET & L3 ES

 Exercice 17 : Alerte quand la mensualité dépasse une valeur limite

Dans le classeur Tableau [Link], sur la feuille Données.

• En cellule G1, saisissez "Limite budget" et en G2 une valeur

• En utilisant la fonction "SI", faites afficher dans la cellule F2 :

o "Attention !" si le montant de la mensualité en E2 dépasse la valeur limite en G2
o "" sinon.
Nota : pour indiquer que l'on ne veut rien afficher dans une cellule, on utilise un double
guillemet (""), qu'on appelle aussi "chaîne vide".
Vérifiez que votre formule fonctionne bien en modifiant le montant de l'emprunt à 1000€.

Exercice 18 : Modification du tableau pour ne faire apparaître que les lignes

utiles

Créer une copie de la feuille Tableau Remboursement

Pour résoudre les problèmes notés en exercice 9, nous allons utiliser une formule
conditionnelle qui affiche les résultats pour une ligne de paiement seulement si le numéro du
paiement de la ligne est inférieur ou égal à la durée de l’emprunt (nombre de mois).

Les numéros de paiement restent toujours affichés.

Utiliser la fonction "SI" dans les cellules en vert pour afficher, en fonction de la condition
indiquée au-dessus, soit le résultat d’une formule de calcul, soit rien ("").
Optionnel : Utiliser une fonction conditionnelle sur les numéros de paiement en jaune pour
qu’ils ne s’affichent pas non plus.

Excel – Aide à la décision Page 25 UT1-Capitole, L3 GET & L3 ES

 Exercice 19 : Résultats d'examens (colonnes complémentaires)

Ouvrir le classeur "Résultats [Link]" depuis votre répertoire "Données TD Excel".

Ce classeur comporte 2 feuilles. Dans cet exercice, vous n'utiliserez que la première.
Dans la première feuille (Résultats) complétez les colonnes en orangé avec les formules
adéquates. Vous devez obtenir les mêmes valeurs que celles qui sont affichées dans les
cellules jaunes.
Nota : Si vous avez plus de 2 valeurs à afficher dans une cellule, il faut imbriquer les fonctions
SI (les tests logiques se combinant)
Exemple : =SI( A4 < 10 ; "Affichage1" ; SI( A4 <20 ; "Affichage2" ; "Affichage3") ) affiche
• "Affichage1" quand A4 < 10
• "Affichage2" quand A4 >=10 et A4 < 20
• "Affichage3" quand A4 >=20

Pensez à utiliser la Bonne pratique 2 pour les

coefficients

2.2 La fonction [Link]

La fonction [Link] compte le nombre de cellules à l'intérieur d'une plage, répondant à un

critère donné.
Syntaxe : =[Link] (plage ; critère)
• Plage : représente la plage de cellules dans laquelle on veut compter les cellules.

• Critère : représente le critère, exprimé sous forme d'expression ("=5"), de nombre

(12), de référence de cellule (I5) ou de texte ("Passable"), qui détermine les cellules
à compter.

Exemple : Soit la plage A2:A10 contenant des nombres.

=[Link](A2:A10;"19") donnera comme résultat le nombre de valeurs de la
plage A2:A10 qui sont égales à 19 (EXCEL interprète "19" comme
l'expression "=19"). Le résultat donne ici 2.

=[Link](A2:A10;">=10") donne ici 6

Nota : pour écrire une expression renvoyant à une cellule (et non à une
valeur), il faut "recréer" la condition en s’aidant de l’opérateur de
concaténation &.
Exemple : pour écrire comme condition > A10 (c’est-à-dire : supérieur au contenu de la
cellule A10) dans une fonction [Link], on écrira : [Link](plage ;">" & A10)
Pour bien comprendre ce que fait EXCEL, écrire la formule =">" & A10 dans une cellule
quelconque.
Le & se trouve en mode minuscule sur la touche du "1" en haut à gauche du clavier.

Excel – Aide à la décision Page 26 UT1-Capitole, L3 GET & L3 ES

 2.3 Les fonctions [Link] et [Link]

[Link]
La fonction [Link] effectue une addition sur les valeurs d'une plage, sous condition.
Cette condition peut porter :
1) Sur les valeurs de la plage que l'on veut additionner
2) Ou sur les valeurs d'une autre plage.

1) Dans le 1er cas (la condition porte sur la même plage), la syntaxe est la suivante :
=[Link] (plage; critère)
• Plage : représente la plage de cellules que l'on, veut additionner et sur lesquelles porte
le critère
• Critère : représente le critère, sous forme d'expression, de nombre ou de texte,
définissant les cellules à additionner (comme la fonction [Link]).

Exemple : =[Link](A2:A10;">=10") calculera la somme des valeurs de la plage A2 à A10

qui sont supérieures ou égale à 10. Le résultat affiché pour l’exemple ci-dessus sera 85.

2) Dans le 2ème cas (la condition porte sur une autre plage), la syntaxe est la suivante
=[Link] (plage_sur_laquelle_porte_le_critère; critère; plage_pour_somme)
• plage_sur_laquelle_porte_le_critère : représente la plage de cellules sur lesquelles
porte le critère
• Critère : représente le critère, sous forme de nombre, d'expression ou de texte,
définissant les cellules à additionner.
• plage_pour_somme : représente la plage de cellules que l'on veut additionner.
Exemple : on veut faire la somme des ventes des lundi
=[Link](A2:A10 ; "lundi" ; B2:B10)
donnera comme résultat 24

[Link]
La syntaxe de la fonction [Link] est identique à celle de [Link]

Excel – Aide à la décision Page 27 UT1-Capitole, L3 GET & L3 ES

 Exercice 20 : Analyse de résultats d'examens

Reprendre le classeur "Résultats [Link]".

1) Dans la seconde feuille (Analyse résultats), insérer les formules calculant (cellules en
orangé) en utilisant les données et les résultats de la première feuille.
1. Le nombre d’étudiants Admis
2. Le nombre d’étudiants ayant une note >10 en Droit. Exception : seulement pour cet
exercice, vous pouvez utiliser le chiffre 10 dans la formule (et non la référence à une
cellule)
3. Le nombre d’étudiants ayant une mention "Très bien"
4. Nombre d’étudiants ayant une note en Informatique supérieure à la moyenne de cette
matière.
Nota : vous devez d’abord effectuer un calcul dans une cellule intermédiaire.
5. La moyenne générale des étudiants admis

2) Dans la feuille "Résultats", nommez la plage des moyennes générales "Moyennes". Dans
la feuille "Analyse résultats", modifiez la formule de la question 5 pour utiliser le nom
"Moyennes" à la place de la référence de plage.

3 FONCTIONS DE MANIPULATION DE DATES

3.1 Dates

• La date du jour : =AUJOURDHUI()

• La date et heure actuelles : =MAINTENANT()
• Une date quelconque : = DATE(num_année ; num_mois ; num_jour)

Exemple : DATE(2015 ; 10 ; 1) donne comme résultat : 01/10/2015 ou 1 Oct 2015

Nota : les valeurs constantes peuvent être remplacées par des adresses de cellules
Ex : =DATE(C2 ; B2 ; A2)
Si la cellule A1 contient une valeur date :
• L'année sera calculée par : =ANNEE(A1)
• Le mois sera calculé par : =MOIS(A1)
• Le jour sera calculé par : =JOUR(A1)
• Le jour de la semaine : =JOURSEM(A1;2) donne un chiffre entre 1 et 7
Nota : le 2ème argument peut être 0, 1 ou 2 (ici 2 => Lundi = 1, Mardi = 2, …)

Excel – Aide à la décision Page 28 UT1-Capitole, L3 GET & L3 ES

 3.2 Différence de dates

Soient la date la plus ancienne en A1 et celle qui est ultérieure en B1 on a :

• La différence en années : =DATEDIF(A1;B1;"y")
• La différence en mois : =DATEDIF(A1;B1;"m")
• La différence en jours : =DATEDIF(A1;B1;"d")
• La différence en mois hors années complètes (mois restants) :
=DATEDIF(A1;B1;"ym")
ATTENTION : la fonction DATEDIF n'apparaît pas dans la liste des fonctions de l'assistant
fonction, mais elle est reconnue par Excel et elle fonctionne.
Saisissez vous-même le nom de la fonction en suivant la syntaxe donnée ci-dessus.

Exercice 21 : Calcul de l'âge et de l'ancienneté

Ouvrir le fichier Exercices_avec_dates.xlsx depuis votre répertoire "Données TD Excel".

Remplissez les colonnes F à J avec les fonctions adéquates.
Aide : Pour la colonne J, vous devez concaténer texte et référence cellule avec le & comme
dans l’exercice 7

Bonne pratique 5
Vérifiez vos résultats en utilisant la commande de tri pour voir quelles
sont les valeurs les plus grandes (ou les plus petites)
Par exemple, dans l'exercice précédent, le nombre maximum d'années
d'ancienneté ne peut dépasser 50.

Excel – Aide à la décision Page 29 UT1-Capitole, L3 GET & L3 ES

 MISE EN PAGE ET IMPRESSION (FACULTATIF)
Excel permet de préparer la mise en page d'une feuille d'un classeur EXCEL, afin
d'assurer au mieux sa présentation sur un support papier.
Les fonctions d'Excel qui vont être utilisées sont :
• la mise en page qui permet en particulier le choix des marges et des entêtes et
pieds de page, ainsi que la répétition des titres de la feuille (Onglet Mise en
page)
• la définition de la zone d'impression qui permet le choix de la partie de la feuille
à imprimer (Onglet Mise en page/ZoneImpr)
Nota : on peut également déterminer la taille et le nombre des pages depuis la
commande Aperçu des sauts de pages (onglet Affichage, groupe Affichages
classeur).

Exercice 22 (Facultatif) : Mise en page et impression

1) Ouvrir le fichier "Liste_telecoms.xlsx" depuis votre répertoire "Données TD Excel".

2) En vous aidant du bouton Aperçu des sauts de pages de la Barre d’état réduisez le
nombre de pages qui seront imprimées à 2 pages (déplacer la limite de fin de page après
la dernière colonne du tableau)
3) Utiliser les commandes suivantes de l’Onglet Mise en page :
a. Orientation (choisir Portrait)
b. Mettre les marges haut, bas, gauche et droite à 2,54 (marges larges) (Commande
Marges)
c. Mettre les marges d'en-tête et de pied de page à 1 (accès aux options de Mise en
page avec d’Options de mise en page, Onglet Marges)
d. Pour le texte d'en-tête choisir le nom du fichier et l’option Page1 (Options de
mise en page avec d’Options de mise en page puis Onglet En-Tête/Pied de
page)

e. Pour le texte de pied de page utiliser Pied de page personnalisé (Options de mise
en page avec d’Options de mise en page puis Onglet En-Tête/Pied de page)
i. Partie gauche : vos noms
ii. Partie centrale : votre option suivie de "L3 Gestion"
iii. Partie droite : la date du jour
f. Commande Imprimer les Titres pour préciser que vous souhaitez que la première
ligne de titre (c'est-à-dire les en-têtes de colonne) soit répétée sur les 2 pages
(zone de saisie "Titre à imprimer", puis "Lignes à répéter en haut").
4) Vérifier la mise en page en faisant un Aperçu (Options de mise en page avec
d’Options de mise en page puis bouton Aperçu). Il est inutile d'imprimer les feuilles,
mais vous pouvez générer un fichier .pdf

Excel – Aide à la décision Page 30 UT1-Capitole, L3 GET & L3 ES

 Chapitre III
Gestion de listes : Filtres – Recherches de valeurs

EXCEL n'est pas un gestionnaire de bases de données, mais il peut être utilisé ponctuellement
comme tel. Noter que l'on peut aussi utiliser des données existantes en faisant une extraction
dans une base de données sous ACCESS par exemple.

1 FILTRES
Les filtres permettent de masquer les lignes du tableau qui ne correspondent pas aux
critères choisis.
Il existe deux types de filtres :
1. Filtre automatique
2. Filtre avancé

1.1 Filtre automatique

Dans ce paragraphe, nous allons utiliser uniquement le Filtre automatique :

1) Cliquer sur une cellule quelconque à l'intérieur de la liste
2) Dans l’onglet DONNEES, activer (cliquer sur) le bouton FILTRER

3) Utilisation du filtre :
o simple : cliquer sur la flèche qui apparaît dans l'entête de l'une des colonnes et
dans la zone Rechercher entrer une valeur, ou sélectionner les valeurs voulues
parmi la liste de cases à cocher. Par défaut, si vous faites des sélections sur
plusieurs colonnes, c'est l'intersection des différents résultats qui sera fournie en
résultat final
o pour des critères plus complexes : choisir dans la liste de valeurs, selon le type des
cellules (numérique, texte ou date), l'option Filtres numériques, Filtres textuels,
Filtres chronologiques ou Filtres personnalisés.

4) Pour revenir à la liste complète, cliquer sur le bouton Effacer du groupe Trier et
Filtrer

Exercice 23 : Utilisation du filtre automatique

Ouvrir le classeur "Ventes Pommes Poires [Link]" depuis votre répertoire "Données
TD Excel".
Positionnez-vous sur la feuille "Données ventes".
Attention : entre chaque question, il faut revenir à la liste complète
1) Filtrer le tableau pour afficher uniquement les ventes du vendeur "Lupin"
2) Filtrer pour afficher uniquement les ventes de poires des vendeurs "Lupin" et "Martin"
3) Filtrer pour afficher les ventes d’un montant compris entre 10 000 € et 15 000 €
Attention : ne pas utiliser les cases à cocher mais un filtre numérique

Excel – Aide à la décision Page 31 UT1-Capitole, L3 GET & L3 ES

 4) Filtrer pour afficher les ventes de scoubidous dont le montant est supérieur ou égal à
15000€, ainsi que celles dont le montant est strictement inférieur à 5 000 €.

Nota : aucune question ne donne un résultat vide

1.2 LE FILTRE AVANCE (FACULTATIF)

Les filtres avancés servent à réaliser des sélections complexes, qui sont impossibles à
réaliser avec un filtre automatique.
Un filtre avancé nécessite deux zones de travail obligatoires et une zone facultative :
1. la liste de données elle-même (désignée par "Plages" dans la fenêtre "Filtre avancé")
2. la zone de critères
3. une zone de destination si la liste n'est pas filtrée sur place (zone "Copier dans" dans
la fenêtre "Filtre avancé")

La zone de critères doit être créée dans la feuille, avant d'appeler le filtre.
Pour créer la zone de critères, il faut :
1. recopier les étiquettes de colonnes à un autre endroit de la feuille (par exemple à partir
de la cellule A1 après avoir ajouté des lignes au-dessus du tableau). La ou les lignes
situées au-dessous des étiquettes ainsi recopiées vont servir de zones pour la saisie des
critères de sélection
2. saisir des valeurs permettant de sélectionner les lignes du tableau en utilisant
éventuellement un opérateur (> < = >= <= <>)

Nota 1 : l'absence d'opérateur équivaut à un "=".

Nota 2 : Règles de filtrage
1) les critères situés sur la même ligne sont traités comme s'ils étaient reliés par
un ET (intersection).
2) les critères situés sur des lignes différentes sont traités comme s'ils étaient
reliés par un OU (union).

Après avoir créé la zone de critère, et pour effectuer le filtrage, il faut :

1) Cliquer sur une cellule quelconque de la liste

2) Dans l’onglet DONNEES cliquer sur le bouton Avancé. La fenêtre

suivante apparaît :

3) Spécifier la plage de la zone de critères en la sélectionnant avec la souris.

Nota : si vous n'avez pas cliqué dans la liste avant d'appeler le filtre, il faut vérifier que
la plage de cellules indiquée dans la zone "Plages" correspond effectivement au tableau à
filtrer.

Excel – Aide à la décision Page 32 UT1-Capitole, L3 GET & L3 ES

 Exercice 24 (Facultatif) : Tester le filtrage avec différents critères

Copier la feuille Données ventes et utilisez la feuille Donné[Link] (2)

(1) Créez une zone de critères au-dessus de la liste des ventes comme montré dans la figure
suivante, en recopiant la ligne des étiquettes de colonnes et en laissant quelques lignes vides
avant les données.

2) Testez le filtrage (sur place) pour afficher les ventes de pommes. La zone de critères
(A1:F2) doit être remplie comme suit :

3) Testez le filtrage (sur place) pour afficher les ventes de pommes ou de poires. La zone de
critères doit être remplie comme suit :

4) Testez le filtrage (sur place) pour afficher les ventes de poires par le vendeur Lupin. La
zone de critères doit être remplie comme suit :

Notez que le filtrage s'effectue au sein de la liste elle-même, en masquant les lignes qui ne
correspondent pas aux critères.

Excel – Aide à la décision Page 33 UT1-Capitole, L3 GET & L3 ES

 Exercice 25 (Facultatif) : Filtre des ventes

Filtrer afin d'afficher uniquement :

1) toutes ventes faites à des GMS direct
2) les ventes de pommes supérieures à 10 000 €.
3) les ventes de pommes supérieures à 10 000 €, ou celles de scoubidous effectuées avant le
01/06/2016.
Attention : ces questions sont indépendantes les unes des autres (il faut rétablir la liste
complète à chaque fois, et effacer le contenu de la zone de critères).

Exercice 26 (Facultatif) : Filtre des ventes vers un autre emplacement

Il vous suffit de cocher le bouton Copier vers un autre emplacement et de préciser dans la
zone "copier dans" la cellule de la destination.
Attention : si vous souhaitez filtrer vers une autre feuille, il faut appeler le filtre à partir de
cette feuille de destination. Dans la fenêtre "Filtre avancé", il vous faudra ensuite spécifier la
plage qui contient la liste (zone "Plages").
1) Refaites un filtrage de l'exercice précédent vers un emplacement différent dans la même
feuille.
2) Refaites un filtrage de l'exercice précédent vers une autre feuille.

Exercice 27 (Facultatif) : Filtre en affichant seulement certaines zones

Jusqu'à présent, la liste filtrée affichait l'ensemble de toutes les colonnes de la liste d'origine.
Vous pouvez faire afficher uniquement certaines colonnes (voir figure suivante).
Pour cela, il faut, avant de réaliser le filtrage, saisir (ou recopier) dans l'emplacement de
destination les étiquettes des colonnes à afficher. Quand vous préciserez la destination, vous
sélectionnerez les cellules contenant les étiquettes de cette zone de destination.
1) Faites afficher les noms des vendeurs et les types de clients pour les ventes de scoubidous
supérieures à 9 000 €
2) Faites afficher uniquement les noms des vendeurs, sans que les noms soient répétés, pour
les ventes de scoubidous supérieures à 9 000 €.

Excel – Aide à la décision Page 34 UT1-Capitole, L3 GET & L3 ES

 2 LES TRIS
On peut trier une liste sur les valeurs d'une ou plusieurs colonnes, notamment pour préparer
des sous totaux automatiques ou une recherche par valeur approchée, ou simplement pour
des raisons de présentation ou de contrôle des résultats affichés.
Le tri peut se faire sur un très grand nombre de critères.
Le tri est accessible (après avoir cliqué dans la liste) :
1. par les boutons tri rapide (croissant ou décroissant ) de l’onglet Données. Le
tri se fera sur un seul critère (celui correspondant à la colonne dans laquelle on a
cliqué avant le tri).

2. par l’onglet Données, bouton Trier , qui ouvre la fenêtre suivante.

La manipulation des niveaux (en haut) vous permet de déterminer le nombre de critères
utilisés pour le tri. Ensuite pour chacun d’entre eux, vous préciserez : la Colonne
correspondante, si le tri se fait sur des valeurs ou une mise en forme, enfin si l’ordre du tri
est croissant, décroissant ou personnalisé (effectué à partir de listes personnalisées).

Exercice 28 : Tri des ventes

1) Trier la table par vendeur et par produit. Quand vous avez vérifié le résultat, remettre la
table dans l'ordre des numéros (1ère colonne).
2) Trier la liste par type de client, par produit et par vendeur. Quand vous avez vérifié le
résultat, remettre la table dans l'ordre des numéros (1ère colonne).

3 RECHERCHES DE VALEURS
La recherche de valeurs dans une liste de données est possible en utilisant des filtres
Il est aussi possible de rechercher des valeurs dans un tableau (ou une plage de cellules) en
utilisant des fonctions spécialisées comme : RECHERCHEV, EQUIV utilisée avec la fonction
INDEX, ou RECHERCHE.
Nota : les syntaxes proposées dans l'aide Excel sont souvent complexes. Nous vous
conseillons d'utiliser les syntaxes décrites ci-dessous.

3.1 Fonction RECHERCHEV

Cette fonction recherche la valeur demandée dans la première colonne d'un tableau, puis
renvoie la valeur correspondant à la colonne dont le rang est précisé en troisième paramètre.
Syntaxe :
=RECHERCHEV(valeur_cherchée; tableau; index_col_res; valeur proche)

Excel – Aide à la décision Page 35 UT1-Capitole, L3 GET & L3 ES

 Exemple

Dans la cellule H4, on saisit la formule

=RECHERCHEV( H3 ; A3:D11 ; 4 ; FAUX)

La formule de la cellule H4 cherche dans

la première colonne du tableau la ligne
où se trouve le mot "Japon" (en H3),
puis renvoie comme résultat le contenu
de la 4ème colonne de cette même ligne,
c'est-à-dire le chiffre "127,6"
Nota : Le paramètre " valeur proche" mis à FAUX (ou à 0, ce qui est équivalent) provoque
une recherche exacte.
Si ce paramètre est mis à VRAI (ou à 1) la fonction réalise une recherche approchée (voir
exercice 30). Il faut que la liste soit triée préalablement dans l’ordre croissant de la
première colonne. Même si l'aide indique que ce paramètre est facultatif, il faut en
réalité absolument le mettre pour éviter des résultats erronés (dans certains cas, la
fonction produit en effet un « bug »).

Exercice 29 : Recherche des données Salariés

Ouvrir le fichier "Exercices_Recherches.xlsx" depuis votre répertoire "Données TD Excel".

Attention, il y a cinq feuilles dans ce classeur (2 feuilles de données et 3 feuilles d'exercices).
Se mettre sur la feuille "exercice Recherchev (FAUX)"
Données à utiliser : feuille "Données salariés", nom Tableau_Salariés associé aux données.
Dans la feuille "exercice Recherchev (FAUX)" : à partir d'un nom de salarié à saisir, faire
afficher ses prénom, numéro de salarié, taux horaire et salaire mensuel dans les cellules en
fond jaune en utilisant la fonction Recherchev (recherche exacte : dernier paramètre à FAUX).

Exercice 30 : Détermination de la tranche de salaire

Toujours dans le même classeur, se mettre sur la feuille "exercice Recherchev (VRAI)".
Dans la feuille "Données salariés", remplir la colonne des limites de tranches du tableau des
tranches de salaires, en saisissant les valeurs appropriées pour le calcul des tranches demandé
ci-après (la limite correspond à la borne inférieure)
Dans la feuille "exercice Recherchev (VRAI)" : en utilisant la fonction Recherchev, faire afficher
le total mensuel chargé, puis la tranche de salaire (recherche approchée : dernier paramètre
à VRAI).

Bonne pratique 6
Dans une formule, n'utilisez pas plus de deux "SI" imbriqués. Si vous
avez besoin de plus de deux "SI", utilisez la fonction RECHERCHEV
avec le dernier paramètre à "VRAI"

Excel – Aide à la décision Page 36 UT1-Capitole, L3 GET & L3 ES

 3.2 FONCTION EQUIV (FACULTATIF)
EQUIV renvoie la position (numéro de ligne ou de colonne) :
− soit d'une valeur recherchée dans une colonne donnée d'un tableau (EQUIV donne
alors le numéro de ligne)
− soit d'une valeur recherchée dans une ligne donnée d'un tableau (EQUIV donne alors
le numéro de colonne)
Syntaxe : EQUIV(valeur_recherchée; colonne de recherche; 0)
Exemple : si l'on reprend le tableau précédent,
EQUIV(H3 ; A:A ; 0) donne pour résultat le numéro de ligne où se trouve la valeur
"Japon" dans la colonne A, c'est-à-dire 8
Mais ATTENTION :
EQUIV(H3 ; A3:A11 ; 0) donne pour résultat 6 car "Japon" se trouve à la 6ème ligne de la
plage A3:A11.

EQUIV(H3; 8:8 ; 0) donne pour résultat le numéro de colonne où se trouve la valeur

"Japon" dans la ligne 8, c'est-à-dire 1
Nota : même remarque que plus haut sur le denier paramètre (ici mettre 0).

3.3 FONCTION INDEX (FACULTATIF)

INDEX retourne la valeur de la cellule située à une position dans une liste donnée (en ligne
ou en colonne)
Syntaxe : INDEX (liste; position)
ATTENTION : les colonnes et les lignes doivent être indiquées de la même manière dans les
deux fonctions (EQUIV et INDEX), afin que l’ensemble soit cohérent.

Exemple 1 : INDEX(C:C ; 10) donne pour résultat le contenu de la cellule C10

Exemple 2 : INDEX(5:5 ; 4) donne pour résultat le contenu de la cellule D5

Mais ATTENTION :
INDEX(B3:B11 ; 6) donne pour résultat "Tokyo" car cette ville est bien inscrite à la 6ème
ligne de la plage B3:B11.

ATTENTION : Un bogue d'Excel fait que, si le 2ème paramètre (n° de ligne ou de colonne)
de la fonction INDEX est à zéro, la fonction renvoie tout de même un résultat (qui est donc
faux).
L'explication semble être que si la fonction est sur une ligne dans laquelle il y a des valeurs
dans la colonne de recherche, c'est la valeur qui est sur la même ligne qui est renvoyée
comme résultat. Si la fonction est sur une ligne dans laquelle il n'y a pas de valeur, c'est
zéro qui est renvoyé comme résultat.
Penser à mettre une validation de données dans la cellule où se trouve le 2ème paramètre
pour empêcher des valeurs inférieures ou égales à zéro.

Excel – Aide à la décision Page 37 UT1-Capitole, L3 GET & L3 ES

 Exercice 31 (Facultatif) : Recherche des résultats d'un concours

Toujours dans le même classeur ("Exercices_Recherches.xlsx"), se mettre sur la feuille

"exercices EQUIV et INDEX".
Données à utiliser : feuille "Données résultats concours"
Attention : avant de commencer, il est nécessaire de trier les données par ordre décroissant
de la note finale .
Dans la feuille "exercices EQUIV et INDEX" : faire afficher les résultats attendus dans les
cellules en jaune pour les 3 questions suivantes :
• Question 1 : Recherche du rang d'un candidat à partir de son nom
• Question 2 : Recherche du nom du candidat à partir d'un rang donné
• Question 3 : Recherche de la note de "Présentation" du candidat ayant un rang donné

INSERTION DE COMMENTAIRES (FACULTATIF)

Excel permet d’insérer des commentaires dans les documents. Cela est très utile
pour commenter des cellules contenant des informations importantes ou des
formules de calculs compliquées. Le créateur d’un document Excel peut aussi s’en
servir comme aide-mémoire sur l’avancement de ses travaux.
Pour créer un commentaire, il suffit de cliquer sur la cellule devant contenir le
commentaire avec le bouton de droite puis de choisir l’option Insérer un
commentaire.
Une zone de commentaire sous forme de Post-It s’affiche avec le nom de l’utilisateur
propriétaire du document (à voir dans Onglet Fichier => Informations => zone
personnes associées).
Un indicateur rouge, situé en haut à droite, signale la présence d’un commentaire
dans une cellule.
Nota : Par défaut, le commentaire s’affiche lorsque l’utilisateur pointe la cellule à
l’aide de la souris. Le commentaire peut rester affiché en permanence (voir clic
droit).

Exercice 32 (Facultatif) : Insertion de commentaires

Reprendre l'exercice précédent et insérer des commentaires sur certaines régions.

Excel – Aide à la décision Page 38 UT1-Capitole, L3 GET & L3 ES

 Chapitre IV
TABLEAUX CROISES DYNAMIQUES

1 TABLEAU CROISE DYNAMIQUE (TCD)

Un TCD permet de présenter une synthèse des données multidimensionnelle. Les données
sont agrégées, selon des critères et une fonction de calcul que l'on peut choisir.
La fonction de calcul est, par défaut :
− SOMME si les données sont numériques,
− NB (nombre de valeurs) si les données sont de type texte.

Exemple : le TCD suivant présente le montant total des ventes par vendeur et produit

Lecture : Le vendeur Lupin a vendu pour 67 240€ de poires.

1.1 Pour créer un TCD

Cliquer dans la liste de données, puis, sous l’onglet Insertion, groupe Tableaux, cliquer
sur le bouton Insérer un tableau croisé dynamique.

Attention, si vous sélectionnez toute la feuille, le TCD affichera des lignes vides car il
prendra en compte les lignes sans données.

Exercice 33 : Création d'un TCD

Objectif : Dans le classeur "Ventes Pommes Poires [Link]", créer le TCD précédent
présentant le montant total des ventes par vendeur et produit à partir de la feuille Données
Ventes. Pour cela :
- Cliquez dans la liste de données avant d'insérer le TCD, cela vous évitera de devoir définir les
références de la liste de données.
- Dans la boîte de dialogue Créer un tableau croisé dynamique, vérifier la plage des données
à analyser, ainsi que l’emplacement du TCD (Nouvelle feuille par défaut), puis cliquez sur OK.
Une nouvelle feuille a été créée, ça sera l’emplacement du TCD.
Renommez cette feuille en "TCD1".

Excel – Aide à la décision Page 39 UT1-Capitole, L3 GET & L3 ES

 Le volet Champs de tableau croisé dynamique est affiché à droite de la feuille de calcul (voir
figure suivante). Le volet est composé de deux zones :

La liste des champs de données

Les zones de dépôt

Nota : Dans la barre de menu, remarquez l'apparition de deux onglets (Analyse et Création)
sous le titre Outils de tableau croisé dynamique.

Dans le volet Champs de tableau croisé dynamique :

1) Cochez les champs à inclure dans le rapport : vendeur, produit, montant
2) puis dans la zone de dépôt, déplacer les champs pour avoir produit en colonnes et vendeur
en lignes.

Nota : pour modifier le nom d'un champ, il suffit de cliquer dessus et aller le changer dans la
barre de formule.

Attention: à faire OBLIGATOIREMENT avant de faire la question suivante (question 3)

Dans les versions récentes d'EXCEL, les TCD avec les options par défaut ne proposent
pas une fonction essentielle : celle qui permet de modifier l'emplacement des champs
dans le TCD même.
Pour obtenir cette fonction absolument indispensable en aide à la décision il faut :
- cliquer dans un TCD pour faire apparaître l’onglet "Analyse de tableau croisé
dynamique" et choisir dans "Options du tableau croisé dynamique" la commande
"Options"
- dans la fenêtre qui s'ouvre, choisir l'onglet "Affichage"
- cocher la case "Disposition classique du tableau croisé dynamique (glisser de
champs dans la grille)".

3) Revenez à la feuille TCD1, faites une copie de cette feuille. Nommez la copie "TCD2" et
modifiez la présentation dans le TCD lui-même, en faisant glisser les étiquettes pour avoir les
produits en ligne et les vendeurs en colonne.

Excel – Aide à la décision Page 40 UT1-Capitole, L3 GET & L3 ES

 4) Pour avoir une analyse suivant les types de client, ajoutez le champ type client dans Filtre
du rapport. Par défaut, le TCD initial correspond à « Tous » les types de client.
Testez ensuite l’affichage pour un type de client particulier.

Attention : à chaque fois que vous créez un tableau croisé dynamique, soyez attentif à ne pas
écraser un TCD précédent (choisissez "Nouvelle feuille" ou un nouvel emplacement sur une
feuille contenant déjà un TCD).
Nota1 : en cliquant avec le bouton droit sur une cellule du TCD, on accède à des options sur
le type de calcul, la présentation etc.
Nota2 : en double-cliquant sur une cellule de valeur agrégée d'un TCD on provoque l'affichage
d'une nouvelle feuille contenant le détail des données ayant produit la valeur agrégée.

Bonne pratique 9
Ne mélangez pas données et résultats des traitements dans une
même feuille. Mettez les données sur une feuille spécifique.

1.2 Fonctions avancées des TCD

Exercice 34 : Groupage des données sur les champs de date

Créer un nouveau TCD (TCD3) dont l’état de départ pour cet exercice doit être : en ligne => les
produits, en colonne => les dates de vente, en valeurs => les montants
Dans le TCD, le regroupement par année peut être décomposé en trimestre et mois en
cliquant sur le + à côté des étiquettes

Exercice 35 (FACULTATIF) : Groupage sur les données sur un autre champ

L'état de départ du TCD35 pour cet exercice doit être : en ligne => les vendeurs, en colonne
=> les fruits, en valeurs => les montants
Groupez les produits en deux groupes (fruits et scoubidous). Pour cela sélectionner les
colonnes pommes et poires, puis clic droit, et dans le menu contextuel choisir Grouper.

1.3 Modifications du TCD

Lors de la création d'un TCD, pour modifier la fonction d'agrégation, plusieurs possibilités :
a) CONSEILLÉ : par un clic droit sur une des données agrégées ou sur l'étiquette grise du
champ ("Somme de montant"), puis commande "Paramètres de champ"
b) autre possibilité : cliquer sur le champ "Somme de montant" dans le volet bas droit de la
zone de dépôt, puis aller sur Paramètres de champs de valeurs.

c) autre possibilité : dans l'onglet Option des Outils de tableau croisé dynamique, bouton
Champ actif, puis Paramètres du champ

Excel – Aide à la décision Page 41 UT1-Capitole, L3 GET & L3 ES

 Exercice 36 : Données agrégées par une autre fonction que la somme (ici :
moyenne)

Faire un nouveau TCD (TCD4) avec : en ligne => les vendeurs, en colonne => les produits, en
valeurs => les montants, avec comme fonction d'agrégation la MOYENNE.

Exercice 37 : Affichage des données en pourcentages

Faire un nouveau TCD (TCD5) avec en ligne => les types de client, en colonne => les dates de
vente, en valeurs => les montants.
Modifier le TCD comme suit :
1) supprimer le champ Années des colonnes pour grouper par trimestre et mois seulement
2) afficher les résultats en % des montants par ligne.

Pour cela, après avoir cliqué sur un montant quelconque dans le TCD utiliser le menu
contextuel (clic bouton droit souris) et aller à Paramètres des champs de valeur, puis onglet
Afficher les valeurs puis choisir % du total de la ligne.

Exercice 38 : Actualisation d'un TCD (après modification des données

d'origine)

Les données liées à un TCD sont stockées sous forme cachée dans la même feuille.
L'actualisation, c'est à dire la modification du TCD en cas de modification de la liste d'origine,
n'est donc pas automatique.
- Modifiez des valeurs dans la liste de données de départ (pas dans le TCD).
Par exemple, modifiez le nom du produit sur une ligne de vente : à la place de "pommes",
mettez "fraises".
- Appelez, dans l’onglet Données, le bouton Actualiser tout dans le groupe Connexions.
- Observez le résultat sur les TCD.

Excel – Aide à la décision Page 42 UT1-Capitole, L3 GET & L3 ES

 Chapitre V
EXERCICES DE CAPITALISATION

Exercice 39 (exercice de capitalisation) : Achats

Dans un nouveau classeur nommé "Capitalisation"

1) Créer le tableau ci-dessous (données, mises en forme, formules si possible) dans une feuille
nommée "Achats".

2) Utiliser une mise en forme conditionnelle qui affiche en rouge sur fond jaune le total des
montants TTC s’il est supérieur à 10 000€.

3) Le fournisseur nous accorde les réductions suivantes sur le montant TTC de chaque produit
- 10 % si la quantité est supérieure à 1500
- 5% si la quantité est comprise entre 500 et 1500 compris
Rien si la quantité est inférieure ou égale à 500
Dans la colonne G, calculer les réductions accordées et dans la colonne H les montants TTC
après réductions

Bonus : ne pas mettre les % et seuils dans les formules mais dans des cellules à côté

4) Réaliser une table de données qui va recalculer le montant TTC total (sans réduction) en
fonction d’une variation du taux de TVA (de 5% à 15% avec un pas de +0.5). Attention à la mise
en forme de la table finale.

Excel – Aide à la décision Page 43 UT1-Capitole, L3 GET & L3 ES

 Exercice 40 (exercice de capitalisation) : Gestion d’hôtel

Contexte : Un responsable d’hôtel souhaite analyser la fréquentation de son établissement

pour mieux cibler sa politique publicitaire auprès des clients ou des intermédiaires (agences,
guides, Internet, …).
Pour cela, utilisez le fichier "Hôtel donné[Link]".
Questions
1) Calcul du montant des séjours des clients
Dans la feuille Paramètres, nommez les données prix des nuitées, pensions et demi
pensions.
Dans la feuille Données, dans la colonne G du tableau, vous saisirez la formule permettant
de calculer le montant du séjour en fonction des prestations choisies.

2) Le responsable souhaite obtenir certaines informations sur l’existant. Dans une nouvelle
feuille "Analyse existant", ajouter :
• Un TCD donnant le nombre de nuitées par département de provenance
• La moyenne du nombre de nuitées par département
• Mettre en évidence les nombres de nuitées du TCD inférieurs à la moyenne
• Le nombre de départements qui ont plus de nuitées que la moyenne

Aide technique : dans certaines fonctions, pour écrire une condition renvoyant à une cellule,
il faut « recréer » la condition en s’aidant de l’opérateur de concaténation « & ».
Exemple : pour écrire comme condition > A10 (c’est-à-dire : supérieur au contenu de la
cellule A10) dans une fonction [Link], on écrira : [Link](plage ;">" & A10)

• Un TCD donnant les montants moyens par département de provenance

• Trier ce TCD sur le montant moyen décroissant

Excel – Aide à la décision Page 44 UT1-Capitole, L3 GET & L3 ES

 3) Il souhaite aussi une analyse prévisionnelle de l’évolution du CA sur les nuitées. Réaliser,
dans une nouvelle feuille "Analyse prévisionnelle", le tableau de calcul du CA en tenant
compte de taux d’évolution qui seront fixés.

4) Ajoutez une table de données qui simulera les calculs en fonction de l’évolution du prix
des nuitées (de 1% à 4% avec un pas de +0.2) et de l’évolution du nombre de nuitées (de 1%
à 2% avec un pas de +0.1).

Exercice 41 (exercice de capitalisation) : Etat des stocks

Données à utiliser : fichier Commandes Produits [Link] dans votre répertoire

"Données TD Excel".
1) Complétez la feuille "Etat des stocks 1)" en écrivant les formules nécessaires.
La cellule où le chiffre "16" est affiché dans l'exemple est la seule cellule de saisie de la colonne
de droite (référence produit).
Les zones vertes en italiques doivent contenir des formules de calcul, qui vont permettre
l'affichage automatique des informations (nom produit, nb en stock, valeur,… réf. fournisseur,
nom société,…). Lorsque vous modifiez la référence du produit, la valeur de toutes les autres
cellules doit être modifiée. Que se passe-t-il avec le produit 14 ?

Etat des stocks

Produit
Réf. produit 16
Nom produit Northwoods Cranberry
Sauce
Nb unités en stock 6
Valeur (euros) des unités en stock 1200
Nb d'unités commandées 0

Fournisseur
Réf. fournisseur 3
Nom société Grandma Kelly's Homestead
Téléphone (313) 555-5735
Question FACULTATIVE 2) Utilisez la feuille "État des stocks 2)". Modifiez les formules
dans les cellules affichant le nom de la société et son numéro de téléphone, pour que
ces informations s'affichent (malgré l'absence de la ligne de la référence fournisseur).

Excel – Aide à la décision Page 45 UT1-Capitole, L3 GET & L3 ES

 Chapitre VI (Facultatif)
Solveur - Formulaire

Chapitre facultatif
1 SOLVEUR EXCEL
Le Solveur d'Excel est un outil d'optimisation notamment pour l'allocation de ressources.
Il est utilisé lorsque l'on recherche la valeur optimale d'une cellule calculée, par ajustement
des valeurs de plusieurs autres cellules qui interviennent dans la formule qui calcule la valeur
de la cellule cible, en tenant compte des limites pour une ou plusieurs valeurs intervenant
dans le calcul.
Il est donc essentiellement un outil d'optimisation, et à ce titre, n'est pas un outil d'aide à
la décision au sens strict, puisqu'il produit lui-même la meilleure décision.
Il correspond à la perspective décrite dans le cours comme "théorie normative de la
décision" (TND).
Le solveur peut cependant être utilisé comme aide à la décision (question 3 de l'exercice
47).

1.1 L'allocation de ressources

L’allocation de ressources est une décision fréquente dans l’entreprise.

Il s’agit par exemple de déterminer le nombre d’unités de différents produits à fabriquer
en fonction de contraintes de capacité de production (ressources humaines, machines...).
Il peut s’agir aussi de maximiser le retour sur investissement du capital immobilisé
(ressources financières).

1.2 Optimisation et satisfaction

Il y a souvent un critère principal utilisé dans une prise de décision : maximiser le profit,
minimiser le coût, minimiser le temps de trajet, la consommation d'une ressource...
Le critère qu’on cherche à optimiser est décrit par une fonction "objectif". Cette fonction
prend en compte le niveau des différentes activités à réaliser (ce qu’on peut faire) et la
contribution de chaque activité au critère (par exemple de profit ou de coût).
Il y a généralement des contraintes sur ce qu'on peut faire, des exigences diverses, comme
par exemple s'assurer de niveaux minima d’activité, ...
Quand on peut décrire la fonction "objectif " et les contraintes sous une forme mathématique
adaptée, on peut trouver une solution optimale.
On peut aussi les utiliser pour trouver une solution proche de l’optimum, ou suffisamment
bonne (solution dite satisfaisante).

Excel – Aide à la décision Page 46 UT1-Capitole, L3 GET & L3 ES

 1.3 Appel du solveur

On appelle le solveur depuis l'onglet Données, groupe Analyse, bouton Solveur.

Nota : Si le groupe Analyse n’est pas présent dans le ruban Données, il est nécessaire de
l’ajouter. Pour cela :
1) Cliquez sur l’onglet Fichier.
2) Cliquez sur Options, puis sur la catégorie Compléments.
3) Près du bas de la boîte de dialogue Options Excel, assurez-vous que Compléments
Excel est sélectionné dans la zone Gérer, puis cliquez sur Atteindre.
4) Dans la boîte de dialogue Compléments, activez les cases à cocher Analysis
ToolPak et Complément Solveur, puis cliquez sur OK.
Sous l’onglet Données, notez qu’un groupe Analyse a été ajouté (voir figure suivante)

Exercice 42 (Facultatif) : Découvrir le SOLVEUR

Découvrez les possibilités du SOLVEUR en étudiant les exemples proposés.

Ouvrez le classeur "[Link]" depuis votre répertoire "Données TD Excel".
Question 1)
Étudiez la feuille "Recherche de gamme" (voir figure suivante).

Lisez les commentaires.

Testez les calculs du Solveur qui ont déjà été préparés pour cet exemple :
Cliquez sur le bouton Solveur, la fenêtre Paramètres du solveur s'ouvre (voir figure
suivante). Pour lancer le calcul d'optimisation, cliquez sur bouton Résoudre.
La fenêtre Paramètres du solveur permet de déterminer :
- la cellule contenant une formule définissant l'objectif à maximiser ou minimiser
- les cellules variables dont le solveur va déterminer la valeur correspondant à l'objectif
- les contraintes que doit satisfaire la solution.
Dans le cas de cet exemple, les paramètres ont déjà été définis.

Excel – Aide à la décision Page 47 UT1-Capitole, L3 GET & L3 ES

 Question 2)
Dans cet exemple, après résolution, les nombres trouvés par le solveur apparaissent être des
entiers (cellules D9 à F9). Il s'agit en fait d'un format d'affichage (vous pouvez vérifier quel est
le chiffre réel en cliquant sur une de ces trois cellules et en regardant la valeur dans la barre
de formule).
Pour que les valeurs soient réellement des entiers, vous allez rajouter une contrainte dans le
solveur. Dans la fenêtre Paramètres du solveur, cliquez sur le bouton Ajouter. Dans la zone
de saisie Références de cellules, sélectionnez les cellules de D9 à F9. Puis cliquez sur la flèche
pour choisir l'opérateur ent. Cliquez sur OK. Lancez à nouveau une résolution (bouton
Résoudre). Vérifiez dans la barre de formule la valeur réelle des cellules D9 à F9.
Attention : il peut arriver que les chiffres n'aient pas été modifiés et ne soient toujours pas
entiers.
Pour résoudre ce problème : dans la fenêtre Paramètres du solveur, cliquez sur le bouton
Options, puis, si elle est cochée, décochez la case Ignorez les contraintes de nombres entiers.
Relancez ensuite une nouvelle résolution.

1.4 Exemple d'optimisation d'allocation de ressources

Il faut tout d’abord identifier les activités. Ce sont les variables de décision. On veut
connaître quelles activités il faut réaliser et à quel niveau elles doivent être faites pour
atteindre le meilleur résultat.
Ensuite, il faut identifier :
− les limitations des ressources disponibles à consommer (temps, matériel, ressources
financières,...),
− et les niveaux maximum ou minimum de certaines variables imposés par la politique de
l’entreprise (minimum d’employés par exemple) ou les conditions du marché (quantité
maximale d’un produit vendu pour une période déterminée,...).
Il faut aussi déterminer combien une activité déterminée consomme de ressource.
Il faut ensuite exprimer en seule formule l'objectif que l’entreprise souhaite maximiser (par
exemple le profit) ou minimiser (par exemple, le coût).
Il faut enfin exprimer l'ensemble (l'objectif, les différentes exigences et les contraintes) dans
la syntaxe imposée par le Solveur d'Excel (cf. image de la fenêtre plus haut).

Excel – Aide à la décision Page 48 UT1-Capitole, L3 GET & L3 ES

 Exercice 43 (Facultatif): Optimisation d'allocation de ressources

L’entreprise VROUM S.A., qui produisait jusque-là uniquement des motos, a décidé de
produire aussi des scooters des neiges. Elle envisage leur production sur la chaîne de
production des motos.
Il s’agit d’analyser le niveau des opérations sur cette chaîne pour un mois hypothétique.
Les deux productions utilisent des ressources : les heures de travail des services mécanique et
montage. La production d'une moto demande 10 heures machine et 17 h d’assemblage. La
production d'un scooter des neiges demande 14 h machine et 12 h d’assemblage.
Le service marketing prévoit des ventes de 30 motos maximum dans le mois, avec un
minimum de production nécessaire de 14. Il prévoit un maximum de vente de 25 scooters des
neiges pour le mois. On supposera que toute unité produite est vendue.
La situation actuelle de l'entreprise est la suivante : elle dispose de 450 heures de machine et
600 h d’assemblage par mois.
Le profit prévu est de 400 € par moto et 300 € par scooter.

Question 1) Il s’agit de trouver le nombre de motos et le nombre de scooters qui permettent

de maximiser le profit en respectant toutes les contraintes.
Les ressources limitées sont les heures de machine et d’assemblage.
Pour cela vous devez :
a) déterminer toutes les données primaires (variables de réglage) dont vous aurez besoin
(à extraire de l'énoncé ci-dessus), et les organiser dans la feuille
b) calculer le profit total (en fonction du nombre d'unités produites pour les deux
produits)
c) déterminer toutes les données calculées dont vous aurez besoin pour définir les
contraintes à inclure dans le solveur (elles sont exprimées dans l'énoncé)
d) paramétrer le solveur pour qu'il calcule le nombre de motos et de scooters à produire
pour maximiser le profit.
Résultat optimisé auquel vous devez parvenir
Nombre de Motos : 26
Nombre de Scooters : 13
Profit total : 14 300

Pensez à utiliser la Bonne pratique 2

pour l'écriture des contraintes
Question 2) Vous travaillerez à partir de la même feuille, en modifiant les données (vous
n'avez pas à faire un nouveau solveur).
Suite à son utilisation de l'outil que vous lui avez fourni, le décideur évolue et se pose de
nouvelles questions. Il veut ainsi connaître l'impact de l'embauche d'un nouveau salarié. Ce
salarié travaillerait en partie à l'assemblage, pour 80 h par mois. L'hypothèse est que le profit
par produit ne serait pas modifié.
Combien de motos et de scooters doit-on fabriquer pour optimiser le profit dans ces nouvelles
conditions. Quel est alors le profit ?

Excel – Aide à la décision Page 49 UT1-Capitole, L3 GET & L3 ES

 Résultat optimisé auquel vous devez parvenir
Nombre de Motos : 30
Nombre de Scooters : 10
Nouveau profit total : 15 000
Question 3) Vous travaillerez à partir de la même feuille, du même solveur.
Outre l'embauche de ce salarié, le décideur envisage d'acheter une nouvelle machine. La
nouvelle machine permettrait d'augmenter les heures disponibles de 150 heures.
Le décideur fait l'hypothèse que le profit par produit serait modifié. Le profit par moto serait
alors de 380 € et celui par scooter de 275 €.
Combien de motos et de scooters doit-on fabriquer pour optimiser le profit dans ces nouvelles
conditions. Quel est alors le profit ?
Résultat optimisé auquel vous devez parvenir
Nombre de Motos : 22
Nombre de Scooters : 25
Nouveau profit total : 15 235

2 FORMULAIRES
ATTENTION : Pour créer un formulaire, il faut disposer de l'onglet Développeur.
Si l’onglet Développeur n’est pas visible dans la barre des rubans il est nécessaire de
l’ajouter. Pour cela cliquer avec le bouton droit dans une partie vierge du ruban courant.
Choisir Personnaliser le ruban, puis cocher l’option Développeur dans la liste des onglets
apparaissant dans la partie droite de la fenêtre de personnalisation (voir figure suivante).
Enfin cliquer sur le bouton OK. L’onglet est alors ajouté à l’interface.

Case à cocher pour

rendre visible
l’onglet
"Développeur"

2.1 Généralités

• Sélectionner l’onglet Développeur.

Dans le groupe Contrôles, les différents contrôles se trouvent dans le menu déroulant

Excel – Aide à la décision Page 50 UT1-Capitole, L3 GET & L3 ES

 de l’Option Insérer, partie Contrôles de formulaire (et NON Contrôles ActiveX) (voir
figure suivante).

• Choisir ensuite un contrôle => vous obtenez la poignée en croix pour dessiner un objet.
• Dessiner le contrôle dans la feuille.
• Pour définir la cellule liée et la plage de valeur (voir ci-dessous § 2.2), cliquer sur le
bouton droit de la souris, choisir Format de Contrôle, puis l'onglet Contrôles.
Pour atteindre les propriétés, vous pouvez aussi cliquer sur le bouton Propriétés situé à
droite de l’Option Insérer.

2.2 Notions liées aux formulaires

Cellule liée : cellule où le résultat est affiché (type numérique ou logique suivant les
boutons).
Plage d'entrée (listes) : plage où sont saisies les données de la liste.
Paramètres spécifiques aux boutons "compteur" et "barre de défilement" : valeurs de
départ, de fin et changement de pas (valeur d'incrémentation du nombre affiché).

Exercice 44 (Facultatif): Réalisation d'un formulaire

Vous devez réaliser le formulaire et le reçu ci-dessous dans deux feuilles (chaque partie
dans une feuille).

Feuille 1 : Formulaire de saisie

Excel – Aide à la décision Page 51 UT1-Capitole, L3 GET & L3 ES

 Feuille 2 : Reçu de prime

Objectif : À partir des données entrées par l'utilisateur dans le formulaire de "Saisie des
données de prime" (Figure Feuille 1), les données variables du "Reçu de prime"
(Figure Feuille 2) doivent s'afficher automatiquement dans les zones du reçu. Il n'y a
donc aucune saisie dans le "Reçu de prime" (hormis bien entendu les libellés que vous
aurez saisis au départ).

Nota : La saisie des noms des vendeurs et des mois par l'utilisateur peut être réalisée de
2 façons :
1) soit à l'aide de l’Onglet Données, Bouton Validation des données, choix Options puis
Liste (cette solution est la plus simple) ; la flèche à droite de la cellule n'apparaît que
lorsqu'on clique sur la cellule
2) soit à l'aide du contrôle Zone de liste déroulante (le deuxième en partant de la
gauche). C'est ce choix qui a été fait sur la figure "Feuille 1".
Les autres saisies se font à l'aide des contrôles comme indiqué sur la figure "Feuille 1".
Attention : les "Boutons d'options" doivent être utilisés en groupe : créer au moins deux
boutons d'options, puis les inclure dans une "Zone De Groupe". Il suffit de définir la cellule
liée pour le premier bouton, les autres boutons de la zone de groupe utiliseront
automatiquement la même cellule.
Données :
Les noms des vendeurs sont : Vincent, François, Paul, Arthur. Le "bonus de Noël" est égal
à 500 €. Le CA du mois de chaque vendeur est compris entre 5 000 € et 30 000 €, avec un
changement de pas de 1 000 €.
Rappel : pour que l'utilisateur ne voie pas ces données (liste des vendeurs, montant du
bonus…), ni les cellules liées, vous pouvez utiliser des cellules dans une partie non visible
de la même feuille (à partir colonne P ou Q par exemple).

Cellules liées aux

contrôles du Valeurs des listes
formulaire déroulantes

Valeurs utiles
aux calculs

Astuce : on peut utiliser des calculs effectués dans les cellules du "Reçu de prime" pour
afficher certains calculs dans le formulaire lui-même (ex. : la "Prime sur CA").

Excel – Aide à la décision Page 52 UT1-Capitole, L3 GET & L3 ES

 Chapitre VII
Programmation VBA Excel

1. PREALABLE : AFFICHER L'ONGLET DEVELOPPEUR

Si l’onglet Développeur n’est pas visible dans la barre des rubans il est nécessaire de
l’ajouter. Pour cela cliquer avec le bouton droit dans une partie vierge du ruban courant.
Choisir Personnaliser le ruban, puis cocher l’option Développeur dans la liste des onglets
apparaissant dans la partie droite de la fenêtre de personnalisation (voir figure suivante).
Enfin cliquer sur le bouton OK. L’onglet est alors ajouté à l’interface.

Case à cocher pour

rendre visible
l’onglet
"Développeur"

2. LES MACROS
VBA est un langage de programmation qui permet de créer, dans Excel, des programmes
pour
- automatiser une série de commandes et appels de fonctions
- créer des interfaces utilisateurs personnalisées
On appelle ce programme en VBA une macro. Elle est enregistrée dans un module de
programme Visual Basic Application (VBA) dans un classeur Excel d’extension xlsm.

Excel – Aide à la décision Page 53 UT1-Capitole, L3 GET & L3 ES

 ATTENTION : lors de l'enregistrement de votre classeur, vous choisirez dans la zone Type
de fichier le type "Classeur Excel (prenant en charge les macros) (* .xlsm)" sinon la
programmation que vous ferez ne sera pas enregistrée dans le fichier.

2.1 Créer une macro sous Visual Basic

Lancer Visual Basic (onglet Développeur, groupe Code, bouton Visual Basic ).
Dans l'Éditeur Visual Basic, les macros que vous avez créées sont regroupées dans des
Modules associés aux classeurs (ouvrir si nécessaire le répertoire Modules, dans la partie
gauche de l'éditeur).

Quand vous êtes positionné dans un module, vous pouvez saisir le texte d'une nouvelle
macro à la suite des macros précédentes.
Vous pouvez aussi créer un nouveau module : Menu Insertion de Visual basic, commande
Module ou dans le menu contextuel du répertoire Modules

2.2 Exécution d'une macro depuis l'éditeur Visual Basic

Pour cela, cliquez n'importe où dans le texte de la macro, puis sur le bouton Exécuter
Sub/User Form montré dans la figure suivante. Conseil : réduisez la fenêtre de l'éditeur, de
façon à voir en même temps le classeur Excel et le résultat de l'exécution de la macro.

Excel – Aide à la décision Page 54 UT1-Capitole, L3 GET & L3 ES

 3. NOTIONS DE PROGRAMMATION

3.1 Les macros

Toute macro doit obligatoirement commencer par l'instruction "Sub", suivie du nom de la
macro et de () contenant si nécessaire des paramètres. Elle se termine par "End Sub".
Ex. : Sub MaBelleMacro ()
Cells(1, 2) = "Vive l'informatique"
End Sub
Les lignes de code d'une macro s'exécutent séquentiellement.
Certaines instructions, dites "de contrôle", permettent de répéter des groupes d'instructions,
ou de les exécuter quand certaines conditions sont remplies. Nous ne verrons par la suite
que l’instructions de contrôle conditionnelle : le If.

Bonne pratique 7
Indenter les différents niveaux d'instruction
L’indentation, c’est à dire le décalage de la ligne de code vers la droite,
sert à visualiser plus facilement les différents blocs d’instructions du
programme

3.2 Les références de cellules et de plages

Dans VB, il y a plusieurs manières de noter les références d'une plage.

Pour désigner une seule cellule :
Range("A1") pour la cellule A1,
Cells(1,2) pour la cellule B1
Attention : le premier paramètre de cells() représente la ligne, le deuxième la colonne.
cells (n° de ligne, n° de colonne)

Pour désigner une plage :

Range("A1:B8")
Range(Cells(1,1), Cells(8,2)) pour la plage A1:B8

3.3 L’affectation

L’affectation, dont le symbole est =, correspond à la mémorisation d’une valeur.

Dans le cas d’une affectation dans une cellule, la valeur est affichée dans le même temps.
Exemples d'utilisation
Cells(3,3) = "Montant" 'affecte (c'est-à-dire mémorise et affiche) la chaîne de
caractères "Montant" dans la cellule C3
Cells(3,4) = 5 'affecte le chiffre 5 dans la cellule D3
Cells(4,4)=Cells(3,4)+2 'affecte le contenu de la cellule D3 + 2 dans la cellule E4

Excel – Aide à la décision Page 55 UT1-Capitole, L3 GET & L3 ES

 Exercice 45 : Affectations de valeurs à des cellules

Créer un nouveau fichier [Link]. Ouvrir l’éditeur VB, créer un nouveau module (cf
2.1). Y saisir une nouvelle macro nommée Affecte1 qui affiche le nombre 120 dans la cellule
A2, le nombre 5 dans la cellule A3 et le résultat de la multiplication des deux nombres dans
la cellule A4. Pour faire référence aux cellules, vous utiliserez l'écriture Cells(n°ligne,
n°colonne), et NON "A2", "A3", "A4".
Exécutez la macro et vérifier le résultat dans la feuille de calcul.

3.4 Les variables

Les variables sont des emplacements dans la mémoire de l'ordinateur que les macros
peuvent utiliser.
Le nom de la variable permet d'accéder aux données qu'elle contient.
Les variables ont un type, qui correspond au type des données qu'elles peuvent contenir :
entier, logique, chaîne de caractères, plage, etc.
Exemple de nom de variable : MaBelleVariable
Exemple d’affectation : MaBelleVariable = "Le ciel est bleu"
La variable MaBelleVariable contient la chaîne de caractères "Le ciel est bleu"

Les variables ne sont pas affichées dans la feuille Excel, elles ne sont donc pas visibles
(contrairement aux cellules), sauf si on demande leur affichage (en général dans une
cellule).
Exemple d’affichage par affectation : Cells(1,1) = MaBelleVariable
La cellule A1 contient et affiche la chaîne de caractères "Le ciel est bleu"

Le contenu des variables peut être modifié pendant l'exécution d'une macro.
Exemple :
Chiffre1 = 4 'affecte l’entier 4 à la variable Chiffre1 (mémorise sans afficher)
Chiffre1 = 5 ‘modifie la valeur de Chiffre1 en remplaçant 4 par 5
Chiffre2 = 10 'affecte la valeur 10 à la variable Chiffre2
Cells(1,1) = Chiffre1 + Chiffre2 'affecte le résultat de l'opération (c'est-à-dire 15)
à la cellule A1 (c'est-à-dire mémorise et affiche)

Exercice 46 : Utilisation de variables

Dans le fichier [Link], ajouter une nouvelle macro Affecte2 qui :

- Affiche le texte "A payer" dans la cellule A2
- Affecter à une variable Montant la valeur 100
- Affecter à une variable TVA la valeur 0.2
- Calculer et affecter le montant TTC dans la cellule B2 (doit afficher 120)

Excel – Aide à la décision Page 56 UT1-Capitole, L3 GET & L3 ES

 3.5 La fonction InputBox()

La fonction InputBox permet, lors de l'exécution de la macro, de demander à l'utilisateur

d'entrer une valeur.
La fonction InputBox comporte plusieurs paramètres, dont seul le premier (texte du
message qui s'affichera dans la fenêtre de saisie) est obligatoire.

Ex. Cells(1,1) = InputBox("Quelle était la couleur du panache d'Henri IV ?")

'affichera la boîte de saisie suivante

Le résultat de la saisie, par exemple ‘Blanc’, est affecté à la cellule A1

Attention ! La valeur saisie par un InputBox est de type chaine de caractère. Pour
convertir cette valeur en entier, vous devez le combiner avec une fonction de conversion :
• Exemple pour un entier : nombre = Cint ( InputBox("Entrez un nombre") )
Fonctions de conversions existantes : [Link]
fr/office/vba/language/concepts/getting-started/type-conversion-functions

Exercice 47 : Saisie d’une valeur par l’utilisateur

Dans le fichier [Link], ajouter une nouvelle macro Saisie qui :

- Efface le contenu de la feuille active
- Affiche le texte "Montant" dans la cellule A2
- Demande à l’utilisateur la valeur du montant et l’affecte à la cellule B2
- Affecter à une variable TVA la valeur 0.2
- Affiche le texte "A payer" dans la cellule A3
- Calculer et affecter le montant TTC dans la cellule B3
Tester avec plusieurs jeux d’essais (c’est-à-dire valeurs saisies)

3.6 La réalisation conditionnelle d'instructions : If

L'instruction If permet de conditionner l'exécution d'instructions à la vérification d'une

condition donnée.
Exemple :
Sub Test_Chiffre()
If Cells(4,1) > 400 Then
Cells(4,2) = "Le chiffre inscrit en A4 est supérieur à 400"
End If
End Sub

Deux cas possibles :

- La cellule A4 contient une valeur supérieure à 400 (condition vraie) : le message
est affiché dans la cellule B4

Excel – Aide à la décision Page 57 UT1-Capitole, L3 GET & L3 ES

 - La cellule A4 contient une valeur inférieure ou égale à 400 (condition fausse) :
aucune action car pas de code pour ce cas.

L'instruction If peut être complétée de Else. Les instructions qui suivent Else seront
exécutées dans le cas où la condition indiquée après If n'est pas vérifiée.
Sub Test_Chiffre()
If Cells(4,1) > 400 Then
Cells(4,2) = "Le chiffre inscrit en A4 est supérieur à 400 !"
Else
Cells(4,2) = "Le chiffre en A4 est inférieur ou égal à 400 !"
End If
End Sub

Deux cas possibles :

- La cellule A4 contient une valeur supérieure à 400 (condition vraie) : le premier
message est affiché dans la cellule B4
- La cellule A4 contient une valeur inférieure ou égale à 400 (condition fausse) : le
deuxième message est affiché dans la cellule B4

Exercice 48 : Que fait la macro suivante ?

Dans un nouveau fichier [Link], créer un nouveau module et y saisir la macro suivante
Sub Groupes()
Cells(1, 1) = Ucase(InputBox("entrez votre nom"))
If Cells(1, 1) > "M" Then
Cells(1, 2) = "Vous êtes dans le groupe 2"
Else
Cells(1, 2) = "Vous êtes dans le groupe 1"
End If
End Sub
Questions : que produit l'exécution de la macro avec les noms suivants : Babar, Zorro,
Mandraque ?
Nota 1 : la fonction Ucase() retourne en lettres majuscules la chaîne de caractères passée en
paramètre.
Nota 2 : Les lettres peuvent se comparer selon l’ordre : A < B < C … <Z.

Exercice 49 : Structures conditionnelles multiples

Dans le fichier [Link], ajouter une nouvelle macro Groupes2 qui :

- Affiche le texte "Satut :" dans la cellule A1
- Demande à l’utilisateur son statut (Etudiant, Alternant, Autre) et l’affecte la cellule
B1
- Affiche dans la cellule C1 un des messages suivants :
o "Vous êtes dans le groupe 1" pour le statut Etudiant
o "Vous êtes dans le groupe 2" pour le statut Alternant
o "Vous êtes dans le groupe 3" pour le statut Autre

Excel – Aide à la décision Page 58 UT1-Capitole, L3 GET & L3 ES

 Nota : On ne traite pas les cas d’erreurs de saisie de l’utilisateur
1) Réaliser cette macro avec 3 structures conditionnelles simples (sans ELSE ) successives
2) Comme pour la fonction if() d’Excel, les if en VB peuvent s’imbriquer. Modifier cette
macro pour transformer les structures conditionnelles simples en imbriquées.

Bonne pratique 8
Réaliser des jeux de tests complets pour vous assurer de la
validité de votre code
Un jeu de test complet testera tous les cas des structures
conditionnelles

Exercice 50 (facultatif) : Structures conditionnelles sur intervalles

Dans le fichier [Link], ajouter une macro Groupes3 pour que :

- Les noms supérieurs à R soient dans le groupe 3
- Les noms supérieurs à M soient dans le groupe 2
- Les noms inférieurs ou égaux à M soient dans le groupe 1
Attention : dans ces cas, les conditions portent sur des intervalles de valeurs ce qui
nécessite :
- Des conditions combinées avec des and pour les if sans else
- Une imbrication des if qui se fait par ordre croissant (ou décroissant) des conditions

4. UN INDICATEUR ELABORE
Les notions de programmation vues précédemment vont permettre la réalisation
d’indicateurs plus élaborés que les graphiques par défaut d’Excel.

Exercice 51 : Un feu d’alerte

Dans le classeur Tableau [Link], copier la feuille Données dans un nouveau fichier
Emprunt [Link].
Objectif : Au lieu d’afficher un message d’alerte en cellule F4 lorsque la mensualité de
remboursement dépasse le budget limite, nous allons afficher cette alerte dans un indicateur
visuel.

Excel – Aide à la décision Page 59 UT1-Capitole, L3 GET & L3 ES

 1) Création des objets visuels
- Utiliser le menu Insertion / Illustrations / Formes pour créer le feu suivant
Le rectangle gris est mis en arrière-plan
Renommez les rectangles RectRouge et RectVert dans la zone de noms, ils
serviront pour changer les couleurs de ce feu.

2) Programmation
Ouvrir l’éditeur VB, créer un nouveau module. Y saisir une nouvelle macro nommée Feu qui :
- affecte la cellule E4 à une variable Mensualite
- affecte la cellule G4 à une variable Limite
- si la mensualité est inférieure à la limite, le feu aura le vert allumé et le rouge éteint sinon
l’inverse.

Aide : pour gérer le changement de couleur du feu, vous aller utiliser la propriété
[Link] des objets rectangles créés.
Par exemple :
[Link]("RectRouge").[Link] = RGB(0,0,0)
Sélectionne la forme RectRouge de la feuille active pour affecter les composantes Red=0,
Green=0 et Blue=0 au remplissage de sa couleur de fond => met en noir le fond du rectangle
RectRouge
Autre composantes couleur :
RGB(255,0,0) met en rouge, RGB(0,255,0) met en vert
Tester les deux possibilités pour voir si votre feu marche

3) Gérer le lancement de cette macro

La macro feu doit être exécutée dès qu’il y a un changement dans les données (soit de calcul
du remboursement, soit la limite).
Dans l’événement Worksheet_Change de votre feuille, saisir le code suivant

Attention : On ne peut pas juste appeler notre macro car elle serait exécutée dès qu’il y a un
changement dans la feuille. Or, changer la couleur du feu est un changement, cela relancerait
la procédure et on aboutirait à une boucle infinie.

4) Améliorations possibles :
- chercher des composantes RGB pour changer la couleur noire
- mettre un feu tricolore. Il vous faudra alors deux valeurs pour les limites budget

Excel – Aide à la décision Page 60 UT1-Capitole, L3 GET & L3 ES

 5. LES BOUCLES EN PROGRAMMATION (FACULTATIF)
Les boucles en programmation permettent de répéter plusieurs fois des
instructions sans avoir à les réécrire.

5.1 La boucle "For"

La boucle "For" permet la répétition, un nombre de fois connu, d’un groupe d'instructions.
Elle encadre les instructions à répétées avec For (en début) et Next (en fin).
Nota : indenter le groupe d’instructions à répéter pour mieux le visualiser
La boucle For nécessite l’utilisation d’une variable compteur pour le nombre de répétitions
(ou itérations)
Ex. Sub ExempleBoucleFor()
For i = 1 To 5
Cells(i, 3) = "Bonjour"
Next i
End Sub
Cette macro affiche "Bonjour" dans 5 cellules (les cellules C1 à C5) car la variable
compteur i prend successivement les valeurs 1, 2, 3, 4 et 5 (soit 5 répétitions)

Exercice 52 : La boucle For et les petites marionnettes

Attention : il y a 4 macros à faire successivement, à écrire dans un nouveau module d’un

nouveau fichier [Link]
1) Réalisez une macro Marionnette1 qui affiche les données suivantes en colonne :

Vous utiliserez la boucle For pour faire afficher "font" dans les trois cellules concernées.
Aide : Ecrire les 3 lignes de code si on ne faisait pas de boucle pour trouver la valeur qui varie
dans ces lignes de code. Vous avez alors les bornes de la variable compteur
2) Recopiez la macro précédente, changez son nom en Marionnette2, et modifiez-la pour que
les données s'affichent sur une même ligne (cellules A1 à E1) comme suit :

3) Recopiez la macro précédente, changez son nom Marionnette3, et modifiez-la pour que les
données s'affichent en diagonale comme suit :

4) Recopiez la macro Marionnette2, changez son nom en Marionnette4, et modifiez-la afin

qu'elle demande à l'utilisateur combien de fois il désire que le mot "font" s'affiche, et faites
afficher en fonction de sa demande. (Vous aurez besoin d’une variable en plus).

Excel – Aide à la décision Page 61 UT1-Capitole, L3 GET & L3 ES

 Ex. : si l'utilisateur répond 5, votre macro affichera (cellules A1 à G1)

Tester avec un grand nombre de répétitions et imaginez combien de lignes de codes auraient
été nécessaires sans le for ! d’où l’intérêt d’une boucle …

5.2 La boucle Do While

La boucle Do While permet la répétition d’un bloc d'instructions aussi longtemps qu'une
condition reste vraie.
Exemple :
Sub Liste_étudiants()
' Macro qui affiche les noms des étudiants saisis dans des cellules consécutives.
‘ Quand l’utilisateur tape sur la touche "Enter" sans saisir de nom, la boucle s’arrête
Continuer = True
i=1
Do While Continuer = True 'On peut aussi écrire simplement While Continuer
Cells(i, 1) = InputBox("Entrez le nom de l'étudiant n° " & i)
If Cells(i, 1) = "" Then
Continuer = False
End If
i=i+1
Loop
End Sub
Nota 1 : Les premières lignes de la macro précédées du caractère ‘ se nomment des
commentaires. Elles servent aux programmeurs et ne sont pas visibles par les utilisateurs.
Nota 2 : On utilise ici une variable booléenne Continuer pour contrôler la répétition.
Cette variable est initialisée à True pour entrer au moins une fois dans la boucle. Elle
passera à False lorsque l’utilisateur ne saisit rien pour arrêter la boucle.
Nota 3 : La valeur vide s’écrit et se teste avec ""
Nota 4 : Contrairement au cas de la boucle FOR qui le fait elle-même, on doit gérer le
compteur du nombre de répétitions i. (initialisation et passage à la valeur suivante)

Exécuter cette macro en mode deboggage (voir le menu de VBA, ou touche F8) pour bien
comprendre comment Excel exécute le code de la macro.

Exercice 53 : Question pour un champion

Dans un nouveau fichier [Link],

1) Écrivez une macro Capitale qui demande "quelle est la capitale du Bangladesh ?" et
recommence tant que la réponse entrée par l'utilisateur n'est pas "Dacca".
Le programme affichera, selon la réponse de l’utilisateur, un message "Perdu ! Recommencez"
ou "gagné ! " grâce à la fonction Msgbox("message")

Excel – Aide à la décision Page 62 UT1-Capitole, L3 GET & L3 ES

 Nota : Si le critère d'arrêt d'une boucle While est mal géré, le programme "tourne"
indéfiniment. Pour forcer son arrêt => touche CTRL + touche "Pause Attn" (cette touche peut
parfois se nommer "Arrêt syst").

2) Modifiez la macro pour que le programme s'arrête quand l'utilisateur entre "Dacca", quelle
que soit la casse (majuscules, minuscules) avec laquelle le nom a été entré (dacca, DACCA,
Dacca…).

Exercice 54 : Liste de mots

Dans le fichier [Link], ajoutez une macro Mots qui :

- demande un 1er mot et l’affiche en cellule A1
- puis demande une suite de mots à l'utilisateur et les inscrit en colonne A, en le
convertissant en majuscule et en changeant de ligne pour chaque mot (voir "Exemple
résultat"). La macro arrête de demander un nouveau mot quand l'utilisateur entre un mot
qui est "plus petit" (c'est-à-dire vient avant dans l'ordre alphabétique) que celui qui le
précède.
Dans l’exemple, la saisie du mot "Ami" amis a arrêté la boucle car "Ami" < "Valise"
- La macro inscrit ensuite "Nbre de mots" en cellule C1, et le nombre total de mots entrés,
en cellule D1.

Exemple résultat : Liste de mots

Excel – Aide à la décision Page 63 UT1-Capitole, L3 GET & L3 ES

 Annexe
Logiciels libres

Outre Microsoft Office qui est installé sur le réseau pédagogique, vous avez également
accès à LibreOffice. Il s'agit d'un logiciel libre (dont le code source est visible, qui est en
"copyleft" par opposition à copyright). Selon la définition de la Free Software Foundation
(FSF), un logiciel libre est un logiciel "fourni avec une autorisation pour quiconque de
l’utiliser, de le copier, et de le distribuer, soit sous une forme conforme à l’original, soit
avec des modifications, gratuitement ou contre un certain montant".

Nota : pour plus d'information sur les logiciels libres, vous pouvez consulter divers
sites, comme par exemple : [Link] (en français), [Link] ou
[Link] (en anglais)

LibreOffice est une suite bureautique comprenant les outils habituels. Une autre suite
bureautique est disponible : OpenOffice. LibreOffice et OpenOffice sont gratuits.

Les deux suites sont très proches, par les fonctionnalités comme par les interfaces, de
la suite Microsoft Office. LibreOffice et OpenOffice sont disponibles pour la plupart des
systèmes d’exploitation (Windows, Linux, Mac OS…). Les formats de fichiers sont
compatibles avec ceux de plusieurs autres logiciels, et bien entendu avec ceux de la suite
Microsoft Office.

Vous pouvez télécharger LibreOffice ou OpenOffice sur votre ordinateur personnel.

Voici les deux liens :
[Link]
[Link]

Les tableurs des deux suites ("Classeur" pour LibreOffice, "Calc" pour OpenOffice), ont
des interfaces très similaires à celle de MS-Excel, et l’on retrouve pratiquement les mêmes
fonctionnalités.

Excel – Aide à la décision Page 64 UT1-Capitole, L3 GET & L3 ES