Как сделать ссылку или vlookup список значений на основе списка ссылок на столбцы в ячейке в Excel, разделенных запятыми?

Question

Я хочу сделать vlookup (или аналогичный) для столбца, который представляет собой список значений. Это прекрасно работает для поиска значения из одной строки, но я хочу иметь возможность искать несколько строк, суммировать результаты и делить на количество строк, на которые ссылаются.

Например:

   A     B            C       D        E             F               G
   [----given values----------------]  [Work/Auth]   [sum(vlookup(each(G),table,5))
                                                      /count(G)]     [given vals]

1  Item  Authorized   OnHand  Working  Operational%  DependencyOR%   Dependencies 
2  A     1            1       1        1              .55            B 
3  B     10           5       5         .50           .55            C,D
4  C     100          75      50        .50           .60            D
5  D     10           10      6         .60          1              

Я хочу показать операционную скорость и операционную скорость систем, от которых зависит каждая система (F). Чтобы получить значение для F, я хочу суммировать по каждому значению в column-E, на которое ссылалась зависимость в column-G, а затем делить на количество зависимостей в G. Column-G может иметь переменную длину и будет быть разделенным запятыми списком значений из столбца-A.

Есть ли способ сделать это в Excel?

Answer 1

так как у вас есть переменное количество значений, самый простой способ - использовать 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)

Answer 2

Вы ищете формулу для каждой строки? Итак, где у вас есть C, D в G3 (?) Я предполагаю, что вы хотите найти C и D в столбце A и усреднить соответствующие значения в столбце E? Если это так, попробуйте эту формулу массива для строки 2, скопированной вниз

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

Для ввода формулы массива вы должны использовать CTRL-SHIFT-ENTER . Excel поместит {} фигурные скобки вокруг функции, чтобы указать, что это функция массива.