1、單表操作（公式與函數(shù)、圖表） 多表與多工作簿操作（鏈接、調(diào)用、計(jì)算、引用、自動(dòng)計(jì)算、數(shù)據(jù)關(guān)聯(lián) ） 類數(shù)據(jù)庫管理 與透視圖表操作（二維表、透視表、數(shù)據(jù)分析）,項(xiàng)目5 統(tǒng)計(jì)學(xué)生成績,學(xué)習(xí)目的: 1.掌握Excel中公式的使用 2.掌握Excel中函數(shù)的使用 3.掌握基本圖表使用 知識(shí)要點(diǎn): 1.Excel中公式與函數(shù)的計(jì)算應(yīng)用 2.Excel中判斷、排序、篩選、分類匯總應(yīng)用 3.常用圖表類型及應(yīng)用 技能要點(diǎn)： 1.如何使用幫助 2.單元格的正確引用 3. 高級(jí)函數(shù)應(yīng)用,項(xiàng)目5 統(tǒng)計(jì)學(xué)生成績,教學(xué)重點(diǎn)： 公式的輸入 函數(shù)的調(diào)用 圖表的使用 必要的統(tǒng)計(jì)實(shí)現(xiàn) 教學(xué)難點(diǎn)： 統(tǒng)計(jì)函數(shù)的用法 絕對(duì)引用與相對(duì)應(yīng)

2、用,項(xiàng)目描述（1）,（1）統(tǒng)計(jì)對(duì)象：初三（1）、初三（2）、初三（3）； （2）班級(jí)人數(shù)：各班分別為：45、48、43； （3）課 程：社會(huì)、語文、數(shù)學(xué)、科學(xué)、英語；,項(xiàng)目描述（2）,（4）統(tǒng)計(jì)要求： 求每個(gè)學(xué)生成績總分與平均分； 求每門課程的平均分、最高分、最低分； 按總分從高到低排序，總分相同者按學(xué)號(hào)順序，同時(shí)寫入名次號(hào)（RANK.EQ）； 每位成績總平均成績等級(jí)（ 統(tǒng)計(jì)各門課分?jǐn)?shù)段的人數(shù)（85-100，70-84，60-69，0-59）； 每門課的成績分布圖； 成績不及格用紅色顯示，90分以上用綠色顯示； 顯示5門成績都及格的學(xué)生； 顯示至少有一門不及格的學(xué)生。,1.建表,1.收集原始數(shù)

3、據(jù)與理解統(tǒng)計(jì)指標(biāo) （1）明確統(tǒng)計(jì)對(duì)象與統(tǒng)計(jì)指標(biāo) 統(tǒng)計(jì)對(duì)象：班級(jí)、人數(shù)、課程名稱 統(tǒng)計(jì)指標(biāo)：總分、平均分、排序、名次等等 （2）搜集信息及建立成績表 收集信息過程 建立成績表的關(guān)鍵是建立表項(xiàng)目,2.建立原始數(shù)據(jù)(1),（1）輸入班級(jí) 在單元格A4輸入“初三（1）”； 按右鍵，用填空柄“”下拉至單元格A48（該班為45人） （2）輸入學(xué)號(hào)（同上操作） （3）輸入姓名,2.建立原始數(shù)據(jù)（2）,（4）輸入成績 使用“數(shù)據(jù)有效性”確定輸入數(shù)據(jù)的左、右邊界。,3.公式與函數(shù)（1）,1.先打開包含所需統(tǒng)計(jì)信息的數(shù)據(jù)表 2.用公式計(jì)算總分與平均分 選中I4單元格，輸入“D4+E4+F4+G4+H4”，然后按下

4、回車鍵，總分即可求出，如下圖所示。,3.公式與函數(shù)（2）,接下來選中I4單元格，然后向下拖動(dòng)填充柄即可完成其余學(xué)生總分的統(tǒng)計(jì)。 平均分的計(jì)算方法也是一樣，選中J4單元格，輸入“(D4+E4+F4+G4+H4)/5”，然后按下回車鍵，平均分即可求出，如圖4.4所示。,3.公式與函數(shù)（3）,3使用函數(shù)計(jì)算總分,選中I4單元格，執(zhí)行菜單欄中的“插入”“函數(shù)”命令，如圖4.6所示。,3.公式與函數(shù)（4）,在彈出的“插入函數(shù)”對(duì)話框中，選中函數(shù)“SUM”，如下左圖、下右圖所示。,3.公式與函數(shù)（5）,函數(shù)參數(shù)的輸入方法： 1）.可以直接在參數(shù)的文本框中直接輸入單元格或數(shù)值 2）.也可以直接單擊文本框后的

5、按鈕 然后直接拖動(dòng)鼠標(biāo)選中單元格區(qū)域，對(duì)話框中將自動(dòng)根據(jù)所選中的區(qū)域顯示，單擊此區(qū)域后的按鈕 返回至“函數(shù)參數(shù)”對(duì)話框。,3.公式與函數(shù)（6）,4.求每門課程的平均分、最高分、最低分,平均分、最高分、最低分統(tǒng)計(jì)結(jié)果的位置設(shè)計(jì)如下圖所示。,3.公式與函數(shù)（7）,求平均分使用函數(shù) AVERAGE 求最高分使用函數(shù) MAX 求最低分使用函數(shù) MIN,3.公式與函數(shù)（7）,思考： 上面分別用公式和函數(shù)的方法求得總分和平均分，你覺得哪種方法更合適？,要求： 分別在教師提供的數(shù)據(jù)表-成績分析表.xlsx上按字段要求進(jìn)行如下操作：,3.公式與函數(shù)（8）,思考： 此處的平均分和前一題的平均分區(qū)別在哪里？ 容易

6、出錯(cuò)在哪里？（要特別注意選擇計(jì)算區(qū)域的正確性）,要求： 分別在教師提供的數(shù)據(jù)表-成績分析表.xls x上按字段要求進(jìn)行如下操作：,3.公式與函數(shù)（9）,5求班名次和段名次 提一個(gè)問題？ 有兩位同學(xué)的總分是一樣的，請(qǐng)問他們的名次有區(qū)別嗎？,思考： 正常情況下，我們要確定董宏峰同學(xué)在初三（1）班的總分名次，是如何來做的？ 求此結(jié)果，需要知道幾個(gè)已有條件： 該學(xué)生的總分成績 340 ？ I4 班級(jí)總分成績的序列 I$4:I$48,3.公式與函數(shù)（10）,選中單元格K4中，在“函數(shù)庫”功能區(qū)中選擇“插入函數(shù)”命令，系統(tǒng)出現(xiàn)“插入函數(shù)”對(duì)話框，在“選擇類別”下拉列表中選擇“統(tǒng)計(jì)”，在“選擇函數(shù)”列表中排

7、序函數(shù)有 “RANK.AVG”和“RANK.EQ”兩項(xiàng)，“RANK.AVG”函數(shù)是將相同總分者的名次求其平均，若第7名和第8名總分相同，則以7.5、7.5的形式表達(dá)；“RANK.EQ”函數(shù)，是將相同總分者的名次按相同名次處理，若第7名和第8名總分相同，則以7、7的形式表達(dá)。本案例選用“RANK.EQ”函數(shù)，系統(tǒng)彈出 下左所示的對(duì)話框，操作結(jié)果如下右圖。,3.公式與函數(shù)（11）,同理求得初三（2）班和初三（3）班的班級(jí)名次。 段名次的求法就比較簡單，只需在“Ref”文本框中輸入“I$4:I$139”， 即可實(shí)現(xiàn)求段名次。,3.公式與函數(shù)（12）,6統(tǒng)計(jì)各門課程各分?jǐn)?shù)段的人數(shù),分?jǐn)?shù)段的劃分規(guī)則是：

8、 85100分屬于優(yōu) 7084分屬于中 6069分屬于及格 059分屬于不及格,各分?jǐn)?shù)段劃分規(guī)則,此處使用COUNTIF函數(shù)實(shí)現(xiàn),先選擇單元格，執(zhí)行菜單欄中的“插入”“函數(shù)”命令，在“插入函數(shù)”對(duì)話框的“選擇類別”下拉列表中選擇“統(tǒng)計(jì)”，在“選擇函數(shù)”列表框中選擇“COUNTIF”并單擊“確定”按鈕。此時(shí)系統(tǒng)彈出“函數(shù)參數(shù)”對(duì)話框，在“Range”文本框中輸入“D4:D139”，在“Criteria”文本框中輸入“=85”，如圖4.19所示。然后按回車鍵即可求得成績屬于優(yōu)的人數(shù)。接下來向右拖動(dòng)填空柄至單元格H143，即可求出所有科目中成績屬于優(yōu)的人數(shù)，如圖4.20所示。,計(jì)算的連續(xù)區(qū)域,滿足的

9、給定條件,3.公式與函數(shù)（13）,3.公式與函數(shù)（14）,如果已經(jīng)掌握了函數(shù)格式，就不需通過“插入函數(shù)參數(shù)”對(duì)話框和“函數(shù)參數(shù)”對(duì)話框進(jìn)行設(shè)置，直接在編輯欄輸入函數(shù)表達(dá)式即可。,選擇單元格D144，輸入 “=COUNTIF(D4:D139,”=70“)-COUNTIF(D4:D139, ”=85“)”， 按回車鍵后，向右拖動(dòng)填空柄至單元格H144。 選擇單元格D145，輸入 “=COUNTIF(D4:D139,”=60“)-COUNTIF(D4:D139, ”=70“)”， 按回車鍵后，向右拖動(dòng)填空柄至單元格H145。 選擇單元格D146，輸入“=COUNTIF(D4:D139,”60“)”，

10、 按回車鍵后，向右拖動(dòng)填空柄至單元格H146。,4.邏輯判斷（1）,將J列學(xué)生的平均分從百分制的分?jǐn)?shù)轉(zhuǎn)換成M列的A（90100）、B（8089）、C（7079）、D（6069）與E（低于60）五個(gè)等級(jí)。解決這類分級(jí)問題用邏輯判斷函數(shù)IF即可。,4.邏輯判斷（2）,如果簡單地將成績分成兩個(gè)等級(jí)A（=60）和B（=60,”A“,”B“)”即可。 如果分成多個(gè)等級(jí)，只要將邏輯判斷的條件進(jìn)行嵌套，在M4中輸入“=IF(J4=90,A,IF(J4=80,B,IF(J4=70,C,IF(J4=60,D,E)”。,4.邏輯判斷（3）,要求： 根據(jù)提供的數(shù)據(jù)表-成績分析表.xlsx中的平均分進(jìn)行等級(jí)劃分，將學(xué)

11、生平均分的百分制轉(zhuǎn)換成不同的等級(jí)。,思考： 如何在嵌套多IF函數(shù)的時(shí)候不易出錯(cuò)？,5.數(shù)據(jù)篩選（1）,在備份的“Sheet2”工作表中，單擊單元格A3，單擊“數(shù)據(jù)”選項(xiàng)卡，“排序與篩選”功能區(qū)中的“篩選”，后，并下拉“社會(huì)”科目后系統(tǒng)暗淡顯示。如下圖所示。,5.數(shù)據(jù)篩選（2）,點(diǎn)擊下拉列表中的“數(shù)字篩選”中的“大于或等于(O)”，彈出“自定義自動(dòng)篩選方式”對(duì)話框。如圖所示。,在“自定義自動(dòng)篩選方式”對(duì)話框中，通過單擊左邊輸入框的下拉按鈕，在下拉列表中選擇“大于或等于”，再在右邊輸入框中輸入“60”，單擊“確定”按鈕，然后將其余四門課程對(duì)應(yīng)的篩選用同樣方式輸入完畢 。,5.數(shù)據(jù)篩選（3）,觀察：

12、 注意最左側(cè)的行號(hào)，顏色是藍(lán)色的，而且行號(hào)是不連續(xù)的，說明這已經(jīng)是篩選的結(jié)果。,5.數(shù)據(jù)篩選（4）,篩選出來的記錄建議放在另一張表中。選定經(jīng)篩選后的記錄和表頭，單擊“復(fù)制”按鈕，切換到“Sheet3”工作表，在單元格A1處單擊“粘貼”按鈕，就將五門課程都及格的學(xué)生清單復(fù)制了過來。然后修改表頭內(nèi)容為“學(xué)校初三段課程全部及格學(xué)生一覽表”，如下圖所示。,5.數(shù)據(jù)篩選（5）,回到上述的“Sheet2”工作表中，選定篩選后的數(shù)據(jù)行（不包括表頭和表標(biāo)題），也就是五門課程都及格的學(xué)生數(shù)據(jù)行，執(zhí)行菜單欄中的“編輯”“刪除行”命令，此時(shí)會(huì)發(fā)現(xiàn)表中除了表頭和表標(biāo)題外，沒有數(shù)據(jù)行了，接下來執(zhí)行選項(xiàng)卡“數(shù)據(jù)”“排序與

13、篩選”功能區(qū)“篩選”或“清除”命令，即可恢復(fù)至正常數(shù)據(jù)狀態(tài)。此時(shí)，數(shù)據(jù)表上的內(nèi)容就是至少有一門課程不及格的學(xué)生記錄。,思考： 分析“學(xué)校初三段課程全部及格學(xué)生一覽表”和“學(xué)校初三段至少有一門不及格學(xué)生一覽表”中的班名次和段名次，是否有別？原因在哪里？,5.數(shù)據(jù)篩選（6）,3制作全科成績合格與至少有一門課程成績不及格的學(xué)生清單（方法2） （1）首先建立條件區(qū)域，如下圖,5.數(shù)據(jù)篩選（7）,單擊“數(shù)據(jù)”選項(xiàng)卡，單擊“排序與篩選”功能區(qū)中圖標(biāo) ，將方式選中“將篩選結(jié)果復(fù)制到其他位置”，列表區(qū)域選擇“A3:L139”，條件區(qū)域選擇剛剛建立的“D149:H154”，復(fù)制到“A160”，“A160”即篩選

14、結(jié)果的起始單元格位置。如圖 所示。,單擊“確定”按鈕后，就會(huì)以“A160”為起始單元格顯示滿足“至少有一門課程不及格”篩選條件的結(jié)果。,5.數(shù)據(jù)篩選（8）,如果要高級(jí)篩選全科成績都合格的學(xué)生，只要將條件格式設(shè)為“與”的方式，即在一行中設(shè)置條件即可，如圖所示。,多種篩選條件同時(shí)成立的情況,與,或,多種篩選條件滿足其一成立的情況,各篩選條件 并排羅列,各篩選條件 錯(cuò)行羅列,多種篩選條件同時(shí)成立的情況,與,或,各篩選條件 并排羅列,各篩選條件 錯(cuò)行羅列,6.條件格式（1）,4將成績不及格用紅色標(biāo)注，90分（包括90分）以上用綠色標(biāo)注。,成績管理中為了清楚地分辨學(xué)生成績的高低，于是用不同顏色給不及格者

15、和90分以上者進(jìn)行區(qū)分，利用“條件格式”就可以輕松完成。,單擊“開始”選項(xiàng)卡，在“樣式”功能區(qū)里下拉“條件格式”，系統(tǒng)出現(xiàn)命令 ，單擊 ，系統(tǒng)彈出“新建格式規(guī)則”對(duì)話框，根據(jù)成績管理的要求設(shè)置單元格值小于60，單擊“格式（F）”按鈕，彈出“單元格格式”對(duì)話框，在“顏色”位置選擇“紅色”，在“字形”位置選擇“加粗”，單擊“確定”按鈕回到“新建格式規(guī)則”對(duì)話框，完成對(duì)不及格成績的條件設(shè)定。如圖 所示。,6.條件格式（2）,同理：求得90以上為綠色加粗,7.分類匯總（1）,5各班各門課程平均值統(tǒng)計(jì),要求對(duì)“Sheet1”工作表中的數(shù)據(jù)進(jìn)行分類匯總，求出初三（1）、初三（2）、初三（3）三個(gè)班級(jí)社會(huì)、

16、語文、數(shù)學(xué)、科學(xué)、英語五門課程的班級(jí)平均分。,小技巧：用鼠標(biāo)單擊任一數(shù)據(jù)單元格，按Ctrl+Shift+8組合鍵，即可選中整個(gè)數(shù)據(jù)清單,單擊“數(shù)據(jù)”選項(xiàng)卡，單擊“分級(jí)顯示”中的“分類匯總”，系統(tǒng)彈出“分類匯總”對(duì)話框。,按照要求需各個(gè)班級(jí)進(jìn)行如下設(shè)置：“分類字段”設(shè)置為“班級(jí)”；“匯總方式”設(shè)置為“平均值”；“選定匯總項(xiàng)”列表框中勾選社會(huì)、語文、數(shù)學(xué)、科學(xué)、英語五門課程對(duì)應(yīng)的復(fù)選框。,7.分類匯總（2）,7.分類匯總（3）,單擊“確定”按鈕，即可求出初三（1）、初三（2）、初三（3）各個(gè)班級(jí)，社會(huì)、語文、數(shù)學(xué)、科學(xué)、英語五門課程的班級(jí)平均分，結(jié)果如圖所示。,選擇“2”,7.分類匯總(4),分類

17、匯總結(jié)果脫離源數(shù)據(jù) 單擊“開始”選項(xiàng)卡，下拉“編輯”功能區(qū)中的“查找與選擇”工具按鈕，系統(tǒng)彈出快捷菜單，選擇“定位條件”，系統(tǒng)出現(xiàn)如圖 所示的對(duì)話框。,8.圖表（1）,每門課的成績區(qū)間直方圖,任務(wù)5 教學(xué)內(nèi)容,先打開包含所需統(tǒng)計(jì)信息的數(shù)據(jù)表 選擇要進(jìn)行數(shù)據(jù)分析的數(shù)據(jù)區(qū)域，橫坐標(biāo)、縱坐標(biāo) 單擊“常用”工具欄中的按鈕，彈出如圖5.1所示的“圖表向?qū)?4步驟之1-圖表類型”對(duì)話框。選擇“柱形圖”，在“子圖表類型”選項(xiàng)區(qū)域中選擇“簇狀柱形圖”選項(xiàng)，然后單擊“下一步”按鈕 ，彈出如圖5.2所示,8.圖表（2）,根據(jù)圖表的要求，使用的橫坐標(biāo)數(shù)據(jù)是：A143：A146，縱坐標(biāo)的數(shù)據(jù)是D143：H146。單擊

18、“插入”選項(xiàng)卡，單擊“圖表”功能區(qū)中“柱形圖”工具圖標(biāo)，系統(tǒng)彈出如所示圖表向?qū)А皥D表類型”對(duì)話框,8.圖表（3）,選中圖表，單擊“圖表工具”“設(shè)計(jì)”中的 按鈕如左下圖所示，單擊“切換行/列”按鈕彈出如圖 5.42所示。,8.圖表（4）,在“圖例項(xiàng)（系列）”選項(xiàng)卡，選中“系列1”，單擊“編輯”按鈕，在對(duì)應(yīng)的“系列名稱”輸入框中輸入“社會(huì)”，同理，繼續(xù)按以上操作步驟，編輯系列2、系列3、系列4、系列5 的“系列名稱”和“系列值”，如下左圖 所示。按“確定”，結(jié)果如下右圖所示。 ”,8.圖表（5）,選中圖表，單擊“圖表工具”中的“布局”選項(xiàng)卡，在“標(biāo)簽”功能區(qū)中，單擊“圖表標(biāo)題”、“圖表上方”，設(shè)置

19、圖表標(biāo)題，并輸入“分?jǐn)?shù)段人數(shù)分布柱形圖”。單擊“坐標(biāo)軸標(biāo)題”，分別設(shè)置橫坐標(biāo)與縱坐標(biāo)標(biāo)題，在對(duì)應(yīng)的文本框中分別輸入“分?jǐn)?shù)段”、“人數(shù)”，系統(tǒng)出現(xiàn)如圖 所示。,總結(jié)（1）,EXCEL中最強(qiáng)大的功能之一就是其函數(shù)功能。,按照不同用途分為財(cái)務(wù)、日期與時(shí)間、數(shù)學(xué)與三角函數(shù)、統(tǒng)計(jì)、查找與引用、數(shù)據(jù)庫、文本、邏輯、信息。用戶需要使用函數(shù)時(shí)，可選擇函數(shù)類型，再選擇該子類中的函數(shù)。若無法判斷使用函數(shù)的類型，可選擇“全部”，再逐步下拉查找。,除了SUM、AVERAGE、IF、COUNTIF、COUNT、MAX等常用函數(shù)外。一般在使用函數(shù)的過程中，可以通過幫助文件獲取操作方法與步驟 ，或者直接通過F1幫助系統(tǒng)獲取

