4.10 月考成績(jī)對(duì)比圖——交互式圖表（三）

案例背景
    每逢考試結(jié)束，考試成績(jī)登統(tǒng)完畢后，一般情況下學(xué)校要組織召開(kāi)學(xué)生家長(zhǎng)會(huì)，班主任在家長(zhǎng)會(huì)上首先向?qū)W生家長(zhǎng)通報(bào)本班考試的整體情況后，各科任課教師還要分別介紹各門課程的學(xué)習(xí)和考試情況，而學(xué)生家長(zhǎng)除了關(guān)注班級(jí)整體情況外，應(yīng)該更關(guān)心自己的孩子的每門課程考試成績(jī)以及自己孩子的成績(jī)?cè)诎嗉?jí)中的位置，細(xì)心地家長(zhǎng)可能還會(huì)關(guān)心自己的孩子每門課程近期的學(xué)習(xí)狀況和發(fā)展趨勢(shì)。
    在3.7節(jié)中介紹了通過(guò)利用數(shù)據(jù)的“自動(dòng)篩選”隱藏行數(shù)據(jù)的特性與圖表源數(shù)據(jù)聯(lián)動(dòng)的特性，通過(guò)篩選不同的學(xué)生實(shí)現(xiàn)圖表的交互功能，3.8節(jié)通過(guò)使用Excel“窗體”來(lái)選擇代表不同的課程考試成績(jī)的列，切換圖表中的“源數(shù)據(jù)”從而實(shí)現(xiàn)圖表的交互功能。
    本案例以2004年天津市某重點(diǎn)中學(xué)理科實(shí)驗(yàn)班部分學(xué)生高三階段按時(shí)間順序排列的四次月考的語(yǔ)文、數(shù)學(xué)、英語(yǔ)和物理四門課程考試成績(jī)?yōu)槔?，?span lang="EN-US">3.7節(jié)和3.8節(jié)的基礎(chǔ)上進(jìn)一步介紹“月考成績(jī)對(duì)比圖”交互式圖表的制作與操作方法。
關(guān)鍵技術(shù)點(diǎn)
要實(shí)現(xiàn)本案例中的功能，學(xué)員應(yīng)該掌握以下EXCEL技術(shù)點(diǎn)。
    ●基礎(chǔ)知識(shí)    指定名稱，粘貼名稱列表，自定義篩選
    ●圖表應(yīng)用    圖表的編輯，自動(dòng)更新圖表標(biāo)題柱形圖
    ●函數(shù)應(yīng)用    CHOOSE函數(shù)，AVERAGE函數(shù) ，MAX函數(shù)，MIN函數(shù)，ROUND函數(shù)，OFFSET函數(shù)
    ●綜合應(yīng)用    定義含有動(dòng)態(tài)區(qū)域的名稱，EXCEL“窗體”的使用，交互式圖表
最終效果展示

4.10.1創(chuàng)建“月考成績(jī)表”
Step 1創(chuàng)建工作簿、重名工作表
創(chuàng)建工作簿“月考成績(jī)對(duì)比圖.xls”，然后將工作表重命名為“月考”，并刪除多余工作表。
Step 2輸入班級(jí)考試成績(jī)
①在單元格區(qū)域A1:Q24輸入學(xué)生姓名和各次月考各門課程原始成績(jī)。

②在單元格區(qū)域A25:A27分別輸入“平均分”，“最高分”和“最低分”。
③在單元格B25輸入公式：“=ROUND(AVERAGE(B2:B24),1)”，然后按鍵確認(rèn)。
④在單元格B26輸入公式：“=MAX(B2:B24)”，然后按鍵確認(rèn)。
⑤在單元格B27輸入公式：“=MIN(B2:B24)”，然后按鍵確認(rèn)。
⑥選中單元格區(qū)域B25:B27，向右拖曳單元格B27右下角的填充柄至Q列即可完成公式填充。

⑦選中單元格區(qū)域A1：Q27，為表格紅色設(shè)置邊框。

