Préparez votre certification
Microsoft Office Specialist

Les fonctions conditionnelles

Recevez ce cours au format PDF en envoyant un message ici

Le fichier de travail est téléchargeable ici. Le classeur contient la feuille : VentesSemaine1Janvier, et la feuille Facture sur lesquelles vous allez effectuer les différentes manipulations.

La Fonction SI (IF)

La fonction SI est la base de toutes les fonctions conditionnelles. Elle permet d'évaluer une expression logique puis de renvoyer une valeur A si l'expression est vraie ou bien une valeur B si l'expression est fausse.

La fonction SI simple

Dans sa syntaxe la plus simple, la fonction SI a besoin d'un argument obligatoire qui est la condition à vérifier, et deux arguments facultatifs à renvoyer (l'un ou l'autre).

Le premier argument Condition qui est obligatoire permet de comparer deux valeurs entre-elles, deux références de cellules ou bien une valeur avec une référence de cellule, et suite à cette comparaison il renvoie soit Vrai (True), soit Faux (False) ; les opérateurs de comparaison étant :

• Égal =
• Supérieur >
• Inférieur <
• Supérieur ou égal > =
• Inférieur ou égal < =
• Différent < >

Ce premier argument peut aussi être le résultat d'une fonction logique ou d'une formule logique.

Le deuxième argument (facultatif) est la valeur à renvoyer si la condition est vraie. Cette valeur peut être un nombre, un texte qu'on doit mettre entre guillemets ou bien la référence d'une cellule.

Le troisième argument (lui aussi facultatif) est la valeur à renvoyer si la condition est fausse ; de même cette valeur peut être un nombre, un texte qu'on doit mettre entre guillemets ou bien la référence d'une cellule.

Appliquons ce que l'on vient de voir sur la feuille CommandesSemaine1Janvier du classeur téléchargé, pour remplir la colonne Demande de la façon suivante : une commande est considérée comme Bonne si la quantité commandée est supérieure à 30, sinon, elle est considérée comme Moyenne.

Pour la première commande (ligne 2) la quantité se trouve dans la cellule D2. On va donc vérifier si celle-ci est supérieure strictement à 30. Par conséquent, l'écriture de la condition va être sous la forme suivante : B2>30.

Si cette condition est vraie, la valeur à retourner est Bonne, c'est un texte on doit donc le mettre entre guillemets, ce qui donne "Bonne" ; dans le cas contraire, la valeur à retourner est le texte "Moyenne".

L'expression à écrire dans la cellule F2 sera donc :

La valeur de la cellule D2 étant 10, la réponse à la question Est-ce que D2>30 ? est Faux (Non), automatiquement le retour de la fonction sera le troisième argument, c'est à dire "Moyenne".

Pour recopier la formule vers les cellules d'en bas, il suffit de double-cliquer sur le petit carré en bas à droite de la cellule F2, ce qui donne le résultat affiché ci-dessous.

Cette expression, on peut la saisir à la main comme le montre l'animation suivante :

... ou bien en utilisant l'assistant

La fonction SI imbriquée

Corsons un peu la chose. Nous voudrions afficher trois étiquettes : Bonne pour une commande supérieure strictement à 30, Moyenne pour une commande supérieure strictement à 20 et Faible pour une commande inférieure ou égale à 20.

On va se poser une première question : Est-ce que la commande est supérieure strictement à 30 ? Si la réponse est Vrai, alors on écrit Bonne et c'est fini. Si la réponse est Faux alors on se pose une deuxième question : Est-ce que la commande est supérieure strictement à 20 ? Si la réponse est Vrai alors on écrit Moyenne, sinon on écrit Faible.

Pour le premier article la quantité de la commande se trouve dans la cellule D2, la traduction de cette phrase en expression informatique donne l'écriture suivante qu'on va mettre dans la cellule F2.

On remarque ici qu'on a une fonction SI qui englobe une autre, on parle de Fonctions SI imbriquées. Cette écriture qui a l'air complexe est en fait facile à comprendre, il suffit de la lire avec un peu de logique. On peut la saisir au clavier ou bien utiliser l'assistant.

Dans l'exemple ci-dessus, on a deux conditions avec deux fonctions SI imbriquées, mais rien n'empêche, en cas de besoin, de rajouter d'autres fonctions, ce qui donnera une expression plus compliquée avec beaucoup de parenthèses.

