Une facture dépasse un seuil, une prime dépend des ventes, un stock passe sous un minimum : dans un tableur, les calculs ne suivent pas toujours une règle unique. Les formules conditionnelles permettent justement d’adapter le résultat à une ou plusieurs situations. Au lieu de vérifier chaque ligne à la main, vous laissez Excel appliquer la bonne règle automatiquement.
Dans ce guide, nous allons voir comment utiliser les principales formules conditionnelles, les combiner et éviter les erreurs courantes. Pour illustrer les exemples, imaginons un tableau de suivi des ventes : la colonne A contient le nom du vendeur, la colonne B le montant vendu et la colonne C le statut de la commande. Les formules sont écrites avec le séparateur point-virgule, utilisé dans de nombreuses versions françaises d’Excel.
À quoi sert une formule conditionnelle ?
Une formule conditionnelle vérifie si une règle est respectée, puis renvoie un résultat en fonction de la réponse. Cette règle peut porter sur un nombre, un texte, une date ou le contenu d’une autre cellule.
Par exemple, vous pouvez demander à Excel d’afficher « Objectif atteint » si un vendeur a réalisé au moins 5 000 € de ventes, et « À suivre » dans le cas contraire. Vous pouvez aussi additionner uniquement les commandes livrées ou compter les factures échues. L’idée est simple : remplacer une vérification répétitive par une formule que vous pouvez recopier.
La fonction SI : le point de départ
La fonction SI teste une condition et renvoie une valeur si elle est vraie, une autre si elle est fausse. Sa structure est la suivante :
=SI(test_logique; valeur_si_vrai; valeur_si_faux)
Si le montant vendu se trouve en B2, cette formule indique si l’objectif de 5 000 € est atteint :
=SI(B2>=5000; "Objectif atteint"; "À suivre")
Excel compare la valeur de B2 à 5 000. Si la condition est vraie, il affiche le premier message. Sinon, il affiche le second. Vous pouvez aussi renvoyer un nombre, une date, une autre cellule ou une formule. Par exemple, pour calculer une prime de 5 % lorsque les ventes dépassent 5 000 € :
=SI(B2>=5000; B2*5%; 0)
Le résultat est une prime calculée, ou zéro si le seuil n’est pas atteint. Il suffit ensuite de recopier la formule vers le bas pour l’appliquer à toute la liste.
Les opérateurs de comparaison à connaître
Le test logique repose souvent sur un opérateur de comparaison. Les plus courants sont :
=: égal à ;<>: différent de ;>: supérieur à ;<: inférieur à ;>=: supérieur ou égal à ;<=: inférieur ou égal à.
Pour rechercher un texte, placez-le entre guillemets. Ainsi, =SI(C2="Livrée"; "À facturer"; "En attente") affiche un message selon le statut indiqué en C2. Pour comparer une cellule à une autre, les guillemets ne sont pas nécessaires : =SI(B2>D2; "Dépassement"; "Dans le budget").
Gérer plusieurs situations avec SI
Que faire si une seule réponse « vrai » ou « faux » ne suffit pas ? Vous pouvez imbriquer plusieurs fonctions SI. Par exemple, pour attribuer une mention selon le montant des ventes :
=SI(B2>=10000; "Excellent"; SI(B2>=7000; "Très bien"; SI(B2>=5000; "Atteint"; "À améliorer")))
Excel évalue les conditions dans l’ordre. Il vérifie d’abord si le montant atteint 10 000 €. Si ce n’est pas le cas, il teste le seuil de 7 000 €, puis celui de 5 000 €. L’ordre des seuils est donc important : placez les conditions les plus exigeantes en premier pour éviter qu’une règle moins stricte ne masque les suivantes.
Les fonctions SI imbriquées sont utiles pour quelques cas, mais la formule peut vite devenir difficile à relire. Si elle s’étire sur plusieurs lignes de parenthèses, ce n’est pas forcément le signe d’une grande sophistication : c’est peut-être le moment de simplifier.
Combiner des conditions avec ET et OU
La fonction ET vérifie plusieurs conditions et renvoie VRAI seulement si elles sont toutes respectées. Par exemple, une prime peut être accordée si le vendeur dépasse 5 000 € de ventes et si son statut est « Actif » :
=SI(ET(B2>5000; D2="Actif"); "Prime accordée"; "Pas de prime")
La fonction OU renvoie VRAI dès qu’au moins une des conditions est vraie. Pour signaler une commande en retard ou annulée, vous pouvez écrire :
=SI(OU(C2="En retard"; C2="Annulée"); "À vérifier"; "RAS")
Ces fonctions évitent de multiplier les fonctions SI lorsque plusieurs critères doivent être examinés en même temps. Gardez toutefois une règle simple : ET signifie « toutes les conditions », OU signifie « au moins une ».
Compter ou additionner selon un critère
Parfois, vous ne souhaitez pas afficher un message, mais produire directement un total ou un décompte. Les fonctions NB.SI et SOMME.SI sont alors très pratiques.
NB.SI compte les cellules qui répondent à un critère. Pour compter les commandes livrées dans la plage C2:C100 :
=NB.SI(C2:C100; "Livrée")
Pour compter les ventes supérieures ou égales à 5 000 € dans la colonne B :
=NB.SI(B2:B100; ">=5000")
Dans ce dernier exemple, le signe de comparaison et le seuil sont réunis dans le critère, entre guillemets. C’est une particularité utile à retenir lorsque vous travaillez avec des critères numériques.
SOMME.SI additionne les valeurs qui respectent un critère. Pour additionner les montants de toutes les commandes livrées, en utilisant les statuts de C2:C100 et les montants de B2:B100 :
=SOMME.SI(C2:C100; "Livrée"; B2:B100)
La première plage contient les cellules à tester, le deuxième argument correspond au critère, et le troisième indique les nombres à additionner. Autrement dit, Excel cherche « Livrée » dans la colonne C, puis additionne les montants correspondants de la colonne B.
Ajouter plusieurs critères avec SOMME.SI.ENS et NB.SI.ENS
Pour croiser plusieurs règles, utilisez SOMME.SI.ENS. Elle peut, par exemple, calculer le total des ventes d’un vendeur donné pour les commandes livrées :
=SOMME.SI.ENS(B2:B100; A2:A100; "Lina"; C2:C100; "Livrée")
La première plage est celle à additionner. Ensuite, chaque paire d’arguments contient une plage de critères et le critère à rechercher. Pour compter le nombre de commandes livrées par Lina, utilisez NB.SI.ENS :
=NB.SI.ENS(A2:A100; "Lina"; C2:C100; "Livrée")
Ces formules sont particulièrement utiles pour les tableaux de suivi : ventes par personne, dépenses par catégorie, retards par service ou commandes par période. Elles évitent de filtrer les données manuellement avant chaque calcul.
Travailler avec des dates et des cellules variables
Les critères peuvent aussi dépendre du contenu d’une autre cellule. Supposons que le seuil à atteindre soit indiqué en F1. La formule devient :
=SI(B2>=$F$1; "Objectif atteint"; "À suivre")
Les signes dollar dans $F$1 verrouillent la référence. Quand vous recopiez la formule vers le bas, Excel continue de comparer chaque vente au même seuil. Sans ce verrouillage, la référence pourrait se décaler vers F2, puis F3, et fausser le résultat.
Pour compter les échéances antérieures à la date du jour, vous pouvez utiliser :
=NB.SI(D2:D100; "<"&AUJOURDHUI())
La fonction AUJOURDHUI renvoie la date actuelle. L’esperluette assemble le signe « inférieur à » et le résultat de la fonction pour former le critère. Vérifiez que les valeurs de la colonne D sont bien reconnues comme des dates par Excel, et non comme du texte : une date mal interprétée peut rendre le calcul trompeur.
Éviter les erreurs les plus courantes
Une formule conditionnelle peut fonctionner sans erreur apparente tout en donnant un résultat inattendu. Avant de chercher une panne mystérieuse, vérifiez les points suivants :
- Les guillemets : un critère textuel comme « Livrée » doit être placé entre guillemets dans la formule.
- Le séparateur : selon les paramètres régionaux, Excel attend généralement des points-virgules dans les formules en français.
- Les plages : avec SOMME.SI.ENS, la plage à additionner et les plages de critères doivent avoir les mêmes dimensions.
- Les espaces invisibles : « Livrée » et « Livrée » suivi d’un espace peuvent être considérés comme deux textes différents. Un copier-coller depuis un autre outil peut introduire ce genre de surprise.
- L’ordre des tests : dans des SI imbriqués, une condition large placée trop tôt peut empêcher Excel de tester les conditions suivantes.
Pour vérifier une formule complexe, testez d’abord chaque partie séparément. Vous pouvez, par exemple, vérifier le résultat de ET(B2>5000; D2="Actif") dans une cellule vide. Si elle renvoie VRAI ou FAUX comme prévu, ajoutez ensuite la fonction SI autour.
Choisir la formule adaptée à votre besoin
Avant d’écrire une formule, définissez précisément le résultat attendu. Voulez-vous afficher un message, compter des lignes ou additionner des montants ? Cette question vous orientera vers la bonne fonction :
- utilisez SI pour renvoyer un résultat différent selon une condition ;
- utilisez ET et OU pour combiner des tests ;
- utilisez NB.SI ou NB.SI.ENS pour compter des éléments selon un ou plusieurs critères ;
- utilisez SOMME.SI ou SOMME.SI.ENS pour additionner des valeurs filtrées par des critères.
Un dernier conseil : choisissez des intitulés de colonnes clairs et gardez les seuils importants dans des cellules dédiées plutôt qu’en dur dans vos formules. Une règle modifiée dans une cellule est plus simple à mettre à jour qu’une série de formules à corriger une par une. Excel devient alors moins une grille de calcul qu’un véritable assistant : il applique vos règles, signale les exceptions et vous laisse davantage de temps pour analyser les résultats.
