第四章 Excel 2010的使用

第四章Excel2010的使用制作人：李龍龍、宋承繼、李瑩、劉引濤《計算機應用基礎》系列課件詩有風雅頌，萬物各有律；用其所長，順其之美學習目標掌握統(tǒng)計函數(shù)等函數(shù)的使用方法及圖表的操作2.1.掌握Excel中各種數(shù)據(jù)的輸入方法及單元格的操作3.掌握分類匯總的使用方法及相應分析函數(shù)的使用能夠熟練掌握Excel的數(shù)據(jù)分析功能并能熟練應用4.主要內(nèi)容4.1Excel基礎應用—制作“學生成績表”4.2Excel綜合應用—制作“成績統(tǒng)計分析表”4.3Excel高級應用—連鎖超市銷售數(shù)據(jù)分析4.1Excel基礎應用——制作“學生成績表”4.1.1Word基礎應用案例分析●1.任務的提出

學期結(jié)束時，班主任劉老師要為本班同學建立一張本學期學生學籍與成績管理表。

困惑：如何按要求計算出每個學生的總分并按總分排名次，并計算出各科分數(shù)的平均分、各科的優(yōu)秀率和及格率，對不及格的課程成績加灰色圖案顯示，最后打印出來存檔。

如何輸入并美化成績表？如何對成績進行計算和處理？4.1.1Word基礎應用案例分析學生成績表●1.任務的提出4.1Excel基礎應用——制作“學生成績表”●2.解決方法應用Excel提供的“條件格式”功能，對不及格成績顯示特殊格式應用Excel提供的求和等函數(shù)與“數(shù)據(jù)排序”功能對成績進行統(tǒng)計與排序應用Excel提供的RANK函數(shù)實現(xiàn)按總分排名次4.1.1Word基礎應用案例分析4.1Excel基礎應用——制作“學生成績表”4.1.2相關(guān)知識點●1.

Excel2010的窗口組成4.1Excel基礎應用——制作“學生成績表”4.1.2相關(guān)知識點●2.

工作薄和工作表

在Excel中，一個Excel文件就是一個工作簿。工作簿是由多個工作表組成的，工作表是由一個個單元格組成的，單元格是組成工作簿的最小單位。

（1）賬本——工作簿

所謂工作簿，是指用來存儲并處理工作數(shù)據(jù)的文件。在一個工作簿中可以建立多個不同類型的工作表。

（2）賬頁——工作表

工作表是工作簿文件的一個組成部分。單擊工作簿標簽上的工作表名，可實現(xiàn)在同一工作簿文件中不同工作表之間的切換。

工作表默認是由1048576行和16384列所構(gòu)成。4.1Excel基礎應用——制作“學生成績表”4.1.2相關(guān)知識點●3.

行、列和單元格4.1Excel基礎應用——制作“學生成績表”4.1.2相關(guān)知識點●4.

區(qū)域

區(qū)域的引用常用左上角、右下角單元格的引用來標志，中間用“：”間隔。引用多個區(qū)域用逗號隔開。如圖所示數(shù)據(jù)系列引用可表示為B4、D4：H4、B6、D6：H6、B11、D11：H11。4.1Excel基礎應用——制作“學生成績表”4.1.2相關(guān)知識點●5.

常用數(shù)據(jù)類型及輸入

在Excel中數(shù)據(jù)有多種類型，最常用的數(shù)據(jù)類型有文本型、數(shù)值型、日期型等。

文本型數(shù)據(jù)可以包括字母、數(shù)字、空格和符號，其對齊方式為左對齊；數(shù)值型數(shù)據(jù)包括0～9、()、+、?等符號，其對齊方式為右對齊。

快速輸入有很多技巧，例如利用填充柄自動填充、自定義序列、按Ctrl+Enter鍵可以在不相鄰的單元格中自動填充重復數(shù)據(jù)等。4.1Excel基礎應用——制作“學生成績表”4.1.2相關(guān)知識點●6.

單元格的格式化設置

單元格的格式化包括設置數(shù)據(jù)類型、單元格對齊方式、設置字體、設置單元格邊框及底紋等。在“單元格格式設置”對話框中：“數(shù)字”選項卡，用于設置單元格中的不同數(shù)值類型。如：貨幣類型、文本類型、百分比類型等?！皩R”選項卡，用于設置單元格中數(shù)據(jù)的對齊方式。如：水平，垂直對齊、合并單元格、自動換行等?！白煮w”選項卡，用于設置單元格中數(shù)據(jù)的“字體”、“字形”、“字號”、“顏色”等?！斑吙颉边x項卡，用于設置單元格或表格的邊框線?！皥D案”選項卡，用于設置單元格的底紋。4.1Excel基礎應用——制作“學生成績表”4.1.2相關(guān)知識點●7.

多工作表的操作

多工作表的操作，包括對工作表的重命名，工作表之間的復制、移動、插入、刪除等，對工作表操作時一定要先選定工作表標簽。

工作表之間數(shù)據(jù)的復制、粘貼、單元格引用等，都是在工作表單元格之間進行的。4.1Excel基礎應用——制作“學生成績表”4.1.2相關(guān)知識點●8.

公式和函數(shù)的使用Excel中的“公式”是指在單元格中執(zhí)行計算功能的等式，所有公式都必須以“=”號開頭，“=”后面是參與計算的運算數(shù)和運算符。每一個運算數(shù)可以是常量、單元格或區(qū)域引用、單元格名稱或函數(shù)等。Excel提供了四種類型的運算符：算術(shù)運算符、比較運算符、文本運算符和引用運算符。Excel函數(shù)是一種預定義的內(nèi)置公式，它使用一些稱為參數(shù)的特定數(shù)值按特定的順序或結(jié)構(gòu)進行計算，然后返回結(jié)果。4.1Excel基礎應用——制作“學生成績表”4.1.2相關(guān)知識點●8.

公式和函數(shù)的使用所有函數(shù)都包含三部分：函數(shù)名、參數(shù)和圓括號，以求和函數(shù)SUM來說明。SUM是函數(shù)名稱，從名稱大略可知該函數(shù)的功能及用途是求和。圓括號用來括起參數(shù)，在函數(shù)中圓括號是不可以省略的。參數(shù)是函數(shù)在計算時所必須使用的數(shù)據(jù)。函數(shù)的參數(shù)不僅是數(shù)字類型，它還可以是字符型、邏輯值或是單元格引用。

如：SUM(B1,C3)，SUM(D2:G2)等。圓括號用于括起參數(shù)SUM（number1,number2,...）函數(shù)名稱各參數(shù)之間用逗號分隔4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●1．輸入“姓名”到“計算機基礎”列數(shù)據(jù)

輸入如圖所示的學生學籍管理表，其中需要不同的數(shù)據(jù)類型。任務1：建立學生成績表4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●2．輸入“學號”列數(shù)據(jù)——填充序列（1）使用鼠標填充“學號”列數(shù)據(jù)。任務1：建立學生成績表4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●2．輸入“學號”列數(shù)據(jù)——填充序列（2）使用命令填充“學號”列數(shù)據(jù)。任務1：建立學生成績表4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●3．特別顯示不及格的成績——條件格式

