Cómo resumir datos en Excel: métodos y herramientas

Si tienes tres o más filas de datos, es muy probable que necesites resumir esos valores de alguna manera. La sumaarización es una de las tareas más comunes que llevamos a cabo en Excel. El término "sumarización" implica agrupar. Ese grupo puede ser el conjunto de datos completo o varios grupos, pero el concepto es el mismo. Resumir el conjunto de datos completo como un único grupo es fácil, y probablemente no necesites ayuda para implementarlo. Resumir varios grupos requiere un poco más de trabajo.

En este artículo, te mostraré tres métodos de sumarización: el uso de la función Filtro, el uso de la función Subtotal y la generación de una tabla dinámica (PivotTable). Agregaremos un requisito de múltiples grupos para complicar las cosas. Específicamente, utilizaremos estas herramientas para obtener la edad promedio por grupos de los registros mostrados en la Figura A.

Figura A

Método de Filtro

Comenzaremos con la función de filtro incorporada de Excel. Para mejor o peor, solo puedes filtrar un grupo a la vez. Lo primero que necesitas es una función de promedio. Podrías considerar usar PROMEDIO(), pero no lo hagas. Funcionará muy bien para evaluar todo el conjunto de datos, pero no se actualizará cuando filtres ese conjunto de datos. En su lugar, usa la función SUBTOTAL(), que se actualizará a medida que filtres los registros.

Esta función usa un argumento para determinar cómo evaluar matemáticamente los valores de referencia utilizando la sintaxis:

Cómo imprimir boletos numerados secuencialmente usando Publisher y Excel

SUBTOTAL(númeroDeFunción, ref1, ref2, ...)

donde númeroDeFunción es uno de los valores en la Tabla A.

Figura B muestra el resultado de insertar algunas filas encima del conjunto de datos y agregar la función =SUBTOTAL(1,B4:B13).

Figura B

Utilizar SUBTOTAL().

Tabla A

El poder oculto del Surface 3: Micro USB al rescate

Hay algunos comportamientos que debes conocer al aplicar SUBTOTAL() a tu trabajo:

• SUBTOTAL() ignora los subtotales anidados para evitar el recuento doble.
• SUBTOTAL() incluye los valores en las filas ocultas por el comando Ocultar filas. Si deseas que SUBTOTAL() ignore los valores en las filas ocultas de esta manera, agrega el valor 100 al número de función. Por ejemplo, en lugar de usar 1 para calcular el promedio, deberías usar 101.
• SUBTOTAL() ignora las filas ocultas por un filtro, independientemente del número de función. Eso es lo que estamos haciendo.
• SUBTOTAL() no admite referencias a varias hojas.

Después de insertar la función SUBTOTAL(), estás listo para filtrar de la siguiente manera:

1. Haz clic en cualquier celda dentro del conjunto de datos y luego haz clic en Filtro en la pestaña Datos (en el grupo Ordenar y filtrar). Otra opción es presionar [Ctrl]+[Shift]+[L]. En Excel 2003, elige Filtro en el menú Datos y luego elige Filtro automático. Excel mostrará desplegables en la celda del encabezado para cada columna.
2. Haz clic en el desplegable de Edad.

El filtrado es fácil de implementar, pero debes filtrar cada grupo cada vez que lo necesites. Por otro lado, es útil cuando te piden detalles inesperados. Por ejemplo, la próxima vez que estés en una reunión y el jefe pregunte: "¿Cuál es el promedio para el grupo de 20-29 años?", tendrás la respuesta con solo unos pocos clics. ¡Impresionante!

Método Subtotal

El filtrado está bien para ver rápidamente un solo grupo. Si deseas ver todos los grupos evaluados a la vez, utiliza la función Subtotal de Excel. Primero, tendrás que ordenar el conjunto de datos, lo cual puede ser problemático para algunos. Además, esta función necesita un punto de referencia, un valor que determine dónde comienza y termina un grupo. En este momento, no hay nada para agrupar los datos; aún no hemos definido los grupos. Siguiendo el ejemplo de filtrado, basaremos nuestros grupos de edad en 10, comenzando desde 0: 0-9, 10-19, 20-19 y así sucesivamente.

Cómo crear presentaciones nuevas utilizando contenido existente en PowerPoint

En este punto, necesitamos una forma de relacionar cada edad con su respectivo grupo. Para ello, agrega la tabla de datos de grupos que se muestra en la Figura F y utiliza la función BUSCARV() para devolver la etiqueta de grupo de edad adecuada para cada registro.

Figura F

Crea una tabla de grupos de edad.

Los valores en la columna E representan la edad más baja de cada grupo, lo cual se ajusta muy bien a la función BUSCARV. Las etiquetas en la columna F incluyen la edad más baja y la edad más alta, como texto. Deberás generar esta tabla manualmente, aunque después de ingresar 0 y 10 en la columna E, puedes usar el autofill para completar esa columna. En Excel 2013, puedes usar el Autofill inteligente para ayudar a completar la columna F. Para obtener más información sobre Autofill inteligente, lee "Excel 2013 Autofill inteligente anticipa tus necesidades". (No esperes la perfección, pero es útil).

Para agregar las etiquetas de grupo (columna C) al conjunto de datos, ingresa la siguiente fórmula para el primer registro (C4) y copia hasta los registros restantes:

