Arrêtez d'imbriquer la fonction SI dans Excel ! Découvrez ces 5 alternatives PRO

Dans ce tutoriel, je vais vous montrer comment calculer automatiquement les remises accordées aux clients en fonction du montant de leurs achats, en utilisant cinq formules Excel différentes.

Nous allons commencer avec la fonction SI, que vous connaissez probablement tous, avant de découvrir quatre alternatives qui permettent d'obtenir exactement le même résultat.

Car le problème avec les fonctions SI, c'est que dès que nous multiplions les conditions, les formules deviennent rapidement difficiles à lire et à modifier. Nous allons donc chercher à comparer ces différentes méthodes, étape par étape, avec un exemple concret que vous pourrez facilement reproduire dans vos propres fichiers.

Et surtout, restez jusqu'à la fin, car nous verrons comment rendre notre calcul entièrement automatique, même lorsque les règles de remise changent !

 

Téléchargement

Télécharger le fichier du tutorielIndiquez votre nom et votre e-mail pour recevoir le lien de téléchargement.

 

Tutoriel Vidéo

 

1. Présentation

Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous retrouvons les achats réalisés par les clients d'une entreprise. Pour chacun de ces clients, nous disposons de son nom et du montant total de ses achats.

Notre objectif est de compléter automatiquement la colonne « Remise », en fonction d'un barème défini à l'avance.

Excel formation - 0134-stopSI - 01

Nous avons également un second tableau contenant les différents seuils de remise appliqués.

Le fonctionnement est simple : lorsqu'un client réalise au moins 500 € d'achats, il bénéficie de 5 % de remise. À partir de 1 000 €, cette remise passe à 8 %, puis à 12 % à partir de 2 000 €.

Pour faciliter les manipulations, nous allons transformer ces deux plages en tableaux structurés.

Nous sélectionnons une cellule du premier tableau, puis nous utilisons le raccourci [Ctrl]+[L]. Dans la fenêtre qui apparaît, nous vérifions que l'option « Mon tableau comporte des en-têtes » est cochée et nous pouvons remplacer le nom proposé par Excel par « Clients ».

Excel formation - 0134-stopSI - 02

Enfin, puis nous validons en appuyant sur le bouton « OK ».

Nous répétons l'opération avec notre barème, auquel nous donnons le nom « Bareme ».

Ces tableaux pourront ainsi s'agrandir automatiquement lorsque nous ajouterons de nouvelles données.

2. Calculer les remises avec les fonctions SI imbriquées

Commençons par la méthode la plus classique : la fonction SI.

Cette fonction permet de tester une condition et de retourner un résultat différent selon que cette condition est vraie ou fausse.

Par exemple, pour accorder 2 % de remise lorsque les achats atteignent 200 €, nous pourrions saisir en C7 :

  =SI(B7>=200;2%;0%)
  

Ici, Excel vérifie si le montant présent en B7 est supérieur ou égal à 200. Si c'est le cas, il affiche 2 %, sinon il affiche 0 %.

Mais notre barème comporte six niveaux de remise. Nous devons donc vérifier plusieurs conditions successivement.

Pour cela, nous allons imbriquer plusieurs fonctions SI les unes dans les autres.

Nous commençons donc par vérifier si le montant est inférieur à 200 €. Si oui, la remise est de 0 %.

Sinon, nous pouvons passer au deuxième test et vérifier si le montant est inférieur à 500 €. Si cette condition est vraie, la remise passe à 2 %.

Ensuite, nous allons continuer jusqu’à prévoir tous les cas possible présentés dans le tableau.

 

  =SI(B7<200;0%;SI(B7<500;2%;SI(B7<1000;5%;SI(B7<2000;8%;SI(B7<3000;12%;15%)))))
  

Excel formation - 0134-stopSI - 03

Pour Bernard, qui a réalisé 750 € d'achats, les deux premiers tests sont faux, mais le troisième est vrai. Excel retourne donc 5 %.

Pour Richard, avec 3 200 €, aucune des cinq conditions n'est satisfaite. Excel affiche alors la dernière valeur prévue, soit 15 %.

Nous validons avec [Entrée]. Comme nous utilisons un tableau structuré, Excel peut automatiquement recopier notre formule sur toute la colonne.

Si ce n'est pas le cas, nous pouvons sélectionner C7 et double-cliquer sur la poignée de recopie, dans le coin inférieur droit de la cellule.

