2.5 考試成績(jī)公布表

    案例背景
    閱卷完畢，考試成績(jī)統(tǒng)計(jì)出來(lái)以后，為了促進(jìn)學(xué)生學(xué)習(xí)的積極性，大多數(shù)學(xué)校會(huì)公布學(xué)生的成績(jī)。某中學(xué)高二年級(jí)需要?jiǎng)?chuàng)建期中考試成績(jī)公布表，要求把各科中不及格的成績(jī)用紅色粗體顯示，按班級(jí)打印排名表送各個(gè)班級(jí)，然后可以選出年級(jí)前50名學(xué)生公布。
    通過(guò)本節(jié)的學(xué)習(xí)，主要應(yīng)掌握自動(dòng)篩選和條件格式的應(yīng)用方法。
    關(guān)鍵技術(shù)點(diǎn)
    要實(shí)現(xiàn)本案例中的功能，學(xué)員應(yīng)該掌握以下Excel技術(shù)點(diǎn) 。
    ●基礎(chǔ)知識(shí)：凍結(jié)窗格、定義名稱、條件格式、篩選下拉菜單、自定義篩選
    ● 函數(shù)應(yīng)用 RANK函數(shù)
    最終成果展示

    2.5.1 創(chuàng)建考試成績(jī)公布表
    在Excel中可以應(yīng)用一些巧妙的方法管理復(fù)雜的工程，可以用名稱來(lái)命名單元格或者區(qū)域，而且可以使用這些名稱進(jìn)行導(dǎo)航和代替公式中的單元格地址，是工作表更容易理解和更新。
    名稱是單元格或者區(qū)域的別名，它代表單元格、單元格區(qū)域、公式或者常量的單詞和字符串。例如用名稱“語(yǔ)文”來(lái)引用區(qū)域“sheet1！C3:C12”，目的是便于理解和使用。在創(chuàng)建比較復(fù)雜的工作簿時(shí)，命名是有非常重要的好處的：使用名稱表明單元格的內(nèi)容比使用單元格地址更清楚明了；在公式或者函數(shù)中使用名稱代替單元格或者區(qū)域的地址，如公式“=AVERAGE（語(yǔ)文）”比公式“=AVERAGE（sheet1！C3：C12）”更容易記憶和書寫；名稱可以用于所有工作表；在工作表中復(fù)制公式時(shí)，使用名稱和使用單元格引用的效果相同。
    默認(rèn)狀態(tài)下，名稱使用的是單元格的絕對(duì)引用，形式為$C$3：$C$12。
Step1 創(chuàng)建工作薄、重命名工作表
參閱1.2.1小節(jié)Step1至Step2創(chuàng)建工作簿“考試成績(jī)公布表.xls”，然后將工作表重命名為“成績(jī)公布表”并刪除多余的工作表。

Step2 輸入表格標(biāo)題
選中A1：J1單元格區(qū)域，設(shè)置格式為“合并及居中”，輸入表格標(biāo)題“期中考試成績(jī)公布表”，然后設(shè)置字形為“加粗”、字體為“黑體”、字號(hào)為“16”。
Step3 輸入表格各個(gè)字段標(biāo)題
在A2：J2單元格區(qū)域分別輸入各個(gè)字段的標(biāo)題名稱，并設(shè)置文本居中顯示，字體為“黑體”，字號(hào)為“12”。

Step4 輸入“班級(jí)”
①選中A3：A42單元格區(qū)域，輸入“1”，按組合鍵確認(rèn)即可完成批量輸入相同的數(shù)據(jù)。
②同樣在A43：A82單元格區(qū)域輸入相同的數(shù)據(jù)“2”。
③在A83：A122單元格區(qū)域輸入相同的數(shù)據(jù)“3”。

Step5 輸入“學(xué)號(hào)”
①選中B3單元格，輸入“8401”；選中B4單元格，輸入“8402”。
②選中B3：B4單元格區(qū)域，將光標(biāo)移到B4單元格的右下角，當(dāng)光標(biāo)變?yōu)?span lang="EN-US">+形狀時(shí)按住鼠標(biāo)左鍵不放向下拖曳填充柄至B122單元格，然后松開(kāi)鼠標(biāo)即可完成內(nèi)容的填充。

