Comprendre la Logique 'CASE WHEN' dans Excel
La logique 'CASE WHEN' est un concept emprunté aux langages de requête de bases de données, notamment SQL. Elle permet d'évaluer une ou plusieurs conditions et de renvoyer une valeur différente selon la condition qui est remplie. En d'autres termes, elle permet de créer une structure de décision complexe.
Bien qu'Excel ne possède pas de fonction native 'CASE WHEN', plusieurs fonctions peuvent être combinées pour obtenir le même résultat. Les plus couramment utilisées sont la fonction SI, la fonction IFS (disponible dans Excel 2016 et versions ultérieures), et des combinaisons de ET et OU avec SI.
La Fonction SI : L'Équivalent de Base
La fonction SI est le pilier de la logique conditionnelle dans Excel. Sa syntaxe est la suivante :
=SI(condition, valeur_si_vrai, valeur_si_faux)
- condition : L'expression logique à évaluer (par exemple,
A1>10). - valeur_si_vrai : La valeur à renvoyer si la condition est vraie.
- valeur_si_faux : La valeur à renvoyer si la condition est fausse.
Exemple :
Supposons que vous ayez une colonne de chiffres en colonne A, et que vous souhaitiez afficher "Positif" si le chiffre est supérieur à zéro, et "Négatif ou nul" sinon. La formule à utiliser serait :
=SI(A1>0, "Positif", "Négatif ou nul")
[Description de l'image: Capture d'écran d'une feuille Excel avec une colonne A contenant des nombres positifs et négatifs. La colonne B contient la formule SI et affiche "Positif" ou "Négatif ou nul" en fonction de la valeur de la colonne A.]
Imbriquer les Fonctions SI pour une Logique Plus Complexe
Pour simuler un 'CASE WHEN' avec plusieurs conditions, vous pouvez imbriquer plusieurs fonctions SI les unes dans les autres. Cela signifie utiliser une fonction SI dans la partie valeur_si_faux d'une autre fonction SI.
Exemple :
Imaginons que vous souhaitiez attribuer une note en fonction d'un score :
- Si le score est supérieur ou égal à 90, la note est "A"
- Si le score est supérieur ou égal à 80 mais inférieur à 90, la note est "B"
- Si le score est supérieur ou égal à 70 mais inférieur à 80, la note est "C"
- Sinon, la note est "D"
La formule imbriquée serait :
=SI(A1>=90, "A", SI(A1>=80, "B", SI(A1>=70, "C", "D")))
[Description de l'image: Capture d'écran d'une feuille Excel avec une colonne A contenant des scores. La colonne B contient la formule SI imbriquée et affiche la note correspondante.]
Attention : Imbriquer trop de fonctions SI peut rendre la formule difficile à lire et à maintenir. De plus, les anciennes versions d'Excel avaient une limite au nombre de fonctions imbriquées. Pour les cas complexes, la fonction IFS est préférable.
La Fonction IFS : Une Alternative Plus Lisible (Excel 2016 et versions ultérieures)
La fonction IFS simplifie l'écriture de structures conditionnelles complexes. Elle permet de spécifier plusieurs conditions et leurs valeurs correspondantes dans une seule fonction. Sa syntaxe est :
=IFS(condition1, valeur_si_condition1_vrai, condition2, valeur_si_condition2_vrai, ..., valeur_si_aucune_condition_vrai)
- condition1, condition2, ... : Les expressions logiques à évaluer.
- valeur_si_condition1_vrai, valeur_si_condition2_vrai, ... : Les valeurs à renvoyer si la condition correspondante est vraie.
- valeur_si_aucune_condition_vrai : (Optionnel) La valeur à renvoyer si aucune des conditions précédentes n'est vraie. Si omis, Excel renvoie une erreur #N/A si aucune condition n'est remplie.
Exemple :
Reprenons l'exemple des notes : en utilisant IFS, la formule devient :
=IFS(A1>=90, "A", A1>=80, "B", A1>=70, "C", TRUE, "D")
[Description de l'image: Capture d'écran d'une feuille Excel avec une colonne A contenant des scores. La colonne B contient la formule IFS et affiche la note correspondante.]
Notez l'utilisation de TRUE comme dernière condition. Cela garantit qu'une valeur sera toujours renvoyée, même si aucune des conditions précédentes n'est remplie. Dans ce cas, si le score est inférieur à 70, la formule renverra "D".
Combiner SI, ET et OU pour des Conditions Multiples
Parfois, vous aurez besoin d'évaluer plusieurs conditions simultanément. Les fonctions ET et OU permettent de combiner des expressions logiques.
- ET(condition1, condition2, ...) : Renvoie
VRAIsi toutes les conditions sont vraies, sinonFAUX. - OU(condition1, condition2, ...) : Renvoie
VRAIsi au moins une des conditions est vraie, sinonFAUX.
Exemple :
Supposons que vous souhaitiez accorder une remise si un client a dépensé plus de 100€ et est membre du programme de fidélité. Vous avez les dépenses en colonne A et un indicateur (1 pour membre, 0 pour non-membre) en colonne B. La formule serait :
=SI(ET(A1>100, B1=1), "Remise accordée", "Pas de remise")
[Description de l'image: Capture d'écran d'une feuille Excel avec une colonne A contenant les dépenses des clients et une colonne B indiquant s'ils sont membres du programme de fidélité. La colonne C contient la formule SI et affiche "Remise accordée" ou "Pas de remise" en fonction des deux conditions.]
Exemple avec OU :
Vous souhaitez offrir une promotion si le client a dépensé plus de 100€ ou s'il a un coupon de réduction. La formule serait :
=SI(OU(A1>100, C1=1), "Promotion applicable", "Pas de promotion") (C1 représente la présence d'un coupon)
[Description de l'image: Capture d'écran d'une feuille Excel avec une colonne A contenant les dépenses des clients et une colonne C indiquant s'ils possèdent un coupon de réduction. La colonne D contient la formule SI et affiche "Promotion applicable" ou "Pas de promotion" en fonction d'au moins une des conditions.]
Utiliser RECHERCHEV/RECHERCHEH pour des Scénarios Complexes
Dans certains cas, la logique 'CASE WHEN' peut être représentée par une table de correspondance. Au lieu d'imbriquer des fonctions SI ou d'utiliser IFS, vous pouvez utiliser les fonctions RECHERCHEV (recherche verticale) ou RECHERCHEH (recherche horizontale) pour rechercher une valeur dans une table et renvoyer une valeur correspondante.
Exemple :
Vous avez une table qui associe un code produit à un prix différent en fonction de la quantité commandée :
| Code Produit | Quantité Minimum | Prix Unitaire |
|---|---|---|
| A101 | 1 | 10€ |
| A101 | 10 | 9€ |
| A101 | 50 | 8€ |
Vous avez le code produit en A1 et la quantité en B1. La formule pour trouver le prix unitaire correct serait :
=RECHERCHEV(A1&B1, TableDePrix, 3, VRAI)
Où TableDePrix est la plage de cellules contenant la table ci-dessus, et la colonne contenant le code produit et la quantité a été concaténée (A1&B1) pour la recherche. Ici, la concaténation permet de créer une clé unique pour chaque combinaison de code produit et quantité.
[Description de l'image: Capture d'écran d'une feuille Excel avec une table de correspondance code produit/quantité/prix. Une autre partie de la feuille contient le code produit et la quantité saisis par l'utilisateur. Une formule RECHERCHEV utilise ces informations pour trouver le prix unitaire correspondant dans la table.]
Bonnes Pratiques et Erreurs à Éviter
- Clarté et Lisibilité : Évitez d'imbriquer trop de fonctions
SI. UtilisezIFSsi possible. Utilisez des noms de plages pour rendre vos formules plus compréhensibles. - Vérification des Conditions : Assurez-vous que toutes les conditions possibles sont couvertes. Utilisez une condition
TRUEdansIFSou unevaleur_si_fauxdansSIpour gérer les cas non prévus. - Priorité des Conditions : L'ordre des conditions est important. Les conditions sont évaluées de haut en bas (ou de gauche à droite dans
IFS). Assurez-vous que les conditions les plus spécifiques sont évaluées en premier. - Tests : Testez vos formules avec différentes valeurs pour vous assurer qu'elles fonctionnent correctement. Utilisez des exemples concrets pour valider votre logique.
- Gestion des Erreurs : Utilisez la fonction
SIERREURpour gérer les erreurs potentielles dans vos formules. Par exemple, si une fonctionRECHERCHEVne trouve pas de correspondance, elle renverra une erreur #N/A. Vous pouvez utiliserSIERREURpour renvoyer une valeur par défaut dans ce cas.
Conclusion
Bien qu'Excel ne propose pas de fonction 'CASE WHEN' native, il offre des outils puissants pour implémenter une logique conditionnelle complexe. En maîtrisant les fonctions SI, IFS, ET, OU et RECHERCHEV, vous pouvez créer des formules qui prennent des décisions basées sur des conditions spécifiques, vous permettant d'analyser et de manipuler vos données de manière efficace.