如何用Excel Power Pivot制作带日期标记的休假统计透视表？

核心思路：先将休假日期范围拆分为单日记录，再用数据透视表按「月份+人员」行、「1-31日」列进行统计标记。

1. 拆分休假范围为单日记录 #

这是解决问题的关键——原表的日期范围无法直接对应到每日，必须拆成每条记录对应一个休假单日。

方法1（Excel 365/2021 动态数组） #

在新工作表的A1单元格输入以下公式，自动生成拆分后的单日数据（替换公式中原表!A2:A100等范围为你的实际数据区域）：

=LET(
    姓名列, 原表!A2:A100,
    起始日列, 原表!B2:B100,
    天数列, 原表!C2:C100,
    生成单日数据, MAP(姓名列,起始日列,天数列, LAMBDA(x,y,z, IF(z>0, CHOOSE({1,2}, REPT(x,z), SEQUENCE(z,,y)), ""))),
    展开数据, VSTACK(生成单日数据),
    FILTER(展开数据, INDEX(展开数据,,1)<>"")
)

公式会自动生成两列：姓名、休假单日，同时过滤掉天数为0的无效记录。

方法2（旧版Excel 无动态数组） #

1. 计算总需生成的记录数：在空白单元格输入=SUM(原表!C:C)，得到所有休假天数的总和。
2. 生成重复的姓名列：在新表A2单元格输入=INDEX(原表!$A:$A,ROUNDUP(ROW(A1)/MAX(原表!$C:$C),0))，下拉填充到总记录数行。
3. 生成对应单日：在新表B2单元格输入=INDEX(原表!$B:$B,ROUNDUP(ROW(A1)/MAX(原表!$C:$C),0))+MOD(ROW(A1)-1,INDEX(原表!$C:$C,ROUNDUP(ROW(A1)/MAX(原表!$C:$C),0)))，下拉填充。

2. 新增辅助列 #

在拆分后的单日数据表中添加3个辅助列：

• 月份：=TEXT(B2,"yyyy年mm月")（按需调整格式，比如只保留"mm月"）
• 日期日数：=DAY(B2)（提取1-31的数字，用于透视列维度）
• 标记：输入"√"或1（用于透视表中标记休假日期）

3. 创建目标数据透视表 #

1. 选中拆分后的完整单日数据区域（含辅助列），点击「插入」→「数据透视表」，选择放置位置。
2. 配置透视表字段：
   • 行区域：依次拖入「月份」、「姓名」（确保先月份后姓名，形成层级）
   • 列区域：拖入「日期日数」（默认按升序排列，若顺序混乱可右键列标签→「排序」→「升序」）
   • 值区域：拖入「标记」，点击值字段设置→选择「计数」或「最大值」（确保有休假的单元格显示标记，无休假的显示空白）
3. 优化格式：
   • 右键值区域→「值字段设置」→「数字格式」，设置空值显示为空白。
   • 可选：给有标记的单元格添加底色突出显示（选中值区域→条件格式→基于值设置规则）

4. 利用已有辅助表补全列（可选） #

如果你的1-31日辅助表要强制显示所有日期（比如部分月份无31号，透视列默认不显示）：

1. 右键透视表的列标签→「字段设置」→「布局和打印」→勾选「显示无数据的项」。
2. 若仍缺列，可将列区域的「日期日数」替换为你辅助表的1-31日列，重复上述步骤。

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