【Excel 函数】函数速查

一、运算符

1、数学运算符

算术运算符	含义
+（加号）	加法
–（减号）	减法/负数
*（星号）	乘法
/（正斜杠）	除法
%（百分号）	百分比
^（脱字号）	乘方

2、比较运算符

名称	符号
等于	=
大于	>
大于等于	>=
小于	<
小于等于	<=
不等于	<>

3、连接运算符

&

4、引用运算符

引用运算符	含义
:（冒号）	区域运算符，生成对两个引用之间的所有单元格的引用，包括这两个引用
,（逗号）	联合运算符，将多个引用合并为一个引用
（空格）	交叉运算符，生成对两个引用共同的单元格的引用

5、运算符优先级

6、绝对引用和相对引用

符号	快捷键
$	F4

二、信息和逻辑函数

1、逻辑值

结果值	注释
TRUE	是
FALSE	否

2、if函数

函数名称	功能介绍	语法格式
if	指定要执行的逻辑检测	=IF(Logical,Value_if_true,Value_if_false)

3、逻辑函数

函数名称	功能介绍	语法格式
AND	如果其所有参数均为 TRUE，则返回 TRUE	=AND(logical1,[logical2],…)
OR	如果任一参数为 TRUE，则返回 TRUE	=OR(logical1,[logical2],…)
NOT	对其参数的逻辑求反	=NOT(logical)
XOR	返回所有参数的逻辑“异或”值	=XOR(logical1,[logical2],…)

4、信息和逻辑判别函数

用途	函数名称	功能介绍	语法格式
判别错误值和处理错误值	iserror	识别任何错误值，返回 TRUE	=ISERROR(value)
	iserr	识别除 #N/A 以外的任何错误值，返回 TRUE	=ISERR(value)
	isna	识别 #N/A 错误值，返回 TRUE	=ISNA(value)
	iferror	识别所有错值,并对错误值处理，非错误值保持原值	=IFERROR(value,value_if_error)
	ifna	识别#N/A错误值，并对错误值处理，非错误值保持原值	=IFNA(value,value_if_error)
判别数字和文本	isnumber	识别数字，返回 TRUE	=ISNUMBER(value)
	istext	识别文本，返回 TRUE	=ISTEXT(value)
判别奇偶	isodd	识别奇数，返回 TRUE	=ISODD(number)
	iseven	识别偶数，返回 TRUE	=ISEVEN(number)
判别引用和公式	isref	识别引用，返回 TRUE	=ISREF(value)
	isformula	识别公式，返回 TRUE	=ISFORMULA(reference)
判别空值和逻辑值	isblank	识别引用，返回 TRUE	=ISBLANK(value)
	islogical	识别公式，返回 TRUE	=ISLOGICAL(value)

5、转换数值函数和返回#N/A函数

函数名称	功能介绍	语法格式
N	返回转换为数字的值	=N(value)
NA	返回错误值 #N/A	=NA()

三、基础数学和统计函数

1、基础运算

函数名称	功能介绍	语法格式
sum	参数列表求和	=SUM(number1,[number2],…)
average	参数列表求平均	=AVERAGE(number1, [number2], …)
count	计算参数列表中数字的个数	=COUNT(value1, [value2], …)
max	参数列表求最大值	=MAX(number1, [number2], …)
min	参数列表求最小值	=MIN(number1, [number2], …)

2、条件运算

