Imbriquer des fonctions pour des calculs plus élaborés

La puissance de calcul d'Excel nous permet d'élaborer des formules complexes et d'imbriquer plusieurs fonctions. Ça se révèle très utile pour les analyses de données ou pour des reportings performants.
Mais avant d'en arriver là, commençons avec des exemples simples. Pour cela, réutilisons nos connaissances sur les booléens et les conditions VRAI/FAUX que nous avons abordés dans un article précédent.


 

Combiner deux fonctions SI

Pour faciliter la compréhension des prochains, commençons par 'traduire' la formule de l'exemple précédent peut se traduire ainsi :
Si Ventes >= Objectifs
Alors Bravo
Sinon Encore un effort

Maintenant, supposons que nous voulons ajouter une comparaison avec les ventes de l'année dernière, comme ceci
Si Ventes N >= Objectifs
Alors Bravo
Sinon Si Ventes N > Ventes N-1
Alors En progression
Sinon Insuffisant

Pour le retranscrire dans Excel, il faut imbriquer une deuxième fonction SI à l'intérieur de la première.

Exemple fonctions imbriquees SI

La deuxième fonction se rédige comme la première. La différence c'est qu'elle n'est évaluée que si la condition du premier SI renvoie FAUX. En cas de VRAI, c'est toujours la première phrase qui est renvoyée.

 

Combiner aussi les conditions

On peut imaginer d'autres situations où nous devons vérifier les conditions simultanément, et non successivement. Regardons l'exemple ci-dessous :
Si Ventes Pdt 1 >= Objectifs Pdt 1 ET Ventes Pdt 2 >= Objectifs Pdt 2
Alors Bravo, 2 objectifs atteints
Sinon Si Ventes Pdt 1 >= Objectifs Pdt 1 OU Ventes Pdt 2 >= Objectifs Pdt 2
Alors Déjà 1 objectif atteint
Sinon Insuffisant

Excel nous propose deux fonctions ET et OU dont le but est exactement celui-là. La seule subtilité concerne la rédaction de la formule. Dans Excel, les noms de fonctions sont toujours placés au début, il faut donc écrire ET( condition 1 ; condition 2 ; condition 3 ...) ce qui est assez peu naturel. De même pour la fonction OU.
Dans les deux cas, 2 conditions minimum sont obligatoires. Et au maximum ? 255, on a de la marge…

Voilà la retranscription Excel du raisonnement précédent :

Exemple conditions fonctions imbriquees ET OU

Les fonctions ET et OU renvoient donc elles aussi un résultat booléen. ET renvoie VRAI si toutes les conditions à l'intérieur sont vraies, OU renvoie VRAI si au moins une des conditions est vraie.
Il existe une troisième fonction pour combiner les booléens, la fonction OUX, ou exclusif. Elle renvoie VRAI si un et un seul des arguments est VRAI. Dès que 2 arguments ou plus sont vrais, OUX donne le résultat FAUX.
Ci-dessous un résumé du comportement des trois fonctions avec 2 conditions.

Resultat combinaison booleens ET OU OUX
 

Lire et déchiffrer des formules complexes

Les exemples que nous avons étudiés étaient relativement simples et comportaient la fonction SI dont la syntaxe suit un cheminement logique.
Mais pour lire des formules complexes avec plusieurs niveaux imbriqués, les lire de façon linéaire n'est pas le meilleur moyen de les comprendre.
Il est préférable de commencer par analyser la fonction qui est la plus imbriquée à l'intérieur de la fonction puis de remonter pour terminer par la dernière fonction qui englobe les autres.

Pour nous en convaincre, nous allons utiliser une astuce pratique pour évaluer les formules pas à pas. Et nous allons reprendre le dernier exemple avec les conditions combinées.

Exemple formule fonctions imbriquees resolution pas a pas

Pour résoudre petit à petit cette formule, on en sélectionne une partie dans la barre de formules puis on appuie sur la touche F9.

Evaluation formule pas a pas touche F9

Avec de longues formules, on peut utiliser cette astuce pour trouver le résultat d'une portion compliquée ou résoudre entièrement la formule étape par étape.

Evaluation formule debogage correction erreurs
 

La gestion des parenthèses

Toutes les fonctions Excel prennent des parenthèses qui encadrent leur contenu. La première parenthèse, ouvrante, marque le début de la fonction, la deuxième, fermante, signale la fin de la fonction. On ne les place donc pas au hasard.
C'est assez simple avec des formules qui ne contiennent qu'une fonction. Mais avec des fonctions imbriquées, il s'agit d'être vigilant sur leurs emplacements. Une parenthèse ouverte dès le début de la formule peut n'être refermée qu'à la fin.

Si nous reprenons notre dernier exemple, la toute dernière parenthèse correspond à celle ouverte avec le premier SI.

Correspondance parentheses fonctions imbriquees

Le deuxième SI n'est traité que si la condition du premier n'est pas vérifiée. Il devra donc être inclus en totalité au sein de la première fonction. Il ne faut donc pas fermer la parenthèse du 1er SI avant d'avoir ouvert celle du 2ème.

Pour la fermeture de ces parenthèses, ça fonctionne comme en mathématiques. Une parenthèse fermante correspond toujours à la dernière qui a été ouverte, comme dans cet exemple.

Equivalence jeux parentheses mathematiques

Excel facilite le repérage des parenthèses en leur attribuant des couleurs en fonction de leur ordre d'ouverture. Les parenthèses ouvrantes et fermantes sont affichées dans la même couleur. Et lorsqu'on ferme une parenthèse, il la place en gras avec celle d'ouverture.

Couleurs gras parentheses formule Excel

Si nous oublions de fermer certaines parenthèses, Excel nous le signale.
Dans certains cas, il nous proposera lui-même une correction. Il s'agit souvent des cas les plus simples, par exemple avec une parenthèse manquante à la fin.

Erreur parentheses proposition correction Excel

Dans la plupart des cas, il notera juste une erreur dans la fonction et nous demandera de la corriger par nous-mêmes. Ci-dessous, il manque une parenthèse fermante à la fonction OU.

Message erreur parentheses

Pour ne pas nous tromper, nous devons toujours garder la logique de la formule à l'esprit en l'écrivant. Les repères visuels d'Excel sont précieux surtout si nous imbriquons 3, 4 fonctions ou même plus encore.
Il faut prendre le temps de bien analyser, une parenthèse fermée au mauvais endroit peut entrainer un mauvais calcul de la formule.

Pour compléter vos connaissances sur les formules, vous pouvez consulter les articles suivants :

N'hésitez pas à laisser vos commentaires ou questions ci-dessous.

 

Abonnez-vous à la newsletter

Illustration couverture ebook 100 trucs et astuces pour Excel-ler au travail

Et recevez gratuitement l'ebook 100 trucs et astuces pour Excel-ler au travail (5€ sur Amazon)

Votre adresse mail ne sera pas communiquée et vous pourrez vous désabonner à tout moment.

Taggé , , , .Lien pour marque-pages : Permaliens.

Laisser un commentaire