Excel: Alternative avec le tableur

Tu dois devenir capable de

Savoir Citer et commenter, dans le cadre du calcul conditionnel, les trois sortes d’informations que peut traiter un ordinateur ; expliquer le principe de construction d’un arbre binaire de décision. Savoir faire écrire un arbre binaire de décision pour une alternative complexe ; Formaliser des situations d’alternative dans la forme si alors sinon et dans un graphe de Nassi-Schneiderman ; écrire une fonction alternative complexe dans le cadre d’un tableur.

Principe du calcul conditionnel Introduction au clavier Démarre l’application Excel et demande une nouvelle feuille de calcul. Suis les indications données dans le tableau ci-dessous. Cellule Frappe au clavier Résultat Explication A1 Il fait beau Le texte « Il fait beau » s’écrit dans la cellule A1 L’affirmation est peut-être correcte, peut-être fausse; l’ordinateur n’a aucun moyen de la savoir A2 =(5+5) La valeur du résultat du calcul, c’est-à-dire 10, apparaît dans la cellule L’ordinateur a évalué l’expression mathématique simple et retourne son résultat A3 =(4>3) La valeur du résultat de l’évaluation, c’est-à-dire « VRAI » apparaît dans la cellule L’ordinateur a évalué l’expression logique simple et retourne son résultat; cette expression est vraie : 4 est plus grand que 3 A4 =(255*4)<1000 La valeur du résultat de l’évaluation, c’est-à-dire « FAUX » apparaît dans la cellule L’ordinateur a évalué l’expression logique simple et retourne son résultat: cette expression est fausse: 255*4 = 1020 qui n’est pas plus petit que 1000

Quand tu as terminé l'expérimentation et compris les résultats obtenus, passe à l'étape suivante.

Principe du calcul conditionnel Conclusion L’ordinateur, assisté d’un programme d’application (ici, Excel), est capable de reconnaître certaines expressions qui peuvent être vraies ou fausses. De telles expressions sont appelées des expressions logiques. Une expression logique est une affirmation que l'on peut évaluer et qui admet deux valeurs possibles : la valeur VRAI ou la valeur FAUX. Coche les cases correctes est une expression logique n'est pas une expression logique Je suis grand 2+2 5 > 7 Je n'ai pas faim > 3 Remarque: l’expression « Je suis le plus beau » est une expression logique ; cependant, l’ordinateur n’a aucun moyen de pouvoir l’évaluer. Des expressions qui font appel à des comparaisons mathématiques sont, par contre, tout à fait à sa portée. Une expression logique est Une expression mathématique calculable par l'ordinateur. Une expression qui est soit vraie, soit fausse. Une expression mathématique qui est soit vraie, soit fausse. Une instruction donnée à l'ordinateur.

Quand tu es capable de définir la notion d'expression logique et de donner de nouveaux exemples, passe à l'étape suivante.

