《精编》HR培训手册

《《精编》HR培训手册》由会员分享，可在线阅读，更多相关《《精编》HR培训手册（69页珍藏版）》请在金锄头文库上搜索。

1、ClerkOffice2003TrainingMaterial文员Office2003培训教材 Exceltrainingmanual IntermediateMaterialExcel培训手册 中级班教材 使用公式公式中的运算符公式中的运算顺序引用单元格创建公式创建公式的方法创建简单公式创建包含引用或名称的公式创建包含函数的公式使用函数常用函数财务函数日期和时间函数数学与三角函数统计函数函数的输入手工输入利用函数向导输入求和计算扩充的自动求和条件求和 建立数据清单建立数据清单的准则使用记录单数据排序对数据清单进行排序创建自定义排序数据筛选自动筛选高级筛选分类汇总分类汇总的计算方法汇总报表和图

2、表插入和删除分类汇总插入图表表示数据复杂的数据链接使用数据透视表创建数据透视表编辑数据透视表controlltoolboxusage 控件工具箱 课程大纲 使用公式 公式中的运算符算术运算符 使用公式 公式中的运算符2 文本运算符 号 可以将文本连接起来 3 比较运算符 使用公式 公式中的运算符4 引用运算符 例 A4 B4 C4 C4 E4 F4 可写为 A4 SUM B4 F4 使用公式 公式中的运算顺序如果在公式中要同时使用多个运算符 则应该了解运算符的优先级 算术运算符的优先级是先乘幂运算 再乘 除运算 最后为加 减运算 相同优先级的运算符按从左到右的次序进行运算 公式中出现不同类型的

3、运算符混用时 运算次序是 引用运算符 算术运算符 文本运算符 如果需要改变次序 可将先要计算的部分括上圆括号 使用公式 引用单元格相对引用相对引用指公式中的单元格位置将随着公式单元格的位置而改变2 绝对引用绝对引用是指公式和函数中的位置是固定不变的 绝对引用是在列字母和行数字之前都加上美元符号 如 A 4 C 63 混合引用混合引用是指在一个单元格引用中 既有绝对引用 也有相对引用 例 行变列不变 A4列变行不变 A 4 创建公式 公式是指对工作表中的数据进行分析和处理的一个等式 不仅用于算术计算 也用于逻辑运算 日期和时间的计算等 一个公式必须具备3个条件 以等式开头 2 含有运算符和运算对

4、象 3 能产生结果公式的3个基本组成部分 数值 包括数字和文本单元格引用操作符 创建公式 创建公式的方法使用编辑栏直接在单元格中输入创建3种类型的公式创建简单公式 2 创建包含引用或名称的公式3 创建包含函数的公式 使用函数 函数是指预定义的内置公式 可以进行数学 文本和逻辑的运算或者查找工作表的信息 函数是一个已经提供给用户的公式 并且有一个描述性的名称 函数结构函数名称 如果要查看可用函数的列表 可以单击一个单元格并按Shift F3键 参数 参数可以是数字 文本 逻辑值 TRUE或FALSE 数值 错误值 如 N A 或单元格引用 参数工具提示 在输入函数时 会出现一个带有语法和参数的工

5、具提示 输入公式 插入函数 对话框 函数的参数函数的参数是一个函数体中括号内的部分 逻辑值 FALSE 需要完全一致TRUE 相似值亦可转换 使用函数 函数的类型1 常用函数 常用函数指经常使用的函数 如求和或计算算术平均值等 包括SUM AVERAGE ISPMT IF HYPERLINK COUNT MAX SIN SUMIF和PMT 它们的语法和作用如下表 使用函数 函数的类型2 财务函数 财务函数用于财务的计算 它可以根据利率 贷款金额和期限计算出所要支付的金额 它们的变量紧密相互关联 如 PMT Rate Nper Pv Fv Type PMT 0 006 360 300000 其中

6、 0 006是每一笔贷款项的利率 360是款项的数目 300000是360笔款项的当前金额 在计算支付金额时 表示当前和将来金额的变量至少有一个通常是负数 注 Rate贷款利率 Nper该项贷款的付款总数 Pv现值 或一系列未来付款的当前值的累积和 也称为本金 Fv为未来值 或在最后一次付款后希望得到的现金余额 如果省略fv则假设其值为零 也就是一笔贷款的未来值为零 Type数字0或1 用以指定各期的付款时间是在期初还是期末 使用函数 函数的类型3 时间和日期函数 主要用于分析和处理日期值和时间值 包括DATE DATEVALUE DAY HOUR TIME TODAY WEEKDAY和YEA

7、R等 例 函数DATA语法 DATE year month day 在单元格中插入当前日期和时间插入静态的日期和时间当前日期选取一个单元格 并按Ctrl 当前时间选取一个单元格 并按Ctrl Shift 当前日期和时间选取一个单元格 并按Ctrl 然后按空格键 最后按Ctrl Shift 插入会更新的日期和时间使用TODAY和NOW函数完成这一工作 小贴士 使用函数 函数的类型4 数学与三角函数 5 统计函数 统计函数是用来对数据区域进行统计分析 主要用于进行各种各样的计算 包括ABS ASIN COMBINE COSLOG PI ROUND SIN TAN和TUUNC等 例 ABS函数语法

8、ABS number 函数是一种特殊的公式 因此所有的函数要以 开始 使用方法如下 1 选中要输入函数的单元格2 单击 Insert 菜单中的命令 弹出如图所示的对话框 使用函数 3 在上图的对话框中 选择 函数分类 下面的函数类型及 函数名 下面的对应函数名 4 单击 确定 按钮 弹出如图所示的相应的调用函数对话框 使用函数 函数的使用 SUM SUM函数函数名称 SUM主要功能 计算所有参数数值的和 使用格式 SUM Number1 Number2 参数说明 Number1 Number2 代表需要计算的值 可以是具体的数值 引用的单元格 区域 逻辑值等 特别提醒 如果参数为数组或引用 只

