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.

Entrée de donné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.

Faire référence à des cellules :

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, ...

Adressage absolu :

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 :

= L1C2 s'écrirait = $B$1.

 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.

Adressage relatif et semi-relatif :

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.

 Les opérations & fonctions numériques usuelles

 Les fonctions statistiques usuelles :

 Les fonctions logiques usuelles :

   =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 :

 Les fonctions de chaînes de caractères usuelles :

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


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

 La recopie des formules :

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 xi = a + ih pour i variant de 0 à 10 avec :
  a=1, b=2, n=10 et h= (b - a)/n.


 

 Problèmes d'arrondi :

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

=SIGNE(X)*ARRONDI (ABS(X);3)

   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 :

=ARRONDI(X;1)

Voir aussi : arrondis , arrondi à la dizaine près

Itération & récursion - références circulaires :


Un processus
itératif consiste à répéter sur un même groupe d'instructions un certain nombre d'opérations tant qu'une (ou plusieurs) condition(s) portant sur ce groupe n'est pas vérifiée : compter de 1 à 20, c'est partir de i = 1 puis utiliser la formule itérative i = i + 1 tant que i < 20.

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.

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.

Usage de VisualBasic :

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 :

=SI(OU(x<-1;x>2);LN(ABS(x) + 1);SI(x>=0;RACINE(2 - x);ABS(x)))

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à :

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

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 :

Pour modifier :


 Pour en savoir plus :

Programmation en JavaScript :


© Serge Mehl - www.chronomath.com