Excel與數(shù)據(jù)處理 第3章公式、名字與函數(shù)

《Excel與數(shù)據(jù)處理 第3章公式、名字與函數(shù)》由會員分享，可在線閱讀，更多相關(guān)《Excel與數(shù)據(jù)處理 第3章公式、名字與函數(shù)（77頁珍藏版）》請在裝配圖網(wǎng)上搜索。

1、Excel與數(shù)據(jù)處理與數(shù)據(jù)處理認(rèn)識認(rèn)識n函數(shù)好比函數(shù)好比EXCEL的的武林秘笈武林秘笈，如果要成為，如果要成為EXCEL高高手手，誰都必須學(xué)習(xí)它。函數(shù)也是，誰都必須學(xué)習(xí)它。函數(shù)也是EXCEL與與Word之類之類軟件中的表格的軟件中的表格的主要區(qū)別主要區(qū)別，它使，它使EXCEL具有強大的具有強大的運算能力。運算能力。nEXCEL提供了涉及各個不同領(lǐng)域的函數(shù)接近提供了涉及各個不同領(lǐng)域的函數(shù)接近400個個。運用它們解決工作中的實際問題，能夠極大提高工作運用它們解決工作中的實際問題，能夠極大提高工作效率效率，使枯燥的工作變得輕松、簡單。，使枯燥的工作變得輕松、簡單。n但學(xué)習(xí)是一個但學(xué)習(xí)是一個枯燥而艱辛

2、枯燥而艱辛的過程！當(dāng)然嘛，誰讓你想的過程！當(dāng)然嘛，誰讓你想成為成為高手高手呢？呢？本章本章學(xué)習(xí)目標(biāo)學(xué)習(xí)目標(biāo)1 1、掌握、掌握數(shù)組公式數(shù)組公式的用法的用法2 2、掌握、掌握名字名字的用法的用法3 3、掌握常用、掌握常用工作表函數(shù)工作表函數(shù)的用法的用法4 4、掌握常見、掌握常見統(tǒng)計函數(shù)統(tǒng)計函數(shù)的用法的用法5 5、掌握、掌握條件函數(shù)條件函數(shù)的用法，特別是：的用法，特別是：IfIfsumifcountifsumifcountif 等等6 6、掌握解、掌握解查找引用類函數(shù)查找引用類函數(shù)的用法的用法7 7、掌握常見、掌握常見日期、文本日期、文本類函數(shù)類函數(shù)3.1 數(shù)組公式數(shù)組公式及其應(yīng)用及其應(yīng)用1、概述、

3、概述n普通公式只執(zhí)行一個簡單計算，并且返回普通公式只執(zhí)行一個簡單計算，并且返回一個一個運算結(jié)果運算結(jié)果。數(shù)組公式能夠同時對一組或兩組以。數(shù)組公式能夠同時對一組或兩組以上的數(shù)據(jù)進(jìn)行計算，計算的結(jié)果可能是上的數(shù)據(jù)進(jìn)行計算，計算的結(jié)果可能是一個一個，也可能是也可能是多個多個。n在數(shù)組公式中使用的數(shù)據(jù)稱為在數(shù)組公式中使用的數(shù)據(jù)稱為數(shù)組參數(shù)數(shù)組參數(shù)，數(shù)組，數(shù)組參數(shù)可以是一個參數(shù)可以是一個數(shù)據(jù)區(qū)域數(shù)據(jù)區(qū)域，也可以是數(shù)組，也可以是數(shù)組常量常量。3.1 數(shù)組公式數(shù)組公式及其應(yīng)用及其應(yīng)用2、數(shù)組公式的建立方法、數(shù)組公式的建立方法n（1）選中需要保存數(shù)組公式結(jié)果的單元格或）選中需要保存數(shù)組公式結(jié)果的單元格或單元格

4、區(qū)域。單元格區(qū)域。n（2）輸入公式的內(nèi)容。）輸入公式的內(nèi)容。n（3）按）按Ctrl+Shift+Enter鍵。鍵。3.1 數(shù)組公式數(shù)組公式及其應(yīng)用及其應(yīng)用3、用數(shù)組公式計算兩個數(shù)據(jù)區(qū)域的乘積、用數(shù)組公式計算兩個數(shù)據(jù)區(qū)域的乘積 【例例3.1】 某茶葉店經(jīng)銷多種茶葉，已知各種茶某茶葉店經(jīng)銷多種茶葉，已知各種茶葉的單價以及葉的單價以及12月份各種茶葉的銷量如圖所示，月份各種茶葉的銷量如圖所示，計算各種茶葉的銷售額。計算各種茶葉的銷售額。3.1 數(shù)組公式數(shù)組公式及其應(yīng)用及其應(yīng)用4、數(shù)組擴展、數(shù)組擴展 n在公式中用數(shù)組作為參數(shù)時，所有的數(shù)組必須在公式中用數(shù)組作為參數(shù)時，所有的數(shù)組必須是同維的。如果數(shù)組參

5、數(shù)或數(shù)組區(qū)域的維數(shù)不是同維的。如果數(shù)組參數(shù)或數(shù)組區(qū)域的維數(shù)不匹配，匹配，Excel會自動擴展該參數(shù)會自動擴展該參數(shù) 。n比如，比如，LG空調(diào)售價為空調(diào)售價為7654元，重慶各商場的元，重慶各商場的銷售數(shù)據(jù)如下圖的銷售數(shù)據(jù)如下圖的B、C兩列所示。現(xiàn)要計算兩列所示。現(xiàn)要計算各商場的銷售總額。各商場的銷售總額。 3.1 數(shù)組公式數(shù)組公式及其應(yīng)用及其應(yīng)用1、選中、選中F8:F14；2、輸入公式：、輸入公式：=C5:C8*C143、按、按Ctrl+Shift+Entern用數(shù)組公式計算銷售總額用數(shù)組公式計算銷售總額3.1 數(shù)組公式數(shù)組公式及其應(yīng)用及其應(yīng)用5、二維數(shù)組、二維數(shù)組 n涉及許多行涉及許多行(或

6、列或列)數(shù)據(jù)處理的數(shù)組公式，就是數(shù)據(jù)處理的數(shù)組公式，就是所謂的二維數(shù)組。所謂的二維數(shù)組。n合理地運用二維數(shù)組的運算功能，會提高數(shù)據(jù)合理地運用二維數(shù)組的運算功能，會提高數(shù)據(jù)處理的能力，有時在不同工作表之間進(jìn)行數(shù)據(jù)處理的能力，有時在不同工作表之間進(jìn)行數(shù)據(jù)匯總時會特別有效匯總時會特別有效 。3.1 數(shù)組公式數(shù)組公式及其應(yīng)用及其應(yīng)用n案例案例n某食品店將每月的食品銷售資料統(tǒng)計在一個工作表中，某食品店將每月的食品銷售資料統(tǒng)計在一個工作表中，16月的銷售數(shù)據(jù)分別記錄在月的銷售數(shù)據(jù)分別記錄在Book1工作簿的工作簿的Sheet1Sheet6中，現(xiàn)在要統(tǒng)計每種商品前中，現(xiàn)在要統(tǒng)計每種商品前6個月的銷售總量和個

