Excel函数 | 数据分析最常用这16个！

作者：Old iron

文末领取Excel学习大礼包

（超全Excel常用模板+函数+快捷键）

下面是我在平时中经常用到的16个函数，可能有一些大家经常用到的我没提到，也可能有一些写的不周全的地方或者错误的地方，希望与大家一起多多学习哈~

01

MID函数

函数定义：从一个文本字符串的指定位置开始,截取指定数目的字符

使用格式：MID(text, start_num, num_chars)

例子：

02

CONCATENATE函数

函数定义：将多个字符文本或单元格中的数据连接在一起,显示在一个单元格中

使用格式：CONCATENATE(text1,text2,……)

重点：也可以用&（和号）运算符代替函数CONCATENATE实现文本项的合并

例子：

03

AND函数

函数定义：检测所有的条件是否为真

使用格式：AND(logical1,logical2,……logical30)

注意事项：

• 如果指定的区域中不包含逻辑值或数值时,函数AND返回错误值#VALUE!.

• Logical1,logical2,……logical30表示待检测的1到30个条件值,各条件值可为TRUE或FALSE

例子：

可以与IF函数连用

04

IF函数

函数定义：根据条件满足与否返回不同的值

使用格式：

IF(logical_test,value_if_true,value_if_false)

白话：IF(条件,与条件一样时运算这个,与条件不同时运算这个)

例子：

常规用法

嵌套使用

公式如下：

'=IF(C13>=$H$10,$I$10,IF(C13>=$H$9,$I$9,IF(C13>=$H$8,$I$8,IF(C13>=$H$7,$I$7,IF(C13>=$H$6,$I$6,IF(C13>=$H$5,$I$5,0))))))

05

DATEDIF函数

函数定义：计算期间内的年数、月数、天数

使用格式：

• DATEDIF(start_date,end_date,'y')

• =DATEDIF(start_date,end_date,'m')

• =DATEDIF(start_date,end_date,'d')

• =DATEDIF(start_date,end_date,'ym')

• =DATEDIF(start_date,end_date,'yd')

• =DATEDIF(date1,date2,'md')

白话：DATEDIF(开始日期,结束日期,要计算的单位)

注意：

• y:计算满年数,返回值为0以上的整数

• m:计算满月数,返回值为0以上的整数;d:计算满日数,返回值为0以上的整数

• ym:计算不满一年的月数,返回值为1~11之间的整数

• yd计算不满一年的天数,返回值为0~365之间的整数

• md:计算不满意一个月的天数,返回值为0~30之间的整数

例子：

06

COUNTIF函数

函数定义：计算满足条件的单元格计数

使用格式：COUNTIF(range,criteria)

白话：COUNTIF(要找的内容所在的区域,要找的内容)

注意事项：

指定的条件必须用 ' ' (双引号括起来),如 '>=100、'男' 等.但,当指定条件为引用单元格时无需双引号括住.通配符使用参看SUMIF函数中的通配符说明

例子：

COUNTIF函数几种用法：

• 求包含值139的单元格数量 '=COUNTIF($D$4:$D$14,139)

• 求包含负值的单元格数量 '=COUNTIF($C$4:$C$14,'<0')

• 求不等于0 的单元格数量 '=COUNTIF($C$4:$C$14,'<>0')

• 求大于等于5的单元格数量 '=COUNTIF($C$4:$C$14,'>=5')

• 求等于单元格B45中内容的单元格数量 '=COUNTIF($B$4:$B$14,B4)

• 求大于单元格E45中内容的单元格数量 '=COUNTIF($E$4:$E$14,'>'&E4)

• 求包含文本内容的单元格数量 '=COUNTIF($B$4:$B$14,'*')

• 求包含六个字符内容的单元格数量 '=COUNTIF($B$4:$B$14,'??????')

• 求在文本中任何位置包含单词'文胸'字符内容的单元格数量 '=COUNTIF($B$4:$B$14,'*文胸*')

