chapter-05公司名稱函數(shù)Excel與數(shù)據(jù)處理(第3版)杜茂康電子教案

1、第5章 公式、名稱與函數(shù)Excel與數(shù)據(jù)處理 第3版2021/9/12認(rèn)識(shí)函數(shù)好比EXCEL的武林秘笈，如果要成為EXCEL高手，誰都必須學(xué)習(xí)它。函數(shù)也是EXCEL與Word之類軟件中的表格的主要區(qū)別，它使EXCEL具有強(qiáng)大的運(yùn)算能力。EXCEL提供了涉及各個(gè)不同領(lǐng)域的函數(shù)接近400個(gè)。運(yùn)用它們解決工作中的實(shí)際問題，能夠極大提高工作效率，使枯燥的工作變得輕松、簡單。但學(xué)習(xí)是一個(gè)枯燥而艱辛的過程！當(dāng)然嘛，誰讓你想成為高手呢？2021/9/12本章學(xué)習(xí)目標(biāo)1、掌握數(shù)組公式的用法2、掌握名稱的用法3、掌握常用工作表函數(shù)的用法4、掌握常見統(tǒng)計(jì)函數(shù)的用法5、掌握條件函數(shù)的用法，特別是：Ifsumifco

2、untif等6、掌握解查找引用類函數(shù)的用法7、掌握常見日期、文本類函數(shù)2021/9/125.1 數(shù)組公式及其應(yīng)用1、概述普通公式只執(zhí)行一個(gè)簡單計(jì)算，并且返回一個(gè)運(yùn)算結(jié)果。數(shù)組公式能夠同時(shí)對(duì)一組或兩組以上的數(shù)據(jù)進(jìn)行計(jì)算，計(jì)算的結(jié)果可能是一個(gè)，也可能是多個(gè)。在數(shù)組公式中使用的數(shù)據(jù)稱為數(shù)組參數(shù)，數(shù)組參數(shù)可以是一個(gè)數(shù)據(jù)區(qū)域，也可以是數(shù)組常量。2021/9/125.1 數(shù)組公式及其應(yīng)用2、數(shù)組公式的建立方法（1）選中需要保存數(shù)組公式結(jié)果的單元格或單元格區(qū)域。（2）輸入公式的內(nèi)容。（3）按Ctrl+Shift+Enter鍵。2021/9/125.1 數(shù)組公式及其應(yīng)用3、用數(shù)組公式計(jì)算兩個(gè)數(shù)據(jù)區(qū)域的乘積 【

3、例5.1】 某茶葉店經(jīng)銷多種茶葉，已知各種茶葉的單價(jià)以及12月份各種茶葉的銷量如圖所示，計(jì)算各種茶葉的銷售額。2021/9/125.1 數(shù)組公式及其應(yīng)用4、數(shù)組擴(kuò)展 在公式中用數(shù)組作為參數(shù)時(shí)，所有的數(shù)組必須是同維的。如果數(shù)組參數(shù)或數(shù)組區(qū)域的維數(shù)不匹配，Excel會(huì)自動(dòng)擴(kuò)展該參數(shù) 。比如，LG空調(diào)售價(jià)為7654元，重慶各商場的銷售數(shù)據(jù)如下圖的B、C兩列所示?，F(xiàn)要計(jì)算各商場的銷售總額。 2021/9/125.1 數(shù)組公式及其應(yīng)用【例5.2】 某次期末考試的學(xué)生成績表如圖5.2所示，其中有四科成績，現(xiàn)要計(jì)算各科成績的總分，以及各科成績的綜合測評(píng)分?jǐn)?shù)?？偡值挠?jì)算方式為各科成績的總和，綜合測評(píng)分的計(jì)算方

4、式為“政治經(jīng)濟(jì)學(xué)*0.2+(技術(shù)經(jīng)濟(jì)學(xué)+計(jì)算機(jī)基礎(chǔ)+PASCAL語言程序設(shè)計(jì))*0.8”。2021/9/125.1 數(shù)組公式及其應(yīng)用【例5.3】假設(shè)某商場A型電視機(jī)售價(jià)一樣，現(xiàn)有一個(gè)28天的銷售記錄，計(jì)算每天的銷售額，如圖5.3所示。D列銷售額的計(jì)算方法如下：2021/9/125.1 數(shù)組公式及其應(yīng)用5、二維數(shù)組 涉及許多行(或列)數(shù)據(jù)處理的數(shù)組公式，就是所謂的二維數(shù)組。合理地運(yùn)用二維數(shù)組的運(yùn)算功能，會(huì)提高數(shù)據(jù)處理的能力，有時(shí)在不同工作表之間進(jìn)行數(shù)據(jù)匯總時(shí)會(huì)特別有效 。2021/9/125.1 數(shù)組公式及其應(yīng)用案例【例5.4】 某商場秋季進(jìn)行換季服裝大降價(jià)，所有服裝都在打折，打折的比例隨時(shí)都在

5、調(diào)整，計(jì)算打折后的新價(jià)格。如圖5.4所示。 2021/9/125.1 數(shù)組公式及其應(yīng)用【例5.5】出版社要統(tǒng)計(jì)2008年發(fā)往各地新華書店的各類圖書的銷售量，并由此計(jì)算出各類圖書的總銷售額。設(shè)每個(gè)省都把本地各新華書店的銷售量統(tǒng)計(jì)在一個(gè)工作表中，最后有許多工作表的數(shù)據(jù)要進(jìn)行匯總，圖5.5該出版社2008年各類圖書在四川、重慶兩者的銷量情況表（實(shí)際的應(yīng)用更加復(fù)雜）。 2021/9/125.1 數(shù)組公式及其應(yīng)用用數(shù)組公式匯總圖書2021/9/125.2 公式的循環(huán)引用1、循環(huán)引用的概念如果公式引用了自己所在的單元格，不論是直接的還是間接的，都稱為循環(huán)引用。在一般情況下，很少使用循環(huán)引用來處理問題。但對(duì)

