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

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

《Excel常用函数典型应用示例.xlsx》由会员分享，可在线阅读，更多相关《Excel常用函数典型应用示例.xlsx（22页珍藏版）》请在冰豆网上搜索。

ExcelExcel常常用用函函数数典典型型应应用用示示例例辽宁熙祥集团信息中心因为有了函数，Excel的功能变得十分强大，但340多个函数确实令人望而生畏。

本文整理出工作中常用的29个函数的37个典型应用（包括函数组合），足以应付大多数工作需求。

但即便是这个数量，要熟练掌握，随手输入也不是容易的事，何况企业不是学校，总不能没事就复习一遍。

本文使用了类似词典的方式解决这个问题，因此，我们也可以形象地将本文称为“函数词典”。

我们只要看索引表，就知道这些典型应用都能实现什么功能，再点击需要的典型应用名称，自动链接到对应的实例，把紫色单元格中的函数复制到自己的表单中，再进行必要修改就可以了。

表格颜色说明索引表含有公式或函数原始数据或说明原始数据表一一、数数学学一一索索引引表表11、ABSABS分组函数类型函数典型应用取绝对值（点击可返回索引表）一数学一ABS取绝对值数据结果INT向下取整66TRUNC舍位-66二数学二ROUND四舍五入22、INTINTROUNDUP向上舍入向下取整（点击可返回索引表）ROUNDDOWN向下舍入数据结果三逻辑IFERROR错误显示215.36215IF多条件判断33、TRUNCTRUNCAND逻辑与舍位（点击可返回索引表）OR逻辑或说明：

不进行四舍五入计算，参数负值表示截断整数位四日期TODAY当前日期数据结果DATEDIF日期间隔2,153.68972,153.68NETWORKDAYS工作日计算2,153.68972,100五数学三SUM多表相同位置求和二二、数数学学二二SUMIF隔列求和，标题行有规则11、ROUNDROUND单条件求和四舍五入（点击可返回索引表）单条件模糊求和数据结果SUMIFS多条件模糊求和215.364215.4六统计COUNT计算数值个数215.364215.36COUNTIF统计重复内容22、ROUNDUPROUNDUP七查找一LOOKUP查找符合条件的最后一条记录向上舍入（点击可返回索引表）多区间条件判断215.364216VLOOKUP纵向单条件查找215.364215.4HLOOKUP横向单条件查找215.364215.37八查找二INDEXMATCH双向查找33、ROUNDDOWNROUNDDOWN向下舍入（点击可返回索引表）九数学四SUMPRODUCT去掉重复后的单元格内容数量215.364215单元格内容重复出现次数215.364215.3单条件求和215.364215.36多条件求和一三三、逻逻辑辑多条件求和二11、IFERRORIFERROR多条件求和三错误显示（点击可返回索引表）隔列求和，标题行没有规则计划值为0显示无计划，否则显示计算结果。

十文本PHONETIC字符串合并实际计划完成率LEFT/RIGHTLEN按位置截取字符串236734%51242%LEFT/RIGHTFIND截取某字符前/后的字符串890无计划95180%IFCOUNTFIND字符串查找22、IFIF多条件判断（点击可返回索引表）一对多字符串查找说明：

两个条件都成立用AND，任一个成立用OR。

补货条件：

库存量低于500的本季原料合计分值库存量本季原料提醒800是200非300是补货33、ANDAND逻辑与（点击可返回索引表）说明：

两个条件都为真，结果才为真挑出大于50且小于75的数据数据结果一结果二50.00FALSE065.00TRUE1102.00FALSE079.00FALSE044、OROR逻辑或（点击可返回索引表）说明：

两个条件之一为真，结果既为真挑出小于50或大于75的数据数据结果一结果二50.00FALSE065.00FALSE045.00TRUE179.00TRUE1扩展：

逻辑值*1变成数值FALSE*1=0；TRUE*1=1四四、日日期期11、TODAYTODAY当前日期（点击可返回索引表）当前日期自动更新，到期显示是，未到期显示否。

