5.4 監(jiān)考安排表

案例背景
    每逢學(xué)期期末考試前，教務(wù)處要組織全?？紕?wù)安排，完成全校各個班級的監(jiān)考安排表的制作，教師們根據(jù)“監(jiān)考安排表”按指定時間到指定班級監(jiān)考。一般來說，一次考試按時間順序有若干場此考試，同一場次有若干班級。而一名教師可能承擔(dān)若干場監(jiān)考任務(wù)。安排監(jiān)考表的要求是：第一，一名教師不能在同一時間被同時分配到兩個班級監(jiān)考；第二，每名教師的監(jiān)考次數(shù)要盡量均衡、合理。
    本案例以某中學(xué)期末考試為例，應(yīng)用Excel技術(shù)制作監(jiān)考安排表，要求本年級每位教師至少監(jiān)考1場，不能超過3場，監(jiān)考安排具有自檢功能，出現(xiàn)錯誤或不符合監(jiān)考安排規(guī)則時，根據(jù)監(jiān)考表提示重新安排。
關(guān)鍵技術(shù)點(diǎn)
    要實現(xiàn)本案例中的功能，學(xué)員應(yīng)該掌握以下EXCEL技術(shù)點(diǎn)。
    ●基礎(chǔ)知識：數(shù)字的”貨幣“格式，條件格式
    ●函數(shù)應(yīng)用：COUNTIF函數(shù)，OR函數(shù)，SUM函數(shù)，MAX函數(shù)
    ●綜述：邏輯判斷，數(shù)組公式
最終效果展示

5.4.1創(chuàng)建監(jiān)考安排表
Step 1創(chuàng)建工作簿，重名工作表
新建一個Excel工作表，保存為“監(jiān)考安排表.xls”，將工作表”Sheet1”重命名為“監(jiān)考表”，”sheet2” 重命名為”教師名單”，刪除其余工作表。

Step 2輸入監(jiān)考表框架信息
①選中下一個區(qū)域A1:G1，設(shè)置為“合并及居中”輸入標(biāo)題“2008-2009學(xué)年第二學(xué)期高一期末考試監(jiān)考表”。
②在單元格區(qū)域A2:G5輸入考試的日期、上午、下午、時間和考試科目等信息

③在單元格區(qū)域A6:A13輸入高一年級8個班的班級名稱，在單元格區(qū)域A14：A15輸入 “巡視”和“自檢”。

④選中單元格區(qū)域A2:G15，為表格設(shè)置邊框

Step3安排監(jiān)考人員
在單元格區(qū)域B6:G14輸入監(jiān)考教師和巡視人員。

Step4應(yīng)用條件格式區(qū)分監(jiān)考場次
①選中單元格B6，單擊“格式”→“條件格式”，彈出“條件格式”對話框。

②單擊“條件格式”對話框的“條件1“選項框右側(cè)的下箭頭按鈕選擇”公式“，然后在其右側(cè)的公式框中輸入如下公式:
” =OR(SUM((B$6:B$14=B6)*1)>=2,SUM(($B$6:$G$14=B6)*1)=4)*(B6<>"")”

③然后單擊“格式“按鈕，彈出”格式“對話框。在”格式“對話框的”字形“選項框選擇”加粗加斜“，在”顏色“框中選擇紅色。

④切換到圖案選項卡，在”顏色“框中選擇”黃色“。

⑤單擊“確定”按鈕返回條件格式”對話框。

⑥再單擊“條件格式”對話框的“條件2“選項框右側(cè)的下箭頭按鈕選擇”公式“，然后在其右側(cè)的公式框中輸入如下公式:
“=SUM(($B$6:$G$14=B6)*1)=3 “
參照上面步驟，設(shè)置格式為藍(lán)色字體并加粗。

⑦再單擊“條件格式”對話框的“條件3“選項框右側(cè)的下箭頭按鈕選擇”公式“，然后在其右側(cè)的公式框中輸入如下公式:
“=SUM(($B$6:$G$14=B6)*1)=2 “
參照上面步驟，設(shè)置格式為綠色字體。

⑧單擊“確定”按鈕完成單元格B6的條件格式的設(shè)置。

⑨單擊常用工具欄按鈕“格式刷“，光標(biāo)選中單元格區(qū)域B6:G14，將單元格B6的格式傳遞到整個監(jiān)考教師名單區(qū)域B6:G14，從而完成條件格式的設(shè)置。

