Aller au contenu
Encyclopédie informatique Édition 2026

Le tableur

Partie 5 sur 6

Niveau de lecture :

11Les fonctions

Les tableurs fournissent « un grand nombre de fonctions intégrées permettant de réaliser des manipulations de données ». Une fonction est caractérisée par son nom, écrit par convention en capitales, suivi de parenthèses qui contiennent ses arguments : les valeurs auxquelles elle s’applique. En version française, les arguments sont séparés par des points-virgules : FONCTION(argument1; argument2; …). Les arguments peuvent être des constantes, des références, des plages, ou le résultat d’une autre fonction (on dit qu’on imbrique les fonctions) : =ARRONDI(MOYENNE(B2:B30);1). On lit une telle formule de l’intérieur vers l’extérieur : d’abord la moyenne, puis l’arrondi à une décimale.

On classe les fonctions en grandes catégories : arithmétiques, statistiques, de date, logiques et financières. On y ajoute aujourd’hui les fonctions de texte (partie 9), de recherche et de référence, d’information et de base de données. Pour en trouver une, on utilise le bouton « insérer une fonction », qui propose une liste classée et une aide intégrée, ou on tape le début du nom et le logiciel propose les fonctions qui correspondent.

11.1Fonctions mathématiques de base

Une trentaine de fonctions sont courantes ; voici les plus utiles, avec leur sens exact :

  • SOMME(v1; v2; …) additionne ; PRODUIT multiplie ; SOMME.CARRES additionne les carrés ;
  • ABS(x) donne la valeur absolue (le nombre sans son signe) ;
  • MOD(x; y) donne le reste de la division entière de x par y ; PAIR et IMPAIR arrondissent au nombre pair ou impair suivant (en s’éloignant de zéro) ;
  • PUISSANCE(x; y) équivaut à x^y ; RACINE(x) donne la racine carrée ;
  • MAX et MIN donnent la plus grande et la plus petite valeur ;
  • ALEA() donne un nombre pseudo-aléatoire entre 0 et 1, recalculé à chaque changement dans la feuille (il est tiré à partir de l’horloge) ; ALEA.ENTRE.BORNES(a; b) donne un entier aléatoire compris entre a et b ;
  • FACT(n) donne la factorielle, produit 1 × 2 × … × n ; EXP, LN, LOG10 et LOG concernent exponentielle et logarithmes. LN est le logarithme népérien ; LOG10 est en base 10 ; LOG(x; base) prend la base en second argument et, sans elle, la base 10.

Les fonctions de trigonométrie (SIN, COS, TAN, ASIN, ACOS, ATAN, leurs versions hyperboliques SINH, COSH, TANH, et les conversions DEGRES et RADIANS) travaillent en radians (un tour complet vaut 2π radians, soit 360 degrés). Pour le sinus de 30 degrés, on écrit =SIN(RADIANS(30)), qui donne 0,5. PI() donne π, soit environ 3,14159265358979 avec quinze chiffres significatifs. Une précision utile : on lit parfois, pour plusieurs de ces fonctions, que l’argument doit être « compris entre −1 et 1 » ; cette condition est vraie pour ASIN et ACOS (arcsinus et arccosinus), mais fausse pour COSH et ATAN, qui acceptent n’importe quel nombre. Une valeur hors limites donne l’erreur #NOMBRE!, et non « NaN » comme on le lit parfois (NaN, « not a number », est le nom utilisé en programmation).

11.2Arrondir correctement

Les fonctions d’arrondi sont les suivantes : ARRONDI(valeur; nombre) arrondit au nombre de décimales indiqué, ARRONDI.INF arrondit vers le bas, ARRONDI.SUP vers le haut, ENT garde la partie entière, TRONQUE coupe sans arrondir, PLANCHER et PLAFOND arrondissent au multiple voisin. Quelques précisions utiles : ARRONDI(2,5;0) donne 3 (les valeurs au milieu sont arrondies en s’éloignant de zéro) ; le nombre de décimales peut être négatif (ARRONDI(1234;-2) donne 1200) ; ENT(-2,5) donne -3 (l’entier inférieur), alors que TRONQUE(-2,5) donne -2. Pour une facture, on arrondit chaque ligne avant de les additionner, afin que le total affiché corresponde à la somme des lignes affichées.

11.3Fonctions statistiques

Voici les principales fonctions statistiques :

  • MOYENNE : la moyenne des valeurs numériques (elle ignore les cellules vides et le texte) ;
  • MEDIANE : la valeur qui sépare la moitié inférieure de la moitié supérieure des données ; elle résiste mieux que la moyenne aux valeurs extrêmes ;
  • MODE.SIMPLE : la valeur la plus fréquente ;
  • ECARTYPE.STANDARD : l’écart type, mesure de la dispersion autour de la moyenne ;
  • NB compte les cellules qui contiennent un nombre ; NBVAL compte les cellules non vides ; NB.VIDE compte les vides ;
  • RANG donne le rang d’une valeur dans une liste ; GRANDE.VALEUR et PETITE.VALEUR donnent la k-ième plus grande ou plus petite ;
  • CENTILE et QUARTILE donnent les valeurs de partage d’une série.