Expressions logiques non mathématiques Cellule Frappe au clavier Résultat Explication C1 ="Jacques"="André" FAUX le texte « Jacques » n’est pas équivalent, caractère par caractère, au texte « André » C2 ="Jacques">"André" VRAI le texte « Jacques » doit être classé plus loin que le texte « André » si l’on se réfère uniquement à l’ordre alphabétique C3 ="anatole"="Anatole" VRAI le logiciel EXCEL ne fait pas de distinction entre les majuscules et les minuscules dans cette comparaison C4 ="Mac Donald">"Quick" ? Justifie sans utiliser d’argument gastronomique! Un ordinateur est donc généralement capable de comparer des chaînes de caractères. Il n’a cependant jamais accès au sens des informations : il ne traite que la forme. Il se base généralement sur l’ordre alphabétique. - Ordre alphabétique, ordre alphabétique... Moi, je veux bien, mais ne m'a-t-on pas expliqué que l'ordinateur ne connaît que les nombres ? Alors, je ne vois vraiment pas comment il pourrait comparer des objets aussi inconnus que des lettres dans sa petite tête ! Judicieuse remarque : effectivement, l'ordinateur n'accède qu'à des nombres. La solution qui a été trouvée est tout simplement de remplacer chaque lettre par un nombre, comme dans certains codes secrets, par exemple. La convention ASCII (American Standard Code for Information Interchange), par exemple est illustrée ci-dessous. 32 51 3 70 F 89 Y 108 l 33 ! 52 4 71 G 90 Z 109 m 34 " 53 5 72 H 91 [ 110 n 35 # 54 6 73 I 92 \ 111 o 36 $ 55 7 74 J 93 ] 112 p 37 % 56 8 75 K 94 ^ 113 q 38 & 57 9 76 L 95 _ 114 r 39 ' 58 : 77 M 96 ` 115 s 40 ( 59 ; 78 N 97 a 116 t 41 ) 60 < 79 O 98 b 117 u 42 * 61 = 80 P 99 c 118 v 43 + 62 > 81 Q 100 d 119 w 44 , 63 ? 82 R 101 e 120 x 45 - 64 @ 83 S 102 f 121 y 46 . 65 A 84 T 103 g 122 z 47 / 66 B 85 U 104 h 123 { 48 0 67 C 86 V 105 i 124 | 49 1 68 D 87 W 106 j 125 } 50 2 69 E 88 X 107 k 126 ~ Les caractères de code inférieur à 32 (code du caractère « espace ») ont des rôles particuliers (retour à la marge, passage à la ligne, ...). Les codes supérieurs à 127 ne sont pas attribués dans le code ASCII original. En fonction de la langue utilisée, les caractères spéciaux peuvent recevoir un code entre 128 et 255. On parle alors de code ASCII étendu. Les caractères majuscules ont un code plus petit que le code des caractères minuscules. Certains logiciels se basent uniquement sur cette convention pour comparer des chaînes dans l’ordre alphabétique. Dès lors, la comparaison "anatole"="Anatole" pourrait avoir la valeur « FAUX » dans certains logiciels ; la réponse dépend du mode de comparaison entre les caractères: codage ASCII strict ou non. Quelle expression est vraie? T > t t > T t = T Quelle est l'expression vraie? "Petit" > "Grand" "Petit" = "Grand" "Petit" < "Grand"

Quand tu as compris la logique des expressions logiques portant sur des lettres, des mots ou des phrases, passe à l'étape suivante.

L’alternative Introduction au clavier Nous utiliserons la feuille de calcul entamée plus haut. Cellule Frappe au clavier Résultat Explication E1 =SI(A1="Il fait beau";"Bronzage";"Parapluie") Le texte « Bronzage » apparaît dans la cellule E1 l’expression logique a été évaluée (elle est vraie) et le mot « Bronzage » apparaît. A1 Il pleut le texte « Parapluie » apparaît dans la cellule E1 l’expression logique a été évaluée (elle est fausse) et le premier mot (Parapluie) apparaît. E2 =SI(A2>=10;0;1) la valeur 0 apparaît dans la cellule E2 l’expression logique a été évaluée (elle est vraie) et la première valeur apparaît. A2 5 la valeur 1 apparaît dans la cellule E2 l’expression logique a été évaluée (elle est fausse) et la deuxième valeur apparaît. Conclusion La conjonction « SI » marque le début d’une alternative. Une alternative est une situation dans laquelle deux (et seulement deux) possibilités se présentent. Le mot « SI » est suivi d’une expression logique, qui est donc vraie ou fausse. L’ordinateur est capable de reconnaître la situation d’alternative et d’évaluer l’expression logique. Si celle-ci est vraie, la fonction alternative retourne la valeur qui suit l’expression logique. Si l’expression logique est fausse, c’est la deuxième valeur qui est retournée.

Quand tu es capable de définir la notion d'alternative et d'exposer des exemples concrets, passe à l'étape suivante.

Ecritures de l’alternative Il est souvent commode d’écrire l’alternative sous l’une ou l’autre forme visuellement parlante. Chacune des trois méthodes suivantes peut être utilisée. Première forme: la phrase alternative Si Alors <action 1> Sinon <action 2> Deuxième forme: graphe de Nassi-Schneiderman (GNS) Expression logique Action si vrai Action si faux Troisième forme: l'arborescence L'appellation " arborescence " prendra tout son sens dans les cas d'alternatives complexes. Exemples: Première forme: Si < il fait beau > Alors < bronzage > Sinon < prendre un parapluie > Deuxième forme: Il fait beau bronzage prendre un parapluie Troisième forme:

Quand tu as compris le sens des trois représentations de l'alternative, passe à l'étape suivante.

L'alternative simple avec Excel Dans un chapitre précédent du cours, tu as mis au point un "jeu de dés". Il serait intéressant que l'ordinateur lui-même annonce le vainqueur. La situation inititale se présente donc comme sur l'illustration ci-contre. Dans ce cas, c'est le joueur bleu qui a gagné. Peux-tu exprimer la situation présentée ci-dessus dans chacune des trois formulations proposées dans ce cours? Quand tu as rédigé les trois formulations sur une feuille de papier, vérifie ton travail.

Quand tes réponses sont bonne, passe à l'étape suivante.

L'alternative simple avec Excel: approfondissement Parmi les façons d'exprimer la situation d'alternative du jeu de dés, on pourrait écrire la représentation en arbre illustrée à droite ou la phrase alternative suivante: Si (B4 > C4) Alors écrire "Le joueur Bleu a gagné" Sinon écrire "Le joueur Jaune a gagné" Comment formuler ces représentations dans le formalisme imposé par le tableur: =SI(Test_logique;Valeur_si_vrai; Valeur_si_faux) On voit que la situation est relativement simple: il suffit d'écrire: =SI(B4 > C4;"Le joueur Bleu a gagné"; "Le joueur Jaune a gagné") Rappel : il faut mettre des guillemets autour des deux phrases car ce ne sont pas des valeurs numériques. Reporte dans la cellule C5 de la feuille de calcul du jeu de dés, l'expression de l'alternative construite ci-dessus. Vérifie que le jeu fonctionne correctement dans tous les cas. Dans quelles circonstances y a-t-il un problème ? - Ben zut alors, tout ça semblait tellement évident et vlan, un problème se pointe ! - Tu viens de faire l'expérience douloureuse de la difficulté de travailler avec un ordinateur. Comme il est, lui-même, très bête, il faut être deux fois plus intelligent à sa place. La réflexion approfondie sera toujours une part importante du travail de l'informaticien.

Quand ton jeu fonctionne et que tu as situé le problème possible, passe à l'étape suivante. Nous résoudrons ce problème plus loin.

Alternative complexe appliquée au jeu de dés La difficulté rencontrée dans le jeu de dés est bien celle du cas d'égalité. Dans la résolution proposée, le joueur Bleu est proclamé gagnant lorsque son score est meilleur ET s'il y a égalité. Ce n'est pas normal. Mais le problème est simple à traiter en utilisant une arborescence à deux niveaux, comme dans l'exemple de l'équation du premier degré traité à l'étape précédente. Remarque qu'il existe plusieurs façons de traiter le problème. On aurait pu utiliser d'autres tests. Peux-tu reformuler l'alternative présentée sous forme d'arborescence dessinée ci-dessus, en utilisant une phrase alternative complexe? Rédige cette phrase alternative. Quand ta phrase alternative est correcte, transpose-la dans la formulation du tableur. Le résultat est semblable à l'aleternative complexe du traitement de l'équation du premier degré abordé à l'étape précédente.

Quand ta feuille de calcul est prête, joue quelques parties afin de vérifier que tout fonctionne correctement. Ensuite, passe à l'étape suivante.

Alternative complexe : (où l'on apprend à l'ordinateur à faire ses devoirs de maths) Énoncé du problème et analyse On demande de programmer le tableur de manière à lui faire résoudre les équations du premier degré du type ax + b = 0 d'inconnue x. Ce type de problème est élémentaire au cours de mathématiques : il se résout de la manière suivante. Il y a ici une situation d'alternative double: Il y a un choix entre deux possibilités; pour choisir, il faut examiner le coefficient de x (il est nul ou il ne l'est pas). Si a0, il y a encore un choix entre deux possibilités; pour choisir, il faut examiner le 2ème membre de cette équation (il est nul ou il ne l'est pas). Résultat attendu et méthode On attend d’obtenir une feuille de calcul semblable aux exemples ci-dessous. Pour la facilité, il serait souhaitable de nommer les deux cellules grisées a et b. Alternative dans le tableur Avant d’écrire le formalisme propre au tableur, nous présenterons l’alternative selon la forme si alors sinon Si a<>0 Alors écrire (-b/a) Sinon Si b <> 0 Alors écrire (« Impossible ») Sinon écrire (« Indéterminé ») La fonction alternative s’écrira donc: =SI(a<>0;-b/a;SI(b<>0;"Impossible";"Indéterminé")) ou, si l'on utilise les coordonnées plutôt que les noms =SI(A2<>0;-D2/A2;SI(D2<>0;"Impossible";"Indéterminé")) Utilise toutes les informations données plus haut pour résoudre le problème de la résolution des équations du premier degré avec le tableur.

Quand ta feuille de calcul est prête, teste tous les cas possibles. Ensuite, passe à l'étape suivante.

Comment construire une structure alternative correcte sans (trop) se fatiguer Dans certains cas, il n'est pas possible d'exprimer aisément la situation d'alternative parce que les conditions ne sont pas simples. Si l'on se trouve dans une telle situation, alors il faut utiliser une méthode pour construire l'alternative. Nous utiliserons la méthode par construction d'un arbre binaire de décision. Problème Écrire un programme qui détermine si une année dont on donne le millésime est bissextile. Une année est bissextile si son millésime est multiple de 4; cependant, les années dont le millésime est multiple de 100 ne sont bissextiles que si c'est aussi un multiple de 400 (1900 n'était pas bissextile, 2000 l'a été). Il faut bien mettre en évidence toutes les conditions particulières à envisager lors de la résolution du problème. Il faut évaluer si: le millésime est un multiple de 4; le millésime est un multiple de 100; le millésime est un multiple de 400. Construction d'un arbre binaire de décision Examine attentivement l'arborescence ci-dessous durant la lecture de l'explication de sa construction. Choisissons une de ces conditions particulières et voyons ce qu'il convient de faire si elle est vraie et si elle est fausse. Nous commencerons par la condition « multiple de 4 ». Si la condition est fausse, l'année n'est pas bissextile et c'est fini, sinon, il faut encore travailler. Quelle question faut-il se poser si le millésime est multiple de 4? Il faut voir si c'est un multiple de 100. Si la condition est fausse, alors l'année est bissextile et c'est fini, sinon, il faut encore travailler. Si la condition est vraie, alors l'année est bissextile, sinon, elle n'est pasbissextile. Dans tous les cas, le travail est terminé. La structure de décision se présente sous la forme d’un arbre inversé, c’est-à-dire dont la racine serait en haut et les branches en bas. Dans le formalisme Si...Alors...Sinon..., ce tableau pourrait s’exprimer: Si millésime multiple de 4 Alors Si Millésime multiple de 100 Alors Si Millésime multiple de 400 Alors " Bissextile " Sinon " Non Bissextile " Sinon " Non bissextile " Sinon " Non bissextile " Vérifie, à l'aide de l'abre binaire de décision ou de la structure si...alors...sinon, que les années 1930, 2003 et 2100 ne sont pas bissextiles. Vérifie ensuite que 1980, 1800 et 2400 sont des années bissextiles. Transpose le formalisme de l'arbre ou de la phrase alternative dans celui qui est propre au tableur. Le but est de pouvoir proposer un tableau semblable à celui de l'illustration ci-contre pour toutes les années à partir de 1582. - Bah! Pourquoi 1582 ? - Et bien, cherche. ;o)

Quand tu as établi la feuille de calcul demandée, passe à l'étape suivante.

Les années bissextiles, en pratique La pratique pose deux problèmes: il faut être capable de déterminer si un nombre est multiple d’un autre ; il faut être capable d’écrire une alternative complexe. Pour savoir si un nombre est divisible par un autre, il suffit de faire la division et de déterminer la valeur du reste de cette division. Les programmes tableurs mettent généralement une fonction MOD à la disposition des utilisateurs. Dans la cellule frappe Résultat A1 =MOD(27;5) 2, le reste de la division de 27 par 5 A2 12 A3 =MOD(A2;A1) 0, la division de A2 par A1 a un résultat entier! Si la cellule qui contient le millésime de l’année est nommée « AN », l’alternative s’écrira alors: Si MOD(AN;4)=0 Alors Si MOD(AN;100)=0 Alors Si MOD(AN;400)=0 Alors " Bissextile " Sinon " Non Bissextile " Sinon " Non bissextile " Sinon " Non bissextile " Ce qui s’écrira: =SI(MOD(AN;4)=0;SI(MOD(AN;100)=0;SI(MOD(AN;400)=0;"Bissextile";"Non bissextile");"Bissextile");"Non bissextile") dans le formalisme propre au tableur. Il paraît bien difficile d’écrire cette formule sans réaliser l’analyse complète du problème.

Quand tu as compris la formule pour déterminer les dates bissextiles dans le tableur, passe à l'étape suivante.

Exercices

Pour chacun des exercices qui suivent, il est demandé de réaliser une analyse complète et de tracer, préalablement, l’arbre binaire de décision. 1. On demande d'établir une feuille de calcul à l'usage d'un employé de banque. Étant donné le solde du compte en banque d'un client et le montant qu'il désire retirer de son compte, le programme doit indiquer le message « retrait accepté », le montant du retrait et le nouveau solde du compte si le compte est suffisamment approvisionné. Si le solde du compte avant retrait est inférieur à –500 €, le retrait est refusé; le nouveau solde du compte après retrait ne peut être inférieur à –500 €, de sorte que le retrait peut, dans certains cas, être limité à un montant tel que cette exigence soit respectée. 2. Établir une feuille de calcul qui indique le plus grand de trois nombres fournis par l'utilisateur. 3. Une entreprise désire établir une feuille de calcul pour déterminer le nombre de jours de congé auxquels ont droit ses employés sachant qu'ils bénéficient de 2 jours de congé par mois de présence durant l'année écoulée et de 10 jours de congé supplémentaires s'ils ont plus de 10 ans de service dans la société. 4. Le règlement d'un club londonien très fermé précise que tout membre doit répondre aux conditions suivantes : il ne peut pas mesurer plus de 1,85 m ni moins de 1,50 m. Cependant, les personnes de plus petite taille (moins de 1,50 m) sont admises à condition de peser plus de 60 kg et d'être âgées de plus de 18 ans. Les personnes de très grande taille (plus de 1,85 m) sont admises si elles pèsent moins de 100 kg, sauf si elles ont plus de 45 ans, cas où elles ne sont jamais admises. Écrire l'arbre de décision binaire puis l’alternative qui permet de déterminer si une personne est admissible. 5. Établir une feuille de calcul qui détermine si une date donnée entre le 1 janvier 1901 et le 31 décembre 1999 a existé. Pour rappel, dans cet intervalle, toutes les années dont le millésime est multiple de 4 sont bissextiles. Ex: le 12/06/1978 est une date licite le 30/02/1989 est une date illicite le 34/15/1965 est une date illicite Astuce: il est possible de déterminer séparément si l’année est bissextile. On peut utiliser le résultat du test dans une autre alternative. 6. Établir une feuille de calcul destinée à rédiger des factures chez un grossiste en disques. Celui-ci vend des disques « single » au prix unique de 5€ et des CD au prix de 9€. Le montant de la TVA à appliquer sur les factures est de 21%. Le client se voit facturer les frais de port (forfait de 5€) au cas où le montant (hors TVA) de sa commande est inférieur à 125 €. Cependant, s'il s'agit d'un bon client (montant des achats supérieurs à 2.500 € au cours de l'année écoulée), les frais de port sont réduits à 1,25 €. La TVA ne s'applique pas sur les frais de port! 7. Les membres de la secte des « mathos-dingos » considèrent qu'un nombre de 4 chiffres est magique si le premier chiffre est pair et que la somme des deux suivants est impaire. Toutefois, si le dernier chiffre est pair, le nombre n'est réellement magique que si le produit des deux médians est un multiple de 3. Etablis une feuille de calcul qui détermine si une suite de 4 chiffres constitue un nombre magique. Ex: 2321 est magique; 4452 ne l'est pas. La fonction ENT (qui signifie « partie entière ») permet de connaître la partie entière d’un nombre fractionnaire. Le fragment de tableur ci-contre permet d’isoler les quatre chiffres de la cellule en haut et à gauche dans la deuxième colonne. 8. Établir une feuille de calcul pour résoudre les équations du second degré à une inconnue de la forme ax²+bx+c=0.