5.8 學(xué)校人事管理表

案例背景
    學(xué)校的領(lǐng)導(dǎo)、人事干部和相關(guān)管理人員，要隨時(shí)關(guān)注本校教師隊(duì)伍的變化情況，比如一年內(nèi)哪個(gè)學(xué)科的那些教師面臨退休，該學(xué)科現(xiàn)有的人員結(jié)構(gòu)情況是怎樣的？是否需要補(bǔ)充新教師？又如那些教師一年內(nèi)已經(jīng)具備申報(bào)高一級(jí)技術(shù)職稱的條件；再如哪些教師在哪年獲得過(guò)何種獎(jiǎng)項(xiàng)，是業(yè)務(wù)類獎(jiǎng)項(xiàng)還是綜合類獎(jiǎng)項(xiàng)，等等，以前這些工作主要靠紙面記錄不便于查詢，現(xiàn)在通過(guò)使用Excel技術(shù)可以很方便的實(shí)現(xiàn)類似上述工作的記錄和查詢。
關(guān)鍵技術(shù)點(diǎn)
    要實(shí)現(xiàn)本案例中的功能，學(xué)員應(yīng)該掌握以下EXCEL技術(shù)點(diǎn)。
    ●基礎(chǔ)知識(shí) 條件格式，插入批注，編輯批注 ，
    ●函數(shù)應(yīng)用 時(shí)間函數(shù) TODAY ，
    ●函數(shù)應(yīng)用 時(shí)間函數(shù) Y EAR函數(shù)，MONTH函數(shù)，IF函數(shù) ，AND函數(shù)，OR函數(shù)
    ●綜述 邏輯函數(shù)IF和日期函數(shù)Y EAR函數(shù)，MONTH函數(shù)的嵌套。AND函數(shù)，OR函數(shù)的嵌套
最終效果展示

5.8.1創(chuàng)建學(xué)校人事管理表
Step 1新建、保存工作簿，重命名工作表
新建一工作簿，并保存為“人事管理.xls”，將“Sheet1”重名為“人事表”

Step 2復(fù)制工作表
打開5.6節(jié) “學(xué)科帶頭人初選表.xls” 工作簿，選中”市級(jí)“工作表的單元格區(qū)域A1:G58，按組合鍵復(fù)制。
Step 3輸入教師原始信息
①單擊“人事管理.xls”工作表的“人事表”工作表標(biāo)簽，按組合鍵粘貼。

②單擊E列列標(biāo)選中該列，單擊菜單“插入“→“列”，插入新的一列，在單元格E1中輸入”年齡“。

③單擊并拖動(dòng)光標(biāo)選中G列和H列，單擊菜單“插入“→“列”，插入新的兩列，在單元格G1中輸入” 任現(xiàn)職稱時(shí)間“，在單元格H1中輸入” 任現(xiàn)職稱年限“。

④在單元格區(qū)域K1:N1中輸入字段名“從教日期“，”教齡“，”單項(xiàng)獎(jiǎng)“和”綜合獎(jiǎng)“，選中單元格區(qū)域A1:N58為表格設(shè)置邊框。

⑤在單元格區(qū)域G2:G58輸入每個(gè)教師的“任現(xiàn)職稱時(shí)間 “。

⑥在單元格區(qū)域K2:K58輸入每個(gè)教師的“從教日期“

Step4統(tǒng)計(jì)教師的“年齡“
①選中單元格E2，在編輯欄中輸入以下公式，然后按鍵確認(rèn)。
“=IF(MONTH(TODAY())>=MONTH(D2),YEAR(TODAY())-YEAR(D2),YEAR(TODAY())-YEAR(D2)-1) “

②選中單元格E2，雙擊單元格E2右下角的填充柄，即可完成其余教師年齡的統(tǒng)計(jì)。

Step5統(tǒng)計(jì)教師的“任現(xiàn)職稱年限“
①選中單元格H2，在編輯欄中輸入以下公式，然后按鍵確認(rèn)。
“=IF(MONTH(TODAY())>=MONTH(G2),YEAR(TODAY())-YEAR(G2),YEAR(TODAY())-YEAR(G2)-1) “

②選中單元格H2，雙擊單元格H2右下角的填充柄，即可完成其余教師“任現(xiàn)職稱年限“的統(tǒng)計(jì)。

