學校人事年報是學校辦公室每年的常規工作，手工統計繁瑣飛逝且容易出錯。每逢有教職工過生日、退休之時，送出溫馨的祝賀或提醒，多一些人情味，增加些凝聚力，且不很好?如能借助wps表格，運用公式和函數建立起一套人事年報和人性化提醒模板，可以一箭雙雕、一勞永逸。

　　準備工作：文件命名為“學校人事年報和人性化提醒模板”;工作表分別命名為“教職工花名冊”“專任教師職稱年齡”“專任教師分課程分學歷”“生日及退休提醒”。

　　1.建立學校教職工花名冊模板

　　1.1按圖1建立表頭。

　　圖1 教職工花名冊

　　1.2設置每頁顯示表頭。依次進入【文件→頁面設置】，在【工作表】的【項端標題行】中輸入“$1:$4”。或者單擊【項端標題行】右側的伸縮按鈕“”，在成績表中拖選表頭，再單擊伸縮按鈕。單擊【確定】，完成設置。

　　1.3填充序號。在A5單元格中輸入“1”，選中A5單元格，依次進入【編輯→.填充→序列】。在對話框中選中【序列產生在“列”】，在終止值中輸入“50”(本例行政管理人員2人，專業技術人員46人，工勤人員2人，共50人)。

　　1.4調整行高列寬。單擊行號和列標的交匯處，選定整個工作表。把鼠標放在行號或列標的交接處，會出現有上下箭頭的圖標“”或左右箭頭的圖標“”，拖動鼠標，按多數行的行高或多數列的列寬設置，松開鼠標鍵。

　　1.5設置數據區域的特殊格式。

　　按住鍵盤上的【Ctrl】鍵，在列標上單擊F、I、N、V、X，選中這5列，依次進入【格式→單元格】，單擊【數字】選項卡，在【分類】中選中“自定義”，在其右側的“類型”框中輸入“yyyy.mm”，單擊【確定】。注意：小數點只能輸入減號代替。

　　拖選L5：L54區域，在“類型”框中輸入“@”。在默認情況下，Excel每個單元格所能顯示的數字為11位，超過11位的數字就會用科學計數法顯示，必須將數字屬性改成文本屬性。注意：必須在輸入號碼之前把格式定好;如果輸好號碼再定格式，顯示還是會不正確。

　　1.6設置數據有效性

　　設置日期區域數據的有效性。拖選F5:F54、I5:I54、V5:V54和X5:X54四個區域，依次進入【數據→有效性】，在【設置】選項卡中的【允許】列表選擇【日期】，在【數據】列表中選擇【大于】在【開始日期】框中輸入“1948-01-01”。在【輸入信息】選項卡中的【標題】框中輸入“請輸入：”，在【輸入信息】框中輸入“6位數日期，中間用“-”連接”。在【出錯警告】選項卡中勾選【輸入無效數據時顯示出錯警告】復選框，在【樣式】列表中選擇【停止】，在【標題】框中輸入“日期錯誤”，在【出錯信息】框中輸入“請重新輸入6位數日期!”。在【輸入法模式】選項卡中選取【關閉(英文模式)】。單擊【確定】。

　　設置C5:C54姓名區域數據的有效性。在【允許】列表選擇中【自定義】，在【公式】框中輸入“=COUNTIF(C:C,C5)=1”。然后在【輸入信息】和【出錯警告】選項卡的相關框中依次輸入“請輸入”“姓名”“姓名重復”“請檢查后重新輸入姓名”等提示信息。【輸入法模式】選取【打開】。公式的設置是為了保證輸入姓名的唯一性。

　　設置身份證號碼區域數據的唯一性和有效性。在【公式】框中輸入 “=AND(COUNTIF(L:L,L5)=1,OR(LEN(L5)=15,LEN(L5)=18))”。然后在【輸入信息】和【出錯警告】選項卡的相關框中依次輸入“請輸入：”“15或18位身份證號碼”“身份證號碼錯誤”“請檢查其唯一性和位數!”?！据斎敕Ｊ健窟x取【關閉】。其中，“COUNTIF(L:L,L5)=1”用于判斷身份證號碼的唯一性。“(LEN(L5)=15,LEN(L)=18))”用于限定輸入的數據必須是 15位或18位。LEN函數是一個表示文本長度的函數，OR、AND函數分別是表示“或”、“和”意思的函數。

