突發起想的表格~
辦公室要進行排打掃衛生的輪值表
就要輸入日期、打掃人
然後兩個資料要手動輸入
排個半年、幾個月,要輸入n多次
或是開始輸好後,複製貼上,手都要抽筋了
其實只要是日期確定、人員確定,就能用excel簡單做一個「自動排輪值表」
結果畫面
製作方式如下
1、資料設立
2、日期設定
3、定義名稱資料與設定
選中名單,點「公式-定義名稱」,修改名稱欄位為「人員名單」(可自行定義名稱資料),設定完後按「確定」即可。
點第1格,選「數據-有效性」,在設置的允許選為「序列」,下方來源輸入「=人員名單」(依自行設定名稱為準)。
提醒:這裡可以點選中A5:G6,然後按上進行設定,這樣每一格都有下拉選單資料,因為輪值不一定是週日,可能為其他日期開始。
4、輸入第一行區域的查找公式
在B6輸入如下
=IFNA(OFFSET(名單資料!$B$1,IF(MATCH(B5,人員名單,0)>=COUNTA(人員名單),MATCH(B5,人員名單,0)-COUNTA(人員名單),MATCH(B5,人員名單,0)),0),"")
找前一位的名單,利用offset公式偏移數組,只找到前一位名單的下一位,此公式用法網上很多不詳說。
在D5輸入如下
=IFNA(OFFSET(名單資料!$B$1,IF(MATCH(B6,人員名單,0)>=COUNTA(人員名單),MATCH(B6,人員名單,0)-COUNTA(人員名單),MATCH(B6,人員名單,0)),0),"")
找前一天最後一位名單,顯示前一位名單的下一位
(公式已有判定,如果名單是最後一位,就從第1位開始~就是那個if公式)
D6、F5、F6同上方式,就把對應的查找資料,改為該欄的前一個格式位址
5、輸入第二行區域的公式
在B8輸入如下
=IFNA(OFFSET(名單資料!$B$1,IF(MATCH(F6,人員名單,0)>=COUNTA(人員名單),MATCH(F6,人員名單,0)-COUNTA(人員名單),MATCH(F6,人員名單,0)),0),"")
此為找前一行區的最後一個,在後面的部分,就可以用「複製 貼上」,不需要一格格輸公式了
6、設定好要排的資料
如果要排3個月的輪值表,就將第二行的「日期、輪值資料」複製貼上到下一行,直到你要的日期截止日(例3/1~5/30)
7、
以上就完成了輪值表表格,如果要修改日期,從第1格日期修改就好了
如果要新增、修改名單,就在分頁改人名與加入新人員名子