函数名称	功能介绍	语法格式
sumif	符合条件的单元格求和	SUMIF(range, criteria, [sum_range]) SUMIF(条件区域, 条件标准, [求和区域])
sumifs	同时符合多个条件的单元格求和	SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) SUMIFS(求和区域, 条件区域1, 条件标准1, [条件区域2, 条件标准2], …)
averageif	符合条件的单元格求算数平均值	AVERAGEIF(range, criteria, [average_range]）
averageifs	符合多个条件的单元格求算数平均值	AVERAGEIFS(average_range,criteria_range1, criteria1, [criteria_range2, criteria2], …)
countif	符合条件的单元格计数	COUNTIF(range,criteria) COUNTIF(区域,条件标准)
countifs	符合多个条件的单元格计数	COUNTIFS(criteria_range1,criteria1，[criteria_range2,criteria2],…) COUNTIFS(条件区域1,条件标准1，[条件区域2,条件标准2],…)
counta	非空单元格计数	COUNTA(value1, [value2], …)
countblank	空单元格计数	COUNTBLANK(range)

注意：当调整条件标准列的顺序时，公式或不会随之变动

3、扩展

函数名称	功能介绍	语法格式
averagea	可以对数值、文本、逻辑值求平均数而不报错	AVERAGEA(value1, [value2], …)
trimmean	修剪两端极值后求平均数	TRIMMEAN(array,percent) TRIMMEAN(求值区域,要被修剪掉数值数量的百分比)
rank	返回一个数在数组中排名， 相同的值排名相同	RANK(number,ref,[order])

4、条件标准的写法——通配符

通配需求	写法
开始是	张*
结束是	*大众
包含	*汽*
固定长度	???

四、时间函数

日期可存储为序列号,这里的序列号就是日期。

1、年月日的获取和转化&时分秒的获取和转化

函数名称	功能介绍	语法格式
year	将序列号转换为年/获取日期的年份	YEAR(serial_number)
month	将序列号转换为月/获取日期的月份	MONTH(serial_number)
day	将序列号转换为日/获取日期的日份	DAY(serial_number)
date	返回特定日期的序列号/组合年月日形成日期	DATE(year,month,day)
datevalue	将文本格式的日期转换为序列号/文本格式转日期格式	DATEVALUE(date_text)

函数名称	功能介绍	语法格式
hour	将序列号转换为小时/获取日期的小时	HOUR(serial_number)
minute	将序列号转换为分钟/获取日期的分钟数	MINUTE(serial_number)
second	将序列号转换为秒/获取日期的秒数部分	SECOND(serial_number)
time	返回特定时间的序列号/组合时分秒形成时间	TIME(hour,minute, second)
timevalue	将文本格式的时间转换为序列号/文本格式转时间格式	TIMEVALUE(time_text)

2、获取当前日期函数

函数名称	功能介绍	语法格式
now	获取当前日期和时间	NOW()
today	获取当前日期	TODAY()

3、日期间隔函数

函数名称	功能介绍	语法格式
datedif	计算两个日期之间的间隔	=DATEDIF(Start_date,End_date,mode)

mode选项	含义	示意表达式
“Y”	起始日到终止日之间的整年数
“M”	起始日到终止日之间的整月数
“D”	起始日到终止日之间的整日数
“YM”	起始日到终止日在前一年对日之间的整月数	MONTH of [ y/m - (y-1)/M]
“YD”	起始日到终止日在前一年对日之间的整日数	DAY of [ y/m/d - (y-1)/M/D]
“MD”	起始日到终止日在上个月对日之间的整日数	DAY of [y/m/d - y/(m-1)/D]

4、获取指定日期

函数名称	功能介绍	语法格式
edate	某日期若干月份前或后的对日	EDATE(start_date,months)
eomonth	返回某日期若干月前或后对日所在月的最后一日	EOMONTH(start_date,months)

5、工作日期和间隔

函数名称	功能介绍	语法格式
workday	某日期若干工作日前或后的日期	WORKDAY(start_date,days,[holidays]) WORKDAY(开始日期,若干工作日,[另外要从工作日中刨除的日期集合])
workday-intl	自定义工作日，某日期若干工作日前或后的日期	WORKDAY.INTL(start_date,days,[weekend],[holidays]) WORKDAY.INTL(开始日期,若干工作日,周末模式编号或者逻辑值文本,[另外要从工作日中刨除的日期集合])
networkdays	两个日期之间完整工作日数	NETWORKDAYS(start_date,end_date,[holidays])
networkdays-intl	自定义工作日，两个日期之间完整工作日数	NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays])

6、日期的序数

函数名称	功能介绍	语法格式
weekday	某日是一周中的周几	WEEKDAY(serial_number,[return_type]) WEEKDAY(日期,[周序类型])
weeknum	某日处于该年第几周	WEEKNUM(serial_number,[return_type])

五、数学函数

1、运算函数

函数名称	功能介绍	语法格式
int	数值取整	INT(number)
mod	取一个数被除后结果的余数	MOD(number,divisor)
product	所有参数相乘	PRODUCT(number1,[number2],[number3]…)
power	幂函数	POWER(number,power)
sqrt	开平方	SQRT(number)
abs	绝对值	ABS(number)

