Microsoft Query汇总Excel工时表时UNION ALL查询过复杂问题求助

方案1：使用Excel Power Query（推荐） #

Power Query是Excel内置的专业数据处理工具，可直接规避ODBC查询的复杂度限制，步骤如下：

1. 打开工时表，选中包含表头的数据区域，点击数据选项卡 → 从表格/区域（旧版Excel找获取外部数据下的对应选项）。
2. 在Power Query编辑器中，选中Employee_ID列，按住Ctrl选中所有日期列（1-Jan、2-Jan...），右键点击 → 逆透视其他列，将宽表转为三列结构：Employee_ID、属性（原日期列名）、值（状态值）。
3. 重命名列：将属性改为Trn_Date，值改为Status。
4. 提取月份：点击添加列 → 自定义列，输入公式=Text.End([Trn_Date], 3)提取月份后缀（如Jan、Feb），命名为Month。
5. 分组统计：点击转换 → 分组依据，设置分组列为Month和Status，新列名设为Count，操作选择行计数。
6. 点击关闭并上载，将统计结果导入Excel，后续可通过筛选月份查看对应数据。

方案2：优化SQL查询结构，减少UNION ALL层级 #

Excel ODBC驱动对UNION ALL的数量有限制，可将每日查询按月份分组合并，把UNION ALL总数量从365降至11：

SELECT   
  Timesheet.Status,
  COUNT(Timesheet.Status) AS Count
FROM 
  (
    -- 1月数据块
    SELECT Emp_No, '1-Jan' AS Trn_Date, `1-Jan` AS Status FROM `Working$`
    UNION ALL SELECT Emp_No, '2-Jan' AS Trn_Date, `2-Jan` AS Status FROM `Working$`
    UNION ALL SELECT Emp_No, '3-Jan' AS Trn_Date, `3-Jan` AS Status FROM `Working$`
    -- ... 补充1月剩余日期的SELECT语句
    UNION ALL
    -- 2月数据块
    SELECT Emp_No, '1-Feb' AS Trn_Date, `1-Feb` AS Status FROM `Working$`
    UNION ALL SELECT Emp_No, '2-Feb' AS Trn_Date, `2-Feb` AS Status FROM `Working$`
    -- ... 补充2月剩余日期的SELECT语句
    UNION ALL
    -- 依次添加3-12月的日期查询块
  ) AS Timesheet
WHERE RIGHT(Timesheet.Trn_Date,3) = ?
GROUP BY Timesheet.Status

该结构将每个月的日期查询合并为一个子块，再用UNION ALL合并12个子块，总UNION ALL数量远低于驱动限制。

方案3：预先在Excel中转置宽表为窄表 #

先将原宽表转换为员工-日期-状态的窄表，再用Microsoft Query查询新表：

• 手动方式：在新工作表中生成员工ID序列、日期序列，用VLOOKUP或INDEX/MATCH公式从原表提取对应状态。
• VBA方式：编写宏遍历原表的员工和日期列，自动拆分生成365行/员工的窄表。

转置完成后，查询语句会大幅简化：

SELECT Status, COUNT(Status) AS Count
FROM `Transposed_Timesheet$`
WHERE RIGHT(Trn_Date,3) = ?
GROUP BY Status

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