我有這樣一個場景，我想在一行中提取一個范圍的子集（最好使用索引、匹配和行函式）并只回傳較小的過濾陣列，其中包含文本/資料的單元格并排除空單元格..我想使用這個過濾后的陣列用于進一步處理
我想從中獲取子集陣列的輸入二維陣列是彩色的
影像顯示了最后一列中回傳的陣列大小
提供了一個下拉選單供用戶選擇所需的行（例如 A1:A6）。
用戶選擇的行確定從 2-D 彩色范圍（例如 B1:G6 ）中提取過濾后的陣列的哪一行，然后僅從該行回傳帶有文本/資料的單元格
我嘗試了以下代碼
我可以使用獲得整個正確行（所有單元格）的陣列
index(B1:G6, match(A1:A6,value_from_dropdown_menue,0),0)
然后我嘗試應用 row() 函式從回傳的陣列中過濾掉空元素..讓我們將回傳的_full_row_array稱為RFRA以提高可讀性
index(RFRA, row(indirect("1:"& counta(RFRA))))
完整的公式如下所示（B8是帶有下拉選單的單元格）
=INDEX(INDEX(2D_Matrix,MATCH(B8,1D_Menue_List,0),0),ROW(indirect("1:"& counta(INDEX(2D_Matrix,MATCH(B8,1D_Menue_List,0),0)))))
但是 row() 函式沒有回傳一個陣列，因此我沒有回傳任何陣列..我只得到第一個元素..即使我硬編碼 row(1:3) ..我沒有得到 3 個元素陣列回傳 ... 只有整行的第一個元素
有任何想法嗎？
編輯：我可以使用索引匹配成功獲取我范圍的整行結果是一個固定大小的 1 x 6 陣列，如下所示（例如第一行和第二行）：
for 1st row >> {"M1-item1",0,0,0,0,0}
for 2nd row >> {"M2-item1","M2-item2","M2-item3","M2-item4",0,0}
然而，所需的輸出應該是一個沒有空/零元素的可變長度陣列：
for 1st row >> {"M1-item1"}
for 2nd row >> {"M2-item1","M2-item2","M2-item3","M2-item4"}
uj5u.com熱心網友回復：
這是一個經典的、痛苦的問題。我喜歡通過首先將范圍內的所有元素移動到單個列中來解決它，如下所示：
=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 函式，因為它允許我定義變數，并使解釋更清晰一些。
逐步通過：
| 姓名 | 它能做什么 |
|---|---|
InputArray |
這就是資料的來源 |
InputRows |
計算行數 |
InputColumns |
計算列數 |
LookupRow |
這使用除法余數給我們行參考 |
LookupColumn |
這將計算我們的列查找 |
RearrangedAsColumn |
這將 InputArray 重述為單列 |
RearrangedAsColumn_IsBlank |
給我們一列顯示單元格是否為空 |
Result |
最后，為非空白單元格過濾 RearrangedAsColumn |
我希望這就是你所追求的！
uj5u.com熱心網友回復：
鑒于最初的問題已經改變，這是一個簡單得多的問題。假設我們有一些命名范圍：
| 命名范圍 | 公式 | 評論 |
|---|---|---|
FullRange |
=L15:Q22 |
全方位的資料 |
MenuList |
=K15:K22 |
選單項串列 |
DropdownSelection |
? | 帶有下拉串列的單元格 |
這將是完成這項作業的公式：
=INDEX(
FullRange,
XMATCH(DropdownSelection,MenuList,0),
XMATCH(
SEQUENCE(
,
MAX(
MMULT(
SEQUENCE(,ROWS(FullRange),1,0),
MMULT((1-ISBLANK(FullRange))*(DropdownSelection=MenuList),
(SEQUENCE(COLUMNS(FullRange))<=SEQUENCE(,COLUMNS(FullRange)))*1)
)
)
),
MMULT(
SEQUENCE(,ROWS(FullRange),1,0),
MMULT(
(1-ISBLANK(FullRange))*(DropdownSelection=MenuList),
(SEQUENCE(COLUMNS(FullRange))<=SEQUENCE(,COLUMNS(FullRange)))*1
)
),
0
)
)
對不起，中間太丑了。它用于ISBLANK查找使用了哪些單元格。
特別是這部分：
SEQUENCE(COLUMNS(FullRange))<=SEQUENCE(,COLUMNS(FullRange)))*1
產生一個上三角矩陣。
如果我們有一個包含以下內容的矩陣：
(1-ISBLANK(FullRange))*(DropdownSelection=MenuList)
它將只包含我們真正想要的那些單元格。
通過取矩陣乘積，我們得到一個矩陣，除了我們所追求的行之外，其他地方都是 0。在我們所追求的行上，它在我們想要的第一個單元格到第二個單元格上有 1，在我們想要到第三個單元格的第二個單元格上有 2，依此類推。通過XMATCH從左邊（向右），它只會匹配我們想要的那些單元格。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/371439.html
上一篇：Excel：動態下拉串列