5.10 教師情況統(tǒng)計表（二）

案例背景
    學(xué)校人事干部和相關(guān)管理人員平時就要注意將本校全體教師的各種信息完整的記錄下來，并以數(shù)據(jù)庫的形式加以保存并不斷更新，這樣一來既可以使自己對全校教師的整體情況了如指掌，又可以根據(jù)各級領(lǐng)導(dǎo)的要求隨時匯總出所需報表，如果給您的報表再配加一幅數(shù)據(jù)圖表，則會使得您的報表更加出彩。本例就在5.9節(jié)的基礎(chǔ)上繼續(xù)應(yīng)用Excel的“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”來制作各類不同的統(tǒng)計表和精美的圖表。
關(guān)鍵技術(shù)點
    要實現(xiàn)本案例中的功能，學(xué)員應(yīng)該掌握以下EXCEL技術(shù)點。
    ●基礎(chǔ)知識 選擇性粘貼，負數(shù)顯示為正數(shù)的自定義格式
    ●圖表 條形圖 圖表的編輯
    ●綜述 數(shù)據(jù)透視表和數(shù)據(jù)透視圖
最終效果展示

5.10.1創(chuàng)建教師基礎(chǔ)數(shù)據(jù)表
Step 1新建、保存工作簿，重命名工作表
新建一工作簿，并保存為“教師統(tǒng)計表2.xls”，將“Sheet1”和“Sheet2”重名為“數(shù)據(jù)表”和“報表”，刪除多余工作表。

Step 2復(fù)制工作表
打開5.8節(jié)中“人事管理.xls”工作簿，在“人事表”中選中單元格區(qū)域A1：N58，按組合鍵復(fù)制。
Step 3輸入“教師情況統(tǒng)計表”原始信息
單擊“教師統(tǒng)計表.xls”工作表的“數(shù)據(jù)表”工作表標簽，按組合鍵粘貼。

5.10.2創(chuàng)建教師情況統(tǒng)計表
Step 1創(chuàng)建基礎(chǔ)統(tǒng)計報表
參照5.9.2的操作過成，只是在“布局“對話框中將“學(xué)科”換成“年齡”，生成
教師年齡—性別統(tǒng)計表。

Step 2按“年齡段”統(tǒng)計匯總
①單擊“年齡”字段的任意單元格，單擊右鍵，在彈出的快捷菜單中“組及顯示明細數(shù)據(jù)“→”“組合“命令，打開”組合”對話框。

②在彈出的”組合”對話框的“步長”選項框中數(shù)“5”。

③然后單擊“確定”按鈕即可得到按年齡段分組的匯總統(tǒng)計表。

④選中單元格區(qū)域A4:C12，按組合鍵復(fù)制，切換到“報表”工作表，選中A2單元格按組合鍵粘貼。
Step 3創(chuàng)建數(shù)據(jù)透視圖
單擊“數(shù)據(jù)透視表“工具欄中的圖標向?qū)О粹o，自動插入一幅基本的數(shù)據(jù)透視圖圖表“Chart1”
Step 4改變數(shù)據(jù)透視圖位置
右鍵單擊圖表空白處，在彈出下拉列表中選擇“位置”，在彈出“圖表位置”對話框中選擇將圖表“作為其中的對象插入”選項，單擊其右側(cè)的下箭頭，在彈出的列表框中選擇“Sheet1”工作表，最后單擊“確定”按鈕。

Step 5隱藏字段按鈕
右鍵單擊圖表中的字段按鈕，在彈出的下拉列表中選擇“隱藏數(shù)據(jù)透視圖字段按鈕“，即可將圖表中的字段按鈕隱藏，從而擴大圖表區(qū)可視區(qū)域。

Step 6添加行字段

Step 7
從“數(shù)據(jù)透視表字段列表”框中將“學(xué)科”拖到“行字段”區(qū)域即可得到教師學(xué)科年齡-性別統(tǒng)計匯總表，當(dāng)然這時的圖表的效果就差多了。

