Excel Arrays: média e se combinar mais de um critério

Question

Eu tenho dados para uma ferramenta de avaliação que tem cerca de 20 categorias e cerca de 200 itens com pontuação de 0-3. A primeira metade da ferramenta é obrigatória, outras seções são opcionais, portanto, há muitas áreas em branco. 4 itens têm a opção de N / A.

Atualmente, uso arrays para coletar dados average if em várias colunas para cada categoria. Recebi ajuda em outro fórum e estou incluindo as fórmulas abaixo também. Eu uso dois diferentes, como mencionado em alguns dos intervalos de dados, todos os itens requerem uma pontuação e, para algumas seções, é opcional ou não é pontuado. Na grade de amostra abaixo, preciso encontrar a média de todas as colunas de dados combinadas ( Q1-Q4 ) com base em um tipo de avaliação B e N . Nota: algumas pontuações são dadas opção para n / a

T   Q1  Q2  Q3  Q4
N   0   0   3   2
B   1   2   3   
N   1   1   0   0
N       
D   0   n/a 2   3
O1  0   1   1   0
O1  2   0   0   0
O2  2   n/a 0   0

A grade que colei aqui não parece ser exibida corretamente e não é possível colar uma imagem dela. Abaixo, não se parece com a minha grade. Então, digamos que na coluna A eu tenho códigos diferentes para o tipo de avaliação. B , D , N , O1 , O2 . Nas colunas B-E , tenho pontuações para cada pergunta. Algumas células estão em branco para uma linha inteira, algumas linhas na coluna C estão marcadas como N / A. Então, a linha dois pode ser: N 0 1 3 0 ; a linha dois pode ser B 0 N / a 2 2 . A linha três pode ser B (células vazias)

Fórmulas atuais para usar um critério:

=AVERAGE(IF(ANSAt!H:H="B",ANSAt!J:AC))

e não incluir espaços em branco em seções com muitos espaços em branco

=AVERAGE(IF(ANSAt!$H:$H="B", IF(XMod!G:Y<>"", XMod!G:Y)))

Se alguém puder me ajudar a mostrar o exemplo, eu agradeceria :) Eu até tentei usar a ferramenta Snip.

    

Answer 1

Se você puder classificar na coluna "T", poderá usar isto:

=AGGREGATE(1,6,INDEX($B$2:$E$9,MATCH(H2,$A$2:$A$9,0),1):INDEX($B$2:$E$9,MATCH(H2,$A$2:$A$9,0)+COUNTIF($A$2:$A$9,H2)-1,4))

EDITAR:

Enquantovocêestátentandoentenderoacima,penseiemadicionarmaisumparavocê.Estafórmulanãoseimportaseéclassificadaounão:

=SUM(IF(ISERROR(INDEX(($A$2:$A$9=H2)*$B$2:$E$9,)),"",INDEX(($A$2:$A$9=H2)*$B$2:$E$9,)))/SUMPRODUCT(--(INDEX(($A$2:$A$9=H2)*ISNUMBER($B$2:$E$9),)))

Esta é uma fórmula de matriz. Ele precisa ser confirmado com Ctrl-Shift-Enter ao sair do modo de edição. Se feito corretamente, o Excel colocará {} em torno da fórmula.

Além disso, você deseja codificar uma dessas fórmulas com o valor de pesquisa e substituir diretamente a referência H2 pela String desejada; "N" .