9、有其中的数字将被计算 数组或引用中的空白单元格 逻辑值 文本或错误值将被忽略 如果将上述公式修改为 SUM LARGE D2 D63 1 2 3 4 5 则可以求出前5名成绩的和 函数的使用 AVERAGE 函数名称 AVERAGE主要功能 求出所有参数的算术平均值 使用格式 AVERAGE number1 number2 参数说明 number1 number2 需要求平均值的数值或引用单元格 区域 参数不超过30个 特别提醒 如果引用区域中包含 0 值单元格 则计算在内 如果引用区域中包含空白或字符单元格 则不计算在内 函数的使用 IF IF函数函数名称 IF主要功能 根据对指定条件的逻辑

10、判断的真假结果 返回相对应的内容 使用格式 IF Logical Value if true Value if false 参数说明 Logical代表逻辑判断表达式 Value if true表示当判断条件为逻辑 真 TRUE 时的显示内容 如果忽略返回 TRUE Value if false表示当判断条件为逻辑 假 FALSE 时的显示内容 如果忽略返回 FALSE 说明 函数IF可以嵌套七层 用value if false及value if true参数可以构造复杂的检测条件 在计算参数value if true和value if false后 函数IF返回相应语句执行后的返回值 如果函

11、数IF的参数包含数组 则在执行IF语句时 数组中的每一个元素都将计算 函数的使用 COUNTIF SUMIF COUNTIF函数函数名称 COUNTIF主要功能 统计某个单元格区域中符合指定条件的单元格数目 使用格式 COUNTIF Range Criteria 参数说明 Range代表要统计的单元格区域 Criteria表示指定的条件表达式 特别提醒 允许引用的单元格区域中有空白单元格出现 SUMIF函数函数名称 SUMIF主要功能 计算符合指定条件的单元格区域内的数值和 使用格式 SUMIF Range Criteria Sum Range 参数说明 Range代表条件判断的单元格区域 C

12、riteria为指定条件表达式 Sum Range代表需要计算的数值所在的单元格区域 函数的使用 SUBTOTAL SUBTOTAL函数函数名称 SUBTOTAL主要功能 返回列表或数据库中的分类汇总 使用格式 SUBTOTAL function num ref1 ref2 参数说明 Function num为1到11 包含隐藏值 或101到111 忽略隐藏值 之间的数字 用来指定使用什么函数在列表中进行分类汇总计算 如图6 ref1 ref2 代表要进行分类汇总区域或引用 不超过29个 应用举例 如图7所示 在B64和C64单元格中分别输入公式 SUBTOTAL 3 C2 C63 和 SUB

13、TOTAL103 C2 C63 并且将61行隐藏起来 确认后 前者显示为62 包括隐藏的行 后者显示为61 不包括隐藏的行 特别提醒 如果采取自动筛选 无论function num参数选用什么类型 SUBTOTAL函数忽略任何不包括在筛选结果中的行 SUBTOTAL函数适用于数据列或垂直区域 不适用于数据行或水平区域 函数的使用 VLOOKUP VLOOKUP函数函数名称 VLOOKUP主要功能 在表格或数值数组的首列查找指定的数值 并由此返回表格或数组当前行中指定列处的数值 使用格式 VLOOKUP lookup value table array col index num range l

14、ookup 参数说明 Lookup value为需要在数组第一列中查找的数值 Lookup value可以为数值 引用或文本字符串 Table array为需要在其中查找数据的数据表 可以使用对区域或区域名称的引用 例如数据库或列表 如果range lookup为TRUE 则table array的第一列中的数值必须按升序排列 2 1 0 1 2 Z FALSE TRUE 否则 函数VLOOKUP不能返回正确的数值 如果range lookup为FALSE table array不必进行排序 通过在 数据 菜单中的 排序 中选择 升序 可将数值按升序排列 Table array的第一列中的数值

15、可以为文本 数字或逻辑值 文本不区分大小写 函数的使用 VLOOKUP VLOOKUP函数Col index num为table array中待返回的匹配值的列序号 Col index num为1时 返回table array第一列中的数值 col index num为2 返回table array第二列中的数值 以此类推 如果col index num小于1 函数VLOOKUP返回错误值值 VALUE 如果col index num大于table array的列数 函数VLOOKUP返回错误值 REF Range lookup为一逻辑值 指明函数VLOOKUP返回时是精确匹配还是近似匹配 如

16、果为TRUE或省略 则返回近似匹配值 也就是说 如果找不到精确匹配值 则返回小于lookup value的最大数值 如果range value为FALSE 函数VLOOKUP将返回精确匹配值 如果找不到 则返回错误值 N A 说明如果函数VLOOKUP找不到lookup value 且range lookup为TRUE 则使用小于等于lookup value的最大值 如果lookup value小于table array第一列中的最小数值 函数VLOOKUP返回错误值 N A 如果函数VLOOKUP找不到lookup value且range lookup为FALSE 函数VLOOKUP返回错误值 N A 函数的使用 DATE TRUE DATE函数函数名称 DATE主要功能 给出指定数值的日期 使用格式 DATE year month day 参数说明 year为指定的年份数值 小于9999 month为指定的月份数值 可以大于12 day为指定的天数 应用举例 在单元格中输入公式 DATE 2003 13 35 确认后 显示出2004 2 4 特别提醒 由于上述公式中 月份为13 多