IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)

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.

       Version PDF (Miroir)   Version hors-ligne (Miroir)
Viadeo Twitter Facebook Share on Google+        



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 à".

warning 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.

warning 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
info 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.

warning 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



               Version PDF (Miroir)   Version hors-ligne (Miroir)

Valid XHTML 1.0 TransitionalValid CSS!