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 Subtotales.

  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. Puede ocultar el detalle (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 luego desea cambiar lo que se calcula, o si necesita cambiar la cantidad de elementos en cada grupo, simplemente elimine los subtotales (Datos | Subtotales | Eliminar todo) 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. Elija Informe de tabla dinámica y gráfico dinámico en el menú Datos. Excel inicia el Asistente para tablas dinámicas y gráficos dinámicos.

  2. Haga clic en Siguiente. (Las selecciones predeterminadas en el paso 1 son correctas). Se muestra el paso 2 del Asistente para tablas dinámicas y gráficos dinámicos.

  3. Debe seleccionarse todo el rango de sus datos (A1: C36001). Haga clic en Siguiente. Se muestra el paso 3 del Asistente para tablas dinámicas y gráficos dinámicos.

  4. Asegúrese de seleccionar Nueva hoja de trabajo y luego haga clic en Finalizar. Excel crea una tabla dinámica en blanco y muestra una lista de campos.

  5. Arrastre el campo Grupo al área Fila.

  6. Arrastre el campo Hora al área de datos.

  7. Arrastre el campo Señal al área de datos.

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

para cada grupo.

  1. En el área de Datos, haga clic con el botón derecho en una de las etiquetas de «Recuento de tiempo». Excel muestra un menú contextual.

  2. Elija Configuración de campo en el menú contextual. Excel muestra el cuadro de diálogo Campo de tabla dinámica.

  3. En la lista Resumir, elija Promedio.

  4. Haga clic en Aceptar. Todas las etiquetas de «Cuenta de tiempo» cambian a «Promedio de tiempo».

  5. En el área de datos, haga clic con el botón derecho en una de las etiquetas «Suma de la señal». Excel muestra un menú contextual.

  6. Elija Configuración de campo en el menú contextual. Excel vuelve a mostrar el cuadro de diálogo Campo de tabla dinámica.

  7. En la lista Resumir, elija Promedio.

  8. Haga clic en Aceptar. Todas las etiquetas de «Suma de señal» cambian a «Promedio de señal».

  9. En el área de datos, haga clic con el botón derecho en una de las etiquetas «Suma de señal2».

Excel muestra un menú contextual.

  1. Elija Configuración de campo en el menú contextual. Excel vuelve a mostrar el cuadro de diálogo Campo de tabla dinámica.

  2. En la lista Resumir, elija StdDev.

  3. Haga clic en Aceptar. Todas las etiquetas de «Suma de señal2» cambian a «StdDev of Signal2».

Ahora sabe cómo desea obtener los datos. Es posible que prefiera una configuración que muestre solo una fila por categoría para sus datos. Si es así, simplemente haga clic en el encabezado de Datos y mantenga presionado el botón del mouse mientras mueve lentamente el mouse hacia la derecha. A medida que mueve el puntero del mouse al área del encabezado Total, un pequeño icono cerca del puntero del mouse muestra un «cambio» en el diseño. Suelte el botón del mouse y tendrá solo una fila para cada grupo en sus datos.

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. A continuación, puede volver a la tabla dinámica, hacer clic con el botón derecho y elegir Actualizar datos.

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

Este consejo (2771) se aplica a Microsoft Excel 97, 2000, 2002 y 2003. Puede encontrar una versión de este consejo para la interfaz de cinta de Excel (Excel 2007 y posteriores) aquí:

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