如何根据 Excel 单元格内以逗号分隔的列引用列表来引用或查找值列表?

如何根据 Excel 单元格内以逗号分隔的列引用列表来引用或查找值列表?

我想对一列值执行 vlookup(或类似操作)。这对于从单行查找值很有效,但我希望能够查找多行、对结果求和,然后除以引用的行数。

例如:

   ABCDEFG
   [----给定值----------------] [工作/授权] [sum(vlookup(each(G),table,5))
                                                      /count(G)] [给定值]

1 项授权现有工作操作% 依赖性或% 依赖性
2 1 1 1 1 .55 乙
3B1055.50.55C,D
4 碳 100 75 50 .50 .60 正
5D10106.601              

我希望能够显示运行率,以及每个系统所依赖的系统的运行率 (F)。为了获得 F 的值,我希望将列 E 中由列 G 中的依赖项引用的每个值相加,然后除以 G 中的依赖项数量。列 G 的长度可以不同,并且将是一个以逗号分隔的值列表,与列 A 的值相加。

有什么方法可以在 Excel 中做到这一点吗?

答案1

您是否在寻找每行的公式?那么 G3 中的 C、D 在哪里(?)我假设您想在 A 列中查找 C 和 D,然后对 E 列中的相应值求平均值?如果是这样,请尝试将第 2 行的这个数组公式复制下来

=AVERAGE(IF(COUNTIF(G2,"*"&A$2:A$10&"*")*(A$2:A$10<>""),E$2:E$10))

要输入数组公式,您必须使用CTRL-SHIFT-ENTER。Excel 将在函数周围放置 {} 括号,以表明它是一个数组函数。

答案2

由于您有可变数量的值,最简单的方法是使用 UDF(用户定义函数)

这应该能让你得到答案:

Function CSVAverage(CSVList As String, DataRange As Range, NumberColumn As Long) 
Dim FindList() As String
Dim NumEntries As Long
Dim I As Long
Dim Tot As Double

FindList = Split(CSVList, ",")
Tot = 0
NumEntries = UBound(FindList) - LBound(FindList) + 1

On Error Resume Next
For I = LBound(FindList) To UBound(FindList)
    Tot = Tot + Application.WorksheetFunction.VLookup(FindList(I), DataRange, NumberColumn, False)
    If Err.Number <> 0 Then
        CSVAverage = CVErr(xlErrNum)
        Exit Function
    End If
Next I
CSVAverage = Tot / NumEntries
End Function

如果您有一个未找到的值,那么该函数将#NUM!
使用您的示例电子表格返回,F1 将会=CSVAverage(G1,A2:E5,5)

相关内容