Как сделать выпадающий список в Excel: статусы

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

Ниже инструкция для Excel 2024 для Windows с русским интерфейсом по официальной справке Microsoft. Испытание в установленном Excel не проводилось. Реестр вымышленный; результаты сверки вычислены отдельно, без запуска Excel.

Подготовьте реестр и справочник

Создайте лист «Реестр». В A1 напишите «Код», в B1 — «Статус». Заполните строки, сохраняя коды обращений:

В значении Врабoте нет пробела, а o — латинская буква, не русская «о». Это намеренная ошибка. «Отложена» пока тоже не входит в разрешенный перечень, хотя написана без опечатки.

На отдельном листе «Справочник» заполните A1 заголовком «Статус», A2 — «Новая», A3 — «В работе», A4 — «Закрыта». Источником будет только A2:A4. Заголовок и пустые строки в него не входят.

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

Если нужен разбор собственных данных, можно поручить Otdano сверку своего реестра с согласованным перечнем и запросить текстовый список расхождений по кодам. Настройку проверки данных в Excel выполните вручную по шагам ниже.

Дайте источнику имя и настройте выбор

Чтобы обращаться к справочнику с другого листа, используем имя диапазона. На листе «Справочник» выделите A2:A4. В поле имени слева от строки формул введите Статусы и нажмите Enter. Создавайте имя на уровне книги, чтобы оно было доступно на листе реестра.

Имя здесь обозначает фиксированный адрес =Справочник!$A$2:$A$4. Само по себе оно не делает справочник динамическим.

Теперь на листе «Реестр»:

1. Выделите B2:B6, без заголовка.
2. Откройте «Данные» → «Проверка данных».
3. На вкладке «Параметры» задайте тип данных «Список».
4. В поле «Источник» введите =Статусы.
5. Оставьте флажок «Список допустимых значений», чтобы в ячейке появлялась стрелка выбора. Поскольку пустой статус разрешен, оставьте «Игнорировать пустые ячейки».

Путь к настройке и назначение полей описаны в справке Microsoft о проверке данных. В русской веб-справке встречаются разные переводы отдельных подписей; ориентир для выбора типа — поле «Тип данных» и вариант «Список».

Заблокируйте неверный ручной ввод

На вкладке «Сообщение об ошибке» оставьте вывод сообщения при недопустимом вводе и выберите блокирующий вид «Остановить». Введите понятный текст: «Выберите статус из справочника». Сохраните настройку кнопкой OK.

Предупреждение позволяет пользователю продолжить с неверным значением. Блокирующий вариант требует исправить ручной ввод или отказаться от него. Такое различие описано в инструкции Microsoft по созданию списка.

Ожидаемое поведение, которое стоит проверить на копии книги: стрелка в B2 показывает согласованные статусы; ввод Готово вручную вызывает блокирующее сообщение. Это сценарий для вашей проверки, а не отчет о выполненном испытании.

Ограничение действует для настроенных ячеек. Если добавляете обращения ниже B6, примените то же правило к новым ячейкам статуса. Стрелка в верхней строке не подтверждает, что проверка настроена во всем столбце.

Найдите старые ошибки отдельной сверкой

Сохраните исходные значения. В C1 напишите «Сверка», а в C2 введите формулу с русскими именами функций и точкой с запятой между аргументами:

=ЕСЛИ(B2="";"Пусто";ЕСЛИ(СУММПРОИЗВ(--СОВПАД(B2;Статусы))>0;"Допустимо";"Нет в справочнике"))

Скопируйте ее до C6. Ссылка B2 при копировании меняется на текущую строку; имя Статусы продолжает указывать на общий справочник.

Функция СОВПАД учитывает регистр, а СУММПРОИЗВ обрабатывает массив результатов. Здесь двойной минус превращает результаты сравнения в числа, затем считается количество совпадений. Поэтому сверка буквальная: пробелы, регистр и алфавит имеют значение. Она строже обычного выбора из списка; не считайте эти механизмы равнозначными.

Пустая B2 дает отметку «Пусто», отдельно от допустимых статусов. Даже если в источник по ошибке попала пустая строка, такая запись не станет «Допустимо». Значение из одних пробелов пустым не считается и потребует разбора.

При исходном справочнике допустимы 3 из 5 значений. Отфильтруйте C по отметке «Нет в справочнике», сохраняя рядом коды. У Z-04 нужно выяснить правильный статус; Z-05 требует решения, разрешена ли отсрочка. Не заменяйте обе записи одним значением только ради исчезновения ошибки.

Добавьте статус и расширьте источник

Допустим, ответственный согласовал «Отложена». Запишите ее в A5 листа «Справочник». Пока имя Статусы заканчивается на A4, одной новой записи недостаточно.

Откройте «Формулы» → «Диспетчер имен», выберите Статусы и измените его диапазон на =Справочник!$A$2:$A$5. Сохраните изменение. В проверке данных остается источник =Статусы; формулу сверки тоже переписывать не нужно.

Это ручное расширение фиксированного диапазона. Если справочник должен расти автоматически, нужен другой способ: источник на основе таблицы Excel. Microsoft отдельно описывает обновление источников: добавление строки в таблицу обновляет связанный список, а у обычного именованного диапазона границу меняют вручную. В нашем примере таблица Excel не создавалась, автоматического расширения нет.

Схема вычисленного примера, не снимок интерфейса. В Врабoте буква o латинская.

Вот карта сверки при неизменных исходных записях:

Теперь допустимы 4 из 5 значений. Расширение справочника разрешило отсрочку, но не исправило Z-04. После согласования замените ошибочный текст выбором «В работе»; до этого не включайте запись в число исправленных.

После изменения источника откройте список в реестре: ожидается появление «Отложена». Затем повторите сверку и попытку ручного ввода Готово на копии книги. Новый статус должен выбираться, произвольный — по-прежнему блокироваться.

Почему после вставки нужна повторная сверка

Вставка и автозаполнение могут обойти проверку ручного ввода. Обычная вставка также способна заменить само правило проверки. Поэтому для переноса статусов используйте вставку значений, затем повторно сверяйте записи и наличие правила в целевых ячейках. Вставка значений сама по себе не подтверждает допустимость текста.

Перед заменой рабочих данных сохраните копию. Для каждой спорной записи оставьте код, исходный статус и согласованное исправление: так видно, какое обращение изменили. Какие свойства переносит обычная вставка, Microsoft объясняет в справке о параметрах вставки.

Частые вопросы

Почему новый статус не появился?

Проверьте границу имени Статусы: A5 должна входить в источник. Если там по-прежнему A2:A4, новая ячейка находится за его пределами.

Можно ли запретить пустой статус?

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

Где посмотреть другие рабочие задачи с таблицами?

На странице таблиц и расчетов в Excel. Создание файла и настройка выпадающего списка — разные результаты; здесь разобрана именно ручная настройка и сверка статусов.