《信息技術(shù)》教案

第四章電子表格處理一、教學(xué)目標(biāo)（一）知識目標(biāo)掌握電子表格的基本操作、數(shù)據(jù)計(jì)算、數(shù)據(jù)管理和分析、使用圖表分析數(shù)據(jù)的操作方法。（二）技能目標(biāo)1.掌握單元格、行和列的相關(guān)操作，掌握使用控制句柄和設(shè)置單元格格式的方法。2.掌握數(shù)據(jù)錄入的技巧，如快速輸入特殊數(shù)據(jù)、快速填充，掌握格式刷、邊框、對齊等常用格式設(shè)置。3.理解單元格絕對地址、相對地址的概念和區(qū)別，掌握相對引用、絕對引用及工作表外單元格的引用方法。4.熟悉公式和函數(shù)的使用，掌握平均值、最大/最小值、求和、計(jì)數(shù)等常見函數(shù)的使用。5.了解常見的圖表類型及電子表格處理工具提供的圖表類型，掌握利用表格數(shù)據(jù)制作常用圖表的方法。6.掌握自動篩選、自定義篩選、高級篩選、排序和分類匯總等操作。7.理解數(shù)據(jù)透視表的概念，掌握數(shù)據(jù)透視表的創(chuàng)建、更新數(shù)據(jù)、添加和刪除字段、查看明細(xì)數(shù)據(jù)等操作。（三）情感目標(biāo)1.培養(yǎng)學(xué)生認(rèn)真的學(xué)習(xí)態(tài)度。2.培養(yǎng)學(xué)生自主探索學(xué)習(xí)的意識。3.培養(yǎng)學(xué)生相互協(xié)作解決問題的意識。二、教學(xué)內(nèi)容1.格式化工作表：設(shè)置單元格格式、條件格式、行高/列寬等，使用樣式，自動套用格式。2.公式與函數(shù)：自動計(jì)算、輸入公式、復(fù)制公式、函數(shù)應(yīng)用。3.圖表：圖表概念，圖表的創(chuàng)建、編輯、修改等。4.工作表中的數(shù)據(jù)庫操作：數(shù)據(jù)篩選、分類匯總，建立數(shù)據(jù)透視表。三、重點(diǎn)難點(diǎn)1.重點(diǎn)：公式計(jì)算、圖表創(chuàng)建、分類匯總。2.難點(diǎn)：函數(shù)應(yīng)用、數(shù)據(jù)透視表的創(chuàng)建。四、教法學(xué)法1.教法：任務(wù)驅(qū)動教學(xué)法、案例教學(xué)法、探究式教學(xué)法。2.學(xué)法：自主學(xué)習(xí)法、探究學(xué)習(xí)法、合作學(xué)習(xí)法、多媒體學(xué)習(xí)法。

