La fonction EQUIV

Dans l’article suivant de cette formation Excel, vous allez découvrir l’utilité de la fonction EQUIV et comment l’utiliser.
La fonction EQUIV

Présentation de La fonction EQUIV

La fonction EQUIV (Match en anglais) renvoie la position d’un élément recherché dans une plage de cellules.

Syntaxe de la fonction EQUIV :

EQUIV(valeur_cherchée, matrice_recherche, [type])

Les trois arguments de la fonction EQUIV  sont donc :
  • valeur_cherchée : c’est la valeur dont on cherche la position dans une plage de cellules. Elle peut être de type texte, numérique ou logique.
  • matrice_recherche : est la référence de la plage de cellules dans laquelle on cherche la position de l’élément recherché.
  • Type : c’est un nombre -1, 0 ou 1 :
    • Type = 1 : La fonction EQUIV recherche la valeur la plus élevée qui est inférieure ou égale à la valeur_cherchée. Dans ce cas la matrice_recherche doit être triée en ordre croissant.
    • Type= 0 : La fonction EQUIV retourne la valeur exacte correspondant à la valeur_cherchée.
    • Type= -1 : La fonction EQUIV recherche la valeur la plus petite qui est supérieure ou égale à la valeur_cherchée. La matrice_recherche doit être triée en ordre décroissant.

Regardez cette courte vidéo pour découvrir comment utiliser la fonction EQUIV :

Exemples d’utilisation de la fonction EQUIV

Le tableau suivant sera utilisé dans les exemples qui suivent :

Exemple 1 : chercher la position exacte d’un élément verticalement

  • Pour trouver la position relative à la valeur « Fluo (230V) » dans la plage de cellules A2:A7, tapez la formule suivante : =EQUIV("fluo (230v)";A2:A7;0)
  • Excel renvoie le numéro 4
Fonction EQUIV -recherche dans une colonne-

Note : la valeur cherchée est mise entre guillemets car elle est de type texte. Vous pouvez également référencer une cellule contenant cette valeur cherchée, dans ce cas vous n’aurez pas besoin d’utiliser les guillemets.


Cellule référencée dans l'argument valeur cherchée de la fonction EQUIV

L’argument Type est égal à "0" à fin de trouver la position exacte de la valeur cherchée « fluo (230v) ».

Exemple 2: chercher la position exacte d’un élément horizontalement

  • Pour trouver la position relative à la valeur « Néon (230V) » dans la plage de cellules (Ligne) A6:C6, tapez la formule suivante : =EQUIV("Néon (230V)";A6:C6;0)
  • Excel affiche le numéro 1
Fonction EQUIV -recherche dans une ligne-

Exemple 3: Utilisation de l’argument Type=1 dans la fonction EQUIV

Dans la colonne « Puis. En Watt », nous voulons chercher la position de la valeur « 75 ».
Et puisque cette valeur n’existe pas dans la plage de cellules B2:B7, nous allons dès lors chercher la position d’une valeur proche d’elle mais inférieure à 75.
Dans ce cas nous allons définir pour l’argument Type la valeur 1.
  • Pour ce faire, veuillez tout d’abord mettre en ordre croissant votre tableau pour obtenir le bon résultat.
  • Puis tapez la formule suivante :  =EQUIV(75;B2:B7;1)
  • Excel vous renvoie 5 qui représente la position de la valeur 50, cette dernière est la valeur la plus élevée et inférieure à 75.
La fonction EQUIV argument Type égal à 1

Exemple 4 : Utilisation de l’argument Type = -1 dans la fonction EQUIV

Dans l’exemple suivant, nous allons demander à Excel de nous renvoyer la position de la valeur 35 dans la plage de cellules B2:B7, ou bien la position d’une autre valeur qui existe dans cette plage et qui est supérieure à 35  au cas où Excel ne trouve pas la valeur cherchée.
  • La première des choses à faire donc est de mettre en ordre décroissant le tableau de données, puis d’utiliser la fonction EQUIV en définissant pour l’argument Type la valeur -1 :
  • Voici la formule à utiliser :