• 求包含以英文'D'(不分大小写)开头内容的单元格数量 '=COUNTIF($B$4:$B$14,'D*')

• 求包含当前日期的单元格数量 '=COUNTIF($E$4:$E$14,TODAY())

• 求大于平均值的单元格数量 '=COUNTIF($D$4:$D$14,'>'&AVERAGE($D$5:$D$14))

与IF连用的几种查找方式

统计区域内不重复数据数

公式：'{=SUM(1/COUNTIF(D5:D14,D5:D14))}

这里其实输入的是=SUM(1/COUNTIF(D5:D14,D5:D14))，然后按ctrl+shift+enter三键结束。

下面公式加了IF判断是否是空格的嵌套,避免出现#DIV/0!错误.

'{{=SUM(IF(D5:D14<>'',1/COUNTIF(D5:D14,D5:D14)))}}

或者可以用

=SUM(IF(ISBLANK(D5:D14),'',1/COUNTIF(D5:D14,D5:D14))),然后按ctrl+shift+enter三键结束。

07

SUMIF函数

函数定义：对满足条件的单元格的数值求和

使用格式：SUMIF(range,criteria,sum_range)

参数解释：

• range：为用于条件判断的单元格区域.指定作为搜索对象的单元格区域

• Criteria：为确定哪些单元格将被相加求和的条件,其形式可以为数字、表达式、文本或通配符

• Sum_range：是需要求和的实际单元格

几种基本用法：

1. 以“文胸”开头的任意文本的销量

=SUMIF($B$4:$B$14,'文胸*',$C$4:$C$14)

注意：如果是“文胸~*”，则此时的“*”就是字符，不是通配符，需要准确查找文本为“文胸*”的销量合计

2. “文胸”后面一定是三个字符的文本的销量

=SUMIF($B$4:$B$4,'文胸???',$C$4:$C$4)

注意：如果是“文胸~???”，则此时的“?”就是字符，不是通配符，需要准确查找文本为“文胸???”的销量合计

3. 销售大于等于5件的销售合计

=SUMIF($C$4:$C$14,'>=5',$C$4:$C$14)

4. 查找内容为c20的销售合计

=SUMIF($B$4:$B$14,C20,$C$4:$C$14)

08

DCOUNT函数

函数定义：计算满足条件的数值的个数

使用格式：DCOUNT(database,field,criteria)

参数定义：

• database: 构成列表或数据库的单元格区域.数据库是包含一组相关数据的列表,其中包含相关信息的行为记录,而包含数据的列为字段.列表的第一行包含着每一列的标志项.

• Field: 指定函数所使用的数据列.列表中的数据列必须在第一行具有标志项.Field可以是文本,即两端带引号的标志项,如'使用年数'或'产量'；此外,Field也可以是代表列表中数据列位置的数字：1表示第一列,2表示第二列,等等.

• Criteria: 为一组包含给定条件的单元格区域.可以为参数criteria指定任意区域,只要它至少包含一个列标志和列标志下方用于设定条件的单元格.
  

注意：

参数field为可选项,如果省略,函数DCOUNT返回数据库中满足条件criteria的所有记录数

例子：

公式：

1. 指定项目的有效支出

=DCOUNT(B3:E9,E3,G3:G4)

这里的结果是2，返回的是10月8日和10月10日的值。

2. 指定项目的支出

=DCOUNT(B3:E9,,G3:G4)

参数field为可选项,如果省略,函数DCOUNT返回数据库中满足条件criteria的所有记录数

这里的结果是3，返回的是10月8日，10月10日和10月15日的值。

09

ISERROR函数