第一節(jié)Excel表格的美化1表格邊框1.1表格邊框的作用默認(rèn)情況下，工作表中的表格線都是淺色的，稱為網(wǎng)格線，它們在打印時(shí)并不顯示。為了打印帶邊框線的表格，可以為其添加不同線型的邊框。1.2操作要點(diǎn)選中單元格區(qū)域——點(diǎn)擊“開始”選項(xiàng)卡——“字體”選項(xiàng)組——“邊框”命令——打開“設(shè)置單元格格式”對話框——在“邊框”選項(xiàng)卡中設(shè)置表格邊框的樣式、顏色——在“預(yù)置”覽中選擇“外邊框”或者“內(nèi)部”框線。1.3實(shí)踐操作為“藥品庫存信息”工作表添加指定樣式的邊框。2表格填充效果2.1表格填充效果的作用在Excel中單元格默認(rèn)的顏色是白色，并且沒有圖案，可以為單元格添加適當(dāng)?shù)奶畛湫Ч嵘龜?shù)據(jù)表的可視化效果。2.2操作要點(diǎn)選中單元格區(qū)域——點(diǎn)擊“開始”選項(xiàng)卡——“字體”選項(xiàng)組——“填充”命令——設(shè)置背景色、填充效果、圖案顏色和圖案樣式等。2.3實(shí)踐操作為“藥品庫存信息”工作表的標(biāo)題行添加指定的填充效果。3套用表格格式3.1套用表格格式的作用對數(shù)據(jù)表的數(shù)據(jù)套用表格式，從而實(shí)現(xiàn)快速美化表格外觀的目的。3.2操作要點(diǎn)（1）套用表格格式：選中單元格區(qū)域——點(diǎn)擊“表設(shè)計(jì)”選項(xiàng)卡——“工具”選項(xiàng)組——“套用表格格式”命令——確認(rèn)數(shù)據(jù)來源區(qū)域是否正確——勾選“表包含標(biāo)題”。（2）轉(zhuǎn)換為普通區(qū)域：選中單元格區(qū)域——點(diǎn)擊“開始”選項(xiàng)卡——“樣式”選項(xiàng)組——“轉(zhuǎn)換為區(qū)域”命令3.3實(shí)踐操作為“藥品庫存信息”工作表套用指定表格樣式，并轉(zhuǎn)換成普通區(qū)域。4條件格式4.1條件格式的作用對單元格區(qū)域中滿足條件的數(shù)據(jù)進(jìn)行格式設(shè)置，如設(shè)置字體顏色、背景色、邊框等。4.2條件格式的規(guī)則在Excel中，條件格式提供了多種規(guī)則來幫助用戶根據(jù)特定條件突出顯示或改變單元格的格式。（1）突出顯示單元格規(guī)則：用于突出顯示大于、小于或等于某個(gè)特定值的單元格。（2）最前/最后規(guī)則：用于突顯在一組數(shù)據(jù)中排名最前或最后的單元格。（3）數(shù)據(jù)條：通過在單元格中添加漸變顏色的條形圖來直觀地表示單元格的值，數(shù)據(jù)條的長度或顏色深淺代表了值的大小。（4）色階：根據(jù)單元格的值，應(yīng)用不同的顏色漸變，顏色的深淺表示數(shù)據(jù)的大小。（5）圖標(biāo)集：使用一組圖標(biāo)來表示單元格的值是否滿足特定的條件。（6）新建規(guī)則：當(dāng)內(nèi)置的規(guī)則無法滿足需求時(shí)，用戶可以選擇“新建規(guī)則”來創(chuàng)建自定義的條件格式規(guī)則。4.3操作要點(diǎn)選中單元格區(qū)域——點(diǎn)擊“開始”選項(xiàng)卡——“樣式”選項(xiàng)組——“條件格式”命令——選擇設(shè)置條件的方式。4.4實(shí)踐操作為“藥品庫存信息”工作表中的特定數(shù)據(jù)設(shè)置條件格式，突出顯示關(guān)鍵信息。

第二節(jié)Excel公式計(jì)算1使用公式1.1公式概述（1）公式是對單元格中的數(shù)據(jù)進(jìn)行處理的等式，用于完成算術(shù)、比較或邏輯等運(yùn)算。（2）Excel中的公式遵循一個(gè)特定的語法，即以等于號開始，后面是用于計(jì)算的表達(dá)式。（3）表達(dá)式是用運(yùn)算符將常數(shù)、單元格引用和函數(shù)連接起來所構(gòu)成的算式。1.2運(yùn)算符介紹（1）算術(shù)運(yùn)算符：+、-、*、/、^、%，用于數(shù)值的四則運(yùn)算。（2）比較運(yùn)算符：=、>、<、>=、<=、<>，用于比較兩個(gè)值，返回邏輯結(jié)果,如果比較的結(jié)果成立，邏輯值為true，否則為false。（3）文本運(yùn)算符：&，用于連接兩個(gè)文本，形成一個(gè)新的連續(xù)的文本值。（4）引用運(yùn)算符：區(qū)域運(yùn)算符(:)與聯(lián)合運(yùn)算符(,)，用于將單元格區(qū)域進(jìn)行合并運(yùn)算。區(qū)域運(yùn)算符(:)是對指定區(qū)域之間包括兩個(gè)單元格在內(nèi)的所有單元格進(jìn)行引用，例如，A1:A4單元格區(qū)域，就是引用了A1、A2、A3、A4一共4個(gè)單元格；聯(lián)合運(yùn)算符(,)可以將多個(gè)引用合并為一個(gè)引用，例如，B1,B4單元格區(qū)域，就是引用了B1、B4一共2個(gè)單元格。1.3運(yùn)算符優(yōu)先級運(yùn)算符說明優(yōu)先級（和）圓括號，可以改變運(yùn)算的優(yōu)先級1-負(fù)號，使正數(shù)變?yōu)樨?fù)數(shù)2%百分號，將數(shù)字變?yōu)榘俜謹(jǐn)?shù)3^乘冪，一個(gè)數(shù)自乘一次4*和/乘法和除法5+和-加法和減法6&文本運(yùn)算符7>、<、>=、<=、<>比較運(yùn)算符82單元格引用在公式中是通過引用單元格的地址來使用其中存放的數(shù)據(jù)。引用分為相對引用和絕對引用，另外公式還可以引用其他工作表中的數(shù)據(jù)，稱之為跨表引用。2.1相對引用復(fù)制或移動公式時(shí)，引用單元格的行號、列標(biāo)會根據(jù)目標(biāo)單元格所在的行號、列標(biāo)的變化自動進(jìn)行調(diào)整。2.2絕對引用復(fù)制或移動公式時(shí)，不論目標(biāo)單元格在什么位置，公式中引用單元格的行號和列標(biāo)均保持不變。絕對引用需要在單元格名稱的行號和列標(biāo)前各添加一個(gè)“$”（如$A$1）。2.3跨表引用如果要引用其他工作表的單元格，就要在引用前說明單元格所在的工作表名稱，格式如sheet1!A1。2.4實(shí)踐操作（1）計(jì)算“藥品銷售信息”工作表中的“原始金額”，公式為“原始金額=單價(jià)*數(shù)量”。（2）計(jì)算“藥品銷售信息”工作表中的“實(shí)際金額”，公式為“實(shí)際金額=原始金額*優(yōu)惠率”。（3）為“藥品銷售信息”工作表的第一條銷售記錄添加銷售營業(yè)員姓名。