20、你想了解的比如函數(shù)格式、具體使用步驟等等,總結(jié)（2）,總結(jié)（3）,1.在函數(shù)或公式的使用過程中，范圍的選擇是關(guān)鍵，同時(shí)要確定是相對(duì)引用還是絕對(duì)引用。 嘗試將上述例子中引用單元格范圍時(shí)的“$”符號(hào)去掉，然后用填充柄實(shí)現(xiàn)其他單元格的統(tǒng)計(jì)，觀察問題所在。 2.在使用公式與函數(shù)進(jìn)行統(tǒng)計(jì)時(shí)，其結(jié)果顯示出來后一定要經(jīng)過人工的檢查與核對(duì)，不能忽略這一重要的環(huán)節(jié)。 盡量采用復(fù)制、粘貼的方法，獲取已經(jīng)經(jīng)過驗(yàn)證的正確函數(shù)表達(dá)式。,總結(jié)（4）,在Excel中對(duì)數(shù)據(jù)篩選可使用“自動(dòng)篩選”與“高級(jí)篩選”兩種方法。 “自動(dòng)篩選”可對(duì)某列設(shè)置單一篩選條件，也可以用“與”關(guān)系或“或”關(guān)系設(shè)定條件；多列設(shè)置，其列與列條件成“與

