Google Sheet 求满足条件的Top5：B列总和≤100且C列总和最大

你遇到的是固定数量的0-1背包问题，LARGE函数仅针对单列排序取值，无法处理多约束的组合优化场景，因此用它解决不了这类问题。下面提供两种可行方案：

方法一：用内置规划求解（Solver）工具 #

这是适合普通用户的可视化操作方案，步骤如下：

1. 在表格空白区域新增辅助列（比如D列），输入1或0，1代表选中该行数据，0代表不选。
2. 计算选中数据的B列总和：在空白单元格输入公式 =SUMPRODUCT(D:D, B:B)，可将该单元格命名为Total_B方便后续操作。
3. 计算选中数据的C列总和：在空白单元格输入公式 =SUMPRODUCT(D:D, C:C)，命名为Total_C。
4. 计算选中数据的数量：在空白单元格输入公式 =SUM(D:D)，命名为Count_Selected。
5. 打开「扩展程序」→「规划求解」（未安装的话先在扩展程序商店添加）：
   • 目标单元格选择Total_C，目标设置为最大化。
   • 变量单元格选择辅助列D的所有数据行。
   • 添加3项约束条件：
     • Total_B <= 100
     • Count_Selected = 5
     • 变量单元格的取值约束为二进制（仅能是0或1）
6. 点击「求解」，计算完成后，D列显示1的行即为选中的最优数据，直接提取对应Name列内容即可。

方法二：用Apps Script编写自定义函数 #

如果数据量较大（如数百行），规划求解效率偏低，可通过脚本实现组合筛选，示例代码如下：

1. 打开Google Sheets的「扩展程序」→「Apps脚本」。
2. 删除默认代码，粘贴以下脚本：

function findTop5Optimal() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const headerRow = 1; // 假设表头在第1行
  const nameCol = 0; // Name列对应第1列（索引从0开始）
  const colB = 1; // Column B对应第2列
  const colC = 2; // Column C对应第3列
  
  // 过滤掉表头，提取有效数据行
  const rows = data.slice(headerRow);
  const validCombos = [];

  // 生成所有5条数据的组合（提前过滤B列值过大的行可减少计算量）
  function generateCombos(start, current) {
    if (current.length === 5) {
      const sumB = current.reduce((total, idx) => total + rows[idx][colB], 0);
      const sumC = current.reduce((total, idx) => total + rows[idx][colC], 0);
      if (sumB <= 100) validCombos.push({ sumC, sumB, indices: current });
      return;
    }
    for (let i = start; i < rows.length; i++) {
      generateCombos(i + 1, [...current, i]);
    }
  }

  generateCombos(0, []);

  if (validCombos.length === 0) return "无符合条件的组合";
  // 找到C列总和最大的组合
  const best = validCombos.sort((a, b) => b.sumC - a.sumC)[0];
  // 提取对应的Name及统计信息
  const result = best.indices.map(idx => rows[idx][nameCol]);
  result.push(`B列总和: ${best.sumB}`);
  result.push(`C列总和: ${best.sumC}`);
  
  return result;
}

3. 保存脚本后回到表格，在空白单元格输入=findTop5Optimal()，按回车即可得到最优的5个Name及对应总和。

注意：若数据行数超过20，生成所有5条数据组合的计算量会剧增（比如25行就有53130种组合），建议先过滤掉B列值大于20的行（5*20=100，超过该值的行无法和其他4条组成符合约束的组合），降低计算压力。

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