函数定义：查看是否为错误，查看是否值为任意错误值(#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!).

使用格式：ISERROR(value)

参数定义：value为需要进行检验的数值。

注意事项：

• S类函数的参数value是不可转换的

• IS类函数在用公式检验计算结果时十分有用，当它与函数IF结合在一起使用时，可以提供一种方法用来在公式中查出错误值

这个函数比较简单，大家可以随意试一下。

10

VLOOKUP函数

（敲黑板，重点！！）

函数定义：按照垂直方向搜索区域

使用格式：

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

参数定义：

• Lookup_value：为需要在数组第一列中查找的数值.Lookup_value可以为数值、引用或文本字符串.

• Table_array: 为需要在其中查找数据的数据表.可以使用对区域或区域名称的引用,例如数据库或列表.

• Col_index: 为table_array中待返回的匹配值的列序号. Col_index_num为1时,返回table_array第一列中的数值；col_index_num为2,返回table_array第二列中的数值,以此类推.如果col_index_num小于1,函数VLOOKUP返回错误值值#VALUE!；如果col_index_num大于table_array的列数, 函数VLOOKUP返回错误值#REF!

• Range_lookup：为一逻辑值,指明函数VLOOKUP返回时是精确匹配还是近似匹配.如果为TRUE或省略,则返回近似匹配值.也就是说.如果找不到精确匹配值,则返回小于lookup_value的最大数值；如果range_value为FALSE,函数VLOOKUP将返回精确匹配值.如果找不到,则返回错误值#N/A

注意：

• 如果range_lookup为TRUE,则table_array的第一列中的数值必须按升序排列：…、 -2、-1、0、1、2、…、-Z、FALSE、TRUE；否则,函数VLOOKUP不能返回正确的数值.如果range_lookup为FALSE,table_array不必进行排序

• Table_array的第一列中的数值可以为文本、数字或逻辑值

• 文本不区分大小写

• 如果函数VLOOKUP找不到lookup_value,且range_lookup为TRUE,则使用小于等于lookup_value的最大值

• 如果lookup_value小于table_array第一列中的最小数值,函数VLOOKUP返回错误值#N/A

• 如果函数VLOOKUP找不到lookup_value且range_lookup为FALSE，函数VLOOKUP返回错误值#N/A

• 若有多个符合条件的情况:vlookup返回的是第一个满足条件的值,lookup返回的是最后一个满足条件的值

例子：

公式：=VLOOKUP(H5,B5:D13,3,0)

精确查找是vlookup最基本也是最常用的功能，对于数据量大的查找,其速度比菜单中的查找还快.设置vlookup第四个参数为false或0，即为精确查找。

11

LEN函数

函数定义：统计文本字符串中字符数目(计算文本的长度)

使用格式：LEN(text)

参数定义：是要查找其长度的文本.空格将作为字符进行计数.

例子：

=LEN(B13)，结果为7

12

SEARCH函数

函数定义：检索字符位置(不区分大小写)

使用格式：SEARCH(find_text,within_text,start_num)

参数定义：

• Find_text：是要查找的文本.可以在find_text中使用通配符,包括问号(?)和星号(*).问号可匹配任意的单个字符,星号可匹配任意一串字符.如果要查找真正的问号或星号,请在该字符前键入波形符(~).

• Within_text：是要在其中查找find_text的文本.

• Start_num：是within_text中开始查找的字符的编号.

要点：使用start_num可跳过指定数目的字符.例如,假定使用文本字符 AYF0093.YoungMensApparel, 如果要查找文本字符串中说明部分的第一个Y的编号,则可将start_num设置为8,这样就不会查找文本的序列号部分.SEARCH将从第8个字符开始查找,而在下一个字符处即可找到find_text,于是返回编号9.SEARCH总是从within_text的起始处返回字符编号，如果start_num大于1,也会对跳过的字符进行计数

注意事项：

• SEARCH和SEARCHB在查找文本时不区分大小写

• SEARCH和SEARCHB类似于FIND和FINDB,但FIND和FINDB区分大小写

• 如果没有找到find_text,则返回错误值#VALUE!

• 如果忽略start_num,则假定其为1

• 如果start_num不大于0(零)或大于within_text,则返回错误值#VALUE!

13

FREQUENCY函数

函数定义：计算区间里所含数值的个数

使用格式：FREQUENCY(data_array,bins_array)

参数定义：

• Data_array：为一数组或对一组数值的引用,用来计算频率.如果data_array中不包含任何数值,函数FREQUENCY返回零数组.

• Bins_array:为间隔的数组或对间隔的引用,该间隔用于对data_array中的数值进行分组.如果bins_array中不包含任何数值,函数FREQUENCY返回data_array中元素的个数.

例子：

公式：{=FREQUENCY($C$19:$C$30,$E$19:$E$23)}

14

INDEX函数

1.INDEX函数（数组形式）

函数定义：(数组形式)返回行和列交叉位置的值

使用格式：INDEX(array,row_num,column_num)

参数定义：

• Array: 为单元格区域或数组常量.如果数组只包含一行或一列,则相对应的参数row_num或column_num为可选.如果数组有多行和多列,但只使用row_num或column_num,函数INDEX返回数组中的整行或整列,且返回值也为数组.

• Row_num: 数组中某行的行序号,函数从该行返回数值.如果省略row_num,则必须有column_num.

• Column_num: 数组中某列的列序号,函数从该列返回数值.如果省略column_num,则必须有row_num.

要点：

• INDEX函数有两种语法形式：数组和引用.数组形式通常返回数值或数值数组,引用形式通常返回引用.当函数INDEX的第一个参数为数组常数时,使用数组形式.

• 此处的行序号参数(row_num)和列序号参数(column_num)是相对于所引用的单元格区域而言的,不是Excel工作表中的行或列序号.

注意：

• 如果同时使用row_num和column_num,函数INDEX返回row_num和column_num交叉处的单元格的数值

• 如果将row_num或column_num设置为0,函数INDEX则分别返回整个列或行的数组数值.若要使用以数组形式返回的值,请将INDEX函数以数组公式形式输入,对于行以水平单元格区域的形式输入,对于列以垂直单元格区域的形式输入.若要输入数组公式,请按Ctrl+Shift+Enter

• Row_num和column_num必须指向array中的某一单元格；否则,函数INDEX返回错误值#REF!

2.INDEX函数（引用形式）

函数定义：(单元格引用方式)返回行和列交差位置的单元格引用

使用格式：

INDEX(reference,row_num,column_num,area_num)

参数定义：

• Reference：对一个或多个单元格区域的引用,如果为引用输入一个不连续的区域,必须用括号括起来.如果引用中的每个区域只包含一行或一列,则相应的参数row_num或column_num分别为可选项.例如,对于单行的引用,可以使用函数INDEX(reference,,column_num).

• Row_num：引用中某行的行序号,函数从该行返回一个引用.

• Column_num：引用中某列的列序号,函数从该列返回一个引用.

• Area_num：选择引用中的一个区域,并返回该区域中row_num和column_num的交叉区域.选中或输入的第一个区域序号为1,第二个为2,以此类推.如果省略area_num,函数INDEX使用区域1.

注意事项：

• 在通过reference和area_num选择了特定的区域后,row_num和column_num将进一步选择指定的单元格：row_num1为区域的首行,column_num1为首列,以此类推.函数INDEX返回的引用即为row_num和column_num的交叉区域

• 如果将row_num或column_num设置为0,函数INDEX分别返回对整个列或行的引用

• Row_num、column_num和area_num必须指向reference中的单元格；否则,函数INDEX返回错误值#REF!.如果省略row_num和column_num,函数INDEX返回由area_num所指定的区域

• 函数INDEX的结果为一个引用,且在其他公式中也被解释为引用.根据公式的需要,函数INDEX的返回值可以作为引用或是数值.例如,公式CELL('width',INDEX(A1:B2,1,2))等价于公式CELL('width',B1).CELL函数将函数INDEX的返回值作为单元格引用.而在另一方面,公式2*INDEX(A1:B2,1,2)将函数INDEX的返回值解释为B1单元格中的数字

例子：

公式：=INDEX((B4:D12,G4:H5),2,2,2)

返回的是第二个区域的值。

15

MATCH函数

函数定义：返回搜索值的相对位置

引用形式：

MATCH(lookup_value,lookup_array,match_type)

参数定义：

• Lookup_value：为需要在数据表中查找的数值.为需要在Look_array中查找的数值.可以为数值(数字、文本或逻辑值)或对数字、文本或逻辑值的单元格引用.

• Lookup_array：可能包含所要查找的数值的连续单元格区域.Lookup_array应为数组或数组引用.

• Match_type：为数字-1、0或1.Match-type指明MicrosoftExcel如何在lookup_array中查找lookup_value.
  

要点：函数MATCH返回lookup_array中目标值的位置,而不是数值本身。例如,MATCH('b',{'a','b','c'},0)返回2,即“b”在数组{'a','b','c'}中的相应位置

注意事项：

• 如果match_type为1,函数MATCH查找小于或等于lookup_value的最大数值.Lookup_array必须按升序排列：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE

• 如果match_type为0,函数MATCH查找等于lookup_value的第一个数值.Lookup_array可以按任何顺序排列

• 如果match_type为-1,函数MATCH查找大于或等于lookup_value的最小数值. Lookup_array必须按降序排列：TRUE、FALSE、Z-A、...、2、1、0、-1、-2、...,等等

• 如果省略match_type,则假设为1

• 查找文本值时,函数MATCH不区分大小写字母

• 如果函数MATCH查找不成功,则返回错误值#N/A

• 如果match_type为0且lookup_value为文本,lookup_value可以包含通配符、星号(*)和问号(?).星号可以匹配任何字符序列；问号可以匹配单个字符

例子：

公式：=MATCH(F5,B5:B14,1)

16

OFFSET函数

函数定义：计算指定位置的单元格引用

使用格式：OFFSET(reference,rows,cols,height,width)

参数定义：

• Reference：作为偏移量参照系的引用区域.Reference必须为对单元格或相连单元格区域的引用；否则,函数OFFSET返回错误值#VALUE!

• Rows：相对于偏移量参照系的左上角单元格,上(下)偏移的行数.如果使用5作为参数Rows,则说明目标引用区域的左上角单元格比reference低5行.行数可为正数(代表在起始引用的下方)或负数(代表在起始引用的上方)

• Cols：相对于偏移量参照系的左上角单元格,左(右)偏移的列数.如果使用5作为参数Cols,则说明目标引用区域的左上角的单元格比reference靠右5列.列数可为正数(代表在起始引用的右边)或负数(代表在起始引用的左边)

• Height：高度,即所要返回的引用区域的行数.Height必须为正数

• Width：宽度,即所要返回的引用区域的列数.Width必须为正数

注意事项：

• 如果行数和列数偏移量超出工作表边缘,函数OFFSET返回错误值#REF!

• 如果省略height或width,则假设其高度或宽度与reference相同

• 函数OFFSET实际上并不移动任何单元格或更改选定区域,它只是返回一个引用.函数OFFSET可用于任何需要将引用作为参数的函数.例如,公式SUM(OFFSET(C2,1,2,3,1))将计算比单元格C2靠下1行并靠右2列的3行1列的区域的总值

公式：

=OFFSET(B5,6,2)

=SUM(OFFSET(B5,6,2,1,2))

END

本文为转载分享，若侵权请联系后台删除

今日福利

互动话题“说出一种你常用的Excel函数”

评论区留言分享即可获得爱数据精选Excel学习大礼包，超全Excel常用模板+快捷键+函数，人手一份，速速来领哦~