如何用公式或脚本自动计算覆盖指定长度所需的预设材料数量

先统一单位 #

首先把固定规格材料转成英寸：

• 10'6" = 126英寸
• 12'6" = 150英寸
• 14'6" = 174英寸

待覆盖长度（如68 1/2"）需先转成纯数值，用以下公式（假设待覆盖长度在A2单元格）：

=IF(ISNUMBER(SEARCH(" ",A2)), 
    VALUE(LEFT(A2,SEARCH(" ",A2)-1)) + VALUE(MID(A2,SEARCH(" ",A2)+1,FIND("/",A2)-SEARCH(" ",A2)-1))/VALUE(MID(A2,FIND("/",A2)+1,LEN(A2)-FIND("/",A2)-1)), 
    VALUE(LEFT(A2,LEN(A2)-1))
)

该公式会把68 1/2"转成68.5，70"转成70。

方法1：用Solver插件（无需写代码） #

这是适合非编程用户的方案，利用Google Sheets的Solver扩展做整数规划：

1. 启用Solver：点击「扩展」→「加载项」→「获取加载项」，搜索「Solver」并安装。
2. 设变量：在B2、C2、D2单元格分别对应10'6"、12'6"、14'6"的使用数量。
3. 设约束：添加约束条件B2*126 + C2*150 + D2*174 >= [转成数值的待覆盖长度单元格]，且B2、C2、D2为非负整数。
4. 设目标：将目标设为最小化B2+C2+D2（即总材料数最少）。
5. 运行Solver：点击「扩展」→「Solver」→「Solve」，即可得到最优材料组合。

方法2：自定义Apps Script函数（自动批量计算） #

如果需要批量自动计算，用自定义脚本更高效，步骤如下：

1. 打开Google Sheets，点击「扩展」→「Apps Script」，打开脚本编辑器。
2. 粘贴以下代码，保存项目（命名任意，比如MaterialCalculator）：

function CALCMATERIALS(targetInches) {
  // 三种材料的英寸长度
  const sizes = [126, 150, 174];
  let minTotal = Infinity;
  let best = [0, 0, 0];

  // 遍历所有可能的组合，找总数量最少的方案
  const max1 = Math.floor(targetInches / sizes[0]) + 2;
  for (let a = 0; a <= max1; a++) {
    const rem1 = targetInches - a * sizes[0];
    if (rem1 <= 0) {
      if (a < minTotal) {
        minTotal = a;
        best = [a, 0, 0];
      }
      continue;
    }
    const max2 = Math.floor(rem1 / sizes[1]) + 2;
    for (let b = 0; b <= max2; b++) {
      const rem2 = rem1 - b * sizes[1];
      if (rem2 <= 0) {
        const total = a + b;
        if (total < minTotal) {
          minTotal = total;
          best = [a, b, 0];
        }
        continue;
      }
      const c = Math.ceil(rem2 / sizes[2]);
      const total = a + b + c;
      if (total < minTotal) {
        minTotal = total;
        best = [a, b, c];
      }
    }
  }

  return `10'6": ${best[0]}, 12'6": ${best[1]}, 14'6": ${best[2]}`;
}

3. 返回表格，在任意单元格输入=CALCMATERIALS(68.5)（或引用转成数值的单元格，比如=CALCMATERIALS(B2)），即可自动得到最优材料组合。

注意事项 #

• 两种方案默认以「总材料数量最少」为目标，若需优先使用某类材料（比如优先用长规格减少拼接），可调整Solver约束或脚本逻辑。
• 脚本法首次运行需授权，按照提示完成权限验证即可。

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