Condition si dans Excel. Comment calculer une prime de vente dans Excel à l'aide des fonctions if() imbriquées. Empêcher la division par zéro

Fonction SI() est l'un des outils les plus puissants dans le monde des fonctions. Si vous parvenez à le maîtriser dans votre travail, vous passerez au niveau suivant de création de formules.

Commençons par l’utilisation la plus simple possible de la formule. Syntaxe: SI (expression_logique ; valeur_si vrai), Où expression_log- une expression logique, c'est-à-dire une expression renvoyant la valeur VRAI ou FAUX (ou des valeurs numériques équivalentes : 0 si FAUX, et toute valeur positive est VRAIE, et où value_if vrai— la valeur renvoyée par la fonction si expression_log a la valeur VRAI.

Par exemple, regardons la formule suivante : =IF(A1>1000,"many") . L'expression logique A1>100 0 est utilisée pour la vérification. Disons que vous ajoutez cette formule à la cellule B1. Si l'expression booléenne est vraie (elle est vraie lorsque le nombre dans A1 est supérieur à 1000), la fonction renvoie la valeur "plusieurs", ce que vous verrez dans la cellule B1 (si A1 est inférieur à 1000, vous verrez la valeur FAUX).

Une autre utilisation simple de la fonction consiste à vérifier les avertissements. Par exemple, si vous avez un livre avec les résultats de l'augmentation des ventes de divers produits en pourcentage. Il sera utile de marquer de manière spécifique les produits dont les ventes ont diminué. La formule de base de cette marque est : =IF(cellule

