Дата публикации: 14.05.2026

Создание выпадающего списка товаров через «Проверку данных»

Хочу себе такие же кнопки
2a75c51f

Что вы получите от этого урока

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

1. Подготовка списка товаров

Шаг Что делаем Почему это важно
1️⃣ Открываем отдельный лист (например, Товары) Хранить справочный список отдельно — лучшая практика, так он не будет случайно изменён.
2️⃣ Записываем названия товаров в один столбец, без пустых строк (например, A2:A50) Пустые ячейки могут привести к появлению «пустых» пунктов в выпадающем меню.
3️⃣ Даем этому диапазону имя (например, Товары_Список) Имена диапазонов позволяют ссылаться на список в любой части книги без указания конкретных ячеек.

Как задать имя диапазона

  1. Выделите ячейки с товарами.
  2. На ленте выберите Формулы → Определить имя (или нажмите Ctrl + F3).
  3. В открывшемся окне введите Товары_Список и нажмите ОК.

Аналогия: имя диапазона — это «адрес» вашего списка, как телефонный номер в телефонной книге. Вызывая его, вы всегда получаете нужный «контакт», даже если номер меняется.

2. Создание простого выпадающего списка

  1. Перейдите к ячейке, где будет использоваться список (например, B2 на листе Заказы).
  2. Откройте Данные → Проверка данных (в Google Sheets — Data → Data validation).
  3. В поле Разрешить выберите Список из диапазона.
  4. В поле Источник введите =Товары_Список.
  5. Нажмите ОК.

Теперь в ячейке B2 появился маленький треугольник‑стрелка. При клике открывается список всех товаров.

Ключевой термин: «Проверка данных» — механизм, который ограничивает вводимые значения определённым набором (списком, числом, датой и т.п.).

3. Динамический список: добавляем новые товары без правки формулы

Если ваш ассортимент растёт, вручную менять диапазон каждый раз неудобно. Решаем задачу с помощью Таблицы или функции OFFSET.

3.1. Таблица Excel

  1. На листе Товары выделите диапазон с текущими товарами.
  2. На ленте нажмите Вставка → Таблица (или Ctrl + T).
  3. Убедитесь, что галочка Таблица с заголовками снята, если заголовков нет.
  4. Таблица автоматически расширяется при добавлении новых строк.

Назовите таблицу, например, ТоварыТаблица. Затем в Проверке данных укажите =ТоварыТаблица[Товар].

3.2. Функция OFFSET (для Google Sheets)

=OFFSET(Товары_Список;0;0;COUNTA(Товары_Список);1)
  • OFFSET задаёт диапазон, начиная с первой ячейки списка.
  • COUNTA считает количество непустых ячеек, тем самым определяя высоту диапазона.

Эта формула автоматически «подхватывает» любые новые записи, добавленные в конец списка.

Пример: если в A2:A100 записаны товары, а вы добавляете новые в A101, диапазон, определённый OFFSET, сразу расширяется до A101.

4. Ограничения ввода и сообщения об ошибках

В окне Проверка данных есть вкладка Сообщение об ошибке.

Параметр Что задаём Как это помогает
Стиль Стоп, Предупреждение, Информация Стоп — пользователь не может ввести недопустимое значение; Предупреждение — ввод допускается, но появляется предупреждение; Информация — лишь подсказка.
Заголовок Краткое название (например, «Выберите товар») Видно сразу, о чём речь.
Текст сообщения Подробное пояснение (например, «Только товары из списка доступны для выбора. Если нужного товара нет — добавьте его в справочный лист.») Снижает количество вопросов к поддержке.

Аналогия: сообщение об ошибке — это «посторонний контролёр», который проверяет ваш ввод и подсказывает, если что‑то пошло не так.

5. Связанные списки (зависимые выпадающие)

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

  1. Создаём отдельные списки:

    • A2:A5 — «Электроника», «Химия», «Текстиль».
    • Для каждой категории создаём отдельный диапазон: Электроника_Товары, Химия_Товары, Текстиль_Товары.

  2. Назначаем имена: аналогично пункту 1.

  3. Выпадающий список категории (ячейка C2) — обычный список из диапазона Категории.

  4. Выпадающий список товара (ячейка D2) — формула: 

    =INDIRECT(C2 & "_Товары")
    • INDIRECT берёт текст из C2, добавляет суффикс «_Товары» и возвращает соответствующий диапазон.

