3.10 學(xué)生個人成績查詢

案例背景
    作為學(xué)校中的一名班主任或者科任教師經(jīng)常會遇到學(xué)生來查詢個人成績，在數(shù)據(jù)表字段比較多的情況下，由于數(shù)據(jù)表會比較寬，必須左右拖動水平滾動條才能夠在數(shù)據(jù)表中看到同一記錄的全部內(nèi)容，使得成績查詢十分不便，為便于學(xué)生成績查詢，我們可以將同一記錄的全部內(nèi)容集中一張“學(xué)生個人成績查詢表”中，這樣每個學(xué)生的情況在同一頁面中就一目了然了。
    在1.2節(jié)和1.3節(jié)中我們學(xué)習(xí)了“學(xué)生成績檔案頁”和“學(xué)生入學(xué)情況匯總表”，那么是如何將“學(xué)生入學(xué)情況匯總表”中的個人信息集中到這個學(xué)生的“學(xué)生成績檔案頁”查詢表中的呢？在本節(jié)中主要使用VLOOKUP函數(shù)將分別置于各個工作表中的某個學(xué)生的各種文本信息和圖片信息動態(tài)的集中到“學(xué)生個人成績查詢表”中，從而實現(xiàn)學(xué)生個人信息查詢。
關(guān)鍵技術(shù)點
    要實現(xiàn)本案例中的功能，學(xué)員應(yīng)該掌握以下EXCEL技術(shù)點。
    ●基礎(chǔ)知識    數(shù)據(jù)有效性的下拉選框，
    ●函數(shù)應(yīng)用    VLOOKUP函數(shù) OFFSET函數(shù) MATCH函數(shù)
    ●綜述    定義動態(tài)區(qū)域名稱，插入、編輯Excel控件 ，數(shù)據(jù)有效性來源區(qū)域的設(shè)置與切換 照片的自動更新
最終效果展示

3.10.1班級名單的數(shù)據(jù)有效性動態(tài)來源區(qū)域
    在前面2.3節(jié)中已經(jīng)介紹了使用數(shù)據(jù)有效性技術(shù)為“性別”字段設(shè)置下拉選框來輸入“男”或“女”，在通常情況下，使用數(shù)據(jù)有效性時，只能處理一組數(shù)據(jù)來源，可是在某些場合，數(shù)據(jù)來源卻不止一組，如本案例中數(shù)據(jù)表中涉及三個班的學(xué)生，通常情況下每個班的學(xué)生數(shù)在50名左右，如果以3個班的150多名學(xué)生的姓名作為一組數(shù)據(jù)來源去設(shè)置姓名下拉選框的話，那么在查詢學(xué)生成績時從下拉選框中的150多名學(xué)生的姓名中去選擇某一位學(xué)生那是十分困難的。因此我們以班為單位分組，為數(shù)據(jù)有效性設(shè)置3組數(shù)據(jù)來源，通過使用OFFSET函數(shù)來實現(xiàn)數(shù)據(jù)有效性各組數(shù)據(jù)來源的切換。由于受到單屏可視區(qū)域所限，在這里每個班級僅取8名學(xué)生的信息為例來說明問題。
Step 1創(chuàng)建、保存工作簿
新建一個Excel工作簿，保存為”學(xué)生成績查詢表.xls”。

Step 2插入工作表，重命名工作表
①右鍵單擊工作表標(biāo)簽“Sheet1”，在彈出的列表中選擇“選定全部工作表”。

②單擊菜單“插入”→“工作表”，這時就一次性插入了3張工作表

③將工作表重命名為“查詢表”，“入學(xué)”，“高一上期中”，“高一上期末”，“高一下期中”，“高一下期末”，

Step 3輸入學(xué)生原始信息
①在“入學(xué)”工作表中輸入學(xué)生入學(xué)時的原始信息。

②在“高一上期中”工作表中輸入學(xué)生高一上學(xué)期各科期中考試成績和總分。

