如何递归查询并汇总制作SCAR突击步枪的基础材料？

需求说明 #

需要查询指定物品（比如SCAR突击步枪）的基础材料——也就是不在上述表格「物品」列里的材料，包括木材、梣树枝、梣木、石头、小型动物皮、动物肌腱、肉、铜矿石、草原亚麻籽，同时算出每种基础材料的总需求量。

现有尝试 #

之前试过arrayformula没搞定，现在有个自定义函数searchmaterial：

=QUERY(Index!$A$2:$K, "SELECT B, C, D, E, F, G, H, I, J, K WHERE LOWER(A) contains '"&LOWER(args1)&"'")

其中args1是目标物品名称，但不知道怎么结合arrayformula和sumproduct实现递归计算基础材料。

两种解决方案 #

方案1：用Google Apps Script写递归函数（推荐，支持多层级） #

1. 打开表格，点击「扩展程序」→「Apps Script」
2. 删除默认代码，粘贴以下内容：

function GET_BASE_MATERIALS(targetItem) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Index");
  const data = sheet.getRange("A2:K").getValues().filter(row => row[0] !== "");
  const itemMap = new Map();
  
  // 把配方表转成物品→原料的映射
  data.forEach(row => {
    const item = row[0];
    const materials = [];
    for (let i = 1; i < row.length; i += 2) {
      const mat = row[i];
      const qty = row[i+1];
      if (mat && qty) materials.push([mat, qty]);
    }
    itemMap.set(item, materials);
  });
  
  const baseMaterials = new Map();
  // 递归处理每个物品，拆解到基础材料
  const processItem = (item, multiplier) => {
    if (!itemMap.has(item)) {
      baseMaterials.set(item, (baseMaterials.get(item) || 0) + multiplier);
      return;
    }
    itemMap.get(item).forEach(([mat, qty]) => {
      processItem(mat, multiplier * qty);
    });
  };
  
  processItem(targetItem, 1);
  // 转成表格格式返回
  return Array.from(baseMaterials.entries()).map(([mat, qty]) => [mat, qty]);
}

3. 保存脚本（随便起个项目名），回到表格
4. 输入公式=GET_BASE_MATERIALS("SCAR突击步枪")，就能得到该物品的所有基础材料及总数量

方案2：纯公式实现（适合层级少的情况） #

如果不想用脚本，可直接在单元格输入以下公式（把A1替换成你要查询的物品名称所在单元格，或直接写物品名）：

=LET(
  target, A1,
  data, Index!$A$2:$K,
  // 获取目标直接原料
  directMats, QUERY(data, "SELECT B,C,D,E,F,G,H,I,J,K WHERE LOWER(A) = '"&LOWER(target)&"'", 0),
  flatDirect, FLATTEN(CHOOSECOLS(directMats, SEQUENCE(5,1,1,2))),
  flatQty, FLATTEN(CHOOSECOLS(directMats, SEQUENCE(5,1,2,2))),
  filteredDirect, FILTER({flatDirect, flatQty}, flatDirect <> ""),
  // 处理下一级原料
  nextLevel, BYROW(filteredDirect, LAMBDA(row, 
    LET(mat, INDEX(row,1), qty, INDEX(row,2),
      IF(ISNA(XMATCH(mat, Index!$A$2:$A)), 
        row,
        LET(
          subMats, QUERY(data, "SELECT B,C,D,E,F,G,H,I,J,K WHERE LOWER(A) = '"&LOWER(mat)&"'", 0),
          flatSub, FLATTEN(CHOOSECOLS(subMats, SEQUENCE(5,1,1,2))),
          flatSubQty, FLATTEN(CHOOSECOLS(subMats, SEQUENCE(5,1,2,2)))*qty,
          filteredSub, FILTER({flatSub, flatSubQty}, flatSub <> ""),
          filteredSub
        )
      )
    )
  )),
  // 汇总所有材料
  allData, TOCOL(nextLevel, 1),
  allMats, CHOOSECOLS(allData, 1),
  allQty, CHOOSECOLS(allData, 2),
  uniqueMats, UNIQUE(allMats),
  summedQty, BYROW(uniqueMats, LAMBDA(m, SUMIF(allMats, m, allQty))),
  // 筛选出基础材料
  FILTER({uniqueMats, summedQty}, ISNA(XMATCH(uniqueMats, Index!$A$2:$A)))
)

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