7、月的銷售總量和銷售金額，并將結(jié)果存放在銷售金額，并將結(jié)果存放在sheet7。見下圖：。見下圖：3.2 公式公式的循環(huán)引用的循環(huán)引用1、循環(huán)引用的概念、循環(huán)引用的概念n如果公式引用了自己所在的單元格，不論是如果公式引用了自己所在的單元格，不論是直直接接的還是的還是間接間接的，都稱為的，都稱為循環(huán)引用循環(huán)引用。n在一般情況下，很少使用循環(huán)引用來處理問題。在一般情況下，很少使用循環(huán)引用來處理問題。但對于但對于迭代迭代求解一類問題，如求數(shù)的階乘、數(shù)求解一類問題，如求數(shù)的階乘、數(shù)列求和等問題，可以用列求和等問題，可以用循環(huán)引用循環(huán)引用求解。求解。n若循環(huán)引用的若循環(huán)引用的條件條件或或迭代次數(shù)迭代次數(shù)設(shè)置

8、不當(dāng)，設(shè)置不當(dāng)，EXCEL會報告一個錯誤信息。會報告一個錯誤信息。 3.2 公式公式的循環(huán)引用的循環(huán)引用n【例例3.5】下圖是循環(huán)引用的一個事例。在下圖是循環(huán)引用的一個事例。在A1單元格中輸入的公式單元格中輸入的公式“=A2+A3”；在；在A2單元格中輸入數(shù)字單元格中輸入數(shù)字2；在；在A3單元格中輸入公式單元格中輸入公式“=A1*0.3”，見，見圖圖(a)。這樣，。這樣，A3單元格的值依賴于單元格的值依賴于A1，而，而A1單單元格的值又依賴于元格的值又依賴于A3，它們形成了間接的循環(huán)引用，在未進(jìn)行迭代，它們形成了間接的循環(huán)引用，在未進(jìn)行迭代求解情況下的結(jié)果如求解情況下的結(jié)果如圖（圖（b）所示。

9、該循環(huán)引用是所示。該循環(huán)引用是可解可解的，進(jìn)行迭代的，進(jìn)行迭代求解最后的結(jié)果如求解最后的結(jié)果如圖（圖（c）所示。所示。 3.3 名字名字1、名字概述、名字概述 n可以給一個單元格或單元格區(qū)域取一個名字，這個名字可以給一個單元格或單元格區(qū)域取一個名字，這個名字可以出現(xiàn)在公式中，用來指代引用的單元格或單元格區(qū)可以出現(xiàn)在公式中，用來指代引用的單元格或單元格區(qū)域，這會使公式的意義更加明確。域，這會使公式的意義更加明確。n名字其實是一個標(biāo)識符，由字母或下劃線開頭的一個或名字其實是一個標(biāo)識符，由字母或下劃線開頭的一個或多個字符組成。名字不區(qū)分字母的大小寫，比如多個字符組成。名字不區(qū)分字母的大小寫，比如AB

10、C、abc、Abc都是同一個名字。在都是同一個名字。在Excel中，還可以用中，還可以用漢字漢字作為名字。作為名字。n可以為同一單元格或單元格區(qū)域定義可以為同一單元格或單元格區(qū)域定義多個多個不同的不同的名字名字。n名字一旦定義就可以在同一工作薄的不同名字一旦定義就可以在同一工作薄的不同工作表工作表之間共用之間共用。3.3 名字名字2、名字的意義、名字的意義n在在Excel中，同一個工作簿中的名字是共享的，中，同一個工作簿中的名字是共享的，在一個工作表中定義的名字，可以被另一個工在一個工作表中定義的名字，可以被另一個工作表的公式引用，這使公式具有更強大的功能，作表的公式引用，這使公式具有更強大的

11、功能，它能夠利用單元格的它能夠利用單元格的名字名字在不同的工作表中在不同的工作表中查查找找到正確的到正確的單元格單元格或單元格或單元格區(qū)域區(qū)域。n名字給單元格的引用帶來了許多方便，因為它名字給單元格的引用帶來了許多方便，因為它使人們不必記住單元格在工作表中的引用位置，使人們不必記住單元格在工作表中的引用位置，用用名字名字就能找到它。就能找到它。3.3 名字名字3、名字的定義、名字的定義 n在在Excel中，可以為一個獨立的單元格、連續(xù)中，可以為一個獨立的單元格、連續(xù)的單元格區(qū)域或許多不連續(xù)的單元格構(gòu)成的單的單元格區(qū)域或許多不連續(xù)的單元格構(gòu)成的單元格組合定義一個名字（或多個名字）元格組合定義一個

12、名字（或多個名字） n定義名字有多種方法，如定義、粘貼、指定或定義名字有多種方法，如定義、粘貼、指定或標(biāo)志等標(biāo)志等 3.3 名字名字4、定義單元格或單元格區(qū)域的名字、定義單元格或單元格區(qū)域的名字【例例3.6】在下圖中，在下圖中，B2單元格被命名為單元格被命名為“存款利率存款利率”，在，在D5中輸入的中輸入的公式是公式是“=C5*存款利率存款利率”，其中的，其中的“存款利率存款利率”就是就是B2單元格的名單元格的名字。字。 B2單元格的名字定義方法如下：單元格的名字定義方法如下：3.3 名字名字n（1）選擇）選擇“插入插入”|“名稱名稱”|“定義定義”菜單菜單命令命令.n（2）在彈出的）在彈出的

13、“定義名字定義名字”對話框的對話框的“引用引用位置位置” 輸入要定義名字的單元格輸入要定義名字的單元格引用位置引用位置。n（3）在）在“在當(dāng)前工作簿中的名稱在當(dāng)前工作簿中的名稱” 中輸入中輸入名字名字 3.3 名字名字5、將工作表的首行（或首列）定義為名字、將工作表的首行（或首列）定義為名字n“指定指定”的方式一次可定義的方式一次可定義多個名字多個名字：可以將：可以將表格的首行或首列指定為相應(yīng)的列或行的名字。表格的首行或首列指定為相應(yīng)的列或行的名字。n方法方法n（1）選中選中要指定名字的單元格要指定名字的單元格區(qū)域區(qū)域。n（2）選擇）選擇“插入插入”|“名稱名稱”|“指定指定”菜單項。菜單項。

14、n（3）在）在“指定名稱指定名稱”對話框中，選中對話框中，選中“首行首行”、“最左列最左列”的復(fù)選框。的復(fù)選框。這樣就把該表的首行、首列指定成了相應(yīng)的列、行的這樣就把該表的首行、首列指定成了相應(yīng)的列、行的名字名字3.3 名字名字n指定首行、最左列為名字的案例指定首行、最左列為名字的案例n例例某學(xué)生成績表如下圖所示。指定其某學(xué)生成績表如下圖所示。指定其首行、首行、最左列為最左列為名字。名字。指定首行、最指定首行、最左列為名字的左列為名字的好處是可以通好處是可以通過過行列交叉點行列交叉點查看單元格數(shù)查看單元格數(shù)據(jù)據(jù)3.3 名字名字n用名字欄定義名字用名字欄定義名字n工作表全選按鈕（工作表列標(biāo)工作表

15、全選按鈕（工作表列標(biāo)A左邊的按鈕）上面的編輯框稱為左邊的按鈕）上面的編輯框稱為名字欄名字欄，它隨時顯示當(dāng)前單元格的名字或引用。它可以用來定義，它隨時顯示當(dāng)前單元格的名字或引用。它可以用來定義名字。名字。n例例用名字欄將下圖用名字欄將下圖B4:B8區(qū)域的名字定義為區(qū)域的名字定義為數(shù)量。數(shù)量。3.3 名字名字6、 名字的應(yīng)用名字的應(yīng)用n1）通過名字在不同的工作表之間）通過名字在不同的工作表之間傳遞數(shù)據(jù)傳遞數(shù)據(jù) n實現(xiàn)方法見下頁。實現(xiàn)方法見下頁。通過名字查找蔬菜單價通過名字查找蔬菜單價指定指定A2:B12最左邊為名字最左邊為名字3.3 名字名字n指定指定A2:B12的的最左列為最左列為名字；名字；n