6、于迭代求解一類問題，如求數(shù)的階乘、數(shù)列求和等問題，可以用循環(huán)引用求解。若循環(huán)引用的條件或迭代次數(shù)設(shè)置不當(dāng)，EXCEL會(huì)報(bào)告一個(gè)錯(cuò)誤信息。 2021/9/125.2 公式的循環(huán)引用【例5.6】下圖是循環(huán)引用的一個(gè)事例。在A1單元格中輸入的公式“=A2+A3”；在A2單元格中輸入數(shù)字2；在A3單元格中輸入公式“=A1*0.3”，見圖(a)。這樣，A3單元格的值依賴于A1，而A1單元格的值又依賴于A3，它們形成了間接的循環(huán)引用，在未進(jìn)行迭代求解情況下的結(jié)果如圖（b）所示。該循環(huán)引用是可解的，進(jìn)行迭代求解最后的結(jié)果如圖（c）所示。 2021/9/125.3 名稱5.3.1、名稱概述 可以給一個(gè)單元格或

7、單元格區(qū)域取一個(gè)名稱，這個(gè)名稱可以出現(xiàn)在公式中，用來指代引用的單元格或單元格區(qū)域，這會(huì)使公式的意義更加明確。名稱其實(shí)是一個(gè)標(biāo)識(shí)符，由字母或下劃線開頭的一個(gè)或多個(gè)字符組成。名稱不區(qū)分字母的大小寫，比如ABC、abc、Abc都是同一個(gè)名稱。在Excel中，還可以用漢字作為名稱?？梢詾橥粏卧窕騿卧駞^(qū)域定義多個(gè)不同的名稱。名稱一旦定義就可以在同一工作薄的不同工作表之間共用。2021/9/125.3 名稱2、名稱的意義在Excel中，同一個(gè)工作簿中的名稱是共享的，在一個(gè)工作表中定義的名稱，可以被另一個(gè)工作表的公式引用，這使公式具有更強(qiáng)大的功能，它能夠利用單元格的名稱在不同的工作表中查找到正確的單元

8、格或單元格區(qū)域。名稱給單元格的引用帶來了許多方便，因?yàn)樗谷藗儾槐赜涀卧裨诠ぷ鞅碇械囊梦恢?，用名稱就能找到它。2021/9/125.3.2 名稱的定義在Excel中，可以為一個(gè)獨(dú)立的單元格、連續(xù)的單元格區(qū)域或許多不連續(xù)的單元格構(gòu)成的單元格組合定義一個(gè)名稱（或多個(gè)名稱） 定義名稱有多種方法，如定義、粘貼、指定或標(biāo)志等 2021/9/125.3.2 名稱的定義(1)、使用命令定義單元格或單元格區(qū)域的名稱【例5.7】在下圖中，B2單元格被命名為“存款利率”，在D5中輸入的公式是“=C5*存款利率”，其中的“存款利率”就是B2單元格的名稱。 B2單元格的名稱定義方法如下：2021/9/125.3

9、.2 名稱的定義（1）單擊“公式”|“定義的名稱”組中|“定義名稱”命令按鈕.（2）在彈出的“新建名稱”對(duì)話框的“引用位置” 輸入要定義名稱的單元格引用位置。（3）在“在當(dāng)前工作簿中的名稱” 中輸入名稱 2021/9/125.3.2 名稱的定義(2)、使用指定的方式定義名稱“指定”的方式一次可定義多個(gè)名稱：可以將表格的首行或首列指定為相應(yīng)的列或行的名稱。方法（1）選中要指定名稱的單元格區(qū)域。（2）單擊“公式”|“定義的名稱”組中|“根據(jù)所選內(nèi)容創(chuàng)建”命令按鈕。（3）在彈出的“以選定區(qū)域創(chuàng)建名稱”對(duì)話框中，選中“首行”、“最左列”的復(fù)選框。這樣就把該表的首行、首列指定成了相應(yīng)的列、行的名稱202

10、1/9/125.3.2 名稱的定義指定首行、最左列為名稱的案例【例5.8】 圖5.10是某建筑工地的工人獎(jiǎng)金統(tǒng)計(jì)表，一共有幾百個(gè)工人，其中的某些工人會(huì)隨時(shí)查詢自己的獎(jiǎng)金，這就需要在A列的幾百行數(shù)據(jù)中進(jìn)行查找。名字能夠很好地解決這類問題，方法如下。 2021/9/125.3.2 名稱的定義(3)用名稱欄定義名稱工作表全選按鈕（工作表列標(biāo)A左邊的按鈕）上面的編輯框稱為名稱欄，它隨時(shí)顯示當(dāng)前單元格的名稱或引用。它可以用來定義名稱?！纠?.9】 在圖5.11中，C4:C8區(qū)域中輸入的是數(shù)組公式：“=數(shù)量*單價(jià)”，其定義方法請(qǐng)見圖中的標(biāo)注。名稱欄2021/9/125.3.3 名稱的應(yīng)用定義一個(gè)工作薄級(jí)的

11、名字后，該名字就能被本工作簿中的各個(gè)工作表直接引用。【例5.10】 圖5.12是某蔬菜超市的銷售記錄。該超市把蔬菜單價(jià)存放在一張工作表中，如圖5.12（a）所示，每種蔬菜的銷售記錄則保存在不同的工作表中，如圖5.12（b）所示?，F(xiàn)以小白菜的銷售金額計(jì)算為例，說明名字的跨表引用問題。2021/9/125.3.3 名稱的應(yīng)用1）通過名稱在不同的工作表之間傳遞數(shù)據(jù) 通過名稱查找蔬菜單價(jià)指定A2:B12最左邊為名稱2021/9/125.3.3 名稱的應(yīng)用2)名稱結(jié)合Indirect函數(shù)的應(yīng)用指定A2:B12的最左列為名稱；用名稱查找蔬菜單價(jià)，見D列；若不用名稱，單價(jià)要從C列的幾百個(gè)數(shù)據(jù)中去查找！ 這種