第三節(jié)Excel函數(shù)之IF1IF函數(shù)IF函數(shù)是條件判斷函數(shù)，它是excel函數(shù)中使用頻率最高的函數(shù)，也是最基礎(chǔ)的函數(shù)。其功能是：如果指定條件的計(jì)算結(jié)果為TRUE，IF函數(shù)將返回某個(gè)值；如果該條件的計(jì)算結(jié)果為FALSE，則返回另一個(gè)值。具體語法為：IF(logical_test,value_if_true,value_if_false)，其中Logical_test是判斷條件，即表示計(jì)算結(jié)果為TRUE或FALSE的任意值或表達(dá)式，Value_if_true表示判斷條件為TRUE時(shí)返回的值，Value_if_false表示判斷條件為FALSE時(shí)返回的值。示例：=IF(A1>=60,”及格”,”不及格”)，即如果A1大于等于60，則輸出及格，否則輸出不及格。2函數(shù)簡單使用下面通過一個(gè)實(shí)例來了解IF函數(shù)的基礎(chǔ)用法，如下所示，要求在數(shù)據(jù)表中根據(jù)“平均成績”列的成績利用IF函數(shù)給出“備注”列的內(nèi)容：如果平均成績大于82分，在相應(yīng)單元格內(nèi)填入“A”，否則在相應(yīng)單元格內(nèi)填入“B”。操作步驟：選中目標(biāo)單元格G3，輸入=IF，然后點(diǎn)擊tab鍵，緊接著點(diǎn)擊編輯欄中的“插入函數(shù)”，打開函數(shù)參數(shù)對話框。在“判斷條件”框中輸入F3大于82，在“條件為真的值”框中輸入A，在“條件為假的值”框中輸入B，點(diǎn)擊確定即可完成。最后利用填充柄下拉即可完成所有操作。3自身嵌套使用IF函數(shù)的嵌套分為自身嵌套和與其他函數(shù)的嵌套。IF函數(shù)自身嵌套是指在一個(gè)IF語句內(nèi)再嵌套一個(gè)或多個(gè)IF語句，用來處理更加復(fù)雜的條件判斷。其具體語法為：=IF(判斷條件1,條件1為真的值,IF(判斷條件2,條件2為真的值,IF(判斷條件3,條件3為真的值,IF(判斷條件4,條件4為真的值,……))))下面我們通過實(shí)例來講解IF函數(shù)的自身嵌套用法，如下所示，要求在數(shù)據(jù)表中根據(jù)“平均成績”列的成績，利用IF函數(shù)給出“備注”列的內(nèi)容：如果平均成績大于85分，在相應(yīng)單元格內(nèi)填入“A”，如果平均成績大于75分且小于等于85分，在相應(yīng)單元格內(nèi)填入“B”，否則在相應(yīng)單元格內(nèi)填入“C”。分析題目可知：判斷條件1大于85分，條件1為真的輸出結(jié)果為A，條件1為假則輸出結(jié)果為嵌套IF函數(shù)；而嵌套函數(shù)中判斷條件2為大于75分，條件2為真則輸出結(jié)果B，條件2為假則輸出結(jié)果為C。操作步驟：按要求選中目標(biāo)單元格G3，輸入=IF，然后點(diǎn)擊tab鍵，緊接著點(diǎn)擊編輯欄中的“插入函數(shù)”，打開函數(shù)參數(shù)對話框；在判斷條件框中輸入F3大于85，在條件為真框中輸入A，在條件為假框中嵌入IF函數(shù)，在框中輸入IF(F3>75,”B”,”C”)，然后點(diǎn)擊確定即可完成。最后利用填充柄下拉即可完成所有操作。4多函數(shù)嵌套IF函數(shù)除了自身嵌套外，很多時(shí)候是與其他函數(shù)嵌套使用的，其中嵌套AND和OR函數(shù)是Excel中最常用的一種結(jié)構(gòu)。在Excel中，IF函數(shù)可以實(shí)現(xiàn)條件判斷，而AND和OR函數(shù)則可以實(shí)現(xiàn)多個(gè)條件的判斷。將這三種函數(shù)結(jié)合起來，可以實(shí)現(xiàn)更加復(fù)雜的條件判斷。下面我們簡單了解一下其各自的用法，首先我們來學(xué)習(xí)IF函數(shù)與AND函數(shù)的嵌套使用，AND函數(shù)是邏輯函數(shù)，語法格式為：=AND(logical1,logical2,……)，其功能是檢查函數(shù)所有參數(shù)的計(jì)算結(jié)果為TRUE時(shí)，返回TRUE；只要有一個(gè)參數(shù)的計(jì)算結(jié)果為FALSE，即返回FALSE。IF函數(shù)嵌套AND函數(shù)的具體語法為=IF(AND(logical1,logical12,……),條件為真的值,條件為假的值)，其結(jié)構(gòu)與IF函數(shù)大同小異，下面我們通過實(shí)例來講解：如下所示，要求在數(shù)據(jù)表中根據(jù)“崗位性質(zhì)、出勤天數(shù)”列的內(nèi)容，利用IF函數(shù)給出“補(bǔ)助”列的金額：如果出勤大于等于18天并且是正式員工，每天補(bǔ)助20元。操作步驟：按要求選中目標(biāo)單元格D3，輸入=IF，然后點(diǎn)擊tab鍵，緊接著點(diǎn)擊編輯欄中的“插入函數(shù)”，打開函數(shù)參數(shù)對話框；在判斷條件框中輸入AND(B3=“正式”,C3>=18)，在條件為真框中輸入20,在條件為假框中輸入兩個(gè)英文雙引號，然后點(diǎn)擊確定即可完成。最后利用填充柄下拉即可完成所有操作。OR函數(shù)也是邏輯函數(shù)，其語法格式與AND函數(shù)相似，其功能是檢查函數(shù)的參數(shù)組中，任何一個(gè)參數(shù)邏輯值為TRUE，即返回TRUE；所有參數(shù)的邏輯值為FALSE，才返回FALSE。IF函數(shù)嵌套OR函數(shù)的具體語法為=IF(OR(logical1,logical12,……),條件為真的值,條件為假的值)，其結(jié)構(gòu)與IF函數(shù)嵌套AND函數(shù)大同小異，下面我們通過實(shí)例來講解：如下所示，要求在數(shù)據(jù)表中根據(jù)產(chǎn)品銷售數(shù)量列利用IF函數(shù)給出“業(yè)績表現(xiàn)”列的內(nèi)容：如果員工的A、B、C三項(xiàng)產(chǎn)品銷售數(shù)量中有一項(xiàng)大于或等于對應(yīng)的平均銷售數(shù)量，則其業(yè)績表現(xiàn)為優(yōu)，否則為空白。操作步驟：按要求選中目標(biāo)單元格E3，輸入=IF，然后點(diǎn)擊tab鍵，緊接著點(diǎn)擊編輯欄中的“插入函數(shù)”，打開函數(shù)參數(shù)對話框；在判斷條件框中輸入OR(B3>$B$13,C3>$C$13,D3>$D$13)，這里要特別注意，平均銷售數(shù)量行是固定不變的，所以需要使用絕對地址引用，在條件為真框中輸入優(yōu)秀，在條件為假框中輸入兩個(gè)英文雙引號，然后點(diǎn)擊確定即可完成。最后利用填充柄下拉即可完成所有操作。

