Excel常用函数典型应用示例.xls

ExcelExcel常常用用函函数数典典型型应应用用示示例例 辽宁熙祥集团 信息中心 因为有了函数 Excel的功能变得十分强大 但340多个函数确实令人望而生畏 本文整理出工作中常用的29个函数的37个典型应用 包括函数组合 足以应付大多数工作需求 但即便是这个数量 要熟练掌握 随手输入也不是容易的事 何况企业不是学校 总不能没事就复习一遍 本文使用了类似词典的方式解决这个问题 因此 我们也可以形象地将本文称为 函数词典 我们只要看索引表 就知道这些典型应用都能实现什么功能 再点击需要的典型应用名称 自动链接到对应的实例 把紫色单元格中的函数复制到自己的表单中 再进行必要修改就可以了 表格颜色说明索引表含有公式或函数原始数据或说明原始数据表 一一 数数学学一一索索 引引 表表 1 1 ABSABS分组函数类型函数典型应用 取绝对值 点击可返回索引表 一数学一 ABS取绝对值 数据结果INT向下取整 66TRUNC舍位 6 6 二数学二 ROUND四舍五入 2 2 INTINTROUNDUP向上舍入 向下取整 点击可返回索引表 ROUNDDOWN向下舍入 数据结果 三逻辑 IFERROR错误显示 215 36215IF多条件判断 3 3 TRUNCTRUNCAND逻辑与 舍位 点击可返回索引表 OR逻辑或 说明 不进行四舍五入计算 参数负值表示截断整数位 四日期 TODAY当前日期 数据结果DATEDIF日期间隔 2 153 68972 153 68NETWORKDAYS工作日计算 2 153 68972 100 五数学三 SUM多表相同位置求和 二二 数数学学二二 SUMIF 隔列求和 标题行有规则 1 1 ROUNDROUND单条件求和 四舍五入 点击可返回索引表 单条件模糊求和 数据结果SUMIFS多条件模糊求和 215 364215 4 六统计 COUNT计算数值个数 215 364215 36COUNTIF统计重复内容 2 2 ROUNDUPROUNDUP 七查找一 LOOKUP 查找符合条件的最后一条记录 向上舍入 点击可返回索引表 多区间条件判断 215 364216VLOOKUP纵向单条件查找 215 364215 4HLOOKUP横向单条件查找 215 364215 37 八查找二 INDEX MATCH 双向查找 3 3 ROUNDDOWNROUNDDOWN 向下舍入 点击可返回索引表 九数学四SUMPRODUCT 去掉重复后的单元格内容数量 215 364215单元格内容重复出现次数 215 364215 3单条件求和 215 364215 36多条件求和一 三三 逻逻辑辑多条件求和二 1 1 IFERRORIFERROR多条件求和三 错误显示 点击可返回索引表 隔列求和 标题行没有规则 计划值为 0 显示 无计划 否则显示计算结果 十文本 PHONETIC字符串合并 实际计划完成率 LEFT RIGHT LEN 按位置截取字符串 236734 51242 LEFT RIGHT FIND 截取某字符前 后的字符串 890无计划 95180 IF COUNT FIND 字符串查找 2 2 IFIF 多条件判断 点击可返回索引表 一对多字符串查找 说明 两个条件都成立用AND 任一个成立用OR 补货条件 库存量低于500的本季原料合计分值 库存量本季原料提醒 800是 200非 五数学三SUMIF 300是补货 3 3 ANDAND 逻辑与 点击可返回索引表 说明 两个条件都为真 结果才为真 挑出大于50且小于75的数据 数据结果一结果二 50 00FALSE0 65 00TRUE1 102 00FALSE0 79 00FALSE0 4 4 OROR 逻辑或 点击可返回索引表 说明 两个条件之一为真 结果既为真 挑出小于50或大于75的数据 数据结果一结果二 50 00FALSE0 65 00FALSE0 45 00TRUE1 79 00TRUE1 扩展 逻辑值 1变成数值 FALSE 1 0 TRUE 1 1 四四 日日期期 1 1 TODAYTODAY 当前日期 点击可返回索引表 当前日期自动更新 到期显示 是 未到期显示 否 有效期当前日期是否过期 2017年8月31日2017年9月14日是 2030年12月12日2017年9月14日否 扩展 是 自动显示红色 是通过单元格的条件格式设置的 2 2 DATEDIFDATEDIF 日期间隔 点击可返回索引表 两日期相隔的年 月 天数计算 开始日期结束日期 计算结果 2015年5月4日2017年12月31日 相隔多少天972 相隔多少月31 相隔多少年2 不考虑年 相隔多少月7 不考虑年 相隔多少天241 不考虑年月 相隔多少天27 说明 Y 时间段中的整年数 M 时间段中的整月数 D 时间段中的天数 MD 天数的差 忽略日期中的月和年 YM 月数的差 忽略日期中的日和年 YD 天数的差 忽略日期中的年 3 3 NETWORKDAYSNETWORKDAYS 工作日计算 点击可返回索引表 依照上表中日期 计算扣除周末双休的工作日天数 法定假期天数20 工作日天数675 实际应用中需要扣除的法定假期天数 需要手工输入 五五 数数学学三三 1 1 SUMSUM 多表相同位置求和 点击可返回索引表 公式 B2 SUM Sheet2 Sheet5 B2 说明 上式中B2为示例 不对应本文任何单元格 2 2 SUMIFSUMIF 1 隔列求和 标题行有规则 点击可返回索引表 数据见表1 计算3个月合计计划产量及实际产量表表1 1 产品名称 合计 产品名称 7月8月 实际计划实际计划实际 120鲜香1713120鲜香169 120香辣1715120香辣976 220辣汤2222220辣汤61010 2 单条件求和 点击可返回索引表 数据见表2 计算4个代理商本期要货数量表表2 2 代理商单位要货数量发货日期代理商商品名称单位 鞍山袋4207月29日鞍山120鲜香袋 沈阳袋3007月31日鞍山120香辣袋 营口袋3007月31日沈阳120鲜香袋 大连袋2407月31日营口120鲜香袋 3 单条件模糊求和 点击可返回索引表 7月31日大连120鲜香袋 数据见表2 计算3类产品本期发货数量 注意通配符的位置 8月5日鞍山180鲜香袋 单位发货数量8月5日营口220辣汤袋 鲜香总数袋7808月15日鞍山220清汤袋 120g总数袋9008月15日沈阳180香辣袋 汤料总数袋1808月15日大连120香辣袋 3 3 SUMIFSSUMIFS8月15日营口220辣汤袋 多条件模糊求和 点击可返回索引表 8月28日鞍山120鲜香袋 数据见表2 计算产品名称以 120 开头 又包含 香 字的发货数量 900 六六 统统计计 1 1 COUNTCOUNT 计算数值个数 点击可返回索引表 计算表2对应区域中数值个数 不能转换为数字的文本被忽略 2 2 2 COUNTIFCOUNTIF 统计重复内容 点击可返回索引表 统计下表第二列中与第一列重复的原料数量 姓名姓名查找重复 芝麻酱韭菜花2 酱油味精1 腐乳酱油0 韭菜花芝麻酱1 味精味精2 精盐芝麻酱0 七七 查查找找引引用用一一 1 1 LOOKUPLOOKUP 1 查找符合条件的最后一条记录 点击可返回索引表 说明 0 或1 条件 可以把不符合条件的变成错误值 而lookup忽略错误 值后继续向下查找 商品名称检索结果 120鲜香60 120香辣120 180鲜香120 180香辣60 220辣汤90 220清汤60 扩展 加 可使得单元格行号在下拉时保持不变 2 多区间条件判断 点击可返回索引表 按照表3中的标准 对地区市场进行评级表表3 3 地区销售数量评级地区市场评级标准 沈阳90堡垒销售情况评级 大连20加强少于30箱加强 鞍山100堡垒30 59箱维持 抚顺30维持60 89箱重要 本溪60重要90箱以上堡垒 2 2 VLOOKUPVLOOKUP 纵向单条件查找 点击可返回索引表 表4是工作中记录的流水帐 将其数据调入到按规则排列地区顺序的表中表表4 4 地区 销售数量 地区 销售数量 120鲜香120香辣120鲜香120香辣 沈阳240120抚顺12060 大连60180大连60180 鞍山300240鞍山300240 抚顺12060沈阳240120 本溪18060本溪18060 3 3 HLOOKUPHLOOKUP 横向单条件查找 点击可返回索引表 依据表5所示提成标准 查找销售金额对应的提成比例 并计算提成金额表表5 5 姓名销售金额提成比例提成金额提成标准 张三35 000 003 00 1 050 00销售金额1 999910000 1999920000 49999 李四78 000 004 00 3 120 00提成基数1 0010 000 0020 000 00 王二19 500 002 00 390 00提成比例1 00 2 00 3 00 说明 销售金额横向排列要按升序 否则得到的提成比例将是错误的 八八 查查找找引引用用二二 INDEXINDEX与与MATCHMATCH 双向查找 点击可返回索引表 利用MATCH函数精确查找表4中行列对应位置 在INDEX函数定义的区域内取值 地区 销售数量 120鲜香120香辣 沈阳240120 大连60180 鞍山300240 抚顺12060 本溪18060 对比发现 VLOOKUP的定位方式要求列顺序不能变 否则会出现数据错误 而 INDEX与MATCH套用的定位方式对此无要求 这样的功能更能满足我们对V3导出数 据处理的要求 九九 数数学学四四 1 1 SUMPRODUCTSUMPRODUCT 判断相等则返回TRUE 不等则返回FALSE 由此建立一个有逻辑值构成的内存 数组 SUMPRODUCT有一个特性 它会将非数值型的数组元素作为0处理 逻辑值自 然是属于非数值型的数组元素 我们使用 1的方式 把逻辑值转化为数值 TRUE转 化为1 FALSE转化为0 最后统计求和 1 去掉重复后的单元格内容数量 点击可返回索引表 统计表2中要货的代理商数量 用COUNTIF统计出每个代理商的出现次数 用1 除的方式把出现次数变成分母 然后相加 本月要货的代理商数量 4 2 单元格内容重复出现次数 点击可返回索引表 统计表2中各代理商要货的次数 代理商要货次数 鞍山5 沈阳2 营口3 大连2 3 单条件求和 点击可返回索引表 统计表2中各代理商要货的数量 代理商单位要货数量 鞍山袋420 沈阳袋300 营口袋300 大连袋240 4 多条件求和一 点击可返回索引表 统计表2中鞍山8月份要了多少货 代理商单位要货数量 鞍山袋240 对比发现 VLOOKUP的定位方式要求列顺序不能变 否则会出现数据错误 而 INDEX与MATCH套用的定位方式对此无要求 这样的功能更能满足我们对V3导出数 据处理的要求 5 多条件求和二 点击可返回索引表 统计表2中鞍山各品要货数量 代理商产品名称数量 鞍山120鲜香120 鞍山120香辣120 鞍山180鲜香120 鞍山180香辣0 鞍山220清汤60 鞍山220辣汤0 6 多条件求和三 点击可返回索引表 统计表2中8月份120鲜香和120香辣一共发了多少货 月份单位要货数量 8月袋180 7 隔列求和 标题行没有规则 点击可返回索引表 数据见表1 计算3个月合计计划产量及实际产量 产品名称 合计 实际计划 120鲜香1713 120香辣1715 220辣汤2222 十十 文文本本 1 PHONETIC 字符串合并 点击可返回索引表 多单元格字符串合并 只能对字符型内容合并 数字不可以 制作图表时 可以用这个函数将多个分类合并为一个坐标值显示 代理商 商品名称合并 沈阳 120鲜香沈阳 120鲜香 沈阳 120香辣沈阳 120香辣 鞍山 120鲜香鞍山 120鲜香 鞍山 120香辣鞍山 120香辣 鞍山 220清汤鞍山 220清汤 鞍山 220辣汤鞍山 220辣汤 2 LEFT RIGHT与LEN 按位置截取字符串 点击可返回索引表 截取后3位 LEN计算出总长度 LEFT RIGHT从左边 右边截总长度减3个 原字符串120鲜香蘸料 结果120鲜香 3 LEFT RIGHT与FIND 截取某字符前 后的字符串 点击可返回索引表 截取 前的部分 用FIND函数查找位置 用LEFT RIGHT截取 合并一级科目 鞍山 120鲜香120鲜香 鞍山 120香辣120香辣 鞍山 220清汤清汤 鞍山 220辣汤鞍山 4 IF与COUNT与FIND 1 字符串查找 点击可返回索引表 FIND查找成功 返回字符的位置 否则返回错误值 而COUNT可以统计出数字 的个数 这里可以用来判断查找是否成功 地址辽宁城市 辽宁沈阳是 吉林长春否 辽宁鞍山是 辽宁大连是 2 一对多字符串查找 点击可返回索引表 设置FIND第一个参数为常量数组 用COUNT函数统计FIND查找结果 地址地区 辽宁沈阳东北 吉林长春东北 辽宁鞍山东北 辽宁大连东北 山东济南其它 难易系数 0 5 0 5 0 5 0 5 0 5 0 5 0 5 0 5 0 5 0 5 0 5 0 5 0 5 1 1 1 ExcelExcel常常用用函函数数典典型型应应用用示示例例 辽宁熙祥集团 信息中心 因为有了函数 Excel的功能变得十分强大 但340多个函数确实令人望而生畏 本文整理出工作中常用的29个函数的37个典型应用 包括函数组合 足以应付大多数工作需求 但即便是这个数量 要熟练掌握 随手输入