办公常用EXCEL技能大全(非常实用)
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
12
使用函数 函数的类型
3. 时间和日期函数:
主要用于分析和处理日期值和时间值,包括DATE、DATEVALUE 、DAY、HOUR、TIME、TODAY、WEEKDAY 和 YEAR等。
例：函数DATA 语法：DATE(year,month,day)
13
在单元格中插入当前日期和时间 插入静态的日期和时间 当前日期 选取一个单元格,并按 Ctrl+; 当前时间 选取一个单元格,并按 Ctrl+Shift+； 当前日期和时间 选取一个单元格,并按 Ctrl+;,然后按空格键,最后按 Ctrl+Shift+; 插入会更新的日期和时间 使用 TODAY 和 NOW 函数完成这一工作。
Clerk Office 2003 Training Material 文员Office 2003培训教材
Excel training manual- Intermediate Material Excel培训手册 — 中级班教材
课程大纲
使用公式 公式中的运算符 公式中的运算顺序 引用单元格 创建公式 创建公式的方法 创建简单公式 创建包含引用或名称的公式 创建包含函数的公式 使用函数 常用函数 财务函数 日期和时间函数 数学与三角函数 统计函数 函数的输入 手工输入 利用函数向导输入 求和计算 扩充的自动求和 条件求和 建立数据清单 建立数据清单的准则 使用记录单 数据排序
示 (A5:A15)
例
, (空格)
SUM(A5:A15,C5:C15) (B7:D7 C6:C8)
例:A4=B4+C4+C4+E4+F4,可写为: A4=SUM(B4:F4)
5
使用公式
公式中的运算顺序 如果在公式中要同时使用多个运算符,则应该了解运算符 的优先级.算术运算符的优先级是先乘幂运算,再乘、除运算, 最后为加、减运算。相同优先级的运算符按从左到右的次序 进行运算。 公式中出现不同类型的运算符混用时,运算次序是：引用 运算符——算术运算符——文本运算符。如果需要改变次序, 可将先要计算的部分括上圆括号。
PMT(Rate,Nper,Pv,Fv,Type)
返回在固定利率下,投资或贷款的等额分期偿还额
11
使用函数 函数的类型
2. 财务函数: 财务函数用于财务的计算,它可以根据利率、贷款金额和期限计算出所要支付 的金额,它们的变量紧密相互关联。 如：PMT(Rate,Nper,Pv,Fv,Type) =PMT（0.006,360,-300000) 其中,0.006是每一笔贷款项的利率,360是款项的数目,-300000是360笔款项的 当前金额(在计算支付金额时,表示当前和将来金额的变量至少有一个通常是负数)
创建公式的方法
1. 使用编辑栏 2. 直接在单元格中输入
创建3种类型的公式
1. 创建简单公式; 2. 创建包含引用或名称的公式
3. 创建包含函数的公式
9
使用函数
函数是指预定义的内置公式,可以进行数学、文本和逻辑的运 算或者查找工作表的信息。 函数是一个已经提供给用户的公式,并且有一个描述性的名称。 函数结构
小于 大于
>=(大于等于号) <>(不等号) <=(小于等于号)
大于等于 不等于 小于等于
4
使用公式 公式中的运算符
4. 引用运算符
引用运算符 :
运算符含义 区域运算符,产生对包 括在丙相引用之间的 所有单元格的引用
联合运算符,将多个引 用合并为一个引用 交叉运算符,产生对两 个引用构有的单元格 的引用
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. 输入公式：“插入函数”对话框。
注: Rate Nper Pv Fv
Type
贷款利率。 该项贷款的付款总数。 现值,或一系列未来付款的当前值的累积和,也称为本金。 为未来值,或在最后一次付款后希望得到的现金余额,如果省略 fv 则假设其值为零,也就是一笔贷款的未来值为零。 数字 0 或 1,用以指定各期的付款时间是在期初还是期末。
函数的参数
函数的参数是一个函数体中括号内的部分
逻辑值: FALSE :需要完全一致
TRUE: 相似值亦可转换
10
使用函数 函数的类型 1. 常用函数:
常用函数指经常使用的函数,如求和或计算算术平均值等.包括SUM,AVERAGE, ISPMT,IF,HYPERLINK,COUNT,MAX,SIN, SUMIF和PMT.它们的语法和作用如下表:
函数名称：SUMIF 主要功能：计算符合指定条件的单元格区域内的数值和。 使用格式：SUMIF（Range,Criteria,Sum_Range） 参数说明：Range代表条件判断的单元格区域；Criteria为指定条件表达式； Sum_Range代表需要计算的数值所在的单元格区域。
21
函数的使用(SUBTOTAL)
2
使用公式
公式中的运算符 1. 算术运算符
算术运算符号 +(加号) -(减号) *(星号 /(斜杠) %(百分号) ^(脱字号) 运算符含义 加 减 乘 除 百分号 乘方 2+3=5 3-1=2 3*2=6 6/2=3 50% 4^3=43=64 示 例
3
使用公式 公式中的运算符
2. 文本运算符 “&”号,可以将文本连接起来. 3. 比较运算符 比较运算符 =(等号) 运算符含义 相等 示 例
6
使用公式
引用单元格
1. 相对引用
相对引用指公式中的单元格位置将随着公式单元格的位置而改变 2. 绝对引用 绝对引用是指公式和函数中的位置是固定不变的. 绝对引用是在列字母和行数字之前都加上美元符号”$”,如 $A$4,$C$6
3. 混合引用
混合引用是指在一个单元格引用中,既有绝对引用,也有相对引用. 例:行变列不变: $A4 列变行不变:A$4
SUBTOTAL函数
函数名称：SUBTOTAL 主要功能：返回列表或数据库中的分类汇总。 使用格式：SUBTOTAL(function_num, ref1, ref2, ...) 参数说明：Function_num为1到11（包含隐藏值）或101到 111（忽略隐藏值）之间的数字,用来指定使用什么函数在 列表中进行分类汇总计算（如图6）；ref1, ref2,„„代 表要进行分类汇总区域或引用,不超过29个。
20
函数的使用 (COUNTIF & SUMIF)
COUNTIF函数 函数名称：COUNTIF 主要功能：统计某个单元格区域中符合指定条件的单元格数目。 使用格式：COUNTIF(Range,Criteria) 参数说明：Range代表要统计的单元格区域；Criteria表示指定的条件表 达式。 特别提醒：允许引用的单元格区域中有空白单元格出现 SUMIF函数
19
函数的使用 (IF)
IF函数
函数名称：IF
主要功能：根据对指定条件的逻辑判断的真假结果,返回相对应的内容。
使用格式：=IF(Logical,Value_if_true,Value_if_false) 参数说明：Logical代表逻辑判断表达式；Value_if_true表示当判断条件 为逻辑“真（TRUE）”时的显示内容,如果忽略返回“TRUE”； Value_if_false表示当判断条件为逻辑“假（FALSE）”时的显示内容, 如果忽略返回“FALSE”。
HYPERLINK(Link_location,Friendly_name) 创建快捷方式,以便打开文档或网络驱动器或连接连接 INTERNET COUNT(Value1,Value2, …) MAX(number1,number2, …) SIN (number) SUMIF (Range, Criteria, Sum_range) 计算参数表中的数字参数和包含数字的单元格的个数 返回一组数值中的最大值,忽略逻辑值和文字符 返回纪念品定角度的正弦值 根据指定条伯对若干单元格求和
16
使用函数 3.在上图的对话框中,选择[函数分类]下面的函数类型及 [函数名]下面的对应函数名。 4. 单击[确定]按钮,弹出如图所示的相应的调用 函数对话框：
17
函数的使用 (SUM)
SUM函数 函数名称：SUM 主要功能：计算所有参数数值的和。 使用格式：SUM（Number1,Number2„„） 参数说明：Number1、Number2„„代表需要计算的值,可以是具体的 数值、引用的单元格（区域）、逻辑值等。 特别提醒：如果参数为数组或引用,只有其中的数字将被计算。数组或 引用中的空白单元格、逻辑值、文本或错误值将被忽略；如果将上述公式 修改为：=SUM(LARGE(D2:D63,{1,2,3,4,5})), 则可以求出前5名成绩的和。
应用举例：如图7所示,在B64和C64单元格中分别输入公 式：=SUBTOTAL(3,C2:C63)和=SUBTOTAL103,C2:C63), 并且将61行隐藏起来,确认后,前者显示为62（包括隐藏 的行）,后者显示为61,不包括隐藏的行。 特别提醒：如果采取自动筛选,无论function_num参数 选用什么类型,SUBTOTAL函数忽略任何不包括在筛选结 果中的行；SUBTOTAL函数适用于数据列或垂直区域,不 适用于数据行或水平区域。
18
ຫໍສະໝຸດ Baidu
函数的使用 (AVERAGE)
函数名称：AVERAGE 主要功能：求出所有参数的算术平均值。 使用格式：AVERAGE(number1,number2,„„) 参数说明：number1,number2,„„：需要求平均 值的数值或引用单元格（区域）,参数不超过30个。 特别提醒：如果引用区域中包含“0”值单元格, 则计算在内；如果引用区域中包含空白或字符单元格, 则不计算在内。
7
创建公式
公式是指对工作表中的数据进行分析和处理的一个等式,不仅 用于算术计算,也用于逻辑运算、日期和时间的计算等。 一个公式必须具备3个条件：
1. 以等式开头; 2. 含有运算符和运算对象; 3. 能产生结果
公式的3个基本组成部分:
1. 数值:包括数字和文本 2. 单元格引用 3. 操作符
8
创建公式
说明:
•函数 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)
作用
返回单元格区域中所有数值的和 返回普通(无提保)的利息偿还 计算参数的算术平均数,参数可以是数值或包含数值的 名称数组和引用 执行真假判断,根据对指定条件进行逻辑评价的真假而 返回不同的结果。
B1=C1,若B1中单元格内的值确实与C1中的值相 等,则产生逻辑真值TRUE,若不相等,则产生逻辑 假值FALSE B1<C1 B1>C1,若B1中数值为6,C1中数值为4,则条件成 立产生逻辑真值TRUE,否则产生逻辑假值 FALSE B1>=C1 B1<>C1 B1<=C1
<(小于号) >(大于号)
对数据清单进行排序
创建自定义排序 数据筛选 自动筛选 高级筛选 分类汇总 分类汇总的计算方法 汇总报表和图表 插入和删除分类汇总 插入图表表示数据 复杂的数据链接 使用数据透视表 创建数据透视表 编辑数据透视表 controll toolbox usage (控件工具箱)
使用函数 函数的类型
3. 时间和日期函数:
主要用于分析和处理日期值和时间值,包括DATE、DATEVALUE 、DAY、HOUR、TIME、TODAY、WEEKDAY 和 YEAR等。
例：函数DATA 语法：DATE(year,month,day)
13
在单元格中插入当前日期和时间 插入静态的日期和时间 当前日期 选取一个单元格,并按 Ctrl+; 当前时间 选取一个单元格,并按 Ctrl+Shift+； 当前日期和时间 选取一个单元格,并按 Ctrl+;,然后按空格键,最后按 Ctrl+Shift+; 插入会更新的日期和时间 使用 TODAY 和 NOW 函数完成这一工作。
Clerk Office 2003 Training Material 文员Office 2003培训教材
Excel training manual- Intermediate Material Excel培训手册 — 中级班教材
课程大纲
使用公式 公式中的运算符 公式中的运算顺序 引用单元格 创建公式 创建公式的方法 创建简单公式 创建包含引用或名称的公式 创建包含函数的公式 使用函数 常用函数 财务函数 日期和时间函数 数学与三角函数 统计函数 函数的输入 手工输入 利用函数向导输入 求和计算 扩充的自动求和 条件求和 建立数据清单 建立数据清单的准则 使用记录单 数据排序
示 (A5:A15)
例
, (空格)
SUM(A5:A15,C5:C15) (B7:D7 C6:C8)
例:A4=B4+C4+C4+E4+F4,可写为: A4=SUM(B4:F4)
5
使用公式
公式中的运算顺序 如果在公式中要同时使用多个运算符,则应该了解运算符 的优先级.算术运算符的优先级是先乘幂运算,再乘、除运算, 最后为加、减运算。相同优先级的运算符按从左到右的次序 进行运算。 公式中出现不同类型的运算符混用时,运算次序是：引用 运算符——算术运算符——文本运算符。如果需要改变次序, 可将先要计算的部分括上圆括号。
PMT(Rate,Nper,Pv,Fv,Type)
返回在固定利率下,投资或贷款的等额分期偿还额
11
使用函数 函数的类型
2. 财务函数: 财务函数用于财务的计算,它可以根据利率、贷款金额和期限计算出所要支付 的金额,它们的变量紧密相互关联。 如：PMT(Rate,Nper,Pv,Fv,Type) =PMT（0.006,360,-300000) 其中,0.006是每一笔贷款项的利率,360是款项的数目,-300000是360笔款项的 当前金额(在计算支付金额时,表示当前和将来金额的变量至少有一个通常是负数)
创建公式的方法
1. 使用编辑栏 2. 直接在单元格中输入
创建3种类型的公式
1. 创建简单公式; 2. 创建包含引用或名称的公式
3. 创建包含函数的公式
9
使用函数
函数是指预定义的内置公式,可以进行数学、文本和逻辑的运 算或者查找工作表的信息。 函数是一个已经提供给用户的公式,并且有一个描述性的名称。 函数结构
小于 大于
>=(大于等于号) <>(不等号) <=(小于等于号)
大于等于 不等于 小于等于
4
使用公式 公式中的运算符
4. 引用运算符
引用运算符 :
运算符含义 区域运算符,产生对包 括在丙相引用之间的 所有单元格的引用
联合运算符,将多个引 用合并为一个引用 交叉运算符,产生对两 个引用构有的单元格 的引用
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. 输入公式：“插入函数”对话框。
注: Rate Nper Pv Fv
Type
贷款利率。 该项贷款的付款总数。 现值,或一系列未来付款的当前值的累积和,也称为本金。 为未来值,或在最后一次付款后希望得到的现金余额,如果省略 fv 则假设其值为零,也就是一笔贷款的未来值为零。 数字 0 或 1,用以指定各期的付款时间是在期初还是期末。
函数的参数
函数的参数是一个函数体中括号内的部分
逻辑值: FALSE :需要完全一致
TRUE: 相似值亦可转换
10
使用函数 函数的类型 1. 常用函数:
常用函数指经常使用的函数,如求和或计算算术平均值等.包括SUM,AVERAGE, ISPMT,IF,HYPERLINK,COUNT,MAX,SIN, SUMIF和PMT.它们的语法和作用如下表:
函数名称：SUMIF 主要功能：计算符合指定条件的单元格区域内的数值和。 使用格式：SUMIF（Range,Criteria,Sum_Range） 参数说明：Range代表条件判断的单元格区域；Criteria为指定条件表达式； Sum_Range代表需要计算的数值所在的单元格区域。
21
函数的使用(SUBTOTAL)
2
使用公式
公式中的运算符 1. 算术运算符
算术运算符号 +(加号) -(减号) *(星号 /(斜杠) %(百分号) ^(脱字号) 运算符含义 加 减 乘 除 百分号 乘方 2+3=5 3-1=2 3*2=6 6/2=3 50% 4^3=43=64 示 例
3
使用公式 公式中的运算符
2. 文本运算符 “&”号,可以将文本连接起来. 3. 比较运算符 比较运算符 =(等号) 运算符含义 相等 示 例
6
使用公式
引用单元格
1. 相对引用
相对引用指公式中的单元格位置将随着公式单元格的位置而改变 2. 绝对引用 绝对引用是指公式和函数中的位置是固定不变的. 绝对引用是在列字母和行数字之前都加上美元符号”$”,如 $A$4,$C$6
3. 混合引用
混合引用是指在一个单元格引用中,既有绝对引用,也有相对引用. 例:行变列不变: $A4 列变行不变:A$4
SUBTOTAL函数
函数名称：SUBTOTAL 主要功能：返回列表或数据库中的分类汇总。 使用格式：SUBTOTAL(function_num, ref1, ref2, ...) 参数说明：Function_num为1到11（包含隐藏值）或101到 111（忽略隐藏值）之间的数字,用来指定使用什么函数在 列表中进行分类汇总计算（如图6）；ref1, ref2,„„代 表要进行分类汇总区域或引用,不超过29个。
20
函数的使用 (COUNTIF & SUMIF)
COUNTIF函数 函数名称：COUNTIF 主要功能：统计某个单元格区域中符合指定条件的单元格数目。 使用格式：COUNTIF(Range,Criteria) 参数说明：Range代表要统计的单元格区域；Criteria表示指定的条件表 达式。 特别提醒：允许引用的单元格区域中有空白单元格出现 SUMIF函数
19
函数的使用 (IF)
IF函数
函数名称：IF
主要功能：根据对指定条件的逻辑判断的真假结果,返回相对应的内容。
使用格式：=IF(Logical,Value_if_true,Value_if_false) 参数说明：Logical代表逻辑判断表达式；Value_if_true表示当判断条件 为逻辑“真（TRUE）”时的显示内容,如果忽略返回“TRUE”； Value_if_false表示当判断条件为逻辑“假（FALSE）”时的显示内容, 如果忽略返回“FALSE”。
HYPERLINK(Link_location,Friendly_name) 创建快捷方式,以便打开文档或网络驱动器或连接连接 INTERNET COUNT(Value1,Value2, …) MAX(number1,number2, …) SIN (number) SUMIF (Range, Criteria, Sum_range) 计算参数表中的数字参数和包含数字的单元格的个数 返回一组数值中的最大值,忽略逻辑值和文字符 返回纪念品定角度的正弦值 根据指定条伯对若干单元格求和
16
使用函数 3.在上图的对话框中,选择[函数分类]下面的函数类型及 [函数名]下面的对应函数名。 4. 单击[确定]按钮,弹出如图所示的相应的调用 函数对话框：
17
函数的使用 (SUM)
SUM函数 函数名称：SUM 主要功能：计算所有参数数值的和。 使用格式：SUM（Number1,Number2„„） 参数说明：Number1、Number2„„代表需要计算的值,可以是具体的 数值、引用的单元格（区域）、逻辑值等。 特别提醒：如果参数为数组或引用,只有其中的数字将被计算。数组或 引用中的空白单元格、逻辑值、文本或错误值将被忽略；如果将上述公式 修改为：=SUM(LARGE(D2:D63,{1,2,3,4,5})), 则可以求出前5名成绩的和。
应用举例：如图7所示,在B64和C64单元格中分别输入公 式：=SUBTOTAL(3,C2:C63)和=SUBTOTAL103,C2:C63), 并且将61行隐藏起来,确认后,前者显示为62（包括隐藏 的行）,后者显示为61,不包括隐藏的行。 特别提醒：如果采取自动筛选,无论function_num参数 选用什么类型,SUBTOTAL函数忽略任何不包括在筛选结 果中的行；SUBTOTAL函数适用于数据列或垂直区域,不 适用于数据行或水平区域。
18
ຫໍສະໝຸດ Baidu
函数的使用 (AVERAGE)
函数名称：AVERAGE 主要功能：求出所有参数的算术平均值。 使用格式：AVERAGE(number1,number2,„„) 参数说明：number1,number2,„„：需要求平均 值的数值或引用单元格（区域）,参数不超过30个。 特别提醒：如果引用区域中包含“0”值单元格, 则计算在内；如果引用区域中包含空白或字符单元格, 则不计算在内。
7
创建公式
公式是指对工作表中的数据进行分析和处理的一个等式,不仅 用于算术计算,也用于逻辑运算、日期和时间的计算等。 一个公式必须具备3个条件：
1. 以等式开头; 2. 含有运算符和运算对象; 3. 能产生结果
公式的3个基本组成部分:
1. 数值:包括数字和文本 2. 单元格引用 3. 操作符
8
创建公式
说明:
•函数 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)
作用
返回单元格区域中所有数值的和 返回普通(无提保)的利息偿还 计算参数的算术平均数,参数可以是数值或包含数值的 名称数组和引用 执行真假判断,根据对指定条件进行逻辑评价的真假而 返回不同的结果。
B1=C1,若B1中单元格内的值确实与C1中的值相 等,则产生逻辑真值TRUE,若不相等,则产生逻辑 假值FALSE B1<C1 B1>C1,若B1中数值为6,C1中数值为4,则条件成 立产生逻辑真值TRUE,否则产生逻辑假值 FALSE B1>=C1 B1<>C1 B1<=C1
<(小于号) >(大于号)
对数据清单进行排序
创建自定义排序 数据筛选 自动筛选 高级筛选 分类汇总 分类汇总的计算方法 汇总报表和图表 插入和删除分类汇总 插入图表表示数据 复杂的数据链接 使用数据透视表 创建数据透视表 编辑数据透视表 controll toolbox usage (控件工具箱)