与 30万 粉丝一起学Excel
VIP学员的问题,要搞一份参会人员座次,老大坐中间,老二靠着坐右边,老三靠着坐左边,依次类推。也就是围绕着领导排开着坐,没序号的地方相当于中间空着的路。
每次排这样一份表,都需要搞很久,因此学员希望能用公式自动生成。
刚开始,卢子看到这么一份表,觉得用公式生成不太现实。不过经过了多次沟通,发现还真有办法一条公式搞定。
准备一份所有单位排名表。
现在就变成根据序号查找单位。
当序号为空时,用VLOOKUP得到错误值#N/A。
=VLOOKUP(A$5,所有单位排名表!$A:$B,2,0)
嵌套IF判断序号是否为空,让空的返回1。这样第一排的单位就安排好了。
=IF(A$5="",1,VLOOKUP(A$5,所有单位排名表!$A:$B,2,0))
第二排可不能直接用上面的公式,要不然得到的单位都一样。每一排是51人,因此在原来的序号基础上加51再查找就行。
=IF(A$5="",2,VLOOKUP(A$5+51,所有单位排名表!$A:$B,2,0))
如果只有三四排的话,可以这样一直改下去。如果会场的人超级多,用这个公式就显得不太智能。更智能的公式需要满足,往下一排,会自动增加51。
ROW(A1)往下拉就是1、2、3,再乘以51就得到51倍数的序列。
=ROW(A1)*51
第一排不需要加,也就是0,第二排开始才需要加51,所以公式得再减去51。
=ROW(A1)*51-51
再将公式进行组合,基本得到需要的结果。只差最后一个小缺陷,单位排满后,有的座位没单位座显示错误值#N/A,需要再处理一下。
=IF(A$5="",ROW(A1),VLOOKUP(A$5+ROW(A1)*51-51,所有单位排名表!$A:$B,2,0))
嵌套IFERROR让错误值显示空白。
=IFERROR(IF(A$5="",ROW(A1),VLOOKUP(A$5+ROW(A1)*51-51,所有单位排名表!$A:$B,2,0)),"")
以后,只要修改所有单位排名表里面的顺序,就可以自动排序。这比在座位表直接修改方便多了。
请把「Excel不加班」推荐给你的朋友
联系客服