SOMMAIRE
Les fonctions mathématiques et statistiques
Les nombres et leur représentation - Les arrondis
Notation des cellules
Une cellule EXCEL est identifiée par sa colonne (A à IV) et son numéro de ligne (1 à 65.536 pour Excel 97). " A1 " et " IV65536 " identifient en conséquence les deux cellules extrêmes d’EXCEL, la plus en haut à gauche et la plus en bas à droite.
Lorsqu’on se servira de la possibilité de désigner une cellule par un nom symbolique, il faudra veiller à éviter les noms pouvant être confondus avec une référence de cellule. Ainsi, appeler une cellule " CA98 " si elle contient le chiffre d’affaires 1998 peut prêter à confusion avec la cellule colonne CA, ligne 98.
Plage de cellules
Une plage de cellules est une façon rapide de noter une série de cellules adjacentes. La notation " A1 :B3 " identifiera les cellules A1, A2, A3, B1, B2 et B3.
Contenu d’une cellule
Cellule de type " Texte "
Contient une chaîne de caractères alphabétiques et/ou numériques (titre d’une colonne ou d’une ligne, texte,…). Par défaut, ces cellules sont cadrées à gauche.
Exemple : " C.A. 1998 "
Cellule de type " Numérique "
Contient un nombre. Par défaut ces cellules sont cadrées à droite.
Exemple : " 12254 "
Cellule de type " Résultat "
Commence obligatoirement par le symbole " = ". Ce symbole indique à EXCEL qu’il doit effectuer une opération ou une fonction, et afficher dans cette cellule le résultat de cette opération ou fonction. Ces cellules sont cadrées à droite ou à gauche, en fonction de la nature du résultat.
Exemple : " =12+30 " (Excel affichera la valeur " 42 ")
" =A3*B5 " (Excel affichera le produit entre les valeurs contenues dans A3 et B5)
Syntaxe générale des fonctions
=<Nom fonction>(Argument 1 ;Argument 2 ;… ;Argument n)
" = " est obligatoire en début de cellule. Sinon, la fonction sera interprétée comme un simple texte.
" <Nom fonction> " identifie la fonction demandée. Excel possède 225 fonctions intégrées de toute nature, qui permettent d’automatiser un nombre considérable de calculs.
Quelque soit la nature de la fonction, la syntaxe générale restera la même.
" Argument x " est un élément dont la fonction a besoin pour donner un résultat. Il existe des fonctions qui n’ont besoin d’aucun argument (" PI() " renverra 3,14159…, avec une précision de 14 décimales, sans qu’il soit besoin de préciser un argument quelconque). D’autres utiliseront un ou plusieurs arguments, qui permettront de paramétrer plus précisément le résultat attendu.
Exemple : " ARRONDI(PI() ;3) " renverra 3,142.
" SOMME(A2 :A4 ;D4 :D8) renverra la somme des cellules des deux plages A2 :A4 et D4 :D8.
Les différents arguments d’une fonction sont séparés par le symbole " ; " (point virgule).
Ils effectuent des opérations arithmétiques entre deux nombres ou le contenu de deux cellules.
" + " Addition entre deux nombres. " 12+25 " renverra " 37 "
" - " Soustraction entre deux nombres. " 12-2 " renverra " 10 "
" * " Multiplication entre deux nombres. " 12*3 " renverra " 36 "
" / " Division entre deux nombres. " 12/4 " renverra " 3 "
" ^ " Exponentiation (élever à la puissance). " 12^2 " renverra " 144 "
" % " Pourcentage. Divise le nombre par 100. " 12% " équivaut à " 0,12 " (mais sera affiché " 12% ")
Ordre des opérations
Les opérateurs sont exécutés dans Excel dans l’ordre de priorité arithmétique : exponentiation, multiplication et division, et enfin addition et soustraction. En cas d’opérateurs de même poids dans la même formule (multiplication et division par exemple), les opérations sont effectuées de gauche à droite.
" 12+2*3 " Renverra " 18 ". La multiplication " 2*3 " est effectuée en premier, puis l’addition.
" 10*3^3 " Renverra " 270 ". L’exponentiation est effectuée en premier, puis la multiplication.
On peut changer l’ordre des opérations en insérant des parenthèses. Le contenu des parenthèses est évalué en premier, puis les opérations en dehors des parenthèses sont effectuées.
" (12+2)*3 " Renverra " 42 ". " 12+2 " est effectué en premier, et le résultat est multiplié par 3.
" (10*3)^3 " Renverra " 27000 ". La parenthèse est calculée, et son résultat élevé à la puissance 3.
Les opérateurs de comparaison ne renvoient pas de valeur numérique, mais une valeur logique : " VRAI " ou " FAUX ". Cette valeur logique peut être utilisée pour conditionner un résultat.
Il est rare qu’un opérateur de comparaison soit utilisé seul dans une cellule, sauf lorsqu’on cherche à mettre au point une formule complexe. En règle générale, un opérateur de comparaison servira d’argument à une fonction booléenne ou conditionnelle (voir ci-après).
Les opérateurs de comparaison disponibles sont :
" = " est égal à
" < " est inférieur à
" > " est supérieur à
" <= " est inférieur ou égal à
" >= " est supérieur ou égal à
" <> " est différent de
Exemples : Résultats des opérateurs en fonction des valeurs de A1 et de B1
Opérateurs booléens (fonctions booléennes)
Il ne s’agit pas à proprement parler d’opérateurs comme ceux décrits ci-dessus, mais de fonctions qui permettent la combinaison de plusieurs opérations de comparaison.
Il peut être intéressant, pour résoudre un problème, de vérifier si plusieurs conditions sont remplies simultanément. Imaginons un tableau de notes scolaires. La dernière colonne pourrait contenir une proposition pour le conseil de classe : félicitations pour l’élève dont la moyenne générale est supérieure à 12 et qui n’a aucune note inférieure à 10. Voici le tableau correspondant :
La colonne " FELIC. " teste :
La fonction booléenne permettant de tester les deux conditions simultanément est la suivante :
=ET(MOYENNE(B2:G2>=12);MIN(B2:G2>=10))
Fonction ET(…)
Syntaxe : ET(condition 1;condition 2;…)
Résultat : renvoie " VRAI " si toutes les conditions sont vérifiées. Renvoie " FAUX " si une ou plusieurs des conditions ne sont pas vérifiées.
Notes : Il suffit qu’une seule des conditions testées soit fausse pour que la fonction renvoie la valeur logique " FAUX ".
Fonction OU(…)
Syntaxe : OU(condition 1;condition 2;…)
Résultat : renvoie " VRAI " si une au moins des conditions testées est vérifiée. Renvoie " FAUX " si TOUTES les conditions testées ne sont pas vérifiées.
Notes : Dans l’exemple ci-dessus (tableau de notes), cette fonction aurait renvoyé " VRAI " pour la 3ème élève (moyenne générale inférieure à 12, aucune note inférieure à 10 : une des deux conditions est vérifiée)
Remarque générale sur les conditions
" condition " peut être n’importe quelle fonction renvoyant une valeur logique " Vrai " ou " Faux " : opération de comparaison directe entre deux valeurs ou fonction booléenne. Il est donc possible d’imbriquer les unes dans les autres plusieurs fonctions booléennes pour réaliser des tests complexes.
Imaginons par exemple que les félicitations soient accordées à un élève s’il n’a aucune note inférieure à 10 et soit une moyenne supérieure à 12, soit une note au moins supérieure à 15. On écrirait la fonction suivante :
=ET(MIN(B2:G2)>=10;OU(MOYENNE(B2:G2)>=12 ; MAX(B2:G2)>=15)
Les fonctions mathématiques et statistiques
Fonctions simples
Toutes les fonctions de ce groupe possèdent la même syntaxe, très simple, limitée à une suite d’arguments de même nature (valeurs ou adresses de cellule). Elles permettent néanmoins de résoudre de nombreux problèmes.
Fonction SOMME()
Syntaxe : SOMME(valeur 1 ; valeur 2 ; valeur 3 ; …)
Résultat : renvoie la somme algébrique des valeurs passées en argument.
Exemples : SOMME(A5 : C5) renvoie la somme des valeurs contenues dans A5, B5 et C5 (équivaut à A5+B5+C5)
SOMME(12 ; 25 ; B7) renvoie la somme 12+25+ la valeur contenue dans B7 (équivaut à 12+25+B7)
Notes : Lorsqu’il s’agit de faire l’addition de deux ou trois cellules, l’écriture de l’opération d’addition directe est aussi rapide et efficace que l’utilisation de la fonction SOMME. Lorsque le problème demande à faire l’addition d’une colonne ou d’une ligne comportant plusieurs cellules, la fonction SOMME est plus rapide, plus simple et plus efficace que l’addition directe.
L’un des intérêts d’utiliser la fonction SOMME dans une addition comportant plusieurs lignes est qu’il n’est pas besoin de modifier la cellule contenant la fonction si l’on insère une nouvelle ligne au milieu : la fonction prendra automatiquement en charge cette nouvelle ligne. Si l’on avait utilisé les références de cellules pour effectuer une addition directe, il aurait fallu modifier la formule résultat pour prendre en compte la nouvelle ligne.
Fonction MOYENNE()
Syntaxe : identique à SOMME()
Résultat : renvoie la moyenne arithmétique des valeurs passées en arguments
Exemple : MOYENNE(B2 : G2) effectue la somme des valeurs contenues en B2, C2, D2, E2, F2 et G2, compte le nombre de valeurs additionnées et divise la somme calculée par ce nombre de valeurs. Les cellules vides ne sont pas comptées dans cette moyenne.
Si l’on suppose que toutes les cellules de B2 à G2 contiennent une valeur, MOYENNE(B2 : G2) équivaut à SOMME(B2 : G2) / 6.
L’intérêt d’utiliser MOYENNE(B2 : G2) plutôt que l’équivalent ci-dessus est que si une des cellules concernées ne contient aucune valeur (c’est-à-dire qu’il n’y a en tout que 5 valeurs à traiter), MOYENNE(B2:G2), portant sur une plage de 6 cellules, effectuera la somme des seules 5 valeurs présentes, et la divisera par 5, sans qu’il soit besoin de préciser quoi que ce soit.
Fonction MIN() et MAX()
Syntaxe : Identique à SOMME()
Résultat : renvoie la plus petite valeur (MIN()) ou la plus grande valeur (MAX()) contenue dans les arguments donnés.
Exemple : La tableau ci-dessous donne le plus bas et le plus haut cours de deux actions sur une période de trois mois.
Autres fonctions mathématiques et statistiques
EXCEL possède, on l’a vu, plus de 200 fonctions intégrées, qui permettent les calculs les plus complexes. Ces fonctions sont regroupées par famille (fonction maths et trigo, statistiques, financières, date et heure, …). En statistiques par exemple, les fonctions disponibles permettent les opérations simples (MOYENNE()) et les calculs statistiques avancés (LOI.POISSON(), KHIDEUX.INVERSE(), …), en passant pas des calculs statistiques courants, mais longs à réaliser à la main (ECART.TYPE())
Il n’est pas nécessaire de connaître par cœur toutes les fonctions disponibles : l’assistant Fonction (menu Insertion/Fonction) permet de repérer facilement la fonction qui peut être utile pour résoudre un problème donné, et de l’insérer dans une cellule rapidement.
Il est important tout de même d’explorer régulièrement cet assistant, ne serait-ce que pour se remettre en mémoire les fonctions disponibles : le jour venu, devant un besoin particulier, il sera peut-être utile de se remémorer qu’il est possible, avec Excel, de traiter ce problème à l’aide d’une fonction donnée.
Les nombres : affichage et représentation
La précision des calculs dans EXCEL
Excel est un outil universel, susceptible d’être utilisé pour des calculs statistiques aussi bien que commerciaux. Les besoins des uns et des autres en matière de précision sont très différents.
En statistiques ou en calcul scientifique, il est nécessaire de disposer d’une grande précision : les calculs se font avec un grand nombre de décimales.
En gestion, la plupart des calculs ne nécessitent que 2 décimales, et un grand nombre de tableaux de synthèse se font en valeurs arrondies au franc, voire en KF ou en MF (milliers ou millions de francs).
EXCEL conserve, pour toutes les opérations arithmétiques, jusqu’à 14 décimales. Les formats d’affichage (monétaire, nombre avec n décimales, …) permettent de " masquer " les décimales inutiles. Mais ce masquage ne concerne que l’affichage du nombre. En réalité, EXCEL conserve en mémoire la TOTALITE des décimales résultant du calcul.
Ceci est très satisfaisant lorsqu’on exécute des calculs statistiques ou scientifiques, mais peut poser des problèmes lors de la résolution de calculs de gestion, où l’arrondi à deux décimales est une règle applicable à chaque calcul.
Par exemple, un montant de facture de 1 234,55 F, avec une remise de 7 % et une TVA de 20.6 % donnera les calculs intermédiaires suivants :
Calcul Affichage
Un arrondi des nombres à deux décimales aurait donné un montant TTC de 1 384,64 F. A ce stade, l’erreur est peu importante, et masquée par les fonctions d’affichage. Mais plus le nombre de calculs concerné est important, plus la précision en décimales induira une différence avec un résultat " commercial ".
Ceci sera d’autant plus important lors du passage de Franc à l’Euro, puisque, dès lors, l’arrondi deviendra une obligation légale.
Les fonctions d’arrondi de nombre
EXCEL dispose de plusieurs fonctions d’arrondi, qui permettent d’éliminer dans le nombre conservé les décimales inutiles. Les trois principales sont :
La première réalise un arrondi selon la règle 5/4, la seconde est appelée " arrondi par excès), et la troisième " arrondi par défaut " ou encore " troncage ".
La syntaxe de ces trois fonctions est la même :
<fonction d’arrondi>(<nombre à arrondir> ; <nombre de décimales à conserver>)
Exemple : ARRONDI.SUP(B5 ;2)
Exemple de résultat obtenu avec les trois fonctions d’arrondi (l’affichage du contenu des cellules est supposé être avec 4 décimales) :
Valeur de la cellule B5 |
ARRONDI(B5 ;2) |
ARRONDI.SUP(B5 ;2) |
ARRONDI.INF(B5 ;2) |
135,4435 |
135,4400 |
135,4500 |
135,4400 |
135,4455 |
135,4500 |
135,4500 |
135,4400 |
135,4491 |
135,4500 |
135,4500 |
135,4400 |
On remarquera que, quelque soit la valeur contenue dans B5, ARRONDI.SUP et ARRONDI.INF renvoient toujours le même résultat. Seule la valeur renvoyée par ARRONDI change, en fonction de la troisième décimale du contenu de B5.
D’autre part, on notera que les décimales non désirées (au-delà de la deuxième) ne sont pas simplement masquées, mais éliminées.
Quand utiliser les fonctions d’arrondi ?
En matière de calcul commerciaux, en principe à chaque étape. Lors du passage définitif du Franc à l’Euro ; il conviendra d’examiner les règles de conversion, mais il semble a priori que celle-ci se fera sur chaque nombre, donc en éliminant les décimales superflues à chaque étape. La fonction indispensable et obligatoire sera ARRONDI() (règle des arrondis 5/4).
Les autres fonctions peuvent trouver une application par exemple dans les calculs concernant une déclaration de TVA, où la " règle des arrondis " doit s’appliquer :