Le guide du débutant sur les fonctions imbriquées dans Excel

L'imbrication dans Microsoft Excel implique d'utiliser une fonction comme argument pour une autre, vous permettant d'effectuer plusieurs calculs en même temps. Cela vous évite d'avoir à utiliser des colonnes d'assistance ou à écrire diverses formules dans différentes cellules, gardant ainsi votre feuille de calcul plus ordonnée et plus efficace.

Pour suivre la lecture de ce guide, téléchargez une copie gratuite du classeur Excel utilisé dans les exemples. Après avoir cliqué sur le lien, vous trouverez le bouton de téléchargement dans le coin supérieur droit de votre écran et lorsque vous ouvrez le fichier, vous pouvez accéder à chaque exemple dans un onglet de feuille de calcul distinct.

Définitions clés

Ce guide utilise les trois termes suivants, alors prenez un moment pour comprendre ce qu'ils signifient :

• Formule: Combinaison de fonctions, de références de cellules, de valeurs, d'opérateurs et de noms qui, lorsqu'ils sont utilisés ensemble après le signe égal (=), créent une nouvelle valeur.
• Fonction: Formule prédéfinie qui effectue un calcul en utilisant des arguments dans un ordre particulier.
• Argument: Une entrée qui fournit des informations à une fonction.

Fonctions d'imbrication dans IF dans Excel

Si vous débutez avec les fonctions imbriquées dans Excel, vous devez commencer par utiliser la fonction IF :

=IF(a,b,c)

où argument un est un test logique, un argument b est la valeur à renvoyer si le test est satisfait, et l'argument c est la valeur à renvoyer si le test n'est pas rempli. Pour le test logique en argumentation unvous pouvez imbriquer une autre fonction.

Tous les exemples de ce guide utilisent des plages régulières et des références de cellules directes afin que les formules soient plus faciles à comprendre et l'imbrication plus facile à voir.

Dans cet exemple, les bonus sont payés si le bénéfice total de l'équipe (la somme des valeurs des cellules B2 à B6) dépasse le seuil (la valeur de la cellule E1). Si le seuil est atteint, vous souhaitez que la cellule E2 indique Ouiet si ce n'est pas le cas, vous voulez qu'il soit lu Non. La formule pour y parvenir est la suivante :

=IF(SUM(B2:B6)>=E1,"Yes","No")

Alors, comment ça marche ?

Lors de la création ou de la lecture de formules contenant des fonctions imbriquées, l’essentiel est de commencer par le centre et de progresser vers l’extérieur. Au cœur de cette formule se trouve la somme des valeurs des cellules B2 à B6, c'est donc par là que vous devez commencer :

=SUM(B2:B6)

Ensuite, vous souhaitez qu'Excel évalue si cette somme est égale ou supérieure à la valeur de la cellule E1. Par défaut, Excel renvoie VRAI si l'évaluation est positive, ou FAUX s'il est négatif, également appelé valeurs booléennes :

=SUM(B2:B6)>=E1

Désormais, plutôt que de renvoyer des valeurs booléennes, vous souhaitez qu'Excel renvoie Oui ou Non. Ainsi, vous devez envelopper la formule SOMME dans la fonction SI, afin que le calcul de la somme soit le test logique (argument un) :

=IF(SUM(B2:B6)>=E1,"Yes","No")

La clé pour travailler avec des fonctions imbriquées est de comprendre l’ordre dans lequel les éléments sont calculés. Comme vous pouvez le voir dans l'exemple ci-dessus, les fonctions imbriquées sont traitées en premier, et leurs résultats alimentent les parties externes de la formule. Ainsi, si vous créez une formule complexe avec de nombreuses fonctions imbriquées, travaillez de l'intérieur vers l'extérieur.