=EQUIV(35;B2:B7;-1)
  • Le résultat renvoyé est 3.
La fonction EQUIV argument Type égal à -1

Vous remarquez que trois valeurs qui sont supérieures à 35 : vous avez 40, 50 et 300. La plus petite de ces valeurs est 40 et sa position dans la plage de cellules B2:B7 est donc 3.

Note : Si la fonction EQUIV ne trouve pas de valeur équivalente, elle renvoie la valeur d’erreur #N/A.

La fonction INDEX

L’article présent de la formation Excel vous permet de découvrir le rôle de la fonction INDEX et comment l’utiliser à travers 6 exemples.
La fonction INDEX

Présentation de la fonction INDEX

La fonction INDEX permet de renvoyer une valeur qui se trouve dans une cellule, d’un tableau ou d’une plage de cellules, à l ‘intersection d’une ligne et d’une colonne spécifiées.

Syntaxes de la fonction INDEX

Excel fournit deux syntaxes de la fonction INDEX :

Forme matricielle et référentielle de la fonction INDEX

  • La première syntaxe :

INDEX(matrice, no_lig, [no_col]) : c’est la syntaxe de la forme matricielle de la fonction INDEX, et c’est la plus utilisée. Cette forme permet de renvoyer une valeur d’une plage de cellules précisée.

Comme vous le voyez, trois arguments qui composent la fonction INDEX :
  1. matrice : est le tableau ou la plage de cellules contenant la valeur à renvoyer.
  2. no_lig : le numéro de la ligne du tableau ou de la plage de cellules où se trouve la valeur à renvoyer.
  3. no_col : le numéro de la colonne du tableau ou de la plage de cellules contenant la valeur à renvoyer.

  • La deuxième syntaxe

INDEX(réf, no_lig, [no_col], [no_zone]): c’est la syntaxe de la forme référentielle qui permet de renvoyer une valeur parmi plusieurs plages de cellules spécifiées.

Pour cette forme référentielle, vous remarquez qu’il existe 4 arguments :
  • réf : indique la référence de la plage de cellules sélectionnée ou les références des plages de cellules spécifiées.
  • no_lig et no_col : sont déjà définis dans la première syntaxe.
  • no_zone : est le numéro d’ordre de la plage de cellules mentionnée dans l’argument réf par rapport aux autres plages de cellules référencées dans le même argument et qui contient la valeur à renvoyer. Par exemple 1 indique à Excel de renvoyer la valeur se trouvant dans la première plage de cellules et à l’intersection de la ligne et de la colonne spécifiées par les deux arguments no_lig et no_col .

Regardez à présent cette courte vidéo pour avoir une idée brève sur comment utiliser la fonction INDEX, puis passez ensuite aux exemples qui vont suivre:

Exemples d’utilisation de la fonction INDEX

Les exemples traités ci-dessous seront appliqués sur le tableau suivant concernant les prix des différents types d’ampoules:

Prix des ampoules
Liste des prix des ampoules


Exemple 1 : Un tableau de plusieurs lignes et colonnes

  • Pour trouver la valeur se trouvant dans la 3ème ligne et la deuxième colonne de la plage de cellules A2:C7, voici la formule utilisée: =INDEX(A2:C7;3;2)
  • Excel renvoie 300
Insérer la fonction INDEX


Exemple 2 : Un tableau de plusieurs lignes et d’une seule colonne

  • Pour trouver la valeur se trouvant dans la 4ème ligne de la plage de cellules A2:A7, tapez : =INDEX(A2:A7;4)
  • Dans ce cas et puisque la plage de cellules A2:A7 ne contient qu’une seule colonne, la spécification du numéro de colonne n’est pas obligatoire.
  • La fonction INDEX renvoie « Fluo (230V) »
INDEX- Tableau d'une seule colonne


Exemple 3 : Un tableau de plusieurs colonnes et d’une seule ligne

  • Pour afficher la valeur se trouvant dans la 3ème colonne du tableau A2:C2, saisissez : =INDEX(A2:C2;;3)
  • Ce tableau ou cette plage de cellules spécifiée se compose d’une seule ligne, nous avons donc laissé l’argument no_lig vide.
  • La valeur renvoyée est « 1 »
