如何跨多工作表匹配最低故障率并返回对应系统名称？

一、动态数组公式方案（适用于Excel 365/2021及以上版本） #

直接在Overview!J27输入以下公式，自动适配新增/删除的系统工作表：

=LET(
    sheets,FILTER(GET.WORKBOOK(1),NOT(ISNUMBER(SEARCH("Overview",GET.WORKBOOK(1))))),
    sheetNames,REPLACE(sheets,1,FIND("]",sheets),""),
    failureRates,BYROW(sheetNames,LAMBDA(s,INDIRECT("'"&s&"'!J87"))),
    systemNames,BYROW(sheetNames,LAMBDA(s,INDIRECT("'"&s&"'!D9"))),
    FILTER(systemNames,failureRates=J26)
)

说明： #

• GET.WORKBOOK(1)获取所有工作表名称（带工作簿前缀）
• FILTER排除Overview工作表
• REPLACE提取纯工作表名称
• BYROW遍历每个工作表，分别获取故障率（J87）和系统名称（D9）
• 最后用FILTER返回所有匹配最低故障率的系统名称（多系统故障率相同时，返回所有对应名称）

二、VBA自定义函数方案（兼容所有Excel版本） #

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

Function GetSystemName(minRate As Double) As String
    Dim ws As Worksheet
    Dim result As String
    result = ""
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Overview" Then
            If ws.Range("J87").Value = minRate Then
                If result <> "" Then result = result & ", "
                result = result & ws.Range("D9").Value
            End If
        End If
    Next ws
    
    GetSystemName = IIf(result = "", "未找到匹配系统", result)
End Function

回到Excel，在Overview!J27输入：

=GetSystemName(J26)

说明： #

• 遍历所有工作表并跳过Overview
• 自动匹配所有故障率等于J26值的系统，返回对应D9的名称，多匹配时用逗号分隔
• 完全适配动态增减的工作表，兼容所有Excel版本

原公式失效原因 #

你尝试的INDEX('Sensor 1:Sensor 5'!D9,MATCH(...))无法工作，因为跨工作表区域引用会返回垂直数组，但MATCH无法正确匹配跨表的单个单元格数组，且无法动态适配工作表数量变化。

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