Exceller

Comment utiliser les critères de formule (50 exemples)

How Use Formula Criteria

L'une des compétences les plus importantes pour créer des formules utiles est de créer Critères – la partie d'une formule qui décide de ce qu'il faut inclure ou exclure dans un calcul. Cependant, il peut être étonnamment difficile de créer des critères efficaces car cela nécessite une bonne compréhension de la façon dont Excel gère les données. Si vous avez déjà passé un après-midi à résoudre une formule qui semble devoir « fonctionner », vous savez ce que je veux dire :)





Ce guide a pour but de vous aider à construire des formules qui fonctionnent premier temps.

Remarque : les spécialistes de la langue souligneront que « critère » est au singulier et que « critère » est au pluriel, mais je vais utiliser « critère » dans les deux cas pour simplifier les choses.





Les noms de fonction sur fond sombre ci-dessous sont des liens vers plus d'informations.

A quoi servent les critères ?

Entre autres, les critères :

  • Flux logique direct avec logique IF/THEN
  • Restreindre le traitement aux valeurs correspondantes uniquement
  • Créer des sommes et des comptes conditionnels
  • Filtrer les données pour exclure les informations non pertinentes
  • Déclencher des règles de mise en forme conditionnelle

Pour aider à planter le décor, regardons trois exemples de critères en action.



Exemple 1

Dans l'écran ci-dessous, F3 contient cette formule :

 
= IF (E3>30,'Yes','No')

Exemple de critères de formule #1

Traduction : Si la valeur dans E3 est supérieure à 30, renvoie 'Oui', sinon renvoie 'Non'.

Ici, E3>30 est le critère utilisé dans IF pour déterminer si la formule doit renvoyer « Oui » ou « Non » pour chaque facture.

Exemple #2

Dans l'exemple suivant, D3 contient cette formule :

 
= IF ( OR (B3='red',B3='green'),C3*1.1,C3)

Exemple de critère de formule n°2 - augmenter le prix si rouge ou vert

compter le nombre de valeurs différentes dans Excel

Traduction : si B3 est 'rouge' ou 'vert', augmentez le prix de 10 %. Sinon, retournez le prix d'origine.

Exemple #3

Dans cet exemple, la fonction SUMIFS est utilisée pour additionner le total uniquement lorsque la couleur est « rouge » :

 
= SUMIFS (E3:E7,B3:B7,'red')

Exemple de critère de formule #2 - SUMIF lorsque la couleur est

Traduction : additionner les valeurs dans E3:E7 lorsque la valeur dans B3:B7 est « rouge ».

Principes de base des critères

Cette section couvre les éléments constitutifs des critères de formule et quelques moyens simples de vérifier que les critères fonctionnent comme prévu.

Quels sont les critères ?

Les critères sont des expressions logiques qui renvoient VRAI ou FAUX, ou leurs équivalents numériques, 1 ou 0.

C'est ça.

L'astuce consiste à construire des critères de manière à ce qu'ils ne renvoient VRAI que lorsque le test répond à vos critères exacts. Dans tous les autres cas, les critères doivent renvoyer FALSE ou zéro. Si vous pouvez maîtriser cette idée, vous avez les bases pour construire et comprendre de nombreuses formules avancées.

Opérateurs logiques

Les critères utilisent souvent les opérateurs logiques répertoriés dans le tableau ci-dessous.

Opérateur Sens Exemple
= Égal à = A1 = 10
Pas égal à = A110
> Plus grand que = A1> 100
< Moins que = A1<100
> = Plus grand ou égal à = A1> = 75
<= Inférieur ou égal à = A1<=0

Les opérateurs logiques peuvent être combinés de différentes manières, comme le montrent les exemples ci-dessous.

Fonctions logiques

Excel possède plusieurs « fonctions logiques » qui peuvent être utilisées pour construire et utiliser des conditions. Le tableau ci-dessous répertorie les fonctions logiques clés.