Fonction INDEX - Tableau avec une seule ligne



Note :
Si vous tapez un numéro de ligne (de colonne) qui se trouve en dehors de la plage sélectionnée, Excel renvoie une erreur REF !.

Fonction INDEX - Erreur REF


Exemple 4 : l’argument no_lig égal à 0

  • Lorsque l’argument no_lig est égal à 0, la fonction INDEX renvoie toutes les valeurs de la colonne spécifiée.
  • Dans l’exemple suivant, sélectionnez la plage de cellules E2:E7 puis tapez dans la barre de formule la formule suivante : =INDEX($A$2:$A$7;0)
  • Ensuite validez par Ctrl+Maj+Entrée et non par Entrée pour créer une formule matricielle.
no_lig égal à 0 dans la fonction INDEX



Cette formule aura plus d’importance quand vous l’utiliserez pour créer une liste déroulante de valeurs.
  • Pour cet effet, sélectionnez une cellule de votre feuille de calcul et cliquez sous l’onglet Données sur Validation de données.
L'outil Validation de données

  • Sous Autoriser, sélectionnez Liste et dans la zone Source saisissez la formule précédente =INDEX($A$2:$A$7;0).
Créer une liste dans Validation de données

  • Cliquez sur OK pour sortir de cette fenêtre.
  • Une flèche apparaît à côté de la cellule, cliquez sur cette flèche pour faire afficher la liste des valeurs se trouvant dans la colonne spécifiée par la fonction INDEX. (ici c’est la colonne Type)
Liste déroulante Excel

Exemple 5 : l’argument no_col égal à 0


Même principe aussi, sauf que dans ce cas, se sont toutes les valeurs de la ligne spécifiée par la fonction INDEX qui seront renvoyées.
  • Sélectionnez à présent la plage de cellules E2:G2 puis tapez dans la barre de formule : =INDEX($A$2:$C$7;3;0)
  • Validez par Ctrl+Maj+Entrée.
  • Excel affiche les valeurs contenues dans la troisième ligne de la plage de cellules A2 :C7.
no_col égal à 0 dans la fonction INDEX


Et comme vous l’avez fait pour créer une liste déroulante dans l’exemple précédent, suivez les mêmes étapes et collez cette fois =INDEX($A$2:$C$7;3;0) dans la zone Source.

Liste créée en utilisant la fonction INDEX


Exemple 6 : Utilisation de la forme référentielle de la fonction INDEX

Cette forme est peu utilisée par les utilisateurs d’Excel, cependant on va voir comment elle fonctionne :
  • Dans cet exemple, nous avons 4 tableaux de notes pour 4 étudiants. Notre but est d’utiliser une seule fonction INDEX incluant les références de ces 4 tableaux pour trouver la note de la matière C++ pour l’étudiant 4.
4 plages de cellules


  • Sélectionnez alors une cellule et collez la formule suivante :

=INDEX((A4:B8;E4:F8;A13:B17;E13:F17);3;2;4)

  • Remarquez que dans l’argument réf, nous avons écrit les références des 4 plages de cellules séparées par des points-virgules et que nous les avons mises entre parenthèses.
  • La matière C++ se trouve dans la troisième ligne dans toutes les plages de cellules.
  • La note correspondante à cette matière se trouve dans la deuxième colonne.
  • L’argument no_zone est égal à 4. En effet, la plage de cellules contenant les notes de l’étudiant E13:F17 est  placée en quatrième position dans l’argument réf.
  • La fonction INDEX renvoie donc la note 13.
Fonction INDEX référentielle


Note : les plages de cellules spécifiées dans l’argument réf de la fonction INDEX doivent exister dans la même feuille, si non, INDEX renvoie l’erreur #CNUM!

Vous pouvez rendre cette formule dynamique, par exemple :
  • Choisissez deux cellules, une pour afficher le numéro de la zone et l’autre pour afficher le numéro de la matière.
  • Puis saisissez les références de ces deux cellules dans les deux arguments no_zone et no_lig comme suit :