5.10.3繪制教師年齡—性別分布條形圖
如圖8所示的教師年齡分布圖將男女教師的數(shù)據(jù)繪制成柱形圖，看起來不太直觀。我們可以利用數(shù)據(jù)透視表的數(shù)據(jù)制作一個條形圖，其左邊是男教師的人數(shù)條形，右邊是女教師的人數(shù)條形，這樣看起來全校的教師年齡結(jié)構(gòu)就一目了然了。
Step1改變男教師人數(shù)的數(shù)值為負數(shù)
單擊工作表標簽“報表”，在任一空單元格如F3輸入“-1”，按組合鍵復(fù)制，然后選中單元格區(qū)域B3：B9，單擊菜單“編輯”→“選擇性粘貼”，在彈出的“選擇性粘貼”對話框的“運算”選項組中選擇“乘”

然后單擊“確定”按鈕即可將男教師人數(shù)數(shù)據(jù)區(qū)的數(shù)值轉(zhuǎn)換成負數(shù)。

Step2為男教師人數(shù)數(shù)據(jù)區(qū)設(shè)置自定義格式
①選中單元格區(qū)域B3：B9，按快捷鍵彈出“單元格格式”對話框，切換到數(shù)字選項卡，在“分類”列表框中選擇“自定義”選項，在“類型”文本框中輸入” 0;0;@”,

然后單擊“確定”按鈕即可將單元格區(qū)域B3：B9的負數(shù)以正數(shù)形式顯示（如果是0則不顯示），但是單元格區(qū)域內(nèi)的數(shù)字仍然是負數(shù)（在編輯欄中可見單元格B3的值為“-3“）。

Step3插入圖表
①單擊菜單“插入“→”圖表“彈出 “圖表向?qū)?SPAN lang=EN-US>-4步驟之1-圖表類型”對話框，在選項卡“標準類型“中的”圖表類型“選項框中選擇”條形圖“，在”子圖表類型 “選項框中選擇”蔟狀條形圖“。

②單擊 “下一步“按鈕，彈出 “圖表向?qū)?SPAN lang=EN-US>-4步驟之2-圖表數(shù)據(jù)源”對話框，單擊“數(shù)據(jù)區(qū)域“右側(cè)的按鈕，用書選取單元格區(qū)域” $A$2：$C$9 “。

③單擊 “下一步“按鈕，彈出 “圖表向?qū)?SPAN lang=EN-US>-4步驟之3-圖表選項” 對話框，在“標題“選項卡的”圖表標題“框中輸入”教師年齡—性別分布圖“；在”分類（x）軸“框中輸入”年齡段“；在”數(shù)值（y）軸“框中輸入”教師人數(shù)“。

④單擊 “下一步“按鈕，彈出 “圖表向?qū)?SPAN lang=EN-US>-4步驟之4-圖表位置” 對話框，在“將圖表“選項框中選擇”作為其中的對象插入“。

⑤單擊”完成“按鈕即可初步得到圖表

Step4修飾美化統(tǒng)計表
①選中單元格區(qū)域A1:C1單擊常用工具欄“合并及居中“，輸入標題”教師年齡—性別統(tǒng)計表“，設(shè)置字體加粗。
②為表格設(shè)置邊框，為字段區(qū)域設(shè)置底紋。

5.10.4編輯條形圖圖表
Step1設(shè)置圖表標題格式
右鍵單擊圖表標題彈出“圖表標題格式”對話框，切換到“字體”選項卡
在“字號”選項框中輸入“9”，然后單擊確定按鈕。

Step2設(shè)置分類軸標題格式
①右鍵單擊分類軸標題彈出“坐標軸標題格式”對話框，切換到“字體”選項卡，在“字號”選項框中輸入“9”。
②再切換到“對齊”選項卡，在“度”選項框中輸入“0”，

③然后單擊“確定”按鈕。

Step3移動分類軸標題位置
左鍵單擊選中分類軸標題框，將其直接拖動到圖標左上角空白處合適的位置。

Step4清除數(shù)值軸標題和圖例
分別用右鍵單擊選中數(shù)值軸標題和圖例，在彈出的列表框中選擇清除。

Step5設(shè)置坐標軸格式
①鼠標指向圖表底部的數(shù)值軸，當(dāng)出現(xiàn)“數(shù)值軸”字樣時右鍵單擊，在彈出的列表框中選擇“坐標軸格式”，彈出“坐標軸格式”對話框。