16、用用名字查找名字查找蔬菜單價，見蔬菜單價，見D列；列；n若不用名字，單價要從若不用名字，單價要從C列的幾百個數(shù)據(jù)中去查列的幾百個數(shù)據(jù)中去查找！找！ 這種方法雖然比直接從工作表的單元格中查找數(shù)這種方法雖然比直接從工作表的單元格中查找數(shù)據(jù)方便了不少，但據(jù)方便了不少，但輸入量仍然較大輸入量仍然較大。用它處理。用它處理成千上萬行的數(shù)據(jù)查詢?nèi)匀怀汕先f行的數(shù)據(jù)查詢?nèi)匀坏托У托?。最佳方法率。最佳方法是用是用名字和名字和Indirect函數(shù)函數(shù)相結(jié)合，非常方便，相結(jié)合，非常方便，高效。高效。3.3 名字名字n某蔬菜商在一個工作表中保存蔬菜的單價，在另一某蔬菜商在一個工作表中保存蔬菜的單價，在另一工作表中保

17、存銷售記錄，現(xiàn)要查找銷售記錄表中各工作表中保存銷售記錄，現(xiàn)要查找銷售記錄表中各蔬菜的單價。蔬菜的單價。指定指定A2:B11區(qū)域區(qū)域的的“最左列最左列”為名為名字字在D2中輸入公式：=INDIRECT(B2)向下復(fù)制該公式！3.3 名字名字n2）名字與）名字與Indirect函數(shù)結(jié)合應(yīng)用函數(shù)結(jié)合應(yīng)用nIndirect函數(shù)與函數(shù)與名字名字相相結(jié)結(jié)合，可使數(shù)據(jù)查找更方便。合，可使數(shù)據(jù)查找更方便。Indirect函數(shù)的用法是：函數(shù)的用法是： Indirect(x) 其中的其中的X可以是單元格引用或名字。可以是單元格引用或名字。n若若X為單元格引用，它必須用為單元格引用，它必須用“”“”引起來，如：引起

18、來，如：=INDIRECT(“A1”)，將返回，將返回A1單元格中的內(nèi)容單元格中的內(nèi)容n若若X為單元格名字，它將返回該名字所對應(yīng)的單元格的為單元格名字，它將返回該名字所對應(yīng)的單元格的內(nèi)容。內(nèi)容。3.3 名字名字n3應(yīng)用行列交叉點查找數(shù)據(jù)應(yīng)用行列交叉點查找數(shù)據(jù)n所謂行列交叉點指的是由所謂行列交叉點指的是由行的名字行的名字 列的名字列的名字確定的確定的單元格。單元格。 n把一個工作表的首行、首列指定為名字之后，可以使把一個工作表的首行、首列指定為名字之后，可以使用名字引用行、列交叉點對應(yīng)單元格中的數(shù)據(jù)。用名字引用行、列交叉點對應(yīng)單元格中的數(shù)據(jù)。n案例案例n某電信公司的電話用戶檔案，其中有成千上萬個

19、電某電信公司的電話用戶檔案，其中有成千上萬個電話用戶的電話號碼。如果知道某個用戶的名字，那話用戶的電話號碼。如果知道某個用戶的名字，那么要在這樣的表中查找該用戶的電話號碼、電話類么要在這樣的表中查找該用戶的電話號碼、電話類型，或申請日期等數(shù)據(jù)就顯得很困難，因為表中的型，或申請日期等數(shù)據(jù)就顯得很困難，因為表中的資料太多了。通過行、列交叉點的名字引用能很輕資料太多了。通過行、列交叉點的名字引用能很輕松地找到所需的數(shù)據(jù)。松地找到所需的數(shù)據(jù)。 3.3 名字名字1 1指定指定A2:E17A2:E17的的“首行，最首行，最左列左列”為名為名字。字。2 2、在任空白單、在任空白單元格中輸入元格中輸入“客戶名

20、字客戶名字庫庫 第二行第二行任一單元格任一單元格標(biāo)題標(biāo)題”，就，就能找到該客能找到該客戶的對應(yīng)數(shù)戶的對應(yīng)數(shù)據(jù)。據(jù)。3.4 函數(shù)函數(shù)簡介簡介1、函數(shù)的概念、函數(shù)的概念n函數(shù)是函數(shù)是能夠完成特定功能的能夠完成特定功能的程序程序。在。在Excel中，中，它是系統(tǒng)它是系統(tǒng)預(yù)定義預(yù)定義的一些的一些公式公式，它們使用一些稱，它們使用一些稱為為參數(shù)參數(shù)的特定數(shù)值按特定的的特定數(shù)值按特定的順序順序或結(jié)構(gòu)進(jìn)行計或結(jié)構(gòu)進(jìn)行計算，然后把計算的算，然后把計算的結(jié)果結(jié)果存放在某個單元格中。存放在某個單元格中。 n在大多數(shù)情況下，函數(shù)的計算結(jié)果是數(shù)值。當(dāng)在大多數(shù)情況下，函數(shù)的計算結(jié)果是數(shù)值。當(dāng)然，它也可以返回文本、引用、

21、邏輯值、數(shù)組然，它也可以返回文本、引用、邏輯值、數(shù)組或工作表的信息或工作表的信息 3.4 函數(shù)函數(shù)簡介簡介n2、Excel函數(shù)分類函數(shù)分類分 類功 能 簡 介數(shù)據(jù)庫函數(shù)對數(shù)據(jù)清單中的數(shù)據(jù)進(jìn)行分析、查找、計算等日期與時間對日期和時間進(jìn)行計算、設(shè)置及格式化處理工程函數(shù)用于工程數(shù)據(jù)分析與處理信息函數(shù)對單元格或公式中數(shù)據(jù)類型進(jìn)行判定財務(wù)函數(shù)進(jìn)行財務(wù)分析及財務(wù)數(shù)據(jù)的計算邏輯函數(shù)進(jìn)行邏輯判定、條件檢查統(tǒng)計函數(shù)對工作表數(shù)據(jù)進(jìn)行統(tǒng)計、分析查找函數(shù)查找特定的數(shù)據(jù)或引用公式中的特定信息文本函數(shù)對公式、單格中的字符、文本進(jìn)行格式化或運算數(shù)學(xué)函數(shù)進(jìn)行數(shù)學(xué)計算等外部函數(shù)進(jìn)行外部函數(shù)調(diào)用及數(shù)據(jù)庫的鏈接查詢等功能自定義函數(shù)