　　設置職稱、學歷和任教年級區域數據的有效性。拖選G5:G52和J5:J52區域，在【允許】列表中選擇【序列】，在【來源】中輸入“中高,中一,中二,中三,未評”(中間的標點符號屬英文半角)，勾選【提供下拉箭頭】。以后要輸入數據，單擊單元格時就會在其右側出現一個倒三角標志“”，單擊它，將出現一個下拉列表，可用鼠標選擇。同理設置S5:S54 、Y5:Y54、Z5:Z52、AA5:AA52四個區域，在【來源】中分別輸入“研究生,本科,?？?高中級,高中以下”“入黨,入團”“初中,高中” “政治,語文,數學,物理,化學,生物,地理,歷史,外語,信息技術,體育,音樂,美術,勞動技術,其他,當年不任課”。

　　1.7插入批注。O2單元格的日期數據“(2009年1月1日至2009年12月30日)”與表中的公式有關，不能隨意修改或刪除，需要提醒使用者“此單元格只能更改年份，否則，會引起表格中公式自動計算的錯誤。”

　　1.8設置隔行著色。拖選行號5:54，依次進入【格式→條件格式】。在【條件1】的下拉列框中選擇【公式】，在右側輸入=MOD(ROW(),3)=0。單擊【格式】，在【圖案】選項卡中選擇一種顏色。兩次【確定】，完成設置。隔行著色顯示，便于輸入數據不錯行。其中，函數ROW是返回一個引用的行號，函數MOD是返回兩數相除的余數;行號除以3余數為0，就是“隔兩行著色”的意思。

　　1.9輸入公式

　　在H5單元格中輸入公式“=CONCATENATE(TEXT(F5,"yyyy.mm"),G5)”。其中，“TEXT(F5,"yyyy.mm")”是將F5的數字格式轉換成文本。然后用CONCATENATE函數把F5和G5兩個單元格的文本連接起來。將任職時間和任職資格分成兩列，一是為了方便輸入，二是為了便于分類統計。

　　在K5單元格中輸入公式“=TEXT(I5,"yyyy.mm")&J5”，把I5和J5兩個單元格的文本連接起來。

　　在M5和N5單元格分別輸入公式“=IF(L5=""," ",IF(LEN(L5)=15,IF(MOD(MID(L5,15,1),2)=1,"男"," 女"),IF(MOD(MID(L5,17,1),2)=1,"男","女")));”“=IF(L5=""," ",IF(MID(L5,7,2)="19",DATE(MID(L5,7,4),MID(L5,11,2),MID(L5,13,2)),DATE("19"& amp;MID(L5,7,2),MID(L5,9,2),MID(L5,11,2))))”。這兩個公式是根據L5單元格的身份證號碼提取性別和出生日期。15位身份證號碼的第7、8位代表出生年份(兩位數)，第9、10位代表出生月份，第11、12位代表出生日期，第15位代表性別，奇數為男，偶數為女。18位身份證號碼的第7、8、9、10位代表出生年份(四位數)，第11、12位代表出生月份，第13、14位代表出生日期，第17位代表性別，奇數為男，偶數為女，最后一位是校驗碼。

　　M5單元格中的公式由4個IF函數構成。第三和第四個IF函數是第二個IF函數的參數。這3個IF函數合起來又是第一個IF函數的參數。第一個 IF函數是是根據L5單元格是否為‘空’，決定下一步怎么辦。如果L5單元格為‘空’，則M5單元格也為空，否則，執行第二個IF函數。公式中的 “LEN(L5)=15”是一個邏輯判斷語句，LEN函數提取L5單元格中的字符長度，如果該字符的長度等于15，則執行第三個IF函數，否則就執行第四個IF函數。在第三個IF函數中，MID函數從L5的指定位置(第15位)提取1個字符，而MOD函數將該字符與2相除，獲取兩者的余數。如果余數是1說明條件成立，這時就會在M5單元格中填入“男”，反之則會填入“女”。如果LEN函數提取的L5單元格中的字符長度不等于15，則會執行第四個IF函數。只不過MID函數是從L5的第17位即倒數第2位提取1個字符。

