大學(xué)計(jì)算機(jī)基礎(chǔ)-基于計(jì)算思維（Windows 10+Office 2016）（第2版）課件 第6章 Excel綜合應(yīng)用

大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）第6章Excel綜合應(yīng)用6.1項(xiàng)目分析輔導(dǎo)員李老師從成績(jī)系統(tǒng)中導(dǎo)出了電子信息工程專業(yè)13級(jí)的全體學(xué)生的成績(jī)，他需要對(duì)比了解各班級(jí)的成績(jī)情況以及評(píng)定本學(xué)期的獎(jiǎng)學(xué)金。項(xiàng)目分析

根據(jù)學(xué)校的獎(jiǎng)學(xué)金評(píng)選條例，除了要滿足相關(guān)的基本條件外，成績(jī)必須滿足：

項(xiàng)目分析

其中，主要學(xué)生干部是指校團(tuán)委、校學(xué)生會(huì)副部長(zhǎng)以上（含副部長(zhǎng)）的學(xué)生干部，院分團(tuán)委、學(xué)生會(huì)部長(zhǎng)（含部長(zhǎng)）以上學(xué)生干部，班級(jí)班長(zhǎng)、團(tuán)支部書記。其他學(xué)生干部為一般學(xué)生干部。（3）獎(jiǎng)學(xué)金按平均課程績(jī)點(diǎn)排序，當(dāng)平均課程績(jī)點(diǎn)相同時(shí)，按德育績(jī)點(diǎn)排序。（4）一等獎(jiǎng)學(xué)金名額不超過專業(yè)總?cè)藬?shù)2％（含，下同），二等獎(jiǎng)學(xué)金名額不超過專業(yè)總?cè)藬?shù)5％，三等獎(jiǎng)學(xué)金名額不超過專業(yè)總?cè)藬?shù)8％，人數(shù)出現(xiàn)小數(shù)的，采用去尾法計(jì)算。（5）當(dāng)平均課程績(jī)點(diǎn)和德育績(jī)點(diǎn)相同導(dǎo)致獎(jiǎng)學(xué)金等級(jí)不同時(shí)，獎(jiǎng)學(xué)金等級(jí)按最高等級(jí)計(jì)算，下一等級(jí)獎(jiǎng)學(xué)金名額相應(yīng)減少。如果平均課程績(jī)點(diǎn)和德育績(jī)點(diǎn)相同導(dǎo)致最后一個(gè)等級(jí)名額不夠時(shí)，則自動(dòng)擴(kuò)充獎(jiǎng)學(xué)金。（6）獎(jiǎng)學(xué)金一次性發(fā)放。一等獎(jiǎng)1500元/人，二等獎(jiǎng)1250元/人，三等獎(jiǎng)750元/人。感謝聆聽！大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）6.2.1知識(shí)點(diǎn)解析第6章Excel綜合應(yīng)用6.2成績(jī)分析1.凍結(jié)窗格

對(duì)一個(gè)超寬超長(zhǎng)表格中的數(shù)據(jù)進(jìn)行操作時(shí)，當(dāng)行、列標(biāo)題行消失后，有時(shí)會(huì)記錯(cuò)各行、列標(biāo)題的相對(duì)位置。為解決該問題，可以將行、列的標(biāo)題部分和數(shù)據(jù)區(qū)域通過“凍結(jié)窗格”視圖來(lái)實(shí)現(xiàn)將標(biāo)題部分保留在屏幕上不動(dòng)，而數(shù)據(jù)區(qū)域部分則可以滾動(dòng)。2.數(shù)據(jù)排序

很多時(shí)候，希望表格中的數(shù)據(jù)按照某種方式來(lái)組織，可以使用“數(shù)據(jù)排序”功能來(lái)實(shí)現(xiàn)?！皵?shù)據(jù)排序”通常是對(duì)選定的數(shù)據(jù)按照某一列或多列的升序或降序進(jìn)行排序。左圖是對(duì)“2013年中國(guó)縣級(jí)城市競(jìng)爭(zhēng)力排行榜”按照“分值”降序排序數(shù)據(jù)，而右圖是按照“所在省份升序，如果省份相同，則按照分值升序”排序數(shù)據(jù)。按分值從大到小排序按省份拼音從小到大排序省份相同時(shí)按分值從小到大排序

3.數(shù)據(jù)篩選數(shù)據(jù)篩選的目的是從一堆數(shù)據(jù)中找出想要的數(shù)據(jù)。通過數(shù)據(jù)篩選功能，可以將符合條件的數(shù)據(jù)集中顯示在工作表上，而將不符合條件的數(shù)據(jù)暫時(shí)隱藏起來(lái)。如圖所示，既可以找出“2013年中國(guó)縣級(jí)城市競(jìng)爭(zhēng)力排行榜”中屬于“江蘇省”的所有城市，也可找出屬于“江蘇省”并且分值在90分以上的所有城市。按所在省份篩選按所在省份及分值>90篩選

3.數(shù)據(jù)篩選通常來(lái)說，要對(duì)數(shù)據(jù)進(jìn)行篩選，數(shù)據(jù)需要滿足（1）有標(biāo)題行，即數(shù)據(jù)區(qū)域的第一行為標(biāo)題。（2）數(shù)據(jù)區(qū)域內(nèi)不能有空行或空列。如果有空行或空列，Excel會(huì)認(rèn)為不是同一個(gè)數(shù)據(jù)區(qū)域。Excel

4.分類匯總?cè)绻枰獙?duì)Excel工作表中的數(shù)據(jù)按類別進(jìn)行求和、計(jì)數(shù)等，可以使用分類匯總功能。所謂分類匯總，就是先對(duì)數(shù)據(jù)進(jìn)行排序（通過排序進(jìn)行分類），然后再進(jìn)行匯總。如圖所示，要統(tǒng)計(jì)每個(gè)省份（稱為分類字段）入選的城市數(shù)（稱為匯總項(xiàng)及匯總方式），按“省份”進(jìn)行排序（可以是升序，也可以是降序）后，通過分類匯總，就可以統(tǒng)計(jì)出來(lái)每個(gè)省份入選的城市數(shù)。顯示級(jí)別3，顯示數(shù)據(jù)明細(xì)及匯總結(jié)果顯示級(jí)別2，顯示匯總結(jié)果

5.數(shù)據(jù)透視表如果需要對(duì)Excel工作表中的數(shù)據(jù)進(jìn)行深入的分析，可以使用數(shù)據(jù)透視表功能。行顯示省份列顯示城市數(shù)量行顯示省份列顯示城市數(shù)量及分值平均值如圖所示，通過數(shù)據(jù)透視表功能，可以得到“2013年中國(guó)縣級(jí)城市競(jìng)爭(zhēng)力排行榜”中每個(gè)省份入選的城市個(gè)數(shù)，也可以得到每個(gè)省份入選的城市個(gè)數(shù)及獲得的平均分。數(shù)據(jù)透視表功能提供了一種比分類匯總功能更強(qiáng)大的方式來(lái)分析數(shù)據(jù)。此外，數(shù)據(jù)透視表功能不會(huì)對(duì)已有數(shù)據(jù)產(chǎn)生任何改變，并可以用不同的方式來(lái)查看數(shù)據(jù)。感謝聆聽！大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）6.2.2任務(wù)實(shí)現(xiàn)第6章Excel綜合應(yīng)用6.2成績(jī)分析1.任務(wù)分析

李老師要對(duì)電子信息工程專業(yè)13級(jí)的全體學(xué)生成績(jī)做分析，要求：（1）計(jì)算學(xué)生的平均課程績(jī)點(diǎn)。（2）按平均課程績(jī)點(diǎn)及德育績(jī)點(diǎn)降序排序。（3）如圖所示，分類匯總每個(gè)班的平均課程績(jī)點(diǎn)的平均值。1.任務(wù)分析

李老師要對(duì)電子信息工程專業(yè)13級(jí)的全體學(xué)生成績(jī)做分析，要求：（4）如圖所示，分類匯總每個(gè)班的平均課程績(jī)點(diǎn)的平均值及班級(jí)人數(shù)。1.任務(wù)分析

李老師要對(duì)電子信息工程專業(yè)13級(jí)的全體學(xué)生成績(jī)做分析，要求：（5）如圖所示，不改變?cè)紨?shù)據(jù)的前提下統(tǒng)計(jì)每個(gè)班的德育及平均課程績(jī)點(diǎn)平均值。1.任務(wù)分析

李老師要對(duì)電子信息工程專業(yè)13級(jí)的全體學(xué)生成績(jī)做分析，要求：（6）如圖所示，不改變?cè)紨?shù)據(jù)的前提下統(tǒng)計(jì)每個(gè)班的德育績(jī)點(diǎn)的最大值和最小值。1.任務(wù)分析

李老師要對(duì)電子信息工程專業(yè)13級(jí)的全體學(xué)生成績(jī)做分析，要求：（7）如圖所示，篩選出可能獲得獎(jiǎng)學(xué)金的學(xué)生名單，即德育績(jī)點(diǎn)3.0以上（含，下同），平均課程績(jī)點(diǎn)2.88以上，單科課程績(jī)點(diǎn)2.2以上（因?yàn)椤爸饕刹俊背煽?jī)績(jī)點(diǎn)可以下調(diào)0.3）。由于學(xué)生成績(jī)數(shù)據(jù)多，在進(jìn)行成績(jī)操作時(shí)，希望表題、學(xué)生姓名以及課程信息等一直顯示在屏幕上，可以通過凍結(jié)窗格來(lái)實(shí)現(xiàn)。操作步驟如下：2.實(shí)現(xiàn)過程

1）復(fù)制信息并凍結(jié)窗格①新建工作簿“成績(jī)分析.xlsx”，打開“相關(guān)素材.xlsx”，同時(shí)選擇“成績(jī)素材”和“課程學(xué)分”工作表，將它們復(fù)制到“成績(jī)分析.xlsx”工作簿，關(guān)閉“相關(guān)素材.xlsx”。將“成績(jī)分析.xlsx”的“成績(jī)素材”工作表重命名為“平均課程績(jī)點(diǎn)計(jì)算”。②選擇“平均課程績(jī)點(diǎn)計(jì)算”工作表的B3單元格。③單擊“視圖”選項(xiàng)卡/“窗口”組/“凍結(jié)窗格”按鈕的“凍結(jié)拆分窗格”。就實(shí)現(xiàn)了表題、學(xué)生姓名以及課程信息等一直顯示在屏幕上?？梢酝ㄟ^鼠標(biāo)左鍵拖動(dòng)窗口的滾動(dòng)條來(lái)查看凍結(jié)的效果。平均課程績(jī)點(diǎn)（不含德育）計(jì)算公式為：，平均課程績(jī)點(diǎn)帶兩位小數(shù)位（四舍五入)。具體操作如下：2.實(shí)現(xiàn)過程

