Excel公式精講與綜合應(yīng)用魏凱課件

第三節(jié)EXCEL公式精講主講人：魏凱第三節(jié)EXCEL公式精講主講人：魏凱本節(jié)重點：1.Excel公式的基本操作2.Excel公式的高級應(yīng)用

本節(jié)難點：Excel公式綜合應(yīng)用

本節(jié)重點：本節(jié)難點：一、Excel公式基本內(nèi)容1.Excel基本概念

由各種運算符構(gòu)成的運算式。Excel函數(shù)也是公式。

注：公式和函數(shù)無法分割，函數(shù)可以作為公式的基本運算單位，公式也可以作為函數(shù)的參數(shù)。

2.Excel公式的構(gòu)成

=數(shù)學(xué)公式。

例：=2+3

注意：

“=”號的意義不表示等于，只表示等號右邊的字符串為數(shù)學(xué)公式

一、Excel公式基本內(nèi)容1.Excel基本概念一、Excel公式基本內(nèi)容3.Excel公式元素

運算符：將多個元素連接起來的符號。包括引用運算符、算數(shù)運算符、文本運算符、文本運算符和比較運算符。

常數(shù)：或字符串：指值永遠不變的數(shù)據(jù)。字符串是用雙引號括起來的文本。如：200，“培訓(xùn)”數(shù)組：單元格引用：用單元格地址或名稱代表單元格中的數(shù)據(jù)進行計算。如：=A1+B1工作表函數(shù)及其參數(shù)。如：sum=（A1：A10）一、Excel公式基本內(nèi)容3.Excel公式元素一、Excel公式基本內(nèi)容4.Excel運算符

引用運算符：用于單元格區(qū)域合并計算。有冒號（:）、逗號（，）和空格。

（1）冒號是區(qū)域運算符。對引用兩個單元格之間所有單元格進行引用。例如：SUM(A1:A10)SUM(A:A)SUM(5:5)

(2)逗號是聯(lián)合運算符。用于將多個引用合并。例如：SUM（A1:A6，A5:D5）（3）空格是交叉運算符。用于對兩個單元格區(qū)域的交叉單元格的引用。例如：=（B5:C5C5:D5）*100一、Excel公式基本內(nèi)容4.Excel運算符一、Excel公式基本內(nèi)容4.Excel運算符

算數(shù)運算符：用于完成基本的算數(shù)運算。

運算優(yōu)先級：負號(-)百分數(shù)（%）冪（^）乘（*）除（/）加（+）減（—）一、Excel公式基本內(nèi)容4.Excel運算符一、Excel公式基本內(nèi)容4.Excel運算符

文本運算符：用于兩個或多個值連接產(chǎn)生一個連續(xù)的文本值。運算符為（&）。

例如：=A1&A2&A3一、Excel公式基本內(nèi)容4.Excel運算符一、Excel公式基本內(nèi)容4.Excel運算符

比較運算符：用于比較兩個值，并返回邏輯值TRUE（真）或FALSE（假）。

運算符有：等于（=）小于（<）小于等于（<=）大于（>）大于等于（>=）不等于（<>）例如：:=2<3(結(jié)果TRUE)、2>=3(結(jié)果FALSE)一、Excel公式基本內(nèi)容4.Excel運算符

5.公式輸入方法：

（1）選定要輸入公式的單元格

（2）輸入“=”號

（3）輸入Excel表達式

（4）按Enter鍵

注意：采用單元格引用方式，可以快速復(fù)制公式到不同目標(biāo)單元格，提高書寫速度一、Excel公式基本內(nèi)容一、Excel公式基本內(nèi)容1.Excel公式中的單元格引用

（1）引用定義:在進行數(shù)據(jù)計算式，用戶既可以輸入數(shù)值，也可以輸入數(shù)值所在的單元格地址和名稱，這就是引用。（2）相對地址引用:當(dāng)某個單元格的公式被復(fù)制到另一個單元格時，原單元格內(nèi)公式中的地址在新的單元格中就要發(fā)生變化，但其引用的單元格地址間的相對位置間距保持不變。

（3）絕對地址引用:指在復(fù)制公式時，絕對引用的單元格地址的行號和列號前各加一個“$”符號。其特點是將此單元格復(fù)制到新的單元格時，公式引用的單元格地址保持不變。(4)混合引用：指列絕對行相對或列相對行絕對。公式所在的單元格的位置發(fā)生改變時，相對引用改變，絕對引用不改變。（5）三維引用：在一個工作薄中，從不同的表引用單元格地址，或從不同的工作薄中引用單元格地址。（6）R1C1表示法：R表示（row）C表示（column）例如：A2可以表示R2C1二、單元格引用1.Excel公式中的單元格引用二、單元格引用三、Excel函數(shù)1.Excel函數(shù)基本分類