Choisir entre moyenne et médiane est une décision de sens : le salaire moyen d’une salle où entre un milliardaire n’a plus de signification, la médiane reste proche de la réalité de la plupart des personnes.

11.4Fonctions de date et d’heure

Comme une date est un nombre (partie 6), on dispose d’outils simples : AUJOURDHUI() donne la date du jour (recalculée à chaque ouverture) et MAINTENANT() la date et l’heure ; DATE(année; mois; jour) construit une date ; ANNEE, MOIS, JOUR en extraient une partie ; JOURSEM donne le jour de la semaine ; JOURS(fin; début) donne le nombre de jours entre deux dates (ou tout simplement la soustraction =B2-A2) ; NB.JOURS.OUVRES compte les jours ouvrés (hors week-ends et jours fériés que vous listez) ; FIN.MOIS donne le dernier jour d’un mois ; DATEDIF calcule un écart en années, mois ou jours (fonction ancienne, peu documentée mais toujours disponible dans Excel). L’âge d’une personne née en B2 s’obtient par =DATEDIF(B2;AUJOURDHUI();"Y").

11.5Fonctions de recherche

La famille la plus utile au travail est celle de la recherche dans une table : retrouver, à partir d’un code, le libellé ou le prix correspondant. L’ancienne fonction RECHERCHEV(valeur; table; n° de colonne; faux) cherche une valeur dans la première colonne d’une table et renvoie la cellule de la n-ième colonne ; son dernier argument FAUX impose une correspondance exacte (sans lui, la recherche suppose une table triée et donne des résultats trompeurs). Excel propose depuis 2019-2020 une fonction plus claire et plus sûre, RECHERCHEX(valeur; colonne_où_chercher; colonne_résultat), qui cherche dans n’importe quelle colonne, renvoie une valeur par défaut si rien n’est trouvé, et ne casse pas si l’on insère une colonne. Les combinaisons INDEX et EQUIV (qui donne la position d’une valeur dans une liste) jouent le même rôle dans toutes les versions et dans les autres tableurs. Calc et Sheets offrent aussi RECHERCHEV ; RECHERCHEX est arrivée plus tard ou s’appelle autrement selon les logiciels.

11.6Les formules « dynamiques »

Dans les versions récentes d’Excel (Microsoft 365) et dans Google Sheets, une formule peut renvoyer plusieurs valeurs d’un coup, qui se « déversent » dans les cellules voisines. =TRIER(A2:B30), =UNIQUE(A2:A100) ou =FILTRE(A2:C100;C2:C100="oui") remplacent des manipulations autrefois manuelles. C’est un changement important dans la manière de construire une feuille. Ces formules ne fonctionnent pas dans les versions anciennes d’Excel : un fichier qui les contient peut afficher des erreurs chez un correspondant qui n’a pas la même version.

11.7Fonctions financières

Enfin, les fonctions financières servent à calculer, par exemple, la mensualité d’un prêt (VPM(taux; durée; capital) : le taux et la durée doivent être exprimés dans la même unité, mensuelle ou annuelle), la valeur future d’un placement (VC), la valeur actuelle (VA), le taux de rentabilité (TRI). Leur usage demande de bien connaître la finance qu’elles modélisent : le logiciel ne vérifie pas que les hypothèses sont raisonnables.

Pour s’exercer

Exercice 5 : statistiques sur une série

Les notes sont : 12, 8, 15, 10, 8. Donnez la somme, la moyenne, la médiane, le mode, le minimum, le maximum et le nombre de notes d’au moins 10.

Voir la réponse

Somme : 12 + 8 + 15 + 10 + 8 = 53. Moyenne : 53 / 5 = 10,6. Médiane : on trie (8, 8, 10, 12, 15) et on prend la valeur du milieu, 10. Mode : la valeur la plus fréquente, 8 (deux fois). Minimum : 8. Maximum : 15. Notes d’au moins 10 : 12, 15, 10, soit 3. Remarquez que la moyenne (10,6) est supérieure à la médiane (10) : la note de 15 tire la moyenne vers le haut.

Exercice 6 : diagnostiquer des erreurs

Un collègue vous montre quatre cellules : (a) #DIV/0! dans un calcul de moyenne par personne ; (b) #REF! dans une cellule qui marchait hier ; (c) la somme d’une colonne vaut 0 alors qu’on voit des nombres ; (d) #NOM? dans =SOMEM(B2:B9). Quelle est la cause probable de chacune ?

Voir la réponse

(a) Le dénominateur (le nombre de personnes, ou le nombre de notes) vaut zéro ou est vide ; on peut tester avant de diviser avec un SI. (b) Une cellule, une ligne, une colonne ou une feuille référencée a été supprimée depuis hier. (c) Les « nombres » sont en réalité du texte (alignés à gauche, souvent importés d’un fichier avec un séparateur décimal ou des espaces différents) ; on les convertit avec CNUM ou l’outil « Convertir » après nettoyage par SUPPRESPACE. (d) Faute de frappe sur le nom de la fonction : SOMEM au lieu de SOMME.