2）平均課程績(jī)點(diǎn)計(jì)算①選擇I3單元格，輸入“=”，用鼠標(biāo)選取D3單元格，輸入“*”，用鼠標(biāo)選取“課程學(xué)分”工作表的B2單元格，按下F4鍵，將B2轉(zhuǎn)換為絕對(duì)地址，此時(shí)，I3單元格編輯區(qū)域的內(nèi)容為“=D3*課程學(xué)分!$B$2”。在此基礎(chǔ)上，輸入“+”，重復(fù)前面的操作，完成課程學(xué)分績(jī)點(diǎn)=的計(jì)算。I3單元格編輯區(qū)域的內(nèi)容最終為“=D3*課程學(xué)分!$B$2+平均課程績(jī)點(diǎn)計(jì)算!E3*課程學(xué)分!$B$3+平均課程績(jī)點(diǎn)計(jì)算!F3*課程學(xué)分!$B$4+平均課程績(jī)點(diǎn)計(jì)算!G3*課程學(xué)分!$B$5+平均課程績(jī)點(diǎn)計(jì)算!H3*課程學(xué)分!$B$6)”。

=

2.實(shí)現(xiàn)過程

2）平均課程績(jī)點(diǎn)計(jì)算②剪切I3單元格編輯欄中“=”之后的內(nèi)容，在“=”之后輸入“(”，粘貼剛才剪切的內(nèi)容，再輸入“)”，I3單元格編輯區(qū)域的內(nèi)容為“=(D4*課程學(xué)分!$B$2+平均課程績(jī)點(diǎn)計(jì)算!E4*課程學(xué)分!$B$3+平均課程績(jī)點(diǎn)計(jì)算!F4*課程學(xué)分!$B$4+平均課程績(jī)點(diǎn)計(jì)算!G4*課程學(xué)分!$B$5+平均課程績(jī)點(diǎn)計(jì)算!H4*課程學(xué)分!$B$6)”。③將鼠標(biāo)定位在I3單元格編輯區(qū)域中內(nèi)容的最后，輸入“/”。④單擊編輯欄的名稱框區(qū)域，選取SUM函數(shù)（如果沒有顯示SUM函數(shù)，則通過“其他函數(shù)”來(lái)選?。鐖D所示。2.實(shí)現(xiàn)過程

2）平均課程績(jī)點(diǎn)計(jì)算⑤SUM函數(shù)的Number1參數(shù)設(shè)置為“課程學(xué)分!$B$2:$B$6”。I3單元格的內(nèi)容顯示為“3.686666667”。⑥剪切I3單元格編輯欄中“=”之后的內(nèi)容，單擊“插入函數(shù)”按鈕，在I3單元格插入ROUND函數(shù)。⑦ROUND函數(shù)的Number參數(shù)設(shè)置為剪切的內(nèi)容“D3*課程學(xué)分!$B$2+平均課程績(jī)點(diǎn)計(jì)算!E3*課程學(xué)分!$B$3+平均課程績(jī)點(diǎn)計(jì)算!F3*課程學(xué)分!B4+平均課程績(jī)點(diǎn)計(jì)算!G3*課程學(xué)分!$B$5+平均課程績(jī)點(diǎn)計(jì)算!H3*課程學(xué)分!$B$6)/SUM(課程學(xué)分!$B$2:$B$6)”，Num_digits參數(shù)設(shè)置為2。I3單元格編輯區(qū)域的內(nèi)容為“=ROUND((D3*課程學(xué)分!$B$2+平均課程績(jī)點(diǎn)計(jì)算!E3*課程學(xué)分!$B$3+平均課程績(jī)點(diǎn)計(jì)算!F3*課程學(xué)分!$B$4+平均課程績(jī)點(diǎn)計(jì)算!G3*課程學(xué)分!$B$5+平均課程績(jī)點(diǎn)計(jì)算!H3*課程學(xué)分!$B$6)/SUM(課程學(xué)分!$B$2:$B$6),2)”，I3單元格顯示的結(jié)果為“3.69”。⑧選擇I3單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成公式的復(fù)制。由于排序和分類匯總會(huì)改變?cè)袛?shù)據(jù)的結(jié)構(gòu)，而篩選會(huì)導(dǎo)致數(shù)據(jù)的顯示發(fā)生變化，因此可以將“平均課程績(jī)點(diǎn)計(jì)算”工作表復(fù)制成新的工作表。操作步驟如下：2.實(shí)現(xiàn)過程

3）排序、篩選和分類匯總數(shù)據(jù)準(zhǔn)備①選擇“平均課程績(jī)點(diǎn)計(jì)算”工作表，復(fù)制為“平均課程績(jī)點(diǎn)計(jì)算(2)”工作表。②由于被復(fù)制的工作表引用了“平均課程績(jī)點(diǎn)計(jì)算”工作表的數(shù)據(jù)，為防止數(shù)據(jù)混亂，需要將其I列的數(shù)據(jù)復(fù)制后再“值粘貼”。③選擇“平均課程績(jī)點(diǎn)計(jì)算(2)”工作表，復(fù)制為“平均課程績(jī)點(diǎn)計(jì)算(3)”工作表、“平均課程績(jī)點(diǎn)計(jì)算(4)”工作表及“平均課程績(jī)點(diǎn)計(jì)算(5)”工作表。④將“平均課程績(jī)點(diǎn)計(jì)算(2)”工作表重命名為“成績(jī)排序”，將“平均課程績(jī)點(diǎn)計(jì)算(3)”工作表重命名為“分類匯總1”，將“平均課程績(jī)點(diǎn)計(jì)算(4)”工作表重命名為“分類匯總2”，將“平均課程績(jī)點(diǎn)計(jì)算(5)”工作表重命名為“數(shù)據(jù)篩選”。按平均課程績(jī)點(diǎn)及德育績(jī)點(diǎn)降序排序。操作步驟如下：2.實(shí)現(xiàn)過程

4）成績(jī)排序①鼠標(biāo)定位在“成績(jī)排序”工作表數(shù)據(jù)區(qū)域的任一單元格。②選擇“數(shù)據(jù)”選項(xiàng)卡/“排序和篩選”組/“排序”按鈕，在彈出的如圖所示對(duì)話框中，勾選“數(shù)據(jù)包含標(biāo)題”，選擇主要關(guān)鍵字為“平均課程績(jī)點(diǎn)”，排序依據(jù)為“數(shù)值”，次序?yàn)椤敖敌颉?。按平均課程績(jī)點(diǎn)及德育績(jī)點(diǎn)降序排序。操作步驟如下：2.實(shí)現(xiàn)過程

4）成績(jī)排序③如圖所示，單擊“添加條件”，選擇次要關(guān)鍵字為“德育績(jī)點(diǎn)”，排序依據(jù)為“數(shù)值”，次序?yàn)椤敖敌颉薄Ｒ诸悈R總每個(gè)班的平均課程績(jī)點(diǎn)的平均值，需要先按班級(jí)排序后再進(jìn)行分類匯總。具體操作如下：2.實(shí)現(xiàn)過程

5）按班級(jí)分類匯總平均課程績(jī)點(diǎn)平均值①如圖所示，鼠標(biāo)定位在“分類匯總1”工作表B列的任意單元格。選擇“開始”選項(xiàng)卡/“編輯”組/“排序和篩選”按鈕的“升序”，按班級(jí)升序排序數(shù)據(jù)。要分類匯總每個(gè)班的平均課程績(jī)點(diǎn)的平均值，需要先按班級(jí)排序后再進(jìn)行分類匯總。具體操作如下：2.實(shí)現(xiàn)過程

5）按班級(jí)分類匯總平均課程績(jī)點(diǎn)平均值②如圖所示，選擇“數(shù)據(jù)”選項(xiàng)卡/“分級(jí)顯示”組/“分類匯總”按鈕，在彈出的對(duì)話框中選擇分類字段為“班級(jí)”，匯總方式為“平均值”，選定匯總項(xiàng)為“平均課程績(jī)點(diǎn)”。③切換到“2級(jí)”顯示，就可以看到分類匯總的結(jié)果了。要分類匯總每個(gè)班的平均課程績(jī)點(diǎn)平均值及班級(jí)人數(shù)，就需要先按班級(jí)對(duì)平均課程績(jī)點(diǎn)分類匯總，然后再按班級(jí)對(duì)姓名進(jìn)行分類匯總。具體操作步驟如下：2.實(shí)現(xiàn)過程

6）按班級(jí)分類匯總平均課程績(jī)點(diǎn)平均值及人數(shù)①鼠標(biāo)定位在“分類匯總2”工作表B列的任意單元格。選擇“開始”選項(xiàng)卡/“編輯”組/“排序和篩選”按鈕的“升序”，按班級(jí)升序排序數(shù)據(jù)。②選擇“數(shù)據(jù)”選項(xiàng)卡/“分級(jí)顯示”組/“分類匯總”按鈕，在彈出的對(duì)話框中選擇分類字段為“班級(jí)”，匯總方式為“平均值”，選定匯總項(xiàng)為“平均課程績(jī)點(diǎn)”。要分類匯總每個(gè)班的平均課程績(jī)點(diǎn)平均值及班級(jí)人數(shù)，就需要先按班級(jí)對(duì)平均課程績(jī)點(diǎn)分類匯總，然后再按班級(jí)對(duì)姓名進(jìn)行分類匯總。具體操作步驟如下：2.實(shí)現(xiàn)過程