有效期当前日期是否过期2017年8月31日2017年9月14日是2030年12月12日2017年9月14日否扩展：

是自动显示红色，是通过单元格的条件格式设置的。

22、DATEDIFDATEDIF日期间隔（点击可返回索引表）两日期相隔的年、月、天数计算开始日期结束日期计算结果2015年5月4日2017年12月31日相隔多少天972相隔多少月31相隔多少年2不考虑年，相隔多少月7不考虑年，相隔多少天241不考虑年月，相隔多少天27说明：

Y时间段中的整年数。

M时间段中的整月数。

D时间段中的天数。

MD天数的差。

忽略日期中的月和年。

YM月数的差。

忽略日期中的日和年。

YD天数的差。

忽略日期中的年。

33、NETWORKDAYSNETWORKDAYS工作日计算（点击可返回索引表）依照上表中日期，计算扣除周末双休的工作日天数。

法定假期天数20工作日天数675实际应用中需要扣除的法定假期天数，需要手工输入。

五五、数数学学三三11、SUMSUM多表相同位置求和（点击可返回索引表）公式：

B2=SUM（Sheet2:

Sheet5!

B2）说明：

上式中B2为示例，不对应本文任何单元格22、SUMIFSUMIF

（1）隔列求和，标题行有规则（点击可返回索引表）数据见表1，计算3个月合计计划产量及实际产量表表11产品名称合计产品名称7月8月实际计划实际计划实际120鲜香1713120鲜香169120香辣1715120香辣976220辣汤2222220辣汤61010

（2）单条件求和（点击可返回索引表）数据见表2，计算4个代理商本期要货数量表表22代理商单位要货数量发货日期代理商商品名称单位鞍山袋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香辣袋33、SUMIFSSUMIFS8月15日营口220辣汤袋多条件模糊求和（点击可返回索引表）8月28日鞍山120鲜香袋数据见表2，计算产品名称以120开头，又包含香字的发货数量900六六、统统计计11、COUNTCOUNT计算数值个数（点击可返回索引表）计算表2对应区域中数值个数，不能转换为数字的文本被忽略222、COUNTIFCOUNTIF统计重复内容（点击可返回索引表）统计下表第二列中与第一列重复的原料数量。

姓名姓名查找重复芝麻酱韭菜花2酱油味精1腐乳酱油0韭菜花芝麻酱1味精味精2精盐芝麻酱0七七、查查找找引引用用一一11、LOOKUPLOOKUP

（1）查找符合条件的最后一条记录（点击可返回索引表）说明：

0（或1）/（条件）可以把不符合条件的变成错误值，而lookup忽略错误值后继续向下查找。

商品名称检索结果120鲜香60120香辣120180鲜香120180香辣60220辣汤90220清汤60扩展：

加$可使得单元格行号在下拉时保持不变。

（2）多区间条件判断（点击可返回索引表）按照表3中的标准，对地区市场进行评级表表33地区销售数量评级地区市场评级标准沈阳90堡垒销售情况评级大连20加强少于30箱加强鞍山100堡垒30-59箱维持抚顺30维持60-89箱重要本溪60重要90箱以上堡垒22、VLOOKUPVLOOKUP纵向单条件查找（点击可返回索引表）表4是工作中记录的流水帐，将其数据调入到按规则排列地区顺序的表中表表44地区销售数量地区销售数量120鲜香120香辣120鲜香120香辣沈阳240120抚顺12060大连60180大连60180鞍山300240鞍山300240抚顺12060沈阳240120本溪18060本溪1806033、HLOOKUPHLOOKUP横向单条件查找（点击可返回索引表）依据表5所示提成标准，查找销售金额对应的提成比例，并计算提成金额表表55姓名销售金额提成比例提成金额提成标准张三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导出数据处理的要求。

九九、数数学学四四11、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鲜香1713120香辣1715220辣汤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与COU