網友問到:如何在 Excel 的工作表中將一欄的資料轉換成多欄?本篇將要把轉換工作,製作成動態可調至任意欄數。

參考下圖:第一欄為基本資料(儲存格A1:A101),要分欄的欄數置於儲存格D1。

Excel-動態將一欄資料轉換成多欄(OFFSET,COLUMN,ROW)

【公式設計與解析】

1.選取儲存格A1:A101,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:資料。

2. 選取儲存格C1:D1,按 Ctrl+Shift+F3 鍵,勾選「最左列」,定義名稱:分欄。

3. 輸入公式

儲存格C4:=IF(COLUMN(A:A)<=分欄,OFFSET($A$2,(ROW(1:1)-1)*分欄+
COLUMN(A:A)-1,0),"")&""

複製儲存格C4,貼至儲存格C4:J51。

(1) ROW(1:1)-1)*分欄+COLUMN(A:A)

公式複製後,得到以下結果(注意:分欄數為5)。

每一列顯示:1,2,3,4,5,6,8→6,7,8,9,10,11,12,13→…。

每一欄顯示:1,6,11,16,21,26,31,36,…→2,7,12,17,22,27,32,37,…→…。

Excel-動態將一欄資料轉換成多欄(OFFSET,COLUMN,ROW)

(2) OFFSET($A$2,第(1)式-1,0)

將第(1)式代入 OFFSET 函數,取得對應的儲存格內容。

如下圖。先觀察H欄、I欄、J欄。

Excel-動態將一欄資料轉換成多欄(OFFSET,COLUMN,ROW)

(3) IF(COLUMN(A:A)<=分欄,第(2)式,"")

判斷將超過分欄數的儲存格內容顯示為空白。

Excel-動態將一欄資料轉換成多欄(OFFSET,COLUMN,ROW)

(4) IF(COLUMN(A:A)<=分欄,第(2)式,"")&""

將上圖中的顯示『0』的儲存格(第24列以後)轉換為空字串(不顯示任何內容)。

(5)如此,便可以由分欄數來控制想要想示的分欄內容了。

Excel-動態將一欄資料轉換成多欄(OFFSET,COLUMN,ROW)

Excel-動態將一欄資料轉換成多欄(OFFSET,COLUMN,ROW)

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

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