如何在Excel PivotTable中按月份展示长期租赁合约月度收入？

问题核心 #

你遇到的问题是因为Excel普通透视表只会识别单条记录的日期字段，无法自动拆分跨月/跨年的日期范围到对应月份。要实现按月份统计收入，需要先把每个合约拆分成其覆盖的每个月份的独立记录，或者用DAX计算每个月份应分摊的收入。

方法一：纯Excel公式生成月度拆分表（无需Power Pivot） #

步骤1：生成目标年份的所有月份列表 #

在空白区域（比如F列）生成你需要统计的年份的12个月份，格式为YYYY-MM（比如2023-01、2023-02...），可以用公式快速生成：

=DATE(2023,ROW(A1),1)

下拉到第12行，再把单元格格式设置为YYYY-MM。

步骤2：计算每个合约在对应月份的收入 #

假设源表结构为：A列=合约ID，B列=开始日期，C列=结束日期，D列=总租金。
在G列（对应F列的月份），用公式判断当前月份是否在合约的日期范围内，并计算该月应分摊的租金：

=IF(AND(F$1>=B2,F$1<=EOMONTH(C2,0)),D2/DATEDIF(B2,C2+1,"m"),0)

说明：

• EOMONTH(C2,0)获取合约结束日期的当月最后一天
• DATEDIF(B2,C2+1,"m")计算合约覆盖的总月数（+1是因为DATEDIF为左闭右开逻辑）
• 如果当前月份在合约范围内，就按总月数分摊租金，否则为0

把这个公式横向/纵向填充，覆盖所有合约和所有目标月份。

步骤3：创建透视表 #

选中生成的月度拆分数据（包含合约ID、月份、分摊收入），插入透视表：

• 行字段：合约ID
• 列字段：月份
• 值字段：分摊收入（求和）

这样就能得到按月份展示的收入分布。

方法二：用Power Pivot + DAX（更高效，适合大量合约） #

如果你的Excel版本支持Power Pivot（2013及以后），可以不用生成中间拆分表，直接用DAX计算：

步骤1：导入源表到Power Pivot #

1. 选中源表数据，点击「数据」选项卡→「从表格/范围」，导入到Power Pivot模型。
2. 在Power Pivot中创建日期表：点击「设计」→「日期表」→「新建」，生成包含所有日期的表，再筛选出你需要的年份的月份。

步骤2：编写DAX度量值 #

在Power Pivot中新建度量值，计算每个月份的应计租金：

月度应计租金 = 
CALCULATE(
    SUM('租赁合约'[总租金]) / DATEDIF('租赁合约'[开始日期], '租赁合约'[结束日期]+1, "m"),
    FILTER(
        '租赁合约',
        '租赁合约'[开始日期] <= EOMONTH('日期表'[日期], 0) &&
        '租赁合约'[结束日期] >= STARTOFMONTH('日期表'[日期])
    )
)

步骤3：创建透视表 #

从Power Pivot中插入透视表：

• 行字段：租赁合约[合约ID]
• 列字段：日期表[月份]（按YYYY-MM格式分组）
• 值字段：月度应计租金

为什么你之前的透视表方法失效？ #

普通透视表的日期筛选是基于单条记录的日期值，而你的合约只有开始和结束两个日期，透视表无法识别这两个日期之间的月份关联，只会把合约归到开始或结束的当月，所以结果异常。必须通过拆分记录或DAX计算的方式，让每个月份和对应的合约建立关联。

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