Ce tutoriel est la dernière partie sur les formules de calculs basiques d’Excel et est la suite de l’article sur les formules Somme, Soustraction, Multiplication, Division et Moyenne.
Pour vous entraîner, vous pouvez télécharger le fichier Excel mis à votre disposition dans l’Espace Membres avec les formules utilisées dans l’article précédent.
Utiliser la fonction SI :
Si je souhaite faire bénéficier de 15 % de remise mes clients dont le montant Total HT de leur commande est supérieur à 50 €, je vais utiliser la fonction Si. Je me place en I1 et je saisis comme titre Remise, je me place ensuite en I2 et je vais dans l’onglet Formules, puis, je clique sur Insérer une fonction
Dans la boite de dialogue qui s’affiche, à la ligne Test logique, je vais indiquer que ma condition sera que le Total HT est supérieur à 50 €, je saisis donc D2>50. Dans Valeur_si_vrai, je vais indiquer ce que je souhaite qu’Excel fasse si ma condition est respectée c’est-à-dire si mon Total HT est supérieur à 50 €. Dans ce cas, je souhaite attribuer une remise de 15 % du Total HT, je saisis donc D2*0,15 (puisque 15 % = 15/100). Enfin, dans Valeur_si_faux, j’indique ce que je souhaite qu’Excel affiche si la condition n’est pas respectée. Pour cet exemple, je souhaite afficher zéro donc je saisis 0. Par défaut, si je ne mets rien dans Valeur_si_faux, Excel affichera FAUX. Si je souhaite écrire une phrase, il faudra la mettre entre guillemets comme par exemple "Pas de remise". Lorsque ma formule est complète, je clique sur Ok.
Ma formule est donc =SI(D2>50;D2*0,15;0) et se lit SI D2 >50 ALORS D2*0,15 SINON 0.
Cette formule peut contenir plusieurs arguments.
Si je souhaite, par exemple, appliquer une remise de 15 % pour un Total HT de 50 € à 150 €, de 20 % de 150 € à 250 €, puis de 25 % au-delà, je vais modifier ma formule.
Voici un petit récapitulatif visuel :
- Moins de 50 € = 0 % de remise
- Moins de 150 € = 15 % de remise
- Moins de 250 € = 20 % de remise
- À partir de 250 € = 25 % de remise
Ma formule devra se présenter comme ceci (les couleurs correspondent aux arguments) :
=SI(D2<50;0;SI(D2<150;D2*0,15;SI(D2<250;D2*0,2;D2*0,25)))
Je peux bien sûr rajouter d’autres arguments en reprenant le même schéma. Il ne me reste plus qu’à recopier ma formule jusqu’à la cellule I21 comme vu lors de la première partie de ce tutoriel.
Utiliser la fonction SOMME.SI :
La fonction Somme.si permet d’additionner plusieurs cellules répondant à certains critères.
Pour mon exemple, je souhaiterais connaître le nombre de quantités commandées pour chaque produit. Je mets en titre Nombre de produits en J1. Je me place en J2 et je vais dans l’onglet Formules, puis, je clique sur Insérer une fonction
Dans la boîte de dialogue qui s’affiche, à la ligne Plage, j’appuie sur
Ma formule est =SOMME.SI(A2:A21;"Produit A";B2:B21).
J’ai bien 21 produits A vendus puisque j’ai 2 commandes avec respectivement 4 et 17 unités.
Pour effectuer cette opération une nouvelle fois, je ne vais pas simplement copier ma formule comme pour les opérations précédentes. Je dois d’abord bloquer les cellules afin que ma formule ne perde pas ces références. C’est-à-dire que si je copie ma formule précédente par un Ctrl + c puis Ctrl + v ou en glissant ma formule, j’aurais =SOMME.SI(A3:A22;"Produit A";B3:B22). Ce qui me fait perdre ma première ligne et me rajoute une ligne en dehors du tableau (vous pouvez imaginer le résultat si vous avez 100 lignes
Pour bloquer mes cellules, avant de la copier, je place le signe du dollar ($) devant chacune des références (chiffres et lettres), ce qui rend mes références absolues et non plus relatives. Ma nouvelle formule se présente maintenant de cette manière :
=SOMME.SI($A$2:$A$21;"Produit A";$B$2:$B$21)
Désormais, je peux copier ma formule, dans mon exemple, 8 fois pour les 8 produits différents. Ensuite, il ne me reste plus qu’à modifier uniquement la lettre du produit dans chacune des formules suivantes :
=SOMME.SI($A$2:$A$21;"Produit B";$B$2:$B$21)
=SOMME.SI($A$2:$A$21;"Produit C";$B$2:$B$21)
=SOMME.SI($A$2:$A$21;"Produit D";$B$2:$B$21)
=SOMME.SI($A$2:$A$21;"Produit E";$B$2:$B$21)
=SOMME.SI($A$2:$A$21;"Produit F";$B$2:$B$21)
=SOMME.SI($A$2:$A$21;"Produit G";$B$2:$B$21)
=SOMME.SI($A$2:$A$21;"Produit H";$B$2:$B$21)
Je peux vérifier si mes formules sont correctes en utilisant la Somme automatique pour ces formules de la cellule J2 à J9 et en l’utilisant également en B22 pour la plage de cellules de B2 à B21. Si les deux quantités sont identiques, les formules sont correctes.
Utiliser la fonction NB.SI :
À l’inverse de la fonction précédente (Somme.si), la fonction Nb.si compte le nombre de cellules, et non son contenu, répondant à un critère.
Dans cet exemple, à la différence du précédent où j’ai souhaité savoir combien d’unité de chaque produit avait été vendue, je souhaiterais maintenant connaître le nombre de commande pour chaque produit. En K1, je saisis le titre Commandes par produit.
Je me place donc en K2 et vais dans l’onglet Formules, puis, je clique sur Insérer une fonction
À la ligne de la Plage, je sélectionne les cellules "à compter", c’est-à-dire les cellules de A2 à A21, puis, à la ligne Critères, je saisis Produit A ou clique sur l’une des cellules comportant ce critère, comme par exemple, A6. Je clique enfin sur Ok.
Je vais bloquer de nouveau les cellules avec le signe du dollar donc =NB.SI(A2:A21;"Produit A") devient =NB.SI($A$2:$A$21;"Produit A"). Je copie ensuite cette formule par un Ctrl + c et Ctrl + v ou en faisant glisser la cellule. Je fais en sorte d’avoir là aussi 8 fois la formule et je modifie juste la lettre du produit dans les différentes opérations.
Pour vérifier cette formule, j’utilise la fonction Somme automatique pour faire la somme de ces résultats, puis, je compte le nombre de lignes que contient mon tableau en me plaçant sur la case n°2 de l’axe vertical, puis je glisse jusqu’à la case n°21. Sur mon curseur, j’ai bien 20L qui correspond à 20 lignes, qui est le même résultat que la Somme automatique.
Utiliser les fonctions MAX et MIN :
Les fonctions Max et Min permettent de connaître les valeurs maximales et minimales d’une plage de données. Je souhaite savoir quelle commande est la plus élevée. Je vais simplement mettre en L1 le titre Max / Min et je me place en L2. Je vais ensuite dans l’onglet Formules, puis, je clique sur Insérer une fonction
Par défaut, le logiciel sélectionne la plage de cellule G2 à K2 mais comme je souhaite connaître le montant de la commande la plus élevée, je clique sur
Mon résultat est bien 684,11 €.
À l’inverse, je souhaiterais maintenant connaître quelle est la commande la moins importante. Pour cela, je procède de la même manière, sauf que, dans la recherche des fonctions, je saisis "min", je clique sur Ok et double-clique sur la première formule de la liste (MIN) ou je la sélectionne et clique sur Ok.
De nouveau, la plage de cellules est sélectionnée horizontalement, je modifie donc la plage de F2 à F21 et je clique sur Ok.
Ma commande la moins élevée est d’un montant de 11,84 €.
Utiliser la fonction ARRONDI :
La fonction Arrondi permet, comme son nom l’indique, d’arrondir des nombres décimaux. Pour cela, nous pouvons modifier le Format des cellules dans l’onglet Accueil > Nombre. Puis dans la catégorie Nombre, Monétaire, Comptabilité ou Scientifique, on peut choisir un nombre de décimales. Cependant, en agissant de cette manière, si vous utilisez cette cellule dans une formule de calculs, Excel conservera le nombre d’origine.
C’est-à-dire que si j’ai une cellule contenant 9,99 € et que je change le format de cellules en lui attribuant 0 décimal, j’aurais à l’affichage 10 € mais si j’utilise cette cellule pour une formule de calcul, par exemple, ma cellule avec 10 € + 0,10 €, j’aurais toujours 10 € alors que si je rajoute 2 décimales j’aurais 10,09 €. Ce qui peut porter à confusion.
Pour utiliser la formule, je vais d’abord sélectionner mes cellules de F2 à F21 puis je vais dans l’onglet Accueil > Nombre et choisir 3 décimales pour que l’on puisse constater le résultat. Puis, j’insère une colonne entre les colonnes F et G en faisant un clic droit sur G puis Insertion
Je me place ensuite en G2 et je vais dans l’onglet Formules, puis, je clique sur Insérer une fonction
Dans la boîte de dialogue, à la ligne Nombre, je choisis la cellule F2 en cliquant sur
Je m’aperçois que ma cellule a récupéré mon format de cellule et j’ai donc 30,000 €. Avant de modifier le format, je vais recopier ma fonction en glissant ma cellule comme d’habitude. Maintenant, j’utilise la Somme automatique pour calculer les cellules de F2 à F21 et G2 à G21. Puis, je sélectionne les cellules de G2 à G22 (donc somme comprise) et je vais dans l’onglet Accueil > Nombre, je choisis le format Monétaire et 0 décimale. L’arrondi a bien fonctionné puisque sans arrondi j’avais 3 635,541 € et maintenant j’ai 3 637 €.
À savoir que, dans cette formule, l’arrondi inférieur est appliqué jusqu’à 4 et qu’à partir de 5 ce sera l’arrondi supérieur. Par exemple, 194,14 € deviendra 194,10 € alors que 194,15 € deviendra 194,20 €.
Pour modifier cette règle, il suffira d’utiliser la fonction ARRONDI.INF pour arrondir au chiffre inférieur et ARRONDI.SUP pour arrondir au chiffre supérieur.
Téléchargez "Utiliser les formules de calculs basiques sur Excel - Partie 2 - Si, somme.si, max, min, nb.si et arrondi" au format PDF
Je vous propose un fichier annexe dans l’Espace Membres contenant le fichier utilisé dans ce tutoriel avec un onglet comportant le tableau vierge et un autre avec les formules.
Jeu de briques
Snake
Pacman
Bubble