贊助廠商

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

網友問到:在 Excel 中如果要將一列多欄轉為多列多欄,該如何處理?

參考下圖,想要將同一列中的儲存格A1:R1,轉換為儲存格A5:D9(四欄)。

Excel-將一列多欄轉為多列多欄(MOD)

 

【公式設計與解析】

儲存格A5:=OFFSET($A$1,0,+(ROW(1:1)-1)*4+MOD(COLUMN(A:A)-1,4),1,1)

複製儲存格A5,貼至儲存格A5:D9。

(ROW(1:1)-1)*4+MOD(COLUMN(A:A)-1,4) 其中參數『4』表示要轉換為 4 欄多列。

公式在儲存格A5:D9,其結果參考下圖,分別產生 0, 1, 2, .... , 19。

image

代入 OFFSET 函數取得對應的儲存格內容。

 

【延伸練習】

仿本例,如果要將一欄多列轉換為多欄多列,該如何設計公式?

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

網友問到:參考下圖,在 Excel 中,如何將表格中的欄列互換?

在下圖中,如果你使用複製功能,再使用『轉置』貼上,即可得到欄列互換的結果。但是,如果要使用公式來設計,該如何處理?

Excel-如何將表格的欄列互換(ROW,COLUMN)

 

【公式設計與解析】

透過 OFFSET 函數處理欄列互換很簡單!根據微軟的定義:

OFFSET函數:傳回根據所指定列數及欄數之儲存格或儲存格範圍之範圍的參照。

語法:OFFSET(reference, rows, cols, [height], [width])

Reference:用以計算位移的起始參照。

Rows:要左上角儲存格往上或往下參照的列數。

Cols:要結果的左上角儲存格向左或向右參照的欄數。

height:要傳回參照的列數高度。

width:要傳回參照的欄數寬度。

公式:

儲存格B11:=OFFSET($A$1,COLUMN(A:A),ROW(1:1),1,1)

只要列和欄的參數分別以 COLUMN 和 ROW 代入即可。

函數中,Rows:COLUMN(A:A),當公式向右複製時 COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→...。

函數中,Cols:ROW(1:1),當公式向下複製時 ,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。

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

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