Step5統(tǒng)計(jì)教師的“教齡“
①選中單元格L2，在編輯欄中輸入以下公式，然后按鍵確認(rèn)。
“=IF(MONTH(TODAY())>=MONTH(K2),YEAR(TODAY())-YEAR(K2),YEAR(TODAY())-YEAR(K2)-1) “

②選中單元格L2，雙擊單元格L2右下角的填充柄，即可完成其余教師“教齡“的統(tǒng)計(jì)。

Step6插入批注
    獲獎(jiǎng)情況一般只記載本聘期5年內(nèi)的獲獎(jiǎng)情況，收到可視區(qū)域的限制，僅以前三名教師的獲獎(jiǎng)情況為例，說(shuō)明如何統(tǒng)計(jì)。
①在單元格N2和N3輸入“1”，在單元格M4輸入“2”，表明前兩位教師各獲得綜合獎(jiǎng)項(xiàng)1次，第三位教師獲得單項(xiàng)獎(jiǎng)兩次，下面通過(guò)插入批注說(shuō)明獲獎(jiǎng)情況
②選中單元格N2，右鍵單擊單元格N2從彈出的下拉列表中選擇插入批注。

③在彈出的批注文本框中輸入說(shuō)明信息“06年局級(jí)先進(jìn)個(gè)人”。

④在單元格N3中輸入說(shuō)明信息“07年校級(jí)先進(jìn)個(gè)人”.在單元格M4輸入說(shuō)明信息“06年教師基本功大賽一等獎(jiǎng)，08年青年教師大競(jìng)賽二等獎(jiǎng)。”

Step7編輯批注
①選中單元格M4，右鍵單擊單元格M4，從彈出的下拉列表中選擇“編輯批注”
彈出批注文本框，再右鍵單擊批注框從彈出的的下拉列表中選擇“編輯文字”，分別注明兩次獲獎(jiǎng)的等級(jí)“06年教師基本功大賽一等獎(jiǎng)（區(qū)級(jí)）”和“08年青年教師大競(jìng)賽二等獎(jiǎng)（市級(jí)）。”

②再右鍵單擊批注框從彈出的的下拉列表中選擇“設(shè)置批注格式”，從彈出的設(shè)置批注格式”對(duì)話框中的“字號(hào)”框選擇“12”。

③切換到“大小”選項(xiàng)卡，在“大小和轉(zhuǎn)角”選項(xiàng)框的“高度”框中輸入“4”厘米，在“寬度”框中輸入“6” 厘米。

④單擊“確定”按鈕，完成批注的格式設(shè)置。

5.8.2統(tǒng)計(jì)面臨退休教師和晉升職稱教師
    按照規(guī)定男教師滿60周歲，女教師滿55周歲當(dāng)月辦理退休手續(xù)。任現(xiàn)職稱年限滿5年可以申報(bào)高一級(jí)技術(shù)職稱。下面通過(guò)設(shè)置條件格式來(lái)提示人事干部或負(fù)責(zé)此項(xiàng)工作的相關(guān)人員。
Step1為“年齡“區(qū)域設(shè)置條件格式
①選中單元格E2，單擊菜單“格式”→“條件格式”彈出“條件格式”對(duì)話框。
②在條件格式”對(duì)話框的“條件1”選項(xiàng)框中選擇“公式”，在其右面的公式框中輸入以下公式：
“=OR(AND(C2="男",E2>=59),AND(C2="女",E2>=54)) ”
然后單擊格式按鈕彈出“單元格格式”對(duì)話框，選擇“紅色”字體并加斜加粗，”淺綠色”背景，單擊“確定”按鈕返回條件格式”對(duì)話框。

③再單擊“確定”按鈕，即可完成單元格E2條件格式的設(shè)置。
④選中單元格E2，單擊常用工具欄“格式刷”，選中單元格區(qū)域E2:E58即可完成格式的復(fù)制。

Step2為“任現(xiàn)職稱時(shí)間“區(qū)域設(shè)置條件格式
①選中單元格H2，單擊菜單“格式”→“條件格式”彈出“條件格式”對(duì)話框。
②在條件格式”對(duì)話框的“條件1”選項(xiàng)框中選擇“公式”，在其右面的公式框中輸入以下公式：
“=AND((F2<>"高級(jí)"),H2>=4) ”
然后單擊格式按鈕彈出“單元格格式”對(duì)話框，選擇“紅色”字體并加粗，”黃色”背景單擊確定按鈕返回條件格式”對(duì)話框。

