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

最近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

感谢收看~

原文发布于微信公众号 - 阿凯的笔记(akainote)

原文发表时间:2018-03-20

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏程序员叨叨叨

6.5 Swizzle 操作符

可以使用Cg语言中的swizzle操作符(.)将一个向量的成员取出组成一个新的向量。swizzle操作符被GPU硬件高效支持。swizzle操作符后接x、y、z...

2105
来自专栏zaking's

js算法初窥07(算法复杂度)

1263
来自专栏数说工作室

统计师的Python日记【第3天:Numpy你好】

本文是【统计师的Python日记】第3天的日记 回顾一下,第1天学习了Python的基本页面、操作,以及几种主要的容器类型;第2天学习了python的函数、循环...

44412
来自专栏CDA数据分析师

用SPSS做数据分析?先弄懂SPSS的基础知识吧

1、SPSS数据分析的流程 ? 2、SPSS特性: ? 3、数据的编辑: 1 常量 数值型常量:除了普通写法外还可以用科学计数法,如:1.3E18; 字符型常量...

34010
来自专栏程序员宝库

LCS 算法:Javascript 最长公共子序列

作者:司徒正美 链接:https://segmentfault.com/a/1190000012864957 最长公共子序列(Longest Common Su...

56310
来自专栏绿巨人专栏

Modern Algebra 读书笔记

3545
来自专栏章鱼的慢慢技术路

牛课堂算法直播题目

2878
来自专栏一“技”之长

从一些简单的例子看算法时间复杂度 原

    在编程中,一段代码的执行效率实际上很难估算和预测,其主要受到如下几个方面的影响:

511
来自专栏cs

Mathematica学习笔记

放假了,近来无事,就复习了一下mathematica相关知识点。已经玩了很多东西,不过大概还是很熟悉。 Mathematica(我简称mma),可以通过交互方...

5496
来自专栏计算机视觉与深度学习基础

Leetcode 8 String to Integer (atoi)

Implement atoi to convert a string to an integer. Hint: Carefully consider all...

1908

扫码关注云+社区

领取腾讯云代金券