职业必备技能——Excel数据分析（四）

害羞的白菜

1527人浏览 · 2025-05-22 17:51:05

害羞的白菜 · 2025-05-22 17:51:05 发布

match函数和index函数

match函数的功能和参数

match 的例子

match不光可以匹配列，还可以对表头进行匹配

match配合index在跨数据表进行数据的提取实战

使用index和match时可能出现的问题

自动填充的时候出现#N/A的报错，最常见的一种错误

总结：

使用求和函数配合index和match进行数据的筛选

match函数和index函数

index和match函数可以根据属性列的名称的输入自由的进行数据的匹配，可以称得上是Excel的高阶用法

match函数的功能和参数

match的功能就是在选定的行或列中找出拟给定的数值，找到值在区域中的第几个位置。参数如下图

查找值表示我门查找的具体的内容

查找区域表示我们在哪一个区域进行查找

匹配类型有三种类型，0表示精确匹配，1表示小于（默认），-1表示大于

match 的例子

例如我们有如下的查找区域，现在需要在“match练习”属性列中查找“查找的随机值”所在的属性列中的列的内容

我门可以随机生成第二列的信息，使用randbetween函数进行数值的随机生成

之后在第三列输入match进行查找的内容即可，具体的代码如下

=MATCH(L45,$K$45:$K$63,0)
=match（）表示我门使用match值位置查找的函数
L45表示我门需要查找的内容
$K$45:$K$63表示我门查找的区域，且该区域是固定的区域，不会因为自动填充而发生变化
0表示使用精确匹配的方式

match不光可以匹配列，还可以对表头进行匹配

具体的方法不做展示，粘贴代码如下

=MATCH(P44,$P$44:$W$44,0)
P44表示我门查找的内容所在的单元格
$P$44:$W$44表示查找的区域
0表示进行精确匹配查找

注意：match不支持合并单元格的跨行查找

这个时候我门可能会出现疑问：我门找到了单元格的位置究竟有什么作用呢？这就要讲到我门的index函数了，index函数中函数的参数就需要用到我门记录的行号和列号

index参数的功能

数组的本质和区域是一致的，我门需要框选出一个区域，但是index会把该区域自动变成一个二维数组，这就是“数组”的由来，需要注意的是本数组的起点是由1开始的而不是0！

行序数表示我门在数组中输入的行

列序数表示我门在数组中输入的列，与行序数进行搭配可以在数组区域中检查出坐标的内容

index的例子

例如我们要查找区域P44到W45区域内的坐标的内容我们可以使用index函数进行实现

match和index的结合使用

根据match的特性可以返回内容所在的行或者列，我门可以使用match记录单元格的行号和列号，之后外面嵌套上index，根据index查找index区域内的数组的行号列号的内容，正好我门数组内的行号列号被我门的match检测出来了（match函数返回了行号和列号）

例如我们要通过index和match检测P44的内容

=INDEX(O44:W45,MATCH(P44,P44:P45,0),MATCH(P44,O44:W44,0))
044:W45表示查找的区域
MATCH(P44,P44:P45,0)表示查找内容所在的行
  P44表示查找的内容
  P44:P45表示查找的区域
  0表示match精确匹配
MATCH(P44,O44:W44,0)表示查找内容所在的列
  P44表示查找的内容
  O44:W44表示查找的区域
  0表示精确匹配
因为index中的格式为index（查找内容，行号，列号），这里的行号和列号都让match代替查找出来了

这样做的目的和优点

当我门使用函数进行匹配的时候方便自动填充，自动填充自动计算行和列的值，让Excel自动识别标头抓取数据，既然能够抓到表头当然就能够很轻易的抓到表头内的数据了

match配合index在跨数据表进行数据的提取实战

我们已经知道了如下选中的表格，想要实现跨数据表拿取数据

对原数据的部分属性列如下图

以拿到“门店ID”举例，总体的格式还是index（内容，match（），match（））这种格式，正确的答案如下，我门开始进行分析