Важно: имена диапазонов не могут содержать пробелы. Если названия категорий с пробелами, замените их на подчёркивания (например, «Тяжёлое_оборудование»).

6. Форматирование и визуальная подсказка

  • Цветовая заливка ячейки с выпадающим списком (например, светло‑зеленый) помогает быстро находить её в таблице.
  • Условное форматирование: если выбранный товар — «Складской запас = 0», ячейка может автоматически менять цвет на красный, сигнализируя о необходимости пополнения.

Пример условного форматирования

  1. Выделите ячейку B2 (список товаров).

  2. Условное форматирование → Новое правило → Формула: 

    =VLOOKUP(B2;Товары_Список;2;FALSE)=0

  3. Установите желаемый цвет заливки.

Как работает: VLOOKUP ищет выбранный товар в справочном списке, берёт значение из второго столбца (например, «Количество на складе») и проверяет, равно ли оно нулю.

7. Защита листа и сохранение целостности данных

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

  1. На вкладке Рецензирование → Защитить лист (или Review → Protect Sheet).
  2. Укажите, какие действия разрешены (например, только ввод данных в ячейки с выпадающими списками).

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

8. Ошибки, которые часто встречаются, и как их решить

Ошибка Причина Как исправить
«Список пустой» Диапазон содержит пустые ячейки или имя диапазона указано неверно. Проверьте, что в диапазоне нет пустых строк, и что имя диапазона точно совпадает.
«Неправильный диапазон» (в Google Sheets) В формуле INDIRECT используется пробел. Замените пробелы на подчёркивания или используйте функцию SUBSTITUTE.
«Список не обновляется» Вы использовали фиксированный диапазон (например, A2:A10). Перейдите к динамическому диапазону (OFFSET или Таблица).
«Появляются дубли» В справочном листе случайно повторяются названия. Удалите дубли, либо используйте функцию UNIQUE в отдельном столбце.

9. Лучшие практики

  • Храните справочные списки на отдельном листе и делайте их скрытыми, чтобы они не мешали пользователю.
  • Ставьте ограничения на количество символов (например, 30) в выпадающем списке, если названия товаров длинные.
  • Регулярно проверяйте актуальность списка: если товар удалён из ассортимента, удалите его и из справочного диапазона.
  • Документируйте имена диапазонов в отдельной таблице «Справочник», чтобы новые коллеги быстро понимали структуру.

Практика для закрепления

  1. Создайте справочный лист «Товары» и введите 15 названий товаров (по вашему выбору). Назовите диапазон Товары_Список.
  2. На листе Заказы сделайте выпадающий список в ячейке C5, используя Проверка данных и ваш диапазон. Проверьте, что список работает.
  3. Добавьте в листе Товары ещё 5 новых товаров. Обновите список так, чтобы новые позиции автоматически появлялись в выпадающем меню без изменения формулы. (Подсказка: используйте Таблицу или OFFSET).
  4. Реализуйте зависимый список: в ячейке D5 выберите категорию (например, «Электроника», «Химия»). В ячейке E5 автоматически появится список товаров только из выбранной категории.
  5. Настройте условное форматирование: если выбранный товар имеет нулевой остаток (в отдельном столбце «Кол‑во»), ячейка с выпадающим списком должна стать красной.

Проверьте: после выполнения всех пунктов ваш документ должен работать без ошибок, а ввод данных — быть быстрым и безопасным.

Поздравляем! Теперь вы уверенно создаёте и поддерживаете выпадающие списки товаров, делая свои логистические таблицы более надёжными и удобными. Если возникнут вопросы, возвращайтесь к этому материалу — он всегда подскажет, как решить типичную проблему. Удачной работы!


