Aller au contenu
GridBelt
  • Test Excel
  • Exercices
  • Programmes
  • Tarifs
Se connecter
Commencer le test gratuit
  • Test Excel
  • Exercices
  • Programmes
  • Tarifs
Se connecterCréer un compte
Commencer le test gratuit
GridBelt

Entraînez-vous sur Excel dans votre navigateur.

Test Excel

  • Niveau 1 : Débutant
  • Niveau 2 : Intermédiaire
  • Niveau 3 : Expert
  • Test Excel en 7 jours
  • Classement
  • Test Excel en entretien d’embauche
  • Évaluation Excel d’Indeed

Exercices

  • Tous les exercices
  • Programmes
  • Pour débutants

Guides

  • Formules Excel
  • Raccourcis Excel

Entreprise

  • À propos
  • Tarifs
  • Comment nous corrigeons

© GridBelt. Tous droits réservés.

  • Politique de confidentialité
  • Conditions d’utilisation
  • Politique de remboursement

Excel est une marque commerciale de Microsoft Corporation. Indeed, TestGorilla, SHL et les autres marques sont la propriété de leurs détenteurs respectifs. GridBelt est un site d’entraînement indépendant ; il n’est affilié ni à Microsoft, ni à TestGorilla, ni à SHL, ni à Indeed, et n’est ni cautionné ni sponsorisé par ces sociétés.

  1. Exercices
  2. /
  3. Drop-down lists

Drop-down list practice: validate typed regions against a list

  • Intermédiaire
  • ~7 min
  • Drop-down lists

Énoncé en anglais

Six sales reps typed the region of 100 new leads by hand into C2:C101. The approved regions — 8 of them — are on the Lists sheet in A2:A9. Some entries are typos ("Nort East", "Midwest.", "Mountains"), some name a region that is not on the list, 2 are empty, and 6 are correct names in another letter case ("southwest"). Before the column gets a drop-down list, the sales manager wants to know which existing entries the list would reject — and how many there are.

Ce que vous devez faire

  1. 1Click F2 and type =COUNTIF(Lists!$A$2:$A$9,C2)>0 — select the list on the Lists tab and press F4 (Windows) or ⌘T (Mac) to lock it. COUNTIF counts how often C2 appears in the list; >0 turns that into TRUE or FALSE.
  2. 2Press Enter and fill F2 down to F101: double-click the fill handle, or select F2:F101 and press Ctrl+D (Windows) / ⌘D (Mac). A different letter case still gives TRUE (a drop-down list ignores case too); typos, unlisted regions and empty cells give FALSE.
  3. 3Click I2 and count the rejects: =COUNTIF(F2:F101,FALSE).
  4. 4Now create the drop-down: select C2:C101, then Data → Data Validation (Windows keyboard: Alt, A, V, V; on Mac Data → Data Validation). Allow: List; Source: =Lists!$A$2:$A$9; clear "Ignore blank" so an empty region counts as an error; OK. The checker grades column F and I2 — the drop-down is what you leave behind so the next lead is picked, not typed.
  5. 5Fix a typo in column C (pick the right region from the new drop-down) and watch its row in F turn TRUE and I2 go down by one. Undo with Ctrl+Z / ⌘Z before you check.

Cellules de réponse

Correction

Saisissez votre formule dans la grille, appuyez sur Enter, puis cliquez sur Vérifier. Nous corrigeons la valeur de chaque cellule de réponse et la formule qui la produit.

Tâches

  1. Non vérifiéIn F2, return TRUE when the region in C2 appears in the list on Lists!A2:A9 (letter case does not matter) and FALSE when it does not — typos, unlisted regions and empty cells — then fill down to F101.
  2. Non vérifiéIn I2, count the entries in column C that the drop-down list would reject.

Ordinateur recommandé pour les tâches dans la feuille de calcul. La feuille défile à l’intérieur de ce cadre.

StartInsertFormulasDataView
A1fxPréparation de votre classeur…
A
B
C
D
E
F
G
H
I
J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sheet1

Saisissez votre formule dans la feuille, puis vérifiez.

À propos de cet exercice

Un jeu de données de 108 lignes sur 2 feuilles, 2 cellules de réponse, environ 7 minutes. Corrigé dans votre navigateur : la valeur et la formule qui la produit, avec des indications sur la correction à apporter.

