Excel/Access中按规则统计缺勤及关联假期的单元格数量

需求说明 #

• 数据源：含日期列、状态列的Excel表格，状态定义：
  • P：出勤
  • A：缺勤
  • H：假期
• 统计规则：
  • 标记为A的日期直接计入缺勤总数
  • 若H的前后日期状态均为A，则该H日期也计入缺勤总数
• 示例验证：

  日期列：1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10
  状态列：P | P | A | P | A | A | H | H | A | A
  按规则统计结果：总缺勤数为7，对应日期：3,5,6,7,8,9,10

Ms-Access 查询实现步骤 #

1. 导入数据 #

将Excel表格导入Access，假设生成的数据表名为AttendanceRecords，包含字段：DateID（日期序号，对应示例的1-10）、Status（状态值P/A/H）。

2. 构建统计查询 #

方式一：单查询完成统计

直接执行以下SQL查询，即可得到总缺勤数及对应日期列表：

SELECT 
    COUNT(*) AS 总缺勤数,
    STRING_AGG(DateID, ', ') AS 缺勤日期
FROM (
    SELECT 
        ar.DateID,
        -- 判断当前记录是否符合缺勤条件
        IIF(
            ar.Status = 'A', 
            True,
            IIF(
                ar.Status = 'H' 
                -- 获取前一条记录的状态
                AND (SELECT TOP 1 Status FROM AttendanceRecords WHERE DateID < ar.DateID ORDER BY DateID DESC) = 'A'
                -- 获取后一条记录的状态
                AND (SELECT TOP 1 Status FROM AttendanceRecords WHERE DateID > ar.DateID ORDER BY DateID ASC) = 'A',
                True,
                False
            )
        ) AS 符合缺勤条件
    FROM AttendanceRecords AS ar
) AS 缺勤校验表
WHERE 符合缺勤条件 = True;

方式二：分步查询（便于调试）

1. 先创建一个查询缺勤标记查询，标记每条记录是否符合缺勤规则：

SELECT 
    ar.DateID,
    ar.Status,
    IIF(
        ar.Status = 'A', 
        '是',
        IIF(
            ar.Status = 'H' 
            AND (SELECT TOP 1 Status FROM AttendanceRecords WHERE DateID < ar.DateID ORDER BY DateID DESC) = 'A'
            AND (SELECT TOP 1 Status FROM AttendanceRecords WHERE DateID > ar.DateID ORDER BY DateID ASC) = 'A',
            '是',
            '否'
        )
    ) AS 是否缺勤
FROM AttendanceRecords AS ar
ORDER BY ar.DateID;

2. 基于上述查询创建统计查询：

SELECT 
    COUNT(*) AS 总缺勤数,
    STRING_AGG(DateID, ', ') AS 缺勤日期
FROM 缺勤标记查询
WHERE 是否缺勤 = '是';

注意事项 #

• 若使用实际日期而非序号，需将DateID替换为实际日期字段，子查询中排序逻辑保持按日期降序/升序即可
• 若你的Access版本不支持STRING_AGG函数，可使用自定义VBA函数实现日期列表拼接，或手动导出符合条件的日期后整理

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