需要掌握的EXCEL函数.docx

需要掌握的EXCEL函数.docx

《需要掌握的EXCEL函数.docx》由会员分享，可在线阅读，更多相关《需要掌握的EXCEL函数.docx（31页珍藏版）》请在冰豆网上搜索。

需要掌握的EXCEL函数

查找函数

1.ADDRESS:

ADDRESS（row_num,column_num,[abs_num],[a1],[sheet_text]）

ADDRESS函数语法具有下列参数：

row_num    必需。

一个数值，指定要在单元格引用中使用的行号。

column_num    必需。

一个数值，指定要在单元格引用中使用的列号。

abs_num    可选。

一个数值，指定要返回的引用类型。

2.CHOOSE:

CHOOSE（index_num,value1,value2,...）

Index_num    指定所选定的值参数。

Index_num必须为1到254之间的数字，或者是包含数字1到254的公式或单元格引用。

∙如果index_num为1，函数CHOOSE返回value1；如果为2，函数CHOOSE返回value2，以此类推。

∙如果index_num小于1或大于列表中最后一个值的序号，函数CHOOSE返回错误值#VALUE!

。

∙如果index_num为小数，则在使用前将被截尾取整。

Value1,value2,...    为1到254个数值参数，函数CHOOSE基于index_num，从中选择一个数值或一项要执行的操作。

参数可以为数字、单元格引用、定义名称、公式、函数或文本。

注解

∙如果index_num为一个数组（数组:

用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。

数组区域共用一个公式；数组常量是用作参数的一组常量。

），则在计算函数CHOOSE时，将计算每一个值。

∙函数CHOOSE的数值参数不仅可以为单个数值，也可以为区域引用。

例如，下面的公式：

=SUM（CHOOSE（2,A1:

A10,B1:

B10,C1:

C10））

相当于：

=SUM（B1:

B10）

然后基于区域B1:

B10中的数值返回值。

●函数CHOOSE先被计算，返回引用B1:

B10。

然后函数SUM用B1:

B10进行求和计算。

即函数CHOOSE的结果是函数SUM的参数。

3.COLUMN:

COLUMN函数语法具有下列参数（参数:

为操作、事件、方法、属性、函数或过程提供信息的值。

）：

∙reference    可选。

要返回其列号的单元格或单元格区域（区域:

工作表上的两个或多个单元格。

区域中的单元格可以相邻或不相邻。

）。

如果省略参数reference或该参数为一个单元格区域，并且COLUMN函数是以水平数组公式的形式输入的，则COLUMN函数将以水平数组的形式返回参数reference的列号。

将公式作为数组公式输入    从公式单元格开始，选择要包含数组公式的区域。

按F2，然后按Ctrl+Shift+Enter。

∙如果参数reference为一个单元格区域，并且COLUMN函数不是以水平数组公式的形式输入的，则COLUMN函数将返回最左侧列的列号。

∙如果省略参数reference，则假定该参数为对COLUMN函数所在单元格的引用。

∙参数reference不能引用多个区域。

4.COLUMNS:

COLUMNS（array）

Array    为需要得到其列数的数组或数组公式（数组公式:

数组公式对一组或多组值执行多重计算，并返回一个或多个结果。

数组公式括于大括号（{}）中。

按Ctrl+Shift+Enter可以输入数组公式。

），或对单元格区域的引用。

5.HLOOKUP:

HLOOKUP（lookup_value,table_array,row_index_num,range_lookup）

Lookup_value    为需要在数据表第一行中进行查找的数值。

Lookup_value可以为数值、引用或文本字符串。

Table_array    为需要在其中查找数据的数据表。

使用对区域或区域名称的引用。

∙Table_array的第一行的数值可以为文本、数字或逻辑值。

∙如果range_lookup为TRUE，则table_array的第一行的数值必须按升序排列：

...-2、-1、0、1、2、…、A-Z、FALSE、TRUE；否则，函数HLOOKUP将不能给出正确的数值。