12、方法雖然比直接從工作表的單元格中查找數(shù)據(jù)方便了不少，但輸入量仍然較大。用它處理成千上萬行的數(shù)據(jù)查詢?nèi)匀坏托?。最佳方法是用名稱和Indirect函數(shù)相結(jié)合，非常方便，高效。2021/9/125.3.3 名稱的應(yīng)用某蔬菜商在一個(gè)工作表中保存蔬菜的單價(jià)，在另一工作表中保存銷售記錄，現(xiàn)要查找銷售記錄表中各蔬菜的單價(jià)。指定A2:B11區(qū)域的“最左列”為名稱在D2中輸入公式：=INDIRECT(B2)向下復(fù)制該公式！2021/9/125.3.3 名稱的應(yīng)用2）名稱與Indirect函數(shù)結(jié)合應(yīng)用Indirect函數(shù)與名稱相結(jié)合，可使數(shù)據(jù)查找更方便。Indirect函數(shù)的用法是： Indirect(x) 其

13、中的X可以是單元格引用或名稱。若X為單元格引用，它必須用“”引起來，如：=INDIRECT(“A1”)，將返回A1單元格中的內(nèi)容若X為單元格名稱，它將返回該名稱所對(duì)應(yīng)的單元格的內(nèi)容。2021/9/125.3.3 名稱的應(yīng)用3)應(yīng)用行列交叉點(diǎn)查找數(shù)據(jù)所謂行列交叉點(diǎn)指的是由行的名稱 列的名稱確定的單元格。 把一個(gè)工作表的首行、首列指定為名稱之后，可以使用名稱引用行、列交叉點(diǎn)對(duì)應(yīng)單元格中的數(shù)據(jù)。案例【例5.11】 圖5.13是一個(gè)學(xué)生成績表，用指定的方式把它的首行、首列指定成了名字。2021/9/125.3.3 名稱的應(yīng)用1、建立如下表格并選中A1:F8區(qū)域2、選擇“公式”|“定義的名稱”|“根據(jù)所

14、選內(nèi)容創(chuàng)建”命令4、指定名字對(duì)話框中和項(xiàng)為名稱現(xiàn)在做試驗(yàn)1、選中K2:K8,輸入“=化學(xué)”,按“Ctrl+Shift+Enter”2、選中A10:F10,輸入“=李四”,按“Ctrl+Shift+Enter”3、在L2單元格中輸入公式“=李達(dá) 化學(xué)”2021/9/125.3.3 名稱的應(yīng)用4)引用其他工作簿中的數(shù)據(jù) 當(dāng)一個(gè)公司的數(shù)據(jù)來源于不同的分公司或部門時(shí)，往往需要對(duì)不同分公司或部門的數(shù)據(jù)進(jìn)行匯總，這就要涉及多個(gè)工作薄的數(shù)據(jù)操作。名稱可以很好地解決這個(gè)問題。【例5.12】 采虹電視經(jīng)銷全國各省，每季度都要統(tǒng)計(jì)各種型號(hào)電視的銷售數(shù)量。各省的銷售代理每季度向采虹廠提供一個(gè)含有各種型號(hào)電視的匯總統(tǒng)

15、計(jì)表。現(xiàn)在對(duì)各省的報(bào)表數(shù)據(jù)進(jìn)行匯總統(tǒng)計(jì)。 2021/9/12指定A3:B8區(qū)域”最左列”為名稱指定A3:B8區(qū)域”最左列”為名稱B3單元格中輸入公式“=四川.xls!_21英寸+重慶.xls!_21英寸”；在B4單元格中輸入公式“=四川.xls!_25英寸+重慶.xls!_25英寸”，其余以此類推 2021/9/125.3.4 定義常量名稱1)常量名稱的意義用名字代表常量，如圓周率3.14159，自然對(duì)數(shù)的底數(shù)e等，然后在整個(gè)工作簿中引用這些常量名，會(huì)給數(shù)據(jù)處理帶來許多方便，這使用戶在運(yùn)用這些數(shù)據(jù)時(shí)不必關(guān)心它所在的工作表或單元格，直接用其名字就可以了。常量名稱的另一個(gè)好處:當(dāng)常量值變化時(shí),只需

16、要修改常量定義就行了,不必須在各具工作表的公式中尋找常量值并進(jìn)行修改.2021/9/125.3.4 定義常量名稱常量名稱的定義【例5.13】 圖5.15是用名字常量pi=3.14計(jì)算圓周長和圓面積的簡單例子。在名稱中輸入名稱指定名稱的有效范疇輸入名稱對(duì)應(yīng)的公式或常數(shù)直接應(yīng)用了常量名稱Pi計(jì)算圓的周長和面積2021/9/125.3.5 名稱管理器選擇“公式”選項(xiàng)卡，單擊其中“定義的名稱”組中的“名稱管理器”按鈕,顯示出名稱管理器對(duì)話框 通過“名稱管理器”可以新建編輯刪除名稱,可以重指定名稱的含義或?qū)?yīng)的單元格引用位置.2021/9/125.4 函數(shù)簡介1、函數(shù)的概念函數(shù)是能夠完成特定功能的程序。

