Aller au contenu principal

5 avril 2012

34

EXCEL : IMBRIQUER DES SI, ET, OU DANS VOS FORMULES

par Sophie Marchand
Fonctions imbriquées 0

LES FONCTIONS IMBRIQUÉES

.

J’aimerais vous présenter une façon intéressante d’utiliser les fonctions SI (IF), ET (AND) et OU (OR) dans Excel. Je remarque souvent que la majorité des utilisateurs d’Excel n’utilisent pas de fonctions imbriquées. Ce sont pourtant les plus puissantes! Cela est souvent lié au fait que la majorité des utilisateurs d’Excel utilise l’assistant pour créer leurs formules et que cet assistant ne propose pas de formules imbriquées. C’est à nous, utilisateurs, d’imbriquer nos fonctions les unes dans les autres!

.

Ci-bas, je vous fais une démonstration de ce que j’entends par "fonctions imbriquées’ en utilisant les fonctions SI, ET, OU. Il s’agit d’un petit modèle qui permet de calculer automatiquement les bonus des vendeurs d’une entreprise et de générer un commentaire de suivi par rapport à leur rendement. Très pratique!

.

Vous trouverez ci-bas un extrait du fichier en question:

.

.

Voici la formule dans la cellule E12:

.

Elle permet de verser un bonus de 10% aux vendeurs qui ont réalisé des ventes entre 200,000$ et 300,000$ et un bonus de 15% aux vendeurs qui ont réalisé des ventes supérieures à 300,000$. La balance des vendeurs ne reçoivent aucun bonus. Ce qui est intéressant, c’est qu’on peut faire varier autant les % de bonus que les bornes en dollars inférieures et supérieures pour le versement.

.

Voici la formule dans la cellule J12:

.

Elle permet de générer le commentaire : "Prendre des mesures" pour tous les vendeurs qui n’ont pas réalisé de bonus en 2011 ou qui ont vendus moins en 2011 qu’en 2010.

.

UNE VIDÉO POUR UNE MEILLEURE COMPRÉHENSION

.

Voyez maintenant ce petit modèle à l’oeuvre, avec explications:

.

.

Si vous aimez cet article, cher lecteurs, je vous invite à le partager dans vos réseaux sociaux. Je vous invite également à laisser un commentaire plus bas.

.

Sophie Marchand, M.Sc., CGA

514-605-7112

 

