如何将Excel勾选按钮与按可用日期筛选的人员切换列表关联

前置准备 #

先完成勾选框值的绑定：

• 选中每个成员对应星期的勾选框，将勾选返回值绑定到同行列的隐藏单元格（勾选时单元格返回TRUE，未勾选返回FALSE）
• 假设成员姓名存储在Sheet2!A2:A13区域，周一到周日的可用状态分别存储在Sheet2!B2:B13到Sheet2!H2:H13区域，选择日期的单元格为日程表!A1

具体实现步骤 #

方案1：Excel 365/2021及以上版本（最简单） #

直接用动态数组函数生成可用成员列表：

• 在要展示可用成员的单元格输入公式：
  =FILTER(Sheet2!A2:A13,INDEX(Sheet2!B2:H13,0,WEEKDAY(日程表!A1,2))=TRUE,"当日无可用成员")
  公式说明：
  • WEEKDAY(日程表!A1,2)会将你选择的日期转换为1-7的数字，对应周一到周日
  • INDEX函数会自动匹配对应星期的可用状态列
  • FILTER直接筛选出状态为TRUE的成员姓名，自动溢出填充所有可用人员
• 如果需要做成下拉选择列表，先定义名称：
  1. 公式选项卡→定义名称，名称填可用成员，引用位置填上述FILTER公式
  2. 选中要做下拉的单元格，数据验证→允许选「序列」，来源填=可用成员即可

方案2：Excel 2019及更低版本（无动态数组函数） #

用数组公式实现：

• 在要展示可用成员的第一个单元格输入公式：
  =IFERROR(INDEX(Sheet2!A$2:A$13,SMALL(IF(INDEX(Sheet2!B$2:H$13,0,WEEKDAY(日程表!A$1,2))=TRUE,ROW($1:$12),999),ROW(A1))),"")
• 按Ctrl+Shift+Enter触发数组公式计算，下拉填充12行（和成员总数一致）即可
• 要做下拉列表的话，同样先定义名称引用上述公式生成的列表区域即可

注意：如果使用的是ActiveX控件格式的勾选框，只需要在控件属性的LinkedCell字段绑定对应单元格，返回值规则和窗体控件一致，不影响后续公式使用

内容的提问来源于stack exchange，提问作者sakaihoikuen