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')
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)
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')
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', '')
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
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')
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 ()
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)
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')
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))
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'))))
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'}))
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.
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.
- Compter les cellules qui contiennent des erreurs
- Somme si la valeur est égale à un parmi plusieurs
- COUNTIFS avec plusieurs critères et logique OU
- Obtenir la nième plus grande valeur avec des critères
- Additionner les n valeurs supérieures avec des critères
- INDEX et MATCH avec plusieurs critères
Plus de ressources sur les formules
Les liens suivants contiennent des informations plus détaillées sur les formules Excel :
- Comment construire des formules logiques (vidéo)
- 19 conseils pour les formules IF imbriquées
- Plus de 30 formules de mise en forme conditionnelle
- Formule de base (formation rémunérée)