Fonction But
SI Tester une condition de flux logique direct
IFS Tester plusieurs conditions en flux logique direct
NE PAS Inverser les critères ou les résultats
ET Testez plusieurs conditions, retournez VRAI si toutes sont VRAIES
OU Tester plusieurs conditions, renvoyer VRAI si au moins une est VRAI
OU exclusif OU exclusif - renvoie VRAI si l'un ou l'autre, pas les deux
SIERREUR Piège les erreurs et renvoie des résultats alternatifs

Critères multiples

Naturellement, il existe de nombreux cas où vous souhaiterez utiliser plusieurs critères. Dans des situations simples, vous pouvez utiliser les fonctions AND, OR et NOT. Voici quelques exemples:

 
= AND (A1>0,A1<10) // greater than 0 and less than 10 = OR (A1='red',A1='blue') // red or blue = NOT ( OR (A1='red',A1='blue')) // not red or blue = AND ( ISNUMBER (A1),A1>100) // number greater than 100

Caractères génériques

Excel fournit trois « caractères génériques » pour faire correspondre le texte dans les formules :

Personnage Nom But
* Astérisque Faire correspondre zéro ou plusieurs caractères
? Point d'interrogation Faites correspondre n'importe quel caractère
~ Signe d'accent Match littéral joker

Les caractères génériques peuvent être utilisés seuls ou combinés pour obtenir une variété de comportements de correspondance :

Usage Comportement Correspondra
? N'importe quel personnage 'A', 'B', 'c', 'z', etc.
?? Deux caractères quelconques « AA », « AZ », « zz », etc.
??? Trois caractères quelconques 'Jet', 'AAA', 'ccc', etc.
* Tous les caractères 'pomme', 'POMME', 'A100', etc.
*e Se termine par 'th' « bain », « quatrième », etc.
c * Commence par 'c' 'Cat', 'CAB', 'cindy', 'candy', etc.
? * Au moins un caractère 'a', 'b', 'ab', 'ABCD', etc.
??? - ?? 5 caractères avec tiret 'ABC-99', '100-ZT', etc.
* ~? Se termine par un point d'interrogation « Bonjour ? », « Quelqu'un à la maison ? », etc.
*xyz* Contient 'xyz' 'le code est XYZ', '100-XYZ', 'XyZ90', etc.

Voici quelques exemples d'utilisation de caractères génériques pour les critères de la fonction COUNTIFS.

 
= COUNTIFS (A1:A100,'*red*') // count cells that contain 'red' = COUNTIFS (A1:A100, 'www*') // count cells starting with 'www' = COUNTIFS (A1:A100,'?????') // count cells with 5 characters

Toutes les fonctions n'autorisent pas les caractères génériques. Voici une liste des fonctions courantes qui le font :

Notez que la fonction SI est ne pas sur cette liste. Pour obtenir un comportement générique avec IF, vous pouvez combiner les fonctions SEARCH et ISNUMBER, comme décrit ci-dessous.

Critères de test

La manière classique de tester les critères est de les envelopper dans la fonction SI. Par exemple, pour vérifier « rouge » ou « bleu », nous pouvons envelopper la fonction OR à l'intérieur de IF comme ceci :

 
= IF ( OR (B3='red',B3='blue'),'OK', '')

Critères de formule - test avec la fonction SI

Traduction : si la couleur est 'rouge' ou 'bleu', retourne 'OK'. Sinon ne retourne rien.

Cependant, vous pouvez également tester des critères directement sur la feuille de calcul sous forme de formule. Supposons que vous souhaitiez traiter des valeurs de 80 et plus. Dans l'écran ci-dessous, C3 contient cette formule, copiée.

 
=B3>=80

Critères de formule - test directement sur la feuille de calcul

Traduction : la valeur en B3 est supérieure ou égale à 80.

