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.
1. FORMAT CONDITIONNEL
Il permet d'afficher une mise en forme différente selon la valeur de la cellule.
a) 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
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
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
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", 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
b) 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
c) 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é
d) 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é"
e) 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
f) 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 la feuille de calcul
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
2. =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 première cellule de la plage
<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 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)
Une fois la cellule renseignée, la fonction SI s'affiche de la manière suivante :
=SI(test;condition oui;condition non)
dans lequel :
= égal indique à Excel qu'une fonction suit ;
SI est le nom de la fonction
(...) parenthèses les parenthèses ouvrantes et fermées encadrent les arguments de la fonction
test définit le test logique
condition oui valeur que prendra la cellule ou formule qui sera calculée si le test est positif
condition non valeur que prendra la cellule ou formule qui sera calculée si letest est négatif
; point virgule caractè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 toutes
les modifications que vous souhaiterez 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.
3. =FAUX()
Cette fonction renvoie la valeur logique "faux".
4. =NON(valeur_logique)
Cette fonction inverse la valeur de l'argument ; si celui-ci est vrai, elle renvoie faux et réciproquement.
5. =OU(valeur_logique1;valeur_logique2...)
Cette fonction donne la valeur "vrai" si au moins un argument est vrai, "faux" sinon.
6. =VRAI()
Cette fonction donne la valeur logique "vrai".
Ouvrir
le classeur
fonctlogi.xlsx et tester les fonctions