Média do Excel e se entradas de simplificação de matriz

Question

Eu tenho um conjunto de dados que agrega várias empresas e categorias. Eu preciso agregar diferentes empresas juntas e média em várias categorias. Eu sei que a solução é uma fórmula de array de média, mas eu estou querendo saber se há uma maneira de tornar as coisas um pouco mais rápidas. Aqui está um exemplo (muito simplificado) do que estou trabalhando.

DigamosqueeupreciseclassificarasempresasAeBjuntasparacadaclassificaçãoseparadamente.Euseiqueafórmulaéassim:

{=MÉDIA(SE(($A$5:$A$13={"A", "B"}) * ($ B $ 5: $ B $ 13="H"), $ C $ 5: $ C $ 13 ))}

Mas eu preciso executar isso para cada uma das classificações (e, na verdade, pode haver várias combinações de empresa / classificação para alterar), então pode haver 5-10 fórmulas para atualizar. Então, eu preciso agregar empresas diferentes, digamos B, C e D. Sei que parece:

{= MÉDIA (SE (($ A $ 5: $ A $ 13 = {"B", "C", "D"}) * ($ B $ 5: $ B $ 13="H"), $ C $ 5 : $ C $ 13))} {= MÉDIA (SE (($ A $ 5: $ A $ 13 = {"B", "C", "D"}) * ($ B $ 5: $ B $ 13="N"), $ C $ 5: $ C $ 13))}, etc

A minha pergunta é, existe alguma maneira de eu colocar a parte {"B", "C", "D"} em outra célula que eu possa mudar rapidamente e então fazer referência a essa célula para evitar mudar tudo manualmente cada vez?

    

Answer 1

É possível, no entanto, talvez seja necessário usar uma função Definida pelo usuário simples para dividir a lista de empresas separadas por vírgula em uma matriz. Neste exemplo A1: empresas da lista A5. B1: B5 como "H" e C1: C5 é calculado com base na sua fórmula. F2 retorna a média & Lista de empresas é mencionada em H1 (separado por vírgulas)

Pressione ALT + F11 para acessar o VBA Editor e inserir um Módulo no menu Inserir. Abra o módulo e cole o código a seguir.

Public Function MyArray(strTest As String)
   MyArray = Split(strTest, ",")
End Function

Agora, em F2, você tem sua fórmula que usa essa função MyArray.

=AVERAGE(IF((A1:A5=MyArray(H1))*(B1:B5="H"),C1:C5))

Pressione CTRL + SHIFT + ENTER para criar uma fórmula de matriz e você terá os resultados pretendidos conforme desejado.

    

Answer 2

Você pode colocar as pesquisas desejadas em um intervalo contíguo e usar:

=AVERAGE(AVERAGEIFS(C:C,B:B,"H",A:A,E1:INDEX(E:E,MATCH("zzz",E:E))))

E use Ctrl-Shift-Enter em vez de Enter ao sair do modo de edição. Se feito corretamente, o Excel colocará {} em torno da fórmula.

Então você pode colocar as pesquisas desejadas iniciando em E1.