②切換到“刻度”選項卡，在“主要刻度單位”選項框中輸入“4”。

③切換到“字體”選項卡，在“字號”選項框中輸入“9”，然后單擊確定按鈕。

④鼠標指向圖表中間的分類軸（縱軸），當(dāng)出現(xiàn)“分類軸”字樣時右鍵單擊，在彈出的列表框中選擇“坐標軸格式”，彈出“坐標軸格式”對話框。
⑤在“圖案”選項卡，的“刻度線標簽”選項組中單擊“圖外”按鈕。

⑥然后切換到“字體”選項卡，在“字號”選項框中輸入“9”，然后單擊“確定“按鈕。

Step6設(shè)置數(shù)據(jù)系列格式
①鼠標指向圖表中任意數(shù)據(jù)系列（任意年齡段中的人數(shù)條形），

當(dāng)出現(xiàn)“系列……引用……”標志時，右鍵單擊，在彈出的下拉列表中選擇“數(shù)據(jù)系列格式”，彈出“數(shù)據(jù)系列格式”對話框。
②單擊圖案選項卡中的“陰影”按鈕，然后單擊“填充效果”按鈕，彈出“填充效果”對話框，在“漸變”選項卡中選擇“單色”，顏色保留原來的紫色，“底紋樣式”選擇“水平”，并向右拖動“深（k） 淺”選項框中的滑動按鈕至適當(dāng)位置。

③單擊確定按鈕返回“數(shù)據(jù)系列格式”對話框，切換到“數(shù)據(jù)標志”選項卡，在“數(shù)據(jù)標簽包括”選項組中勾選“值”選項。

④切換到“選項”選項卡，在“重貼比例”選項框中輸入“100”；在“分類間隔”選項框中輸入“0”.

⑤單擊“確定“按鈕，完成圖表右側(cè)女教師數(shù)據(jù)系列格式的設(shè)置。

⑥按照上面的操作過程完成男教師的數(shù)據(jù)系列格式的設(shè)置。

Step7清除網(wǎng)格線
鼠標指向圖表中任意網(wǎng)格線，當(dāng)出現(xiàn)“數(shù)值軸主要網(wǎng)格線”字樣時右鍵單擊，在彈出的列表框中選擇“清除“

Step8調(diào)整“繪圖區(qū)“大小
鼠標單擊圖表中灰色區(qū)域選中“繪圖區(qū)“，將鼠標移到需要調(diào)整的”繪圖區(qū)“四周的任一方塊上，待鼠標指針變成雙向箭頭時拖動，將繪圖區(qū)調(diào)整到合適的大小。

Step9設(shè)置圖表區(qū)格式
①鼠標右鍵單擊圖表區(qū)白色區(qū)域，在彈出的下拉列表中選擇“圖表區(qū)格式”，在彈出“圖表區(qū)格式”對話框中的“圖案”選項卡下單擊“陰影”和“圓角”按鈕，然后單擊 “填充效果”按鈕”。

②彈出“填充效果”對話框，在“漸變”選項卡中選擇“雙色”，在“顏色1”選擇淺黃色顏色，在“顏色2”中選擇海綠色，在“底紋樣式”選擇“斜上”。

③單擊確定按鈕返回“數(shù)據(jù)系列格式”對話框，然后切換到“字體”選項卡，在“字號”選項框中輸入“9”，然后再切換到“屬性”選項卡，在“對象位置”選項組中單擊“大小、位置均固定”按鈕。

④單擊確定按鈕，完成圖表區(qū)格式的設(shè)置

Step10設(shè)置繪圖區(qū)格式
①鼠標右鍵單擊繪圖區(qū)灰色區(qū)域，在彈出的下拉列表中選擇“繪圖區(qū)格式”，在彈出的“繪圖區(qū)格式”對話框中單擊“填充效果”按鈕”，
②彈出“填充效果”對話框，在“漸變”選項卡中選擇“單色”，在“顏色1”選擇淺青綠色，在“底紋樣式”選擇“水平”，然后單擊確定按鈕，即可完成繪圖區(qū)格式的設(shè)置。

    至此教師年齡—性別分布條形圖圖表的編輯工作全部完成，與基本圖表圖18相比，無論是圖案、色彩還是對比效果都已經(jīng)有了很大的改進，相信學(xué)習(xí)者通過模仿上述操作一定有了一點成就感了吧，根據(jù)每個人對色彩的不同感覺，大家還能繪制出更加美觀的圖表。

