Excel.001 函数学习(1) - 查找与引用
1 LOOKUP 函数
LOOKUP 函数返回向量（单行区域或单列区域）或数组（用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式。数组常量是用作参数的一组常量）中的数值。函数LOOKUP有两种
函数 LOOKUP 有两种语法形式
1.1 向量
函数 LOOKUP 的向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值
LOOKUP(lookup_value,lookup_vector,result_vector)
| 参数 | 简单说明 |
|---|---|
| Lookup_value | 为函数 LOOKUP 在第一个向量中所要查找的数值。lookup_value 可以为数字、文本、逻辑值或包含数值的名称或引用 |
| Lookup_vector | 为只包含一行或一列的区域。lookup_vector 的数值可以为文本、数字或逻辑值。 |
| Result_vector | 只包含一行或一列的区域，其大小必须与 lookup_vector 相同 |
=LOOKUP(x,A:A,B:B)
# 在 A 列中查找 x，并返回同一行 B 列的值
- 数组
函数 LOOKUP 的数组形式是在数组的第一行或第一列中查找指定数值，然后返回最后一行或最后一列中相同位置处的数值。
LOOKUP(lookup_value,array)
LOOKUP_vector 的数值必须按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE。否则，函数 LOOKUP 不能返回正确的结果。文本不区分大小写。
=LOOKUP("C",{"a","b","c","d";1,2,3,4})
# 在数组的第一行中查找“C”，并返回同一列中最后一行的值 (3)
2 VLOOKUP
VLOOKUP 函数在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时，可以使用函数VLOOKUP代替函数HLOOKUP。在VLOOKUP中的V代表垂直。
2.1 语法
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
| 参数 | 简单说明 | 输入数据类型 |
|---|---|---|
| lookup_value | 要查找的值 | 数值、引用或文本字符串 |
| table_array | 要查找的区域 | 数据表区域 |
| col_index_num | 返回数据在查找区域的第几列数 | 正整数 |
| range_lookup | 模糊匹配/精确匹配 | TRUE/FALSE（或不填） |
2.2 举例
# 新建excel
# "=CHAR(RAND()*26+65)" 生成随机大写字母
# "=CHAR(RAND()*26+97)" 生成随机小写字母
# "=RANDBETWEEN(bottom,top)" 生成随机数
# vlookup() 函数按列筛选匹配的数据，VLOOKUP(1,A:C,3,TRUE)为在 A ：C列中查找 1，并根据查到的匹配值返回A:C区域第3列的对应值
image.png
3 HLOOKUP
在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。当比较值位于数据表的首行，并且要查找下面给定行中的数据时，请使用函数 HLOOKUP。当比较值位于要查找的数据左边的一列时，请使用函数 VLOOKUP。
HLOOKUP 中的 H 代表“行”。
语法
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
| 参数 | 简单说明 |
|---|---|
| lookup_value | 为需要在数据表第一行中进行查找的数值,可以为数值、引用或文本字符串 |
| Table_array | 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用 |
| row_index_num | 为 table_array 中待返回的匹配值的行序号。row_index_num 为 1 时，返回 table_array 第一行的数值，row_index_num 为 2 时，返回 table_array 第二行的数值，以此类推 |
| Range_LOOKUP | 为一逻辑值，指明函数 HLOOKUP 查找时是精确匹配，还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值。也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值。如果 range_value 为 FALSE，函数 HLOOKUP 将查找精确匹配值，如果找不到，则返回错误值 #N/A! |
3.2 说明
- 如果函数 HLOOKUP 找不到 lookup_value，且 range_lookup 为 TRUE，则使用小于 lookup_value 的最大值。
- 如果函数 HLOOKUP 小于 table_array 第一行中的最小数值，函数 HLOOKUP 返回错误值 #N/A!
image.png
HVLOOKUP与VLOOKUP用法相同，其中H代表行，V代表列，返回的值分别为指定的行和列
4 MATCH
返回在指定方式下与指定数组匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用 MATCH 函数而不是 LOOKUP 函数。
4.1 语法
MATCH(lookup_value,lookup_array,match_type)
| 参数 | 简单说明 |
|---|---|
| Lookup_value | 为需要在数据表中查找的数值 |
| Lookup_array | 可能包含所要查找的数值的连续单元格区域。lookup_array 应为数组或数组引用 |
| Match_type | 为数字 -1、0 或 1。MATCH-type 指明WPS表格如何在 lookup_array 中查找 lookup_value |
4.2 说明
- 函数 MATCH 返回 lookup_array 中目标值的位置，而不是数值本身。例如，MATCH("b",{"a","b","c"},0) 返回 2，即“b”在数组 {"a","b","c"} 中的相应位置。
- 查找文本值时，函数 MATCH 不区分大小写字母。
- 如果函数 MATCH 查找不成功，则返回错误值 #N/A。
- 如果 MATCH_type 为 0 且 lookup_value 为文本，lookup_value 可以包含通配符、星号 (*) 和问号 (?)。星号可以匹配任何字符序列。问号可以匹配单个字符
image.png
5 INDEX
返回表或区域中的值或值的引用。函数INDEX()有两种形式：
数组和引用。数组形式通常返回数值或数值数组。引用形式通常返回引用。
- INDEX(array,Row_num,column_num) 返回数组中指定单元格或单元格数组的数值。
- INDEX(reference,Row_num,column_num,area_num) 返回引用中指定单元格区域的引用。
5.1 数组
返回数据清单或数组中的元素值，此元素由行序号和列序号的索引值给定。
INDEX(array,Row_num,column_num)
| 参数 | 简单说明 |
|---|---|
| Array | 为单元格区域或数组常量 |
| Row_num | 数组中某行的行序号，函数从该行返回数值。如果省略 Row_num，则必须有 column_num |
| Column_num | 数组中某列的列序号，函数从该列返回数值。如果省略 column_num，则必须有 Row_num |
说明
- 如果同时使用 Row_num 和 column_num，函数 INDEX 返回 Row_num 和 column_num 交叉处的单元格的数值。
- 如果将 Row_num 或 column_num 设置为 0，函数 INDEX 则分别返回整个列或行的数组数值。若要使用以数组形式返回的值，请将 INDEX 函数以[数组公式](javascript:viewsampleb())形式输入，对于行以水平单元格区域的形式输入，对于列以垂直单元格区域的形式输入。若要输入数组公式，请按 Ctrl+Shift+Enter。
-
Row_num 和 column_num 必须指向 array 中的某一单元格。否则，函数 INDEX 返回错误值 #REF!。
image.png
5.2 引用
返回指定的行与列交叉处的单元格引用。如果引用由不连续的选定区域组成，可以选择某一连续区域。
INDEX(reference,Row_num,column_num,area_num)
| 参数 | 简单说明 |
|---|---|
| Reference | 对一个或多个单元格区域的引用，如果为引用输入一个不连续的区域，必须用括号括起来，如果引用中的每个区域只包含一行或一列，则相应的参数 Row_num 或 column_num 分别为可选项。例如，对于单行的引用，可以使用函数 INDEX(reference,,column_num) |
| Row_num | 引用中某行的行序号，函数从该行返回一个引用 |
| COLUMN_num | 引用中某列的列序号，函数从该列返回一个引用 |
说明
- 在通过 reference 和 area_num 选择了特定的区域后，Row_num 和 column_num 将进一步选择指定的单元格：Row_num 1 为区域的首行，column_num 1 为首列，以此类推。函数 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 单元格中的数字。
image.png
6 COLUMNS
返回数组或引用的列数。
语法
COLUMNS(array)
Array 为需要得到其列数的数组或数组公式，或对单元格区域的引用
image.png
7 ROWS
返回引用或数组的行数。
语法
ROWS(array)
Array 为需要得到其行数的数组、数组公式或对单元格区域的引用。
image.png
8 ADDRESS
按照给定的行号和列标，建立文本类型的单元格地址。
语法
ADDRESS(row_num,column_num,abs_num,a1,sheet_text)
| 参数 | 简单说明 |
|---|---|
| Row_num | 在单元格引用中使用的行号 |
| Column_num | 在单元格引用中使用的列标 |
| Abs_num | 指定返回的引用类型 |
说明
- 1或省略 绝对引用
- 2 绝对行号，相对列标
- 3 相对行号，绝对列标
- 4 相对引用
# 对A1孔绝对引用
# 直接输入
=$A$1
# 输入"＝"，选中引用孔，按下"F4"，可以转换为绝对引用
=$A$1
image.png
9 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 列的区域的总值。
image.png
