Google Sheet如何自动将Sheet1新增未在Sheet3存在的零件加行并优化库存计算

1. 自动同步Sheet1新增零件编号到Sheet3 #

不需要使用VLOOKUP，直接用Google Sheets原生动态数组函数即可实现自动新增行，无需手动操作：

• 在Sheet3零件编号列的首个数据行（假设A1为表头，A2为首个数据行）输入以下公式：
  =UNIQUE(FILTER('Parts In'!F:F, ISNA(XMATCH('Parts In'!F:F, A:A)), 'Parts In'!F:F<>""))
• 公式说明：自动筛选Sheet1（Parts In）中所有尚未在Sheet3零件编号列存在的非空零件编号，自动去重后溢出填充，每当Google表单提交新数据时，会自动在Sheet3追加新行。
• 其他列自动填充：以零件名称列为例，在B2输入以下公式即可自动匹配Sheet1中的对应信息，随新增零件号自动扩展：
  =BYROW(A2:A, LAMBDA(当前零件号, IF(当前零件号="", "", XLOOKUP(当前零件号, 'Parts In'!F:F, 'Parts In'!对应信息列:对应信息列, ""))))

2. 更高效的库存统计方案 #

你当前使用的SUMIF搭配通配符的方案运算效率较低，尤其是数据量超过1000行后会明显卡顿，优化方案如下：

• 如果你是精确匹配零件编号，直接移除公式中的通配符，同时用动态数组实现整列自动计算，无需每行单独下拉公式，在库存数列的首个数据行输入：
  =BYROW(A2:A, LAMBDA(当前零件号, IF(当前零件号="",, SUMIF('Parts In'!F:F, 当前零件号, 'Parts In'!E:E)-SUMIF('Parts Used'!E:E, 当前零件号, 'Parts Used'!C:C))))
• 如果你确实需要模糊匹配零件编号，将原公式的整列引用改为动态范围，避免扫描无意义的空单元格，可提升30%以上运算效率，示例：
  =SUMIF('Parts In'!F2:INDEX('Parts In'!F:F, COUNTA('Parts In'!F:F)), "*"&A3&"*", 'Parts In'!E2:INDEX('Parts In'!E:E, COUNTA('Parts In'!E:E)))-SUMIF('Parts Used'!E2:INDEX('Parts Used'!E:E, COUNTA('Parts Used'!E:E)), "*"&A3&"*", 'Parts Used'!C2:INDEX('Parts Used'!C:C, COUNTA('Parts Used'!C:C)))
• 额外优化：如果需要同时统计所有零件的出入库数据，可使用QUERY函数一次性生成完整的库存表，无需分单元格计算，适合数据量较大的场景。

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