Fonctions SI, ET, NB.SI, OU et CONCATENER

La fonction SI s'écrit sous cette forme :

Code : Autre

1	=SI(condition;valeur si VRAI;valeur si FAUX)

Argument	Définition
Condition	Contrôle à effectuer
Valeur si VRAI	Opération à effectuer si le résultat du contrôle est VRAI
Valeur si FAUX	Opération à effectuer si le résultat du contrôle est FAUX

Définition	Symbole
égal à	=
inférieur à	<
inférieur ou égal à	<=
supérieur à	>
supérieur ou égal à	>=
différent de	<>

Prenez le tableau (classeur).
Vous voyez 3 colonnes :

• BON
• MAUVAIS-
• MAUVAIS+

Ces 3 colonnes vont nous permettre de déterminer si les résultats obtenus sont bons, pas assez nombreux ou trop, pour chaque élément.

Éléments	Tolérances
Calcium	70 à 130
Magnésium	3 à 4,2
Sodium	3 à 4,5
Potassium	1,5 à 2
Chlorures	3,2 à 5
Sulfates	46 à 72
Nitrates	< 2
Hydrogénocarbonates	214 à 324

Nous devons raisonner correctement.
Nous allons commencer par contrôler les conditions pour déterminer si c'est MAUVAIS-.
Nous avons inscrit nos valeurs dans la colonne C.

Pour le calcium, si les valeurs en C3 sont inférieures à 70 alors, elles sont "MAUVAIS-".

Oui, mais...
Exactement, j'ai oublié la "valeur si FAUX".

Dans notre cas, si c'est FAUX, on lui demandera de ne rien inscrire.

Dans les formules EXCEL, on écrit cela de cette manière :
Code : Autre

1

""

Le raisonnement final pour cette fonction sera donc :
si les valeurs en C3 sont inférieures à 70, alors elles sont "MAUVAIS-" et nous mettons une croix à cet endroit, sinon, n'affichons rien.

Dans la cellule G3 (MAUVAIS- / Calcium), nous allons donc mettre :

Code : Autre

Nous pouvons donc les adapter aux 7 autres éléments.

Ainsi, pour le magnésium en ligne 4, nous mettrons, en G4 :
Code : Autre

Il n'y a que la condition qui change. Elle est liée à l'élément à contrôler.

En G5, nous mettrons :
Code : Autre

En G6, nous mettrons :
Code : Autre

... ainsi de suite...

En G9, nous mettrons :
ici, nous n'avons pas de formule à mettre, car tout ce qui est inférieur à 2 est "bon".

En G10, nous mettrons :
Code : Autre

Nous venons de définir les conditions pour être "MAUVAIS-".
Nous allons définir les conditions pour être "MAUVAIS+".


Le maximum de la nominale, pour le calcium, est de 130 ; ainsi, tout ce qui est supérieur à 130 sera MAUVAIS+.

On reprend la même formule :
Code : Autre

Et on la modifie en fonction.

Ce qui donnera en H3 :
Code : Autre

1	=SI(C3>130;"X";"")

Faisons de même pour les autres cellules de la colonne "MAUVAIS+".

En H4, nous mettrons :
Code : Autre

... ainsi de suite...

En H10, nous mettrons :
Code : Autre


Désormais, si une des valeurs n'est pas comprise dans la nominale, une croix s'affichera dans une des colonnes "MAUVAIS".
Vous pouvez constater que le calcium est "MAUVAIS+".


Supprimez donc tous les chiffres de la colonne "Valeurs obtenues".




Eh oui !
C'est de notre faute, ça !
Pour définir les "MAUVAIS-", nous lui avons demandé de mettre une croix si les valeurs en colonne "Valeurs obtenues" étaient inférieures au minimum de la nominale.

Nous allons devoir ajouter une condition.

Pour pallier le problème de croix en colonne "MAUVAIS-", nous allons donc ajouter la fonction ET.
Celle-ci s'écrit sous cette forme :

Code : Autre

1	=ET(condition1;condition2;condition3;...)

Elle indiquera VRAI seulement si toutes les conditions sont VRAIES.

Nous devons donc reconsidérer la formule en G3 :
Code : Autre

1	=SI(C3<70;"X";"")

Nous dirons donc que SI C3 est inférieur à 70 ET C3 n'est pas vide alors nous mettons une croix, sinon, n'affichons rien.



Nous allons imbriquer la fonction ET dans la formule et nous l'écrirons de cette manière :
Citation : formulation


Ce qui donne :