操作實例：黃牌警告——特別顯示不及格的成績。選定E2:H11區(qū)域，對成績小于60分的成績加灰色圖案。任務1：建立學生成績表4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●3．特別顯示不及格的成績——條件格式

操作實例：黃牌警告——特別顯示不及格的成績。選定E2:H11區(qū)域，對成績小于60分的成績加灰色圖案。任務1：建立學生成績表4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●3．特別顯示不及格的成績——條件格式

操作實例：黃牌警告——特別顯示不及格的成績。選定E2:H11區(qū)域，對成績小于60分的成績加灰色圖案。任務1：建立學生成績表格式設置結(jié)果4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●4．求各種分數(shù)——應用公式和函數(shù)Excel中的“公式”是指在單元格中執(zhí)行計算功能的等式。Excel中的所有公式都必須以“＝”號開頭，若無等號，Excel將其理解為正文，所以“＝”是公式中絕對不可缺少的一個運算符?！埃健焙竺媸菂⑴c計算的運算數(shù)和運算符，每一個運算數(shù)可以是常量、單元格或區(qū)域引用、單元格名稱或函數(shù)等。Excel提供了四種類型的運算符：算術(shù)運算符、比較運算符、文本運算符和引用運算符。任務1：建立學生成績表4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●4．求各種分數(shù)——應用公式和函數(shù)

操作實例：求總分。求E2、F2、G2、H2四個單元格內(nèi)數(shù)字的和。

操作步驟如下：

方法一：將鼠標移到I2單元格，單擊“公式”選項卡中的“自動求和”按鈕“∑”計算出I2單元格的分數(shù)，最后選中I2單元格，將光標移至右下角，當光標變?yōu)樾『谑謺r雙擊，就可以求出該列的總分。

方法二：使用SUM函數(shù)。其語法格式為SUM(Ref)，此處Ref為參與計算的單元格區(qū)域。將鼠標移到I2單元格，然后輸入=SUM(E2:H2)后按Enter鍵，即可求出E2、F2、G2、H2四個單元格內(nèi)數(shù)字的和。任務1：建立學生成績表4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●4．求各種分數(shù)——應用公式和函數(shù)操作實例：求平均分。用AVERAGE函數(shù)，其語法格式為AVERAGE(Ref)，此處Ref為參與計算的單元格區(qū)域。例如，AVERAGE(E2:E11)是求E2:E11區(qū)域內(nèi)數(shù)字的平均值。操作步驟如下：①將鼠標移到E12單元格。②輸入=AVERAGE(E2:E11)后回車，即可求出E2:E11區(qū)域內(nèi)數(shù)字的平均值。任務1：建立學生成績表4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●4．求各種分數(shù)——應用公式和函數(shù)操作實例：求最高分、最低分。用MAX和MIN函數(shù)，語法格式分別為MAX(Ref)和MIN(Ref)，例如上例中求E2:E11的最高分和最低分的方法為：MAX(E2:E11)和MIN(E2:E11)。操作步驟如下：①將鼠標移到E13單元格。②輸入“=MAX(E2:E11)”后按Enter鍵，即可求出E2:E11區(qū)域內(nèi)數(shù)字的最大值。求最低分操作類似。任務1：建立學生成績表4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●5．單元格引用的方法——相對引用和絕對引用

單元格引用是指公式中指明的一個單元格或一組單元格。（1）直接引用單元格名稱（2）引用當前表中的單元格地址①相對引用：用單元格的行號和列號表示單元格地址②絕對引用：在單元格的行號、列號前面各加一個“$”符號③混合引用：在公式中同時使用相對引用和絕對引用④在當前工作簿中引用其他工作表中的單元格：在該單元格坐標或名稱前加上其所在的工作表名稱和嘆號分隔符“！”⑤引用其他工作簿中的單元格：[工作簿名]工作表名！單元格坐標（或名稱）任務1：建立學生成績表4.1Excel基礎應用——制作“學生成績表”

公式中對單元格的引用分為相對引用、絕對引用和混合引用。相對引用用“H2”這樣的方式來引用單元格是相對引用。相對引用是指當公式在復制或移動時，公式中引用單元格的地址會隨著移動的位置自動改變。絕對引用在行號和列號前均加上“$”，如：“$H$2”這樣的方式來引用單元格是絕對引用。當公式在復制或移動時，公式中引用單元格的地址不會隨著公式的位置改變。混合引用混合引用是指單元格地址中既有相對引用，也有絕對引用?！?H2”，表示具有絕對列和相對行，當公式在復制或移動時，保持列不變，而行變化；H$2”表示具有相對列和絕對行，當公式在復制或移動時，保持行不變，而列變化。4.1.3實現(xiàn)方法●5．單元格引用的方法——相對引用和絕對引用任務1：建立學生成績表4.1Excel基礎應用——制作“學生成績表”

在相對引用中，當把一個含有單元格地址的公式移動或復制到一個新的位置時，公式中的單元格地址會隨之發(fā)生變化。例如，圖4-11中I2單元格中的公式“I2=E2+F2+G2+H2”。4.1.3實現(xiàn)方法●5．單元格引用的方法——相對引用和絕對引用任務1：建立學生成績表4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●5．單元格引用的方法——相對引用和絕對引用任務1：建立學生成績表

公式“I2=E2+F2+G2+H2”在被復制到G3單元格時會自動變?yōu)椤癐3=E3+F3+G3+H3”，從而得到了如圖4-12所示的結(jié)果。4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●6．成績排行榜——數(shù)據(jù)排序

（1）使用“數(shù)據(jù)”選項卡中的升序按鈕或降序按鈕直接排序①選定要排序的單元格區(qū)域。②在“數(shù)據(jù)”選項卡中，單擊“排序和篩選”選項組中的升序按鈕

或降序按鈕

，即可完成選定單元格區(qū)域的數(shù)據(jù)排序。采用此方法排序，數(shù)據(jù)的排列順序以所選范圍的第一列“升序”或“降序”排列。任務1：建立學生成績表4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●6．成績排行榜——數(shù)據(jù)排序

（2）單擊“排序和篩選”選項組中的“排序”按鈕排序。任務1：建立學生成績表4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●7．排列名次任務1：建立學生成績表

在學生學籍管理表中，需要按學生的總分排名次，得到成績排行榜。可以使用Excel提供的RANK函數(shù)完成。RANK函數(shù)是排定名次的函數(shù)，用于返回一個數(shù)值在一組數(shù)值中的排序，排序時不改變該數(shù)值原來的位置。語法格式為：RANK(number,ref,order)共包括三個參數(shù)，其中：Number

為需要找到排位的數(shù)字。Ref為數(shù)字列表數(shù)組或?qū)?shù)字列表的引用。Ref中的非數(shù)值型參數(shù)將被忽略。Order

為一數(shù)字，指明排位的方式。如果Order為0或省略，按照降序排列；如果Order不為零，按照升序排列。4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●7．排列名次任務1：建立學生成績表4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●8．工作薄的保存任務1：建立學生成績表單擊快速訪問工具欄中的“

