分行的逆向操作,将标准的数据按地区将人员合并在一个单元格。
关于这种有很多类似的案例,卢子总结在一起说明。
将A列的地区复制粘贴到E列,点数据,删除重复值。
在单元格输入公式,按Ctrl+Shift+Enter三键结束。IF函数部分就是判断A列是否等于E2,如果是就返回对应的B列,否则返回空白。TEXTJOIN函数就是将内容按分隔符号合并在一起。
=TEXTJOIN("、",1,IF($A$2:$A$6=E2,$B$2:$B$6,""))
假如现在不是要合并在一个单元格,而是依次放在多个单元格。
典型的筛选公式,IF部分就是让满足条件的返回本身的行号,再嵌套SMALL从小到大获取行号,INDEX就是获取行号的对应值,IFEEROR就是让错误值显示空白。
=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$6=$E2,ROW($2:$6)),COLUMN(A1))),"")
当然,也可以借助辅助列,将地区转换成唯一值,然后用VLOOKUP查找。
辅助列,区域B$2:B2这样写下拉的时候可以扩大区域,从而起到累计次数的作用。这个用法经常看到学员写错,写成B:B,就变成统计每个地区的总次数,这样一来就失去了辅助列的作用了。
=COUNTIF(B$2:B2,B2)&B2
VLOOKUP的查找值用COLUMN(A1)右拉就获取1、2、3,起到跟COUNTIF累计次数一样的作用。
=IFERROR(VLOOKUP(COLUMN(A1)&$F2,$A:$C,3,0),"")
现在布局进一步改变,两边的地区都出现多次,要依次查找对应的人员。
这种也是借助COUNTIF+VLOOKUP就可以解决。
A列的辅助列,跟原来一样。
=COUNTIF(B$2:B2,B2)&B2
同理,右边的地区也可以用辅助列。
=COUNTIF(F$2:F2,F2)&F2
当然,这个直接作为VLOOKUP的查找值也行。
=IFERROR(VLOOKUP(COUNTIF(F$2:F2,F2)&F2,A:C,3,0),"")
每个人做表的习惯不一样,所以就会出现很多类似的需求,其实用到的知识都大同小异,关键看你能否灵活运用。
提取码:097r
推荐:VLOOKUP函数跟这个巧妙的辅助列,简直就是绝配,狂赞!
关于查找引用,你还有啥问题?
作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)
联系客服