Excel公式精讲与综合应用魏凯课件

第三节EXCEL公式精讲主讲人：魏凯第三节EXCEL公式精讲主讲人：魏凯本节重点：1.Excel公式的基本操作2.Excel公式的高级应用

本节难点：Excel公式综合应用

本节重点：本节难点：一、Excel公式基本内容1.Excel基本概念

由各种运算符构成的运算式。Excel函数也是公式。

注：公式和函数无法分割，函数可以作为公式的基本运算单位，公式也可以作为函数的参数。

2.Excel公式的构成

=数学公式。

例：=2+3

注意：

“=”号的意义不表示等于，只表示等号右边的字符串为数学公式

一、Excel公式基本内容1.Excel基本概念一、Excel公式基本内容3.Excel公式元素

运算符：将多个元素连接起来的符号。包括引用运算符、算数运算符、文本运算符、文本运算符和比较运算符。

常数：或字符串：指值永远不变的数据。字符串是用双引号括起来的文本。如：200，“培训”数组：单元格引用：用单元格地址或名称代表单元格中的数据进行计算。如：=A1+B1工作表函数及其参数。如：sum=（A1：A10）一、Excel公式基本内容3.Excel公式元素一、Excel公式基本内容4.Excel运算符

引用运算符：用于单元格区域合并计算。有冒号（:）、逗号（，）和空格。

（1）冒号是区域运算符。对引用两个单元格之间所有单元格进行引用。例如：SUM(A1:A10)SUM(A:A)SUM(5:5)

(2)逗号是联合运算符。用于将多个引用合并。例如：SUM（A1:A6，A5:D5）（3）空格是交叉运算符。用于对两个单元格区域的交叉单元格的引用。例如：=（B5:C5C5:D5）*100一、Excel公式基本内容4.Excel运算符一、Excel公式基本内容4.Excel运算符

算数运算符：用于完成基本的算数运算。

运算优先级：负号(-)百分数（%）幂（^）乘（*）除（/）加（+）减（—）一、Excel公式基本内容4.Excel运算符一、Excel公式基本内容4.Excel运算符

文本运算符：用于两个或多个值连接产生一个连续的文本值。运算符为（&）。

例如：=A1&A2&A3一、Excel公式基本内容4.Excel运算符一、Excel公式基本内容4.Excel运算符

比较运算符：用于比较两个值，并返回逻辑值TRUE（真）或FALSE（假）。

运算符有：等于（=）小于（<）小于等于（<=）大于（>）大于等于（>=）不等于（<>）例如：:=2<3(结果TRUE)、2>=3(结果FALSE)一、Excel公式基本内容4.Excel运算符

5.公式输入方法：

（1）选定要输入公式的单元格

（2）输入“=”号

（3）输入Excel表达式

（4）按Enter键

注意：采用单元格引用方式，可以快速复制公式到不同目标单元格，提高书写速度一、Excel公式基本内容一、Excel公式基本内容1.Excel公式中的单元格引用

（1）引用定义:在进行数据计算式，用户既可以输入数值，也可以输入数值所在的单元格地址和名称，这就是引用。（2）相对地址引用:当某个单元格的公式被复制到另一个单元格时，原单元格内公式中的地址在新的单元格中就要发生变化，但其引用的单元格地址间的相对位置间距保持不变。

（3）绝对地址引用:指在复制公式时，绝对引用的单元格地址的行号和列号前各加一个“$”符号。其特点是将此单元格复制到新的单元格时，公式引用的单元格地址保持不变。(4)混合引用：指列绝对行相对或列相对行绝对。公式所在的单元格的位置发生改变时，相对引用改变，绝对引用不改变。（5）三维引用：在一个工作薄中，从不同的表引用单元格地址，或从不同的工作薄中引用单元格地址。（6）R1C1表示法：R表示（row）C表示（column）例如：A2可以表示R2C1二、单元格引用1.Excel公式中的单元格引用二、单元格引用三、Excel函数1.Excel函数基本分类

在日常工作中，有些计算内容只能借助于函数来完成。分为9大类共300多个函数，包括统计函数、数学与三角函数、逻辑函数、文本函数、日期与时间函数、数据库函数、财务函数、查找与引用函数和信息函数。

函数的一般格式

函数名（参数1，参数2，……，参数n）

例如：sum（20,30,40）表示对三个数值求和sum（A5，A7，A8）表示对三个单元格数据求和

三、Excel函数1.Excel函数基本分类2.Excel函数输入方法

（1）直接输入函数①选定要输入函数的单元格②输入：=函数名（参数1，参数2，……,参数n）③单键Enter键

（2）使用插入函数对话框输入函数①选定要输入函数单元格②单击“插入”菜单选择“函数”命令，弹出“插入函数”对话框③在对话框中单击“或选择类型”，在“选择函数”列表框中选择所需函数，单击“确

定”按钮，弹出“函数參数”对话框④在“函数参数”对话框中，分别输入Number1，Number2等要求的参数⑤单击“确定”按钮

三、Excel函数2.Excel函数输入方法三、Excel函数3.Excel常用函数实例

（1）求和函数sum（）

（2）平均值函数average（）

（3）最大值函数max（）

（4）最小值函数min（）

（5）if函数if（条件式，条件为真时的参数，条件为假时的参数）

三、Excel函数3.Excel常用函数实例三、Excel函数4.公式高级应用

（1）公式中使用嵌套函数

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

