Les tests logiques avec Excel - lyceeduruy.fr

1 Les tests logiques Page 1 Les tests logiques avec Excel 1. La fonction SI (test simple):a) Nous aimerions qu' Excel nous avertisse d s qu'une certaine valeur est atteinte, par exemple en affichant un message d'avertissement. Nous demandons Excel d'effectuer un test logique : nous lui demandons de tester si une condition d termin e est remplie ou non lorsqu'on effectue une mise jour de la saisie d'une valeur; la r ponse pourra tre soit vrai soit faux. Pour effectuer ce test nous allons utiliser la fonction SI. La syntaxe de la fonction SIest compos e de trois arguments: =SI(test_logique;valeur_si_vrai;valeur_s i_faux) L'argument test_logique: est la condition qui doit tre remplie, c est- -dire toute valeur dont le r sultat peut tre vrai ou faux.

2 (par exemple un seuil d pass ; valeur>100) L'argument valeur_si_vrai est la valeur ou l'action qui doit tre affich e ou ex cut e si la condition du test logique est remplie. Si la valeur est du texte, on la place entre guillemets L'argument valeur_si_fauxest la valeur ou l'action qui doit tre affich e ou ex cut e si la condition du test logique n'est pas remplie. b) Exemple :c) Ouvrir une nouvelle feuille et construire le tableau ci-dessous:ABCDE1 Produit Quantit re ue Quantit vendue Quantit en stock Message d'avertissement2 Cahiers 1200 500 700 3 Crayons 500 200 300 4 Bloc Notes 400 300 100 Les tests logiques Page 2 Vous g rez votre stock de marchandises et vous aimeriez qu' Excel vous avertisse d s qu'un seuil d termin est franchi, par exemple d s que vous avez moins de 200 unit s de chaque produit.

3 Si la quantit en stock se situe en dessous de 200 unit s il faudra qu' Excel affiche le message d'avertissement suivant: "Stock trop faible". Dans la colonne Message d'avertissement, saisir la formule utilisant la condition SI et recopier vers le bas : Quantit _en_stock<200 =test logique (la condition qui doit tre remplie) "Stock trop faible" = Message d'avertissement qui s'affiche si la condition est remplie (VRAI) " " = on n affiche rien si la condition n'est pas remplie (FAUX) d) Construire le tableau ci-dessous (les nombres sont en euros).

4 Indiquer, dans les cellules C4 et D4, les formules permettant le calcul de la prime et du total. Recopier ces formules vers le bas. 2. Les tests imbriqu s:Pour tester plusieurs conditions il est possible d'imbriquer plusieurs tests dans une suite logique. Par exemple, vous devez appliquer deux taux de rabais en fonction de deux montants d'achat diff rents: partir de 300 d'achat vous appliquez un rabais de 5% et partir de 500 un rabais de 8%. La fonction contenant les deux tests logiques imbriqu s aura la syntaxe suivante: =SI(Test_logique1;Valeur_si_vrai1;SI(Tes t_logique2;Valeur_si_vrai2;Valeur_si_fau x)) a) Ouvrir une nouvelle feuille et construire le tableau ci-dessous:ABCDE1 Produit Prix de vente Quantit vendue Prix totalRabais 2 Cahiers 2500 1000 3 Crayons 1400 400 4 Bloc Notes2100 200 Les tests logiques Page 3 Attention: Il faudra pr ter une attention particuli re l'ordre dans lequel vous imbriquez les tests , pour viter qu'une condition ne soit pas d j reprise au pr alable par un autre test.

5 Dans notre cas le fait de tester d'abord les montants >300 pour accorder le rabais de 5%, rendrait un deuxi me test sur des montants d passant 500 inutile car ces valeurs auraient d j t reprises par le premier test (500 tant plus grand que 300 !). b) Reprendre le tableau du ) avec le mode de calcul suivant : 3. Les tests labor s avec les op rateurs ET et OU:a) Les op rateurs logiques ET et OU permettent de cr er des tests plus restrictifs. Il s'agit ici de tests logiques qui permettent de tenir compte de plusieurs conditions. L'op rateur OU permet de tenir compte de conditions qui peuvent intervenir sans s'exclure mutuellement.