17、在Excel中，它是系統(tǒng)預(yù)定義的一些公式，它們使用一些稱為參數(shù)的特定數(shù)值按特定的順序或結(jié)構(gòu)進(jìn)行計(jì)算，然后把計(jì)算的結(jié)果存放在某個(gè)單元格中。 在大多數(shù)情況下，函數(shù)的計(jì)算結(jié)果是數(shù)值。當(dāng)然，它也可以返回文本、引用、邏輯值、數(shù)組或工作表的信息 2021/9/125.4 函數(shù)簡介2、Excel函數(shù)分類分 類功 能 簡 介數(shù)據(jù)庫函數(shù)對(duì)數(shù)據(jù)清單中的數(shù)據(jù)進(jìn)行分析、查找、計(jì)算等日期與時(shí)間對(duì)日期和時(shí)間進(jìn)行計(jì)算、設(shè)置及格式化處理工程函數(shù)用于工程數(shù)據(jù)分析與處理信息函數(shù)對(duì)單元格或公式中數(shù)據(jù)類型進(jìn)行判定財(cái)務(wù)函數(shù)進(jìn)行財(cái)務(wù)分析及財(cái)務(wù)數(shù)據(jù)的計(jì)算邏輯函數(shù)進(jìn)行邏輯判定、條件檢查統(tǒng)計(jì)函數(shù)對(duì)工作表數(shù)據(jù)進(jìn)行統(tǒng)計(jì)、分析查找函數(shù)查找特定的數(shù)據(jù)

18、或引用公式中的特定信息文本函數(shù)對(duì)公式、單格中的字符、文本進(jìn)行格式化或運(yùn)算數(shù)學(xué)函數(shù)進(jìn)行數(shù)學(xué)計(jì)算等外部函數(shù)進(jìn)行外部函數(shù)調(diào)用及數(shù)據(jù)庫的鏈接查詢等功能自定義函數(shù)用戶用vba編寫，用于完成特定功能的函數(shù)2021/9/125.4.1 函數(shù)調(diào)用1、函數(shù)的語法 函數(shù)名(參數(shù)1, 參數(shù)2, 參數(shù)3,) 在公式中調(diào)用函數(shù) =IF(AVERAGE(F2:F5)50,SUM(G2:G5),0)嵌套函數(shù)Excel 2007允許嵌套多達(dá)64層Excel 2007可達(dá)255個(gè)參數(shù)2021/9/125.4.2 函數(shù)輸入在公式中直接輸入函數(shù)調(diào)用用函數(shù)調(diào)用向?qū)л斎?、Excel的幫助系統(tǒng)2021/9/125.5 使用Excel幫

19、助理解函數(shù)Excel的幫助系統(tǒng)具有極其強(qiáng)大的功能，利用幫助系統(tǒng)能夠解決使用Excel過程中所遇到的各種問題。這些問題包括Excel的新技術(shù)、疑難、專用名字解決、函數(shù)說明、函數(shù)應(yīng)用實(shí)例等。Excel的幫助系統(tǒng)比許多參考書內(nèi)容更全面，講述更清楚，一個(gè)Excel的真正用戶應(yīng)該會(huì)運(yùn)用幫助系統(tǒng)，從中獲取有用的資料。進(jìn)入Excel幫助系統(tǒng)的常用方法有：按F1鍵。單擊功能選項(xiàng)卡標(biāo)題欄最右邊的按鈕 2021/9/125.6 邏輯函數(shù)5.6.1 Excel的比較運(yùn)算符比較運(yùn)算又稱關(guān)系運(yùn)算，就是人們常說的比較式。比較運(yùn)算只有兩種不同的結(jié)果，要么“正確”，要么“錯(cuò)誤”，不可能有第三種結(jié)果 比較運(yùn)算符含 義示 例=等

20、于a1=3大于a1b1小于a1=大于等于a1=60比較運(yùn)算符含 義示 例=小于等于a1=”dd”不等于a102021/9/125.6.2 AND、NOT、OR、TRUE、FALSE函數(shù)AND、NOT、OR、TRUE、FALSE函數(shù)用法AND(x1,x2, ,x30)OR(x1, x2, ,x30)NOT(logical)案例AND(TRUE,TRUE)=TRUE，OR(TRUE,TRUE)=TRUEAND(TRUE,FALSE,TRUE, TRUE)=FALSE，OR(TRUE,FALSE,TRUE,TRUE)=TRUE。如果B1，B2，B3單元格中的值為 TRUE，F(xiàn)ALSE，TRUE，則A

21、ND(B1:B3)=FALSE，但OR(B1:B3)=TRUE。AND(2,2+3=5)=TRUE2021/9/125.6.3 條件函數(shù)IF用法IF(條件, 表達(dá)式1, 表達(dá)式2)。功能當(dāng)條件成立時(shí)，計(jì)算出表達(dá)式1的值；當(dāng)條件不成立時(shí)，計(jì)算出表達(dá)式2的值事例If(A160,“及格了”,“不及格”)，若A1單元格的值是76，則該函數(shù)的結(jié)果是：及格了；若A1單元格的值是50，則該函數(shù)的結(jié)果是:不及格。IF函數(shù)的嵌套調(diào)用2021/9/125.6 邏輯函數(shù)【例5.15】某單位有1 000多位黨員，要收黨費(fèi)。黨費(fèi)根據(jù)工資的高低，按不同的費(fèi)率收取，收費(fèi)的費(fèi)稅如右表所示。類似于這樣的工作，使用IF函數(shù)進(jìn)行數(shù)

22、據(jù)的匹配與查找是非常方便的。本例解決方法如下。工 資黨費(fèi)費(fèi)率2 000以上3%1 5002 0002.5%1 2001 5002%1 0001 2001.5%8001 0001%800以下02021/9/125.6 邏輯函數(shù)F3中公式意思為：當(dāng)E3大于2 000時(shí)，公式的值為3%；當(dāng)E3小于等于2 000，但大于1 500時(shí)，公式的值為2.5%；其余的以此類推 2021/9/125.6 邏輯函數(shù)【例5.16】某班考查成績?nèi)缦聢DA1:E10所示，將D列的地理成績轉(zhuǎn)換成等級(jí)制。2021/9/125.7 統(tǒng)計(jì)函數(shù)5.7.1、匯總求和函數(shù) 自動(dòng)求和按鈕1、選中F62、單擊“公式”選項(xiàng)卡中“自動(dòng)求和”按

