Aller au menu - Aller au contenu

Icône Fonctions SI, ET, NB.SI, OU et CONCATENER

Par Trickus
Mise à jour : 10/06/2009
Difficulté : Facile Facile
1 091 visites depuis 7 jours, classé 117/786
Salut à tous !


Pour ceux qui débutent avec EXCEL, je vous suggère d'aller, au plus vite, prendre connaissance du tutoriel sur "Les bases d'EXCEL" fait par pablounet et tarzoune.
Il vous permettra d'avoir le minimum de connaissances requises pour suivre ce tuto.


Ici, je vous ferai travailler sur un tableau d'analyse de l'eau.
En fonction des résultats d'analyse, le tableau décidera si l'eau est conforme ou non.

Il vous faudra raisonner comme EXCEL et je vous aiderai à aller dans ce sens.

Sujet


Nous avons un appareil permettant de quantifier certains éléments contenus dans l'eau.
Après mesures, nous obtenons des résultats en "mg/l". Ceux-ci sont à relever et à inscrire dans un tableau.
Nous allons donc déterminer le taux de sodium, de calcium, de potassium... contenus dans l'eau.
Le tableau se chargera du reste.

Je vous fournis le tableau sur lequel nous allons travailler.
Tableau_de_base_fonctions.xls
Retour en haut
Sommaire du tutoriel :
Icône du chapitre
Retour en haut

La fonction SI

La fonction SI s'écrit sous cette forme :

Code : Autre
1

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





ArgumentDé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



Voici un tableau des symboles utilisés dans les formules

DéfinitionSymbole
é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+

BON = BON
MAUVAIS- = sous le minimum toléré
MAUVAIS+ = au-dessus du maximum toléré


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.

Je vous remets le tableau avec les éléments et les valeurs bonnes (nominales).

ÉlémentsTolé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
1

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


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

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

=SI(C4<3;"X";"")


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

En G5, nous mettrons :
Code : Autre
1

=SI(C5<3;"X";"")


En G6, nous mettrons :
Code : Autre
1

=SI(C6<1,5;"X";"")


... 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
1

=SI(C10<214;"X";"")


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
1

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


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
1

=SI(C4>4,2;"X";"")


... ainsi de suite...

En H10, nous mettrons :
Code : Autre
1

=SI(C10>324;"X";"")



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".


Mais... Euh... ? Pourquoi des croix apparaissent partout en "MAUVAIS-" ?


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.


Voici ce que vous devez avoir :
Tableau_de_base_fonctions_si.xls
Retour en haut

La fonction ET

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.

Si C3 n'est pas vide, nous devons plutôt dire que la cellule C3 est différente de "rien".


Nous allons imbriquer la fonction ET dans la formule et nous l'écrirons de cette manière :
Citation : formulation
=SI(ET(condition1;condition2);"X";"")



Ce qui donne :

Citation : formulation
=SI(ET(C3<70;C3<>"");"X";"")


Notez bien l'écriture de C3 différent de rien.Code : Autre
1

C3<>""


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



En G4, nous mettrons :
Code : Autre
1

=SI(ET(C4<3;C4<>"");"X";"")


En G5, nous mettrons :
Code : Autre
1

=SI(ET(C5<3;C5<>"");"X";"")


... ainsi de suite...

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

En G10, nous mettrons :
Code : Autre
1

=SI(ET(C10<214;c10<>"");"X";"")



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
1

=SI(ET(C4<>"";G4="";H4="");"X";"")


En F5, nous mettrons :
Code : Autre
1

=SI(ET(C5<>"";G5="";H5="");"X";"")


... ainsi de suite...

En G9, nous mettrons :
Code : Autre
1

=SI(ET(C9<>"";H9="");"X";"")

Notez bien que je n'ai pas de condition en G9.


En G10, nous mettrons :
Code : Autre
1

=SI(ET(C10<>"";G10="";H10="");"X";"")





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. :lol:
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.

Voici ce que vous devez avoir :
Tableau_de_base_fonctions_et.xls
Retour en haut

La fonction NB.SI

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)




Toutes les cellules de la colonne plage seront comprises dans la formule.


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
1

