Excel双周期（QY/OY）资金剩余追踪方案技术咨询

一、基础数据整理与自动计算公式配置 #

首先确保源数据包含以下核心字段：周期类型（QY/OY）、周期年份（如2013~2014）、总资金额、月份（格式统一为YYYY-MM）、月度发票金额。

注意：需确保每个周期的月份范围准确匹配：QY Term对应当年11月至次年10月，OY Term对应当年7月至次年6月。

1. 剩余资金自动计算 #

假设数据结构如下：

• A列：周期类型（QY/OY）
• B列：周期年份
• C列：总资金
• D列：月份（如2013-07）
• E列：月度发票金额
• F列：剩余资金（待计算）

在F2单元格输入以下公式，下拉填充至所有行：

=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$D$2:D2,"<="&D2)=1, C2-E2, F1-E2)

公式逻辑：

• 通过COUNTIFS判断当前行是否为对应周期的首个月份，若是则用总资金直接扣除当月发票；
• 非首个月份则以上月剩余资金扣除当月发票，实现逐月累计扣减。

二、数据透视表（Pivot Table）创建 #

1. 搭建步骤 #

• 选中全部源数据区域，点击「插入」→「数据透视表」，指定透视表放置位置；
• 行标签：依次添加「月份」「周期类型」（此顺序可让你直接对比同一月份下两个周期的差异）；
• 值区域：添加「剩余资金」（设置为「求和」或「最大值」，因单周期单月仅一条剩余数据）、「月度发票金额」（设置为「求和」）；
• 样式调整：在「数据透视表工具-设计」选项卡选择目标样式，也可对「月份」标签按年份分组，提升数据可读性。

2. 指定时间节点追踪 #

透视表完成后，直接在「月份」筛选器中选择目标月份（如2013年4月、2014年3月），即可快速查看对应月份两个周期的资金使用及剩余差异。

三、剩余资金预警设置 #

条件格式直接显示提示语 #

• 选中剩余资金列（如F列），点击「开始」→「条件格式」→「新建规则」；
• 选择「仅对包含以下内容的单元格设置格式」，条件设为「单元格值」「小于」5000；
• 点击「格式」→「数字」→「自定义」，在类型框中输入：

$#,##0.00;"Please request more funding. Thank you!"

• 确认设置后，当剩余资金低于$5000时，单元格自动显示指定提示语；金额达标时正常显示数值。

四、5年双周期数据适配 #

因项目覆盖5年、每年2个周期，只要源数据完整录入每个周期的总资金及对应月份的发票金额，上述公式与透视表会自动适配所有周期，无需额外调整。

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