Как реализовать динамический зависимый список проверки данных в Excel

Question

Источники в Интернете показывают, как реализовать зависимые списки проверки данных, но все, с чем я столкнулся, являются статичными и не обновляются автоматически.

Я полагаю, что тот, кто сможет ответить на этот вопрос, уже знает на профессиональном уровне, как работают списки проверки данных, но для таких учащихся, как я, я дам краткое описание (рекомендую более подробно почитать в Google и посмотреть видео на YouTube).

Списки проверки данных позволяют вам создать определенную структуру данных в вашей рабочей книге. Они больше предназначены для взаимодействия с пользователем. Если вы хотите, чтобы пользователь рабочей книги вставлял только набор заданных данных, например, пончики, пирожные, кексы и пышки, вы можете создать список, используя именованный диапазон, скажем, «Закуски», или можете создать таблицу и назвать ее «Закуски». Где заданное имя — одно слово (т. е. в заданном имени нет пробелов)

Недостатком использования списков является то, что если бы нам пришлось добавить еще одну закуску в список под названием Круассаны, то нам пришлось бы переопределять изначально созданные списки. Таблицы обходят эту неэффективность в том смысле, что вы можете легко добавить новую строку в таблицу, добавив ее из последней строки или записав в следующей строке после текущей строки таблицы и нажав Enter.

Теперь для проверки данных я рекомендую перейти по ссылке ниже, чтобы получить подробное объяснение, поскольку объяснение делает этот вопрос слишком длинным.http://analyticsdemystified.com/excel-tips/excel-dropdowns-done-right/

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

Таким образом, если в ячейке есть раскрывающийся список основных категорий, как создать динамический зависимый список проверки на основе подкатегорий?

Под динамически зависимым списком проверки я подразумеваю, что все списки столбцов являются таблицами, как показано ниже.

Теперь вопрос в том, как реализовать смежную ячейку, например, если ячейка C13 содержит список проверки для таблицы «Закуски», как сделать ячейку D13 списком проверки на основе выбранного пользователем ввода C13 (т. е. если C13 выбрана как «Пончики», то возможным выбором списка проверки D13 будут данные столбца таблицы «Пончики»).

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

Answer 1

Легкий способ в 4 простых шага

Шаг 1 Создайте свои таблицы:

Шаг 2 Назовите свои таблицы: выберите таблицы и переименуйте их так, чтобы они соответствовали значению в основной таблице.Таблица 1, например, Таблица2 с заголовком «Торты» должна быть названаТорт, так как это значение в первичной таблицеТаблица 1.
Но как? Щелкните угол таблицы, чтобы выбрать ее, введите новую метку там, где написаноТаблица 2или что-то подобное и нажмите Enter. Не волнуйтесь, если там все еще написано "Table2" и т. д.
(Примечание!Это не обязательно для основной таблицы, например Table1.)

Шаг 3 Выберите, где вы хотите разместить свой первый список, перейдите на вкладку «Данные» и в «Проверка данных». Выберите «список» и введите: =INDIRECT("Table1")в исходном окне:

Шаг 4 Выберите ячейку для второго списка, который будет зависеть от первого списка. Вернитесь к проверке данных, выберите «список» и введите, =INDIRECT(K2)где «K2» — местоположение первого списка.

СДЕЛАННЫЙ

Для третьего списка, зависящего от второго списка, повторите шаг 4 и обратитесь ко второму списку. Удачи!

Answer 2

Вам просто придется поверить мне на слово, что это работает, но я написал (очень запутанный, спагетти-код) макрос именно для этой цели. К сожалению, код совсем не элегантный, но, эй, он работает! Как говорится... "Сделай так, чтобы работало, сделай правильно, сделай быстро".

Шаг 1: Код VBA

Вам понадобятся три вещи:

1. Модуль массивов Чипа Пирсона--- сам код VBA находится ближе к концу.

2. Модуль сортировки Чипа Пирсона-- фактический код VBA также ближе к концу.

3. Мой ужасно организованный макрос

