商场营运工作必会的EXCEL函数公式与营运实际操作案例，个个都很实用，一定要注意收藏哦！ by杨叫兽
叫兽开场白
EXCEL中的函数很多，功能也非常强大，如能掌握一些常用的函数，将给日常的营运数据处理带来很大的便利，帮助节省时间，提高工作效率。
下面，杨叫兽通过实际案例为各位营运小伙伴介绍一些非常实用的Excel技巧和公式，希望对大家有所帮助，让大家一秒变工作小能手！
——（注：本文所有数据均为假设虚拟数据）
★案例——计算商铺销售完成率
如下图，达到目标销售额就写完成，未达到则列出差额，公式为：
=IF(D2>C2,'完成',D2-C2)
★案例——可能负值的完成率计算
如下图，根据预算和实际，计算完成率，公式为：
=IF(B3<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,'<>
★案例——统计符合多条件的商铺数量
=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
V=Vertical 垂直 即列 | H=Horizontal水平 即行 |
★案例——找出对应的上月销售额
如下图，有本月（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你丫是故意的吗？
编辑：营运部杨琪琪
联系客服
