Excel使用培训公式篇.pptx

Excel使用培训 公式篇 信息技术部办公软件技能培训系列 1 本课程您将学到什么 这不是一个Excel的入门教程 Excel公式知识的一个通览 可能解答一些困惑您的问题 培训内容 Excel公式基础数组公式调试公式函数规划求解 3 Excel公式基础 4 Excel公式基础 数据类型运算符使用函数在公式中引用数据 5 数据类型 数值型包括整数 小数 有最大有效数字位数限制 1 2 1 2 日期时间型 2011 06 0112 22 32 字符串 文本 型 HelloExcel 6 运算符 算术运算符逻辑运算符文本运算符引用运算符运算符优先级使用括号 7 算术运算符 8 逻辑运算符 9 文本运算符 与号 联接或连接一个或多个文本字符串 以生成一段文本 10 当把一个非文本型的数据用此连接时 会将非文本型数据转换为文本型进行计算 但原值包持类型不变 例如 A1 123 A2 A1 元 引用运算符 11 运算符优先级 12 若是您不确定某两个运算符谁的优先级更高 或是要更改求值的顺序 请将公式中要先计算的部分用圆括号括起来 如 5 2 先单目后双目 先乘除后加减最后才比较 在公式中引用数据 A1引用样式R1C1引用样式 13 A1引用样式 默认引用样式默认情况下 Excel使用A1引用样式 此样式引用字母标识列 以及数字标识行 例如 B2引用列B和行2交叉处的单元格限制 最大XFD共16384列 最多65536行 Excel2007为1048576行 引用其他工作表中的单元格格式 工作簿名称 工作表名 单元格 区域 的引用地址例 计算另一个文件book2 xls中Sheet1工作表的B1 B10区域内的平均值 AVERAGE book2 xls Sheet1 b1 b10 14 A1引用样式 相对引用公式中的相对单元格引用 如A1 是基于包含公式和单元格引用的单元格的相对位置 如果把公式移动位置 引用位置也随之改变 如果多行或多列地复制或填充公式 引用会自动调整 默认情况下 新公式使用相对引用 绝对引用公式中的绝对单元格引用 如 A 1 总是在特定位置引用单元格 如果公式所在单元格的位置改变 绝对引用将保持不变 如果多行或多列地复制或填充公式 绝对引用将不作调整 15 A1引用样式 混合引用混合引用具有绝对列和相对行或绝对行和相对列 绝对引用列采用 A1 B1等形式 绝对引用行采用A 1 B 1等形式 如果公式所在单元格的位置改变 则相对引用将改变 而绝对引用将不变 三维引用样式包含单元格或区域引用 前面加上工作表名称的范围 三维引用包含单元格或区域引用 前面加上工作表名称的范围 例如 SUM Sheet2 Sheet13 B5 将计算B5单元格内包含的所有值的和 单元格取值范围是从工作表2到工作表13 16 R1C1引用样式 在R1C1样式中 Excel指出了行号在R后而列号在C后的单元格的位置 通过设置 R1C1引用样式 复选框来打开或关闭R1C1引用样式 该复选框位于 Excel设置 对话框 例子R 2 C对同一列中上面两行的单元格的相对引用 R 2 C 2 对在下面两行 右面两列的单元格的相对引用 R2C2对在工作表的第二行 第二列的单元格的绝对引用 R 1 对活动单元格整个上面一行单元格区域的相对引用 R对当前行的绝对引用 17 数组公式 18 为什么要使用数组公式 对一系列的值同时进行计算 而非对单个值计算 通常是用两个单行或单列区域进行对应单元格进行计算 19 选中多个单元格 输入数据后按Ctrl Enter 可以同时在多个单元格输入相同数据 输入一个数组公式 选中 E2 E11 C2 C11 D2 D11按Ctrl shift EnterExcel将用大括号将数组公式括起来 20 如果您手动键入大括号 公式将被视为文本字符串 并且不起作用 数组常量 格式 元素 元素 元素 另一行 水平数组 1 2 3 4 5 垂直数组 1 2 3 4 5 二维数组 1 2 3 4 5 6 7 8 9 10 11 12 示例 SUM A1 E1 1 2 3 4 5 SUM 3 4 5 6 7 1 2 3 4 5 1 2 3 4 5 6 7 8 9 1 2 3 4 5 6 7 8 9 1 2 3 4 5 6 7 8 9 9 21 调试公式 22 引用追踪 追踪引用单元格 看看本单元格中的公式引用了谁 追踪从属单元格 看看本单元格被哪些公式引用 操作 公式审核追踪单元格 23 公式求值 单步对公式进行求值计算 以进行监控求值过程 每次按下 求值 按钮 公式计算一步 按下步入按钮 进入嵌套的公式求值 24 函数 25 函数概述 函数是预定义的公式 通过使用一些称为参数的特定数值来特定的顺序或结构执行计算 函数可用于执行简单或复杂的计算 Excel函数与数学上的函数概念类似 输入0个1个或多个参数 按一定的逻辑得到一个结果 26 善用Excel的帮助来学习函数 在Excel帮助中搜索需要的函数名 或是分类查找 函数参考 可找到函数的详细说明 在键入函数时 Excel也会提示函数的说明 关于函数的文档格式说明 枚举1 枚举2 尖括号 要替换为所需要的内容 有些文档中不使用 方括号 表示可选项 大括号 表示成组的内容 视同为一项 竖线 表示互斥的枚举项 只用选用其一 27 函数的结构 函数的结构 2 函数名称 如果要查看可用函数的列表 可单击一个单元格并按Shift F3 3 参数参数可以是数字 文本 TRUE或FALSE等逻辑值 数组 错误值 如 N A 或单元格引用 指定的参数都必须为有效参数值 参数也可以是常量 公式或其他函数 参数工具提示 在键入函数时 会出现一个带有语法和参数的工具提示 例如 键入 ROUND 时 工具提示就会出现 工具提示只在使用内置函数时出现 28 常量 不进行计算的值 因此也不会发生变化 例如 数字210以及文本 每季度收入 都是常量 嵌套函数 在某些情况下 您可能需要将某函数作为另一函数的参数使用 例如 int sum a1 a10 嵌套限制有效的返回值当嵌套函数作为参数使用时 它返回的数值类型必须与参数使用的数值类型相同 例如 如果参数返回一个TRUE或FALSE值 那么嵌套函数也必须返回一个TRUE或FALSE值 否则Excel将显示 VALUE 错误值 嵌套级别限制公式可包含多达七级的嵌套函数 当函数B在函数A中用作参数时 函数B则为第二级函数 以此类推 29 数学函数 abs number 绝对值函数ceiling number significance 沿绝对值增大的方向取最接近的significance的倍数 combin number number chosen 给定数目的对象集合中提取若干对象的组合数even number 沿绝对值增大方向取最接近的偶数 fact number 返回某数的阶乘 一个数的阶乘等于1 2 3 该数 floor number significance 将number向下舍入 向零的方向 到最接近的significance的倍数 gcd number1 number2 返回两个或多个整数的最大公约数int number 将数字向下 减小方向 舍入到最接近的整数 lcm number1 number2 返回整数的最小公倍数 30 数学函数 2 mod number divisor 返回两数相除的余数 结果的正负号与除数相同 mround number multiple 返回参数按指定基数舍入后的数值 odd number 沿绝对值增大方向取最接近的奇数 rand 返回大于等于0及小于1的均匀分布随机实数 每次计算工作表时都将返回一个新的随机实数 round number num digits 将某个数字四舍五入为指定的位数 rounddown number num digits 靠近零值 向下 绝对值减小的方向 舍入数字 roundup number num digits 远离零值 向上舍入数字 sign number 返回数字的符号 当数字为正数时返回1 为零时返回0 为负数时返回 1 sum number1 number2 返回某一单元格区域中所有数字之和 31 数学函数 3 sumif range criteria sum range 可以对区域中符合指定条件的值求和 trunc number num digits 将数字的小数部分截去 返回整数 average number1 number2 返回参数的平均值 算术平均值 count value1 value2 计算包含数字的单元格以及参数列表中数字的个数Convert在不同的单位之间转换数据 32 Excel还有大量三角函数 矩阵运算等函数 统计函数 Count计数Sum求和AVERAGE平均数AVERAGEA包括非数字单元格 视为0 的平均数Max最大值Min求最小值Median一组已知数字的中值Mode某一数组或数据区域中出现频率最多的数值Frequency 数组 分隔点 数值在某个区域内的出现频率 然后返回一个垂直数组Rank返回一列数字的数字排位PercentRank 数据集中值的百分比排位 33 文本函数 asc将字符串中的全角 双字节 英文字母或片假名更改为半角 单字节 字符char返回由代码数字指定的字符clean删除文本中所有非打印字符code返回文本字符串中第一个字符的unicode数字代码concatenate将几个文本项合并为一个文本项exact检查两个文本值是否相同find findb在一个文本值中查找另一个文本值 区分大小写 想一想 如果希望不分大小写查询该怎么办 fixed将数字格式设置为具有固定小数位数的文本jis将字符串中的半角 单字节 英文字母或片假名更改为全角 双字节 字符 34 文本函数 2 left leftb返回文本值中最左边的字符len lenb返回文本字符串中的字符个数lower将文本转换为小写mid midb从文本字符串中的指定位置起返回特定个数的字符proper将文本值的每个字的首字母大写replace replaceb按位置替换文本中的字符rept按给定次数重复文本right rightb返回文本值中最右边的字符search searchb在一个文本值中查找另一个文本值与find区别 不区分大小写 可以使用通配符 substitute在文本字符串中用新文本替换旧文本t检查参数是否是文本 如是 返回该文本 否则返回空 35 文本函数 3 text设置数字格式并将其转换为文本 Text 1 000000 返回 000001 trim删除文本中的空格upper将文本转换为大写形式value将文本参数转换为数字n检查参数是否可以视为数字 如是返回转换为数字的值 否则返回0 type返回表示值的数据类型的数字 36 关于Unicode和DBCS Unicode 一个字符就视为一个字符 与物理上采用什么保存格式无关 DBCS 对于中文 日文等 一个字符可能视为二个字节 名称里加 B 后缀的函数就是DBCS格式的函数 find 人 中华人民共和国 返回3 findB 人 中华人民共和国 返回5 37 日期时间 date返回特定日期的序列号datevalue将文本格式的日期转换为序列号day将序列号转换为月份内日期EDATE返回用于表示开始日期之前或之后月数的日期的序列号EOMONTH返回指定月数之前或之后的月份的最后一天的序列号hour将序列号转换为小时minute将序列号转换为分钟month将序列号转换为月networkdays返回两个日期间的全部工作日数now返回当前日期和时间的序列号second将序列号转换为秒time返回特定时间的序列号 38 日期时间 2 timevalue将文本格式的时间转换为序列号today返回今天日期的序列号weekday将序列号转换为星期日期weeknum将序列号转换为代表该星期为一年中第几周的数字workday返回指定的若干个工作日之前或之后的日期的序列号year将序列号转换为年yearfrac返回两个日期之间年数 小数 39 关于日期与时间的一些话题 日期与时间的运算日期与时间在excel内部是以一个小数保存的 整数部分为从 纪元 如1900 1 1 以来的天数 小数部分为当天按24小时的比例值 如0 6表示14 24日期加1即是第二天的同一时刻 减1即是前一天同一时刻 日期常量的输入 YEARFRAC 2011 5 1 2012 5 2 40 想一想 YEARFRAC 2011 5 1 2012 5 2 是什么结果 关于日期与时间的一些话题 2 Excel有两个日期系统 1900日期系统 纪元从1900 1 1开始的日期系统 默认日期系统 1904日期系统 纪元从1904 1 2开始的日期系统 是为了兼容早期的Macintosh 在不同的的日期系统中 相同的序列值对应的日期不同 相差1462天 约4年零1天 基于两个不同日期系统的工作簿之间复制和粘贴日期或创建外部引用时会遇到问题 41 修改使用的日期系统 Excel选项 然后单击 高级 类别 中或清除 使用1904日期系统 复选框 逻辑函数 逻辑值是指对于 是否 的判断与运算 FALSE逻辑真TRUE逻辑假AND 逻辑值1 如果其所有参数均为TRUE 则返回TRUEIF 逻辑值 返回值1 返回值2 指定要执行的逻辑检测 如为真 返回第一个值 否则返回第二个值 IFERROR 公式 默认值 如果公式的计算结果错误 则返回指定的值 否则返回公式的结果NOT 逻辑值 对其参数的逻辑求反OR 逻辑值1 如果任一参数为TRUE 则返回TRUE 42 查找和引用函数 address以文本形式生成一个引用值串 address 2 3 1 false excelsheet column返回引用的列号columns返回引用中包含的列数hlookup查找数组的首行 并返回指定单元格的值hyperlink创建快捷方式或跳转 以打开存储在网络服务器 intranet或internet上的文档index使用索引从引用或数组中选择值lookup在向量或数组中查找值match在引用或数组中查找值 得到索引序号 offset从给定引用中返回引用偏移量row返回引用的行号 43 查找和引用函数 2 ROWS返回引用中的行数TRANSPOSE返回数组的转置VLOOKUP在数组第一列中查找 然后在行之间移动以返回单元格的值CELL返回有关单元格格式 位置或内容的信息Indirect引用指定的单元格内容 44 为什么需要这些函数 因为你可能会需要根据不同的变量来决定引用哪个单元格 信息函数 ERROR TYPE返回对应于错误类型的数字INFO返回有关当前操作环境的信息ISBLANK如果值为空 则返回TRUEISERR如果值为除 N A以外的任何错误值 则返回TRUEISERROR如果值为任何错误值 则返回TRUEISEVEN如果数字为偶数 则返回TRUEISLOGICAL如果值为逻辑值 则返回TRUE 45 信息函数 2 ISNA如果值为错误值 N A 则返回TRUEISNONTEXT如果值不是文本 则返回TRUEISNUMBER如果值为数字 则返回TRUEISODD如果数字为奇数 则返回TRUEISREF如果值为引用值 则返回TRUEISTEXT如果值