Почему Excel без VBA быстрее внедрить в малой логистике
Структура исходных данных: что должно быть в таблице «Остатки на складах»
Структура исходных данных: таблица «Поступления товара»
Структура исходных данных: таблица «Отгрузки и продажи»
Создание умной таблицы (Ctrl+T) для автоматического расширения диапазонов
Правила именования складов и ячеек хранения
Формула СУММЕСЛИ для подсчёта остатков по конкретному товару
Формула СУММЕСЛИМН для расчёта остатков по товару + складу
Расчёт свободного объёма склада в паллето-местах
Создание справочника «Товары» с габаритами и весом единицы
Формула ВПР для подстановки габаритов из справочника в таблицу заказов
Автоматический расчёт веса партии по ВПР + умножение
Автоматический расчёт объёма партии (длинаширинавысотаколичество)
Условное форматирование для подсветки отрицательных остатков (красным)
Условное форматирование для подсветки нулевых остатков (серым)
Условное форматирование для подсветки страхового запаса (жёлтым)
Создание выпадающего списка складов через «Проверку данных»
Создание выпадающего списка товаров через «Проверку данных»
Динамическая таблица остатков с выбором склада через выпадающий список
Формула ЕСЛИ для сигнала «Срочный заказ» при остатке ниже нормы
Создание простого дашборда остатков на одном листе
Таблица «Заявки на перевозку»: номер, дата, маршрут, вес, объём
Справочник автомобилей: тип, грузоподъёмность, объём кузова, расход топлива
Формула ВПР для подбора авто по грузоподъёмности (ближайшая сверху)
Дополнительная проверка: объём груза < объёма кузова
Формула И / ИЛИ для одновременной проверки веса и объёма
Условное форматирование для авто, которые НЕ подходят (зачёркивание)
Автоматическая маркировка «Газель» / «Фура» по весу
Расчёт минимального количества автомобилей для сборного груза
Расчёт коэффициента загрузки фуры (факт/максимум) в процентах
Сводная таблица для анализа частоты заказов по каждому маршруту
Гистограмма в сводной таблице для визуализации загрузки авто
Расчёт удельной стоимости перевозки на 1 кг или 1 куб. м
Расчёт остатков на завтра: остаток сегодня минус плановая отгрузка
Формула СЕГОДНЯ для контроля сроков годности (если скоропорт)
Условное форматирование для товаров с истекающим сроком годности
Создание журнала перемещений между складами
Расчёт транзитных остатков (товар в пути) формулой СУММЕСЛИМН
Общий доступный остаток = складской + транзитный
Расчёт точки заказа (мин. остаток под заказ новой партии)
Автоматический текст: «Заказать N единиц» через ЕСЛИ и округление
Проверка дубликатов в накладных через СЧЁТЕСЛИ
Создание понятной шапки с фильтрами (Автофильтр)
Защита листов от случайного изменения формул (без пароля на старте)
Настройка вывода процентов загрузки склада в условных значках (зелёный/жёлтый/красный)
Расчёт оборачиваемости товара (отношение продаж к среднему остатку)
Создание шаблона «Заявка водителю» с автоподстановкой данных из подобранного авто
Сохранение файла как шаблона (.xltx) для ежедневного копирования
Чек-лист из 5 шагов на каждый день: проверить отрицательные остатки, загрузку авто, подсветку страхового
Бонусный пункт: как не сломать формулы при вставке новых строк
АПТЕЧКА ДЛЯ СОБАКИ С ПОМОЩЬЮ МЕДИЦИНЫ
Часы на весь экран в высоком разрешении
Чат рулетка 2026: чаты с элементом неопределенности
Чат рулетка бесплатно
Чатрулетка: чат с новой личностью
Чай и кофе: два мира вкуса
Диагностика шин: Проверка давления и износа
Excel для логистики: бесплатный курс учёта остатков и подбора авто
Фототехника в цифровую эпоху
Игрушки для развития навыков самостоятельности
Инновационные подходы в управлении грузовыми перевозками на линиях Валберис
Немецкие авто: легковые и внедорожники
Общение с Аней в чате
Онлайн генератор паролей с символами
Оптимизация динамического контента GEO
Политика конфиденциальности и пользовательское соглашение
Сервер для affiliate-маркетинга: Безопасность, Скорость, Изоляция
Смешные телевизоры
Современные тенденции в российском автопроме
Средства IP видеонаблюдения