　　N5單元格中的公式有兩個IF函數。第二個IF函數是說如果發現L5單元格字符串的第7個字符串開始的連續2個字符串是‘19’，就會執行第二個參數，即日期函數DATE，否則執行第三個參數。日期函數DATE有3個參數，分別是年、月、日。

　　在O5單元格中輸入公式“=IF(L5=""," ",DATEDIF(IF(LEN(L5)=15,DATE("19"& MID(L5,7,2),MID(L5,9,2),MID(L5,11,2)),IF(LEN(L5)=18,DATE(MID(L5,7,4),MID(L5,11,2),MID(L5,13,2)),"")),DATE((MID($O$2,2,4)),"9","1"),"y"))”。函數DATEDIF()是一個老版本的Excel粘貼函數，從Excel2000及以后的版本中無所查及，但系統一直隱匿可用，此公式的的含義是用第二個參數減去第一個參數，第三個參數是結果‘差’的單位。第一個參數是根據身份證號碼提取的日期。第二個參數是以從O2單元格中提取的數字作為“年”，以 “9”作為月，以“1”作為日。因為學校的學年初報表包含專任教師的統計數據，是以9月1日為界限的。注意：年度末呈報單位的人員花名冊時請將公式中的9 月1日改為12月31日。第三個參數“y”表明返回的是整年數。

　　在W5單元格中輸入公式“=IF(V5=""," ",MID($O$2,2,4)-YEAR(V5)+1)”。IF函數的第三個參數是根據工齡的計算公式(工齡=年-年+1)來設計。“+1”是表示工齡是兩頭算，即算虛年不算實年。

　　1.10復制公式。拖選H5:W5區域，在其右下角有一個小“十”字，鼠標放在上面會出現一個大“十”字，此時拖動鼠標至W54，松開鼠標，上述公式被自動填充到應設公式的區域。

　　1.11設定允許編輯區域。選定整個工作表，依次進入【格式→單元格】，單擊【保護】選項卡，去掉【鎖定】前的對勾“√”。再拖選整個工作表設定了公式的區域，在【鎖定】前打上對勾“√”。依次進入【工具→保護→保護工作表】，在對話框中設定密碼。

　　1.12輸入數據。將需手工輸入的數據輸入表里，注意有些數據要確保前后的一致，如“外語”學科不能輸入“英語”，否則，會造成統計的遺漏。

　　1.13隱藏列。12月份年報時，選定F、G、I、J、L、V、X、Z、AA列，在列標的右擊快捷菜單中選擇【隱藏】。 2.建立專任教師職稱年齡模板

　　2.1定義名稱。在“教職工花名冊”工作表中，執行【插入→名稱→定義】命令，在【在當前工作簿的名稱】文本框中輸入“職稱”，鼠標放在【引用位置】框內，再用鼠標拖選表的G7:G52區域，單擊【添加】按鈕。這樣就將G7:G52區域定義為“職稱”了。接著重復【在當前工作簿的名稱→引用位置】步驟，將M7:M52、O7:O52、P7:P52、S7:S52、Z7:Z52、AA7:AA52幾個區域分別命名為“性別”“年齡”“民族”“學歷” “年級”“學科”。這些名稱將在這個工作簿中起作用，并在下文的公式中得到使用，它們可以簡化公式。

　　2.2按表2建好統計表。

　　圖2 專任教師職稱年齡

　　2.3輸入公式

　　F10：{=SUM((學校人事年報和人性化提醒模板.xls!年級="初中")*(學校人事年報和人性化提醒模板.xls!職稱="中高")* (學校人事年報和人性化提醒模板.xls!年齡<=25))}。這是一個數組公式，是對滿足條件“年級是初中、職稱是中高、年齡是小于等于25歲” 的人計數。其中的大括號是數組公式的標志，不輸入，在輸入或復制粘貼公式后同時按下鍵盤上的【Ctrl+Shift+Enter】組合鍵，將自動產生這個標志。

