ChronoMath, une chronologie des MATHÉMATIQUES
à l'usage des professeurs de mathématiques, des étudiants et des élèves des lycées & collèges

La feuille de calcul : le tableur

Une feuille de calcul électronique, dite aussi "tableur", ("calc" en anglais) est constitué de cellules repérées par leur ligne et leur colonne pouvant contenir des informations de type numérique ou texte et être reliées entre elle par de puissantes instructions logiques, fonctionnelles ou mathématiques. Le tableur est aussi capable de traiter les chaînes de caractères (concaténation, extraction de sous-chaînes)

C'est un incomparable outil de calcul (comptabilité, calculs scientifiques itératifs) et de simulation (gestion prévisionnelle, réglages de paramètres d'une machine-outil, etc.). Les pionniers en la matière furent VISICALC, puis MULTIPLAN (Microsoft).

La programmation sur tableur est très formatrice : elle apprend à manipuler des structures de données diverses (variables, tableaux, listes) et à organiser sa pensée avec un contrôle visuel des actions placées dans les cellules. Cependant, elle demande une connaissance élémentaire de la programmation : variable, boucle, itération, instructions conditionnelles et sa mise en œuvre paraît plus complexe dans sa syntaxe, moins proche du langage mathématique usuel.

Familiarisé au langage Basic ou à tout autre langage de programmation structuré, comme le PASCAL (tout autant démodé que Basic mais toujours en vogue dans certaines prépa.), ou plus récemment, JavaScript , vous trouverez ce dernier particulièrement plus efficace dans les problèmes itératifs.

Le tableur pourra être utilisé dans tous les cas où les résultats intermédiaires présentent un intérêt : étude d'une suite, d'une série (visualisation d'une conjecture de convergence), d'une limite, d'une approximation par itération (avec visualisation de l'erreur maximale à chaque étape), etc.

En outre, le tableur permet une initiation simple aux algorithmes récursifs (possibilité pour une procédure de faire appel à elle-même). Notons ici que JavaScript accepte également la récursion.

Les notions présentées ci-dessous ne prétendent pas être un exposé sur l'usage d'un tableur: elles ne sont là que pour éclairer l'utilisateur non averti sur la puissance de programmation d'un tel outil.

La présentation et la syntaxe sont celles d'Excel de Microsoft. Il n'y a pas de différences fondamentales d'un tableur à l'autre, comme celui d'OpenOffice par exemple.

Un tableur vous permet d'entrer dans des cases, appelées cellules  et constituant votre feuille de calcul, essentiellement des nombres, mais aussi du texte pour dénommer ou apporter des commentaires, et d'établir des relations  entre ces nombres au moyen de formules arithmétiques, statistiques ou purement mathématiques faisant référence à d'autres cellules ou groupe de cellules : plage (aussi appelé champ). La modification du contenu d'une cellule se répercute sur l'ensemble des cellules ou plages liées.

On utilise les touches de direction ou la souris pour se placer dans la cellule désirée; on tape le nombre et on valide (touche entrée). Les tableurs comprennent qu'une donnée est numérique si elle commence par un chiffre, une virgule (un point dans les versions anglaises), les signe + ou -, une parenthèse ouvrante ou bien le signe = : Entrer =2 revient à entrer 2. Toute autre entrée est considérée comme texte : c'est le cas de L1C1 (ou A1) qui contient le mot taux et de L12C3 (ou C12) qui contient le mot x(i) servant à indiquer à l'utilisateur du programme la signification du calcul effectué en D12.

Lorsque vous êtes placé dans une cellule, vous établirez généralement une formule faisant appel à un résultat (ou une donnée) placé(e) dans une autre cellule. Un tableur vous permet de repérer les cellules de différentes façons : Les lignes sont toujours numérotées, les lignes et les colonnes peuvent être numérotées (mode L1C1) ou seulement les lignes (mode A1). Dans ce dernier cas, la 1ère colonne est A, la seconde est B,..., la 26ème est Z, la 27ème est AA, ...

on fait référence à une cellule, indépendamment de la place de la cellule qui y fait appel. C'est le cas de la cellule L2C4 qui prend la valeur de L1C2 dans le tableau ci-dessus. En mode A1, on fait précéder la colonne et/ou la ligne d'un dollar :

 ➔   En mathématiques, on nomme les variables ou les fonctions utilisées. On peut faire de même avec tout tableur : Avec EXCEL, placez-vous dans la cellule voulue et choisissez l'option Définir un nom... dans le menu Sélection ou, suivant votre version : Insertion ... Nom... Définir. Dans l'exemple précédent :

=1+i*h : la cellule L12C1 (ou $A$12) a été nommée i et L12C2 ($B$12) a été nommée h, ce qui permet une écriture plus compréhensible; on utilisera des noms le plus souvent possible.

