Реклама
Этот сайт использует Яндекс.Метрику для анализа посещаемости. Подробнее
Формулы и функции

Зависимая проверка данных с использованием динамических массивов в Excel

forest_paw 23.01.2026 445

Зависимая проверка данных с использованием динамических массивов в Excel

В этом руководстве вы узнаете, как настроить зависимую проверку данных в Excel, используя мощные функции динамических массивов, такие как SORT, УНИК / UNIQUE и ФИЛЬТР / FILTER. Этот метод позволяет создавать интеллектуальные выпадающие списки, где выбор в одном поле определяет доступные варианты в другом.

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

Настройка показана для настольного Excel Microsoft 365 и Excel 2021/2024 для Windows/Mac с поддержкой разлива. Исходные записи без заголовков: A4:A23 — продукт, B4:B23 — класс; ячейки D4 и E4 находятся на том же листе, вне таблицы, с местом для разлива. Списки не должны содержать пустые записи или ошибки.

Если вы разбираете формулы в Excel с нуля или хотите ускорить расчеты, здесь показано, как использовать функции в экселе на понятных и рабочих примерах.

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

Как настроить зависимые списки

  1. Подготовьте исходные данные. Создайте таблицу с двумя колонками, например, «Продукт» и «Класс». Это будет ваша база данных для списков.
  2. Создайте первый (основной) список. Используйте формулу Русская версия: =СОРТ(УНИК(B4:B23))English version: =SORT(UNIQUE(B4:B23)), чтобы получить уникальные и отсортированные значения для первого выпадающего списка. Введите её в D4. Результат займёт диапазон разлива.
  3. Настройте проверку данных для первого поля. В ячейке, где должен быть первый список (например, H3), откройте инструмент «Проверка данных» (Data Validation). Выберите тип «Список», включите выпадающий список в ячейке и укажите в поле «Источник» ссылку на диапазон разлива из шага 2, используя нотацию решетки: Русская версия: =D4#English version: =D4#.
  4. Создайте формулу для зависимого списка. В другой ячейке (например, E4) введите формулу для фильтрации продуктов на основе выбранного класса: Русская версия: =СОРТ(УНИК(ФИЛЬТР(A4:A23;(B4:B23=H3)*(H3<>"");"")))English version: =SORT(UNIQUE(FILTER(A4:A23,(B4:B23=H3)*(H3<>""),""))). Эта формула вернет только те продукты, которые соответствуют значению, выбранному в ячейке H3.
  5. Настройте проверку данных для второго поля. В ячейке для зависимого списка (например, H5) снова откройте «Проверку данных» и в качестве источника укажите диапазон разлива из формулы фильтрации: Русская версия: =E4#English version: =E4#.
Зависимые списки в H3 и H5 со вспомогательными диапазонами разлива D4# и E4#.
Исходные продукты и классы — A4:B23. Список классов разливается из D4, список продуктов выбранного класса — из E4. Источники проверок для H3 и H5: =D4# и =E4#. Формулы выше удаляют повторы; вместо текстового заполнителя на иллюстрации используется пустая строка.

Советы и лучшие практики

  • Используйте таблицы Excel для вашей исходной базы данных. Это позволит использовать структурированные ссылки (например, Таблица1[Класс]), которые автоматически расширяются при добавлении новых данных.
  • При отсутствии выбора или совпадений третий аргумент ФИЛЬТР возвращает пустую строку; это не настоящий пустой массив и не обработка всех ошибок. Подсказку «Сначала выберите класс» поместите в сообщение для ввода, чтобы она не стала допустимым пунктом списка.
  • Если ваш список очень длинный, рассмотрите возможность использования функции ПРОСМОТРХ (XLOOKUP) в комбинации с ФИЛЬТР / FILTER для более сложных сценариев поиска.

Заключение

Использование динамических массивов для создания зависимой проверки данных — это современный и эффективный подход в Excel. Он даёт альтернативу методам с именованными диапазонами и функцией ДВССЫЛ / INDIRECT, предлагая более гибкое, читаемое и автоматически обновляемое решение. Освоив этот метод, вы сможете создавать интуитивно понятные и формы с проверкой ввода для сбора данных.

Поделиться:

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