Sans IF ou une autre fonction, nous obtenons uniquement un résultat VRAI ou FAUX, mais il suffit de vérifier que les critères fonctionnent comme prévu.

Ne vous laissez pas décourager par le signe égal (=) lorsque vous testez des critères sous forme de formule. Toutes les formules Excel doivent commencer par un signe égal, il doit donc être inclus. Supprimez le signe égal lorsque vous déplacez des critères dans une autre formule.

Une autre façon de tester les critères consiste à utiliser F9 pour évaluer les critères en place. Il suffit de sélectionner soigneusement une expression logique et d'appuyer sur F9. Excel évaluera immédiatement l'expression et affichera le résultat.

Vidéo: Comment utiliser F9 pour déboguer une formule .

Ajout de critères aux formules

Bien sûr, dans la plupart des cas, vous ne voulez pas retourner VRAI ou FAUX à une cellule, vous voulez retourner une autre valeur basée sur des critères renvoyant VRAI ou FAUX. Pour ce faire, supprimez simplement le signe égal et ajoutez les critères si nécessaire dans la formule.

Dans l'exemple ci-dessous, la formule C3 contient cette formule, qui utilise les critères ci-dessus comme test logique dans IF :

 
= IF (B3>=80,'Pass','Fail')

Ajouter des critères à une formule

Traduction : si la valeur en B3 est supérieure ou égale à 80, retourne 'Pass'. Sinon, retournez « Echec ».

Voir aussi : 23 astuces pour les formules ( vidéo | article )

Exemples de critères

Cette section montre des exemples sur la façon de créer des critères pour accomplir une variété de tâches pour différents types de contenu.

Vide ou pas vide

Il existe plusieurs façons de vérifier les cellules vides ou non vides. Pour renvoyer VRAI si A1 est vide, vous pouvez utiliser soit :

 
= ISBLANK (A1) =A1=''

Pour inverser la logique et vérifier les cellules non vides, vous pouvez utiliser :

 
= NOT ( ISBLANK (A1)) =A1''

Une autre façon de tester une cellule vide consiste à vérifier le nombre de caractères :

 
= LEN (A1)=0

Si le compte est zéro, la cellule est « vide ». Cette formule est utile lors du test de cellules pouvant contenir des formules renvoyant des chaînes vides (''). ISBLANK(A1) renverra FALSE si une formule renvoie une chaîne vide dans A1, mais LEN(A1)=0 renverra TRUE.

Critères pour le texte

Pour renvoyer TRUE si une cellule contient 'red', vous pouvez utiliser :

 
=A1='red' 

Pour inverser la logique, vous pouvez utiliser la fonction NOT ou l'opérateur non égal à () comme ceci :

 
= NOT (A1='red') =A1'red'

Notez dans chaque cas que le texte EST entouré de guillemets doubles (par exemple 'rouge'). Si vous n'utilisez pas de guillemets, Excel pensera que vous essayez de référencer une plage nommée ou une fonction et renverra l'erreur #NAME.

Critères pour les nombres

Pour tester si un A1 est égal à 5, vous pouvez utiliser des critères comme celui-ci :

 
=A1=5 // TRUE if A1 equals 5

Voici quelques autres exemples de critères pour tester des valeurs numériques :

 
=A1<100 // less than 100 =A1>=1 // greater than or equal to 0 =A10 // not equal to zero = AND (A1>0,A1<5) // greater than zero, less than 5 = MOD (A1,3)=0 // value is a multiple of 3

Les numéros d'avis ne sont PAS entourés de guillemets doubles. Si vous mettez un nombre entre guillemets, vous dites à Excel de traiter le nombre comme du texte, ce qui rendra les critères inutiles. N'oubliez pas non plus que formatage des nombres dans Excel n'affecte que l'affichage et ne modifie en aucun cas les données numériques. N'incluez pas les signes de dollar ($), les signes de pourcentage (%) ou d'autres informations de formatage lors de la création de critères pour tester les nombres.

