巧妙解决二维表信息匹配问题

最近UP主在准备考试，所以更新频率略有下降。本期和大家分享的是Excel一个世界性难题！！

对就是上面那货！

二维表格的信息返回匹配是个难题，我们分享案例如下：

我有一张二维表，A列是投诉细项名称，B至E列是不同的问题及问题占比。

那我们将问题占比最大的五个数字进行了标红。标红是如何实现的呢？

请自行百度条件格式~

我们需要的效果是什么呢？

问题一：将二维表中最大的五个数字罗列出来

问题二：将每个问题对应的投诉细项匹配过来

解决问题一：N大数字罗列

先分享Large函数，他接两个参数

=Large（区域，数字N）

返回某个区域内第N大的数字

那本问题就比较好解决了！

很简单，就是利用Large函数，先选择区域，然后再输入返回第几个大的数即可，并且将第几个大的数通过引用数字实现。

到这里肯定有人问，假如没有辅助列呢？数字需要自己输入吗？

如何实现呢？

把第二个参数直接输入ROW（A1）即可，然后双击自动填充即可！

解决问题二：

方法一

将每个问题对应的投诉细项匹配

如何根据问题返回对应的投诉细项呢？

如果数字只是某一列，返回对应的投诉细项非常的容易，类似于Vlookup的反向匹配即可！可以使用match加index函数组合实现，那我们先试一下~

=INDEX(A2:A11,MATCH(A15,B2:B11,0))

之前很多文章介绍过match和index的组合用法，这里不累述了。

但是针对D列的2.67%如何返回对应的细项D呢？

前面large函数可以选择一个区域，但是match函数的第二个参数只能输入一个一维的数组哦！

那如何才能实现多列，如果某列满足条件就返回对应的值呢？思路如下：

1、编写四个match函数

2、如果能查到，返回对应的数字；如果没有找到，返回#N/A

3、Iferror嵌套在match外面，将#N/A修正为0

4、将所有函数结果汇总求和作为index的第二个参数

=INDEX(A2:A11,(IFERROR(MATCH(A15,B2:B11,0),0)+IFERROR(MATCH(A15,C2:C11,0),0)+IFERROR(MATCH(A15,D2:D11,0),0)+IFERROR(MATCH(A15,E2:E11,0),0)))

以28.18%为例，

IFERROR(MATCH(A15,B2:B11,0),0)返回值是4

IFERROR(MATCH(A15,C2:C11,0),0)返回值是0

IFERROR(MATCH(A15,D2:D11,0),0)返回值是0

IFERROR(MATCH(A15,E2:E11,0),0)返回值是0

四个参数相加后依旧为4

使用index返回对应数据即可！

这种方法就解决了多列数据进行匹配返回的问题！

解决问题二：

方法二

将每个问题对应的投诉细项匹配

上述问题可以解决本案例，并且从单match函数推导至多match函数组合应用，但是感觉略麻烦，所以又分享了如何使用sumproduct函数进行分享

=INDEX(A2:A11,SUMPRODUCT(((B2:B11=A15)+(C2:C11=A15)+(D2:D11=A15)+(E2:E11=A15))*(ROW(A1:A10))))

index函数不再解释，和大家分享一下sumproduct函数的思路

SUMPRODUCT(((B2:B11=A15)+(C2:C11=A15)+(D2:D11=A15)+(E2:E11=A15))*(ROW(A1:A10)))

sumproduct函数里面是两个数组相乘

数组一：(B2:B11=A15)+(C2:C11=A15)+(D2:D11=A15)+(E2:E11=A15)

数组二：ROW(A1:A10)

数组一每个等式的效果为何呢？

我们发现等号形成了一个逻辑数组，相等的为Ture，不等为False

那将逻辑数组加和为何物呢！

我们得到了一个1和0的数组。

数组二：ROW(A1:A10)是什么呢？

Row（A1：A10）是一个数字1至10的数组，与之前的相乘就等于4了。

然后这个4作为index的第二个参数就好啦~

以上~

附上Excel练习表~

链接: https://pan.baidu.com/s/1TBAz3W0xn0oSM2IQELBeMQ 密码: r9f3

感谢收看~