Université de La Rochelle Informatique Transversale
Partie 1
TP n°7 : Fonctions conditionnelles, fonctions
logiques.
Objectif du TP :
Le but de cette séance est de réinvestir les compétences acquises lors des
séances précédentes. Nous découvrirons les fonctions conditionnelles qui
permettent d'afficher une valeur ou d'effectuer un calcul en fonction d'un
critère donné.
Pour la seconde partie, vous aurez le choix entre la notion de pourcentages et
celle de corrélation, présentées un peu plus loin dans ce document.
Table des matières
I. Préparation......................................................................................................1
II. Révisions.........................................................................................................2
III. Fonction Si.....................................................................................................2
III.1- Principe...................................................................................................2
III.2- Application..............................................................................................2
IV. La fonction de dénombrement conditionnel : [Link].......................................4
IV.1- Principe...................................................................................................4
IV.2- Application..............................................................................................4
IV.3- Précautions à prendre.............................................................................5
V. La fonction de somme conditionnelle : [Link]..........................................6
V.1- Principe....................................................................................................6
V.2- Application...............................................................................................6
I. Données utilisées............................................................................................8
II. Travail à réaliser au cours de la séance..........................................................8
II.1- Bilan global..............................................................................................8
II.2- Bilan par région en 2003.........................................................................9
II.3- Bilan par région pour toute la période...................................................10
II.4- Bilan par ville.........................................................................................12
III. Dépôt sur Moodle.........................................................................................12
I. Préparation
Comme d’habitude, créez un dossier « TP07» dans votre dossier « Informatique
Transversale ». Récupérez sur Moodle, l’archive « Maté[Link] » et
décompressez-la dans votre dossier « TP07».
Page 1/12
Université de La Rochelle Informatique Transversale
Partie 1
PARTIE 1 : Fonctions conditionnelles
II. Révisions
Nous allons étudier les trois fonctions conditionnelles : Si, [Link] et [Link] .
Nous les appliquerons dans le même exercice. Mais avant de rentrer dans le vif
du sujet, voici quelques questions pour réviser.
Ouvrir le fichier [Link]
En C20, calculer la somme des notes du bulletin de l'étudiant Durand.
Attention : le bulletin n'est pas complet mais les formules devront toujours
être valables si on ajoute des notes
En C22, calculer le nombre de notes du bulletin
En C25 et C26, calculer la moyenne des notes en utilisant 2 méthodes
différentes (fonction moyenne ou somme / nb notes)
III. Fonction Si
III.1- Principe
Au cours des séances précédentes, chaque cellule ne contenait qu'un seul
élément (date, texte, valeur numérique). Cependant, il est parfois utile de
pouvoir adapter le contenu d'une cellule en fonction d'un critère donné.
La vidéo ci-dessous explique brièvement les principes de la fonction SI.
Vidéo 2 : Exercice « Notes » fonction SI
III.2- Application
Notre exemple : Dans la cellule C28, on souhaite afficher "Admis", si la note
est supérieure ou égale à 10 sinon on affichera "Ajourné". Cette opération est
possible avec la fonction SI.
Syntaxe : SI(test_logique; valeur_si_vrai ; valeur_si_faux)
La fonction renvoie une valeur si la condition que vous spécifiez est VRAIE et
une autre valeur si cette valeur est FAUSSE.
• test_logique : représente toute valeur ou expression qui peut prendre la
valeur VRAI ou FAUX. Par exemple : A10 = 100 est une expression
Page 2/12
Université de La Rochelle Informatique Transversale
Partie 1
logique. Si la valeur contenue dans la cellule A10 est égale à 100, le
résultat de l'expression est VRAI. Dans le cas contraire, le résultat est
FAUX. Cet argument peut utiliser n'importe quel opérateur de calcul par
comparaison.
• valeur_si_vrai : est la valeur qui est renvoyée si le test logique est VRAI.
• valeur_si_faux : est la valeur qui est renvoyée si le test logique est
FAUX.
Comment mettre en place la fonction si en reprenant l'exemple précédent ?
Méthode 1 : Utiliser l'assistant fonctions.
Se placer en C28 et exécuter l'assistant fonctions.
Sélectionner la fonction Si.
L'assistant fonction aide l'utilisateur à fournir les paramètres utiles aux
fonctions proposées par le tableur. Ici nous devons fournir 3 paramètres :
Test_logique, Valeur_si_vrai et Valeur_si_faux .
• Test_logique : quel est le test à effectuer ?
Nous somme en C28, nous nous demandons si la moyenne située en C25 est
supérieure ou égale à 10, dans le champ permettant la saisie du paramètre
Test_logique, nous écrivons C25 >= 10 .
• Valeur_si_vrai : quel est le résultat à afficher si le résultat du test est
vrai ?
Si C25 >= 10 est vrai, nous voulons afficher Admis. Ecrivons donc "Admis"
(avec les guillemets car il s'agit de texte) dans le champ Valeur_si_vrai.
• Valeur_si_faux : quel est le résultat à afficher si le résultat du test est
faux ?
Si C25 >= 10 est faux, nous voulons afficher Ajourné, écrivons donc "Ajourné"
dans ce champ.
• Appuyer sur OK pour valider la formule.
Méthode 2 : Écrire la formule
Si vous connaissez la syntaxe de la fonction SI, il suffit d'écrire directement
dans la cellule C28 : = SI(C25 >= 10 ; "Admis" ; "Ajourné" );
Saisir la fonction SI dans la cellule C28 (si ce n'est pas encore fait).
Page 3/12
Université de La Rochelle Informatique Transversale
Partie 1
Vérifier que la formule fonctionne correctement en modifiant les notes de
façon à ce que la moyenne soit supérieure à 10. Le résultat doit passer
automatiquement à "Admis".
En C30 on désire afficher "Oui" si Durand mérite une mention, sinon on
affichera "Non".
Etablir la formule en C30
Remarque : Pour avoir une mention, il faut avoir une moyenne supérieure ou
égale à 12.
IV. La fonction de dénombrement conditionnel : [Link]
IV.1- Principe
La fonction NB étudiée lors de la séance précédente permet de comptabiliser
toutes les cellules qui contiennent des valeurs numériques. Cependant, il est
souvent utile de pouvoir compter les cellules qui vérifient un certain critère. La
fonction [Link] doit alors être utilisée.
La vidéo suivante, présente la fonction [Link] :
Vidéo 3 : Fonctions conditionnelles
IV.2- Application
Notre exemple : Dans la cellule G4, on souhaite déterminer le nombre de
notes de Durand dans la matière Informatique. Cette opération est possible
avec la fonction [Link].
Syntaxe : [Link](plage_de_cellules ; critères)
• plage_de_cellules : correspond à la plage à laquelle les critères sont à
appliquer.
• critères : indique le critère sous la forme d'un nombre, d'une expression
ou d'une chaîne de caractères.
Comment mettre en place la fonction [Link] en reprenant l'exemple précédent ?
Se placer en G4 et exécuter l'assistant fonctions.
Sélectionner la fonction [Link].
Page 4/12
Université de La Rochelle Informatique Transversale
Partie 1
L'assistant fonction vous indique que vous avez 2 paramètres à fournir : plage
et critères.
Il s'agit de compter combien de fois "Informatique" apparaît dans la liste des
matières du bulletin.
• Le critère est donc "Informatique".
• La plage dans laquelle il s'agit de compter est celle où peut figurer le mot
"Informatique", c'est-à-dire B4:B18. Il suffit de se placer dans le champ
Plage, d'appuyer sur pour revenir dans la feuille de calcul, de
sélectionner les cellules B4 à B18 à la souris, puis de revenir dans
l'assistant en appuyant de nouveau sur .
• Valider votre formule en cliquant sur OK.
Saisir la formule en G4.
Vérifier que cette formule fonctionne en ajoutant une note d'informatique
dans le bulletin.
IV.3- Précautions à prendre
En G5, G6 et G7 on veut calculer respectivement le nombre de notes en
Anglais, Mathématiques et Français.
Étendre la formule réalisée en G4 aux cellules G5, G6 et G7.
Question : le résultat est-il correct ?
Se placer en G7 et appuyer sur F2 pour visualiser les éléments utilisés dans
la fonction.
Que remarquez vous en observant la formule générée par l'extension ?
Réponse : 2 problèmes doivent apparaître :
• La formule compte le nombre d'occurrences du mot "Informatique" alors
qu'en G7 nous voulons compter le nombre de notes en Français !
• La plage de cellule n'est plus B4:B18.
Se placer en G4 et corriger la formule pour ne plus avoir les deux erreurs
précédentes.
Etendre la formule réalisée en G4 aux cellules G5, G6 et G7.
Vérifier la formule en G7 (touche F2). Si la formule n'est toujours pas bonne,
Page 5/12
Université de La Rochelle Informatique Transversale
Partie 1
recommencez.
Si tout cela vous paraît encore flou, n’hésitez pas à reprendre la fiche
suivante :
Fiche : Fixer des cellules à l’aide du $
V. La fonction de somme conditionnelle : [Link]
V.1- Principe
Parallèlement à la fonction [Link] qui ne comptabilise que les cellules qui vérifie
un critère donné, le tableur dispose d'une fonction [Link]. Cette fonction
permet de calculer la somme des valeurs d’une plage de cellules qui répond à
un critère spécifié.
La vidéo suivante présente la fonction [Link]
Vidéo 3 : Fonctions conditionnelles
V.2- Application
Notre exemple : Dans la cellule H4, on souhaite déterminer la somme des
notes de Durand dans la matière Informatique. Cette opération est possible
avec la fonction [Link].
Syntaxe : [Link](Plage ; Critères ; plage_de_somme)
• Plage : correspond à la plage sur laquelle les critères sont à appliquer.
• Critères : correspond à la cellule dans laquelle s'affiche le(s) critère(s) de
recherche.
• plage_de_somme : est la plage dans laquelle les valeurs sont
additionnées. Si ce paramètre n'a pas été indiqué, les valeurs trouvées
dans la Plage sont additionnées.
Comment mettre en place la fonction [Link] en reprenant l'exemple
précédent ?
Se placer en H4 et exécuter l'assistant fonctions.
Page 6/12
Université de La Rochelle Informatique Transversale
Partie 1
Sélectionner la fonction [Link].
L'assistant fonction vous indique que vous avez 3 paramètres à fournir : plage,
critères et plage_somme.
Il s'agit de faire la somme des notes d' "Informatique".
• La plage où il faut localiser la matière "Informatique" est la plage B4:B18
• Le critère à appliquer est donc "Informatique".
• la plage qui sera utilisée pour faire la somme est la plage des notes,
c'est-à-dire C4:C18.
• Valider votre formule en cliquant sur OK.
En d'autres termes, la fonction localise toutes les notes (colonne C) dont la
matière est l' "informatique" (colonne B) et réalise la somme de ces notes.
Saisir la formule en H4.
Vérifier que cette formule fonctionne en ajoutant une note d'informatique
dans le bulletin.
Étendre la formule réalisée en H4 aux cellules H5, H6 et H7.
Comme pour la fonction [Link], vérifier que le résultat est cohérent
Corriger la formule en H4, si nécessaire avant d'étendre à nouveau la
formule.
Compléter la cellule I4 (moyenne des notes en Informatique) et étendre
cette formule.
Si tout cela vous paraît encore flou, n’hésitez pas à reprendre la fiche
suivante :
Fiche : Fixer des cellules à l’aide du $ du TP06
Page 7/12
Université de La Rochelle Informatique Transversale
Partie 1
PARTIE 2 : Exercice
I. Données utilisées
Nous utiliserons le fichier [Link]. Ce fichier contient des
informations sur la fréquentation de 1230 musées français de 2003 à 2012.
Le fichier est constitué de quatre onglets. Le premier Légende donne la
signification des codes utilisés. Le second onglet Fréquentation contient les
données que nous utiliserons. Les deux derniers onglets Bilan et Bilan_2
seront utilisés pour répondre aux questions ci-dessous.
Ouvrir le fichier [Link].
Aller dans l'onglet Legende pour observer les codes utilisés.
Sélectionner ensuite l'onglet Données et observer le contenu du tableau
II. Travail à réaliser au cours de la séance
Les réponses aux questions ci-dessous, seront réalisées dans l'onglet Bilan.
II.1- Bilan global
On commencera par réaliser un bilan global comme le montre la figure ci-
dessous.
Pour chacune des années, vous réaliserez les calculs suivants :
• Total des fréquentation (ligne 4).
• Nombre de musées fermées (ligne 5).
• Nombre de musées n'ayant pas communiqué d'information (ligne 6).
• Nombre de musées pour lesquels ces données sont sans objet (ligne 7).
• Vous en déduirez le nombre de musées ouverts (ligne 8).
• Et vous calculerez la fréquentation moyenne (ligne 9).
Page 8/12
Université de La Rochelle Informatique Transversale
Partie 1
Remarque : les calculs seront faits pour toutes les années de 2003 à 2012.
II.2- Bilan par région en 2003
Parmi les informations associées à chaque musée, on trouve la région et la ville
dans laquelle il est situé. Nous allons donc faire un bilan pour chaque région.
Nous nous contenterons de faire un bilan pour l'année 2003 conformément à la
figure ci-dessous.
Remarque : pour faciliter votre travail, la liste des régions a déjà été saisie
dans l'onglet Bilan. Éviter de modifier cette liste pour limiter le risque d'erreur.
Vous calculerez ainsi pour chaque région :
• Le nombre de musées (colonne B)
• la fréquentation en 2003 (colonne C)
Vous en déduirez respectivement en C40 et C41, le total des fréquentations et
la fréquentation moyenne pour l'année 2003.
Pour chaque région :
• la colonne D contiendra le texte « Supérieur » si la fréquentation
(colonne C) est supérieure à la fréquentation moyenne (cellule C41),
Page 9/12
Université de La Rochelle Informatique Transversale
Partie 1
sinon le texte sera « inférieur ».
• la colonne E contiendra la proportion que représente la fréquentation des
musées dans la région par rapport à la fréquentation totale en 2003.
A partir de ce tableau, vous réaliserez le graphique ci-dessous représentant le
nombre de musées par régions. Vous modifierez les caractéristiques du
graphiques pour afficher les étiquettes des deux régions possédant le plus de
musées. Vous modifierez également la couleur des barres en rouge et vert
comme dans l'exemple ci-dessous.
II.3- Bilan par région pour toute la période
Les réponses aux questions ci-dessous, seront réalisées dans l'onglet Bilan_2.
On souhaite maintenant faire le bilan chaque région mais pour la totalité de la
période : de 2003 à 2012.
• L'objectif est d'établir UNE SEULE FORMULE en cellule B4 et par
extension de la formule de remplir la totalité du tableau (colonne B à K /
Ligne 4 à 30).
• Vous déterminerez ensuite la progression des fréquentions entre 2003 et
2012 (colonne L)
Page 10/12
Université de La Rochelle Informatique Transversale
Partie 1
On souhaite ensuite apporter un commentaire sur la progression de la
fréquentation entre 2003 et 2012 comme indiqué sur la figure ci-dessous.
Pour chaque région, dans la colonne M, le commentaire sera déterminé ainsi :
• Si la progression est supérieur à 30 %, le commentaire sera « Forte
augmentation de la fréquentation »
• Si la progression est comprise entre 0 et 30 %, le commentaire sera
« Augmentation moyenne de la fréquentation ».
• Sinon le commentaire sera « Baisse de la fréquentation »
Conseil : la fonction SI ne permet d'afficher que 2 valeurs en fonction du
critère. Pour répondre à cette dernière question, il faut imbriquer deux
fonctions SI.
Page 11/12
Université de La Rochelle Informatique Transversale
Partie 1
Exemple : = SI(Critère 1 ; Valeur_1 ; SI(Critère 2 ; Valeur_2 ; Valeur_3) )
II.4- Bilan par ville
• Créer un nouvel onglet Bilan_3
• Vous ferez un tableau similaire à celui établi au paragraphe II.2.
Cependant ce bilan sera fait pour l'année 2012 et pour les villes
suivantes : Aix-en-Provence, Bordeaux, La Rochelle, Lyon, Marseille, Nice,
Paris et Toulouse comme le montre le tableau ci-dessous.
III. Dépôt sur Moodle
Afin de ne pas perdre vos bonnes habitudes, vous allez compresser et déposer
sur moodle le travail réalisé aujourd'hui.
• Enregistrer votre travail et fermer Libre Office. Aucune fenêtre Calc ne
doit rester ouverte.
• Compresser le dossier TP07(Celui est censé contenir le travail réalisé).
• Renommez l'archive obtenue afin qu'elle se nomme nom_pré[Link]
(Attention avec VOTRE nom et VOTRE prénom).
• Déposer l'archive sur moodle en accédant au lien Dépôt_TP07dans la
rubrique TP07
Page 12/12