Excel之动态下拉列表,选一个就少一个,杜绝重复
- 2026-11-07 12:39:24
Excel之动态下拉列表,选一个就少一个,杜绝重复在排班、抽奖、订机票等场景中，经常需要杜绝重复，比如安排了张三2026年11月7日到北京出差，那么2026年11月7日去广州出差的任务就不能安排给张三。张三的名字直接从出差人备选名单中剔除掉，这样才能真正意义上杜绝重复。比如公司举办抽奖活动，每人中奖的次数只有一次，如果有人已经抽中奖，那么他的名字直接从抽奖名单中剔除掉，他就不会再次中奖。今天就以排班为案例，分享这类场景中的表格设计方法，案例中也会穿插其它知识。 
每人一周内只能值一次班，如果安排了令狐冲周一值班，那么周二到周日的值班名单中就不会出现令狐冲的名字。 制作方法分享 主要思路： 首先得有所有值班人员的名单，叫固定名单。还需要一份待值班人员的名单，已经被安排值班了的人员不出现在这个名单中，所以这个名单中的人员、数量是动态变化的，叫动态名单。 如何实现动态变化 第1步：首先用COUNTIF函数在值班表与固定名单之间进行对比，统计值班表中的名字在固定名单中出现的次数。若在值班表中没出现名字，函数结果返回0。若出现在了值班表中，函数结果为非0值，只要函数结果为0的就是待值班人员。 啰嗦一句：这里的COUNTIF函数是以值班表为区域，以固定名单为判断条件进行统计。不要搞反了，不能以固定名单为区域，以值班表为条件。 第2步：用FILTER函数在固定名单中，以COUNTIF函数的返回值0为条件进行筛选，筛选出来的名单就是动态名单； 第3步：以动态名单为数据源制作下拉列表。 
最终公式： K2=FILTER(I2:I16,COUNTIF(D9:D15,I2:I16)=0) 啰嗦一句：发现上图值班表中有两个令狐冲。这是因为，此时值班表中还未设置数据验证下拉列表，没有与动态名单关联，是为验证公式的正确性复制进去的名字。 
其它知识 这个排班表中，还应用了关于日期、时间方面的函数，通过点击微调按钮在改变周数时，下方的日期会自动更新。 
想要深度了解Excel对于日期处理的底层逻辑，请翻阅我之前的文章，一文搞懂Excel处理日期和时间的底层逻辑 这个涉及到一个函数，如何计算某一天所在的那一周是全年的第几周。 
其实，Excel自身提供了两个用于计算某一天所在周是全年第几周的函数。一个是WEEKNUM函数，另一个是ISOWEEKNUM函数。 WEEKNUM函数 国内常用。规则：1月1日所在那一周为全年第1周；只要包含1月1日，就算当年第1周，哪怕只有1天； ISOWEEKNUM函数 国际标准，通常用于企业报表，规则：周一为起点，一年第1周必须包含本年第1个星期四。1月1日、2日、3日有可能算为上一年的最后一周，不会强行算作本年第1周。 比如：2022年的第1个星期四是2022年1月6日，那么2022年1月6日所在这一周是2022年第一周，星期一是2022年1月3日，那么2022年1月2日和3日并不算作2022年的周。 这个案例中并没有用WEEKNUM，用的公式是：
C9=DATE($D$4,1,1)-WEEKDAY(DATE($D$4,1,1),2)+(ROW()-ROW($C$8)+($D$6-1)*7)
公式思路
(1) 先算出当年1月1日是星期几；
公式：=WEEKDAY(DATE($D$4,1,1),2)
(2) 再用当年1月1日的日期减去当年1月1日的星期数再+1，结果就是当年第1周星期一的日期。如2026年1月1日为星期四，2026年1月1日往前推3天（减星期数+1）就是这周星期一的日期；
公式：=DATE($D$4,1,1)-WEEKDAY(DATE($D$4,1,1),2)+1
注意：最终公式里并没有+1，+1合并在第（3）中
(3) 第1周星期二就是第1周星期一加1天，星期三就是星期一加两天，第(2)因为公式需要往下下拉填充，用ROW()函数来实现动态加1、加2、加3、加4、加5、加6，为避免在表头上方插入行后，ROW()函数发生变化，没有直接采用ROW()函数，而是用了公式
ROW()-ROW($C$8），即当前行数-表头行数，这样在表头上方插行也不影响函数结果。
第1周星期一日期为当年1月1日的日期数减去当年1月1日的星期数+1，这里的ROW()-ROW($C$8)就将这1加进去了，星期一为第9行，9-8=1；
(4) 第2周的星期一、星期二、星期三……就是第1周星期一、星期二、星期三……往后推7天，第3周的星期一、星期二、星期三……就是第1周星期一、星期二、星期三……往后推2*7天，第n周的星期一、星期二、星期三……就是第1周星期一、星期二、星期三……往后推（n-1）*7天；
公式：($D$6-1)*7) 其中D6就是第n周的周数。
本文来自网友投稿或网络内容，如有侵犯您的权益请联系我们删除，联系邮箱：wyl860211@qq.com 。