La fonction SI.CONDITIONS

Excel 2019 (et la version 365) propose parmi ses nouveautés, la fonction SI.CONDITIONS qui est une alternative pour la complexité de la fonction SI imbriquée.

La syntaxe de la fonction SI.CONDITIONS est assez simple puisqu'on met entre parenthèses la suite des conditions suivies chacune de sa valeur correspondante avec des points-virgules. Le dernier argument VRAI détermine la valeur par défaut c'est à dire la valeur à renvoyer dans le cas où aucune des conditions n'est vraie.

La fonction SI imbriquée utilisée dans le paragraphe précédent peut être remplacée par la nouvelle fonction comme le montre la capture suivante :

Cette expression est à écrire manuellement ou bien via l'assistant.

La fonction NB.SI (COUNTIF)

La fonction NB.SI est une association entre la fonction NB et la fonction SI, avec deux avantages :

• tout d'abord elle compte le nombre des valeurs numériques et des valeurs textes (la fonction NB, elle, ne prend en considération que les valeurs numériques)
• le deuxième avantage c'est qu'elle ne comptabilise que les valeurs qui respectent une certaine condition

La fonction requière deux paramètres comme le montre la capture qui suit :

1. Plage : C'est la plage de cellules dans laquelle on veut compter les valeurs qui respectent le critère
2. Critère : C'est le critère à respecter pour être comptabilisé

On va utiliser cette fonction pour remplir le tableau récapitulatif de la feuille CommandesSemaine1Janvier, pour voir le nombre de commandes qui ont été faites pour chaque article. On va insérer la fonction dans la cellule I2, la plage de référence étant B2:B21 et le critère se trouve dans la cellule H2.

Cliquer droit sur l'animation puis Afficher l'image pour agrandir

La fonction SOMME.SI (SUMIF)

La fonction SOMME.SI est une fusion entre la fonction SOMME qui effectue l'addition d'une série de données numériques, et la fonction SI qui vérifie un test logique puis prend une décision selon le résultat de cette vérification. Ainsi, la fonction permet de faire la somme de valeurs numériques qui répondent à un certain critère.

La syntaxe de la fonction SOMME.SI comprend deux paramètres obligatoires et un paramètre facultatif.

• Plage : Paramètre obligatoire, elle définit la plage de cellules à additionner selon un critère défini. Cette plage doit contenir des valeurs numériques, les valeurs texte sont ignorées.
• Critère : Paramètre obligatoire, c'est lui qui définit les valeurs à additionner. Le critère peut être une expression, un nombre ou une référence.
• SumPlage : Paramètre facultatif, c'est une plage de cellule qui doit être de la même taille que le premier argument (Plage) et doit contenir des valeurs numériques ; si cette plage est définie, c'est elle qui sera additionnée, si elle est omise ce sont les valeurs du premier paramètre qui seront additionnées.

Dans la cellule K2 de la feuille CommandesSemaine1Janvier du classeur du travail, on veut calculer le chiffre d'affaire (CA) pour chaque article. Le premier paramètre c'est donc la plage B2:B21, le deuxième paramètre c'est la cellule H2 et le troisième paramètre, les valeurs à additionner, se trouve dans la plage E2:E21.

Après avoir validé l'expression, on va la recopier vers les cellules d'en bas.

Cliquer droit sur l'animation puis Afficher l'image pour agrandir

L'insertion de la fonction peut aussi se faire à l'aide de l'assistant.

Cliquer droit sur l'animation puis Afficher l'image pour agrandir

La fonction MOYENNE.SI (AVERAGEIF)

La fonction MOYENNE.SI est bâtie sur le même modèle que la fonction SOMME.SI, c'est une combinaison de la fonction MOYENNE qui renvoie la moyenne de plusieurs valeurs numériques, et de la fonction SI qui évalue une expression logique puis renvoie une valeur ou une autre selon le résultat de cette expression.

MOYENNE.SI a la même syntaxe que la fonction SOMME.SI, seuls le nom et le rendu de la fonction changent.

Ceci étant dit, il suffit de projeter ce qui est vu à propos de la fonction SOMME.SI, sur la fonction MOYENNE.SI et de pratiquer.

Validation des données