22、用戶用vba編寫，用于完成特定功能的函數(shù)3.4 函數(shù)函數(shù)簡介簡介3、函數(shù)調(diào)用、函數(shù)調(diào)用 n函數(shù)的語法函數(shù)的語法 n函數(shù)名函數(shù)名(參數(shù)參數(shù)1, 參數(shù)參數(shù)2, 參數(shù)參數(shù)3,) n在公式中調(diào)用函數(shù)在公式中調(diào)用函數(shù)n =IF(AVERAGE(F2:F5)50,SUM(G2:G5),0)嵌套函嵌套函數(shù)數(shù)3.4 函數(shù)函數(shù)簡介簡介4、函數(shù)輸入、函數(shù)輸入n在公式中直接輸入函數(shù)調(diào)用在公式中直接輸入函數(shù)調(diào)用n用函數(shù)調(diào)用向?qū)л斎胗煤瘮?shù)調(diào)用向?qū)л斎?、Excel的幫助系統(tǒng)的幫助系統(tǒng)3.6 常用常用工作表函數(shù)工作表函數(shù)1、匯總求和函數(shù)、匯總求和函數(shù) n自動求和按鈕自動求和按鈕圖示工作表圖示工作表中的所有匯中的所有匯總數(shù)

23、據(jù)可用總數(shù)據(jù)可用自動和按鈕自動和按鈕計算出來！計算出來！3.6 常用常用工作表函數(shù)工作表函數(shù)2、SUM函數(shù)函數(shù)n用法用法SUM(x1, x2,x30) x1，x2，x30是需要求和的參數(shù)是需要求和的參數(shù) ，可以是數(shù)據(jù)或單元格區(qū)域，可以是數(shù)據(jù)或單元格區(qū)域n功能功能n計算各參數(shù)的數(shù)值之和計算各參數(shù)的數(shù)值之和 n說明：說明： 參數(shù)表中的數(shù)字、邏輯值及數(shù)字的文本表達(dá)式將被計參數(shù)表中的數(shù)字、邏輯值及數(shù)字的文本表達(dá)式將被計算，文本值被轉(zhuǎn)換成數(shù)字，而邏輯值算，文本值被轉(zhuǎn)換成數(shù)字，而邏輯值“true”被轉(zhuǎn)換成被轉(zhuǎn)換成數(shù)字?jǐn)?shù)字1。 例如，例如，SUM(3,2)=5，SUM(9,20,true)=30。3.6 常

24、用常用工作表函數(shù)工作表函數(shù)n 如果參數(shù)為數(shù)組或引用，那么只有其中的數(shù)字被計如果參數(shù)為數(shù)組或引用，那么只有其中的數(shù)字被計算。數(shù)組或引用中的空白單元格、邏輯值、文本或錯算。數(shù)組或引用中的空白單元格、邏輯值、文本或錯誤值將被忽略。誤值將被忽略。n例如，設(shè)例如，設(shè)A1的值為的值為“9”，A2為為true，則公式，則公式SUM(A1,A2,20)的計算結(jié)果為的計算結(jié)果為20，而不是，而不是30。因。因為本公式中包括兩個引用為本公式中包括兩個引用A1、A2，而，而A1的值為文的值為文本，本，A2的值為邏輯值，它們在計算時被忽略，最終的值為邏輯值，它們在計算時被忽略，最終就只有一個數(shù)值就只有一個數(shù)值20參與

25、運算。參與運算。n SUM函數(shù)的參數(shù)最多可達(dá)函數(shù)的參數(shù)最多可達(dá)30個，不同類型的參數(shù)個，不同類型的參數(shù)可以同時出現(xiàn)?？梢酝瑫r出現(xiàn)。n例如，例如，A2:E2 包含包含 5、15、30、40、50、a3的值的值為為10，則，則SUM(A2:C2,A3) =60，SUM(B2:E2,15) =150，SUM(A2:D2,1,2,3,4,A3,10)=110。3.6 常用常用工作表函數(shù)工作表函數(shù)3、平均值函數(shù)、平均值函數(shù)AVERAGE，AVERAGEA n用法用法nAVERAGE(n1，n2，n30)nAVERAGEA(n1，n2，n30)n其中，其中，n1，n2，n30是要計算平均值的參數(shù)，該是要計

26、算平均值的參數(shù)，該函數(shù)最多允許有函數(shù)最多允許有30個參數(shù)個參數(shù)。參數(shù)可以是數(shù)字，或者是涉。參數(shù)可以是數(shù)字，或者是涉及數(shù)字的及數(shù)字的名稱名稱、數(shù)組數(shù)組或或引用引用 n功能功能n兩函數(shù)都是求參數(shù)的兩函數(shù)都是求參數(shù)的平均值平均值。AverAge不對不對文本文本和和邏輯值邏輯值類數(shù)據(jù)進(jìn)行計算；類數(shù)據(jù)進(jìn)行計算；nAverageA要對文本和邏輯函數(shù)進(jìn)行平均值計算：要對文本和邏輯函數(shù)進(jìn)行平均值計算：文本文本被視為被視為0，邏輯值，邏輯值true被視為被視為1，false被視為被視為0，空文本（空文本（）也作為）也作為0計算計算 3.6 常用常用工作表函數(shù)工作表函數(shù)n計數(shù)函數(shù)案例計數(shù)函數(shù)案例n下圖說明下圖說明

27、AVERAGE函數(shù)與函數(shù)與AVERAGEA函數(shù)的區(qū)別函數(shù)的區(qū)別 3.6 常用常用工作表函數(shù)工作表函數(shù)4、計數(shù)函數(shù)、計數(shù)函數(shù)COUNT、COUNTA、COUNTBLANK、COUNTIF n用法用法nCOUNT(x1, x2, ,x30)nCOUNTA(x1, x2, ,x30)nCOUNTBLANK (range)n功能功能nCount 統(tǒng)計數(shù)字的個數(shù)統(tǒng)計數(shù)字的個數(shù)nCountA統(tǒng)計數(shù)字和文本的個數(shù)統(tǒng)計數(shù)字和文本的個數(shù)nCountBlank統(tǒng)計空白單元格的個數(shù)統(tǒng)計空白單元格的個數(shù)3.6 常用常用工作表函數(shù)工作表函數(shù)n計數(shù)函數(shù)案例計數(shù)函數(shù)案例本例可以本例可以看出，用看出，用不同的函不同的函數(shù)對相

28、同數(shù)對相同區(qū)域進(jìn)行區(qū)域進(jìn)行計數(shù)，統(tǒng)計數(shù)，統(tǒng)計結(jié)果是計結(jié)果是不同的。不同的。3.6 常用常用工作表函數(shù)工作表函數(shù)n【例例】某班期末成績表如圖所示?，F(xiàn)要統(tǒng)計每位同學(xué)的總某班期末成績表如圖所示?，F(xiàn)要統(tǒng)計每位同學(xué)的總分，各科目的應(yīng)考人數(shù)、缺考人數(shù)及各科平均成績分，各科目的應(yīng)考人數(shù)、缺考人數(shù)及各科平均成績 3.7 邏輯函數(shù)邏輯函數(shù)n1、Excel的比較運算符的比較運算符n比較運算又稱關(guān)系運算，就是人們常說的比較式。比較運算又稱關(guān)系運算，就是人們常說的比較式。比較運算只有兩種不同的結(jié)果，要么比較運算只有兩種不同的結(jié)果，要么“正確正確”，要，要么么“錯誤錯誤”，不可能有第三種結(jié)果，不可能有第三種結(jié)果 比較運

29、算符含 義示 例=等于a1=3大于a1b1小于a1=大于等于a1=60比較運算符含 義示 例=小于等于a1=”dd”不等于a103.7 邏輯函數(shù)邏輯函數(shù)2 AND、NOT、OR、TRUE、FALSE函數(shù)函數(shù)n用法用法nAND(x1,x2, ,x30)nOR(x1, x2, ,x30)nNOT(logical)n案例案例nAND(TRUE,TRUE)=TRUE，nOR(TRUE,TRUE)=TRUEnAND(TRUE,FALSE,TRUE, TRUE)=FALSE，OR(TRUE,FALSE,TRUE,TRUE)=TRUE。n如果如果B1，B2，B3單元格中的值為單元格中的值為 TRUE，F(xiàn)AL

