打造學校人事年報和人性化提醒模板

　　學校人事年報是學校辦公室每年的常規工作，手工統計繁瑣飛逝且容易出錯。每逢有教職工過生日、退休之時，送出溫馨的祝賀或提醒，多一些人情味，增加些凝聚力，且不很好?如能借助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位身份證號碼”“身份證號碼錯誤”“請檢查其唯一性和位數!”。【輸入法模式】選取【關閉】。其中，“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”是表示工齡是兩頭算，即算虛年不算實年