Une autre chose qui entre dans la logique des faits c'est de vérifier la saisie de l'utilisateur et ne l'accepter que si elle respecte un certain nombre de règles. La validation des données a pour but de contrôler la saisie de l'utilisateur et la restreindre à des valeurs cohérentes.

Exemples :

• Empêcher l'utilisateur de saisir du texte dans une zone réservée à des nombres
• Limiter la saisie à des nombres entiers
• Ne pas dépasser une certaine longueur pour une chaine saisie
• Saisir une date supérieure ou égale à la date du jour
• Se limiter aux éléments d'une liste

Restreindre la saisie

Restreindre la saisie c'est ne pas laisser l'utilisateur taper n'importe quoi au clavier, il faut le guider pour qu'il ne saisisse que ce que l'application attend de lui. Par exemple, dans la colonne Quantité de la feuille Facture, Excel attend la quantité des articles commandés, c'est à dire un nombre ; et puisque les articles ne sont pas divisibles (on ne peut pas vendre un disque dur et demi), Excel attend un nombre entier. Il ne faut donc pas accepter des nombres avec virgule et encore moins les nombres négatifs et du texte.

Pour appliquer ces restrictions on doit d'abord sélectionner les cellules concernées, dans notre cas la plage B6:B12 puis sous l'onglet Données, dans le groupe Outils de données, ouvrir le bouton Validation et choisir Validation des données...

Dans la boite de dialogue Validation des données sous l'onglet Options, faire les manipulations suivantes :

1. Ouvrir la liste Autoriser et choisir Nombre entier
2. Ouvrir la liste Données et choisir Supérieur à
3. Dans le champ Minimum taper 0 (zéro)
4. Valider en cliquant sur OK

Alors maintenant que la saisie est restreinte, voyons si on a le droit de saisir des données non valides.

Très bien, Excel n'accepte plus les saisies intempestives, il ne laisse passer que les nombres entiers strictement positifs, ce qui est cohérent avec le champ Quantité.

De la même manière on peut contrôler la saisie des données des autres types : Date, Heure, Texte, Liste et même des autorisations personnalisées (en utilisant des formules).

Se limiter aux éléments d'une liste

La restriction de la saisie peut aussi se faire en utilisant une liste. C'est un moyen efficace et qui a l'avantage d'épargner l'utilisateur de toute saisie, il n'a qu'à faire son choix dans la liste ; tant mieux puisque ça évite les fautes de frappe.

Mettons cette technique en œuvre. On veut limiter les désignations de la facture à la liste des articles qui existent réellement dans le magasin. Cette liste se trouve dans la plage H2:H11 de la feuille CommandesSemaine1Janvier.

Après avoir sélectionné, dans la feuille Facture, les cellules dont on veut limiter la saisie et ouvert la boite de dialogue Validation des données, on suit les étapes indiquées dans l'animation ci-dessous.

Cliquer droit sur l'animation puis Afficher l'image pour agrandir

Créer un message de saisie et une alerte d'erreur

Pour aider l'utilisateur à saisir les informations attendues, on utilise un message de saisie qui apparait sous forme d'une infobulle lorsque celui-ci s'apprête à saisir.

1. Sélectionner les cellules de saisie, puis ouvrir la boite de dialogue Validation des données et activer l'onglet Message de saisie
2. Saisir le titre de l'infobulle
3. Taper le corps du message
4. Valider

Dans le cas d'une saisie erronée, l'utilisateur doit être alerté par un message d'erreur. Ce message est créé de la même façon que le message de saisie avec une étape de plus pour choisir le style du message. Ce style existe en trois variantes :

• Information : c'est un message qui donne une information à l'utilisateur, sans rien de grave
• Avertissement : c'est un message qui met l'utilisateur en garde et qui indique que la donnée qui vient d'être saisie pourrait causer des ennuis par la suite
• Stop : message qui veut dire que l'utilisateur a entré une donnée incompatible et qu'il faut la modifier immédiatement ou bien l'annuler

Dans le cas présent, le message de type Stop est utilisé car la valeur négative saisie (-6) est complétement inacceptable pour la quantité commandée ; il faut alors la modifier en cliquant sur le bouton Réessayer ou bien annuler la saisie en cliquant sur le bouton Annuler.

Recevez ce cours au format PDF en envoyant un message ici