Comment le résoudre

  • F2=COUNTIF(Lists!$A$2:$A$9,C2)>0
  • I2=COUNTIF(F2:F101,FALSE)

A drop-down list is a data-validation rule of the type List: a cell may only hold one of the values in the source range. Before adding it to a column that already has data, you check which entries would break the rule — exactly what COUNTIF(list, C2)>0 does row by row. COUNTIF compares like the drop-down does: letter case is ignored, but every other character counts, so "Nort East" and "Midwest." are rejected while "southwest" passes. Locking the list with $ keeps every row pointing at Lists!A2:A9 when you fill down, and COUNTIF(F2:F101,FALSE) counts the rows to fix.

Pitfall 1: hard-coding the regions — =OR(C2="Northeast",C2="Mid-Atlantic",…) or a typed source "Northeast,Mid-Atlantic,…" in the validation dialog — works today and breaks the day a region is added or renamed; a source range on its own sheet (or a named range or table column) keeps one master list. Pitfall 2: data validation only checks new typing; values that were already in the column, or pasted over it, stay as they are, which is why the helper column (or Circle Invalid Data in desktop Excel) is the audit step. Pitfall 3: "Ignore blank" is ticked by default, so an empty cell passes validation; clear it when the field is mandatory — and note that COUNTIF with an empty C2 finds nothing, so the helper already treats an empty region as invalid. Alternatives: =ISNUMBER(MATCH(C2,Lists!$A$2:$A$9,0)) and =OR(C2=Lists!$A$2:$A$9) give the same TRUE/FALSE (MATCH and = also ignore case); =SUMPRODUCT(--(COUNTIF(Lists!$A$2:$A$9,C2:C101)=0)) counts the rejects in one cell without the helper column; in Excel 365, =FILTER(A2:C101,F2:F101=FALSE) lists them. Version note: in Excel 365 the source can also be a spilled list (=H2# under a =SORT(UNIQUE(...)) of the regions); a table column needs a named range or =INDIRECT("Table1[Region]"), because the dialog does not accept structured references; a source on another sheet works in every version since Excel 2010.

Autres méthodes valables

  • =ISNUMBER(MATCH(C2,Lists!$A$2:$A$9,0)) — MATCH finds the position or returns #N/A; ISNUMBER turns that into TRUE/FALSE
  • =OR(C2=Lists!$A$2:$A$9) — compares C2 with every list entry at once
  • =SUMPRODUCT(--(COUNTIF(Lists!$A$2:$A$9,C2:C101)=0)) — the reject count in one cell, without the helper column
  • =COUNTIF(F2:F101,FALSE) — the count used in the solution; =ROWS(FILTER(C2:C101,F2:F101=FALSE)) is the 365 equivalent

Erreurs fréquentes

Leaving the list unlocked: =COUNTIF(Lists!A2:A9,C2)>0 filled down slides to Lists!A3:A10, A4:A11… so rows further down stop seeing the first regions and valid entries turn FALSE.

Comment nous corrigeons

Toute formule qui produit le bon résultat est acceptée, sauf si la tâche nomme une fonction : une autre fonction rapporte alors la moitié des points. Nous comparons les valeurs avec une petite tolérance et ignorons la casse et les espaces dans le texte. Un nombre correct saisi à la main au lieu d’une formule rapporte la moitié des points. Les tâches sur plusieurs cellules rapportent tous les points avec 100 % de cellules justes, la moitié à partir de 50 %. Lorsque c’est pertinent, nous modifions aussi une cellule d’entrée sur une copie de votre feuille et vérifions que votre formule suit.

Réponses calculées selon les règles d’Excel 365 et vérifiées dans notre correcteur le 16 novembre 2026. Auteur : GridBelt team.

Dans ces programmes

  • Excel for Administrative AssistantsJour 10
  • Learn Excel From ScratchJour 13

Exercices associés

  • Restrict quantity to whole numbers 1–100Intermédiaire, ~7 min
  • List unique customers (UNIQUE) — remove duplicatesIntermédiaire, ~8 min
  • Highlight overdue invoices (conditional formatting)Intermédiaire, ~8 min

Référence de la fonction

Documentation officielle

Exercice suivant

Restrict quantity to whole numbers 1–100