Les fonctions Mathématiques

Difficulté Facile
Note
Mis à jour le lundi 22 avril 2013

Dans cette première partie, nous allons étudier les fonctions « Mathématiques » d'Excel. Elles se trouvent ici :

A partir du ruban et de l'onglet « Formules », de la rubrique « Bibliothèque de fonctions » et dans la catégorie « Maths et trigonométrie ».

Image utilisateur

Ou à partir du ruban et de l'onglet « Formules », de la rubrique « Bibliothèque de fonctions » et de cliquer sur « Insérer une fonction ». Une fenêtre s'ouvre, sélectionner dans le menu déroulant de la catégorie : « Math & trigo. » :

Image utilisateur

Je vais vous proposer des fonctions de base de la catégorie « Mathématiques et trigonométrie » qui ne sont pas forcement intuitives. D'autres fonctions existent mais sont très simples d'utilisation.

Pour suivre avec moi cette sous-partie et vous exercer de votre côté, je vous propose de :

Image utilisateurTélécharger le fichier fonctions_mathematiques.xlsx

Ce classeur Excel contient tous les exemples utilisés dans cette sous-partie. Il y a la base des exemples, à vous d'entrer les formules.

INTRODUCTION

Dans cette introduction, nous allons parler des opérateurs et des priorités mathématiques. Cela peut paraître facile, mais un rappel n'est pas une perte de temps pour certains. :-° Les trois fonctions qui suivent permettent d'effectuer les opérations suivantes : addition, soustraction, multiplication, division.

Un petit tableau qui récapitule les signes utilisés pour ces opérations :

Opération

Opérateur

Addition

+

Soustraction

-

Multiplication

*

Division

/

Dans une formule Excel, on peut utiliser ces opérateurs pour effectuer des calculs. Mais lorsqu'il s'agit d'additionner 50 cellules, la formule devient très longue. C'est pourquoi les fonctions sont utiles.

Petit rappel mathématique : les opérations de multiplication et division sont prioritaires sur les opérations d'addition et de soustraction.

Une formule est lue et exécutée de gauche à droite et effectue les opérations dans l'ordre. Mais elle respecte les propriétés opératoires rappelées juste avant. La formule effectue donc d'abord toutes les multiplications et divisions et ensuite les additions et soustractions. Si des additions doivent être effectuées avant les multiplications par exemple, il faut alors utiliser les parenthèses. Ainsi, une addition entre parenthèses est effectuée AVANT une multiplication. Voici des exemples :

Formule

Résultat

=10+3*5-2

23

=(10+3)*3-2

37

=(15+30)/(2+1)

15

=5*6+3

33

=(5*6)+3

33

J'espère que ça vous a rappelé de bons souvenirs et que vous connaissez maintenant ces opérations et opérateurs. Des erreurs courantes viennent de ces priorités opératoires non prises en compte par l'utilisateur.

SOMME

Que permet-elle ?

Elle permet l'addition de plusieurs nombres ou cellules.

Comment s'écrit-elle et quels paramètres ?

La fonction SOMME s'écrit de la façon suivante et prend un nombre d'arguments très variable.

=SOMME(100;250)

Mais la plupart du temps, on ne connaît pas les nombres à additionner on utilise alors les coordonnées de cellules de cette façon :

=SOMME(E2;F4)

On peut aussi additionner plusieurs cellules différentes ou même des plages de cellules. Pour plusieurs cellules on utilise le point-virgule (;) pour séparer les cellules. Lorsqu'il s'agit d'une plage de cellules, on entre la première cellule de la plage et la dernière cellule de cette même plage séparées par deux points (:). Pour vulgariser et bien retenir, le point-virgule (;) signifie "et", et les deux points (:) signifient "jusqu'à".

=SOMME(E2;F4;G6) pour calculer la somme des valeurs des cellules E2, F4 et G6.
=SOMME(E2:E5) pour calculer la somme des valeurs des cellules E2, E3, E4 et E5.

Un exemple théorique et un exemple concret

Voici un exemple théorique sur des données aléatoires :

Image utilisateur

Dans la colonne B on a les formules entrées dans la colonne C et qui nous donnent les résultats de la capture d'écran.

Nous allons voir maintenant un exemple plus concret. Dans une équipe de handball, nous allons voir combien de buts chaque joueur a marqués (résultats fictifs). Voici ce que ça donne :