Step5設(shè)置監(jiān)考“重排“自檢功能
①選中單元格B15，在編輯欄輸入如下數(shù)組公式，按組合鍵確認(rèn)。
“=IF(MAX(COUNTIF(B6:B14,B6:B14))>1,"重復(fù)","ok") “

②選中單元格B15，向右拖曳右下角的填充柄至單元格G15完成公式填充。

Step6重新設(shè)置表格邊框和底紋
①選中單元格區(qū)域A2:G15為表格設(shè)置邊框。

②選中單元格區(qū)域A2:G5，為其設(shè)置底紋

至此“監(jiān)考安排表“初步制作完成，按照預(yù)定的安排監(jiān)考的規(guī)則我們可以發(fā)現(xiàn)，”監(jiān)考安排表“提示我們安排出現(xiàn)錯誤：7月9日下午安排教師“顧菲“同時到”高一3班“和”高一8班“監(jiān)考外語，應(yīng)該予以糾正。另外”監(jiān)考安排表“還提示我們教師” 諸宏健“安排了4次監(jiān)考，違反了預(yù)定的安排監(jiān)考規(guī)則，應(yīng)該適當(dāng)調(diào)整。

5.4.2創(chuàng)建監(jiān)考統(tǒng)計表
Step1輸入監(jiān)考統(tǒng)計表原始信息
①單擊工作表標(biāo)簽“教師名單“，在單元格A1:E1，分別輸入”序號“，”教師姓名“，”監(jiān)考次數(shù)“，”標(biāo)準(zhǔn)“和”監(jiān)考費(fèi)“。

②在單元格B2:B21,陸續(xù)輸入應(yīng)該參加期末考試監(jiān)考的教師姓名，在單元格A2輸入1，單元格A3輸入2，選中單元格區(qū)域A2:A3，雙擊單元格A3右下角的填充柄即可完成序號的填充。

③選中單元格區(qū)域D2:D21，輸入監(jiān)考費(fèi)標(biāo)準(zhǔn)“30“，按組合鍵確認(rèn)完成單元格區(qū)域內(nèi)的數(shù)據(jù)批量輸入。
Step2統(tǒng)計監(jiān)考次數(shù)和監(jiān)考費(fèi)
①在單元格C2輸入如下公式，然后按< Inter>鍵確認(rèn)即可完成第一個教師監(jiān)考次數(shù)的統(tǒng)計。
“=COUNTIF(監(jiān)考表!$B$6:$G$14,B2) “

②選中單元格C2，雙擊單元格C2右下角的填充柄即可完成其他教師監(jiān)考次數(shù)的統(tǒng)計。

③在單元格E2中輸入如下公式，然后按< Inter>鍵確認(rèn)即可完成第一個教師監(jiān)考費(fèi)的統(tǒng)計。
“=C2*D2 “

④選中單元格E2，雙擊單元格E2右下角的填充柄即可完成其他教師監(jiān)考費(fèi)的統(tǒng)計。

⑤在單元格A22輸入“合計“，在單元格E22輸入如下公式，然后按< Inter>鍵確認(rèn)即可完成期末考試監(jiān)考費(fèi)的統(tǒng)計。
“=SUM(E2:E21) “

Step3設(shè)置“監(jiān)考費(fèi)“為”貨幣“格式
①選中單元格區(qū)域E2:E22，按組合鍵彈出“單元格格式“對話框，切換到”數(shù)字“選項卡，在”分類“選項框中選擇”貨幣“，在”貨幣符號“選項框中選擇”￥“。

②單擊“確定”按鈕，即可完成將監(jiān)考費(fèi)“設(shè)置為”貨幣“格式。

