Tableur EXCEL/Formules Complexes

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Bonjour !
Bienvenue sur notre page, nous sommes le dimanche 24 novembre 2024

La Fonction SI et ses dérivées

La fonction SI est une fonction EXCEL des plus connues, une des plus simples mais également une des plus compliquées, c’est pour ça qu’il est intéressant de bien comprendre l’utilisation de la fonction SI et de ses dérivées.

Ces fonctions nous permettent d'effectuer des calculs sous conditions.

Dans ce chapitre, nous allons traiter la fonction SI et l’ensemble des dérivées plus ou moins connues.

La fonction SI

Présentation de la fonction SI

La fonction SI est une fonction logique d’Excel. Elle permet l’affichage d’une valeur en fonction des conditions imposées. Elle se présente sous la forme:

SI(Test_logique;Valeur_si_vrai;Valeur_si_faux)

Elle est composée de 3 paramètres obligatoires qui sont :

Paramètre	Définition
Test_logique	Ce paramètre reprend la valeur étudiée
Valeur_si_vrai	Ce paramètre désigne la valeur que doit afficher la fonction si le test de logique est vrai
Valeur_si_faux	Ce paramètre désigne la valeur que doit afficher la fonction si le test de logique est faux

Exemple

Vous souhaitez afficher en face de chaque élève le résultat de validation d’un examen en fonction de la note qu’il a obtenu. Dans ce cas, les paramètres à prendre en compte sont :

	Valeurs
Test_logique	La note inférieure à 10
Valeur_si_vrai	Si la note est inférieure à 10, l'élève va au rattrapage
Valeur_si_faux	Si la note n’est pas inférieure à 10, l'élève valide

• 1ère étape: Cliquez sur la cellule C2

SI 1PNG

• 2ème étape: Insérez une fonction en cliquant sur f(x)
• 3ème étape: Entrez les paramètres suivants:

fonction SI 2 format PNG

• 4ème étape: Faites un copier/coller de la formule dans les cellules C3:C8

SI 3 PNG

Particularités de la fonction SI

La fonction SI peut également être utilisée de manière imbriquée. Cela signifie qu’au sein d’une fonction SI il peut y en avoir plusieurs. Excel accepte de recevoir jusqu’à 64 fonctions SI imbriquées.

Dans notre exemple précédent, les SI imbriqués vont permettre de mettre en évidence des critères supplémentaires. Ainsi, nous allons pouvoir définir des mentions en fonction des notes attribués aux élèves.

Les nouveaux critères impliquent 5 fonctions SI imbriquées :

• Les notes supérieures à 16 : mention très bien
• Les notes comprises entre 14 et 16 : mention bien
• Les notes comprises entre 12 et 14 : mention assez bien
• Les notes comprises entre 10 et 12 : mention passable
• Les notes comprises entre 8 et 10 : rattrapage
• Les notes inférieures à 8 : doublement

Afin d’intégrer ses critères, il convient d’utiliser la formule ET qui permet d’associer plusieurs tests de logique pour la première donnée de la fonction SI. Pour intégrer le SI imbriqué, il faut inscrire au niveau de la « valeur_si_faux » la nouvelle fonction SI. Ainsi la formule va s’écrire en cellule C2 :

=SI(B2>=16;"Très bien";SI(ET(B2<16;B2>=14);"Bien";SI(ET(B2<14;B2>=12);"Assez bien";SI(ET(B2<12;B2>=10);"Passable";SI(ET(B2<10;B2>=8);"Rattrapage";"doublement")))))

SI 4 PNG

Il est possible d'écrire la formule sans la formule ET. En effet dans le premier test Note>=16, si c'est faux, il est inutile de dire dans le second test que la note est inférieure à 16, c'est évident. Donc juste Note >=14 suffit, ce qui permet de raccourcir la formule. et ainsi de suite pour tous les tests. Les résultats sont identiques.

=SI(B2>=16;"Très bien";SI(B2>=14;"Bien";SI(B2>=12;"Assez bien";SI(B2>=10;"Passable";SI(B2>=8;"Rattrapage";"doublement")))))

Cette variante permet d'économiser 40 caractères sur la longueur de la formule !

Egalement, la fonction SI est souvent combinée avec les fonctions ET() et OU() et leurs dérivées !

La fonction MOYENNE.SI

Présentation de la fonction MOYENNE.SI

La fonction moyenne.si permet de calculer la moyenne des valeurs d’une plage répondant à un critère.

Elle se présente sous la forme :