”保存命令按鈕，或選擇“文件”→“保存”命令。4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●9．工作表的打印任務1：建立學生成績表①設置打印范圍。②進行頁面設置。圖4-17“頁面設置”對話框4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●9．工作表的打印任務1：建立學生成績表③打印預覽和打印。4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●1．調(diào)整行高和列寬任務2：調(diào)整和美化成績表（1）用鼠標調(diào)整行高和列寬（2）用選項卡命令調(diào)整行高和列寬

在“開始”選項卡中單擊“單元格”選項組的“格式”下拉列表，選擇“行高”命令。4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●2．增減行、列、單元格任務2：調(diào)整和美化成績表（1）增加一行或一列——行或列的插入（2）插入空白的單元格（3）行、列和單元格的刪除4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●3．美化表格——單元格格式的用法任務2：調(diào)整和美化成績表（1）設置單元格字體格式4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●3．美化表格——單元格格式的用法任務2：調(diào)整和美化成績表（2）設置數(shù)值顯示格式

①直接輸入法：在普通樣式中，單元格默認的數(shù)字格式為G/通用，表示用哪一種格式輸入數(shù)字，數(shù)字就會以該種格式顯示。②通過“數(shù)字”選項卡輸入：在“開始”選項卡中單擊“數(shù)字”選項組的對話框啟動器，顯示“設置單元格格式”對話框的“數(shù)字”選項卡。③使用格式工具欄的數(shù)字格式按鈕對數(shù)字進行格式設置。4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●3．美化表格——單元格格式的用法任務2：調(diào)整和美化成績表（3）對齊單元格的數(shù)據(jù)

①設置單元格數(shù)據(jù)的水平對齊方式。②設置單元格數(shù)據(jù)的垂直對齊方式③將工作表中學生姓名用字是兩個字的與3個字的左右對齊——“分散對齊”。4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●3．美化表格——單元格格式的用法任務2：調(diào)整和美化成績表（4）改變單元格中文本的方向

單元格中的數(shù)據(jù)默認為“橫排”但是數(shù)據(jù)除了橫著放以外，還可以“豎著放”，也可以旋轉(zhuǎn)。4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●3．美化表格——單元格格式的用法任務2：調(diào)整和美化成績表（5）標題處理——合并單元格與跨列居中4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●3．美化表格——單元格格式的用法任務2：調(diào)整和美化成績表（6）為單元格設置邊框和底紋

為單元格加上邊框：①選擇要添加邊框的單元格或區(qū)域。②在“開始”選項卡中單擊“單元格”選項組的“格式”按鈕，從下拉列表中選擇“設置單元格格式”命令，單擊“邊框”選項卡。③選擇邊框、線條樣式和顏色。

給單元格設置圖案和底紋：①選擇需要格式化的單元格或區(qū)域。②在“開始”選項卡中單擊“單元格”選項組的“格式”按鈕，從下拉列表中選擇“設置單元格格式”命令，單擊“填充”選項卡。③從“顏色”和“圖案”調(diào)色板中選色顏色和圖案，“示例”框?qū)@示設置情況。4.1Excel基礎應用——制作“學生成績表”4.1.4案例總結(jié)本案例主要通過3個工作任務介紹了工作表的操作。Excel中有很多快速輸入數(shù)據(jù)的技巧，熟練掌握這些技巧可以提高輸入速度。在輸入數(shù)據(jù)時，要注意數(shù)據(jù)單元格的數(shù)據(jù)分類。Excel中對工作表的格式化操作包括：工作表中各種類型數(shù)據(jù)的格式化、字體格式、行高和列寬、數(shù)據(jù)的對齊方式、表格的邊框和底紋等。4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法●4．使用條件格式——讓行列更清晰任務2：調(diào)整和美化成績表給單元格逐行設置不同的底紋顏色①選中數(shù)據(jù)區(qū)中所有的單元格。

②在“開始”選項卡中單擊“樣式”選項組的“條件格式”按鈕，選擇“新建規(guī)則”命令，在打開的對話框中，選擇規(guī)則類型為“使用公式確定要設置格式的單元格”，然后在下方的輸入欄中輸入公式：=MOD(ROW(),2)。③

單擊下方的“格式”按鈕，在彈出的“設置單元格格式”對話框中單擊“填充”選項卡，指定單元格的填充顏色，并選擇“邊框”選項卡，為單元格加上外邊框。

④最后連續(xù)單擊“確定”按鈕。4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法任務3：多工作表操作一個工作簿中可以包含多張工作表。文檔窗口下方的Sheet1、Sheet2、Sheet3為工作表的名稱。文檔窗口只能顯示一張工作表的內(nèi)容，稱為當前工作表。當前工作表的名稱下有下劃線，且為白色填充。單擊名稱標簽可切換工作表，單擊工作表切換按鈕可顯示被隱藏的工作表名稱。對于工作表，可以進行更改名稱、刪除未用的工作表、工作表的移動和復制、插入空工作表等操作，方法為：單擊右鍵，從快捷菜單中選擇相應的命令。4.1Excel基礎應用——制作“學生成績表”4.1.3實現(xiàn)方法任務3：多工作表操作在工作表的計算操作中，需要用到同一工作簿文件中其他工作表中的數(shù)據(jù)時，可在公式中引用其他工作表中的單元格。引用格式如下：〈工作表名〉！〈單元格地址〉。例如，若想在工作表Sheet1的D6單元格中放入C6與工作表Sheet2中單元格B2的乘積，可在Sheet1的D6單元格中輸入“=C6*Sheet2！B2”。此外，也可以引用不同工作簿文件中的單元格，此時需注明工作簿的文件名。4.1Excel基礎應用——制作“學生成績表”4.1.4案例總結(jié)在進行公式和函數(shù)計算時，要熟悉公式的輸入規(guī)則、函數(shù)的輸入方法、單元格的3種引用方式和跨工作表和工作簿的單元格引用等，還應注意以下幾點：①公式是對單元格中數(shù)據(jù)進行計算的等式，公式必須以“=”開始。②函數(shù)的引用形式為：函數(shù)名(參數(shù)1，參數(shù)2，…)，參數(shù)之間必須用逗號隔開。如果單獨使用函數(shù)，需在函數(shù)名稱前面輸入“=”構(gòu)成公式。③公式中的單元格引用可分為相對引用、絕對引用和混合引用3種。要特別注意這3種引用的適用場合。4.1Excel基礎應用——制作“學生成績表”4.2.1“成績統(tǒng)計分析表”案例分析●1.任務的提出

