No estoy seguro de cuál es la mejor manera de formular esta pregunta en este momento, así que usaré un ejemplo con números aleatorios. Empiezo con valores asignados a los ID, de modo que cada ID pueda 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.
Quiero calcular en Excel/Calc el promedio y la SD para que los valores estén alineados correctamente (idealmente habría celdas fusionadas), dadas sus una, dos, tres... etc. celdas de entrada, una celda de salida.
Captura de pantalla de ejemplo:
Lo que quiero obtener. AVG y SD significan valores adecuados para los datos dados (aleatorios); por lo tanto AVG y SD están correctamente alineados]1
Es decir, quiero una forma automatizada de calcular el promedio y la SD teniendo en cuenta n diferentes, para que esté correctamente alineado/formateado.
Debe haber una manera fácil de hacerlo, pero ahora mismo no tengo ni idea. -_-
Agradecería cualquier sugerencia.
Respuesta1
Esto no es difícil si supone que la Columna Aestá ordenada, por lo que estamos tratando con rangos contiguos en la Columna B
(que es lo que sugieren los aspectos visuales de su pregunta). Configure Columna Ecomo columna auxiliar con esta fórmula:
E2→=IF(A2=A3, E3, ROW())
Para cada fila, esto identifica la última fila del rango en el que se encuentra la fila actual. Luego puede obtener los resultados que desea con
B2→=IF(A1=A2, "", AVERAGE(B2:INDIRECT("B" & E2)))C2→=IF(A1=A2, "", STDEV(B2:INDIRECT("B" & E2)))
(O utilice el método que desee para calcular la desviación estándar). Esto comprueba si es la primera fila de un rango. Si es así, utiliza la INDIRECT()función para construir un rango entre la celda actual y la última celda con el mismo valor de ID.
Y, por supuesto, puede ocultar Columna Eo utilizar alguna columna que no esté a la vista (por ejemplo, Z) como columna auxiliar. Tenga en cuenta que esta solución no utiliza fórmulas matriciales.
Respuesta2
Esto no es exactamente lo que pediste, pero usaría una tabla dinámica:
(Cambié el encabezado de la primera columna a ID y formateé la segunda y tercera columnas para que solo mostraran dos decimales; de lo contrario, es solo lo que se muestra en el Generador de tablas dinámicas).
Podrás conseguir lo que pediste colocando:
=IF($A2=$A1,"",AVERAGEIF($A2:$A19,$A2,$B2:$B19))
en C2, y:
=IF($A2=$A1,"",STDEV.P(INDIRECT("R"&MATCH($A2,$A:$A,0)&"C2:R"&MATCH($A2,$A:$A,1)&"C2",0)))
en D2 y llenando ambas columnas hacia abajo. El IF externo en cada fórmula es colocar el valor solo en la primera fila que contiene una ID particular. El resto de la fórmula C2 debe ser sencillo: PROMEDIOSI promedia números para los cuales un criterio específico es verdadero. En este caso, busca en la primera columna, selecciona números con el mismo valor que el valor de la fila actual en la primera columna y luego promedia los números correspondientes en la segunda columna.
Desafortunadamente, no existe "STDEVIF" (al menos en Excel 2011 en Mac, tal vez sí lo haya en cualquier programa de hoja de cálculo que esté usando. Si es así, utilícelo en lugar de PROMEDIO en la fórmula C2), por lo que debe ser astuto. :-). El enfoque es encontrar el rango de celdas cuya desviación estándar desea, construir una referencia a esas celdas y luego pasar esa referencia a STDEV.P. El rango se construye encontrando elprimerofila en la columna 1 con el mismo valor que el valor en la fila actual en la columna 1, luego encontrar elúltimofila en la columna 1 con el mismo valor que el valor en la fila actual en la columna 1. Esos dos valores delinean la parte superior e inferior del subrango de la columna 1 que desea usar, así que construya una referencia de estilo R1C1 en una cadena, use INDIRECTO para convertirlo en una referencia real, luego páselo a STDEV.P. ¡Simple! :-) Ok, es un poco horrible, pero funciona.
Respuesta3
Excel no tiene una funcionalidad como esta incorporada. Necesitaría usar subtotales o tablas dinámicas que no hacen lo que busca.
Para construir la tabla con fórmulas utilice las siguientes dos funciones.
En C2 poner
=IF(A2<>A1,AVERAGEIF($A$2:$A$13,A2,$B$2:$B$13),"")
En D2 poner y entrar pulsandoctrl+shift+enter
=IF(A2<>A1,STDEV(IF($A$2:$A$13=A2,$B$2:$B$13)),"")
Luego copia estas fórmulas
El IF(A2<>A1... al principio básicamente dice que solo muestra algo si la Columna A difiere entre esta fila y la anterior.
Averageif funciona exactamente como usted cree que debería.
La columna D es una fórmula matricial, por lo que primero ingresa y realiza la declaración if en cada celda del rango y devolverá una matriz similar a (1,14,13,3,FALSE,FALSE...) para cada celda y luego calcula la desviación estándar de esto, que básicamente debería ignorar los valores FALDOS.
Este método supone que los datos están ordenados por ID. Los cálculos de promedio y desarrollo estándar serían correctos si no estuvieran ordenados, pero aparecerían cada vez que cambie el ID, no solo el primero.