單元5操作練習(xí)
1.以您任教的學(xué)科的全年級各班期中和期末考試成績?yōu)槔瑓⒄?SPAN lang=EN-US>5.1節(jié)介紹的教師教學(xué)效果評價方法,從下面兩種角度進行模擬評價。
1.1按下面格式從考試名次變化評價教師的教學(xué)效果

1.2按下面格式從兩次考試平均分的標準分差評價教師的教學(xué)效果.

2.以本年級上學(xué)期的課程安排表為例按照下面的格式制作一張課程安排表。

3.參照5.3節(jié)介紹的教師“教學(xué)任務(wù)書”制作方法，以練習(xí)2的“課程安排表”為數(shù)據(jù)表，參照下面任務(wù)書的格式制作本年級的教師“教學(xué)任務(wù)書”。

4按照5.4節(jié)案例介紹的制作監(jiān)考安排表的方法，以本年級教師為例，模擬安排監(jiān)考表，具體監(jiān)考安排規(guī)則為：
本年級每位教師至少監(jiān)考1場；
每位教師監(jiān)考場次不得超過3場；
通過設(shè)置條件格式將滿足以下條件的教師所在單元格中設(shè)置成下圖樣式；并設(shè)置重排檢查功能。 ①條件1：監(jiān)考4次或重排，文字顏色“粉紅”加粗加斜，背景“灰色”。
②條件2：監(jiān)考3次，文字顏色“藍色”加粗。
③條件3：監(jiān)考2次，文字顏色“綠色”。

5.參照5.4節(jié)案例介紹的制作“教師監(jiān)考場次和監(jiān)考費統(tǒng)計表”的方法，按下面統(tǒng)計表樣式，以練習(xí)4的“監(jiān)考安排表”為數(shù)據(jù)表，使用COUNTIF函數(shù)制作“教師監(jiān)考場次和監(jiān)考費統(tǒng)計表”。

6.參照下面成績表的樣式，以您任教的班級近期考試成績?yōu)槔M行高級篩選，具體高級篩選條件為：
6.1語文和外語成績均不低于90或數(shù)學(xué)成績不低于85；
6.2物理成績或化學(xué)成績不及格；
6.3數(shù)學(xué)成績高于全班數(shù)學(xué)平均分*。

7.以練習(xí)2“課程安排表”為數(shù)據(jù)表，使用高級篩選的“篩選不重復(fù)記錄”功能，篩選出一份本年級不重復(fù)的教師名單。

8.參照5.8節(jié)案例中的”人事管理表”的制作方法，將本年級教師的基本情況制作一張人事管理表,其中：
① “年齡”列區(qū)域的條件格式的公式改為：
“=OR(AND(C7="男",E7>=49),AND(C7="女",E7>=44))”，滿足條件的格式為：
文字為宋體14號加粗加斜，海綠色背景。
② “任現(xiàn)職稱年限”列區(qū)域的條件格式的公式不變，滿足條件的格式為：文字為宋體14號加粗，淡紫色背景。
③在“獎項”列插入“批注”并編輯“批注”。

9.以練習(xí)8的“人事管理表”為數(shù)據(jù)表，創(chuàng)建數(shù)據(jù)透視表統(tǒng)計本年級教師情況的情況。
9.1學(xué)歷—性別統(tǒng)計表，數(shù)據(jù)透視圖
9.2年齡—學(xué)科統(tǒng)計表，數(shù)據(jù)透視圖
9.3職稱—學(xué)歷統(tǒng)計表，數(shù)據(jù)透視圖
9.4學(xué)科、年齡—性別統(tǒng)計表
10.參照5.10節(jié)案例中，“教師年齡性別分布圖”的制作方法，按照下表、下圖樣式，以練習(xí)8的“人事管理表”為數(shù)據(jù)表，制作一張 “本年級教師年齡性別分布圖”。

全部完。