如果range_lookup为FALSE，则table_array不必进行排序。

∙文本不区分大小写。

∙将数值按升序排列（从左至右）。

有关详细信息，请参阅排序数据。

Row_index_num    为table_array中待返回的匹配值的行序号。

Row_index_num为1时，返回table_array第一行的数值，row_index_num为2时，返回table_array第二行的数值，以此类推。

如果row_index_num小于1，函数HLOOKUP返回错误值#VALUE!

；如果row_index_num大于table_array的行数，函数HLOOKUP返回错误值#REF!

。

Range_lookup    为一逻辑值，指明函数HLOOKUP查找时是精确匹配，还是近似匹配。

如果为TRUE或省略，则返回近似匹配值。

也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值。

如果lookup_value为FALSE，函数HLOOKUP将查找精确匹配值，如果找不到，则返回错误值#N/A。

注解

∙如果函数HLOOKUP找不到lookup_value，且range_lookup为TRUE，则使用小于lookup_value的最大值。

∙如果函数HLOOKUP小于table_array第一行中的最小数值，函数HLOOKUP返回错误值#N/A。

∙如果range_lookup为FALSE且lookup_value为文本，则可以在lookup_value中使用通配符、问号（?

）和星号（*）。

问号匹配任意单个字符；星号匹配任意字符序列。

如果要查找实际的问号或星号，请在该字符前键入波形符（~）。

6.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。

∙例如，如果引用描述的单元格为（A1:

B4,D1:

E4,G1:

H4），则area_num1为区域A1:

B4，area_num2为区域D1:

E4，而area_num3为区域G1:

H4。

注解

∙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单元格中的数字。

7.INDIRECT:

INDIRECT（ref_text,a1）

Ref_text    为对单元格的引用，此单元格可以包含A1-样式的引用、R1C1-样式的引用、定义为引用的名称或对文本字符串单元格的引用。

如果ref_text不是合法的单元格的引用，函数INDIRECT返回错误值#REF!

。

∙如果ref_text是对另一个工作簿的引用（外部引用），则那个工作簿必须被打开。

如果源工作簿没有打开，函数INDIRECT返回错误值#REF!

。

∙如果ref_text引用的单元格区域超出行限制1,048,576或列限制16,384（XFD），则INDIRECT返回#REF!

错误。

 注释   此行为不同于MicrosoftOfficeExcel2007之前的Excel版本，早期的版本会忽略超出的限制并返回一个值。

A1    为一逻辑值，指明包含在单元格ref_text中的引用的类型。

∙如果a1为TRUE或省略，ref_text被解释为A1-样式的引用。

∙如果a1为FALSE，ref_text被解释为R1C1-样式的引用

8. LOOKUP:

LOOKUP（lookup_value,lookup_vector,result_vector）

LOOKUP函数向量形式语法具有以下参数（参数:

为操作、事件、方法、属性、函数或过程提供信息的值。

）：

∙lookup_value    必需。

LOOKUP在第一个向量中搜索的值。

Lookup_value可以是数字、文本、逻辑值、名称或对值的引用。

∙lookup_vector    必需。

只包含一行或一列的区域。

lookup_vector中的值可以是文本、数字或逻辑值。

 要点   lookup_vector中的值必须以升序排列：

...,-2,-1,0,1,2,...,A-Z,FALSE,TRUE。

否则，LOOKUP可能无法返回正确的值。

大写文本和小写文本是等同的。

∙result_vector    必需。

只包含一行或一列的区域。

result_vector参数必须与lookup_vector大小相同。

说明

∙如果LOOKUP函数找不到lookup_value，则它与lookup_vector中小于或等于lookup_value的最大值匹配。

∙如果lookup_value小于lookup_vector中的最小值，则LOOKUP会返回#N/A错误值。

9.MATCH:

MATCH（lookup_value,lookup_array,[match_type]）

MATCH函数语法具有以下参数（参数:

为操作、事件、方法、属性、函数或过程提供信息的值。

）：

∙lookup_value    必需。

需要在lookup_array中查找的值。

例如，如果要在电话簿中查找某人的电话号码，则应该将姓名作为查找值，但实际上需要的是电话号码。

lookup_value参数可以为值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。

∙lookup_array    必需。

要搜索的单元格区域。

∙match_type    可选。

数字-1、0或1。

match_type参数指定Excel如何在lookup_array中查找lookup_value的值。

此参数的默认值为1。

10.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列的区域的总值。

11.ROW:

ROW（reference）

Reference    为需要得到其行号的单元格或单元格区域。

∙如果省略reference，则假定是对函数ROW所在单元格的引用。

∙如果reference为一个单元格区域，并且函数ROW作为垂直数组（数组:

用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。

数组区域共用一个公式；数组常量是用作参数的一组常量。

）输入，则函数ROW将以垂直数组的形式返回reference的行号。

∙Reference不能引用多个区域。

12.ROWS:

ROWS（array）

Array    为需要得到其行数的数组、数组公式（数组公式:

数组公式对一组或多组值执行多重计算，并返回一个或多个结果。

数组公式括于大括号（{}）中。

按Ctrl+Shift+Enter可以输入数组公式。

）或对单元格区域的引用

13.VLOOKUP:

VLOOKUP（lookup_value,table_array,col_index_num,range_lookup）

Lookup_value    为需要在表格数组（数组:

用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。

数组区域共用一个公式；数组常量是用作参数的一组常量。

）第一列中查找的数值。

Lookup_value可以为数值或引用。

若lookup_value小于table_array第一列中的最小值，VLOOKUP返回错误值#N/A。

Table_array    为两列或多列数据。

使用对区域或区域名称的引用。

table_array第一列中的值是由lookup_value搜索的值。

这些值可以是文本、数字或逻辑值。

文本不区分大小写。

Col_index_num    为table_array中待返回的匹配值的列序号。

Col_index_num为1时，返回table_array第一列中的数值；col_index_num为2，返回table_array第二列中的数值，以此类推。

如果col_index_num：

∙小于1，VLOOKUP返回错误值#VALUE!

。

∙大于table_array的列数，VLOOKUP返回错误值#REF!

。

Range_lookup    为逻辑值，指定希望VLOOKUP查找精确的匹配值还是近似匹配值：

∙如果为TRUE或省略，则返回精确匹配值或近似匹配值。

也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值。

table_array第一列中的值必须以升序排序；否则VLOOKUP可能无法返回正确的值。

有关详细信息，请参阅排序数据。

∙如果为FALSE，VLOOKUP将只寻找精确匹配值。

在此情况下，table_array第一列的值不需要排序。

如果table_array第一列中有两个或多个值与lookup_value匹配，则使用第一个找到的值。

如果找不到精确匹配值，则返回错误值#N/A。

注解

∙在table_array第一列中搜索文本值时，请确保table_array第一列中的数据没有前导空格、尾部空格、直引号（'或"）与弯引号（‘或“）不一致或非打印字符。

否则，VLOOKUP可能返回不正确或意外的值。

有关详细信息，请参阅CLEAN和TRIM。

∙在搜索数字或日期值时，请确保table_array第一列中的数据未存储为文本值。

否则，VLOOKUP可能返回不正确或意外的值。

有关详细信息，请参阅将保存为文本的数字转换为数字值。

∙如果range_lookup为FALSE且lookup_value为文本，则可以在lookup_value中使用通配符、问号（?

）和星号（*）。

问号匹配任意单个字符；星号匹配任意字符序列。

如果要查找实际的问号或星号，请在该字符前键入波形符（~）。

日期和时间函数

14.DATE:

DATE（year,month,day）

DATE函数语法具有下列参数（参数:

为操作、事件、方法、属性、函数或过程提供信息的值。

）：

∙year    必需。

year参数的值可以包含一到四位数字。

