|
与 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） |
|
|
