Excel公式实现月份切换时同项目统计序号重置为1

需求说明 #

• 目标效果：AB列序号需满足两个规则：同个Y列维度值在同一月份内重复出现时序号保持一致；每进入新的自然月，序号自动重置为1重新开始连续编号
• 现有方案问题：当前AA列使用的全局序号公式仅支持全表维度的连续编号，无法实现跨月自动重置逻辑，现有公式如下：

=IF(COUNTIF(Y$2:Y4,Y4)=1,MAX(AA$1:AA3)+1,VLOOKUP(Y4,Y$1:AA3,3,0))

实现方案 #

前置说明 #

公式默认你的表格存在对应行的业务日期列（示例中假设日期存储在X列，使用前请替换为你表格中实际的日期列列标），数据从第2行开始（第1行为表头），从AB2单元格输入对应公式后下拉填充即可。

全Excel版本兼容公式 #

=IF(COUNTIFS(Y$2:Y2,Y2,X$2:X2,">="&EOMONTH(X2,-1)+1,X$2:X2,"<="&EOMONTH(X2,0))=1,IF(MONTH(X2)&YEAR(X2)<>MONTH(X1)&YEAR(X1),1,MAX(AB$1:AB1)+1),VLOOKUP(Y2&TEXT(X2,"yyyymm"),CHOOSE({1,2},Y$1:Y1&TEXT(X$1:X1,"yyyymm"),AB$1:AB1),2,0))

提示：Excel 2019及更早版本输入上述兼容公式后，请按Ctrl+Shift+Enter三键确认数组公式，再下拉填充。

公式逻辑拆解：

• 用COUNTIFS同时匹配「当前Y列值+当前记录所属自然月」两个条件，统计到当前行为止同维度值同月份的记录数，等于1时说明是该维度值在当月第一次出现，需要分配新序号
• 新序号分配时判断当前行的年月和上一行年月是否一致，不一致（即进入新月/新年）直接返回1作为重置后的起始序号，一致则取上方已有序号的最大值+1生成连续号
• 如果不是该维度值当月第一次出现，用「Y列值+年月」作为唯一匹配键，查找上方区域已分配的对应序号，保证同维度同月份序号统一，不会出现跨年同月串号问题

Excel 365/2021及以上版本简化公式 #

如果使用支持动态数组的Excel版本，可以用更简洁的写法，输入后自动溢出填充整列，无需手动下拉：

=MAP(Y2:Y1000,X2:X1000,LAMBDA(y,x,
    LET(
        m,TEXT(x,"yyyymm"),
        cnt,COUNTIFS(Y$2:y,y,X$2:x,">="&EOMONTH(x,-1)+1,X$2:x,"<="&EOMONTH(x,0)),
        IF(cnt=1,IF(m<>@TEXT(X1:X1,"yyyymm"),1,MAX(AB:AB)+1),XLOOKUP(y&m,Y$1:y&TEXT(X$1:x,"yyyymm"),AB$1:AB))
    )
))

注意：公式里的Y1000/X1000请替换为你表格实际的最后一行数据行号。

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