贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

廣告贊助

網友問到:如何在 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)

文章標籤
創作者介紹

學不完.教不停.用不盡

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


留言列表 (1)

發表留言
  • Harold
  • A1欄 儲存格1.4.6.13
    B1欄儲存格2.4.6.22.14
    C1欄儲存格3.4.6.22.23.30
    A1欄儲存格的數字元為 4個
    B1欄儲存格的數字元為 5個
    C1欄儲存格的數字元為 6個
    請問如何用公式計算儲存格的數字元?
找更多相關文章與討論

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