Citation : formulation



Nous allons faire de même avec toutes les autres formule de la colonne "MAUVAIS-".



En G4, nous mettrons :
Code : Autre

En G5, nous mettrons :
Code : Autre

... ainsi de suite...

En G9, nous mettrons :
pas besoin de changer quoi que ce soit.

En G10, nous mettrons :
Code : Autre


Maintenant, votre tableau n'affiche plus de croix si les cellules de la colonne "Valeurs obtenues" sont vides.
Nous avons tous les éléments pour afficher ce qui est mauvais.
Nous pouvons donc passer à ce qui est "bon".

Nous devons définir ce qui est "BON"

Pour le calcium, par exemple, nous allons devoir admettre que la cellule C3 doit être renseignée et que les cellules G3 et H3 sont vides.
S'il y a une croix en G3 ou H3 : c'est mauvais.

En F3 nous pourrons donc mettre :
Code : Autre

1	=SI(ET(C3<>"";G3="";H3="");"X";"")

Ce qui signifie que SI C3 est différent de rien (est vide) ET G3 est vide ET H3 est vide, alors nous pouvons mettre une croix, sinon, n'affichons rien.


Faisons de même pour les autres cellules de la colonne "BON".


En F4, nous mettrons :
Code : Autre

En F5, nous mettrons :
Code : Autre

... ainsi de suite...

En G9, nous mettrons :
Code : Autre


En G10, nous mettrons :
Code : Autre




Je veux savoir combien il y a de bons résultats dans mon tableau.
Remplissons-le d'abord !


Mettez :

64 en C3
4,5 en C4
3 en C5
1,6 en C6
4 en C7
40 en C8
1,2 en C9
365 en C10


Si vous comptez 4 croix dans la colonne "BON", vous savez compter.
Mais on peut s'y prendre autrement.
J'ai spécialement préparé une cellule pour vous : D13.

Pour cela, nous allons voir la prochaine fonction de ce tuto.

La fonction NB.SI sert de compteur. Elle s'utilise avec une plage de données.

Elle s'écrit sous cette forme :

Code : Autre

1	=NB.SI(plage;condition)

La plage de données concernant les résultats bons est :
F3:F10
F3, F4, F5, F6, F7, F8, F9 et F10 seront donc prises en compte.

La condition est d'avoir une croix à l'intérieur des cellules, soit :
"X"

J'ai prévu une cellule pour comptabiliser les résultats bons.


On devrait donc mettre en D13 :
Code : Autre


Toutefois, vous devez commencer à prendre le pli de vous dire qu'EXCEL vous mettra un "0" si aucune cellule n'est renseignée.
Nous devons donc imbriquer cette fonction dans une autre contenant bien sûr SI et ET.

Nous devons donc dire que :
SI C3 est vide ET C4 est vide ET C5 est vide ET C6 est vide ET C7 est vide ET C8 est vide ET C9 est vide ET C10 est vide, alors n'affichons rien, sinon, comptons le nombre de croix dans la plage F3 à F10.

Cette formule s'écrit donc :

Code : Autre

1	=SI(ET(C3="";C4="";C5="";C6="";C7="";C8="";C9="";C10="");"";NB.SI(F3:F10;"X"))

Ainsi, dés que toutes les cellules de C3 à C10 sont renseignées, le résultat s'affichera.


Nous allons maintenant déterminer quand l'eau est mauvaise.

Pour déterminer si l'eau est bonne, nous devons admettre que le total de croix de F3 à F10 est égal à 8 (tous les éléments sont donc bons).
Simple à faire.

Il suffit de dire :
SI le nombre de croix ("X") de F3 à F10 est égal à 8, alors affichons "OUI", sinon, affichons "NON".

Dans ce cas-là, si nous n'obtenons pas "OUI", nous avons forcément 1 valeur mauvaise : donc, l'eau n'est pas bonne.

Il suffirait donc d'écrire en D17 :
Code : Autre

1	=SI(NB.SI(F3:F10;"X")=8;"OUI";"NON")

Selon les résultats, nous obtenons :
L'eau est bonne : OUI ou NON.
Malheureusement, il y a toujours cette fâcheuse valeur si FAUX qui vient s'afficher même si les cellules C3, C4, C5, C6, C7, C8, C9 et C10 ne sont pas renseignées.
On va donc devoir dire que SI toutes les cases ne sont pas renseignées, alors il ne faut rien afficher, sinon respecter la formule :
Code : Autre

1	=SI(NB.SI(F3:F10;"X")=8;"OUI";"NON")

