如何在Excel中自动规整客户提供的非标物料颜色数据？

针对你遇到的客户颜色数据格式混乱、普通SEARCH函数无法匹配的问题，以下是三个可行的实现方案，按需选择：

方案1：函数组合（适合无VBA/Power Query权限的场景） #

假设你的自有颜色数据库存放在Sheet2中：A列是颜色代码（如7035），B列是目标规整格式（如Frame Ral 7035）。

针对Excel 365/2021（支持动态数组） #

在需要输出规整结果的单元格中输入以下公式，下拉填充即可：

=IFERROR(XLOOKUP(TRUE,ISNUMBER(SEARCH(Sheet2!$A$2:$A$100,A2)),Sheet2!$B$2:$B$100,"未匹配到颜色代码"),"未匹配到颜色代码")

• 逻辑：用SEARCH遍历颜色库所有代码，检查是否在客户单元格中存在；XLOOKUP返回第一个匹配代码对应的目标格式；IFERROR处理无匹配的情况。

兼容旧版Excel #

用LOOKUP+SEARCH组合实现：

=IFERROR(TEXTJOIN(", ",TRUE,LOOKUP(9^9,SEARCH(Sheet2!$A$2:$A$100,A2),Sheet2!$B$2:$B$100)),"未匹配到颜色代码")

• 逻辑：SEARCH返回匹配代码的位置，LOOKUP(9^9,...)取最后一个匹配结果；TEXTJOIN支持同一单元格存在多个颜色代码的情况（用逗号分隔）。

方案2：Power Query批量处理（适合一次性处理大量数据） #

如果需要批量规整数据，Power Query是更高效的选择：

1. 将客户数据和自有颜色库分别导入Power Query（「数据」选项卡→「自表格/区域」）。
2. 选中客户数据的颜色列，点击「添加列」→「自定义列」，输入以下公式（注意替换颜色库为你的颜色表名称，颜色字段为客户数据的颜色列名）：

let
    颜色代码列表 = 颜色库[颜色代码],
    匹配到的代码 = List.FindText(颜色代码列表, [颜色字段]),
    结果 = if List.Count(匹配到的代码)>0 then Table.SelectRows(颜色库, each [颜色代码] = 匹配到的代码{0})[目标格式]{0} else "未匹配"
in
    结果

3. 点击「关闭并上载」，规整后的颜色格式会自动同步到Excel表格中。

方案3：VBA宏（适合频繁重复操作） #

如果需要一键快速处理，写个简单的VBA宏：

1. 按Alt+F11打开VBA编辑器，插入一个新模块。
2. 粘贴以下代码（根据你的实际工作表名称、列号修改）：

Sub 规整颜色格式()
    Dim colorWS As Worksheet, dataWS As Worksheet
    Dim colorDict As Object
    Dim lastRow As Long, i As Long
    Dim cellText As String, code As Variant
    
    ' 替换为你的颜色库工作表和客户数据工作表名称
    Set colorWS = ThisWorkbook.Sheets("颜色库")
    Set dataWS = ThisWorkbook.Sheets("客户数据")
    Set colorDict = CreateObject("Scripting.Dictionary")
    
    ' 把颜色库加载到字典，提升匹配效率
    lastRow = colorWS.Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastRow
        colorDict(colorWS.Cells(i, "A").Value) = colorWS.Cells(i, "B").Value
    Next i
    
    ' 遍历客户数据的颜色列（假设是A列，结果输出到B列）
    lastRow = dataWS.Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastRow
        cellText = dataWS.Cells(i, "A").Value
        For Each code In colorDict.Keys
            If InStr(cellText, code) > 0 Then
                dataWS.Cells(i, "B").Value = colorDict(code)
                Exit For ' 找到第一个匹配就停止，如需匹配多个可删除此句
            End If
        Next code
    Next i
End Sub

3. 回到Excel，按Alt+F8运行宏即可完成批量规整。

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