excel使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算課件

第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算Excel應(yīng)用基礎(chǔ)第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算Excel應(yīng)用基礎(chǔ)2023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算2本章主要內(nèi)容：

如何創(chuàng)建公式在公式中引用單元格創(chuàng)建數(shù)組公式使用自動求和函數(shù)2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算22023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算313.1

創(chuàng)建公式

在Excel中，公式是在工作表中對數(shù)據(jù)進(jìn)行分析的等式。公式中可以包含的元素有運(yùn)算符、函數(shù)、常數(shù)、單元格引用和單元格區(qū)域引用。需要注意的是，公式是以等號（＝）開始的。2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算32023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算41、公式語法順序運(yùn)算符說明順序運(yùn)算符說明1:引用運(yùn)算符6^乘冪2,7*,/乘號和除號3(空格)8+,-加和減4(負(fù)號)9&連接字符串文本5%百分比10=<><=>=<>比較運(yùn)算符2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算42023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算52、運(yùn)算符

在Excel中，運(yùn)算符可以分成四種類型：算術(shù)運(yùn)算符比較運(yùn)算符文本運(yùn)算符引用運(yùn)算符2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算52023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算613.2

單元格引用

在Excel的公式中可以直接引用單元格。單元格的引用分為絕對引用和相對引用。相對引用直接用單元格的列號與行號來標(biāo)識，公式復(fù)制或填充時(shí)行號與列號會自動改變。絕對引用是指所引用的單元格不會因公式的填充而改變，引用的方式為在行標(biāo)記或列標(biāo)記前加上絕對引用符號“$”。2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算62023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算7

在同一工作簿中，不同工作表中的單元格可以相互引用，即可以引用其他工作表中的單元格，稱這種引用為單元格的三維引用。單元格三維引用的表示方法為“工作表名!(空格)單元格名”，如“Sheet4!B8”，引用的為工作表Sheet4中的單元格B8。2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算72023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算813.3

創(chuàng)建數(shù)組公式

數(shù)組公式作用于兩組或多組被稱為數(shù)組參數(shù)的數(shù)組。每組數(shù)組參數(shù)必須具有相同數(shù)目的行和列。如果需要返回多個(gè)結(jié)果，必須在多個(gè)單元格輸入公式。數(shù)組公式建立后，用戶不能編輯數(shù)組單元格中的某一個(gè)單元格。2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算82023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算9

選定用來存放計(jì)算結(jié)果的單元格區(qū)域

輸入等號，開始建立公式

完成公式的輸入后按Shift+Ctrl+Enter組合鍵，在開始選定的單元格數(shù)組中顯示出數(shù)組公式的計(jì)算值2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算92023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算102022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算12023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算1113.4

在公式中調(diào)用函數(shù)在公式中使用函數(shù)單擊需要輸入公式的單元格，輸入等號在編輯欄上，將光標(biāo)移到需要插入函數(shù)的位置單擊“插入”“函數(shù)”命令，或單擊“常用”工具欄的“粘貼函數(shù)”按鈕，打開“粘貼函數(shù)”對話框2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算12023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算12

在“函數(shù)分類”中選擇需要的函數(shù)類型，在“函數(shù)名”列表框中選擇函數(shù)單擊“確定”按鈕，屏幕上將出現(xiàn)“公式選項(xiàng)板”在“公式選項(xiàng)板”中輸入函數(shù)參數(shù)或引用單元格單擊“確定”按鈕，可在編輯欄中繼續(xù)輸入公式壓縮對話框按鈕2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算12023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算13

函數(shù)由函數(shù)名和括號內(nèi)參數(shù)組成，其中的參數(shù)可以是一個(gè)或多個(gè)常數(shù)、單元格引用、表達(dá)式，各個(gè)參數(shù)之間用“，”分隔。1、求和函數(shù)SUM

格式：SUM（參數(shù)1，參數(shù)2……）作用：對參數(shù)1，參數(shù)2……進(jìn)行求和。例1：SUM（100，43，56）=199

例2：SUM（C5:C7）=1976補(bǔ)充：常用函數(shù)2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算12023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算14

例1：計(jì)算總評分2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算12023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算152022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算12023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算162022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算12023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算172、求平均函數(shù)AVERAGE

格式：AVERAGE（參數(shù)1，參數(shù)2……）

作用：對參數(shù)1、參數(shù)2……進(jìn)行求平均。

例1：AVERAGE（10，20，30）=20

例2：計(jì)算各學(xué)生的平均成績2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算12023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算183、統(tǒng)計(jì)函數(shù)COUNT

格式：COUNT（參數(shù)1，參數(shù)2……）作用：統(tǒng)計(jì)參數(shù)或區(qū)域內(nèi)數(shù)值的個(gè)數(shù)。例：COUNT（10，A，20，30，F(xiàn)）結(jié)果為3，表明有3個(gè)數(shù)值。2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算12023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算194、條件統(tǒng)計(jì)函數(shù)COUNTIF

