如何在Excel 365中基于Timesquare排班数据按半小时统计出勤人员？

一、用Power Query合并员工单日多班次数据 #

核心是按员工+日期分组，提取当日首次签到和末次签退时间，同时保留远程办公标记：

1. 提取日期列：给清理后的StartTime列添加自定义列，公式为Date.From([StartTime])，命名为WorkDate，用来作为分组的日期依据。
2. 分组聚合：点击「开始」选项卡的「分组依据」，设置：
   • 分组列：选择员工唯一标识（比如姓名+部门，避免同名混淆）+ WorkDate
   • 聚合列1：列名FirstCheckIn，操作选「最小值」，值选StartTime（取当日最早签到时间）
   • 聚合列2：列名LastCheckOut，操作选「最大值」，值选EndTime（取当日最晚签退时间）
   • 聚合列3：列名IsRemote，操作选「自定义」，公式List.Contains([RemoteWork], TRUE)（只要当日有远程班次，标记为远程）
3. 确认分组后，就能得到单日单条的员工出勤记录，解决午休拆分班次的问题。

二、按半小时间隔统计工位需求 #

基于合并后的单日数据，生成半小时间序列并统计出勤人数：

1. 生成半小时间序列表：
   在Power Query中新建空白查询，输入以下M代码生成覆盖统计周期的所有半小时间点：

   let
       // 从合并后的表中获取起止日期
       StartDate = List.Min(#"合并后的出勤数据"[WorkDate]),
       EndDate = List.Max(#"合并后的出勤数据"[WorkDate]),
       // 生成日期列表
       DateList = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1,0,0,0)),
       // 生成00:00到23:30的半小时间点列表
       TimeList = List.Times(#time(0,0,0), 48, #duration(0,30,0,0)),
       // 合并日期和时间，生成完整时间序列
       TimeSequence = List.Transform(DateList, (d) => List.Transform(TimeList, (t) => DateTime.From(d) + t)),
       // 转成表格
       TimeTable = Table.FromList(TimeSequence, Splitter.SplitByNothing(), {"TimePoint"})
   in
       TimeTable
   

2. 关联数据并统计：
   • 将时间序列表与合并后的出勤表做交叉合并（无需匹配键，直接全关联）
   • 添加自定义列判断：[TimePoint] >= [FirstCheckIn] and [TimePoint] <= [LastCheckOut] and [IsRemote] = FALSE，命名为IsOnSite
   • 按TimePoint分组，对IsOnSite列做「计数」（只统计值为TRUE的行数），得到每个半时段的在岗（需工位）人数。

三、优化建议 #

• 恢复员工唯一标识：之前删除的ID列建议保留，或用「姓名+部门」作为分组键，避免同名员工数据混淆。
• 处理跨天签退：如果存在员工签退时间跨到次日的情况，需在时间序列中包含次日的时间点，或在分组时单独判断并调整LastCheckOut的日期逻辑。
• 数据校验：抽样对比合并前后的原始数据，确保首次/末次时间提取准确，尤其是午休拆分的多班次场景。

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