Выпадающий список через проверку данных

Ошибки в таблице проще предупреждать ещё при вводе. Если один статус записать как «Оплачен», другой как «оплачен », а третий с опечаткой, фильтры и точный подсчёт могут разнести их по разным категориям. Выпадающий список позволяет выбирать значения из заранее заданного набора.

Для чего нужна проверка данных и что видит пользователь

Когда несколько человек заполняют один и тот же столбец вручную, данные начинают расходиться. «Да», «Да » со случайным пробелом и «Даа» с опечаткой — разные текстовые значения. СЧЁТЕСЛИ не учитывает регистр букв, поэтому «Да» и «да» совпадут, но лишний пробел или опечатка способны нарушить фильтр и подсчёт.

Выпадающий список устраняет эту проблему на уровне ввода: пользователь не печатает значение, а выбирает его из готового набора. Это заметно снижает риск опечаток при обычном вводе.

Видимый признак того, что на ячейку наложен список, — маленькая стрелка, которая появляется справа от ячейки при её выделении. Нажимаете на стрелку — раскрывается список допустимых вариантов. Кликаете по нужному — оно вставляется в ячейку.

Сам инструмент называется «Проверка данных». Он входит в стандартный Excel и живёт на вкладке «Данные». Выпадающий список — самый распространённый из его режимов, но не единственный: тот же механизм умеет ограничивать ввод целыми числами, датами или текстом определённой длины. В этом уроке нас интересует только список.

Последовательность действий в диалоге «Данные → Проверка данных»

Допустим, у вас есть столбец C «Статус заявки», и в каждой строке нужно выбрать одно из трёх значений: «Новая», «В работе», «Закрыта». Вот как создать список.

Выделите ячейки, на которые хотите наложить правило — например, C2:C10. Затем перейдите на вкладку «Данные» и нажмите кнопку «Проверка данных». Откроется диалог с несколькими вкладками; вам нужна вкладка «Параметры» — она открывается по умолчанию.

В поле «Тип данных» выберите «Список». Ниже появится поле «Источник». Именно сюда вводятся допустимые значения. Для простого фиксированного набора напишите значения через точку с запятой без пробелов вокруг неё:

Новая;В работе;Закрыта

Проследите, чтобы был включён флажок «Список допустимых значений» — он отвечает за отображение стрелки в ячейке. По умолчанию он включён, и трогать его обычно не нужно.

Нажмите ОК. Теперь выделите любую из ячеек C2:C10 — справа появится стрелка. Нажмите на неё, и вы увидите три варианта из источника. Ввести что-то другое с клавиатуры всё ещё можно, но если вы нажмёте Enter, Excel покажет предупреждение об ошибке — это штатное поведение по умолчанию.

Два способа задать источник: перечисление и ссылка на диапазон

В поле «Источник» можно написать значения прямо там — как в предыдущем примере. Это перечисление: значения хранятся внутри правила и никак не связаны с ячейками листа.

Перечисление удобно, когда список короткий и почти никогда не меняется. Три-пять значений, которые вы знаете наизусть, — нормальный случай.

Но представьте, что список из 20 городов нужен сразу в нескольких столбцах, и раз в месяц в него добавляется новый город. Каждый раз открывать диалог для каждого столбца и вручную дописывать значение — неудобно.

Решение — вынести значения в отдельный диапазон ячеек и сослаться на него. Скажем, в колонке F на том же листе записано:

F2: Москва
F3: Санкт-Петербург
F4: Казань
F5: Екатеринбург

Тогда в поле «Источник» диалога вместо перечисления вводите ссылку:

=$F$2:$F$5

Знаки доллара делают ссылку абсолютной: при копировании правила в другие ячейки координаты F2:F5 не сдвигаются. Но фиксированный диапазон не расширяется сам. Если вы добавили новый город в F6, снова откройте «Проверку данных» и измените источник на =$F$2:$F$6. Для автоматически растущих списков нужен отдельный механизм, например справочник в умной таблице или динамический именованный диапазон.

Источник можно хранить и на другом листе. В актуальном настольном Excel при заполнении поля «Источник» можно перейти на нужный лист и выделить диапазон мышью. Если ваша версия не позволяет выбрать межлистовой диапазон напрямую, создайте для него именованный диапазон и укажите в источнике его имя, например =СписокГородов.

Изменение источника, удаление правила и что происходит с уже введёнными значениями

Изменить источник для нескольких ячеек сразу

Если правило наложено на диапазон C2:C10 и нужно изменить список — выделите весь этот диапазон целиком, а не только одну ячейку. Иначе изменения затронут только выделенную ячейку, и диапазон станет частично «старым», частично «новым».

После выделения откройте «Данные» → «Проверка данных», исправьте поле «Источник» и нажмите ОК. Правило применится ко всему выделенному диапазону.

Удалить правило

Процедура та же: выделяете нужные ячейки, открываете «Проверка данных», нажимаете кнопку «Очистить всё» — и ОК. Стрелка исчезнет, ячейки станут обычными, без ограничений. Данные, которые в них уже записаны, останутся нетронутыми.

Что происходит со старыми значениями

Здесь важно понять одну вещь: проверка данных работает только в момент ввода нового значения. На ячейки с уже существующим содержимым она не распространяется.

Предположим, до создания списка кто-то вручную написал в C5 «В работе » с лишним пробелом, а правило допускает только «В работе». После применения правила C5 будет показывать «В работе » без каких-либо предупреждений — Excel не помечает и не удаляет существующие данные автоматически.

Эти значения будут участвовать в формулах как обычно. Если СЧЁТЕСЛИ ищет точное значение «В работе», ячейка C5 с хвостовым пробелом в подсчёт не войдёт — и вы получите неверный результат, не зная почему.

Чтобы найти такие ячейки, в меню «Проверка данных» есть команда «Обвести неверные данные» — Excel обведёт красным овалом ячейки, содержимое которых не соответствует текущему правилу. После этого вы сможете точечно исправить каждую из них, выбрав правильный вариант из списка.