易失函数
使用这些函数后，会引发工作表的重新计算，
有时我们打开一个工作薄但不做任何更改就关闭时，EXCEL却提醒我们是否要保存
这就是因为文件用到了一些“易失性函数”引发了文件重算

支持通配符的函数
Countif、Countifs、Sumif、Sumifs、Match、Search、Vlookup、Hlookup
Average、Averageif、Averageifs、Sum

不等式作条件
不等式作条件，需要用双引号将不等式括起来
=AVERAGEIF( M: M : M,”<=100”,C:C)

数组运算
Countif等函数通过数组的相乘，可以实现多条件计数
返回数组的函数，要先计算返回数组的尺度，在选中相应尺度的单元格
按Ctrl+Shift+Enter，得到计算结果

查询函数
Index：二维查找，逆向查找
Lookup：多条件查找，返回等级
Vlookup：简单超找
多条件查询：可以使用&符号，将多个条件串接起来

返回函数中间值，追踪错误
拖动边框，放大编辑栏，选中公式中需要返回中间值的部分，按F9键

或者使用“公式→公式审核→检查错误/公式求值”

可以返回数组的函数
如：Frequency，Offset
该公式返回的是几行几列的数组，就选中几行几列的区域，在编辑栏输入公式，按Ctrl+Shift+Enter生成结果
数组生成后，该区域即为一个整体，将无法单独删除区域中的单元格数据，只能一起删除整个区域数据

Lookup、Vlookup、Hlookup
=Lookup（查找的值，查找的范围，返回值的范围）
“查找的范围”中的数值必须按升序排序
如果 lookup函数中的（查找的值）在（查找的范围）中找不到，
它会默认匹配（查找的范围） 中小于（查找的值）的最大值。
lookup没有精确查找的选项，若想精确查找需要使用多条件查找的套路
主要用于多条件查找（非常占用运算资源）
以及返回成绩等级
VLOOKUP(查找的值，查找区域，返回列的位置，查找的方式)
查找的方式：模糊查找（1） ，精确查找（0），默认为模糊查找
模糊查询类似与lookup，所以也可以实现返回成绩等级
可以用于制作工资条和考场桌贴、可以批量替换数据
查找的值支持通配符
hlookup函数——横向查找，在第一行搜索lookup_value。
vlookup函数——纵向查找，在第一列搜索lookup_value。
lookup函数——根据数组的维度进行搜索lookup_value。
Vlookup逆向查询：IF({1,0},E2:E10,D2:D10)
交换E列和D列数据

Offset
（基准单元格，行偏移量，列偏移量，返回几行，返回几列）
偏移量可以有正负，正在下，负在上
返回几行和几列主要用于产生数组，作为其他函数的参数
返回几行和几列决定了返回值是一个单元格还是一个区域
如果省略 height 或 width，则假设其高度或宽度与基准位置相同。
公式 SUM(OFFSET(C2,1,2,3,1)) 可计算 （E4:E6）的总值。
可以进行单列多列之间的转换，可以生成工资条、考场桌贴

IF函数
省略了第三参数，则返回第三参数值时会返回FALSE
IF数组公式同时满足多条件表达：
=SUM(IF((A2:A4=”A”)*(B2:B4>40),B2:B4))
=SUM(IF((A2:A4=”A”)+(B2:B4>40),B2:B4))
IF函数加数组公式可以生成一个新的数组，用于其他函数，作用很大
使用Shift+Ctrl+Enter生成结果
计算指定条件的最小值：MIN(IF(A2:A9=F2,D2:D9))
=IF(A1>5,”大”)，返回逻辑值FALSE
=IF(A1>5,”大”,)，返回数值0
不支持通配符

Row、Column
row（单元格） 可以返回单元格的行数
column（单元格） 可以返回单元格的列数
row()    返回公式所在行的行数
column() 返回公式所在列的行数

Counta
统计非空单元格个数
Offset(A1,0,0,1,Counta( 1: 1 : 1))

IF型函数都是条件区域在前，数据区域在后
IFS型函数都是数据区域在前，条件区域在后
Count函数无数据区域，故而IF和IFS型都是条件区在前

