【数据分析之EXCEL篇】6个常见的关联匹配函数

目录

1. VLOOKUP
2. INDEX
3. MATCH
4. ROW
5. COLUMN
6. OFFSET

   ---

    

1 VLOOKUP函数

• 功能：按列查找
• 语法格式：＝VLOOKUP（查找目标，查找范围，返回值的列数，精确or 模糊查找）
• 查找方式有2种：
  • 精确查找：0或FALSE
  • 模糊查找：1或TRUE
• 查询方式

      (1) 单条件查询（使用单个关键字检索，且检索关键字在选择区域的第一列。如下图检索关键字为工号，检索工号对应的工资）

(2)  反向查询

• 检索的关键字不在查找区域的第一列（如下图所示，姓名不在查找区域的第1列，而在第2列）
• 语法格式：=VLOOKUP(检索关键字，IF(｛1，0｝，检索关键字所在列，查找值所在列)，2，0）

（3）多条件查询

• 语法格式:＝VLOOKUP(检索关键字1＆检索关键字2，IF(｛1,0｝,序列1＆序列2，查找值所在列)，2，0）
• 注意:使用了数组的公式不能直接回车，应使用shift+ctrl+enter结束,不然会出错哦！
• 如下图：=VLOOKUP(F2&G2,IF({1,0},$A$2:$A$10&$B$2:$B$10,$C$2:$C$10),2,0) 下图为根据工号+姓名查找对应的部门

2 INDEX函数

• 功能:返回表或区域中的值或值的引用形式
• 2种引用形式
  • 数组形式
    • 语法格式:=INDEX(数组，行序数，列序数)
    • 应用如下图：数组B2:D10，  行序数为4 ，列序数2。结果返回数组区域第四行第二列：行政部

• • 引用形式
    • 语法格式：=INDEX(数组，行序数，列序数，区域序数)
    • 注意:
      • 这里数组为对一个或多个单元格的引用，如果为引用不连续的区域，则必须将其用括号括起来，如下图所示。
      • 区域序数省略不填默认为第一个区域。区域序数为数组括号内的填写顺序

3 MATCH函数

• 功能:返回在指定方式下与指定匹配的数组中元素的相应位置
• 语法格式:=MATCH(查找值，查找区域，匹配类型） 
• 注意
  • 查找区域为连续的一行或一列
  • 匹配类型一般有3种:
    • 0 即精确查找:表示查找等于"查找值"的第一个数值
    • 1 :表示查找小于等于"查找值"的最大数值
    • -1 :表示查找大于等于"查找值"的最小值
    • 应用如下图所示

• MATCH与INDEX联合使用，可以达到VLOOKUP的查找功能

4 ROW函数

• 功能:返回指定的行号
• 语法格式:=ROW(参照区域)
• 注意
  • 省略参照区域，则默认为引用函数ROW所在的单元格；若要返回指定行号1，则参照区域填A1

 

5 COLUMN函数

• 功能:返回指定引用的列号
• 语法格式: =COLUMN(参照区域)

6 OFFSET函数

• 功能:以指定引用为参照系，通过给定的偏移量、行数及列数返回一个新的引用
• 语法格式: =OFFSET(参照区域，行偏移量，列偏移量，高度，宽度)
• 注意:
  • 行\列偏移量可为正数也可为负数，分别代表在参照区域的下边\右边，或上边\左边
  • 高度\宽度必须为正数