格式：COUNTIF(參數(shù)1，參數(shù)2…“條件”)

作用：統(tǒng)計(jì)各參數(shù)式區(qū)域內(nèi)滿足條件的個(gè)數(shù)。

例：統(tǒng)計(jì)學(xué)生成績表中各科及格人數(shù)和及格率。2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算12023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算205、條件求和函數(shù)SUMIF

格式：SUMIF（條件區(qū)域，“條件”，求和區(qū)域）

作用：對在條件區(qū)域內(nèi)滿足條件的記錄的求和區(qū)域進(jìn)行累加求和。

例：如在一單元格內(nèi)輸入公式“=SUMIF（B4:B7,">=60",B4:B7）”，結(jié)果為對B4：B7區(qū)域內(nèi)大于等于60分的單元格進(jìn)行累加求和，結(jié)果為78+67+80=225。2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算22023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算21又如按部門對同一部門職工的實(shí)發(fā)工資進(jìn)行求和2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算22023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算226、邏輯與函數(shù)AND

格式：AND（條件1，條件2）作用：當(dāng)條件1和條件2都成立時(shí)才為TRUE，否則為FALSE

例如：AND(3>6,4<5)結(jié)果為FALSE7、邏輯非函數(shù)NOT

格式：NOT（條件）作用：當(dāng)條件為TRUE時(shí)，結(jié)果為FALSE，當(dāng)條件為FALSE時(shí)，結(jié)果為TRUE2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算22023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算238、邏輯或函數(shù)OR

格式：OR（條件1，條件2）作用：當(dāng)條件1或條件2兩者之一為TRUE時(shí)，結(jié)果為TRUE。當(dāng)且僅當(dāng)兩個(gè)條件都為FALSE時(shí)，結(jié)果才為FALSE。9、條件函數(shù)IF

格式：IF（條件，結(jié)果1，結(jié)果2）作用：當(dāng)條件為TRUE時(shí)，得到結(jié)果1，否則得到結(jié)果2。2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算22023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算24

例：參考學(xué)生成績表，在平均分后有一個(gè)“評語”字段，當(dāng)成績>=70分評定為“合格”，否則評定為“不合格”。第一步：在第一個(gè)記錄的評語單元格G4寫上公式“=IF（F4>=70,"合格","不合格"）”。2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算22023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算252022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算22023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算26

第三步：通過填充，得到其他同學(xué)的評語。2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算22023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算2713.5

使用自動求和函數(shù)

在Excel的常用工具欄中提供有自動求和計(jì)算的命令按鈕：“∑”。2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算22023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算28

例：參考工資表，添加小計(jì)一欄為各項(xiàng)工資的縱向累計(jì)值，計(jì)算工資表中的各項(xiàng)小計(jì)。選定需要求和計(jì)算的數(shù)值范圍，C5：F9；按下常用工具欄上的求和按鈕“∑”，各字段的求和結(jié)果立即出現(xiàn)。2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算22023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算292022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算2第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算Excel應(yīng)用基礎(chǔ)第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算Excel應(yīng)用基礎(chǔ)2023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算31本章主要內(nèi)容：

如何創(chuàng)建公式在公式中引用單元格創(chuàng)建數(shù)組公式使用自動求和函數(shù)2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算22023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算3213.1

創(chuàng)建公式

在Excel中，公式是在工作表中對數(shù)據(jù)進(jìn)行分析的等式。公式中可以包含的元素有運(yùn)算符、函數(shù)、常數(shù)、單元格引用和單元格區(qū)域引用。需要注意的是，公式是以等號（＝）開始的。2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算32023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算331、公式語法順序運(yùn)算符說明順序運(yùn)算符說明1:引用運(yùn)算符6^乘冪2,7*,/乘號和除號3(空格)8+,-加和減4(負(fù)號)9&連接字符串文本5%百分比10=<><=>=<>比較運(yùn)算符2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算42023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算342、運(yùn)算符

在Excel中，運(yùn)算符可以分成四種類型：算術(shù)運(yùn)算符比較運(yùn)算符文本運(yùn)算符引用運(yùn)算符2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算52023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算3513.2

單元格引用

在Excel的公式中可以直接引用單元格。單元格的引用分為絕對引用和相對引用。相對引用直接用單元格的列號與行號來標(biāo)識，公式復(fù)制或填充時(shí)行號與列號會自動改變。絕對引用是指所引用的單元格不會因公式的填充而改變，引用的方式為在行標(biāo)記或列標(biāo)記前加上絕對引用符號“$”。2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算62023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算36

在同一工作簿中，不同工作表中的單元格可以相互引用，即可以引用其他工作表中的單元格，稱這種引用為單元格的三維引用。單元格三維引用的表示方法為“工作表名!(空格)單元格名”，如“Sheet4!B8”，引用的為工作表Sheet4中的單元格B8。2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算72023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算3713.3