第四節(jié)Excel函數(shù)之SUMIF等1SUMIF函數(shù)SUMIF函數(shù)是條件求和函數(shù)，它是Excel常用函數(shù)之一。使用SUMIF函數(shù)可以對數(shù)據(jù)報(bào)表范圍中符合指定條件的值求和。Excel中SUMIF函數(shù)的用法是根據(jù)指定條件對若干單元格、區(qū)域或引用求和。其語法格式為：=SUMIF(range,criteria,sum_range)其中：range是用于條件判斷的單元格區(qū)域；criteria是由數(shù)字、邏輯表達(dá)式等組成的判定條件；sum_range為需要求和的單元格、區(qū)域或引用。若sum_range省略，則條件區(qū)域就是實(shí)際求和區(qū)域。下面我們通過幾個(gè)實(shí)例來學(xué)習(xí)SUMIF函數(shù)的應(yīng)用。首先我們來看一下SUMIF函數(shù)的基礎(chǔ)用法：如下所示，要求在數(shù)據(jù)表中根據(jù)成績表計(jì)算三班的總成績，結(jié)果置于E3單元格中。操作步驟：選中目標(biāo)單元格E3，輸入=SUMIF，然后點(diǎn)擊tab鍵，緊接著點(diǎn)擊編輯欄中的“插入函數(shù)”，打開函數(shù)參數(shù)對話框；在“條件區(qū)域”框中輸入B3:B12，在“求和條件”框中輸入“=三班”,在“求和區(qū)域”框中輸入C3:C12，點(diǎn)擊確定即可完成。下面我們來學(xué)習(xí)SUMIF函數(shù)對指定條件之外的數(shù)據(jù)進(jìn)行求和：若我們要在數(shù)據(jù)表中根據(jù)成績表計(jì)算除了三班以外其他班級的總成績，結(jié)果置于E3單元格中。操作步驟：選中目標(biāo)單元格，輸入=SUMIF，然后點(diǎn)擊tab鍵，緊接著點(diǎn)擊編輯欄中的“插入函數(shù)”，打開函數(shù)參數(shù)對話框；在“條件區(qū)域”框中輸入B3:B12，在“求和條件”框中輸入“!=三班”,在“求和區(qū)域”框中輸入C3:C12，點(diǎn)擊確定即可完成。最后我們來學(xué)習(xí)SUMIF函數(shù)的隔列求和：若我們想在數(shù)據(jù)表中根據(jù)A1:H11單元格區(qū)域中的數(shù)據(jù)計(jì)算各超市、各類水果的一、二、三月總進(jìn)貨數(shù)，結(jié)果置于L3:L11單元格區(qū)域中，那該如何隔列求和呢？操作步驟：選中目標(biāo)單元格，輸入=SUMIF，然后點(diǎn)擊tab鍵，緊接著點(diǎn)擊編輯欄中的“插入函數(shù)”，打開函數(shù)參數(shù)對話框；在“條件區(qū)域”框中輸入$C$2:$H$2、利用絕對引用使區(qū)域固定不變，在“求和條件”框中輸入“進(jìn)貨”,在“求和區(qū)域”框中輸入C3:H3，點(diǎn)擊確定即可完成。最后利用填充柄下拉即可完成所有操作。2COUNTIF函數(shù)COUNTIF函數(shù)是條件計(jì)數(shù)函數(shù)，它是對指定區(qū)域中符合指定條件的單元格進(jìn)行計(jì)數(shù)的一個(gè)函數(shù)，其語法規(guī)則為：=COUNTIF(range,criteria)，其中，第一個(gè)參數(shù)是要計(jì)算其中非空單元格數(shù)目的區(qū)域，第二個(gè)參數(shù)是以數(shù)字、表達(dá)式或文本形式定義的條件。下面，我們將通過一些實(shí)例來講解COUNTIF函數(shù)的應(yīng)用。先來看一下如何計(jì)算數(shù)據(jù)表中成績分別等于、大于和小于60分的人數(shù)：操作步驟：選中目標(biāo)單元格E3，輸入=COUNTIF，然后點(diǎn)擊tab鍵，緊接著點(diǎn)擊編輯欄中的“插入函數(shù)”，打開函數(shù)參數(shù)對話框；在“區(qū)域”框中輸入B3:B17，在“條件”框中輸入"=60",點(diǎn)擊確定即可完成。同理，大于60分和小于60分的人數(shù)只需將“條件”框中的等于分別修改為大于和小于即可，而“區(qū)域”框保留不變。緊接著我們來看一看真空和非真空單元格的個(gè)數(shù)計(jì)算，所謂的真空是指什么內(nèi)容也沒有的單元格。計(jì)算真空單元格的個(gè)數(shù)，“區(qū)域”框保留不變，“條件”框中直接輸入“=”即可，代表等于空值。計(jì)算非真空單元格的個(gè)數(shù)，只需將“條件”框中的等于修改為“<>”即可。3AVERAGEIF函數(shù)AVERAGEIF函數(shù)是條件平均值函數(shù)，其功能是返回某個(gè)區(qū)域內(nèi)滿足給定條件的所有單元格的平均值，其語法格式為：=AVERAGEIF(range,criteria,average_range)它與SUMIF函數(shù)非常相似，同學(xué)們可以參照SUMIF函數(shù)。其中，range是要計(jì)算平均值的一個(gè)或多個(gè)單元格，其中包含數(shù)字或包含數(shù)字的名稱、數(shù)組或引用；criteria是形式為數(shù)字、表達(dá)式、單元格引用或文本的條件，用來定義將計(jì)算平均值的單元格；average_range是計(jì)算平均值的實(shí)際單元格組，如果省略，則使用條件區(qū)域。我們通過幾個(gè)實(shí)例來講解AVERAGEIF函數(shù)的應(yīng)用：如下所示，要求在數(shù)據(jù)表中根據(jù)A2:D20的數(shù)據(jù)利用AVERAGEIF函數(shù)分別計(jì)算任務(wù)1、任務(wù)2、任務(wù)3的平均分，結(jié)果對應(yīng)置于G4:H6、G12:G13、N4:N21單元格區(qū)域中。操作步驟：任務(wù)1是要求各班語文、數(shù)據(jù)平均分。選中目標(biāo)單元格，輸入=AVERAGEIF，然后點(diǎn)擊tab鍵，緊接著點(diǎn)擊編輯欄中的“插入函數(shù)”，打開函數(shù)參數(shù)對話框；在“條件區(qū)域”框中輸入$A$3:$B$20、絕對值引用，在“條件”框中輸入F4，在“求平均值區(qū)域”框中輸入$C$3:$C$20、同樣需要絕對值引用，點(diǎn)擊確定即可完成。同理，我們可以用相同的方法計(jì)算數(shù)學(xué)的平均值。最后利用填充柄下拉即可完成所有操作。任務(wù)2是要求小于80的語文、數(shù)學(xué)平均分。選中目標(biāo)單元格，輸入=AVERAGEIF，然后點(diǎn)擊tab鍵，緊接著點(diǎn)擊編輯欄中的“插入函數(shù)”，打開函數(shù)參數(shù)對話框；在“條件區(qū)域”框中輸入$C$3:$C$20、絕對值引用，在“條件”框中輸入"<80"，在“求平均值區(qū)域”框中輸入$C$3:$C$20、絕對值引用，點(diǎn)擊確定即可完成。數(shù)學(xué)的平均分計(jì)算可以以此類推。任務(wù)3是要求每個(gè)同學(xué)語文、數(shù)學(xué)成績大于80的平均分。選中目標(biāo)單元格，輸入=AVERAGEIF，然后點(diǎn)擊tab鍵，緊接著點(diǎn)擊編輯欄中的“插入函數(shù)”，打開函數(shù)參數(shù)對話框；在“條件區(qū)域”框中輸入L4:M4，在“條件”框中輸入">80"，在“求平均值區(qū)域”框中輸入L4:M4，點(diǎn)擊確定即可完成。最后利用填充柄下拉即可完成所有操作。我們發(fā)現(xiàn)在計(jì)算結(jié)果中出現(xiàn)錯(cuò)誤值，這是因?yàn)闆]有滿足的條件。如果要屏蔽掉錯(cuò)誤值，可以利用IFERROR函數(shù)，在AVERAGEIF函數(shù)外面添加一個(gè)IFERROR函數(shù)，然后判斷是否為錯(cuò)誤值，如果為錯(cuò)誤值則輸出空值即可。