=NB.SI(F3:F10;"X")


Cette formule a pour effet de compter combien de fois il y a une croix dans la plage F3 à F10 (soit dans toute la colonne "BON")...

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.

Voici ce que vous devez avoir :
Tableau_de_base_fonctions_nb_si.xls
Retour en haut

La fonction OU

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
1

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

nous mettrons simplement :
Code : Autre
1

SI(D13=8;"OUI";"NON")


Nous obtenons donc, au final, en D17 :
Citation : formulation
=SI(ET(C3="";C4="";C5="";C6="";C7="";C8="";C9="";C10="");"";SI(D13=8;"OUI";"NON"))






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
1

=SI(ET(C3<>"";C4<>"";C5<>"";C6<>"";C7<>"";C8<>"";C9<>"";C10<>"";


Nous avons défini notre première condition : si toutes les cellules de C3 à C10 sont renseignées.


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
=SI(ET(C3<>"";C4<>"";C5<>"";C6<>"";C7<>"";C8<>"";C9<>"";C10<>"";OU(NB.SI(G3:G10;"X")>0;NB.SI(H3:H10;"X")>0))


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
=SI(ET(C3<>"";C4<>"";C5<>"";C6<>"";C7<>"";C8<>"";C9<>"";C10<>"";OU(NB.SI(G3:G10;"X")>0;NB.SI(H3:H10;"X")>0));"L'eau est mauvaise !";"L'eau est bonne !")


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.

Notre définition finale sera donc :
SI toutes les cellules de la colonne "Valeurs obtenues" sont différentes de rien ET que le nombre de croix de G3 à G10 est égal à zéro OU que le nombre de croix de H3 à H10 est égal à zéro, alors nous affichons "L'eau est mauvaise !", sinon, SI toutes les cellules de C3 à C10 sont différentes de rien ET que le nombre de croix de F3 à F10 est égal à 8, alors nous affichons "L'eau est bonne !", sinon, n'affichons rien.


Ce qui donnera, en A21 :

Citation : formule regroupant plusieurs fonctions
=SI(ET(C3<>"";C4<>"";C5<>"";C6<>"";C7<>"";C8<>"";C9<>"";C10<>"";OU(NB.SI(G3:G10;"X")>0;NB.SI(H3:H10;"X")>0));"L'eau est mauvaise !";SI(ET(C3<>"";C4<>"";C5<>"";C6<>"";C7<>"";C8<>"";C9<>"";C10<>"";NB.SI(F3:F10;"X")=8);"L'eau est bonne !";""))



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

Voici ce que vous devez avoir :
Tableau_de_base_fonctions_ou.xls
Retour en haut

La fonction CONCATENER

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 ; ... )



Il ne faut pas oublier de mettre des espaces dans les guillemets afin que le texte final soit lisible.



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
1

=SOMME(C3:C10)



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

=CONCATENER("La somme totale de tous les éléments représente ";SOMME(C3:C10);" milligrammes par litre d'eau.")


Regardez bien les espaces placés dans la formule, au niveau des guillemets. Ils représentent l'espace nécessaire pour séparer le texte du résultat de la somme.



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
=SI(ET(C3<>"";C4<>"";C5<>"";C6<>"";C7<>"";C8<>"";C9<>"";C10<>"";D13<>"";A21<>"");CONCATENER("La somme totale de tous les éléments représente ";SOMME(C3:D10);" milligrammes par litre d'eau. Les valeurs bonnes sont au nombre de ";D13;". ";A21);"")


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.

Vous devez remarquer, ici, le point ajouté entre les deux guillemets après ;D13; et l'espace ajouté après ce point dans ces mêmes guillemets.


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
Retour en haut
Ce tableau permet de voir l'utilité qu'on peut avoir de certaines formules.

La fonction SI permet de procéder à une opération en fonction d'une condition contrôlée vraie ou fausse.
La fonction ET permet de procéder à une opération en fonction de toutes les conditions.
La fonction NB.SI sert à compter le nombre de fois où 1 critère apparaît dans une plage de données.
La fonction OU permet de procéder à une opération si une condition est vraie sur plusieurs conditions ciblées.
La fonction CONCATENER permet de faire appel à des cellules, des formules et du texte et de mêler le tout dans une seule cellule pour en faire une phrase correctement tournée en fonction des résultats divers.


J'espère avoir aidé certains à comprendre l'importance de certaines fonctions dans certains cas bien précis.
Retour en haut

Partager

Retour en haut
14 commentaires pour "Fonctions SI, ET, NB.SI, OU et CONCATENER"
Note moyenne : 2.00 / 4 (3 votes)
Pseudo Commentaire
Hors ligne Trickus # Posté le 09/05/2010 à 09:09:14
Autorisation parentale
Groupe : Anciens

Merci, je regarderai à en changer la forme.
3D 2Y
zingwai vaincra ! ;)

Calendrier annuel français (à vie) fait avec EXCEL 2002
Il n'est valable qu'avec les jours fériés actuels !
 
Hors ligne Tartemine # Posté le 13/06/2010 à 20:00:03

Bonjour,

Je viens de réussir quelque chose que je n'arrivais pas à faire dans la fonction Concatener : ajouter des retours à la ligne.
Alors comme je suis contente, je le partage avec vous.

La formule classique : =CONCATENATE(D2;" ";C2;" ici on fait alt+retour
";F2;"ici on fait alt+retour
";
H2;" - ";I2)
Hors ligne Trickus # Posté le 09/10/2010 à 08:50:55
Autorisation parentale
Groupe : Anciens

Tartemine, il existe une fonction pour ça.

car(10)



Citation : car(10)
="ici et" & car(10) & "là"

donnera

Citation : résultat
ici et
là



Je n'oublierai pas de l'expliquer dans la mise à jour des tutos.
3D 2Y
zingwai vaincra ! ;)

