Выпадающий список в эксель: как сделать для статусов
Выпадающий список в эксель: как сделать статусы заказов, задать источник вариантов и проверить неверный ввод. Пошаговый пример для настольного Excel.
Ольга
Основатель малого бизнеса, партнёр
8 мин чтения
У нас было три готовых заказа, а фильтр показывал один. В колонке стояло «Готов», «можно забирать» и «готов к выдаче». Для сотрудников смысл совпадал, для таблицы это были разные значения. Поэтому выпадающий список в эксель я начала делать не ради удобной стрелочки. Сначала договорилась, как будем называть состояния заказа. Ниже покажу, как сделать такой список и проверить его поведение.
Таблица была маленькая, мастерская тоже. Казалось, устно договориться легче, чем настраивать правила. Но при передаче смены приходилось перечитывать заметки, чтобы понять, можно ли звонить клиенту. Одно и то же состояние каждый описывал своим языком.
Для примера возьмём настольный Excel для Windows из Microsoft 365 или Excel 2024. Основной инструмент здесь — «Проверка данных». Макросы и отдельные расширения для такого списка не нужны.
Выпадающий список в эксель: как сделать полезный набор вариантов
Я сначала выписала возможные состояния. Не по словам из старой колонки, а по тому, какие действия уже выполнены и что сотрудник должен сделать дальше.
Вариант списка · Что он означает в нашем примере · Что делаем следующим
Принят — Заказ зарегистрирован — Передаём на диагностику
Ждёт детали — Работу остановили до получения детали — Уточняем поступление
Готов к выдаче — Работы завершены, можно выдавать — Связываемся с клиентом
Выдан — Технику передали клиенту — Завершаем запись
Это четыре условных статуса для разбора, а не обязательная схема любого ремонта. Если у вас иначе устроена работа, набор будет другим. Главное, чтобы сотрудники одинаково понимали различие вариантов.
Я убрала «Срочно» из этой колонки. Оно сообщает о приоритете, но не о готовности. Срочный заказ может одновременно ждать деталь. Если смешать два признака в одном списке, потом снова придётся угадывать, что именно хотели отметить.
По той же причине оплату я бы учитывала отдельно. «Выдан» не доказывает, что в таблице правильно записан расчёт. Выпадающий список помогает выбирать единое значение, но не устанавливает все правила работы мастерской.
Мне пригодилась короткая проверка: можно ли у одной записи одновременно поставить два предложенных варианта? Если да, возможно, мы смешали в колонке разные признаки. Тогда лучше пересобрать структуру до настройки стрелок.
Сначала справочник, затем ячейки заказов
Чтобы повторить пример, откройте пустую учебную книгу. В основной таблице используем колонку D под статус. В H1 напишем «Статусы», а в H2:H5 — четыре значения из нашего списка, каждое в отдельной ячейке.
Я оставила справочник рядом с таблицей на время настройки. Так видно, откуда взялись варианты. Его можно позже перенести в отдельную область книги, но не стоит прятать источник раньше, чем вы убедились, что он правильный.
Теперь подключаем диапазон:
Выделите D2:D20 — ячейки, где сотрудники будут выбирать состояние.
Откройте вкладку «Данные» и команду «Проверка данных».
В параметрах проверки выберите тип «Список».
В поле «Источник» укажите диапазон H2:H5. Для этого примера ссылка выглядит как `=$H$2:$H$5`.
Оставьте включённым показ раскрывающегося списка в ячейке.
Подтвердите настройки и выберите одну из ячеек D2:D20.
Заголовок H1 в источник не включаем. Он объясняет назначение справочника, но не является состоянием заказа. Пустые промежутки между вариантами тоже лучше убрать, чтобы список не содержал бессодержательных строк.
Знаки доллара фиксируют адреса в ссылке. В нашем примере источник остаётся одним и тем же набором H2:H5. Это удобно, когда правило используют для нескольких строк заказов.
Я проверила верхнюю и нижнюю ячейки выбранной области. Недостаточно увидеть стрелку только в D2: работать будут и с последующими записями. Если новая строка появится за пределами D20, отдельно убедитесь, что нужная проверка действует и там.
Можно записать варианты прямо в поле источника. Но для рабочего справочника я предпочитаю ячейки: их легче увидеть, обсудить и изменить. Кроме того, не приходится объяснять коллегам, какой разделитель использует их настройка Excel.
Стрелка появилась. А свободный ввод остался?
Я проверяла только выбор существующего статуса. Нажала стрелку, выбрала «Принят», решила, что правило работает. Но человек может попробовать вписать своё значение с клавиатуры.
Откройте настройки той же проверки и найдите вкладку сообщения об ошибке. Включите его показ при неверном вводе. Если требуется останавливать ввод значения вне списка, выберите соответствующий строгий тип сообщения; в справке Microsoft он обозначен как «Остановить».
У других видов сообщений другое поведение. «Предупреждение» и «Информация» могут сообщить о проблеме, не запретив продолжение ввода. Поэтому я проверяю результат действием, а не только названием вкладки.
В учебной копии попробуйте:
выбрать «Готов к выдаче» из списка;
ввести с клавиатуры «Уже сделали», которого в справочнике нет;
исправить значение на допустимое;
открыть список в другой подготовленной строке.
Если второе действие проходит так, как вы не планировали, вернитесь к виду сообщения. Возможно, вы настроили подсказку вместо ограничения. Это не повод усложнять книгу макросами.
Текст ошибки я тоже переписала. Вместо непонятного «Введенное значение неверно» лучше сообщить: «Выберите состояние из списка. Новый вариант сначала добавляем в справочник». Сотруднику понятно, что делать, а не только что программа недовольна.
Для обсуждения набора можно открыть LibraChat прямо сейчас. Не нужно передавать реальные заказы и контакты клиентов; достаточно описать процесс:
Составляю список состояний для [процесс]. Сейчас предлагаю варианты: [перечень]. Для каждого нужно понимать следующее действие сотрудника. Найди смешение состояний, приоритетов и других признаков. Предложи короткие однозначные названия. Не придумывай, что уже выполнено в конкретных заказах.
Сервис поможет разобрать формулировки. Саму проверку данных всё равно нужно настроить в книге и испытать в нужных ячейках. Перечень вариантов в ответе чата не становится работающим списком на листе.
Когда добавился пятый статус
Через некоторое время может понадобиться новый вариант, например «Согласование». Важно изменить не только текст справочника, но и проверить, входит ли новая ячейка в источник правила.
Если источник задан как H2:H5, значение в H6 само по себе ещё находится за указанной границей. Откройте правило и посмотрите поле «Источник». В простом примере диапазон потребуется расширить до H2:H6.
Я бы после этого снова открыла список в ячейке заказа. Изменение справочника считается завершённым не тогда, когда слово появилось на листе, а когда его действительно можно выбрать там, где ведут заказы.
Для растущего справочника Microsoft рекомендует использовать таблицу Excel как источник: при корректной настройке связанные списки обновляются при добавлении и удалении её элементов. Но это отдельный вариант организации источника. Не стоит объявлять обычную фиксированную ссылку динамической только потому, что рядом дописана строка.
Здесь полезно различать изменения:
Переименовали значение в пределах прежнего диапазона.
Добавили ячейку за его пределами.
Добавили элементы в источник, организованный как таблица Excel.
У этих действий могут быть разные следующие шаги. Поэтому я сначала выясняю, как устроен конкретный список, а уже потом правлю справочник.
Ещё одна работа ждёт старые записи. Новый набор вариантов не объясняет автоматически, какой из прежних текстов что означал. «Можно забирать» я могу сопоставить с «Готов к выдаче», если именно такой смысл подтверждён. А запись «позвонить» придётся уточнить: она описывает поручение, а не состояние техники.
Для передачи смены я дописала ещё одну договорённость: кто меняет состояние и после какого действия. «Готов к выдаче» выбираем после завершения работ, а не после обещания закончить вечером. Excel позволяет выбрать слово, но не наблюдает за ремонтом. Если сотрудник отметил готовность заранее, правильное написание статуса не сделает запись достоверной.
Добавление нового варианта мы тоже сначала обсуждаем. Иначе справочник быстро собирает все фразы, от которых мы пытались уйти. Просьбу «позвонить после обеда» я оставлю заметкой к заказу, а не превращу в пятый статус. Так следующая смена различает состояние работы и отдельное поручение. Список остаётся коротким, но не за счёт потери нужной подробности.
Моя финальная проверка теперь начинается с неправильного значения. Если книга спокойно принимает произвольное слово, я ещё не получила нужное ограничение. А когда все выбирают одинаковые статусы и понимают их одинаково, фильтр перестаёт требовать перевода с языка каждой смены.
