Como referenciar ou visualizar uma lista de valores com base em uma lista separada por vírgulas de referências de coluna dentro de uma célula no Excel?

Question

Eu quero fazer um vlookup (ou similar) contra uma coluna que é uma lista de valores. Isso funciona bem para procurar um valor em uma única linha, mas quero poder pesquisar várias linhas, somar os resultados e dividir pelo número de linhas referenciadas.

Por exemplo:

   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              

Eu quero poder mostrar uma Taxa Operacional e uma taxa operacional dos sistemas dos quais cada sistema depende (F). Para obter um valor para F, quero somar cada valor na coluna-E que foi referenciado por uma dependência na coluna-G e, em seguida, dividir pelo número de dependências em G. A coluna-G pode ter comprimentos variados e seja uma lista separada por vírgula de valores da coluna-A.

Existe alguma maneira de fazer isso no excel?

    

Answer 1

Você está procurando uma fórmula para cada linha? Então, onde você tem C, D em G3 (?), Eu suponho que você queira pesquisar C e D na coluna A e, em média, os valores correspondentes na coluna E? Em caso afirmativo, tente esta fórmula de matriz para a linha 2 copiada para baixo

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

Para inserir a fórmula de matriz, você deve usar CTRL-SHIFT-ENTER . O Excel colocará chaves {} ao redor da função para indicar que é uma função de matriz.

    

Answer 2

como você tem uma quantidade variável de valores, a maneira mais fácil é usar uma UDF (função definida pelo usuário)

Isso deve te dar sua resposta:

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

Se você tiver um valor que não foi encontrado, a função retornará #NUM!
usando sua planilha de exemplo, F1 teria =CSVAverage(G1,A2:E5,5)