请求实现基于日期自动填充月度合同项目追踪表状态的方案

一、需求概述 #

搭建年度月度合同追踪表，统计各月合同的进行中（In Process）、**已完成（FE/Fully executed/End）**状态数量，生成累计完成进度图表；实现F-M列（对应各月份）基于C列（开始日期）、D列（完成日期）自动填充：

• 未填写完成日期时，从开始月份起持续填充「In Process」
• 填写完成日期后，完成月份显示「FE」，之前的月份显示「In Process」，无关月份留空

（项目表截图展示B-D列的合同名称、起止日期，以及F-M列的月度状态列；月度报表截图展示各月合同状态统计的可视化需求）

二、现有表格结构 #

• B列：合同名称
• C列：开始日期
• D列：完成日期
• F-M列：对应年度内各月份，原手动填写状态值

三、自动化实现方案 #

方案1：纯公式实现（无需脚本） #

针对F列（对应1月）输入以下公式，下拉填充至M列及所有数据行：

=IF(ISBLANK($D2), 
    IF(MONTH($C2)<=MONTH(DATE(2024,COLUMN()-5,1)), "In Process", ""),
    IF(MONTH($D2)=MONTH(DATE(2024,COLUMN()-5,1)), "FE",
        IF(MONTH($C2)<=MONTH(DATE(2024,COLUMN()-5,1)) && MONTH($D2)>MONTH(DATE(2024,COLUMN()-5,1)), "In Process", "")))

公式说明：

• COLUMN()-5：F列是第6列，6-5=1对应1月，以此类推M列对应8月
• 替换2024为目标年度
• 空白单元格表示该合同与当月无关

方案2：Google Script实现（批量/自动更新） #

适合需要批量处理或自动触发更新的场景，代码如下：

function updateContractStatus() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues();
  const targetYear = 2024; // 替换为目标年度

  // 遍历数据行（跳过表头行）
  for (let rowIdx = 1; rowIdx < values.length; rowIdx++) {
    const startDate = values[rowIdx][2]; // C列（索引从0开始）
    const endDate = values[rowIdx][3]; // D列
    if (!startDate) continue; // 无开始日期的行跳过

    const startMonth = startDate.getMonth() + 1;
    // 无完成日期则设为13（超过最大月份，确保后续月份都显示In Process）
    const endMonth = endDate ? endDate.getMonth() + 1 : 13;

    // 更新F-M列（索引5到12）
    for (let colIdx = 5; colIdx <= 12; colIdx++) {
      const currentMonth = colIdx - 4; // F列索引5对应1月
      if (currentMonth >= startMonth && currentMonth < endMonth) {
        values[rowIdx][colIdx] = "In Process";
      } else if (currentMonth === endMonth && endMonth !== 13) {
        values[rowIdx][colIdx] = "FE";
      } else {
        values[rowIdx][colIdx] = "";
      }
    }
  }

  // 将更新后的数据写回表格
  dataRange.setValues(values);
}

使用步骤：

1. 打开Google表格，点击「扩展程序」→「Apps脚本」
2. 粘贴代码，修改targetYear为当前年度
3. 运行updateContractStatus，首次运行需完成授权
4. 可设置触发器（「编辑」→「当前项目的触发器」），实现编辑日期时自动更新状态

四、累计完成图表实现 #

1. 新建统计区域（如O1:P9）：
   • O列：填写月份（1月-8月）
   • P列：累计完成数量，公式如下（下拉填充）：

     =IF(O2=1, COUNTIF($F$2:$F$100, "FE"), P1 + COUNTIF(OFFSET($F$2:$M$100,0,O2-1,ROWS($F$2:$M$100),1), "FE"))
     

2. 选择统计区域，插入「折线图」或「柱状图」，即可展示累计完成进度；可新增列统计当月进行中数量，实现双系列对比图表

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