Guide : Astuces pour les Formules Conditionnelles dans Microsoft Excel

Les formules conditionnelles dans Excel permettent d’introduire des décisions logiques dans vos calculs. Elles sont essentielles pour analyser des données, automatiser des processus, et personnaliser les rapports. Voici un guide pratique pour maîtriser les formules conditionnelles, accompagné d’astuces utiles pour les rendre encore plus puissantes.

1. La Base : La Formule SI()

La fonction SI() est au cœur des formules conditionnelles.

Syntaxe :

=SI(Test_Logique, Valeur_Si_Vrai, Valeur_Si_Faux)

Exemple : Vérifier une condition simple

Si le chiffre dans A1 est supérieur à 100, renvoyez “Oui”, sinon renvoyez “Non” : =SI(A1>100,"Oui","Non")

Astuces :

Valeur par défaut :
- Si aucune valeur ne convient, utilisez une valeur par défaut : =SI(A1>100,"Oui",SI(A1=100,"Égal à 100","Non"))
Imbrication de plusieurs SI :
- Gérez plusieurs conditions en imbriquant les fonctions SI : =SI(A1>100,"Grand",SI(A1=100,"Égal","Petit"))

2. SI.CONDITIONS() : Simplifier les Tests Multiples

Disponible dans Excel 2016 et versions ultérieures, SI.CONDITIONS() remplace les imbrications complexes.

Syntaxe :

=SI.CONDITIONS(Test1, Résultat1, Test2, Résultat2, ..., [VRAI, RésultatParDéfaut])

Exemple : Classifier des valeurs

Classifier un score dans des catégories : =SI.CONDITIONS(A1>80,"Excellence",A1>50,"Passable",A1>30,"À améliorer",VRAI,"Insuffisant")

3. Utiliser des Fonctions Conditionnelles Associées

a) SOMME.SI() et SOMME.SI.ENS()

Additionner les valeurs répondant à des critères spécifiques.

SOMME.SI() : =SOMME.SI(A1:A10,">50",B1:B10) Ajoute les valeurs dans B1:B10 si les valeurs correspondantes dans A1:A10 sont supérieures à 50.
SOMME.SI.ENS() : Additionnez en fonction de plusieurs critères : =SOMME.SI.ENS(B1:B10,A1:A10,">50",C1:C10,"=Ventes")

b) NB.SI() et NB.SI.ENS()

Comptez le nombre de cellules répondant à des critères spécifiques.

NB.SI() : =NB.SI(A1:A10,">100")
NB.SI.ENS() : =NB.SI.ENS(A1:A10,">100",B1:B10,"Ventes")

4. Gérer les Erreurs avec SIERREUR() et SI.NON.DISP()

a) SIERREUR()

Gérez les erreurs dans vos formules.

Exemple : Évitez une erreur de division par zéro : =SIERREUR(A1/B1,"Erreur")

b) SI.NON.DISP()

Remplacez une erreur #N/A par une valeur spécifique :

=SI.NON.DISP(RECHERCHEV("Produit",A1:B10,2,FAUX),"Non trouvé")

5. Formules Conditionnelles avec du Texte

Vous pouvez utiliser des critères textuels dans vos conditions.

Exemple : Vérifiez le contenu d’une cellule

=SI(A1="OK","Validé","Non validé")

Astuce : Rendre les tests insensibles à la casse

Utilisez MAJUSCULE() ou MINUSCULE() :

=SI(MINUSCULE(A1)="ok","Validé","Non validé")

6. Appliquer des Formules Conditionnelles avec des Dates

Excel prend en charge les dates pour des analyses basées sur le temps.

a) Vérifiez si une date est passée

=SI(A1<AUJOURDHUI(),"En retard","À temps")

b) Calculer l’écart entre deux dates

Avec DATEDIF() : =DATEDIF(A1,B1,"D")
- “D” : Jours.
- “M” : Mois.
- “Y” : Années.

7. Utilisez des Plages Dynamiques dans les Formules

Les plages dynamiques permettent de rendre les formules conditionnelles plus flexibles.

Exemple : Total des ventes pour une région spécifique

=SOMME.SI(Tableau1[Produit],"Produit A",Tableau1[Montant])

8. Combinez avec d’Autres Fonctions pour Plus de Puissance

a) SI + ET

Utilisez ET pour tester plusieurs conditions :

=SI(ET(A1>50,B1<100),"OK","Non OK")

b) SI + OU

Utilisez OU pour une validation conditionnelle alternative :

=SI(OU(A1="Actif",B1>100),"Valide","Non valide")

c) SI + RECHERCHEV

Combinez les recherches avec des conditions :

=SI(RECHERCHEV("Produit A",A1:B10,2,FAUX)>100,"Satisfaisant","Insuffisant")

9. Ajouter de la Mise en Forme Conditionnelle

Les formules conditionnelles peuvent être renforcées par une mise en forme conditionnelle.

Exemple : Colorer les valeurs supérieures à 100

Sélectionnez la plage de données.
Allez dans Accueil > Mise en forme conditionnelle > Nouvelle règle.
Entrez une formule : =A1>100

10. Optimisez Vos Formules

