mosprepaPréparez votre certification
Microsoft Office Specialist

MS Excel 2013/2016

Les fonctions conditionnelles

Les fonctions conditionnelles, on dit aussi les fonctions logiques, sont plus intelligentes que les fonctions "normales", elles retournent une valeur si une condition est vérifiée ou bien une autre valeur dans le cas contraire.

Dans ce chapitre on va voir les fonctions suivantes et aussi comment paramétrer Excel pour vérifier les données saisies par l'utilisateur avant de les valider :

  1. La Fonction SI (IF)
  2. La fonction SOMME.SI (SUMIF)
  3. La fonction MOYENNE.SI (AVERAGEIF)
  4. La fonction NB.SI (COUNTIF)
  5. Validation des données

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.

Les fonctions conditionnelles Excel

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

Les fonctions conditionnelles Excel

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 :

Les fonctions conditionnelles Excel

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

Les fonctions conditionnelles Excel

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.

Les fonctions conditionnelles Excel

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

Les fonctions conditionnelles Excel

... ou bien en utilisant l'assistant

Les fonctions conditionnelles Excel

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.

Les fonctions conditionnelles Excel

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.

Les fonctions conditionnelles Excel

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.

Astuce d'écriture

Pour une lecture plus claire de l'expression, utiliser des retours à la ligne (ALT + Entrée) au début de chaque fonction SI, comme c'est le cas ici avec trois imbrications.

Les fonctions conditionnelles Excel

La fonction SI.CONDITIONS

Excel 2016 (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.

Les fonctions conditionnelles Excel

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 :

Les fonctions conditionnelles Excel

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

Les fonctions conditionnelles Excel

Travail à faire

Dans cet exercice on va essayer de remédier au problème d'affichage des zéros 0.00 si aucune quantité n'est saisie. On voudrait que si une case Quantité est vide ses correspondantes Total HT et Total TTC restent vides.

Les fonctions conditionnelles Excel

Indications :

  • Utiliser la fonction SI
  • Pour dire qu'une cellule est vide on utilise des guillemets vides "" (rien à l'intérieur même pas un espace)

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 :

Les fonctions conditionnelles Excel

  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.

Les fonctions conditionnelles Excel

Note importante :

Si on a l'intention de recopier la fonction vers d'autres cellules (comme c'est le cas ici) il faut penser à fixer la plage de référence en utilisant les symboles dollar $ ou bien en nommant la plage.

Les fonctions conditionnelles Excel

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.

Les fonctions conditionnelles Excel

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

Le paramètre Critère peut contenir des caractères génériques comme :

  • le point d'interrogation (?) pour remplacer un seul caractère
  • l'astérisque (*) pour indiquer une suite de caractères

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.

Les fonctions conditionnelles Excel

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

Note importante :

Si on a l'intention de recopier la fonction vers d'autres cellules (comme c'est le cas ici) il faut penser à fixer le premier argument Plage et le troisième SumPlage lorsqu'il est défini, en utilisant les symboles dollar $ ou bien en nommant ces plages.

Les fonctions conditionnelles Excel

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.

Les fonctions conditionnelles Excel

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.

Les fonctions conditionnelles Excel

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.

Travail à faire

Sur la feuille CommandesSemaine1Janvier du classeur téléchargé, utiliser la fonction MOYENNE.SI pour calculer la moyenne de CA pour chaque article (colonne J).

Les fonctions conditionnelles Excel

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

Les fonctions conditionnelles Excel

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

Les fonctions conditionnelles Excel

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

Les fonctions conditionnelles Excel

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.

Les fonctions conditionnelles Excel

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

Les fonctions conditionnelles Excel

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

Les fonctions conditionnelles Excel

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.

Téléchargement

Télécharger ce document au format PDF

MOS Prepa