第6章EXCE綜合應(yīng)用課件

1、大學(xué)計(jì)算機(jī)基礎(chǔ)大學(xué)計(jì)算機(jī)基礎(chǔ)計(jì)算機(jī)基礎(chǔ)教學(xué)部第第5 5章章 EXCELEXCEL綜合應(yīng)用綜合應(yīng)用內(nèi)容提要項(xiàng)目分析項(xiàng)目分析12成績(jī)分析成績(jī)分析獎(jiǎng)學(xué)金評(píng)定獎(jiǎng)學(xué)金評(píng)定34獎(jiǎng)學(xué)金統(tǒng)計(jì)獎(jiǎng)學(xué)金統(tǒng)計(jì)項(xiàng)目分析（1）項(xiàng)目分析（2）成績(jī)分析知識(shí)點(diǎn)解析（1）1. 凍結(jié)窗格v對(duì)一個(gè)超寬超長(zhǎng)表格中的數(shù)據(jù)進(jìn)行操作時(shí)，當(dāng)行、列標(biāo)題行消失后，有時(shí)會(huì)記錯(cuò)各行、列標(biāo)題的相對(duì)位置。為解決該問(wèn)題，可以將行、列的標(biāo)題部分和數(shù)據(jù)區(qū)域通過(guò)“凍結(jié)窗格”視圖來(lái)實(shí)現(xiàn)將標(biāo)題部分保留在屏幕上不動(dòng)，而數(shù)據(jù)區(qū)域部分則可以滾動(dòng)。2. 數(shù)據(jù)排序v可以使用“數(shù)據(jù)排序”功能來(lái)按照某種方式來(lái)組織。“數(shù)據(jù)排序”通常是對(duì)選定的數(shù)據(jù)按照某一列或多列的升序或降序進(jìn)行排序

2、。左圖是對(duì)“2013年中國(guó)縣級(jí)城市競(jìng)爭(zhēng)力排行榜”按照“分值”降序排序數(shù)據(jù)，而右圖是按照“所在省份升序，如果省份相同，則按照分值升序” 排序數(shù)據(jù)。成績(jī)分析知識(shí)點(diǎn)解析（2）3. 數(shù)據(jù)篩選v數(shù)據(jù)篩選的目的是從一堆數(shù)據(jù)中找出想要的數(shù)據(jù)。通過(guò)數(shù)據(jù)篩選功能，可以將符合條件的數(shù)據(jù)集中顯示在工作表上，而將不符合條件的數(shù)據(jù)暫時(shí)隱藏起來(lái)。通常來(lái)說(shuō)，要對(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ū)域。 v如圖所示，既可以找出“2013年中國(guó)縣級(jí)城市競(jìng)爭(zhēng)力排行榜”中屬于“江蘇省”的所有城市，也可找出屬于“江

3、蘇省”并且分值在90分以上的所有城市。成績(jī)分析知識(shí)點(diǎn)解析（3）4. 分類匯總v如果需要對(duì)Excel工作表中的數(shù)據(jù)按類別進(jìn)行求和、計(jì)數(shù)等，可以使用分類匯總功能。所謂分類匯總，就是先對(duì)數(shù)據(jù)進(jìn)行排序（通過(guò)排序進(jìn)行分類），然后再進(jìn)行匯總。v要統(tǒng)計(jì)每個(gè)省份（稱為分類字段）入選的城市數(shù)（稱為匯總項(xiàng)及匯總方式），按“省份”進(jìn)行排序（可以是升序，也可以是降序）后，通過(guò)分類匯總（對(duì)城市名稱進(jìn)行計(jì)數(shù)），就可以統(tǒng)計(jì)出來(lái)每個(gè)省份入選的城市數(shù)。v值得注意的是，要進(jìn)行分類匯總，必須先排序，后匯總。此外，分類匯總功能在當(dāng)前數(shù)據(jù)區(qū)域插入了若干行，用于匯總數(shù)據(jù)的顯示。也就是說(shuō)，分類匯總功能改變了當(dāng)前數(shù)據(jù)區(qū)的原有結(jié)構(gòu)。成績(jī)分析知

4、識(shí)點(diǎn)解析（4）5. 數(shù)據(jù)透視表v如果需要對(duì)Excel工作表中的數(shù)據(jù)進(jìn)行深入的分析，可以使用數(shù)據(jù)透視表功能。數(shù)據(jù)透視表是基于Excel工作表中的數(shù)據(jù)而產(chǎn)生的動(dòng)態(tài)匯總表格。數(shù)據(jù)透視表功能提供了一種比分類匯總功能更強(qiáng)大的方式來(lái)分析數(shù)據(jù)。此外，數(shù)據(jù)透視表功能不會(huì)對(duì)已有數(shù)據(jù)產(chǎn)生任何改變，并可以用不同的方式來(lái)查看數(shù)據(jù)。如圖所示，通過(guò)數(shù)據(jù)透視表功能，可以得到“2013年中國(guó)縣級(jí)城市競(jìng)爭(zhēng)力排行榜”中每個(gè)省份入選的城市個(gè)數(shù)，也可以得到每個(gè)省份入選的城市個(gè)數(shù)及獲得的平均分。成績(jī)分析任務(wù)實(shí)現(xiàn)（1）1.任務(wù)分析（1）計(jì)算學(xué)生的平均課程績(jī)點(diǎn)。（2）按平均課程績(jī)點(diǎn)及德育績(jī)點(diǎn)降序排序。（3）如圖6-5所示，分類匯總每個(gè)班的