6）按班級(jí)分類匯總平均課程績(jī)點(diǎn)平均值及人數(shù)③如圖所示，再次選擇“數(shù)據(jù)”選項(xiàng)卡/“分級(jí)顯示”組/“分類匯總”按鈕，在彈出的對(duì)話框中選擇分類字段為“班級(jí)”，匯總方式為“計(jì)數(shù)”，選定匯總項(xiàng)為“姓名”，不勾選“替換當(dāng)前分類匯總”（表明在原有的分類匯總基礎(chǔ)上再分類匯總）。④切換到“3級(jí)”顯示，就可以看到分類匯總的結(jié)果了。要不改變?cè)紨?shù)據(jù)的前提下統(tǒng)計(jì)每個(gè)班的德育績(jī)點(diǎn)及平均課程績(jī)點(diǎn)的平均值，可以使用數(shù)據(jù)篩選功能。具體操作步驟如下：2.實(shí)現(xiàn)過程

7）統(tǒng)計(jì)每個(gè)班的德育及平均課程績(jī)點(diǎn)平均值①如同所示，鼠標(biāo)定位在“平均課程績(jī)點(diǎn)計(jì)算”工作表的任一單元格，選擇“插入”選項(xiàng)卡/“表格”組/“數(shù)據(jù)透視表”按鈕的“數(shù)據(jù)透視表”。要不改變?cè)紨?shù)據(jù)的前提下統(tǒng)計(jì)每個(gè)班的德育績(jī)點(diǎn)及平均課程績(jī)點(diǎn)的平均值，可以使用數(shù)據(jù)篩選功能。具體操作步驟如下：2.實(shí)現(xiàn)過程

7）統(tǒng)計(jì)每個(gè)班的德育及平均課程績(jī)點(diǎn)平均值②彈出如圖所示的“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框。在該對(duì)話框中，系統(tǒng)已經(jīng)自動(dòng)選擇要分析的數(shù)據(jù)區(qū)域（用戶也可以自己修改數(shù)據(jù)區(qū)域）。選擇將產(chǎn)生的數(shù)據(jù)透視表放置在新工作表中。要不改變?cè)紨?shù)據(jù)的前提下統(tǒng)計(jì)每個(gè)班的德育績(jī)點(diǎn)及平均課程績(jī)點(diǎn)的平均值，可以使用數(shù)據(jù)篩選功能。具體操作步驟如下：2.實(shí)現(xiàn)過程

7）統(tǒng)計(jì)每個(gè)班的德育及平均課程績(jī)點(diǎn)平均值③在如圖所示的工作表中，選擇要添加到報(bào)表的“班級(jí)”、“德育績(jī)點(diǎn)”及“平均課程績(jī)點(diǎn)”字段，就在工作表的左側(cè)生成了每個(gè)班的德育及平均課程績(jī)點(diǎn)的和。選擇“數(shù)據(jù)透視表工具/設(shè)計(jì)”選項(xiàng)卡/“數(shù)據(jù)透視表樣式”組的“數(shù)據(jù)透視表樣式淺色17”。要不改變?cè)紨?shù)據(jù)的前提下統(tǒng)計(jì)每個(gè)班的德育績(jī)點(diǎn)及平均課程績(jī)點(diǎn)的平均值，可以使用數(shù)據(jù)篩選功能。具體操作步驟如下：2.實(shí)現(xiàn)過程

7）統(tǒng)計(jì)每個(gè)班的德育及平均課程績(jī)點(diǎn)平均值④單擊上圖所示的“求和項(xiàng)：德育績(jī)點(diǎn)”旁的下三角，彈出如圖所示的快捷菜單，選擇“值字段設(shè)置(N)…”。要不改變?cè)紨?shù)據(jù)的前提下統(tǒng)計(jì)每個(gè)班的德育績(jī)點(diǎn)及平均課程績(jī)點(diǎn)的平均值，可以使用數(shù)據(jù)篩選功能。具體操作步驟如下：2.實(shí)現(xiàn)過程

7）統(tǒng)計(jì)每個(gè)班的德育及平均課程績(jī)點(diǎn)平均值⑤在彈出的如圖所示的“值字段設(shè)置”對(duì)話框中，將計(jì)算類型改為“平均值”。⑥將“求和項(xiàng)：平均課程績(jī)點(diǎn)”的計(jì)算類型改為“平均值”，就可以得到每個(gè)班的德育及平均課程績(jī)點(diǎn)的平均值了。⑦將透視結(jié)果所在的工作表重命名為“數(shù)據(jù)透視表1”。要統(tǒng)計(jì)每個(gè)班的德育績(jī)點(diǎn)的最大值和最小值，可以使用數(shù)據(jù)篩選功能。具體操作步驟如下：2.實(shí)現(xiàn)過程

8）統(tǒng)計(jì)每個(gè)班的德育績(jī)點(diǎn)的最大值和最小值①鼠標(biāo)定位在“平均課程績(jī)點(diǎn)計(jì)算”工作表的任一單元格，選擇“插入”選項(xiàng)卡/“表格”組/“數(shù)據(jù)透視表”按鈕的“數(shù)據(jù)透視表”。②在數(shù)據(jù)透視工作表中，選擇要添加到報(bào)表的“班級(jí)”及“德育績(jī)點(diǎn)”字段。③如圖所示，鼠標(biāo)指向“德育績(jī)點(diǎn)”，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“添加到數(shù)值”。要統(tǒng)計(jì)每個(gè)班的德育績(jī)點(diǎn)的最大值和最小值，可以使用數(shù)據(jù)篩選功能。具體操作步驟如下：2.實(shí)現(xiàn)過程

8）統(tǒng)計(jì)每個(gè)班的德育績(jī)點(diǎn)的最大值和最小值④將數(shù)值中的“求和項(xiàng)：德育績(jī)點(diǎn)”的計(jì)算類型改為“最大值”，“求和項(xiàng)：德育績(jī)點(diǎn)2”的計(jì)算類型改為“最小值”，就可以得到每個(gè)班的德育績(jī)點(diǎn)的最大值和最小值了。⑤透視結(jié)果所在的工作表重命名為“數(shù)據(jù)透視表2”。根據(jù)獎(jiǎng)學(xué)金評(píng)選規(guī)則，可能獲得獎(jiǎng)學(xué)金的學(xué)生成績(jī)應(yīng)該滿足：德育績(jī)點(diǎn)3.0以上，平均課程績(jī)點(diǎn)2.88以上，單科課程績(jī)點(diǎn)2.2以上。因此，可以篩選出滿足上述條件的數(shù)據(jù)。具體操作步驟如下：2.實(shí)現(xiàn)過程

9）篩選出可能獲得獎(jiǎng)學(xué)金的學(xué)生名單①如圖所示，鼠標(biāo)定位在“數(shù)據(jù)篩選”工作表的任一單元格。選擇“數(shù)據(jù)”選項(xiàng)卡/“排序和篩選”組的“篩選”按鈕。根據(jù)獎(jiǎng)學(xué)金評(píng)選規(guī)則，可能獲得獎(jiǎng)學(xué)金的學(xué)生成績(jī)應(yīng)該滿足：德育績(jī)點(diǎn)3.0以上，平均課程績(jī)點(diǎn)2.88以上，單科課程績(jī)點(diǎn)2.2以上。因此，可以篩選出滿足上述條件的數(shù)據(jù)。具體操作步驟如下：2.實(shí)現(xiàn)過程

9）篩選出可能獲得獎(jiǎng)學(xué)金的學(xué)生名單②如圖所示，單擊C2單元格（德育績(jī)點(diǎn)）旁的下三角，在彈出的快捷菜單中選擇“數(shù)字篩選”的“大于或等于(O)…”。根據(jù)獎(jiǎng)學(xué)金評(píng)選規(guī)則，可能獲得獎(jiǎng)學(xué)金的學(xué)生成績(jī)應(yīng)該滿足：德育績(jī)點(diǎn)3.0以上，平均課程績(jī)點(diǎn)2.88以上，單科課程績(jī)點(diǎn)2.2以上。因此，可以篩選出滿足上述條件的數(shù)據(jù)。具體操作步驟如下：2.實(shí)現(xiàn)過程

9）篩選出可能獲得獎(jiǎng)學(xué)金的學(xué)生名單③在彈出的如圖所示的“自定義自動(dòng)篩選方式”對(duì)話框中，將“德育績(jī)點(diǎn)”設(shè)置為“大于或等于3.0”。根據(jù)獎(jiǎng)學(xué)金評(píng)選規(guī)則，可能獲得獎(jiǎng)學(xué)金的學(xué)生成績(jī)應(yīng)該滿足：德育績(jī)點(diǎn)3.0以上，平均課程績(jī)點(diǎn)2.88以上，單科課程績(jī)點(diǎn)2.2以上。因此，可以篩選出滿足上述條件的數(shù)據(jù)。具體操作步驟如下：2.實(shí)現(xiàn)過程

9）篩選出可能獲得獎(jiǎng)學(xué)金的學(xué)生名單④將大學(xué)英語(yǔ)績(jī)點(diǎn)、大學(xué)計(jì)算機(jī)績(jī)點(diǎn)、高等數(shù)學(xué)績(jī)點(diǎn)、數(shù)字電路績(jī)點(diǎn)及單片機(jī)績(jī)點(diǎn)的條件設(shè)置為大于或等于2.2。⑤將平均課程績(jī)點(diǎn)的條件設(shè)置為大于或等于2.88。在狀態(tài)欄就可以看到“在200條記錄中找到89個(gè)”的提示，表明可能獲得獎(jiǎng)學(xué)金的學(xué)生有89人。感謝聆聽！大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）6.2.3總結(jié)與提高第6章Excel綜合應(yīng)用6.2成績(jī)分析1.高級(jí)篩選

利用Excel的數(shù)據(jù)篩選功能，可以將符合條件的結(jié)果顯示在原有的數(shù)據(jù)表格中，不符合條件的將自動(dòng)隱藏。但在有些時(shí)候，可能需要更復(fù)雜的篩選。例如，需要將“德育績(jī)點(diǎn)4.0，或者課程平均績(jī)點(diǎn)<=2.88并且大學(xué)英語(yǔ)績(jī)點(diǎn)3.0以上”的數(shù)據(jù)篩選出來(lái)，就需要使用高級(jí)篩選功能。（1）條件的標(biāo)題要與數(shù)據(jù)表的原有標(biāo)題完全一致。（2）多字段間的條件若為“與”關(guān)系，則寫在一行。（3）多字段間的條件若為“或”關(guān)系，則寫在下一行。針對(duì)高級(jí)篩選，首先需要設(shè)置篩選條件區(qū)域。篩選條件有3個(gè)特征