30、SE，TRUE，則則AND(B1:B3)=FALSE，但，但OR(B1:B3)=TRUE。nAND(2,2+3=5)=TRUE3.7 邏輯函數(shù)邏輯函數(shù)3、條件函數(shù)、條件函數(shù)IF n用法用法nIF(條件條件, 表達(dá)式表達(dá)式1, 表達(dá)式表達(dá)式2)。n功能功能n當(dāng)條件成立時，計算出表達(dá)式當(dāng)條件成立時，計算出表達(dá)式1的值；當(dāng)條件不成的值；當(dāng)條件不成立時，計算出表達(dá)式立時，計算出表達(dá)式2的值的值n事例事例nIf(A160,“及格了及格了”,“不及格不及格”)，若，若A1單元格的單元格的值是值是76，則該函數(shù)的結(jié)果是：及格了；若，則該函數(shù)的結(jié)果是：及格了；若A1單元單元格的值是格的值是50，則該函數(shù)的結(jié)果

31、是，則該函數(shù)的結(jié)果是:不及格。不及格。nIF函數(shù)的嵌套調(diào)用函數(shù)的嵌套調(diào)用3.7 邏輯函數(shù)邏輯函數(shù)n【例例1】某單位有某單位有1 000多位黨員，多位黨員，要收黨費要收黨費。黨。黨費根據(jù)工資的高低，按不費根據(jù)工資的高低，按不同的費率收取，收費的費同的費率收取，收費的費稅如右表所示。稅如右表所示。n類似于這樣的工作，使用類似于這樣的工作，使用IF函數(shù)進(jìn)行數(shù)據(jù)的匹配與函數(shù)進(jìn)行數(shù)據(jù)的匹配與查找是非常方便的。查找是非常方便的。n本例解決方法如下。本例解決方法如下。工 資黨費費率2 000以上3%1 5002 0002.5%1 2001 5002%1 0001 2001.5%8001 0001%800以下

32、03.7 邏輯函數(shù)邏輯函數(shù)nF3中公式意思為：當(dāng)中公式意思為：當(dāng)E3大于大于2 000時，公式的值為時，公式的值為3%；當(dāng)當(dāng)E3小于等于小于等于2 000，但大于，但大于1 500時，公式的值為時，公式的值為2.5%；其余的以此類推；其余的以此類推 3.7 邏輯函數(shù)邏輯函數(shù)n【例例2】某班考查成績?nèi)缦聢D某班考查成績?nèi)缦聢DA1:E10所示，將所示，將D列的地理列的地理成績轉(zhuǎn)換成等級制。成績轉(zhuǎn)換成等級制。3.7 邏輯函數(shù)邏輯函數(shù)4、COUNTIF函數(shù)函數(shù)n用法用法nCOUNTIF(range, criteria)n功能功能 n統(tǒng)計出統(tǒng)計出range中滿足條件中滿足條件criteria的數(shù)據(jù)個數(shù)。的

33、數(shù)據(jù)個數(shù)。3.7 邏輯函數(shù)邏輯函數(shù)5、條件求和函數(shù)、條件求和函數(shù)SUMIFn用法用法SUMIF(range,criteria,sum_range) n其中，其中，range是用于條件判斷的單元格區(qū)域，是用于條件判斷的單元格區(qū)域，criteria條件，條件，其形式可以為數(shù)字、表達(dá)式或文本；其形式可以為數(shù)字、表達(dá)式或文本；nsum_range是需求和的實際單元格。只有當(dāng)是需求和的實際單元格。只有當(dāng)range中的相應(yīng)單元格滿足條件時，才對中的相應(yīng)單元格滿足條件時，才對sum_range中的單元格求和。若省略中的單元格求和。若省略sum_range，則直接對則直接對range中的單元格求和。中的單元格

34、求和。 n功能功能 n對對range單元格區(qū)域中的數(shù)據(jù)進(jìn)行單元格區(qū)域中的數(shù)據(jù)進(jìn)行Criteria條件檢查，然后對條件檢查，然后對滿足條件行的滿足條件行的sum_range同行進(jìn)行求和。同行進(jìn)行求和。3.7 邏輯函數(shù)邏輯函數(shù)nSumIF案例案例n某商場的銷售記錄如圖所示。統(tǒng)計各類商品的銷售總數(shù)、總金某商場的銷售記錄如圖所示。統(tǒng)計各類商品的銷售總數(shù)、總金額。額。n統(tǒng)計男、女銷售人員的人數(shù)及銷售額在統(tǒng)計男、女銷售人員的人數(shù)及銷售額在10000以上的人數(shù)以上的人數(shù)n=SUMIF(D$3:D$13,I3,G$3:G$13)n向下復(fù)制此公式邏輯函數(shù)邏輯函數(shù)工作表函數(shù)工作表函數(shù)5、綜合案例、綜合案例n某班學(xué)

35、生成績表如圖所示，統(tǒng)計出其中的某班學(xué)生成績表如圖所示，統(tǒng)計出其中的平均分、總平均分、總分、參考人數(shù)、等級及缺考人數(shù)分、參考人數(shù)、等級及缺考人數(shù)等數(shù)據(jù)。等數(shù)據(jù)。 該工作表要該工作表要用到：用到：if,average,sum,count,countif，max,min等函數(shù)等函數(shù)邏輯函數(shù)邏輯函數(shù)工作表函數(shù)工作表函數(shù)n在按下圖所示，輸入各單元格公式，然后向下在按下圖所示，輸入各單元格公式，然后向下或向右復(fù)制，就能得出需要的數(shù)據(jù)。或向右復(fù)制，就能得出需要的數(shù)據(jù)。3.8 數(shù)學(xué)數(shù)學(xué)和三角函數(shù)和三角函數(shù)n1、概述、概述nExcel提供了許多數(shù)學(xué)和三角函數(shù)，它們能夠提供了許多數(shù)學(xué)和三角函數(shù)，它們能夠完成大多數(shù)

36、數(shù)學(xué)和三角運算，這些函數(shù)可以完成大多數(shù)數(shù)學(xué)和三角運算，這些函數(shù)可以在公式中直接引用，然后將公式的計算結(jié)果在公式中直接引用，然后將公式的計算結(jié)果返回到輸入公式的單元格中。返回到輸入公式的單元格中。2、常見的數(shù)學(xué)函數(shù)、常見的數(shù)學(xué)函數(shù)nMOD 、TRUNC 、ABS 、SQRT 、SIN 、ASIN(n1)，ACOS(n1)，ATAN(n2)、EXP(n)、POWER(x, n)、LN(n)、FACT(n)、LOG(n, base)、MINVERSE(array)、MMULT(array1, array2) 3.8 數(shù)學(xué)數(shù)學(xué)和三角函數(shù)和三角函數(shù)3、數(shù)學(xué)函數(shù)應(yīng)用案例、數(shù)學(xué)函數(shù)應(yīng)用案例【例例1】用隨機函

