На приведенном ниже снимке экрана показано, как это сделать, используя динамический список значений для раскрывающихся списков.
Первый список значений 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) и может выполнять вычисления массива без необходимостиCTRLShiftEnter .
Массив, с которым он работает (ROW(E$5:E$8)-ROW(E$4))/(E$5:E$8<>""), это массив, разделенный на массив True/Falseзначений, где диапазон E5:E8не пуст: .
В арифметических операциях участвуют логические значения Trueи Falseпреобразуются в 1 и 0. Таким образом, деление дает массив {#DIV/0!;2;3;#DIV/0!}и AGGREGATE()игнорирует ошибки, давая номера строк, которые не являются пустыми: в случае, показанном выше.
Этот массив затем используется в качестве row_nums в an INDEX(), который возвращает непустые строки из E5:E8. Наконец, IFERROR()возвращает пустое, когда INDEX()возвращается#NUM! поскольку оно заполнено дальше, чем количество значений в его списке row_nums.
Формулы для именованных диапазонов используются INDEX()для возврата последнего непустого значения в каждом списке. SUM()подсчитывает количество непустых ячеек в диапазоне, которое используется как row_num для INDEX().