Countif
单条件计数
Countif（条件区域，条件）
条件的形式可以是数字、表达式或文本，甚至可以使用通配符，具体数值文本需要加双引号。
=COUNTIF(B:B,”梁*”)，统计姓梁的人数
Countif函数中的参数条件使用单元格地址的值
这时要使用连接符“&”，把关系符>和单元格地址连接起来
输入公式=COUNTIF(D2:D8,”>”&D9)
COUNTIF函数在处理时，会将文本型数值识别为数值进行统计。
在Excel中超过15位的数值只能保留15位有效数字，后3位全部视为0处理，
因此COUNTIF遇到身份证时，需要使用通配符”“将其强行识别为文本进行统计=COUNTIF( B B 2:B11,B2&”“)
或者直接使用等式B2= B B 2:B11
=IF(SUM(N(B2= B B 2:B11))>1,”重复”,”“)
等式B2= B B 2:B11返回的是逻辑值，用N函数转换为数值，再用SUM函数求和，需要使用组合回车。

Sumif
单条件求和函数
条件区域，条件，求和区域
=Sumif(C2:C39,“>100”,C2:C39)
当条件区域与求和区域相同时，求和区域可省略
=Sumif(C2:C39,E7&F7)
E7：>
F7：100

Averageif
单条件求平均值函数
(条件区域，条件，平均值区域)
函数会自动舍去空值、错误值、文本值，从而不参与计算
本函数只支持07以上版本，03版本可以使用average和搭配数组相乘的if函数实现多条件平均
支持通配符

Countifs
多条件计数
Countifs（条件区域1，条件1，条件区域2，条件2，……）
支持通配符

Sumifs
多条件求和函数
(求和区域，条件区域1，条件1，条件区域2，条件2，……)
相比于Sumproduct的优势在于支持通配符

Averageifs
多条件求平均值函数
(求平均值区域，条件区域1，条件1，条件区域2，条件2，……)
函数会自动舍去空值和错误值，从而不参与计算
本函数只支持07以上版本，03版本可以使用average和搭配数组相乘的if函数实现多条件平均
使用Sumproduct/N可以实现部分情况下的多条件平均
（只能求个数确定的数据组的平均值，且不支持通配符）
支持通配符

Sumproduct
sum是和，product是积，所以是乘积之和的意思。
=Sumproduct（array1,array2,array3, …）
Array为数组，逗号前后各个数组之间的维度必须一致
逗号分割的各个参数必须为数字型数据，否则会自动当作0来处理
多条件求和：
满足所有条件
SUMPRODUCT(条件1*条件2*条件3…条件N，求和范围)
SUMPRODUCT(条件1*条件2*条件3…条件N*求和范围)
满足任一条件
SUMPRODUCT(条件1+条件2+条件3…+条件N，求和范围)
多条件计数：
=SUMPRODUCT((A2:A13=”成都”)*(C2:C13=”重庆”))
单价数量求总额：
原始用法=Sumproduct(B2:B9,C2:C9)
Sumproduct不支持通配符
逗号和星号一般情况下是等效的
但是逗号的用法可以将非数值（抱括逻辑值）的数据当作0来处理
而星号*的用法中，自动将true转为1，false转为0，其他非数值数据会报错#VAlUE
在非数值的数据数组后加上*1会将其转换为数值数组

Randbetween
生成指定数字之间的随机数

Char
将数字转化为ANSI码上对应的字符
0~9 48~57
A~Z 65~90
A~z 97~122
可以通过Char嵌套Between实现英文字母的随机生成

Int
可用于截取时间中的日期
日期时间数据类型，整数部分通常是日期，小数部分为时间
或者用TEXT(A1,”yyyy-mm-dd”)函数也行

Find、Search
查找指定字符在字符串中的位置
Find（要查找的内容，查找的范围，查找起始位置）
Search（要查找的内容，查找的范围，查找起始位置）
1. FIND函数区分大小写，而SEARCH函数则不区分。
2. SEARCH函数支持通配符，而FIND函数不支持。
如果要查找的字符串就是问号或星号，则必须在这两个符号前加上“～”符号。

Mid、Right、Left
A1单元格内容是“郑州市花园路”
=left（A1，3）载取前三个字符，结果是“郑州市”
=right(a1,3)  载取右边三个字符，结果是”花园路”
=mid(a1,2,3)  从第二个开始载取，截取3个字符，结果是 “州市花”
=mid(a1,find(a1,”市”)+1,3) 查找“市”的位置，然后在此位置后截取3个字符，结果是“花园路”