37、數(shù)產(chǎn)生大量的實驗數(shù)據(jù)，計算排名。用隨機函數(shù)產(chǎn)生大量的實驗數(shù)據(jù)，計算排名。問題：有一張工資工作表，結(jié)構(gòu)如圖所示?，F(xiàn)以問題：有一張工資工作表，結(jié)構(gòu)如圖所示。現(xiàn)以此表為例說明隨機函數(shù)和數(shù)組的結(jié)合使用。此表為例說明隨機函數(shù)和數(shù)組的結(jié)合使用。 3.8 數(shù)學(xué)數(shù)學(xué)和三角函數(shù)和三角函數(shù)n產(chǎn)生日期產(chǎn)生日期（1）在）在B2單元格中輸入?yún)⒓庸ぷ鞯淖钤鐣r間，即在單元格中輸入?yún)⒓庸ぷ鞯淖钤鐣r間，即在B2單元格中輸入單元格中輸入“1970/1/1”。（2）選中要產(chǎn)生日期的單元格區(qū)域，如）選中要產(chǎn)生日期的單元格區(qū)域，如B3:B8。（3）輸入公式）輸入公式“=B2+RAND()*1825”。（4）按）按Ctrl+Enter鍵

38、。鍵。 （5）將）將B3:B8格式化為需要的日期格式。格式化為需要的日期格式。n產(chǎn)生加班時間，計算加班工資產(chǎn)生加班時間，計算加班工資n（1）選中）選中C2:C8單元格區(qū)域。單元格區(qū)域。n（2）輸入公式）輸入公式“= INT(RAND()*600)”。n（3）按）按Ctrl +Enter鍵。鍵。3.8 數(shù)學(xué)數(shù)學(xué)和三角函數(shù)和三角函數(shù)n產(chǎn)生基本工資產(chǎn)生基本工資假設(shè)基本工資在假設(shè)基本工資在8003 000這個范圍內(nèi)，其產(chǎn)生方法如下：這個范圍內(nèi)，其產(chǎn)生方法如下：（1）選擇）選擇D2:D8單元格區(qū)域。單元格區(qū)域。（2）輸入公式）輸入公式“=800+int(RAND()*2200)”。（3）按）按Ctrl

39、+Enter鍵。鍵。n產(chǎn)生其它數(shù)據(jù)產(chǎn)生其它數(shù)據(jù)假設(shè)圖中的其他數(shù)據(jù)都在假設(shè)圖中的其他數(shù)據(jù)都在1 000以內(nèi)，其產(chǎn)生方法如下：以內(nèi)，其產(chǎn)生方法如下：（1）選擇）選擇E2:H8單元格區(qū)域。單元格區(qū)域。（2）輸入公式：）輸入公式：“=int(RAND()*1000)”。（3）按）按Ctrl +Enter鍵鍵 3.8 數(shù)學(xué)數(shù)學(xué)和三角函數(shù)和三角函數(shù)n計算總收入和收入排名計算總收入和收入排名n（1）在）在I2輸入公式：輸入公式：“=sum(D2:H2)”。向下復(fù)制此公式，。向下復(fù)制此公式，n（2）在）在J2中輸入張大海的排名公式：中輸入張大海的排名公式：“=RANK(I2,$I$2:$I$8)”。向下復(fù)制此

40、公式。向下復(fù)制此公式。n上述操作的結(jié)果如下：上述操作的結(jié)果如下：3.8 數(shù)學(xué)數(shù)學(xué)和三角函數(shù)和三角函數(shù)3、數(shù)學(xué)函數(shù)應(yīng)用案例、數(shù)學(xué)函數(shù)應(yīng)用案例【例例2】用舍入函數(shù)解決計算誤差用舍入函數(shù)解決計算誤差 問題：下圖是一個工資數(shù)據(jù)表，問題：下圖是一個工資數(shù)據(jù)表，B2:G3單元格區(qū)域的數(shù)單元格區(qū)域的數(shù)據(jù)是用數(shù)組公式據(jù)是用數(shù)組公式“=RAND()*100”產(chǎn)生的。產(chǎn)生的。 n將小數(shù)點后的有效位將小數(shù)點后的有效位縮減為縮減為2位后的數(shù)據(jù)如第位后的數(shù)據(jù)如第4行所示；行所示；將將第第4行數(shù)據(jù)乘以行數(shù)據(jù)乘以3的結(jié)果如的結(jié)果如6行所示，可以看出，行所示，可以看出，E6：G6中的計算結(jié)果是中的計算結(jié)果是錯誤錯誤的。的。n

41、用用round函數(shù)將第函數(shù)將第1行的數(shù)據(jù)精確到小數(shù)點后行的數(shù)據(jù)精確到小數(shù)點后2位，其位，其結(jié)果如第結(jié)果如第8行所示，再將此結(jié)果乘以行所示，再將此結(jié)果乘以3，最后結(jié)果如第，最后結(jié)果如第10行所示。行所示。n第第10行的計算結(jié)果是正確的。行的計算結(jié)果是正確的。3.8 數(shù)學(xué)數(shù)學(xué)和三角函數(shù)和三角函數(shù)3.9日期日期及時間函數(shù)及時間函數(shù) 1、Excel處理日期的方式處理日期的方式nMicrosoft Excel 將日期存儲為序列號（稱為序列值），即一系將日期存儲為序列號（稱為序列值），即一系列連續(xù)的數(shù)字編號，每一個數(shù)字編號代表一個日期。在默認(rèn)情況列連續(xù)的數(shù)字編號，每一個數(shù)字編號代表一個日期。在默認(rèn)情況下，

42、數(shù)字下，數(shù)字1代表代表1900 年年 1 月月1 日，日，2代表代表1900年年1月月2日，日，39,448代表代表2008 年年 1 月月 1 日，因為從日，因為從1900 年年 1 月月 1 日到日到2008 年年 1 月月 1 日正好日正好 39,448 天。天。nExcel 將時間存儲為小數(shù)，因為時間被看作天的一部分。時間也將時間存儲為小數(shù)，因為時間被看作天的一部分。時間也被存為序列號，此序列號以秒為單位遞增。因為一天共有：被存為序列號，此序列號以秒為單位遞增。因為一天共有：24*60*60=86400秒，所以秒，所以1/86400代表的時間是：代表的時間是：00:00:01，2/86

43、400代表的時間是：代表的時間是：00:00:02。n日期和時間都是數(shù)值，因此它們也可以進(jìn)行各種運算。日期和時間都是數(shù)值，因此它們也可以進(jìn)行各種運算。如果要計算兩個日期之間的差值，可以用一個日期減如果要計算兩個日期之間的差值，可以用一個日期減去另一個日期。去另一個日期。 3.9日期日期及時間函數(shù)及時間函數(shù)2DATE函數(shù)函數(shù)n用法用法nDATE(year, month, day)n功能功能nDATE函數(shù)利用所給的參數(shù)，構(gòu)造一個日期序列數(shù)函數(shù)利用所給的參數(shù)，構(gòu)造一個日期序列數(shù) n例如例如nDATE(2005,3,21)的結(jié)果是的結(jié)果是2005-3-213.9日期日期及時間函數(shù)及時間函數(shù)3YEAR，