要將“德育績(jī)點(diǎn)4.0，或者課程平均績(jī)點(diǎn)<=2.88并且大學(xué)英語(yǔ)績(jī)點(diǎn)3.0以上”的數(shù)據(jù)篩選出來(lái)，首先設(shè)置如圖所示的篩選條件區(qū)域K2:M4。具體內(nèi)容為：（1）K2:M2放置的為標(biāo)題字段。（2）K3的值為4，表示“德育績(jī)點(diǎn)=4”。L4的值為>=3.0，表示“大學(xué)英語(yǔ)績(jī)點(diǎn)>=3.0”。M4的值為<=2.88，表示“平均課程績(jī)點(diǎn)<=2.88”。（3）第4行兩個(gè)條件為與的關(guān)系，即“大學(xué)英語(yǔ)績(jī)點(diǎn)>=3.0并且平均課程績(jī)點(diǎn)<=2.88”。（4）第3行條件和第4行條件為或的關(guān)系，即“‘德育績(jī)點(diǎn)=4’或者‘大學(xué)英語(yǔ)績(jī)點(diǎn)>=3.0并且平均課程績(jī)點(diǎn)<=2.88’”。1.高級(jí)篩選

選擇“數(shù)據(jù)”選項(xiàng)卡/“排序和篩選”組/“高級(jí)”按鈕，彈出如圖所示的“高級(jí)篩選”對(duì)話框，其中，列表區(qū)域$A$2:$I$202為要參與篩選的原始數(shù)據(jù)區(qū)域，條件區(qū)域?yàn)?K$2:$M$4，篩選出來(lái)的數(shù)據(jù)放置在$K$8開始的位置。1.高級(jí)篩選1.任務(wù)分析

就可以得到如圖所示的篩選結(jié)果了。2.數(shù)據(jù)透視表與篩選

如圖所示，利用數(shù)據(jù)透視表，可以分專業(yè)統(tǒng)計(jì)每個(gè)考場(chǎng)及考試時(shí)間的參加考試學(xué)生人數(shù)。通過增加“性別”作為報(bào)表篩選字段，就可以篩選出每個(gè)考場(chǎng)的參加考試男女生人數(shù)等。利用數(shù)據(jù)透視表的篩選功能，可以對(duì)透視結(jié)果進(jìn)行進(jìn)一步的分析統(tǒng)計(jì)。2.數(shù)據(jù)透視表與篩選

如圖所示，利用數(shù)據(jù)透視表，可以分專業(yè)統(tǒng)計(jì)每個(gè)考場(chǎng)及考試時(shí)間的參加考試學(xué)生人數(shù)。通過增加“性別”作為報(bào)表篩選字段，就可以篩選出每個(gè)考場(chǎng)的參加考試男女生人數(shù)等。利用數(shù)據(jù)透視表的篩選功能，可以對(duì)透視結(jié)果進(jìn)行進(jìn)一步的分析統(tǒng)計(jì)。3.數(shù)據(jù)透視表與切片器

①鼠標(biāo)定位在如所示的數(shù)據(jù)透視表的任一單元格。②單擊“數(shù)據(jù)透視表工具-選項(xiàng)”選項(xiàng)卡/“排序和篩選”組/“插入切片器”按鈕。③如圖6-29所示，在“插入切片器”對(duì)話框中，選擇“專業(yè)”，就可以按專業(yè)來(lái)查看分布在每個(gè)考場(chǎng)及考試時(shí)間的參加考試學(xué)生人數(shù)了。如果需要同時(shí)查看多個(gè)專業(yè)，則可以按下Ctrl鍵來(lái)進(jìn)行選擇。利用切片器功能，可以讓數(shù)據(jù)分析與呈現(xiàn)更加可視化。例如，如果想按專業(yè)查看分布在每個(gè)考場(chǎng)及考試時(shí)間的參加考試學(xué)生人數(shù)，可以：4.數(shù)據(jù)透視圖

通過“插入”選項(xiàng)卡/“表格”組/“數(shù)據(jù)透視表”按鈕的“數(shù)據(jù)透視圖”，就可以同時(shí)生成如圖所示的數(shù)據(jù)透視表與數(shù)據(jù)透視圖。此外，可以將切片器與數(shù)據(jù)透視表結(jié)合使用，數(shù)據(jù)透視圖會(huì)隨著切片器數(shù)據(jù)的變化而變化Excel在生成數(shù)據(jù)透視表時(shí)，可以同時(shí)生成數(shù)據(jù)透視圖，從而更直觀地顯示統(tǒng)計(jì)結(jié)果。5.多重合并

例如，如圖所示，Shee1工作表和Sheet2工作表中存放了好再來(lái)辦公設(shè)備公司2014年1月和2月的銷售統(tǒng)計(jì)數(shù)據(jù)，現(xiàn)在希望能得到該公司2014年1-2月的統(tǒng)計(jì)數(shù)據(jù)，可以通過數(shù)據(jù)透視表的多重合并來(lái)實(shí)現(xiàn)。如果需要對(duì)多個(gè)工作表中的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)，可以使用數(shù)據(jù)透視表的多重合并功能。5.多重合并

同時(shí)按下“ALT+D+P”，調(diào)出如圖所示的“數(shù)據(jù)透視表和收據(jù)透視圖”向?qū)?，在步驟1中選擇“多重合并計(jì)算數(shù)據(jù)區(qū)域”，步驟2a中選擇“自定義頁(yè)字段”，步驟2b中選取區(qū)域?yàn)镾heet1!$A$2:$B$7和Sheet2!$A$2:$B$8，步驟3中指定數(shù)據(jù)透視表的位置，就可以得到對(duì)應(yīng)的數(shù)據(jù)透視表，對(duì)其進(jìn)行字段改名即可得到數(shù)據(jù)透視結(jié)果。感謝聆聽！大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）6.3.1知識(shí)點(diǎn)解析第6章Excel綜合應(yīng)用6.3獎(jiǎng)學(xué)金評(píng)定1.VLOOKUP函數(shù)

如圖所示，要填寫“錄取名錄表”中每個(gè)學(xué)生的錄取專業(yè)名稱及輔導(dǎo)員，就需要根據(jù)每個(gè)學(xué)生的錄取專業(yè)代碼去查找“專業(yè)代碼對(duì)應(yīng)表”中的數(shù)據(jù)。例如，要填寫“陳萍萍”的錄取專業(yè)名稱及輔導(dǎo)員，就需要根據(jù)“陳萍萍”的專業(yè)代碼“1301”，在“專業(yè)代碼對(duì)應(yīng)表”中找到專業(yè)代碼“1301”所在的行，然后將“1301”所在行對(duì)應(yīng)的錄取專業(yè)名稱及輔導(dǎo)員，將其填寫到對(duì)應(yīng)位置。1.VLOOKUP函數(shù)

因此，實(shí)現(xiàn)上述填充查找的關(guān)鍵是：（1）明確要填充的結(jié)果（錄取專業(yè)名稱，輔導(dǎo)員）。（2）要填充的結(jié)果所依據(jù)的查找元素（錄取專業(yè)代碼）。（3）被查找的元素在哪里（專業(yè)代碼對(duì)應(yīng)表），稱之為查找區(qū)域。（4）在查找區(qū)域的什么位置查找（專業(yè)代碼列）。（5）找到被查找的元素后，哪些結(jié)果作為填充值（對(duì)應(yīng)專業(yè)代碼行所在的專業(yè)名稱及專職輔導(dǎo)員）。Excel提供了VLOOKUP函數(shù)來(lái)實(shí)現(xiàn)上述功能。VLOOKUP函數(shù)可以在查找區(qū)域的第一列中查找指定的值，然后返回與該值同行的其他列的數(shù)據(jù)。

VLOOKUP函數(shù)的語(yǔ)法格式為VLOOKUP(Lookup_value,Table_arrary,Col_index_num,Range_lookup)，參數(shù)的含義分別是：1.VLOOKUP函數(shù)（1）Lookup_value：表示要填充的結(jié)果所依據(jù)的查找元素。（2）Table_arrary：被查找的元素所在的查找區(qū)域，并要求在查找區(qū)域的第1列去查找。（3）Col_index_num：找到被查找的匹配項(xiàng)后，被查找到的結(jié)果位于查找區(qū)域的第幾列。（4）Range_lookup：查找時(shí)是大致匹配查找（1或ture）還是精確匹配查找（0或false）。VLOOKUP的返回結(jié)果為要填充的元素。

根據(jù)VLOOKUP函數(shù)的語(yǔ)法格式，要填寫“陳萍萍”的錄取專業(yè)名稱，對(duì)應(yīng)的VLOOKUP參數(shù)值是：1.VLOOKUP函數(shù)（1）Lookup_value為B15，即根據(jù)“陳萍萍”的錄取專業(yè)代碼值去查找。（2）Table_arrary為A3:B8，A3:B8為查找區(qū)域，要確保所依據(jù)的查找元素（錄取專業(yè)代碼）對(duì)應(yīng)的專業(yè)代碼在查找區(qū)域的第1列。（3）Col_index_num為2，表示找到對(duì)應(yīng)的匹配項(xiàng)后，返回A3:B8區(qū)域中找到的行數(shù)據(jù)中的第2列，即對(duì)應(yīng)的專業(yè)名稱值作為函數(shù)的結(jié)果。（4）Range_lookup為0，表示精確查找。

1.VLOOKUP函數(shù)因此，C15單元格對(duì)應(yīng)的公式為“=VLOOKUP(B15,A3:B8,2,0)”。由于公式復(fù)制時(shí)，查找區(qū)域A3:B8是不發(fā)生改變的，因此C15單元格對(duì)應(yīng)的最終公式為“=VLOOKUP(B15,$A$3:$B$8,2,0)”。而要填寫“陳萍萍”的輔導(dǎo)員，D15單元格對(duì)應(yīng)的公式為“=VLOOKUP(B15,$A$3:$C$8,3,0)”。也可將單元格區(qū)域A3:C8定義為“專業(yè)代碼區(qū)域”名稱，這樣，C15單元格格的公式可以寫為“=VLOOKUP(B15,專業(yè)代碼區(qū)域,2,0)”，而D15單元格對(duì)應(yīng)的公式為“=VLOOKUP(B15,專業(yè)代碼區(qū)域,3,0)”。