23、鈕，就能求出F3、F4、F5之和。3、同方法求其它匯總和。2021/9/125.7.1、匯總求和函數(shù)2、SUM和SumQ函數(shù)用法SUM(x1, x2,x255) SUMQ(x1, x2,x255) x1，x2，x30是需要求和的參數(shù) ，可以是數(shù)據(jù)或單元格區(qū)域功能sum計(jì)算各參數(shù)的數(shù)值之和 ;sumq計(jì)算各數(shù)的平方之后說明： 參數(shù)表中的數(shù)字、邏輯值及數(shù)字的文本表達(dá)式將被計(jì)算，文本值被轉(zhuǎn)換成數(shù)字，而邏輯值“true”被轉(zhuǎn)換成數(shù)字1。 例如，SUM(3,2)=5，SUM(9,20,true)=30。2021/9/125.7.1、匯總求和函數(shù) 如果參數(shù)為數(shù)組或引用，那么只有其中的數(shù)字被計(jì)算。數(shù)組或引用

24、中的空白單元格、邏輯值、文本或錯(cuò)誤值將被忽略。例如，設(shè)A1的值為“9”，A2為true，則公式SUM(A1,A2,20)的計(jì)算結(jié)果為20，而不是30。因?yàn)楸竟街邪▋蓚€(gè)引用A1、A2，而A1的值為文本，A2的值為邏輯值，它們?cè)谟?jì)算時(shí)被忽略，最終就只有一個(gè)數(shù)值20參與運(yùn)算。 參數(shù)最多可達(dá)255個(gè)，不同類型的參數(shù)可以同時(shí)出現(xià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。2021/9/125.7.1 匯總求和函數(shù)1、條件求和函數(shù)SUMIF用法

25、SUMIF(range,criteria,sum_range) 其中，range是用于條件判斷的單元格區(qū)域，criteria條件，其形式可以為數(shù)字、表達(dá)式或文本；sum_range是需求和的實(shí)際單元格。只有當(dāng)range中的相應(yīng)單元格滿足條件時(shí)，才對(duì)sum_range中的單元格求和。若省略sum_range，則直接對(duì)range中的單元格求和。 功能 對(duì)range單元格區(qū)域中的數(shù)據(jù)進(jìn)行Criteria條件檢查，然后對(duì)滿足條件行的sum_range同行進(jìn)行求和。2021/9/125.7.1 匯總求和函數(shù)2、條件求和函數(shù)SUMIFS用法SUMIFS(sum_range, range1,criteria

26、1, range2,criteria2) 其中range1, range2, 是計(jì)算關(guān)聯(lián)條件的 1 至 127 個(gè)區(qū)域；Criteria1, criteria2, 是數(shù)字、表達(dá)式、單元格引用或文本形式的 1 至 127 個(gè)條件，用于定義要對(duì)哪些單元格求和。這些區(qū)域與條件是對(duì)應(yīng)的，即Criteria1是用于range1區(qū)域的條件，criteria2是用于range2的條件，以此類推。Sum_range是求和區(qū)域。功能 SUMIFS函數(shù)對(duì)某一區(qū)域內(nèi)滿足多重條件的單元格求和。2021/9/125.7.1 匯總求和函數(shù)【例5.18】 某家電商場的銷售記錄如圖5.22的A1:G13所示。現(xiàn)要統(tǒng)計(jì)出各種電

27、器的銷售總數(shù)量和銷售總金額，并將它統(tǒng)計(jì)在I1:K6區(qū)域的對(duì)應(yīng)單元格中；統(tǒng)計(jì)每位職工銷售各種產(chǎn)品的總數(shù)量，并將它統(tǒng)計(jì)在I8:L13區(qū)域中。2021/9/12（1）在圖5.22的J3單元格中輸入公式：=SUMIF($D$3:$D$10，I3，$E$3:$E$10)(2)將此公式向下復(fù)制到J4、J5、J6（1）在K3單元格輸入公式：=SUMIF($D$3:$D$10，I3，$G$3:$G$10)(2)將此公式向下復(fù)制到K4、K5、K6在J10單元格中輸入統(tǒng)計(jì)李本成銷售的彩電總數(shù)量的計(jì)算公式：=SUMIFS($E$3:$E$13,$D$3:$D$13,$I10,$A$3:$A$13,J$9)(2)將此

28、公式向下角復(fù)制到L13，2021/9/125.7.1 匯總求和函數(shù)4、乘積和與平方和SUMPRODUCT函數(shù)可在給定的幾組數(shù)組中，將數(shù)組間對(duì)應(yīng)的元素相乘，并返回乘積之和。用法如下：SUMPRODUCT(array1,array2,array3, .)Array1, array2, array3, . 為 2 到 255 個(gè)數(shù)組，其相應(yīng)元素需要進(jìn)行相乘并求和。此外，SUMXMY2(array_x,array_y)SUMX2MY2(array_x,array_y)SUMX2PY2(array_x,array_y) SUMXMY2計(jì)算兩數(shù)組中對(duì)應(yīng)數(shù)值之差的平方和 ，SUMX2PY2計(jì)算兩數(shù)組中對(duì)應(yīng)數(shù)

29、值的平方和之和 ，SUMX2MY2計(jì)算兩數(shù)組中對(duì)應(yīng)數(shù)值的平方差之和。2021/9/125.7.2 平均值函數(shù)1、平均值函數(shù)AVERAGE，AVERAGEA 用法AVERAGE(n1，n2，n255)AVERAGEA(n1，n2，n255)其中，n1，n2，n255是要計(jì)算平均值的參數(shù)，該函數(shù)最多允許有255個(gè)參數(shù)。參數(shù)可以是數(shù)字，或者是涉及數(shù)字的名稱、數(shù)組或引用 功能兩函數(shù)都是求參數(shù)的平均值。AverAge不對(duì)文本和邏輯值類數(shù)據(jù)進(jìn)行計(jì)算；AverageA要對(duì)文本和邏輯函數(shù)進(jìn)行平均值計(jì)算：文本被視為0，邏輯值true被視為1，false被視為0，空文本（）也作為0計(jì)算 2021/9/125.7.

30、2 平均值函數(shù)計(jì)數(shù)函數(shù)案例下圖說明AVERAGE函數(shù)與AVERAGEA函數(shù)的區(qū)別 2021/9/125.7.2 平均值函數(shù)2條件平均值函數(shù)Excel2007提供了兩個(gè)根據(jù)條件計(jì)算平均值的函數(shù)AVERAGEIF和AVERAGEIFS。AVERAGEIF(average_range, range1,criteria1)AVERAGEIFS(average_range,range1,criteria1,range2,criteria2)其中average_range是要計(jì)算平均值的一個(gè)或多個(gè)單元格，其中包括數(shù)字或包含數(shù)字的名稱、數(shù)組或引用；range1, range2, 是計(jì)算關(guān)聯(lián)條件的 1 至 1

31、27 個(gè)區(qū)域；Criteria1是用于range1的條件, criteria2是用于range2的條件, 它可以是數(shù)字、表達(dá)式、單元格引用或文本形式的 1 至 127 個(gè)條件，用于定義要對(duì)哪些單元格求平均值。2021/9/125.7.2 平均值函數(shù)【例5.20】某高三年級(jí)的期末考試成績表如圖5.21的B1:F10區(qū)域所示，統(tǒng)計(jì)每個(gè)班男女生的各科目平均成績。在J4中輸入如下公式,并將它向下向右復(fù)制到其它單元格=AVERAGEIFS(D$4:D$10,$B$4:$B$10,$H4,$C$4:$C$10,$I4)2021/9/125.7.3 統(tǒng)計(jì)個(gè)數(shù)的函數(shù)1、計(jì)數(shù)函數(shù)COUNT、COUNTA、COU

32、NTBLANK、COUNTIF 用法COUNT(x1, x2, ,x30)COUNTA(x1, x2, ,x30)COUNTBLANK (range)功能Count 統(tǒng)計(jì)數(shù)字的個(gè)數(shù)CountA統(tǒng)計(jì)數(shù)字和文本的個(gè)數(shù)CountBlank統(tǒng)計(jì)空白單元格的個(gè)數(shù)2021/9/125.7.3 統(tǒng)計(jì)個(gè)數(shù)的函數(shù)【例5.21】 用count和countA函數(shù)統(tǒng)計(jì)單元格個(gè)數(shù)。圖5.22是用這兩個(gè)函數(shù)統(tǒng)計(jì)同一區(qū)域的不同結(jié)果。2021/9/125.7.3 統(tǒng)計(jì)個(gè)數(shù)的函數(shù)2、COUNTIF函數(shù)用法COUNTIF(range, criteria)功能 統(tǒng)計(jì)出range中滿足條件criteria的數(shù)據(jù)個(gè)數(shù)。3、Counti

33、fsExcel 2007還提供了一個(gè)多條件統(tǒng)計(jì)函數(shù)COUNTIFS，它可以一次對(duì)多個(gè)不同區(qū)域進(jìn)行不同條件的計(jì)數(shù)，其用法如下：COUNTIF(range1， criteria1，range2， criteria2，range127， criteria127)2021/9/125.7.3 統(tǒng)計(jì)個(gè)數(shù)的函數(shù)【例5.22】有學(xué)生成績表如圖5.23所示。統(tǒng)計(jì)其中總分220分以下的人數(shù)；統(tǒng)計(jì)總分200分以上，物理80分以下，地理70分以下，化學(xué)65分以下的人數(shù)；統(tǒng)計(jì)姓王的同學(xué)人數(shù)。2021/9/125.7.3 統(tǒng)計(jì)個(gè)數(shù)的函數(shù)3計(jì)算大小、百分比和名次的函數(shù)LARGE(array,k)SMALL(array,k

34、)LARGE函數(shù)返回?cái)?shù)據(jù)集中第k個(gè)最大值，SMALL返回?cái)?shù)據(jù)集中第 k 個(gè)最小值，這兩個(gè)函數(shù)可以根據(jù)相對(duì)標(biāo)準(zhǔn)來選擇數(shù)值，返回?cái)?shù)據(jù)集中特定位置上的數(shù)值。例如，可以使用函數(shù)LARGE得到第一名、第二名或第三名的得分。2021/9/125.7.3 統(tǒng)計(jì)個(gè)數(shù)的函數(shù)中間值函數(shù)MEDIAN和眾數(shù)函數(shù)MODEMEDIAN(number1,number2,.)MODE(number1,number2,.)MEDIAN函數(shù)計(jì)算出給定數(shù)據(jù)的中值，MODE函數(shù)返回一組數(shù)中出現(xiàn)次數(shù)最多的數(shù)（眾數(shù)）。2021/9/125.7.3 統(tǒng)計(jì)個(gè)數(shù)的函數(shù)排名函數(shù)RANKRANK(number,ref,order)。RANK函數(shù)返

