我想对一列值执行 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)