44、MONTH，DAY函數(shù)函數(shù)n用法用法nYEAR(serial_number)nMONTH(serial_number)nDAY(serial_number)n其中的其中的serial_number是一個日期或數(shù)字。是一個日期或數(shù)字。n功能功能nYEAR函數(shù)返回某日期的年份。函數(shù)返回某日期的年份。nMONTH函數(shù)返回以系列數(shù)表示的日期中的月份。函數(shù)返回以系列數(shù)表示的日期中的月份。nDAY函數(shù)返回以系列數(shù)表示的某日期的天數(shù)，用整函數(shù)返回以系列數(shù)表示的某日期的天數(shù)，用整數(shù)數(shù)131表示。表示。 3.9日期日期及時間函數(shù)及時間函數(shù)4TODAY、NOW函數(shù)函數(shù)n用法用法nTODAY( )nNOW( )n功

45、能功能nTODAY函數(shù)返回系統(tǒng)的當(dāng)前日期。函數(shù)返回系統(tǒng)的當(dāng)前日期。nNOW函數(shù)計算當(dāng)前日期和時間。函數(shù)計算當(dāng)前日期和時間。 3.9日期日期及時間函數(shù)及時間函數(shù)5WEEKDAYn用法用法nWEEKDAY(serial_number, return_type)n其中：其中：nserial_number代表要查找的日期，或日期的系列代表要查找的日期，或日期的系列數(shù)，以了解該日期為星期幾；數(shù)，以了解該日期為星期幾；return_type確定返確定返回值類型的數(shù)字，?；刂殿愋偷臄?shù)字，。n功能功能nWEEKDAY計算給定的日期是星期幾計算給定的日期是星期幾 3.9日期日期及時間函數(shù)及時間函數(shù)6、NETWO

46、RKDAYS函數(shù)函數(shù)n用法用法nNETWORKDAYS(start_date, end_date, holidays) n其中其中nstart_date表示代表開始日期，表示代表開始日期，end_date為終止為終止日期，日期， holidays表示不在工作日歷中的一個或多個表示不在工作日歷中的一個或多個日期所構(gòu)成的可選區(qū)域，如元旦節(jié)、五一節(jié)、春節(jié)。日期所構(gòu)成的可選區(qū)域，如元旦節(jié)、五一節(jié)、春節(jié)。 3.9日期日期及時間函數(shù)及時間函數(shù)n案例案例n【例例】某公司將每個員工的加班時間記錄在某公司將每個員工的加班時間記錄在Excel的工作表的工作表中，便于計算加班工資。加班工資按小時計算，若加班時中，便

47、于計算加班工資。加班工資按小時計算，若加班時間不足間不足1小時，但超過半小時由按小時，但超過半小時由按1計時計算，不足半小時計時計算，不足半小時則不算加班時間。平時的加班工資每小時則不算加班時間。平時的加班工資每小時10元，節(jié)假時則元，節(jié)假時則加倍為加倍為20元每小時元每小時 。n假設(shè)員工的加班時間如圖的假設(shè)員工的加班時間如圖的A1:C11所示，現(xiàn)要計算加班時所示，現(xiàn)要計算加班時長（長（D列），判斷加班時間是星期幾和節(jié)假日（列），判斷加班時間是星期幾和節(jié)假日（E、F列），列），計算工齡（計算工齡（J列）、總加班時間（列）、總加班時間（L列）、節(jié)假時加班時間列）、節(jié)假時加班時間（L列）、工作日加

48、班（列）、工作日加班（M列，本該休息，但加班了），以列，本該休息，但加班了），以及加班工資（及加班工資（N列）。列）。 3.9日期日期及時間函數(shù)及時間函數(shù)n用用日期、時間日期、時間函數(shù)計算下表函數(shù)計算下表D、E、F、J、K、L、M、N列相應(yīng)數(shù)據(jù)。列相應(yīng)數(shù)據(jù)。3.10 字符字符函數(shù)函數(shù)1、概述、概述nExcel提供了接近提供了接近30個文本、字符方面的函數(shù)，個文本、字符方面的函數(shù)，用這些函數(shù)對工作表中的文本數(shù)據(jù)進(jìn)行查找、用這些函數(shù)對工作表中的文本數(shù)據(jù)進(jìn)行查找、替換、取子串、數(shù)字與文本的轉(zhuǎn)換，以及數(shù)據(jù)替換、取子串、數(shù)字與文本的轉(zhuǎn)換，以及數(shù)據(jù)的格式化操作等都有十分重要的作用的格式化操作等都有十分重要

49、的作用 。2、常用文本類函數(shù)、常用文本類函數(shù)nRIGHT(text,n)該函數(shù)從該函數(shù)從text文本的右邊取出文本的右邊取出n個字符。個字符。nLEFT(text,n)該函數(shù)從該函數(shù)從text文本的左邊取出文本的左邊取出n個字符個字符3.10 字符字符函數(shù)函數(shù)nMID(text,n,m)n該函數(shù)從該函數(shù)從text文本的第文本的第n個字符起，取出個字符起，取出m個字符。個字符。nREPLACE(text1,n,m,text2)n該函數(shù)用該函數(shù)用text2替換替換text1文本的第文本的第n個位置開始的個位置開始的m個字符。個字符。nSEARCH(text1,text2,n)n該函數(shù)從該函數(shù)從te

50、xt1的第的第n個字符位置開始查找個字符位置開始查找text1中中是否包含是否包含text2，若找就返回，若找就返回text2在在text1中的起中的起始位置。若沒有找到則返回錯誤值始位置。若沒有找到則返回錯誤值“#value!”3.10 字符字符函數(shù)函數(shù)nTEXT(value,format_text)n該函數(shù)指定的格式碼該函數(shù)指定的格式碼format_text將數(shù)值型數(shù)據(jù)將數(shù)值型數(shù)據(jù)Value格式化為文本。其中格式化為文本。其中value可為數(shù)字、結(jié)果可為數(shù)字、結(jié)果為數(shù)字的計算公式、包含數(shù)字的單元格引用。為數(shù)字的計算公式、包含數(shù)字的單元格引用。nformat_text可為任何數(shù)字格式（可為任

51、何數(shù)字格式（有關(guān)格式碼請參有關(guān)格式碼請參考第考第2章的相關(guān)內(nèi)容章的相關(guān)內(nèi)容）。）。nVALUE(text)n將數(shù)字形式的文本轉(zhuǎn)換成數(shù)字。將數(shù)字形式的文本轉(zhuǎn)換成數(shù)字。n比如：比如：VALUE(32)=32，VALUE(09)=9 3.10 字符字符函數(shù)函數(shù)n案例案例n【例例】文本函數(shù)應(yīng)用舉例文本函數(shù)應(yīng)用舉例 某銀行要建立下圖某銀行要建立下圖3.所示的用戶資料表。所示的用戶資料表。n其中的其中的出生日期出生日期從身份證號碼中提取；從身份證號碼中提取；n如果是男性，其尊稱就是如果是男性，其尊稱就是“X先生先生”，如果是女性，如果是女性，其尊稱就是其尊稱就是“X女士女士”，X是用戶的姓氏；是用戶的姓氏

52、；n存款帳號存款帳號是出生日期（去掉出生年的前兩位數(shù)字）；是出生日期（去掉出生年的前兩位數(shù)字）；n存款密碼存款密碼是身份證號碼的最后是身份證號碼的最后6位數(shù)字；位數(shù)字；n最后要最后要找出找出解放西路的用戶姓名，以便給他們發(fā)新解放西路的用戶姓名，以便給他們發(fā)新的銀行卡。的銀行卡。3.10 字符字符函數(shù)函數(shù)n用文本類函數(shù)產(chǎn)生用文本類函數(shù)產(chǎn)生E2:I9區(qū)域的數(shù)據(jù)。區(qū)域的數(shù)據(jù)。3.10 字符字符函數(shù)函數(shù)（1）產(chǎn)生出生日期）產(chǎn)生出生日期n在在E2單元格輸入公式，然后將它向下復(fù)制此公式就能夠生成所有單元格輸入公式，然后將它向下復(fù)制此公式就能夠生成所有人的出生日期。人的出生日期。n=MID(D2,7,4)&