=INDEX('源数据0701-0816'!$A:$AJ,MATCH($O58,'源数据0701-0816'!$C:$C,0),MATCH(P$57,'源数据0701-0816'!$1:$1,0))
分析：
'源数据0701-0816'!$A:$AJ 表示我门从哪个数组组成的域中拿到数据
MATCH($O58,'源数据0701-0816'!$C:$C,0)
  O58的内容为拌客（武宁路店），就是我门查找的内容所在的行
  '源数据0701-0816'!C:C表示拌客（武宁路店）出现在原数据的哪个属性列中，根据图2我门可知表示拌客（武宁路店）在原数据中的C列
  0表示精确匹配
MATCH(P$57,'源数据0701-0816'!$1:$1,0)
  P57的内容为门店ID，就是我门查找内容所在的列
  '源数据0701-0816'!1:1表示门店ID在原数据中的哪一个区域，哪一行可以涵盖门店ID？回到图二我门知道在原数据的第一行就是所有的表头信息，可以涵盖门店ID所在的列
  0表示精确匹配

之后我们通过自动填充可以将另一个品牌“蛙小辣火锅杯（总账号）”的门店ID使用Index和match筛选出来

这样我门进行自动填充的时候就可以一键完成数据的填充了

使用index和match时可能出现的问题

自动填充的时候出现#N/A的报错，最常见的一种错误

需要我门重新检查函数部分，一般的index中第一个参数数组的位置是锁定的，且第一个match的第一个参数是用来查找行的需要把列锁住；第二个match的第一个参数是用来查找列的则需要把行锁住

总结：

1.如想使用index和match提取数据，首先要找的就是数据的行标题在原数据中的哪一行（拌客（武宁路店）在原数据的哪一行）之后要找的就是数据的列标题在原数据中的哪一列（门店ID在原数据中的哪一列）

2.不是直接找单元格而是先定位单元格的行标题和列标题在原数据这个数组中的位置（使用index函数把原数据变成数组）找到了单元格在原数据中的行和列之后使用index定位数据内容。

3.需要注意的是自动填充的时候原表格的表头的顺序需要和我门的index、match中的顺序一致

原数据表的顺序

进行index和match运算的数据表的顺序

使用求和函数配合index和match进行数据的筛选

如果我门想要求得不同平台门店的GMV，这样就是求和函数结合index和match进行函数的运算，此时的最外层的函数的结果为sumifs，具体的代码如下

=SUMIFS(INDEX('源数据0701-0816'!$A:$AJ,0,MATCH(V$57,'源数据0701-0816'!$1:$1,0)),'源数据0701-0816'!$I:$I,$U58)


sumifs表示条件求和，INDEX('源数据0701-0816'!$A:$AJ,0,MATCH(V$57,'源数据0701-0816'!$1:$1,0))表示sumifs的第一个参数，即计算出sumifs的求和区域，对所有行的gmv进行求和，注意这个gmv是excel自己计算出来的
  INDEX('源数据0701-0816'!$A:$AJ,0,MATCH(V$57,'源数据0701-0816'!$1:$1,0))
    源数据0701-0816'!$A:$AJ表示数组代表的区域
    0在index中的“行”的位置，表示选中了所有的行
    MATCH(V$57,'源数据0701-0816'!$1:$1,0)
      这里的V$57代表的是“GMV”，而'源数据0701-0816'!$1:$1表示在原数据的第一行进行查找，方式为精确查找（0）
  '源数据0701-0816'!$I:$I,表示sumifs的第二个参数，就是“区域”，对应到原数据中的I列代表的是“平台门店名称”属性列
  $U58表示的是平台门店的名称，这里的内容为“拌客·干拌麻辣烫(武宁路店)”

总结：上述代码的功能就是让index和match自动根据属性列的列名称回到原数据中进行查找，找到数据到底在哪一列，返回第几个数字，之后sumifs就可以基于这一列的数值进行计算了，条件即为“平台门店的名称”，要找到原数据中的平台门店名称属性列中有哪些门店，之后找平台门店名称=$U58的即可

注意列的拖拽，哪些是绝对引用哪些是相对引用。