Об этом упражнении
Набор данных (строк: 108) на 2 листах, 2 ячейки ответа, около 7 мин. Проверка прямо в браузере: значение и формула, которая его даёт, с обратной связью о том, что исправить.
Как решить
- 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.
Другие рабочие способы
- =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
Частые ошибки
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.
Как мы проверяем
Принимается любая формула, которая даёт верный результат, если только задание не называет функцию: тогда другая функция даёт половину балла. Мы сравниваем значения с небольшим допуском и не учитываем регистр и пробелы в тексте. Верное число, введённое вручную вместо формулы, даёт половину балла. Задания с несколькими ячейками получают полный балл, если верны 100% ячеек, и половину — если верны от 50%. Там, где это важно, мы также меняем входную ячейку в копии вашего листа и проверяем, что формула пересчитывается правильно.
Ответы рассчитаны по правилам Excel 365 и 16 ноября 2026 г. сверены в нашей системе проверки. Автор: GridBelt team.