Step6 輸入原始數(shù)據(jù)
在C3：G122單元格區(qū)域輸入原始數(shù)據(jù)。

Step7 統(tǒng)計(jì)“總分”
①選中H3單元格，輸入以下公式。=SUM（C3：G3）
②選中H3單元格，移動(dòng)光標(biāo)到該單元格的右下角，當(dāng)光標(biāo)變?yōu)?span lang="EN-US">+形狀時(shí)向下拖曳填充柄至H122單元格，然后松開(kāi)鼠標(biāo)即可完成公式的填充。

Step8 凍結(jié)窗格
    當(dāng)編輯過(guò)長(zhǎng)或過(guò)寬的Excel工作表時(shí)，需要向下或向右滾動(dòng)屏幕，這時(shí)表頭也會(huì)相應(yīng)地滾動(dòng)，而不能在屏幕上顯示出來(lái)。
    單擊第3行的行標(biāo)以選中第3行，然后單擊菜單“窗口”→“凍結(jié)窗格”。

    這時(shí)在第3行的上方就會(huì)插入一條凍結(jié)線，之后拖動(dòng)上下滾動(dòng)條即可查看數(shù)據(jù)的詳細(xì)情況，而表頭行則始終存在。
單擊菜單“窗口”→“取消凍結(jié)窗格”即可取消對(duì)窗格的凍結(jié)。

Step9 給單元格區(qū)域取名
①選擇要命名的H3：H122單元格區(qū)域，單擊菜單“插入”→“名稱”→“定義”。

②在彈出的“定義名稱”對(duì)話框中的“在當(dāng)前工作簿中的名稱”文本框中輸入要定義的名稱，如“zongfen”。
③單擊“添加”按鈕，定義好的名稱就會(huì)顯示在列表框中。此時(shí)H3：H122單元格區(qū)域已經(jīng)定義名稱為“zongfen”，然后單擊“確定”按鈕。

再次選中被命名的單元格區(qū)域時(shí)，名稱框中會(huì)直接顯示所定義的名稱。而如果只是選擇了某一個(gè)單元格，名稱框則不會(huì)顯示區(qū)域名稱。

Step10 統(tǒng)計(jì)“年級(jí)名次”
選中I3單元格，輸入以下公式，然后按鍵確認(rèn)。=RANK（H3，zongfen，0）

Step11 自動(dòng)填充公式
參閱2.1.1小節(jié)Step7，選中I3單元格，移動(dòng)光標(biāo)到該單元格的右下角，當(dāng)光標(biāo)變?yōu)?span lang="EN-US">+形狀時(shí)向下拖曳填充柄至I122單元格，然后松開(kāi)鼠標(biāo)即可完成公式的填充。

Step12 使用“名稱框”定義名稱
使用“名稱框”可以更方便地定義名稱。
①首先選中要命名的H3：H42單元格區(qū)域，然后單擊“編輯欄”左側(cè)的“名稱框”。
②輸入要定義的名稱，如“class1”。
③按鍵確認(rèn)，完成名稱的定義。

④同樣選中H43：H82單元格區(qū)域，定義名稱為“class2”。
⑤選中H83：H122單元格區(qū)域，定義名稱為“class3”。

Step13 統(tǒng)計(jì)“班級(jí)名次”
①選中J3單元格，輸入以下公式，然后按鍵確認(rèn)。=RANK（H3，class1，0）
②移動(dòng)光標(biāo)到J3單元格的右下角，當(dāng)光標(biāo)變?yōu)?span lang="EN-US">+形狀時(shí)向下拖曳填充柄至J42單元格，然后松開(kāi)鼠標(biāo)即可完成公式的填充。
③選中J43單元格，輸入以下公式，然后按鍵確認(rèn)。=RANK（H43，class2，0）
④向下拖曳J43單元格右下角的填充柄至J82單元格完成公式的填充。
⑤選中J83單元格，輸入以下公式，然后按鍵確認(rèn)。=RANK（H83，class3，0）
⑥向下拖曳J83單元格右下角的填充柄至J122單元格完成公式的填充。

