有網友問到:在 Excel 中有一個項目清單(參考下圖左),若要依照項目的數量列出(如下圖右),該如何處理?

Excel-將項目依清單數量列出(OFFSET,SUMIF,INDEX)


【公式設計與解析】

我參考了網路其他網友提供的公式,覺得是解題的好方式。

在本例中,項目個數有「5個」,要分別依其個數列出項目。

儲存格D2:{=IFERROR(INDEX($A$2:$A$6,SUM(1*(ROW(1:1)>SUMIF(OFFSET
($B$2,0,0,ROW($1:$5),1),">0")))+1),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動輸入「{}」

(1) OFFSET($B$2,0,0,ROW($1:$5),1)

因為項目個數有5個,使用 ROW($1:$5),若項目個數有6個,則使用ROW($1:$6)。此 OFFSET 函數在陣列公式中分別取得儲存格範圍:B2:B2、B2:B3、B2:B4、B2:B5、B2:B6)。

(2) SUMIF(OFFSET($B$2,0,0,ROW($1:$5),1),">0")))

利用 SUMIF 函數找出儲存格範圍B2:B2、B2:B3、B2:B4、B2:B5、B2:B6中,大於0的數值總和,分別傳回:3, 8, 12, 14, 17。

(3) SUM(1*(ROW(1:1)>SUMIF(OFFSET($B$2,0,0,ROW($1:$5),1),">0")))+1

利用 ROW(1:1)=1 在第 1 個儲存中判斷是否大於 {3,8,12,14,17},結果傳回{FALSE,FALSE,FALSE,FALSE,FALSE},透過「1*」運算,將 TRUE/FALSE 轉換為 1/0

例如:在儲存格B10中,為 ROW(9:9) 判斷是否大於 {3,8,12,14,17},結果傳回{TRUE,TRUE,FALSE,FALSE,FALSE},最後 SUM 運算會加總 1,1,0,0,0,再加 1,最後結果為「3」。

將上式的傳回傳代入 INDEX 函數,顯示儲存格A2:A6中對應的儲存格,例如上式傳回值為「3」,再傳回「丙」。

再利用 IFFERROR 函數將公式向下複製時若傳回錯誤訊息時,顯示為空字串。

創作者介紹

學不完.教不停.用不盡

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()