Tableur EXCEL/Formules Complexes
Bienvenue sur notre page, nous sommes le jeudi 21 novembre 2024
- 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
modifierPrésentation de la fonction SI
modifier- 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
modifier- 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
- 2ème étape: Insérez une fonction en cliquant sur f(x)
- 3ème étape: Entrez les paramètres suivants:
- 4ème étape: Faites un copier/coller de la formule dans les cellules C3:C8
Particularités de la fonction SI
modifier- 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 :
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.
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
modifierPrésentation de la fonction MOYENNE.SI
modifier- 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
modifier- 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)
La moyenne totale des étudiants poursuivant leurs études est de 12,6.
La fonction MOYENNE.SI.ENS
modifierPrésentation de la fonction MOYENNE.SI.ENS
modifier- 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
modifier- 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,
- 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.
La fonction SOMME.SI
modifierPrésentation de la fonction SOMME.SI
modifier- 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
modifier- 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])
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
modifier- 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
modifierPrésentation de la fonction SOMME.SI.ENS
modifier- 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
modifier- 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:
NB: L'utilisation des "$" permet de verrouiller les lignes ou les colonnes de notre choix
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
modifier- 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
modifierPrésentation de la fonction NB.SI
modifier- 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
modifier- 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)
Ici, nous pouvons voir qu’il y a eu 3 ventes à Paris
Particularités de la fonction NB.SI
modifier- 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
modifierPrésentation de la fonction NB.SI.ENS
modifier- 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
modifier- 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)
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
modifier- Les critères peuvent être eux-mêmes une formule
- Les plages ne doivent pas être nécessairement consécutives
La fonction SIERREUR
modifierPrésentation de la fonction SIERREUR
modifier- 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
modifier- 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) |