Блог

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

Входной 2-D массив, из которого я хочу получить массив подмножеств, окрашен

На изображении показан возвращенный размер массива в последнем столбце

Пользователю предоставляется выпадающее меню для выбора нужной строки (скажем, A1:A6).

Строка, выбранная пользователем, определяет, из какой строки 2-D цветного диапазона (скажем, B1:G6 ) извлекать отфильтрованный массив, тогда из этой строки возвращаются только ячейки с текстом/данными

Я попробовал следующий код

Я могу получить массив всей правильной строки (все ячейки), используя

 index(B1:G6, match(A1:A6,value_from_dropdown_menue,0),0)  

затем я попытался применить функцию row() для фильтрации пустых элементов из этого возвращенного массива .. давайте для удобства чтения будем ссылаться на returned_full_row_array как RFRA

 index(RFRA, row(indirect("1:"amp; counta(RFRA))))  

полная формула выглядит так (B8-ячейка с выпадающим списком )

 =INDEX(INDEX(2D_Matrix,MATCH(B8,1D_Menue_List,0),0),ROW(indirect("1:"amp; counta(INDEX(2D_Matrix,MATCH(B8,1D_Menue_List,0),0)))))  

но функция row() не возвращает массив, и, следовательно, я не возвращаю массив .. я получаю только первый элемент.. даже если я жестко закодировал строку(1:3) .. я не возвращаю массив из 3 элементов … только первый элемент всей строки

есть какие-нибудь идеи?

Редактировать : Я могу успешно получить всю строку моего диапазона, используя соответствие индексу. Результатом является массив фиксированного размера 1 на 6, подобный этому (пример первой и 2-й строки).:

 for 1st row gt;gt; {"M1-item1",0,0,0,0,0} for 2nd row gt;gt; {"M2-item1","M2-item2","M2-item3","M2-item4",0,0}  

желаемый результат, однако, должен быть массивом переменной длины без пустых/нулевых элементов :

 for 1st row gt;gt; {"M1-item1"} for 2nd row gt;gt; {"M2-item1","M2-item2","M2-item3","M2-item4"}  

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

Это была бы формула для выполнения этой работы:

 =INDEX(  FullRange,  XMATCH(DropdownSelection,MenuList,0),  XMATCH(  SEQUENCE(  ,  MAX(  MMULT(  SEQUENCE(,ROWS(FullRange),1,0),  MMULT((1-ISBLANK(FullRange))*(DropdownSelection=MenuList),  (SEQUENCE(COLUMNS(FullRange))lt;=SEQUENCE(,COLUMNS(FullRange)))*1)  )  )  ),  MMULT(  SEQUENCE(,ROWS(FullRange),1,0),  MMULT(  (1-ISBLANK(FullRange))*(DropdownSelection=MenuList),  (SEQUENCE(COLUMNS(FullRange))lt;=SEQUENCE(,COLUMNS(FullRange)))*1  )  ),  0  ) )   

Извините, что это так некрасиво посередине. Он используется ISBLANK для определения того, какие ячейки используются.

В частности, эта часть:

 SEQUENCE(COLUMNS(FullRange))lt;=SEQUENCE(,COLUMNS(FullRange)))*1  

создает верхнюю треугольную матрицу.

Если у нас есть матрица, содержащая это:

 (1-ISBLANK(FullRange))*(DropdownSelection=MenuList)  

он будет содержать только те ячейки, которые нам действительно нужны.

Взяв матричное произведение, мы получим матрицу с 0 везде, кроме строки, которую мы ищем. В строке, которую мы ищем, в ней есть 1 в первой ячейке, которую мы хотим до 2-й ячейки, 2 во второй ячейке, которую мы хотим до третьей, и так далее. XMATCH Двигаясь слева (двигаясь вправо), он будет совпадать только с теми ячейками, которые мы хотим.

Это классическая, болезненная проблема. Мне нравится решать эту проблему, сначала перемещая все элементы диапазона в один столбец, как это:

 =LET(  InputArray,  B1:G8,  InputRowCount,  ROWS(InputArray),  TotalCellCount,  COLUMNS(InputArray)*InputRowCount,  LookupRow,  MOD(SEQUENCE(TotalCellCount,,0),InputRowCount),  LookupColumn,  (SEQUENCE(TotalCellCount,,0)-LookupRow)/InputRowCount,  RearrangedAsColumn,  INDEX(InputArray,LookupRow,LookupColumn),  RearrangedAsColumn_IsBlank,  INDEX(ISBLANK(InputArray),LookupRow,LookupColumn),  Result,  FILTER(RearrangedAsColumn,1-RearrangedAsColumn_IsBlank),  Result )  

Я использовал здесь функцию LET, потому что она позволяет мне определять переменные и делает объяснение немного более ясным.

Переступая через:

Я надеюсь, что это то, что вам нужно!