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所指忽略隐藏参数指的是忽略所隐藏的行，不忽略所隐藏的列，也就是说隐藏的行不参与求和。

参考：SUBTOTAL函数：各种简单统计，用我就够了

## ※ 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))

原文参考：Sumifs函数5个经典公式，最后一个90%的人都不会！

## ※ 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))

参考：VLOOKUP函数怎么使用？看完这篇就知道了~

参考:十大Vlookup常见错误！

参考:7个Vlookup进阶案例详解

※ HLOOKUP()

公式 = VLOOKUP(查找值，查找区域，返回第几行数据，精确/模糊查找）

参考：HLOOKUP函数的3个应用示例

## ※ 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（找谁，在哪儿找，第几列，匹配方式）

参考

必备的Excel报表技巧：INDEX+MATCH函数

## ※ OFFSET()

举例：公式 = =OFFSET(C3,4,2,4,3)就是以C3为基点，向下偏移4行，向右偏移2列，新引用的行数是4行，新引用的列数是3列，最终得到对E7:G10单元格区域的引用。

参考：大家认识非常好用的OFFSET函数

## ※ 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)