INDEX+SMALL+IF+ROW
更新时间:2024-03-18 07:50:01 阅读量: 综合文库 文档下载
很多人在Excel中用函数公式做查询的时候,都必然会遇到的一个大问题,那就是一对多的查找/查询公式应该怎么写?大多数人都是从VLOOKUP、INDEX+MATCH中入门的,纵然你把全部的多条件查找方法都学会了而且运用娴熟,如VLOOKUP和&、SUMPRODUCT、LOOKUP(1,0/....,但仍然只能对这种一对多的查询望洋兴叹。
这里讲的INDEX+SMALL+IF+ROW的函数组合,就是解决一对多查询的一种通式,如果你能掌握,那在Excel里基本上就没有什么查询你是实现不了的了(除了INDIRECT+RC引用)。
下面,我们先来看看示例数据和查询要求:
由于VLOOKUP、INDEX+MATCH、LOOKUP(1,0/都只是一对一的查询,有的是只查询第一个,有的是只查找最后一个,所以这种组合对于我们这里的要求完全无用武之地。所以,你也别把精力都花在这个上面,虽然也是可以构造出来的,但今天我们要讲的这个组合,是最基本,也是最容易理解的通式,所以请把精力花在这上面。
问题1,解答:
=IF(ROW(A1)>COUNTIF($B:$B,\$2:$20)),ROW(A1)))) [公式一]
=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$20=\ [公式二]
=INDEX(A:A,SMALL(IF($B$2:$B$20=\ [公式三]
首先,这三个都是数组公式,什么是“数组公式”呢?数组公式最特殊也最直接的表现,就是你在单元格里输入完公式之后,要按Ctrl+Shift+Enter三键结束,跟“普通公式”只按Enter结束有明显的区别。
为什么有三个公式呢?其实这三个公式的作用和核心是一样的,只是应对不同Excel版本、不同数据类型所用到的屏蔽错误值的手法有所差异而已。
下面我们着重讲讲[公式三],因为这个结构里函数要素最齐全,而组合也是非常符合我们标题所讲到的。
首先我们来简化和分解一下这个公式:
从上图我们不难看出,这个组合就是由INDEX作为主体函数,第一参数就是我们要查询并返回的数据区域,第二参数就是由SMALL构造的一个公式,而SMALL构造的函数,无非就是由IF判断生成的一个内存array。
如果你没有函数基础的话,估计还是理解不了,那我们先返回IF结构的计算结果,也就是判断B2:B20区域,如果等于“Sam”,就返回对应的所在行号,不相等的话,就返回4^8,就是4的8次方幂,即65,536,这在xls格式文档中,相当于最大行号,在xlsx格式则不然。
OK,就我们图中的数据,抹黑SMALL函数的array参数,再按F9,不难返回一个内存数组如下:
{65536;65536;65536;65536;6;65536;65536;65536;65536;11;65536;13;65536;65536;65536;65536;18;65536;65536}
简化一下,我们用“极大”来表示65536,那结果就是:
{ 极大;极大;极大;极大; 6 ;极大;极大;极大;极大; 11 ;极大; 13 ;极大;极大;极大;极大; 18 ;极大;极大 }
这个内存数组也就是这个公式组合里最关键的,你可以看到SMALL函数的第二参数是ROW(A1),这个是返回A1单元格所在的行号,也就是1,当我们整个公式下拉填充之后,就可以得到ROW(A2)、ROW(A3)、ROW(A4)这样的变化,也就是1、2、3、4 …… 这样的自然数序列,从而可以把上面简化了的内存数组里的6、11、13、18给提取出来,因为6是最小值、11是倒数第二小、13是倒数第三小、18是倒数第四小的值,如果还不明白,那请在单元格里输入“=SMALL(”然后按F1查阅SMALL函数的语法和功能说明。
6、11、13、18代表什么,我们提取出来有什么用呢?回过头去看看IF函数就明白了,原来这就是那些满足条件的记录所在的行号,这样一来我们就可以把一对多的所有符合条件的记录全都提取出来了。
现在回过头来,说说这三个公式都有什么差异和优势?
从上面的分解过程我们也可以看到,其实我们只能凭借下拉公式来得到所有满足条件的所有记录,但具体有多少记录我们不清楚,而且不同的条件返回的记录数量也是不确定的,所以这个公式就决定了我们必须要有容错机制,保证公式下拉之后,不因为返回记录数量的不同而显示多余的0值或者错误值,最常见的如#NUM!。 ----------------------------------------------------------
第一个公式比较长,但公式用了一个IF,直接用COUNTIF返回满足条件的记录数量,然后只显示满足条件的记录,公式下拉后其余数量一率用空值表示,而且这里IF函数的False结果可以直接省略以返回FALSE;
第二个公式尤其适用于xlsx格式文档上,直接省略IF的第三参数,因为IFERROR可以涵盖所有错误而不必多费心;
第三个公式只适用在没有特殊格式的数据上,如我们示例数据里的日期、数值,其实都不适合用这个公式,因为我们公式有一个4^8的极大值,而且INDEX函数最后面接了一个&\,其根本目的是为了避免返回65536行里空值通过公式得到0,但这个的间接作用就是将数据直接转化为文本,所以当你要返回的数据里有数值或者日期值,或者其他自定义格式时,就都会被打回原形。。。。
到此为止,你应该基本上能自己应用了吧?如果还不行,那请重读一遍,熟能生巧嘛~~ 下面讲讲第二、第三个问题的公式写法,其实会了第一个,第二个依瓢画葫芦是不成问题的,巧妙的是第三个问题,由于我们本身就是在SMALL的第一参数返回一个内存数组,所以第三个问题才突显这个组合的优势。
这里就只讲公式写法而不展开讨论,公式很容易看明白的,只是内在的机理可能需要先去接触学习一下数组公式的基础内容,才容易深化。
问题2,解答:
=IFERROR(INDEX(A:A,SMALL(IF($C$2:$C$20%<50,ROW($2:$20)),ROW(A1))),\
=IF(ROW(A1)>COUNTIF($C:$C,\20<5000,ROW($2:$20)),ROW(A1))))
问题3,解答:
=IFERROR(INDEX(A:A,SMALL(IF(MONTH($D$2:$D$20)=3,ROW($2:$20)),ROW(A1))),\
=IF(ROW(A1)>SUMPRODUCT(N(MONTH($D$2:$D$20)=3)),\(IF(MONTH($D$2:$D$20)=3,ROW($2:$20)),ROW(A1))))
然后,而且必须是数组公式,Ctrl+Shift+Enter三键结束,自己书写公式的时候注意绝对引用与相对引用的适当使用,这又属于基本功咯,请加油。
另外这种组合里你可能看到INDEX+SMALL+IF+ROW+COUNTIF,COUNTIF就是用在SMALL函数的第二个参数,这个主要是根据列出的数据的个数,提取第几个的值,对于杂
序无指条件的重复值提取,就正好派上用场,具体可以根据自己的使用情况和需求,消化吸收为自己的知识。
正在阅读:
INDEX+SMALL+IF+ROW03-18
最新部编版三年级上册语文书写字表12-06
基于Opengl的屏幕对象拾取 毕业论文04-26
客户投诉处理作业规程04-06
五年级传统文化教案04-25
软件项目管理案例教程习题答案04-20
北京市四中初二上期中试题及答案10-02
进出口货物征免税申请表 填法05-29
- 1JBPM 6 开发者指南 Index
- 2The Pittsburgh sleep quality index as a screening tool for s
- 3Module1Unit1 We lived in a small house
- 4Galaxy Size Problem at z=3 Simulated Galaxies Are Too Small
- 5Green&39;s and spectral functions of the small Frolich polaron
- 6最新工会会计科目表 - Index of
- 7dedecms5.7 网站首页自动添加index.html除掉方法
- 8Joint generalization of city points and road network for small-scale mapping
- 9Joint generalization of city points and road network for small-scale mapping
- 10Fluxes of atmospheric muons underwater depending on the small-x gluon density
- Win7 安装MySql图示
- 计算器课程设计报告
- 部编版八年下语文第三单元第六单元古诗文理解默写练习及答案
- 13质量通病防治方案和施工措施
- 土力学试题~~~~
- 公务员打印资料
- 传热膜系数测定实验报告 - 图文
- 新时期煤矿协管安全工作的创新与实践
- 第五章 习题及参考答案
- 220kV架空线路强条执行记录表
- 音乐欣赏读后感
- 高炉
- 劳动教育需要新的时代内涵
- 10建筑地面工程施工质量验收规范GB50209-20021
- 银行会计练习题2答案
- 2013年七年级地理上册知识点复习提纲湘教版
- 人教版三年级语文上册第四单元测试题(A卷)(有答案)
- 营养师第九章练习题
- 湖北省武汉市2018届高三毕业生二月调研 理综化学
- 行业分析2018-2023年中国男性护肤品行业市场发展分析及投资前景
- INDEX
- SMALL
- ROW
- 高中地理 2.4 中国的气象灾害导学案 新人教版选修5
- 初中语文体验式教学
- 九个民间炒股高手的实战经验(收益翻30倍以上)
- 《微机原理及应用》实验指导书2012.3.30
- 古诗三首1
- 制造(修理)计量器具许可证申请书
- 2011年安全生产宣传月知识竞赛复习题2
- 论述经济发展与环境治理的辩证关系
- 宁南220KV变电站土建施工组织设计
- 2018年人教版高一数学必修一各章知识点总结及测试题组全套(含答
- 安防监控中心值守部门管理规定
- 南海区推进村改居工作实施方案
- 20XX年地税局工作总结
- 汽车构造下册习题(含答案)
- 郑州大学大学生创业扶持项目资金管理办法(试行)
- 矿山合作开采合同
- 2014上海奉贤区高三数学(文理合卷)二模试题(附答案)
- 长春税务学院辅导员工作考核办法(施行) - 图文
- 2019年三年级语文上册 23、美丽的小兴安岭课时训练 新人教版
- 楼宇智能控制