Image utilisateur

Nous venons de voir une utilisation concrète de la fonction SOMME mais elle est souvent combinée à d'autres fonctions. Vous savez quand même comment faire une somme de plusieurs cellules.

PRODUIT

Que permet-elle ?

Elle permet de multiplier plusieurs nombres ou cellules entre eux.

Comment s'écrit-elle et quels paramètres ?

La fonction PRODUIT s'écrit de la même façon que la fonction SOMME et fonctionne exactement de la même façon.

Un exemple théorique et un exemple concret

Voici un exemple théorique sur des données aléatoires :

Image utilisateur

Avec un exemple plus concret, on peut voir l'utilité de la fonction dans une facture par exemple et on peut combiner la fonction SOMME :

Image utilisateur

QUOTIENT

Que permet-elle ?

Elle permet de renvoyer la partie entière d'une division.

Comment s'écrit-elle et quels paramètres ?

La fonction QUOTIENT s'écrit de la façon suivante et prend deux paramètres : le diviseur et le dividende.

=QUOTIENT(100;25)

Mais la plupart du temps, on ne connaît pas les nombres à diviser on utilise alors les coordonnées de cellules de cette façon :

=QUOTIENT(E2;F4)

Un exemple théorique et un exemple concret

Voici un exemple théorique sur des données aléatoires :

Image utilisateur

Avec un exemple plus concret, on peut voir l'utilité de la fonction dans le calcul de la répartition des denrées par élève, étant donné qu'il est difficile de distribuer des quarts de bonbons, il est préférable d'avoir des valeurs entières :

Image utilisateur

Simplifier ces fonctions

Nous venons de voir trois fonctions d'Excel qui sont très souvent utilisées et peuvent être simplifiées grâce aux opérateurs numériques que nous avons vus en introduction. Les voici :

Description

Opérateur

Simplification

Somme

+

=SOMME(B2;C4) revient à écrire =B2+C4

Différence

-

=SOMME(B2;-C4) revient à écrire =B2-C4

Produit

*

=PRODUIT(B2;C4) revient à écrire =B2*C4

Quotient

/

Pas de simplification

