Fonctions conditionnelles dans les feuilles de calcul

Quand on apprend à programmer, quels que soient le langage et le niveau, on découvre très vite les conditionnelles. Les classiques instructions IF-ELSEIF-ELSE permettent d’exécuter des opérations différentes selon des conditions logiques. Ce n’est pas forcément ce à quoi l’on pense en premier avec Excel. Pourtant, elles existent et deviennent vite essentielles dès que vos rapports Excel gagnent en complexité. Dans ce tutoriel, nous allons voir comment sont structurées les instructions IF-ELSE dans Excel, ainsi que des fonctions plus avancées comme COUNTIF() qui se révèlent très utiles dans certaines situations.

Instructions IF de base

Commençons par un aperçu de l’écriture d’une instruction IF simple dans Excel. La syntaxe est la suivante :

IF(condition, value_if_true, value_if_false)

Où :

• condition : une valeur ou une opération logique évaluée à TRUE ou FALSE
• value_if_true : la valeur renvoyée si la condition est TRUE
• value_if_false : la valeur renvoyée si la condition est FALSE

Passons à un exemple simple d’utilisation d’une condition de base avec un petit tableau :

Simple, non ? Si vous utilisez R, vous aurez remarqué que cette syntaxe est très proche de la fonction ifelse(). Il y a toutefois quelques différences, notamment du côté des opérateurs logiques. Dans l’exemple ci-dessus, j’ai utilisé l’opérateur logique ">", qui signifie supérieur à. D’autres sont moins intuitifs. Par exemple, l’opérateur différent de s’écrit "<>".

Voici la liste complète des opérateurs de comparaison utilisés dans Excel :

OR(), AND() et NOT()

Les opérateurs de comparaison ne sont pas les seuls éléments des conditionnelles Excel qui diffèrent de ce que vous utilisez peut-être en Python, R ou Matlab. La manière d’écrire les opérations booléennes OR, AND et NOT change également. Par exemple, dans Excel, vous écririez une expression OR ainsi :

IF(OR(value_1 = k, value_2 = y), value_if_true, value_if_false)

Pour voir tous les opérateurs booléens à l’œuvre dans Excel, regardez l’exemple ci-dessous :

Imbrication d’instructions IF

Comme en Python, R ou Matlab, vous pouvez imbriquer des IF-ELSEIF-ELSE dans Excel. Il suffit d’ajouter une instruction IF à l’intérieur d’une autre, selon que la première évalue à TRUE ou FALSE. Cela vous permet de tester davantage de conditions et de renvoyer des résultats différents. Excel peut imbriquer jusqu’à 64 instructions IF. Toutefois, je vous déconseille d’en empiler trop : la logique devient vite délicate à suivre, et le débogage ou les modifications peuvent se révéler très pénibles.

Voici un cas d’usage simple d’IF imbriqués directement dans Excel :

COUNTIF() et COUNTIFS()

Au-delà des IF classiques, Excel propose d’autres fonctions qui opèrent en fonction d’un ensemble de conditions. Dans cette section, je me concentre sur les fonctions de comptage conditionnel, et plus précisément sur COUNTIF() et COUNTIFS().

COUNTIF() permet de compter les cellules qui respectent un seul critère. Sa syntaxe est la suivante :

COUNTIF(cell_range_to_count, criteria)

Où :

• cell_range_to_count : l’intervalle précis de cellules à compter
• criteria : le critère définissant quelles cellules doivent être comptées. Ils peuvent être très variés, par exemple :
  • COUNTIF(A1:A10, 10) - Compte le nombre de cellules de A1 à A10 égales à 10
  • COUNTIF(A1:A10, B5) - Compte le nombre de cellules de A1 à A10 égales à la valeur de B5
  • COUNTIF(A1:A10, "Gandalf") - Compte le nombre de cellules de A1 à A10 égales à "Gandalf"
  • COUNTIF(A1:A10, ">20") - Compte le nombre de cellules de A1 à A10 supérieures à 20
  • COUNTIF(A1:A10, "<>Gandalf") - Compte le nombre de cellules de A1 à A10 différentes de "Gandalf"

COUNTIFS, comme vous vous en doutez, diffère de COUNTIF car elle permet de compter les cellules qui satisfont plusieurs critères. Sa syntaxe est la suivante :

COUNTIFS(cell_range_to_count_1, criteria_1, cell_range_to_count_2, criteria_2,...cell_range_to_count_n, criteria_n)

Important : pour chaque plage supplémentaire précisée dans COUNTIFS, le nombre de lignes et de colonnes doit être identique à celui de la plage d’origine (cell_range_to_count_1), faute de quoi vous obtiendrez une erreur.

Ces deux fonctions sont parfaites pour les rapports métiers. Un excellent exemple : compter facilement le nombre de rendez-vous pris chaque mois par nos commerciaux et leur position dans l’entonnoir de vente.

Dans les vidéos ci-dessous, je montre ces deux fonctions en action. Commençons par compter le total des rendez-vous d’un groupe de commerciaux fictifs :

Facile, n’est-ce pas ? Vous venez de totaliser le nombre de rendez-vous de chacun de nos commerciaux fictifs. En théorie, on pourrait le faire avec des IF de base, mais cela demanderait bien plus d’efforts pour un résultat identique.

Imaginons maintenant que vous souhaitiez aussi totaliser les rendez-vous de chacun par mois. Dans ce cas, COUNTIFS() est la solution : voyez-la à l’œuvre dans la vidéo ci-dessous :