6 Voici les quatre possibilit s pour l'op rateur OU et les r sultats respectifs:1 re condition 2 me condition R sultat remplie (VRAI) OU remplie (VRAI) VRAI non remplie (FAUX)OU remplie (VRAI) VRAI remplie (VRAI) OU non remplie (FAUX)VRAI non remplie (FAUX)OU non remplie (FAUX)FAUX =SI(OU(1 re condition;2 me condition);Valeur si vrai;Valeur si faux) On peut avoir plusieurs conditions : =SI(OU(Cond1;Cond2;..;CondN) ; action r aliser si au moins une des conditions est satisfaite ;action r aliser si aucune des conditions n'est satisfaite)Les tests logiques Page 4 L'op rateur ET permet de restreindre le test logique des conditions qui doivent tre remplies simultan ment.

7 Voici les quatre possibilit s pour l'op rateur ET et les r sultats respectifs:1 re condition 2 me condition R sultat remplie (VRAI) ET remplie (VRAI) VRAI non remplie (FAUX)ET remplie (VRAI) FAUX remplie (VRAI) ET non remplie (FAUX)FAUX non remplie (FAUX)ET non remplie (FAUX)FAUX =SI(ET(1 re condition;2 me condition);Valeur si vrai;Valeur si faux) On peut avoir plus de deux conditions : =SI(ET(Cond1;Cond2;..;CondN) ; action r aliser si les N conditions sont satisfaites ;action r aliser si au moins une des conditions n'est pas satisfaite)b) Exemples: On d sire attribuer une ristourne de fin d'ann e de 2% aux clients qui remplissent les deux conditions suivantes : tre grossiste ET avoir achet plus de 100 00 de marchandises chez nous.

8 Nos clients sont r pertori s dans le tableau ci-dessous. ABC1 Nom du clientType de clientAchats2 EMELINE Grossiste 6700 3 ACQUIN D taillant 13800 4 HENDOL Grossiste 21300 5 JUNEZ D taillant 5950 Reproduire le tableau ci-dessus. Nommer la colonne B : type de client. Nommer la colonne C : achats Cr er une colonne ristourne et saisir dans cette colonne la formule suivante : =SI(ET(type de client="grossiste";achats>100000);achats *2%;0 ) Une entreprise souhaite verser une prime de fin d'ann e ses repr sentants s'ils remplissent l'une OU l'autre des conditions suivantes : Avoir plus de 5 ans d'anciennet . Avoir r alis plus de 100000 euros de chiffre d'affaires.

9 Les repr sentants sont r pertori s dans le tableau suivant : ABC1 Nom du Repr sentantAnn es d'anciennet Chiffre d'affaires2 ARMAND 31 21300 3 FLORA 699700 4 NINIAN 71 01600 Les tests logiques Page 5 Reproduire le tableau ci-dessus. Nommer la colonne B : ann es d anciennet . Nommer la colonne C : chiffre d affaires. Cr er une colonne PRIME et saisir la formule suivante : =SI(OU(ann es d'anciennet >5;chiffre d'affaires>1000000);1000;0 )c) Reprendre le tableau du ), ins rer la colonne Nombre d employ s et calculer les nouvelles primes et les nouveaux totaux. 4. La fonction :a) Somme conditionnelle Cette fonction permet de sommer un ensemble de cellules situ es dans une colonne la condition qu'un autre ensemble de cellules situ es dans une colonne voisine r pondent un crit re.

10 Pour comprendre le r le de cette fonction il faut voir un tableau comme une succession de lignes, une cellule dans une ligne est retenue pour une sommation la condition qu'une autre cellule dans la m me ligne respecte un certain crit re; toutes les cellules retenues sont alors somm es. La syntaxe de est la suivante: = (plage_de_cellules_ _comparer ; crit re ; plage_de_cellules_ _sommer) b) Exemple: onveut la somme des montants r alis s pour la r gion Sud (sans trier ni retoucher le tableau): La plage de cellules comparer repr sente toutes les cellules d'une r gion de ventes et les cellules sommer toutes les cellules de la colonne Montant.