學不完．教不停．用不盡

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

【公式設計與解析】

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

在本例中，項目個數有「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 函數將公式向下複製時若傳回錯誤訊息時，顯示為空字串。