34 Commentaires Poster un commentaire
  1. Stephane
    mai 27 2013

    Bonjour Madame,

    Je tenais à vous remercie pour l’effort que vous consacrer à vos tutoriel car ils m’aident énormément dans mes projets. Je vous sollicite car je ne trouve pas de solution à mon problème.
    J’essaye d’ajouter 9 ans pour toutes les dates avant le 22/11/97 et 7 ans après le 22/11/97 dans un fichier Excel.
    Voici ceux que j’ai faits mais il ne fonctionne que lorsque cette date est supérieur

    =IF(DATE(I39889>1997,11,22),DATE(YEAR(I39889)+7,MONTH(I39889),DAY(I39889)),DATE(YEAR(I39889)+9,MONTH(I39889),DAY(I39889)))

    I39889 : représente le 28/07/2005.

    Je vous remercie
    Cordialement
    Stephane

    Répondre
    • mai 29 2013

      Bonjour Stéphane,

      Merci pour votre question. Je trouve bizarre que votre formule ne fonctionne pas. Quand je l’écris, de mon côté, elle fonctionne très bien.

      Je vous envoie le fichier en question à votre adresse courriel.

      Au plaisir,

      Sophie

      Répondre
  2. Laura
    oct 6 2013

    Bonjour madame,

    J’ai suivi vos conseils mais en vain, voilà j’ai essayé une formule avec SI

    =SI(ET(D14>=1,D14=2);60;SI(ET(D14>2,D14=3);100;SI(ET(D14>3,D14=5);150

    Cela fait plusieurs semaine que j’écume les sites sur les formules conditionnelles, peut être que l’ordre n’est pas bon mais je n’y arrive plus. Ce que je veux transposer en formule c’est :

    Si D14 est supérieur ou égale à 1 ou égale à 2 alors le résultat sera 60, Si D14 est supérieur à 2 ou égale à 3 alors le résultat sera 100, Si D14 est supérieur à 3 ou égale à 5 alors le résultat sera 150.

    Si vous pouvez m’aider je vous en serai reconnaissante.

    Merci d’avance.

    Cordialement Laura

    Répondre
    • oct 6 2013

      Bonjour Laura,

      D’abord, vous devez remplacer les ET par des OU.

      Ensuite, vous devez faire attention à vos parenthèses. Il en manque 3 à la fin!

      Si vous avez encore des problèmes, faites-moi signe.

      Au plaisir,

      Sophie

      Répondre
      • Laura
        oct 7 2013

        Je vous remercie d’avoir répondu à mon commentaire, mais cela, n’a malheureusement pas marché
        Je me demande si cela ne vient pas de ma répétition de la cellule D14 dans une même parenthèse, car quand je l’enlève la formule me met FAUX, peut être que cela vient aussi de mes virgules.

        Merci de m’accorder de votre temps.

        Cordialement Laura

      • oct 7 2013

        Bonjour Laura,

        Svp, faites-moi parvenir votre fichier par courriel à marchandsophie@hotmail.com et je corrigerai votre formule pour vous.

        Merci,

        Sophie

  3. Clémence
    oct 15 2013

    Bonjour madame,
    Je n’avais absolument rien compris avec l’assistant fonction mais vous m’avez fait comprendre la formule elle-même, je tenais donc à vous remercier!
    Merci donc,
    Clémence

    Répondre
  4. déc 21 2013

    Merci beaucoup !!

    Répondre
  5. Fabrice
    déc 24 2013

    Bonjour Madame,
    merci déjà pour votre contribution à l’amélioration de nos connaissances. le résultat de la formule me donne la numéro de la cellule au lieu d’une valeur. si par exemple G4<=B8, le résultat donnera G8 au lieu de la valeur contenue dans G8. pouvez vous m'aider s'il vous plaît

    =SI(G4<=B8;"G8";SI(G4<=B9;"G9";SI(G4<=B10;"G10";"G11")))

    Répondre
    • déc 25 2013

      Bonjour Fabrice,

      Merci pour votre question. Lorsque vous faites référence à une cellule dans votre formule, vous ne devez pas mettre de guillemets. Vous devez employer les guillemets seulement lorsque vous faites référence à du texte. Comme vous utilisez les guillemets pour faire référence à une cellule, Excel croit que c’est du texte et vous renvoie le texte résultant.

      Au plaisir,

      Sophie

      Répondre
  6. will
    jan 28 2014

    Merci pour cette aide si simple mais si utile !!
    quand je pense au nombre de colonnes que je rajoutais pour fractionner mes formules …

    Répondre
  7. Fabrice M.
    fév 4 2014

    Bonjour Madame,

    Voilà ma formule : =SI(ET(K2>7;A2=1);1400;SI(ET(K2>15;A2=1);2800;0))
    Le but de celle-ci est que si A2=1 la cellule cible contienne :
    – 1400 si K2 est supérieur à 7
    – 2800 si K2 est supérieur à 15
    Et si A2 n’est pas rempli, la cellule cible contient 0.

    La formule fonctionne partiellement puisque c’est uniquement 1400 qui s’affiche dans les cellules de mon tableau remplissant les conditions (A2=1 et K2>7) ! La deuxième partie de ma formule (A2=1 et K2>15) semble ne pas être prise en compte…?

    J’ai beau chercher, tester, inventer, rien ne fonctionne…je cale.

    Merci d’avance pour votre aide !

    Répondre
    • fév 5 2014

      Bonjour Fabrice,

      L’erreur que vous commettez est l’ordre de vos conditions.

      Une valeur qui est plus grande que 7, est nécessairement plus grande que 15.

      Donc, quand Excel résout votre équation, il vérifie d’abord si la cellule est plus grande que 7. Si c’est le cas, il va afficher 1400.

      Pour corriger le tout, vous devez inverser votre formule. Commencez d’abord par faire le test "plus grand que 15" et ensuite, faites le test "plus garnd que 7".

      Merci,

      Sophie

      Répondre
      • Fabrice M.
        fév 5 2014

        Bonjour !

        Effectivement, je ne savais pas qu’il y avait une notion d’ordre dans la vérification des conditions !

        Merci beaucoup !

  8. Labass
    fév 8 2014

    Bonjour Madame!

    Ce site est extraordinaire; rarement j’ai vu un site aussi explicite sur les fonctions d’excel. Mon challenge est le suivant:

    j’essaie de faire un planning automatique des conges des employes que j’administre. Comme ex. un employe A prend commence ses conges a la date du 8 fev.14 et reprendra le service le 08 mars 14.

    J’aimerais donc creer un tableau excel remplissant automatiquement les jours compris entre le 08 fev et le 08 mars et affichant par la meme occasion les lettres D et F en dessous des dates de debut et de fin des conges.

    J’ai essay d’utiliser la commande mise en forme conditionnelle mais je n’y arrive pas. Tout ce que j’ai pu faire, c’est un tableau manuel.

    Dans mes recherches sur le net, je suis tombe sur la formule ci après (formule dont je ne comprend guere les arguments)

    =SI(F$6=$C7,"A",SI(F$6=$D7,"D",SI(ET(F$6>$C7,F$610,3,2))=0),"x","")))

    Merci pour votre assistance

    Répondre
    • fév 9 2014

      Bonjour,

      SVP me faire parvenir votre fichier par courriel à smarchand@lecfomasque.com. Je crois qu’il y a une erreur dans la retranscription de votre formule ci-haut. De toutes façons, il sera plus facile pour moi de vous aider en ayant accès à votre fichier.

      Merci,

      Sophie

      Répondre
  9. TAHON
    mar 27 2014

    Merci beaucoup !!!!

    Répondre
  10. MERIAUX Stéphanie
    avr 17 2014

    Bonsoir Sophie !
    Vous m’avez l’air d’une magicienne sur excel alors je tente ma chance !
    Je suis en train de créer des fiches de paie, dans le cadre de révisions et je cale sur une formule.
    En résumé je cherche à faire:
    – si mon salaire brut > plafond SS alors Plafond SS
    – si mon salaire brut < plafond SS alors salaire brut
    MAIS
    – si l'effectif de l'entreprise C16;C10>20);C16;si(et(G1720);G17;0) mais cela me marque #VALEUR! et je ne comprends pas mon erreur!!!!

    Bon j’en ai essayé d’autres mais c’est la dernière sur laquelle je me suis arrêtée avant de devenir folle !

    Pourriez-vous m’aider ?
    Merci d’avance et, en attendant, bonne soirée !

    Cordialement,

    Stéphanie

    Répondre
    • avr 17 2014

      Bonjour,

      Je vous réponds par courriel car il semble qu’il y ait un problème en ce moment sur mon site avec les guillemets notamment…

      Sophie

      Répondre
  11. Abena
    mai 2 2014

    Merci Beaucoup chère madame. Car depuis que j’ai découvert vos rubriques, je fais énormément de choses en statistiques. Seulement voilà, j’ai un problème avec cette formule qui ne marche pas. je sais pas pourquoi ?{=SOMME(SI(C5:AG5="c";1;0))}. pouvez-vous m’aider ?

    Répondre
  12. Pouvez-vous m’aider ? petits problèmes avec "et" et "si". Voici les données : si C25 = 4000 et B12>0, alors tarif cellule A2, si C25 = 2000 et B12>0, alors tarif cellule A3. Possible ? merci

    Répondre
  13. Il en manquait un morceau, désolé ! Pouvez-vous m’aider ? petits problèmes avec "et" et "si". Voici les données : si C25=6000 et B12 >0, alors tarif cellule A1, si C25 = 4000 et B12>0, alors tarif cellule A2, si C25 = 2000 et B12>0, alors tarif cellule A3. Possible ? merci

    Répondre
    • mai 9 2014

      Bonjour Jean-Charles,

      Voici ce que vous recherchez:

      =si(et(c25=6000;B12>0);A1;si(et(c25=4000;B12>0);A2;si(et(C25=2000;B12>0);A3;0)))

      Sophie

      Répondre
      • mai 10 2014

        Merci beaucoup. Je ne vois pas "A2",je suppose qu’il est après (c25=4000;B12>0); ????. Je me permets de vous envoyer un fichier par mail car j’aimerais savoir si c’est possible en rajoutant une condition "ET". Merci pour tout.

  14. hivet
    juin 11 2014

    bonjour, pouvez vous m’aider
    j’arrive à faire mes 2 formules suivantes :
    SI(et(I1="interne/externe";N1>0);N1+15) OU SI(ET(I1="externe";N1>0);N1+15)
    je voudrais pouvoir combiner les 2 à savoir que si en I1 on a "interne/externe" OU "externe" et que N1>0 alors je veux qu’il compte N1+15 mais ça ne fonctionne pas
    j’espère être compréhensible.
    merci d’avance

    Répondre
    • juin 11 2014

      Bonjour,

      Si je comprends bien, vous cherchez probablement à faire ceci:

      =SI(et(ou(I1="interne/externe";I1="externe");N1>0);N1+15;0).

      Merci,

      Sophie

      Répondre
  15. hivet
    juin 11 2014

    Merci beaucoup il ne manquait pas grand chose, je viens de découvrir votre blog au détour de mes recherches sur excel je viens de le mettre dans les favoris je pense y trouver une mine d’infos intéressantes.

    Répondre
  16. NOAH NOAH Jean Crepin
    juil 8 2014

    Bonjour je uis NOAH NOAH Crepin depuis le cameroun
    je n’arrive pas avoir un resultat avec ucette formule
    si N9>=50, V9>=80 ou si W9>=140 alors "AMJ" aussi si (W9>=150 alors "AM" dans le cas contraire "RF"
    voilà celle que j’ai entrée: mais je n’ai pas de "AM" pourtant j’ai des valeurs > à 150 à W9
    =SI(ET(N9>=50;V9>=80);"AMJ";SI(ET(W9>=140); "AMJ";SI(ET(W9>=150);"AM";"RF")))

    Répondre
    • juil 8 2014

      Bonjour,

      Je ne suis pas certaine d’avoir bien compris ce que vous tentez de faire, mais je crois que vous tentez de faire ceci:

      =IF(W9>=150,"AM",IF(OR(N9>=50,V9>=80,W9>=140),"AMJ","RF"))

      ou encore

      =SI(W9>=150;"AM";SI(OU(N9>=50;V9>=80;W9>=140);"AMJ";"RF"))

      Merci,

      Sophie

      Répondre
  17. VIGUIE
    juil 21 2014

    Bonjour,
    Voilà je cherche à fiabiliser une formule depuis des jours et rien à faire il y a toujours un bug.
    Alors voilà ma problématique, dans 1 colonne j’ai des index de compteurs qui ont un cadran de 5 chiffres donc plusieurs possibilités, l’index relevé peut-être inférieur au précédent relevé, ou supérieur. Ensuite, il se peut aussi que le compteur soit allé jusqu’à 99999 puis à recommencer à 0.

    Index Consommation totale
    54048
    55412 1364 (calcul 55412-54048 donc là ca marche)
    12760 57347 (calcul de 55412 à 99999 puis de 0 à 12760 donc là, ca marche aussi :)
    35113 22353 (calcul 35113-12760 donc là ca marche)
    60953 25840 (idem ca marche)
    83000 22047 (idem ca marche)
    82864 99863 (et ben là, ca ne marche pas car il ne comprends pas que là, il ne doit pas
    faire de 82864 à 99999 puis de 00000 à 82864 mais faire 82864-83000 sur ce
    coup ci :((( Il devrait me donner comme conso -136
    00869 18004 (ici il comprends bien qu’il doit aller jusqu’à 99999 puis de 00000 à 00869
    donc ca marche)

    J’ai donc un cas qui ne fonctionne pas mais pour lequel je m’arrache la tête…

    Merci d’avance pour l’aide que vous pourrez m’accorder
    VirginieV

    Répondre
  18. GOËTA Gabriel
    août 16 2014

    Bonjour,

    Au secours SVP !! :-) Cela fais prés de 3h que je me bas avec cette formule :
    =(SI(B31000012000001300000;B3*6%)"8000"))

    En fait, je voudrai dire la chose suivante : Si le prix est < à 100 000 alors prix de vente * 10 % SI entre 100 001 et 200 000 alors prix de vente * 8 %
    SI entre 200 001 et 300 000 alors prix de vente * 7 % et
    SI plus de 300 001 alors prix de vente * 6 %

    Je ne comprends pas comment mettre les parenthèses, je pense que ca vient de là…

    Merci d'avance pour votre aide et j'espère que cela ne vous prendra pas trop de votre temps..

    Sincères salutations

    Répondre
  19. GOËTA Gabriel
    août 16 2014

    Oups le copier colle de la formule n’a pas fonctionné..

    =(SI(B31000012000001300000;B3*6%)"8000"))

    Répondre
  20. GOËTA Gabriel
    août 16 2014

    Toujours pas… dsl

    Répondre

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s

Remarque : Le HTML est autorisé. Votre adresse email ne sera jamais publiée.

S'abonner aux commentaires

%d bloggers like this: