B. FONCTIONS LOGIQUES

B.             FONCTIONS LOGIQUES

Les fonctions logiques sont de deux types :

La fonction conditionnelle  =SI()

Le premier argument est la condition ;

le deuxième argument donne la valeur de la cellule condition remplie ;

le troisième argument donne la valeur de la cellule condition non remplie.

 

Les fonctions de type vrai/faux

Ce sont des fonctions =SI limitées.

Elles posent une condition définie et affichent la valeur "vrai" si la condition est remplie et "faux" sinon. (voir fonclogi.xls)

 

Que viennent compléter la mise en forme conditionnelle

• Qui n'est pas une fonction logique mais qui obéit aux mêmes principes.

a)                LE FORMAT CONDITIONNEL

Il permet d'afficher une mise en forme différente selon la valeur de la cellule.

(1)          LES CONDITIONS SIMPLES

Des opérateurs logiques sont utilisés pour définir le format.

ONGLET "ACCUEIL"
groupe "style" (5ème bloc)
sélectionner la plage ou <clic g> sur une cellule à mettre en forme
<clic g> sur
<clic g> sur
indiquer la règle à appliquer
renseigner les conditions d'application et le format correspondant
pour valider
recopier éventuellement le format avec si vous n'avez mis en forme qu'une seule cellule

 

 

Lorsque vous appelez l'outil de mise en forme conditionnelle sur une plage qui a déjà reçu une mise conditionnelle, les caractéristiques de cette dernière s'ajoutent à la précédente

 

Vous pouvez aussi appliquer sur une cellule puis copier la mise en forme conditionnelle sur les autres cellules

 

Utiliser l'outil de recopie de mise en forme pour copier la mise en forme conditionnelle sur d'autres cellules et l'outil pour l'effacer

 

Ouvrir le classeur "représentants.xlsx", feuille "commissions"
Dans la colonne "ventes", mettre en rouge les ventes supérieures à 100 000

 

 

(2)          LES CONDITIONS COMBINÉES

Elles s'appliquent de manière presque identique et en répétant l'opération plusieurs fois.

ONGLET "ACCUEIL"
groupe "style" (5ème bloc)
sélectionner la plage ou <clic g> sur la cellule à mettre en forme
<clic g> sur
<clic g> sur
sélectionner la règle à appliquer
renseigner les conditions d'application et le format correspondant
pour valider

 

 

propose les onglets suivants :

 

Pour affecter des couleurs de police différentes selon les outils, je choisis :
puis
Je sélectionne la règle
je choisis ou
je saisis ou copie le texte
j'indique le format
je valide par
je répète l'opération pour les scies et les tournevis en indiquant leur couleur respective

 

 

indique les règles de la cellule ou colonne en cours
indique les règles de la feuille de travail en cours

 

 

mieux, je désigne une cellule spécifique dans laquelle je mettrais la valeur pour ne pas avoir à revenir dans la règle pour la modifier

 

Ouvrir le classeur "représentants.xlsx", feuille "commissions"
Dans la colonne "ventes", afficher les outils selon les critères suivants :
Pour les ventes supérieures à 50000 €, Police verte sur fond vert
Pour les ventes inférieures à 50000 €, Police bleue sur fond bleu

 

 

 

Dans le même classeur "représentants.xlsx", feuille "outils"
Dans la colonne "outils", afficher les outils selon les critères suivants :
Police jaune/orange pour les marteaux, rouge pour les tournevis, vert pour les scies

(3)          LES VALEURS REMARQUABLES

Excel permet d'attribuer une mise en forme spécifique aux valeurs remarquables (valeurs les plus élevées, les moins élevées, supérieures ou inférieurs à la moyenne).

ONGLET "ACCUEIL"
groupe "style" (5ème bloc)
sélectionner la plage ou <clic g> sur la cellule à mettre en forme
<clic g> sur
<clic g> sur
indiquer la règle à appliquer
renseigner les conditions d'application et le format correspondant
pour valider

 

 

 

Dans le même classeur "représentants.xlsx", feuille "outils"
Dans la colonne "ventes", afficher les valeurs selon les critères suivants :
Rouge pour les valeurs inférieures à la moyenne
Vert pour les valeurs supérieures à la moyenne

 

 

(4)          LES BARRES DE DONNÉES

Elles affichent dans la cellule une barre plus ou moins longue selon la valeur dans la cellule. Elles sont à la fois très esthétiques et très parlantes.

ONGLET "ACCUEIL"
groupe "style" (5ème bloc)
sélectionner la plage ou <clic g> sur la cellule à mettre en forme
<clic g> sur
<clic g> sur
pointer sur les barres pour les visualiser
<clic g> sur la barre à utiliser

 

 

Dans le même classeur "représentants.xlsx", feuille "outils"
Dans la colonne "ventes", compléter la mise en forme conditionnelle par une barre de données bleu foncé

 

(5)          LES NUANCES DE COULEURS

La nuance de la couleur utilisée dans le dégradé représente ici la valeur de la cellule.

ONGLET "ACCUEIL"
groupe "style" (5ème bloc)
sélectionner la plage ou <clic g> sur la cellule à mettre en forme
<clic g> sur
<clic g> sur
pointer sur les nuances pour les visualiser
<clic g> sur la nuance à utiliser

 

 

Dans le même classeur "représentants.xlsx", feuille "chiffre d'affaires"
Dans la colonne "ventes", utiliser une nuance de couleurs "dégradé"

 

 

(6)          LES ICÔNES

Des icônes symbolisent la valeur de la cellule par rapport à l'ensemble des données. Le calcul est différent selon le nombre de symboles du jeu d'icônes.

ONGLET "ACCUEIL"
groupe "style" (5ème bloc)
sélectionner la plage ou <clic g> sur la cellule à mettre en forme
<clic g> sur
<clic g> sur
pointer sur les jeux d'icônes pour les visualiser
<clic g> sur le jeu d'icônes à utiliser

 

 

Dans le même classeur "représentants.xlsx", feuille "chiffre d'affaires"
Dans la colonne "ventes", ajouter à la nuance un jeu d'icônes

 

 

 

 

permet de modifier les critères de comparaison

(7)          LA GESTION DES RÈGLES

Elle permet de combiner les options proposées.

ONGLET "ACCUEIL"
groupe "style" (5ème bloc)
sélectionner la plage ou <clic g> sur la cellule à mettre en forme
<clic g> sur
<clic g> sur
indiquer feuille de calcul ou sélection
effectuer les modifications
pour valider

 

La gestion des règles permet de récapituler les règles appliquées sur une plage, de les modifier ou les supprimer

 

Lorsque pour une condition remplie, vous ne voulez pas afficher des données, pensez toujours à la possibilité d'utiliser la même couleur (y compris le blanc) pour la police et le fond de la cellule

 

Dans le même classeur "représentants.xlsx", feuille "chiffre d'affaires"
Afficher les règles

b)               =SI(test;valeur_si_vrai;valeur_si_faux)

Cette fonction contrôle la valeur affichée par la cellule en fonction d'une condition.

ONGLET "FORMULES"
groupe "bibliothèque de fonctions" (1er bloc)
<clic g> sur la cellule ou la première cellule de la plage à renseigner
<clic g> sur
sélectionner la fonction  
indiquer le test
indiquer la valeur si la condition est remplie
indiquer la valeur si la condition n'est pas remplie
pour valider
recopier éventuellement la formule sur les autres cellules de la plage

 

 

Il est plus simple de renseigner d'abord la première cellule de la plage puis, lorsque le résultat est satisfaisant, de recopier ce résultat sur les autres cellules de la plage

 

Dans le même classeur "représentants.xlsx", feuille "commissions"
Dans la colonne "commissions" calculer la commission des commerciaux pour chaque vente selon les critères suivants :
10% pour les ventes inférieures à 50 000 €
5% pour les ventes supérieures à 50 000 €

 

Plutôt qu'indiquer des valeurs, indiquer l'emplacement de ces valeurs dans la feuille ; cela donne beaucoup plus de souplesse pour toute modification ou simulation

 

Dans le même classeur "représentants.xlsx", feuille "commissions"
Faire un tableau des commissions dans la feuille (en I1:K2 par exemple)

Utiliser ce tableau pour calculer les commissions

 

 

Une fois la cellule renseignée, la fonction SI s'affiche de la manière suivante : 

=SI(test;condition oui;condition non)

DANS LEQUEL :

=égalindique à Excel qu'une fonction suit ;

SIest le nom de la fonction

(...)parenthèsesles parenthèses ouvrantes et fermées encadrent les arguments de la fonction

testdéfinit le test logique

conditionouivaleur que prendra la cellule ou formule qui sera calculée si le test est positif

condition nonvaleur que prendra la cellule ou formule qui sera calculée si letest est négatif

;point virgulecaractères séparateurs indispensables entre test, condition oui
et condition non.

 

Il est possible d'imbriquer des fonctions "si" les unes dans les autres de façon à gérer une multitude de cas ; la fonction doit alors être écrite manuellement
Créer d'abord la fonction "si" principale
Dans la condition "non", indiquer une nouvelle fonction "si"
Répéter l'opération pour chaque condition "non"
etc… (jusqu'à 64 niveaux d'imbrication sont possibles)
Penser à fermer avec le nombre de parenthèses adéquates

 

La source d'erreur la plus fréquente est un nombre incorrect de parenthèses ; heureusement, Excel vous aide en affichant les parenthèses de différentes couleurs et par paires et même en vous proposant de faire seul la correction (à vérifier cependant)

 

Écrivez un minimum dans la formule même et faites au maximum référence à des cellules contenant les valeurs, le texte ou les formules afin d'apporter un maximum de souplesse à votre modèle ; vous pourrez ainsi faire les modifications souhaiteés dans ces cellules sans avoir à modifier la formule

 

Dans le même classeur "représentants.xlsx", feuille "outils"
Faire une colonne "commissions" calculant la commission des commerciaux pour chaque vente selon les conditions suivantes :
10% pour les marteaux / 8% pour les tournevis / 6% pour les pinces / 4% pour les scies

 

 

 

Si les conditions OUI ou NON sont du texte et que vous écrivez la formule, pensez à mettre ce texte entre double guillemets  

 

Pour définir des catégories de valeurs, encadrez ces dernières avec des opérateurs "inférieur" ou "supérieur" successifs. Penser à utiliser selon les besoins "< inférieur" ou "<= inférieur ou égal" et "> supérieur" ou ">= supérieur ou égal"

 

Pour définir des catégories de 0 à 100 000 par tranche de 20 000
<20 000 définit la première catégorie
<40 000 définit la seconde (entre 20000 et 40000 puisque ceux inférieurs à 20000 ont déjà été extraits)
<60000 définit la troisième (entre 40000 et 60000)
<80000 définit la troisième (entre 60000 et 80000)
<100000 définit la dernière (entre 80000 et 100000)

 

  Dans le même classeur "représentants.xlsx", feuille "chiffre d'affaires"
Faire une colonne "commissions" calculant la commission des commerciaux, soit :
10% pour la part de chiffre d'affaire réalisé inférieure à 50000 €
8% pour la part comprise entre 50 et 75000 €
6% pour la part comprise entre 75 et 100000 €
4% pour la part supérieure à 100000 €

 

 

 

Pour les formules et les fonctions très complexes, si vous n'y arrivez pas du premier coup, vous pouvez mettre " (guillemets français) devant le signe = (égal) de la formule qui est alors acceptée (mais comme texte) ; vous pourrez y revenir plus tard en enlevant les guillemets (pour qu'elle soit de nouveau considérée comme une formule)

 

Afficher le classeur "emprunt.xlsx"
Connaissant le montant de la mensualité de remboursement pour un emprunt
de 300 000 F sur 8 ans au taux de 8%
Calculer la part d'intérêt et la part de capital pour chaque mensualité
Faire le cumul intérêt et capital dans les colonnes adéquates.
Ne plus afficher les résultats lorsque l'emprunt est remboursé
Figer les volets de titre et protéger votre travail.

 

 

c)                 =FAUX()""

Cette fonction renvoie la valeur logique "faux".

 

d)               =NON(valeur_logique)

Cette fonction inverse la valeur de l'argument ; si celui-ci est vrai, elle renvoie faux et réciproquement.

 

e)                =OU(valeur_logique1;valeur_logique2...)

Cette fonction donne la valeur "vrai" si au moins un argument est vrai, "faux" sinon.

 

f)                 =VRAI()

Cette fonction donne la valeur logique "vrai".

 

Ouvrir le classeur fonctlogi.xlsx et tester les fonctions