Critères pour les dates

Les dates dans Excel ne sont que des nombres, ce qui signifie que vous êtes libre d'utiliser des opérations mathématiques ordinaires sur les dates si vous le souhaitez. Avec les dates de commande dans la colonne A et les dates de livraison dans la colonne B, cette formule dans la colonne C marquera les délais de livraison supérieurs à 3 jours comme « en retard » :

 
= IF ((B2-A2)>3,'Late','')

Excel fournit également un grand nombre de fonctions spécifiques pour travailler avec des dates. Par exemple, pour vérifier si une date est « dans le futur », vous pouvez utiliser la fonction AUJOURD'HUI comme ceci :

 
=A1> TODAY ()

Exemple de date de critères de formule - supérieure à aujourd'hui

Pour vérifier si une date survient dans les 30 prochains jours, la formule peut être étendue à :

 
= AND (A1> TODAY (),A1<=( TODAY ()+30))

Traduction : SI A2 est supérieur à aujourd'hui ET inférieur ou égal à aujourd'hui + 30 jours, renvoie VRAI.

Excel somme une colonne basée sur une autre

Voici quelques autres exemples de critères pour les dates, en supposant que A1 contienne une date valide :

 
= DAY (A1)>15 // greater than 15th = MONTH (A1)=6 // month is June = YEAR (A1) = 2019 // year is 2019 = WEEKDAY (A1)=2 // date is a Monday

Le moyen le plus sûr d'insérer une date valide dans les critères consiste à utiliser la fonction DATE, qui accepte l'année, le mois et le jour comme arguments séparés. Voici quelques exemples :

 
=A1> DATE (2019,1,1) // after Jan. 1, 2019 = AND (A1>= DATE (2018,6,1),B4<= DATE (2018,8,31)) // Jun-Aug 2018

Critères de temps

Les temps sont des nombres fractionnaires dans Excel, vous pouvez donc utiliser des mathématiques simples pour le temps dans certains cas. Par exemple, pour vérifier si une heure dans A1 est après 12h00 (plus de 12 heures), vous pouvez utiliser :

 
=A1>.5

Cela fonctionne car 1 jour = 24 heures, donc une demi-journée = 12 heures.

Pour un travail plus granulaire, Excel dispose de fonctions spéciales pour extraire le temps par composant. Par exemple, avec l'heure 8h45 dans la cellule A1 :

 
= HOUR (A1) // returns 8 = MINUTE (A1) // returns 45 = SECOND (A1) // returns 0

Le moyen le plus sûr d'insérer une heure dans les critères est d'utiliser la fonction TIME. Voici quelques exemples:

 
=A1> TIME (9,15,0) // after 9:15 AM = AND (A1>= TIME (9,0,0),A1<= TIME (17,0,0)) // 9 AM to 5 PM

Critères pour SUMIFS, COUNTIFS, etc.

Les critères pour SUMIFS, COUNTIFS, AVERAGEIFS et les fonctions similaires basées sur une plage suivent des règles légèrement différentes. En effet, les critères sont divisés en deux parties (plage de critères et critères), ce qui a un impact sur la syntaxe lorsque les critères incluent des opérateurs.

Des critères simples basés sur l'égalité ne nécessitent pas de traitement particulier. L'opérateur égal (=) est implicite, il n'est donc pas nécessaire de l'inclure dans les critères :

 
= COUNTIFS (A1:A100,10) // count cells equal to 10 = COUNTIFS (A1:A100,'red') // count cells that equal 'red'

Cependant, les choses changent lorsque nous ajoutons des opérateurs :

 
= COUNTIFS (A1:A100,'>10') // count cells greater than 10 = COUNTIFS (A1:A100,'<0') // count cells less than zero

Remarquez les guillemets ('') autour des critères ? Ceux-ci sont requis lorsque les critères incluent un opérateur dans ces fonctions.

Critères pour les types de données