=MOYENNE(L2C3 : L4C7) ou =MOYENNE(C2 : G4) : on fait la moyenne de la plage de cellules situées en ligne 2 de la colonne 3 à la colonne 7. Les deux points (:) délimitent la cellule de départ et la cellule d'arrivée. Si les lignes de départ et d'arrivée sont distinctes, le tableau de cellules est défini par son coin supérieur gauche et son coin inférieur droit.

On écrit la référence en précisant, entre parenthèses le décalage en lignes et/ou colonnes par rapport à la cellule active.

En mode A1, ces références relatives (absence de $) sont moins visibles mais l'écriture des formules est plus concise et plus claire :

Le but de ces notations est la possibilité de recopier vers le bas ou vers la droite de la feuille de calcul, une même formule agissant sur les cellules correspondantes d'une autre colonne ou d'une ligne.

 ➔  Dans l'exemple ci-dessus, on a seulement écrit en B1, la formule =A1+1 et on a demandé la recopie vers le bas de B1 à B4. L'ordinateur a fourni les formules conformément à l'adressage relatif : si B1 se réfère à A1, alors B2 se réfère à A2, etc. En mode L1C1, la cellule B1 contiendrait =LC(-1)+1.

• + , - , * , / , ^ * est la multiplication et ^ l'exponentiation
• ABS(X) : valeur absolue de X
• ARRONDI(X;N) : arrondit X à N décimales (au plus proche)
• ENT(X) : partie entière mathématique de X
• SIGNE(X) fournit -1, 0 ou 1 suivant que x est strictement négatif, nul ou strictement positif
• RACINE(X) : racine carrée de X.
• MOD(N;D) : reste de la division de N par D
• COS(X) , SIN(X), ACOS(X), etc. : cosinus de X , sinus de X, Arc cosinus de X
• PI() : nombre π = 3,1415926535
• ALEA() : nombre de l'intervalle ]0,1[
• ALEA.ENTRE.BORNES(a,b) : nombre entier compris entre a et b
• EXP(X) , LN(X) : exponentielle de X , logarithme népérien de X
• LOG(X) ou LOG10() : logarithme en base 10 de x

• SOMME(plage1;plage2;....) Somme des valeurs dans les plages indiquées
• MOYENNE(plage1;plage2;....) Moyenne des valeurs dans les plages indiquées
• MEDIANE(plage1;plage2;....) Médiane des valeurs dans les plages indiquées
• ECARTYPE(plage1;plage2;....) Ecart type des valeurs dans les plages indiquées

• =SI(C;E1;E2) Si C est VRAI alors faire E1 sinon faire E2.
• =OU(E1;E2;...) VRAI si une au moins des expressions E1,E2,... est vraie
• =ET(E1;E2;...) VRAI si toutes les expressions E1, E2,... sont vraies
• =NON (var.logique) VRAI si var. logique est vraie, FAUX sinon
• Les OU, ET et NON sont généralement utilisés dans un SI :

   =SI(OU(x<-1;x>2);LN(ABS(x) + 1);SI(x>=0;RACINE(2 - x);ABS(x))) dans une cellule nommée Y contient revient à définir la fonction mathématique ci-dessous, définie par intervalles :

En langage JavaScript, nous aurions écrit :

        if (x < -1 || x > 2) {y = log(abs(x) + 1)} else {if (x >=0) {y = sqrt(2 - x)} else {y = abs(x)}}

  =SI(N<=2;"OK";N-1) fait afficher la chaîne OK si N est effectivement inférieur ou égal à 2 et la valeur numérique de N - 1 sinon : on voit là la puissance du tableur : une même cellule peut contenir, sans déclaration préalable, des chaînes de caractères et des nombres.

Dans une expression comme ci-dessus, le mot OK doit être, comme en JavaScript et la plupart des langages, entouré de guillemets car EXCEL ne doit pas confondre les variables numériques et les chaînes de caractères (textes).

  Exercices OU, ET, SI :  »

Concaténation de deux chaînes : ="EX"&"CEL" retourne le mot EXCEL dans la cellule

• CTXT(Expr) Convertit en texte l'expression Expr
• MAJUSCULE(texte) Remplace texte par son écriture en majuscules
• NBCAR(cellule) Retourne le nombre de caractères présents dans la cellule
• GAUCHE(texte;n) Retourne les n premiers caractères de texte
• DROITE(texte;n) Retourne les n derniers caractères de texte
• STXT(t1;n1;n2) Extrait la sous-chaîne de n2 caractères dans la chaîne texte à partir du rang n1

  =GAUCHE("tableur",5) retourne le mot table dans la cellule active.

Comme il a été évoqué plus haut, une particularité fondamentale du tableur est sa faculté de reproduire dans une plage une formule éditée dans une cellule. Cette reproduction peut se faire vers le bas ou vers la droite. Les tableaux ci-après expriment cette technique :

  On se propose de faire apparaître x_i = a + ih pour i variant de 0 à 10 avec :
  a=1, b=2, n=10 et h= (b - a)/n.

