Chris tiene una gran cantidad de datos en una hoja de trabajo y quiere analizar los datos basándose en diferentes agrupaciones dentro de ella. Por ejemplo, tiene datos en las celdas A2: B36001, donde la fila 1 contiene los encabezados de columna Hora y Señal. Quiere dividir los datos en grupos que constan de un número arbitrario de valores secuenciales y luego extraer, para cada grupo, un valor medio para el Tiempo, un valor medio para la Señal y una desviación estándar para la Señal.

La forma más sencilla de manejar este tipo de requisitos es agregar una columna que se usa para indicar un número de grupo para cada fila. Siga estos pasos:

  1. Coloque el encabezado Grupo en la celda C1.

  2. En la celda E1 ingrese el número de valores que deben estar en cada grupo.

Por ejemplo, si desea que cada grupo contenga 10 valores secuenciales, ingrese el número 10 en la celda E1.

  1. En la celda C2 ingrese esta fórmula: = INT FILA () – FILA ($ C $ 2 / $ E $ 1) +1. Copie la fórmula de la celda C2 en el rango C3: C36001. La columna C ahora contiene un «número de grupo» para cada fila, según el valor de la celda E1.

Si E1 es 10, terminas con 3600 grupos, del 1 al 3600. Si E1 es 100, terminas con 360 grupos, del 1 al 360.

Con los números de grupo configurados, está listo para realizar el análisis. Hay varias formas de hacer esto. Una forma es utilizar las capacidades de subtotal de Excel. Seleccione una de las celdas en el área de datos y siga estos pasos:

  1. Elija Subtotales en el menú Datos. Excel muestra el cuadro de diálogo Subtotal.

  2. Cambie la lista desplegable En cada cambio en a Grupo.

  3. Cambie la lista desplegable Usar función para indicar el tipo de estadística que desea calcular para cada grupo.

  4. Cambie el área Agregar subtotal a para que solo se seleccione Hora o Señal, según corresponda.

  5. Haga clic en Aceptar.

Excel agrupa y subtotaliza los datos, como se indica. (Este proceso puede tardar un tiempo dependiendo del tamaño de sus grupos). Puede ocultar los detalles y mostrar solo los subtotales haciendo clic en el 2 pequeño (con el cuadro alrededor) en el área de contorno a la izquierda de la hoja de trabajo. Si más tarde desea cambiar lo que se calcula, o si necesita cambiar el número de elementos en cada grupo, simplemente elimine los subtotales (usando el botón en el cuadro de diálogo Subtotal) y repita los pasos anteriores.

Otra forma de derivar las estadísticas de sus datos es utilizar una tabla dinámica. Asegúrese de que no haya subtotales en los datos y seleccione una celda dentro de los datos. Luego sigue estos pasos:

  1. Muestre la pestaña Insertar de la cinta.

  2. Haga clic en la herramienta de tabla dinámica. (Esta herramienta es la primera a la izquierda de la pestaña Insertar). Excel muestra el cuadro de diálogo Crear tabla dinámica.

  3. Haga clic en Aceptar. (Las opciones predeterminadas en el cuadro de diálogo están bien). Excel crea una tabla dinámica en blanco y muestra una lista de campos a la derecha de la hoja de trabajo.

  4. Arrastre el campo Grupo al área Etiquetas de fila, justo debajo de la lista de campos.

  5. Arrastre el campo Hora al área Valores, justo debajo de la lista de campos.

  6. Arrastre el campo Señal al área Valores, justo debajo de la lista de campos.

  7. Arrastre el campo Señal, una vez más, al área de Valores. La tabla dinámica debería mostrar ahora «Recuento de tiempo», «Suma de señal» y «Suma de señal2».

  8. En el área de Valores, haga clic en la etiqueta «Recuento de tiempo». Excel muestra un menú contextual.

  9. Elija Configuración de campo de valor. Excel muestra el cuadro de diálogo Configuración de campo de valor.

  10. En la lista Resumir campo de valor por, elija Promedio.

  11. Haga clic en Aceptar. Las etiquetas de «Recuento de tiempo» cambian a «Promedio de tiempo».

  12. En el área de valores, haga clic en la etiqueta «Suma de la señal». Excel muestra un menú contextual.

  13. Elija Configuración de campo de valor. Excel muestra el cuadro de diálogo Configuración de campo de valor.

  14. En la lista Resumir campo de valor por, elija Promedio.

  15. Haga clic en Aceptar. Las etiquetas de «Suma de señal» cambian a «Promedio de señal».

  16. En el área de valores, haga clic en la etiqueta «Suma de señal2». Excel muestra un menú contextual.

  17. Elija Configuración de campo de valor. Excel muestra el cuadro de diálogo Configuración de campo de valor.

  18. En la lista Resumir campo de valor por, elija StdDev.

  19. Haga clic en Aceptar. Las etiquetas de «Suma de señal» cambian a «StdDev of Signal».

Ahora tiene los datos deseados. Si necesita cambiar el número de elementos de datos en cada grupo, simplemente vuelva a la hoja de trabajo de datos y cambie la celda E1 a un valor diferente. Luego puede volver a la tabla dinámica, mostrar la pestaña Opciones de la cinta y hacer clic en el botón Actualizar.

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (8628) se aplica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 y Excel en Office 365. Puede encontrar una versión de este consejo para la interfaz de menú anterior de Excel aquí:

link: / excel-Calculating_Statistical_Values_on_Different-Sized_Subsets_of_Data [Cálculo de valores estadísticos en subconjuntos de datos de diferentes tamaños].