☰

Особая потребность в MS Excel для зависимых раскрывающихся значений

tag-icon • tag-icon microsoft-excel
Особая потребность в MS Excel для зависимых раскрывающихся значений

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

для домена может быть несколько ролей, например

Domain Role
A       XYZ
A       ABC
B       DEF
C       DHG
A       LKJ
B       OIO
C       CND

и т. д.

У меня есть на том же листе или на другом листе столбец, содержащий уникальные значения в столбце Domain, а в следующем столбце мне нужно отобразить раскрывающийся список на основе выбора в столбце Domain. Например, если кто-то выбрал B в раскрывающемся списке Domain в одной строке, то столбец role той же строки должен отобразить в раскрывающемся списке только значения, которые являются специфичными для значений B в ролях, показанных выше, т. е. DEF и OIO.

решение1

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

Я составил свой пример рабочего листа следующим образом:

Скриншот рабочего листа

Введите эту формулу в качестве источника проверки данных раскрывающегося списка в ячейке E2:

=INDEX($B:$B,MATCH(D2,$A:$A,0)):INDEX($B:$B,MATCH(D2,$A:$A,0)+COUNTIF($A:$A,D2)-1)

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

Недостатки:

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

Существуют более сложные решения для преодоления всех этих ограничений.

Ладно. Просто чтобы Раджеш С.счастливый, (и докажите, что он неправ ;-) эм, нет, тыненужна формула массива для генерации уникального списка,илисоздать зависимый/отфильтрованный список,если исходный список отсортирован), вот обновленный рабочий лист, включающий создание списка уникальных доменов и раскрывающуюся формулу проверки в столбце, Dкоторый его использует:

Обновленный скриншот рабочего листа

Theобычный, не входящий в массивВведенная C2и затем заполненная формула выглядит следующим образом:

=T(INDEX($A:$A,IFERROR(MATCH(C1,$A:$A,0),ROW())+COUNTIF($A:$A,C1)))

Формула проверки данных D2:

=INDEX($C:$C,MATCH("Unique",$C:$C,0)+1):INDEX($C:$C,MATCH("Unique",$C:$C,0)+ROWS(C:C)-COUNTIF(C:C,"")-1)

решение2

Вам необходимо создать зависимые раскрывающиеся списки.

введите описание изображения здесь

Следуй этим шагам:

  • В ячейке B304 напишите haeder, LISTA и поместите значения в строки ниже, как показано на снимке экрана.
  • Выберите B305:B307 и отсортируйте диапазон по возрастанию. Затем назовите этот диапазон "ListA".

Примечание. Выполните также описанные выше шаги для списков ListB и ListC.

  • Выберите B304:D304 и назовите этот диапазон ROLE.

  • Выберите E304, перейдите на вкладку ДАННЫЕ и нажмите «Проверка данных».

  • На вкладке «Настройка» для параметра «Разрешить» выберите «СПИСОК», а для параметра «Источник» — «Запись» =Role.

  • Поместите указатель ячейки в ячейку G304 и снова перейдите к проверке данных.

  • Напишите =INDIRECT($E$304) в текстовом поле «Источник».

Примечание, Затем, как только вы выберете имя домена из первого раскрывающегося списка, Excel отфильтрует связанные роли в соседнем раскрывающемся списке, как вы можете видеть на снимке экрана.

Примечание.

  • Настройте адрес ячейки в соответствии с вашими потребностями.

  • Если вы считаете, что можете заменить LISTA, LISTB и LISTC на A, B и C.

Связанный контент