創(chuàng)建數(shù)組公式

數(shù)組公式作用于兩組或多組被稱為數(shù)組參數(shù)的數(shù)組。每組數(shù)組參數(shù)必須具有相同數(shù)目的行和列。如果需要返回多個(gè)結(jié)果，必須在多個(gè)單元格輸入公式。數(shù)組公式建立后，用戶不能編輯數(shù)組單元格中的某一個(gè)單元格。2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算82023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算38

選定用來存放計(jì)算結(jié)果的單元格區(qū)域

輸入等號，開始建立公式

完成公式的輸入后按Shift+Ctrl+Enter組合鍵，在開始選定的單元格數(shù)組中顯示出數(shù)組公式的計(jì)算值2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算92023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算392022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算12023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算4013.4

在公式中調(diào)用函數(shù)在公式中使用函數(shù)單擊需要輸入公式的單元格，輸入等號在編輯欄上，將光標(biāo)移到需要插入函數(shù)的位置單擊“插入”“函數(shù)”命令，或單擊“常用”工具欄的“粘貼函數(shù)”按鈕，打開“粘貼函數(shù)”對話框2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算12023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算41

在“函數(shù)分類”中選擇需要的函數(shù)類型，在“函數(shù)名”列表框中選擇函數(shù)單擊“確定”按鈕，屏幕上將出現(xiàn)“公式選項(xiàng)板”在“公式選項(xiàng)板”中輸入函數(shù)參數(shù)或引用單元格單擊“確定”按鈕，可在編輯欄中繼續(xù)輸入公式壓縮對話框按鈕2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算12023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算42

函數(shù)由函數(shù)名和括號內(nèi)參數(shù)組成，其中的參數(shù)可以是一個(gè)或多個(gè)常數(shù)、單元格引用、表達(dá)式，各個(gè)參數(shù)之間用“，”分隔。1、求和函數(shù)SUM

格式：SUM（參數(shù)1，參數(shù)2……）作用：對參數(shù)1，參數(shù)2……進(jìn)行求和。例1：SUM（100，43，56）=199

例2：SUM（C5:C7）=1976補(bǔ)充：常用函數(shù)2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算12023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算43

例1：計(jì)算總評分2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算12023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算442022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算12023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算452022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算12023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算462、求平均函數(shù)AVERAGE

格式：AVERAGE（參數(shù)1，參數(shù)2……）

作用：對參數(shù)1、參數(shù)2……進(jìn)行求平均。

例1：AVERAGE（10，20，30）=20

例2：計(jì)算各學(xué)生的平均成績2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算12023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算473、統(tǒng)計(jì)函數(shù)COUNT

格式：COUNT（參數(shù)1，參數(shù)2……）作用：統(tǒng)計(jì)參數(shù)或區(qū)域內(nèi)數(shù)值的個(gè)數(shù)。例：COUNT（10，A，20，30，F(xiàn)）結(jié)果為3，表明有3個(gè)數(shù)值。2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算12023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算484、條件統(tǒng)計(jì)函數(shù)COUNTIF

格式：COUNTIF(參數(shù)1，參數(shù)2…“條件”)

作用：統(tǒng)計(jì)各參數(shù)式區(qū)域內(nèi)滿足條件的個(gè)數(shù)。

例：統(tǒng)計(jì)學(xué)生成績表中各科及格人數(shù)和及格率。2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算12023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算495、條件求和函數(shù)SUMIF

格式：SUMIF（條件區(qū)域，“條件”，求和區(qū)域）

作用：對在條件區(qū)域內(nèi)滿足條件的記錄的求和區(qū)域進(jìn)行累加求和。

例：如在一單元格內(nèi)輸入公式“=SUMIF（B4:B7,">=60",B4:B7）”，結(jié)果為對B4：B7區(qū)域內(nèi)大于等于60分的單元格進(jìn)行累加求和，結(jié)果為78+67+80=225。2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算22023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算50又如按部門對同一部門職工的實(shí)發(fā)工資進(jìn)行求和2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算22023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算516、邏輯與函數(shù)AND

格式：AND（條件1，條件2）作用：當(dāng)條件1和條件2都成立時(shí)才為TRUE，否則為FALSE

例如：AND(3>6,4<5)結(jié)果為FALSE7、邏輯非函數(shù)NOT

格式：NOT（條件）作用：當(dāng)條件為TRUE時(shí)，結(jié)果為FALSE，當(dāng)條件為FALSE時(shí)，結(jié)果為TRUE2022/12/26第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算22023/1/3第十三章使用公式和函數(shù)進(jìn)行數(shù)據(jù)計(jì)算528、邏輯或函數(shù)OR

格式：OR（條件1，條件