在上一案例中，劉老師已經(jīng)順利完成了“學生成績表”的制作，現(xiàn)在他要根據(jù)“學生成績表”中的數(shù)據(jù)，制作“成績統(tǒng)計分析表”、“各科成績等級表”此外還要根據(jù)“成績統(tǒng)計分析表”中的數(shù)據(jù)制作“成績統(tǒng)計分析圖”，以便對全班的成績進行各種統(tǒng)計分析。當前工作表中沒有計算所需要的源數(shù)據(jù)，如何分析和統(tǒng)計數(shù)據(jù)？如何快速地將每位學生的分數(shù)換算成相應的成績等級？能否快速準確地統(tǒng)計人數(shù)

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”困惑4.2.1“成績統(tǒng)計分析表”案例分析●1.任務的提出

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.1“成績統(tǒng)計分析表”案例分析●2.解決方法要引用其他工作表的數(shù)據(jù)，只要從源數(shù)據(jù)所在工作表中選擇數(shù)據(jù)區(qū)域即可統(tǒng)計人數(shù)，可以使用COUNT、COUNTIF等函數(shù)來完成制作“各科成績等級表”則必須使用IF函數(shù)

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.2相關(guān)知識點●1.函數(shù)與函數(shù)語法函數(shù)是一種預先定義好的、經(jīng)常使用的內(nèi)置公式。Excel提供了很多內(nèi)部函數(shù)，用戶需要時，可按照函數(shù)的格式直接引用。函數(shù)由函數(shù)名和參數(shù)組成，其形式為：

函數(shù)名（參數(shù)1，參數(shù)2，…）

①函數(shù)名可以大寫也可以小寫，當有兩個以上的參數(shù)時，參數(shù)之間要用逗號隔開。例如，函數(shù)SUM（10，20，30），其中SUM是函數(shù)名，表示函數(shù)的功能，10、20、30是參數(shù)，表示函數(shù)運算的數(shù)據(jù)。②參數(shù)可以是數(shù)字、文本、邏輯值、數(shù)組或單元格引用。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.2相關(guān)知識點●2.數(shù)學與三角函數(shù)（1）取整函數(shù)INT格式：INT（number）功能：將數(shù)值向下取整為最接近的整數(shù)。（2）取余函數(shù)MOD格式：MOD（number,divisor）功能：返回兩數(shù)相除后的余數(shù)，結(jié)果正負號與除數(shù)相同。參數(shù)：number為被除數(shù)，divisor為除數(shù)，若為0，則返回#DIV/0!。（3）四舍五入函數(shù)ROUND格式：ROUND(number,num_digits)功能：按指定位數(shù)對數(shù)值進行四舍五入。參數(shù)：number為需要四舍五入的數(shù)字，num_digits為指定的位數(shù)，按此位數(shù)進行四舍五入。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.2相關(guān)知識點●3.統(tǒng)計函數(shù)（1）求和函數(shù)格式：SUM(number1,number2,…)功能：返回參數(shù)列表中所有數(shù)值之和。參數(shù)：number1,number2,…為1～30個需求和的參數(shù)，可為數(shù)字、文本方式的數(shù)字、文本、邏輯值、單元格引用或單元格區(qū)域的引用等。（2）求平均函數(shù)AVERAGE格式：AVERAGE（number1,number2,…）功能：返回參數(shù)列表中所有數(shù)值的平均值。（3）計數(shù)函數(shù)COUNT格式：COUNT（number1,number2,…）功能：返回參數(shù)列表中數(shù)字的個數(shù)。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.2相關(guān)知識點●3.統(tǒng)計函數(shù)（4）COUNTA（number1,number2,…）格式：COUNTA功能：返回參數(shù)組中非空值的數(shù)目。利用函數(shù)COUNTA可以計算數(shù)組或單元格區(qū)域中數(shù)據(jù)項的個數(shù)。（5）最大值函數(shù)MAX格式：MAX（number1，number2，…）功能：返回一組數(shù)中的最大值，忽略邏輯值及文本字符。（6）最小值函數(shù)MIN格式：MIN（number1，number2，…）功能：返回一組數(shù)中的最小值，忽略邏輯值及文本字符。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.2相關(guān)知識點●4.邏輯函數(shù)（1）條件函數(shù)IF格式：IF（logical_test,value_if_true,value_if_false）功能：執(zhí)行真假值判斷，根據(jù)對指定條件進行邏輯判斷的真假而返回不同的結(jié)果。參數(shù)：logical_test為條件表達式；value_if_true是logical_test為true時的返回值；value_if_false是logical_test為false時的返回值。如：IF（A10﹤﹦100，"正常"，"超支"）

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.2相關(guān)知識點●4.邏輯函數(shù)（1）條件函數(shù)IF【例4-4】

根據(jù)學生成績來確定A、B、C、D、E五個等級，如表4-1所示。此時，可使用下列嵌套IF函數(shù)：IF（成績﹥89,"A",IF（成績﹥79,"B",IF（成績﹥69,"C",IF（成績﹥59,"D","E"））））。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.2相關(guān)知識點●4.邏輯函數(shù)（2）取反函數(shù)NOT格式：NOT（logical）功能：對參數(shù)值求反。當要確保一個值不等于某一特定值時，可以使用NOT函數(shù)。參數(shù)：logical為邏輯值的常量、變量或公式。（3）真值函數(shù)TRUE格式：TURE()功能：返回邏輯真值。（4）假值函數(shù)FALSE格式：FALSE()功能：返回邏輯假值。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.2相關(guān)知識點●4.邏輯函數(shù)（5）與函數(shù)AND格式：AND（logical1,logical2,…）功能：當參數(shù)的邏輯值為真時返回TRUE；只要一個參數(shù)的邏輯值為假即返回FALSE。參數(shù)：logical1,logical2,…表示待檢測的1～30個條件值，各條件值或為TRUE，或為FALSE。（6）或函數(shù)OR格式：OR(logical1,logical2,…)功能：在其參數(shù)組中，任何一個參數(shù)邏輯值為TRUE，即返回TRUE。參數(shù)：logical1,logical2,…為1～30個要測試的條件，這些條件的結(jié)果是TRUE或FALSE。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.2相關(guān)知識點●5.數(shù)據(jù)庫函數(shù)常用的數(shù)據(jù)庫函數(shù)有：

DSUM（database,field,criteria）：對數(shù)據(jù)庫中的某個字段的數(shù)字求和。

DAVERAGE（database,field,criteria）：對數(shù)據(jù)庫中的某個字段的數(shù)字求平均值。

DCOUNT（database,field,criteria）：統(tǒng)計數(shù)據(jù)庫中的某個字段的數(shù)字個數(shù)。

DMAX（database,field,criteria）：求數(shù)據(jù)庫中的某個字段的最大數(shù)。

DMIN（database,field,criteria）：求數(shù)據(jù)庫中的某個字段的最小數(shù)。參數(shù)：

Database為包含數(shù)據(jù)清單的區(qū)域，必須包括字段名行。

Field為要操作的字段或字段名所在的單元格引用，包括字段名（外加西文引號）；要操作的字段相對數(shù)據(jù)清單第一列的偏移量。