Step14 設(shè)置條件格式
為了便于發(fā)現(xiàn)指標(biāo)較低的部分以進(jìn)行改善，可以對(duì)表格數(shù)據(jù)設(shè)置條件格式。本例設(shè)定以不同方式顯示數(shù)值低于60的單元格。
①選中C3：G122單元格區(qū)域，單擊菜單“格式”→“條件格式”彈出“條件格式”對(duì)話框。

②在“條件格式”對(duì)話框的“條件1”文本框中依次設(shè)置：“單元格數(shù)值”、“小于”和“60”，如圖18所示。

③單擊“格式”按鈕彈出“單元格格式”對(duì)話框。
④在“單元格格式”對(duì)話框中切換到“字體”選項(xiàng)卡，在“字形”列表框中選擇“加粗”。單擊“顏色”右側(cè)的下箭頭按鈕，在彈出的“顏色”調(diào)色板中選擇“紅色”。
⑤單擊“確定”按鈕返回“條件格式”對(duì)話框。

⑥單擊“確定”按鈕保存條件格式。

此時(shí)C3：G122單元格區(qū)域即考試成績(jī)區(qū)域就都應(yīng)用了條件格式，凡是小于60的單元格均顯示為粗體紅色，效果如圖21所示。

Step15 調(diào)整表格列寬
參閱1.1.2小節(jié)Step2調(diào)整表格列寬。
Step16 設(shè)置表格邊框
參閱1.2.1 Step19，選中A2：J122單元格區(qū)域，然后設(shè)置表格邊框。

關(guān)鍵知識(shí)點(diǎn)講解
    1.名稱命名的規(guī)則
     ● 名稱可以使任意的字符與數(shù)字組合在一起，但不能以數(shù)字開(kāi)頭，更不能以數(shù)字作為名稱，如7AB。同時(shí)名稱不能與單元格地址相同，如A3。
     ● 如果要以數(shù)字開(kāi)頭，可以在前面加上下劃線，如_7AB。
     ● 不能以字母R、C、r、c作為名稱，因?yàn)?span lang="EN-US">R、C在R1C1引用樣式中表示工作表的行、列。
     ● 名稱中不能包含空格，可以用下劃線或點(diǎn)號(hào)代替。
     ● 不能使用除了下劃線、點(diǎn)號(hào)和反斜線（/）等之外的其他符號(hào)。允許使用問(wèn)號(hào)（？），但不能作為名稱的開(kāi)頭，如Range？可以，但？Range就不可以。
    ● 名稱字符不能超過(guò)255個(gè)。一般情況下名稱應(yīng)該便于記憶且應(yīng)盡量簡(jiǎn)短，否則就違背了定義名稱的初衷。
    ● 名稱中的字母不區(qū)分大小寫。
    2.RANK函數(shù)
    RANK(number,ref,order)
    函數(shù)用途
    返回一個(gè)數(shù)字在數(shù)字列表中的排位。數(shù)字的排位是其大小與列表中其他值的比值（如果列表已排過(guò)序，那么數(shù)字的排位就是它當(dāng)前的位置）。
    參數(shù)說(shuō)明
    number：為需要找到排位的數(shù)字。
    ref：為數(shù)字列表數(shù)組或?qū)?shù)字列表的引用。Ref中的非數(shù)值型參數(shù)將被忽略。
    order：為一個(gè)數(shù)字，指明排位的方式。如果order為0或省略，Excel對(duì)數(shù)字的排位是基于ref為參照降序排列的列表；如果order不為零，Excel對(duì)數(shù)字的排位是基于ref為參照升序排列的列表。
    函數(shù)說(shuō)明
    ● RANK函數(shù)對(duì)重復(fù)數(shù)的排位相同。但重復(fù)數(shù)的存在將影響后續(xù)數(shù)值的排位。例如在一列按升序排列的整數(shù)中，如果整數(shù)10出現(xiàn)了兩次，其排位為5，那么11的排位則為7（沒(méi)有排位為6的數(shù)值）。
    ● 由于某些原因，用戶可能會(huì)考慮重復(fù)數(shù)字的排位定義。在前面的示例中，用戶可能要將整數(shù)10的排位改為5.5.這可通過(guò)將下列修正因素添加到按排位返回的值來(lái)實(shí)現(xiàn)。該修正因素對(duì)于按照升序計(jì)算排位（順序=非零值）或按照降序計(jì)算排位（順序=0或被忽略）的情況都是正確的。
    重復(fù)數(shù)排位的修正因素=[COUNT(ref)+1-RANK(number,ref,0)- RANK(number,ref,1)]/2。
    在下面的示例中，RANK（A2，A1：A5，1）等于3。修正因素是（5+1-2-3）/2=0.5，考慮重復(fù)數(shù)排位的修改排位是3+0.5=3.5。如果數(shù)字僅在ref中出現(xiàn)一次，由于不必調(diào)整RANK，因此修正因素為0。
    函數(shù)簡(jiǎn)單示例

    本例公式說(shuō)明
    本例中的公式為：    =RANK（H3，zongfen，0）
    其各個(gè)參數(shù)值指定RANK函數(shù)計(jì)算H3單元格在H3：H122單元格區(qū)域中按降序排位的位數(shù)。
    2.5.2 篩選數(shù)據(jù)
    利用Excel的篩選功能能夠在一份復(fù)雜的數(shù)據(jù)清單中迅速地查找到滿足條件的數(shù)據(jù)資料。