2.IFERROR函數(shù)如圖所示，由于“劉嘉楠”的錄取專業(yè)代碼在“專業(yè)代碼對(duì)應(yīng)表”（A3:B7）中找不到，因此填充結(jié)果為#N/A，表示公式執(zhí)行過程中出現(xiàn)了“值不可用”的錯(cuò)誤。

5.數(shù)據(jù)透視表可以使用IFERROR函數(shù)來(lái)捕獲和處理公式中的錯(cuò)誤。IFERROR函數(shù)的語(yǔ)法格式為IFERROR(value,value_if_error)，其參數(shù)的含義是：（1）Value：為可能出錯(cuò)的公式。如果公式不出錯(cuò)，就將Value公式的結(jié)果作為返回值。（2）Value_if_error：公式計(jì)算結(jié)果錯(cuò)誤時(shí)返回的值。如上例所示，可以將C15單元格的公式修改為“=IFERROR(VLOOKUP(B15,$A$3:$B$7,2,0),"無(wú)此專業(yè)代碼")”來(lái)解決專業(yè)代碼不存在的情況。這樣，在進(jìn)行公式復(fù)制后，“劉嘉楠”的錄取專業(yè)名稱顯示為“無(wú)此專業(yè)代碼”。感謝聆聽！大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）6.3.2任務(wù)實(shí)現(xiàn)第6章Excel綜合應(yīng)用6.3獎(jiǎng)學(xué)金評(píng)定（1）確定其級(jí)別身份為“主要干部”、“一般干部”還是“無(wú)”。如圖所示。（2）根據(jù)獎(jiǎng)學(xué)金條件限制的單科課程績(jī)點(diǎn)要求，分別統(tǒng)計(jì)單科課程績(jī)點(diǎn)>=2.5的門次，單科課程績(jī)點(diǎn)∈[2.4,2.5)的門次以及單科課程績(jī)點(diǎn)∈[2.2,2.5)的門次。1.任務(wù)分析

要計(jì)算某學(xué)生是否獲得獎(jiǎng)學(xué)金，需要：

（3）根據(jù)級(jí)別身份，計(jì)算每個(gè)學(xué)生達(dá)到獎(jiǎng)學(xué)金要求的課程門次，如表所示。1.任務(wù)分析要計(jì)算某學(xué)生是否獲得獎(jiǎng)學(xué)金，需要：表6-1達(dá)到獎(jiǎng)學(xué)金要求的課程門次級(jí)別身份達(dá)到獎(jiǎng)學(xué)金要求的課程門次主要干部單科課程績(jī)點(diǎn)>=2.5的門次+最多2門單科課程績(jī)點(diǎn)∈[2.2,2.5)一般干部單科課程績(jī)點(diǎn)>=2.5的門次+最多2門單科課程績(jī)點(diǎn)∈[2.4,2.5)無(wú)單科課程績(jī)點(diǎn)>=2.5的門次

（4）獎(jiǎng)學(xué)金資格判定：如果達(dá)到獎(jiǎng)學(xué)金要求的課程門次=修讀課程門次，則表明其有評(píng)選獎(jiǎng)學(xué)金資格，否則就沒有評(píng)選獎(jiǎng)學(xué)金資格。（5）篩選出有獎(jiǎng)學(xué)金資格的名單，并對(duì)名單按平均課程績(jī)點(diǎn)排序，當(dāng)平均課程績(jī)點(diǎn)相同時(shí)，按德育績(jī)點(diǎn)排序。（6）計(jì)算獎(jiǎng)學(xué)金名額。一等獎(jiǎng)學(xué)金名額不超過專業(yè)總?cè)藬?shù)2％（含，下同），二等獎(jiǎng)學(xué)金名額不超過專業(yè)總?cè)藬?shù)5％，三等獎(jiǎng)學(xué)金名額不超過專業(yè)總?cè)藬?shù)8％，人數(shù)出現(xiàn)小數(shù)的，采用去尾法計(jì)算。（7）按給定名額數(shù)量，對(duì)符合獎(jiǎng)學(xué)金的學(xué)生進(jìn)行評(píng)定。（8）確定最終獎(jiǎng)學(xué)金名單，并給出按姓名查詢?cè)敿?xì)情況。1.任務(wù)分析

在相關(guān)素材中，“職務(wù)級(jí)別”工作表存放的是如右上圖所示的職務(wù)與級(jí)別的對(duì)應(yīng)關(guān)系，“任職一覽表”工作表存放的是如左上圖所示的所有擔(dān)任學(xué)生干部的名單，“獎(jiǎng)學(xué)金計(jì)算”工作表存放的是如右下圖所示的篩選出來(lái)的可能符合獎(jiǎng)學(xué)金評(píng)選條件的名單。因此，新建“獎(jiǎng)學(xué)金評(píng)定.xlsx”工作簿，將上述3張工作表復(fù)制到該工作簿中。2.實(shí)現(xiàn)過程1）數(shù)據(jù)準(zhǔn)備根據(jù)“職務(wù)級(jí)別”工作表的對(duì)應(yīng)關(guān)系，將“任職一覽表”工作表中學(xué)生的擔(dān)任職務(wù)轉(zhuǎn)換為對(duì)應(yīng)的職務(wù)級(jí)別。要得到“陳俊曄”同學(xué)的職務(wù)級(jí)別（D3），就需要根據(jù)他的“擔(dān)任職務(wù)”（C3單元格），在“職務(wù)級(jí)別”工作表中的!$A$2:$B$18單元格區(qū)域去查找。操作步驟如下：

2.實(shí)現(xiàn)過程2）確定擔(dān)任職務(wù)的對(duì)應(yīng)級(jí)別①選擇“任職一覽表”工作表的D3單元格，單擊“公式”選項(xiàng)卡/“函數(shù)庫(kù)”組/“查找與引用”按鈕的VLOOKUP函數(shù)，彈出如圖所示的“函數(shù)參數(shù)”對(duì)話框。

2.實(shí)現(xiàn)過程2）確定擔(dān)任職務(wù)的對(duì)應(yīng)級(jí)別②在“函數(shù)參數(shù)”對(duì)話框中，選擇C3作為L(zhǎng)ookup_value參數(shù)值，選擇職務(wù)級(jí)別!$A$2:$B$18作為Table_array參數(shù)值，Col_index_num設(shè)置為2，Range_lookup是指為0。對(duì)應(yīng)的公式為“=VLOOKUP(C3,職務(wù)級(jí)別!$A$2:$B$18,2,0)”，其含義是：根據(jù)當(dāng)前C3單元格的值，去單元格區(qū)域!$A$2:$B$18的第一列查找，如果找到匹配項(xiàng)，則將單元格區(qū)域!$A$2:$B$18的對(duì)應(yīng)匹配項(xiàng)所在行的第二列的值填充到當(dāng)前位置。③選擇D3單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成黑色填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成函數(shù)及公式的復(fù)制?？梢酝ㄟ^在“獎(jiǎng)學(xué)金計(jì)算”工作表運(yùn)用VLOOKUP函數(shù)，將“任職一覽表”中的相應(yīng)數(shù)據(jù)查找出來(lái)。操作步驟如下：

2.實(shí)現(xiàn)過程3）確定獎(jiǎng)學(xué)金名單的級(jí)別身份①如圖所示，選擇“任職一覽表”工作表的A3:D57單元格區(qū)域，單擊“公式”選項(xiàng)卡/“定義的名稱”組的“定義名稱”按鈕，在彈出的“新建名稱”對(duì)話框中，將其命名為“職務(wù)信息”。這樣，需要引用“任職一覽表”工作表A3:D57單元格區(qū)域的地方都可以用“職務(wù)信息”名稱來(lái)替代。

2.實(shí)現(xiàn)過程3）確定獎(jiǎng)學(xué)金名單的級(jí)別身份③如圖所示，將鼠標(biāo)定位在Table_array參數(shù)區(qū)域，單擊“公式”選項(xiàng)卡/“定義的名稱”組/“用于公式”的“職務(wù)信息”，將名稱“職務(wù)信息”作為Table_array參數(shù)值。對(duì)應(yīng)的公式為“=VLOOKUP(A4,職務(wù)信息,4,0)”，結(jié)果顯示為“#N/A”。②選擇“獎(jiǎng)學(xué)金計(jì)算”工作表的C4單元格，單擊“公式”選項(xiàng)卡/“函數(shù)庫(kù)”組/“查找與引用”的VLOOKUP函數(shù)，選擇A4作為L(zhǎng)ookup_value參數(shù)值，Col_index_num設(shè)置為4，Range_lookup是指為0。

2.實(shí)現(xiàn)過程3）確定獎(jiǎng)學(xué)金名單的級(jí)別身份⑤選擇C4單元格，單擊“公式”選項(xiàng)卡/“函數(shù)庫(kù)”組/“邏輯”的IFERROR函數(shù)。在如圖所示的對(duì)話框中，Value值為剪切的內(nèi)容（“VLOOKUP(A4,職務(wù)信息,4,0)”），Value_if_error值為“無(wú)”。對(duì)應(yīng)的公式為“=IFERROR(VLOOKUP(A4,職務(wù)信息,4,0),"無(wú)")”，結(jié)果顯示為“無(wú)”。⑥選擇C4單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成黑色填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成函數(shù)及公式的復(fù)制。④將C4單元格編輯欄區(qū)域中“=”之后的內(nèi)容剪切下來(lái)。根據(jù)獎(jiǎng)學(xué)金條件限制的單科課程績(jī)點(diǎn)要求，分別統(tǒng)計(jì)單科課程績(jī)點(diǎn)>=2.5的門次，單科課程績(jī)點(diǎn)∈[2.4,2.5)的門次以及單科課程績(jī)點(diǎn)∈[2.2,2.5)的門次，可以采用COUNTIF及COUNTIFS函數(shù)來(lái)實(shí)現(xiàn)。操作步驟如下：

