2011年6月23日 星期四

Excel-產生文字清單(CHOOSE,MOD)

在 Excel 中有提供自訂清單功能,如果想要以公式產生想要的清單,該如何處理呢?

儲存格A2:=CHOOSE(MOD(ROW(1:1)-1,7)+1,"星期日","星期一","星期二","星期三","星期四","星期五","星期六 ")

MOD(ROW(1:1)-1,7)+1:根據不同列得到1,2,3…數字,透過MOD取得和變化量(本例為7)相除的餘數,即可產生1,2,3,4,5,6,7,1,2,3,4,5,6,7,…。

再透過CHOOSE乙數對照得到對應的一個字串。

同理:

儲存格B2:=CHOOSE(MOD(ROW(1:1)-1,10)+1,"甲","乙","丙","丁","戊"," 己","庚","辛","壬","癸")

儲存格C2:=CHOOSE(MOD(ROW(1:1)-1,12)+1,"一年一班","一年二班","一年三班","一年四班","一年五班","一年六班","一年七班","一年八班","一年九班","一年十班","一年十一班","一年十二班")

複製儲存格A2:C2,往下各列貼上。

沒有留言:

張貼留言

好康東東