1.6Excel--查找和引用函数

1.1 函数清单

函数	介绍
VLOOKUP()	垂直方向查找
OFFSET()	计算偏移量
MATCH()	查找位置
INDEX()	查找数据
ROW()	引用行的数据
COLUMN()	引用列的数据
INDIRECT()	文本字符串指定的引用
HLOOKUP()	水平方向查找（自学）
Choose()	索引值指定参数列表中的数值。（自学）
Find()	一个字符串在另一个字符串的起始位置（自学）

1.2详解函数–Excel查找引用函数

1.2.1 VLOOKUP()

1. 语法：VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])。

VLOOKUP(查找值，查找范围，查找列数，精确匹配或者近似匹配）

2. 参数：

   参数	简单说明	输入数据类型
   lookup_value	要查找的值	数值，引用或文本字符串
   table_array	要查找的区域	数据表区域
   col_index_num	返回数据在查找区域的第几列数	正整数
   range_lookup	精确匹配/近似匹配	FALSE（或0）/TRUE（或1或不填）

要查找的区域始终位于所在区域的第一列，这样VLOOKUP 才能正常工作

3. 案例：

   1.1VLOOPUP函数模型

   ​ 1.案例：获取湖南特产

   ​ 2.方案：=VLOOKUP(C19,C14:I19,6,FALSE)

   ​ 3.知识点：要查找的值必须是选中范围首列

只支持正向查找

​ 2.2VLOOPUP实例1

​ 1.案例：将I列的数据库技术与编程复制到D列的数据库技术与编程

​ 2.方案：=VLOOKUP(B2,$H$2:$I$122,2,FALSE)

​ 3.3员工档案

​ 1.案例1：通过身份证号码获取省编号

​ 方案：LEFT(D2,2)

知识点：LEFT 从文本字符串的第一个字符开始返回指定个数的字。

LEFT(text, [num_chars])

• Text 必需。 包含要提取的字符的文本字符串。
• num_chars 可选。 指定要由 LEFT 提取的字符的数量。
• num_chars 必须大于或等于零。
• 如果 num_chars 大于文本长度，则 LEFT 返回全部文本。
• 如果省略 num_chars，则假定其值为 1。

2. 案例2：通过身份证号码获取所属省自治区直辖市

   方案：=VLOOKUP(LEFT(D2,2),全国地区表!$A$1:$B$34,2,FALSE)

3. 案例3：通过身份证号码获取所属区县

   方案：=VLOOKUP(LEFT(D2,6),全国地区表!$A$35:$B$5944,2,FALSE)

4. 案例4：通过身份证号码获取出生日期

   方案：=–TEXT(MID(D2,7,8),"####-##-##")

知识点：MID 返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。MID(text, start_num, num_chars)

• 文本 必需。 包含要提取字符的文本字符串。

• start_num 必需。 文本中要提取的第一个字符的位置。 文本中第一个字符的 start_num 为 1，以此类推。

  • 如果 start_num 大于文本长度，则 MID/MIDB 返回 “” （空文本）。

  • 如果 start_num 小于文本的长度，但 start_num 加 num_chars 超过文本的长 度，则 MID/MIDB 返回文本结尾的字符。

• 如果 start_num 小于1，MID/MIDB 将返回 #VALUE！ 。

• num_chars 对 MID 是必需的。 指定希望 MID 从文本中返回字符的个数。

• 如果 num_chars 为负值，MID 将返回 #VALUE!。

1.2.2 OFFSET()

1. 语法：OFFSET(reference, rows, cols, [height], [width])

   Offffset（参照单元格，行偏移量，列偏移量，返回几行，返回几列）

2. 参数：

   • Reference 必需。 要作为偏移基准的参照。 引用必须引用单元格或相邻单元格区域。否则, OFFSET 返回 #VALUE! 。
   • Rows* 必需。 需要左上角单元格引用的向上或向下行数。 使用 5 作为 rows 参数，可指定引用中的左 上角单元格为引用下方的 5 行。 Rows 可为正数（这意味着在起始引用的下方）或负数（这意味着在起 始引用的上方）。
   • Cols 必需。 需要结果的左上角单元格引用的从左到右的列数。 使用 5 作为 cols 参数，可指定引用中 的左上角单元格为引用右方的 5 列。 Cols 可为正数（这意味着在起始引用的右侧）或负数（这意味着在 起始引用的左侧）。
   • 高度 可选。 需要返回的引用的行高。 Height 必须为正数。
   • 宽度 可选。 需要返回的引用的列宽。 Width 必须为正数。

3. 作用：提取单元格的值，或者目标区域的值

4. 备注：

• 如果 “行” 和 “cols 偏移” 引用覆盖了工作表的边缘, 则 offffset 返回 #REF! 。

• 如果省略 height 或 width，则假设其高度或宽度与 reference 相同。

• OFFSET 实际上并不移动任何单元格或更改选定区域；它只是返回一个引用。 OFFSET 可以与任何期待 引用参数的函数一起使用。 例如，公式 SUM(OFFSET(C2,1,2,3,1)) 可计算 3 行 1 列区域（即单元格 C2 下方的 1 行和右侧的 2 列的 3 行 1 列区域）的总值。

3.案例：

1.4OFFSET应用基础

​ 1.案例1：复制固定区域

​ 方案：=OFFSET(B1,6,1,5,3)

2.5Offffset理解图示

1.案例1：不规则坐标复制固定区域

  方案：=OFFSET(D9,-3,-1,3,3) 

3.练习

1.2.3 MATCH()

1. 语法：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_value 与 lookup_array 中的值匹配。 此参数的默认值为 1。 下表介绍该函数如何根据 match_type* 参数的设置 查找值。

  • 0：任何排序
  • 1：升序
  • -1：降序

3. 案例：

MATCH理解

​ 1.6案例1：查找100的位置–单列位置

​ 方案：=MATCH(100,C2:C14,0)

​ 2.案例2：查找中国的位置–单行位置

​ 方案：=MATCH(H4,F4:L4,0）

2. 7相同个数

   1.案例：统计左栏和右栏匹配个数

   方案：=COUNT(MATCH(A2:A11,B2:B11,0))

1.2.4 INDEX()

1. 语法：INDEX(array, row_num, [column_num])

INDEX(选择范围，行，列)

2. 参数：

• 数组 必需。 单元格区域或数组常量。

  • 如果数组只包含一行或一列，则相应的 row_num 或 column_num 参数是可选的。
  • 如果数组具有多行和多列，并且仅使用 row_num 或 column_num，则 INDEX 返回数组中整个行 或列的数组。

• row_num 必需，除非存在 column_num。 选择数组中的某行，函数从该行返回数值。 如果省略 row_num，则需要 column_num。

• column_num 可选。 选择数组中的某列，函数从该列返回数值。 如果省略 column_num，则需要 row_num。

• 案例：

  1.8INDEX理解

  ​ 1.案例1：根据位置来查找数据（多行多列）

  ​ 方案：=INDEX(B2:H14,6,4)

  ​ 2.案例2：根据单列查找数据 （单列）

  ​ 方案：=INDEX(E5:E12,3)

  ​ 3.案例2：根据单行查找数据 （单行）

  ​ 方案：=INDEX(D7:F7,2)

1.2.5 综合练习

1.2.5_1 9累积数据

案例：计算累加和

方案1：=SUM($B$2:B2)

方案2：=SUM(OFFSET($B$1,1,0,DAY(A2),1))

方案3：=SUM(OFFSET($B$1,1,0,ROW(A1),1))

方案4：=SUM(OFFSET($B$1,1,0,COUNT($B$2:B2),1))

1.2.5_2 10提取姓名

案例：提取表格中的姓名

方案1：=OFFSET($B$1,ROW()*2-3,0)

方案2：=INDEX($B$1:$B$12,ROW()*2-2)

方案3：=INDEX($B$2:$B$12,ROW()*2-3)

1.2.6 INDIRECT

1. 语法：INDIRECT(ref_text, [a1])

INDIRECT(引用区域，引用格式）

2. 参数：

• Ref_text 必需。 对包含 A1 样式的引用、R1C1 样式的引用、定义为引用的名称或对单元格的引用作为 文本字符串的单元格的引用。

  • 如果 ref_text 不是有效的单元格引用, 则间接返回 #REF! 。
  • 如果 ref_text 引用另一个工作簿 (外部引用), 则必须打开另一个工作簿。 如果源工作簿未打开, 则间 接返回 #REF! 。

  注意 Excel Web App 中不支持外部引用。

  • 如果 ref_text 引用的单元格区域超出1048576的行限制或列限制 16384 (XFD), 则间接返回 #REF! 错误。

  注意 此行为不同于早于Microsoft Offiffiffice Excel 2007 的 Excel 版本, 这将忽略超过的限制并返回 值。

• A1 可选。 一个逻辑值，用于指定包含在单元格 ref_text 中的引用的类型。

  • 如果 a1 为 TRUE 或省略，ref_text 被解释为 A1-样式的引用。
  • 如果 a1 为 FALSE，则将 ref_text 解释为 R1C1 样式的引用。

• 案例：

  1.11二级组合框-indirect

• =INDIRECT(直接引用单元格地址)

• =INDIRECT(A4)
  

• =INDIRECT(引用字符串)

• =INDIRECT(“A4”)
  

1.12行政区划分

案例：省直辖市自治区和关联市/区–下拉选框格式

方案：

（1）公式—》名称管理器—》新建—》名称—》省直辖市自治区—》引用位置

（2）公式—》选中市区数据—》根据所选内容创建—》首行

（3）数据—》数据验证—》序列—》来源—》=省直辖市自治区

（4）数据—》数据验证—》序列—》来源—》=indirect($A$2)