2016年2月24日 星期三

Excel-每欄取定量重排並去除空白(OFFSET,MOD,INT)

網友問到:在 Excel 的工作表中(如下圖左),如果想要在每欄取定量來重排,並且去除空白儲存格(如下圖右),該如何處理?
在下圖中,在甲、乙、丙三種不同且重覆的欄位,如果根據取樣中的數量(本例為5),將甲的多欄資料重組在一欄(每欄取5個),並且希望去除空格。
Excel-每欄取定量重排並去除空白(OFFSET,MOD,INT)

【公式設計與解析】
(1) OFFSET(A$2,MOD(ROW(1:1)-1,$H$2),INT((ROW(1:1)-1)/$H$2)*3)
MOD(ROW(1:1)-1,$H$2):依儲存格H2的數值(=5),當公式向下複製時傳回 0, 1, 2, 3, 4, 0, 1, 2, 3, 4, ...。
INT((ROW(1:1)-1)/$H$2)*3:依儲存格H2的數值(=5),當公式向下複製時傳回 0, 0, 0, 0, 0, 3, 3, 3, 3, 3, 3, ...。

(2) 取出定量重排
儲存格I2:=IF(ISBLANK(第(1)式,"",第(1)式)
當 OFFSET 函數取得的儲存格內容為空白儲存格時,改以空字串顯示。
複製儲存格I2,往下各列貼上。

(3) 去除空白儲存格
儲存格M2:{=IFERROR(OFFSET(I$2,SMALL(IF(I$2:I$17<>"",ROW(I$2:I$17), 
FALSE),ROW(1:1))-2,,,),"")}
這是陣列公式,輸入完成要按 Ctrl+Shift+F3 鍵,Excel 會自動產生「{}」。
IF(I$2:I$17<>"",ROW(I$2:I$17):在陣列公式中判斷儲存格I2:I17裡不是空白儲存格者,傳回其列號(ROW(I2:I17)。
SMALL(IF(I$2:I$17<>"",ROW(I$2:I$17),FALSE):將上式傳回的列號中,由小到大依序取出其最小者。
OFFSET(I$2,SMALL(IF(I$2:I$17<>"",ROW(I$2:I$17),FALSE),ROW(1:1))-2,,,):將
上式代入 OFFSET 函數傳回對應儲存格的內容。
最後利用 IFERROR 函數,將因查詢不到資料而傳回錯誤訊息者,以空白顯示。
複製儲存格M2,往下各列貼上。

沒有留言:

張貼留言

好康東東