一、判断公式
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】
★案例——商铺销售额排名
如上图，已知各商铺销售额，对其进行销售排名excel公式中$代表什么意思，公式为：
=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】
★案例——找出前三名的销售额
如下图excel公式中$代表什么意思，已知各商铺明细，找出前三名销售额的公式为：
=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三键完成输入。
五、数字处理
=ABS(数字)
=INT(数字)
=ROUND(数字,小数位数)
上述三个函数的例子如下——
六、日期与时间
1
日期计算
1）日期有固定的输入模式，以下都可以：
更多日期格式可以参考——
2）月份显示也是用日期表示，默认是每月1日。
3）日期和时间的相关函数
2
【Date】
语法：DATE(year,month,day)
3
【Time】
语法：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”。
4
【Dateif】
=DATEDIF(开始日期,结束日期,返回什么结果)
下面这几种写法，用到的比较少，大家简单了解一下就可以了。
七、文本处理
常用的文本函数如下：
【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的函数应用】
理解：因为函数是英文，所以英文转化为汉语，汉语转化为真实含义，都是一个过程。理解之后，使用起来就会得心应手。
运用：许多函数忘记了，是因为基本用不上，把好用的函数用起来，后面就会越用越上瘾。
目标：是偷懒，偷懒的心，其实是前期设置好，后期自动化。
心法：是解决问题的总思路。这个学会了，所有的方法技巧，都可以随便就能拿过来。
限 时 特 惠： 本站每日持续更新海量各大内部创业教程，一年会员只需98元，全站资源免费下载 点击查看详情
站 长 微 信： muyang-0410
