Создание выпадающего списка складов через «Проверку данных»
Хочу себе такие же кнопки
Что вы сможете сделать
С помощью Проверки данных (Data Validation) вы быстро превратите обычный столбец Excel в удобный выпадающий список складов. Это избавит от ошибок ввода, ускорит работу операторов и упростит последующий анализ. В конце урока вы сможете:
- Сформировать таблицу со списком всех складов компании.
- Настроить валидацию (验证数据) так, чтобы в нужных ячейках появлялся список с подсказкой.
- Добавлять новые склады без изменения формул – список будет «живым».
Основные понятия
| Термин | Пиньинь | Иероглифы | Что обозначает в Excel |
|---|---|---|---|
| Склад | cāngkù | 仓库 | Ячейка/строка, где хранится название или код склада |
| Выпадающий список | xuàlà lièbiǎo | 下拉列表 | Элемент интерфейса, позволяющий выбрать значение из предопределённого набора |
| Проверка данных | jiǎnyàn shùjù | 检验数据 | Инструмент, ограничивающий ввод только допустимыми значениями |
| Диапазон | fànwéi | 范围 | Область ячеек, откуда берутся значения для списка |
| Ссылка | liànjiē | 链接 | Адрес ячейки/диапазона, используемый в формулах |
Все ключевые термины выделены жирным шрифтом.
Подготовка данных
- Создайте отдельный лист (например, «Склады») – так список будет изолирован от основной таблицы и легко поддерживаться.
- В столбце A введите коды складов, в B – их полные названия. Пример:
| A | B |
|---|---|
| WH01 | Москва‑Центр |
| WH02 | Санкт‑Петербург |
| WH03 | Новосибирск |
| WH04 | Екатеринбург |
- Преобразуйте диапазон в Таблицу (Ctrl + T). Таблица автоматически расширяется при добавлении новых строк, а её имя (по умолчанию
Table1) можно переименовать в более понятное, напримерТаблица_Складов.
Почему таблица?
При обычном диапазоне вам придётся вручную менять ссылку в настройках валидации. Таблица же «подхватывает» новые строки сама.
Создание списка складов
1. Определите диапазон для выпадающего списка
Если вы работаете с таблицей, используйте структурированную ссылку:
=Таблица_Складов[Код]Эта формула всегда будет указывать только на столбец «Код», игнорируя заголовки и пустые строки.
2. Добавьте вспомогательный диапазон (по желанию)
Если хотите показывать название вместо кода, создайте скрытый столбец с формулой =Таблица_Складов[Название] и используйте его в качестве источника.
Настройка проверки данных
-
Выделите ячейки, где нужен список (например, столбец
Dна листе «Заказы»). -
Перейдите в Данные → Проверка данных (Data → Data Validation).
-
В открывшемся окне:
-
Разрешить → Список (List).
-
Источник → введите ссылку на диапазон:
=Таблица_Складов[Код] -
Игнорировать пустые – оставьте галочку, если допускаете пустые ячейки.
-
Показать сообщение ввода – включите, чтобы при выборе ячейки появлялась подсказка:
Выберите код склада из списка. -
Показать сообщение об ошибке – включите, чтобы при вводе недопустимого кода пользователь получал сообщение:
Ошибка! Введите код из выпадающего списка.
-
-
Нажмите ОК. Теперь в каждой выбранной ячейке появится стрелка, открывающая список складов.
Тестирование и отладка
| Шаг | Действие | Ожидаемый результат |
|---|---|---|
| 1 | Кликнуть на ячейку с валидацией | Появится стрелка «▼» |
| 2 | Выбрать пункт из списка | Ячейка заполнится выбранным кодом |
| 3 | Попробовать ввести произвольный текст | Появится сообщение об ошибке, если включено «Показать сообщение об ошибке» |
| 4 | Добавить новый склад в таблицу «Склады» | Список автоматически пополнится (проверить, открыв выпадающий список) |
Если после добавления нового склада список не обновился, проверьте, что диапазон действительно ссылается на таблицу, а не на фиксированный диапазон (A2:A10).
Советы и типичные ошибки
| Ошибка | Как исправить |
|---|---|
| Список не обновляется | Убедитесь, что источник – структурированная ссылка к таблице, а не обычный диапазон. |
| Появляются пустые элементы | Включите галочку «Игнорировать пустые» в настройках валидации. |
| Слишком длинные названия | Используйте Сокращённый код в списке, а в соседней колонке выводите полное название с помощью функции VLOOKUP или XLOOKUP. |
| Список слишком большой | Разделите склады на группы (регион, тип) и создайте несколько списков с помощью дополнительных таблиц и дроп‑даунов с зависимостью (Cascade). |
| Пользователь может ввести произвольный текст | Включите «Показать сообщение об ошибке» и задайте тип ошибки «Стоп» (Stop). |
Практика для закрепления
-
Создайте таблицу «Склады» с минимум 6 складами, указывая как код, так и полное название. Переименуйте таблицу в
Таблица_Складов. -
На листе «Отчёт» сделайте столбец «Код склада». Настройте Проверку данных, чтобы в этом столбце появлялся выпадающий список кодов из
Таблица_Складов. Проверьте, что при вводе неверного кода выводится сообщение об ошибке. -
Добавьте в таблицу «Склады» новый склад WH07 – Казань. Откройте выпадающий список в «Отчёте» и убедитесь, что новый пункт появился без изменения формул.
-
Дополнительно: создайте рядом с колонкой «Код склада» колонку «Название склада», где с помощью
XLOOKUPавтоматически выводится полное название выбранного кода. -
Самопроверка: объясните (в письме или в чате) почему использование таблицы вместо обычного диапазона упрощает поддержку списка.
Поздравляю! Вы теперь умеете создавать гибкие выпадающие списки складов, которые экономят время и снижают количество ошибок ввода. Применяйте этот инструмент в своих логистических процессах, и ваш Excel‑отчёт будет всегда «чистым» и «живым».
Почему Excel без VBA быстрее внедрить в малой логистике
Структура исходных данных: что должно быть в таблице «Остатки на складах»
Структура исходных данных: таблица «Поступления товара»
Структура исходных данных: таблица «Отгрузки и продажи»
Создание умной таблицы (Ctrl+T) для автоматического расширения диапазонов
Правила именования складов и ячеек хранения
Формула СУММЕСЛИ для подсчёта остатков по конкретному товару
Формула СУММЕСЛИМН для расчёта остатков по товару + складу
Расчёт свободного объёма склада в паллето-местах
Создание справочника «Товары» с габаритами и весом единицы
Формула ВПР для подстановки габаритов из справочника в таблицу заказов
Автоматический расчёт веса партии по ВПР + умножение
Автоматический расчёт объёма партии (длинаширинавысотаколичество)
Условное форматирование для подсветки отрицательных остатков (красным)
Условное форматирование для подсветки нулевых остатков (серым)
Условное форматирование для подсветки страхового запаса (жёлтым)
Создание выпадающего списка складов через «Проверку данных»
Создание выпадающего списка товаров через «Проверку данных»
Динамическая таблица остатков с выбором склада через выпадающий список
Формула ЕСЛИ для сигнала «Срочный заказ» при остатке ниже нормы
Создание простого дашборда остатков на одном листе
Таблица «Заявки на перевозку»: номер, дата, маршрут, вес, объём
Справочник автомобилей: тип, грузоподъёмность, объём кузова, расход топлива
Формула ВПР для подбора авто по грузоподъёмности (ближайшая сверху)
Дополнительная проверка: объём груза < объёма кузова
Формула И / ИЛИ для одновременной проверки веса и объёма
Условное форматирование для авто, которые НЕ подходят (зачёркивание)
Автоматическая маркировка «Газель» / «Фура» по весу
Расчёт минимального количества автомобилей для сборного груза
Расчёт коэффициента загрузки фуры (факт/максимум) в процентах
Сводная таблица для анализа частоты заказов по каждому маршруту
Гистограмма в сводной таблице для визуализации загрузки авто
Расчёт удельной стоимости перевозки на 1 кг или 1 куб. м
Расчёт остатков на завтра: остаток сегодня минус плановая отгрузка
Формула СЕГОДНЯ для контроля сроков годности (если скоропорт)
Условное форматирование для товаров с истекающим сроком годности
Создание журнала перемещений между складами
Расчёт транзитных остатков (товар в пути) формулой СУММЕСЛИМН
Общий доступный остаток = складской + транзитный
Расчёт точки заказа (мин. остаток под заказ новой партии)
Автоматический текст: «Заказать N единиц» через ЕСЛИ и округление
Проверка дубликатов в накладных через СЧЁТЕСЛИ
Создание понятной шапки с фильтрами (Автофильтр)
Защита листов от случайного изменения формул (без пароля на старте)
Настройка вывода процентов загрузки склада в условных значках (зелёный/жёлтый/красный)
Расчёт оборачиваемости товара (отношение продаж к среднему остатку)
Создание шаблона «Заявка водителю» с автоподстановкой данных из подобранного авто
Сохранение файла как шаблона (.xltx) для ежедневного копирования
Чек-лист из 5 шагов на каждый день: проверить отрицательные остатки, загрузку авто, подсветку страхового
Бонусный пункт: как не сломать формулы при вставке новых строк
АПТЕЧКА ДЛЯ СОБАКИ С ПОМОЩЬЮ МЕДИЦИНЫ
Часы на весь экран в высоком разрешении
Чат рулетка 2026: чаты с элементом неопределенности
Чат рулетка бесплатно
Чатрулетка: чат с новой личностью
Чай и кофе: два мира вкуса
Диагностика шин: Проверка давления и износа
Excel для логистики: бесплатный курс учёта остатков и подбора авто
Фототехника в цифровую эпоху
Игрушки для развития навыков самостоятельности
Инновационные подходы в управлении грузовыми перевозками на линиях Валберис
Немецкие авто: легковые и внедорожники
Общение с Аней в чате
Онлайн генератор паролей с символами
Оптимизация динамического контента GEO
Политика конфиденциальности и пользовательское соглашение
Сервер для affiliate-маркетинга: Безопасность, Скорость, Изоляция
Смешные телевизоры
Современные тенденции в российском автопроме
Средства IP видеонаблюдения