2.實(shí)現(xiàn)過程4）統(tǒng)計(jì)單科課程績(jī)點(diǎn)范圍①K4單元格的公式為“=COUNTIF(E4:I4,">=2.5")”。②L4單元格的公式為“=COUNTIFS(E4:I4,"<2.5",E4:I4,">=2.4")”。③M4單元格的公式為“=COUNTIFS(E4:I4,"<2.5",E4:I4,">=2.2")”。④分別選擇K4、L4及M4單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成黑色填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成函數(shù)及公式的復(fù)制。根據(jù)級(jí)別身份，計(jì)算每個(gè)學(xué)生達(dá)到獎(jiǎng)學(xué)金要求的課程門次。由于“主要干部”最多可以有2門單科課程績(jī)點(diǎn)∈[2.2,2.5)，“一般干部”最多可以有2門單科課程績(jī)點(diǎn)∈[2.4,2.5)，因此可以用IF函數(shù)來(lái)對(duì)不同級(jí)別身份身份進(jìn)行判斷，而MIN函數(shù)則可以求出最多可以有2門單科課程績(jī)點(diǎn)∈[2.2,2.5)和最多可以有2門單科課程績(jī)點(diǎn)∈[2.4,2.5)，其邏輯表達(dá)如表所示。操作步驟如下：

2.實(shí)現(xiàn)過程5）符合獎(jiǎng)學(xué)金課程門次計(jì)算表6-2達(dá)到獎(jiǎng)學(xué)金要求的課程門次計(jì)算邏輯級(jí)別身份達(dá)到獎(jiǎng)學(xué)金要求的課程門次主要干部單科課程績(jī)點(diǎn)>=2.5的門次+MIN(單科課程績(jī)點(diǎn)∈[2.2,2.5)的門次,2)一般干部單科課程績(jī)點(diǎn)>=2.5的門次+MIN(單科課程績(jī)點(diǎn)∈[2.4,2.5)的門次,2)無(wú)單科課程績(jī)點(diǎn)>=2.5的門次

2.實(shí)現(xiàn)過程5）符合獎(jiǎng)學(xué)金課程門次計(jì)算①選擇N4單元格，插入IF函數(shù)，IF函數(shù)的Logical_test的值為“C4="主要干部"”，鼠標(biāo)定位在Value_if_true區(qū)域，選擇K4單元格，然后輸入“+”，在名稱框選擇需要嵌入的MIN函數(shù)，如圖所示。

2.實(shí)現(xiàn)過程5）符合獎(jiǎng)學(xué)金課程門次計(jì)算②在如圖所示的MIN函數(shù)的對(duì)話框中，Number1區(qū)域選擇M4單元格，Number2的值輸入2。

2.實(shí)現(xiàn)過程5）符合獎(jiǎng)學(xué)金課程門次計(jì)算③單擊確定后，鼠標(biāo)定位在N4單元格編輯區(qū)域的IF中間位置，單擊編輯欄前的“插入函數(shù)”圖標(biāo)，彈出IF函數(shù)對(duì)話框。在Value_if_false的輸入框中，在名稱框選擇需要嵌入的IF函數(shù)，IF函數(shù)的參數(shù)分別為：Logjcal_test的值為“C4="一般干部"”，Value_if_true的值為“K4+MIN(L4,2)”，Value_if_false的值為“K4”。N4單元格的最后公式為“=IF(C4="主要干部",K4+MIN(M4,2),IF(C4="一般干部",K4+MIN(L4,2),K4))”，結(jié)果顯示為5。④選擇N4單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成黑色填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成函數(shù)及公式的復(fù)制。如果達(dá)到獎(jiǎng)學(xué)金的課程門次與本學(xué)期所學(xué)的課程門次相等，就表示該學(xué)生具備獎(jiǎng)學(xué)金資格。因此O4單元格可以根據(jù)N4單元格的值是否與課程總門次（B2）是否相等，來(lái)填寫是否具備獎(jiǎng)學(xué)金資格。需要注意的是，由于課程總門次（B2）不隨公式的位置變化而發(fā)生變化，因此，需要使用絕對(duì)地址。操作步驟如下：

2.實(shí)現(xiàn)過程6）獎(jiǎng)學(xué)金資格判定①選擇O4單元格，選擇IF函數(shù)并填寫相應(yīng)參數(shù)。O4的公式為“=IF(N4=$B$2,"是","否")”，結(jié)果顯示為“是”。②選擇O4單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成黑色填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成函數(shù)及公式的復(fù)制。將有獎(jiǎng)學(xué)金資格的學(xué)生名單篩選出來(lái)，并對(duì)名單按平均課程績(jī)點(diǎn)排序，當(dāng)平均課程績(jī)點(diǎn)相同時(shí)，按德育績(jī)點(diǎn)排序。操作步驟如下：

2.實(shí)現(xiàn)過程7）篩選和排序符合獎(jiǎng)學(xué)金名單①選擇A3:P92單元格區(qū)域，單擊“開始”選項(xiàng)卡/“編輯”組/“排序和篩選”按鈕的“篩選”。②單擊O3單元格的篩選下三角，在彈出的菜單中只勾選“是”。狀態(tài)欄顯示“在89條記錄中找到69個(gè)”，表明有69個(gè)學(xué)生具備獎(jiǎng)學(xué)金資格。③鼠標(biāo)定位在“平均課程績(jī)點(diǎn)”列的任一單元格，單擊“開始”選項(xiàng)卡/“編輯”組/“排序和篩選”按鈕的“降序”，J3單元格旁就出現(xiàn)了，表明數(shù)據(jù)就按照平均課程績(jī)點(diǎn)的大小進(jìn)行了降序排序。④鼠標(biāo)定位在當(dāng)前工作表的任一位置，單擊“開始”選項(xiàng)卡/“編輯”組/“排序和篩選”按鈕的“自定義排序”，在彈出的“排序”對(duì)話框中添加如圖所示的條件。

2.實(shí)現(xiàn)過程7）篩選和排序符合獎(jiǎng)學(xué)金名單根據(jù)獎(jiǎng)學(xué)金評(píng)定規(guī)則，首先要確定每個(gè)等級(jí)的獲獎(jiǎng)人數(shù)：一等獎(jiǎng)學(xué)金名額不超過專業(yè)總?cè)藬?shù)2％（含，下同），二等獎(jiǎng)學(xué)金名額不超過專業(yè)總?cè)藬?shù)5％，三等獎(jiǎng)學(xué)金名額不超過專業(yè)總?cè)藬?shù)8％，人數(shù)出現(xiàn)小數(shù)的，采用去尾法計(jì)算。操作步驟如下：

2.實(shí)現(xiàn)過程8）獎(jiǎng)學(xué)金名額確定①將相關(guān)素材的“獎(jiǎng)學(xué)金名額”工作表復(fù)制到“獎(jiǎng)學(xué)金評(píng)定.xlsx”工作簿中。②計(jì)算每個(gè)等級(jí)的獲獎(jiǎng)人數(shù)，由于人數(shù)出現(xiàn)小數(shù)的，要采用去尾法計(jì)算，因此，可以使用ROUNDDOWM函數(shù)來(lái)實(shí)現(xiàn)。D2單元格的公式為“=ROUNDDOWN($B$1*B2,0)”，結(jié)果為4。③選擇D2單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成黑色填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成函數(shù)及公式的復(fù)制。計(jì)算得到一等獎(jiǎng)4名，二等獎(jiǎng)10名，三等獎(jiǎng)16名。要根據(jù)獎(jiǎng)學(xué)金名額來(lái)評(píng)定獎(jiǎng)學(xué)金，還需要考慮如下規(guī)則：當(dāng)平均課程績(jī)點(diǎn)和德育績(jī)點(diǎn)相同導(dǎo)致獎(jiǎng)學(xué)金等級(jí)不同時(shí)，獎(jiǎng)學(xué)金等級(jí)按最高等級(jí)計(jì)算，下一等級(jí)獎(jiǎng)學(xué)金名額相應(yīng)減少。如果平均課程績(jī)點(diǎn)和德育績(jī)點(diǎn)相同導(dǎo)致最后一個(gè)等級(jí)名額不夠時(shí)，則自動(dòng)擴(kuò)充獎(jiǎng)學(xué)金。操作步驟如下：

2.實(shí)現(xiàn)過程9）獎(jiǎng)學(xué)金評(píng)定①單擊“獎(jiǎng)學(xué)金計(jì)算”工作表的J3單元格的篩選按鈕，在彈出的菜單中選擇“數(shù)字篩選”的“10個(gè)最大的值”，如圖所示。②在彈出的“自動(dòng)篩選前10個(gè)”對(duì)話框中選擇顯示4項(xiàng)，如圖所示。

2.實(shí)現(xiàn)過程9）獎(jiǎng)學(xué)金評(píng)定③狀態(tài)欄顯示“在89個(gè)記錄中找到4個(gè)”，將其獎(jiǎng)學(xué)金區(qū)域填充為“一等獎(jiǎng)”。④單擊J3單元格的篩選按鈕，篩選出前14項(xiàng)（一、二等獎(jiǎng)的人數(shù)），將其獎(jiǎng)學(xué)金區(qū)域?yàn)榭盏?0個(gè)單元格填寫為“二等獎(jiǎng)”。

2.實(shí)現(xiàn)過程9）獎(jiǎng)學(xué)金評(píng)定⑤單擊J3單元格的篩選按鈕，篩選出前30項(xiàng)（一、二、三等獎(jiǎng)的人數(shù)），狀態(tài)欄顯示“在89個(gè)記錄中找到31個(gè)”。觀察最后的數(shù)據(jù)，發(fā)現(xiàn)第30和第31個(gè)數(shù)據(jù)的平均課程績(jī)點(diǎn)和德育績(jī)點(diǎn)相同，因此符合三等獎(jiǎng)的學(xué)生有17名，將其獎(jiǎng)學(xué)金區(qū)域?yàn)榭盏?7個(gè)單元格填寫為“三等獎(jiǎng)”。⑥選擇整個(gè)工作表，將其內(nèi)容復(fù)制，粘貼到一個(gè)新的工作表，命名為“獎(jiǎng)學(xué)金最終名單”。需要按獎(jiǎng)學(xué)金最終名單數(shù)據(jù)，按“姓名”查詢每個(gè)學(xué)生的詳細(xì)情況。由于查詢的姓名是獎(jiǎng)學(xué)金最終名單的學(xué)生，因此可以用數(shù)據(jù)有效性來(lái)進(jìn)行姓名限制。而要查詢到其他的具體情況，可以根據(jù)姓名去查詢獎(jiǎng)學(xué)金最終名單中對(duì)應(yīng)的數(shù)據(jù)，可以使用VLOOKUP來(lái)實(shí)現(xiàn)。操作步驟如下：

