Google Sheets：将姓名/日期/时间列表转置格式化为排班表

1. 数据预处理（清洗原始数据） #

假设原始数据存于Sheet1，列对应关系：

• A: EmployeeName
• B: ShiftDate
• C: Store
• D: ShiftStart
• E: ShiftEnd

拆分合并的员工名 #

如果EmployeeName列存在合并单元格（同一员工仅首行显示），在Sheet2的A2单元格输入公式，自动填充所有行的员工名称：

=ARRAYFORMULA(IF(Sheet1!A2:A="", OFFSET(Sheet1!A2:A, -1, 0), Sheet1!A2:A))

转换日期格式 #

在Sheet2的B2单元格输入公式，将原始日期转为星期, 月 日, 年格式：

=ARRAYFORMULA(TEXT(Sheet1!B2:B, "dddd, mmm dd, yyyy"))

格式化排班时间 #

在Sheet2的C2单元格输入公式，把上下班时间转为12小时制，缺失时显示空（或自定义文本）：

=ARRAYFORMULA(IF(Sheet1!D2:D=""&Sheet1!E2:E="", "", TEXT(Sheet1!D2:D, "h:mm AM/PM") & IF(Sheet1!D2:D<>""&Sheet1!E2:E<>"", " - ", "") & TEXT(Sheet1!E2:E, "h:mm AM/PM")))

若希望缺失时间时显示无排班，替换为：

=ARRAYFORMULA(IF(Sheet1!D2:D=""&Sheet1!E2:E="", "无排班", TEXT(Sheet1!D2:D, "h:mm AM/PM") & " - " & TEXT(Sheet1!E2:E, "h:mm AM/PM")))

2. 搭建排班表框架 #

在Sheet3中构建最终排班表：

• 表头行（第1行）：
  • A1输入Employee Name
  • B1输入公式，提取所有不重复的格式化日期并横向排列：

=TRANSPOSE(UNIQUE(FILTER(Sheet2!B2:B, Sheet2!B2:B<>"")))
```

• 员工列（A列）：
  • A2输入公式，提取所有不重复的员工名：

=UNIQUE(FILTER(Sheet2!A2:A, Sheet2!A2:A<>""))
```

3. 自动填充排班数据 #

在Sheet3的B2单元格输入公式，批量填充所有员工对应日期的排班信息，无排班时显示空：

=ARRAYFORMULA(IFERROR(VLOOKUP($A2:$A&B$1:$Z$1, {Sheet2!A2:A&Sheet2!B2:B, Sheet2!C2:C}, 2, FALSE), ""))

额外优化 #

• 若原始EmployeeName无合并单元格，仅存在重复值，可直接用UNIQUE提取，跳过拆分步骤。
• 需按门店筛选时，在提取员工名或日期的公式中加入FILTER条件，例如：UNIQUE(FILTER(Sheet2!A2:A, Sheet2!C2:C="XX门店"))

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