Excel使用培训：公式篇.pptx

Excel使用培训：公式篇 信息技术部 办公软件技能培训系列 1 本课程您将学到什么？ 这不是一个 Excel的入门教程。 Excel公式知识的一个通览。 可能解答一些困惑您的问题。 培训内容 Excel公式基础 数组公式 调试公式 函数 规划求解 3 Excel公式基础 4 Excel公式基础 数据类型 运算符 使用函数 在公式中引用数 据 5 数据类型 数值型 包括整数、小数。 有最大有效数字位数限制。 1, -2, 1.2 日期时间型 “2011-06-01 12:22:32“ 字符串（文本）型 “Hello Excel!“ 6 运算符 算术运算符 逻辑运算符 文本运算符 引用运算符 运算符优先级 使用括号 7 算术运算符 算术运算符含义示例 +（加号）加法3+3 （减号）减法 负数 3-1 -1 *（星号）乘法 3*3 /（正斜杠）除法 3/3 %（百分号）百分比 20% （脱字号）乘方32 8 逻辑运算符 逻辑运算符含义示例 =（等号）等于 A1=B1 （大于号）大于A1B1 =（大于等于号）大于等于A1=B1 （不等号）不等于A1 ( 枚举枚举 1|1|枚举枚举 2, , , . )3. ) 尖括号：要替换为所需要的内容。（有些文档中不使用 )。 方括号：表示可选项。 大括号：表示成组的内容，视同为一项。 竖线：表示互斥的枚举项，只用选用其一。 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，则返回 TRUE IF(逻辑值 ,返回值 1,返回值 2) 指定要执行的逻辑检测,如为真，返回第一个值，否则返回第 二个值。 IFERROR(公式 ,默认值 ) 如果公式的计算结果错误，则返回指定的值；否则返回公式的 结果 NOT(逻辑值 ) 对其参数的逻辑求反 OR(逻辑值 1, ) 如果任一参数为 TRUE，则返回 TRUE 42 查找和引用函数 address 以文本形式生成一个引用值串。 =address(2,3,1,false,“excel sheet“) column 返回引用的列号 columns 返回引用中包含的列数 hlookup 查找数组的首行，并返回指定单元格的值 hyperlink 创建快捷方式或跳转，以打开存储在网络服务 器、 intranet 或 internet 上的文档 index 使用索引从引用或数组中选择值 lookup 在向量或数组中查找值 match 在引用或数组中查找值，得到索引序号。 offset 从给定引用中返回引用偏移量 row 返回引用的行号 43 查找和引用函数 (2) ROWS 返回引用中的行数 TRANSPOSE 返回数组的转置 VLOOKUP 在数组第一列中查找，然后在行之间 移动以返回单元格的值 CELL 返回有关单元格格式、位置或内容的信息 Indirect 引用指定的单元格内容 44 为什么需要这些函数? 因为你可能会需要根据不同的变量 来决定引用哪个单元格。 信息函数 ERROR.TYPE 返回对应于错误类型的数字 INFO 返回有关当前操作环境的信息 ISBLANK 如果值为空，则返回 TRUE ISERR 如果值为除 #N/A 以外的任何错误 值，则返回 TRUE ISERROR 如果值为任何错误值，则返回 TRUE ISEVEN 如果数字为偶数，则返回 TRUE ISLOGICAL 如果值为逻辑值，则返回 TRUE 45 信息函数 (2) ISNA 如果值为错误值 #N/A，则返回 TRUE ISNONTEXT 如果值不是文本，则返回 TRUE ISNUMBER 如果值为数字，则返回 TRUE ISODD 如果数字为奇数，则返回 TRUE ISREF 如果值为引用值，则返回 TRUE ISTEXT 如果值为文本，则返回 TRUE 46 规划求解 47 概述 规划求解加载宏是一个Excel 加载项程序 ，用于对一个方程式进行试探式的求解。它 是“单变量求解 ”功能的加强版。 对于难以推导反函数的方程，可以以此工具 求解。 如果没有这个功能，请在“加载宏 ”菜单里增 加勾选 “规划求解 ”加载宏。 此工具的限制 利用穷举法求值，只能得到单一解