EXCEL函数汇总查找与引用函数EXCEL帮助导出.docx
- 文档编号:28672483
- 上传时间:2023-07-19
- 格式:DOCX
- 页数:47
- 大小:41.41KB
EXCEL函数汇总查找与引用函数EXCEL帮助导出.docx
《EXCEL函数汇总查找与引用函数EXCEL帮助导出.docx》由会员分享,可在线阅读,更多相关《EXCEL函数汇总查找与引用函数EXCEL帮助导出.docx(47页珍藏版)》请在冰豆网上搜索。
EXCEL函数汇总查找与引用函数EXCEL帮助导出
EXCEL查找与引用函数汇总
(共计18个)
1、ADDRESS
按照给定的行号和列标,建立文本类型的单元格地址。
语法
ADDRESS(row_num,column_num,abs_num,a1,sheet_text)
Row_num 在单元格引用中使用的行号。
Column_num 在单元格引用中使用的列标。
Abs_num 指定返回的引用类型。
Abs_num
返回的引用类型
1或省略
绝对引用
2
绝对行号,相对列标
3
相对行号,绝对列标
4
相对引用
A1 用以指定A1或R1C1引用样式的逻辑值。
如果A1为TRUE或省略,函数ADDRESS返回A1样式的引用;如果A1为FALSE,函数ADDRESS返回R1C1样式的引用。
Sheet_text 为一文本,指定作为外部引用的工作表的名称,如果省略sheet_text,则不使用任何工作表名。
示例
1
2
3
4
5
6
A
B
公式
说明(结果)
=ADDRESS(2,3)
绝对引用($C$2)
=ADDRESS(2,3,2)
绝对行号,相对列标(C$2)
=ADDRESS(2,3,2,FALSE)
在R1C1引用样式中的绝对行号,相对列标(R2C[3])
=ADDRESS(2,3,1,FALSE,"[Book1]Sheet1")
对其他工作簿或工作表的绝对引用([Book1]Sheet1!
R2C3)
=ADDRESS(2,3,1,FALSE,"EXCELSHEET")
对其他工作表的绝对引用('EXCELSHEET'!
R2C3)
2、AREAS
返回引用中包含的区域个数。
区域表示连续的单元格区域或某个单元格。
语法
AREAS(reference)
Reference 对某个单元格或单元格区域的引用,也可以引用多个区域。
如果需要将几个引用指定为一个参数,则必须用括号括起来,以免MicrosoftExcel将逗号作为参数间的分隔符。
请参阅以下示例:
示例
1
2
3
4
A
B
公式
说明(结果)
=AREAS(B2:
D4)
引用中包含的区域个数
(1)
=AREAS((B2:
D4,E5,F6:
I9))
引用中包含的区域个数(3)
=AREAS(B2:
D4B2)
引用中包含的区域个数
(1)
3、CHOOSE
使用index_num返回数值参数列表中的数值。
使用CHOOSE可以根据索引号从最多254个数值中选择一个。
例如,如果value1到value7表示一周的7天,当将1到7之间的数字用作index_num时,则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的参数。
示例1
1
2
3
4
5
A
B
数据
数据
1st
Nails
2nd
Screws
3rd
Nuts
完成
Bolts
公式
说明(结果)
=CHOOSE(2,A2,A3,A4,A5)
第二个参数A3的值(2nd)
=CHOOSE(4,B2,B3,B4,B5)
第四个参数B5的值(Bolts)
示例2
1
2
3
4
5
A
数据
23
45
12
10
公式
说明(结果)
=SUM(A2:
CHOOSE(2,A3,A4,A5))
计算单元格区域A2:
A4中所有数值的和(80)
4、COLUMN
返回给定引用的列标。
语法
COLUMN(reference)
Reference 为需要得到其列标的单元格或单元格区域。
如果省略reference,则假定为是对函数COLUMN所在单元格的引用。
如果reference为一个单元格区域,并且函数COLUMN作为水平数组 (数组:
用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。
数组区域共用一个公式;数组常量是用作参数的一组常量。
)输入,则函数COLUMN将reference中的列标以水平数组的形式返回。
Reference不能引用多个区域。
示例
1
2
3
A
B
公式
说明(结果)
=COLUMN()
公式所在的列
(1)
=COLUMN(A10)
引用的列
(1)
5、COLUMNS
返回数组 (数组:
用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。
数组区域共用一个公式;数组常量是用作参数的一组常量。
)或引用的列数。
语法
COLUMNS(array)
Array 为需要得到其列数的数组或数组公式 (数组公式:
数组公式对一组或多组值执行多重计算,并返回一个或多个结果。
数组公式括于大括号({})中。
按Ctrl+Shift+Enter可以输入数组公式。
),或对单元格区域的引用。
示例
如果将示例复制到一个空白工作表中,可能会更容易理解该示例。
1
2
3
A
B
公式
说明(结果)
=COLUMNS(C1:
E4)
引用中的列数(3)
=COLUMNS({1,2,3;4,5,6})
数组常量中的列数(3)
6、GETPIVOTDATA
返回存储在数据透视表中的数据。
如果报表中的汇总数据可见,则可以使用函数GETPIVOTDATA从数据透视表中检索汇总数据。
注释 通过以下方法可以快速地输入简单的GETPIVOTDATA公式:
在返回值所在的单元格中,键入=,然后在数据透视表中单击包含要返回的数据的单元格。
语法
GETPIVOTDATA(data_field,pivot_table,field1,item1,field2,item2,...)
Data_field 为包含要检索的数据的数据字段的名称,用引号引起。
Pivot_table 在数据透视表中对任何单元格、单元格区域或定义的单元格区域的引用。
该信息用于决定哪个数据透视表包含要检索的数据。
Field1,Item1,Field2,Item2 为1到126对用于描述要检索的数据的字段名和项名称,能够以任何次序排列。
字段名和项名称(而不是日期和数字)用引号引起来。
对于OLAP数据透视表,项可以包含维的源名称,以及项的源名称。
OLAP数据透视表的一对字段和项如下所示:
"[产品]","[产品].[所有产品].[食品].[烤制食品]"
说明
在函数GETPIVOTDATA的计算中可以包含计算字段、计算项及自定义计算方法。
如果pivot_table为包含两个或更多个数据透视表的区域,则将从区域中最新创建的报表中检索数据。
如果字段和项的参数描述的是单个单元格,则返回此单元格的数值,无论是文本串、数字、错误值或其他的值。
如果某个项包含日期,则值必须表示为序列号或使用DATE函数,这样如果在其他位置打开电子表格,该值仍然存在。
例如,某个项引用了日期“1999年3月5日”,则应输入36224或DATE(1999,3,5)。
时间可以输入为小数值或使用TIME函数来输入。
如果pivot_table并不代表找到了数据透视表的区域,则函数GETPIVOTDATA将返回错误值#REF!
。
如果参数未描述可见字段,或者参数包含未显示的页字段,则GETPIVOTDATA函数将返回#REF!
。
示例
包含数据透视表的区域为:
GETPIVOTDATA("Sales",$A$4)返回“销售额”字段的总计值¥49,325。
GETPIVOTDATA("SumofSales",$A$4)也返回“销售额”字段的总计值¥49,325。
字段名可以按照它在工作表上显示的内容直接输入,也可以只输入主要部分(没有“求和项:
”、“计数项:
”等)。
GETPIVOTDATA("Sales",$A$4,"Month","March")返回“三月”的总计值¥30,337。
GETPIVOTDATA("Sales",$A$4,"Month","March","Product","Produce","Salesperson","Buchanan")返回¥10,201。
GETPIVOTDATA("Sales",$A$4,"Region","South")返回错误值#REF!
,这是因为“南部”地区的数据是不可见的。
GETPIVOTDATA("Sales",$A$4,"Product","Beverages","Salesperson","Davolio")返回错误值#REF!
,这是因为没有“Davolio”饮料销售的汇总值。
7、HLOOKUP
在表格或数值数组 (数组:
用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。
数组区域共用一个公式;数组常量是用作参数的一组常量。
)的首行查找指定的数值,并在表格或数组中指定行的同一列中返回一个数值。
当比较值位于数据表的首行,并且要查找下面给定行中的数据时,请使用函数HLOOKUP。
当比较值位于要查找的数据左边的一列时,请使用函数VLOOKUP。
HLOOKUP中的H代表“行”。
语法
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中使用通配符、问号(?
)和星号(*)。
问号匹配任意单个字符;星号匹配任意字符序列。
如果要查找实际的问号或星号,请在该字符前键入波形符(~)。
示例
1
2
3
4
A
B
C
Axles
Bearings
Bolts
4
4
9
5
7
10
6
8
11
公式
说明(结果)
=HLOOKUP("Axles",A1:
C4,2,TRUE)
在首行查找Axles,并返回同列中第2行的值。
(4)
=HLOOKUP("Bearings",A1:
C4,3,FALSE)
在首行查找Bearings,并返回同列中第3行的值。
(7)
=HLOOKUP("B",A1:
C4,3,TRUE)
在首行查找B,并返回同列中第3行的值。
由于B不是精确匹配,因此将使用小于B的最大值Axles。
(5)
=HLOOKUP("Bolts",A1:
C4,4)
在首行查找Bolts,并返回同列中第4行的值。
(11)
=HLOOKUP(3,{1,2,3;"a","b","c";"d","e","f"},2,TRUE)
在数组常量的第一行中查找3,并返回同列中第2行的值。
(c)
8、HYPERLINK
创建一个快捷方式(跳转),用以打开存储在网络服务器、Intranet (Intranet:
一种组织内部的、使用Internet技术(如HTTP或FTP协议)的网络。
通过利用超链接,您可以在Intranet上浏览对象、文档、网页和其他目标内容。
)或Internet中的文件。
当单击函数HYPERLINK所在的单元格时,MicrosoftExcel将打开存储在link_location中的文件。
语法
HYPERLINK(link_location,friendly_name)
Link_location 为文档的路径和文件名,此文档可以作为文本打开。
Link_location还可以指向文档中的某个更为具体的位置,如Excel工作表或工作簿中特定的单元格或命名区域,或是指向MicrosoftWord文档中的书签。
路径可以是存储在硬盘驱动器上的文件,或是服务器(在MicrosoftExcelforWindows中)上的“通用命名规范”(UNC)路径,或是在Internet或Intranet上的“统一资源定位符”(URL (统一资源定位符(URL):
一种地址,指定协议(如HTTP或FTP)以及对象、文档、万维网网页或其他目标在Internet或Intranet上的位置,例如:
路径。
Link_location可以为括在引号中的文本字符串,或是包含文本字符串链接的单元格。
如果在link_location中指定的跳转不存在或不能访问,则当单击单元格时将出现错误信息。
Friendly_name 为单元格中显示的跳转文本值或数字值。
单元格的内容为蓝色并带有下划线。
如果省略Friendly_name,单元格将link_location显示为跳转文本。
Friendly_name可以为数值、文本字符串、名称或包含跳转文本或数值的单元格。
如果Friendly_name返回错误值(例如,#VALUE!
),单元格将显示错误值以替代跳转文本。
说明
若要选定一个包含超链接的单元格并且不跳往超链接的目标文件,请单击单元格区域并按住鼠标按钮直到光标变成一个十字
,然后释放鼠标按钮。
示例
下面的示例打开存储在Internet的地址上的工作表BudgetReport.xls,并显示文本“Clickforreport”:
=HYPERLINK("report.xls","Clickforreport")
下面的示例创建对工作簿BudgetReport.xls的Annual工作表中单元格F10的超链接,该工作簿存储在Internet的地址上。
工作表中包含超链接的单元格将以单元格D1的内容作为其显示的跳转文本:
=HYPERLINK("[report.xls]Annual!
F10",D1)
下面的示例创建对工作簿BudgetReport.xls的FirstQuarter工作表中DeptTotal区域的超链接,该工作簿存储在Internet的地址上。
工作表中包含超链接的单元格将显示跳转文本“ClicktoseeFirstQuarterDepartmentTotal”:
=HYPERLINK("[report.xls]FirstQuarter!
DeptTotal","ClicktoseeFirstQuarterDepartmentTotal")
若要对MicrosoftWord文档中的特定位置创建超链接,必须使用书签来定义文档中所要跳转的位置。
下面的示例创建对上名为AnnualReport.doc的文档中的书签QrtlyProfits的超链接:
=HYPERLINK("[Report.doc]QrtlyProfits","QuarterlyProfitReport")
在ExcelforWindows中,下面的示例可以将单元格D5中的内容显示为超链接单元格中的跳转文本,并打开名为1stqtr.xls的文件,该文件存储在FINANCE服务器的Statements共享文件夹中。
此示例使用UNC路径:
=HYPERLINK("\\FINANCE\Statements\1stqtr.xls",D5)
下面的示例打开ExcelforWindows的文件1stqtr.xls,该文件存储在驱动器D的名为Finance的目录下,并显示存储在单元格H10中的数字值:
=HYPERLINK("D:
\FINANCE\1stqtr.xls",H10)
在ExcelforWindows中,下面的示例将创建指向另一个(外部)工作簿Mybook.xls中名为Totals的区域的超链接:
=HYPERLINK("[C:
\MyDocuments\Mybook.xls]Totals")
在MicrosoftExcelforMacintosh中,下面的示例将在单元格中显示“Clickhere”,并打开保存在名为MacintoshHD硬盘上BudgetReports文件夹中的FirstQuarter文件:
=HYPERLINK("MacintoshHD:
BudgetReports:
FirstQuarter","Clickhere")
可以在工作表内创建超链接,以便从一个单元格跳转到另一个单元格。
例如,如果Budget工作簿中的June工作表为活动工作表,则下面的公式将创建跳转到单元格E56的超链接,链接的文本本身为单元格E56中的数值。
=HYPERLINK("[Budget]June!
E56",E56)
若要跳转到同一工作簿中的其他工作表,请更改超链接中的工作表名称。
在前例中,若要创建指向September工作表中单元格E56的链接,请将单词“June”更改为“September”。
9、INDEX
返回表或区域中的值或值的引用。
函数INDEX有两种形式:
数组 (数组:
用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。
数组区域共用一个公式;数组常量是用作参数的一组常量。
)形式和引用形式。
如果需要
则参阅
返回指定单元格或单元格数组的值
数组形式
返回指定单元格的引用
引用形式
数组形式
返回表格或数组 (数组:
用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。
数组区域共用一个公式;数组常量是用作参数的一组常量。
)中的元素值,此元素由行序号和列序号的索引值给定。
当函数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。
说明
如果同时使用参数row_num和column_num,函数INDEX返回row_num和co
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- EXCEL 函数 汇总 查找 引用 帮助 导出