在日常工作中，有些計算內(nèi)容只能借助于函數(shù)來完成。分為9大類共300多個函數(shù)，包括統(tǒng)計函數(shù)、數(shù)學(xué)與三角函數(shù)、邏輯函數(shù)、文本函數(shù)、日期與時間函數(shù)、數(shù)據(jù)庫函數(shù)、財務(wù)函數(shù)、查找與引用函數(shù)和信息函數(shù)。

函數(shù)的一般格式

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

例如：sum（20,30,40）表示對三個數(shù)值求和sum（A5，A7，A8）表示對三個單元格數(shù)據(jù)求和

三、Excel函數(shù)1.Excel函數(shù)基本分類2.Excel函數(shù)輸入方法

（1）直接輸入函數(shù)①選定要輸入函數(shù)的單元格②輸入：=函數(shù)名（參數(shù)1，參數(shù)2，……,參數(shù)n）③單鍵Enter鍵

（2）使用插入函數(shù)對話框輸入函數(shù)①選定要輸入函數(shù)單元格②單擊“插入”菜單選擇“函數(shù)”命令，彈出“插入函數(shù)”對話框③在對話框中單擊“或選擇類型”，在“選擇函數(shù)”列表框中選擇所需函數(shù)，單擊“確

定”按鈕，彈出“函數(shù)參數(shù)”對話框④在“函數(shù)參數(shù)”對話框中，分別輸入Number1，Number2等要求的參數(shù)⑤單擊“確定”按鈕

三、Excel函數(shù)2.Excel函數(shù)輸入方法三、Excel函數(shù)3.Excel常用函數(shù)實例

（1）求和函數(shù)sum（）

（2）平均值函數(shù)average（）

（3）最大值函數(shù)max（）

（4）最小值函數(shù)min（）

（5）if函數(shù)if（條件式，條件為真時的參數(shù)，條件為假時的參數(shù)）

三、Excel函數(shù)3.Excel常用函數(shù)實例三、Excel函數(shù)4.公式高級應(yīng)用

（1）公式中使用嵌套函數(shù)

例如：=SUM（A1,B1,AVERAGE(C1:E1)）

（2）公式中使用簡單條件表達式

例如：=A1<>(C1-200)

例如：=IF(A1>100，1，0.9)*A1

（3）公式中使用復(fù)雜條件表達式例如：=IF（OR（A1=“電視”,A1=“冰箱”）,0.9,1）*B1

=IF（（A1=“電視”）+（A1=“冰箱”），0.9,1）*B1

例如:=IF（AND（A1>100,A1<1000）,0.9,1）*B1

=IF（（A1>100）*（A1<1000），0.9,1）*B1

三、Excel函數(shù)4.公式高級應(yīng)用三、Excel函數(shù)1.公式基本應(yīng)用訓(xùn)練2.函數(shù)基本應(yīng)用訓(xùn)練3.公式綜合案例訓(xùn)練四、公式綜合訓(xùn)練

1.公式基本應(yīng)用訓(xùn)練四、公式綜合訓(xùn)練第三節(jié)EXCEL公式精講主講人：魏凱第三節(jié)EXCEL公式精講主講人：魏凱本節(jié)重點：1.Excel公式的基本操作2.Excel公式的高級應(yīng)用

本節(jié)難點：Excel公式綜合應(yīng)用

本節(jié)重點：本節(jié)難點：一、Excel公式基本內(nèi)容1.Excel基本概念

由各種運算符構(gòu)成的運算式。Excel函數(shù)也是公式。

注：公式和函數(shù)無法分割，函數(shù)可以作為公式的基本運算單位，公式也可以作為函數(shù)的參數(shù)。

2.Excel公式的構(gòu)成

=數(shù)學(xué)公式。

例：=2+3

注意：

“=”號的意義不表示等于，只表示等號右邊的字符串為數(shù)學(xué)公式

一、Excel公式基本內(nèi)容1.Excel基本概念一、Excel公式基本內(nèi)容3.Excel公式元素

運算符：將多個元素連接起來的符號。包括引用運算符、算數(shù)運算符、文本運算符、文本運算符和比較運算符。

常數(shù)：或字符串：指值永遠不變的數(shù)據(jù)。字符串是用雙引號括起來的文本。如：200，“培訓(xùn)”數(shù)組：單元格引用：用單元格地址或名稱代表單元格中的數(shù)據(jù)進行計算。如：=A1+B1工作表函數(shù)及其參數(shù)。如：sum=（A1：A10）一、Excel公式基本內(nèi)容3.Excel公式元素一、Excel公式基本內(nèi)容4.Excel運算符

引用運算符：用于單元格區(qū)域合并計算。有冒號（:）、逗號（，）和空格。

