Excel 常用函数
Excel Function
※ IFERROR()
举例：公式 = IFERROR(A2/B2,"")
如果是错误值则显示为空，否则正常显示。
## ※ IF()
举例：公式 = IF(AND(A2<500,B2="未到期"),"补款","")
两个条件同时成立用AND,任一个成立用OR函数。
嵌套应用举例：
公式 = IF(C2 > 90,"优",IF(AND(C2>79,C2<90),"中",IF(AND(C2 > 59,C2 < 80),"良",IF(C2 < 60,"差"))))
客户经理分段计算奖金的例子：
= (IF(B16<=10,B16*0.04,
IF(AND(B16>10,B16<=20),10*0.04+(B16-10)*0.1,
IF(AND(B16>20,B16<=30),10*0.04+(20-10)*0.1+(B16-20)*0.15,
IF(AND(B16>30,B16<=40),10*0.04+(20-10)*0.1+(30-20)*0.15+(B16-30)*0.2,
IF(AND(B16>40,B16<=50),10*0.04+(20-10)*0.1+(30-20)*0.15+(40-30)*0.2+(B16-40)*0.21,
IF(AND(B16>50,B16<=60),10*0.04+(20-10)*0.1+(30-20)*0.15+(40-30)*0.2+(50-40)*0.21+(B16-50)*0.22,
IF(B16>60,10*0.04+(20-10)*0.1+(30-20)*0.15+(40-30)*0.2+(50-40)*0.21+(60-50)*0.22+(B16-60)*0.23))))))))*10000
小应用：检查单元格有无空格
公式 = IF(ISERR(FIND(" ",B9)),"无","有")
IF多层嵌套最多嵌套 7 次！
## ※ RAND()
举例：公式 = RAND()*(b-a)+a
生成 a 与 b 之间的随机实数
延伸下，如果 0 到 100 之间的整数，INT(RAND()*100)
## ※ SUBTOTAL()
举例：公式 = SUBTOTAL(9,C2:C83)
举例：公式 = SUBTOTAL(109,C2:C83)
| 参数1(隐藏行参与计算) | 参数1(隐藏行不参与计算) | 函数 | 功能 |
|---|---|---|---|
| 1 | 101 | AVERAGE | 平均值 |
| 2 | 102 | COUNT | 数值个数 |
| 3 | 103 | COUNTA | 非空单元个数 |
| 4 | 104 | MAX | 最大值 |
| 5 | 105 | MIN | 最小值 |
| 6 | 106 | PRODUCT | 括号内数据乘积 |
| 7 | 107 | STDEV | 标准偏差 |
| 8 | 108 | STDEVP | 总体标准偏差 |
| 9 | 109 | SUM | 求和 |
| 10 | 110 | VAR | 方差 |
| 11 | 111 | VARP | 总体方差 |
SUBTOTAL 会忽略中间的汇总行，不会累计相加；会神奇的忽略自己求出来的结果！
SUBTOTAL函数第二个参数101-111所指忽略隐藏参数指的是忽略所隐藏的行，不忽略所隐藏的列，也就是说隐藏的行不参与求和。
## ※ SUMIF()
举例：公式 = SUMIF(A2:A5,">160000",B2:B5)
第一个参数是筛选条件的范围，这里是财产价值列; 第二个参数是条件; 第三个参数是求和的区域，这里是佣金列;
公司的含义是：在财产价值范围里找出大于160000的佣金之和。
## ※ SUMIFS()
举例：公式 =SUMIFS(A2:A9, B2:B9, "<>香蕉", C2:C9, "卢宁")
第一个参数是求和区域的范围，这里是销售数量列; 第二个参数是条件范围，这里是销售产品列; 第三个参数是销售产品的筛选条件，可以用通配符，第四个参数是另一个条件范围，这里是人员列; 第五个参数是销售人员的筛选条件。
公司的含义是：计算卢宁售出的非香蕉产品的总量。
SUMIFS 和 SUMIF 的参数顺序有所不同。 具体而言，sum_range（也就是求和列）参数在 SUMIFS 中是第一个参数，而在 SUMIF 中，却是第三个参数。 这是使用这些函数时出现问题的一个常见原因。
※ Sumifs函数5个经典公式
Sumifs函数是工作中最常用的多条件求和函数，今天兰色分享它最常用的5个公式，其中几个公式还是有难度的哦，可能你是第一次看到。
1、多条件（并列）求和
要求统计公司1的人事部工资总和
=Sumifs(D:D,A:A,"公司1",B:B,"人事部”）
2、 多条件（或者）求和
要求统计公司1人事部，以及公司2财务的工资之和
=SUM(SUMIFS(D:D,A:A,{"公司1";"公司2"},B:B,{"人事部";"财务部"}))
3、按时间段求和
要求统计2016-1-3~2016-1-4日 产品E的销量
=SUMIFS(C:C,A:A,">="&F2,A:A,"<="&F3,B:B,F4)
4、模糊条件求和
要求计算A列产品中包含“电视”并且B列地区为郑州的数量之和
公式：C11
=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)
说明：在sumifs中可以使用通配符 *
5、多表多条件求和
1~5号的车辆销售明细表
要求在汇总表中对各个车型进行汇总
=SUMPRODUCT(SUMIFS(INDIRECT(ROW(1:5)&"!c:c"),INDIRECT(ROW(1:5)&"!a:a"),A2,INDIRECT(ROW(1:5)&"!b:b"),B2))
## ※ SUMPRODUCT() 实现多条件求和
公式 = SUMPRODUCT((条件一)*(条件二)……,求和区域)
举例：公式 = SUMPRODUCT((MONTH(A2:A13)=6)*(B2:B13="财务部"),D2:D13)
统计六月份财务部发放了多少工资?第一个条件，发放工资的时间必须是六月份;
第二个条件，发放工资的部门必须是财务部。
举例：公式 = SUMPRODUCT((MONTH(A2:A13)=6)(B2:B13={"财务部","市场部"})D2:D13)
举例：公式 = SUMPRODUCT((C2:C13="bella")*1)
bella领取了几次工资?
举例：公式 = SUMPRODUCT((C2:C13="bella")*D2:D13)
bella累计领取了多少工资?
下面这段视频讲的比较清楚了：
## ※ COUNTIF()
单条件统计数量
举例：公式 = COUNTIF(A1:A10,">=5")
举例：公式 = COUNTIF(A1:A10,"刘*")
※ COUNTIFS()
多条件统计数量
举例：公式 = COUNTIFS(B2:B5,"=是",C2:C5,"=是")
举例：公式 = COUNTIFS(A2:A7, "<5",B2:B7,"<5/3/2011")
※ COUNTA()
举例：公式 = COUNTA(A2:A7)
计算单元格区域 A2 到 A7 中非空单元格的个数。
※ COUNT()
举例：公式 = COUNT(A2:A7)
计算单元格区域 A2 到 A7 中包含数字的单元格的个数。
## ※ FREQUENCY()
举例：公式 = FREQUENCY( C1:C10,{80,60})
统计 C列成绩在60~80的人数
※ LOOKUP()
§ 查找最后一条符合条件的记录
公式的模式化写法为：
=LOOKUP(1,0/(条件区域=条件),查询区域)
首先使用条件区域=条件得到一组逻辑值，然后使用0除以这些逻辑值，如果符合条件，就是0/TRUE，结果是0。
如果不符合条件，就是0/FALSE，结果是错误值，这一步最终得到由一组错误值和0构成的内存数组。
然后使用1作为查询值，在这个内存数组中查找小于等于1的最大值，内存数组中没有1，所以就以0进行匹配，并且返回第三参数对应位置的内容。
§ 查询符合多个条件的记录
公式的模式化写法为：
=LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)),查询区域)
这里的查询套路和第一个问题一致。使用两个条件判断，然后将判断之后得到的两组逻辑值中的元素对应相乘。
如果两个条件同时符合，就是TRUE*TRUE，结果为1。
如果有一个条件不符合或是都不符合，就是TRUEFALSE或是FALSEFALSE，结果是0。
最后使用1作为查询值，在这个内存数组中查找匹配0的位置，并且返回第三参数对应位置的内容。
§ 逆向查询
按照以上相同的思路，最后的查询区域可以灵活写成任意一列，从右至左、自下而上查询，统统没问题。
§ 查询最后一笔业务日期
公式的模式化写法为：
=LOOKUP(1,0/(条件区域<>""),查询区域)
本例来说，公式可以简化为：
=LOOKUP(1,0/B4:B11,$A4:$A11)
但是只适合条件区域为数值的情况，而失去通用性了。
§ 模糊查询考核等级
这种方法可以取代IF函数完成多个区间的判断查询，前提是对照表的首列，必须是升序处理。
如果在E列中找不到B4单元格的具体成绩79，就以小于这个成绩的最大值60进行匹配，并返回F列对应位置的内容。
§ 提取单元格内的数字
A2公式为
=-LOOKUP(1,-LEFT(A2,ROW($1:$99)))
首先用LEFT函数从A2单元格左起第一个字符开始，依次返回长度为ROW($1:$99)也就是1至99的字符串，添加负号后，数值转换为负数，含有文本字符的字符串则变成错误值。
LOOKUP函数使用1作为查询值，在由负数、0和错误值构成的数组中，忽略错误值提取最后一个等于或小于1的数值。
最后再使用负号，将提取出的负数转为正数。
※ VLOOKUP() 返回表中的值
公式 = VLOOKUP(查找值，查找区域，返回第几列数据，精确/模糊查找）
=VLOOKUP(A2,B5:E11,3,0)
在B2单元格中，需要根据A2的值（联系人：表姐），在数据源区域（B5：E11）中，查找其对应的第3列的值（身份证号码），是多少，并且要求精确查找。
VLOOKUP函数有个必须的条件，那就是必须在表格或数值数组的首列（最左列）查找指定的数值。通俗一点，那就是只能查找第一列所对应的数值。
匹配条件,精确查找它的数值就是“0”，模糊查找它的数值就是“1”。通常情况下，我们都是选择的精确查找。
vlookup 查找值不在首列的解决办法
例如:在B列查找D2对应值,取A列值
公式 =INDEX(A:A,MATCH(D2,B:B,0))
※ HLOOKUP()
公式 = VLOOKUP(查找值，查找区域，返回第几行数据，精确/模糊查找）
## ※ INDEX() 返回表中的值。
公式 = INDEX(搜索的区域，行号，列号）
举例：公式 = INDEX(B2:D9，3，1)
公式含义： 返回B2:D9 区域第三行第一列的数据
**重点是这个，举例：公式 = INDEX(B2:D9，0，3)** >公式含义： 返回B2:D9 区域第三列的数据,返回值是个数组 >这个特性可以用于控件中下拉框的索引引用
## ※ MATCH() 返回指定数值在指定区域中的**位置**
举例：公式 = MATCH(100，B2：B5，0)
公式含义：查询在B2：B5区域首个等于100的数值是第几个，这里是3行;
返回值是行还是列要看区域是行还是列。
第三个参数,匹配方式：0 精确查找 1 模糊查找
※ 几个查找函数的通俗解释:
1、INDEX函数：返回表中的值。
=INDEX（在哪儿找，第几行）
2、MATCH函数：返回指定数值在指定区域中的位置。
=MATCH（找谁，在哪儿找，匹配方式）
3、VLOOKUP函数：纵向查找返回表中的值。缺点：查阅值需要位于查找区域的第一列。
=VLOOKUP（找谁，在哪儿找，第几列，匹配方式）
参考
## ※ OFFSET()
举例：公式 = =OFFSET(C3,4,2,4,3)就是以C3为基点，向下偏移4行，向右偏移2列，新引用的行数是4行，新引用的列数是3列，最终得到对E7:G10单元格区域的引用。
## ※ INDIRECT()
INDIRECT函数的引用的两中形式:加引号，一种不加引号。
=INDIRECT("A1") 文本引用——即引用A1单元格所在的文本(B2)。
=INDIRECT(A1) 地址引用——因为A1的值为B2，B2又=11，所以返回。
## ※ ROW() COLUMN()
= COLUMN(F7) 单元格F7占一列，返回值1;
= ROW(E4:I7) 区域E4:I7)占4行，返回值4。
## ※ ADDRESS()
= ADDRESS(2,3) 返回$c$2
= ADDRESS(2,3,2) 返回c$2
第三个参数 1 绝对应用; 2 固定行号; 3 固定列号; 4 相对引用
返回值是引用的坐标，通过参数控制相对和绝对引用
## ※ REPT()
= REPT("※",B3/500) 重复显示※，B3如果=1000,就是2个。
## ※ HYPERLINK()
参数说明：
HYPERLINK参数1：文件夹的路径; HYPERLINK参数2：单元格里显示的文字内容
- 打开文件
A1单元格，输入公式：=HYPERLINK("C:\Documents and Settings\Administrator\桌面\hyperlink函数资料.doc","hyperlink函数资料")
意思是单击A1单元格，将打开桌面的“hyperlink函数资料”这个doc文档。
参数：文件夹的路径，包括文件名和扩展名
- 打开文件夹
A1单元格，输入公式：=HYPERLINK("C:\Documents and Settings\Administrator\桌面","excel论坛")
意思是，当单击A1单元格用hyperlink函数就直接打开桌面的“excel论坛”文件夹。
- 打开网站 =HYPERLINK("//www.excelwm.net","完美论坛")
参数：网站地址，记得一定前面一定要加
//
- 链接到工作表指定位置
链接当前工作表的指定位置，点击跳转到当前工作表指定的单元格。
比如A1单元格，输入公式：=HYPERLINK("#B8","跳转到B8")
意思是单击A1单元格，将跳转到B8单元格。
- 链接到当前工作薄中其他工作表的指定位置或区域
比如A1单元格输入：= HYPERLINK("#sheet2!B8","跳到SHEET2工作表B8")
另外，假如区域定义了名称，如定义名称X=sheet2!A10:B15，则以下公式或跳转到X区域的并选定该区域：= HYPERLINK("#X","跳到区域名称X")
等同于= HYPERLINK("#sheet2!A10:B15","跳到A10:B15")
- 打开工作簿
同目录下的文件：=HYPERLINK("book2.xls#sheet2!a1"，"到BOOK2中Sheet2!A1")
不同目录下：=HYPERLINK("C:\My Documents\XLS\book2.xls#sheet2!a1"，"到BOOK2中Sheet2!A1")
参考：百度文库说明文档
※ 去空格函数TRIM() SUBSTITUTE() CLEAN()
=TRIM(A2)
此方法只能删除字符串首尾的空格，而不能删除字符串中间的空格！故此方法适用于删除字符串首尾的空格
=SUBSTITUTE(A2," ","")
注意函数中间的英文双引号中间是一个空格符号，而左边的双引号是空白的，意思就是将A2单元格的字符串中的空格去掉
SUBSTITUTE函数法可以删除字符串中所有的空格
=CLEAN(B2)
B2 的内容 =CHAR(9)&"Monthly report"&CHAR(10)
用于删除文本中不能打印的字符例如：
char(9) 水平制表符
char(10) 换行
char(13) 回车
※ 和区域名称配合动态引用位置
OFFSET(Lists!$C$1,1,0,MATCH(REPT("z",255),Lists!$C:$C),1)
OFFSET(Lists!$C$1,1,0,MATCH(REPT("座",255),Lists!$C:$C),1)
函数的意思就移c1为参考，引用位置c1下移一行右移0列c2位置开始，到这一行的有文字单元格的结尾行c座；就是选中C2:c座的区域
OFFSET(Lists!$C$1,1,0,MATCH(REPT("座",255),Lists!$C:$C),1)
参考系:Lists!$C$1
下移几个行 1
右移几个行列 0
MATCH返回C列最后一个有文字单元格行数
选中1列即C列
中文字符是要用“座”
末行行号的取法
A1:A20存放着数字、文本、错误值等，下列公式将返回:
=LOOKUP(9E+307,A1:A20)－－－－－－－－－－返回数值
=LOOKUP(9E+307,A1:A20,ROW(A1:A20))－－－－返回行号
=LOOKUP(2,1/(A1:A20<>""),A1:A20)－－－－－返回非空单元格
=LOOKUP(2,1/(A1:A20<>""),ROW(A1:A20))－－－返回行号
=LOOKUP(2,1/(A1:A20<>0),A1:A20)－－－－－－返回非零单元格
=LOOKUP(2,1/(A1:A20<>0),ROW(A1:A20))－－－返回行号
=LOOKUP(2,1/(A1:A20="a"),A1:A20)－－－－－返回指定文本单元格
=LOOKUP(2,1/(A1:A20="a"),ROW(A1:A20))－－－返回行号
=LOOKUP(2,1/(1-ISBLANK(A1:A20)),A1:A20)－－返回非空单元格
=LOOKUP(2,1/(1-ISBLANK(A1:A20)),ROW(A1:A20))－－返回行号
=LOOKUP(2,1/((A1:A20<>0)*ISNUMBER(A1:A20)),A1:A20)－－返回不为零非空单元格
=LOOKUP(2,1/((A1:A20<>0)*ISNUMBER(A1:A20)),ROW(A1:A20))－－返回行号
=VLOOKUP(9E+307,A1:A20,1,1)－－返回最大数值
=VLOOKUP(REPT("龠",255),A1:A20,1,1)－－返回最大文本
=INDEX(A1:A20,,MATCH("*",A1:A20,-1))－－返回任意值
=INDEX(A1:A20,MAX(IF(A1:A20<>"",ROW(A1:A20))))返回非空单元格
返回最后的“行号”:
非空值(方法一)
LOOKUP(2,1/(A3:A65536<>""),row(A3:A65536))
非空值(方法二)
LOOKUP(2,1/(1-ISBLANK(A3:A65536)),row(A3:A65536))
非空值(方法三 数组公式)
MAX((A3:A65536<>"")*ROW(A3:A65536))
数值(方法一)
LOOKUP(9E+307,A:A)
数值(方法二)
MATCH(9E+307,A:A)
文本(方法一)
LOOKUP("座",row(A:A))
文本(方法二)
MATCH("座",A:A)
既然都知道这么多方法，为什么不试验一下呢。
日程安排表里的尝试成功,公式-区域名称-定义为holiday 引用位置公式：
=OFFSET($B$1,1,0,LOOKUP(2,1/(B:B<>""),ROW(B:B)),1)
row()确保返回行号 如果只是区域返回单元格值（2020/10/7）就无法满足 offset() 参数要求了;
单元格公式：
=IF($D$4=TRUE,($C11+$E11-1),WORKDAY(C11,E11-1,holiday))
>参考：[Excel精英培训:最常用的30个Excel函数公式](https://mp.weixin.qq.com/s?__biz=MjM5NDYyNzAzNQ==&mid=2652913414&idx=1&sn=0f1ce55dff1142a2b986e54b681d48fc&chksm=bd5073b28a27faa4139587922a762b36456e28090dcbdb38950d9291301c45a104605717de4c&mpshare=1&scene=1&srcid=1001UaMS65oAt9nhYDfS22WS#rd)
