如何按分配的服务代码自动化录入客户信息并清理表格数据？

一、先清理Spreadsheet1的B列内容 #

• 手动批量清理：选中B列，按Ctrl+H（Windows）/Cmd+H（Mac），查找框输入#，替换框留空，点击「全部替换」即可快速删除所有#。如果需要完全自动化，下面的脚本会包含这一步。

二、Google Sheets 自动化脚本方案 #

核心逻辑 #

基于Spreadsheet3的匹配规则，将Spreadsheet2中的客户姓名、位置映射到Spreadsheet1对应服务代码的行中，全程无需手动操作。

脚本代码 #

function matchCustomerData() {
  // 绑定三个工作表（需确保表名与实际一致）
  const ss1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Spreadsheet1");
  const ss2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Spreadsheet2");
  const ss3 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Spreadsheet3");

  // 获取所有表格数据
  const ss1Data = ss1.getDataRange().getValues();
  const ss2Data = ss2.getDataRange().getValues();
  const ss3Rules = ss3.getDataRange().getValues();

  // 构建服务代码→客户关联键的映射
  const ruleMap = new Map();
  for (let i = 1; i < ss3Rules.length; i++) { // 跳过表头行
    const serviceCode = ss3Rules[i][0];
    const customerKey = ss3Rules[i][1]; // 假设Spreadsheet3第2列是客户关联键（如客户编号）
    ruleMap.set(serviceCode, customerKey);
  }

  // 构建客户关联键→[姓名, 位置]的映射
  const customerMap = new Map();
  for (let i = 1; i < ss2Data.length; i++) { // 跳过表头行
    const customerKey = ss2Data[i][0]; // 假设Spreadsheet2第1列是客户关联键
    const name = ss2Data[i][1];
    const location = ss2Data[i][2];
    customerMap.set(customerKey, [name, location]);
  }

  // 填充Spreadsheet1的客户信息
  for (let i = 1; i < ss1Data.length; i++) { // 跳过表头行
    const serviceCode = ss1Data[i][0];
    const customerKey = ruleMap.get(serviceCode);
    if (customerKey && customerMap.has(customerKey)) {
      const [name, location] = customerMap.get(customerKey);
      ss1Data[i][2] = name; // 假设填充到第3列（姓名）
      ss1Data[i][3] = location; // 假设填充到第4列（位置）
    } else {
      ss1Data[i][2] = "无匹配";
      ss1Data[i][3] = "无匹配";
    }
  }

  // 写回处理后的数据
  ss1.getDataRange().setValues(ss1Data);

  // 自动清理B列的#
  const bColumn = ss1.getRange("B:B");
  const cleanedB = bColumn.getValues().map(row => [row[0].toString().replace(/#/g, "")]);
  bColumn.setValues(cleanedB);
}

使用步骤 #

1. 打开目标Google表格，点击「扩展程序」→「Apps脚本」
2. 粘贴上述代码，根据实际表格的列顺序、表头位置修改代码中的索引（比如如果Spreadsheet3的客户关联键在第3列，就把ss3Rules[i][1]改成ss3Rules[i][2]）
3. 保存脚本，点击运行完成首次授权
4. 可设置定时触发器（「编辑」→「当前项目的触发器」），让脚本自动定期执行

三、Excel VBA 自动化方案 #

代码示例 #

Sub MatchCustomerData()
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Dim ruleDict As Object, customerDict As Object
    Dim lastRow1 As Long, lastRow2 As Long, lastRow3 As Long
    Dim i As Long, serviceCode As String, customerKey As String
    
    ' 绑定工作表（需确保表名与实际一致）
    Set ws1 = ThisWorkbook.Sheets("Spreadsheet1")
    Set ws2 = ThisWorkbook.Sheets("Spreadsheet2")
    Set ws3 = ThisWorkbook.Sheets("Spreadsheet3")
    
    ' 创建字典存储映射关系
    Set ruleDict = CreateObject("Scripting.Dictionary")
    Set customerDict = CreateObject("Scripting.Dictionary")
    
    ' 加载服务代码匹配规则
    lastRow3 = ws3.Cells(ws3.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastRow3
        serviceCode = Trim(ws3.Cells(i, "A").Value)
        customerKey = Trim(ws3.Cells(i, "B").Value)
        If Not ruleDict.Exists(serviceCode) Then
            ruleDict.Add serviceCode, customerKey
        End If
    Next i
    
    ' 加载客户姓名与位置数据
    lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastRow2
        customerKey = Trim(ws2.Cells(i, "A").Value)
        Dim customerInfo As Variant
        customerInfo = Array(Trim(ws2.Cells(i, "B").Value), Trim(ws2.Cells(i, "C").Value))
        If Not customerDict.Exists(customerKey) Then
            customerDict.Add customerKey, customerInfo
        End If
    Next i
    
    ' 填充Spreadsheet1的客户信息
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastRow1
        serviceCode = Trim(ws1.Cells(i, "A").Value)
        If ruleDict.Exists(serviceCode) Then
            customerKey = ruleDict(serviceCode)
            If customerDict.Exists(customerKey) Then
                ws1.Cells(i, "C").Value = customerDict(customerKey)(0) ' 姓名列
                ws1.Cells(i, "D").Value = customerDict(customerKey)(1) ' 位置列
            Else
                ws1.Cells(i, "C").Value = "无匹配"
                ws1.Cells(i, "D").Value = "无匹配"
            End If
        Else
            ws1.Cells(i, "C").Value = "无匹配规则"
            ws1.Cells(i, "D").Value = "无匹配规则"
        End If
    Next i
    
    ' 自动清理B列的#
    ws1.Columns("B").Replace What:="#", Replacement:="", LookAt:=xlPart
End Sub

使用步骤 #

1. 打开Excel文件，按Alt+F11打开VBA编辑器
2. 插入模块，粘贴上述代码，根据实际列位置调整索引
3. 运行宏即可完成匹配；若需自动触发，可在ThisWorkbook的Workbook_Open事件中调用该子过程

四、关键注意事项 #

• 确保三张表格的关联键（如客户编号）格式统一，避免空格、大小写差异导致匹配失败
• 脚本运行前建议备份数据，防止意外覆盖
• 若存在重复服务代码，脚本会保留最后一条规则的匹配结果，可根据需求修改映射逻辑

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