Calendrier annuel français (à vie) fait avec EXCEL 2002
Il n'est valable qu'avec les jours fériés actuels !
 
Hors ligne ProvensAzur # Posté le 06/06/2011 à 00:54:42

Avis : Très bon

Bon tuto sur ces fonctions élémentaires. Simple, concis, rien à dire.
Hors ligne Lumière de Lune # Posté le 04/04/2012 à 00:26:40

Le tuto présente bien les fonctions, mais il ne montre pas comment utiliser au mieux excel.
En passant sur le fait qu'il aurait mieux valu mettre les "plages" dans deux colonnes, ce qui permettait une formule unique par colonne, on peut faire beaucoup beaucoup plus simple, en utilisant les opérations logiques, et les formattages de nombres.

Excel donne la possibilité de personnaliser les affichages des nombres en différenciant valeur positive ( correspond à Vrai ), valeurs négatives ( correspond à faux ) et valeur égale à zéro.

Donc on peut faire une formule simple, dans chaque colonne :
Pour la colonne "Bon" : =SI(ESTNUM(C3);(SI(ET(C3>=70;C3<=100);1;0));-100) avec comme format d'affichage "x";"!!!";
Pour la colonne "Mauvais-" et Mauvais+" : =SI(ESTNUM(C3);SI(C3<70;1;0);-100) et =SI(ESTNUM(C3);SI(C3>130;1;0);-100) avec le même format d'affichage

Ensuite, il suffit de faire un simple test sur la somme de la colonne Bon
- si on veut utiliser nb.si ça sera =SI(NB.SI(F3:F10;1)=8;1;SI(NB.SI(F3:F10;-100)>0;0;-1))
- sinon, plus simple =SI(somme(F3:F10) = 8 ; 1; SI ( somme(F3:F10) < 0 ; 0 ; -1 ))
dans les deux cas avec un format d'affichage "L'eau est bonne";"L'eau est mauvaise";"Pas assez d'informations"

Avantage :
- les formules sont nettement plus courtes, nettement plus simples à corriger
- on peut définir des moyennes par ligne pour arriver à une estimation plus "fine" d'une eau bonne ou mauvaise (dans la vraie vie)

Si rien de ce qui n'est dit dans le tuto est faux, je trouve que c'est quand même une solution hyper compliquée, et qui oriente des novices vers des mauvaises pratiques.

Voir tous les commentaires
Ce tutoriel a été corrigé par les zCorrecteurs.