Não tenho certeza da melhor maneira de formular esta questão agora, então usarei um exemplo com números aleatórios. Começo com valores atribuídos aos IDs, para que cada ID possa ser n=1, n=2, ... etc.
ID Value
1 1235
1 326
1 567
2 768
2 646
3 4367
3 346
3 35
4 436
5 3467
5 46
6 3467
6 3532
6 457
7 3463
7 3463
7 9328
7 2498
etc.
Quero calcular no Excel/Calc a Média e o SD para que os valores fiquem devidamente alinhados (idealmente haveria células mescladas), dadas suas uma, duas, três... etc células de entrada, uma célula de saída.
Captura de tela de exemplo:
O que eu quero obter. AVG e SD significam valores adequados para os dados fornecidos (aleatórios); portanto, AVG e SD estão devidamente alinhados]1
Quero, ou seja, uma forma automatizada de calcular a Média e o SD tendo em conta n diferentes, para que fique devidamente alinhado/formatado.
Deve haver uma maneira fácil de fazer isso, mas agora não tenho ideia. -_-
Eu apreciaria qualquer sugestão.
Responder1
Isso não é difícil se você assumir que Column Aestá classificado, portanto, estamos lidando com intervalos contíguos em Column B
(que é o que sugerem os aspectos visuais da sua pergunta). Configure a coluna Ecomo uma coluna auxiliar com esta fórmula:
E2→=IF(A2=A3, E3, ROW())
Para cada linha, isso identifica a última linha do intervalo em que a linha atual está. Então você pode obter os resultados desejados com
B2→=IF(A1=A2, "", AVERAGE(B2:INDIRECT("B" & E2)))C2→=IF(A1=A2, "", STDEV(B2:INDIRECT("B" & E2)))
(Ou use qualquer método desejado para calcular o desvio padrão.) Isso verifica se é a primeira linha de um intervalo. Se for, usa a INDIRECT()função para construir um intervalo entre a célula atual e a última célula com o mesmo valor de ID.
E, claro, você pode ocultar Column Eou usar alguma coluna fora da vista (por exemplo, Z) como coluna auxiliar. Observe que esta solução não usa fórmulas de matriz.
Responder2
Não foi exatamente isso que você pediu, mas eu usaria uma tabela dinâmica:
(Alterei o cabeçalho da primeira coluna para ID e formatei a segunda e a terceira colunas para mostrar apenas duas casas decimais, caso contrário, é apenas o que é mostrado no Construtor de Tabela Dinâmica.)
Você pode obter o que pediu colocando:
=IF($A2=$A1,"",AVERAGEIF($A2:$A19,$A2,$B2:$B19))
em C2, e:
=IF($A2=$A1,"",STDEV.P(INDIRECT("R"&MATCH($A2,$A:$A,0)&"C2:R"&MATCH($A2,$A:$A,1)&"C2",0)))
em D2 e preenchendo ambas as colunas. O IF externo em cada fórmula deve colocar o valor apenas na primeira linha que contém um ID específico. O resto da fórmula C2 deve ser simples, AVERAGEIF calcula a média dos números para os quais um critério específico é verdadeiro. Nesse caso, ele olha na primeira coluna, seleciona números com o mesmo valor que o valor na linha atual na primeira coluna e, em seguida, calcula a média dos números correspondentes na segunda coluna.
Infelizmente não existe "STDEVIF" (pelo menos no Excel 2011 no Mac, talvez exista em qualquer programa de planilha que você esteja usando. Nesse caso, basta usá-lo no lugar de MÉDIA na fórmula C2), então você precisa ser complicado :-). A abordagem é encontrar o intervalo de células do qual você deseja o desvio padrão, construir uma referência para essas células e, em seguida, passar essa referência para STDEV.P. O intervalo é construído encontrando oprimeirolinha na coluna 1 com o mesmo valor que o valor na linha atual na coluna 1 e, em seguida, encontrar odurarlinha na coluna 1 com o mesmo valor que o valor na linha atual na coluna 1. Esses dois valores delineiam a parte superior e inferior do subintervalo da coluna 1 que você deseja usar, então construa uma referência de estilo R1C1 em uma string, use INDIRETO para transformá-lo em uma referência real e, em seguida, passe-a para STDEV.P. Simples! :-) Ok, é um pouco horrível, mas funciona.
Responder3
O Excel não possui funcionalidades como essa integradas. Você precisaria usar subtotais ou tabelas dinâmicas que não fazem o que você deseja.
Para construir a tabela com fórmulas, use as duas funções a seguir.
Em C2 coloque
=IF(A2<>A1,AVERAGEIF($A$2:$A$13,A2,$B$2:$B$13),"")
Em D2 coloque e entre pressionandoctrl+shift+enter
=IF(A2<>A1,STDEV(IF($A$2:$A$13=A2,$B$2:$B$13)),"")
Em seguida, copie essas fórmulas
O IF(A2<>A1... no início basicamente diz apenas mostrar algo se a coluna A for diferente entre esta linha e a linha acima.
Averageif funciona exatamente como você acha que deveria.
A coluna D é uma fórmula de matriz, então primeiro ela entra e faz a instrução if em cada célula do intervalo e retornaria uma matriz algo como (1,14,13,3,FALSE,FALSE...) para cada célula e em seguida, calcula o desvio padrão que deve basicamente ignorar os valores FALSO.
Este método assume que os dados são classificados por ID. Os cálculos de média e Std Dev estariam corretos se não fossem classificados, mas apareceriam sempre que o ID mudasse, não apenas o primeiro.