如何在Excel中基于颜色匹配返回多个指定单元格值？

假设你的ColorMatch函数是用来匹配单元格填充色的自定义函数，以下是基于它编写的新函数，可直接提取数据工作表中绿色标记行的指定指标值：

自定义VBA函数代码 #

打开VBA编辑器（Alt+F11），在对应模块中添加以下代码：

Function GetColoredRowValues(dataRange As Range, targetColNum As Integer, targetColor As Variant) As Variant
    Dim resultArr() As Variant
    Dim cell As Range
    Dim rowCount As Integer
    Dim i As Integer
    
    rowCount = 0
    ' 遍历数据区域的首列单元格，判断每行颜色
    For Each cell In dataRange.Columns(1).Cells
        ' 调用已有的ColorMatch函数验证当前行颜色
        If ColorMatch(cell, targetColor) = True Then
            rowCount = rowCount + 1
            ReDim Preserve resultArr(1 To rowCount)
            ' 提取目标列的对应值
            resultArr(rowCount) = dataRange.Cells(cell.Row - dataRange.Row + 1, targetColNum).Value
        End If
    Next cell
    
    ' 返回结果数组，支持动态溢出或数组公式
    GetColoredRowValues = resultArr
End Function

参数说明 #

• dataRange：数据工作表中包含所有数据的区域（例如数据!$A:$Z）
• targetColNum：要提取的指标所在列号，可结合MATCH函数动态获取（例如MATCH("revenue", 数据!$1:$1, 0)）
• targetColor：目标填充色的匹配值，需与你的ColorMatch函数参数格式一致（例如颜色索引10，或RGB值RGB(0,255,0)）

工作表使用方法 #

在关键指标工作表的任意单元格输入以下公式（以提取revenue为例）：

=GetColoredRowValues(数据!$A:$Z, MATCH("revenue", 数据!$1:$1, 0), 10)

• Excel 365/2021及以上版本：公式会自动溢出显示所有符合条件的值
• 旧版Excel：选中足够多的单元格，输入公式后按Ctrl+Shift+Enter作为数组公式执行

注意事项 #

• 确保ColorMatch函数能正确识别单元格填充色，若该函数参数为RGB格式，需将公式中的10替换为RGB(0,255,0)
• 数据工作表的绿色标记行更新后，按F9刷新计算即可同步关键指标工作表的值

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