excel用INDEX函数查询出满足同一条件的所有记录-excel技巧-电脑技巧收藏家
|||||||||||||
当前位置: &&&&正文
excel用INDEX函数查询出满足同一条件的所有记录
使用INDEX配合其他函数查询出满足同一条件的所有记录：
在本例中统计了各个店面的销售情况，现在要达到的统计目的是，将某一个店面的所有记录都依次显示出来。我们可以使用INDEX函数以下方法配合SMALL函数、ROW函数来实现：
1）在工作表中建立查询表（也可以在其他工作表中建立，本例为方便读者查看所以在当前工作表中建立），如下图所示。
2）选中F4：F11单元格区域（根据当前记录的多少来选择，比如当前销售记录非常多，为了一次显示某一店面的所有记录，则需要向下多选取一些单元格），在编辑栏中输入公式：
=IF(ISERROR(SMALL(IF($A$2:$A$11)=$H$1),ROW(2:11),ROW(1:11))),&&,INDEX(A:A,SMALL(IF($A$2:$A$11)=$H$1),ROW(2:11),ROW(1:11))))，如上图所示；
3）同时按&Ctrl+Shift+Enter&组合键，可一次性将A列中所有等于H1单元格中指定的店面的记录都显示出来，如上图所示；
4）选中F4：F11单元格区域，将光标定位到右下角，出现黑色十字形状时按住鼠标左键向右拖动，完成公式的复制（得到H1单元格中指定店面的所有记录），如上图所示。
5）如果再查询其他店面的销售记录，只需要在H1单元格中重新输入店面名称即可（可以通过数据有效性功能设置选择序列），如上图所示。
来源：互联网
免责声明：本站资料来源于互联网，其版权归原作者所有。如内容涉及或侵犯了您的权益,请通知我!
版权所有 Copyright&2009&
建议使用分辨率：及更高kirin 的BLOG
用户名：kirin
文章数：126
评论数：434
访问量：301822
注册日期：
阅读量：5863
阅读量：12276
阅读量：351067
阅读量：1049589
51CTO推荐博文
查询函数一直是Excel中常被用到的一种函数，本篇来介绍一下index与match在实际工作中的应用实例。
先看一下这个Excel工作簿。要求：将&用户分析&工作表中机房名称列中输入函数，向下拖动使其自动选择对应&号段检索&工作表中备注的机房名称。
其中故障号码为&号段检索&表中起始、结束号段中的码号。因此这里需要利用index与match函数来完成检索号段归属机房查询工作。
想到了index与match函数了吧，可以先回顾一下。
------------------------------------------------------------------------------------------
------------------------------------------INDEX----------------------------------------
index函数的意义：返回指定行列交叉处引用的单元格。&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
公式：=index(reference,row_num,column_num,area_num)
reference指的是要检索的范围；
row_num指的是指定返回的行序号，如超出指定检索范围，返回错误值#REF!;
column_num指的是指定返回的列序号，如超出指定检索范围，返回错误值#REF!;
area_num指的是返回该区域中行和列的交叉域。可省略，默认1。如小于1时返回错误值#VALUE!
------------------------------------------------------------------------------------------
------------------------------------------MATCH--------------------------------------
match函数的意义：返回指定方式下查找指定查找值（可以是数字、文本或逻辑值）在查找范围1行或1列的位置。
公式：=match(lookup_value,lookup_array,match_type)
lookup_value指指定查找值；
lookup_array指的是1行或1列的被查找连续单元格区域。
match_type指的是查找方式，1或省略指查找小于或等于lookup_value的最大值，lookup_array必须为升序排列，否则无法得到正确结果。
0指查找等于lookup_value的第一个数值，如果不是第一个数值则返回#N/A
-1指查找大于或等于lookup_value的最小值，lookup_array必须为降序，否则无法得到正确结果。
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
那么在这里是用match函数来定位&用户分析&表中故障号码在&号段检索&起始号段或结束号段的所在行序号。
如下图：=MATCH(用户分析!K2,号段检索!B:B,1)。但是为什么检索出来的行号会是错误值呢？
别忘了，match_type为-1时，lookup_array必须为升序排列，也就是&号段检索&表中起始号段应按升序排序。
OK，这次查询正确，也可以抽一个验证一下无误即可。
最后index函数利用match定位的行序号，来引用&号段检索&表中该行序号所在备注的机房名称。
输入公式如下：INDEX(号段检索!A:G,MATCH(用户分析!K2,号段检索!B:B,1),6)。
最后向下拖动公式，完成机房名称检索。
在实际应用中可能常用的是精确查找，但是如果match函数需要应用-1和1时，特别要注意lookup_array的排序问题。本文出自 “” 博客，请务必保留此出处
了这篇文章
类别：┆阅读(0)┆评论(0)
12:53:43 15:55:51 17:55:38 17:56:05 14:52:57 13:14:38查看: 5003|回复: 14
求index与match函数出现并列值的解决办法
如图，我希望按照成绩中选出最大的前5名，按照顺序应当是张三、李四、王一、陈武、李飞。
我用的是这个函数=INDEX($A$2:$A$11,MATCH(LARGE($B$2:$B$11,1),($B$2:$B$11),0))。但是由于出现2次92分，所以excel返回两个李四，跪求高手教我。
(49 KB, 下载次数: 18)
10:33 上传
=INDEX($A$2:$A$11,MATCH(LARGE($B$2:$B$11+(12-ROW(2:11))*0.1^4,ROW(A1)),($B$2:$B$11+(12-ROW(2:11))*0.1^4),0))
上个附件吧，图片不好操作
做个调整就可以了
=INDEX(A$2:A$11,MATCH(LARGE(B$2:B$11-ROW($2:$11)/100,ROW(A1)),B$2:B$11-ROW($2:$11)/100,))三键下拉
本帖最后由 tgydslr 于
12:19 编辑
=INDEX(A$2:A$11,MATCH(LARGE(B$2:B$11-ROW($2:$11)%,ROW(A1)),B$2:B$11-ROW($2:$11)/100,))
=INDEX(A:A,MOD(LARGE(B$2:B$11*100+ROW($2:$11),ROW(A1)),100))
=INDEX(A:A,MOD(LARGE(B$2:B$11*10000+(9-ROW($2:$11))*100+ROW($2:$11),ROW(A1)),100))
tgydslr 发表于
=INDEX(A$2:A$11,MATCH(LARGE(B$2:B$11-ROW($2:$11)%,ROW(A1)),B$2:B$11-ROW($2:$11)/100,))
=INDEX(A:A,M ...
非常感谢！想请教一个问题：ROW(A1)代表的是什么意思？
tgydslr 发表于
做个调整就可以了
=INDEX(A$2:A$11,MATCH(LARGE(B$2:B$11-ROW($2:$11)/100,ROW(A1)),B$2:B$11-ROW($2:$11) ...
非常感谢！！，请问ROW（A1)代表的是什么意思？ROW($2：$11)代表的是什么意思？
miaosha3yao 发表于
非常感谢！！，请问ROW（A1)代表的是什么意思？ROW($2：$11)代表的是什么意思？
=INDEX(A$2:A$11,MATCH(LARGE(B$2:B$11-ROW($2:$11)%,ROW(A1)),B$2:B$11-ROW($2:$11)/100,))
row(a1)就是第一大啊
LARGE(B$2:B$11-ROW($2:$11)%,ROW(A1))就是算出来B$2:B$11-ROW($2:$11)%的第一大（ROW(A1))值
ROW($2:$11)%就是为了规避B$2:B$11中的重复项目，加个小尾巴，但不影响整体排序
比方说，都是92，一个是92-0.03，一个是92-0.04,这样的话就能区分出大小不会有重复了
tgydslr 发表于
=INDEX(A$2:A$11,MATCH(LARGE(B$2:B$11-ROW($2:$11)%,ROW(A1)),B$2:B$11-ROW($2:$11)/100,))
=INDEX(A:A,M ...
貌似弄懂了！谢谢大神！现在想请教另一个问题：当选出来人名之后，如何在后面一个单元格显示其对应的成绩？
tgydslr 发表于
=INDEX(A$2:A$11,MATCH(LARGE(B$2:B$11-ROW($2:$11)%,ROW(A1)),B$2:B$11-ROW($2:$11)/100,))
row(a1)就是 ...
貌似弄懂了！刚才终于把你的公式给弄出来了，开始下拉没用，发下是没有三键下拉。
现在想请教另一个问题：当选出来人名之后，如何在后面一个单元格显示其对应的成绩？拜托了。
|||Excel精英培训
Powered by查看: 1294|回复: 4
Excel视频教程——使用Excel函数反向查找——INDEX+MATCH
和爱学习的人一起激发动力
每月至少2次直播分享，有回放
全日制：就业率连年100%
非全日制双证：更高职业起点
国际名校港中大＆会计领军上国会
联手打造一流CFO硕士
使用Excel函数反向查找——INDEX+MATCH
该贴已经同步到
本帖被以下淘专辑推荐:
& |主题: 33, 订阅: 8
会计视野论坛帖子版权归原作者所有。对发帖人声明原创的帖子，中国会计视野有使用权和转载权。其他网站在写明来源、作者、会计视野论坛首发网址的情况下可以转载，原创作者保留禁止转载和向其他转载网站索取稿酬的权力。
本帖最后由 yl_li 于
09:55 编辑
learn & use
爱问分享里找不到资料了。
爱问分享里找不到资料了。
这个爱享传不上去，先在线看吧！
站长推荐 /2
本次调查继续设立小礼品抽奖。祝你好运！
企业盈利逻辑变迁：从单链思维到跨界整合；顾客与数据的价值；生态圈的风险（滴滴的麻烦、小米的风险、亚马逊的布局）；突破惯性思维
Powered by