16个数据分析最常用的【Excel函数】 ！

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

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

使用格式：ISERROR(value)

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

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

（敲黑板，重点！！）

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

使用格式：

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

例子：

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

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

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

使用格式：LEN(text)

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

例子：

=LEN(B13)，结果为7

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

使用格式：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,也会对跳过的字符进行计数

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

使用格式：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)}

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工作表中的行或列序号.

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)

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

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

引用形式：

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)

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

使用格式：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(B5,6,2)

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