2.實(shí)現(xiàn)過程10）獎(jiǎng)學(xué)金名單查詢①將相關(guān)素材中的“獎(jiǎng)學(xué)金名單查詢”工作表復(fù)制到“獎(jiǎng)學(xué)金評(píng)定.xlsx”工作簿中。②選擇“獎(jiǎng)學(xué)金最終名單”工作表的A4:P34單元格區(qū)域，將其名稱定義為“獎(jiǎng)學(xué)金名單”。③選擇“獎(jiǎng)學(xué)金名單查詢”的B2單元格，將其數(shù)據(jù)有效性的條件設(shè)置為允許“序列”，來(lái)源于“=獎(jiǎng)學(xué)金最終名單!$A$4:$A$34”。④選擇B3單元格，如圖所示，定義其公式為“=VLOOKUP($B$2,獎(jiǎng)學(xué)金名單,2,0)”，表示根據(jù)B2單元格值，去查找“獎(jiǎng)學(xué)金名單”名稱區(qū)域，找到后將其第2列數(shù)據(jù)返回。需要注意的是，由于B3:B12單元格都需要根據(jù)姓名（B2單元格的值）去查找，因此將B2定義為絕對(duì)地址$B$2。

2.實(shí)現(xiàn)過程10）獎(jiǎng)學(xué)金名單查詢⑤選擇B3單元格，動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成黑色填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成函數(shù)及公式的復(fù)制。⑥選擇B4單元格，修改公式中的參數(shù)，返回?cái)?shù)據(jù)為第3列，對(duì)應(yīng)公式為“=VLOOKUP($B$2,獎(jiǎng)學(xué)金名單,3,0)”。⑦按照對(duì)應(yīng)關(guān)系，分別選擇B5:B12單元格，修改公式中的參數(shù)。感謝聆聽！大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）6.3.3總結(jié)與提高第6章Excel綜合應(yīng)用6.3獎(jiǎng)學(xué)金評(píng)定1.VLOOKUP函數(shù)

如圖所示，值得注意的是，如果VLOOKUP函數(shù)的查找區(qū)域中第一列中有多個(gè)相同的值，VLOOKUP函數(shù)只能返回與查找值相同的第1條數(shù)據(jù)所對(duì)應(yīng)的其他列的值。VLOOKUP函數(shù)的參數(shù)Range_lookup指明在查找時(shí)是按大致匹配查找（1或ture）還是精確匹配查找（0或false）。1.VLOOKUP函數(shù)

如圖所示，如果將C22的公式變更為“=VLOOKUP(B22,$A$3:$B$7,2,1)”，采取大致匹配查找法進(jìn)行查找，就出現(xiàn)了與圖6-33截然不同的結(jié)果。這是由于在查找區(qū)域$A$3:$B$7查找“1306”時(shí)，沒有找到“1306”，因此將與其最接近的“1305”作為查找到的匹配元素，因此將“1305”所對(duì)應(yīng)的“電子信息工程”當(dāng)作了返回結(jié)果。1.VLOOKUP函數(shù)

大致匹配查找通常用作如圖所示的區(qū)間范圍查找的情況。需要注意的是，大致匹配查找是將<=Lookup_value的最接近的值作為查找到的匹配項(xiàng)（如與8000最接近，又小于等于8000的數(shù)字為0，因此返回的是0所對(duì)應(yīng)的提成比例0.01）。2.HLOOKUP函數(shù)

HLOOKUP函數(shù)與VLOOKUP相似，都是用來(lái)查找數(shù)據(jù)。其不同點(diǎn)是VLOOKUP函數(shù)在查找區(qū)域中第1列找到匹配項(xiàng)后，將匹配項(xiàng)所在行對(duì)應(yīng)的某列的值作為結(jié)果返回。而HLOOKUP函數(shù)則是在查找區(qū)域中第1行找到匹配項(xiàng)后，將匹配項(xiàng)所在行對(duì)應(yīng)的某行的值作為結(jié)果返回。其應(yīng)用示例如圖所示。其中，C11單元格的公式為“=HLOOKUP(B11,$B$2:$G$4,2,0)”。3.名稱管理

如果需要對(duì)工作簿中已經(jīng)定義的名稱進(jìn)行查看、編輯或者刪除等操作，可以通過“公式”選項(xiàng)卡/“定義的名稱”組/“名稱管理器”按鈕，打開“名稱管理器”對(duì)話框來(lái)進(jìn)行操作。感謝聆聽！大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）6.4.1知識(shí)點(diǎn)解析第6章Excel綜合應(yīng)用6.4獎(jiǎng)學(xué)金統(tǒng)計(jì)1.批注

通過批注，可以對(duì)單元格的內(nèi)容添加注釋或者說明。如圖所示，通過對(duì)“小計(jì)”添加批注，可以清晰知道小計(jì)金額的由來(lái)。由于批注類似于Word中的文本框，因此，可以通過設(shè)置批注的填充效果為圖片，而讓“活頁(yè)夾”添加圖片批注。2.SUMIF函數(shù)

（1）確定要過濾的單元格區(qū)域。（2）將“北京”地區(qū)的所有數(shù)據(jù)過濾出來(lái)。（3）在過濾出來(lái)的數(shù)據(jù)中，找到對(duì)應(yīng)的游戲積分區(qū)域。（4）對(duì)找到的對(duì)應(yīng)游戲積分區(qū)域求和。如圖所示，要統(tǒng)計(jì)“北京”地區(qū)的游戲總積分，需要：SUMIF(Range,Criteria,Sum_range)2.SUMIF函數(shù)

而要實(shí)現(xiàn)上述功能的自動(dòng)計(jì)算，可以采用SUMIF函數(shù)來(lái)完成。如圖所示，要統(tǒng)計(jì)“北京”地區(qū)的游戲總積分，需要：SUMIF函數(shù)的語(yǔ)法格式為其中，Range為要過濾的單元格區(qū)域（即第1步中確定的單元格區(qū)域），Criteria為指定的條件（即第2步中給定的條件），Sum_range表示需要對(duì)哪些單元格求和（即第3步中找到的單元格區(qū)域）。SUMIF函數(shù)將得到符合條件的和（即第4步中的對(duì)應(yīng)單元格區(qū)域的求和）。對(duì)應(yīng)的公式為“=SUMIF(B3:B10,"北京",C3:C10)”。其含義是：將B3:B10單元格區(qū)域中為“北京”的數(shù)據(jù)過濾出來(lái)，在過濾出來(lái)的數(shù)據(jù)中求對(duì)應(yīng)的C3:C10單元格數(shù)值的和。3.COUNTIFS函數(shù)的條件區(qū)域

如圖所示，要統(tǒng)計(jì)“北京”地區(qū)游戲積分在17000分以上（含）的人數(shù)，需要：（1）將“北京”地區(qū)的所有數(shù)據(jù)過濾出來(lái)。（2）在過濾出來(lái)的數(shù)據(jù)中，對(duì)積分>=17000的數(shù)據(jù)進(jìn)行再次過濾。（3）統(tǒng)計(jì)最后過濾出來(lái)的積分>=17000的單元格數(shù)。

因此，可以使用COUNTIFS函數(shù)來(lái)實(shí)現(xiàn)。其對(duì)應(yīng)的參數(shù)是：（1）Criteria_range1參數(shù)值為B3:B10單元格區(qū)域。（2）Criteria1參數(shù)值為“北京”。（3）Criteria_range2參數(shù)值為C3:C10單元格區(qū)域。（4）Criteria2參數(shù)值為“>=17000”。3.COUNTIFS函數(shù)的條件區(qū)域公式為“=COUNTIFS(B3:B10,"北京",C3:C10,">=17000")”。其含義是：將B3:B10單元格區(qū)域中為“北京”的數(shù)據(jù)過濾出來(lái)，在過濾出來(lái)的數(shù)據(jù)中，再將C3:C10單元格區(qū)域中>=170000的數(shù)據(jù)過濾出來(lái)，在得到的數(shù)據(jù)區(qū)域中統(tǒng)計(jì)C3:C10單元格區(qū)域的單元格個(gè)數(shù)。感謝聆聽！大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）大學(xué)計(jì)算機(jī)基礎(chǔ)——基于計(jì)算思維（Windows10+Office2016）6.4.2任務(wù)實(shí)現(xiàn)第6章Excel綜合應(yīng)用6.4獎(jiǎng)學(xué)金統(tǒng)計(jì)1.任務(wù)分析

（5）用圖表展示每個(gè)班的各等級(jí)獎(jiǎng)學(xué)金人數(shù)，如圖所示。輔導(dǎo)員李老師現(xiàn)在要統(tǒng)計(jì)獎(jiǎng)學(xué)金獲獎(jiǎng)情況，包括：2.實(shí)現(xiàn)過程

1）數(shù)據(jù)準(zhǔn)備在相關(guān)素材中，“獎(jiǎng)學(xué)金名單”工作表存放的是所有獲得獎(jiǎng)學(xué)金的名單，“獎(jiǎng)學(xué)金標(biāo)準(zhǔn)”工作表存放的是各等級(jí)獎(jiǎng)學(xué)金對(duì)應(yīng)的獎(jiǎng)學(xué)金金額。因此，新建“獎(jiǎng)學(xué)金統(tǒng)計(jì).xlsx”工作簿，將上述2張工作表復(fù)制到該工作簿中，并將“獎(jiǎng)學(xué)金名單”工作表重命名為“獎(jiǎng)學(xué)金發(fā)放統(tǒng)計(jì)”。要填寫每個(gè)學(xué)生的獎(jiǎng)學(xué)金數(shù)額，需要根據(jù)每個(gè)人的獲獎(jiǎng)等級(jí)，查找到對(duì)應(yīng)的獎(jiǎng)學(xué)金標(biāo)準(zhǔn)，因此可以用VLOOKUP函數(shù)來(lái)實(shí)現(xiàn)。操作步驟如下：