2、数值截取和位数保留

函数名称	功能介绍	语法格式
trunc	指定小数位数截取数值	TRUNC(number,[num_digits]) TRUNC(数值，[要保留的小数位数])
round	四舍五入	ROUND(number,num_digits)
roundup	向上进位	ROUNDUP(number,num_digits)
rounddowm	向下舍位	ROUNDDOWN(number,num_digits)
ceiling	基数s成倍增大，返回直到刚大于n的值	CEILING(number,signficance) CEILING(参考值，基数值)
floor	基数s成倍增大，返回直到将要超过n的值	FLOOR(number,signficance)

3、随机函数

函数名称	功能介绍	语法格式
rand	随机生成一个[0，1)的小数	RAND()
randbetween	生成指定区间内的整数	RANDBETWEEN(bottom, top)

4、数组函数

函数名称	功能介绍	语法格式
sumproduct	同维度同方向区域数据乘积和	PRODUCT(array1,[array2]…)
mmult	维度相同方向互相垂直的两个区域的乘积和	MMULT(array1,array2)
munit	生成指定维度的单位矩阵	MUNIT(dimension)

5、转换函数

函数名称	功能介绍	语法格式
base	进制转换	BASE(Number, Radix, [Min_length]) BASE(要转化的数 ，进制, [最小长度])
sign	返回数字-1=负数，0=0，1=正数	SIGN(number)
roman	阿拉伯数字转换成罗马数字	ROMAN(number,[form])
arabic	罗马数字转换成阿拉伯数字	ARABIC(text)

六、字符串函数

1、字符串截取函数

函数名称	功能介绍	语法格式
left	从左提取指定字符数的字符串	LEFT(text,[num_chars])
right	从右提取指定字符数的字符串	RIGHT(text,[num_chars])
mid	从指定位置提取指定字符数的字符串	MID(text,start_num,num_chars)

2、字符串长度

函数名称	功能介绍	语法格式
len	字符串包含的字符个数	LEN(text)
lenb	字符串包含的字节个数	LENB(text)

3、查找字符位置

函数名称	功能介绍	语法格式
find	返回一个字符在另一个字符出现的位置,区分大小写	FIND(find_text,whinin_text,[start_num])
search	返回一个字符在另一个字符出现的位置，忽略大小写，支持通配符	SEARCH(find_text,whinin_text,[start_num])

4、字符替换

函数名称	功能介绍	语法格式
replace	指定位置指定长度替换替换	REPLACE(old_text,start_num,num_chars,new_text) REPLACE(原文本,开始位置,字符数,要去替换的新字符串)
substitute	指定内容替换	SUBSTITUTE(text,old_text,new_text,[instance_num]) SUBSTITUTE(文本,被替换的旧字符串,要去替换的新字符串,[第几个旧字符串])

5、代码数字转换

函数名称	功能介绍	语法格式
char	返回由代码数字指定的字符	CHAR(number)
code	返回文本字符串中第一个字符的数字代码	CODE(text)

6、文本转换

函数名称	功能介绍	语法格式
proper	单个单词首字母大写	PROPER(text)
upper	转大写	UPPER(text)
lower	转小写	LOWER(text)
trim	删除多余空字符，仅保留词间1个空格	TRIM(text)
exact	比较两个字符串是否完全相等，区分大小写	EXACT(text1,text2)
rept	字符串指定次数重复	REPT(text,number_times)
dollar	数值转美元格式文本	DOLLAR(number,[decimals]) DOLLAR(数值,[小数位数])
rmb	数值赚人民币格式文本	RMB(number,[decimals])
numbervalue	文本转数字	NUMBERVALUE(text,[decimal_separator],[group_separator]) NUMBERVALUE(文本,[小数分隔符],[位数分隔符])

7、连接函数

函数名称	功能介绍	语法格式
concat	将多个区域和/或字符串的文本组合起来，但不提供分隔符或 IgnoreEmpty 参数	CONCAT(text1,…)
concatenate	将几个文本项合(不是区域)并为一个文本项	CONCATENATE(text1,…)
textjoin	将多个区域和/或字符串的文本组合起来，并包括在要组合的各文本值之间指定的分隔符。如果分隔符是空的文本字符串，则此函数将有效连接这些区域。	TEXTJOIN(dilimiter, ignore_empty, text1, [text2], …) TEXTJOIN(分隔符, 是否忽略空格, 区域或文本1, [区域或文本1], …)
phonetic	提取文本字符串中的拼音（汉字注音）字符，连接连续区域，不支持持数字、日期、时间、逻辑值、错误值等	PHONETIC(reference)

