商场营运工作必会的EXCEL函数公式与营运实际操作案例,个个都很实用,一定要注意收藏哦! by杨叫兽 叫兽开场白 EXCEL中的函数很多,功能也非常强大,如能掌握一些常用的函数,将给日常的营运数据处理带来很大的便利,帮助节省时间,提高工作效率。 下面,杨叫兽通过实际案例为各位营运小伙伴介绍一些非常实用的Excel技巧和公式,希望对大家有所帮助,让大家一秒变工作小能手! ——(注:本文所有数据均为假设虚拟数据) ★案例——计算商铺销售完成率 如下图,达到目标销售额就写完成,未达到则列出差额,公式为: =IF(D2>C2,'完成',D2-C2) ★案例——可能负值的完成率计算 如下图,根据预算和实际,计算完成率,公式为: =IF(B3<0,2-c3>0,2-c3> (*关注上述预算是负值的完成率计算结果不一样) ★案例——可能负值的完成率计算 如下图,根据2016年和2015年,计算同比增长率,公式为: =(B3-C3)/IF(C3>0,C3,-C3) (*关注上述2015年是负值的同比增长率计算结果不一样。) ★案例——处理公式产生的错误值 如下图,处理同比增长率出现错误的单元格,如果是错误值则显示为空,否则正常显示。 把错误值显示为空,公式为: =IFERROR(C3/D3-1,'') 把错误值显示为“新开业”,公式为: =IFERROR(C3/D3-1,'新开业') 说明:两个条件同时成立用AND,任一个成立用OR函数。 ★案例——判断销售额在一个区间: 如下图,判断销售额在100万-150万之间的商铺,显示“是”与“否”,公式为: =IF(AND(D2>1000000,D2<>是','否') 案例延伸: 同样是上图,判断业态是餐饮且销售额大于100万,公式为: =IF(AND(D2>1000000,C2='餐饮'),'是','否') 判断业态是餐饮或快时尚,公式为: =IF(OR(C2='餐饮',C2='快时尚'),'是','否') 判断业态是餐饮或快时尚,且销售额大于100万,公式为: =IF(and(OR(C2='餐饮',C2='快时尚'),D2>1000000),'是','否') 重点关注以下第6)项与第7)项,多个工作表求和和合并单元格求和: Sum主要有以下6种用法: 1)对数字求和: =sum(1,2,3,4) 2)对几个单元格求和: =sum(A2,C4,B3,B4,D2) 3)对连续单元格求和: =sum(A2:A6) 4)对列或行求和: =sum(A:A) =sum(6:6) 5)对区域求和 =sum(A2:F6) =sum(A2:F6,C2:G6) 6)多个工作表求和 如下图,假设各个sheet的格式一致,每个sheet 代表一个楼层,且该楼层的总销售额都在C1单元格,要求各楼层的总和,公式为: =SUM(负一层:四层!C1)
7)合并单元格求和 如下图所示,要求在D列对C列的类别求和: =SUM(C2:C$10)-SUM(D3:D$10) 公式输入方法:先选取D2:D10,在编辑栏中输入上述公式,再按ctrl+enter完成批量输入。 ★案例——求各业态的销售额 如下图,已知各商铺的销售额,要对各业态的销售额求和,公式为: =SUMIF(C$2:C$8,F2,D$2:D$8) 案例延伸: ——同是上图,假设商铺号01-01中的前面两位数是楼层号,已知各商铺号,要对1层的商铺销售额进行求和,公式为: =SUMIF(B2:B8,'01*',E2:E8) ——同是上图,计算商铺名称为三个字的销售额之和,公式为: =SUMIF(B2:B8,'???',E2:E8)s ——注:”*”和”?”都属于通配符: *可以代表任何文字或字符(任意个数) 仅代表单个文字或字符 ★案例——隔列求和 如下图,已知各商铺的每月实际和目标销售额,要隔列求和,计算第一季度的合计公式为: =SUMIF($C$2:$H$2,I$2,$C3:$H3) (注:隔列的标题必须完全一致) ★案例——求多种条件下(如各楼层、各业态等)的销售额之和 如下图,已知各商铺的销售额,要对各楼层各业态的销售额求和,公式为: =SUMIFS(E$2:E$8,A$2:A$8,G2,D$2:D$8,H2) 案例延伸:(如上图) ——统计”除快时尚以外”的销售额之和,公式为: =SUMIF(D2:D8,'<>快时尚',E2:E8) ——也可以去掉行号,写成整列引用,但必须前后一致: =SUMIF(D:D,'<>快时尚',E:E) ——统计”销售额大于100万”的销售额之和,公式为: =SUMIF(E2:E8,'>1000000',E2:E8) ——sumifs可以用于无限个条件,语法为: SUMIFS(统计区域,第一条件区域,条件,[第二条件区域,第二条件....]) Sumif与Sumifs易错点解析: 1)sumifs与sumif语法格式几乎是相反的。 Sumif的统计区域在最后,Sumifs的统计区域在最前面。 2)要搞清楚绝对引用和相对引用,导致下拉公式时,需要固定的数据区域发生了变化; 3)原始表格的条件区域表格要规范(不能有时是“服饰”,有时是“普通服饰“,必须严格一致) ★案例——求各业态的销售额 如下图,已知各商铺的面积和租金单价,要对总租金求和,公式为: =SUMPRODUCT(D2:D8,E2:E8) 案例延伸:sumproduct函数也可以用于多条件求和,和多条件计数,但是数据量非常大时运行速度较慢,所以不推荐使用。 运行速度最快请用: 多条件求和——sumifs, 多条件计数——countifs. 【Count/Counta/Countblank】简单统计 如下图所示,三个函数的不同功能和结果: ★案例——统计符合单一条件的商铺数量 如下图所示,用countifs做相关统计: 计算纯保底的公式是: (注:G3单元格=”纯保底”) 如上图,延伸计算: 销售额大于100万的商铺数: =COUNTIF(D$2:D$19,'>1000000') 销售额小于等于75万的商铺数 =COUNTIF(D$2:D$19,'<> 销售额大于75万且小于100万的商铺数 =COUNTIF(D$2:D$19,'<><> 在上述countif的案例中,销售额大于75万且小于100万的商铺数 也可以用countifs(多条件求和)来处理: =COUNTIFS(D$2:D$19,'>750000',D$2:D$19,'<> ★案例——统计符合多条件的商铺数量 统计餐饮业态销售额大于50万的商铺数量: =countifs(C2:C19,'餐饮”,E2:E19, '>500000”) ——countifs可以用于无限个条件,语法为: countifs(第一条件区域,条件,[第二条件区域,第二条件....]) ★案例——用average时要注意空值与0值对结果的影响。 通过上图结果(销售额为0和空值在此处的情况是一样的,均为商铺未开业),空值也数值为0的单元格,会影响average的计算,空值不参加平均计算,而0值会参加平均。 此处正确公式应为: =SUM(B2:D2)/COUNTIF(B2:D2,'>0') ★案例——用averageifs计算工作日、节假日平均 如上图,计算工作日的日均公式是: =AVERAGEIF(B2:B15,'工作日',C2:C15) ——AVERAGEIF可以用于无限个条件,语法为: AVERAGEIF (统计区域,第一条件区域,条件,[第二条件区域,第二条件....]) ——AVERAGE与AVERAGEIF语法格式几乎是相反的。 【Rank】 ★案例——商铺销售额排名 如上图,已知各商铺销售额,对其进行销售排名,公式为: =RANK(D2,D$2:D$19,0) Rank函数易错点解析: 1)要搞清楚绝对引用和相对引用,导致下拉公式时,需要固定的数据区域发生了变化; 2)后面的数字0代表降序,如果改成1则为升序。 3)函数RANK对重复数值的排位相同。但重复数的存在将影响后续数值的排位。如在一列整数中,若整数60出现两次,其排位为5,则61的排位为7(没有排位为6的数值)。 案例延伸:如果是多条件的排名,用的是 countifs,统计比数值大的商铺有多少个,再+1 。 如下图,即公式为: =COUNTIFS(C$2:C$19,C2,D$2:D$19,'>'&D2)+1 两者与average的语法相同。Max找出最大值,Min找出最小值。 ★案例——找出商铺的最大的月销售额数值(或最小值),公式为: =MAX(B2:D2) (最小值为:=MIN(B2:D2)) ★案例——找出前三名的销售额 如下图,已知各商铺明细,找出前三名销售额的公式为: =LARGE(D$2:D$19,F2) 如果改成后三名的公式为: =SMALL(D$2:D$19,F2) 语法:LOOKUP(要查找的数值,查找区域,结果区域) 要点: 这些数值必须按升序排列:...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE;否则,函数 LOOKUP 不能返回正确的结果。 通常情况下,最好使用函数 HLOOKUP 或函数 VLOOKUP 来替代函数 LOOKUP
★案例——找出对应的上月销售额 如下图,有本月(2月)和上月(1月)两个sheet,但两者商铺列表不一致,需要把1月销售额对应到2月的表上: 公式为:=VLOOKUP(B2,上月!B:C,2,0) Vlookup函数易错点解析: 以上面公式为例:=VLOOKUP(B2,上月!B:C,2,0) (注:”上月!”是指跨表之间引用;如果是同一个表引用可以省略 ) 1)第一个参数“B2”,是查找对象; 2)第二个区域是查找区域,查找的对象必须在查找区域对应的第一列(即上月sheet的B列)。如果在查找区域里没有找到B2,则会返回错误“#N/A”; 3)第三个参数“2“,指的是返回查找区域里相应的第“2”列。查找区域必须至少包括2列,可以多,但不可以少,例如可以写成”B:Z“,但不可以写成”B:B。如果区域设置错误,会返回错误“#REF!”; 4)第四个参数“0“,表示精确查找,为1或省略时表示模糊查找。如果忘了设置第4个参数则会被公式误以为是故意省略,按模糊查找进行。当区域也不符合模糊查找规则时,公式就会返回错误值; 5)要搞清楚绝对引用和相对引用,导致下拉公式时,需要固定的数据区域发生了变化。 Hlookup的语法和Vlookup的语法是一致的。具体用法及注意事项说明请参考Vlookup。 ★案例——通过行去查找 以下图为例,通过行去查找管理费单价,公式为: =HLOOKUP(B2,F$1:J$2,2,0) Vlookup、Hlookup只能应用于列与列、行与行之间查找(只能从左到右,从上到下),如果要列+行纵横查找或反向查找(从右到左,从下到上),就必须要用到【Index+match】。 ★案例——Index,Match分别的用法 (如上图的数据案例,以下是公式功能解释:) 语法:index(区域,第几行,第几列) 语法:match(目标值,查找区域,0) 注:这里的0指的是精确查找。 ★案例——Index+Match Index和Match单用的意义不大,要配合起来用才会彰显价值。 说明:利用MATCH函数查找位置,用INDEX函数取值。 如下图,多条件查找商铺销售额,公式为: =INDEX(B3:E8,MATCH(C12,A3:A8,0),MATCH(A12,B2:E2,0)) (注:通过Match找到相应的行号和列号) ★案例——Index+Match进阶多条件查找 如下图,通过多条件去查找,需要用以数组公式: =INDEX(C3:F8,MATCH(C12&D12,A3:A8&B3:B8,0),MATCH(A12,C2:F2,0)) 由于公式中含有数组运算(一组数同另一组数同时运算),所以公式需要按ctrl+shift+enter三键完成输入。 1. 【ABS】取绝对值 =ABS(数字) 2. 【INT】取整 =INT(数字) 3. 【Round】四舍五入 =ROUND(数字,小数位数) 上述三个函数的例子如下—— 1)日期有固定的输入模式,以下都可以: 2016/1/1 2016年1月1日 2016-1-1 1/1或1-1或1月1日(这时是如果不输年份,是默认为当前年份) 更多日期格式可以参考—— 2)月份显示也是用日期表示,默认是每月1日。
3)日期和时间的相关函数 语法:DATE(year,month,day) 语法:TIME(hour,minute,second) 参数:Hour是0到23之间的数,代表小时;Minute是0到59之间的数,代表分;Second是0到59之间的数,代表秒。 实例:公式“=TIME(12,10,30)”返回序列号0.51,等价于12:10:30 PM。=TIME(9,30,10)返回序列号0.40,等价于9:30:10AM。=TEXT(TIME(23,18,14),'h:mm:ss AM/PM')返回“11:18:14 PM”。 =DATEDIF(开始日期,结束日期,返回什么结果) 第三参数写成'm',就是计算两个日期之间的整月数。 第三参数写成'Y',就是计算两个日期之间的整年数,这个在计算工龄的时候经常用到的。 下面这几种写法,用到的比较少,大家简单了解一下就可以了。 第三参数写成“MD”,返回日期中天数的差。忽略日期中的月和年。 第三参数写成“YM”,返回日期中月数的差。忽略日期中的日和年。 第三参数写成“YD”,日期中天数的差。忽略日期中的年。 常用的文本函数如下: 【LEN】 LEN(text) 返回字符串中的字符数 【TEXT】 TEXT(value,format_text)将一数值转换为按指定数字格式表示的文本。 【MID】 MID(text,start_num,num_chars) 返回字符串中从指定位置开始的特定数目的字符 【RIGHT】 RIGHT(text,num_chars) 根据指定的字符数返回文本串中最后一个或多个字符 【LEFT】 LEFT(text,num_chars) 基于所指定的字符数返回文本串中的第一个或前几个字符。 【Find】 FIND(find_text,within_text,start_num)按指定的小数位数进行四舍五入,利用句点和逗号,以小数格式对该数设置格式,并以文字串形式返回结果。 【Value】 VALUE(text) 将代表数字的文字串转换成数字 ★案例——通过身份证号提取营业员生日 =--TEXT(MID(B2,7,8),'0-00-00') 公式解释:MID(B2,7,8)的意思是提取从B2单元第7位开始往后的8位数字,即19871209,TEXT函数前的两个减号,负负为正的意思,被称为减负运算。 然后用TEXT函数把数据转换成1987-12-09 ★案例——文本函数制作会议时间议程表 功能:只要修改任意时长或开始时间,后面的时间全部自动生成。 结束时间=开始时间+时长 下一阶段的开始时间=上一阶段的结束时间 时间的整体显示公式如下: =TEXT(A4,'H:MM')&'-'&TEXT(C4,'H:MM') 总结前面的七大部分函数如下,方便各位小伙伴按需查找: 附——函数报错的类型: 【最后,关于EXCEL的函数应用】 理解:因为函数是英文,所以英文转化为汉语,汉语转化为真实含义,都是一个过程。理解之后,使用起来就会得心应手。 运用:许多函数忘记了,是因为基本用不上,把好用的函数用起来,后面就会越用越上瘾。 目标:是偷懒,偷懒的心,其实是前期设置好,后期自动化。 心法:是解决问题的总思路。这个学会了,所有的方法技巧,都可以随便就能拿过来。 希望大家可以灵活运用以上套路,提高工作效率,告别不必要的加班~~~ 最后,EXCEL祝大家光棍节快乐~~~~~~ EXCEL你丫是故意的吗? 编辑:营运部杨琪琪 |
|