如何用Excel/Google Sheets实现标签号匹配的库存转售自动化？

我正在编写程序简化销售审计工作，需求如下：

• 输入已售商品的标签号
• 在工作簿的5张库存表（存在库存重叠，后续会合并为1-2张）中定位对应商品的全部信息
• 将该商品从库存表中彻底删除，并添加至Sold表

我想优先用Excel/Google Sheets的公式实现，而非Python。目前已用Xlookup从单个库存表提取信息，但嵌套IFS实现多表查询失败，还不知道如何处理重复标签号的情况（比如输入123456时，某库存表有多个相同标签，只想删除首个匹配行而非全部）。我写的公式如下：

=ifs( A1 = vlookup(A1, Incoming!$G:$G,1, FALSE), xlookup(A1, Incoming!$G:$G, Incoming!$A:$P), A1 = vlookup(A1, AUDIT_7.20.22!$G:$G, 1, FALSE), xlookup(A1, AUDIT_7.20.22!$G:$G, AUDIT_7.20.22!$A:$P), A1 = vlookup(A1, AUDIT_SHEET_BIG_HAMMAL!$G:$G, 1, FALSE), xlookup(A1, AUDIT_SHEET_BIG_HAMMAL!$G:$G, AUDIT_SHEET_BIG_HAMMAL!$A:$P), A1 = vlookup(RECEIVING_MOVE!$G:$G, 1, FALSE), xlookup(A1, RECEIVING_MOVE!$G:$G, RECEIVING_MOVE!$A:$P), A1 = vlookup(A1, OLD_WAREHOUSE!$G:$G, 1, FALSE), xlookup(A1, OLD_WAREHOUSE!$G:$G, OLD_WAREHOUSE!$A:$P) )

一、多表查询的公式修正 #

你的IFS公式存在两个核心问题：一是用A1=VLOOKUP(...)做条件判断冗余，且VLOOKUP找不到值会返回#N/A导致条件失效；二是第四个VLOOKUP缺失了查找值参数。以下是修正后的方案：

Excel 方案 #

用IFERROR嵌套XLOOKUP，按优先级顺序查询各表，找到首个匹配结果即返回：

=IFERROR(XLOOKUP(A1, Incoming!$G:$G, Incoming!$A:$P),
 IFERROR(XLOOKUP(A1, AUDIT_7.20.22!$G:$G, AUDIT_7.20.22!$A:$P),
  IFERROR(XLOOKUP(A1, AUDIT_SHEET_BIG_HAMMAL!$G:$G, AUDIT_SHEET_BIG_HAMMAL!$A:$P),
   IFERROR(XLOOKUP(A1, RECEIVING_MOVE!$G:$G, RECEIVING_MOVE!$A:$P),
    XLOOKUP(A1, OLD_WAREHOUSE!$G:$G, OLD_WAREHOUSE!$A:$P)
   )
  )
 )
)

Google Sheets 方案 #

除了复用上述Excel公式，还可以用QUERY合并所有库存表后查询，更适合后续合并表的场景：

=QUERY({Incoming!$A:$P; AUDIT_7.20.22!$A:$P; AUDIT_SHEET_BIG_HAMMAL!$A:$P; RECEIVING_MOVE!$A:$P; OLD_WAREHOUSE!$A:$P}, 
 "select * where Col7 = '"&A1&"' limit 1", 0)

其中Col7对应标签号所在的G列（第7列），limit 1确保只返回首个匹配行。

二、删除首个匹配行+转移至Sold表的自动化 #

公式无法直接修改/删除工作表行数据，需结合宏（Excel）或脚本（Google Sheets）实现：

Excel 方案（VBA宏） #

1. 按Alt+F11打开VBA编辑器，插入模块并粘贴以下代码：

Sub MoveSoldItem()
    Dim tagNum As String
    Dim ws As Worksheet
    Dim foundRow As Range
    Dim soldWs As Worksheet
    
    Set soldWs = ThisWorkbook.Sheets("Sold")
    tagNum = InputBox("请输入标签号：") ' 也可指定固定单元格，比如Range("A1").Value
    
    ' 按优先级遍历库存表
    For Each ws In ThisWorkbook.Sheets(Array("Incoming", "AUDIT_7.20.22", "AUDIT_SHEET_BIG_HAMMAL", "RECEIVING_MOVE", "OLD_WAREHOUSE"))
        Set foundRow = ws.Columns("G").Find(What:=tagNum, LookIn:=xlValues, LookAt:=xlWhole)
        If Not foundRow Is Nothing Then
            ' 复制整行到Sold表末尾
            foundRow.EntireRow.Copy soldWs.Cells(soldWs.Rows.Count, 1).End(xlUp).Offset(1, 0)
            ' 删除库存表中的该行
            foundRow.EntireRow.Delete
            MsgBox "已找到并转移商品至Sold表"
            Exit Sub ' 找到首个匹配后停止遍历
        End If
    Next ws
    
    MsgBox "未找到该标签号的商品"
End Sub

2. 返回Excel，通过「开发工具→插入→按钮」关联该宏，点击按钮即可执行操作。

Google Sheets 方案（Apps Script） #

1. 打开表格，点击「扩展程序→Apps Script」
2. 粘贴以下代码并保存：

function moveSoldItem() {
    const tagNum = Browser.inputBox("请输入标签号：");
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const soldSheet = ss.getSheetByName("Sold");
    const sheetNames = ["Incoming", "AUDIT_7.20.22", "AUDIT_SHEET_BIG_HAMMAL", "RECEIVING_MOVE", "OLD_WAREHOUSE"];
    
    for (const sheetName of sheetNames) {
        const sheet = ss.getSheetByName(sheetName);
        if (!sheet) continue;
        const data = sheet.getDataRange().getValues();
        // 从第2行开始遍历（假设第1行是表头）
        for (let i = 1; i < data.length; i++) {
            if (data[i][6] === tagNum) { // G列对应数组索引6
                soldSheet.appendRow(data[i]);
                sheet.deleteRow(i + 1); // 表格行号从1开始，需加1转换
                SpreadsheetApp.getUi().alert("已找到并转移商品至Sold表");
                return;
            }
        }
    }
    
    SpreadsheetApp.getUi().alert("未找到该标签号的商品");
}

3. 运行脚本并授权后，即可通过「扩展程序」调用该功能。

三、重复标签号处理说明 #

• 上述宏/脚本均按库存表优先级顺序查找，找到首个匹配行后立即执行转移删除，不会处理同表内的其他重复标签
• 后续合并库存表时，建议先将所有数据合并到一张表，添加「来源表」列标记原始位置，再处理会更高效

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