5、平均課程績(jī)點(diǎn)的平均值。（4）如圖6-6所示，分類匯總每個(gè)班的平均課程績(jī)點(diǎn)的平均值及班級(jí)人數(shù)。（5）如圖6-7所示，不改變?cè)紨?shù)據(jù)的前提下統(tǒng)計(jì)每個(gè)班的德育及平均課程績(jī)點(diǎn)平均值。（6）如圖6-8所示，不改變?cè)紨?shù)據(jù)的前提下統(tǒng)計(jì)每個(gè)班的德育績(jī)點(diǎn)的最大值和最小值。（7）如圖6-9所示，篩選出可能獲得獎(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）。成績(jī)分析任務(wù)實(shí)現(xiàn)（2）2.實(shí)現(xiàn)過(guò)程 請(qǐng)參照書本完成，有問(wèn)題請(qǐng)隨時(shí)舉手示意老師。成績(jī)分析總結(jié)與提高（1）1. 高級(jí)篩選（1）v需要將“德育績(jī)點(diǎn)4.0，或者課程平均績(jī)點(diǎn)

6、=2.88并且大學(xué)英語(yǔ)績(jī)點(diǎn)3.0以上”的數(shù)據(jù)篩選出來(lái)，就需要使用高級(jí)篩選功能。v針對(duì)高級(jí)篩選，首先需要設(shè)置篩選條件區(qū)域。篩選條件有3個(gè)特征：（1）條件的標(biāo)題要與數(shù)據(jù)表的原有標(biāo)題完全一致。（2）多字段間的條件若為“與”關(guān)系，則寫在一行。（3）多字段間的條件若為“或”關(guān)系，則寫在下一行。v要將“德育績(jī)點(diǎn)4.0，或者課程平均績(jī)點(diǎn)=3.0，表示“大學(xué)英語(yǔ)績(jī)點(diǎn)=3.0”。M4的值為=2.88，表示“平均課程績(jī)點(diǎn)=3.0并且平均課程績(jī)點(diǎn)=3.0并且平均課程績(jī)點(diǎn)=2.5的門次，單科課程績(jī)點(diǎn)2.4,2.5)的門次以及單科課程績(jī)點(diǎn)2.2,2.5)的門次。（3）根據(jù)級(jí)別身份，計(jì)算每個(gè)學(xué)生達(dá)到獎(jiǎng)學(xué)金要求的課程門次，

7、如表6-1所示。（4）獎(jiǎng)學(xué)金資格判定：如果達(dá)到獎(jiǎng)學(xué)金要求的課程門次=修讀課程門次，則表明其有評(píng)選獎(jiǎng)學(xué)金資格，否則就沒(méi)有評(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é)金名額不超過(guò)專業(yè)總?cè)藬?shù)2（含，下同），二等獎(jiǎng)學(xué)金名額不超過(guò)專業(yè)總?cè)藬?shù)5，三等獎(jiǎng)學(xué)金名額不超過(guò)專業(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ì)情況。獎(jiǎng)學(xué)金評(píng)定任務(wù)實(shí)現(xiàn)（2）2.實(shí)現(xiàn)過(guò)程 請(qǐng)參照書本完成，有問(wèn)題請(qǐng)隨時(shí)舉手示意老師。獎(jiǎng)學(xué)金評(píng)定總結(jié)與提

8、高（1）1. VLOOKUP函數(shù)（1）v 值得注意的是，如果VLOOKUP函數(shù)的查找區(qū)域中第一列中有多個(gè)相同的值，VLOOKUP函數(shù)只能返回與查找值相同的第1條數(shù)據(jù)所對(duì)應(yīng)的其他列的值。獎(jiǎng)學(xué)金評(píng)定總結(jié)與提高（2）1. VLOOKUP函數(shù)（2）vVLOOKUP函數(shù)的參數(shù)Range_lookup指明在查找時(shí)是按大致匹配查找（1或ture）還是精確匹配查找（0或false）。v這是由于在查找區(qū)域$A$3:$B$7查找“1306”時(shí)，沒(méi)有找到“1306”，因此將與其最接近的“1305”作為查找到的匹配元素，因此將“1305”所對(duì)應(yīng)的“電子信息工程”當(dāng)作了返回結(jié)果。獎(jiǎng)學(xué)金評(píng)定總結(jié)與提高（3）1. VLOO