Step1 對(duì)單一值的篩選
①選中工作表中數(shù)據(jù)區(qū)域中的任意一個(gè)單元格，然后單擊菜單“數(shù)據(jù)”→“篩選”→“自動(dòng)篩選”，此時(shí)數(shù)據(jù)清單中的每一列都會(huì)添加一個(gè)下箭頭的按鈕。
②單擊下箭頭按鈕將顯示這一列所有的不重復(fù)的值，用戶可以對(duì)這些值進(jìn)行選擇。

③選中某一個(gè)數(shù)值，如“2”后Excel將會(huì)顯示出于這個(gè)數(shù)據(jù)有關(guān)的記錄，其他的記錄會(huì)被隱藏起來(lái)。篩選出來(lái)的記錄的行標(biāo)記會(huì)變?yōu)樗{(lán)色，這些藍(lán)色的行標(biāo)記是在原始數(shù)據(jù)清單中的行標(biāo)記。

Step2 按班級(jí)打印排名表
①單擊A2單元格右側(cè)的下箭頭按鈕選中“1”，此時(shí)Excel將顯示出與班級(jí)1相關(guān)的記錄。
②參閱1.2.2小節(jié)Step2設(shè)置打印區(qū)域。
③單擊菜單“文件”→“打印”即可打印班級(jí)1的排名表。

④在打印預(yù)覽頁(yè)中單擊A2單元格右側(cè)的下箭頭按鈕選中“2”，然后單擊菜單“文件”→“打印”即可打印班級(jí)2的排名表。
類似的可以打印班級(jí)3的排名表。

⑤打印完畢單擊菜單“視圖”→“普通”恢復(fù)到普通狀態(tài)視圖，然后單擊A2單元格右側(cè)的下箭頭按鈕選擇全部。

Step3 指定條件篩選
①單擊“年級(jí)名次”的下箭頭按鈕，在下拉列表中選擇“自定義”選項(xiàng)彈出“自定義自動(dòng)篩選方式”對(duì)話框。

②在該對(duì)話框的“年級(jí)名次”組合框中，在左邊的下拉列表中選擇條件為“小于或等于”，在右側(cè)的數(shù)值文本框中輸入“50”，然后單擊“確定”按鈕。

篩選后的結(jié)果是“年級(jí)名次”在1~50之間的記錄。