Excel permet trois types de données principaux : texte, nombres et logiques. Les dates, heures, pourcentages et fractions ne sont que des nombres avec formatage des nombres appliqué pour changer la façon dont ils sont affichés. Par défaut, les nombres sont alignés à droite, le texte est aligné à gauche et les valeurs logiques sont centrées. Mais un utilisateur peut remplacer l'alignement manuellement, ce n'est donc pas un bon test de type.

Excel fournit trois fonctions que vous pouvez utiliser pour vérifier les types de données : ISTEXT, ISNUMBER et ISLOGICAL. Ces fonctions renvoient VRAI ou FAUX. Dans l'écran ci-dessous, les cellules D3, F3 et H3 contiennent ces formules, copiées :

 
= ISTEXT (B3) = ISNUMBER (B3) = ISLOGICAL (B3)

Critères de formule - utilisation de fonctions pour tester les types de données

Pour utiliser ces fonctions comme critères, il suffit de les placer ensuite au bon endroit d'une formule. Par exemple, pour vérifier si A1 contient un nombre, vous pouvez utiliser ISNUMBER comme test logique dans IF comme ceci :

 
= IF ( ISNUMBER (B3),'OK','Invalid')

Remarque : les formules ne sont pas un type de données, mais vous pouvez vérifier les formules avec le Fonction ISFORMULE :

 
= ISFORMULA (A1) // TRUE if A1 contains formula

Devenir fantaisiste

Les exemples ci-dessus montrent les principes fondamentaux de l'utilisation de critères dans les formules, il existe de nombreuses façons de rendre les critères plus sophistiqués. Cette section explore quelques techniques.

Rendre les critères variables

Il est souvent utile de rendre les critères variables, en référençant une cellule sur la feuille de calcul. Par exemple, dans la feuille de calcul ci-dessous, la note de passage se trouve dans la cellule E3 et la formule pour déterminer la réussite ou l'échec ressemble à ceci :

 
= IF (B3>=$E,'Pass','Fail')

Rendre les critères variables - exemple de score de test

Le fait de placer la note de passage dans la cellule E3 permet de la modifier facilement à tout moment sans modifier les formules. Notez que la référence à $E est absolu pour empêcher les modifications lorsque la formule est copiée.

Rendre les critères variables dans COUNTIFS, SUMIFS, etc.

Comme précédemment, si les critères testent l'égalité, aucun traitement spécial n'est nécessaire :

 
= COUNTIF (range,A1) // count cells equal to A1

Cependant, si les critères incluent des opérateurs, vous devrez utiliser enchaînement . Par exemple, pour compter les cellules plus grand que A1, vous devrez joindre '>' à 'A1' comme ceci :

 
= COUNTIF (range,'>'&A1)

La concaténation s'exécute en premier. Si A1 contient le nombre 10, voici la formule après concaténation :

 
= COUNTIF (range,'>10')

Notez que le modèle est le même que celui expliqué précédemment - si les critères incluent des opérateurs, il doit apparaître entre guillemets ('').

Voici d'autres exemples d'utilisation de la concaténation dans les critères :

 
= COUNTIF (range,'<'&B1) // count less than value in B1 = COUNTIF (range,''&'') // count not blank cells = COUNTIF (range,'*'&B1&'*') // count contains text in B1 = COUNTIF (range,'>'& TODAY ()) // count dates in future = COUNTIF (range,'<'& TODAY ()+7) // count up to 7 days from today

Contient du texte spécifique

Une situation délicate est lorsque vous voulez tester si une cellule contient texte spécifique. Pour les fonctions qui prennent en charge les caractères génériques (comme COUNTIFS, SUMIFS, etc.), vous pouvez utiliser des caractères génériques pour ce faire. Par exemple, pour compter les cellules qui contiennent « rouge » n'importe où dans une cellule avec COUNTIFS, vous pouvez utiliser un astérisque comme celui-ci :

 
= COUNTIFS (A1:A100,'*red*')