③在“高一上期末”工作表中輸入學(xué)生高一上學(xué)期各科期末考試成績和總分。

④在“高一下期中”工作表中輸入學(xué)生高一下學(xué)期各科期中考試成績和總分。

⑤在“高一下期末”工作表中輸入學(xué)生高一下學(xué)期各科期末考試成績和總分。

⑥單擊工作表標(biāo)簽“入學(xué)”，在單元格區(qū)域M1:O1中輸入“一班”，“二班”，“三班”，在單元格區(qū)域M2:O9中輸入每個班的8個學(xué)生的姓名，并為單元格區(qū)域M1：O9設(shè)置邊框。

Step4插入動態(tài)區(qū)域名稱
①光標(biāo)選中M列的列標(biāo)選中M列，然后向右拖動到L列選中M、N、L三列，單擊菜單“插入”→“名稱”→“指定”，彈出“指定名稱”對話框。

②在彈出的“指定名稱”對話框中的“名稱創(chuàng)建于”選項組中單擊“首行”按鈕，然后單擊確定按鈕即可完成名稱的創(chuàng)建。

③單擊M2單元格，按組合鍵，選中單元格區(qū)域M2:M65536，此時名稱框顯示該區(qū)域名稱“一班”。

同樣當(dāng)選中單元格區(qū)域N2:N65536 和L2:L65536時，名稱框分別顯示該區(qū)域名稱“二班”和“三班”。

Step5設(shè)置數(shù)據(jù)有效性
①單擊工作表標(biāo)簽“”查詢表，選中單元格Q1，，單擊菜單“數(shù)據(jù)”→“有效性”，彈出 “數(shù)據(jù)有效性”對話框。

②在“數(shù)據(jù)有效性”對話框的“設(shè)置”選項卡中的“允許”下拉列表框中選擇“序列”，在“來源“框中輸入公式： “ =OFFSET(一班,,$R$1-1) ”。

③切換到“輸入信息“選項卡，在”標(biāo)題“選項框中輸入”選擇學(xué)生姓名“，在”輸入信息“選項框中輸入”請選擇需要查詢成績的學(xué)生姓名“，然后單擊確定按鈕。

3.10.2創(chuàng)建學(xué)生成績查詢表
Step1設(shè)計學(xué)生成績查詢表
在“查詢表”工作表中輸入表頭“學(xué)生成績檔案”以及表格的其他字段信息。

Step2輸入學(xué)生的“入學(xué)“原始信息
①在單元格B3中輸入公式:”=Q1”, 然后按鍵確認。

②在單元格C2輸入公式：“="高一"&VLOOKUP($B$3,入學(xué)!$A$2:$K$25,2,0)&"班"”
然后按鍵確認。

③單擊單元格F3，單擊常用工具欄“合并及居中“撤銷“合并及居中“，然后輸入公式=VLOOKUP($B$3,入學(xué)!$A$2:$K$25,3,0) 然后再按鍵確認，

再選中單元格區(qū)域F3:G3單擊常用工具欄“合并及居中。

④在單元格I3，輸入公式=VLOOKUP($B$3,入學(xué)!$A$2:$K$25,4,0) ，按鍵確認，
在單元格K3，輸入公式=VLOOKUP($B$3,入學(xué)!$A$2:$K$25,5,0) ，按鍵確認。

⑤在單元格C4，E4，G4，I4，K4，M4中分別輸入以下公式：
=VLOOKUP($B$3,入學(xué)!$A$2:$K$25,6,0) ，=VLOOKUP($B$3,入學(xué)!$A$2:$K$25,7,0) ，
=VLOOKUP($B$3,入學(xué)!$A$2:$K$25,8,0) ，=VLOOKUP($B$3,入學(xué)!$A$2:$K$25,9,0) ，
=VLOOKUP($B$3,入學(xué)!$A$2:$K$25,10,0) ，=VLOOKUP($B$3,入學(xué)!$A$2:$K$25,11,0) ，
然后按鍵確認，完成該學(xué)生入學(xué)成績的導(dǎo)入。