Fonction INDEX dynamique

  • Il ne vous reste maintenant qu’à taper le numéro de la plage de cellules et le numéro de la matière pour que la fonction INDEX vous renvoie le résultat correspondant. Regardez l'illustration suivante :
Exemple Fonction INDEX dynamique


La fonction cachée DATEDIF : Calculer la différence entre deux dates en jours, mois et années

L’article d’aujourd’hui dans cette Formation Excel vous permet de savoir comment utiliser la fonction DATEDIF pour calculer par exemple combien il vous reste d’années, de mois et de jours pour un rendez-vous pris.

Fonction DATEDIF

Présentation de la fonction DATEDIF

Si vous essayez d’insérer la fonction DATEDIF en utlisiant les méthodes connues d’insertion des fonctions Excel, vous n’allez pas la trouver dans la bibliothèque des fonctions, cependant elle fonctionne parfaitement !

Cette fonction fait partie donc de la catégorie des fonctions dites « cachées ».

Alors, pour l'insérer, vous devez tout simplement connaitre sa syntaxe et puis la saisir manuellement. 
Voici donc la syntaxe de la fonction DATEDIF :
DATEDIF(date_début,date_fin,unité)

La fonction DATEDIF contient, et comme vous le voyez, 3 arguments :
Les deux arguments date_début et date_fin sont très clairs, quant au dernier argument unité, c'est un paramètre de type texte qui peut être :
  • « y » pour calculer la différence entre les deux dates en années. Y est la première lettre de Year (année en anglais)
  • « m » pour calculer la différence entre les deux dates en mois. m est la première lettre de Month (mois en anglais)
  • « d » pour calculer la différence entre les deux dates en jours. d est la première lettre de Day (jour en anglais).
  • "ym" : renvoie la différence en mois, en ignorant les jours et les années des deux dates.
  •  "yd"  : affiche la différence en jours. Les années sont ignorées.
  •  "md" : renvoie la différence en jours. Les mois et les années des dates sont ignorés.
Note: Le paramètre unité doit être écrit entre deux guillemets. 



Exemple 1 : Calculer le nombre de jours

Calculer le nombre de jours DATEDIF

Exemple 2 : Calculer le nombre de jours en ignorant les années

Calculer le nombre de jours en ignorant les années DATEDIF

Exemple 3 : Calculer le nombre de jours en ignorant les années et les mois

Calculer le nombre de jours en ignorant les années et les mois DATEDIF

Exemple 4 : Calculer le nombre de mois

Calculer le nombre de mois DATEDIF

Exemple 5 : Calculer le nombre de mois en ignorant les jours et les années des deux dates

Calculer le nombre de mois en ignorant les jours et les années des deux dates DATEDIF

Exemple 6 : Calculer le nombre d’années

Calculer le nombre d’années

Note: Essayez de modifier les dates pour voir les autres résultats qui peuvent être obtenus. et n'oubliez pas que la date de fin doit être supérieure à la date de début :).

Calculer le nombre de jours, de mois et d’années restant pour une date d'un rendez-vous pris

L’exemple ci-dessous contient deux dates :

Fonction AUJOURDHUI

  • Date d’aujourd’hui: qui affiche la date en cours. Et pour obtenir cet affichage nous avons inséré la fonction AUJOURDHUI().
  • Date du Rendez-Vous (RDV) : par exemple nous aurons un rendez-vous le 05/09/2018.
  • Devant la cellule « il vous reste », nous allons calculer la différence entre les deux dates en jours, en mois et en années à la fois. Ce calcul sera mis à jour automatiquement lors de l’ouverture de votre fichier Excel.

Voici les étapes à suivre :
  • Tapez =DATEDIF(A2;B2;"y")& " Année" pour calculer le nombre d’années en premier.
DATEDIF calcul années restantes

  • Ajoutez ensuite ceci : & " , " &DATEDIF(A2;B2;"ym")& " Mois" pour calculer les mois:
DATEDIF calcul années et mois restants

  • Enfin tapez : & " et " & DATEDIF(A2;B2;"md")& " Jours" pour calculer les jours:
DATEDIF calcul de jours restants

  • Vous pouvez améliorer cette formule pour gérer les pluriels et les singuliers surtout pour les années en intégrant la fonction Si comme suit :

Integrer la fonction SI dans DATEDIF

  • Ou bien encore, n’affichez le nombre d’années que s’il est différent de 0 :
DATEDIF Gérer le nul

De la même manière, vous pouvez modifier cette formule en y intégrant d'autres fonctions si vous aimez obtenir d'autres résultats ou bien créer vos propres formules en utilisant DATEDIF pour calculer par exemple un âge en années, mois et jours cumulés, le nombre d'années de service d’une personne...


Les deux fonctions ET et OU imbriquées dans la fonction SI

L’article présent de la formation Excel vous montre comment imbriquer la fonction ET ou la fonction OU dans la fonction SI.

Les deux fonctions ET et OU imbriquées dans la fonction SI


Les deux fonctions Et et OU font partie de la catégorie des fonctions logiques d’Excel. Leur fonction est de tester plusieurs conditions pour renvoyer des valeurs logiques VRAI ou FAUX.

Cependant l’utilité de ces deux fonctions s’avèrent très importante lorsque vous les imbriquez dans une autre fonction telle que la fonction SI.

Essayons tout d’abord de voir comment peut-on utiliser les deux fonctions Et et OU, puis découvrons comment les imbriquer dans la fonction SI.

La fonction ET

La fonction ET renvoie VRAI si tous les arguments sont VRAI et renvoie FAUX si au moins l'un des arguments est FAUX.
  • Sa syntaxe est la suivante :

ET(valeur_logique1;[valeur_logique2];...)

Les arguments valeur_logique(1,2,3…) représentent les conditions à tester. On peut tester jusqu’à 255 conditions.

Exemple d’utilisation

Dans le tableau suivant, on veut donner une remise sur l’achat des Housses mais à condition que le smartphone acheté soit un iPhone.

Tableau d'achat de smartphones


  • Commençons par tester en premier les valeurs contenues dans les deux cellules A2 et B2 si elles répondent à notre condition ou non.
  • Dans la cellule F2 entrez la formule suivante =ET(A2="iphone";B2="housse")
  • Le résultat renvoyé est VRAI.


Exemple d'utilisation de la fonction ET


  • Faites copier-coller dans les autres cellules en bas.
  • Voici ce que vous allez obtenir


deuxième exemple d'utilisation de la fonction ET


La fonction OU

La fonction OU renvoie la valeur VRAI si une des conditions testées est VRAI et la valeur FAUX si toutes les conditions sont fausses.
  • Sa syntaxe prend la forme suivante :

OU(valeur_logique1;[valeur_logique2],...)

Comme la fonction ET, la fonction OU peut tester jusqu’à 255 conditions.

Exemple d’utilisation :

Suivez cette courte vidéo pour avoir une idée rapide sur comment utiliser la fonction OU :
  • En travaillant sur l’exemple précédent, on va maintenant offrir une remise sur l’achat des accessoires SAMSUNG ou si la quantité d’achat dépasse 150 quelque soit le smartphone acheté.
  • Pour cela, sélectionnez la cellule F2 et entrez la formule suivante : =OU(A2="Samsung";C2>150)


Exemple d'utilisation de la fonction OU


  • Excel affiche FAUX car aucune condition n’est remplie.
  • Faites copier-coller de la formule dans les autres cellules en bas, et voici le résultat que vous obtiendrez :


Deuxième exemple d'utilisation de la fonction OU


Imbriquer Et dans la fonction SI

Voilà, maintenant on va imbriquer dans la fonction SI notre formule précédente dans laquelle nous avons utilisé la fonction ET pour calculer la remise de 10% sur l’achat des housses du smartphone iPhone.
Rappelons-nous cette formule qui s’est écrite de la façon suivante : =ET(A2="iphone";B2="housse")

En l’imbriquant dans la fonction SI, nous aurons la formule qui suit :
=SI(ET(A2="iphone";B2="housse");D2*10%;0)

