Excel中的函数在数据分析中非常重要，它们可以帮助进行各种数据加工、整理、汇总、统计等操作，从而提高数据分析的效率和准确性.
此处总结了大部分常用函数的功能以及如何使用供参考学习.
目录
1.3 MID(text,start_num,num_chars)
1.5 FIND(find_text,within_text,[start_num]) / SEARCH(find_text,within_text,[start_num])
1.6 REPLACE(old_text,start_num,num_chars,new_text)/
1.9 SUBSTITUTE(text,old_text,new_text,[instance_num])
2.4 RAND() / RANDBETWEEN(bottom,top)
3.1 AVERAGE(number1,number2,...)
3.3 COUNT(number1,number2,...) / COUNTA(number1,number2,...)
3.4 COUNTIF(range,criteria) / COUNTIFS(criteria_range1,criteria1,...)
3.5 MAX(number1,number2,...) / MIN(number1,number2,...)
4.1 DATE(year,month,day) / TIME(hour,minute,second)
4.2 DATEVALUE(date_text) / TIMEVALUE(time_text)
4.3 YEAR() / MINTH() / DAY() ; HOUR() / MINUTE() / SECOND()
4.5 EOMONTH(start_date,months) / EDATE(start_date,months)
4.6 DATEDIF(start_date,end_date,unit)
5.1 AND(logical1,logical2,...) / OR(logical1,logical2,...) / NOT(logical)
5.3 IFERROR(value,value_if_error)
1.文本函数
1.1 LEFT(text,[num_chars])
功能：从文本字符串的最左边开始,返回指定数量(包含符号和空格)的字符串.
例如：=LEFT("山水间歌声回荡，回荡思念的滚烫.",12)
返回值：山水间歌声回荡，回荡思念
1.2 RIGHT(text,[num_chars])
功能：从文本字符串的最右边开始,返回指定数量(包含符号和空格)的字符串.
例如：=LEFT("山水间歌声回荡，回荡思念的滚烫.",11)
返回值：回荡，回荡思念的滚烫.
1.3 MID(text,start_num,num_chars)
功能：从文本字符串的指定位置(该位置是从左往右数的,且包含该位置)开始,返回指定数量(包含符号和空格)的字符串.
例如：=MID("山水间歌声回荡，回荡思念的滚烫.",4,9)
返回值:歌声回荡，回荡思念
解释：字符中"歌"字位于从左往右第4个字符，所以从该字符开始返回9个字符
1.4 LEN(text)
功能：返回字符串中字符个数.
例如：=LEN("山水间歌声回荡，回荡思念的滚烫.")
返回值：16
1.5 FIND(find_text,within_text,[start_num]) / SEARCH(find_text,within_text,[start_num])
功能：两个函数都是用于查找一个字符串在另一个字符串中的位置，区别是FIND()区分大小写，SEARCH()不区分大小写。FIND()的find_text参数不能写通配符，SEARCH()的find_text参数可以写通配符.
例如：=FIND("回","山水间歌声回荡，回荡思念的滚烫.")
返回值：6
解释：很明显该例句中字符"回"出现了两次，而通常默认返回第一次的位置，要返回第二次的位置则要在[start_num]处填上比第一次的位置还要大的值.
例如=FIND("回","山水间歌声回荡，回荡思念的滚烫.",7)
返回值：9
1.6 REPLACE(old_text,start_num,num_chars,new_text)/
SUBSTITUTE(text,old_text,new_text,[instance_num])
将指定字符串的指定位置（字节下标）指定字节数的字符串替换为新字符串.
例如：=REPLACE("山水间歌声回荡，回荡思念的滚烫.",4,2,"鸟啼")
返回值：山水间鸟啼回荡，回荡思念的滚烫.
1.7 LOWER(text) / UPPER(text)
功能：LOWER(text) 将字符串中的所有大写字母转换为小写字母,UPPER(text)将字符串中的所有小写字母转换为大写字母.
例如：=LOWER("Hello World")
返回值：hello world
1.8 REPT(text,number_times)
功能：将指定文本重复指定次数.
例如：=REPT("*",3)
返回值：***
1.9 SUBSTITUTE(text,old_text,new_text,[instance_num])
功能：用新的字符串替换指定的字符串.
例如：=SUBSTITUTE(H1,"回荡","徜徉",1)
返回值：山水间歌声徜徉，回荡思念的滚烫.
解释：[instance_num]表示要替换的位置在第几次出现的，如果为2则返回：山水间歌声回荡，徜徉思念的滚烫.如果不填则全部替换.
例如=SUBSTITUTE(H1,"回荡","徜徉")
返回值：山水间歌声徜徉，徜徉思念的滚烫.
1.10 TRIM(text)
功能：移除文本中的所有空格，单词之间单个空格除外.
例如：=TRIM(" Hello world ")
返回值：Hello world
1.11 TEXT(value,format_text)
功能：自定义单元格格式.
例如：=TEXT("12.234","$#.0000")
返回值：$12.2340
例如：=TEXT("20120925","0000-00-00")
返回值：2012-09-25
1.12 VALUE(text)
功能：将单元格中的文本或公式转换为数值.
例如：=VALUE("$1,456,123")
2.数学函数
2.1 ABS(number)
功能：返回数字的绝对值.
例如：=ABS(-2.5)
返回值：2.5
2.2 INT(number)
功能：向下(小)取整.
例如：=ABS(-2.3)
返回值：-3
2.3 MOD(number,divisor)
功能：返回两数相除的余数
例如：=ABS(10,3)
返回值：1
2.4 RAND() / RANDBETWEEN(bottom,top)
功能：RAND()是返回[0,1)之间的随机数，RANDBETWEEN(bottom,top)是返回指定两个数之间的随机整数
例如：=RANDBETWEEN(0,5)
返回值：2
解释：每次执行返回值都不同
2.5 ROUND(number,num_digits)
功能：将数字四舍五入指定位数
例如：=ROUND(2.34681,3)=SQRT(9)
返回值：2.347
2.6 SQRT(number)
功能：返回指定数字的算术平方根
例如：=SQRT(9)
返回值：3
2.7 SUM(number1,number2,...)
功能：求和
例如：=SUM(1,3,5)
返回值：9
2.8 SUMIF(range,criteria,[sum_range]) / SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,...)
功能：SUMIF()按条件求和,SUMIFS()按多个条件求和
例如：现给定如下销售表，"类别"位于Excel表位置：E27
| 类别 | 食物 | 销售额 |
| 蔬菜 | 西红柿 | ¥23,000.00 |
| 蔬菜 | 西芹 | ¥55,000.00 |
| 水果 | 橙子 | ¥8,000.00 |
| 黄油 | ¥4,000.00 | |
| 蔬菜 | 胡萝卜 | ¥42,000.00 |
| 水果 | 苹果 | ¥12,000.00 |
1.“水果”类别下所有食物的销售额之和(只存在一个条件：“水果”类)
输入：=SUMIF(E27:E33,"水果",G27:G33)
返回值：20000
2."蔬菜"类的以“西”开头食物的销售额之和(存在多个条件：“西”开头,"蔬菜"类)
输入：=SUMIFS(G27:G33,E27:E33,"蔬菜",F27:F33,"西*")
返回值：78000
解释：SUMIF()的待求和项在函数中是第三个输入值，而SUMIFS()待求和项在函数中是第一个输入值，此处要特别注意.
3.统计函数
3.1 AVERAGE(number1,number2,...)
功能：求平均值（求指定范围内容所有数字的和再除以数字的个数）
例如：=AVERAGE(10,20,30)
返回值：20
3.2 AVERAGEIF(range,criteria,[average_range]) / AVERAGEIFS(average_range,criteria_range1,criteria1,...)
功能：AVERAGEIF()按条件求指定值的平均值,AVERAGEIFS()按多个条件求指定值的平均值
例如：现给定如下价值表，"日期"位于Excel表位置：B28
| 日期 | 财产价值 | 佣金 |
| 2011/5/1 | 100000 | 7000 |
| 2011/5/2 | 200000 | 14000 |
| 2011/5/3 | 300000 | 21000 |
| 2011/5/4 | 400000 | 28000 |
| 2011/5/5 | 500000 | 35000 |
| 2011/5/6 | 600000 | 42000 |
1.1.求“佣金”的平均值，当求"财产价值"大于200000时
输入：=AVERAGEIF(C28:C34,">200000",D28:D34)
返回值：31500
2.求“佣金”的平均值，当求"财产价值"大于200000，且日期早于2011/5/5时
输入：=AVERAGEIFS(D28:D34,C28:C34,">200000",B28:B34,"<2011/5/5")
返回值：78000
解释：和条件求和函数类似，AVERAGEIF()的待求均值项在函数中是第三个输入值, AVERAGEIFS()待求均值项在函数中是第一个输入值，要特别注意.
3.3 COUNT(number1,number2,...) / COUNTA(number1,number2,...)
功能： COUNT()计算参数列表中数字的个数,COUNTA()计算参数列表中值的个数.
例如：=COUNT(10,20,"hello")
返回值：2
例如：=COUNTA(10,20,"hello")
返回值：3
解释：前者只计算数字个数，其他值不计算，所以返回2，后者计算所有个数，所以返回3
3.4 COUNTIF(range,criteria) / COUNTIFS(criteria_range1,criteria1,...)
功能：COUNTIF()计算区域内符合给定条件的单元格的数量,COUNTIFS()计算区域内符合多个条件的单元格的数量
例如：使用上述3.2的价值表，"日期"位于Excel表位置：B28
1.求表中“佣金”小于30000的个数
输入：=COUNTIF(D28:D34,"<30000")
返回值：4
2.求表中“佣金”小于30000的个数，且"财产价值"大于200000的个数
输入：=COUNTIFS(D28:D34,"<30000",C28:C34,">200000")
返回值：2
3.5 MAX(number1,number2,...) / MIN(number1,number2,...)
功能： MAX()返回参数列表中最大值,MIN()返回参数列表中最小值.(只计算数值)
例如：=MAX(10,20,"hello")
返回值：20
3.6FILTER(数组,包括,[if_empty])
功能： 筛选满足条件"包括"的数组
例如：销售表，"类别"位于Excel表位置：E27
| 类别 | 食物 | 销售额 |
| 蔬菜 | 西红柿 | ¥23,000.00 |
| 蔬菜 | 西芹 | ¥55,000.00 |
| 水果 | 橙子 | ¥8,000.00 |
| 黄油 | ¥4,000.00 | |
| 蔬菜 | 胡萝卜 | ¥42,000.00 |
| 水果 | 苹果 | ¥12,000.00 |
筛选出类别为蔬菜的食物
输入：=FILTER(E27:G33,E27:E33="蔬菜","无")
输出：
| 蔬菜 | 西红柿 | ¥23,000.00 |
| 蔬菜 | 西芹 | ¥55,000.00 |
| 蔬菜 | 胡萝卜 | ¥42,000.00 |
如果是多条件筛选，则第二个参数的条件之间用*连接，如
输入：=FILTER(E27:G33,(E27:E33="蔬菜")*(F27:F33="胡萝卜"),"无")
输出：
| 蔬菜 | 胡萝卜 | ¥42,000.00 |
4.日期函数
4.1 DATE(year,month,day) / TIME(hour,minute,second)
功能： DATE()根据年、月、日三个数值返回其表示的日期,TIME()根据时、分、秒三个数值返回其表示的时间的十进制格式
例如：=DATE(2023,11,16)
返回值：2023/11/16
例如：=TIME(13,30,17)
返回值：1:30 PM
4.2 DATEVALUE(date_text) / TIMEVALUE(time_text)
功能： DATEVALUE()返回由文本字符串表示的日期的十进制数字,TIMEVALUE()返回由文本字符串表示的时间的十进制数字
例如：=DATEVALUE(2023,11,16)
返回值：45246
例如：=TIMEVALUE("13:30")
返回值：0.5625
4.3 YEAR() / MINTH() / DAY() ; HOUR() / MINUTE() / SECOND()
功能：返回序列数表示的年/月/日;返回时间值的时/分/秒.
例如：给定如下日期，位于Excel表位置：H2
| 2023/11/16 13:30 |
例如：=YEAR(H2)
返回值：2023
例如：=MONTH(H2)
返回值：11
例如：=HOUR(H2)
返回值：13
4.4 TODAY() / NOW()
功能：TODAY()返回当前日期，NOW()返回当前日期和时间.
例如：=TODAY()
返回值：2023/11/16
例如：=NOW()
返回值：2023/11/16 13:51
4.5 EOMONTH(start_date,months) / EDATE(start_date,months)
功能：EOMONTH()返回指定日期加上months个月份数的月份的最后一天，EDATE()返回与指定日期加上months个月的日期的序列数.
例如：=EOMONTH(NOW(),3)
返回值：2024/2/29
例如：=EDATE(NOW(),3)
返回值：2024/2/16
4.6 DATEDIF(start_date,end_date,unit)
功能：计算两个日期之间的天数、月数、年数
具体返回值由unit确定，如下表所示，"start_date"位于Excel表位置：A14
| start_date | end_date | unit | 公式 | 返回值 | 说明 |
| 2018/1/1 | 2020/3/5 | Y | =DATEDIF(A15,B15,"Y") | 2 | 相差年数 |
| 2019/2/1 | 2023/4/10 | M | =DATEDIF(A16,B16,"M") | 50 | 相差总月数 |
| 2020/3/1 | 2022/10/1 | D | =DATEDIF(A17,B17,"D") | 944 | 相差总天数 |
| 2021/4/1 | 2023/9/28 | YM | =DATEDIF(A18,B18,"YM") | 5 | 一年内相差月数 |
| 2022/5/1 | 2023/11/11 | YD | =DATEDIF(A19,B19,"YD") | 194 | 一年内相差天数 |
| 2023/6/1 | 2023/11/16 | MD | =DATEDIF(A20,B20,"MD") | 15 | 一月内相差天数 |
| 2023/12/1 | 2023/11/16 | MD | =DATEDIF(A20,B21,"MD") | #NUM! | start_大于end_ |
解释：如果start_date大于end_date,则返回结果为：#NUM！.
5.逻辑函数
5.1 AND(logical1,logical2,...) / OR(logical1,logical2,...) / NOT(logical)
功能： AND()用于测试所有条件是否均为TRUE，OR()用于测试是否有为TRUE的条件，NOT()将TRUE或者FALSE的结果取反
| 值 | 函数 | 输出 |
| 0 | =AND(L5,L6<L7) | TURE |
| 0 | =AND(L5,L6>L7) | FALSE |
| 30 | =OR(L4,L5) | TURE |
| 50 | =OR(L3,L4) | FALSE |
| 70 | =NOT(1+1=2) | FALSE |
5.2 IF(logical_test,value_if_ture,value_if_false) / IFS(logical_test1,value_if_ture1,logical_test2,value_if_ture2,...)
功能： IF()判断单个条件是否为TURE，IFS()判断是否满足一个或多个条件，且返回符合第一个TURE条件的值
例如：=IF(1>2,"正确","错误")
返回值：错误
解释：IFS()只能在 Office 2019 或 Microsoft 365 上使用，如果没有环境但是仍需要判断多个条件，则可以使用多个IF()嵌套.
5.3 IFERROR(value,value_if_error)
功能： 可以使用该函数捕获和处理公式中的错误,公式计算结果为错误时返回指定的值;否则,它将返回公式的结果。
例如：=IFERROR(FIND("A","Hello world"),"查无此值")
返回值：查无此值
解释：前面提到FIND()是第二个参数里查找第一个参数，如果直接使用则返回：#VALUE！，该值可能会影响其他函数正常使用，影响后续分析，所以使用IFERROR()函数，在该函数中第一个参数无法返回正常值则返回第二个自定义的参数，此时表格中就没有错误值了.
6.查找与引用函数*(重要)
6.1 VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) / HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])
功能：VLOOKUP()纵向查找-按列查找,HLOOKUP()横向查找-按行查找
例如有成绩表1，"姓名"位于Excel表位置：A1
| 姓名 | 成绩 |
| 张1 | 89 |
| 张2 | 23 |
| 张3 | 54 |
| 张4 | 78 |
| 张5 | 96 |
| 张6 | 87 |
| 张7 | 77 |
该图标有两列，使用VLOOKUP()查找张3的成绩
输入：=VLOOKUP("张3",A1:B8,2,0)
返回值：54
解释：函数中第一个参数为需要查找的对象："张3"，第二个参数为列表位置：A1:B8，第三个参数为返回值在列表的第几列，此处在第二列所以输入：2，第四个参数为查找方式：0-FALSE-精确匹配，1-TURE-近似匹配.
缺点：此函数不能用于返回列在查找列之前，即如果成绩列在姓名列前则无法使用，要想解决此问题只有用XLOOKUP()，或者INDEX()与MATCH()嵌套使用，后续会详细提到.
现有成绩表2，"姓名"位于Excel表位置：A16
| 姓名 | 张1 | 张2 | 张3 | 张4 | 张5 |
| 成绩 | 90 | 74 | 88 | 78 | 89 |
该图标有两行，使用HLOOKUP()查张2的成绩
输入：=HLOOKUP("张2",A16:F17,2,FALSE)
返回值：74
解释：函数中第一个参数为需要查找的对象："张2"，第二个参数为列表位置：A16:F17，第三个参数为返回值在列表的第几行，此处在第二行所以输入：2，第四个参数为查找方式：0-精确匹配，1-近似匹配.
缺点：此函数不能用于返回列在查找列之前，即如果成绩列在姓名列前则无法使用，要想解决此问题只有用XLOOKUP()，或者INDEX()与MATCH()嵌套使用，后续会详细提到.
6.2 XLOOKUP**(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])
功能：纵向查找或者横向查找都可以.
例如：
1.在上述纵向的成绩表1中，查找"张3"成绩
输入：=XLOOKUP("张3",A1:A8,B1:B8,"无成绩")
返回值：54
解释：函数中第一个参数为需要查找的对象："张3"，第二个参数为查找对象，此处对象为一列，所以填入：A1:A8，第三个参数为返回值的列：B1:B8，第四个参数为如果未找到则返回值："无成绩"，此处找到成绩所以就返回成绩.
2.在上述横向的成绩表2中，查找"张2"成绩
输入：=XLOOKUP("张2",A16:F16,A17:F17,"无成绩")
返回值：74
解释：函数中第一个参数为需要查找的对象："张2"，第二个参数为查找对象，此处对象为一行，所以填入：A16:F16，第三个参数为返回值的行：A17:F17，第四个参数为如果未找到则返回值："无成绩"，此处找到成绩所以就返回成绩.
说明：该函数功能强大，但只能在 Excel 2021 及之后版本可用，只要环境允许尽量使用该函数.
6.3 COLUMN(reference) / ROW(reference)
功能：COLUMN()返回reference的列号对应的数字，ROW()返回reference的行号对应的数字.
例如该单元格位于Excel表位置：C29
| hello world |
输入：=COLUMN(C29)
返回值：3
输入：=ROW(C29)
返回值：29
6.4 MATCH(lookup_value,lookup_array,[match_type])
功能：返回参数lookup_value的行号对应的数字或列号对应的数字
例如成绩表1，"姓名"位于Excel表位置：A1
| 姓名 | 成绩 |
| 张1 | 89 |
| 张2 | 23 |
| 张3 | 54 |
| 张4 | 78 |
| 张5 | 96 |
| 张6 | 87 |
| 张7 | 77 |
函数中第一个参数为查找值，第二个参数为查找值所在的行或列，第三个参数为匹配模式，1-小于，0-精确匹配，-1-大于.
1.查找"张3"在姓名列的第几行
输入：=MATCH("张3",A1:A8,0)
返回值：4
2.查找"成绩"在第一行的第几列
输入：=MATCH("成绩",A1:B1,0)
返回值：2
解释：上述得到的两个返回值：4行、2列，组合起来得到B4，即是张3成绩的单元格位置，所以如果需要返回某个值，只要求得该值的行列即可.
6.5 INDEX(array,row_num,[colum_num])
功能：返回行号为row_num，列号为colum_num区域中的值.
例如在上述成绩表1
输入：=INDEX(A1:B8,5,1)
返回值：张4
解释：张四的位置在表中为：A5，函数第一个参数为列表位置：A1:B8，第二个参数为行号：5，第三个参数为列号：1
此函数可以搭配MATCH()函数混合使用，达到查找的目的
例如要查找“张4”的成绩
输入：=INDEX(A1:B8,MATCH("张4",A1:A8,0),MATCH("成绩",A1:B1,0))
返回值：78
6.6 UNIQUE(array,[by_col],[exactly_once])
功能： 对某范围数据按列或按行去重.
例如数字表，"10"位于Excel表位置：G33
| 10 | 12 | 12 | 15 | 19 | 20 | 20 | 30 |
| 12 | |||||||
| 12 | |||||||
| 15 | |||||||
| 19 | |||||||
| 20 | |||||||
| 20 | |||||||
| 30 |
函数中第一个参数值为需要去重的值，第二个参数为按行或者按列去重：0-FALSE-按行，1-TURE-按列，第三个参数为返回只出现一次的值或者只返回无重复的值：TURE-返回无重复的值，FALSE-返回只出现一次的值
1.按列去重，输入：=UNIQUE(G33:G40,0,0) 结果：
| 10 |
| 12 |
| 15 |
| 19 |
| 20 |
| 30 |
2.按行去重且返回从未重复的值，输入：=UNIQUE(G33:N33,TRUE,TRUE)
结果：
| 10 | 15 | 19 | 30 |
基本上大多数常用函数都总结了，只要这些函数能熟练应用就能满足大部分Excel数据分析的操作需求.
本篇文章由个人总结，目的是记录学习，也可给需要的人提供帮助，如有错误欢迎指出.
1682
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