Step3輸入語文考試成績
①在單元格B7中輸入以下公式，然后按鍵確認：
“=VLOOKUP($B$3,高一上期中!$C$2:$Q$51,COLUMN(),0) ”
②在單元格B8中輸入以下公式，然后按鍵確認：
“=VLOOKUP($B$3,高一上期末!$C$2:$Q$51,COLUMN(),0) ”
③在單元格B9中輸入以下公式，然后按鍵確認：
“=VLOOKUP($B$3,高一下期中!$C$2:$Q$51,COLUMN(),0) ”
④在單元格B10中輸入以下公式，然后按鍵確認：
“=VLOOKUP($B$3,高一下期末!$C$2:$Q$51,COLUMN(),0) ”

Step4輸入其他學(xué)科考試成績
選中下一個區(qū)域B7:B10，向右拖曳單元格B10右下角的填充柄至O列即可完成其他學(xué)科考試成績的導(dǎo)入，然后修復(fù)表格外邊框。

3.10.3在查詢表中輸入學(xué)生照片
    在Excel中，使用動態(tài)名稱和ActiveX控件，就能夠輕松地實現(xiàn)工作表中的圖片自動更新。下面我們將為“學(xué)生成績檔案”插入照片，使學(xué)生的照片能夠隨著學(xué)生姓名的變化而變化。
Step1插入并重命名工作表
單擊菜單“插入”→“工作表”，插入一張工作表并將其重命名為“照片”。
Step2輸入學(xué)生原始信息
①單擊工作表標(biāo)簽“入學(xué)”，選中單元格區(qū)域A1:D25，按組合鍵復(fù)制，單擊工作表標(biāo)簽“照片”，按組合鍵粘貼。
②在單元格E1中輸入“照片”，并未單元格區(qū)域A1:E25設(shè)置邊框。

Step3調(diào)整行高與列寬
①單擊列標(biāo)E選中E列，單擊菜單“格式”→“列”→“列寬”，在彈出的“列寬”對話框中輸入“13.5”.
②光標(biāo)選中第2行至第25行，單擊菜單“格式”→“行”→“行高”，在彈出的“行高”對話框中輸入“101”。

Step4創(chuàng)建照片庫
①單擊單元格E2，參照1.2節(jié)介紹的插入圖片的方法將第一個學(xué)生的照片插入“照片”工作表。

②右鍵單擊該學(xué)生照片，彈出“設(shè)置圖片格式”對話框，切換到“大小”選項卡，在“大小和轉(zhuǎn)角”選項組的“高度”框中輸入“3.47”厘米，寬度框中輸入“2.96”厘米。

③切換到“屬性”選項卡，在“對象位置”選項組中單擊“大小、位置隨單元格而變”

④單擊確定按鈕，然后用鼠標(biāo)將照片拖放到指定單元格E2。

⑤單擊照片，然后單擊繪圖工具欄的“繪圖”→“自動靠齊”→“對齊網(wǎng)格”。

⑥使用相同方法為其余學(xué)生插入照片

Step5定義名稱
按組合鍵彈出“定義名稱”對話框，在“定義名稱”對話框中在“在當(dāng)前工作簿中的名稱”文本框中輸入“pic”，在“引用位置”文本框中輸入公式：
“=OFFSET(照片!$E$2,MATCH(查詢表!$B$3,照片!$A$2:$A$25,0)-1,0) ”
然后單擊確定按鈕。

Step6插入控件
①切換到“查詢表”工作表，單擊菜單“試圖”→“工具欄”→“控件工具箱”，彈出 “控件工具箱”，在“控件工具箱”。

②在“控件工具箱”的工具欄中單擊“命令按鈕”控件，此時鼠標(biāo)光標(biāo)呈細黑十字狀，單擊準備放置照片的N3單元格左上角，然后向右下方向拖動，畫出一個符合單元格的大小的命令按鈕。