21、”的關(guān)系。 “高級(jí)篩選”功能，要先設(shè)置條件區(qū)域，條件區(qū)域由標(biāo)題行和條件行組成。,總結(jié)（5）,“條件格式”要做兩項(xiàng)操作： 一是設(shè)定條件（表達(dá)式）； 二是設(shè)定格式（字體、字號(hào)、顏色、下劃線等）。 該功能操作的結(jié)果是將滿足條件的數(shù)據(jù)用指定的格式顯示，如不及格者用“紅色”標(biāo)注，以示醒目。,總結(jié)（6）,使用“分類匯總”功能時(shí)，在操作前必須要先對(duì)關(guān)鍵字段進(jìn)行排序，不然的話，結(jié)果沒有任何意義。分類匯總的結(jié)果有以下三種顯示形式： 1）明細(xì)記錄與分類匯總值。（3） 2）分類匯總值。（2） 3）總匯總值。（1）,總結(jié)（7）,IF函數(shù)作為邏輯判斷用途很廣，簡單的IF語句可通過對(duì)話框進(jìn)行設(shè)置。但大多數(shù)的統(tǒng)計(jì)需要多重嵌套，因此在編寫公式的時(shí)候，先分成兩段式，再將后一段繼續(xù)用IF函數(shù)展開，最后一定要檢查括號(hào)的使用是否正確與配套，否則有可能導(dǎo)致結(jié)果錯(cuò)誤或彈出非法公式的錯(cuò)誤信息對(duì)話框。,總結(jié)（8）,Excel具有強(qiáng)大的圖表自動(dòng)生成功能，生成圖表的操作操作步驟如下： 1）選擇圖表類型及子類。 2）選擇圖表源數(shù)據(jù)