2017-2018
TD 2 – Excel
ENREGISTREZ VOS DOCUMENTS REGULIEREMENT
UTILISEZ LES UTILITAIRES D'AIDE
A Fonction Si()
A.1 Rappel sur les fonctions Excel
Une fonction permet de calculer un résultat à partir d'un certain nombre de paramètres ou arguments.
Un argument ou paramètre est une valeur utilisée par une fonction pour effectuer des calculs.
Une fonction se caractérise par :
1. un nom : par convention écrit en majuscules.
2. des paramètres ou arguments : le type des arguments utilisés par une fonction lui est spécifique. Une
fonction peut ne pas admettre de paramètres. Le nombre de paramètres peut être variable ou encore
certains paramètres d'une fonction peuvent être facultatifs.
Les paramètres sont donnés entre parenthèses à la suite du nom et sont séparés par un « ; ».
Un argument peut être :
a. une valeur constante
b. une référence à une cellule ou plage de cellules : référence relative, absolue, mixte ou un nom
c. une fonction
La syntaxe d'une fonction est donc : NOMFONCTION(argument1;argument2;...)
A.2 Rappel sur la fonction Si()
La fonction « Si() » permet de moduler des calculs. Par exemple:
À partir d'un certain nombre d'heures, les employés ont droit à des heures supplémentaires.
À partir d'un certain salaire, le taux d'imposition fiscale augmente.
Lorsque l'entrepôt est plein, il y a des frais de surcharge pour l'entreposage.
Lorsqu'un client achète un certain nombre d'un produit, il a droit à un rabais.
La fonction Si() a absolument besoin de trois paramètres pour fonctionner. Il faut premièrement connaître la
condition: qui a-t-il à comparer? La seconde partie est ce que la fonction doit faire lorsque la condition est vraie.
La troisième partie est ce que la fonction doit faire lorsque la condition est fausse. On peut résumer comme ceci:
=SI(condition ; vrai ; faux). Les « ; » servent à séparer les différentes parties de la fonction.
Ex.: =SI(b1>=10;b3*0,1;0) Si le contenu de la cellule B1 est supérieur ou égale à 10, affiche le résultat de B3
multiplié par 0,1. Sinon affiche zéro.
A.3 ET() et OU()
La fonction Si() peut être améliorée lorsqu'elle est utilisée avec d'autres fonctions telles que ET() et OU(). Ces
opérateurs permettent la construction d’expressions logiques (conditions) plus élaborées.
=ET(condition1;condition2;condition3 ...) Il faut au moins deux conditions. Mais vous pouvez en ajouter
d'autres. Toutes les conditions doivent être remplies pour que la
fonction affiche VRAI. Sinon, elle affichera FAUX.
=OU(condition1;condition2; condition3 ...) Il faut au moins deux conditions. Vous pouvez en ajouter d'autres.
L'une des conditions doit être remplie pour que la fonction affiche
VRAI. Si aucune des conditions n’est remplie, elle affichera FAUX.
Exemple :
1
2017-2018
Figure 1 : Utilisation des fonctions ET() et OU()
Il est possible d'ajouter ces fonctions, ou tout autre, dans la fonction Si(). Pour ces deux dernières fonctions, il est
préférable de les placer dans la partie des conditions.
Insérer les deux formules suivantes successivement dans les cellules A3 et B3 puis vérifier les résultats de ces
fonctions en modifiant le contenu des cellules A1 et A2.
=Si(et(a1=1;a2=1);1000;0)
=Si(ou(a1=1;a2=1);1000;0)
Ceci vous donne plus de potentiel pour la fonction Si() puisque vous pouvez couvrir plusieurs conditions en même
temps.
A.4 Exercices
A.4.1 Exercice guidé
Pour mieux vous faire comprendre le fonctionnement de la fonction Si() et de son potentiel, la partie qui suit vous
donne quelques exercices. On se place dans le contexte d'un marchand qui veut offrir un rabais à ses clients s'ils
achètent une certaine quantité.
Avant de commencer, ouvrez une nouvelle feuille de calcul ou un classeur.
Remplissez les cellules suivantes avec le texte et les formules suivantes.
Figure 2 : Données initiales pour le calcul d’un rabais sur marchandises
Pour le premier exemple, le marchand va offrir un rabais encore indéterminé si le client achète 10 articles
identiques ou plus. L’exercice doit permettre d’afficher si oui ou non le client a droit à un rabais.
Cellule B10 : Utilisation de la fonction Si pour afficher un message texte : Selon la quantité qui est entrée
dans la cellule B5, la cellule B10 doit afficher soit « Rabais accordé » ou « Désolé, aucun rabais ». Cette
fonction a les trois parties. En premier la condition pour vérifier si le contenu de la cellule B5 (le nombre
d’articles) est supérieur ou égal à 10. Si oui, la fonction affiche le premier message « Rabais accordé ».
Sinon, elle affiche le deuxième message « Désolé, aucun rabais ».
2
2017-2018
La fonction suivante a pour résultat une formule ou un nombre. Le marchand s'est décidé. Il offre 10%
de rabais à ceux qui achètent 10 articles identiques ou plus. La prochaine formule va calculer ce rabais.
Cellule B11, Utilisation de la fonction Si pour calculer un résultat : La condition est la même que la
formule précédente. Pourtant, le résultat est différent. Le rabais va changer selon la quantité saisie dans
la cellule B5. Si la quantité est égale ou plus grande que 10, il y aura un rabais de 10% sur le total de la
cellule B6. Sinon, il n'y a pas de rabais. Donc, la valeur est zéro (0).
Le troisième exercice consiste à voir les fonctions Si() imbriquées. Il est possible d'avoir des fonctions
Si() dans des Si(). En fait, il est possible d'avoir des fonctions dans des fonctions. Par exemple, =ABS
(SOMME(B1:B200)) vous donne la valeur absolue de la somme des cellules B1 à B200. Il est possible
d'imbriquer jusqu'à six Si() l'un dans l'autre. Le prochain exercice vous en donne l'exemple.
Cellule B12 : Le marchand s'aperçoit que son rabais ne fait pas l'affaire de tous. Il ajuste donc sa
démarche. Il offre un rabais de 5% à ceux qui achètent 5 articles identiques ou plus. En plus, il donne
toujours un rabais de 10% si un client achète 10 articles identiques ou plus. Proposer une formule
permettant de calculer l’ensemble des rabais proposés.
A.4.2 Exercice
Objectif
Maîtriser la notion de condition vue précédemment sur différents exercices avancés. Les fonctions abordées ici
sont Si(), [Link] et [Link].
Enoncé
Afin de faciliter la lecture du tableau de notes finales concernant un groupe de 9 étudiants, nous allons mettre en
place un ensemble de fonctions en nous appuyant notamment sur la notion de condition. Pour cet exercice, nous
allons utiliser et enrichir le fichier Excel [Link].
Le fichier [Link] est décomposé de la façon suivante :
- Feuille ListeEtudiants : liste (Nom et Prénom) de 9 étudiants formant le groupe de travail ;
- Feuille Notes : Notes dans les matières Droit, Economie et Informatique pour l’ensemble des étudiants.
La moyenne de chaque étudiant est également calculée.
- Feuille Coefficients : Coefficient pour chacune des trois matières Droit, Economie et Informatique.
Questions
Question 1 - Calcul du résultat en fin d’année
Dans la feuille Notes, ajouter une colonne, « Résultat », dans laquelle viendra s'inscrire « Ajourné »
(moyenne < 8), « Rattrapage » (8 <= moyenne < 10), ou « Admis » (moyenne > =10).
Question 2 - Gestion des mentions
Ajouter une colonne « Mention » dans laquelle viendront s'inscrire automatiquement les mentions des
étudiants (TB, B, AB, Passable).
Question 3 - Obtention du C2I
Ajouter une colonne, « C2I », dans laquelle viendra s'inscrire « OUI » si l'étudiant a obtenu le C2i et NON
si ce n’est pas le cas. Pour information, le C2i est obtenu si l'étudiant a une moyenne en informatique >12
ou s'il a une mention AB au moins.
Question 4 - Prise en compte des coefficients
Ajouter une colonne « Moyenne avec coefficients » et recalculer la moyenne de chaque étudiant en
prenant en compte les coefficients (présents dans la feuille coefficients).
Question 5 : Nombre d’étudiants ayant la moyenne
Calculer le nombre d'étudiants qui ont une moyenne supérieure à 10 (C21 pour l’intitulé, D21 pour le
résultat).
Indication : voir la fonction [Link]
3
2017-2018
Question 6 : pour aller plus loin
Calculer la moyenne des étudiants qui ont obtenu une mention AB (C22 pour l’intitulé, D22 pour le
résultat).
On peut remarquer que pour les étudiants dont on n'a pas saisi les notes, les calculs se font tout de même.
Modifier vos formules de façon à ce que rien ne s'affiche lorsqu'aucune valeur n'a été saisie. Indication :
[Link], SI, [Link], estvide, etc.
Résultats attendus
Figure 3 : Résultats attendus à la fin de l’exercice
B Les fonctions RechercheV() et RechercheH()
B.1.1 Définition
Nous appellerons référence, toute information permettant d'accéder à un ensemble d'informations.
Suivant l'organisation de ces références dans les feuilles, Excel met à notre disposition des formules de recherche.
Nous aborderons par la suite, deux fonctions de recherche : RechercheV() et RechercheH() suivant que les
informations sont référencées en colonne ou en ligne dans les feuilles.
Les fonctions RechercheV() et RechercheH() ont besoin de quatre paramètres. Le premier est l'adresse de la cellule
qui sera comparée. Elle sera comparée au contenu du deuxième paramètre qui contiendra l'adresse du bloc de
cellules d'un tableau de comparaison. Le troisième paramètre indique le contenu de la colonne à afficher (pour
RechercheV()) ou de la ligne (pour RechercheH()) à afficher au sein du tableau de comparaison. Le dernier
paramètre est une valeur logique qui permet d’effectuer une recherche exacte ou voisine de celle recherchée. Si la
valeur de ce paramètre est VRAI ou omise, une donnée égale ou immédiatement inférieure à la valeur recherchée
est affichée. Si sa valeur est FAUX, seule la valeur recherchée s’affiche.
RechercheV(cellule à comparer ; tableau de comparaison ; index de colonne ; valeur_proche)
Remarque :
- Si vous devez écrire une formule qui doit être recopiée (vers le bas ou vers le haut), alors il faudra penser
à bloquer les coordonnées de la matrice (tableau de comparaison) afin qu'elles ne bougent pas avec la
4
2017-2018
recopie. Dans ce cas là, la manière la plus simple de procéder consiste à nommer la matrice : menu
Formule/Définir Nom.
- Pour la RechercheV() La ligne comprenant les entêtes des colonnes ne doivent pas être prises en compte
dans la matrice (le tableau de comparaison).
- RechercheV(), RechercheH() imbriquées : il se peut que l'information qui serve de référence dans une
recherche ne soit pas directement accessible, qu'il faille d'abord aller la chercher avant de faire la
recherche. Dans ce cas, on imbriquera des fonctions de recherche.
B.2 Exercices
B.2.1 Exercice guidé
Cet exercice consiste à changer dans quelques colonnes la fonction RechercheV() qui va chercher l'information à
afficher selon le type de client. Pour cet exercice, il y a trois catégories de clients: type 1, 2 et 3 et pour chaque
catégorie, des taux de réduction sont calculés en fonction de la quantité de produits achetés. La fonction devra
chercher l'information à afficher dans la deuxième, la troisième ou la quatrième colonne du tableau; soit le type de
client + 1 puisque la première colonne du tableau est utilisée pour la grille de comparaison pour déterminer la
quantité du rabais.
Créez un nouveau classeur, enregistrez le sous le nom test_RechercheV.xls et remplissez le tableau en
suivant la figure suivante :
Figure 4 : Données initiales utiles pour l’exercice utilisant la fonction RechercheV()
Cellule B3, écrivez la formule suivante: =RechercheV(B1;A7:D9;D1 + 1). Essayez ensuite les
combinaisons suivantes de chiffres dans les cellules B1 et D1.
B1 D1 Résultat
4 1 0%
7 1 2,5%
7 2 5%
7 3 7,5%
10 3 12,5%
-5 1 #N/A
10 4 #REF!
Toutes les valeurs fonctionnent sauf pour les deux derniers cas. Pouvez-vous l’expliquer ?
5
2017-2018
B.2.2 Exercice 1
Objectif
Maîtriser la notion de recherche sur un exemple simple. Les fonctions abordées ici sont RechercheV() et
RechercheH().
Enoncé
Afin de faciliter le travail de la scolarité, on souhaite saisir un minimum d'informations sur la feuille Récapitulatif.
Les fonctions de recherche rechercheV() et rechercheH() présentées précédemment sont justement définies pour
éviter des saisies répétées et nous allons donc les utiliser.
Pour les besoins de l’exercice, télécharger le classeur [Link] qui servira de base de travail. Les données
concernant les étudiants sont rangées verticalement suivant une référence qui est leur code (cf. feuille Notes). A
partir du code d'un étudiant, il est possible d'accéder à l'ensemble des informations concernant cet étudiant. Ainsi,
il est tout à fait possible dans la feuille récapitulative, de ne saisir que le code d'un étudiant et de remplir
automatiquement toutes les informations le concernant : nom, prénom, note de droit, note d'éco et note d'info.
Notes de travail
Dans la feuille Paramètres : afin de faciliter l'écriture des formules de recherche concernant les
coefficients des matières, nommer la plage B2:D3 en MatCoef.
Dans la feuille Notes : afin de faciliter l'écriture des formules de recherche concernant les informations
relatives à un étudiant, nommer la plage A3:F15 en MatNotes.
Pourquoi met-on des lignes vides dans cette matrice ?
Questions
Question 1 : Recherche et affichage d’informations relatives à un étudiant à partir de sa référence
Le travail est à réaliser dans la feuille Récapitulatif
Rechercher le nom, le prénom, la note de droit, la note d’économie et la note d’informatique de l'étudiant
à partir de son code saisi en B8.
Indications : cette formule doit être recopiée de la ligne 8 à la ligne 20.
Remarque : que se passe-t-il si un code étudiant n'est pas renseigné ou inexact ? Comment remédier à ce
problème lors de la recherche des informations relatives à un étudiant? Ecrire une formule qui permet de
ne rien afficher si le code n'est pas saisi et d'afficher « Num_inconnu » si le code de l'étudiant est inexact.
Indications : utiliser les fonctions SI(), ESTVIDE(), ESTNA().
Question 2 : Recherche et affichage de coefficients
Le travail est à réaliser dans la feuille Récapitulatif
Cellule E7 : Rechercher le coefficient associé à la matière dont le nom est inscrit en E6. Cette formule
permettra d'afficher « Mat_inconnue » dans le cas où la matière n'est pas connue.
Recopier cette formule en F7 et G7. Que se passe-t-il ? D'où vient l'erreur ? Faire les modifications
nécessaires.
Question 3 : Travail en autonomie
Le travail est à réaliser dans la feuille Récapitulatif, puis dans la feuille Analyse
En reprenant le travail réalisé jusque là, mettre à jour les formules de H8 à L20, et les formules de la
feuille Analyse.
6
2017-2018
Résultats attendus
Figure 5 : Feuille Récapitulatif - Résultat attendu à la fin de l’exercice
Figure 6 : Feuille Analyse - Résultat attendu à la fin de l’exercice
B.2.3 Exercice 2
Objectif
Manipuler l’ensemble des fonctions étudiées dans les exercices précédents (conditions, recherches, etc.)
Enoncé
Nous allons définir un Convertisseur de Devises, outil monétaire surement le plus populaire au monde. Il doit
permettre d'effectuer des conversions et offre un ensemble des cours de devises étrangères.
L’outil doit permettre à l’utilisateur de choisir les devises de départ et d’arrivée utilisées pour la conversion parmi
une liste de devises pré-saisies, puis de saisir le montant à convertir dans une zone de texte. Le calcul de la
conversion doit se faire automatiquement une fois l’ensemble des informations saisies.
Notes de travail
Pour les besoins de l’exercice, télécharger le classeur [Link] qui servira de base de travail. Les
données concernant les devises sont rangées verticalement (cf. feuille Feuil1).
L’ensemble des fonctions seront à saisir sur la feuille Feuil1 dans le tableau « Convertisseur ».
Questions
Question 1 : Recherche et affichage d’informations liées aux devises
A partir des devises de départ et d’arrivée saisies manuellement, proposer une fonction permettant
d’afficher pour chaque devise le pays correspondant.
7
2017-2018
Question 2 : Calcul de la conversion
Proposer une fonction qui, à partir de la devise de départ et du montant initial saisi, calcule le montant
dans la devise d’arrivée.
Indications : Pour vérifier les résultats obtenus, 100USD 72,85€, 100USD 97,79CHF, 100€
137,26USD.
Résultats attendus
Figure 7 : Résultats attendus à la fin de l’exercice
