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

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

　　1.建立學(xué)校教職工花名冊模板

　　1.1按圖1建立表頭。

　　圖1 教職工花名冊

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

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

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

　　1.5設(shè)置數(shù)據(jù)區(qū)域的特殊格式。

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

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

　　1.6設(shè)置數(shù)據(jù)有效性

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

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

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

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

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

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

　　1.9輸入公式

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

　　在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))))”。這兩個公式是根據(jù)L5單元格的身份證號碼提取性別和出生日期。15位身份證號碼的第7、8位代表出生年份(兩位數(shù))，第9、10位代表出生月份，第11、12位代表出生日期，第15位代表性別，奇數(shù)為男，偶數(shù)為女。18位身份證號碼的第7、8、9、10位代表出生年份(四位數(shù))，第11、12位代表出生月份，第13、14位代表出生日期，第17位代表性別，奇數(shù)為男，偶數(shù)為女，最后一位是校驗碼。

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

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

　　在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"))”。函數(shù)DATEDIF()是一個老版本的Excel粘貼函數(shù)，從Excel2000及以后的版本中無所查及，但系統(tǒng)一直隱匿可用，此公式的的含義是用第二個參數(shù)減去第一個參數(shù)，第三個參數(shù)是結(jié)果‘差’的單位。第一個參數(shù)是根據(jù)身份證號碼提取的日期。第二個參數(shù)是以從O2單元格中提取的數(shù)字作為“年”，以 “9”作為月，以“1”作為日。因為學(xué)校的學(xué)年初報表包含專任教師的統(tǒng)計數(shù)據(jù)，是以9月1日為界限的。注意：年度末呈報單位的人員花名冊時請將公式中的9 月1日改為12月31日。第三個參數(shù)“y”表明返回的是整年數(shù)。

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

　　1.10復(fù)制公式。拖選H5:W5區(qū)域，在其右下角有一個小“十”字，鼠標(biāo)放在上面會出現(xiàn)一個大“十”字，此時拖動鼠標(biāo)至W54，松開鼠標(biāo)，上述公式被自動填充到應(yīng)設(shè)公式的區(qū)域。

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

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

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

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

　　2.2按表2建好統(tǒng)計表。

　　圖2 專任教師職稱年齡

　　2.3輸入公式

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

　　3.建立專任教師分課程分學(xué)歷模板

　　圖3 專任教師分課程分學(xué)歷

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

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

　　4.1按表4建好表頭。

　　圖4 生日及退休提醒

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

　　4.3輸入公式

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

　　在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)," 準(zhǔn)備祝賀",""))))”。公式的意思是，“今天”如果與出生日期同月同日，則顯示文字“生日”;如果與出生日期相比，是同月少1天或2天，則顯示文字 “準(zhǔn)備祝賀”;否則不顯示。

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

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

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

　　4.4設(shè)置條件格式。為了讓生日和退休的提醒更加醒目，可以設(shè)置條件格式。拖選A4:L53區(qū)域，執(zhí)行“格式→條件格式”命令，在下拉列表中選取【公式】，在框中輸入“=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())”，顏色選淺藍(lán)色。經(jīng)過設(shè)置后，當(dāng)教職工生日的日期與系統(tǒng)當(dāng)前日期相同時，單元格被填充為“粉紅色”;當(dāng)教職工生日的日期比系統(tǒng)當(dāng)前日期提前1天或2天時，單元格被填充為“淺黃色”;當(dāng)教職工生日的月份與系統(tǒng)當(dāng)前月份相同時，單元格被填充為“淺藍(lán)色”。

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