Criteria為包含條件的單元格區(qū)域。條件區(qū)域的建立同高級篩選。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.2相關(guān)知識點●6.日期與時間函數(shù)（1）日期函數(shù)DATE格式：DATE（year,month,day）功能：返回某一指定日期的序列數(shù)。（2）取年份函數(shù)YEAR格式：YEAR（serial_number）功能：返回日期序列數(shù)所代表的年份（1900～9999）。（3）取月份函數(shù)MONTH格式：MONTH（serial_number）功能：將序列數(shù)轉(zhuǎn)換為對應的月份數(shù)。（4）取日函數(shù)DAY格式：DAY（serial_number）功能：將序列數(shù)轉(zhuǎn)換為對應的日。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.2相關(guān)知識點●6.日期與時間函數(shù)（5）取日期函數(shù)TODAY格式：TODAY()功能：返回計算機系統(tǒng)內(nèi)部時鐘的當前日期。（6）取當前時間函數(shù)NOW格式：NOW()功能：返回計算機系統(tǒng)內(nèi)部時鐘的當前日期和時間。（7）取指定時間函數(shù)TIME格式：TIME（hour,minute,second）功能：返回指定時間的系列號，系列號為0～0.99999999的小數(shù)。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.2相關(guān)知識點●7.圖表Excel能將工作表中的數(shù)據(jù)以各種統(tǒng)計圖表的形式顯示，使得數(shù)據(jù)更加直觀、易懂。Excel提供了11種標準圖表類型。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法●1.求及格率、優(yōu)秀率（1）E3:E12中是本班的大學英語成績，求英語的及格率。操作步驟如下：①將鼠標移到E16單元格，并輸入公式=COUNTIF(E3:E12,">=60")/COUNT(E3:E12)后回車。②把鼠標指針移動到填充柄上按下鼠標左鍵向右拖動鼠標即可算出其他課程的及格率。③在“開始”選項卡中單擊“數(shù)字”選項組中的“百分比樣式”按鈕，將及格率設置成百分比樣式，單擊“數(shù)字”選項組中的“增加小數(shù)位數(shù)”按鈕，將及格率保留2位小數(shù)。任務1：建立成績統(tǒng)計分析表

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法●1.求及格率、優(yōu)秀率（2）E3:E12中是本班的大學英語成績，求英語的優(yōu)秀率。操作步驟如下：①將鼠標移到E17單元格，并輸入公式=COUNTIF(E3:E12,">=90")/COUNT(E3:E12)后回車。②把鼠標指針移動到填充柄上按下鼠標左鍵向右拖動鼠標即可算出其他課程的優(yōu)秀率和及格率。③在“開始”選項卡中單擊“數(shù)字”選項組中的“百分比樣式”按鈕，將優(yōu)秀率設置成百分比樣式，單擊“數(shù)字”選項組中的的“增加小數(shù)位數(shù)”按鈕，將優(yōu)秀率保留2位小數(shù)。任務1：建立成績統(tǒng)計分析表

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法●2.統(tǒng)計各分數(shù)段的人數(shù)①在“成績統(tǒng)計分析表”中，選擇目標單元格E18（對應“90～100人數(shù)”）。②在編輯欄處輸入“=COUNTIF(E3:E12,">=90")”，按Enter鍵確認。③在“成績統(tǒng)計分析表”中，選擇目標單元格E19（對應“80～89人數(shù)”）。④在編輯欄處輸入“=COUNTIF(E3:E12,">=80")-E18”，按Enter鍵確認。⑤在“成績統(tǒng)計分析表”中，選擇目標單元格E20（對應“70～79人數(shù)”）。⑥在編輯欄處輸入“=COUNTIF(E3:E12,">=70")-E18-E19”，按Enter鍵確認。⑦在“成績統(tǒng)計分析表”中，選擇目標單元格E21（對應“60～69人數(shù)”）。⑧在編輯欄處輸入“=COUNTIF(E3:E12,">=60")-E18-E19-E20”，按Enter鍵確認。⑨在“成績統(tǒng)計分析表”中，選擇目標單元格E22（對應“0～59人數(shù)”）。⑩在編輯欄處輸入“=COUNTIF(E3:E12,"<60")”，按Enter鍵確認。任務1：建立成績統(tǒng)計分析表

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法任務1：建立成績統(tǒng)計分析表統(tǒng)計分析結(jié)果：

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法●1.單元格數(shù)據(jù)的刪除與清除操作實例29：將“成績統(tǒng)計分析表”工作表復制一份，并將復制后的工作表更名為“各科等級表”。在“各科等級表”中清除“大學英語”、“高等數(shù)學”、“大學語文”及“計算機應用”列中的分數(shù)內(nèi)容，并清除總分、“名次”列的所有屬性；刪除“平均分”、“最高分”、“最低分”等下面各行的所有單元格區(qū)域。方法：（1）在“開始”選項卡中單擊“編輯”選項組中的“清除”按鈕，在下拉列表中選擇“清除內(nèi)容”命令，或按Del鍵，只清除選定單元格的內(nèi)容，而保留了單元格的格式。（2）在“開始”選項卡中單擊“編輯”選項組中的“清除”按鈕，在下拉列表中選擇“全部清除”命令，此時這兩列中的內(nèi)容和格式全部被清除。任務2：用IF函數(shù)制作“各科等級表”

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法●2.條件函數(shù)IF的使用條件函數(shù)IF最多可以嵌套7層，用value_if_true及value_if_false參數(shù)可以構(gòu)造復雜的檢測條件。現(xiàn)在以“根據(jù)學生分數(shù)評定成績等級”為例，說明IF嵌套函數(shù)的使用方法。分數(shù)與等級的對應關(guān)系如表4-2所示。任務2：用IF函數(shù)制作“各科等級表”

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法●2.條件函數(shù)IF的使用操作實例30：按表4-1所示的分數(shù)與等級的對應關(guān)系，利用IF嵌套函數(shù)根據(jù)“成績統(tǒng)計分析表”中的大學英語、高等數(shù)學、大學語文、計算機基礎4門課程的分數(shù)，在“各科等級表”的對應科目中設置相應的等級。操作步驟如下：①在“各科等級表”工作表中，選擇目標單元格E3。②在E3中輸入“=IF('成績統(tǒng)計分析表'!E3>=90,"優(yōu)秀",IF('成績統(tǒng)計分析表'!E3>=80,"良好",IF('成績統(tǒng)計分析表'!E3>=70,"中等",IF('成績統(tǒng)計分析表'!E3>=60,"及格","不及格"))))”，其中第一個IF函數(shù)中value_if_false參數(shù)共嵌套了4層IF函數(shù)，從第一層到第四層的輸入情況如圖4-33所示。任務2：用IF函數(shù)制作“各科等級表”

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法任務3：分類匯總在分類匯總框中，單擊“全部刪除”命令按鈕可取消分類匯總的結(jié)果。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法●2.條件函數(shù)IF的使用任務2：用IF函數(shù)制作“各科等級表”③單擊“確定”按鈕后，即可設置“李麗麗的大學英語”課程所對應的等級。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法●2.條件函數(shù)IF的使用④使用公式填充方式，填充至H3處，即可得到李麗麗的高等數(shù)學、大學語文、計算機基礎3門課程所對應的等級。⑤選定區(qū)域E3:H3，拖動填充手柄至E12:H12處，即可得到其他同學的大學英語、高等數(shù)學、大學語文、計算機基礎4門課程所對應的等級。任務2：用IF函數(shù)制作“各科等級表”

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法Excel提供的分類匯總功能，即對所有具有相同值的行進行求和、計數(shù)、求均值等計算。分類匯總時必須先按所依據(jù)的列進行排序。操作實例31：為了比較同學中黨員與非黨員的學習情況，現(xiàn)要求根據(jù)是否為黨員對大學英語、高等數(shù)學、大學語文、計算機基礎4門課程和總分這五項進行匯總（即求這五項的平均成績）。①在成績統(tǒng)計分析表中，按“是否黨員”列排序。②選擇“數(shù)據(jù)”→“分類匯總”命令，在分類匯總對話框（見圖4-34）中指定分類字段為“是否黨員”、匯總方式為“平均值”、選定的匯總項為“大學英語”、“高等數(shù)學”、“大學語文”、“計算機基礎”、“總分”。單擊“確定”按鈕，分類匯總結(jié)果如圖4-35所示。任務3：分類匯總

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法任務3：分類匯總

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法任務4：制作成績統(tǒng)計分析圖Excel中的圖表分兩種，一種是嵌入式圖表，它和創(chuàng)建圖表的數(shù)據(jù)源放置在同一張工作表中，打印時也同時打印；另一種是獨立圖表，它是一張獨立的圖表工作表，打印時也將與數(shù)據(jù)表分開打印。圖表的生成有兩種途徑：利用圖表工具生成圖表或直接按F11鍵快速創(chuàng)建圖表。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法任務4：制作成績統(tǒng)計分析圖●1.利用圖表工具生成圖表操作步驟如下：