Ainsi, nous allons écrire :
Code : Autre

1	=SI(ET(C3="";C4="";C5="";C6="";C7="";C8="";C9="";C10="");"";SI(NB.SI(F3:F10;"X")=8;"OUI";"NON"))

Comme nous avions déjà comptabilisé les croix dans la colonne "BON", nous reprendrons le résultat au lieu de réécrire la formule.
Au lieu de mettre :
Code : Autre
nous mettrons simplement :
Code : Autre

Nous obtenons donc, au final, en D17 :
Citation : formulation





J'aimerais plutôt me pencher sur une autre formulation possible.
J'aimerais une grosse phrase qui fasse un effet flash sur mon tableau pour m'avertir si elle est mauvaise.



Définissons l'eau mauvaise.
L'eau sera mauvaise SI le nombre de croix de G3 à G10 OU de H3 à H10 est supérieur à "0".

Voici donc cette fonction OU.

Elle s'écrit sous cette forme :
Code : Autre

1	=OU(condition1;condition2;condition3;...)

Si l'une des conditions est remplie, alors le résultat renvoie VRAI.




Bien sûr, nous devons considérer que les cellules C3 à C10 sont renseignées. Donc différentes de rien.

Nous devons donc imbriquer le ET, comme la formule précédente en changeant le = par <>.
Code : Autre



Nous devons donc poursuivre par :
SI le nombre de croix de G3 à G10 OU de H3 à H10 est supérieur à "0".

Ce qui donne :
Citation : formulation

Ajoutons ensuite la valeur si VRAI et la valeur si FAUX.
La valeur si les conditions sont vraies est :
"L'eau est mauvaise!"

La valeur si FAUX est :
"L'eau est bonne !"


Ce qui donne :
Citation : formulation

Bien entendu, si nous ne mettons pas que SI aucune cellule n'est renseignée de C3 à C10, alors il affichera que l'eau est bonne.
Nous devons donc inclure cette condition.



Ce qui donnera, en A21 :

Citation : formule regroupant plusieurs fonctions


Cela aura pour effet de ne rien afficher tant que toutes les cellules de C3 à C10 ne sont pas renseignées.

Cette fonction permet de reprendre des cellules, des formules et du texte afin de créer une phrase adaptée aux divers résultats.

Cette fonction s'écrit de la sorte :

Citation : formule

=CONCATENER( "texte", cellule ou formule ; "texte", cellule ou formule ; "texte", cellule ou formule ; ... )

On pourrait très écrire une formule qui afficherait :
La somme totale de tous les éléments représente "x" milligrammes par litre d'eau.

Pour obtenir la somme de tous les éléments de la plage C3 à C10, on a juste à écrire la formule :
Code : Autre


Afin d'afficher la phrase voulue, on écrirait, en A28 par exemple :
Code : Autre




Remplissez toutes les cellules de C3 à C10.
Mettez ce que vous voulez. Des valeurs bonnes ou mauvaises...


Vous voyez ce que permettent toutes les fonctions que l'on a vues dans ce tuto.

La fonction CONCATENER est très intéressante car elle permet de reprendre des résultats et de les mélanger à du texte bien formulé pour obtenir des résumés ou autres phrases du même genre.


On peut continuer notre fonction pour en faire un vrai petit résumé, et surtout la rectifier afin qu'elle n'affiche rien si tous les éléments ne sont pas renseignés.

Ma phrase sera :
La somme totale de tous les éléments représente "x" (est la somme des éléments de C3 à C10) milligrammes par litre d'eau.
Les valeurs bonnes sont au nombre de "y" (représente le résultat en D13).
"z" (représente le résultat en A21)

Cette phrase est donc le bilan simplifié de mon tableau.

On aura donc, en A28 :

Citation : formule

La définition de cette formule s'explique donc comme ça :
SI toutes les cellules de la fonction ET sont renseignées, alors affichons la phrase désirée avec la fonction CONCATENER, sinon, n'affichons rien.



Rien de tel qu'un tableau bien fini pour voir toutes les fonctions réagir en fonction des données introduites.
Vous pouvez vous amuser à mettre les valeurs que vous voulez dans la colonne "Valeurs obtenues" afin de voir réagir le tableau comme il le doit.

Voici le tableau fini pour ceux qui désirent le voir sans forcément suivre tout le tuto.
Je précise quand même aux novices qu'il est préférable de suivre le tuto du début à la fin pour bien comprendre les fonctions utilisées.


Tableau_de_base_fini.xls