Exercice 7 : dates et arrondis

(a) Combien de jours séparent le 4 novembre 2026 du 25 décembre 2026, et quelle formule les calcule si ces dates sont en A1 et B1 ? (b) Quelle date obtient-on avec =DATE(2026;11;4)+30 ? (c) Pourquoi la formule =0,1+0,2=0,3 peut-elle poser problème dans un tableur, et comment l’éviter ?

Voir la réponse

(a) Du 4 au 30 novembre, il y a 26 jours, auxquels s’ajoutent 25 jours de décembre : 51 jours. Formule : =B1-A1 (ou =JOURS(B1;A1)). (b) Novembre compte 30 jours : le 4 novembre + 30 jours = 4 décembre 2026. (c) Les nombres décimaux sont stockés en binaire, où 0,1 et 0,2 n’ont pas de représentation exacte ; la somme peut différer d’un millionième de milliardième de 0,3 et le test peut échouer. On compare des valeurs arrondies : =ARRONDI(0,1+0,2;10)=ARRONDI(0,3;10).

12Erreurs affichées et pièges

Un tableur ne dit presque jamais « votre calcul est faux » : il affiche un résultat, parfois absurde, parfois très plausible. Deux familles d’erreurs sont à distinguer. Les erreurs visibles sont des messages qui commencent par # ; les erreurs silencieuses donnent un chiffre faux sans aucun avertissement. Ces dernières sont de loin les plus dangereuses.

12.1Les messages d’erreur

  • #DIV/0! : division par zéro ou par une cellule vide. Vérifiez le dénominateur.
  • #REF! : la référence n’existe plus (ligne ou colonne supprimée, feuille effacée).
  • #NOM? : nom de fonction ou de plage inconnu (faute de frappe, guillemets oubliés, fonction qui n’existe pas dans votre version).
  • #VALEUR! : le type d’une valeur ne convient pas (calcul sur du texte).
  • #N/A : valeur introuvable (typiquement une recherche qui échoue).
  • #NOMBRE! : nombre invalide (racine carrée d’un nombre négatif, résultat trop grand).
  • #NUL! : intersection vide de deux plages (rare).
  • ##### : ce n’est pas une erreur de calcul, mais une colonne trop étroite pour afficher le nombre ; il suffit de l’élargir.

12.2Les erreurs silencieuses

  • Un nombre stocké comme texte : il est aligné à gauche, une petite flèche verte apparaît, et la somme l’ignore.
  • Une plage trop courte : =SOMME(B2:B29) alors que les données vont jusqu’à B30. Une ligne ajoutée à la fin n’est pas incluse. Les tableaux structurés (partie 13) évitent ce piège.
  • Une référence relative non verrouillée (partie 4), qui donne un résultat faux seulement à partir de la deuxième ligne.
  • Une valeur « en dur » cachée dans une formule : =B2*1,2, où le 1,2 est un taux oublié quand il change. On met le taux dans une cellule, nommée ou verrouillée.
  • Une comparaison entre deux résultats de calcul avec = (voir plus bas).
  • Des lignes ou colonnes masquées qui contiennent des valeurs comptées dans une somme sans qu’on les voie.
  • Un calcul laissé en mode manuel (partie 7).

12.3Les décimales binaires

Un ordinateur stocke les nombres en base 2 (voir les nombres en informatique). Or beaucoup de fractions décimales simples n’ont pas d’écriture finie en base 2, tout comme un tiers n’a pas d’écriture finie en base 10. C’est le cas de 0,1 et de 0,2. Les tableurs utilisent en général la représentation « double précision » (environ quinze à dix-sept chiffres significatifs). Ainsi, dans de nombreux logiciels, 0,1+0,2 vaut en réalité 0,30000000000000004. Le tableur masque en général cet écart en n’affichant que quinze chiffres ; mais un test comme =(0,1+0,2)=0,3 peut donner un résultat inattendu, ou la différence =0,1+0,2-0,3 peut afficher un nombre minuscule différent de zéro (de l’ordre de 5,55E-17). Règle : ne jamais comparer directement le résultat de deux calculs décimaux. On arrondit d’abord : =ARRONDI(0,1+0,2;10)=ARRONDI(0,3;10). Les calculs d’argent sont exposés à ce problème ; on les arrondit au centime à chaque ligne.

Autre limite : la précision d’un nombre est limitée à quinze chiffres significatifs. Un numéro de carte ou de compte de seize chiffres saisi comme nombre perd ses derniers chiffres (ils sont remplacés par des zéros). Les identifiants longs se saisissent donc comme du texte.

12.4Une particularité historique : l’année 1900

Pour rester compatible avec Lotus 1-2-3, Excel considère par erreur que l’année 1900 est bissextile (elle ne l’est pas : une année de siècle n’est bissextile que si elle est divisible par 400). Le 29 février 1900 existe donc dans Excel, et les calculs de jours qui franchissent cette date (avant le 1er mars 1900) sont décalés d’un jour. Cela ne concerne pas les dates modernes, mais illustre une règle plus générale : même les logiciels très répandus gardent des choix du passé.