表格中根据A2下拉选中月份自动填充日期行的公式求助

假设前提 #

• A2单元格已设置月份下拉菜单（示例以中文月份选项为例，如“一月”至“十二月”）
• 目标是从B2单元格开始，向右填充选中月份的所有日期

方案1：适配Excel 365/2021及以上版本（用SEQUENCE函数） #

在B2单元格输入以下公式，回车后将自动生成当月完整日期序列：

=SEQUENCE(DAY(EOMONTH(DATE(YEAR(TODAY()),MATCH(A2,{"一月","二月","三月","四月","五月","六月","七月","八月","九月","十月","十一月","十二月"},0),1),0)),1,DATE(YEAR(TODAY()),MATCH(A2,{"一月","二月","三月","四月","五月","六月","七月","八月","九月","十月","十一月","十二月"},0),1))

公式拆解 #

• MATCH(A2,{"一月",...,"十二月"},0)：匹配A2选中月份对应的数字（1-12）
• DATE(YEAR(TODAY()),匹配月份,1)：生成选中月份的当月第一天
• EOMONTH(...,0)：获取当月最后一天，DAY()提取该日期的数字得到当月总天数
• SEQUENCE(总天数,1,第一天)：生成从当月第一天到最后一天的连续日期

如果只需显示“日”的数字（如1、2、3...），可套上DAY()函数：

=DAY(SEQUENCE(DAY(EOMONTH(DATE(YEAR(TODAY()),MATCH(A2,{"一月","二月","三月","四月","五月","六月","七月","八月","九月","十月","十一月","十二月"},0),1),0)),1,DATE(YEAR(TODAY()),MATCH(A2,{"一月","二月","三月","四月","五月","六月","七月","八月","九月","十月","十一月","十二月"},0),1)))

方案2：兼容旧版Excel（无SEQUENCE函数） #

在B2单元格输入以下公式，然后向右拖动填充至出现空白（代表当月日期已填充完毕）：

=IF(ISERROR(DATE(YEAR(TODAY()),MATCH(A2,{"一月","二月","三月","四月","五月","六月","七月","八月","九月","十月","十一月","十二月"},0),COLUMN(A:A))),"",DATE(YEAR(TODAY()),MATCH(A2,{"一月","二月","三月","四月","五月","六月","七月","八月","九月","十月","十一月","十二月"},0),COLUMN(A:A)))

公式拆解 #

• COLUMN(A:A)：单元格右移时依次返回1、2、3...，对应日期的“日”数
• DATE(...)：生成对应日期，当超过当月最大天数时返回错误值，用IF(ISERROR(...),"",...)隐藏错误值

自定义调整提示 #

• 若A2下拉选项为英文月份（如"January"），将公式中的中文月份数组替换为{"January","February","March","April","May","June","July","August","September","October","November","December"}即可
• 如需固定年份（而非当前年份），把YEAR(TODAY())替换为具体年份数字，比如2024

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