Excel函数应用集_第五版_网络版.xls

使用说明NO 实例作用描述网络 压缩软件下载1按日期求和 2AND 和 OR 示例 3计算间隔行数 4自动取得工作表名 并超级链接 5省 市 区分离提取 6条件格式简单示例 7从前 从后提取示例 8查找单元格中含有內容 返回指定值 9按照指定规则进行分配座位 10判断最新的日期是否已过期 11按指定日期区间求和 12VLOOKUP 双条件多条件查找 13VLOOKUP 跨表查詢 14INDEX OFFSET INDIRECT 结合使用 15INDEX 求和 16OFFSET 函数应用 17SUMIF 多条件求和 18TEXT 函数应用技巧 19每月工作日期 20跨表求和 21多表 动态 查询 22颜色管控时间 23行转列 24多条件查找及求和 25查找单元格含有指定字符 26多种查询函数的使用 27多列查找 并返回行号 28不重复值 跨表多区 29查找符合两项条件的交差值 30查找成绩前十名人员 31查找 筛选 提取 32查找两条件对应的值 33SUMPRODUCT应用实例 34提取非空数据 35多条件判断核对 36工程项目分级考核人员分析表 37一列数据转到行 38最相近的数值 39统计工作日上班时间 40条件求和 41合并表 42另类查询 43多级相乘 44提取 查找 45分配到单元格 46计算单元格符号的个数 47计算同一日期的不重复人员数 48查找同一日期的人员清单 49看不见的空值 50不重复值 多行多列筛选 51日期函数应用实例 52几个常用的日期与时间函数 53数字与中文金额互转 54跨表查询示例 55列出工作薄中的所有表 表名无规律 56Excel取不重复值 57返回最下面一个值 58双条件不重复值 59查找重复字符在单元格的位置 60学生成绩分析 61排名示例 62两列合并 63单条件多条件查找取值 64HLOOKUP 实例 65Lookup 实例 66CHOOSE 67工资打印套表 68Excel中玩转邮件合并 69指定填充排列下去 70字母 例 B 3 AAA 等于绝对引用 B 3 AAA 向下引用也是 B 3 2绝对列和相对行 例 B3 AAAAAA 绝对列和相对行引用 B4 向下可以引用 向右则不行 BBB 3绝对行和相对列 例 B 3 AAA111 绝对行和相对列引用C 3 向右可以引用 向下则不行 AAA 4相对列和相对行 例 B3 AAA111 相对列引用C4 向右向下都可以引用 BBB222 5跨工作表引用 引用 B25 跨工作表引用 6跨工作簿引用 例 Book2 xls Sheet1 A 1 工作簿 xls 工作表名 单元格地址 提提示示 按按F4F4可可以以在在变变换换引引用用方方式式 什什么么是是数数组组公公式式 数组公式在函数中使用非常多 体现形式 SUM C 5 C 7 F6 1 数组公式 需要同时按 Shint Ctrl EnterShint Ctrl Enter 才能得出结果 姓名性別數量性別人數數量 刘少青男10男110 江红英女15女235 孙倩倩女20 李永高男15 返返回回 错误 正确 什什么么是是多多单单元元格格数数组组公公式式 针对一或多组值执行多个计算 然后传回单一结果或多个结果的公式 数组公式 需要同时按 Shint Ctrl EnterShint Ctrl Enter 才能得出结果 编号姓名 1刘少青编号姓名 2江红英1刘少青 3孙倩倩3孙倩倩 4杨孝平5李永高 5李永高 1 选中需要数组公式的区域 输入公式 同时按 Shint Ctrl Enter 结束 2 要修改区域数组公式 先清除 然后写入新的公式 同时按 Shint Ctrl Enter 结束 不然会出现以下情况 3 清除多单元格数组公式方法如下 方法一 选定某含公式的一格 按下面三步就取消了多单元格数组公式 修改后重新选定区域输入 a 按 Ctrl b 按 F2 c 按 Ctrl Enter 方法二 选择其中的一个单元格在编辑栏把内容全部删除 按ctrl shift enter就把该数组公式都删除掉了 返返回回 2 要修改区域数组公式 先清除 然后写入新的公式 同时按 Shint Ctrl Enter 结束 不然会出现以下情况 选定某含公式的一格 按下面三步就取消了多单元格数组公式 修改后重新选定区域输入 选择其中的一个单元格在编辑栏把内容全部删除 按ctrl shift enter就把该数组公式都删除掉了 工工作作表表打打开开不不能能计计算算的的问问题题 经常出现表格里的公式突然不能计算 或者要回车才能得出结果 解决 打开工具 选项 重新计算 把自动计算点上 返返回回 常常见见问问题题及及解解决决 返返回回 1 不想公式的结果出现 NA 该怎么办呢 当在函数或公式中没有可用数值时 将产生错误值 N A 可以使用isna函数处理 当内容找不到时显示空白 姓名性別姓名性別 刘少青男刘少青男 女江红英 N A 男李永高 IF ISNA VLOOKUP D9 A7 B9 2 0 VLOOKUP D9 A7 B9 2 0 2 怎样不显示像 N A VALUE REF DIV 0 NUM NAME 或 NULL 这样的字 姓名性別姓名处理前处理后 刘少青10刘少青 VALUE IF ISERROR E16 B16 E16 B16 江红英1A江红英 VALUE IF ISERROR SUM A16 A18 D17 B16 B18 李永高15李永高 DIV 0 IF ISERROR SUM B18 15 0 SUM B18 15 0 3 这样的错误 如果单元格所含的数字 日期或时间比单元格宽 或者单元格的日期时间公式产生了一个负值 就会产生 这个看起来比较简单 大家应该都了解吧 解决方法 如果单元格所含的数字 日期或时间比单元格宽 可以通过拖动列表之间的宽度来修改列宽 如果使用的是1900年的日期系统 那么Excel中的日期和时间必须 为正值 如果公式正确 也可以将单元格的格式改为非日期和时间型来显示该值 4 关于空格问题 1 在实际工作中经常碰到从其它文件转过来的数据 如从公司ERP系统导出来的数据 往往中间会有不少空格 有空格 的数据 EXCEL默认的是文本 不能加减计算处理 解决办法 利用EXCEL的查找与替换解决 在查找栏打一空格 替换为空 全部替换 这样所有的空格都会被取消 2 对看起来没有空格的单元格 但实际有空格的问题 很让人困惑 不能计算 不能引用 造成这样的问题原因很多 有的是格式系统认不了 有的是从其它系统做的文件 换一个系统使用就会出现这样的问题 解决办法 开一个文本文件 把怀疑有空格的数据复制到里面粘贴 这时有空格的就会看出来了 保存 然后到EXCEL里面 从数据 导入外部数据 下一步 在分列符号栏选其它中敲一个空格 分列完成就可以了 5 关于工作表打开不能计算的问题 1 经常出现表格里的公式突然不能计算 或者要回车才能得出结果 解决办法 打开工具 选项 重新计算 把自动计算点上 SUBTOTALSUBTOTAL示示例例 返返回回 功功能能 返回数据清单或数据库中的分类汇总 通常 使用 数据 菜单中的 分类汇总 命令 可以容易地创建带有分类汇总的数据清单 一旦创建了分类汇总 就可以通过编辑 SUBTOTAL 函数对该数据清单进行修改 语语法法 SUBTOTAL function num ref1 ref2 Function num 为 1 到 11 之间的数字 指定使用何种函数在数据清单中进行分类汇总计算 Function Num函数 1AVERAGE返回参数的平均值 2COUNT返回包含数字以及包含参数列表中的数字的单元格的个数 3COUNTA返回参数列表中非空值的单元格个数 4MAX返回一组值中的最大值 5MIN返回一组值中的最小值 6PRODUCT将所有以参数形式给出的数字相乘 并返回乘积值 7STDEV 8STDEVP 9SUM返回某一单元格区域中所有数字之和 10VAR 11VARP Ref1 ref2 为要进行分类汇总计算的 1 到 29 个区域或引用 示示例例一一 名称金额 名称金额自动编号8127 00自动计算 邓文1199 71 SUBTOTAL 3 B 26 B26 1 黄珍1113 72 筛选时 F25单元自 动填上 姓名 筛选时 自动编号 向华1027 73 邓文941 74 黄珍855 75 向华769 76 邓文683 77 黄珍597 78 向华511 79 邓文425 710 示示例例二二 机台班组名称数量 返回包含数字以及包含参数列表中的数字的单元格的个数 1301B313 SUBTOTAL 2 OFFSET B40 COUNTA B 40 B 52 2501B3 3301B3名称数量 返回某一单元格区域中所有数字之和 忽略由数据清单筛选时产生的任何隐藏行 4401B44913 SUBTOTAL 9 B 40 B 52 5201B4 6501A2名称数量 返回包含数字以及包含参数列表中的数字的单元格的个数 7501A24012 COUNTIF OFFSET B40 COUNTA B 40 B 52 E46 8301B3 9401B3 10301B3 11501B2 12201B4 13501B4 返回某一单元格区域中所有数字之和 忽略由数据清单筛选时产生的任何隐藏行 SUMPRODUCTSUMPRODUCT简简单单示示例例 返返回回 功功能能 在给定的几组数组中 将数组间对应的元素相乘 并返回乘积之和 语语法法 SUMPRODUCT array1 array2 array3 Array1 array2 array3 为 2 到 30 个数组 其相应元素需要进行相乘并求和 说明数组参数必须具有相同的维数 否则 函数 SUMPRODUCT 将返回错误值 VALUE 函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理 大小产产品品50M50M150M150M200M200M Mysql60120180 空间100200300高级应用 折率一年9 8 折9 5 折9 折 二年8 8 折8 5 折8 折 三年7 8 折7 5 折7 折 1 计算三种50M 150M 200M空间 一年折率的和 558 SUMPRODUCT D11 F11 D14 F14 10 558558 100 0 98 200 0 95 300 0 9 2 计算Mysql 空间各三种50M 150M 200M 二年 三年折的和 736 8 SUMPRODUCT D10 F11 D14 F15 10 736 8736 8 60 0 88 100 0 78 120 0 85 200 0 75 180 0 8 300 0 7 3 组合查找计算 大大小小产产品品折折率率金金额额 50M50MMysqlMysql二二年年6060 SUMPRODUCT 多条件求和 代码分数 A30 求A C E的和 A70 B20 C30 D15 E10 四四舍舍五五入入 返返回回 返回数字按指定位数取整后的数字 公式结果 3 23 ROUND B6 0 将 3 2 向上舍入 小数位为 0 76 5876 6 ROUND B7 1 将 76 58 向上舍入 小数位为 1 3 141593 142 ROUND B8 3 将 3 14159 向上舍入 保留三位小数 ROUND模型 向向上上舍舍入入 返返回回 远离零值 向上舍入数字 公式结果 3 24 ROUNDUP B6 0 将 3 2 向上舍入 小数位为 0 76 5876 6 ROUNDUP B7 1 将 76 58 向上舍入 小数位为 1 3 141593 142 ROUNDUP B8 3 将 3 14159 向上舍入 保留三位小数 向向下下舍舍入入 返返回回 靠近零值 向下 绝对值减小的方向 舍入数字 公式结果 3 23 ROUNDDOWN B6 0 将 3 2 向下舍入 小数位为 0 76 5876 5 ROUNDDOWN B7 1 将 76 58 向下舍入 小数位为 1 3 141593 141 ROUNDDOWN B8 3 将 3 14159 向下舍入 保留三位小数 指指定定向向上上进进位位数数进进入入 返返回回 数值进位数结果 2 113 CEILING B6 C6 将 2 1 向上按进位数进入 结果是3 1 522 1 933 202121 CEILING B9 C9 将 21 向上按进位数进入 结果是21 253030 403060 单单元元格格数数量量的的统统计计 返返回回 COUNT COUNTA 计算含有数字的储存格数量 代理报价 NIPPON500结果 NIPPON无数值4 COUNT B6 C11 YUSEN455 YUSEN430混合12 COUNTA B6 C11 近铁无 捷扬480有报价的单元格 COUNTBLANK 计算指定范围内空白储存格的个数 结果 0 COUNTBLANK B6 C11 COUNTIF高级应用 计算区域中满足给定条件的单元格的个数 代理结果 YUSEN 2 COUNTIF B6 C11 B23 报价大于450的数量 结果 3 COUNTIF B6 C11 450 计计算算文文本本字字符符串串中中的的字字符符数数 返返回回 LEN LENB 代理结果 NIPPONLEN6 近铁LEN2 YUSENLENB5 康捷空LENB6 汉字占两个字符 英文字母占一个字符 计计算算一一个个数数的的绝绝对对值值 返返回回 ABS 这个函数是用来计算一个数的绝对值 省略正负数符号 数字绝对值 1010 ABS B6 1010 1 251 25 1 251 25 实实例例 表格1是原始的计算方法 由于出现了负数 使得计算出的百分率产生错误 不管测量结果与要求的数是长了或是短了 这个差距都应该以一个绝对值表示 表1试验分组需要的长度实际长度差异误差百分率 试验112012000 试验2120903025 试验3120150 30 25 C17 D17 表格2用同样的数据 但使用 ABS 函数纠正了计算结果 表2试验分组需要的长度实际长度差异误差百分率 试验112012000 试验2120903025 试验31201503025 ABS C23 D23 判判断断是是否否正正确确 返返回回指指定定值值 返返回回 IFIF 评定成绩 姓名分数成绩判定 小王100太好了 IF C5 90 太好了 IF C5 80 可以了 IF C5 70 差不多 IF C5 60 刚好 IF C590 太好了 IF C5 80 可以了 IF C5 70 差不多 IF C5 60 刚好 IF C5 80 D4 80 邓红艳6075双科是否达70以上否否 IF AND C5 70 D5 70 是 否 朱鸿燕7675双科是否超过平均分是是 IF AND C6 C9 D6 D9 是 否 杜莲7560否否 平均分74 7574 OR 与 AND 的区别 OR 只要有一个为TRUE 就返回TRUE AND 全部为TRUE 才返回TRUE 检检测测两两个个或或多多个个条条件件值值 并并返返回回TRUETRUE或或FALSE FALSE 返返回回 姓名语文数学结果 朱洁8886双科是否达85以上TRUETRUE OR C4 85 D4 85 邓红艳6075双科是否达70以上是是 IF OR C5 70 D5 70 是 否 朱鸿燕6572双科是否超过平均分否否 IF OR C6 C9 D6 D9 是 否 杜莲6860否否 平均分70 2573