　　G10：{=SUM((學校人事年報和人性化提醒模板.xls!年級="初中")*(學校人事年報和人性化提醒模板.xls!職稱="中高")*(學校人事年報和人性化提醒模板.xls!年齡<=30))-F10}。

　　H10：{=SUM((學校人事年報和人性化提醒模板.xls!年級="初中")*(學校人事年報和人性化提醒模板.xls!職稱="中高")*(學校人事年報和人性化提醒模板.xls!年齡<=35))-SUM(F10:G10)}。

　　I10：{=SUM((學校人事年報和人性化提醒模板.xls!年級="初中")*(學校人事年報和人性化提醒模板.xls!職稱="中高")*(學校人事年報和人性化提醒模板.xls!年齡<=40))-SUM(F10:H10)}。

　　J10：{=SUM((學校人事年報和人性化提醒模板.xls!年級="初中")*(學校人事年報和人性化提醒模板.xls!職稱="中高")*(學校人事年報和人性化提醒模板.xls!年齡<=45))-SUM(F10:I10) }。

　　K10：{=SUM((學校人事年報和人性化提醒模板.xls!年級="初中")*(學校人事年報和人性化提醒模板.xls!職稱="中高")*(學校人事年報和人性化提醒模板.xls!年齡<=50))-SUM(F10:J10)}。

　　L10：{=SUM((學校人事年報和人性化提醒模板.xls!年級="初中")*(學校人事年報和人性化提醒模板.xls!職稱="中高")*(學校人事年報和人性化提醒模板.xls!年齡<=55))-SUM(F10:K10)}。

　　M10：{=SUM((學校人事年報和人性化提醒模板.xls!年級="初中")*(學校人事年報和人性化提醒模板.xls!職稱="中高")*(學校人事年報和人性化提醒模板.xls!年齡<=60))-SUM(F10:L10)}。

　　N10：{=SUM((學校人事年報和人性化提醒模板.xls!年級="初中")*(學校人事年報和人性化提醒模板.xls!職稱="中高")*(學校人事年報和人性化提醒模板.xls!年齡>=61))}。

　　拖選F10：N10區域，拖動其右下角的填充柄至N14。單擊F11單元格，按【Ctrl+H】組合鍵，在【查找內容】和【替換為】框中分別輸入 “中高”“中一”，單擊9次“替換”，鼠標進入F12單元格。同樣地，將F12:N12、 F13:N13、F14：N14區域公式中的“中”高分別替換為“中二”“中三”“未評”。

　　拖選F10:N10區域，拖動其右下角的填充柄至N8，將F8:N8和F9:N9區域公式中的“職稱="中高"”分別替換為“性別="女"”“民族<>"漢"”。

　　在F7輸入“=SUM(F10:F14)”，將此公式填充到N7。

　　拖選F7:N14區域，復制，在F15單元格【粘貼】。將F15：N22區域公式中的“初中”替換為“高中”。

