Fonctions logiques "ET" "OU" "SI" (partie 1)

Publié le 21 juin 2009 par Marc

Exposé du problème :

Le directeur commercial de la société SERENDIPITY qui manage 5 responsables régionaux, doit décider chaque début d’année si ceux-ci, individuellement, se verront attribuer une prime sur résultats, basée sur le chiffre d’affaires réalisé entre janvier et décembre. La décision se prend en fonction de 2 critères, à savoir :

1. La réalisation d’un chiffre d’affaires minimal de 10 000 € mensuel doit être atteint,

2. La réalisation d’un chiffre d’affaires minimal de 150 000 € annuel doit être atteint.

Présentation du tableau télechargeable plus bas

Dans les cellules B3 à F14, nous avons le CA mensuel par responsable commercial.

Les cellules B19 et B20 contiennent les objectifs mensuels et annuels.

Antoine, Catherine et Serge ont atteint leurs objectifs mensuels ainsi que l’objectif annuel. La prime leur est accordée (Valeur VRAI en Bleu).

Fabrice ne touchera pas sa prime (valeur FAUX en Rouge), il a été défaillant au mois de février, même s’il a atteint l’objectif annuel.

Sandrine ne touchera pas sa prime non plus (valeur FAUX en Rouge), elle n’a pas atteint l’objectif annuel, même si elle n’est jamais passée sous le seuil des 10 000 € mensuels.

Calculs à effectuer :

Le CA total par responsable doit être calculé automatiquement,
Lorsque la prime est accordée, VRAI doit apparaître en caractères Bleu ; sinon FAUX doit apparaître en Rouge.

Contraintes à prendre en compte :

Le droit à la prime (VRAI ou FAUX) est déterminé directement à partir des valeurs saisies manuellement dans les cases de critères d’affectation (Objectifs mensuels et annuels)
Seul Antoine fera l’objet d’une saisie complète de la formule qui valide l’obtention de la prime. Les autres responsables verront la formule de calcul recopiée par tout moyen à votre convenance.

Connaissances requises pour résoudre ce problème :

Fonctions :

· SOMME()

· ET()

Autres connaissances :

· Références relatives et absolues

· Mise en forme conditionnelle

A - Copier les données dans Excel ou téléchargez le fichier vierge (.xls)
Pour vous faciliter la tâche et vous permettre de passer immédiatement à l'essentiel, vous aller commencer par sélectionner l'intégralité du tableau ci-après (les caractères bleus). Pour cela, cliquez à hauteur de la première ligne du tableau, le plus possible à gauche , puis faites glisser le curseur jusqu'en bas à droite de la dernière ligne du tableau.

Copiez le texte (Menu Édition) puis prenez une nouvelle feuille de calcul dans Excel cliquez dans la cellule A1, puis collez le texte (Menu Édition).

Si tout se passe bien - je n'ai pas testé à ce jour tous les navigateurs - les données devraient être présentes dans les bonnes cellules de votre feuille de calcul.

N.B. Libre à vous de choisir vos polices de caractères et le type d'encadrement que vous souhaitez.

	Responsables commerciaux
	Antoine	Catherine	Fabrice	Sandrine	Serge
Janvier	17 000	23 000	13 000	13 000	11 500
Février	12 000	17 000	8 500	17 000	21 000
Mars	14 500	12 000	10 500	10 500	10 000
Avril	10 500	17 000	17 000	12 000	14 500
Mai	17 000	17 000	13 000	10 500	10 500
Juin	13 000	14 000	17 000	12 000	16 000
Juillet	14 000	13 500	10 500	14 500	17 000
Août	12 500	17 000	10 500	10 500	14 000
Septembre	19 500	13 000	12 000	13 000	14 500
Octobre	21 000	16 500	13 000	10 500	17 000
Novembre	17 000	14 500	22 000	13 000	12 000
Décembre	12 000	14 500	10 500	10 500	21 000
CA Total
Prime

Critères d'affectation de la prime
Objectif mensuel	10 000
Objectif annuel	150 000

B - Calcul du CA annuel de chaque responsable

Pour commencer, nous allons calculer le CA annuel pour chacun de nos responsables commerciaux régionaux.

Cliquez dans la cellule B15 et saisissez la formule (3 méthodes) :

