網友問到:在 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,往下各列貼上。

arrow
arrow
    全站熱搜

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