Vous n'êtes pas obligé d'utiliser la fonction SUM dans IF. En fait, vous pouvez imbriquer à peu près n’importe quelle fonction comme test logique. Ici, la fonction MOYENNE calcule la moyenne des valeurs des cellules B2 à F2, et si le résultat est supérieur à 5 000, la formule renvoie Oui (ou Non si ce n'est pas le cas) :

=IF(AVERAGE(B2:F2)>5000,"Yes","No")

Vous pouvez ensuite double-cliquer sur la poignée de remplissage dans le coin inférieur droit de la cellule G2 pour étendre la formule aux cellules restantes de la colonne G.

Cette fois, la fonction OU ouvre deux options pour le test logique de la fonction SI. En d’autres termes, si la valeur de B2 est Ordonné ou En transitla formule renvoie En cours. Sinon, ça revient Complété:

=IF(OR(B2="Ordered",B2="In transit"),"In progress","Completed")

Encore une fois, double-cliquez sur la poignée de remplissage pour appliquer la formule aux lignes restantes de l'ensemble de données.

Autres exemples d'imbrication dans Excel

Une fois que vous commencez à expérimenter d’autres fonctions, le pouvoir de l’imbrication devient rapidement évident.

FILTRE emboîtable à l’intérieur UNIQUE

Dans ce cas, votre objectif est d'extraire les noms de tous ceux qui ont remporté un prix à New York.

Autrement dit, vous souhaitez utiliser la fonction FILTER pour reproduire une version filtrée de la colonne C. Pour cela, tapez :

=FILTER(C2:C15,B2:B15=F1)

dans la cellule E2 et appuyez sur Entrée. C2:C15 est le tableau que vous souhaitez filtrer (noms des personnes), et B2:B15=F1 indique à Excel d'inclure uniquement les valeurs pour lesquelles la valeur correspondante dans la colonne B est égale à la valeur dans la cellule F1 (New York).

Cependant, comme vous pouvez le voir sur la capture d'écran ci-dessus, l'un des noms est répété car cette personne a gagné deux fois à New York. Vous devez donc imbriquer la fonction FILTER dans UNIQUE pour renvoyer chaque nom une seule fois :

=UNIQUE(FILTER(C2:C15,B2:B15=F1))

La clé de ce processus est de forcer Excel à effectuer d’abord le calcul principal, puis d’utiliser le résultat de cette formule pour en piloter un autre.

Les fonctions imbriquées ne nécessitent pas de signes égal (=). Le signe égal n'est requis qu'au début pour indiquer à Excel que vous saisissez une formule dans une cellule.

Imbrication de XMATCH dans l'INDEX

L'une des combinaisons de fonctions les plus populaires d'Excel est INDEX et XMATCH, qui fonctionnent ensemble pour récupérer et extraire une valeur d'un ensemble de données.

Ici, après avoir entré l'identifiant d'un joueur dans la cellule F2, vous souhaitez renvoyer son score dans la cellule G2.

Pour ce faire, vous pouvez utiliser la fonction INDEX :

=INDEX(a,b,c)

où un est le tableau à analyser, b est le numéro de ligne, et c est le numéro de colonne.

Pour argumenter bétant donné que la ligne change en fonction de l'ID de joueur que vous entrez dans la cellule F2, vous devez imbriquer la fonction XMATCH, qui recherche un élément spécifié et renvoie sa position relative. Et comme la meilleure approche pour l’imbrication dans Excel consiste à commencer par les fonctions imbriquées, c’est ici que vous devriez commencer. Alors, dans la cellule G2, tapez :

=XMATCH(F2,A2:A6)

Lorsque vous appuyez sur Entrée, cette formule renvoie correctement 4car le joueur D est sur la quatrième rangée de la plage A2 à A6.

Désormais, cette formule XMATCH peut servir d'argument b de la fonction INDEX, vous devez donc maintenant envelopper le reste des arguments INDEX autour de XMATCH :

=INDEX(a,XMATCH(F2,A2:A6),c)

Argument c va être 4 car vous souhaitez renvoyer la valeur de la quatrième colonne (total) :

=INDEX(a,XMATCH(F2,A2:A6),4)

Alors maintenant, il suffit d'entrer un argument unet comme c'est tout le tableau à analyser, il s'agira des cellules A2 à D6 :

=INDEX(A2:D6,XMATCH(F2,A2:A6),4)

Désormais, lorsque vous entrez un autre joueur dans la cellule F2, le XMATCH imbriqué le recherche dans les cellules A2 à A6 et renvoie sa position relative pour l'argument. b de la fonction INDEX.

Pour rendre une formule complexe avec des fonctions imbriquées plus facile à comprendre et à analyser, divisez la formule sur plusieurs lignes dans la barre de formule à l'aide de Alt+Entrée. Après cela, cliquez et faites glisser le bas de la barre de formule vers le bas afin que vous puissiez voir toutes les lignes de la formule en même temps.

Si vous ne souhaitez pas toujours renvoyer le total dans la quatrième colonne, vous pouvez aller plus loin et imbriquer une autre formule XMATCH pour déterminer le numéro de colonne :

=INDEX(A1:D6,XMATCH(F2,A1:A6),XMATCH(G2,A1:D1))

Dans ce cas, pour argument cXMATCH prend la valeur dans la cellule G5, la recherche dans les cellules A1 à D1 et renvoie sa position relative.

Imbrication MAX à l'intérieur MOYENNE

Supposons que vous disposiez de cette feuille de calcul, qui contient les scores de cinq joueurs sur trois jeux, et que votre objectif soit de trouver la moyenne des scores les plus élevés dans chacun.

En d’autres termes, vous avez d’abord besoin d’Excel pour calculer les scores maximum dans les cellules B2 à B6, C2 à C6 et D2 à D6, respectivement, avant de faire la moyenne du résultat. Ainsi, la fonction MAX doit être imbriquée dans la fonction MOYENNE.

Tout d'abord, configurez les trois imbrications MAX, en gardant à l'esprit que les arguments de la fonction MOYENNE sont séparés par une virgule :

MAX(B2:B6),MAX(C2:C6),MAX(D2:D6)

Maintenant, avec le mode Entrée toujours activé, enveloppez la fonction MOYENNE autour de ces formules MAX imbriquées :

=AVERAGE(MAX(B2:B6),MAX(C2:C6),MAX(D2:D6))

Avant d'imbriquer, prenez un moment pour vérifier que les arguments d'une fonction n'ont pas la capacité de faire la même chose. Par exemple, vous n'avez pas besoin d'imbriquer XLOOKUP dans IFERROR, car XLOOKUP possède son propre argument de gestion des erreurs.

Fonctions d'imbrication à l'intérieur d'elles-mêmes

Jusqu’à présent, tous les exemples ont imbriqué une fonction dans une autre. Cependant, vous devrez parfois imbriquer une fonction en elle-même. Déroutant ? Laissez-moi vous montrer comment cela fonctionne en utilisant la fonction IF.

Dans cette feuille de calcul, la prime d'un employé est déterminée par le bénéfice qu'il réalise. Plus précisément, les bénéfices supérieurs à 25 000 $ donnent droit à un bonus de 10 %, les bénéfices supérieurs à 20 000 $ donnent droit à un bonus de 5 %, les bénéfices supérieurs à 10 000 $ donnent droit à un bonus de 2 % et les bénéfices supérieurs à 10 000 $ donnent droit à un bonus de 1 %.

Assurez-vous que le format numérique Pourcentage est appliqué à toutes les cellules concernées afin qu'elles apparaissent sous forme de valeurs de pourcentage plutôt que de nombres décimaux.

Dans ce cas, vous devez imbriquer IF pour créer un système d'évaluation à plusieurs niveaux, du plus élevé au plus bas. En effet, les fonctions IF imbriquées sont évaluées de gauche à droite, en s'arrêtant à la première condition vraie. Alors, dans la cellule C2, tapez :

=IF(B2>25000,10%

mais n'appuyez pas encore sur Entrée. Cette première section indique à Excel d'évaluer la cellule B2 et si elle est supérieure à 25 000 $ (en d'autres termes, s'il évalue le test logique comme VRAI), retour 10%. Cependant, vous devez indiquer à Excel quoi faire s’il renvoie FALSE. Dans ce cas, vous souhaitez vérifier si la valeur de la cellule B2 est supérieure à la valeur de deuxième niveau, 20 000 $, en renvoyant 5 % si tel est le cas :

=IF(B2>25000,10%,IF(B2>20000,5%

Si cette fonction IF imbriquée renvoie FALSE, vous souhaitez qu'Excel passe au niveau suivant, vous devez donc imbriquer une autre fonction IF :

=IF(B2>25000,10%,IF(B2>20000,5%,IF(B2>15000,2%

Maintenant, vous devez créer le niveau le plus bas via une autre fonction IF imbriquée :

=IF(B2>25000,10%,IF(B2>20000,5%,IF(B2>15000,2%,IF(B2>10000,1%

Puisqu'il n'y a plus de niveaux, vous devez indiquer à Excel ce qu'il doit renvoyer si aucune des fonctions IF imbriquées n'est évaluée comme VRAI. Dans ce cas, vous souhaitez revenir 0%:

=IF(B2>25000,10%,IF(B2>20000,5%,IF(B2>15000,2%,IF(B2>10000,1%,0%

Maintenant que tous les niveaux sont créés via des fonctions IF imbriquées, vous pouvez fermer les parenthèses. Il y a quatre parenthèses ouvrantes, vous devez donc saisir quatre parenthèses fermantes avant d'appuyer sur Entrée :

=IF(B2>25000,10%,IF(B2>20000,5%,IF(B2>15000,2%,IF(B2>10000,1%,0%))))

Lorsque vous tapez les parenthèses fermantes, Excel met temporairement en surbrillance les parenthèses ouvrantes auxquelles elles correspondent, et les parenthèses sont codées par couleur, ce qui signifie que vous pouvez rapidement vérifier que toutes les paires sont complètes.

Vous pouvez maintenant double-cliquer sur la poignée de remplissage pour appliquer la formule aux cellules restantes de la colonne C.

Dans Excel 2007 et versions ultérieures (y compris Excel pour Microsoft 365), vous pouvez imbriquer jusqu'à 64 fonctions IF. Dans les anciennes versions (Excel 2003 ou antérieure), vous pouvez en imbriquer jusqu'à sept.

---

Les fonctions d'imbrication dans Excel vous permettent de profiter des capacités de plusieurs fonctions en même temps. Plus vous tirerez le meilleur parti de cette structure de formule, plus vous commencerez à découvrir quelles combinaisons de fonctions fonctionnent bien pour accélérer le flux de travail de votre feuille de calcul.