2）計(jì)算學(xué)生應(yīng)發(fā)獎(jiǎng)學(xué)金①選擇“獎(jiǎng)學(xué)金發(fā)放統(tǒng)計(jì)”工作表的E3單元格，單擊“公式”選項(xiàng)卡/“函數(shù)庫(kù)”組/“查找與引用”的VLOOKUP函數(shù)。②在“函數(shù)參數(shù)”對(duì)話框中，選擇D3作為L(zhǎng)ookup_value參數(shù)值，選擇“獎(jiǎng)學(xué)金標(biāo)準(zhǔn)!$A$2:$B$4”作為Table_array參數(shù)值，Col_index_num設(shè)置為2，Range_lookup是指為0。對(duì)應(yīng)的公式為“=VLOOKUP(D3,獎(jiǎng)學(xué)金標(biāo)準(zhǔn)!$A$2:$B$4,2,0)”。③選擇E3單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成黑色填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成函數(shù)及公式的復(fù)制。2.實(shí)現(xiàn)過程要從獎(jiǎng)學(xué)金學(xué)生名單的班級(jí)信息（B2:B33單元格區(qū)域）而得到每個(gè)獲獎(jiǎng)班級(jí)的班級(jí)名稱，實(shí)際上可以認(rèn)為是從有重復(fù)數(shù)據(jù)的序列中去掉重復(fù)數(shù)據(jù)，可以使用數(shù)據(jù)篩選的高級(jí)篩選功能來(lái)實(shí)現(xiàn)。操作步驟如下：

3）填充班級(jí)名稱①選擇B2:B33單元格區(qū)域。②單擊“數(shù)據(jù)”選項(xiàng)卡/“排序和篩選”組/“高級(jí)”按鈕，彈出如圖所示的“高級(jí)篩選”對(duì)話框。2.實(shí)現(xiàn)過程要從獎(jiǎng)學(xué)金學(xué)生名單的班級(jí)信息（B2:B33單元格區(qū)域）而得到每個(gè)獲獎(jiǎng)班級(jí)的班級(jí)名稱，實(shí)際上可以認(rèn)為是從有重復(fù)數(shù)據(jù)的序列中去掉重復(fù)數(shù)據(jù)，可以使用數(shù)據(jù)篩選的高級(jí)篩選功能來(lái)實(shí)現(xiàn)。操作步驟如下：

3）填充班級(jí)名稱③在“高級(jí)篩選”對(duì)話框中，選擇方式為“將篩選結(jié)果復(fù)制到其他位置”，復(fù)制到$G$3，并勾選“選擇不重復(fù)的記錄”。這樣，就從B2:B33單元格區(qū)域篩選出了獲獎(jiǎng)學(xué)生的對(duì)應(yīng)班級(jí)名稱。④選擇G3:G8單元格區(qū)域，將其復(fù)制到G12:G17單元格區(qū)域。2.實(shí)現(xiàn)過程為H3單元格添加“按班級(jí)統(tǒng)計(jì)獎(jiǎng)學(xué)金總額”批注，為G12單元格添加“按班統(tǒng)計(jì)各等級(jí)獎(jiǎng)學(xué)金獲獎(jiǎng)人數(shù)”批注。操作步驟如下：

4）添加批注①如圖所示，選擇H3單元格，單擊“審閱”選項(xiàng)卡/“批注”組/“新建批注”按鈕，在對(duì)應(yīng)的批注框中輸入“按班級(jí)統(tǒng)計(jì)獎(jiǎng)學(xué)金總額”。此時(shí)，“新建批注”按鈕變成“編輯批注”。2.實(shí)現(xiàn)過程為H3單元格添加“按班級(jí)統(tǒng)計(jì)獎(jiǎng)學(xué)金總額”批注，為G12單元格添加“按班統(tǒng)計(jì)各等級(jí)獎(jiǎng)學(xué)金獲獎(jiǎng)人數(shù)”批注。操作步驟如下：

4）添加批注②選擇“按班級(jí)統(tǒng)計(jì)獎(jiǎng)學(xué)金總額”文本，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“設(shè)置批注格式”將其設(shè)置為紅色，加粗顯示。③調(diào)整批注欄到合適大小。④單擊“審閱”選項(xiàng)卡/“批注”組/“顯示所有批注”按鈕，將批注顯示在屏幕上。⑤為G12單元格添加“按班統(tǒng)計(jì)各等級(jí)獎(jiǎng)學(xué)金獲獎(jiǎng)人數(shù)”批注。2.實(shí)現(xiàn)過程要統(tǒng)計(jì)每個(gè)班的獎(jiǎng)學(xué)金總額，實(shí)際上是對(duì)獲獎(jiǎng)名單按“班級(jí)”來(lái)對(duì)“應(yīng)發(fā)獎(jiǎng)學(xué)金”進(jìn)行求和。例如，要統(tǒng)計(jì)“13電子1”的獎(jiǎng)學(xué)金總額，就是要將獲獎(jiǎng)名單中屬于“13電子1”的數(shù)據(jù)過濾出來(lái)，然后將過濾出來(lái)的“應(yīng)發(fā)獎(jiǎng)學(xué)金”相加即可。上述要求可以使用SUMIF函數(shù)。操作步驟如下：

5）統(tǒng)計(jì)每個(gè)班的獎(jiǎng)學(xué)金總額2.實(shí)現(xiàn)過程①選擇H4單元格，單擊單元格編輯欄前的插入函數(shù)按鈕，通過查找，找到并插入SUMIF函數(shù)，彈出如圖所示的“函數(shù)參數(shù)”對(duì)話框。

5）統(tǒng)計(jì)每個(gè)班的獎(jiǎng)學(xué)金總額2.實(shí)現(xiàn)過程②選擇B3:B33單元格區(qū)域作為Range參數(shù)值，G4單元格作為Criteria參數(shù)值，E3:E33單元格區(qū)域作為Sum_range參數(shù)。其含義是：將B3:B33單元格區(qū)域中等于G4單元格內(nèi)容的行過濾出來(lái)，將過濾得到的行所對(duì)應(yīng)的E列單元格區(qū)域中的值相加作為結(jié)果。由于在公式復(fù)制時(shí)，B3:B33單元格區(qū)域和E3:E33單元格區(qū)域都是不應(yīng)該變化的，因此將其轉(zhuǎn)化為絕對(duì)引用。H4單元格的最終公式為“=SUMIF($B$3:$B$33,G4,$E$3:$E$33)”。③選擇H4單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成黑色填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成函數(shù)及公式的復(fù)制。要統(tǒng)計(jì)每個(gè)班獎(jiǎng)學(xué)金各等級(jí)的人數(shù)，實(shí)際上是對(duì)獲獎(jiǎng)名單按“班級(jí)”及“獎(jiǎng)學(xué)金資格”來(lái)匯總對(duì)應(yīng)的單元格個(gè)數(shù)。例如，要統(tǒng)計(jì)“13電子1”獲得“一等獎(jiǎng)”的人數(shù)，就是要將獲獎(jiǎng)名單中屬于“13電子1”的數(shù)據(jù)過濾出來(lái)，然后在過濾出來(lái)的數(shù)據(jù)中再用“一等獎(jiǎng)”進(jìn)行過濾，最后統(tǒng)計(jì)過濾出來(lái)的“一等獎(jiǎng)”單元格數(shù)。上述要求可以使用COUNTIFS函數(shù)。操作步驟如下：

6）統(tǒng)計(jì)每個(gè)班獎(jiǎng)學(xué)金各等級(jí)的人數(shù)2.實(shí)現(xiàn)過程①選擇H13單元格，單擊“公式”選項(xiàng)卡/“函數(shù)庫(kù)”組/“其他函數(shù)”按鈕的“統(tǒng)計(jì)”下面的COUNTIFS函數(shù)，彈出如圖所示的“函數(shù)參數(shù)”對(duì)話框。

6）統(tǒng)計(jì)每個(gè)班獎(jiǎng)學(xué)金各等級(jí)的人數(shù)2.實(shí)現(xiàn)過程②選擇$B$3:$B$33單元格區(qū)域作為Criteria_range1參數(shù)值，選擇G13作為Criteria1參數(shù)值，選擇$D$3:$D$33單元格區(qū)域作為Criteria_range2參數(shù)值，輸入“一等獎(jiǎng)”作為Criteria2參數(shù)值，其含義是：將$B$3:$B$33單元格區(qū)域中等于G13單元格內(nèi)容的數(shù)據(jù)過濾出來(lái)，將過濾得到數(shù)據(jù)中$D$3:$D$33單元格區(qū)域中等于“一等獎(jiǎng)”的數(shù)據(jù)再過濾出來(lái)，最后統(tǒng)計(jì)過濾出來(lái)的$D$3:$D$33單元格區(qū)域中的單元格數(shù)。H13單元格的最終公式為“=COUNTIFS($B$3:$B$33,G13,$D$3:$D$33,"一等獎(jiǎng)")”。

6）統(tǒng)計(jì)每個(gè)班獎(jiǎng)學(xué)金各等級(jí)的人數(shù)2.實(shí)現(xiàn)過程③選擇H13單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成黑色填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成函數(shù)及公式的復(fù)制。④I13單元格的最終公式為“=COUNTIFS($B$3:$B$33,G13,$D$3:$D$33,"二等獎(jiǎng)")”。⑤選擇I13單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成黑色填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成函數(shù)及公式的復(fù)制。⑥J13單元格的最終公式為“=COUNTIFS($B$3:$B$33,G13,$D$3:$D$33,"三等獎(jiǎng)")”。⑦選擇J13單元格，移動(dòng)鼠標(biāo)，當(dāng)鼠標(biāo)變成黑色填充柄時(shí)，雙擊鼠標(biāo)左鍵，完成函數(shù)及公式的復(fù)制。要制作如圖所示的各班級(jí)獎(jiǎng)學(xué)金獲獎(jiǎng)情況對(duì)比圖，可以用“三維柱形圖”來(lái)實(shí)現(xiàn)。操作步驟如下：

7）制作各班級(jí)獎(jiǎng)學(xué)金獲獎(jiǎng)情況對(duì)比圖2.實(shí)現(xiàn)過程①選擇G12:J17單元格區(qū)域的任一單元格。②單擊“插入”選項(xiàng)卡/“圖表”組/“柱形圖”按鈕，在彈出的菜單中選擇“三維柱形圖”的“三維簇狀柱形圖”。③單擊“圖表工具/設(shè)計(jì)”選項(xiàng)卡/“圖表布局”組/“布局5”按鈕。④將圖表標(biāo)題改為“20