4.10.2創(chuàng)建圖表動(dòng)態(tài)“源數(shù)據(jù)”
    圖表的數(shù)據(jù)系列是以一行數(shù)據(jù)或一列數(shù)據(jù)為源數(shù)據(jù)的，下面通過(guò)創(chuàng)建一組四個(gè)“窗體”的組合框控件，并將四個(gè)“窗體”的組合框控件的“單元格鏈接”設(shè)為同一個(gè)單元格，利用“單元格鏈接“的聯(lián)動(dòng)來(lái)實(shí)現(xiàn)下面圖表中四個(gè)”數(shù)據(jù)系列“——一月考、二月考、三月考和四月考的單科成績(jī)列的整體切換，從而實(shí)現(xiàn)圖表的交互功能。
Step 1創(chuàng)建“窗體”組合框控件的數(shù)據(jù)源區(qū)域
①選中單元格區(qū)域B1:E1，按組合鍵復(fù)制，單擊單元格B30，按組合鍵粘貼。
②選中單元格區(qū)域F1:I1，按組合鍵復(fù)制，單擊單元格B31，按組合鍵粘貼。
③選中單元格區(qū)域J1:M1，按組合鍵復(fù)制，單擊單元格B32，按組合鍵粘貼。
④選中單元格區(qū)域N1:Q1，按組合鍵復(fù)制，單擊單元格B33，按組合鍵粘貼。
⑤選中單元格區(qū)域B30:E33，按組合鍵復(fù)制，單擊菜單“編輯”→“選擇性粘貼”，彈出“選擇性粘貼”對(duì)話框，在“選擇性粘貼”對(duì)話框中勾選“轉(zhuǎn)置”復(fù)選框，然后單擊“確定”按鈕。。
⑥選中單元格區(qū)域B30:E33，為表格重新設(shè)置藍(lán)色邊框。

Step 2創(chuàng)建圖表交互功能按鈕——“窗體”組合框
①單擊菜單“視圖”→“工具欄”→“窗體”，在桌面上調(diào)出“窗體”組合工具欄命令按鈕。
②單擊選中“窗體”組合工具欄中的“組合框”按鈕，選中單元格H30，按照單元格H30大小初步設(shè)置“組合框”按鈕的大小。
③右鍵單擊單元格H30 中的“組合框”按鈕，在彈出的下拉列表中選擇“設(shè)置控件格式”命令，彈出“設(shè)置控件格式”對(duì)話框。
④切換到“控制”選項(xiàng)卡，在“數(shù)據(jù)源區(qū)域”復(fù)選框中輸入：“$B$30:$B$33”，在“單元格鏈接”復(fù)選框中輸入：“$G$29”，在“下拉顯示項(xiàng)數(shù)”復(fù)選框中輸入“4”，勾選“三維陰影“命令按鈕。

⑤然后單擊“確定”按鈕。
⑥右鍵單擊選中單元格H30中的組合框控件，從彈出的列表中選擇“復(fù)制“命令，在單元格H31，H32， H33中分別按組合鍵粘貼。
Step 3修改“組合框”控件數(shù)據(jù)源
①右鍵單擊選中單元格H31中的組合框控件，從彈出的列表中選擇“設(shè)置控件格式”命令，彈出“設(shè)置控件格式”對(duì)話框。
②切換到“控制”選項(xiàng)卡，在“數(shù)據(jù)源區(qū)域”復(fù)選框中將原來(lái)的數(shù)據(jù)區(qū)域：“$B$30:$B$33”修改為“$C$30:$C$33“，然后單擊“確定”按鈕。

③按照上述操作過(guò)程將單元格H32中的組合框控件的數(shù)據(jù)源修改為“$D$30:$D$33“。
④按照上述操作過(guò)程將單元格H33中的組合框控件的數(shù)據(jù)源修改為“$E$30:$E$33。

Step 4定義“名稱“
①選中單元格區(qū)域B1:Q27，單擊菜單“插入”→“名稱“→“指定“，彈出”指定名稱“對(duì)話框。

②在”指定名稱“對(duì)話框中的”名稱創(chuàng)建于“選項(xiàng)框中勾選”首行“命令按鈕。