MOYENNE.SI(plage; critères; [plage_moyenne]

Ses 3 paramètres correspondent à :

Paramètre	Définition	Nécessité
Plage	Ce paramètre va chercher à calculer la moyenne des cellules sélectionnées	Indispensable
Critères	Ce paramètre représente la condition, sous forme de chiffre, d’expression, de référence de cellule ou de texte, qui détermine les cellules dont la moyenne est à calculer	Indispensable
Plage_moyenne	Ce paramètre représente l’ensemble des cellules dont la moyenne est à calculer sous la condition choisie. Si cet argument est omis, c’est alors l’argument Plage qui est utilisé	Facultatif

Exemple

Vous souhaitez afficher la note moyenne des élèves qui poursuivent leurs études. Pour cela les paramètres à prendre en compte sont :

	Définition	Valeurs
Plage	Sélectionner la plage reprenant le critère de poursuite d’études 0	cellules C2:C10
Critères	Il faut que la réponse affichée soit « oui »	"Oui"
Plage_moyenne	ce sont les notes que l’on veut prendre en compte pour le calcul de la moyenne	cellules D2:D10

• 1 ère étape Cliquez sur la cellule
• 2 ème étape Insérez une fonction en cliquant sur "f(x)"
• 3 ème étape Entrez les paramètres définis précédemment, afin d'obtenir la formule suivante : =MOYENNE.SI(C2:C10;"oui";D2:D10)

MoyenneSI1PNG

La moyenne totale des étudiants poursuivant leurs études est de 12,6.

MoyenneSI2PNG

La fonction MOYENNE.SI.ENS

Présentation de la fonction MOYENNE.SI.ENS

La fonction moyenne.si.ens permet de calculer la moyenne des valeurs d’une plage répondant à plusieurs critères.

Elle se présente sous la forme:

MOYENNE.SI.ENS (plage_moyenne; plage_critères1; critère1; [plage_critères2; critère2]; ...)

Paramètre	Définition	Nécessité
Plage_moyenne	Ce paramètre va chercher à calculer la moyenne des cellules sélectionnées	Indispensable
Plage_critères1	Ce paramètre représente la plage sous la condition choisie en critère 1	Indispensable
Plage_critères2...	Ce paramètre peut être une plage complémentaire associée à la condition du critère 2	Facultatif
Critères1	Ce paramètre représente la condition, sous forme de chiffre, d’expression, de référence de cellule ou de texte, qui détermine les cellules dont la moyenne est à calculer	Indispensable
Critères2...	Ce paramètre peut représenter une condition supplémentaire que l’on veut respecter pour calculer notre moyenne	Facultatif

Exemple

Vous souhaitez afficher le note moyenne des élèves ayant eu un BAC ES avec une note supérieure à 10

Voici notre tableau de données,

MoyenneSI3PNG

• 1ère étape Cliquez sur la cellule
• 2ème étape Insérez une fonction en cliquant sur "f(x)"
• 3ème étape Entrez les paramètres suivants afin d'obtenir la formule suivante : =MOYENNE.SI.ENS(E2:E10;B2:B10;"ES";E2:E10;">10";D2:D10;">10") comme ci-dessus

	Définitions	Valeurs
Plage_moyenne	Il faut sélectionner la plage des notes que l’on veut prendre en compte pour le calcul de la moyenne	E2:E10
Plage_critères1	Le premier paramètre à prendre en compte c’est le type de BAC, il faut donc sélectionner la plage du type de BAC	B2:B10
Plage_critères2	Le second paramètre à prendre en compte est la note 1 obtenue au BAC	E2:E10
Plage_critères3	Le troisième paramètre à prendre en compte est la note 2 obtenue au BAC	D2:D10
Critères1	La moyenne doit se calculer uniquement pour les BAC ES	ES
Critères2	La moyenne doit comprendre uniquement les notes 1 supérieures à 10	>10
Critères3	La moyenne doit comprendre uniquement les notes 2 supérieures à 10	>10

Ainsi, nous observons que la moyenne des étudiants ayant obtenu un BAC ES et plus de 10 aux notes 1 et 2, est de 14.

MoyenneSI4PNG

La fonction SOMME.SI

Présentation de la fonction SOMME.SI

Cette fonction permet d’additionner des valeurs répondant à un critère dans une plage.

Elle se présente sous la syntaxe suivante:

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

Paramètre	Définition	Nécessité
Plage_critères	Ce paramètre correspond à la plage de cellules où le critère sera recherché	Indispensable
Critères	Ce paramètre peut être exprimé sous forme de nombre, d’expression ou de référence de cellule. Il peut aussi être un caractère générique comme le « ? » ou « * ». Ces caractères font référence à un critère ou une chaine de critères quelconque	Indispensable
somme_plage	Ce paramètre additionne toutes les cellules correspondantes au critère dans la plage sélectionnée	Facultatif

Exemple SOMME.SI

Dans cet exemple, nous cherchons à déterminer le nombre de produits vendus. Pour obtenir le nombre de chaises vendues

	Valeurs
Plage_critère	Ce sont les cellules C2:C9 sous le nom "article" qui représentent les articles dans le tableau défini. La formule cherche dans cette plage le critère défini.
Critère	Le critère ici est une référence de cellule qui contient le nom de l’article dont on cherche à avoir la vente. C’est donc le mot chaise que la formule va chercher dans C2:C9.
somme_plage	Ce sont les cellules D2 :D9 sous le nom "quantité achetée" dans le tableau défini. A chaque fois que le mot chaise est inscrit dans C2:C9, la formule additionne toutes les quantités correspondantes.

D'après la syntaxe de la formule et ne reprenant nos paramètres définis ci-dessus, nous rentrons : =SOMME.SI(Tableau1[Article];F2;Tableau1[Quantité achetée])

SommeSI1 PNG

Selon notre exemple, nous obtenons que la quantité totale de chaises vendues est de 6 + 4 + 8 = 18

Particularités de la fonction SOMME.SI

• Si l’argument plage somme n’est pas renseigné alors Excel additionne les cellules de la plage de critère.
• La chaine de caractères a une taille maximale de 255.
• La plage_critère1 et la somme_plage peuvent avoir une taille différente.
• Les critères textes sont obligatoirement exprimés entre guillemets.

La fonction SOMME.SI.ENS

Présentation de la fonction SOMME.SI.ENS

Cette fonction permet d’additionner des valeurs répondant à plusieurs critères dans une plage.

Elle se présente sous la forme:

SOMME.SI.ENS(somme_plage;plage_critères;critères1;[plage_critères2;critères2];…)

Elle est composée de 3 paramètres obligatoires, et d'autres facultatifs:

Paramètre	Définition	Nécessité
somme_plage	Ce paramètre va chercher à calculer la somme des cellules sélectionnées	Indispensable
Plage_critères1	Ce paramètre représente la plage sous la condition choisie en critère 1	Indispensable
Plage_critères2...	Ce paramètre peut être une plage complémentaire associée à la condition du critère 2	Facultatif
Critères1	Ce paramètre représente la condition, sous forme de chiffre, d’expression, de référence de cellule ou de texte, qui détermine les cellules dont la moyenne est à calculer	Indispensable
Critères2...	Ce paramètre peut représenter une condition supplémentaire que l’on veut respecter pour calculer notre somme	Facultatif

Exemple SOMME.SI.ENS

Dans cet exemple, nous souhaitons savoir combien d’articles ont été vendus par ville. Il y a donc deux critères à respecter. Ces derniers sont des références de cellule.

	Définitions
somme_plage	Ce sont les cellules D2 :D9, représentant la quantité achetée. A chaque fois que les deux critères sont respectés, la formule additionne toutes les quantités correspondantes
Plage_critères1	Ce sont les cellules B2 :B9 qui contiennent la ville où a eu lieu la vente
Plage_critères2 ...	Ce sont les cellules C2 :C9 qui contiennent le produit vendu dans cette ville
Critères1	C’est une référence de cellule qui contient le nom de la ville. Ici ce sont les entêtes du tableau. La formule cherche dans B2 :B9 le nom de la ville
Critères2 ...	C’est une référence de cellule qui contient le nom de l'article. Ici ce sont les lignes du tableau. La formule cherche dans C2 :C9 le nom de l'article.

Afin de répondre à la question, entrons maintenant la formule avec les paramètres ci-dessus:

=SOMME.SI.ENS(Tableau1[Quantité achetée];Tableau1[Ville];G$1;Tableau1[Article];$F2)

NB: L'utilisation des "$" permet de verrouiller les lignes ou les colonnes de notre choix

SommesiENS1 PNG

D'après cet exemple, la quantité totale de chaises vendues à Evry est égale à 6

L'addition n'a lieu que si les deux critères sont strictement respectés

Particularités de la fonction SOMME.SI.ENS

• Le nombre de critères maximum est de 127 (N = 127)
• Les critères textes sont obligatoirement exprimés entre guillemets

La fonction NB.SI

Présentation de la fonction NB.SI

Cette fonction sert à compter le nombre de cellules qui contiennent un critère défini. Elle s'écrit sous la forme:

NB.SI(plage;critère)

Elle se compose de 2 paramètres obligatoires:

Paramètre	Définition	Nécessité
Plage	Ce paramètre correspond à la plage de cellules où le critère sera recherché	Indispensable
Critère	Ce paramètre peut être exprimé sous forme de nombre, d’expression ou de référence de cellule. Il peut aussi être un caractère générique comme le « ? » ou « * ». Ces caractères font référence à un critère ou une chaine de critères quelconque	Indispensable

Exemple NB.SI

Dans cet exemple, nous souhaitons savoir combien de fois des ventes ont eu lieu dans une ville. Ce qui revient à compter le nombre de fois que la ville apparaît dans le tableau

	Définitions
plage	Ce sont les cellules B2 :B9 représentant les différentes villes. Ce sont elles qui seront comptées si elles respectent le critère.
Critère	C’est une référence de cellule qui correspond au nom de la ville.

Ainsi, en rentrant la formule avec les paramètres définis: =NB.SI(Tableau1[Ville];F2)

NbSI1PNG

Ici, nous pouvons voir qu’il y a eu 3 ventes à Paris

Particularités de la fonction NB.SI

• Le critère peut être lui-même une formule
• Les plages ne doivent pas être nécessairement consécutives.

La fonction NB.SI.ENS

Présentation de la fonction NB.SI.ENS

Cette fonction sert à compter le nombre de cellules qui contiennent des critères définis.

Elle se définit ainsi:

NB.SI.ENS(plage_critères1;critères1;[plage_critères2;critères2];...)

Paramètre	Définition	Nécessité
Plage_critères1	Ce paramètre représente la plage sous la condition choisie en critère 1	Indispensable
Plage_critères2...	Ce paramètre peut être une plage complémentaire associée à la condition du critère 2	Facultatif
Critères1	Ce paramètre représente la condition, sous forme de chiffre, d’expression, de référence de cellule ou de texte, qui détermine les cellules dont la moyenne est à calculer	Indispensable
Critères2...	Ce paramètre peut représenter une condition supplémentaire que l’on veut respecter pour calculer notre somme	Facultatif

Exemple NB.SI.ENS

Dans cet exemple, nous souhaitons savoir combien fois ont été vendus des produits dans une ville. Cela revient à compter le nombre de fois où la combinaison ville-produit apparaît dans le tableau.

	Définitions
Plage_critères1	Ce sont les cellules B2 :B9 correspondant aux différentes villes. Ce sont elles qui seront comptées si elles respectent le critère.
Plage_critères2 ...	Ce sont les cellules C2 :C9 représentant les articles. Ce sont elles qui seront comptées si elles respectent le critère.
Critères1	C’est une référence de cellule qui correspond au nom de la ville
Critères2 ...	C’est une référence de cellule qui correspond au nom de l'article

La formule à insérer dans la cellule pour les différentes villes est donc : =NB.SI.ENS(Tableau1[Ville];G$1;Tableau1[Article];$F2)

NbsiENS1PNG

Selon nos résultats, nous pouvons déduire qu'aucune table n'a été vendue à Evry ou bien qu'un seul lit a été vendu à Etampes !

Particularités de la fonction NB.SI.ENS

• Les critères peuvent être eux-mêmes une formule
• Les plages ne doivent pas être nécessairement consécutives

La fonction SIERREUR

Présentation de la fonction SIERREUR

La fonction renvoit à une valeur spécifiée si une formule contient une erreur sinon elle indique le résultat de la formule. Elle permet donc de détecter et de traiter les erreurs.

Elle s'écrit:

SIERREUR(Valeur;Valeur_si_erreur)

Elle se compose de 2 paramètres obligatoires:

Paramètre	Définition	Nécessité
Valeur	C’est la formule ou l’argument vérifié.	Indispensable
Valeur_si_erreur	C’est la valeur à afficher si la formule contient une erreur. Elle peut être exprimée sous forme de nombre, d’expression ou de référence de cellule	Indispensable

Exemple SIERREUR

SIerreur PNG

Dans cet exemple, nous souhaitons afficher l’expression « Pas de table basse vendue cette année » lorsque la fonction RechercheV ne retrouve pas la valeur cherchée « table basse » qui se situe dans la cellule F2.

Pour cela il faut entrer la formule suivante : SIERREUR, en prenant en compte les paramètres suivants :

	Définitions
Valeur	RECHERCHEV(F2;C:C;1;Faux)
Critère	"Pas de table basse vendue cette année"

De cette manière nous avons immédiatement le résultat de la recherche et l’interprétation de l’erreur s’il y en a une.

Pour tester ses connaissances → QCM Fonction SI et ses dérivées
Pour s'entraîner avec des exercices → Exercice Fonction SI et ses dérivées

BBH2M (discussion)

Association Wikimédia France