Como você alinha corretamente as saídas de média e desvio padrão em uma planilha?

Question

Eu não tenho certeza da melhor maneira de formular essa pergunta agora, então vou usar um exemplo usando números aleatórios. Começo com valores atribuídos a 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

Eu quero calcular no Excel / Calc a média e o SD para que os valores estejam alinhados corretamente (idealmente, haveria células mescladas), dadas suas células de entrada, uma, duas, três ... etc, uma célula de saída .

Exemplo de captura de tela:

Oqueeuqueroobter.AVGeSDsignificamvaloresadequadosparaosdadosfornecidos(aleatórios);assim,oAVGeoSDestãoalinhadoscorretamente] 1

Eu quero, isto é, uma maneira automatizada de calcular a Média e o SD tendo em conta diferentes n, para que eles fiquem alinhados / formatados adequadamente.

Deve haver uma maneira fácil de fazer isso, mas agora eu não tenho a menor ideia. -_-

Eu apreciaria qualquer sugestão.

    

Answer 1

Isso não é difícil se você assumir que Coluna A está classificada, então estamos lidando com intervalos contíguos na coluna B (que é o que os aspectos visuais da sua pergunta sugerem). Configure a coluna E como uma coluna auxiliar com esta fórmula:

• E2 → =IF(A2=A3, E3, ROW())

Para cada linha, isso identifica a última linha do intervalo que a linha atual está dentro 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 que você queira para calcular o desvio padrão.) Isso verifica se é a primeira linha de um intervalo. Se for, ele usa a função INDIRECT() 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 a coluna E ou usar alguma coluna fora da vista (por exemplo, Z ) como coluna auxiliar. Observe que esta solução não usa fórmulas de matriz.

    

Answer 2

Isso não é exatamente o que você pediu, mas eu usaria uma tabela dinâmica:

(EumudeiocabeçalhodaprimeiracolunaparaIDeformateiasegundaeterceiracolunasparamostrarapenasduascasasdecimais,casocontrário,éapenasoqueémostradonoConstrutordeTabelaDinâmica.)

Vocêpodeobteroquesolicitoucolocando:

=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 é colocar o valor apenas na primeira linha que contém um ID específico. O resto da fórmula C2 deve ser direta, números médios AVERAGEIF para os quais um critério específico é verdadeiro. Nesse caso, ele procura na primeira coluna, seleciona números com o mesmo valor que o valor na linha atual da primeira coluna, e calcula a média dos números correspondentes na segunda coluna.

Infelizmente não há "STDEVIF" (pelo menos no Excel 2011 no Mac, talvez exista em qualquer programa de planilha que você esteja usando. Se sim, use-o no lugar de AVERAGE na fórmula C2), então você tem ser complicado :-). A abordagem é encontrar o intervalo de células para o qual você quer o desvio padrão, construir uma referência a essas células e, em seguida, passar essa referência para STDEV.P. O intervalo é construído localizando a linha primeiro na coluna 1 com o mesmo valor que o valor na linha atual na coluna 1, depois encontrando a linha última 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, portanto, construa uma referência de estilo R1C1 em uma cadeia, use INDIRECT para transformá-la em uma referência real, então passe isso para STDEV.P. Simples! :-) Ok, é um pouco medonho, mas funciona.

    

Answer 3

O Excel não tem funcionalidade como essa integrada. Você precisaria usar subtotais ou tabelas dinâmicas que não fazem o que você quer depois.

Paraconstruiratabelacomfórmulas,useasduasfunçõesaseguir.

EmC2,coloque

=IF(A2<>A1,AVERAGEIF($A$2:$A$13,A2,$B$2:$B$13),"")

Em D2, insira e insira pressionando ctrl+shift+enter

=IF(A2<>A1,STDEV(IF($A$2:$A$13=A2,$B$2:$B$13)),"")

Copie essas fórmulas para baixo

O IF (A2 & A1; A1 ... no início basicamente diz apenas mostrar algo se a coluna A difere entre esta linha e a acima.

A Averageif funciona exatamente como você acha que deveria.

A coluna D é uma fórmula de matriz, então primeiro ela entra e faz a declaração if em cada célula do intervalo e retornaria uma matriz como (1,14,13,3, FALSE, FALSE ...) para cada célula e, em seguida, calcula o desvio padrão sobre isso que deve basicamente ignorar os valores FALSE.

Este método assume que os dados são classificados no ID. Os cálculos médios e de desenvolvimento padrão estariam certos se não fossem classificados, mas apareceriam sempre que o ID fosse alterado, não apenas o primeiro.