網友問到:在 Excel 中有如下圖左的資料清單,如何能轉換為下圖右的排列方式?(共有二種)

轉換一:

Excel-手動資料轉置(OFFSET,ROW,COLUMN)

【公式設計與解析】

分析原始資料,資料分佈於間隔 2 列(2,4,6,8)、連續 1 欄(1,2,3,4,5,6)。

儲存格I1:=OFFSET($A$1,INT((ROW(1:1)-1)/6)*2+1,MOD((ROW(1:1)-1),6))

複製儲存格I1,貼至儲存格I1:I24。

(1) INT((ROW(1:1)-1)/6)*2+1

當公式向下複製時,依序產生 1,1,1,1,1,1,3,3,3,3,3,3,5,,5,5,5,5,5,7,7,7,7,7,7。

公式中的「/6」乃因為連續 6 欄;公式中的「*2」乃因為間隔 2 列。

(2) MOD((ROW(1:1)-1),6)

當公式向下複製時,依序產生 0,1,2,3,4,5,0,1,2,3,4,5, 0,1,2,3,4,5,0,1,2,3,4,5。

(3) OFFSET($A$1,第(1)式,第(2)式)

將第(1)式和第(2)式代入 OFFSET 函數中,依序取出對應的儲存格內容。

 

轉換二:

Excel-手動資料轉置(OFFSET,ROW,COLUMN)

【公式設計與解析】

分析原始資料,資料分佈於連續 1 列(2,3,4,5)、連續 1 欄(1,2,3,4,5,6)。

儲存格I1:=OFFSET($A$1,INT((ROW(1:1)-1)/6)+1,MOD((ROW(1:1)-1),6))

複製儲存格I1,貼至儲存格I1:I24。

(1) INT((ROW(1:1)-1)/6)+1

當公式向下複製時,依序產生 1,1,1,1,1,1,2,2,2,2,2,2,3,3,3,3,3,3,4,4,4,4,4,4。

(2) MOD((ROW(1:1)-1),6)

當公式向下複製時,依序產生 0,1,2,3,4,5,0,1,2,3,4,5, 0,1,2,3,4,5,0,1,2,3,4,5。

(3) OFFSET($A$1,第(1)式,第(2)式)

將第(1)式和第(2)式代入 OFFSET 函數中,依序取出對應的儲存格內容。

學不完.教不停.用不盡文章列表

arrow
arrow
    文章標籤
    Excel OFFSET ROW COLUMN
    全站熱搜

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