（2）公式中使用简单条件表达式

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

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

（3）公式中使用复杂条件表达式例如：=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函数4.公式高级应用三、Excel函数1.公式基本应用训练2.函数基本应用训练3.公式综合案例训练四、公式综合训练

1.公式基本应用训练四、公式综合训练第三节EXCEL公式精讲主讲人：魏凯第三节EXCEL公式精讲主讲人：魏凯本节重点：1.Excel公式的基本操作2.Excel公式的高级应用

本节难点：Excel公式综合应用

本节重点：本节难点：一、Excel公式基本内容1.Excel基本概念

由各种运算符构成的运算式。Excel函数也是公式。

注：公式和函数无法分割，函数可以作为公式的基本运算单位，公式也可以作为函数的参数。

2.Excel公式的构成

=数学公式。

例：=2+3

注意：

“=”号的意义不表示等于，只表示等号右边的字符串为数学公式

一、Excel公式基本内容1.Excel基本概念一、Excel公式基本内容3.Excel公式元素

运算符：将多个元素连接起来的符号。包括引用运算符、算数运算符、文本运算符、文本运算符和比较运算符。

常数：或字符串：指值永远不变的数据。字符串是用双引号括起来的文本。如：200，“培训”数组：单元格引用：用单元格地址或名称代表单元格中的数据进行计算。如：=A1+B1工作表函数及其参数。如：sum=（A1：A10）一、Excel公式基本内容3.Excel公式元素一、Excel公式基本内容4.Excel运算符

引用运算符：用于单元格区域合并计算。有冒号（:）、逗号（，）和空格。

（1）冒号是区域运算符。对引用两个单元格之间所有单元格进行引用。例如：SUM(A1:A10)SUM(A:A)SUM(5:5)

(2)逗号是联合运算符。用于将多个引用合并。例如：SUM（A1:A6，A5:D5）（3）空格是交叉运算符。用于对两个单元格区域的交叉单元格的引用。例如：=（B5:C5C5:D5）*100一、Excel公式基本内容4.Excel运算符一、Excel公式基本内容4.Excel运算符

算数运算符：用于完成基本的算数运算。

运算优先级：负号(-)百分数（%）幂（^）乘（*）除（/）加（+）减（—）一、Excel公式基本内容4.Excel运算符一、Excel公式基本内容4.Excel运算符

文本运算符：用于两个或多个值连接产生一个连续的文本值。运算符为（&）。

例如：=A1&A2&A3一、Excel公式基本内容4.Excel运算符一、Excel公式基本内容4.Excel运算符

比较运算符：用于比较两个值，并返回逻辑值TRUE（真）或FALSE（假）。

运算符有：等于（=）小于（<）小于等于（<=）大于（>）大于等于（>=）不等于（<>）例如：:=2<3(结果TRUE)、2>=3(结果FALSE)一、Excel公式基本内容4.Excel运算符

5.公式输入方法：

（1）选定要输入公式的单元格

（2）输入“=”号

（3）输入Excel表达式

（4）按Enter键

注意：采用单元格引用方式，可以快速复制公式到不同目标单元格，提高书写速度一、Excel公式基本内容一、Excel公式基本内容1.Excel公式中的单元格引用

（1）引用定义:在进行数据计算式，用户既可以输入数值，也可以输入数值所在的单元格地址和名称，这就是引用。（2）相对地址引用:当某个单元格的公式被复制到另一个单元格时，原单元格内公式中的地址在新的单元格中就要发生变化，但其引用的单元格地址间的相对位置间距保持不变。

（3）绝对地址引用:指在复制公式时，绝对引用的单元格地址的行号和列号前各加一个“$”符号。其特点是将此单元格复制到新的单元格时，公式引用的单元格地址保持不变。(4)混合引用：指列绝对行相对或列相对行绝对。公式所在的单元格的位置发生改变时，相对引用改变，绝对引用不改变。（5）三维引用：在一个工作薄中，从不同的表引用单元格地址，或从不同的工作薄中引用单元格地址。（6）R1C1表示法：R表示（row）C表示（column）例如：A2可以表示R2C1二、单元格引用1.Excel公式中的单元格引用二、单元格引用三、Excel函数1.Excel函数基本分类

在日常工作中，有些计算内容只能借助于函数来完成。分为9大类共300多个函数，包括统计函数、数学与三角函数、逻辑函数、文本函数、日期与时间函数、数据库函数、财务函数、查找与引用函数和信息函数。

函数的一般格式

函数名（参数1，参数2，……，参数n）

例如：sum（20,30,40）表示对三个数值求和sum（A5，A7，A8）表示对三个单元格数据求和

三、Excel函数1.Excel函数基本分类2.Excel函数输入方法

（1）直接输入函数①选定要输入函数的单元格②输入：=函数名（参数1，参数2，……,参数n）③单键Enter键

（2）使用插入函数对话框输入函数①选定要输入函数单元格②单击“插入”菜单选择“函数”命令，弹出“插入函数”对话框③在对话框中单击“或选择类型”，在“选择函数”列表框中选择所需函数，单击“确

定”按钮，弹出“函数參数”对话框④在“函数参数”对话框中，分别输入Number1，Number2等要求的参数⑤单击“确定”按钮

三、Excel函数2.Excel函数输入方法三、Excel函数3.Excel常用函数实例

（1）求和函数sum（）

（2）平均值函