10查找和引用函数
- 10x01 ADDRESS 函数
- 语法
- 10x02 AREAS 函数
- 语法
- 10x03 CHOOSE 函数
- 语法
- 10x04 COLUMN 函数
- 语法
- 10x05 COLUMNS 函数
- 语法
- 10x06 FILTER 函数
- 语法
- 10x07 FORMULATEXT 函数
- 语法
- 10x08 GETPIVOTDATA 函数
- 语法
- 10x09 HLOOKUP 函数
- 语法
- 10x10 HYPERLINK 函数
- 语法
- 10x11 INDEX 函数
- 语法
- 10x12 INDIRECT 函数
- 语法
- 10x13 LOOKUP 函数
- 语法
- 10x14 MATCH 函数
- 语法
- 10x15 OFFSET 函数
- 语法
- 10x16 ROW 函数
- 语法
- 10x17 ROWS 函数
- 语法
- 10x18 RTD 函数
- 语法
- 10x19 SORT 函数
- 语法
- 10x20 SORTBY 函数
- 语法
- 10x21 TRANSPOSE 函数
- 语法
- 10x22 UNIQUE 函数
- 语法
- 10x23 VLOOKUP 函数
- 语法
- 10x24 XLOOKUP 函数
- 语法
- 10x25 XMATCH 函数
- 语法
10x01 ADDRESS 函数
你可以使用 ADDRESS 函数，根据指定行号和列号获得工作表中的某个单元格的地址。 例如，ADDRESS(2,3) 返回 $C$2。 再例如，ADDRESS(77,300) 返回 $KN$77。 可以使用其他函数（如 ROW 和 COLUMN 函数）为 ADDRESS 函数提供行号和列号参数。
语法
ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
| 参数 | 说明 |
|---|---|
| row_num 必需 | 一个数值，指定要在单元格引用中使用的行号 |
| column_num 必需 | 一个数值，指定要在单元格引用中使用的列号 |
| abs_num 可选 | 一个数值，指定要返回的引用类型，详情见下表 |
| a1 可选 | 一个逻辑值，指定 A1 或 R1C1 引用样式。 在 A1 样式中，列和行将分别按字母和数字顺序添加标签。 在 R1C1 引用样式中，列和行均按数字顺序添加标签。 如果参数 A1 为 TRUE 或被省略，则 ADDRESS 函数返回 A1 样式引用；如果为 FALSE，则 ADDRESS 函数返回 R1C1 样式引用。 |
| sheet_text 可选 | 一个文本值, 指定要用作外部引用的工作表的名称。 例如, 公式=ADDRESS (1, 1, “Sheet2”)返回Sheet2! $A $1。 如果省略了sheet_text参数, 则不使用工作表名称, 并且该函数返回的地址引用当前工作表上的单元格 |
abs_num取值和返回类型对应关系表
| abs_num | 返回的引用类型 |
|---|---|
| 1 或省略 | 绝对值 |
| 2 | 绝对行号，相对列标 |
| 3 | 相对行号，绝对列标 |
| 4 | 相对值 |
例子：
| 公式 | 说明 | 结果 |
|---|---|---|
| =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,“EXCEL SHEET”) | 对另一个工作表的绝对引用 | ‘EXCEL SHEET’!R2C3 |
10x02 AREAS 函数
返回引用中的区域个数。 区域是指连续的单元格区域或单个单元格。
语法
AREAS(reference)
| 参数 | 说明 |
|---|---|
| reference 必需 | 对某个单元格或单元格区域的引用，可包含多个区域。 如果需要将几个引用指定为一个参数，则必须用括号括起来，以免 Microsoft Excel 将逗号解释为字段分隔符。 |
例子:
| 公式 | 说明 | 结果 |
|---|---|---|
| =AREAS(B2:D4) | 引用中包含的区域个数 | 1 |
| =AREAS((B2:D4,E5,F6:I9)) | 引用中包含的区域个数 | 3 |
| =AREAS(B2:D4 B2) | 引用中包含的区域个数 | 1 |
10x03 CHOOSE 函数
使用 index_num 返回数值参数列表中的数值。 使用 CHOOSE 可以根据索引号从最多 254 个数值中选择一个。 例如，如果 value1 到 value7 表示一周的 7 天，那么将 1 到 7 之间的数字用作 index_num 时，CHOOSE 将返回其中的某一天。
语法
CHOOSE(index_num, value1, [value2], …)
choose函数详情见Excel函数大全-01最常用的十个函数
10x04 COLUMN 函数
COLUMN 函数返回给定单元格引用的列号。 例如，公式=COLUMN(D10)返回4，因为D列是第4列。
语法
COLUMN([reference])
| 参数 | 说明 |
|---|---|
| reference 可选 | 要返回其列号的单元格或单元格范围。 |
注意：
- 如果省略参数 reference 或该参数为一个单元格区域，并且 COLUMN 函数是以水平数组公式的形式输入的，则 COLUMN 函数将以水平数组的形式返回参数 reference 的列号。
- 如果参数 reference 为一个单元格区域，并且 COLUMN 函数不是以水平数组公式的形式输入的，则 COLUMN 函数将返回最左侧列的列号。
- 如果省略参数 reference，则假定该参数为对 COLUMN 函数所在单元格的引用。
- 参数 reference 不能引用多个区域。
例子：
10x05 COLUMNS 函数
返回数组或引用的列数。
语法
COLUMNS(array)
| 参数 | 说明 |
|---|---|
| array 必需 | 要计算列数的数组、数组公式或是对单元格区域的引用。 |
例子：
| 公式 | 说明 | 结果 |
|---|---|---|
| =COLUMNS(C1:E4) | 引用 C1:E4 中的列数。 | 3 |
| =COLUMNS({1,2,3;4,5,6}) | 数组常量 {1,2,3;4,5,6} 中的列数。 其中有两行，每行 3 列，第一行中包含 1、2、3，第二行中包含 4、5、6。 | 3 |
10x06 FILTER 函数
office 365才可用，FILTER 函数可以基于定义的条件筛选一系列数据。
语法
FILTER(array,include,[if_empty])
FILTER 函数基于布尔值 (True/False) 数组筛选数组。
| 参数 | 说明 |
|---|---|
| array 必需 | 要筛选的数组或区域 |
| include 必需 | 布尔值数组，其高度或宽度与数组相同 |
| if_empty 可选 | 所含数组中的所有值都为空（筛选器不返回任何内容）时返回的值 |
注意：
- 可将数组视为一行值、一列值或几行值和几列值的组合。 在上述示例中，FILTER 公式的源数组为范围 A5:D20。
- FILTER 函数将返回一个数组，如果该数组是公式的最终结果，则将溢出。 这意味着，当按“Enter”时，Excel 将动态创建相应大小的数组范围。 如果支持数据位于 Excel 表格中，若使用结构化引用，则从数组范围中添加或删除数据时，数组将自动重设大小。 有关详细信息，请参阅关于溢出数组行为的此文章。
- 如果数据集可能返回空值，请使用第三个参数 ([if_empty])。 否则将导致 #CALC! 错误 ，因为 Excel 当前不支持空数组。
- 如果 include 参数的任何值都是一个错误的值（#N/A、#VALUE 等）或无法转换为布尔值，则 FILTER 函数将返回一个错误。
- Excel 对工作簿之间的动态数据提供有限支持，并且仅当这两个工作簿时都处于打开状态时才支持此方案。 如果关闭源工作簿，刷新时，任何链接的动态数组公式都将返回 #REF! 错误 。
例子1：
用于返回多个条件的 FILTER
在此示例中，我们使用乘法运算符 (*)，以返回数组范围 (A5:D20) 中包含“苹果”且位于东部区域的所有值：=FILTER(A5:D20,(C5:C20=H1)(A5:A20=H2),"")。
例子2：
用于返回多个条件并排序的 FILTER
在此示例中，我们配合使用之前的 FILTER 函数和 SORT 函数，以返回数组范围 (A5:D20) 中包含“苹果”且位于东部区域的所有值，然后对 Units 进行降序排序：=SORT(FILTER(A5:D20,(C5:C20=H1)(A5:A20=H2),""),4,-1)
在此示例中，我们配合使用 FILTER 函数和加法运算符 (+)，以返回数组范围 (A5:D20) 中包含“苹果”或位于东部区域的所有值，然后对 Units 进行降序排序：=SORT(FILTER(A5:D20,(C5:C20=H1)+(A5:A20=H2),""),4,-1)。
10x07 FORMULATEXT 函数
以字符串的形式返回公式。
语法
FORMULATEXT(reference)
| 参数 | 说明 |
|---|---|
| reference 必需 | 对单元格或单元格区域的引用。 |
注意：
- 如果 Reference 参数表示另一个未打开的工作薄，则 FORMULATEXT 返回错误值 #N/A。
- 用作 Reference 参数的单元格不包含公式，则 FORMULATEXT 返回错误值 #N/A。
- 单元格中的公式超过 8192 个字符，则 FORMULATEXT 返回错误值 #N/A。
- 无法在工作表中显示公式；例如，由于工作表保护，，则 FORMULATEXT 返回错误值 #N/A。
- 如果 Reference 参数表示整行或整列，或表示包含多个单元格的区域或定义名称，则 FORMULATEXT 返回行、列或区域中最左上角单元格中的值，但也可以选好同样大小的输出区域，将FORMULATEXT函数作为数组公式使用，则返回对应区域每一个单元格对应的公式文本。
- 当Reference为正在输入FORMULATEXT函数的单元格时，不会导致循环引用警告，FORMULATEXT 将成功将公式返回为单元格中的文本。
例子：
| 公式 | 说明 | 结果 |
|---|---|---|
| =FORMULATEXT(A2) | Reference为正在输入FORMULATEXT函数的A2单元格，并没有出现警告，而是正确返回了结果。 | =FORMULATEXT(A2) |
| =TODAY() | C3中的"=FORMULATEXT(A3)"返回A3中的公式=TODAY() | =TODAY() |
10x08 GETPIVOTDATA 函数
GETPIVOTDATA函数从数据透视表中返回可见数据。
语法
GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], …)
| 参数 | 说明 |
|---|---|
| data_field 必需 | 包含要检索的数据的数据透视表字段的名称。 这需要用引号括起来。 |
| pivot_table 必需 | 对数据透视表中任何单元格、单元格区域或单元格已命名区域的引用。 此信息用于确定包含要检索数据的数据透视表。 |
| field1、item1、field2、item2… 可选 | 描述要检索的数据的 1 到 126 个字段名称对和项目名称对。 这些对可按任何顺序排列。 日期和数字以外的项目的域名和名称需要用引号括起来。 |
注意:
- 可通过以下方法快速输入简单的 GETPIVOTDATA 公式：在返回值所在的单元格中，键入 =（等号），然后在数据透视表中单击包含要返回数据的单元格，GETPIVOTDATA 公式将自动生成。
- 可以关闭此功能，方法是选择现有数据透视表中的任意单元格，然后转到 “数据透视表分析” 选项卡 > “数据透视表 >” 选项 >取消选中 "生成 GetPivotData " 选项。
- 在 GETPIVOTDATA 计算中可以包含计算字段或项以及自定义计算。
- 如果 pivot_table 参数是包含两个或多个数据透视表的区域，则将从最近创建的任何数据透视表检索数据。
- 如果字段和项目参数描述单个单元格，则返回该单元格的值，无论它是字符串、数字、错误还是空白单元格。
- 如果项目包含日期，则此值必须以序列号表示或使用 DATE 函数进行填充，以便在其他位置打开此工作表时将保留此值。 例如，引用日期 1999 年 3 月 5 日的项目可按 36224 或 DATE(1999,3,5) 的形式输入。 时间可按小数值的形式输入或使用 TIME 函数输入。
- 如果通过 pivot_table 参数找不到数据透视表区域，则 GETPIVOTDATA 返回 #REF！。
- 如果参数未描述可见字段，或者参数包含其中未显示筛选数据的报表筛选项，则 GETPIVOTDATA 返回错误值 #REF!。
例子：
下面示例中的公式显示了从数据透视表获取数据的各种方法。
10x09 HLOOKUP 函数
在表格的首行或数值数组中搜索值，然后返回表格或数组中指定行的所在列中的值。 当比较值位于数据表格的首行时，如果要向下查看指定的行数，则可使用 HLOOKUP。 当比较值位于所需查找的数据的左边一列时，则可使用 VLOOKUP。
HLOOKUP 中的 H 代表“行”。
语法
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
| 参数 | 说明 |
|---|---|
| lookup_value 必需 | 要在表格的第一行中查找的值。 Lookup_value 可以是数值、引用或文本字符串。 |
| Table_array 必需 | 在其中查找数据的信息表。 使用对区域或区域名称的引用。 1. Table_array 的第一行的数值可以为文本、数字或逻辑值. 2.如果 range_lookup 为 TRUE，则 table_array 的第一行的数值必须按升序排列：…-2、-1、0、1、2、…、A-Z、FALSE、TRUE；否则，HLOOKUP 将不能给出正确的数值。 如果 range_lookup 为 FALSE，则 table_array 不必进行排序。 3.文本不区分大小写。 |
| 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 的最大值。 如果为 False，则 HLOOKUP 将查找精确匹配值。 如果找不到精确匹配值，则返回错误值 #N/A。 |
注意:
- 如果 range_lookup 为 FALSE 且 lookup_value 为文本，则可以在 lookup_value 中使用通配符（问号 (?) 和星号 (*)）。 问号匹配任意单个字符；星号匹配任意一串字符。 如果要查找实际的问号或星号，请在字符前键入波形符 (~)。
- excel按行排序，数据-排序-选项-按行排序，主要关键字选择需要进行排序的行。
例子：
| 车轴 | 轴承 | 螺钉 |
|---|---|---|
| 4 | 4 | 9 |
| 5 | 7 | 10 |
| 6 | 8 | 11 |
| 公式 | 说明 | 结果 |
| =HLOOKUP(“车轴”, A1:C4, 2, TRUE) | 在首行查找车轴，并返回同列（列 A）中第 2 行的值。 | 4 |
| =HLOOKUP(“轴承”, A1:C4, 3, FALSE) | 在首行查找轴承，并返回同列（列 B）中第 3 行的值。 | 7 |
| =HLOOKUP(“B”, A1:C4, 3, TRUE) | 在首行查找 B，并返回同列中第 3 行的值。 因为找不到 B 的完全匹配项，将使用第 1 行列 A 中小于 B 的最大值 “车轴”。 | 5 |
| =HLOOKUP(“螺栓”, A1:C4, 4) | 在首行查找螺栓，并返回同列（列 C）中第 4 行的值。 | 11 |
| =HLOOKUP(3, {1,2,3;“a”,“b”,“c”;“d”,“e”,“f”}, 2, TRUE) | 在三行数组常量中查找数字 3，并返回同列（本例中为第三列）中第 2 行的值。 数组常量中有三行数值，并且每行都用分号;分隔。 因为在第 2 行和第 3 列（同一列）中找到 c，因此将返回 c。 | c |
10x10 HYPERLINK 函数
HYPERLINK函数创建一个快捷方式, 可跳转到当前工作簿中的其他位置, 或打开存储在网络服务器、 intranet 或 Internet 上的文档。 单击包含超链接函数的单元格时, Excel 将跳转到列出的位置, 或打开您指定的文档。
语法
HYPERLINK(link_location, [friendly_name])
| 参数 | 说明 |
|---|---|
| link_location 必需 | 要打开的文档的路径和文件名。 Link_location 可以指向文档中的某个更为具体的位置 ，如 Excel 工作表或工作簿中特定的单元格或命名区域，或是指向 Microsoft Word 文档中的书签。 路径可以指向存储在硬盘驱动器上的文件。 路径还可以是服务器上的通用命名约定 (UNC) 路径 (在 Microsoft Excel for Windows 中) 或 Internet 或 intranet 上的统一资源定位器 (URL) 路径。 |
| Friendly_name 可选 | 单元格中显示的跳转文本或数字值。 Friendly_name 显示为蓝色并带有下划线。 如果省略 Friendly_name，单元格会将 link_location 显示为跳转文本。 |
注意：
- Excel 网页版 HYPERLINK 函数仅对 web 地址 (url) 有效。 Link_location 可以是用引号引起的文本字符串, 或对包含文本字符串链接的单元格的引用。
- 如果 link_location 中指定的跳转不存在或无法导航, 单击单元格时将出现错误。
- Friendly_name 可以为数值、文本字符串、名称或包含跳转文本或数值的单元格。
- 如果 Friendly_name 返回错误值（例如，#VALUE!），单元格将显示错误值以替代跳转文本。
- 在 Excel 桌面应用程序中，若要选择一个包含超链接的单元格，但不跳转到超链接目标，请单击单元格并按住鼠标按钮直到指针变成十字 Excel 选择光标 ，然后释放鼠标按钮。 在 Excel 网页版 中, 当指针为箭头时单击某个单元格, 将其选中;通过单击指针为手形来跳转到超链接目标。
例子：
| 示例 | 结果 |
|---|---|
| =HYPERLINK(“http://example.microsoft/report/budget report.xlsx”, “Click for report”) | 此函数将打开保存在以下位置的工作簿：http://example.microsoft/report。 单元格会将 Click for report 显示为跳转文本。 |
| =HYPERLINK("[http://example.microsoft/report/budget report.xlsx]Annual!F10", D1) | 将创建一个超链接，指向工作簿的 Annual 工作表中的单元格 F10，该工作簿存储在 http://example.microsoft/report 上。 工作表中包含超链接的单元格将单元格 D1 的内容显示为跳转文本。 |
| =HYPERLINK("[http://example.microsoft/report/budget report.xlsx]‘First Quarter’!DeptTotal", “Click to see First Quarter Department Total”) | 将创建一个超链接，指向工作簿的 First Quarter 工作表中名为 DeptTotal 的区域，该工作簿存储在 http://example.microsoft/report 上。 工作表中包含超链接的单元格将 “Click to see First Quarter Department Total” 显示为跳转文本。 |
| =HYPERLINK(“http://example.microsoft/Annual Report.docx]QrtlyProfits”, “Quarterly Profit Report”) | 若要创建指向 Word 文件中特定位置的超链接，必须使用书签来定义文件中所要跳转到的位置。 此示例将创建一个超链接，指向保存在 http://example.microsoft 上的文件 Annual Report.doc 中的书签 QrtlyProfits。 |
| =HYPERLINK("\FINANCE\Statements\1stqtr.xlsx", D5) | 将单元格 D5 的内容显示为单元格中的跳转文本，并打开工作簿，该工作簿存储在 FINANCE 服务器上的 Statements 共享文件夹中。 此示例使用 UNC 路径。 |
| =HYPERLINK(“D:\FINANCE\1stqtr.xlsx”, H10) | 将打开工作簿 1stqtr.xlsx 并显示存储在单元格 H10 中的数字值，该工作簿存储在驱动器 D 上的 Finance 目录中。 |
| =HYPERLINK("[C:\My Documents\Mybook.xlsx]Totals") | 将创建一个超链接，指向另一个（外部）工作簿 Mybook.xlsx 中的 Totals 区域。 |
| =HYPERLINK("[Book1.xlsx]Sheet1!A10",“Go to Sheet1 > A10”) | 若要跳转到当前工作表中的其他位置, 请同时包含工作簿名称和工作表名称 (如下所示), 其中 Sheet1 是当前工作表。 |
| =HYPERLINK("[Book1.xlsx]January!A10",“Go to January > A10”) | 若要跳转到当前工作表中的其他位置, 请同时包含工作簿名称和工作表名称, 其中一月是工作簿中的另一个工作表。 |
| =HYPERLINK(CELL(“address”,January!A1),“Go to January > A1”) | 若要在不使用完全限定的工作表引用 ([Book1]) 的情况下跳转到当前工作表中的其他位置, 可以使用cell函数, 其中单元格 (“address”) 返回当前工作簿的名称。 |
| =HYPERLINK($Z$1) | 若要快速更新工作表中使用具有相同参数的 HYPERLINK 函数的所有公式，可以将链接目标放在同一或另一工作表中的另一个单元格中，然后将对该单元格的绝对引用用作 HYPERLINK 公式中的 link_location。 对链接目标所做的更改将立即反映到 HYPERLINK 公式中。 |
10x11 INDEX 函数
INDEX 函数返回表格或区域中的值或值的引用。分为数组形式和引用形式。
语法
index函数详情见Excel函数大全-01最常用的十个函数
10x12 INDIRECT 函数
返回由文本字符串指定的引用。 此函数立即对引用进行计算，并显示其内容。 如果需要更改公式中对单元格的引用，而不更改公式本身，请使用函数 INDIRECT。
语法
INDIRECT(ref_text, [a1])
| 参数 | 说明 |
|---|---|
| Ref_text 必需 | 对包含 A1 样式的引用、R1C1 样式的引用、定义为引用的名称或对单元格的引用作为文本字符串的单元格的引用。 如果 ref_text 不是有效的单元格引用, 则间接返回 #REF! 。 |
| A1 可选 | 一个逻辑值，用于指定包含在单元格 ref_text 中的引用的类型。 1.如果 a1 为 TRUE 或省略，ref_text 被解释为 A1-样式的引用。 2.如果 a1 为 FALSE，则将 ref_text 解释为 R1C1 样式的引用。 |
注意：
- indirect函数时易失性函数，如果 ref_text 引用另一个工作簿 (外部引用), 则必须打开另一个工作簿。 如果源工作簿未打开, 则间接返回 #REF! 。Excel Web App 中不支持外部引用。
- 如果 ref_text 引用的单元格区域超出1048576的行限制或列限制16384 (XFD), 则间接返回 #REF! 错误。此行为不同于早于Microsoft Office Excel 2007 的 Excel 版本, 这将忽略超过的限制并返回值。
例子：
| 数据 | ||
|---|---|---|
| B2 | 1.333 | |
| B3 | 45 | |
| 赵强 | 10 | |
| 5 | 62 | |
| 公式 | 说明 | 结果 |
| '=INDIRECT(A2) | 单元格 A2 中的引用值。 引用的是单元格 B2，其中包含值 1.333。 | 1.333 |
| '=INDIRECT(A3) | 单元格 A3 中的引用值。 引用的是单元格 B3，其中包含值 45。 | 45 |
| '=INDIRECT(A4) | 因为单元格 B4 有定义名“国明”，对定义名的引用即是对单元格 B4 的引用，其中包含值 10。 | 10 |
| '=INDIRECT(“B”&A5) | 将 B 和 A5 中的值 (5) 合并在一起。 这将反过来引用单元格 B5，其中包含值 62。 | 62 |
10x13 LOOKUP 函数
当您需要查询一行或一列并查找另一行或列中的相同位置的值时，会使用其中一个查找和引用函数 LOOKUP。LOOKUP 有两种使用方式：向量形式和数组形式。
语法
LOOKUP 函数详情见Excel函数大全-01最常用的十个函数
10x14 MATCH 函数
使用 MATCH 函数在范围单元格中搜索特定的项，然后返回该项在此区域中的相对位置。
语法
MATCH 函数详情见Excel函数大全-01最常用的十个函数
10x15 OFFSET 函数
返回对单元格或单元格区域中指定行数和列数的区域的引用。 返回的引用可以是单个单元格或单元格区域。 可以指定要返回的行数和列数。
语法
OFFSET(reference, rows, cols, [height], [width])
| 参数 | 说明 |
|---|---|
| Reference 必需 | 要作为偏移基准的参照。 引用必须引用单元格或相邻单元格区域，当为相邻单元格区域时，以左上角单元格为参照基准。否则, OFFSET 返回 #VALUE! 。 |
| rows 必需 | 需要左上角单元格引用的向上或向下行数。 使用 5 作为 rows 参数，可指定引用中的左上角单元格为引用下方的 5 行。 Rows 可为正数（这意味着在起始引用的下方）或负数（这意味着在起始引用的上方）。 |
| cols 必需 | 需要结果的左上角单元格引用的从左到右的列数。 使用 5 作为 cols 参数，可指定引用中的左上角单元格为引用右方的 5 列。 Cols 可为正数（这意味着在起始引用的右侧）或负数（这意味着在起始引用的左侧）。 |
| height 可选 | 需要返回的引用的行高。 Height 必须为正数。 |
| width 可选 | 需要返回的引用的行高。 width必须为正数。 |
注意:
- 如果 “行” 和 “cols 偏移” 引用覆盖了工作表的边缘, 则 offset 返回 #REF! 。
- 如果省略 height 或 width，则假设其高度或宽度与 reference 相同。
- OFFSET 实际上并不移动任何单元格或更改选定区域；它只是返回一个引用。 OFFSET 可以与任何期待引用参数的函数一起使用。 例如，公式 SUM(OFFSET(C2,1,2,3,1)) 可计算 3 行 1 列区域（即单元格 C2 下方的 1 行和右侧的 2 列的 3 行 1 列区域）的总值。
例子：
| 公式 | 说明 | 结果 |
|---|---|---|
| =OFFSET(D3,3,-2,1,1) | 显示单元格 B6 中的值 (4) | 4 |
| =SUM(OFFSET(D3:F5,3,-2, 3, 3)) | 对数据区域 B6:D8 求和 | 34 |
| =OFFSET(D3, -3, -3) | 返回错误值，因为引用的是工作表中不存在的区域。 | #REF! |
| 数据 | 数据 | |
| 4 | 10 | |
| 8 | 3 | |
| 3 | 6 |
10x16 ROW 函数
返回引用的行号。
语法
ROW([reference])
| 参数 | 说明 |
|---|---|
| reference 可选 | 需要得到其行号的单元格或单元格区域。 |
注意:
- 如果省略 reference，则假定是对函数 ROW 所在单元格的引用。
- 如果 reference 为一个单元格区域，并且 ROW 作为垂直数组输入，则 ROW 将以垂直数组的形式返回 reference 的行号，水平数组输入时，只能返回reference中第一个单元格对应的行号，且数组值都是相同的第一个单元格对应的行号。
- Reference 不能引用多个区域。
例子：
| 公式 | 说明 | 结果 |
|---|---|---|
| =ROW() | 公式所在行的行号 | 2 |
| =ROW(C10) | 引用所在行的行号 | 10 |
10x17 ROWS 函数
返回引用或数组的行数。
语法
ROWS(array)
| 参数 | 说明 |
|---|---|
| Array 必需 | 需要得到其行数的数组、数组公式或对单元格区域的引用。 |
例子:
| 公式 | 说明 | 结果 |
|---|---|---|
| =ROWS(C1:E4) | 引用中的行数 | 4 |
| =ROWS({1,2,3;4,5,6}) | 数组常量中的行数 | 2 |
10x18 RTD 函数
RTD即real time data，从支持 COM 自动化的程序中检索实时数据，可参考以下链接：
excel函数实例教程：[16]RTD函数怎么用(1)
excel函数实例教程：[17]RTD函数怎么用(2)
语法
RTD(ProgID, server, topic1, [topic2], …)
| 参数 | 说明 |
|---|---|
| ProgID 必需 | 已安装在本地计算机上的已注册 COM 自动化加载项 ProgID 的名称。 将该名称用引号括起来。 |
| server 必需 | 应运行加载项的服务器的名称。 如果没有服务器，则在本地运行程序，将此参数保留为空。 否则，输入引号 ("") 将服务器名称括起来。 在 Visual Basic for Applications (VBA) 中使用 RTD 时，服务器需要双引号或 VBA Nullstring 属性，即使在本地运行服务器也不例外。 |
| Topic1, topic2, … Topic1 是必需的，后续主题是可选的 | 1 到 253 个参数，这些参数放在一起代表一个唯一的实时数据 |
注意：
- 必须在本地计算机上创建和注册 RTD COM 自动化加载项。 如果未安装实时数据服务器，则在尝试使用 RTD 函数时，单元格中将出现错误消息。
- 如果服务器持续更新结果，那么与其他函数不同，RTD 公式将在 Microsoft Excel 处于自动计算模式时进行更改。
例子：
| 公式 | 描述（结果） | 结 果 |
|---|---|---|
| =RTD(“mycomaddin.progid”,“Server_name”,“Price”) | 从支持 COM 自动化的程序中检索实时数据。 | #NAM |
10x19 SORT 函数
office 365才可用，SORT 函数可对某个区域或数组的内容进行排序。
语法
SORT(array,[sort_index],[sort_order],[by_col])
| 参数 | 说明 |
|---|---|
| array 必需 | 要排序的区域或数组 |
| sort_index 可选 | 一个数字，表示要按其排序的行或列，row1/col1（默认值） |
| sort_order 可选 | 一个数字，表示所需的排序顺序；1 表示升序（默认值），-1 表示降序 |
| by_col 可选 | 一个逻辑值，指示所需的排序方向；False 表示按行排序（默认值），True 表示按列排序 |
注意:
- 提供 SORT 函数，以对数组中的数据排序。 如果想要对网格中的数据排序，最好使用 SORTBY 函数，因为它更灵活。 SORTBY 将尊重列添加/删除，因为SORTBY 引用区域，而SORT 引用列索引号。
- SORT 函数将返回一个数组，如果该数组是公式的最终结果，则将溢出。 这意味着，当按“Enter”时，Excel 将动态创建相应大小的数组范围。 如果源数据位于 Excel 表格中，若对excel表格使用结构化引用，则从数组范围中添加或删除数据时，数组将自动重设大小。
- Excel 对工作簿之间的动态数据提供有限支持，并且仅当这两个工作簿时都处于打开状态时才支持此方案。 如果关闭源工作簿，刷新时，任何链接的动态数组公式都将返回 #REF! 错误 。
例子：
按降序对一定范围的值进行排序。
将 SORT 和 FILTER 一起使用，按升序对一定范围的值进行排序，且值必须超过 5,000。
10x20 SORTBY 函数
office 365才可用，SORTBY 函数基于相应范围或数组中的值对范围或数组的内容进行排序。
语法
SORTBY(array，by_array1，[sort_order1]，[by_array2，sort_order2],…)
| 参数 | 说明 |
|---|---|
| array 必需 | 要排序的区域或数组 |
| by_array1 必需 | 要对其进行排序的数组或区域 |
| sort_order1 可选 | 要用于排序的顺序。 1表示升序，-1 表示降序。 默认值为 “升序”。 |
| by_array2 可选 | 要对其进行排序的数组或区域 |
| sort_order2 可选 | 要用于排序的顺序。 1表示升序，-1 表示降序。 默认值为 “升序”。 |
| … | … |
注意:
- By_array 参数必须为一行高或一列宽。
- 所有参数必须大小相同。
- 如果排序顺序参数不为 -1 或 1，则公式将导致 #VALUE! 错误。 如果您忽略 “排序次序” 参数，Excel 将默认为升序排序。
- 可将数组视为一行值、一列值或几行值和几列值的组合。 在上述示例中，SORTBY 公式的数组为范围 D2:E9。
- SORTBY 函数将返回一个数组，如果该数组是公式的最终结果，则将溢出。 这意味着，当按“Enter”时，Excel 将动态创建相应大小的数组范围。 如果支持数据位于 Excel 表格中，若使用结构化引用，则从数组范围中添加或删除数据时，数组将自动重设大小。 有关详细信息，请参阅关于溢出数组行为的此文章。
- Excel 对工作簿之间的动态数据提供有限支持，并且仅当这两个工作簿时都处于打开状态时才支持此方案。 如果关闭源工作簿，刷新时，任何链接的动态数组公式都将返回 #REF! 错误 。
例子：
按照区域对表格进行升序排序，然后按照每个人员的年龄进行降序排序。
配合使用 SORTBY 与 RANDARRAY以及 COUNTA 随机化值列表。 在本例中，E2# 引用从单元格 E2 开始的动态数组范围，因为该范围使用 =SEQUENCE(10) 填充。 # 符号称为溢出范围运算符。
使用 SORTBY 按照高温对温度和降水值表格进行排序。
10x21 TRANSPOSE 函数
返回数组的转置。有时，你需要切换或旋转单元格。 可通过复制、粘贴和使用“转置”选项来执行此操作。 但这样做会创建重复的数据。 如果不希望产生重复数据，可选择TRANSPOSE 函数。
语法
TRANSPOSE(array)
| 参数 | 说明 |
|---|---|
| array 必需 | 要转置的工作表上的数组或单元格区域。 数组的转置是使用数组的第一行作为新数组的第一列、数组的第二行作为新数组的第二列等创建的。 |
注意：
- 无需手动键入范围。 键入 =TRANSPOSE( 后，可使用鼠标选择范围。 只需单击并从范围的开始处拖到结尾处。 但请记住：完成操作后，请按 Ctrl+Shift+Enter，而不只是 Enter。
- 还需转换文本和单元格格式？ 尝试复制、粘贴和使用“转置”选项。 但请注意，此操作会创建重复内容。 因此，如果原始单元格发生更改，副本不会更新。
例子:
步骤 1：选择空白单元格
首先选择一些空白单元格。 但请确保选择的单元格数量与原始单元格数量相同，但方向不同。 例如，此处有 8 个垂直排列的单元格：
因此，我们要选择 8 个水平排列的单元格，如下所示：
转置的新单元格将位于此处。
步骤 2：键入 =TRANSPOSE(
使这些空单元格保持选中状态，键入：=TRANSPOSE(
Excel 的外观将如下所示：
请注意，即使已开始输入公式，8 个单元格仍处于选中状态。
步骤 3：键入原始单元格的范围。
现在，键入想要转置的单元格范围。 在此示例中，我们要转置单元格 A1 到 B4。 所以此示例的公式是：= TRANSPOSE(A1:B4) – ，但此时还不能按 Enter！ 停止键入，转到下一步。
Excel 的外观将如下所示：
步骤 4：最后，按 Ctrl+Shift+Enter
现在按 CTRL + SHIFT + ENTER。 为什么？ 因为transpose函数仅在数组公式中使用，这就是完成数组公式的方法。 数组公式（简称）是应用于多个单元格的公式。 由于您在步骤1中选择了多个单元格，公式将应用于多个单元格。 下面是按 CTRL + SHIFT + ENTER 后的结果：
10x22 UNIQUE 函数
office 365才可用，UNIQUE 函数返回列表或范围中的一系列唯一值。
语法
UNIQUE(array，[by_col]，[exactly_once])
| 参数 | 说明 |
|---|---|
| array 必需 | 要从中返回唯一行或列的区域或数组 |
| by_col 可选 | By_col 参数是指示如何比较的逻辑值。TRUE将比较列并返回唯一列。FALSE （或省略）将比较行并返回唯一行 |
| exactly_once 可选 | Exactly_once 参数是一个逻辑值，它将返回在区域或数组中仅出现一次的行或列。 这是唯一的数据库概念。TRUE将返回从区域或数组中恰好出现一次的所有非重复行或列。FALSE （或省略）将返回区域或数组中的所有非重复行或列 |
例子：
示例 1
此示例将 “排序” 和 “唯一” 结合使用，以按升序返回唯一名称列表。
示例 2
此示例将 exactly_once 参数设置为 TRUE，该函数仅返回只有一次服务的客户。 如果您想确定没有接受过多次服务的人员，这样做可能会很有用，您可以与他们再次联系。
示例 3
此示例使用与号（&）将姓氏和名字连接到全名。 请注意，公式引用 A2： A12 和 B2： B12 中的整个名称范围。 这允许 Excel 返回所有名称的数组。
示例 4
此示例比较两列，并仅返回它们之间的唯一值。
10x23 VLOOKUP 函数
当需要在表格或区域中按行查找项目时，请使用 VLOOKUP。
= VLOOKUP （你想要查找的内容，要查找的位置，包含要返回的值的区域中的列号，返回近似或精确匹配-表示为 1/TRUE 或 0/假）。
语法
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
VLOOKUP 函数详情见Excel函数大全-01最常用的十个函数
10x24 XLOOKUP 函数
当需要在表格或区域中按行查找项目时，请使用XLOOKUP函数。 例如，按部件号查找汽车部件的价格，或根据员工 ID 查找员工姓名。 借助 XLOOKUP，你可以在一列中查找搜索词，并在同一行的另一列中返回结果，无论返回结果的列在原列的哪一侧。
语法
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| 参数 | 说明 |
|---|---|
| lookup_value 必需 | 查找值 |
| lookup_array 必需 | 要搜索的数组或区域 |
| return_array 必需 | 要返回的数组或区域 |
| if_not_found 可选 | 如果找不到有效的匹配项，则返回你提供的 [if_not_found] 文本。如果找不到有效的匹配项，并且缺少 [if_not_found]，则会返回 #N/A。 |
| match_mode 可选 | 指定匹配类型： 0 - 完全匹配。 如果未找到，则返回 #N/A。 这是默认选项。 -1 - 完全匹配。 如果没有找到，则返回下一个较小的项。 1 - 完全匹配。 如果没有找到，则返回下一个较大的项。 2 - 通配符匹配，其中 *, ? 和 ~ 有特殊含义。 |
| search_mode 可选 | 指定要使用的搜索模式： 1 - 从第一项开始执行搜索。 这是默认选项。 -1 - 从最后一项开始执行反向搜索。 2 - 执行依赖于 lookup_array 按升序排序的二进制搜索。 如果未排序，将返回无效结果。 2 - 执行依赖于 lookup_array 按降序排序的二进制搜索。 如果未排序，将返回无效结果。 |
注意:
- XLOOKUP 与 VLOOKUP 的不同之处在于，它使用单独的查找并返回数组，而 VLOOKUP 使用一个表数组，后跟列索引号。
- 与 VLOOKUP 不同，lookup_array 列位于 return_array 列的右侧，而 VLOOKUP 只能从左到右查找。
例子：
示例 1
此示例使用简单的 XLOOKUP 查找国家/地区名称，然后返回其电话国家/地区代码。 它仅包括 lookup_value（单元格 F2）、lookup_array（范围 B2:B11）和 return_array（范围 D2:D11）参数。 它不包括 match_mode 参数，因为 XLOOKUP 默认为完全匹配。
示例 2
在此示例中，我们将根据员工 ID 编号查找员工信息。 与 VLOOKUP 不同，XLOOKUP 可以返回具有多个项的数组，这允许单个公式从单元格 C5： D14 返回员工姓名和部门。
示例 3
此示例将if_not_found参数添加到上面的示例。
示例 4
以下示例在列 C 中查找在单元格 E2 中输入的个人收入，并在列 B 中查找匹配的税率费率。如果未找到任何内容，则将 if-not_found 参数设置为返回0。 Match_mode 参数设置为1，这意味着该函数将查找精确匹配，如果找不到它，它将返回下一个较大的项。 最后，search_mode 参数设置为1，这意味着该函数将从第一个项搜索到最后一个项。
示例 5
接下来，我们将使用嵌套的 XLOOKUP 函数执行垂直和水平匹配。 在这种情况下，它将首先查找 B 列中的毛利润，然后查找表格首行（范围 C5:F5）中的第一季度，并返回两者交集的值。 这类似于结合使用 INDEX 和 MATCH 函数。 你也可以使用 XLOOKUP 替换 HLOOKUP 函数。
单元格 D3:F3 中的公式是：=XLOOKUP(D2,
B
6
:
B6:
B6:B17,XLOOKUP(
C
3
,
C3,
C3,C5:
G
5
,
G5,
G5,C6:$G17))。
示例 6
此示例使用 SUM 函数，并嵌套两个 XLOOKUP 函数对两个范围之间的所有值求和。 在这种情况下，我们希望对葡萄、香蕉和 包含梨的值进行求和，这些值位于两个值之间。
单元格 E3 中的公式为： = SUM （XLOOKUP （B3，B6： B10，E6： E10）： XLOOKUP （C3，B6： B10，E6： E10））
它如何工作？ XLOOKUP 返回一个区域，因此当它计算时，该公式最后看起来如下所示： = SUM （$E $7： $E $9）。 可通过选择包含与此类似的 XLOOKUP 公式的单元格来查看其工作原理，然后转到公式 > 公式审核 > 公式求值，再按“求值”按钮逐步执行计算。
10x25 XMATCH 函数
XMATCH函数在数组或单元格区域中搜索指定项，然后返回该项的相对位置。
语法
XMATCH(lookup_value，lookup_array，[match_mode]，[search_mode])
| 参数 | 说明 |
|---|---|
| lookup_value 必需 | 查找值 |
| lookup_array 必需 | 要搜索的数组或区域 |
| match_mode 可选 | 指定匹配类型： 0-精确匹配（默认值） -1-完全匹配项或下一个最小项 1-完全匹配项或下一个最大的项 2 - 通配符匹配，其中 *, ? 和 ~ 有特殊含义。 |
| search_mode 可选 | 指定搜索类型： 1-第一次搜索（默认值） -1-搜索最后一个（反向搜索）。 2-执行二进制搜索，该搜索依赖于按升序排序的 lookup_array。 如果未排序，将返回无效结果。 2 - 执行依赖于 lookup_array 按降序排序的二进制搜索。 如果未排序，将返回无效结果。 |
例子：
示例 1
下面的示例查找精确匹配的第一项的位置或下一个最大值（即以 “Gra” 开头）。
示例 2
下一个示例查找符合奖励的销售人员的数量。 这也为 match_mode 使用1查找精确匹配项或列表中的下一个最大项，但由于数据是数字，因此它返回值的计数。 在这种情况下，该函数将返回4，因为有4个销售代表超过奖励金额。
示例 3
接下来，我们将使用 INDEX/XMATCH/XMATCH 的组合执行同时垂直和水平查找。 在这种情况下，我们希望返回给定销售代表和给定月份的销售额。 这类似于结合使用INDEX和MATCH函数，只不过它需要的参数较少。
示例 4
也可以使用 XMATCH 返回数组中的值。 例如， =XMATCH(4,{5,4,3,2,1}) 将返回2，因为4是数组中的第二项。 这是一个精确匹配方案，而=XMATCH(4.5,5,4,3,2,1},1) 返回1，因为 match_mode 参数（1）被设置为返回精确匹配或下一个最大的项目，即5。
更多推荐
Excel函数大全-10查找和引用函数
发布评论