③單擊確定按鈕，即可完成從語(yǔ)文一月考到物理四月考各成績(jī)列的名稱定義工作。
④單擊菜單“插入”→“名稱“→“定義“，彈出”定義名稱“對(duì)話框，在”當(dāng)前工作簿中的名稱“框中輸入”yiyk“，在”引用位置“框中輸入公式：
” =OFFSET(語(yǔ)文一月考,0,月考!$G$29-1) “。

⑤單擊“添加“按鈕，在”當(dāng)前工作簿中的名稱“框中輸入”eryk“，在”引用位置“框中輸入公式：
” =OFFSET(語(yǔ)文二月考,0,月考!$G$29-1) “。

⑥單擊“添加“按鈕，在”當(dāng)前工作簿中的名稱“框中輸入”sanyk“，在”引用位置“框中輸入公式：
” =OFFSET(語(yǔ)文三月考,0,月考!$G$29-1) “。

⑦單擊“添加“按鈕，在”當(dāng)前工作簿中的名稱“框中輸入”siyk“，在”引用位置“框中輸入公式：
” =OFFSET(語(yǔ)文四月考,0,月考!$G$29-1) “。

Step 5粘貼名稱列表
①選中單元格S2，單擊菜單“插入”→“名稱“→“粘貼“，彈出”粘貼名稱“對(duì)話框，
②單擊“粘貼列表“命令按鈕。

這時(shí)將上面定義的全部20個(gè)名稱都粘貼到單元格區(qū)域S2:T21中以便于用戶查看。

4.10.3創(chuàng)建交互式圖表
①選中單元格G30，單擊菜單“插入”→“圖表”。
②彈出“圖表向?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ì)話框，在 “系列產(chǎn)生在”選項(xiàng)框中選擇“列”。
④切換到“系列“選項(xiàng)卡，單擊”添加“按鈕，添加一個(gè)數(shù)據(jù)系列”系列1“，在名稱框中輸入”系列1“的名稱”一月考“，在”值“框中輸入含有名稱的公式：”=月考!yiyk “。

⑤單擊“分類軸（X）軸標(biāo)志（T）“右側(cè)的拾取按鈕，彈出 “圖表向?qū)?span lang="EN-US">-4步驟之2-圖表數(shù)據(jù)源-分類軸（X）軸標(biāo)志”對(duì)話框，用鼠標(biāo)直接選取單元格區(qū)域A2:A27。

⑥再單擊“拾取“按鈕，彈出“源數(shù)據(jù)“對(duì)話框。

⑦單擊“添加“按鈕，添加數(shù)據(jù)系列”系列2“，然后重復(fù)操作上面步驟：在名稱框中輸入”二月考“，在”值“框中輸入公式：”=月考!eryk “。

⑧單擊“添加“按鈕，添加數(shù)據(jù)系列”系列3“，然后重復(fù)操作上面步驟：在名稱框中輸入”三月考“，在”值“框中輸入公式：”=月考!sanyk “。

⑨單擊“添加“按鈕，添加數(shù)據(jù)系列”系列3“，然后重復(fù)操作上面步驟：在名稱框中輸入”四月考“，在”值“框中輸入公式：”=月考!siyk “。

⑩單擊 “下一步“按鈕，彈出 “圖表向?qū)?span lang="EN-US">-4步驟之3-圖表選項(xiàng)” 對(duì)話框，在“標(biāo)題“選項(xiàng)卡的”圖表標(biāo)題“框中輸入”考試成績(jī)—名次組合圖“；在”分類（x）軸“框中輸入”學(xué)生“；在”數(shù)值（y）軸“框中輸入”分?jǐn)?shù)“。

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

⑿單擊”完成“按鈕即可初步得到圖表”月考成績(jī)圖表“。
拖動(dòng)圖表至第30行以下。H列右側(cè)的相應(yīng)位置，避免后面篩選時(shí)隱藏圖表

