Cómo agrupar filas y columnas con contornos y subtotales en Excel
Si es un analista de datos profesional, es probable que maneje varias hojas de trabajo en libros de Excel. Hay miles de columnas y filas en un archivo de Excel, lo que dificulta la extracción de datos significativos.
Sin embargo, las funciones de Excel como el esquema, los datos de grupo y los subtotales en una lista de datos son esenciales para analizar los datos comerciales y presentar informes impresionantes a la audiencia o al cliente.
Cómo dar formato a sus datos para agrupar columnas en Excel
Antes de seguir adelante para crear impresionantes visualización de datos usando el Excel función subtotal y el grupo de auto Excel, es necesario reestructurar los datos en bruto.
Para agregar un subtotal en los datos de Excel, primero deberá ordenar la lista de datos. Luego, puede usar la función Ordenar de Excel de la siguiente manera:
- Haga clic en el encabezado de cualquier columna que desee ordenar.
- En la cinta de la pestaña Inicio , busque Edición .
- Ahora, haga clic en Ordenar y filtrar y luego elija Ordenar de la A a la Z , Ordenar de la Z a la A o Orden personalizado , como mejor le parezca.
Cuando desee agrupar columnas en Excel, asegúrese de que la hoja de trabajo contenga conjuntos de datos con encabezados de columna únicos, sin columnas o filas vacías y subtotales para cada subconjunto de filas. Después de la reestructuración, sus datos deberían verse como se muestra en esta imagen:
Puede descargar esta hoja de cálculo de Excel gratuita para practicar la función Esquemas y subtotales antes de trabajar con datos de la vida real.
Cómo agregar subtotales en conjuntos de datos de Excel
La función de subtotal de Excel le permite calcular totales generales y subtotales para una columna en una lista de datos. No es necesario formatear miles de columnas y filas, ya que Excel reestructurará la lista de datos automáticamente.
La función Subtotal de Excel agrupa automáticamente los datos y podrá utilizar funciones como COUNT, SUM, AVERAGE, Max, Min, Product, Count Numbers, StdDev, etc. Para agregar subtotales a su hoja de cálculo de Excel, siga estos pasos:
- Ordene los datos de la hoja de trabajo de acuerdo con el encabezado de la columna para la que desea agregar subtotales.
- En la hoja de trabajo de ejemplo actual, la columna Categoría es la base para la clasificación de datos.
- Ahora, seleccione cualquier celda dentro del rango de datos.
- En la cinta , haga clic en la pestaña Datos .
- En la sección Esquema dentro de la pestaña Datos , haga clic en Subtotal .
- Excel seleccionará el conjunto de datos completo dentro de la hoja de trabajo y verá el cuadro de diálogo Subtotal .
7. En el cuadro de diálogo Subtotal , elija la columna deseada haciendo clic en el menú desplegable en Cada cambio en .
8. Ahora, debe configurar el campo Usar función seleccionando una opción del campo desplegable. Puede elegir cualquier cosa entre SUMA , PROMEDIO , Máx , Mín , etc.
9. Excel colocará el subtotal calculado en la celda que elija en Agregar subtotal a .
10. Haga clic en Aceptar y Excel agregará automáticamente los subtotales junto con los esquemas .
Los grupos delineados automatizados mostrarán subtotales debajo de cada grupo, según su selección de encabezado de columna. Por ejemplo, en este tutorial, las categorías de alimentos como barras , galletas , galletas saladas y bocadillos son grupos separados. El precio total de cada grupo está disponible en la columna Precio total (USD) .
Cómo agrupar en Excel para visualizar datos anidados
Excel le permite crear un esquema que consta de hasta ocho niveles. Es una herramienta de uso frecuente cuando necesita resumir una larga lista de datos como un grupo de encabezados individuales. El contorno más a la izquierda o exterior comienza con el Número 1 (Uno) y, a medida que avanza hacia la derecha, puede subir al Número 8 (ocho) .
Puede visualizar rápidamente datos anidados como un grupo o datos de resumen o incluso revelar los detalles de cada grupo utilizando las filas de grupo de Excel o la herramienta de esquema. Excel le permite crear el esquema de columnas, el esquema de filas y el esquema de filas y columnas.
Grupo automático de Excel para crear un esquema
Cuando necesite organizar listas de datos anidadas más pequeñas, puede utilizar la herramienta Esquema para crear grupos automáticos. Puedes probar estos pasos:
- Haga clic en cualquier celda dentro de los datos de la fila que necesita agrupar.
- Ahora, vaya a la pestaña Datos en la cinta de Excel.
- Dentro de la sección Esquema , encontrará el menú desplegable Grupo .
- Haga clic en Grupo y luego seleccione Contorno automático .
Notarás que la función de grupo organizó las filas perfectamente. También verá las barras de esquema en el lado izquierdo que representan múltiples estructuras de nivel de datos.
Puede explorar múltiples opciones de visualización interactuando con el botón Niveles como Número 1 (Uno), Número 2 (Dos), Número 3 (Tres), etc.
1. Si selecciona el nivel más bajo, Número 1 (Uno) , verá datos mínimos dentro de la hoja de trabajo. En el ejemplo actual, es el costo total general de todos los alimentos.
2. Cuando haga clic en el siguiente nivel, solo verá las filas de subtotales junto con la fila de totales generales. No verá otras listas de datos detalladas dentro de la hoja de cálculo de Excel.
3. Ahora puede seleccionar el nivel más alto o el Número 3 (Tres) en los datos de ejemplo actuales para expandir todas las filas y columnas junto con la vista delineada.
También puede utilizar los pasos anteriores para agrupar columnas en Excel. Formatee los conjuntos de datos en consecuencia para que el comando de esquema automático de Excel pueda reconocer los subtotales.
Comando de grupo de Excel para agrupar filas manualmente
Cuando la hoja de trabajo contiene conjuntos de datos complejos como más de un nivel de información, debe usar el comando Agrupar para la agrupación manual.
- Seleccione un subconjunto de datos que desee incluir en un grupo y luego haga clic en el comando Grupo en la pestaña Datos de la cinta de Excel.
- Para crear otro grupo con otro subconjunto de datos, seleccione las filas y luego haga clic en el comando Agrupar o use esta tecla de método abreviado Shift + Alt + Flecha derecha .
3. Si desea crear un grupo anidado, elija todas las filas de detalles dentro de la fila de resumen relacionada y luego use el atajo Shift + Alt + Flecha derecha .
Puede ocultar filas detalladas utilizando la opción de contraer filas de Excel: el signo menos (-) en los contornos para simplificar los datos anidados complejos. Cuando contrae una barra de contorno, el signo menos (-) cambia al signo más (+) para mostrar las filas más tarde.
Además, puede usar los botones Mostrar detalles y Ocultar detalles en la sección Esquema de la pestaña Datos en la cinta de Excel.
Hay estilos incorporados para grupos y esquemas dentro de la hoja de cálculo de Excel. Para aplicar estilos , haga clic en el icono del iniciador del cuadro de diálogo (una pequeña flecha desplegable) en la sección Contornos de la pestaña Datos .
Verá un cuadro de diálogo Configuración . Haga clic en la casilla de verificación para estilos automáticos y luego haga clic en el botón Aplicar estilos .
Aplique un toque profesional a sus informes de Excel
Con este tutorial de Excel sobre subtotales y esquemas de Excel (grupos), podrá organizar datos complejos en una tabla fácil de entender. Con las hojas de cálculo de Excel, puede ver datos anidados desde múltiples perspectivas para extraer información valiosa de datos.