③再單擊“確定”按鈕，即可完成單元格E2條件格式的設(shè)置。
④選中單元格H2，單擊常用工具欄“格式刷”，選中單元格區(qū)域H3:H58即可完成格式的復(fù)制。

    管理表中通過(guò)文字的顏色和背景對(duì)面臨退休和符合晉升職稱年限條件的教師加以突出顯示，這樣可提提醒人事干部注意，以便統(tǒng)籌安排學(xué)校工作職稱申報(bào)工作。
關(guān)鍵知識(shí)點(diǎn)講解
1.TODAY函數(shù)
函數(shù)名稱：TODAY
主要功能：返回當(dāng)前日期的序列號(hào)。序列號(hào)是 Microsoft Excel 日期和時(shí)間計(jì)算使用的日期-時(shí)間代碼。如果在輸入函數(shù)前，單元格的格式為“常規(guī)”，則結(jié)果將設(shè)為日期格式。
使用格式：TODAY( )
函數(shù)說(shuō)明
Microsoft Excel 可將日期存儲(chǔ)為可用于計(jì)算的序列號(hào)。默認(rèn)情況下，1900 年 1 月 1 日的序列號(hào)是 1 而 2008 年 1 月 1 日的序列號(hào)是 39448，這是因?yàn)樗?SPAN lang=EN-US> 1900 年 1 月 1 日有 39448 天。Microsoft Excel for the Macintosh 使用另外一個(gè)默認(rèn)日期系統(tǒng)。
2.AND函數(shù)
函數(shù)名稱：AND
主要功能：所有參數(shù)的邏輯值為真時(shí)，返回 TRUE；只要一個(gè)參數(shù)的邏輯值為假，即返回 FALSE。
使用格式：AND(logical1,logical2, ...)
參數(shù)說(shuō)明：
Logical1, logical2, ... 表示待檢測(cè)的 1 到 30 個(gè)條件值，各條件值可為 TRUE 或 FALSE。
函數(shù)說(shuō)明
    ●參數(shù)必須是邏輯值 TRUE 或 FALSE, 或者包含邏輯值的數(shù)組或引用。
    ●如果數(shù)組或引用參數(shù)中包含文本或空白單元格，則這些值將被忽略。
    ●如果指定的單元格區(qū)域內(nèi)包括非邏輯值，則 AND 將返回錯(cuò)誤值 #VALUE!。

案例公式解析：
    Step4之①單元格E2中的公式為：
    =IF(MONTH(TODAY())>=MONTH(D2),YEAR(TODAY())-YEAR(D2),YEAR(TODAY())-YEAR(D2)-1) “
    其中MONTH(D2)為MONTH(1953-12-12)返回“12”，YEAR(D2)為YEAR(1953-12-12)返回“1953”，MONTH(TODAY())返回當(dāng)月的月份“6”，YEAR(TODAY())返回當(dāng)年的年份“2009”，公式可化簡(jiǎn)為：
    =IF(6>=12,2009-1953,2009-1953-1) “
    E2中的公式返回2009-1953-1=55
    5.8.2節(jié)Step1之②單元格E14的條件格式”的“條件1”中公式為
    =OR(AND(C14="男",E14>=59),AND(C14="女",E14>=54)) ”
    其中單元格C14的值為“女”，單元格E14的值為“54”，公式化簡(jiǎn)為
    =OR(false,true) ”
    滿足條件格式”的“條件1”中公式的OR函數(shù)的第2個(gè)條件，最后返回“TRUE“，因此該單元格條件格式起作用，數(shù)字顯示為紅字加斜加粗淺綠色背景。
    Step2之②單元格H18的條件格式”的“條件1”中公式為：
     “=AND((F18<>"高級(jí)"),H18>=4) ”
    其中單元格F18的值為“一級(jí)“，單元格H18的值為“9”，公式化簡(jiǎn)為
     “=AND((TRUE, TRUE) ”
    同時(shí)滿足條件格式”的“條件1”中公式的AND函數(shù)的兩個(gè)條件，所以最后返回“TRUE“。
    因此該單元格條件格式起作用，數(shù)字顯示為紅字加粗黃色背景。