Conditions, catégorisation et calculs conditionnels
Nous allons ici explorer quelques fonctions d’Excel qui permettent de gérer des conditions. Il ne s’agira pas de mises en forme conditionnelles de cellules ou de mises en forme conditionnelles de tableaux mais de la fonction très courante SI ainsi que de fonctions voisines : SI.CONDITIONS, SI. MULTIPLE et les calculs conditionnels.
Données
Nous utiliserons les données suivantes :
Fonction SI
La syntaxe de SI est simple. Comme toujours, les arguments sont séparés par des points-virgules :
=SI(condition; valeur_si_vrai; valeur_si_faux)
La condition peut porter sur des chaînes de caractères, des valeurs numériques, des dates… De même, les deux autres arguments peuvent être des calculs, du texte, etc.
Exemple de condition portant sur des valeurs et des arguments sous forme de texte : si c’est une ville de plus de 100 000 habitants, il sera indiqué « grande ville » en colonne D. Sinon, « petite ville ». On se positionne en D2 et on écrit la formule suivante, que l’on recopie ensuite vers le bas :
=SI(B2>100000; "grande ville"; "petite ville")
La condition est un test logique. Elle peut parfaitement intégrer OU ou ET. Par exemple, réutilisons la colonne D pour indiquer « EST » si la région est soit Grand Est, soit Bourgogne-Franche-Comté. Sinon, la formule doit renvoyer « AUTRE ».
=SI(OU(C2="Bourgogne-Franche-Comté"; C2="Grand Est"); "EST"; "AUTRE")
Compliquons un peu la condition. Nous souhaitons que la ville appartienne à la région Bourgogne-Franche-Comté et que sa population soit comprise entre 100 1000 et 150 000 habitants. Si c’est le cas, la colonne D doit comporter un smiley (alt+1, à placer entre guillemets puisque c’est du texte). Sinon, rien.
=SI(ET(B3>100000; B3<150000; C3="Bourgogne-Franche-Comté"); "☺"; "")
Remarquez que la condition est double sur la colonne B et qu’elle porte aussi sur la colonne C. Remarquez aussi les guillemets qui n’encadrent rien pour indiquer que si la condition n’est pas remplie la cellule doit rester vierge. En l’occurrence, un seul smiley apparaît (pour Besançon).
Catégorisation
Il est aussi possible d’imbriquer plusieurs conditions les unes dans les autres.
Supposons que vous souhaitez passer vos vacances dans l’une de ses villes. En colonne D, vous indiquez « choix 1 » si c’est une petite ville du Centre-Val de Loire et « choix 2 » si c’est une petite ville d’une autre région. Sinon, c’est un troisième choix (on conservera notre définition des petites villes).
Ceci se traduit par une imbrication d’une deuxième fonction SI dans le troisième argument de la première : si la région est le Centre-Val de Loire et si la population est inférieure à 100 000, alors il faut indiquer « choix 1 ». Sinon, il faut poser une autre condition : si la population est inférieure à 100 000 (on se fiche désormais de sa région), alors on indique « choix 2 » et dans tous les autres cas, « choix 3 ».
=SI(ET(B2<100000;C2="Centre-Val de Loire"); "choix 1"; SI(B2<100000; "choix 2"; "choix 3"))
Cette technique n’est pas toujours simple à gérer. Il faut être très rigoureux car on a vite fait d’oublier des cas ou au contraire d’être redondant quand trois, quatre, cinq conditions sont imbriquées ! D’où une autre fonction d’Excel, apparue à partir de la version 2016 : SI.CONDITIONS.
=SI.CONDITIONS(condition1; valeur1; condition2; valeur2; ...)
Ceci nous permet de réécrire nos conditions de façon plus claire pour un résultat identique :
=SI.CONDITIONS(ET(B13<100000; C13="Centre-Val de Loire"); "choix 1"; B13<100000; "choix 2"; B13>=100000; "choix 3")
En fait, cette fonction est surtout utile pour catégoriser une variable de façon pratique (exemples : BFC pour Bourgogne-Franche-Comté, GE pour Grand Est… ou pour définir plusieurs tailles de villes en fonction du nombre d’habitants).
La fonction SI.MULTIPLE d’Excel 365 permet elle aussi la catégorisation.
=SI.MULTIPLE(C2; "Bourgogne-Franche-Comté"; "BFC"; "Grand Est"; "GE"; "Centre-Val de Loire"; "CVL")
Calculs conditionnels
La fonction =NB.SI est très différente des précédentes. Elle sert à compter les éléments qui satisfont à un ou plusieurs critères (et n’a donc généralement pas vocation à être recopiée). Syntaxe :
=NB.SI(plage; critère)
Exemple :
=NB.SI(C2:C13; "Grand Est")
On obtient : 5
Il est bien sûr possible de combiner cette fonction avec SI. Par exemple, affichons « Plusieurs villes Grand Est » dans une cellule si leur nombre dépasse trois.
=SI(NB.SI(C2:C13; "Grand Est")>=3; "plusieurs villes Grand Est"; "")
Les fonctions SOMME.Si et MOYENNE.SI s’emploient de la même manière.
=SOMME.SI(plage_critère; critère; plage_somme)
=MOYENNE.SI(plage_critère; critère; plage_moyenne)
Exemple de la somme des habitants des cinq villes du Grand Est. Le critère est la région et la plage sur laquelle la somme est calculée est la colonne B :
=SOMME.SI(C2:C13; "Grand Est"; B2:B13)
La formule suivante donne la taille moyenne des trois villes du Centre-Val de Loire.
=MOYENNE.SI(C2:C13; "Centre-Val de Loire"; B2:B13)
Et celle-ci donne la taille moyenne des seules villes de plus de 100 000 habitants.
=MOYENNE.SI(B2:B13; ">100000"; B2:B13)
Enfin, si l’on souhaite effectuer des calculs conditionnels sur des conditions multiples, il faut leur ajouter .ENS (donc NB.SI.ENS, SOMME.SI.ENS et MOYENNE.SI.ENS). Syntaxe :
=SOMME.SI.ENS(plage_somme; plage_critère1; critère1; plage_critère2; critère2; ...)
Exemple d'une fonction qui additionne les populations des grandes villes du Grand Est :
=SOMME.SI.ENS(B2:B13; C2:C13; "Grand Est"; B2:B13; ">100000")