Nous pouvons prendre comme exemple un bulletin de notes pour regrouper l'addition, la multiplication et la division. Pour calculer la moyenne d'un élève au bac, on calcule dans un premier temps le nombre de points que rapporte chaque matière en multipliant la note par le coefficient. Dans un second temps, on obtient le nombre total de points obtenus et le nombre de coefficients total. Enfin, pour calculer la moyenne on divise le nombre de points par le nombre de coefficients pour avoir la moyenne sur 20. Dans notre exemple, notre élève de terminale S spécialité physique-chimie (précision qui n'a aucun intérêt ^^ ), obtient la moyenne de 13,71 :

Image utilisateur

Voilà la partie la plus simple de ce tutoriel de terminée. Bah ouais, on a juste vu les fonctions de calcul de base... On attaque la suite avec une nouvelle fonction.

MOD

Que permet-elle ?

Elle permet de renvoyer le reste d'une division.

Comment s'écrit-elle et quels paramètres ?

La fonction MOD s'écrit de la façon suivante et prend deux paramètres (comme pour la fonction QUOTIENT en fait).

=MOD(100;18)

Mais la plupart du temps, on ne connaît pas les nombres à diviser on utilise alors les coordonnées de cellules de cette façon :

=MOD(E2;F4)

Un exemple théorique et un exemple concret

Voici un exemple théorique sur des données aléatoires :

Image utilisateur

Pour ce qui est de l'exemple plus concret, on peut reprendre la liste des denrées par enfants. Mais ici, la colonne de résultat nous donne les restes après le partage équitable des denrées.

Image utilisateur

PGCD

Que permet-elle ?

Elle permet de renvoyer le plus grand dénominateur commun de plusieurs nombres ou cellules.

Comment s'écrit-elle et quels paramètres ?

La fonction PGCD s'écrit de la même façon que la fonction SOMME.

=PGCD(E2;F4;G6) pour calculer le PGCD des valeurs des cellules E2, F4 et G6.
=PGCD(E2:E5) pour calculer le PGCD des valeurs des cellules E2, E3, E4 et E5.

Un exemple théorique et un exemple concret

Voici un exemple théorique sur des données aléatoires :

Image utilisateur

Vous ne voyez pas l'utilité du PGCD ? Voici un exemple : vous cherchez à couvrir une surface de 210 cm sur 135 cm avec des carreaux de carrelage. Il vous faut le moins de carreaux possible donc des carreaux les plus grands possible. Il faut aussi qu'on ait que des carreaux entiers. En effet, couper un carreau de carrelage, c'est pas facile... On cherche alors la taille d'un carreau (carré) de carrelage. On utilise alors le PGCD!

Image utilisateur

Petite pause

Nous allons faire une pause dans les fonctions pour présenter le concept de condition utile dans ... beaucoup de fonctions et notamment dans les prochaines fonctions présentées. C'est une pause dans l'étude des fonctions, mais pas dans l'apprentissage ! Ce passage est très important, mais pas compliqué. Il faut bien comprendre tout ça pour utiliser à bon escient les fonctions qui comportent des conditions.

Pour démarrer, on va expliquer ce qu'est une condition. Une condition commence toujours par un SI. Dans la vie courante, on peut dire : "Si je finis de manger avant 13h, je vais regarder le journal télévisé". On peut aussi aller plus loin en disant "Sinon, j'achète le journal". Pour Excel, c'est la même chose. On a une fonction SI présentée plus en détail dans la partie sur les fonctions logiques qui fonctionne de la même façon. Une condition et donc un "si", une valeur si c'est vrai et une valeur si c'est faut (qui correspond au sinon).

Pour faire une condition, il faut un critère de comparaison. Lorsque vous faites un puzzle, vous triez en premier les pièces qui font le tour pour délimiter le puzzle et aussi parce que le critère de comparaison entre les pièces est simple : sur les pièces du tour, il y a un côté plat. Donc lorsque vous prenez une pièce en main, vous comparez les côtés de la pièce à un côté plat et vous la mettez soit dans la boîte des pièces du tour soit dans les pièces qui seront retriées par la suite.

Dans Excel, ce critère de comparaison est soit une valeur, une cellule ou encore du texte. On compare les données d'une cellule à notre critère de comparaison et Excel renvoie VRAI si la comparaison est juste sinon Excel renvoie FAUX et Excel exécute ce que vous lui avez dit de faire en fonction de ce que renvoie la comparaison.

Pour comparer des valeurs numériques ou même du texte, on utilise des signes mathématiques. Le plus connu des signes de comparaison est égal à (=). Si les valeurs sont égales, alors fait ça sinon fait ci. Je vous donne la liste de tous les opérateurs utilisés dans Excel pour les comparaisons :

Opérateur de comparaison

Signification

=

Égal à

>

Supérieur à

<

Inférieur à

>=

Supérieur ou égal à

<=

Inférieur ou égal à

<>

Différent de

On peut donc avec ces opérateurs de comparaison, faire toutes les comparaisons possibles entre deux valeurs. On va alors s'entraîner à faire des comparaisons avec des données :

Comparaison

A3<=A4

B7<>G9

L2>A1

B2=B5

A4>=F8

M3<D9

Alors, vous savez à quoi correspondent toutes ces comparaisons? La réponse juste en dessous.

Comparaison

Signification (Si...)

A3<=A4

A3 est plus petit ou égal à A4

B7<>G9

B7 est différent de G9

L2>A1

L2 est plus grand que A1

B2=B5

B2 est égal à B5

A4>=F8

A4 est plus grand ou égal à F8

M3<D9

M3 est plus petit que D9

Pour comparer à une valeur sans passer par la cellule, on entre la valeur telle quelle : A1=10. Par contre si on veut comparer à du texte, il faut alors le mettre entre guillemets : A1="Jean".

On va complexifier cette notion de condition et de comparaison au fil du cours dans les différentes fonctions.

SOMME.SI

Que permet-elle ?

Elle permet l'addition de plusieurs nombres ou cellules selon un critère de comparaison.

Comment s'écrit-elle et quels paramètres ?

La fonction SOMME.SI s'écrit de la façon suivante et prend 2 ou 3 paramètres.

=SOMME.SI(plage;critère;[somme_plage])

Le premier paramètre est la plage, c'est l'ensemble des cellules à comparer. Le second est le critère de comparaison, c'est à ce critère que la fonction va comparer les cellules de la plage. Enfin, le troisième paramètre est facultatif. S'il n'est pas présent, ce sont les valeurs de la plage qui sont additionnées. Si le paramètre somme_plage est renseigné, ce sont les cellules de cette plage qui sont additionnées.

Ce qui nous donne pour une écriture avec des données aléatoires:

=SOMME.SI(E2:E8;">10";F2:F8)

On vient de voir que quand il y avait une comparaison, Excel renvoie VRAI si elle est juste sinon elle renvoie FAUX et qu'Excel exécute ce qu'il faut en fonction. Ici, l'instruction qui est faite par Excel après la comparaison, c'est la prise en compte ou non de la valeur. En fait si la condition est vraie, la valeur correspondante est prise en compte, sinon elle n'est pas prise en compte dans l'addition. On ne choisit pas ce que fait la fonction après avoir renvoyé VRAI ou FAUX, c'est la fonction qui s'en charge seule. On choisit seulement avec la fonction SI étudiée dans les fonctions logiques.

Un exemple théorique et un exemple concret

Voici un exemple théorique sur des données aléatoires :

Image utilisateur

Petite explication : la fonction regarde la cellule B2 et la compare au critère que nous avons entré, si cette valeur est plus grande que 320, alors j'intègre la cellule C2 dans l'addition, sinon je ne prends pas en compte la cellule C2. La fonction répète cela pour toutes les cellules de la plage de B2 à B10.

Pour voir si vous avez compris, on va refaire un exemple avec un cas concret. On va faire nos courses. Pour économiser, on achète que les articles à moins de 10€. Combien d'articles vais-je avoir à la sortie du magasin ?

Image utilisateur

Si le prix du savon est inférieur à 10€ alors j'achète les 2 savons (donc 2 articles). On fait la même chose pour toutes les lignes de la plage. On se retrouve à la sortie du magasin avec 14 articles. Alors que si nous avions tout acheté, nous aurions eu 16 articles.

Voilà ce qu'il y a à savoir sur la fonction SOMME.SI.

SOMMEPROD

Que permet-elle ?

Elle permet de comptabiliser des données en multipliant des matrices entre elles. Pour être clair, elle permet de compter le nombre d'entrées d'une liste selon des conditions, mais aussi d'additionner des cellules d'une liste selon des conditions.

Comment s'écrit-elle et quels paramètres ?

La fonction SOMMEPROD s'écrit de la façon suivante et prend un nombre très variable d'arguments.

=SOMMEPROD((plage1="critère1")*(plage2="critère2")*(plage3)*...)

Les paramètres sont tous les mêmes, ce sont des plages de cellules. Elles peuvent prendre deux formes. Soit, comme dans l'exemple plage1 et plage2, elle est suivie d'un critère de comparaison soit, comme dans l'exemple plage3, elle ne possède pas de critère de comparaison. On peut compter le nombre de lignes où la plage1 (colonne 1) est égale à critère1 et où la plage2 (colonne 2) est égale à critère2. Si on insère une troisième plage (colonne 3), on additionne les cellules de cette plage.

Je ne comprends pas tout là, ça reste flou pour moi...

Comme pour la fonction précédente, on utilise des critères de comparaison qui varient selon le type de données dans les plages. Voici un exemple plus concret pour expliquer (une exemple est plus parlant qu'un long discours) :

=SOMMEPROD((E2:E8="Jacques")*(F2:F8="Janvier")*(G2:G8))

La première plage est comparée au critère "Jacques", la seconde au critère "Janvier" et la troisième n'a pas de critère de comparaison. La fonction va donc regarder sur la première ligne, si la première cellule est égale à "Jacques" et si la seconde cellule de la ligne est égale à "Janvier". Si c'est le cas, alors la valeur de la troisième cellule de la ligne est prise en compte dans l'addition. S'il n'y avait pas de troisième plage, la fonction se contente de compter le nombre de lignes où les deux comparaisons sont vraies. On obtient ainsi le nombre de fois où Jacques et Janvier sont sur la même ligne.

Avec cette fonction on peut compter le nombre de lignes qui respectent les conditions ou alors additionner les cellules de chaque ligne. Nous allons voir un exemple concret directement pour que ce soit plus facile à comprendre.

Un exemple concret

Nous allons utiliser une feuille de données que je vous montre ici (et disponible dans le fichier téléchargeable) :

Image utilisateur

Ce tableau représente les ventes de chaque vendeur d'un magasin sur les trois premiers mois de l'année. Il y a 4 vendeurs (Jean, Pierre, Paul, Jacques).

Comment faire pour savoir le nombre de ventes de Paul au mois de Mars ? En utilisant la fonction SOMMEPROD pardi !

Pour cela il faut entrer la formule suivante :

=SOMMEPROD((A2:A31="Paul")*(B2:B31="Mars"))

On obtient bien 3 ! Et oui Paul a fait 3 ventes au mois de Mars. Maintenant on cherche à savoir combien d'argent a rapporté Paul au mois de Mars. Il suffit de multiplier par la colonne "Montant" de cette manière :

=SOMMEPROD((A2:A31="Paul")*(B2:B31="Mars")*(C2:C31))

Tada ! On obtient donc 2230. En effet, la fonction a effectué le calcul suivant : 840+660+730=2230. C'est top non ? On peut faire plein de combinaisons avec cette formule ! Mais attention il y a quelques règles à respecter :

Je vous propose d'autres exemples pour bien comprendre et voir un peu ce que l'on peut faire avec cette fonction.

Exemple 1 : compter le nombre de ventes réalisées par Jean (on peut aussi réaliser cette opération avec la fonction NB.SI) :

=SOMMEPROD((A2:A31="Jean")*1)

On obtient ainsi : 8. Cet exemple n'est pas le meilleur pour montrer la puissance de la fonction, mais elle montre qu'on n'est pas obligé d'avoir beaucoup de paramètres compliqués.

Exemple 2 : compter le nombre de ventes supérieures à 600€ au mois de Janvier :

=SOMMEPROD((B2:B31="Janvier")*(C2:C31>600))

On obtient ainsi : 2. On peut ainsi combiner les conditions pour prendre les valeurs comprises entre 200 et 600 par exemple.

Exemple 3 : totaliser la somme accumulée grâce à Pierre aux mois de Janvier et Mars :

=SOMMEPROD((A2:A31="Pierre")*((B2:B31="Janvier")+(B2:B31="Mars"))*(C2:C31))

On obtient ainsi : 2760.

Pour synthétiser ce tableau, on peut créer ces deux tableaux :

Image utilisateur

Dans chaque cellule non grisée, on a des fonctions SOMMEPROD. Je vous laisse vous entraîner en essayant de reproduire ces tableaux. Si vous avez des questions, demandez-moi dans les commentaires ou par MP. Pour les cellules grisées, on peut utiliser la fonction SOMME tout simplement. Je propose, pour bien apprivoiser la fonction étudiée, de l'utiliser pour obtenir les mêmes résultats qu'avec la fonction SOMME. Vous en êtes largement capable, j'ai confiance en vous ;) .

Nous en avons fini avec la fonction SOMMEPROD et j'espère que vous avez compris. Elle est vraiment très puissante et utile pour synthétiser des tableaux comme on vient de le faire !

PI

Que permet-elle ?

Elle permet de renvoyer la valeur de pi.

Comment s'écrit-elle et quels paramètres ?

Elle s'écrit de la façon suivante mais ne demande aucun paramètre :

=PI()

Image utilisateur

Un exemple d'utilisation

On cherche à calculer le périmètre et l'aire de différents disques selon leur rayon :

Image utilisateur

RACINE

Que permet-elle ?

Elle permet de calculer la racine carrée d'un nombre ou d'une cellule.

Comment s'écrit-elle et quels paramètres ?

Elle ne prend qu'un paramètre, un nombre ou une cellule.

=RACINE(100)

=RACINE(E2)

Un exemple d'application

En course d'orientation, je dois aller du point A au point B. Je connais la distance à vol d'oiseau entre ces deux points. Par contre, le carré au centre ne me permet pas d'aller tout droit c'est une forêt de buisson. Je dois donc calculer la distance à parcourir en prenant le chemin (trait noir).

Image utilisateur

On utilise alors le fameux théorème de Pythagore qui nous dit que AB²+AC²=BC² lorsque le triangle est rectangle en A. Ici, nous avons un carré donc les deux segments sont de mêmes longueurs et 2x²=AB². Il faut alors résoudre cette petite équation. 2x étant la distance à parcourir. Voici la réponse grâce à Excel :

Image utilisateur

ARRONDI

Que permet-elle ?

Elle permet d'arrondir le résultat d'un quotient par exemple au nombre significatif que l'on veut.

Comment s'écrit-elle et quels paramètres ?

Elle prend deux paramètres, le chiffre à arrondir et le nombre de décimal à afficher. On l'écrit ainsi :

=ARRONDI(valeur;nombre_de_décimale)
=ARRONDI(100,029384;2)

On obtient alors la valeur 100,02. C'est très pratique au lieu de formater les cellules avec deux décimales avant de faire les calculs.

Un exemple théorique et un exemple concret

Pour vous montrer comment on utilise la fonction, on l'applique à des données aléatoires.

Image utilisateur

On vient de voir dans l'exemple que l'on peut appliquer des valeurs négatives. Vous avez sûrement deviné que ça permet d'arrondir avant la virgule et donc à la dizaine (pour -1) près ou à la centaine (pour -2) près.

Vous avez vraiment besoin d'un exemple concret pour cette fonction ? Allez, pour le fun et parce que je suis sympa, je vous en propose un. De plus la répétition permet l'apprentissage donc ça ne vous fera pas de mal ;) . J'aime bien la bouffe alors encore un exemple sur des courses :) .

Image utilisateur

ARRONDI.INF et ARRONDI.SUP

Que permettent-elles ?

Comme la fonction ARRONDI, elles permettent d'arrondir un chiffre selon un nombre de décimales ou, en utilisant les nombres négatifs, d'arrondir avant la virgule. Pour la fonction ARRONDI.INF on arrondit à l'inférieur alors qu'avec ARRONDI.SUP on arrondit au supérieur. On ne se préoccupe plus de savoir ce qui suit la partie tronquée.

Comment s'écrivent-elles et quels paramètres ?

De la même façon que la fonction ARRONDI. Elles prennent 2 paramètres, le nombre à arrondir et le nombre de décimales.

=ARRONDI.INF(valeur;nombre_de_décimale)
=ARRONDI.SUP(valeur;nombre_de_décimale)

Je ne vais pas vous en dire plus sur cette fonction puisque c'est la même chose que pour la fonction ARRONDI. Je ne peux m'empêcher de vous proposer un exemple quand même :

Image utilisateur

ALEA.ENTRE.BORNES

Que permet-elle ?

Elle permet de renvoyer un nombre entier aléatoire qui est situé entre deux bornes spécifiées par l'utilisateur (c'est à dire vous).