Évitez les calculs redondants :
- Utilisez des colonnes auxiliaires pour simplifier les formules imbriquées.
Plages spécifiques :
- Réduisez l’utilisation de plages comme A:A au profit de plages définies A1:A100.
Validez les formules avec l’Auditeur :
- Allez dans Formules > Auditer les Formules pour visualiser les dépendances.

Comment Imbriquer des Formules Conditionnelles dans Excel

L’imbrication des formules conditionnelles dans Excel permet de gérer des scénarios complexes où plusieurs conditions doivent être vérifiées. En imbriquant des fonctions comme SI(), ET(), OU(), ou encore des formules avancées comme SI.CONDITIONS(), vous pouvez créer des calculs puissants et précis.

1. La Base : Imbrication de SI()

La fonction SI() peut être imbriquée pour traiter plusieurs conditions successives.

Exemple : Classifier des valeurs

Contexte : Vous voulez catégoriser des scores :
- 80 : “Excellence”.
- 50-80 : “Passable”.
- <50 : “Insuffisant”.
Formule : =SI(A1>80,"Excellence",SI(A1>=50,"Passable","Insuffisant"))
Explication :
- Si A1 > 80, retourne “Excellence”.
- Sinon, si A1 >= 50, retourne “Passable”.
- Sinon, retourne “Insuffisant”.

2. Simplifiez avec SI.CONDITIONS()

La fonction SI.CONDITIONS() (disponible dans Excel 2016 et versions ultérieures) rend l’imbrication plus lisible.

Exemple : Classifier des ventes

Contexte : Vous voulez catégoriser les ventes :
- 10,000 : “Excellent”.
- 5,000–10,000 : “Bon”.
- <5,000 : “À améliorer”.
Formule : =SI.CONDITIONS(A1>10000,"Excellent",A1>=5000,"Bon",VRAI,"À améliorer")
Avantages :
- Plus facile à lire que plusieurs SI() imbriqués.
- Pas de limite pratique au nombre de conditions.

3. Combiner SI() avec ET() ou OU()

a) SI + ET : Toutes les conditions doivent être vraies

Exemple : Vérifiez si une commande est prioritaire (quantité >100 ET date avant le 31 décembre) : =SI(ET(A1>100,B1<DATE(2023,12,31)),"Prioritaire","Standard")

b) SI + OU : Une des conditions doit être vraie

Exemple : Vérifiez si un produit est éligible pour une réduction (quantité >50 OU catégorie = “Promo”) : =SI(OU(A1>50,B1="Promo"),"Réduction","Plein tarif")

4. Formules Imbriquées avec Textes

Exemple : Attribuer une évaluation basée sur un texte

Contexte : Notez un employé en fonction des retours (Excellent, Bon, Insuffisant). =SI(A1="Excellent",5,SI(A1="Bon",3,1))

5. Combiner avec des Fonctions Numériques

Exemple : Calcul basé sur plusieurs seuils

Contexte : Calculez un bonus basé sur les ventes :
- 10,000 : 20%.
- 5,000–10,000 : 10%.
- <5,000 : 0%.
Formule : =SI(A1>10000,A1*0.2,SI(A1>=5000,A1*0.1,0))

6. Imbrication avec des Formules de Recherche

Combinez SI() et RECHERCHEV() pour des analyses complexes.

Exemple : Rechercher une valeur et vérifier une condition

Contexte : Vérifiez si le produit recherché dépasse un certain seuil. =SI(RECHERCHEV("Produit A",A1:B10,2,FAUX)>1000,"OK","Non OK")

7. Utiliser SIERREUR() pour Gérer les Erreurs

Lorsque vous imbriquez des formules, des erreurs peuvent apparaître (ex. : recherche non trouvée). Utilisez SIERREUR() pour les gérer.

Exemple : Gérer les erreurs dans une recherche

=SIERREUR(RECHERCHEV("Produit A",A1:B10,2,FAUX),"Non trouvé")

8. Gestion des Dates avec DATEDIF() et SI()

Exemple : Calculer un statut basé sur une date

Contexte : Déterminez si une tâche est en retard : =SI(AUJOURDHUI()>B1,"En retard","À jour")

Exemple : Évaluer la durée entre deux dates

Contexte : Catégorisez les projets en fonction de leur durée :
- <30 jours : “Court”.
- 30–60 jours : “Moyen”.
- 60 jours : “Long”.
Formule : =SI(DATEDIF(A1,B1,"D")<30,"Court",SI(DATEDIF(A1,B1,"D")<=60,"Moyen","Long"))

9. Imbrication avec Plages Dynamiques

Exemple : Utilisation avec des plages dynamiques et conditions multiples

Contexte : Calculez une remise en fonction de plusieurs produits. =SI.CONDITIONS(SOMME.SI(A1:A10,"Produit A",B1:B10)>1000,"Réduction","Pas de réduction")

10. Bonnes Pratiques pour l’Imbrication

Limitez les Imbrications de SI() :
- Trop d’imbrications peuvent rendre la formule difficile à lire.
- Préférez SI.CONDITIONS() si possible.
Testez chaque étape :
- Vérifiez chaque condition avant d’imbriquer pour éviter les erreurs.
Documentez vos formules :
- Utilisez des commentaires ou des notes pour expliquer des formules complexes.
Utilisez des Colonnes Auxiliaires :
- Décomposez des calculs complexes en étapes dans des colonnes séparées.