按Enroll匹配条件将横向休假登记表转置为纵向月历

核心需求 #

将横向结构的休假记录（每行对应一个Enroll的整月每日休假状态），转置为纵向月历格式（每行对应一个Enroll+日期+休假状态的组合），确保Enroll与对应日期的休假数据准确匹配。

方法1：公式法（适合小数据量） #

假设原数据在Sheet1：

• A列：Enroll编号
• B_{AF列：对应当月1}31号的休假状态（例如“年假”“事假”或空值）

在新工作表中按以下步骤操作：

1. 提取不重复Enroll：在新表A2单元格输入=UNIQUE(Sheet1!A:A)，下拉填充得到所有唯一的Enroll列表。
2. 生成对应日期：在新表B2单元格输入=DATE(2024, 8, 1)（替换2024和8为目标年份、月份），然后下拉填充至31行，得到当月1~31号的日期。
3. 匹配休假状态：在新表C2单元格输入公式：

   =XLOOKUP($A2&"|"&DAY(B2), Sheet1!$A:$A&"|"&COLUMN(Sheet1!$B:$AF)-1, Sheet1!$B:$AF, "")
   

   下拉填充所有行，即可匹配到对应Enroll在对应日期的休假状态。

方法2：Power Query法（适合大数据量，批量处理更高效） #

1. 选中原数据区域，点击「数据」选项卡→「从表格/区域」（Excel），或在Google Sheets中点击「数据」→「导入数据」→「从表格」，进入Power Query编辑器。
2. 选中Enroll列，点击「转换」选项卡→「逆透视列」→「逆透视其他列」。此时会生成两列：Attribute（原列名，即日期数字）和Value（休假状态）。
3. 转换为日期格式：点击「添加列」→「自定义列」，输入公式（替换年份和月份为实际值）：

   = Date.FromText("2024-08-" & [Attribute])
   

   重命名该列为「日期」，并删除原Attribute列。
4. 调整列顺序为「Enroll」→「日期」→「休假状态」，点击「关闭并上载」，即可得到自动匹配的纵向月历数据。

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