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

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

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

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

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

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

②在彈出的”組合”對(duì)話框的“步長(zhǎng)”選項(xiàng)框中數(shù)“5”。

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

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

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

Step 6添加行字段

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1.2按下面格式從兩次考試平均分的標(biāo)準(zhǔn)分差評(píng)價(jià)教師的教學(xué)效果.

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

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

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

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

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

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

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

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

全部完。