我有一张二维表,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的第二个参数就好啦 |
|