注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Oh! I see you!

Hi! ppmm~~

 
 
 

日志

 
 

Excel自定义列筛选  

2014-04-25 11:42:51|  分类: Excel |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

本文链接:http://oicu.cc.blog.163.com/blog/static/1230394712014325113133524/

老习惯,先描述问题,是这样的:

发信人: sKor (eFFiciENcy), 信区: OfficeSoft
标  题: [Excel]这个怎么做
发信站: 水木社区 (Tue Apr 22 17:50:29 2014), 站内

表格如下:

文字 A B C
xxx  1 0 1
yyy  0 1 1
zzz  1 1 0

我想某个单元格有一个下拉列表,列表的选项是A,B,C,如果选中A,
那么只显示A那列值为1的那些行。

简单解释一下需求,就是想得到这样的一个筛选结果:

如果B1下拉选择了A,则

文字 A
xxx  1
zzz  1

如果B1下拉选择了C,则

文字 C
xxx  1
yyy  1

 

好了,下面开始动手了,2个公式轻松搞掂,不解释过程了。
方法很多,比如offset或者match都可以,我这里都使用,算是举例。

在新的sheet2里,A1内容写“文字”,C1内容写筛选值“1”,
B1使用数据验证,序列设置为
=Sheet1!$B$1:$D$1

假定原来sheet1的数据只有100行,根据情况修改公式里的$100范围。

Sheet2!A2输入数组公式

{=IFERROR(INDEX(Sheet1!A:A,SMALL(IF(OFFSET(INDIRECT("Sheet1!$"&$B$1&"$1:$"&$B$1&"$100"),,1)=$C$1,ROW($1:$100),4^8),ROW(1:1)))&"","")}

为了兼顾Excel 2003,可以使用这个:
{=IF(ROW(1:1)>COUNTA(Sheet1!A:A),"",INDEX(Sheet1!A:A,SMALL(IF(OFFSET(INDIRECT("Sheet1!$"&$B$1&"$1:$"&$B$1&"$100"),,1)=$C$1,ROW($1:$100),4^8),ROW(1:1))))&""}

因为列名不一定就是A、B、C,准确的应该用下面的:

=IFERROR(INDEX(Sheet1!A:A,SMALL(IF(INDIRECT("Sheet1!"&ADDRESS(1,MATCH($B$1,Sheet1!$B$1:$D$1,0)+1)&":"&ADDRESS(100,MATCH($B$1,Sheet1!$B$1:$D$1,0)+1))=$C$1,ROW($1:$100),4^8),ROW(1:1)))&"","")

Sheet2!B2输入公式:
=IFERROR(VLOOKUP(A2,Sheet1!A:D,MATCH($B$1,Sheet1!$B$1:$D$1,0)+1,FALSE),"")

选择Sheet2!A2:B2往下填充。完工。

2个公式里都含有相同的match,为了显示简洁,可以用一个辅助单元格使用match。

  评论这张
 
阅读(7625)| 评论(1)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017