Cependant, de nombreuses autres fonctions (comme la fonction IF) ne prennent pas en charge les caractères génériques. Dans ce cas, vous pouvez combiner ISNUMBER et SEARCH pour créer des critères qui vérifient une cellule pour une correspondance partielle. Dans l'écran ci-dessous, D3 contient cette formule :

 
= ISNUMBER ( SEARCH (C3,B3))

Critères de formule - la cellule contient un texte spécifique

Vous pouvez utiliser cette expression comme critère dans IF comme ceci

 
= IF ( ISNUMBER ( SEARCH ('red',A1)),'red', '')

Traduction : si « rouge » se trouve n'importe où dans A1, renvoie « rouge ».

Cela fonctionne car SEARCH renvoie une position numérique si 'red' est trouvé, et ISNUMBER renvoie TRUE. Sinon, SEARCH renvoie une erreur et ISNUMBER renvoie FALSE. Pour plus de détails, voir cette page .

FI imbriqués

Les formules SI imbriquées sont souvent utilisées pour vérifier plusieurs critères et renvoyer plusieurs résultats. En général, le défi consiste à construire des FI imbriqués afin que les critères s'exécutent dans le bon ordre. Par exemple, voici une formule IF imbriquée qui attribue une note alphabétique en fonction d'un score numérique :

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C', IF (C5<95,'B','A'))))

Exemple d'IF imbriqué pour l'attribution de notes

Notez que nous testons d'abord les scores faibles, puis les scores progressivement plus élevés.

Suite: 19 conseils pour les FI imbriqués (avec variantes)

Constantes de tableau dans les critères

Les constantes de tableau sont des tableaux codés en dur avec des valeurs fixes comme ceci : {'A','B','C'}. Ils peuvent parfois être utilisés comme critères pour créer des critères logiques OU simples. Par exemple, dans l'écran ci-dessous, la cellule F4 contient cette formule :

 
= SUM ( SUMIFS (C3:C7,B3:B7,{'red','gold'}))

Critères de formule avec constantes matricielles

Traduction : SUM ventes où la couleur est « rouge » OU « or ».

Comme nous donnons à SUMIFS deux valeurs pour les critères, il renvoie deux résultats. La fonction SUM renvoie alors la somme des deux résultats.

Critères de formule matricielle simple

Les formules matricielles sont un sujet compliqué, mais les critères pour les formules matricielles simples peuvent être assez simples. Un exemple classique consiste à utiliser la fonction SI pour « filtrer » les valeurs qui devraient être exclues, en traitant le résultat avec une autre fonction.

Dans l'écran ci-dessous, la formule en G4 est :

 
{= MAX ( IF (regions=F4,totals))}

où « régions » est le plage nommée B3:B8 et 'totaux' est la plage nommée D3:D8.

Remarque : il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée.

Le résultat est la valeur supérieure pour chaque région.

Critères de formule pour une formule matricielle simple

Pour les critères, on utilise l'expression :

 
regions=F4

Cela compare toutes les valeurs de région avec « Ouest » à partir de F4 et renvoie le résultat de tableau suivant dans le test logique pour IF :

 
{TRUEFALSETRUEFALSETRUEFALSE}

Le tableau final renvoyé par IF ressemble à ceci :

 
{10500FALSE12500FALSE11800FALSE}

Seules les valeurs associées à la région « Ouest » font partie du tableau. Les valeurs associées à la région « Est » sont FAUX.

La fonction MAX renvoie ensuite la plus grande valeur du tableau, en ignorant toutes les valeurs FAUX.

Critères de formule avancés

Vous trouverez ci-dessous des liens vers des exemples de critères de formule plus avancés. Chaque lien a une capture d'écran et une explication complète.

Plus de ressources sur les formules

Les liens suivants contiennent des informations plus détaillées sur les formules Excel :

Auteur Dave Bruns


^