35、回一個(gè)數(shù)字在一組數(shù)中的排位，即位次。Number為需要找到排位的數(shù)字，Ref可以是一個(gè)數(shù)組或單元格區(qū)域，Ref 中的非數(shù)值型參數(shù)將被忽略。Order用于指明排位的方式，如果為 0或省略，則Rank 對(duì)數(shù)字的排位是基于 ref 為按照降序排列的列表；如果 order 不為零，則Rank對(duì)數(shù)字的排位是基于 ref 為按照升序排列的列表。 2021/9/12【例5.23】有學(xué)生成績表如圖5.24中A1:F10區(qū)域所示。計(jì)算各學(xué)生成績的名稱、百分比排位、第3名和倒數(shù)第3名總分、中間成績、出現(xiàn)次數(shù)最多的分?jǐn)?shù)及在38%總分位置的分?jǐn)?shù)大致是多少等數(shù)據(jù)。在G3中輸入公式：=RANK(F3,$F$3:$F$10

36、) 向下復(fù)制此公式可計(jì)算名次在H3中輸入公式：=PERCENTRANK($F$3:$F$10,F3,2) 向下復(fù)制此公式可計(jì)算名次=LARGE(F3:F10,3) =SMALL(F3:F10,3) =MEDIAN(F3:F10) =MODE(C3:E10) =PERCENTILE(F3:F10,0.38) 2021/9/125.7 統(tǒng)計(jì)函數(shù)【例5.24】某班期末成績表如圖所示?，F(xiàn)要統(tǒng)計(jì)每位同學(xué)的總分，各科目的應(yīng)考人數(shù)、缺考人數(shù)及各科平均成績 2021/9/125.8 數(shù)學(xué)和三角函數(shù)1、概述Excel提供了許多數(shù)學(xué)和三角函數(shù)，它們能夠完成大多數(shù)數(shù)學(xué)和三角運(yùn)算，這些函數(shù)可以在公式中直接引用，然后將