Ici cellule- l'adresse de la cellule à vérifier, et marque- un certain type de texte à afficher à l'écran pour indiquer une valeur négative. Voici un exemple : =SI(B1

Une version légèrement améliorée de la marque, dans laquelle elle dépendra de l'ampleur de la valeur négative, ressemble à ceci : =REPEAT("

Gérer un faux résultat

Comme vous pouvez le voir sur la fig. 4.15, si en fonction SI() L'expression booléenne prend une valeur négative, la fonction renvoie donc FALSE. Ce n’est pas un inconvénient majeur, mais cela donne l’impression que les feuilles de calcul sont « plus lourdes » que si le résultat renvoyait, par exemple, une ligne vide.

Pour ce faire, vous devez utiliser la syntaxe de fonction étendue : IF(logical_expression; value_if_true; value_if_false). Nous connaissons déjà les deux premiers arguments, mais le dernier argument valeur_if_false spécifie la valeur de retour de la fonction SI(), lorsque le résultat de l'évaluation d'une expression_log est faux.

Par exemple, considérons la formule suivante : =IF(A1>1000,"many","little") . Si cette cellule temporelle A1 contient un nombre inférieur ou égal à 1000, la formule affichera la chaîne « quelques ». Pour l'exemple de marquage des volumes de ventes négatifs (voir Fig. 4.15 ci-dessus), vous devez utiliser la formule suivante : =IF(B1

Comme vous pouvez en juger sur la fig. 4.16, la feuille de calcul est désormais bien meilleure.

Empêcher la division par zéro

Comme nous l'avons indiqué précédemment, la fonction renvoie une erreur #DIV/0 !, si la formule tente de diviser une valeur par 0. Pour éliminer ne serait-ce que la possibilité de commettre une telle erreur, vous pouvez utiliser la fonction SI() pour vérifier la division.

Par exemple, le calcul le plus simple du pourcentage de bénéfice brut en utilisant la formule (Revenus - Dépenses) / Revenus. Pour vous assurer que la valeur Revenu n'est pas égale à zéro, utilisez la formule suivante (on suppose que les mots Revenus et Dépenses seront remplacés par les valeurs nécessaires) : =IF(Revenu 0; (Revenu - Dépenses)/Revenu; "Le revenu est de 0!" ) . Si une expression logique Revenu 0 est correct, ce qui signifie que le calcul aura lieu selon la formule. Sinon, la fonction renverra un message d'avertissement au lieu d'une erreur.

Les objectifs importants du cursus pédagogique à l'école sont le développement des opérations mentales de base chez les élèves (analyse, synthèse, comparaison, généralisation), la normalisation de la relation entre leurs activités et la parole, la formation de techniques de travail mental : analyse des données initiales , la planification des activités, la mise en œuvre de la maîtrise de soi étape par étape et finale, pour lesquelles la planification des cours du processus d'apprentissage est utilisée. Une grande importance est attachée à la capacité de parler du travail effectué avec l'utilisation correcte de la terminologie appropriée et l'établissement de liens logiques dans le matériel présenté.

La fonction SI appartient aux fonctions logiques et est l'une des plus fréquemment utilisées dans le travail.À l'aide de cet opérateur, vous pouvez effectuer diverses tâches lorsque vous devez comparer certaines données et produire le résultat. La fonction IF dans Excel permet d'utiliser des algorithmes de branchement, de construire un arbre de décision, etc.

Vidéo sur l'utilisation de la fonction IF dans Excel

Exemples d'utilisation de l'opérateur IF

La fonction SI ressemble à ceci :

SI (expression ; vrai ; faux).

Et maintenant un peu plus de détails :

Par exemple, vous pouvez saisir le chiffre 8 dans le champ C1, et écrire ceci dans le champ D1 : =IF(C1<10; 1; 2). То есть программа будет сравнивать значение в поле C1 с цифрой 10, и когда оно окажется меньше 10 (C1<10), то в поле D1 будет написана единица. В противном случае программа выдаст число 2.

L'exemple suivant : il y a une liste des étudiants, ainsi que leurs notes qu'ils ont reçues à l'examen : 5, 4, 3 et 2. La condition de la tâche est que pour chaque élève, vous devez rédiger un commentaire textuel « réussi » ou « échoué ». Autrement dit, si un étudiant a reçu une note de 3 ou plus, il a réussi l'examen, sinon il n'a pas réussi. Pour résoudre ce problème vous pouvez écrire ceci : =IF(C1<3; «не сдал»; «сдал»). Excel будет сравнивать оценку каждого студента, и когда она будет меньше 3 (т.е. двойка), то в соответствующем поле будет написан комментарий «не сдал». Если же оценка будет 3 или выше, то в ячейке будет отмечено, что этот студент сдал экзамен. La fonction SI logique fonctionne dans Excel selon ce principe simple. Il est à noter que les commentaires textuels doivent être placés entre guillemets.

Lorsque vous utilisez la fonction IF dans Excel, vous pouvez utiliser les opérateurs de comparaison suivants :

plus (>);
moins (<);
supérieur ou égal à (>=);
inférieur ou égal (<=);
est égal(=);
inégal (< >).

Exemples de fonctions ET, OU

De plus, lors de l'utilisation de la fonction SI dans Excel, il est possible de la combiner avec des opérateurs de comparaison simples (comme AND, OR). Par exemple, vous pouvez écrire la condition suivante : =IF(AND(A1<=5; A1>3); « passe », « ne passe pas »). La condition suivante est obtenue : si la note de l'étudiant est inférieure ou égale à 5 et supérieure à 3, alors le commentaire « réussite » sera affiché, sinon le commentaire « échec » sera affiché (uniquement les étudiants dont les notes sont de quatre et les cinq passent).

Un exemple plus complexe d'utilisation de la fonction SI - utilisation des conditions AND ou OR

La condition suivante avec un exemple de l'opérateur OR : =IF(OR(A1=5; A1=10); 100; 0). Il s'avère que si le nombre dans la cellule A1 est 5 ou 10, le programme affichera 100, sinon - 0.

De plus, vous pouvez utiliser ces opérateurs simples pour résoudre des problèmes plus complexes. Par exemple, dans la base de données, il est nécessaire de sélectionner les débiteurs qui doivent plus de 10 000 roubles et qui n'ont pas payé depuis plus de 6 mois. Si le programme trouve une telle personne, laissez-le émettre le critère « emprunteur à problèmes ».

Disons que la cellule A1 contient des informations sur la durée de la dette (en mois) et que le champ B1 contient le montant de la dette. La formule ressemblera alors à ceci : =IF(AND(A1>=6; B1>10000); "emprunteur à problèmes"; ""). Si une personne est trouvée qui remplit la condition spécifiée, le programme écrira le commentaire « emprunteur à problème » à côté de son nom de famille, sinon la cellule restera vide.

Si l'un des paramètres est considéré comme critique, alors vous pouvez créer une formule comme celle-ci : =IF(OR(A1>=6; B1>10000); « situation critique » ; « »). Si le programme trouve des correspondances pour au moins un paramètre (soit la durée, soit le montant de la dette), l'utilisateur verra un message indiquant que la situation est critique. La différence avec la formule précédente est que dans le premier cas, le message « emprunteur à problèmes » n'était émis que lorsque les deux conditions étaient remplies.

Autres exemples d'utilisation de l'instruction IF

Très souvent, dans Excel, une erreur telle que « DIV/0 » se produit, c'est-à-dire division par 0. En règle générale, cela apparaît dans les cas où la formule « A/B » est copiée et où le nombre B dans certaines cellules est égal à zéro. Cela peut être évité en utilisant l'instruction IF. Pour ce faire, vous devez écrire ceci : =IF(B1=0; 0; A1/B1). Il s'avère que s'il y a un zéro dans la cellule B1, alors Excel affichera immédiatement zéro, sinon le programme divisera A1 par B1 et affichera le résultat.

Une autre situation qui se produit assez souvent dans la pratique est le calcul d'une remise en fonction du montant total de l'achat. Pour ce faire, vous aurez besoin de quelque chose comme cette matrice :

jusqu'à 1 000 - 0 % ;
de 1001 à 3000 - 3% ;
de 3001 à 5000 - 5% ;
plus de 5001 - 7%.

Par exemple, Excel dispose d'une base de données théorique de clients et d'informations sur le montant qu'ils ont dépensé en achats. La tâche consiste à calculer une remise pour eux. Pour ce faire, vous pouvez écrire ceci : =IF(A1>=5001; B1*0.93; IF(A1>=3001; B1*0.95;..). L'essentiel est clair : le montant total des achats est vérifié, et lorsqu'il s'agit par exemple de plus de 5 001 roubles, alors multiplié par 93 % du coût du produit (cellule B1*0,93), lorsqu'il est supérieur à 3 001 roubles, alors multiplié par 95 % du coût du produit, etc. facilement être utilisé dans la pratique : le niveau du volume des ventes et le niveau des remises sont fixés à votre discrétion.

Ainsi, vous pouvez utiliser la fonction IF dans presque toutes les situations, la fonctionnalité le permet. L'essentiel est de formuler correctement la formule pour que le résultat ne s'avère pas erroné.

Une question courante dans Excel est « Comment écrire plusieurs conditions dans une seule formule ? » Il est particulièrement courant d'utiliser deux conditions ou plus lors de l'utilisation de la fonction IF. Faire plusieurs conditions dans la formule IF est assez simple, l'essentiel est de connaître les principes de base. Nous en discutons ci-dessous.

À mon avis, il est instructif de considérer un exemple de résolution d’un système de conditions. De telles tâches sont souvent confiées dans des instituts, dans des classes Excel.

Par exemple, il y a cette formule un peu encombrée :

Regardons un exemple de la façon de le transférer vers Excel

Il est clair que cette formule sera composée d'au moins 3 parties :

PÉCHÉ(B1)^2 =COS(B1) =EXP(1/B1)

Mais comment pouvons-nous écrire plusieurs de ces fonctions en une seule, également par condition ? Pour comprendre cela, examinons de plus près la fonction IF.

Sa composition est la suivante :

SI(Condition;si condition = OUI (VRAI);si condition = NON (FAUX))

Ceux. si nous écrivons une formule simple, qu’obtenons-nous dans la cellule B2 ?

Correct - 100 sera affiché. Si A1 contient une autre valeur autre que 1, alors B2 affichera 0.

Revenons à notre système de conditions. Nous devons maintenant comprendre comment écrire deux conditions à la fois avant le premier point-virgule. Nous avons B1 vide, ce qui signifie = 0, et ce n'est que si les deux conditions A1=1 et B1=0 (signe *) sont remplies que la valeur de la formule sera égale à 100.

Regardons de plus près le * entre parenthèses

L'opérateur Et * signifie que les deux conditions doivent être remplies simultanément, A1=1 et B1=0.

Si vous mettez + (ou) entre parenthèses, alors une des conditions sera suffisante. Par exemple, seulement si A1=1, alors 100 sera affiché.

Nous sommes prêts à écrire la formule, nous le ferons par parties

Écrivons la première condition

SI((B1>-2)*(B1<9);SIN(B1)^2);

Si la condition est remplie, alors la première formule avec sinus est exécutée
Sinon, la deuxième condition

SI((B1>-2)*(B1<9);SIN(B1)^2;SI((B1>=9)*(B1<=19);COS(B1)

Dans tous les autres cas, la formule =EXP(1/B1) sera satisfaite
Le total est:

SI((B1>-2)*(B1<9);SIN(B1)^2;ЕСЛИ((B1>=9)*(B1<=19);COS(B1);EXP(1/B1)))

Écrire plusieurs formules en une

S'il y a du texte dans les cellules B1, la formule générera une erreur. C'est pourquoi j'utilise souvent la formule.

Imaginons que toute notre formule du paragraphe précédent soit un argument conditionnel A

Alors =SIERREUR(A;"")

Ou pour notre exemple

SIERREUR(SI((B1>-2)*(B1<9);SIN(B1)^2;ЕСЛИ((B1>=9)*(B1<=19);COS(B1);EXP(1/B1)));"")

L'exemple peut être téléchargé

Informations générales sur l'IF

La fonction SI est l'une des fonctions les plus populaires dans Excel. Dans Excel anglais, ainsi que dans Google Sheets, LibreOffice, OpenOffice, cette fonction s'appelle IF. IF (IF) fait référence à des fonctions logiques.

Niveau de difficulté sur l'échelle BRP ADVICE - 2 sur 7. Chaque IF imbriqué double la complexité de la formule.

IF (IF) vous permet de construire un arbre de décision, c'est-à-dire que si une condition est remplie, effectuez une action, et si elle n'est pas remplie, une autre. Dans ce cas, la condition doit être une question avec des options de réponse « oui/non » ou « vrai/faux » (en termes d'Excel, Google Sheets, LibreOffice, OpenOffice c'est « VRAI / FAUX »).

Pour comprendre la fonction SI, vous devez d’abord comprendre ce que sont les fonctions logiques.

Que sont les fonctions logiques

Dans Excel, Google Sheets, LibreOffice, OpenOffice et autres tableurs, le fonctionnement des fonctions logiques repose sur l'existence de paramètres logiques. Il existe deux paramètres logiques : le premier est VRAI, le second est FAUX.

Sur la base de l'utilisation de ces paramètres logiques, un arbre de décision peut être construit. La version la plus simple de cet arbre posera une question à laquelle on peut répondre par VRAI ou FAUX, et donnera des instructions sur ce qu'il faut faire dans chacun de ces deux cas. Un tel arbre de décision est représenté schématiquement dans la figure ci-dessous.

Dessin. L'arbre de décision le plus simple

Les fonctions logiques vous permettent soit de construire un tel arbre de décision, soit de poser une question et de recevoir un paramètre logique. Les premiers incluent, par exemple, IF (IF), SIERREUR. Les seconds sont ISNUMBER, AND (AND), OR (OR).

Excel, Google Sheets, LibreOffice, OpenOffice et la plupart des autres logiciels vous permettent d'utiliser les paramètres logiques VRAI et FAUX lors de l'exécution d'opérations mathématiques. Le plus souvent, TRUE prend la valeur 1, FALSE prend la valeur 0. Bien que parfois TRUE et FALSE prennent d'autres valeurs, par exemple, lors de la programmation en VBA, TRUE vaut -1 et non 1.

À propos, les paramètres logiques sont également appelés paramètres booléens en l'honneur du mathématicien et logicien anglais George Boole.

Fonction SI

Ainsi, la fonction IF vous permet de construire un arbre de décision. Cet arbre de décision comporte une question d’entrée et deux options. La question a nécessairement deux réponses possibles : oui/non, vrai/faux, ou en termes de paramètres logiques VRAI/FAUX.

Une question et deux options d'action sont les trois arguments de la fonction SI.

Le premier argument de la fonction SI est une question logique. Dans Excel, cela s'appelle "log_expression". Excel, Google Sheets, LibreOffice, OpenOffice trouvent automatiquement la réponse à cette question, et cette réponse doit être VRAI/FAUX. Que peut donner une telle réponse ? Les options les plus simples sont les égalités et inégalités classiques. Par exemple, l'expression 12=12 renverra le paramètre logique VRAI, et l'inégalité 12>40 renverra le paramètre logique FAUX.

Dans une question logique, vous pouvez utiliser des égalités (les côtés gauche et droit sont comparés à l'aide du signe « = »), des inégalités (plus - « > », moins - «<», больше или равно - «>=", inférieur ou égal à "<=»), а также просто не равно - «<>».

Des questions logiques plus complexes peuvent être posées à l'aide de fonctions imbriquées. À la suite du calcul de ces fonctions imbriquées, le même paramètre logique VRAI ou FAUX doit être obtenu. De telles fonctions incluent, par exemple, ISNUMBER, ISTEXT, ISNA, AND, OR, dans les cas complexes - un autre IF.

Les deuxième et troisième arguments sont la fonction SI pour déterminer quand la réponse à la question est VRAIE et quand elle est FAUX. La fonction IF évalue soit uniquement le deuxième argument (si VRAI), soit uniquement le troisième argument (si FAUX).

Examinons des exemples d'utilisation de la fonction IF avec une ou plusieurs conditions.

Utiliser IF avec une condition

Vous pouvez télécharger l'exemple de fichier n°1.

Supposons que l'entreprise ait un plan de vente : chaque manager doit vendre au moins 1 million de roubles par mois. Le salaire d'un directeur commercial est de 20 000 roubles. Lorsque le plan est réalisé, le manager perçoit un salaire et une prime de 5 % du volume réel des ventes. Si le plan de vente n'est pas respecté, uniquement le salaire.

A la fin de chaque mois, un tableau est généré contenant des informations sur les ventes de chaque manager. Ce tableau pourrait ressembler, par exemple, à la figure ci-dessous.

Dessin. Ventes par directeur commercial pour le mois de reporting

Grâce à la fonction IF, ce tableau peut rapidement être transformé d'un simple ensemble de données de ventes pour le mois en un rapport qui montrera qui a respecté le plan, qui ne l'a pas fait et quel sera le salaire de chaque manager. Un tel rapport peut ressembler à la figure ci-dessous.

Dessin. Rapport sur les performances des directeurs commerciaux

Afin de remplir automatiquement les colonnes «Mise en œuvre du plan» et «Salaire du mois, frottez». (colonnes E et F, respectivement), vous pouvez utiliser la fonction IF.

Exemple 1.1 - substitution de texte en utilisant IF

Dans la colonne « Exécution du plan » de la cellule E4, nous utilisons la formule suivante :

IF(D4>=1000000;"Bravo!";"Plan non réalisé :(")

IF(D4>=1000000;"Bravo!";"Plan non réalisé :(") .

D'ailleurs, dans certaines versions d'Excel, au lieu de ";" "," doit être utilisé.

Après cela, la cellule peut être copiée jusqu'à la fin de la colonne et le programme écrira sur chaque ligne qui a bien fait et qui n'a pas réalisé le plan.

Que signifient tous les arguments IF ?

1. Expression logique : D4>=1000000. Dans notre exemple, la question logique est une comparaison entre le résultat réel et le plan de vente. D4 est un lien vers la cellule avec les ventes réelles de ce manager. Excel, Google Sheets, LibreOffice, OpenOffice remplacent la valeur de cette cellule par D4 et vérifiez si l'inégalité spécifiée est vraie. Suite à la vérification de la formule, un résultat intermédiaire est obtenu, il est utilisé pour sélectionner la branche souhaitée dans l'arbre de décision.

2. Value_if_true. Dans nos diagrammes, il s'agit de la branche gauche de l'arbre de décision. Dans l'exemple actuel, la valeur de l'argument est "Bravo !". Cet argument indique ce que la fonction IF doit faire lorsque le premier argument est évalué à TRUE. Dans l’exemple actuel, il vous suffit d’écrire le texte « Bravo !

Au fait, "Bravo !" nous l'avons écrit entre guillemets, car tout texte à l'intérieur de la formule doit être écrit entre guillemets. Les seules exceptions sont les noms de fonctions et les plages nommées. Sinon, mettez toujours le texte entre guillemets.

3. Value_if_false. Dans nos diagrammes, il s’agit de la branche droite de l’arbre de décision. Dans l'exemple actuel, la valeur de l'argument est "Plan failed:(". Cet argument indique ce que la fonction IF doit faire lorsque le premier argument est évalué à FALSE. Dans l'exemple actuel, vous écrivez simplement le texte "Plan failed :( ".

Ici, nous mettons également le texte entre guillemets car si vous ne mettez pas de guillemets autour du texte dans une formule, l'erreur #NOM ? (#NOM?). Les seules exceptions sont les noms de fonctions et les plages nommées.

Tout d'abord, la fonction SI répond à une question logique (évalue le premier argument). Deuxièmement, il va à la branche correspondante de l’arbre de décision. Selon Alexandrov P.F. ça se passe comme ça :

1. D4>=1000000, donc on vérifie 1000329>

2. Accédez à l'argument Value_if_true. Il vous suffit d'insérer le texte « Bravo ! » Spécifiez le texte dans la cellule. Fin des calculs.

Dessin. Comment fonctionne la fonction IF lorsqu'une expression booléenne renvoie VRAI ?

1. D5>=1000000, donc on vérifie 848880>

2. Accédez à l'argument Value_if_false. Il vous suffit d'insérer le texte « Plan non réalisé :( ». Nous indiquons le texte dans la cellule. Fin des calculs.

Schématiquement, les calculs ressemblent à la figure ci-dessous.

Dessin. Comment fonctionne la fonction IF lorsqu'une expression booléenne renvoie FALSE ?

Exemple 1.2 - calculer différentes formules en utilisant IF

Vous pouvez télécharger le fichier exemple n°1.

SI(D4>=1000000;20000+D4*5/100;20000)

ou pour l'anglais Excel, Google Sheets, LibreOffice, OpenOffice :

SI(D4>=1000000;20000+D4*5/100;20000) .

Après cela, la cellule peut être copiée jusqu'à la fin de la colonne et le programme écrira le salaire de chaque manager sur chaque ligne.

Que fait exactement la fonction IF dans cet exemple ?

La fonction SI répond à la question logique (évalue le premier argument) et se déplace vers la branche appropriée de l'arbre de décision. Selon Alexandrov P.F. ça se passe comme ça :

1. D4>=1000000, donc on vérifie 1000329>=1000000, l'expression est vraie, ce qui signifie que le paramètre logique est VRAI.

2. Accédez à l'argument Value_if_true. Vous devez calculer 20 000+D4*5/100 (c'est-à-dire que le salaire est de 20 000 et le même bonus est de 5 % des ventes). On obtient 70016, indiquez cette valeur dans la cellule. Fin des calculs.

L'argument Value_if_false est ignoré dans ce cas par la fonction IF.

Selon Ilyin M.A. ça se passe comme ça :

1. D5>=1000000, donc on vérifie 848880>=1000000, l'expression n'est pas vraie, ce qui signifie que le paramètre logique est FALSE.

2. Accédez à l'argument Value_if_false. Il vous suffit de mettre 20000. Nous indiquons le numéro dans la cellule. Fin des calculs.

L'argument Value_if_true dans ce cas est ignoré par la fonction IF.

Comme toujours, nos exercices fonctionnent dans Excel 2007-2013 et les fonctionnalités avancées peuvent être utilisées dans Excel 2010 et 2013. Avec lui, vous pouvez commencer n'importe quel exercice depuis le début avec un seul bouton « Recommencer » sur l'onglet CONSEILS BRP qui apparaît dans Excel lorsque vous ouvrez nos exercices. N'oubliez pas d'activer les macros.

Utiliser IF avec plusieurs conditions

Exemple 2 - différentes conditions dans une expression logique

Vous pouvez télécharger le fichier exemple n°2.

Dans l’exemple précédent, les managers et les cadres supérieurs avaient le même plan de vente pour le mois. Compliquons la tâche : nous fixerons un plan majoré pour les cadres supérieurs - 1 million 200 mille par mois. Le rapport ressemblera alors à la figure ci-dessous.

Dans ce cas, dans la colonne « Exécution du plan » de la cellule E4, nous utilisons la formule suivante :

IF(IF(C4="Senior Manager";D4>=1200000;D4>=1000000);"Bravo!";"Plan non réalisé :(")

ou pour l'anglais Excel, Google Sheets, LibreOffice, OpenOffice :

IF(IF(C4="Senior Manager";D4>=1200000;D4>=1000000);"Bravo!";"Plan non réalisé :(") .

Que fait exactement la fonction IF dans cet exemple ?

Selon Alexandrov P.F. ça se passe comme ça :

2. La fonction IF imbriquée vérifie l'expression logique : le poste du manager est senior manager ou non. Alexandrov P.F. - ce n'est pas un cadre supérieur. Par conséquent, l’expression logique dans le IF (IF) imbriqué renvoie FALSE.

3. La fonction IF imbriquée accède à l'argument Value_if_false et compare les ventes réelles au plan du directeur commercial. 1 000 329 est supérieur à 1 000 000, donc le IF imbriqué renvoie le paramètre booléen TRUE.

Selon Ilyin M.A. ça se passe comme ça :

1. La fonction IF commence son calcul avec une expression logique et y voit une fonction IF imbriquée. Excel, Google Sheets, LibreOffice, OpenOffice calcule d'abord la fonction imbriquée.

2. La fonction IF imbriquée vérifie l'expression logique : le poste du manager est senior manager ou non. Ilyin M.A. - ce n'est pas un cadre supérieur. Par conséquent, l’expression logique dans le IF (IF) imbriqué renvoie FALSE.

3. La fonction IF imbriquée accède à l'argument Value_if_false et compare les ventes réelles au plan du directeur commercial. 848 880 est inférieur à 1 000 000, donc le IF imbriqué renvoie le paramètre booléen FALSE.

Voir l'arbre de décision construit dans le diagramme ci-dessous.

Dessin. Arbre de décision pour une fonction IF avec plusieurs conditions

Selon Nezenetsev A.A. ça se passe comme ça :

1. La fonction IF commence son calcul avec une expression logique et y voit une fonction IF imbriquée. Excel, Google Sheets, LibreOffice, OpenOffice calcule d'abord la fonction imbriquée.

2. La fonction IF imbriquée vérifie l'expression logique : le poste du manager est senior manager ou non. Nezenetsev A.A. - C'est un cadre supérieur. Par conséquent, l'expression logique dans le IF (IF) imbriqué renvoie VRAI.

3. La fonction IF imbriquée va à Value_if_true et compare les ventes réelles au plan du directeur commercial principal. 1 204 346 est supérieur à 1 200 000, donc le IF imbriqué renvoie le paramètre booléen TRUE.

4. Le résultat du calcul de la fonction IF imbriquée est transmis à la fonction principale. La fonction IF principale voit l'argument booléen TRUE et accède à son argument (et non à son argument imbriqué) Value_if_true. Cet argument est simplement le texte « Bravo ! »

Une formule avec plusieurs conditions, c'est-à-dire avec des fonctions IF imbriquées, renvoie le texte « Bien joué ! » dans la cellule.

Voir l'arbre de décision construit dans le diagramme ci-dessous.

Dessin. Arbre de décision pour une fonction IF avec plusieurs conditions

Selon Sokolova N.I. ça se passe comme ça :

1. La fonction IF commence son calcul avec une expression logique et y voit une fonction IF imbriquée. Excel, Google Sheets, LibreOffice, OpenOffice calcule d'abord la fonction imbriquée.

2. La fonction IF imbriquée vérifie l'expression logique : le poste du manager est senior manager ou non. Sokolova N.I. - C'est un cadre supérieur. Par conséquent, l'expression logique dans le IF (IF) imbriqué renvoie VRAI.

3. La fonction IF imbriquée va à Value_if_true et compare les ventes réelles au plan du directeur commercial principal. 1 046 625 est inférieur à 1 200 000, donc le IF imbriqué renvoie le paramètre booléen FALSE.

4. Le résultat du calcul de la fonction IF imbriquée est transmis à la fonction principale. La fonction IF principale voit le paramètre booléen FALSE et passe à son argument (et non à son argument imbriqué) Value_if_false. Cet argument est simplement le texte « Le plan a échoué :( ».

Une formule avec plusieurs conditions, c'est-à-dire avec des fonctions IF (IF) imbriquées, renvoie le texte « Échec du plan :( » dans la cellule.

Voir l'arbre de décision construit dans le diagramme ci-dessous.

Dessin. Arbre de décision pour une fonction IF avec plusieurs conditions

Formule de calcul du salaire dans l'exemple 3

Dans la colonne "Salaire mensuel, frottez". dans la cellule F4 nous utilisons cette formule :

SI(IF(C4="Senior Manager";D4>=1200000;D4>=1000000);20000+D4*5/100;20000)

ou pour l'anglais Excel, Google Sheets, LibreOffice, OpenOffice :

SI(IF(C4="Senior Manager";D4>=1200000;D4>=1000000);20000+D4*5/100;20000) .

N'oubliez pas, dans certaines versions d'Excel, au lieu du ";" "," doit être utilisé.

Dans ce cas, la fonction SI fonctionne exactement de la même manière que dans la cellule E4.

Exemple 4 - conditions différentes à la fois dans l'expression logique et dans les branches de l'arbre de décision

Vous pouvez télécharger le fichier exemple n°3.

Nous avons donc des managers, nous avons des cadres supérieurs. Les cadres supérieurs ont un plan plus élevé que les cadres ordinaires. Pour que ce modèle fonctionne, des incitations supplémentaires à l’intention des cadres supérieurs sont souvent nécessaires. Par exemple, la prime du cadre supérieur passe à 6 %. Autrement dit, nous avons plusieurs conditions à la fois :

1. Le bonus n'est payé que si le plan est réalisé.

2. Si le poste est celui de cadre supérieur, le plan est de 1 million 200 000, sinon de 1 million.

3. Si le poste est celui de cadre supérieur, la prime est de 6 %, sinon de 5 %.

Le résultat est un rapport comme celui ci-dessous.

Dessin. Rapport sur la performance des gestionnaires et des cadres supérieurs

Comment résoudre un tel problème en utilisant la fonction SI ?

Dans la cellule F4, vous pouvez écrire la formule suivante :

IF(C4="Cadre supérieur";D4>=1200000;D4>=1000000);

20000+D4*IF(C4="Senior Manager";6;5)/100;

ou pour l'anglais Excel, Google Sheets, LibreOffice, OpenOffice :

IF(C4="Cadre supérieur";D4>=1200000;D4>=1000000);

20000+D4*IF(C4="Senior Manager";6;5)/100;

N'oubliez pas, dans certaines versions d'Excel, au lieu du ";" "," doit être utilisé.

La figure ci-dessous montre schématiquement l’arbre de décision construit.

Dessin. Exemple d'arbre de décision avec plusieurs conditions à la fois dans l'expression logique et dans d'autres arguments de la fonction IF

Erreurs courantes lorsque vous travaillez avec la fonction IF

1. Pour la fonction IF, le premier argument doit toujours être spécifié - une expression logique et le deuxième argument - la valeur si vrai. Le troisième argument est facultatif. Les utilisateurs oublient souvent de spécifier le troisième argument, en particulier lorsqu'ils travaillent avec des formules complexes, de ce fait, dans certains cas, au lieu du résultat souhaité, un paramètre logique FALSE apparaît dans la cellule.

2. La complexité des formules augmente très rapidement lors de l'utilisation de IF imbriqués. Pour cette raison, très souvent les utilisateurs oublient de fermer les parenthèses des calculs imbriqués et ne définissent pas le séparateur d'argument (« ; » ou « »). En fonction de l'erreur, soit la formule ne peut pas être écrite dans la cellule, soit elle est mal calculée.

3. Dans les formules complexes avec IF (IF), il est très difficile de suivre l'exactitude des calculs : chaque fonction IF (IF) imbriquée ajoute une question et au moins deux branches à votre arbre de décision. En moyenne, une personne a jusqu'à 7 objets dans son esprit, il s'avère qu'avec trois FI (IF) imbriqués, elle doit garder 3 questions et 6 branches de l'arbre de décision dans son esprit. La contrôlabilité et la fiabilité de la formule diminuent rapidement.

Comment éviter ces erreurs lorsque vous travaillez avec la fonction IF ? Minimisez l’utilisation des IF avec d’autres fonctions et en particulier avec les IF imbriquées. Il est préférable de faire des calculs intermédiaires dans des cellules adjacentes.

Astuce : Travailler avec des formules complexes

Nous devons souvent travailler avec des formules complexes, des formules dans lesquelles d’autres sont imbriquées au sein d’une même fonction. Comment ne pas se tromper en créant une telle formule ? Suivez l'algorithme suivant :

1. Déterminez le but ultime de vos calculs : quel résultat vous devriez obtenir au final.

2. Définissez une fonction qui vous permet de faire cela.

3. Commencez à créer une formule avec cette fonction, spécifiez-la et passez au travail avec les arguments.

5. Si vous devez effectuer des calculs intermédiaires, déterminez l'objectif final de ces calculs, la fonction, etc. En règle générale, la tâche des calculs intermédiaires consiste à obtenir un argument pour la fonction principale. Gardez cela à l'esprit, car vous avez parfois besoin d'obtenir un argument d'un certain type (texte, nombre, paramètre logique ou autre).

6. Surveillez toujours les parenthèses : Une fois que vous avez fini de décrire la fonction, fermez les parenthèses.

Et rappelez-vous, si la formule est trop complexe, il vaut mieux faire un calcul intermédiaire dans la cellule suivante.

Comment compléter et remplacer la fonction IF

Au lieu de constantes, vous pouvez utiliser des plages nommées dans une formule.

La résolution d'un problème avec plusieurs conditions peut être grandement simplifiée en utilisant les fonctions imbriquées AND (AND) et OR (OR).

La fonction IF peut parfois être remplacée par une fonction VLOOKUP, HLOOKUP, LOOKUP, IFERROR, SUMIF ou COUNTIF.

Exemple de fichier n°1 « Utilisation de la fonction IF avec une condition » téléchargeable .

Exemple de dossier n°2 "Utiliser la fonction IF avec plusieurs conditions".

Vous avez encore des questions ? Écrivez-nous dans le formulaire de commentaires et inscrivez-vous pour Cours intensif Excel ou fonctions Excel.

Fonction SI l'une des fonctions Excel les plus populaires et les plus fréquemment utilisées. En l'utilisant avec des opérateurs de comparaison et d'autres fonctions logiques d'Excel, vous pouvez résoudre des problèmes assez complexes. Dans cette leçon, nous essaierons d'analyser son action à l'aide d'exemples simples, et apprendrons également à utiliser plusieurs fonctions à la fois. SI en une seule formule.

En bref sur la syntaxe

Fonction SI n'a que trois arguments :

=IF(condition_spécifiée; valeur_if_TRUE; valeur_if_FALSE)

Le premier argument est la condition grâce à laquelle la formule peut prendre des décisions. La condition est vérifiée en premier et ne peut renvoyer que deux valeurs - VRAI ou FAUX. Si la condition est vraie, alors la formule renverra le deuxième argument, sinon le troisième.

Pour savoir comment définir des conditions dans Excel, lisez les articles : Comment définir une condition logique simple dans Excel et Utilisation des fonctions logiques Excel pour définir des conditions complexes.

Exemple 1

Regardons l'exemple montré dans les figures ci-dessous. Dans cet exemple, la fonction SI vérifie d'abord l'état A1>25. Si tel est le cas, la formule renverra la chaîne de texte « supérieur à 25 » ; dans les autres cas, « inférieur ou égal à 25 ».

Exemple 2

Fonction SI est très flexible et peut être utilisé dans diverses situations. Regardons un autre exemple. Le tableau ci-dessous présente les résultats de la recertification des salariés de l'entreprise :

Dans la colonne C, nous devons mettre le résultat de l'examen, qui ne doit contenir que deux options : Passé ou N'a pas passé. Ceux qui ont obtenu plus de 45 points ont réussi l'examen, les autres ne l'ont pas fait.

Fonction IF et plusieurs conditions

Les fonctions SI peuvent être imbriqués si vous avez besoin d’élargir vos options de prise de décision dans Excel. Par exemple, pour le cas évoqué précédemment de recertification d'un employé, il est nécessaire de donner non pas un résultat, mais une note de la série : Excellent, Bon et Mauvais. Grade Super donné lorsque le nombre de points est supérieur à 60, le score Bien pour plus de 45 et score Mal dans d'autres cas.

Comme vous pouvez le voir, au lieu des deuxième et troisième arguments de la fonction SI vous pouvez ajouter de nouvelles fonctions SI, augmentant ainsi le nombre de conditions que la formule peut traiter. De cette façon, vous pouvez créer le nombre requis de pièces jointes. Certes, cette conception présente un inconvénient évident : après 3 à 5 investissements, la formule deviendra illisible et encombrante, et il sera impossible de travailler avec elle.

Dans Excel, il existe des outils plus nobles pour gérer un grand nombre de conditions, par exemple la fonction RECHERCHEV ou VOIR.

Ainsi, dans cette leçon, nous avons examiné la fonction logique SI dans toute sa splendeur et ses exemples, et a également analysé un exemple simple utilisant plusieurs fonctions à la fois SI en une seule formule. J'espère que ces informations vous ont été utiles. Bonne chance et bonne réussite dans l'apprentissage de Microsoft Excel !