4.10.4編輯圖表
    由于數(shù)據(jù)表中有20多名學(xué)生的各科成績(jī)，致使圖表中代表學(xué)生各科成績(jī)的圖形較多，不便于觀察，下面首先通過(guò)自定義篩選來(lái)使得圖表中只顯示某個(gè)學(xué)生和班級(jí)平均分的圖形，然后再編輯圖表，添加數(shù)據(jù)標(biāo)志。
Step 1自定義篩選
①光標(biāo)置于成績(jī)表中任意單元格中，然后單擊菜單“數(shù)據(jù)”→“篩選”→“自動(dòng)篩選”，單擊單元格A1中的下箭頭，在彈出的列表中選擇“自定義”，彈出“自定義自動(dòng)篩選方式”對(duì)話框，在第一個(gè)條件左側(cè)框中選擇“等于”，右側(cè)框中選擇學(xué)生“曹賁”，然后單擊“或”命令按鈕，再在第二個(gè)條件左側(cè)框中選擇“等于”，右側(cè)框中選擇 “平均分”。

②單擊“確定”按鈕，完成自定義篩選，此時(shí)圖表也隨之聯(lián)動(dòng)，這時(shí)圖表只顯示學(xué)生“曹賁”和班級(jí)“平均分”的圖形。

為使圖表取得更明顯的比較效果，下面添加代表分?jǐn)?shù)的數(shù)據(jù)標(biāo)志來(lái)增強(qiáng)圖表的對(duì)比效果。
Step 2添加數(shù)據(jù)標(biāo)志
①單擊圖表區(qū)選中圖表，單擊右鍵從彈出的列表中選擇“圖表選項(xiàng)”，彈出“圖表選項(xiàng)“對(duì)話框。
②切換到 “數(shù)據(jù)標(biāo)志”選項(xiàng)卡，在“數(shù)據(jù)標(biāo)志包括”選項(xiàng)框中勾選“值”命令按鈕。

③然后單擊“確定”按鈕。
Step 3編輯圖表區(qū)
①右鍵單擊圖表區(qū)從彈出的下拉列表框中選擇“圖表區(qū)格式“
②彈出“圖表區(qū)格式“對(duì)話框，在”圖案“選項(xiàng)卡的”邊框“選項(xiàng)框中勾選”陰影“和”圓角“選項(xiàng)，在”區(qū)域“選項(xiàng)框中選擇”茶色“。

③單擊“填充效果“按鈕，彈出” 填充效果“對(duì)話框，在“顏色”復(fù)選框中選擇“雙色”,在”顏色2“選項(xiàng)框中選擇”淺綠“色，在”底紋樣式“選項(xiàng)框中單擊”斜下“按鈕。

④然后單擊“確定“按鈕返回到“圖表區(qū)格式“對(duì)話框，再單擊“確定“按鈕即可完成圖表區(qū)的編輯工作。

Step4編輯圖例
①右鍵單擊圖例，從彈出的列表中選擇“圖例格式”，彈出“圖例格式”對(duì)話框。
②在彈出“圖例格式”對(duì)話框的“區(qū)域“選項(xiàng)組中，單擊選中“無(wú)”命令按鈕。

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

4.10.5切換圖表源數(shù)據(jù)
Step 1 縱向轉(zhuǎn)化圖表源數(shù)據(jù)
①單擊單元格A1中的下箭頭，在彈出的列表中選擇“自定義”，彈出“自定義自動(dòng)篩選方式”對(duì)話框，在第一個(gè)條件左側(cè)框中選擇“等于”，右側(cè)框中選擇學(xué)生“陳曦”，然后單擊“或”命令按鈕，再在第二個(gè)條件左側(cè)框中選擇“等于”，右側(cè)框中選擇 “最高分”。
②單擊“確定”按鈕，完成自定義篩選，圖表也隨之聯(lián)動(dòng)，這時(shí)圖表只顯示學(xué)生“陳曦”和班級(jí)“最高分”的圖形。