• Tout d'abord les cellules L3C4, L4C4, L5C4, L7C4 sont nommées respectivement a, b, n et h en utilisant le menu Sélection... Définir un nom...ou, suivant votre version : Insertion ... Nom... Définir.

• La colonne 3 contient des informations de type texte pour une meilleure compréhension du tableau
• En L7C4, la formule =(b-a)/n engendre le calcul de h, soit 0,1.

• En L3C1, on a introduit 0 : c'est la valeur initiale de i.
• En L3C2, on écrit la formule =a+LC(-1)*h : c'est a+ih.
• En L4C1, on écrit que i est incrémenté de 1 par rapport à la ligne supérieure.
• On sélectionne alors la zone L4C1 : L13C1 et on demande une recopie vers le bas.
• On sélectionne enfin la zone L3C2 : L13C2 et on demande la recopie vers le bas.
• On obtient alors le tableau ci-dessous où les valeurs ont été demandées centrées pour une meilleure présentation :

 

Problèmes d'arrondi :

♦  Vous voulez arrondir le nombre X au millième près le plus proche
en le nombre encore noté X :

♦   Maintenant le nombre X considéré est une moyenne de vos notes ou de l'un de vos élèves et vous avez décidé d'arrondir cette moyenne au 1/10e près le plus proche. : 12,233333 donne 12,2 ; 12,6666666 donne 12,7 ; 12,1 donnera 12,1 :

L'affectation i = i + 1 se lit "donner à i sa valeur actuelle augmentée de 1" : dans la terminologie du tableur, on parle de référence circulaire : il faudrait écrire, dans la cellule que nous nommerions i, l'expression: = i+1.

Ainsi une instruction écrite dans une cellule C fait référence à C elle-même : on parle, en programmation, d'appel récursif. En quelque sorte, "on tourne en rond..." et un message du type Impossible de résoudre les références circulaires apparaîtra.

Il en serait de même pour des calculs dans des cellules se faisant mutuellement appel.

Les auteurs d'EXCEL ont cependant prévu ce type de calcul, apparaissant fréquemment dans les calculs comptables et prévisionnels, avec un test automatique de convergence.

• Choisissez Mode de calcul ... dans le menu Options ou, suivant votre version, Préférences... Calculs dans le menu Outils;
• Cochez Itération et indiquez dans le dialogue proposé le nombre d'itérations (100 par défaut) voulu et l'écart maximum entre deux itérations (0,001 par défaut) : vous autorisez ainsi les références circulaires.

Si aucune condition d'arrêt ne figure dans les formules que vous placerez dans les cellules concernées, EXCEL arrêtera les calculs soit au bout de 100 itérations soit si le pourcentage d'écart entre deux valeurs consécutives, dans les cellules soumises à itération, est inférieure à 10^-3.

Le langage VisualBasic est intégré à Excel depuis sa version 6. Très proche de l'ancien Basic Microsoft, il permet la construction de petites applications ou de fonctions nouvelles, mathématiques en particulier. Prenons l'exemple de la fonction définie par :

        

Sa traduction sur tableur sera :

Mais si nous avons besoin d'utiliser l'expression de f dans plusieurs cellules, la crise de nerfs est assurée et on préférera sans doute utiliser un autre langage : VisualBasic est là :

• Barre de menu, choisissez : Outils ... Macros ... VisualBasic
• Barre de menu, choisissez : Insertion ... Module...

Dans la fenêtre qui s'ouvre, tapez :

• function f(x) et validez
• Excel répond en ajoutant automatiquement End Function.
• Entre function f(x) et End function, tapez l'expression de Y :

  If x < -1 Or x > 2 Then
                             Y= Log(Abs(x) + 1)
                              Else
                              If x >= 0 And x <= 2 Then Y = Sqr(2 - x) Else Y = Abs(x)
                              End If

• Barre de menu, choisissez : Débogage ... Compiler
  et si tout est bon :
• Barre de menu, choisissez : Fermer et retourner à Microsoft Excel

Testez votre fonction dans une cellule, par exemple tapez dans A1 : =f(9). Vous devriez obtenir 2,302585093, c'est à dire ln(10). Dans ce langage, les mots clés sont en anglais :

• racine carrée = sqr
• logarithme népérien = Log

Pour modifier :

• Outils ... Macros ... VisualBasic
• Barre de menu, choisissez : Affichage... Explorateur de projets
  Ouvrez le dossier modules.

 ➔  Pour en savoir plus :

• La littérature est très fournie sur le sujet (Microsoft Excel) dans les rayons informatiques des librairies;
• Programmes sur tableur dans ChronoMath.