excel数据分析 – 8个关联匹配类函数

大家好，欢迎来到IT知识分享网。

1. LOOKUP函数

①单条件定位查找

=lookup( 待匹配内容,  待匹配内容所在区域 , 结果范围显示区域 )  两个区域的列数需相同

e.g.  查找 “东区”对应的C1省会城市，

     = lookup （A2, A:A , C:C ）= 杭州

②多条件定位查找

   两个及以上的条件之间用*连接，条件用括号括起来

   = lookup（ 1,0 / 

          ( ( 定位点1所在列 即条件区域 = 定位点1单元格） * ( 条件区域2 = 定位点2单元格 ）*…），

           要查找的对应列 即结果区域 ）

2. VLOOKUP函数

① 单条件匹配

区域匹配取值，即纵向查找-替换

=VLOOKUP(待查单元格，查找的条件区域，查找的定位列数num，模糊or精确匹配 一般填0）

② 辅助列-多条件匹配

在查找范围添加待查找条件的辅助列，用 ＆ 连接，使两列或多列显示在同一单元格字段内，再同样用＆ 连接待定位的单元格。

注：用辅助列时，注意事先排序

   e.g.  =VLOOKUP(F2&G2,$A$1:$D$15,4,0)

③ 通配符 -模糊查找

④近似匹配的适用情况

⑤数值-文本转换

方法2：文本型经过算数运算后，可转为数值型，在前面加2个负号即可（或其他计算后为0的表达式）

方法3： 插入一列空白列，输入 = 文本单元格*1      即可转为数字格式

3. HLOOKUP函数

相当于vlookup的兄弟，vlookup是同行不同列的查找，hlookup是同列不同行查询。

e.g. 查找 B1单元格对应的销量，查找C1单元格对应的单价

  = hlookup ( B1, A:C, 2, 0)  = 1234

  = hlookup ( C1, A:C, 3, 0)  = 11.2

4. INDEX函数

返回表or区域中的值，或返回值的引用，用行/列数去返回对应内容

=index（选中的索引区域，索引行数，索引列数） 以区域的左上角为起点

e.g. 返回表中的第3行、第2列的值，即 甘肃

    = index （A1:C3 , 3 ,2） = 甘肃

5. MATCH函数

返回指定数值在指定数组区域中的位置

① 找到单元格 所在行/列数

  =match （待查找内容单元值，待查找范围区域，查找方式）

查找方式： 1 –   小于or等于   待查找内容单元值   的最大值

                   0 –   等于             待查找内容单元值   的第一个值

                  -1 –   大于or等于   待查找内容单元值   的最小值

   e.g. =match （ C5,A5:D6,0)  

② match + vlookup组合

   实现 快速返回多列结果：将match嵌在vlookup里，使其成为第三参数     

   e.g.  = vlookup ( A15,  A1:A29,   match ( B12,A:G,0), 0 )

   用match函数，替换VLOOKUP函数中的第三个参数，即实现返回的内容所在列数的变化，而不是用固定的数字，具体步骤如下：

③ match+index组合

      =  INDEX (选中待查找单元格的所在列, （MATCH (单元格,  条件区域 ,  0))

6. ROW函数

返回单元格所在行数 （可用于排序or随机抽样），参照范围为全表

=row（单元格）

e.g1.  查看A1单元格所在的行数  = row（A1）

e.g2. 查看本单元格所在的行数  = row（）

7. COLUMN函数

返回单元格所在列数

= COLUMN（单元格）

e.g1.  查看A1单元格所在的列数  = COLUMN（A1）

e.g2. 查看本单元格所在的列数  = COLUMN（）

8. OFFSET函数

单元格or区域位移。即以指定的（单元格or区域）为参照系，通过给定偏移量得到新的引用

=OFFSET(选中起点单元格, 向下移动行数  向上为负,  向右移动列数 向左为负,  引用区域行数 选填,  引用区域列数 选填)

e.g. 选中A2单元格“东区”为起点，向下移动1行，向右移动2列

     =OFFSET(A2，1,2）= 兰州