Чтобы вставить их в модули VBA в вашей рабочей книге, нажмите Alt+ F11, найдите свой проект в Project Explorer, щелкните правой кнопкой мыши папку Modules и выберите "Insert Module". Вставьте код из каждой из 3 ссылок выше в отдельные модули.

Находясь в редакторе VBA, откройте диалоговое окно «Ссылки» ( Alt+, Tзатем Enter) и установите флажок «Microsoft Scripting Runtime».

Шаг 2: Структурирование данных

С этим разобрались, теперь вам понадобится лист с вашими данными (называемыми, скажем, «Данные»), как если бы это была хорошо структурированная база данных. Вот как бы я это сделал:

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

Шаг 3: Основной лист

Давайте перейдем к листу данных и настроим следующую структуру:

Выберите ячейку C2и назовите ее Snack.Selected. Чтобы назвать ячейку, выберите ее и нажмите Alt M M D, затем введите имя в появившемся диалоговом окне, как показано на рисунке. На данный момент введите значение, например «Торт», чтобы следующий шаг был более понятным.

Продолжайте и назовите ячейку C3«Type.Selected», но пока оставьте ее пустой.

Шаг 4: Извлечение значений из нашей таблицы в листе данных

Перейдите к листу проверки и настройте следующую структуру:

Как вы можете видеть на снимке экрана, вам нужно выделить ячейки B3:B20и ввести следующую формулу, и именно здесь мой макрос вступает в действие:

=MultiLookup("Data","Snack",TRUE,TRUE)

Вместо нажатия Enter нажмите Ctrl+Shift+Enter, потому что этоФормула массива

Если все работает так, как заявлено, вы должны увидеть список ваших закусок, за которым следуют ошибки #N/A. Ошибки ожидаемы — они просто означают, что у вас меньше закусок, чем вы ожидали, исходя из размера B3:B20.

Объясняем параметры в формуле:

1. Лист, где находятся данные («Данные»)
2. Поле, которое вы хотите заполнить на этом листе («Закуска»)
3. Удалить дубликаты (т.е. сгруппированные)? (ИСТИНА)
4. Сортировать по алфавиту? (ВЕРНО)

Хорошо, это кажется большой работой за небольшую ценность, но вот и гранд-финал. В ячейках C3:C20вы разместите следующую формулу:

=MultiLookup("Data","Type",TRUE,TRUE,"Snack",Snack.Selected)

Это должно показать доступные типы тортов, которые соответствуют значению, введенному вами на основном листе на предыдущем шаге, помните?

Это работает путем добавления двух аргументов к формуле:

5. Поле для фильтрации ("Закуска")
6. Выбрать только значения, равные (Snack.Selected)

Поэтому, когда вы изменяете данные в Main, столбец Types обновляется автоматически!

Шаг 5: Завершение проверки

Чтобы завершить все это, давайте закончим нашу проверку данных, создав имена Snack.Choicesи Type.Choices. Пожалуйста, назовите ячейки B1и C1в разделе Проверка соответственно. Вы также можете просто ввести в этом маленьком поле, чтобы назвать ячейку:

В ячейку B1необходимо поместить следующую формулу:

="Validation!"&CELL("address",B3)&":"&CELL("address",OFFSET(B$2,COUNTIF(B3:B50,"*"),0))

Это создает текстовую ссылку на Validation!$B$3:$B$6, где находятся ваши варианты закусок. Скопируйте эту формулу вправо, и все готово!

Вернитесь на основной лист и обратимся к этим ячейкам в правилах проверки для «Закуска» и «Типа».

Выберите ячейку C2и kbd>Alt A V V, чтобы создать проверку данных. Выберите «Список» и установите источник на =INDIRECT(Snack.Choices). Обратите внимание, что вокруг нет кавычекSnack.Choices

Сделайте то же самое для ячейки, C3но установите источник на =INDIRECT(Type.Choices).

Мы используем , INDIRECT()поскольку значения Snack.Choicesи Type.Choicesявляются косвенными (т.е. текстовыми) ссылками на диапазоны в нашей рабочей книге.

Теперь поэкспериментируйте с параметрами проверки, чтобы увидеть все это в действии.

Дайте знать, если у вас появятся вопросы!