Excel公式与函数培训

14
使用函数 函数的类型
4. 数学与三角函数:
主要用于进行各种各样的计算,包括ABS、ASIN、COMBINE、 COSLOG、PI、ROUND、SIN、TAN 和TUUNC等。
例：ABS函数 语法：ABS(number)
5. 统计函数: 统计函数是用来对数据区域进行统计分析。
15
使用函数
函数是一种特殊的公式,因此所有的函数要以“=”开始。 使用方法如下：1.选中要输入函数的单元格 2.单击[Insert]菜单中的 命令,弹出如图所示的对话框：
1. 函数名称：如果要查看可用函数的列表,可以单击一个单元格并按 Shift+F3键； 2. 参数：参数可以是数字、文本、逻辑值（TRUE或FALSE）、数值、 错误值（如#N/A）或单元格引用。 3. 参数工具提示：在输入函数时,会出现一个带有语法和参数的工具提示。 4. 输入公式：“插入函数”对话框。
创建公式的方法
1. 使用编辑栏 2. 直接在单元格中输入
创建3种类型的公式
1. 创建简单公式; 2. 创建包含引用或名称的公式
3. 创建包含函数的公式
9
使用函数
函数是指预定义的内置公式,可以进行数学、文本和逻辑的运 算或者查找工作表的信息。 函数是一个已经提供给用户的公式,并且有一个描述性的名称。 函数结构
6
使用公式
引用单元格
1. 相对引用
相对引用指公式中的单元格位置将随着公式单元格的位置而改变 2. 绝对引用 绝对引用是指公式和函数中的位置是固定不变的. 绝对引用是在列字母和行数字之前都加上美元符号”$”,如 $A$4,$C$6
3. 混合引用
混合引用是指在一个单元格引用中,既有绝对引用,也有相对引用. 例:行变列不变: $A4 列变行不变:A$4
20
函数的使用 (COUNTIF & SUMIF)
COUNTIF函数 函数名称：COUNTIF 主要功能：统计某个单元格区域中符合指定条件的单元格数目。 使用格式：COUNTIF(Range,Criteria) 参数说明：Range代表要统计的单元格区域；Criteria表示指定的条件表 达式。 特别提醒：允许引用的单元格区域中有空白单元格出现 SUMIF函数
18
函数的使用 (AVERAGE)
函数名称：AVERAGE 主要功能：求出所有参数的算术平均值。 使用格式：AVERAGE(number1,number2,„„) 参数说明：number1,number2,„„：需要求平均 值的数值或引用单元格（区域）,参数不超过30个。 特别提醒：如果引用区域中包含“0”值单元格, 则计算在内；如果引用区域中包含空白或字符单元格, 则不计算在内。
2
使用公式
公式中的运算符 1. 算术运算符
算术运算符号 +(加号) -(减号) *(星号 /(斜杠) %(百分号) ^(脱字号) 运算符含义 加 减 乘 除 百分号 乘方 2+3=5 3-1=2 3*2=6 6/2=3 50% 4^3=43=64 示 例
3
使用公式 公式中的运算符
2. 文本运算符 “&”号,可以将文本连接起来. 3. 比较运算符 比较运算符 =(等号) 运算符含义 相等 示 例
使用公式 公式中的运算符 公式中的运算顺序 引用单元格 创建公式 创建公式的方法 创建简单公式 创建包含引用或名称的公式 创建包含函数的公式 使用函数 常用函数 财务函数 日期和时间函数 数学与三角函数 统计函数 函数的输入 手工输入 利用函数向导输入 求和计算 扩充的自动求和 条件求和 建立数据清单 建立数据清单的准则 使用记录单 数据排序
7
创建公式
公式是指对工作表中的数据进行分析和处理的一个等式,不仅 用于算术计算,也用于逻辑运算、日期和时间的计算等。 一个公式必须具备3个条件：
1. 以等式开头; 2. 含有运算符和运算对象; 3. 能产生结果
公式的3个基本组成部分:
1. 数值:包括数字和文本 2. 单元格引用 3. 操作符
8
创建公式
B1=C1,若B1中单元格内的值确实与C1中的值相 等,则产生逻辑真值TRUE,若不相等,则产生逻辑 假值FALSE B1<C1 B1>C1,若B1中数值为6,C1中数值为4,则条件成 立产生逻辑真值TRUE,否则产生逻辑假值 FALSE B1>=C1 B1<>C1 B1<=C1
<(小于号) >(大于号)
应用举例：如图7所示,在B64和C64单元格中分别输入公 式：=SUBTOTAL(3,C2:C63)和=SUBTOTAL103,C2:C63), 并且将61行隐藏起来,确认后,前者显示为62（包括隐藏 的行）,后者显示为61,不包括隐藏的行。 特别提醒：如果采取自动筛选,无论function_num参数 选用什么类型,SUBTOTAL函数忽略任何不包括在筛选结 果中的行；SUBTOTAL函数适用于数据列或垂直区域,不 适用于数据行或水平区域。
16
使用函数 3.在上图的对话框中,选择[函数分类]下面的函数类型及 [函数名]下面的对应函数名。 4. 单击[确定]按钮,弹出如图所示的相应的调用 函数对话框：
17
函数的使用 (SUM)
SUM函数 函数名称：SUM 主要功能：计算所有参数数值的和。 使用格式：SUM（Number1,Number2„„） 参数说明：Number1、Number2„„代表需要计算的值,可以是具体的 数值、引用的单元格（区域）、逻辑值等。 特别提醒：如果参数为数组或引用,只有其中的数字将被计算。数组或 引用中的空白单元格、逻辑值、文本或错误值将被忽略；如果将上述公式 修改为：=SUM(LARGE(D2:D63,{1,2,3,4,5})),则可以求出前5名成绩的和。
函数名称：SUMIF 主要功能：计算符合指定条件的单元格区域内的数值和。 使用格式：SUMIF（Range,Criteria,Sum_Range） 参数说明：Range代表条件判断的单元格区域；Criteria为指定条件表达式； Sum_Байду номын сангаасange代表需要计算的数值所在的单元格区域。
21
函数的使用(SUBTOTAL)
HYPERLINK(Link_location,Friendly_name) 创建快捷方式,以便打开文档或网络驱动器或连接连接 INTERNET COUNT(Value1,Value2, …) MAX(number1,number2, …) SIN (number) SUMIF (Range, Criteria, Sum_range) 计算参数表中的数字参数和包含数字的单元格的个数 返回一组数值中的最大值,忽略逻辑值和文字符 返回纪念品定角度的正弦值 根据指定条伯对若干单元格求和
说明:
•函数 IF 可以嵌套七层,用 value_if_false 及 value_if_true 参数可以构造复杂的检测条件。 •在计算参数 value_if_true 和 value_if_false 后，函数 IF 返回相应语句执行后的返回值。 •如果函数 IF 的参数包含数组，则在执行 IF 语句时，数组中的每一个元素都将计算。
语法
SUM(number1,number2, …) ISPMT(Rate,Per,Nper,Pv) AVERAGE(Number1,Number2, …) IF(Logical_test,Value_if_true,Value_if_fals e)
作用
返回单元格区域中所有数值的和 返回普通(无提保)的利息偿还 计算参数的算术平均数,参数可以是数值或包含数值的 名称数组和引用 执行真假判断,根据对指定条件进行逻辑评价的真假而 返回不同的结果。
函数的参数
函数的参数是一个函数体中括号内的部分
逻辑值: FALSE :需要完全一致
TRUE: 相似值亦可转换
10
使用函数 函数的类型 1. 常用函数:
常用函数指经常使用的函数,如求和或计算算术平均值等.包括SUM,AVERAGE, ISPMT,IF,HYPERLINK,COUNT,MAX,SIN, SUMIF和PMT.它们的语法和作用如下表:
小于 大于
>=(大于等于号) <>(不等号) <=(小于等于号)
大于等于 不等于 小于等于
4
使用公式 公式中的运算符
4. 引用运算符
引用运算符 :
运算符含义 区域运算符,产生对包 括在丙相引用之间的 所有单元格的引用
联合运算符,将多个引 用合并为一个引用 交叉运算符,产生对两 个引用构有的单元格 的引用
对数据清单进行排序
创建自定义排序 数据筛选 自动筛选 高级筛选 分类汇总 分类汇总的计算方法 汇总报表和图表 插入和删除分类汇总 插入图表表示数据 复杂的数据链接 使用数据透视表 创建数据透视表 编辑数据透视表 controll toolbox usage (控件工具箱)
Clerk Office 2003 Training Material 文员Office 2003培训教材
Excel training manual- Intermediate Material Excel培训手册 — 中级班教材
编写: Guihui Huang
日期: 2006-08-30
课程大纲
19
函数的使用 (IF)
IF函数
函数名称：IF
主要功能：根据对指定条件的逻辑判断的真假结果,返回相对应的内容。
使用格式：=IF(Logical,Value_if_true,Value_if_false) 参数说明：Logical代表逻辑判断表达式；Value_if_true表示当判断条件 为逻辑“真（TRUE）”时的显示内容,如果忽略返回“TRUE”； Value_if_false表示当判断条件为逻辑“假（FALSE）”时的显示内容, 如果忽略返回“FALSE”。