37、公式的計(jì)算結(jié)果返回到輸入公式的單元格中。2、常見的數(shù)學(xué)函數(shù)MOD 、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) 2021/9/125.8 數(shù)學(xué)和三角函數(shù)3、數(shù)學(xué)函數(shù)應(yīng)用案例【例5.25】用隨機(jī)函數(shù)產(chǎn)生大量的實(shí)驗(yàn)數(shù)據(jù)，計(jì)算排名。問題：有一張工資工作表，結(jié)構(gòu)如圖所示?，F(xiàn)以此表為例說明隨機(jī)函數(shù)和數(shù)組的結(jié)合使用。 2021/9/125.8 數(shù)學(xué)和三角函數(shù)產(chǎn)生日期（1）在B2單元格中

38、輸入?yún)⒓庸ぷ鞯淖钤鐣r(shí)間，即在B2單元格中輸入“1970/1/1”。（2）選中要產(chǎn)生日期的單元格區(qū)域，如B3:B8。（3）輸入公式“=B2+RAND()*1825”。（4）按Ctrl+Enter鍵。 （5）將B3:B8格式化為需要的日期格式。產(chǎn)生加班時(shí)間，計(jì)算加班工資（1）選中C2:C8單元格區(qū)域。（2）輸入公式“= INT(RAND()*600)”。（3）按Ctrl +Enter鍵。2021/9/125.8 數(shù)學(xué)和三角函數(shù)產(chǎn)生基本工資假設(shè)基本工資在8003 000這個(gè)范圍內(nèi)，其產(chǎn)生方法如下：（1）選擇D2:D8單元格區(qū)域。（2）輸入公式“=800+int(RAND()*2200)”。（3）按C

39、trl +Enter鍵。產(chǎn)生其它數(shù)據(jù)假設(shè)圖中的其他數(shù)據(jù)都在1 000以內(nèi)，其產(chǎn)生方法如下：（1）選擇E2:H8單元格區(qū)域。（2）輸入公式：“=int(RAND()*1000)”。（3）按Ctrl +Enter鍵 2021/9/125.8 數(shù)學(xué)和三角函數(shù)計(jì)算總收入和收入排名（1）在I2輸入公式：“=sum(D2:H2)”。向下復(fù)制此公式，（2）在J2中輸入張大海的排名公式：“=RANK(I2,$I$2:$I$8)”。向下復(fù)制此公式。上述操作的結(jié)果如下：2021/9/125.8 數(shù)學(xué)和三角函數(shù)3、數(shù)學(xué)函數(shù)應(yīng)用案例【例5.26】用舍入函數(shù)解決計(jì)算誤差 問題：下圖是一個(gè)工資數(shù)據(jù)表，B2:G3單元格區(qū)域

40、的數(shù)據(jù)是用數(shù)組公式“=RAND()*100”產(chǎn)生的。 將小數(shù)點(diǎn)后的有效位縮減為2位后的數(shù)據(jù)如第4行所示；將第4行數(shù)據(jù)乘以3的結(jié)果如6行所示，可以看出，E6：G6中的計(jì)算結(jié)果是錯(cuò)誤的。用round函數(shù)將第1行的數(shù)據(jù)精確到小數(shù)點(diǎn)后2位，其結(jié)果如第8行所示，再將此結(jié)果乘以3，最后結(jié)果如第10行所示。第10行的計(jì)算結(jié)果是正確的。2021/9/125.8 數(shù)學(xué)和三角函數(shù)2021/9/125.9日期及時(shí)間函數(shù) 1、Excel處理日期的方式Microsoft Excel 將日期存儲(chǔ)為序列號(hào)（稱為序列值），即一系列連續(xù)的數(shù)字編號(hào)，每一個(gè)數(shù)字編號(hào)代表一個(gè)日期。在默認(rèn)情況下，數(shù)字1代表1900 年 1 月1 日，