Step 2 橫向轉(zhuǎn)換化圖表源數(shù)據(jù)
①單擊單元格G30中的組合框控件箭頭，從下拉選框中選擇“語(yǔ)文一月考”，這時(shí)單元格G29顯示的值是“1”。圖表中顯示的是“語(yǔ)文一月考”，“語(yǔ)文二月考”，“語(yǔ)文三月考”和“語(yǔ)文四月考”的圖形。
②也可在單元格G29直接輸入“2”，這時(shí)圖表源數(shù)據(jù)會(huì)發(fā)生變化，圖表中顯示的是“數(shù)學(xué)一月考”，“數(shù)學(xué)二月考”，“數(shù)學(xué)三月考”和“數(shù)學(xué)四月考”的圖形。

Step2設(shè)置標(biāo)題自動(dòng)更新
①在單元格A29輸入如下公式，然后按鍵確認(rèn)。
“=CHOOSE(G29,"語(yǔ)文","數(shù)學(xué)","英語(yǔ)","物理")&"月考成績(jī)圖表"”
②選中單元格區(qū)域A29:C29，單擊“合并及居中“按鈕，并為該區(qū)域設(shè)置”淺綠色“底紋。
③單擊選中圖表標(biāo)題，在編輯欄輸入公式：“=Sheet1!$A$29”，然后按鍵確認(rèn)即可完成圖表標(biāo)題自動(dòng)更新的設(shè)置。

    這時(shí)圖表標(biāo)題引用的是單元格A29的內(nèi)容，而單元格A29的內(nèi)容是隨著單元格G29的內(nèi)容變化而改變的，從而也就實(shí)現(xiàn)了圖表標(biāo)題的動(dòng)態(tài)變化。
    至此“月考成績(jī)圖表“交互式圖表全部制作完畢。
關(guān)鍵知識(shí)點(diǎn)講解：
案例公式解析
    4.10.2之Step 4之③”中的名稱“引用位置“框中的公式為：
    ” =OFFSET(語(yǔ)文一月考,0,月考!$G$29-1) “。
其中名稱“語(yǔ)文一月考“引用區(qū)域是單元格區(qū)域 “B2:B27”，公式的含義是以B2:B27為起點(diǎn)，行偏移量為”0“，列偏移量為單元格G29中的數(shù)值減去”1“，并且單元格G29中的數(shù)值由”組合框“控件的選擇來(lái)決定。
    4.10.5之Step2之①中公式為：
     “=CHOOSE(G29,"語(yǔ)文","數(shù)學(xué)","英語(yǔ)","物理")&"月考成績(jī)圖表"”
其中單元格G29是可變單元格，它的數(shù)值由”組合框“控件的選擇來(lái)決定。當(dāng)時(shí)G29中的數(shù)值為”2“，因此公式”CHOOSE(G29,"語(yǔ)文","數(shù)學(xué)","英語(yǔ)","物理")“可化簡(jiǎn)為”CHOOSE(2,"語(yǔ)文","數(shù)學(xué)","英語(yǔ)","物理")“，CHOOSE函數(shù)返回第二個(gè)引用區(qū)域”數(shù)學(xué)“，最終公式返回” 數(shù)學(xué)月考成績(jī)圖表“。

單元4操作練習(xí)
1.按照4.1節(jié)介紹的方法，參照下圖格式統(tǒng)計(jì)各科或各班分?jǐn)?shù)段人數(shù)
1.1首先為各科或各班的期中、期末和總評(píng)成績(jī)定義名稱；
1.2用數(shù)組函數(shù)FREQUENCY統(tǒng)計(jì)各科或各班的期中、期末和總評(píng)成績(jī)分?jǐn)?shù)段人數(shù)；

2. 按照4.2節(jié)介紹的方法，參照上圖格式統(tǒng)計(jì)各科或各班分?jǐn)?shù)段人數(shù)
2.1用數(shù)組公式統(tǒng)計(jì)各科或各班的期中、期末和總評(píng)成績(jī)分?jǐn)?shù)段人數(shù)；
2.2根據(jù)統(tǒng)計(jì)結(jié)果，繪制并編輯一幅折線圖圖表圖表。
3. 科任教師以您任教的若干班級(jí)近期期末考試成績(jī)?yōu)閿?shù)據(jù)表、班主任以本班近期各科期末考試成績(jī)?yōu)閿?shù)據(jù)表，按照4.3節(jié)介紹的方法，參照下圖格式用數(shù)據(jù)透視表統(tǒng)計(jì)各科或各班分?jǐn)?shù)段人數(shù)，繪制并編輯一幅數(shù)據(jù)透視圖，圖表類型不限。

