2017年4月10日 星期一

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

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

沒有留言:

張貼留言

檢視其他文章

好康東東