2017年5月23日 星期二

Excel-資料交錯排列組合(OFFSET,INT,MOD)

在 Excel 的資料表如下圖左,網友問到如何重組每三個文字插入一個數字,依此規則產生資料清單?
在下圖左之中,A欄由數字組成,B欄由文字組成,如何取出每三個文字插入一個數字?
Excel-資料交錯排列組合(OFFSET,INT,MOD)

【公式設計與解析】
(1) 處理數字部分
儲存格F1:=OFFSET($A$1,INT((ROW(1:1)-1)/4),0)
利用 INT 函數將除法結果再取不大於的最大整數。
公式:INT((ROW(1:1)-1)/4)
當公式向下複製時,產生:0,0,0,0,1,1,1,1,2,2,2,2,3,3,3,3,4,4, ...。
儲存格F1向下複製時,產生:1,1,1,1,2,2,2,2,3,3,3,3,4,4,4,4,5,5, ...。
(2)處理文字部分
儲存格G1:=OFFSET($B$1,ROW(1:1)-INT(ROW(1:1)/4)-1,0)
當公式向下複製時,產生:0,1,2,2,3,4,5,5,6,7,8,8,9,10,11,11,12,13, ...。
儲存格G1向下複製時,產生:甲,乙,丙,丙,丁,戊,己,己,庚,辛,壬,壬,癸,子,丑,丑,寅,卯, ...。
(3) 整合文字和數字
儲存格D1:=IF(MOD(ROW(1:1),4)=0,OFFSET($A$1,INT((ROW(1:1)-1)/4),0),
OFFSET($B$1,ROW(1:1)-INT(ROW(1:1)/4)-1,0))
MOD(ROW(1:1),4)=0:判斷儲存格的列號是否為 4,8,12,16, ...。
公式判斷,每隔四列時,會顯示數字,其餘顯示文字。
當公式向下複製時,會產生資料:甲,乙,丙,1,丁,戊,己,2,庚,辛,壬,3,癸,子,丑,4, ...。

沒有留言:

張貼留言

好康東東