与 30万 读者一起学Excel
没错,这就是传说中Excel最厉害的查找套路,现在知道还不晚。每个复杂的套路,都是从基础慢慢推导出来的。
筛选公式,就是通过筛选部门,可以自动查找到所有对应值。
基础版:返回满足条件的最大值。
=MAX(IF(条件区域=条件,返回区域))
进阶版:返回满足条件的最大行号,再返回最大行号的对应值。
=INDEX(返回区域,MAX(IF(条件区域=条件,返回行号)))
这2个套路只能查找到一个值,如果是多个对应值都要查找出来,这时就延伸出来最终套路。
最终版:返回满足条件的所有行号,再依次查找所有行号的对应值,最后错误值的显示空白。
=IFERROR(INDEX(返回区域,SMALL(IF(条件区域=条件,返回行号),ROW(A1))),"")
SMALL函数就是获取第N小的值,这样ROW(A1)下拉就依次获取最小的行号,倒数第2小的行号,依次类推,就返回所有对应值。IFERROR函数是为了做容错处理。
这里用LARGE函数代替SMALL函数也可以。
这样,最终公式就出来了。在F4输入公式,按Ctrl+Shift+Enter三键结束,下拉和右拉。
=IFERROR(INDEX(A:A,SMALL(IF($C$2:$C$7=$F$1,ROW($2:$7)),ROW(A1))),"")
返回区域A:A没有锁定,这样公式右拉的时候就会变成B:B、C:C从而查找到每一列的对应值。
格式略作改变,查找每个部门的所有姓名。
在G13输入公式,按Ctrl+Shift+Enter三键结束,下拉和右拉。
=IFERROR(INDEX($B:$B,SMALL(IF($C$2:$C$7=$F13,ROW($2:$7)),COLUMN(A1))),"")
这里只是查找姓名,所以返回区域需要锁定$B:$B。公式右拉要依次获取对应值,ROW函数是下拉生成序号,而COLUMN函数是右拉生成序号。
这个套路厉害在哪?
只要是你觉得很难搞定的问题,用这个套路基本都搞定。套路虽然固定,用法变化无穷。
再举个案例。
原来提取不重复部门是手工操作的,其实用这个套路也可以搞定。
在F13输入公式,按Ctrl+Shift+Enter三键结束,下拉。
=IFERROR(INDEX(C:C,SMALL(IF(MATCH($C$2:$C$7,$C$2:$C$7,0)=ROW($2:$7)-1,ROW($2:$7)),ROW(A1))),"")
MATCH函数就是查找内容首次出现的位置,首次出现的位置如果等于ROW-1,就证明是不重复值,因此返回本身行号。
以不变应万变,才是最厉害的。
推荐:不可思议,MAX+IF组合居然比VLOOKUP查找更牛逼
以不变应万变,敌变我不变,万变不离其宗。世间万物,道理都是相通的,你觉得呢?
作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)
联系客服