Alors, si la fonction ET renvoie VRAI la remise sera calculée en multipliant le prix d’achat par 10%, et si la fonction ET renvoie Faux, un zéro 0 sera affiché.

  • Tapez cette formule dans la cellule E2, et voici ce que vous aurez comme résultat :

Imbriquer la fonction ET dans la fonction SI



  • Copiez ensuite la formule dans les autres cellules et remarquez le résultat obtenu :

Associer la fonction ET à la fonction SI - exemple 2

Regardez cette courte vidéo qui explique comment utiliser la fonction ET

Imbriquer OU dans la fonction SI

De la même façon, on va imbriquer la fonction OU dans la fonction SI en repartant de notre exemple d’utilisation de la fonction OU vu en haut.

On va donc calculer une remise de 15% sur l’achat des accessoires SAMSUNG ou si la quantité d’achat dépasse 150 quelque soit le smartphone acheté.

Notre formule utilisant la fonction OU était : =OU(A2="Samsung";C2>150).

  • Sélectionnez alors la cellule E2 et entrez la formule suivante en imbriquant la fonction OU dans  la fonction SI :
=SI(OU(A2="Samsung";C2>150);D2*15%;0)

Imbriquer la fonction OU dans la fonction SI



  • Copiez ensuite la formule dans les cellules de E3 à E13
Note : vous pouvez aussi sélectionner la plage de cellules E2:E13, puis entrer la formule =SI(OU(A2="Samsung";C2>150);D2*15%;0) dans la barre de formule et taper enfin Ctrl+Entrée.

Insérer rapidement une formule dans plusieurs cellules


  • Voici alors le résultat affiché :

Associer la fonction OU à la fonction SI - exemple 2


Si imbriqué : 3 méthodes pour une bonne utilisation

Dans cet article, de la formation Excel, nous allons voir comment utiliser SI imbriqué en suivant 3 méthodes différentes.
Si imbriqué


Dans l’article précédent, nous avons vu comment utiliser la fonction SI mais en traitant uniquement une seule condition à tester.
Aujourd’hui nous allons voir comment utiliser la fonction SI en testant plusieurs conditions. On parle donc de Si imbriqué.

Si nous reprenons la syntaxe de la fonction SI déjà vue, elle s’écrit de la façon suivante :
Si(test_logique ;[valeur_si_vrai] ;[valeur_si_faux])

La fonction SI renvoie le premier résultat si la condition est remplie, si non elle renvoie le deuxième résultat. Or, et si cette condition n’est pas remplie et que la fonction SI nécessite encore de tester une autre condition pour qu’elle puisse renvoyer le résultat souhaité, la syntaxe prendra alors la forme suivante :
Si(test_logique ;[valeur_si_vrai] ; Si(test_logique ;[valeur_si_vrai] ;[valeur_si_faux]))

De la même façon aussi et si on aura besoin de tester une troisième condition si les deux premières conditions ne sont pas remplies, nous allons écrire la syntaxe de la formule comme suit :

Si(test_logique ;[valeur_si_vrai] ;Si(test_logique ;[valeur_si_vrai] ;Si(test_logique ;[valeur_si_vrai] ;[valeur_si_faux]))).

Comme vous le remarquez nous avons imbriqué deux fonctions SI dans la première fonction SI, c’est pourquoi qu’on dit qu’on utilise une fonction SI imbriquée.

L’exemple suivant va éclaircir mieux les choses :

Tableau de chiffre d'affaire


Notre objectif est de calculer une prime selon les trois conditions suivantes :
  • Si le chiffre d’affaire est supérieur ou égal à 20000 euros, la prime sera égale à 3% de ce chiffre d’affaire.
  • Si le chiffre d’affaire est entre 10000 euros et 20000 euros non inclus, on calcule la prime en multipliant ce chiffre d’affaire par 2%.
  • Si le chiffre d’affaire est strictement inférieur à 10000 euros, aucune prime ne sera calculée.

Notre formule sera donc la suivante :

=SI(A2>=20000;A2*3%;SI(A2>=10000;A2*2%;""))

Insertion de la fonction SI imbriquée