至此監(jiān)考次數(shù)和監(jiān)考費(fèi)的統(tǒng)計工作全部完成，因為是動態(tài)的統(tǒng)計，可以根據(jù)統(tǒng)計結(jié)果調(diào)整原先“監(jiān)考安排表”中違反預(yù)先設(shè)定監(jiān)考安排規(guī)則之處，統(tǒng)計結(jié)果顯示原先監(jiān)考安排有如下不合理之處：下面根據(jù)統(tǒng)計結(jié)果重新對監(jiān)考安排表做出如下微調(diào)。
第一，教師“潘艷波”未安排監(jiān)考，貯備安排3次監(jiān)考，首先安排潘艷波替換已經(jīng)安排5此監(jiān)考的劉帥老師的7月8日上午高一6班語文課監(jiān)考，其次替換7月9日下午高一8班排重的顧菲老師，最后再替換已經(jīng)安排4次的諸宏健老師7月10日下午高一4班的政治課監(jiān)考。
第二，安排劉超老師替換已經(jīng)安排5次監(jiān)考的范春玲老師的7月8日上午高一7班語文課監(jiān)考和7月9日下午高一6班外語課監(jiān)考。
第三，安排呂曉辰老師替換已經(jīng)安排5次監(jiān)考的楊新玉老師7月8日上午語文課和7月10上午高一8班化學(xué)課的監(jiān)考。
第四，安排王文靜老師替換劉帥老師7月9日下午高一5班外語課的監(jiān)考。
Step4微調(diào)監(jiān)考安排
①光標(biāo)切換到“教師名單”工作表，選中單元格B21，按組合鍵復(fù)制，單擊工作表標(biāo)簽“監(jiān)考表”按鍵同時選中單元格B11，E13和G9，按組合鍵粘貼。

②光標(biāo)切換到“教師名單”工作表，選中單元格B20，按組合鍵復(fù)制，單擊工作表標(biāo)簽“監(jiān)考表”按鍵同時選中單元格B12和E11，按組合鍵粘貼。
③光標(biāo)切換到“教師名單”工作表，選中單元格B10，按組合鍵復(fù)制，單擊工作表標(biāo)簽“監(jiān)考表”按鍵同時選中單元格C14和F13，按組合鍵粘貼。

④光標(biāo)切換到“教師名單”工作表，選中單元格B2，按組合鍵復(fù)制，單擊工作表標(biāo)簽“監(jiān)考表”按鍵同時選中單元格E10，按組合鍵粘貼。

⑤光標(biāo)切換到“教師名單”工作表，可以看出經(jīng)過調(diào)整后，如圖31所示，所有教師監(jiān)考次數(shù)均在2—3次之間了，至此監(jiān)考那批表制作完成，可以組織老師按此“監(jiān)考安排表”到時進(jìn)考場監(jiān)考或巡視了。

關(guān)鍵知識點(diǎn)解析
案例案例解析
5.4.1節(jié)Step4之②中單元格A6中的條件1的公式為
” =OR(SUM((B$6:B$14=B6)*1)>=2,SUM(($B$6:$G$14=B6)*1)=4)*(B6<>"")”
其中(B$6:B$14=B6)為一含有9個邏輯值TRUE或FALSE的一維數(shù)組，而通過將此以邏輯值為元素的一維數(shù)組乘以“1”后變?yōu)楹?span lang="EN-US">9個“1”或“0”的一維數(shù)組。即(B$6:B$14=B6)*1變成（1，0，0，0，0，0，0，0，0），此時SUM((B$6:B$14=B6)*1)= SUM（1，0，0，0，0，0，0，0，0）=1，
而($B$6:$G$14=B6)為一含有9行6列的多維數(shù)組，SUM(($B$6:$G$14=B6)*1)的輸出結(jié)果是求出整個單元格區(qū)域$B$6:$G$14中等于單元格B6的數(shù)量，
SUM(($B$6:$G$14=B6)*1)=4則是判斷整個單元格區(qū)域$B$6:$G$14中等于單元格B6的數(shù)量是否等于4，綜上單元格A6中條件格式“條件1”的公式OR函數(shù)的第一個判斷是B6:B14中等于B6的值的和是否大于或等于2，OR函數(shù)的第二個判斷單元格區(qū)域$B$6:$G$14中等于單元格B6的數(shù)量是否等于4，如果滿足上面連個條件之一，該單元格的文字就顯示為紅色加粗加斜，背景為黃色。
5.4.1節(jié)Step5中單元格E15中的自檢公式為：
“=IF(MAX(COUNTIF(E6:E14,E6:E14))>1,"重復(fù)","ok")”
這是一個數(shù)組公式，下面以示意表形式解析公式

在原先監(jiān)考安排表中教師“顧菲“同時被安排到”高一3班“和”高一8班“監(jiān)考外語，因此“自檢”公式輸出結(jié)果“重復(fù)”，提示負(fù)責(zé)安排監(jiān)考的工作人員重新安排。