Динамические раскрывающиеся списки Excel на основе матрицы

Question

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

На приведенном ниже примере снимка экрана первый раскрывающийся список позволяет выбрать либо feature1, либо feature2. Если выбрана функция 1, то X в матрице указывают, что функции 4 и 5 не разрешены в раскрывающемся списке 2, а в раскрывающемся списке 2 должны отображаться только функции 3 и 6 в качестве возможных вариантов выбора. Если функция 3 затем выбрана во втором раскрывающемся списке, то в раскрывающемся списке 3 будет отображаться только функция 8 в качестве опции. Функция 7 недоступна, если выбрана функция 3, а функция 9 была исключена из-за выбора функции 1 в начале.

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

контрольная матрица

Answer 1

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

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

Второй список значений, List Values 2 , создает динамический упорядоченный список значений из List Values 1 , помещая любые пустые значения в конец. Формулы в E14 и E18 :

=IFERROR(INDEX(E$5:E$8,AGGREGATE(15,6,(ROW(E$5:E$8)-ROW(E$4))/(E$5:E$8<>""),ROWS($E$14:$E14)),1),"")
=IFERROR(INDEX(E$9:E$11,AGGREGATE(15,6,(ROW(E$9:E$11)-ROW(E$8))/(E$9:E$11<>""),ROWS($E$18:$E18)),1),"")

Наконец, две формулы создают массивы непустых значений в значениях List Values 2 и присваиваются именам List_2 и List_3 , которые используются для указания значений списка для раскрывающихся списков 2 и 3. Эти формулы показаны на скриншоте.

Надеюсь, это поможет, и удачи. ______________________________________________________________________________

Как это работает: внутренняя функция AGGREGATE() описанная выше, выполняет те же действия, что и SMALL() (function = 15), за исключением того, что она имеет возможность игнорировать ошибки (option = 6) и может выполнять вычисления в массиве без использования CTRL. Shift Enter.

Массив, с которым он работает, (ROW(E$5:E$8)-ROW(E$4))/(E$5:E$8<>"") , то есть массив {1;2;3;4} деленный на массив значений True/False где диапазон E5:E8 не пуст: {False;True;True;False} .

В арифметических операциях с использованием логических значений True и False преобразуются в 1 и 0. Таким образом, деление дает массив {#DIV/0!;2;3;#DIV/0!} , И AGGREGATE() игнорирует ошибки, давая непустые номера строк: {2;3} , в случае, показанном выше.

Этот массив затем используется в качестве row_nums в INDEX() , который возвращает непустые строки из E5:E8 . Наконец, IFERROR() возвращает пробел, когда INDEX() возвращает #NUM! так как он заполняется дальше, чем количество значений в его списке row_nums.

В формулах для именованных диапазонов используется INDEX() чтобы вернуть последнее непустое значение в каждом списке. SUM() подсчитывает количество непустых ячеек в диапазоне, и это используется как row_num для INDEX() .