9、KUP函數(shù)（3）v大致匹配查找通常用作區(qū)間范圍查找的情況。需要注意的是，大致匹配查找是將=17000的數(shù)據(jù)進(jìn)行再次過(guò)濾。（3）統(tǒng)計(jì)最后過(guò)濾出來(lái)的積分=17000的單元格數(shù)。v可以使用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”。v公式為“=COUNTIFS(B3:B10,北京,C3:C10,=17000)”。其含義是：將B3:B10單元格區(qū)域中為“北京”的數(shù)據(jù)過(guò)濾出來(lái)，在

10、過(guò)濾出來(lái)的數(shù)據(jù)中，再將C3:C10單元格區(qū)域中=170000的數(shù)據(jù)過(guò)濾出來(lái)，在得到的數(shù)據(jù)區(qū)域中統(tǒng)計(jì)C3:C10單元格區(qū)域的單元格個(gè)數(shù)。獎(jiǎng)學(xué)金統(tǒng)計(jì)任務(wù)實(shí)現(xiàn)（1）1.任務(wù)分析（1）計(jì)算每個(gè)人應(yīng)獲得的獎(jiǎng)學(xué)金數(shù)額。（2）為了按班級(jí)總體發(fā)放獎(jiǎng)學(xué)金，需要統(tǒng)計(jì)每個(gè)班獲得的獎(jiǎng)學(xué)金總額。（3）為了對(duì)比各班之間的情況，需要按班統(tǒng)計(jì)每個(gè)班的各等級(jí)獎(jiǎng)學(xué)金獲獎(jiǎng)人數(shù)。（4）為H3單元格添加“按班級(jí)統(tǒng)計(jì)獎(jiǎng)學(xué)金總額”批注，為G12單元格添加“按班統(tǒng)計(jì)各等級(jí)獎(jiǎng)學(xué)金獲獎(jiǎng)人數(shù)”批注，如圖6-53所示。（5）用圖表展示每個(gè)班的各等級(jí)獎(jiǎng)學(xué)金人數(shù)，如圖6-54所示。獎(jiǎng)學(xué)金統(tǒng)計(jì)任務(wù)實(shí)現(xiàn)（2）2.實(shí)現(xiàn)過(guò)程 請(qǐng)參照書本完成，有問(wèn)題請(qǐng)隨時(shí)舉手示

11、意老師。獎(jiǎng)學(xué)金統(tǒng)計(jì)總結(jié)與提高（1）1. SUMIFS函數(shù)vSUMIF函數(shù)用來(lái)對(duì)區(qū)域中滿足多個(gè)條件的單元格求和。SUMIF函數(shù)的語(yǔ)法格式為SUMIFS(Sum_range, Criteria_range1, Criteria1, Criteria_range2, Criteria2, )。參數(shù)的含義是：Criteria_range1和Criteria1：用Criteria1條件對(duì)criteria_range1區(qū)域進(jìn)行過(guò)濾。Criteria_range2和Criteria2：用Criteria2條件對(duì)criteria_range2區(qū)域進(jìn)行過(guò)濾。Sum_range：要求和的單元格區(qū)域。v如圖所示，要

12、統(tǒng)計(jì)“北京”地區(qū)游戲積分在17000分以上（含）的總積分，需要：（1）將“北京”地區(qū)的所有數(shù)據(jù)過(guò)濾出來(lái)。（2）在過(guò)濾出來(lái)的數(shù)據(jù)中，對(duì)積分=17000的數(shù)據(jù)進(jìn)行再次過(guò)濾。（3）統(tǒng)計(jì)最后過(guò)濾出來(lái)的積分=17000的和。v因此，統(tǒng)計(jì)“北京”地區(qū)游戲積分在17000分以上（含）的總積分的公式為“=SUMIFS(C3:C10,B3:B10,北京,C3:C10,=17000)”。獎(jiǎng)學(xué)金統(tǒng)計(jì)總結(jié)與提高（2）2. 雙軸線圖表制作v如圖所示，要制作國(guó)內(nèi)生產(chǎn)總值及增速比較圖，由于數(shù)據(jù)差異大，采用統(tǒng)一的坐標(biāo)軸來(lái)展現(xiàn)是不合適的。因此可以通過(guò)調(diào)整分別設(shè)置國(guó)內(nèi)生產(chǎn)總值的坐標(biāo)軸和增速的坐標(biāo)軸，即用雙軸線圖表來(lái)實(shí)現(xiàn)數(shù)據(jù)差異過(guò)大的圖表展現(xiàn)。v請(qǐng)參見老師操作。獎(jiǎng)學(xué)金統(tǒng)計(jì)總結(jié)與提高