③單擊命令按鈕，把光標(biāo)定位到編輯欄，將原有內(nèi)容：“=EMBED("Forms.CommandButton.1","")”
改為此前定義的名稱“=pic”。這時，第一個學(xué)生的照片就顯示出來了。

Step7調(diào)整命令按鈕的大小
為了讓照片的大小與單元格大小匹配，可以調(diào)整命令按鈕的大小，方法同前

    至此學(xué)生成績查詢表的設(shè)計工作全部完成，通過單元格Q1的下拉選框選擇不同的學(xué)生，“學(xué)生成績檔案”中就會顯示該學(xué)生的成績記錄和本人照片。
單元3操作練習(xí)
     1.按照3.2節(jié)“學(xué)生座位號碼表“的順序按下圖格式（8行6列），分別面向?qū)W生和面向教師重新安排座位表（也可以您任教的班級學(xué)生為例先設(shè)定學(xué)生座位號碼，然后按8行6列安排座位表），

     2.按照3.2節(jié)“學(xué)生座位號碼表“的順序按下圖格式（6行8列），分別面向?qū)W生和面向教師重新安排座位表

     3.參照3.3節(jié)的案例的方法，將您任教的班級上學(xué)期的各科考試成績匯總起來形成“成績匯總表“。
     4.在練習(xí)3的基礎(chǔ)上，參照3.4節(jié)的案例按如下格式用“添加輔助列“的方法制作“成績條“。

     5. 在練習(xí)3的基礎(chǔ)上，參照3.4節(jié)的拓展案例的“成績條”“函數(shù)嵌套”制作方法制作“成績條“，并在成績條下方增加一行“空白行”，“空白行”行高為“15像素”。

     6.以您任教的班級上學(xué)期期中、期末和總評成績?yōu)闃颖?，參?SPAN lang=EN-US>3.5節(jié)提供的統(tǒng)計方法，統(tǒng)計應(yīng)考人數(shù)、實考人數(shù)、及格人數(shù)、不及格人數(shù)、平均分、最高分、及格率、良好率、優(yōu)秀率和標(biāo)準差。
     7.參照3.6節(jié)介紹的方法，使用COUNTIF函數(shù)統(tǒng)計你任教的班級上學(xué)期期末各科考試成績的分數(shù)段。并作出分數(shù)段統(tǒng)計柱形圖和餅圖圖表。

     8.參照3.7節(jié)介紹的方法，以您任教的班級上學(xué)期期末各科考試成績?yōu)槔谱鲌D表，并應(yīng)用自動篩選技術(shù)實現(xiàn)圖表的交互功能，將每個學(xué)生的成績與班級平均分最高分和最低分集中于同一圖表進行比較。
     9. 參照3.8節(jié)介紹的方法，應(yīng)用“窗體”組合框控件制作具有交互功能的“單科成績—名次組合圖”。
     10.參照3.10節(jié)介紹的創(chuàng)建照片庫的方法，為你任教的班級學(xué)生建立一個照片庫。
     11.參照3.10節(jié)介紹的創(chuàng)建“數(shù)據(jù)有效性”班級學(xué)生姓名下拉選框的方法，班主任將本班學(xué)生分成6-8組，科任教師按照任教班級將學(xué)生分成6-8個班組，利用OFFSET函數(shù)創(chuàng)建具有多組動態(tài)數(shù)據(jù)源區(qū)域的 “數(shù)據(jù)有效性”下拉選框。
     12. 參照3.10節(jié)介紹的方法，利用VLOOKUP函數(shù)創(chuàng)建“學(xué)生個人成績查詢表”。
     13.按照3.9節(jié)介紹的隨機抽取每個學(xué)生考場座位號的方法，以您任教的班級的實際學(xué)生數(shù)為考場容量，為你的學(xué)生隨機抽取考場座位號。