edubun
Français
English한국어EspañolPortuguêsFrançaisDeutschItalianoРусскийBahasa Indonesia日本語
Accueil›Tableurs et données›Formules de tableur

AND, OR et IF imbriqués

Une note de 81 avec 70 % de présence réussit le test de la note et échoue au test de présence. AND exige les deux ; OR n'en exige qu'un.

ContexteUn exemple inventé. Une professeure prépare une feuille de notes la veille de la date limite. La règle : A pour 90 et plus, B pour 80, C pour 70, D pour 60 et F en dessous. Elle écrit les tests dans l'ordre où ils lui viennent, en commençant par le plus bas : =IF(B2>=60, "D", IF(B2>=70, "C", ...)). Tous les élèves qui ont 60 ou plus obtiennent D, y compris celui qui a eu 93. Aucune erreur ne s'affiche. Un élève le lui signale le lendemain matin. La solution consiste à inverser l'ordre, et la raison est expliquée plus bas.

Deux conditions à la fois : AND et OR

La même professeure a une deuxième règle. Pour être reçu, un élève doit avoir au moins 60 et une présence d'au moins 80 %. AND prend un ou plusieurs tests (Excel en accepte jusqu'à 255) et ne renvoie TRUE que lorsque chacun d'eux vaut TRUE : =AND(B2>=60,C2>=80). OR renvoie TRUE quand au moins un test vaut TRUE : =OR(B2>=90,C2=100) pourrait signaler pour un prix un élève qui a une note très élevée ou qui n'a manqué aucun cours. NOT change TRUE en FALSE et inversement, ce qui convient aux règles formulées avec « sauf si ».

Seuls, AND et OR ne donnent que TRUE ou FALSE. Place-les dans IF pour transformer ce résultat en mots : =IF(AND(B2>=60, C2>=80), "Yes", "No"). Les tests à l'intérieur peuvent viser des colonnes différentes, comparer du texte ou utiliser des calculs. Dans les plages, AND et OR ignorent le texte et les cellules vides ; s'ils ne trouvent rien à tester, ils renvoient #VALUE!. Le tableau ci-dessous applique les deux tests, une note d'au moins 60 et une présence d'au moins 80, à quatre élèves.

Dis la règle avec des mots simples avant de l'écrire. « Reçu si la note vaut au moins 60 et la présence au moins 80 » se traduit directement par AND ; « reçu si l'un des deux tests est réussi » se traduit par OR. Une règle qui mélange les deux, comme « au moins 60, et soit 80 % de présence, soit un certificat médical », demande une fonction dans l'autre : =AND(B2>=60, OR(C2>=80, D2="note")). Écris d'abord la partie intérieure. Vérifie qu'elle donne TRUE et FALSE là où tu l'attends, et seulement ensuite enveloppe-la.

NotePrésence %ANDOR
9395TRUETRUE
8170FALSETRUE
6688TRUETRUE
5570FALSEFALSE

Pourquoi le 93 de l'histoire est devenu un D

IF donne deux résultats. Pour en avoir plus, place un autre IF à la place de la réponse fausse, et le barème devient =IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F")))). Le tableur essaie le premier test et ne passe au suivant que s'il échoue. C'est exactement ce qui a mal tourné dans l'histoire. Avec B2>=60 en premier, un 93 réussit le tout premier test et s'arrête à D. Les tests plus élevés ne sont jamais lus. Commence par le seuil le plus haut et descends.

Compte les parenthèses : quatre IF demandent quatre parenthèses fermantes à la fin. La page des limites d'Excel autorise 64 niveaux de fonctions imbriquées, mais au-delà de trois ou quatre niveaux, une formule devient difficile à lire et facile à casser. Les versions récentes des deux logiciels ont IFS, qui aligne des paires test-réponse les unes après les autres sans imbrication, et l'aide de Google sur IF y renvoie. Pour un long barème, la table de recherche de l'unité suivante est en général encore plus claire, car les seuils sont dans des cellules où chacun peut les voir et les modifier.

Dans le labo ci-dessous, la colonne des notes utilise quatre IF imbriqués et la colonne de réussite utilise IF avec AND. Recopie les deux vers le bas et compare Ben et Dev. Ben a la meilleure note mais échoue sur la présence. Dev est présent mais a moins de 60.

Teste les limites. Pour un barème, essaie exactement 90, 89.9, 60 et 59.9 avant de faire confiance à la formule. La plupart des erreurs se cachent à une limite, là où > et >= donnent des réponses différentes.
Chargement

Fais glisser pour tourner. Fais défiler ou pince pour zoomer.

Écris la formule de la note en D2 et recopie vers le bas. Essaie de mettre le test 60 en premier et regarde Ana descendre à D.
Erreurs fréquentes
  • Commencer un IF imbriqué par le seuil le plus bas. Le premier test qui vaut TRUE l'emporte. Avec B2>=60 en premier, toute note de 60 ou plus s'arrête à D.
  • Écrire =IF(B2>=60 AND C2>=80, ...). AND est une fonction qui prend les tests comme arguments, pas un mot placé entre eux. Écris AND(B2>=60, C2>=80).
  • Utiliser OR quand la règle exige les deux. Un élève qui a 81 points et 70 % de présence est reçu avec OR et refusé avec AND. Dis la règle à voix haute : un « et » dans la phrase signifie AND.
  • Perdre le compte des parenthèses fermantes. Quatre IF imbriqués demandent quatre ) à la fin. S'il en manque une, la formule est refusée.
