2016年9月4日 星期日

Excel-自動複製第二列的資料至第一列和第三列(OFFSET,INT,ROW)

有網友問到:在 Excel 的工作表中,每三列中只有第二列有資料,如何複製第二列的資料至第一列和第三列中?
如下圖,儲存格A2有資料,要將儲存格A1和儲存格A3複製儲存格A2的資料,同欄依此類推的操作,該如何處理?
Excel-自動複製第二列的資料至第一列和第三列(OFFSET,INT,ROW)

【公式設計與解析】
1. 原始資料在A欄,在C欄中顯示處理後的結果。
儲存格C1:=OFFSET($A$1,INT((ROW(1:1)-1)/3)*3+1,0)
複製儲存格C1,貼至儲存格C1:C21。
INT((ROW(1:1)-1)/3)*3+2:當公式向下複製時,會產生 2,2,2,5,5,5,8,8,8, ..., 20,20,20。
利用 OFFSET 函數顯示以儲存格C1為起始儲存格,依上式傳回的數值,取出對照的儲存格。
提供另一種解法:
儲存格C1:=INDIRECT(ADDRESS(INT((ROW(1:1)-1)/3)*3+2,1))

2. 原始資料在A欄,在A欄中顯示處理後的結果。
參考以下的步驟:
(1) 選取儲存格A1:A21。
(3) 按 Ctrl+G 鍵,顯示[到]對話框。
(4) 按[特殊]按鈕。
image
(5) 在[特殊目標]對話框中選取「空格」,按一下[確定]按鈕。
image
(6)在被選取儲存格的第一個(儲存格A1)輸入公式:
=OFFSET($A$1,INT((ROW(1:1)-1)/3)*3+1,0)。
image
7. 按下 Ctrl+Enter 鍵。即可產生所要的結果。
image

沒有留言:

張貼留言

好康東東