Excel数据分析：函数公式之查找引用函数

公式和函数在Excel数据分析中是数据清洗中必不可少的一环，用于汇总统计、文本处理、时间计算、查找匹配等

说明：本文仅做个人笔记记录，如有疑问欢迎戳我~

1、公式使用技巧

单元格地址引用时的锁定

在锁定位置前加$符号

功能强大的公式审核

位置：“公式”选项卡——公式审核模块

1、追踪引用单元格：在公式选项卡的公式审核中，点击追踪引用单元格，用箭头标识出当前公式引用了哪些单元格

2、追踪从属单元格：在公式选项卡的公式审核中，点击追踪引用单元格，用箭头标识出哪些单元格引用了当前单元格

3、显示公式：显示公式内容，而不是计算结果，并标识关联的单元格

4、公式求值：在公式选项卡的公式审核中，点击公式求值，【求值】按钮逐步显示公式计算结果，帮助快速理解复杂的公式

5、错误检查：在公式选项卡的公式审核中，点击错误检查，可以查看公式错误的原因

2、查找引用函数

示例表

LOOKUP

含义：从单行或单列或数组中查找一个值，必须按升序排序

表达式：=LOOKUP(lookup_value,lookup_vector,[result_vector])

参数说明：

lookup_value：要匹配查找的内容

lookup_vector：匹配查找的数组区域，lookup_value的值需要在此区域内[result_vector]：查找条件返回的内容所在的列

示例：

表1查找编号2的水果：=LOOKUP(2,A2:A6,B2:B6) —— 结果返回：香蕉

VLOOKUP

含义：列查找函数，在给定查找目标时，能从指定查找范围中返回要查找的值

表达式：=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

参数说明：

lookup_value：要匹配查找的内容

table_array：匹配查找的范围（匹配在首列）

col_index_num：返回的内容所在的列数

[range_lookup]：0为精确匹配，1为近似匹配，也可以为TRUE/FALSE（模糊匹配无对应值时返回小于查找值的最大值）

示例：

表1查找编号2的水果：=VLOOKUP(2,A2:B6,2,0)

HLOOKUP

含义：可以在表格或数值数组的首行查找指定的数值，并返回表格或数组中指定行的同一列的数值

表达式：=HLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

参数说明：

lookup_value：要匹配查找的内容某一单元格

table_array：去匹配查找的数组区域，匹配行在首行

col_index_num：返回的内容所在行数（首行为1）

[range_lookup]：0为精确匹配，1为近似匹配，也可以为TRUE/FALSE

示例：

表2查找编号2的水果：=HLOOKUP(2,E1:I2,2,0)

CHOOSE

含义：根据索引值，从参数中返回相应的值或操作

表达式：=CHOOSE(index_num,value1,value2,...)

参数说明：

index_num：索引值

value：参数

示例：=CHOOSE(3,"A","B","C","D") —— 结果返回：C

INDEX

含义：返回表或区域中的值或值的引用，以行列数返回对应内容（结合MATCH找出某个值所在位置（不能有合并的单元格））

表达式：=INDEX(array,row_num,[column_num])

参数说明：

array：单元格区域或数组

row_num：行号

[column_num]：列号，可选，默认为第1列

示例：

表1返回第二行和第一列交叉的值：=INDEX(A2:B6,2,1) —— 结果返回：2

表1返回第三行和第二列交叉的值：=INDEX(A2:B6,3,2)  —— 结果返回：橘子

如果index函数第二个或者第三个参数为0，函数将分别返回整列或整行的数组值（输出区域需要和数值数量相同，使用 Ctrl + Shift + Enter 结束公式）

MATCH

含义：返回指定数值在指定数组区域中的第一个的位置（常和VLOOKUP匹配，可用来做查重）

表达式：MATCH(lookup_value,lookup_array,[match_type])

参数说明：

lookup_value：要查找的内容单元值，若是字符型需要加双引号

lookup_array：要在其查找的区域（行列）

[match_type]：精确匹配或模糊匹配，可以为以下值

1或省略：小于或等于lookup_value的最大值，lookup_array参数中的值必须按升序排列

0：等于lookup_value的第一个值，lookup_array 参数中的值可以按任何顺序排列

-1：大于或等于lookup_value的最小值，lookup_array 参数中的值必须按降序排列

示例：

表1查看"苹果"所在的位置：=MATCH("苹果",B2:B6,0) —— 结果返回：1

表1查看小于或等于3的最大值位置：=MATCH(3,A2:A6,1) —— 结果返回：3

INDIRECT

含义：间接引用，返回文本字符串所指定的应用（位置），可以辅助很多函数做事情

表达式：=INDIRECT(ref_text,[a1])

参数说明：

ref_text：定义的名称或者文本字符构成的引用的数据区域

a1：单元格引用类型，一般直接将其省略掉即可

示例：

① 在D1单元格查看文本格式的C1单元格地址所指向的内容：

② 跨表引用：

有1-12月工作表格式同下图

员工顺序相同：张三的业绩固定为G2，李四-G3，王五-G4，赵六-G5

按月份汇总张三总业绩

员工顺序不同

③ 制作二级下拉列表：

定义省份：选中A4:A13 - 公式 - 定义名称 - 名称同表头，吉林省（其他省相同操作）

选中F列：数据 - 数据验证 - 允许选择序列，来源【A3:C3】

选中G列：数据 - 数据验证 - 允许选择序列，来源【=indirect(F1)】

设置表头：选中F1和G1，数据验证全部清除，添加表头

OFFSET

含义：以指定的引用（单元格或相连单元格区域的引用）为参照系，通过给定偏移量得到新的引用

表达式：=OFFSET(reference,rows,cols,[height],[width])

参数说明：

reference：起点（偏移量的引用区域）

rows：上下偏移的行数，向下为正，向上为负

cols：左右偏移的列数，向右为正，向左为负

[height]：可选，引用区域行数（含到达点），默认为1

[width]：可选，引用区域列数（含到达点），默认为1

示例：

表1返回B2不偏移值：=OFFSET(B2,0,0) —— 结果返回：苹果

表1返回B2向下偏移1个单元格，向左偏移1个单元格 ：=OFFSET(B2,1,-1) —— 结果返回：2

ROW

含义：获取行号的函数（参照范围为整个表）

表达式：=ROW(reference)

参数说明：reference为引用的单元格

示例：

表1查看"苹果"所在的行数：=ROW(B2) —— 结果返回：2

表1查看此单元格所在的行数：=ROW()

COLUMN

含义：获取列号的函数（参照范围为整个表）

表达式：COLUMN(reference)

参数说明：reference为引用的单元格

示例：

表1查看"苹果"所在的列数：=COLUMN(B2) —— 结果返回：2

表1查看此单元格所在的列数：=COLUMN()