Aller au contenu

Tableur EXCEL/Formules Complexes

Leçons de niveau 15
Une page de Wikiversité, la communauté pédagogique libre.
Début de la boite de navigation du chapitre
Formules Complexes
Icône de la faculté
Chapitre no 8
Leçon : Tableur EXCEL
Chap. préc. :Formules
Chap. suiv. :Formules Index Equiv
fin de la boite de navigation du chapitre
En raison de limitations techniques, la typographie souhaitable du titre, « Tableur EXCEL : Formules Complexes
Tableur EXCEL/Formules Complexes
 », n'a pu être restituée correctement ci-dessus.


Bonjour !
Bienvenue sur notre page, nous sommes le mardi 17 décembre 2024


La fonction SI

[modifier | modifier le wikicode]

Présentation de la fonction SI

[modifier | modifier le wikicode]
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


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
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
fonction SI 2 format PNG


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


SI 3 PNG
SI 3 PNG

Particularités de la fonction SI

[modifier | modifier le wikicode]
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
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 !


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

La fonction MOYENNE.SI

[modifier | modifier le wikicode]

Présentation de la fonction MOYENNE.SI

[modifier | modifier le wikicode]
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
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
MoyenneSI1PNG


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


MoyenneSI2PNG
MoyenneSI2PNG

La fonction MOYENNE.SI.ENS

[modifier | modifier le wikicode]

Présentation de la fonction MOYENNE.SI.ENS

[modifier | modifier le wikicode]
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


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
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
MoyenneSI4PNG

La fonction SOMME.SI

[modifier | modifier le wikicode]

Présentation de la fonction SOMME.SI

[modifier | modifier le wikicode]
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 | modifier le wikicode]
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. À 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
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

[modifier | modifier le wikicode]
  • 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

[modifier | modifier le wikicode]

Présentation de la fonction SOMME.SI.ENS

[modifier | modifier le wikicode]
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 | modifier le wikicode]
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. À 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
SommesiENS1 PNG


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

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

Particularités de la fonction SOMME.SI.ENS

[modifier | modifier le wikicode]
  • 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

[modifier | modifier le wikicode]

Présentation de la fonction NB.SI

[modifier | modifier le wikicode]
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
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
NbSI1PNG

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

Particularités de la fonction NB.SI

[modifier | modifier le wikicode]
  • 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

[modifier | modifier le wikicode]

Présentation de la fonction NB.SI.ENS

[modifier | modifier le wikicode]
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 | modifier le wikicode]
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
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 à Étampes !

Particularités de la fonction NB.SI.ENS

[modifier | modifier le wikicode]
  • Les critères peuvent être eux-mêmes une formule
  • Les plages ne doivent pas être nécessairement consécutives

La fonction SIERREUR

[modifier | modifier le wikicode]

Présentation de la fonction SIERREUR

[modifier | modifier le wikicode]
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 | modifier le wikicode]
SIerreur PNG
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