第五節(jié)Excel函數(shù)之VLOOKUP1函數(shù)概述VLOOKUP函數(shù)是Excel中的一個(gè)縱向查找函數(shù)，在日常工作中有著廣泛應(yīng)用。其功能是按列查找，最終返回該列所需查詢序列所對應(yīng)的值。VLOOKUP函數(shù)的語法為：=VlookUp(lookup_value,table_array,col_index_num,[range_lookup])其中四個(gè)參數(shù)分別為查找的值，查找范圍，返回列數(shù)和查找方式。對于vlookup函數(shù)的四個(gè)參數(shù)，第1個(gè)參數(shù)lookup_value為需要在數(shù)據(jù)表第一列中進(jìn)行查找的值，其可以為數(shù)值、引用或文本字符串。當(dāng)vlookup函數(shù)省略“查找的值”時(shí)，表示用0查找。第2參數(shù)table_array為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表，使用對區(qū)域或區(qū)域名稱的引用。第3個(gè)參數(shù)col_index_num為查找數(shù)據(jù)返回的列序號。例如返回列數(shù)為1時(shí)，表示返回查找范圍第1列的值，為2時(shí)，則返回查找范圍第2列的值，以此類推。如果“返回列數(shù)”小于1，函數(shù)將返回錯(cuò)誤值#VALUE!。如果“返回列數(shù)”大于“查找范圍”的列數(shù)，函數(shù)同樣將返回錯(cuò)誤值#REF!。最后一個(gè)參數(shù)range_lookup為一邏輯值，指明VLOOKUP查找時(shí)是精確匹配，還是近似匹配。如果為FALSE或0，則返回精確匹配，如果找不到，則返回錯(cuò)誤值#N/A；如果為TRUE或1，VLOOKUP將查找近似匹配值，也就是說，如果找不到精確匹配值，則返回小于“查找的值”的最大數(shù)值；如果“查找方式”省略，則默認(rèn)為TRUE或1。2基礎(chǔ)用法如下所示，要求在數(shù)據(jù)表的I2:J13區(qū)域中提取各個(gè)學(xué)號對應(yīng)的班級名稱，并將其對應(yīng)的填寫到B3:B33區(qū)域的“班級”列中。操作步驟：選中目標(biāo)單元格，輸入=VLOOKUP，然后點(diǎn)擊tab鍵，緊接著點(diǎn)擊編輯欄中的“插入函數(shù)”，打開函數(shù)參數(shù)對話框；在“查找的值”框中輸入A3，在“查找范圍”框中輸入$I$2:$J$13（請注意，因查找范圍固定不變，所以需要用絕對地址引用）,在“返回列數(shù)”框中輸入2，最后在“查找方式”框中輸入FALSE，點(diǎn)擊確定即可完成。最后利用填充柄下拉即可完成所有操作。如下所示，要求在數(shù)據(jù)表的B2:E11區(qū)域中提取各個(gè)產(chǎn)品對應(yīng)的地址名稱，并將其對應(yīng)地填寫到H2:H6區(qū)域的“地址”列中。在前面我們已經(jīng)知道，VLOOKUP函數(shù)第1個(gè)參數(shù)“查找的值”必須是查找范圍中第一列的值。但我們發(fā)現(xiàn)，當(dāng)前所要查找的內(nèi)容在“查找的值”的左側(cè)，沒辦法進(jìn)行匹配查找。所以我們只能重新構(gòu)建數(shù)據(jù)列，將產(chǎn)品列復(fù)制到地址列前即可。操作步驟：選中目標(biāo)單元格，輸入=VLOOKUP，然后點(diǎn)擊tab鍵，緊接著點(diǎn)擊編輯欄中的“插入函數(shù)”，打開函數(shù)參數(shù)對話框；在“查找的值”框中輸入G3，在“查找范圍”框中輸入$A$2:$B$12、絕對值引用，或者是$A$2:$E$12也可以，在“返回列數(shù)”框中輸入2，最后在“查找方式”框中輸入FALSE，點(diǎn)擊確定即可完成。最后利用填充柄下拉即可完成所有操作。3進(jìn)階用法通過以上兩個(gè)實(shí)例我們了解了VLOOKUP函數(shù)的基礎(chǔ)用法，下面我們進(jìn)一步拓展VLOOKUP函數(shù)的應(yīng)用。首先我們來學(xué)習(xí)文本數(shù)值混合查找，如圖所示，要求在數(shù)據(jù)表的A2:B8、F2:G8區(qū)域中分別提取姓名和部門，并將其對應(yīng)地填寫到H3:H8、C3:C8區(qū)域的“姓名”、“部門”列中。我們可以發(fā)現(xiàn)，在A3:A8的工號是文本數(shù)據(jù)，而F3:F8的工號是數(shù)值數(shù)據(jù)，兩個(gè)區(qū)域的數(shù)據(jù)類型不一致。如果我們直接進(jìn)行查找的話，發(fā)現(xiàn)找不到數(shù)據(jù)。原因就是文本和數(shù)值類型不一致，無法匹配查找。那應(yīng)該如何處理呢？解決方案就是進(jìn)行數(shù)據(jù)類型的轉(zhuǎn)化，我們來看一下具體操作：如果是數(shù)值數(shù)據(jù)轉(zhuǎn)換為文本數(shù)據(jù)的話，只需將數(shù)值連接一個(gè)空值即可完成。選中目標(biāo)單元格，輸入=VLOOKUP，然后點(diǎn)擊tab鍵，緊接著點(diǎn)擊編輯欄中的“插入函數(shù)”，打開函數(shù)參數(shù)對話框;在“查找的值”框中輸入F3連接空值，在“查找范圍”框中輸入$A$2:$B$8、絕對值引用，在“返回列數(shù)”框中輸入2，最后在“查找方式”框中輸入FALSE，點(diǎn)擊確定即可完成。如果是文本數(shù)據(jù)轉(zhuǎn)換為數(shù)值數(shù)據(jù)的話，只需將文本強(qiáng)制乘于1即可。選中目標(biāo)單元格，輸入=VLOOKUP，然后點(diǎn)擊tab鍵，緊接著點(diǎn)擊編輯欄中的“插入函數(shù)”，打開函數(shù)參數(shù)對話框;在“查找的值”框中輸入A3乘于1，在“查找范圍”框中輸入$F$2:$G$8、絕對值引用，在“返回列數(shù)”框中輸入2，最后在“查找方式”框中輸入FALSE，點(diǎn)擊確定即可完成。下面我們來講解關(guān)鍵字詞的查找，如圖所示，要求在數(shù)據(jù)表的A2:C7區(qū)域中提取各個(gè)合作公司的合同金額，并將其對應(yīng)地填寫到G3:G7區(qū)域的“合同金額”列中。在數(shù)據(jù)表中我們可以發(fā)現(xiàn)，兩個(gè)區(qū)域中的合作公司名稱不一樣，一個(gè)是公司全稱，而一個(gè)是簡稱。如果我們直接進(jìn)行查找的話，可以發(fā)現(xiàn)找不到數(shù)據(jù)。因此我們需要利用通配符，構(gòu)建新的查找的值。方法如下：選中目標(biāo)單元格，輸入=VLOOKUP，然后點(diǎn)擊tab鍵，緊接著點(diǎn)擊編輯欄中的“插入函數(shù)”，打開函數(shù)參數(shù)對話框；在“查找的值”框中輸入星號連接F3連接星號，因?yàn)樾翘柺亲址孕枰秒p引號，在“查找范圍”框中輸入$A$2:$C$7、絕對值引用，在“返回列數(shù)”框中輸入3，最后在“查找方式”框中輸入FALSE，點(diǎn)擊確定即可完成。4使用注意事項(xiàng)Vlookup函數(shù)具有強(qiáng)大的縱向查找功能，在日常工作中有著廣泛應(yīng)用，但同學(xué)們在應(yīng)用過程中應(yīng)注意：1.在一般情況下，“查找的值”必須在“查找范圍”中處于第1列；2.在“查找范圍”固定不變的情況下，需要使用絕對值引用；3.使用vlookup函數(shù)時(shí)，返回的是目標(biāo)區(qū)域第一個(gè)符合查找值的數(shù)值。也就是說在目標(biāo)區(qū)域存在多個(gè)目標(biāo)值時(shí)，則應(yīng)特別注意；4.如果找不到數(shù)據(jù)，vlookup函數(shù)總會傳回一個(gè)錯(cuò)誤值（#N/A）。