Alors, si la première condition est remplie, c’est-à-dire, si le chiffre d’affaire contenu dans la cellule A2 est supérieur ou égal (>=) à 20000 euros, la fonction SI applique le premier argument A2*3%.
Si cette condition n’est pas remplie, c’est-à-dire si le chiffre d’affaire est strictement inférieur à 20000 euros, on passe alors à la deuxième SI imbriquée qui va, à son tour, tester la deuxième condition définie dans son test logique : A2>=10000.
Si cette dernière est remplie donc la fonction SI calcule la prime en effectuant le calcul A2*2% ce qui donnera 300 euros dans ce cas, si non la fonction SI n’affiche rien car les deux premières conditions ne sont pas remplies.
  • Voici alors le résultat obtenu :


Résultat d'utilisation de la fonction SI imbriquée


  • En faisant une copie sur les cellules restantes, on obtient cet affichage :
Résultat total de SI imbriquée



Combien de fonctions SI peut-on imbriquer ?

Avec les versions récentes d’Excel, vous pouvez imbriquer un nombre maximum de 64 fonctions SI !!! c’est un nombre très intéressant mais aussi très difficile à gérer!

En effet, utiliser plusieurs fonctions SI imbriquées demande plus de précision et d’attention afin de ne pas commettre d’erreur. En plus, et pour que ces fonctions renvoient le bon résultat vous devez vous assurer que vous avez entré vos conditions dans un ordre correct, ce qui est très pénible surtout avec un grand nombre de fonctions SI utilisées.

Gardez le bon ordre des conditions

C’est un élément essentiel dans l’utilisation des fonctions SI imbriquées. Un ordre incorrect des conditions utilisées dans les arguments de ces fonctions mène à commettre des erreurs et à renvoyer des résultats illogiques.

Reprenons l’exemple en haut, et remarquez ce qui va se passer lorsqu’on changera l’ordre des conditions déterminées dans les fonctions SI utilisées :

Voici donc la formule qu’on a tapé en changeant l’ordre des conditions pour calculer la prime selon le chiffre d’affaire réalisé :

=SI(A2>=10000;A2*2%;SI(A2>=20000;A2*3%;""))

Ordre incorrect des conditions dans SI imbriquée


Vous voyez donc que la prime n’est pas bien calculée pour les chiffres d’affaires supérieurs à 20000 euros.
En effet, la fonction SI commence par tester la première condition A2>=10000, (en allant bien sûr de gauche à droite), si elle est remplie, la fonction SI applique ce qui est demandé dans le premier argument : « faire le calcul A2*2% », ce calcul donc sera effectué pour tous les chiffres d’affaires qui sont supérieurs à 10000 euros en incluant évidemment ceux qui sont supérieurs aussi à 20000 euros.

C’est pour cela qu’il est impérativement conseillé d’entrer les conditions dans un ordre correct.

Utiliser la fonction SI.CONDITIONS

Si vous disposez de la version Excel 2016 ou vous travaillez sur Excel Online, vous pouvez utiliser la fonction SI.CONDITIONS (IFS en anglais) qui permet d’utiliser plusieurs instructions SI imbriquées facilement.

La syntaxe de la fonction SI.CONDITIONS est de la forme suivante :

SI.CONDITIONS(test_logique1 ; valeur_si_Vrai1; [test_logique2 ; valeur_si_Vrai2];...[ test_logique127 ; valeur_si_Vrai127])

A partir de cette syntaxe, vous pouvez remarquer que la fonction SI.CONDITIONS a tout organisé pour vous, vous n’aurez pas besoin d’entrer de multiples SI ou de parenthèses, remplissez seulement les paramètres demandés selon l’ordre défini :
  • test_logique1 : cet argument vous permet d’entrer votre première condition. C’est obligatoire!
  • valeur_si_Vrai1 : entrez votre valeur que va renvoyer la fonction SI.CONDITIONS lorsque la première condition est remplie. C’est obligatoire aussi.
  • test_logique2 : cet argument vous permet d’entrer la deuxième condition qui sera testée lorsque la première n’est pas remplie. Ce choix est optionnelle.
  • valeur_si_Vrai2 : saisissez la valeur vraie correspondante à la deuxième condition.
  • Et ainsi de suite en gardant l’ordre : chaque condition est suivie de sa valeur vraie. Et comme vous l’avez vu sans doute, vous pouvez entrer jusqu’à 127 conditions différentes à tester!