=BUSCARV(B4,$E$3:$F$13)

Esta función hace referencia a los valores de edad en la columna B y revisa la primera columna (columna E) en la tabla de datos de grupos (columnas E y F) en busca del primer valor que coincida o sea menor que el valor de edad. Luego, devuelve el valor correspondiente en la columna F. Por ejemplo, 3 es el primer valor de edad. El segundo valor en la columna E es 10, por lo que BUSCARV evalúa el valor anterior de 0 y devuelve la etiqueta de grupo 0-9. Para que funcione correctamente, los valores de búsqueda en la columna E deben estar en orden numérico. Como mencioné antes, los valores de agrupación también deben estar ordenados.

Cómo crear y analizar datos con PivotTables en Excel

Ahora evaluemos los datos por los nuevos valores de agrupación utilizando la función Subtotal de Excel de la siguiente manera:

1. Haz clic en cualquier celda de la columna Edad, ya que debes ordenar estos valores para que funcione la función Subtotal.
2. Haz clic en Ordenar y filtrar en el grupo Edición (en la pestaña Inicio).
3. Elige Ordenar de menor a mayor. En Excel 2003, utiliza Ordenar de forma ascendente en la barra de herramientas Estándar.
4. Después de ordenar el conjunto de datos, haz clic en la pestaña Datos y luego haz clic en Subtotal en el grupo Esquema. En Excel 2003, elige Subtotales en el menú Datos.
5. En el primer desplegable, elige Grupo para identificar los valores de agrupación.
6. Elige Promedio en el desplegable Usar función.

Para ver solo las filas de resumen, como se muestra en la Figura I, haz clic en 2 en el panel de Subtotal a la izquierda. Haz clic en 1 para mostrar la fila de total y haz clic en 3 para mostrar todo. De manera intencional, dejé un valor de edad en blanco para que puedas ver el error en B20. En este caso, no es perjudicial. Para eliminar las filas de subtotal, haz clic dentro del conjunto de datos. Haz clic en Subtotales en la pestaña Datos y luego haz clic en Eliminar todos en el cuadro de diálogo resultante.

Figura I

Ocultar los registros de detalle.

Tabla dinámica

Insertar una tabla dinámica es otra forma de evaluar promedios agrupados. Una tabla dinámica te permite reorganizar y resumir datos de una manera significativa y visual, sin cambiar la estructura de los datos subyacentes. Para generar una tabla dinámica utilizando el formato .xlsx, sigue estos pasos:

Cómo utilizar las herramientas de análisis de Excel para tomar decisiones informadas

1. Haz clic en cualquier lugar dentro del conjunto de datos. No necesitas ordenar nada primero, pero necesitas la tabla de datos de grupos y las etiquetas de agrupación (función BUSCARV) de la sección anterior.

Si utilizas Excel 2003, selecciona PivotTable y PivotTable Chart del menú Datos y luego haz clic en Finalizar. Utilizando la lista de campos de la tabla dinámica, agrega Edad al Área de datos. Haz clic derecho en el campo resultante, elige Configuración de campo, selecciona Promedio y luego haz clic en Aceptar. A continuación, agrega el campo Grupo al Área de filas.

En este punto, es posible que hayas terminado. Sin embargo, podrías agregar los registros de detalle a la tabla arrastrando el campo Nombre a la lista de Etiquetas de filas, como se muestra en la Figura N.

Figura N

Mostrar los registros de detalle.

La tabla dinámica puede parecer la ruta más eficiente. Puedes ubicarla en otra hoja de cálculo y utilizarla de forma independiente a los datos. Puedes mostrar fácilmente los registros de detalle o no. Por otro lado, si actualizas los valores en el conjunto de datos, debes recordar actualizar la tabla dinámica para evaluar esos nuevos valores. Esa es la principal desventaja: las personas simplemente olvidan actualizar la tabla dinámica después de cambiar los datos subyacentes.

Resumiendo grupos

Tres características incorporadas te brindan resultados rápidos al resumir datos por múltiples grupos: puedes filtrar, utilizar la función Subtotal y generar una tabla dinámica. Todas tienen sus pros y sus contras. Los datos y la forma en que deseas evaluarlos e informarlos deben guiar tu elección. También podrías utilizar fórmulas, las cuales no he mostrado en este artículo.

Cómo crear presentaciones visuales impresionantes con SmartArt en PowerPoint

Envíame tu pregunta sobre Office

Respondo preguntas de los lectores sobre Microsoft Office cuando puedo, pero no hay garantías. Al contactarme, sé lo más específico posible. Por ejemplo, "Por favor, soluciona los problemas de mi libro de trabajo y corrige lo que está mal" probablemente no recibirá una respuesta, pero "¿Puedes decirme por qué esta fórmula no devuelve los resultados esperados?" podría hacerlo. Por favor, menciona la aplicación y la versión que estás utilizando. No recibo ningún reembolso de Newsmatic por mi tiempo o experiencia, ni solicito una tarifa a los lectores. Puedes contactarme en [email protected].

En Newsmatic nos especializamos en tecnología de vanguardia, contamos con los artículos mas novedosos sobre Microsoft, allí encontraras muchos artículos similares a Cómo resumir datos en Excel: métodos y herramientas , tenemos lo ultimo en tecnología 2023.