Excel按日/周/月/年筛选RecordsTable统计CARD计数与求和异常问题

环境与需求 #

• 工作簿包含Database工作表，其中RecordsTable表格有两列：
  • REG.DATE：以月/日/年格式存储的日期（如8/7/2023）
  • CARD：数值类型的金额数据（如500.00）
• CardRevenue工作表设置两个数据验证下拉框作为筛选条件：
  1. 周期类型：DAY/WEEK/MONTH/YEAR
  2. 年份：2023/2022/2021等
• 筛选规则：

  DAY - 匹配与今日日期日部分一致的记录
  WEEK - 匹配与今日所在周一致的记录
  MONTH - 匹配与今日月份一致的记录
  YEAR - 匹配与今日年份一致的记录

• 第二个年份筛选条件用于校验记录的实际年份

示例数据 #

预期筛选结果 #

DAY和2022 → 计数=0，求和=0
DAY和2023 → 计数=1，求和=300.00
WEEK和2022 → 计数=0，求和=0
WEEK和2023 → 计数=1，求和=300.00（今日为8月28日，是一周的开始）
MONTH和2022 → 计数=0，求和=0
MONTH和2023 → 计数=2，求和=500.00
YEAR和2022 → 计数=0，求和=0
YEAR和2023 → 计数=3，求和=1000.00

当前问题 #

DAY、MONTH、YEAR筛选均正常，但选择WEEK和任意年份时，计数单元格（A7）和求和单元格（G7）均返回#VALUE!错误，提示「公式中使用的值类型错误」。

当前公式 #

计数（A7） #

=IF(OR(H5="Day", H5="Week", H5="Month", H5="Year"),
   IF(H5="Day",
      SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=YEAR(TODAY()))*(MONTH(RecordsTable[REG.DATE])=MONTH(TODAY()))*(DAY(RecordsTable[REG.DATE])=DAY(TODAY()))*(RecordsTable[CARD]>0)),
      IF(H5="Week",
         SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=YEAR(TODAY()))*(WEEKNUM(RecordsTable[REG.DATE])=WEEKNUM(TODAY()))*(RecordsTable[CARD]>0)),
         IF(H5="Month",
            SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=L5)*(MONTH(RecordsTable[REG.DATE])=MONTH(TODAY()))*(RecordsTable[CARD]>0)),
            IF(H5="Year",
               SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=L5)*(RecordsTable[CARD]>0)),
               0
            )
         )
      )
   ),
   SUMPRODUCT((YEAR(RecordsTable[REG.YEAR])=L5)*(RecordsTable[CARD]>0))
)

求和（G7） #

=IF(OR(H5="Day", H5="Week", H5="Month", H5="Year"),
   IF(H5="Day",
      SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=YEAR(TODAY()))*(MONTH(RecordsTable[REG.DATE])=MONTH(TODAY()))*(DAY(RecordsTable[REG.DATE])=DAY(TODAY()))*(RecordsTable[CARD]>0), RecordsTable[CARD]),
      IF(H5="Week",
         SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=YEAR(TODAY()))*(WEEKNUM(RecordsTable[REG.DATE])=WEEKNUM(TODAY()))*(RecordsTable[CARD]>0), RecordsTable[CARD]),
         IF(H5="Month",
            SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=L5)*(MONTH(RecordsTable[REG.DATE])=MONTH(TODAY()))*(RecordsTable[CARD]>0), RecordsTable[CARD]),
            IF(H5="Year",
               SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=L5)*(RecordsTable[CARD]>0), RecordsTable[CARD]),
               0
            )
         )
      )
   ),
   SUMPRODUCT((YEAR(RecordsTable[REG.YEAR])=L5)*(RecordsTable[CARD]>0), RecordsTable[CARD])
)

问题原因 #

1. WEEKNUM参数不统一：默认WEEKNUM以周日为一周起始（参数1），但示例中说明今日为一周开始，若实际为周一起始会导致周数匹配错误；若REG.DATE存在文本型日期，WEEKNUM会直接返回#VALUE!。
2. 未使用年份筛选条件：WEEK分支硬编码YEAR(TODAY())，未使用选中的年份L5，既不符合筛选规则，也可能引发跨年份周数匹配的类型错误。
3. 缺少无效日期处理：若REG.DATE存在无效值，WEEKNUM无法处理，直接传递错误值到SUMPRODUCT。

修复方案 #

1. 确保REG.DATE为标准日期格式 #

选中RecordsTable[REG.DATE]列，设置单元格格式为「短日期」；若存在文本型日期，使用DATEVALUE转换为标准日期值。

2. 修复后的公式 #

计数（A7）

=IF(OR(H5="Day", H5="Week", H5="Month", H5="Year"),
   IF(H5="Day",
      SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=L5)*(MONTH(RecordsTable[REG.DATE])=MONTH(TODAY()))*(DAY(RecordsTable[REG.DATE])=DAY(TODAY()))*(RecordsTable[CARD]>0)),
      IF(H5="Week",
         SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=L5)*(WEEKNUM(RecordsTable[REG.DATE],2)=WEEKNUM(TODAY(),2))*(RecordsTable[CARD]>0)),
         IF(H5="Month",
            SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=L5)*(MONTH(RecordsTable[REG.DATE])=MONTH(TODAY()))*(RecordsTable[CARD]>0)),
            IF(H5="Year",
               SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=L5)*(RecordsTable[CARD]>0)),
               0
            )
         )
      )
   ),
   SUMPRODUCT((YEAR(RecordsTable[REG.YEAR])=L5)*(RecordsTable[CARD]>0))
)

求和（G7）

=IF(OR(H5="Day", H5="Week", H5="Month", H5="Year"),
   IF(H5="Day",
      SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=L5)*(MONTH(RecordsTable[REG.DATE])=MONTH(TODAY()))*(DAY(RecordsTable[REG.DATE])=DAY(TODAY()))*(RecordsTable[CARD]>0), RecordsTable[CARD]),
      IF(H5="Week",
         SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=L5)*(WEEKNUM(RecordsTable[REG.DATE],2)=WEEKNUM(TODAY(),2))*(RecordsTable[CARD]>0), RecordsTable[CARD]),
         IF(H5="Month",
            SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=L5)*(MONTH(RecordsTable[REG.DATE])=MONTH(TODAY()))*(RecordsTable[CARD]>0), RecordsTable[CARD]),
            IF(H5="Year",
               SUMPRODUCT((YEAR(RecordsTable[REG.DATE])=L5)*(RecordsTable[CARD]>0), RecordsTable[CARD]),
               0
            )
         )
      )
   ),
   SUMPRODUCT((YEAR(RecordsTable[REG.YEAR])=L5)*(RecordsTable[CARD]>0), RecordsTable[CARD])
)

关键修改点 #

• WEEK分支替换YEAR(TODAY())为L5，匹配选中的年份筛选条件
• WEEKNUM添加第二参数2（周一为一周起始），可根据需求调整为1（周日起始）
• DAY分支同步修改为使用L5年份，符合筛选规则

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