=somme(B3:B14)
tapez=somme( puis cliquez dans la cellule B3 et faites glissez le curseur vers le bas (en maintenant le bouton de la souris enfoncé) jusqu'à la cellule B14 ; tapez ensuite la fermeture de votre parenthèse )
Double cliquez sur le symbole SIGMA, cela aura pour effet de saisir automatiquement avec la fonction SOMME l'ensemble des nombres qui se trouvent au dessus de la cellule B15.

Maintenant que nous connaissons le CA annuel réalisé par Antoine, nous allons recopier cette formule pour l'ensemble de nos responsables commerciaux.

Pour cela, il suffit de cliquer dans la cellule B15 puis, après avoir relaché le bouton de la souris de placer le curseur en bas à droite de cette même cellule (jusqu'au moment où le curseur se transforme en croix noire) de cliquer, et en gardant la pression sur le bouton de la souris, de faire glisser le curseur vers la droite, jusqu'à la cellule F15.

Ainsi, la formule que vous avez saisi une fois en B15 se trouve recopiée automatiquement.

Notez bien que si vous cliquez successivement dans chacune de ces cellules, la formule s'est progressivement transformée comme suit :
en C15, elle est devenue =SOMME(C3:C15) en lieu et place de =SOMME(B3:B15) et ainsi de suite jusqu'en F15.

C - Qui a droit à la prime ?

Afin de savoir qui a droit à la prime, nous allons utiliser une fonction logique. Il s'agit de la fonction ET().

Nous allons procéder de la même manière que précédemment en n'effectuant qu'une seule saisie de formule en B16.

C1 - La fonction logique ET (généralités)

Cette fonction renvoie la valeur logique VRAI si la ou les conditions sont remplies, et la renvoie la valeur FAUX, si la ou l'une ou plusieurs des conditions ne sont pas remplies.

Syntaxe :

ET(valeur_logique1;valeur_logique2;...)

Remarque :

Il est possible de tester de 1 à 255 arguments qui renverront soit VRAI, soit FAUX.

Dans le cas ou nous devons effectuer plusieurs tests logiques, chacun d'eux est séparée par un ; .

C2 - La fonction logique ET dans notre exemple

Dans le cas qui nous intéresse, nous devons vérifier que chaque responsable a atteint ou dépassé :

Son objectif de 10 000 € chaque mois (cellule B19),
Son objectif de 150 000 € annuel (cellule B20).

Pour cela, si l'on prend les résultats d'Antoine par exemple, nous devrons tester une par une les cellules B3 à B14, et les comparer à la cellule B19 (qui contient l'objectif mensuel) ; de plus, nous devrons tester la cellule B15 à la cellule B20 (qui contient l'objectif annuel)

=ET(B3>=$B19;B4>=$B19;B5>=$B19;B6>=$B19;B7>=$B19;B8>=$B19;B9>=$B19;
B10>=$B19;B11>=$B19;B12>=$B19;B13>=$B19;B14>=$B19;B15>=$B20)

Les tests effectués en bleu dans la formule concernent l'objectif mensuel; celui en rouge l'objectif annuel.

Il vous reste maintenant à recopier la cellule vers la droite en procédant de la même manière que nous l'avons fait avec la cellule B15 dans le chapitre B - Calcul du CA annuel de chaque responsable.

A l'intention de ceux qui ne connaîtraient pas la notion de références relatives, absolues et mixtes

Vous avez certainement remarqué qu'à chaque fois que l'on fait référence aux cellules B19 et B20 (objectifs mensuel et annuel, le symbole $ apparaît devant la lettre B.

Cela est extrêmement utile dans le cas d'une recopie ou d'un copier-coller. En effet, lorsque nous faisons glisser dans notre exemple la cellule B16 vers F16, excel comprend (grâce au symbole $) que nous ne voulons pas que la colonne B se transforme en colonne C, puis D, etc.

Si le symbole $ était omis, la formule ferait référence aux cellules C19 et C20, puis D19 et D20. Ces cellules ne contenant aucune valeur, notre résultat serait ainsi complètement faussé, car une cellule sans valeur a par défaut la valeur zéro. Ainsi, Fabrice et Sandrine auraient touché une prime non méritée…

Un article vous sera proposé prochainement pour expliquer ces notions de références relatives et mixtes. De plus, la plupart des exercices qui vous seront proposés cette notion sera abordée.

D - Coloration automatique du texte VRAI en bleu et FAUX en rouge

Pour terminer cet exemple, nous allons demander à Excel de mettre en couleur le résultat de notre calcul précédent, à savoir colorer en bleu si la prime est accordée (VRAI) et de le colorer en rouge si la prime est refusée (FAUX).

Nous allons sélectionner tout d'abord les cellules B16 à F16
Puis, dans le Menu Format, sélectionner l'item Mise en forme conditionnelle…

Vous obtenez la fenêtre suivante :

Que vous remplissez comme indiqué ci dessus ; "La valeur de la cellule est" "égale à" "VRAI".
Vous cliquez sur le bouton Format… et choisissez dans la nouvelle fenêtre la couleur bleu ainsi que d'autres attributs si vous le souhaitez, puis vous validez en cliquant sur le bouton OK.
Vous êtes de retour sur l'écran précédent et, comme l'on souhaite affecter la couleur rouge au texte "FAUX", vous cliquez sur le bouton Ajouter>> pour définir une condition supplémentaire.
Vous remplissez cette fois "La valeur de la cellule est" "égale à" "FAUX"
Vous cliquez sur le bouton Format… et choisissez dans la nouvelle fenêtre la couleur rouge, puis vous validez en cliquant sur le bouton OK.
Vous êtes de retour sur l'écran précédent et cliquez sur OK.

Et voilà, si tout s'est bien passé, vous devriez obtenir aux différences de présentation près, les mêmes résultats que dans le premier tableau présenté dans l'énoncé de ce cas.

Dans la seconde partie de cet exercice, nous traiterons de la fonction logique et OU et de la fonction SI.

N'hésitez pas à laisser un commentaire si certains points vous semblent peu clairs, j'essayerai de les éclaircir.