Et voilà, nos dix clients disposent maintenant de leur remise.

Le problème, c'est que notre formule commence déjà à être longue.

Et si nous ajoutons plusieurs seuils supplémentaires, il faudra la modifier, ce qui risque d’être une tâche relativement complexe…

Voyons donc comment automatiser ce calcul en utilisant des fonctions Excel plus adaptées.

3. Simplifier les conditions avec SI.CONDITIONS et SI.MULTIPLE

   3.1. La fonction SI.CONDITIONS

 

Pour éviter d'imbriquer plusieurs fonctions SI, Excel propose la fonction « SI.CONDITIONS ».

Son fonctionnement est assez proche, sauf que nous pouvons directement enchaîner les conditions et les résultats correspondants.

Nous remplaçons donc notre formule en C7 par :

  =SI.CONDITIONS(B7<200;0%;B7<500;2%;B7<1000;5%;B7<2000;8%;B7<3000;12%;VRAI;15%)
  

Nous retrouvons exactement les mêmes conditions que précédemment, mais sans avoir à ouvrir une nouvelle fonction SI à chaque fois.

Excel teste les conditions dans l'ordre et retourne le résultat associé à la première condition vraie.

Regardons également la fin de notre formule : « VRAI;15% ».

Cela signifie que si aucune des conditions précédentes n'est satisfaite, Excel doit retourner 15 %.

C'est notre résultat par défaut.

Cette méthode est donc plus facile à lire, et surtout plus simple à corriger si nous nous trompons dans un seuil.

   3.2. La fonction SI.MULTIPLE

Nous pouvons également utiliser une autre fonction elle aussi inspirée de la fonction SI, appelée SI.MULTIPLE.

Habituellement, celle-ci sert à comparer une valeur à plusieurs possibilités. Mais nous pouvons détourner légèrement son fonctionnement pour tester des conditions.

Nous saisissons :

  =SI.MULTIPLE(VRAI;B7<200;0%;B7<500;2%;B7<1000;5%;B7<2000;8%;B7<3000;12%;15%)
  

L'astuce consiste à utiliser « VRAI » comme premier argument.

En effet, chaque comparaison, comme « B7<200 », retourne elle-même VRAI ou FAUX.

SI.MULTIPLE recherche donc la première condition dont le résultat correspond à VRAI, puis retourne la remise associée.

Le dernier argument, 15 %, est utilisé lorsqu'aucune condition ne correspond.

Nous obtenons une nouvelle fois les mêmes résultats.

Personnellement, pour ce type de calcul, je vous recommande plutôt SI.CONDITIONS, dont la syntaxe correspond directement à notre besoin.

Attention toutefois : avec ces trois premières méthodes, les seuils restent inscrits directement dans nos formules.

Si nous souhaitons modifier notre barème, nous devons donc modifier les formules elles-mêmes.

Voyons comment éviter cela.

 

4. Utiliser RECHERCHEV pour calculer les remises

Cette fois, nous allons exploiter directement le tableau de barème que nous avons créé dans les colonnes E et F.

L'idée est de rechercher le montant des achats dans ce tableau afin de retrouver automatiquement la remise correspondante.

Nous nous plaçons en C7 et nous saisissons :

  =RECHERCHEV(B7;$E$7:$F$12;2;VRAI)
  

Le premier argument, B7, correspond au montant des achats que nous recherchons.

Le deuxième argument désigne notre barème, de E7 à F12. Nous utilisons les signes « $ » pour bloquer cette plage lorsque nous recopions notre formule. Nous pouvons également les ajouter en sélectionnant la référence puis en appuyant sur [F4].

Le troisième argument, « 2 », indique que nous souhaitons récupérer le résultat dans la deuxième colonne du barème, celle des remises.

Enfin, le dernier argument, « VRAI », demande à Excel d'effectuer une recherche approximative.

Et c'est précisément ce qui nous intéresse ici !

Prenons Bernard, avec ses 750 € d'achats.

Excel ne trouve pas 750 dans notre barème, mais il retrouve le seuil immédiatement inférieur, soit 500 €, et retourne donc 5 %.

Attention, pour que cette méthode fonctionne correctement, notre première colonne doit impérativement être triée par ordre croissant.

Nous pouvons maintenant modifier directement les taux du barème sans toucher à notre formule.