8、其他函数

函数名称	功能介绍	语法格式
clean	删除文本中非打印字符	CLEAN(text)
value	将文本参数转换为数字	VALUE(text)
valuestring	数字转中文大写	NUMBERSTRING(value,type)

9、text函数

函数名称	功能介绍	语法格式
text	设置数字格式并将其转换为文本	TEXT(value,format_text）

正;负;0;文本

10、自定义数值格式的应用

暂略

七、查找和引用函数

1、行和列

函数名称	功能介绍	语法格式
column	返回引用的列号	COLUMN([reference])
row	返回引用的行号	ROW([reference])

2、查找函数

函数名称	功能介绍	语法格式
vlookup	在数组第一列中查找，然后在行之间移动以返回单元格的值	VLOOKUP(look_value,table_array,col_index_num,[range_lookup]) VLOOKUP(要查找的值，查找区域，要返回的列数，是否精确匹配）
hlookup	在数组第一列中查找，然后在行之间移动以返回单元格的值	HLOOKUP(look_value,table_array,col_index_num,[range_lookup])
lookup	在单行或单列区域查找值，匹配比它略小或相等的一个值，返回对应结果区域的值	LOOKUP(lookup_value,lookup_vector,[result_vector]) LOOKUP(要查找的值,查找向量,[结果向量])
	查找一个区域中比较略小或相等的值	LOOKUP(lookup_value,array) LOOKUP(要查找的值,区域)

3、引用和索引

函数名称	功能介绍	语法格式
index	引用区域中指定行列索引坐标处的单元格	INDEX(array,row_num,[column_num]) INDEX(区域,行数,[列数])
	多个区域可选，引用区域中指定行列索引坐标处的单元格	INDEX(reference,row_num,[column_num],[area_num]) INDEX(一个或多个引用区域,行数,[列数],[要返回值的区域编号])
match	在序列数组中查找到值，并返回索引数	MATCH(lookup_value, lookup_array, [match_type])
choose	根据索引号在列表中选择值	CHOOSE(index_num, value1, [value2], ...)
indirect	根据单元格地址指向的单元格，a1=TRUE then A1样式 else R1C1样式	INDIRECT(ref_text, [a1])
offset	返回原引用区域进行指定行列偏移后的单元格	OFFSET(reference,rows,cols,[height],[width]) OFFSET(原引用区域，偏移行数，偏移列数，占行数，占列数）
address	指定行号和列号获取单元格地址	ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text]) ADDRESS(行号,列号,[引用模式],[引用样式],[工作表名称])

4、其他

函数名称	功能介绍	语法格式
transpose	返回转置后的数组，使用数组输入方式键入	TRANSPOSE(array)
formulatext	以字符串形式返回引用单元格的公式内容	FORMULATEXT(reference)
colums	返回引用中涉及的列数	COLUMNS(array)
rows	返回引用中涉及的列数	ROWS(array)
areas	返回引用中涉及的区域个数	AREAS(reference)
getpivotdata	返回数据透视表中存储的数据	GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], …)
hyperlink	创建快捷方式或跳转，以打开存储在网络服务器、Intranet 或 Internet 上的文档	hyperlink(link_location，[friendly_name])

八、高级函数

函数名称	功能介绍	语法格式
small	返回数据集中的第 k 个最小值	SMALL(array,k)
large	返回数据集中第 k 个最大值	LARGE(array,k)
subtotal	返回列表或数据库中的分类汇总	SUBTOTAL(function_num,ref1,[ref2],…) SUBTOTAL(函数编号，引用）
mmult	返回两个数组的矩阵乘积	mmult(数组1，数组2）
frequency	以垂直数组的形式返回频率分布	FREQUENCY(data_array, bins_array) FREQUENCY(数值数组，分界数组)
cell	返回有关单元格格式、位置或内容的信息	CELL(info_type, [reference]) cell(信息类型，引用)
mode	返回在数据集内出现次数最多的值(众数)	mode(数值1，数值2…）
median	返回给定数值集合的中值	median(数值1，数值2…)