Note du professeurLe IF imbriqué est souvent la première formule que les élèves ne savent pas lire d'un coup d'œil. Construis-la par étapes au tableau : un seul IF pour A ou « pas A », puis remplace « pas A » par un deuxième IF, et ainsi de suite, en testant après chaque étape. Fais prédire aux élèves la note de 93, 85, 60 et 59.9 avant de recopier vers le bas. L'histoire du mauvais ordre vaut la peine d'être racontée. L'erreur passe sans aucun message, et la correction est évidente une fois qu'on l'a vue.
  1. B2 vaut 81 et C2 vaut 70. Que renvoie =AND(B2>=60,C2>=80) ?
    • FALSE
    • TRUE
  2. Mêmes cellules. Que renvoie =OR(B2>=60,C2>=80) ?
    • TRUE
    • FALSE
  3. Que renvoie =NOT(5>3) ?
    • FALSE
    • TRUE
  4. B2 vaut 93. Que renvoie =IF(B2>=90, "A", IF(B2>=80, "B", "C")) ?
  5. B2 vaut 85. Même formule. Que renvoie-t-elle ?
  6. B2 vaut 93. Que renvoie la formule mal ordonnée =IF(B2>=60, "D", IF(B2>=90, "A", "F")) ?
  7. Combien de parenthèses fermantes terminent une formule composée uniquement de quatre IF imbriqués ?
  8. Combien de niveaux de fonctions imbriquées Excel autorise-t-il ?
  9. B2 vaut 66 et C2 vaut 88. Que renvoie =IF(AND(B2>=60, C2>=80), "Yes", "No") ?
  10. Quelles notes testent le mieux une formule « reçu à partir de 60 » ?
    • 60 et 59.9
    • 100 et 0
    • 75 et 76
    • 30 et 90
L'essentiel
  • AND : tous les tests valent TRUE. OR : au moins un vaut TRUE. NOT : le contraire.
  • Utilise-les dans IF pour transformer TRUE ou FALSE en mots.
  • IF imbriqué : place le IF suivant à la place de la réponse fausse, en commençant par le seuil le plus haut.
  • Teste les limites. Pour les longs barèmes, une table de recherche est plus claire.
Sources de ce cours (19)
  • Microsoft Support, "Switch between relative, absolute, and mixed references" (support.microsoft.com/en-us/office/dfec08cd-ae65-4f56-839e-5f0d8d0baca9: $ before column and row, F4 to switch, copying two down and two right: $A$1 stays, A$1 to C$1, $A1 to $A3, A1 to C3)
  • Microsoft Support, "AVERAGE function" (support.microsoft.com/en-us/office/047bac88-d466-426c-a32b-8f33eb960cf6: text, logical values and empty cells in a reference ignored, zeros included; 10, 7, 9, 27, 2 gives 11, with 5 gives 10; mean 5, median 4, mode 3 for 2 3 3 5 7 10)
  • Microsoft Support, "COUNT function" (support.microsoft.com/en-us/office/a59cd7fc-b623-4d93-87a4-d23bf411294c: only numbers counted, dates count, errors and text do not)
  • Microsoft Support, "ROUND function" (support.microsoft.com/en-us/office/c018c5d8-40fb-4053-90b1-b3e7f61a213c: num_digits required; 2.15 to 2.2, -1.475 to -1.48, 626.3 to 1000, 1.98 to 0, -50.55 to -100)
  • Microsoft Support, "IF function" (support.microsoft.com/en-us/office/69aed7c9-4e8a-4755-a9bc-aa8bbff73be2: value_if_false optional, text in quotes except TRUE and FALSE, 0 and #NAME? problems)
  • Microsoft Support, "VLOOKUP function" (support.microsoft.com/en-us/office/0bbc8083-26fe-4963-8ab8-93a18ad188a1: range_lookup TRUE by default, sorted first column, #N/A, #REF!, #VALUE!, absolute table range, wildcards with FALSE)
  • Microsoft Support, "XLOOKUP function" (support.microsoft.com/en-us/office/b7fd680e-6d10-43e6-84f9-88eae8bf5929: not in Excel 2016 and Excel 2019, exact match by default)
  • Microsoft Support, "How to correct a #VALUE! error" (support.microsoft.com/en-us/office/15e1b616-fbf2-4147-9c0b-0a11a20e409e: + and * may not calculate cells with text, use SUM instead, IFERROR hides all errors)
  • Microsoft Support, "Excel specifications and limits" (15 digits, 64 nested levels, 255 arguments)
  • Google Docs Editors Help, "VLOOKUP" (support.google.com/docs/answer/3093318: is_sorted TRUE by default, FALSE recommended, search down to a larger value, " Apple" and "Apple " differ, wildcards, IFNA, absolute range)
  • Google Docs Editors Help, "XLOOKUP" (support.google.com/docs/answer/12405947)
  • Google Docs Editors Help, "ROUND" (support.google.com/docs/answer/3093440: places optional, 0 by default, rounding by magnitude)
  • Google Docs Editors Help, "IF" (support.google.com/docs/answer/3093364: order of the answers is the most common problem, IFS)
  • Google Docs Editors Help, "AVERAGE" (support.google.com/docs/answer/3093615: text ignored, any number of arguments)
  • Google Docs Editors Help, "COUNTA" (support.google.com/docs/answer/3093991: zero-length strings and spaces count)
  • Google Docs Editors Help, "Keyboard shortcuts for Google Sheets" (support.google.com/docs/answer/181110: F4 and Fn+F4 while entering a formula, F4 as Redo)
  • Pryor Learning and Exceljet (F4 cycle A1, $A$1, A$1, $A1; = comparison not case-sensitive)
  • Ablebits (IF without value_if_false returns FALSE)
  • Spreadsheet Class (Sheets circular dependency shows #REF!)

Consulté le: 2026-10-19