第六節(jié)Excel數(shù)據(jù)篩選1數(shù)據(jù)篩選的作用數(shù)據(jù)篩選功能是從大量的數(shù)據(jù)記錄中檢索到所需的信息，能把滿足條件的記錄顯示出來，不滿足條件的記錄暫時(shí)隱藏，減少發(fā)生數(shù)據(jù)遺漏和差錯(cuò)，在日常工作中幫助人們提高工作效率。2自動篩選2.1自動篩選選定有效數(shù)據(jù)區(qū)域——點(diǎn)擊“數(shù)據(jù)”功能選項(xiàng)卡“排序和篩選”組中的“篩選”按鈕——在篩選按鈕下拉框中選擇或輸入篩選條件。2.2數(shù)字篩選自動篩選中還提供了“數(shù)字篩選”功能，針對含有數(shù)據(jù)的列單元格，可以篩選符合相應(yīng)數(shù)據(jù)范圍的記錄。2.3取消篩選（1）恢復(fù)被隱藏的記錄：單擊“排序和篩選”組中的“清除”按鈕；（2）取消“篩選”功能：單擊“篩選”按鈕，退出篩選狀態(tài)。2.4實(shí)踐操作（1）在“各季度藥品銷售情況表”中篩選出所有膠囊劑的藥品記錄。（2）篩選出第四季度銷售量大于200的藥品記錄。3高級篩選3.1設(shè)定條件區(qū)域在空白單元格位置輸入條件區(qū)域。條件區(qū)域中條件之間的關(guān)系不同，書寫方式也不同。如果多個(gè)條件之間是“與”的關(guān)系，那么多個(gè)條件在書寫時(shí)應(yīng)寫在同一行；如果是“或”的關(guān)系，那么書寫時(shí)，多個(gè)條件應(yīng)錯(cuò)行書寫。3.2高級篩選點(diǎn)擊“數(shù)據(jù)”功能選項(xiàng)卡中“排序和篩選”組的“高級”按鈕，分別設(shè)置列表區(qū)域、條件區(qū)域及結(jié)果顯示方式。結(jié)果顯示方式有“在原有區(qū)域顯示篩選結(jié)果”和“將篩選結(jié)果復(fù)制到其他位置”兩種。3.3實(shí)踐操作在“各季度藥品銷售情況表”中篩選出劑型為片劑或顆粒劑且各季度藥品銷售量均大于100的記錄。

第七節(jié)Excel數(shù)據(jù)分類匯總與透視表1數(shù)據(jù)分類匯總分類匯總是對數(shù)據(jù)清單中的數(shù)據(jù)按類別分別進(jìn)行求和、求平均等匯總的一種基本的數(shù)據(jù)分析方法。1.1操作要點(diǎn)（1）對分類關(guān)鍵字進(jìn)行排序。排序的目的是為了使相同類別的記錄集中在一起。（2）點(diǎn)擊“數(shù)據(jù)”功能選項(xiàng)卡中“分類匯總”按鈕，分別對“分類字段”、“匯總方式”、“選定匯總項(xiàng)”進(jìn)行設(shè)置。1.2實(shí)踐操作利用分類匯總計(jì)算出各劑型藥品各季度的銷售量總和。2數(shù)據(jù)透視表數(shù)據(jù)透視表能一次完成排序、篩選、分類匯總?cè)?xiàng)工作，是一種交