Exemple pratique

Le tableau suivant présente des quantités différentes de stock :

tableau Quantité de stock


Nous souhaitons donc créer une formule qui répond à ces instructions de vérification de stock :
  • Si la quantité de stock est inférieure ou égale (<=) à 40, afficher le message « Stock épuisé ».
  • Si la quantité est entre 41 et 50 (inclus), afficher le message « Stock bientôt épuisé »
  • Si la quantité est entre 51 et 60 (inclus), afficher « Vérifiez votre stock ».
  • Si la quantité est supérieure à (>) 61, ne rien afficher.

Dans la cellule B2 entrez la formule suivante pour tester la quantité contenue dans la cellule A2 :

=SI.CONDITIONS(A2<=40;"Stock épuisé";A2<=50;"Stock bientôt épuisé";A2<=60;"Vérifiez votre stock";VRAI;"")

Copiez-la ensuite dans le reste des cellules, et voici ce que vous allez obtenir :

Exemple d'utilisation de la fonction SI.CONDITIONS


Note : 
  • Entrez la valeur VRAI pour spécifier un résultat par défaut lorsque toutes les conditions mentionnées ne sont pas remplies. Dans notre cas, VRAI remplace la condition A2>60.
  • Vous pouvez aussi entrer une formule qui renvoie toujours VRAI à la place de la valeur VRAI, par exemple 1=1, 200=200 ou 50=50 …
  • Veuillez aussi garder le bon ordre des conditions, et qu’elles renvoient uniquement les valeurs VRAI ou FAUX si non la fonction SI.CONDITIONS renvoie des erreurs.

Optez aussi pour la fonction SI.MULTIPLE

C’est une nouvelle fonction logique qui est apparue aussi avec l’arrivée de la version Excel 2016 et disponible également pour les utilisateurs d’Excel Online, la fonction SI.MULTIPLE (SWITCH en anglais) simplifie l’utilisation des instructions Si imbriquées mais d’une façon différente.
  • Voici la syntaxe de la fonction SI.MULTIPLE :

=SI.MULTIPLE(expression ;valeur1 ;résultat1 ;[défaut_ou_valeur2 ;résultat2]….;[défaut_ou_valeur126 ;résultat126])

Le travail de la fonction SI.MULTIPLE consiste à évaluer une valeur, entrée dans le paramètre expression, par rapport à une liste de valeurs (valeur1, valeur2 ……valeur126) et renvoie le résultat en fonction de la première valeur correspondante. S’il n’y a pas de correspondance, une valeur (facultative ) par défaut peut être renvoyée.

Suivez l’exemple suivant pour mieux comprendre l’utilisation de cette fonction :

  • Un dossier traité par une agence et avant d’être payé ou rejeté passe par les étapes suivantes :  Enregistré, en cours de traitement, validé ou non.

Chaque étape de traitement est spécifiée par un code, comme c’est décrit dans la liste suivante, accompagné de sa désignation:
  • PA : Payé
  • EN : Enregistré
  • EC : En cours de traitement.
  • VA : Validé
  • RE : Rejeté

Alors dans la cellule C2, nous entrons la formule suivante pour que la fonction SI.MULTIPLE renvoie la désignation du code contenu dans la cellule B2 qui sera donc la valeur à évaluer:

=SI.MULTIPLE(B2;"VA";"Validé";"EC";"En cours de traitement";"EN";"Enregistré";"PA";"Payé";"Rejeté")

Ensuite nous la copions dans les autres cellules de C3 à C10, et voici le résultat obtenu :

Exemple d'utilisation de la fonction SI.MULTIPLES



Note : la valeur par défaut dans cet exemple et qui sera affichée lorsqu’il n’y a aucune correspondance, est « Rejeté », et nous l’avons écrite en dernier.