（1）冒號是區(qū)域運算符。對引用兩個單元格之間所有單元格進行引用。例如：SUM(A1:A10)SUM(A:A)SUM(5:5)

(2)逗號是聯(lián)合運算符。用于將多個引用合并。例如：SUM（A1:A6，A5:D5）（3）空格是交叉運算符。用于對兩個單元格區(qū)域的交叉單元格的引用。例如：=（B5:C5C5:D5）*100一、Excel公式基本內(nèi)容4.Excel運算符一、Excel公式基本內(nèi)容4.Excel運算符

算數(shù)運算符：用于完成基本的算數(shù)運算。

運算優(yōu)先級：負號(-)百分數(shù)（%）冪（^）乘（*）除（/）加（+）減（—）一、Excel公式基本內(nèi)容4.Excel運算符一、Excel公式基本內(nèi)容4.Excel運算符

文本運算符：用于兩個或多個值連接產(chǎn)生一個連續(xù)的文本值。運算符為（&）。

例如：=A1&A2&A3一、Excel公式基本內(nèi)容4.Excel運算符一、Excel公式基本內(nèi)容4.Excel運算符

比較運算符：用于比較兩個值，并返回邏輯值TRUE（真）或FALSE（假）。

運算符有：等于（=）小于（<）小于等于（<=）大于（>）大于等于（>=）不等于（<>）例如：:=2<3(結(jié)果TRUE)、2>=3(結(jié)果FALSE)一、Excel公式基本內(nèi)容4.Excel運算符

5.公式輸入方法：

（1）選定要輸入公式的單元格

（2）輸入“=”號

（3）輸入Excel表達式

（4）按Enter鍵

注意：采用單元格引用方式，可以快速復(fù)制公式到不同目標(biāo)單元格，提高書寫速度一、Excel公式基本內(nèi)容一、Excel公式基本內(nèi)容1.Excel公式中的單元格引用

（1）引用定義:在進行數(shù)據(jù)計算式，用戶既可以輸入數(shù)值，也可以輸入數(shù)值所在的單元格地址和名稱，這就是引用。（2）相對地址引用:當(dāng)某個單元格的公式被復(fù)制到另一個單元格時，原單元格內(nèi)公式中的地址在新的單元格中就要發(fā)生變化，但其引用的單元格地址間的相對位置間距保持不變。

（3）絕對地址引用:指在復(fù)制公式時，絕對引用的單元格地址的行號和列號前各加一個“$”符號。其特點是將此單元格復(fù)制到新的單元格時，公式引用的單元格地址保持不變。(4)混合引用：指列絕對行相對或列相對行絕對。公式所在的單元格的位置發(fā)生改變時，相對引用改變，絕對引用不改變。（5）三維引用：在一個工作薄中，從不同的表引用單元格地址，或從不同的工作薄中引用單元格地址。（6）R1C1表示法：R表示（row）C表示（column）例如：A2可以表示R2C1二、單元格引用1.Excel公式中的單元格引用二、單元格引用三、Excel函數(shù)1.Excel函數(shù)基本分類

在日常工作中，有些計算內(nèi)容只能借助于函數(shù)來完成。分為9大類共300多個函數(shù)，包括統(tǒng)計函數(shù)、數(shù)學(xué)與三角函數(shù)、邏輯函數(shù)、文本函數(shù)、日期與時間函數(shù)、數(shù)據(jù)庫函數(shù)、財務(wù)函數(shù)、查找與引用函數(shù)和信息函數(shù)。

函數(shù)的一般格式

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

例如：sum（20,30,40）表示對三個數(shù)值求和sum（A5，A7，A8）表示對三個單元格數(shù)據(jù)求和

三、Excel函數(shù)1.Excel函數(shù)基本分類2.Excel函數(shù)輸入方法

（1）直接輸入函數(shù)①選定要輸入函數(shù)的單元格②輸入：=函數(shù)名（參數(shù)1，參數(shù)2，……,參數(shù)n）③單鍵Enter鍵

（2）使用插入函數(shù)對話框輸入函數(shù)①選定要輸入函數(shù)單元格②單擊“插入”菜單選擇“函數(shù)”命令，彈出“插入函數(shù)”對話框③在對話框中單擊“或選擇類型”，在“選擇函數(shù)”列表框中選擇所需函數(shù)，單擊“確

定”按鈕，彈出“函數(shù)參數(shù)”對話框④在“函數(shù)參數(shù)”對話框中，分別輸入Number1，Number2等要求的參數(shù)⑤單擊“確定”按鈕

三、Excel函數(shù)2.Excel函數(shù)輸入方法三、Excel函數(shù)3.Excel常用函數(shù)實例

（1）求和函數(shù)sum（）

（2）平均值函