如何在Google Sheets中选取正数凑出指定目标总和

这个需求本质是背包问题的变种（允许最后一项调整数值），Google Sheets的标准函数（如SUMIF、QUERY）无法处理这种动态选值逻辑，因此需要用Apps Script实现自动化。

核心逻辑 #

1. 计算B列所有数值的总和作为目标值
2. 筛选出B列的正数记录（关联A列记录名）
3. 将正数按从大到小排序，优先选取大值快速接近目标
4. 累加选中数值：
   • 若累加后仍未达到目标，继续选下一个正数
   • 若加上下一个正数会超过目标，则取「目标值-当前累加和」作为该正数的调整后数值，完成匹配

Apps Script 代码实现 #

打开Google Sheets，点击扩展程序 > Apps 脚本，替换默认代码为以下内容：

function matchTargetSum() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  
  // 计算目标值+收集正数记录（跳过表头）
  let targetSum = 0;
  const positiveRecords = [];
  for (let i = 1; i < data.length; i++) {
    const value = data[i][1];
    targetSum += value;
    if (value > 0) {
      positiveRecords.push({name: data[i][0], value: value});
    }
  }
  
  // 正数按降序排序
  positiveRecords.sort((a, b) => b.value - a.value);
  
  // 选取符合条件的记录
  const selected = [];
  let currentSum = 0;
  
  for (let record of positiveRecords) {
    if (currentSum + record.value <= targetSum) {
      selected.push({name: record.name, usedValue: record.value});
      currentSum += record.value;
    } else {
      const needed = targetSum - currentSum;
      if (needed > 0) {
        selected.push({name: record.name, usedValue: needed});
        currentSum = targetSum;
      }
      break;
    }
  }
  
  // 异常判断：正数总和不足以覆盖目标值
  if (currentSum < targetSum) {
    SpreadsheetApp.getUi().alert("正数总和无法达到目标值");
    return;
  }
  
  // 输出结果到表格（D、E列存选中记录，G列存目标值）
  const outputSheet = sheet;
  outputSheet.getRange("D1").setValue("选中记录名");
  outputSheet.getRange("E1").setValue("使用数值");
  outputSheet.getRange("G1").setValue("目标值");
  outputSheet.getRange("G2").setValue(targetSum);
  
  for (let i = 0; i < selected.length; i++) {
    outputSheet.getRange(i + 2, 4).setValue(selected[i].name);
    outputSheet.getRange(i + 2, 5).setValue(selected[i].usedValue);
  }
}

使用步骤 #

1. 确保表格第一行是表头（A列「记录名」，B列「数值」）
2. 保存脚本后，回到表格，点击扩展程序 > Apps 脚本 > matchTargetSum运行
3. 首次运行需完成授权，按提示操作即可
4. 结果会自动输出到D、E列，G列显示计算出的目标值

示例验证 #

假设示例数据：

• 目标值：100-50+80+60-20 = 170
• 正数排序：甲(100)、丙(80)、丁(60)
• 匹配过程：累加甲的100后，离目标差70，因此取丙的70而非原80
• 最终输出：

可选优化 #

• 若需自动更新结果，可添加onEdit()触发器，或给脚本绑定表格按钮
• 修改代码中outputSheet.getRange的参数，可调整结果输出位置
• 若不需要调整最后一项数值，可修改循环逻辑，直接选取多个正数直到总和不低于目标

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