本文主要针对Excel函数的梳理，按照函数类型进行分类说明。
1、逻辑函数
2、查找函数
3、统计函数
4、引用函数
5、数学函数
7、日期函数
8、文本函数
9、信息函数
10、数组
一、公式规范
Excel公式用“=”开头，用括号()来区分优先级，成对出现
快捷键说明：
F3：调出“名称对话框”
F9：显示函数中自逻辑的值，按下ESC或者Ctrl+z退出
F4：相对引用、绝对引用、混合引用的切换
Ctrl+~：显示单元格中的公式
Alt+F1：快速插入图表（柱形图）
对区域进行公示计算是，选中区域，并输入公式后按住“Ctrl+回车”，公式会自动填充区域，包括行和列。
二、逻辑函数
1、True（1）和False（0）
逻辑判断符号：=、>、>=、<=、<>
逻辑值可以转换为数字（*1即可）
2、IF函数，及IF嵌套函数
=IF(F2>3,5000,"无奖金")
=IF(F2>=4,5000,IF(F2=3,3000,IF(F2=2,2000,0))) ---嵌套函数逻辑关系必须有层级关系。
3、逻辑函数And、Or、Not
And：用于判断多个条件是否同时成立，示例：=IF(AND(F2>3,E2="A类"),5000,0)
Or：只要有一个是真就是真，示例：=IF(OR(F2>3,E2="A类",D2>=10),5000,0)
Not：取反，示例：=IF(NOT(F2>3),0,5000)
快捷键F9，可以查看函数值，例如函数=IF(NOT(F2>3),0,5000)，选中黄色内容按下F9后公式显示：=IF(FALSE,0,5000)
三、查找函数
1、Vlookup函数
=Vlookup(要查找的值，要查找的区域，返回第几列，查找类型)，其中查找类型有两种：1或True 模糊匹配，0或Flase精确匹配
=Vlookup(lookup_value,table_array,col_index_num,[range_lookup])
VlookUp中Vertival adj.垂直的，直立的；头顶的；顶点的；n.垂直线，垂直面
在区域或数组的第一列中查找数据
在区域或数组中查找数据
返回与指定值同行的该区域或数组中其他列的值
近似匹配时，要求table_array的首列中的值必须以升序排序
这里对提成率区域命名为“提成率表”，选择Vlookup中的区域部分按快捷键F3，调出名称对话框，选中名称即可用名称替代区域。
嵌套复合调用，可以将Vlookup的结果集作为下一个Vlookup的区域进一步进行查找。
=VLOOKUP(VLOOKUP(C2,G:H,2,0),J:T,8,0)
2、Hlookup函数
=HlookUp(要查找的值，查找区域，返回区域中的第几行，查找类型)，其中查找类型有两种：1或True 模糊匹配，0或Flase精确匹配
=HlookUp(loopup_value,table_array,row_index_row,[range_lookup])
HlookUp中Horizontal adj.水平的；地平线；同一阶层的；n.水平线；水平面；水平位置
在区域或数组的第一行中查找数据，返回与指定数值同行的该区域或数组中的其他行的值
返回区域的第2列，Vlookup是返回区域的第2行。。。
3、Match函数
=MATCH(查找的值，查找的区域，查找类型)，其中查找类型有3种：1为小于（升序），0为精确匹配，-1为大于（降序）
=MATCH(E2,$B$2:$B$6,0) 返回E2在B2:B6区域中的第几行（这里的行相对于区域而非工作表）
=MATCH(I3&"-"&J3,M3:M12,0) 复合公式
4、Index函数
=INDEX(区域,区域中行,区域中列)
=INDEX($D$4:$K$8,N3,O3)
5、Index和Match的组合
用match获取关键字在区域中的行，index通过行在区域中查找具体单元格的值。
index(要查找的区域，第几行，第几列)，返回指定行列单元格的值
match(要查找的值，查找的区域，匹配类型)，返回指定区域中指定值的行，其中匹配类型：1为小于（升序），0为精确匹配，-1为大于（降序）
6、LookUp函数
向量形式=LookUp(要查找的值，要查找的区域，返回查找的结果或区域)，其中要查找的区域，和返回查找的结果或区域 只能是一行或一列。
数组形式=LookUp(要查找的值，要查找的区域)
使用LoopUp的区域首列要升序排列，且没有重复值。
7、Choose函数
=CHOOSE(MONTH(B2),1,1,1,2,2,2,3,3,3,4,4,4)
四、统计函数
1、Count、CountA、CountBlank
示例：count统计数字个数；counta统计非空字符个数；countblank统计空单元格个数
2、CountIf、SumIf、AVERAGEIF
countif计算满足一个条件的单元格个数；countIfs计算满足多个条件的单元格个数
=COUNTIF(C:C,"A类") ，countIf(区域，条件)
=COUNTIF(C:C,H4)
=SUMIF(C:C,"A类",D:D)，sumif(区域，条件，求值区域)
=AVERAGEIF(C:C,"A类",D:D)， averageIf(区域，条件，求值区域)
=SUMIF($B$2:$B$46,"小计",C2:C46)
=SUMIF($B$2:$B$46,$B55,C$2:C$46)
3、CountIfs、SumIfs、AVERAGEIFs
=SUMIFS(D:D,B:B,">=10",C:C,"A类") ,=sumifs(求和区域，条件1区域，条件1，条件2区域，条件2，……)
=AVERAGEIFS(D:D,B:B,">=10",C:C,"A类") ,=averageifs(求平均值区域，条件1区域，条件1，条件2区域，条件2，……)
=COUNTIFS(B:B,">=10",C:C,"A类") ，=countifs(条件1区域，条件1，条件2区域，条件2，……)
=SUMIFS($D:$D,$B:$B,$F3,$C:$C,G$2)
4、Median和Mode函数
=MEDIAN(求值区域)，用来求该区域（行或列）的中值，如果为奇数则返回中间的一个数，如果为偶数则返回中间两个数的平均值。
=MODE(求值区域)，用来返回求值区域中出现次数最多的数
5、Rank函数
=rank(关键值，区域)， 返回一个数值在一组数字中的排位（序号）
rank(数字，区域，排序类型)，其中排序类型0为降序（默认不写时为0），1为升序。
Rank (number,ref,[order])
Rank.EQ(number,ref,[order]) 等价于Rank
Rank.AVG(number,ref,[order])
6、Max、Min、Large、Small函数
Max(区域)，求一个区域的最大值
Min(区域)，求一个区域的最小值
Large(区域，k)，求一个区域中第k大的值
Small(区域，k)，求一个区域中第k小的值
五、引用函数
1、offset函数
=offset(单元格，偏移行数，偏移列数，新区域的高，新区域的宽)
2、Indirect
=\'2009年度\'!B8 等价于=INDIRECT(A14&"!B8")
=SUM(\'2009年度:2011年度\'!B4) ，这里按住shift选中sheet页。等价于 =\'2009年度\'!B4+\'2010年度\'!B4+\'2011年度\'!B4
Indirect(ref_text,[a1])，返回由文本字符串指定的引用
=INDIRECT("D8")，使用双引号，当成一个值来处理，如果没有双引号，则当着变量
************************************************
1、A列“数据”——“数据验证”——“序列”，引用区域中第一列
2、创建区域名称，选中区域，“公式”——“根据所选内容创建”——“最左列” 确定即可。
在“公式”——“名称管理器”中可以查看名称清单
3、给B列增加“序列”，引用名称与“A1”相等的区域
4、这样创建的B列“序列”下拉有空白处，解决办法是选中数据区域，定位空值，右键删除即可。
3、Row
row([引用参数])，参数可以为空，此时返回当前单元格所在的行号，如果有参数则返回参数单元格的行数。
=ROW()
=ROW(B8)
=ROW(2:2)
=ROW()-1
=COLUMN()
=COLUMN((B8))
快速生成工资条方法一：
=IF(MOD(ROW(1:1),2),B$1,OFFSET(B$1,$A13,0))
生成工资条方法二（通过排序）：
生成工资条方法三：
在最后两列分别输入两个数，选定最后两列的区域，定位常量，右键，插入整行。
复制标题，选中“序号”列，定位空行，粘贴 即可。
给区域隔多少行增加一空行，就在第几行后输入数一起拖动增加序列，定位常量，右键插入即可。
给区域隔多少列增加一空列，就按住Ctrl（必须是Ctrl），点选所有列后右键插入即可。
4、ROWS、COLUMNS
=ROWS(3:8) 等于6（行）
=COLUMNS(C:E) 等于3（列）
六、数学函数
1、Round函数
=ROUND(27715.3943,2)
=ROUND(K9,-1)
选中区域鼠标右键拖动可以复制，复制时可以选中格式，数值等多种选项。
2、Round、RoundDown、RoundUp
=ROUND(D2,0) 四舍五入
=ROUNDDOWN(D2,0) 向下舍入（丢弃）
=ROUNDUP(D2,0) 向上舍入（进位）
Mround
Ceiling
Floor
Even
Odd
3、Int函数
=INT(B2)
=TRUNC(B2) 该参数可以带指定保留小数位数。
4、Mod
=MOD(A2,B2)
每隔一行插入背景色
选择数据区域——>表格——>插入表格，会新建工作表
每隔五行插入背景色
全选工作表——>“开始”选项卡下——>条件格式——>新建规则——>使用公式确定要设置格式的单元格——>输入公式：=MOD(ROW(A1),5)=0——>设置格式
5、Rand函数
=RAND() 返回0-1之间的随机数，每次更新工作表时，这些数据会重新计算（对他们排序时没有用的）
=RANDBETWEEN(0,100)，两个入参分别是最小值和最大值
返回两个日期之间的日期，一定要用到绝对引用。
=RANDBETWEEN($R$2,$R$3)
生成0-100的随机数
=RANDBETWEEN(0,100)
用随机数抽签，去掉小数位后就只有两个随机数，0和1.
6、Convert函数
=CONVERT(E1,"in","cm")
=CONVERT(E7,"Nmi","km")
=CONVERT(E13,"C","F")
七、日期函数
1、日期的本质
日期第一天从1900年1月1日开始，日期的本质是数值。
时间是日期的小数部分。
日期的输入，用“-”或“/”分割，时间的输入，用“:”隔开。
日期的计算，跟数值计算一样，根据需要可能要设置格式。
Ctrl+； 会自动插入系统的日期
Ctrl+Shift+； 会自动插入系统的时间
“Ctrl”+“；”+“空格”+“Ctrl+Shift”+“；” 会自动输入当前的日期和时间
=TODAY() 返回系统当前日期
=NOW() 返回系统当前时间
2、提取日期和时间
年月日year、month、day，时分秒hour、minute、second
=DATE(A2,B2,C2) 组合时间
=TIME(F2,G2,H2)组合日期
3、weekday函数
设置日期格式，aaa表示星期的简写，aaaa表示星期的全称
weekday函数两个参数，将日期转换为星期几
通过数据有效性，安排会议时间，排除周六日
=and(weekday(k1)<>7,weekday(k1)<>1
4、Networkdays
=NETWORKDAYS(B2,B3,{"2013-04-04","2018-05-01","2013-06-12","2013-09-09","2013-10-10"})
=NETWORKDAYS($B$2,$B$3,D2:D6)
=NETWORKDAYS($B$2,$B$3,{41368;41395;41437;41536;41548})
holidays入参可以是单元格区域，也可以是数组。
日期之间没有法定节假日用=NETWORKDAYS(B2,B3)
日期之间有法定节假日=NETWORKDAYS(B2,B3,D2:D6)
不排除节假日=WORKDAY(A2,B2)
排除节假日=WORKDAY(A2,B2,E2)
=NETWORKDAYS.INTL(B2,B3,1,D2:D6)
5、Eomonth
指定日期向前或向后几个月的那个月的最后一天日期
=EOMONTH(A2,0)，这里0表示当月，负值表示往前几个月，整数表示往后几个月
=EOMONTH(A2,2)
=EDATE(A2,2)
6、Datedif函数
这是一个隐藏函数，在函数列表里找不到，只能手动输入
表示返回两个日期之间的间隔，单位为y时表示间隔年份，m表示间隔月份，d表示间隔天数，ym表示忽略年仅核算间隔的年数，……
=DATEDIF(A2,B2,"y")
\'=DATEDIF(H2,TODAY(),"y")
八、文本函数
1、Find Mid函数
=FIND("F",A2)
=SEARCH("F",A2)
=FIND("-",E2)
=MID(E2,F2+1,4)
=MID(E2,FIND("-",E2)+1,4)
2、Left Right函数
=LEFT(A1,3)
=RIGHT(A1,8)
=LEFT(F2,LEFT(FIND("省",F2)))
=MID(LEFT(F2,FIND("市",F2)),FIND("省",F2)+1,10)
=RIGHT(F2,LEN(F2)-FIND("市",F2))
3、Trim函数
=TRIM(A2)，只能删除两头的空格，字符串中间的空格没法处理。
=CLEAN(A1)
4、Concatenate
=CONCATENATE(A1,B1,C1)
=CONCATENATE(I1,"-",J1)
=I3 & "-" & J3
5、Upper Lower
PROPER将文本中每个单词的首字母转为大写
Upper将文本转为大写
Lower将文本转为小写
=PROPER(CONCATENATE(A2,B2)) ---首字母大写
6、Replace subsitute函数
=REPLACE(A2,5,1,"") 替换A2单元格中第5个字符开始，的1个字符，替换为空
=REPLACE(D2,8,1,"CN")
=SUBSTITUTE(A8,"-","") A8中所有"-"替换为空
=SUBSTITUTE(D8,"-","CN",2) D8中将第2次出现的"-"替换为CN，参数中instance_num为出现的次数
7、Text
=TEXT(F3,"000-00000000") 这里0表示占位符
可以通过“格式”设置单元格内容位数，0表示占位符，不足用0表示
九、信息函数
信息函数用来判断文本信息，大部分都是is开头的函数，返回值只有Ture或False两种。
1、is函数
=ISTEXT(A3)
=ISNUMBER(A3)
=ISNONTEXT(A3)
2、iserror函数
iserr、isna（NA错误）、iserror（所有错误）
公式返回的错误值大概有7种，比如NA
=IF(ISERROR((G4-F4)/F4),"NA",(G4-F4)/F4)
=IFERROR((G4-F4)/F4,"NA") 如果是错误值返回NA，否则返回第一个值
十、数组
1、什么是数组
对于数组公式，一定要同时按“Ctrl”+“Shift”+“回车”。
行方向的一维数组,用逗号分隔：={1,2,3,4}
列方向的一维数组,用冒号分隔：={5;6;7;8}
二维数组，嵌套分号和冒号：={1,2,3;4,5,6}
{=SUM($C$3:$C$15*$D$3:$D$15)}
{=AVERAGE(H3:H15-G3:G15)}
2、数组公式
{=SUM($C$4:$L$4*C5:L5)}
{=IF(I2:O2=I1:O1,"重复","不重复")}
3、数组统计个数
{=SUM(1/COUNTIF(A2:A40,A2:A40))}
4、Frequency函数
{=FREQUENCY(B2:B41,D5:D9)}
Alt+F1 ：快速插入图表。
5、Transpose函数
可能用复制——转置粘贴，但是这样转置后的表跟原表就没有关联关系了。
{=TRANSPOSE(A2:H5)}用公式可以建立原表与新表的关联。
6、数组查询
{=VLOOKUP(B3&"-"&C3,G:H,2,0)}
{=INDEX(D:D,MATCH(G2,B:B&"-"&C:C,0),0)}
