Exprimer des critères avec Excel
Date de publication : 11 janvier 2012.
Par
Pierre Fauconnier (http://fauconnier.developpez.com) (Blog)
Dans ce tutoriel, j'aborde les notions de critérisation avec Excel. Vous apprendrez
à rédiger vos critères tant pour les fonctions qui les utilisent (fonctions conditionnelles,
de recherche, de bases de données) que pour l'utilisation des filtres automatiques ou avancés.
I. Introduction
I-A. Objectifs
I-B. Prérequis
II. Synopsis
II-A. Exemples de critères formulés
II-B. Exemples de critères évaluant tout ou partie de la valeur d'une cellule
III. Les tests logiques
III-A. Constantes logiques
III-B. Opérateurs logiques
III-C. Fonctions logiques, fonctions d'information
IV. Les critères formulés
V. Critères utilisant le résultat de formules ou de comparaison
V-A. Formulation condensée de la fonction SI()
V-B. Transtypage des constantes logiques VRAI et FAUX
V-C. SOMME MATRICIELLE ( et SOMMEPROD )
VI. Conclusions
VII. Remerciements
I. Introduction
Pouvoir exprimer des critères ou des conditions lors de recherches ou de filtres
ainsi qu'au sein de formules conditionnelles me semblent devoir faire partie
des bases d'Excel, qui est avant tout autre chose un analyseur de données.
Au vu du temps que je dois passer à expliquer ces notions aux participants
de mes formations, il m'a semblé utile d'écrire un tutoriel qui ferait "le tour du sujet".
I-A. Objectifs
I-B. Prérequis
II. Synopsis
Nous pouvons scinder les critères en deux catégories :
1. les critères formulés, qui sont donc exprimés par une formule qui doit
renvoyer une constante logique, c'est-à-dire VRAI ou FAUX ;
2. les critères qui examinent tout ou partie de la valeur d'une cellule.
Il sera important de faire la distinction entre les formulations, car
certaines fonctions d'Excel n'acceptent que les critères formulés, alors que
d'autres n'acceptent que les critères s'appuyant sur la valeur des cellules.
II-A. Exemples de critères formulés
II-B. Exemples de critères évaluant tout ou partie de la valeur d'une cellule
III. Les tests logiques
A la base des critères que nous allons formuler en Excel, il y a un test logique.
Ce test revient à poser un postulat qui va être vérifié ou non, et qui renverra donc
une valeur logique. Cette valeur logique peut être VRAI ou FAUX
III-A. Constantes logiques
Excel expose deux constantes logiques qui sont VRAI et FAUX.
III-B. Opérateurs logiques
Excel expose six opérateurs logiques qui permettent la comparaison :
- = qui signifie "est égal à" ;
- <> qui signifie "est différent de " ;
- < qui signifie "est plus petit que" ;
- <= qui signifie "est plus petit que ou égal à " ;
- > qui signifie "est plus grand que" ;
- >= qui signifie "est plus grand que ou égal à".
 |
Il faut noter dès maintenant que l'égalité testée avec l'opérateur =
n'est pas binaire, mais textuelle. En d'autres mots, il n'y a pas de différenciation
de la casse des caractères. Si l'on souhaite tenir compte de la casse, on devra
utliser la fonction EXACT(Texte1;Texte2)
|
III-C. Fonctions logiques, fonctions d'information
Dans sa bibliothèque, Excel propose des fonctions logiques qui permettent
de vérifier la véracité d'un postulat (voir fig. IIIC_001).
Ces fonctions renvoient une des valeurs logiques VRAI ou FAUX :
- ET(ValeurLogique1;ValeurLogique2;...) renvoie VRAI si tous les
paramètres renvoient VRAI ;
- OU(ValeurLogique1;ValeurLogique2;...) renvoie VRAIsi au moins un
des paramètres renvoient VRAI ;
- VRAI() renvoie VRAI et est souvent remplacé par
la constante logique VRAI ;
- FAUX() renvoie FAUX et est souvent remplacé par
la constante FAUX ;
- NON(ValeurLogique) inverse la valeur logique passée en argument.
|
Il est important de noter que ET() et OU() sont des fonctions et non des
opérateurs. En Excel, on ne dira pas Prénom = "Pierre" ou "Martine" ni
Prénom = "Pierre" OU Prénom = "Martine" mais bien OU(Prénom = "Pierre" ;
Prénom = "Martine")
|
Les valeurs logiques passées en arguments peuvent être les constantes logiques
VRAI ou FAUX, des références à des cellules contenant une valeur logique
ou des résultats de formules renvoyant une valeur logique.
Les fonctions d'information qui renvoient VRAI ou FAUX
En plus de ces fonctions logiques, certaines fonctions d'information
renvoient l'une des deux constantes logiques. Elles commencent par le mot EST.
Voici la liste de ces fonctions en Excel 2010 (voir fig. IIIC_002).
Les fonctions d'information qui renvoient VRAI ou FAUX
 |
Nous verrons dans la suite de ce cours comment formuler tout cela en Excel.
|
IV. Les critères formulés
V. Critères utilisant le résultat de formules ou de comparaison
Nous avons vu ci-dessus les limitations des critères des formules conditionnelles.
Excel permet de lever ces limites par l'emploi de formules matricielles qui nous
offrent la possibilité de tester des conditions dont:
- une au moins doit être rencontrée ;
- une seule doit être rencontrée ;
- un certain nombre doit (au moins) être rencontré.
Excel lève également la limitation sur ce sur quoi porte la condition,
puisque nous pouvons tester des valeurs dépendant du résultat de fonctions
portant sur des cellules, là où les formules vues plus haut ne travaillent que sur
tout ou partie de la valeur d'une cellule.
C'est donc une ouverture considérable qui est proposée ici, puisque la seule
limitation sera votre (absence de) maîtrise de ce qui va suivre ;-)
V-A. Formulation condensée de la fonction SI()
Pour renvoyer une valeur en fonction d'une condition, nous avons
l'habitude d'utiliser SI(Test logique; [Valeur si VRAI] ; [Valeur si FAUX]).
Ainsi, il est possible de renvoyé "PAYÉ" dans une cellule lorsqu'une date
de paiement est saisie en regard d'une facture en utilisant la formule (voir fig. 1) :
=SI(NON(ESTVIDE(D2));"Payée";"Ouverte")
Fig. 1 : Utilisation habituelle de la syntaxe de la fonction SI()
On pourrait souhaiter renvoyer la constante logique VRAI dans la colonne
à la place des mots Payée et Ouverte et FAUX dans les autres cas.
Dès lors, la formule devient
=SI(NON(ESTVIDE(D2));VRAI;FAUX) (voir fig. 2)
Fig. 2 : Utilisation de SI() pour renvoyer VRAI ou FAUX
Lorsque l'on souhaite obtenir VRAI ou FAUX dans la cellule, on peut également
poser le postulat à vérifier, c'est-à-dire le test logique exprimé dans la fonction SI, et
laisser le soin à Excel de renvoyer VRAI ou FAUX pour confirmer ou infirmer notre affirmation.
La formule devient alors =NON(ESTVIDE(D2)) (voir fig. 3).
Fig. 3 : Pour renvoyer VRAI ou FAUX, SI() n'est pas nécessaire
Cette utilisation condensée de la fonction SI() sera très souvent utilisée dans la suite
de ce tutoriel, pour deux raisons :
1. syntaxe de formule plus concise ;
2. impossibilité d'utiliser SI() au sein de SOMMEPROD.
V-B. Transtypage des constantes logiques VRAI et FAUX
Le transtypage (casting en anglais) est l'action de convertir une donnée
d'un type en un autre. Ce transtypage peut être explicite, grâce à
l'utilisation de fonctions de conversion, ou implicite. Dans ce dernier cas,
le logiciel, Excel en l'occurrence, tente de transformer la valeur en fonction
de la formule qui utilise la formule.
Excel peut modifier les constantes logiques VRAI et FAUX en valeurs numériques
en leur attribuant respectivement 1 et 0.
|
Cette conversion est propre à Excel. La plupart des logiciels et langages
transforment VRAI en -1 et FAUX en 0.
|
V-C. SOMME MATRICIELLE ( et SOMMEPROD )
VI. Conclusions
VII. Remerciements
