一种在excel中通过index+match函数实现多条件数据匹配的方法

当一个统计的表格中有名称、型号、数量,在下发填写收集上来后一些项目做了增减,而你还需要苦哈哈的一个一个对名称、型号来汇总数量的时候,你就知道函数的好了。

index函数可以在一个区域内通过行数、列数定位你要的数据,即

=index(数据区域,行数,列数)

match函数可以在一个区域内定位值的行数,即

=match(值,数据区域,精确/近似匹配)

两相结合可以得到

=index(表2.数量值所在列,match(表1.名称,表2.名称列,精确匹配))

类似于vlookup函数,即vlookup(表1.名称,表2.名称列、数量列所在区域,列数,精确/近似匹配),vlookup简易好上手,只要保证[表2.名称列、数量列所在区域]中,第一列是名称列用于函数匹配得出行数,结合输入的列数,单条件匹配完全够用。但是一旦涉及到多条件,这样的匹配方式就不太适用了。

回到index+match,现在的问题是有名称、型号两种条件,需要找到对应名称、型号的那一行,怎么办呢?通过在match函数中使用数组运算可以解决。

回忆一下match函数,是在数据区域中,找到匹配值的行数,一般是一列中找一个匹配值,假设是A1:A100,如果A10=匹配值,match函数就会得出10。我们在这样的基础上思考,两个条件会如何,即有两列A1:A100,B1:B100,匹配值有两个a、b,如果有那么一行满足A10=a且B10=b,我们想要match函数返回10,

但是他的匹配值只有一个参数,数据区域只有一个区域

这个时候聪明的你看到“A10=a且B10=b”就想到了数组运算。A1:A100=a,会得到一个布尔数组{false,false....true,...},在这个数组中A10=a的行true,将这个布尔数组作为数据区域,相应的匹配值就是1或者0。

但是我们是多条件,继续往下,同理B1:B100=b也会得到一个数组{false,true....true,...},在这个数组中B10=b的行true,两个数组相乘,则对应位置的布尔值相乘,得到一个新的布尔数组{false....,true,...},同真则真,即“A10=a且B10=b”的行true,这样就解决一个数据区域和一个参数的问题。

此时可以得出最终答案:

=index(表2.数量值所在列,match(1,(表2.名称列=表1.a)*(表2.型号列=表1.b),精确匹配))

注意事项:

1.index本还有第三个列数参数,但是这里选取的单列,所以可以省略这个参数

2.非 Office 365 / Excel 2021 版本中使用数组公式,最终确认需要Ctrl+Shift+Enter,单单Enter会将match函数演变成match(1,1,0),然后报错。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值