如何对单个单元格逗号分隔值执行VLOOKUP并按填充色填充对应列

前置说明 #

Excel 2010 没有内置直接识别单元格填充色的工作表函数，无法直接用VLOOKUP匹配颜色，需要先通过宏表函数或者VBA自定义函数实现颜色编码获取，再完成匹配。 #

场景1：F列单个单元格为统一填充色，G列返回单个对应值 #

步骤1：定义获取填充色编码的宏表函数 #

• 选中G列第一个需要填充的单元格（如G1）
• 点击顶部菜单栏「公式」-「定义名称」
• 弹窗内填写以下参数：
  • 名称：GetColor
  • 引用位置：=GET.CELL(38,Sheet1!F1)（将Sheet1替换为你实际的工作表名，F1为对应行的F列单元格，不要加$锁定行号）
• 点击「确定」保存

步骤2：建立颜色-尺码匹配对照表 #

在工作表空白区域（如I1:J3）录入匹配规则：

注：上述编码为Excel默认标准填充色的编码：黑色填充对应1，黄色对应6，红色对应3。如果你使用的是自定义填充色，可在对应F列单元格输入=GetColor获取实际编码后替换上表的数值。

步骤3：写入公式并填充 #

在G1单元格输入公式：
=VLOOKUP(GetColor,$I$1:$J$3,2,0)
按回车后，选中G1单元格下拉填充整列G列即可。 #

场景2：F列单个单元格内为逗号分隔的多值，需返回对应逗号分隔的多尺码 #

需要使用VBA自定义函数实现，操作如下：

• 按Alt+F11打开VBA编辑器，点击顶部菜单栏「插入」-「模块」
• 在弹出的代码编辑窗口粘贴以下代码：

Function GetSizeByColor(rng As Range) As String
    Dim colorIdx As Integer
    Dim sizeStr As String
    Dim valArr As Variant
    Dim i As Integer
    ' 获取单元格填充色编码
    colorIdx = rng.Interior.ColorIndex
    ' 匹配对应尺码
    Select Case colorIdx
        Case 1: sizeStr = "S"
        Case 6: sizeStr = "M"
        Case 3: sizeStr = "XL"
        Case Else: sizeStr = ""
    End Select
    ' 处理逗号分隔多值场景
    If InStr(rng.Value, ",") > 0 And sizeStr <> "" Then
        valArr = Split(rng.Value, ",")
        GetSizeByColor = ""
        For i = LBound(valArr) To UBound(valArr)
            GetSizeByColor = GetSizeByColor & sizeStr & ","
        Next
        GetSizeByColor = Left(GetSizeByColor, Len(GetSizeByColor) - 1)
    Else
        GetSizeByColor = sizeStr
    End If
End Function

• 关闭VBA编辑器，回到工作表，在G1单元格输入公式=GetSizeByColor(F1)，按回车后下拉填充即可。

注意：使用VBA自定义函数需要将文件保存为「Excel 启用宏的工作簿（.xlsm）」格式，后续打开文件时需要启用宏才能正常计算。

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