一对多查询,超实用的函数教程

今天和大家分享的是如何快速获取符合某个条件的第N个数。

如果这个问题改为获取最大的数,那一定很好解决,但是工作中不但要知道第一高峰是珠穆朗玛峰,还需要知道第二高峰是乔戈里峰,第三高峰是干城章嘉峰。

怎么样?看本公众号还可以学习地理知识吧!

咳咳,鉴于我一直是很严肃的小编,在此,正式和大家分享。

需求如下,小编所在的年级的成绩排行如下:

我想根据年级的成绩排名,获取一班的前三名的名称,就是标黄色的!

至此,读者一定有两个问题?

1、阿凯是谁? 2、为啥他是0分?

答:1、阿凯是小编,2、你和这么多美女一个班级,你会好好学习哇!!!!

怎么得到这个答案的尼~~~~~

直接把答案粘贴下来吧:

{=INDEX(B1:B9,SMALL(IF(C2:C9="一班",ROW(C2:C9),100),G2))}

我翻译一下:

{=INDEX(姓名列表,SMALL(IF(班级信息="一班",ROW(班级信息),一个很大的数),第几名))}

至此,如果能够读懂这个函数的人,可以直接关掉本篇内容,继续和苍老师谈人生好了!

还在的亲们,介绍几个函数给各位亲:

1、IF函数,最常用的判断函数,咋用呢?

=IF(判断条件,为真返回什么,为假返回什么)

例子:

=IF(小编=最帅的人,“真有眼光”,“不喜欢我的人多了,你算老几!”)

返回值是“真有眼光”

2、Small函数,返回某个数组的第N个小的值

姐妹函数是Large,返回某个数组的第N个大的值

=Small(数组,第几个小的数)

例子:

=Small({4,3,1,2,8},3) 返回值是3

3、Index函数,引用函数,返回某个数组的第几个值

注意:这里是没有进行排序的,直接按照顺序返回第几个

=Index(数组,第几个值)

=Index({4,3,1,6,8},3) 返回值是1

知道Index和Small的区别了吧!

4、Row函数,返回某个单元格的对应行数

=Row(单元格)

=Row(C4) 返回值是4

啰嗦这么多,你们肯定把问题都忘了,重新上图

我想获取一班第一至第三名需要怎么做呢?简述一下思路

知道哪些人是一班的->得到他们的行号->然后找到第几小的行号信息->返回行对应的姓名

慢动作分解第一次!

第一步:我需要知道哪些人是一班的(用IF语句实现)

如果他是一班的,返回他对应的行数,如果不是一班的,我就赋予他一个很大的值(本案例赋值100)

语句实现:

=IF(C2:C9="一班",ROW(C2:C9),100)

翻译:IF(班级信息="一班",ROW(班级信息),100)

由于班级信息是{一班,二班,二班,一班,一班,一班,二班,一班}

所以IF的返回值就是{2,100,100,5,6,7,100,9}

第二步:接下来要获取第一名所对应的行信息

语句实现:

=Small({2,100,100,5,6,7,100,9},1)

如果是第一名就返回的是2,如果是第二名返回的就是5

这二步实现了获取不同名次的行信息。

第三步:知道了行信息,返回对应的姓名

语句实现:

=Index(姓名列表,返回第几个姓名)

整体函数的实现思路就是这样!

如果不清楚,慢动作分解第二次!

第一步:判断语句,如果是一班,返回行数,如果不是,返回100

=IF(C2:C9="一班",ROW(C2:C9),100)

第二步:获取上面数组中第N个小的值(本案例获取第二个小的)

=SMALL(F2:F9,2)

第三步:返回第五行对应的姓名信息

整个函数的逻辑引用精华就是在上面这个图啦,然后就将上一个步骤的函数嵌套到下一个函数的里面就好!

结论性的公式如下:

{=INDEX(B1:B9,SMALL(IF(C2:C9="一班",ROW(C2:C9),100),G2))}

看到外面的{ }了没,这个不是人为输入的,而是因为用到了数组,所以编辑好函数后,要用Ctrl+Shift+Enter结束,结束后自动添加{}!

如果还想优化一下的话,可以做个绝对引用!

{=INDEX($B$1:$B$9,SMALL(IF($C$1:$C$9="一班",ROW($C$1:$C$9),100),G2))}

今天就到这里!

感谢观看!

原文发布于微信公众号 - 阿凯的Excel(akexcel)

原文发表时间:2017-01-26

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏小樱的经验随笔

HDU 1754 I Hate It(线段树之单点更新,区间最值)

I Hate It Time Limit: 9000/3000 MS (Java/Others)    Memory Limit: 32768/32768 K ...

2747
来自专栏数说工作室

移形换影 | 【SAS Says · 扩展篇】

【SAS Says·扩展篇】移形换影 | 5. call PRXCHANGE() 0. 前集回顾 1. 新的问题 2. 初识 PRXCHANGE() 3. 问题...

34611
来自专栏ImportSource

java中的Checked Exceptions和Unchecked Exceptions

在java世界中有两种异常,一种Checked Exceptions ,另一种叫Unchecked Exceptions. 1) Checked Except...

3004
来自专栏数据结构与算法

BZOJ3693: 圆桌会议(Hall定理 线段树)

我的思路:对于区间分两种情况讨论,一种是完全包含,另一种是部分包含。 第一种情况非常好判断,至于计算对于一个区间[l, r]的$\sum a[i]$就可以了,但...

852
来自专栏从零开始学自动化测试

python笔记1-用python解决小学生数学题

前几天有人在群里给小编出了个数学题: 假设你有无限数量的邮票,面值分别为6角,7角,8角,请问你最大的不可支付邮资是多少元? 小编掰着手指头和脚趾头算了下,答案...

2929
来自专栏小樱的经验随笔

51Nod 1016 水仙花数 V2(组合数学,枚举打表法)

1016 水仙花数 V2 基准时间限制:1 秒 空间限制:131072 KB 分值: 160         难度:6级算法题 水仙花数是指一个 n 位数 ...

2607
来自专栏tkokof 的技术,小趣及杂念

位运算实用指南

说明 : 想来这应该是初次接触移位操作符时一定会了解到的知识点,根据2进制的整数表示方法应该不难理解,原因细节不再赘述~

561
来自专栏小詹同学

Leetcode打卡 | No.012 整数转罗马数字

欢迎和小詹一起定期刷leetcode,每周一和周五更新一题,每一题都吃透,欢迎一题多解,寻找最优解!这个记录帖哪怕只有一个读者,小詹也会坚持刷下去的!

1021
来自专栏Java学习网

10种简单的Java性能优化学习

10种简单的Java性能优化学习 你是否正打算优化hashCode()方法?是否想要绕开正则表达式?Lukas Eder介绍了很多简单方便的性能优化小贴士以及扩...

2556
来自专栏ImportSource

来来来,快来围观那个Kotlin

这个世界怎么了?我都惊了。 kotlin来了,就因为Google背书了一哈,你们就无条件的沸腾了。 这年头出来了所谓语言还少吗? ? 三天两头搞些新花样。 你们...

46011

扫码关注云+社区