4.參照4.4節(jié)給出的學(xué)生成績(jī)由百分制轉(zhuǎn)換成等級(jí)分的的方法，按照下面的表格樣式，以您任教班級(jí)的學(xué)生近期考試為數(shù)據(jù)表，首先自己制定等級(jí)轉(zhuǎn)換標(biāo)準(zhǔn)制成“轉(zhuǎn)換對(duì)照表”，然后將百分制分?jǐn)?shù)轉(zhuǎn)換成等級(jí)分。

5. 參照4.5節(jié)給出的學(xué)生成績(jī)由百分制轉(zhuǎn)換成Z分?jǐn)?shù)和T分?jǐn)?shù)的方法，按照下面的表格樣式，以您任教班級(jí)的學(xué)生近期考試為數(shù)據(jù)表，首先應(yīng)用函數(shù)AVERAGE和STDEVP求出平均分和標(biāo)準(zhǔn)差，然后應(yīng)用函數(shù)STANDARDIZE將百分制分?jǐn)?shù)轉(zhuǎn)換成Z分?jǐn)?shù)，再把Z分?jǐn)?shù)轉(zhuǎn)換成T分?jǐn)?shù)。

6.參照4.6節(jié)介紹的方法，以您任教班級(jí)為例，按照下圖樣式制作考試成績(jī)統(tǒng)計(jì)表，統(tǒng)計(jì)各班中期末考試及格人數(shù)與及格率，優(yōu)秀人數(shù)與優(yōu)秀率，良好人數(shù)和良好率；設(shè)置可變單元格，動(dòng)態(tài)的統(tǒng)計(jì)各班里年級(jí)前若干名和后若干名的人數(shù)和比例。同時(shí)將前若干名和后若干名學(xué)生的成績(jī)使用條件格式在統(tǒng)計(jì)表中動(dòng)態(tài)的標(biāo)示出來(lái)，以使得前、后若干名的成績(jī)能夠在成績(jī)表突出顯示。

7.參照4.7介紹的方法，以您任教的年級(jí)為例設(shè)計(jì)制作考試統(tǒng)計(jì)表，要求能夠動(dòng)態(tài)的統(tǒng)計(jì)各學(xué)科中考試成績(jī)的年級(jí)10名的分?jǐn)?shù)以及獲得該成績(jī)的學(xué)生的姓名班級(jí)和名次。
8.根據(jù)4.8節(jié)介紹的方法，完成以下操作：
8.1按照下面給出的樣式，首先制作各單項(xiàng)考試換算表，

8.2以您的10名學(xué)生為例模擬出各單項(xiàng)成績(jī)，按照下面樣式制作學(xué)生體育課成績(jī)匯總表，其中，“立定跳遠(yuǎn)”、“50米短跑”和“800米中長(zhǎng)跑”的成績(jī)各占學(xué)期總成績(jī)的“20%”，“40%”，“40%”，學(xué)期總成績(jī)不保留小數(shù)，并將匯總后體育課總成績(jī)轉(zhuǎn)換成等級(jí)分。

9以年級(jí)各班或您任教的若干班級(jí)期末考試的客觀題考試成績(jī)?yōu)槔M(jìn)行試卷分析，參照4.9節(jié)介紹的試卷分析方法，按照下表樣式對(duì)客觀題進(jìn)行分題匯總和分類匯總。

10.參照4.10節(jié)介紹的動(dòng)態(tài)圖表的制作方法，選擇你任教的班級(jí)按時(shí)間順序排列的4次考試為例（比如上一年度上下兩個(gè)學(xué)期的期中和期末考試）選擇其中4個(gè)學(xué)科成績(jī)制作具有交互功能的柱形圖圖表。