（1）選定需生成圖表的數(shù)據(jù)區(qū)域。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法任務4：制作成績統(tǒng)計分析圖●1.利用圖表工具生成圖表操作步驟如下：

（2）設置圖表的類型。

打開“圖表”對話框從中選擇所需圖表類型。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法任務4：制作成績統(tǒng)計分析圖●1.利用圖表工具生成圖表操作步驟如下：

（3）設置圖表數(shù)據(jù)來源。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法任務4：制作成績統(tǒng)計分析圖●1.利用圖表工具生成圖表操作步驟如下：

（4）設置圖表選項。

在“圖表工具/布局”選項卡中，通過“標簽”、“坐標軸”、“背景”選項組中的按鈕或命令，用戶可以設置一些數(shù)據(jù)系列以外的選項，包括可對圖表標題、坐標軸標題、圖例、數(shù)據(jù)標簽、坐標軸、網(wǎng)格線、繪圖區(qū)等，如圖4-39所示。圖4-39“圖表工具/布局”選項卡

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法任務4：制作成績統(tǒng)計分析圖●1.利用圖表工具生成圖表操作步驟如下：

（5）設置圖表位置。若選取“新工作表”，則工作簿會插入一張新的工作表來存放圖表，圖4-40中“新工作表”右欄可設置工作表的標志名稱。若選取“對象位于”，表示要將圖表放在現(xiàn)有的工作表中，成為工作表中的一個圖表對象。可在右側(cè)的下拉列表窗口中選取欲存到哪一個工作表，結(jié)果如圖4-41所示。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法任務4：制作成績統(tǒng)計分析圖●1.利用圖表工具生成圖表操作步驟如下：

（5）設置圖表位置。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法任務4：制作成績統(tǒng)計分析圖●1.利用圖表工具生成圖表操作步驟如下：

（5）設置圖表位置。本例選取“新工作表”，轉(zhuǎn)換成獨立圖表放在Chart1中，結(jié)果如圖4-42所示。圖4-42轉(zhuǎn)換成獨立圖表

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法任務4：制作成績統(tǒng)計分析圖●2.利用快捷鍵F11快速建立圖表接續(xù)上例，選擇數(shù)據(jù)區(qū)域B2、E2:H2、B18:B22、E18:H22，按F11鍵，則Excel會以默認的圖表類型（柱狀圖）快速產(chǎn)生一份圖表文件，其結(jié)果如圖4-42所示。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法任務4：制作成績統(tǒng)計分析圖●3.格式化圖表圖表建立完成后，如果顯示的效果不太美觀，可以對圖表的外觀進行適當?shù)馗袷交?，即對圖表的各個對象進行一些必要的修飾，使其更協(xié)調(diào)、更美觀。因此，在格式化圖表之前，必須先熟悉圖表的組成以及選擇圖表對象的方法。操作實例33：將操作實例34中生成的“成績統(tǒng)計分析圖”中的各圖表對象進行格式化設置。圖表的組成及各對象的名稱：圖表區(qū)、繪圖區(qū)、圖表標題、數(shù)據(jù)軸標題、分類軸標題、圖例等，如圖4-43所示。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法任務4：制作成績統(tǒng)計分析圖●3.格式化圖表

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法任務4：制作成績統(tǒng)計分析圖●3.格式化圖表選擇圖表對象進行格式化設置，通常可采用以下4種方法：①雙擊圖表對象，直接打開格式設置對話框，這種方法最方便、快捷，也最常用。②用鼠標指向圖表對象，單擊鼠標右鍵，從彈出的快捷菜單中選擇格式設置命令。③選定圖表對象，此時“圖表工具/格式”選項卡中會出現(xiàn)相應的格式設置命令。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法任務4：制作成績統(tǒng)計分析圖●3.格式化圖表④在“圖表工具/格式”或“圖表工具/布局”選項卡的“當前所選內(nèi)容”選項組（見圖4-44）中，單擊“圖表元素”下拉列表中選擇圖表對象，再單擊下方的“設置所選內(nèi)容格式”按鈕，打開對象格式設置對話框。這種方法對于一些不太容易從圖表中直接選擇的對象，如網(wǎng)格線、分類軸等的選擇非常有效。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法任務4：制作成績統(tǒng)計分析圖●4.修改圖表（1）更改圖表類型操作實例：把如圖4-41所示的柱形圖改用“（XY）散點圖”來表示成績統(tǒng)計圖。操作步驟如下：①在條形圖的圖表區(qū)內(nèi)單擊鼠標左鍵，選取條形圖對象，此時條形圖周圍會出現(xiàn)8個控點。②單擊“圖表工具/設計”選項卡的“更改圖表類型”按鈕，從彈出的對話框中選取要變換的圖表類型，如“（XY）散點圖”，則條形圖就會變?yōu)椤埃╔Y）散點圖”，如圖4-45所示。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法任務4：制作成績統(tǒng)計分析圖●4.修改圖表（1）更改圖表類型圖4-45用“（XY）散點圖”表示的成績統(tǒng)計圖

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法任務4：制作成績統(tǒng)計分析圖●4.修改圖表（2）更改數(shù)據(jù)區(qū)域操作實例35：更改數(shù)據(jù)區(qū)域，只查看大學英語和高等數(shù)學兩門課的情況。操作步驟如下：①單擊“圖表工具/設計”選項卡“數(shù)據(jù)”選項組中的“選擇數(shù)據(jù)”按鈕，彈出“選擇源數(shù)據(jù)”對話框。②到工作表中重新選取數(shù)據(jù)范圍，假定數(shù)據(jù)區(qū)域為B2、E2:F2、B18:B22、E18:F22，如圖4-46所示。③單擊“確定”按鈕即完成更改，如圖4-47所示。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法任務4：制作成績統(tǒng)計分析圖●4.修改圖表（2）更改數(shù)據(jù)區(qū)域圖4-46更改數(shù)據(jù)區(qū)域為B2，E2:F2，B18:B22，E18:F22與“選擇源數(shù)據(jù)”對話框

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.3實現(xiàn)方法任務4：制作成績統(tǒng)計分析圖●4.修改圖表（2）更改數(shù)據(jù)區(qū)域圖4-47更改數(shù)據(jù)區(qū)域示意圖

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.4案例總結(jié)本案例主要通過統(tǒng)計函數(shù)COUNT、COUNTA、COUNTIF、條件函數(shù)IF的使用來介紹函數(shù)的用法，通過具體圖表的創(chuàng)建、格式化、圖表的修改來介紹圖表的用法。1．幾個統(tǒng)計函數(shù)的區(qū)別COUNT、COUNTA、COUNTIF函數(shù)用于統(tǒng)計指定范圍內(nèi)單元格的個數(shù)。

