2015年9月1日 星期二

Excel-找出天干和地支的全部組合(OFFSET,INT,MOD)

孩子無意中提到天干、地支為何?排列結果為何?
天干:甲、乙、丙、丁、戊、己、庚、辛、壬、癸。
地支:子、丑、寅、卯、辰、巳、午、未、申、酉、戌、亥。
天干和地支的組合形成了循環60個循環,如下圖的D欄。
該如何利用 Excel 的公式找出天干和地支的全部組合呢?

【公式設計與解析】
組合一(參考D欄)
儲存格D2:=OFFSET($A$2,MOD(ROW(1:1)-1,10),,,)&OFFSET($B$2,MOD(
ROW(1:1)-1,12),,,)
MOD(ROW(1:1)-1,10):當公式向下複製時,會產生 0,1,2,3,4,5,6,7,8,9。
上式置入 OFFSET 函數,以儲存格A2為起點,取得「甲、乙、丙、丁、戊、己、庚、辛、壬、癸」的循環。
MOD(ROW(1:1)-1,12):當公式向下複製時,會產生 0,1,2,3,4,5,6,7,8,9,10,11。
上式置入 OFFSET 函數,以儲存格B2為起點,取得「子、丑、寅、卯、辰、巳、午、未、申、酉、戌、亥」的循環。
可產生 60 個不重覆的組合。

組合二(參考E欄)
儲存格E2:=OFFSET($A$2,INT((ROW(1:1)-1)/12),,,)&OFFSET($B$2,MOD(
ROW(1:1)-1,12),,,)
INT((ROW(1:1)-1)/12):當公式向下複製時,會產生12個0、12個1、12個2、...。
上式置入 OFFSET 函數,以儲存格A2為起點,取得「12個甲、12個乙、12個丙、...」的循環。
MOD(ROW(1:1)-1,12):當公式向下複製時,會產生 0,1,2,3,4,5,6,7,8,9,10,11。
上式置入 OFFSET 函數,以儲存格B2為起點,取得「子、丑、寅、卯、辰、巳、午、未、申、酉、戌、亥」的循環。
可產生 120 個不重覆的組合。

沒有留言:

張貼留言

好康東東