　　在E7中輸入“{=SUM((學校人事年報和人性化提醒模板.xls!年級="初中")*(學校人事年報和人性化提醒模板.xls!性別="女"))”。

　　在E9中輸入“{={SUM((學校人事年報和人性化提醒模板.xls!年級="初中")*(學校人事年報和人性化提醒模板.xls!民族& lt;>"漢")*(學校人事年報和人性化提醒模板.xls!性別="女"))}”。將公式填充至E14;并將E10:E14區域公式中的“民族& lt;>"漢"”分別替換為“職稱="中高"”“職稱="中一"”“職稱="中二"”“職稱="中三"”“職稱="未評"”。

　　拖選E7:E14區域，將公式復制到E15:E22區域，并將公式中的‘初中”替換成“高中”。

　　在E6中輸入“=SUM(E7,E15)”，并將此公式填充至N6。

　　在D6中輸入“=SUM(F6:N6)”，并將此公式填充至D22。

　　2.4 D6:N22區域不顯示0值。有三種實現的方法。①依次進入【格式→條件格式】，設置為“單元格數值”“等于”“0”。單擊【格式】按鈕，【字體】顏色選擇“白色”(與底色同色)。②執行【工具→選項→視圖】，去掉【零值】前的“對勾”。③在右擊的快捷菜單上單擊【設置單元格格式】，執行【數字→分類→自定義】，在【類型】框中，鍵入“0;-0;;@”(注意鍵入的是英文半角字符)。

　　3.建立專任教師分課程分學歷模板

　　圖3 專任教師分課程分學歷

　　本模板的建立過程與“專任教師職稱學歷模板”大致相同，這里不再贅述。

　　4.建立生日及退休提醒模板

　　4.1按表4建好表頭。

　　圖4 生日及退休提醒

　　4.2引用數據。在B4 、C4、D4、E4、H4中分別輸入“=教職工花名冊!C5”“=教職工花名冊!L5、=教職工花名冊!M5”“=教職工花名冊!N5”“=教職工花名冊!V5”，并填充至適當位置。

　　4.3輸入公式

　　在F2中輸入“=TODAY()”。這個日期將隨電腦系統當前日期而變化。

　　在F4中輸入“=IF(D4=""," ",DATEDIF(E4,TODAY(),"Y"))”。這將計算出教職工自出生至“今”的“周歲”。

　　在G4中輸入 “=IF(D14="","",IF(E14="","",IF(DATE(YEAR(TODAY()),MONTH($E14),DAY($E14))-TODAY()=0," 生日",IF(OR(DATE(YEAR(TODAY()),MONTH($E14),DAY($E14))-TODAY()=1,DATE(YEAR(TODAY()),MONTH($E14),DAY($E14))-TODAY()=2)," 準備祝賀",""))))”。公式的意思是，“今天”如果與出生日期同月同日，則顯示文字“生日”;如果與出生日期相比，是同月少1天或2天，則顯示文字 “準備祝賀”;否則不顯示。

　　在I4中輸入“=IF(E4=""," ",IF(C4="男",DATE(YEAR(E4)+60,MONTH(E4),DAY(E4)),DATE(YEAR(E4)+55,MONTH(E4),DAY(E4))))”，以男60歲、女55歲為標準，計算退休日期。

　　在J4中輸入“=IF(H4=""," ",YEAR(NOW())-YEAR(H4)+1)”，計算工齡。

　　在K4中輸入“=IF(D4=""," ",IF(C4="男",IF(F4=59,"準備退休",IF(F4>=60,"退休"," ")),IF(F4=54,"準備退休",IF(F4>=55,"退休"," "))))”。意思是，如果男的滿60歲，女的滿55歲，就顯示文字“退休”;如果男的滿59歲，女的滿54歲，就顯示文字“準備退休”;否則就不顯示。

　　4.4設置條件格式。為了讓生日和退休的提醒更加醒目，可以設置條件格式。拖選A4:L53區域，執行“格式→條件格式”命令，在下拉列表中選取【公式】，在框中輸入“=TODAY()=DATE(YEAR(TODAY()),MONTH($E11),DAY($E11))”。再單擊【格式】，在【圖案】選項卡中選擇粉紅色。單擊【添加】按鈕，添加【條件2】，仿照上面操作，輸入公式：“=OR(DATE(YEAR(TODAY()),MONTH($E11),DAY($E11))-TODAY()=2, DATE(YEAR(TODAY()),MONTH($E11),DAY($E11))- TODAY()=1)”，顏色選黃色。再添加【條件3】,輸入“=MONTH($E11)=MONTH(TODAY())”，顏色選淺藍色。經過設置后，當教職工生日的日期與系統當前日期相同時，單元格被填充為“粉紅色”;當教職工生日的日期比系統當前日期提前1天或2天時，單元格被填充為“淺黃色”;當教職工生日的月份與系統當前月份相同時，單元格被填充為“淺藍色”。

　　自此，一套多用途的自動化人事模板大功告成了。