Par exemple, si nous remplaçons 5 % par 6 %, tous les clients concernés voient immédiatement leur remise recalculée.

 

5. Utiliser RECHERCHEX pour obtenir une formule entièrement dynamique

Terminons avec RECHERCHEX, disponible notamment dans Excel 365 et les versions récentes d'Excel.

Cette fonction est plus souple que RECHERCHEV, notamment parce que nous indiquons séparément la colonne dans laquelle rechercher et celle contenant le résultat.

Nous saisissons en C7 :

  =RECHERCHEX(B7;$E$7:$E$12;$F$7:$F$12;;-1)
  

Le premier argument correspond toujours au montant des achats.

Le deuxième désigne les seuils de notre barème, tandis que le troisième correspond aux remises à retourner.

Nous laissons le quatrième argument vide et nous utilisons « -1 » comme cinquième argument.

Ce « -1 » indique à Excel de rechercher une correspondance exacte ou, à défaut, la valeur immédiatement inférieure.

Contrairement à RECHERCHEV, cette méthode ne nécessite pas que les seuils soient triés pour identifier la bonne correspondance.

Mais nous pouvons aller encore plus loin !

Puisque nous avons nommé notre tableau « Bareme », nous allons remplacer les références classiques par des références structurées :

  =RECHERCHEX(B7;Bareme[Montant minimum];Bareme[Remise];"Barème  absent";-1)
  

Notre formule ne dépend maintenant plus d'une plage fixe.

Si nous ajoutons une nouvelle ligne dans le tableau « Bareme », elle sera automatiquement prise en compte.

Pour le vérifier, ajoutons un nouveau seuil de 5 000 €, avec une remise de 18 %.

Le client Simon, qui a réalisé 5 100 € d'achats, bénéficie immédiatement de cette nouvelle remise.

Nous n'avons modifié aucune formule !

C'est pour cette raison que je vous recommande RECHERCHEX lorsque nous devons appliquer un barème susceptible d'évoluer.

6. Qu’en pense l’IA ?

Pour terminer ce tutoriel, voyons maintenant comment nous aurions pu obtenir notre formule directement avec l'intelligence artificielle, sans avoir à choisir nous-mêmes entre les cinq méthodes que nous venons de découvrir.

Pour cela, nous allons utiliser mon complément « IA Excelformation.fr », qui permet notamment de générer des formules Excel en décrivant simplement le résultat que nous en attendons.

Ici, nos deux tableaux sont suffisamment explicites pour que nous puissions même laisser l'IA déterminer toute seule le calcul à effectuer.

Excel formation - 0134-stopSI - 04

Nous sélectionnons simplement la cellule C7, puis nous nous rendons dans l'onglet « IA Excelformation.fr ».

Dans le menu « Tous les prompts », nous sélectionnons la catégorie « Excel », puis l'option « Créer formule Excel - Cellule active automatique ». Cette commande permet en effet de transmettre à l'IA le contexte de notre feuille, afin qu'elle puisse analyser les données présentes et proposer une formule adaptée à la cellule sélectionnée.

Dans notre exemple, elle va donc pouvoir identifier notre tableau de clients, la colonne « Montant des achats » et le barème de remises situé à droite. Et c’est à partir de ces informations, qu’elle peut comprendre que nous cherchons à attribuer un taux de remise en fonction du montant des achats.

Évidemment, le résultat dépendra du modèle d'IA utilisé et de la manière dont il interprète nos données. Il est même possible que deux demandes successives produisent des formules différentes, tout en donnant exactement les mêmes résultats.

Mais il y a de fortes chances qu’elle privilégie l’une des deux dernière options que nous avons vu ici, à partir des fonctions RECHERCHEV() et RECHERCHEX() :

  =RECHERCHEV(B7;$E$7:$F$12;2;VRAI)
  

Cela dit, il reste important de bien comprendre les fonctions Excel, même lorsque nous utilisons l'intelligence artificielle pour les générer. Car l'IA peut nous faire gagner du temps pour construire une formule, mais c'est en comprenant son fonctionnement que nous pouvons vérifier son résultat, repérer une erreur et choisir la solution la plus adaptée à notre fichier

Et vous, parmi ces cinq méthodes, laquelle préférez-vous utiliser ?

N'hésitez pas à me le dire dans les commentaires de la vidéo Youtube, et pensez à vous abonner pour découvrir d'autres astuces Excel !