Substitute、Replace
Substitude(数据，被替换值，替换值，被替换值重复时指定位次)
直接替换值
=SUBSTITUTE(A4, “1”, “2”, 3) 用 2 替换第三个 1（2011 年第一季度） 2012 年第一季度
Replace(数据，开始位置，替换长度，替换值)
按位置替换值
=REPLACE(A2,6,5,”“) 从第六个字符 (f) 开始，使用单个字符 替换五个字符。 abcde*k

Text
(需要转换的值，格式)
会将数字转换为文本  
提取出生日期
=–Text(Mid(A2,7,8),”0-00-00”)
公式 说明
= TEXT(TODAY(),”MM/DD/YY”) 目前日期采用 YY/MM/DD 格式，如 12/03/14
=TEXT(TODAY(),”DDDD”) 一周中的当天，如周日
=TEXT(0.285,”0.0%”) 百分比，如 28.5%
=TEXT(4.34 ,”# ?/?”) 分数，如 4 1/3

Round
四舍五入法，指定小数的显示位数
（数字，小数的位数）

 
T函数
是文本返回本身，否则返回空字符。

Match
match本身即为匹配的意思
返回查找内容所在单元格的位置
（查询的指定内容，查询的指定区域，查询的指定方式）
支持通配符
查询方式 说明
1或省略 小于或等于指定内容的最大值，指定区域升序排列
0 等于指定内容的第一个数值
-1 大于或等于指定内容的最小值，指定区域降序排列

Address
返回值为单元格地址
（行号，列号，引用类型，引用方式，工作表名称）
有4种引用类型：
1绝对引用，如果第三个参数省略，默认为1
2绝对行号，相对列号
3相对行号，绝对列号
4相对引用
有2种引用方式：
TRUE为A1样式，默认为A1样式
FALSE为R1C1样式

Index
直接引用函数
index本身即为索引的意思
根据行号列号，返回单元格中的数据
（数据所在区域，返回值所在的行号，返回值所在的列号）
如果行号或列号为0，将返回整列或者整行
可以用来制作抽奖器
=INDEX(A 4:A 4 : A 53,RANK(B4,B 4:B 4 : B 53))
A4:A53员工名单，B4:B53为生成的随机数，按F9重计算

Indirect
间接引用函数
indirect本身即为间接的意思
（单元格名称或单元格地址的字符串）
只有一个参数，返回值为所指定的单元格的值
Indirect最终落脚点在内容为单元格地址的字符串（类似于“指针”）
层层递进，直到出现“指针”，再根据地址值，到相应单元格中取出数据
若最终取出的不是“指针”，则会报错

直接指定单元格地址：=INDIRECT(“A4”)，返回值”A3”
隐式指定单元格地址：=INDIRECT(A4)，返回值”3”，也就是A3单元格的值
这里A4是一个单元格的地址而非字符串，所以要取出单元格A4的值（也就是字符串”A3”）
然后对拿到的地址（A3）进行取值得到”3”
=INDIRECT(A2)，这个时候公式就会返回#REF!错误
因为单元格A2的值是”2”，而不存在一个地址为2的单元格，所以会报错
=INDIRECT(B1&A3)，这时会返回值”3”
参数不是单元格地址或者名称，首先将参数转化成字符串格式的地址或者名称，
B1的值是”A”，A3的值是”3”通过&连接后得到=INDIRECT(“A3”)
使用单元格名称：=INDIRECT(A5)，会返回”B”，也就是B2单元格的内容。
将A5转换成单元格名称的字符串”TEST”，然后取得”TEST”所代表的单元格的值

Frequency
单词本身即为“频率”的意思
统计各区段上，数据出现的频数
（给定的一组值，数据区段）
返回值为数组，表示各个区段上数据的个数
数据区段可以引用表中的单元格，也可写成常数数组
写成常数时，各数字之间用分号隔开
忽略空白单元格和文本

{=FREQUENCY(K:K,M2:M11)}
由于返回值为数组，所以需要选定N2:N11单元格区域
输入公式，按组合键
不可以选择单一单元格

更多推荐

Excel函数的使用