41、2代表1900年1月2日，39,448代表2008 年 1 月 1 日，因?yàn)閺?900 年 1 月 1 日到2008 年 1 月 1 日正好 39,448 天。Excel 將時(shí)間存儲(chǔ)為小數(shù)，因?yàn)闀r(shí)間被看作天的一部分。時(shí)間也被存為序列號(hào)，此序列號(hào)以秒為單位遞增。因?yàn)橐惶旃灿校?4*60*60=86400秒，所以1/86400代表的時(shí)間是：00:00:01，2/86400代表的時(shí)間是：00:00:02。日期和時(shí)間都是數(shù)值，因此它們也可以進(jìn)行各種運(yùn)算。如果要計(jì)算兩個(gè)日期之間的差值，可以用一個(gè)日期減去另一個(gè)日期。 2021/9/125.9日期及時(shí)間函數(shù)2DATE函數(shù)用法DATE(year, month

42、, day)功能DATE函數(shù)利用所給的參數(shù)，構(gòu)造一個(gè)日期序列數(shù) 例如DATE(2005,3,21)的結(jié)果是2005-3-212021/9/125.9日期及時(shí)間函數(shù)3YEAR，MONTH，DAY函數(shù)用法YEAR(serial_number)MONTH(serial_number)DAY(serial_number)其中的serial_number是一個(gè)日期或數(shù)字。功能YEAR函數(shù)返回某日期的年份。MONTH函數(shù)返回以系列數(shù)表示的日期中的月份。DAY函數(shù)返回以系列數(shù)表示的某日期的天數(shù)，用整數(shù)131表示。 2021/9/125.9日期及時(shí)間函數(shù)4TODAY、NOW函數(shù)用法TODAY( )NOW( )

43、功能TODAY函數(shù)返回系統(tǒng)的當(dāng)前日期。NOW函數(shù)計(jì)算當(dāng)前日期和時(shí)間。 2021/9/125.9日期及時(shí)間函數(shù)5WEEKDAY用法WEEKDAY(serial_number, return_type)其中：serial_number代表要查找的日期，或日期的系列數(shù)，以了解該日期為星期幾；return_type確定返回值類型的數(shù)字，。功能WEEKDAY計(jì)算給定的日期是星期幾 2021/9/125.9日期及時(shí)間函數(shù)6、NETWORKDAYS函數(shù)用法NETWORKDAYS(start_date, end_date, holidays) 其中start_date表示代表開始日期，end_date為終止日

44、期， holidays表示不在工作日歷中的一個(gè)或多個(gè)日期所構(gòu)成的可選區(qū)域，如元旦節(jié)、五一節(jié)、春節(jié)。 2021/9/125.9日期及時(shí)間函數(shù)案例【例.27】某公司將每個(gè)員工的加班時(shí)間記錄在Excel的工作表中，便于計(jì)算加班工資。加班工資按小時(shí)計(jì)算，若加班時(shí)間不足1小時(shí)，但超過半小時(shí)由按1計(jì)時(shí)計(jì)算，不足半小時(shí)則不算加班時(shí)間。平時(shí)的加班工資每小時(shí)10元，節(jié)假時(shí)則加倍為20元每小時(shí) 。假設(shè)員工的加班時(shí)間如圖的A1:C11所示，現(xiàn)要計(jì)算加班時(shí)長（D列），判斷加班時(shí)間是星期幾和節(jié)假日（E、F列），計(jì)算工齡（J列）、總加班時(shí)間（L列）、節(jié)假時(shí)加班時(shí)間（L列）、工作日加班（M列，本該休息，但加班了），以及加班

45、工資（N列）。 2021/9/125.9日期及時(shí)間函數(shù)用日期、時(shí)間函數(shù)計(jì)算下表D、E、F、J、K、L、M、N列相應(yīng)數(shù)據(jù)。2021/9/125.10 字符函數(shù)1、概述Excel提供了接近30個(gè)文本、字符方面的函數(shù)，用這些函數(shù)對(duì)工作表中的文本數(shù)據(jù)進(jìn)行查找、替換、取子串、數(shù)字與文本的轉(zhuǎn)換，以及數(shù)據(jù)的格式化操作等都有十分重要的作用 。2、常用文本類函數(shù)RIGHT(text,n)該函數(shù)從text文本的右邊取出n個(gè)字符。LEFT(text,n)該函數(shù)從text文本的左邊取出n個(gè)字符2021/9/125.10 字符函數(shù)MID(text,n,m)該函數(shù)從text文本的第n個(gè)字符起，取出m個(gè)字符。REPLACE

46、(text1,n,m,text2)該函數(shù)用text2替換text1文本的第n個(gè)位置開始的m個(gè)字符。SEARCH(text1,text2,n)該函數(shù)從text1的第n個(gè)字符位置開始查找text1中是否包含text2，若找就返回text2在text1中的起始位置。若沒有找到則返回錯(cuò)誤值“#value!”2021/9/125.10 字符函數(shù)TEXT(value,format_text)該函數(shù)指定的格式碼format_text將數(shù)值型數(shù)據(jù)Value格式化為文本。其中value可為數(shù)字、結(jié)果為數(shù)字的計(jì)算公式、包含數(shù)字的單元格引用。format_text可為任何數(shù)字格式（有關(guān)格式碼請(qǐng)參考第4章的相關(guān)內(nèi)容）。VALUE(text)將數(shù)字形式的文本轉(zhuǎn)換成數(shù)字。比如：VALUE(32)=32，VALUE(09)=9 2021/9/125.10 字符函數(shù)案例【例5.28】文本函數(shù)應(yīng)用舉例 某銀行要建立下圖所示的用戶資料表。其中的出生日期從身份證號(hào)碼中提??；如果是男性，其尊稱就是“X先生”，如果是女性，其尊稱就是“X女士”，X是用戶的姓氏；存款帳號(hào)是出生日期（去掉出生年的前兩位數(shù)字）；存款密碼是身份證號(hào)碼的最后6位數(shù)