每天推送商业地产干货，喂饱你！
导读：商场营运工作必会的EXCEL函数公式与营运实际操作案例，个个都很实用，一定要注意收藏哦！
EXCEL中的函数很多，功能也非常强大，如能掌握一些常用的函数，将给日常的营运数据处理带来很大的便利，帮助节省时间，提高工作效率。
下面，通过实际案例为各位营运小伙伴介绍一些非常实用的Excel技巧和公式，希望对大家有所帮助，让大家一秒变工作小能手！（注：本文所有数据均为假设虚拟数据）
判 | 断 | 公 | 式
1
【IF】单条件判断
★案例—计算商铺销售完成率
如下图，达到目标销售额就写完成，未达到则列出差额，公式为：
=IF(D2>C2,”完成”,D2-C2)
★案例—可能负值的完成率计算
如下图，根据预算和实际，计算完成率，公式为：
=IF(B3
（*关注上述预算是负值的完成率计算结果不一样）
★案例—可能负值的完成率计算
如下图，根据2016年和2015年，计算同比增长率，公式为：
=(B3-C3)/IF(C3>0,C3,-C3)
（*关注上述2015年是负值的同比增长率计算结果不一样。）
2
【Iferror】处理错误值
★案例—处理公式产生的错误值
如下图，处理同比增长率出现错误的单元格，如果是错误值则显示为空，否则正常显示。
把错误值显示为空，公式为：
=IFERROR(C3/D3-1,””)
把错误值显示为“新开业”，公式为：
=IFERROR(C3/D3-1,”新开业”)
3
【IF、AND、OR】，多条件判断
说明：两个条件同时成立用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),”是”,”否”)
求 | 和 | 公 | 式
1
【Sum】
重点关注以下第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)
（注：保证合计单元格在各个sheet的位置要完全一致）
7）合并单元格求和
如下图所示，要求在D列对C列的类别求和：
=SUM(C2:C$10)-SUM(D3:D$10)
公式输入方法：先选取D2:D10,在编辑栏中输入上述公式，再按ctrl+enter完成批量输入。
2
【Sumif】单条件求和
★案例—求各业态的销售额
如下图，已知各商铺的销售额，要对各业态的销售额求和，公式为：
=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)
（注：隔列的标题必须完全一致）
3
【Sumifs】多条件求和
★案例—求多种条件下（如各楼层、各业态等）的销售额之和
如下图，已知各商铺的销售额，要对各楼层各业态的销售额求和，公式为：
=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）原始表格的条件区域表格要规范（不能有时是“服饰”，有时是“普通服饰“，必须严格一致）
4
【Sumproduct】乘积
★案例—求各业态的销售额
如下图，已知各商铺的面积和租金单价，要对总租金求和，公式为：
=SUMPRODUCT(D2:D8,E2:E8)
案例延伸：
sumproduct函数也可以用于多条件求和，和多条件计数，但是数据量非常大时运行速度较慢，所以不推荐使用。
运行速度最快请用：
多条件求和——sumifs,
多条件计数——countifs.
统 | 计 | 公 | 式
1
【Count/Counta/Countblank】简单统计
如下图所示，三个函数的不同功能和结果：
2
【COUNTIF】单条件的统计
★案例—统计符合单一条件的商铺数量
如下图所示，用countifs做相关统计：
计算纯保底的公式是：
（注：G3单元格=”纯保底”）
如上图，延伸计算：
销售额大于100万的商铺数：
=COUNTIF(D$2:D$19,”>1000000″)
销售额小于等于75万的商铺数
=COUNTIF(D$2:D$19,”0″)
★案例—用averageifs计算工作日、节假日平均
如上图，计算工作日的日均公式是：
=AVERAGEIF(B2:B15,”工作日”,C2:C15)
—AVERAGEIF可以用于无限个条件，语法为：
AVERAGEIF (统计区域，第一条件区域，条件，[第二条件区域，第二条件….])
—AVERAGE与AVERAGEIF语法格式几乎是相反的。
5
【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
6
【Max、Min】
两者与average的语法相同。Max找出最大值，Min找出最小值。
★案例—找出商铺的最大的月销售额数值（或最小值）
公式为：
=MAX(B2:D2) （最小值为：=MIN(B2:D2)）
7
【Large、Small】
★案例—找出前三名的销售额
如下图首码项目，已知各商铺明细，找出前三名销售额的公式为：
=LARGE(D$2:D$19,F2)
如果改成后三名的公式为：
=SMALL(D$2:D$19,F2)
查 | 找 | 与 | 应 | 用
1
Lookup
语法：LOOKUP(要查找的数值,查找区域,结果区域)
要点： 这些数值必须按升序排列：…、-2、-1、0、1、2、…、A-Z、FALSE、TRUE；否则，函数 LOOKUP 不能返回正确的结果。
通常情况下，最好使用函数HLOOKUP或函数VLOOKUP来替代函数LOOKUP
V=Vertical垂直
即列
H=Horizontal水平
即行
2
【Vlookup】
★案例—找出对应的上月销售额
如下图，有本月（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）要搞清楚绝对引用和相对引用，导致下拉公式时，需要固定的数据区域发生了变化。
3
Hlookup
Hlookup的语法和Vlookup的语法是一致的。具体用法及注意事项说明请参考Vlookup。
★案例—通过行去查找
以下图为例，通过行去查找管理费单价，公式为：
=HLOOKUP(B2,F$1:J$2,2,0)
4
【Index+match】双向查找/纵横查找
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
日期计算
1）日期有固定的输入模式，以下都可以：
2016/1/1
2016年1月1日
2016-1-1
1/1或1-1或1月1日(这时是如果不输年份，是默认为当前年份)