Remarque : vous aurez peut-être remarqué que j’ai entouré de signes $ la plage à compter dans la dernière vidéo. Cela permet de la verrouiller et d’éviter qu’elle ne change lorsque je fais glisser la formule vers le bas.

Et voilà, nous avons totalisé les rendez-vous de nos commerciaux fictifs par mois sans difficulté. Sachez qu’il est possible de procéder de façon plus « efficiente » en jouant avec les $ pour verrouiller certaines cellules et plages lorsque vous recopiez les formules (par exemple, j’aurais pu écrire la formule en F2 puis la propager). J’ai préféré expliciter les formules COUNTIFS pour janvier et février afin d’être plus pédagogique. Si vous êtes curieux, je vous invite à réécrire la formule COUNTIFS() en F2 de manière à pouvoir la recopier vers les autres cellules tout en obtenant les mêmes résultats. C’est un excellent exercice, d’autant plus utile que la maîtrise des verrouillages de cellules et plages fait gagner un temps précieux quand vos rapports Excel prennent de l’ampleur. Voici le tableau de données utilisé dans ce tutoriel.

SUMIF() et SUMIFS()

Nous avons vu comment compter les cellules répondant à un critère unique ou multiple avec COUNTIF() et COUNTIFS(). Mais si vous devez additionner les cellules plutôt que les compter ? Place à SUMIF() et SUMIFS(). Ces fonctions sont particulièrement utiles pour additionner, par exemple, le chiffre d’affaires d’un commercial sur la période ou par mois. Leur syntaxe est la suivante :

SUMIF(criteria_range, criteria, (optional) sum_range)

SUMIFS(sum_range, criteria_range_1, criteria_1, criterion_range_2, criteria_2...criteria_range_n, criteria_n)

Où :

• criteria_range : la plage de cellules dans laquelle le critère est évalué.
• criteria : le critère conditionnel déterminant quelles cellules seront additionnées.
• sum_range : les cellules à additionner. Si elle n’est pas fournie dans COUNTIF(), les cellules de criteria_range sont additionnées.

Sans plus attendre, voyons un exemple utilisant SUMIF() et SUMIFS() pour calculer le chiffre d’affaires total et mensuel généré par nos commerciaux fictifs :

À noter : pour SUMIFS(), sum_range est un argument obligatoire et le premier de la liste, tandis qu’il est optionnel et placé en dernier dans SUMIF(). Cette différence peut prêter à confusion lorsque vous utilisez les deux formules, comme on le voit lors de la saisie de SUMIFS pour février. Soyez vigilant : vérifiez toujours que vous additionnez les bonnes plages.

IFERROR()

Si vous connaissez un langage de programmation ou de script, vous savez qu’il existe des blocs try/catch pour la gestion des erreurs. Dans Excel, la fonction IFERROR() joue ce rôle. Elle permet de « capturer » des erreurs comme #N/A, #VALUE! ou #REF! et de renvoyer une sortie plus parlante. La syntaxe de la fonction IFERROR est la suivante :

IFERROR(value, value_if_error)

Où :

• value : la valeur ou la formule à tester pour une erreur
• value_if_error : la valeur renvoyée lorsque la fonction rencontre une erreur.

Voyons-la en action dans la vidéo suivante :

Dans les rapports commerciaux, il est souvent judicieux d’englober certaines formules dans des IFERROR. Il arrive que de nouveaux commerciaux aient très peu de rendez-vous, d’opportunités qualifiées ou d’affaires gagnées, ce qui peut provoquer des divisions par 0 lors du calcul, par exemple, du pourcentage de rendez-vous convertis en opportunités qualifiées. Ces erreurs peu esthétiques nuisent à la lisibilité de votre rapport pour les décideurs. En recouvrant ces calculs d’instructions IFERROR(), vous pouvez remplacer ces erreurs par un tiret, bien plus propre à l’œil, et rendre votre rapport plus présentable. Au-delà d’un affichage plus agréable, IFERROR() permet aussi des usages avancés : vous pouvez imbriquer ces instructions pour, par exemple, enchaîner des VLOOKUP.

Conclusion

Félicitations ! Vous connaissez désormais plusieurs façons d’exploiter les conditionnelles dans Excel. Dans ce tutoriel, j’ai couvert les fonctions que j’utilise le plus souvent lorsque je dois réaliser des rapports métiers sous Excel. Si vous souhaitez aller plus loin, sachez qu’il existe d’autres fonctions comme SWITCH(), AVERAGEIF() ou AVERAGEIFS() dont je n’ai pas parlé ici et qui pourraient vous intéresser.

Par ailleurs, l’utilisation de jokers (wildcards) avec des fonctions comme COUNTIFS() est une méthode tout à fait valide pour compter des correspondances partielles de chaînes. Par exemple, si vous avez une liste d’éléments de menu d’un restaurant et souhaitez compter ceux qui mentionnent le mot "fish", vous pourriez écrire COUNTIF(cell_range,"*fish") : la fonction compterait alors swordfish ou dolphinfish. Je vous encourage vivement à vous familiariser avec les jokers : ils sont très utiles et peuvent vous dépanner dans votre prochain projet Excel.

Enfin, vous pouvez aussi consulter le cours Data Analysis with Spreadsheets sur DataCamp, qui consacre une section aux fonctions conditionnelles et pourra vous servir de référence complémentaire.

Découvrez aussi notre tutoriel if…elif…else en Python.