53、年年&VALUE(MID(D2,11,2)&月月&MID(D2,13,2)&日日“（2）生成用戶的尊稱）生成用戶的尊稱n在在F2單元格輸入下面的公式，并將此公式向下復(fù)制，生成所有人單元格輸入下面的公式，并將此公式向下復(fù)制，生成所有人的尊稱。的尊稱。n=LEFT(A2,1)&IF(B2=女女,女士女士,先生先生)（3）生成存款帳號）生成存款帳號n在在G2單元格輸入下面的公式，并將此公式向下復(fù)制，單元格輸入下面的公式，并將此公式向下復(fù)制，生成所有人的存款帳號。生成所有人的存款帳號。n=TEXT(E2,yymmdd)3.10 字符字符函數(shù)函數(shù)n（4）生成存款密碼）生成存款密碼n在在H2中輸入下面的公

54、式，并向下復(fù)制，產(chǎn)生所有人的存款密碼。中輸入下面的公式，并向下復(fù)制，產(chǎn)生所有人的存款密碼。n=RIGHT(D2,6)n（5）找出解放西路的用戶姓名）找出解放西路的用戶姓名n在在I2單元格中輸入下面的公式，并向下復(fù)制單元格中輸入下面的公式，并向下復(fù)制n=IF(SEARCH(解放西路解放西路,C2),A2)3.11 錯誤信息錯誤信息函數(shù)函數(shù)1、 Excel的常見錯誤信息的常見錯誤信息 錯錯 誤誤 值值錯錯 誤誤 原原 因因# 單元格所含的數(shù)字、日期或時間比單元格寬，或者單元格的日期、時間公式產(chǎn)生了一個負(fù)單元格所含的數(shù)字、日期或時間比單元格寬，或者單元格的日期、時間公式產(chǎn)生了一個負(fù)值，就會產(chǎn)生值，就

55、會產(chǎn)生 # 錯誤。錯誤。#VALUE! 1. 在需要數(shù)字或邏輯值時輸入了文本，在需要數(shù)字或邏輯值時輸入了文本，Microsoft Excel 不能將文本轉(zhuǎn)換為正確的數(shù)據(jù)類型。不能將文本轉(zhuǎn)換為正確的數(shù)據(jù)類型。#DIV/O! 1. 輸入的公式中包含明顯的除數(shù)為零（輸入的公式中包含明顯的除數(shù)為零（0），如），如 =5/0。2. #NAME? 1. 在公式中輸入文本時沒有使用雙引號。在公式中輸入文本時沒有使用雙引號。Microsoft 將其解釋為名稱，但這些名字沒有定義。將其解釋為名稱，但這些名字沒有定義。2. 函數(shù)名的拼寫錯誤。函數(shù)名的拼寫錯誤。#N/A 1. 內(nèi)部函數(shù)或自定義工作表函數(shù)中缺少一個或

56、多個參數(shù)。內(nèi)部函數(shù)或自定義工作表函數(shù)中缺少一個或多個參數(shù)。2. 在數(shù)組公式中，所用參數(shù)的行在數(shù)組公式中，所用參數(shù)的行數(shù)或列數(shù)與包含數(shù)組公式的區(qū)域的行數(shù)或列數(shù)不一致。數(shù)或列數(shù)與包含數(shù)組公式的區(qū)域的行數(shù)或列數(shù)不一致。3. 在沒有排序的數(shù)據(jù)表中使用了在沒有排序的數(shù)據(jù)表中使用了VLOOKUP，HLOOKUP或或MATCH工作表函數(shù)查找數(shù)值工作表函數(shù)查找數(shù)值#REF!刪除了公式中所引用的單元或單元格區(qū)域刪除了公式中所引用的單元或單元格區(qū)域#NUM!1. 由公式產(chǎn)生的數(shù)字太大或太小，由公式產(chǎn)生的數(shù)字太大或太小，2. 在需要數(shù)字參數(shù)的函數(shù)中使用了非數(shù)字參數(shù)在需要數(shù)字參數(shù)的函數(shù)中使用了非數(shù)字參數(shù)#NULL!

57、在公式的兩個區(qū)域中加入了空格從而求交叉區(qū)域，但實際上這兩個區(qū)域無重疊區(qū)域。在公式的兩個區(qū)域中加入了空格從而求交叉區(qū)域，但實際上這兩個區(qū)域無重疊區(qū)域。3.11 錯誤信息錯誤信息函數(shù)函數(shù)n2、 Excel錯誤信息函數(shù)錯誤信息函數(shù) 函數(shù)名函數(shù)名函數(shù)功能函數(shù)功能ISBLANK（X）判定判定X是否為空白單元格。是否為空白單元格。ISERR（X）判定判定X是否為任意錯誤值（除去是否為任意錯誤值（除去 #N/A）。）。ISERROR（X）判定判定X是否為任意錯誤值（是否為任意錯誤值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或或 #NULL!）。）。ISLOGICAL（

58、X）判定判定X是否為邏輯值。是否為邏輯值。ISNA（X）判定判定X是否為錯誤值是否為錯誤值 #N/A（值不存在）。（值不存在）。ISNONTEXT（X）判定判定X是否不是文本的任意項（注意此函數(shù)在值為空白單元是否不是文本的任意項（注意此函數(shù)在值為空白單元格時返回格時返回 TRUE）。）。ISNUMBER（X）判定判定X是否為數(shù)字。是否為數(shù)字。ISREF（X）判定判定X是否為引用。是否為引用。ISTEXT（X）判定判定X是否為文本。是否為文本。3.11 錯誤信息錯誤信息函數(shù)函數(shù)n案例案例n【例例】某商場將各種商品的定價保存在一個獨某商場將各種商品的定價保存在一個獨立的工作表中，如圖（立的工作表中

59、，如圖（a）所示。）所示。 將產(chǎn)品的銷將產(chǎn)品的銷售記錄保存在另一個工作表中，如圖（售記錄保存在另一個工作表中，如圖（b）所）所示。示。n其中銷售記錄表中的其中銷售記錄表中的“單價單價”是用是用Vlookup函數(shù)從函數(shù)從“商品定價表商品定價表”中查找。中查找。nF列總額是用公式計算出來的；列總額是用公式計算出來的；nG列數(shù)據(jù)是條件函數(shù)和文本函數(shù)查詢的結(jié)果；列數(shù)據(jù)是條件函數(shù)和文本函數(shù)查詢的結(jié)果；3.11 錯誤信息錯誤信息函數(shù)函數(shù)n雖然圖（雖然圖（b）的信息是我們需要的，但在實際工作中，這樣的工作表）的信息是我們需要的，但在實際工作中，這樣的工作表并不實用。并不實用。n用錯誤信息處理函數(shù)處理后的工作表如下頁所示，這才是可用的工作用錯誤信息處理函數(shù)處理后的工作表如下頁所示，這才是可用的工作表表3.11 錯誤信息錯誤信息函數(shù)函數(shù)n用錯誤信息函數(shù)處理的結(jié)果用錯誤信息函數(shù)處理的結(jié)果The End