Excel将根据计算机所使用的日期系统来解释year参数。

默认情况下，MicrosoftExcelforWindows将使用1900日期系统，而MicrosoftExcelforMacintosh将使用1904日期系统。

 提示   为避免出现意外结果，建议对year参数使用四位数字。

例如，使用“07”将返回“1907”作为年值。

ExcelforWindows如何使用1900日期系统？

∙如果year介于0（零）到1899之间（包含这两个值），则Excel会将该值与1900相加来计算年份。

例如，DATE（108,1,2）将返回2008年1月2日（1900+108）。

∙如果year介于1900到9999之间（包含这两个值），则Excel将使用该数值作为年份。

例如，DATE（2008,1,2）将返回2008年1月2日。

∙如果year小于0或大于等于10000，则Excel将返回错误值#NUM!

。

ExcelfortheMacintosh如何使用1904日期系统？

∙如果year介于4到1899之间（包含这两个值），则Excel会将该值与1900相加来计算年份。

例如，DATE（108,1,2）将返回2008年1月2日（1900+108）。

∙如果year介于1904到9999之间（包含这两个值），则Excel将使用该数值作为年份。

例如，DATE（2008,1,2）将返回2008年1月2日。

∙如果year小于4或大于等于10000，或者位于1900到1903之间（包含这两个值），则Excel将返回错误值#NUM!

。

∙month    必需。

一个正整数或负整数，表示一年中从1月至12月（一月到十二月）的各个月。

如果month大于12，则month从指定年份的一月份开始累加该月份数。

例如，DATE（2008,14,2）返回表示2009年2月2日的序列号。

如果month小于1，month则从指定年份的一月份开始递减该月份数，然后再加上1个月。

例如，DATE（2008,-3,2）返回表示2007年9月2日的序列号。

∙Day    必需。

一个正整数或负整数，表示一月中从1日到31日的各天。

如果day大于指定月份的天数，则day从指定月份的第一天开始累加该天数。

例如，DATE（2008,1,35）返回表示2008年2月4日的序列号。

如果day小于1，则day从指定月份的第一天开始递减该天数，然后再加上1天。

例如，DATE（2008,1,-15）返回表示2007年12月16日的序列号。

注释   Excel将日期存储为可用于计算的序列号。

默认情况下，1900年1月1日的序列号是1，而2008年1月1日的序列号是39448，这是因为它距1900年1月1日有39447天。

MicrosoftExcelfortheMacintosh使用另外一个日期系统作为其默认日期系统

15.DAY:

DAY（serial_number）

Serial_number    要查找的那一天的日期。

应使用DATE函数输入日期，或者将函数作为其他公式或函数的结果输入。

例如，使用函数DATE（2008,5,23）输入2008年5月23日。

如果日期以文本形式输入，则会出现问题。

16.HOUR:

HOUR（serial_number）

Serial_number    表示一个时间值，其中包含要查找的小时。

时间有多种输入方式：

带引号的文本字符串（例如"6:

45PM"）、十进制数（例如0.78125表示6:

45PM）或其他公式或函数的结果（例如TIMEVALUE（"6:

45PM"））。

注解

MicrosoftExcelforWindows和ExcelforMacintosh使用不同的默认日期系统。

时间值为日期值的一部分，并用十进制数来表示（例如12:

00PM可表示为0.5，因为此时是一天的一半）。

17.MONTH:

MONTH（serial_number）

Serial_number    表示要查找的月份的日期。

应使用DATE函数输入日期，或者将函数作为其他公式或函数的结果输入。

例如，使用函数DATE（2008,5,23）输入2008年5月23日。

如果日期以文本形式输入，则会出现问题。

18.NOW:

返回当前日期和时间的序列号。

如果在输入该函数前，单元格格式为“常规”，Excel会将单元格格式更改为与“控制面板”的区域日期和时间设置中指定的日期和时间格式相同的格式。

可以在功能区“开始”选