COUNT函數(shù)返回包含數(shù)字以及參數(shù)列表中數(shù)字型的單元格的個數(shù)。

COUNTA函數(shù)返回參數(shù)列表中非空值的單元格個數(shù)，單元格的類型不限。

COUNTIF函數(shù)返回指定區(qū)域內(nèi)滿足給定條件的單元格個數(shù)。在使用COUNTIF函數(shù)時應該注意，當公式需要復制時，如果參數(shù)Range的引用固定不變，通常應使用絕對引用或區(qū)域命名方式實現(xiàn)；如果參數(shù)Criteria是表達式或字，必須在兩邊加上西文雙引號。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.2.4案例總結(jié)2．條件函數(shù)IF條件函數(shù)IF用于判斷給出的條件是否滿足，如果滿足返回邏輯值為真時的值，不滿足則返回邏輯值為假時的值。當邏輯判斷給出的條件多于兩個時，通常采用IF函數(shù)嵌套，即將一個IF函數(shù)的返回值作為另一個IF函數(shù)的參數(shù)。3．圖表圖表比數(shù)據(jù)更易于表達數(shù)據(jù)之間的關(guān)系以及數(shù)據(jù)變化的趨勢。對圖表的操作主要包括：創(chuàng)建各種類型的圖表、圖表的修改、圖表的格式化等。制作圖表時，應了解表現(xiàn)不同的數(shù)據(jù)關(guān)系時如何選擇合適的圖表類型，特別要注意正確選擇源數(shù)據(jù)。圖表既可以插入到工作表中，生成嵌入圖表，也可以生成一張單獨的工作表。在對圖表進行格式化時，要注意正確選擇圖表中的對象，不同對象的格式化設置選項是不同的。

4.2Excel綜合應用——制作“成績統(tǒng)計分析表”4.3.1連鎖超市銷售案例分析●1.任務的提出小張和小李在華潤萬家連鎖超市總公司的銷售部實習，本周的工作任務是統(tǒng)計上周周六各超市飲料的銷售情況并進行分析，具體要求是統(tǒng)計各連鎖超市的銷售額、毛利潤、各區(qū)域的飲料銷售情況和各區(qū)域銷售最好的飲料。已制作工作表：“銷售記錄”工作表記錄了上周周六各超市的銷售情況；“飲料價格”工作表給出了每種飲料的“單位”、“進價”和“售價”；“超市”工作表給出了各超市的名稱和各超市所在的地區(qū)。

4.3Excel高級應用——連鎖超市銷售數(shù)據(jù)分析4.3.1連鎖超市銷售案例分析●1.任務的提出圖4-48各超市的銷售記錄圖4-49飲料價格圖4-50超市信息

4.3Excel高級應用——連鎖超市銷售數(shù)據(jù)分析4.3.1連鎖超市銷售案例分析●1.任務的提出困惑：（1）如何讓Excel根據(jù)飲料名稱就能自動查找該飲料的“單位”、“進價”和“售價”等信息，并引用到“銷售記錄”工作表的相應單元格中？（2）如何從很長的銷售記錄中能快速進行一些特別的查詢操作？

4.3Excel高級應用——連鎖超市銷售數(shù)據(jù)分析4.3.1連鎖超市銷售案例分析●2.解決方法查找函數(shù)VLOOKUP能夠在數(shù)據(jù)區(qū)域的第1列中查找指定的數(shù)值，并返回數(shù)據(jù)區(qū)域當前行中指定列處的數(shù)值統(tǒng)計各超市或各區(qū)的銷售情況，則可以用分類匯總或數(shù)據(jù)透視表來實現(xiàn)進行數(shù)據(jù)查詢可以對數(shù)據(jù)進行排序或利用自動篩選或高級篩選來實現(xiàn)

4.3Excel高級應用——連鎖超市銷售數(shù)據(jù)分析4.3.2相關(guān)知識點●1.VLOOKUP函數(shù)格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)功能：查找數(shù)據(jù)區(qū)域首列滿足條件的元素，并返回數(shù)據(jù)區(qū)域當前行中指定列處的值。參數(shù)：lookup_value：查找的內(nèi)容。

table_array：查找的區(qū)域。

col_index_num：查找區(qū)域中的第幾列。

range_lookup：精確查找或模糊查找，F(xiàn)ALSE表示是模糊查找；TRUE或忽略表示是精確查找。注意：要查找的對象（參數(shù)1）一定要定義在查找數(shù)據(jù)區(qū)域（參數(shù)2）的第l列。

4.3Excel高級應用——連鎖超市銷售數(shù)據(jù)分析4.3.2相關(guān)知識點●2.分類匯總分類匯總是指對工作表中的某一項數(shù)據(jù)進行分類，再對需要匯總的數(shù)據(jù)進行匯總計算。在分類匯總前要先對分類字段進行排序?！?.數(shù)據(jù)透視表數(shù)據(jù)透視表是一種交互式工作表，用于對現(xiàn)有工作表進行匯總和分析。創(chuàng)建數(shù)據(jù)透視表后，可以按不同的需要、依不同的關(guān)系來提取和組織數(shù)據(jù)。

4.3Excel高級應用——連鎖超市銷售數(shù)據(jù)分析4.3.2相關(guān)知識點●4.數(shù)據(jù)篩選數(shù)據(jù)篩選是指從數(shù)據(jù)庫或數(shù)據(jù)清單眾多的數(shù)據(jù)行中找出滿足一定條件的幾行或幾列數(shù)據(jù)。數(shù)據(jù)篩選將把數(shù)據(jù)清單或數(shù)據(jù)庫中所有不滿足條件的記錄行暫時隱藏起來，只顯示那些滿足條件的記錄行。篩選前與篩選后的工作表都是同一個，篩選后只是把原工作表中不滿足條件的記錄行隱藏起來而已。Excel提供了2種不同的方式用于數(shù)據(jù)篩選，它們是自動篩選和高級篩選。自動篩選可以很輕松地顯示數(shù)據(jù)表中滿足條件的記錄行，高級篩選能提供多條件的、復雜的條件查詢。