Comment s'écrit-elle et quels paramètres ?

Elle prend deux paramètres obligatoires, la borne minimale (la valeur sera supérieure ou égale à cet argument) et la borne maximale (la valeur sera supérieure ou égale à cet argument).

=ALEA.ENTRE.BORNES(borne_minimale;borne_maximale)

Avec des valeurs aléatoires, on a ceci :

=ALEA.ENTRE.BORNES(0;100)

Si vous entrez cette formule chez vous, vous n'obtenez jamais le même résultat. C'est pourquoi je ne vous donne pas ce que j'ai parce que ce n'est pas forcement la même que vous. Mais on peut aussi spécifier des cellules (lorsque l'on entre des valeurs dans les cellules au lieu de modifier la formule) comme ceci :

=ALEA.ENTRE.BORNES(E2;F2)

Un exemple que vous pouvez adapter

Je vous présente ici un exemple avec différentes bornes totalement aléatoires et vous n'aurez pas les mêmes valeurs que moi. D'ailleurs, si vous recopiez la formule avec les mêmes bornes, vous n'aurez pas la même valeur.

Image utilisateur

Une combinaison avec la fonction ARRONDI

Pour obtenir un nombre aléatoire parmi les dizaines de 0 à 100. On cherche à avoir 0, 10, 20, 30, 40, 50, 60, 70, 80, 90 ou 100. Comment faire ? En combinant la fonction ARRONDI et la fonction ALEA.ENTRE.BORNES! Voici la réponse :

=ARRONDI(ALEA.ENTRE.BORNES(0;100);-1)

Vous pouvez donc adapter cet exemple, mais aussi combiner d'autres fonctions entre elles !

Les auteurs