4.3Excel高級應用——連鎖超市銷售數(shù)據(jù)分析4.3.2相關(guān)知識點●5.固定表頭凍結(jié)窗格的功能是將某些行、列的位置固定，不隨垂直或水平滾動條上滑塊的移動而移動。該功能在編輯行、列數(shù)據(jù)很多的Excel工作表時很有用。例如，一張Excel工作表中，存放了4000多個地球土壤化學樣品的30多種元素的含量分析結(jié)果。第一行作為標題行，為了查看方便，希望在移動垂直滾動條滑塊時，第一行固定不動。這就需要使用凍結(jié)窗格功能固定表頭。

4.3Excel高級應用——連鎖超市銷售數(shù)據(jù)分析4.3.3實現(xiàn)方法●1.創(chuàng)建“單位”的查找公式操作實例36：用VLOOKUP函數(shù)填寫工作表中的“單位”。任務1：用VLOOKUP函數(shù)計算“單位”、“進價”和“售價”等

4.3Excel高級應用——連鎖超市銷售數(shù)據(jù)分析4.3.3實現(xiàn)方法●1.創(chuàng)建“單位”的查找公式操作實例36：用VLOOKUP函數(shù)填寫工作表中的“單位”。任務1：用VLOOKUP函數(shù)計算“單位”、“進價”和“售價”等

4.3Excel高級應用——連鎖超市銷售數(shù)據(jù)分析4.3.3實現(xiàn)方法●2.使用復制公式的方式查找“進價”和“售價”操作實例37：用VLOOKUP函數(shù)填寫操作實例38工作表中的“進價”和“售價”，并計算出“銷售額”和“毛利潤”。操作步驟如下：①使用復制公式的方式，從D3沿著列拖動時到D171（即最后一行），即可確定出“銷售記錄”工作表中各行所對應的“單位”。②按照與上面查找“單位”相同的方法，完成下面的內(nèi)容：在G2單元格中輸入公式：=VLOOKUP(D2,飲料價格!$B$2:$E$18,3,FALSE)，并用公式復制的方式沿著列拖動到最后一行。在H2單元格中輸入公式：=VLOOKUP(D2,飲料價格!$B$2:$E$18,4,FALSE)，并用公式復制的方式沿著列拖動到最后一行。任務1：用VLOOKUP函數(shù)計算“單位”、“進價”和“售價”等

4.3Excel高級應用——連鎖超市銷售數(shù)據(jù)分析4.3.3實現(xiàn)方法●2.使用復制公式的方式查找“進價”和“售價”操作實例37：用VLOOKUP函數(shù)填寫操作實例38工作表中的“進價”和“售價”，并計算出“銷售額”和“毛利潤”。操作步驟如下：③用公式計算“銷售額”和“毛利潤”兩列的值。在I2單元格中輸入公式：=H2*E2（即銷售額=售價×數(shù)量），并用公式復制的方式沿著列拖動到最后一行。在J2單元格中輸入公式：=(H2-G2)*E2（即毛利潤=（售價-進價）×數(shù)量，并用公式復制的方式沿著列拖動到最后一行。最后，計算出結(jié)果的“銷售記錄”工作表，如圖4-53所示。任務1：用VLOOKUP函數(shù)計算“單位”、“進價”和“售價”等

4.3Excel高級應用——連鎖超市銷售數(shù)據(jù)分析4.3.3實現(xiàn)方法任務1：用VLOOKUP函數(shù)計算“單位”、“進價”和“售價”等圖4-53計算出結(jié)果的“銷售記錄”工作表

4.3Excel高級應用——連鎖超市銷售數(shù)據(jù)分析4.3.3實現(xiàn)方法操作實例38：續(xù)上例，用“分類匯總”統(tǒng)計上周六的銷售額和毛利潤。操作步驟如下：①建立表“銷售記錄”的兩個副本“銷售記錄②”和“銷售記錄③”。②在“銷售記錄②”工作表中按“店名”排序。③在菜單欄中選擇“數(shù)據(jù)”→“分類匯總”命令，打開“分類匯總”對話框，按“店名”分類，然后對銷售額和毛利潤進行分類匯總，如圖4-54所示。任務2：統(tǒng)計各連鎖超市和各個區(qū)中各種飲料的“銷售額”和“毛利潤”

4.3Excel高級應用——連鎖超市銷售數(shù)據(jù)分析4.3.3實現(xiàn)方法任務2：統(tǒng)計各連鎖超市和各個區(qū)中各種飲料的“銷售額”和“毛利潤”

4.3Excel高級應用——連鎖超市銷售數(shù)據(jù)分析4.3.3實現(xiàn)方法任務2：統(tǒng)計各連鎖超市和各個區(qū)中各種飲料的“銷售額”和“毛利潤”④單擊“確定”按鈕后顯示出匯總結(jié)果，如圖4-55所示。圖4-55匯總結(jié)果

4.3Excel高級應用——連鎖超市銷售數(shù)據(jù)分析4.3.3實現(xiàn)方法任務3：用“數(shù)據(jù)透視表”分析各區(qū)各飲料的銷售情況和暢銷飲料操作實例39：用“數(shù)據(jù)透視表”統(tǒng)計各個區(qū)每種飲料的銷售情況。操作步驟如下：①單擊“銷售記錄”工作表數(shù)據(jù)清單中的任一單元格。②切換到“插入”選項卡，單擊“表格”選項組中“數(shù)據(jù)透視表”按鈕，打開如圖4-56所示的“創(chuàng)建數(shù)據(jù)透視表”對話框。③在該對話框中選擇要分析的數(shù)據(jù)，建立數(shù)據(jù)透視表的數(shù)據(jù)源區(qū)域，本例選擇整個“銷售記錄”工作表。④選擇放置數(shù)據(jù)透視表的位置。

4.3Excel高級應用——連鎖超市銷售數(shù)據(jù)分析4.3.3實現(xiàn)方法任務3：用“數(shù)據(jù)透視表”分析各區(qū)各飲料的銷售情況和暢銷飲料圖4-56“創(chuàng)建數(shù)據(jù)透視表”對話框

4.3Excel高級應用——連鎖超市銷售數(shù)據(jù)分析4.3.3實現(xiàn)方法任務3：用“數(shù)據(jù)透視表”分析各區(qū)各飲料的銷售情況和暢銷飲料在“創(chuàng)建數(shù)據(jù)透視表”對話框中，單擊“確定”按鈕，即可在工作表中插入數(shù)據(jù)透視表，出現(xiàn)數(shù)據(jù)透視表區(qū)域，如圖4-57所示，同時打開如圖4-58所示“數(shù)據(jù)透視表