Agrupar una selección en una tabla dinámica permite reunir manualmente varios elementos bajo una categoría nueva, aunque esa categoría no exista en la base de datos original. Esta función resulta útil para analizar productos por secciones, vendedores por grupos, ciudades por regiones u otros conjuntos definidos según las necesidades del análisis.
La agrupación se realiza directamente sobre la tabla dinámica, sin modificar los datos de origen. Al crear un grupo, la tabla dinámica incorpora un nuevo campo que puede utilizarse, quitarse o reorganizarse desde la lista de campos.
Qué significa agrupar una selección
Si no contamos con un criterio cronológico o numérico en una tabla dinámica, quizás necesitemos agrupar de una manera personalizada. Eso es lo que en Excel 2016 para Mac se llama "Agrupar por selección dentro de las tablas dinámicas".
La función de agrupar datos nos permite trabajar directamente sobre una tabla dinámica realizando agrupaciones de datos que como tal no se contemplan en la base de datos. De esta forma poder establecer las agrupaciones que queramos o necesitemos realizar ad hoc sin necesidad de tocar la base de datos, directamente desde la tabla dinámica.
Esto por ejemplo nos permitiría el poder agrupar en una misma línea varias filas de datos, o en una misma columna, varias columnas en una solo, agrupando y sumando los datos.
Cuándo conviene utilizar la agrupación por selección
La agrupación por selección es especialmente apropiada cuando los elementos no pueden organizarse mediante intervalos numéricos o periodos de fecha. Por ejemplo, podemos agrupar productos que pertenecen a una misma sección, vendedores que comparten una categoría comercial o ciudades que corresponden a una comunidad autónoma.
Al hacer una agrupación solo tendríamos que cambiar el nombre del grupo creado y poner el de la Comunidad que estemos creando. Así podríamos repetir el proceso, y tendremos una nueva forma de ver los datos, un concepto que no está recogido en la base de datos pero que, si ya tenemos creado como campos de tabla dinámica, disponibles para poner o quitar cuando veamos oportuno.
Puedes crear agrupaciones en filas como hemos visto, pero puedes crear agrupaciones de datos en columnas también, usando el mismo procedimiento.
Preparar la tabla dinámica
Antes de crear una agrupación, conviene disponer de una tabla dinámica que muestre los elementos que se van a seleccionar. Los campos de texto suelen colocarse en el área Filas, mientras que el campo que se desea resumir suele situarse en el área Valores.
Use la sección de campos de la lista de campos para agregar campos a la tabla dinámica, activando la casilla junto a los nombres de campo para colocar los campos en el área predeterminada de la lista de campos.
Use la sección de las áreas (en la parte inferior) de la lista de campos para reorganizar los campos de la forma que desee arrastrándolos entre las cuatro áreas.
Si no ve la lista de campos al hacer clic en la tabla dinámica, ábrala haciendo clic en cualquier lugar de la tabla dinámica. A continuación, abra las Herramientas de tabla dinámica en la cinta de opciones y haga clic en Analizar>lista de campos.
Cómo agrupar elementos seleccionados
Para agrupar elementos seleccionados, mantenga presionada la tecla Ctrl y seleccione dos o más valores. Haga clic con el botón derecho y seleccione Agrupar.
También es posible seleccionar los elementos mediante una selección discontinua, pulsando Ctrl, y después activar desde el menú Datos > Grupo y esquema la opción Agrupar.
En Excel para Mac, la selección se realiza como si fuera una celda normal, haciendo clic y manteniendo pulsada la tecla Comando para añadir a la selección el resto de elementos.
- Haga clic en un elemento de la tabla dinámica.
- Mantenga pulsada la tecla Ctrl en Windows o la tecla Comando en Mac.
- Seleccione dos o más elementos que deban pertenecer al mismo grupo.
- Haga clic con el botón derecho sobre la selección.
- Seleccione Agrupar.
En la tabla dinámica, haga clic con el botón derecho en un valor y seleccione Agrupar.
En esta paso sucedieron dos cosas. La primera es que todos los elementos seleccionados se agruparon dentro de un solo grupo llamado Grupo1. La segunda cosa que sucedió es que se creó un nuevo campo llamado Vendedor2 que puedes observar en la lista de campos.
Ejemplo: agrupar productos por sección
En las tablas dinámicas se pueden agrupar diferentes campos de forma que podamos analizar la información por categorías que no estaban inicialmente previstas en nuestra tabla de datos original. Por ejemplo, podemos agrupar los Productos por la sección a que corresponden, Carnes o Verduras.
Para exponer su funcionamiento, comencemos creando una tabla de datos simple que muestre la Suma de Ventas por Producto.
Seleccionaremos mediante selección discontinua, si es preciso pulsando Ctrl, los elementos de la carnicería, a saber, Cerdo, Cordero, Pollo y Vacuno.
Se creará una nueva etiqueta de campo, de nombre Producto2, que mostrará un dato Agrupar1, correspondiente a los productos de Carnes más el resto de productos sin agrupar aún.
Seguidamente seleccionaremos los productos de Verduras.
Atención: debemos hacerlo en la columna Producto, no en la columna Producto2, pues de lo contrario, crearemos un tercer grupo.
Se mostrará un nuevo grupo denominado Agrupar2.
| Elemento original | Grupo creado |
|---|---|
| Cerdo, Cordero, Pollo y Vacuno | Agrupar1 |
| Productos de Verduras | Agrupar2 |
| Campo generado | Producto2 |
Ejemplo: agrupar vendedores por selección
En una tabla dinámica simple, por ejemplo, donde tenemos la información por vendedores de una empresa, vamos a hacer una agrupación de varios vendedores, clasificándolos en dos grupos según las ventas realizadas de cada uno de los vendedores.
Así establecemos 3 categorías donde agrupamos a los vendedores según la importancia e impacto que tienen para la empresa.
Vamos a agrupar entonces a todos los vendedores de la empresa en 3 grupos, según las ventas realizadas.
Ordenar previamente la tabla dinámica
Ordenar la tabla dinámica no es un paso necesario que debas realizar si o si a la hora de agrupar datos en Excel. Sin embargo, en el ejemplo que vamos a explicar, queremos crear grupos de personas en la tabla dinámica en función de si han realizado más o menos ventas.
En cualquier caso, ordenar la tabla dinámica es un paso importante, pues siempre te ayudará a leer mejor los datos.
Nos situamos en una celda de la columna “VENTAS TOTALES” y hacemos clic derecho con el ratón. Seleccionamos “ordenar” y después “ordenar de mayor a menor”.
El criterio que se usa para poder ordenar una tabla dinámica dependerá de la situación, de los datos que quieras destacar, de cuál sea el valor principal para tu negocio o para el trabajo que vayas a realizar.
En nuestro caso ficticio, al ser datos de una empresa, establecemos como valor principal o KPI más importante las ventas, y por esta razón ordenamos según este criterio.
Ahora ya tenemos nuestra tabla dinámica ordenada por ventas, como en la imagen de abajo, y podemos ver de forma clara quiénes son las personas de la empresa, en este caso vendedores, que generan mayor volumen de ventas, y que comerciales venden menos.
Definir los grupos
Ahora como hemos comentado queremos agrupar a los vendedores según el volumen de ventas que ha hecho cada uno, clasificándolos en tres grupos:
- Un grupo para agrupar todos los vendedores que han superado más de 30 ventas.
- Otro grupo para los que han generado más de 16 ventas, pero menos de 30.
- Por último, los vendedores restantes que serían los que han realizado menos de 16 ventas o menos.
Para realizar el primer grupo por tanto pulsamos la tecla Ctrl y hacemos clic en los nombres de Carla y Charles, que son vendedores en color rojo en la tabla, los que han realizado más de 30 ventas.
Después hacemos clic en el botón derecho y, en el menú, seleccionamos “Agrupar”.
Ahora ya podemos repetir el proceso para crear el grupo 2. Recuerda que sería seleccionar los nombres de los vendedores con la tecla Ctrl y con el ratón y después hacer clic derecho y agrupar.
Seleccionamos en el ejemplo todos los nombres de los vendedores que queremos incluir en el grupo 2.
Y volvemos a repetir de nuevo el proceso para crear el grupo 3.
Una vez agrupados, veremos una tabla compacta con un nuevo concepto o agrupación que hemos creado según el volumen de ventas.
El nuevo campo generado por la agrupación
Al crear una selección agrupada, la tabla dinámica incorpora un nuevo campo. En el ejemplo de los vendedores, el campo nuevo se llama Vendedores2, mientras que en el ejemplo de los productos se crea Producto2.
Para simplificar la tabla ahora es importante que, en el menú de campos de tabla dinámica, quitemos el campo “vendedores” ya que como veremos nos ha creado un nuevo campo de valor que se llama “Vendedores2”.
No es necesario que quites el campo de vendedores en la lista de campos, pero como te acabamos de explicar este paso te ayudará a que tu tabla dinámica sea más limpia, de esta forma en la tabla dinámica solo vas a ver en filas las agrupaciones que vas a crear.
Quitamos por tanto el campo “vendedores” y volvemos a ver la tabla con el formato inicial.
Si quieres volver a ver los datos de tu tabla por vendedor, solo tienes que quitar de la tabla dinámica “VENDEDORES2” y volver a poner “VENDEDORES”.
A partir de ahora podrás usar estas agrupaciones cuando quieras siempre usando el menú de Campos o lista de campos de tu tabla dinámica.
Podrás elegir este campo o quitarlo de la configuración de tu tabla dinámica cuando sea preciso, cuando lo necesites.
Cambiar el nombre de un grupo
Después de crear una agrupación, es posible sustituir nombres genéricos como Grupo1, Agrupar1 o Agrupar2 por nombres que describan mejor el contenido.
A continuación podremos cambiar los nombres mostrados tanto en la nueva etiqueta de campo como en las etiquetas de los grupos. Los queremos denominar Sección, Carnes y Verduras, respectivamente.
Cambiar el texto de una etiqueta de campo
La etiqueta de campo Producto2 no puede editarse como una celda normal.
- Selecciona la etiqueta con el teclado.
- Haz dos veces clic secundario sobre la misma; no es un doble clic.
- Escribe en la celda o en la barra de fórmulas la palabra Sección.
- Pulsa Intro.
Tras pulsar Intro, aparecerá Sección como nombre de campo.
Cambiar el texto de las etiquetas de grupo
Ahora nos situamos en la celda Agrupar1 y cambiamos su nombre a Carnes.
Repetimos el proceso para la celda Agrupar2 y cambiamos su nombre a Verduras.
En la versión 4.2 y 4.3, con la que estamos trabajando, debido a un error del programa, los grupos desaparecen cuando se les cambia el nombre.
Para subsanar este error, tras renombrar la etiqueta de grupo, actualizaremos la tabla dinámica y se mostrará todo con normalidad.
Al pulsar Intro, desaparecerá el grupo; si actualizamos la tabla se mostrará correctamente.
A partir de ahora, podemos trabajar con la etiqueta de campo Sección al igual que con cualquier otra etiqueta de campo, cambiar su área de ubicación, mostrar u ocultar subtotales, etc.
Asignar un nombre a un grupo en Excel
Seleccione el grupo.
Seleccione Analizar>configuración de campo.
En la pestaña Analizar de tabla dinámica, en Campo activo, haga clic en Configuración de campo.
Cambie el nombre personalizado al que quiera y seleccione Aceptar.
Agrupar elementos en columnas
Puedes crear agrupaciones en filas como hemos visto, pero puedes crear agrupaciones de datos en columnas también, usando el mismo procedimiento.
Los campos del área Columnas se muestran como Etiquetas de columna en la parte superior de la tabla dinámica.
En función de la jerarquía de los campos, las columnas se pueden anidar dentro de columnas que estén en una posición más alta.
Si tiene más de un campo en un área, puede cambiar su orden arrastrándolos a la posición que desee.
Agrupar campos numéricos
La agrupación de selección se diferencia de la agrupación numérica. Cuando los elementos representan números, pueden organizarse en intervalos definidos.
La agrupación de datos en una tabla dinámica puede ayudarle a mostrar un subconjunto de datos para analizar.
En el campo Agrupados por hemos introducido un 1000, el rango de nuestro intervalo.
Para los campos numéricos, indique un intervalo numérico para cada grupo.
Agrupar ventas en intervalos
Siguiendo con nuestro documento de trabajo, ahora queremos conocer la distribución de las distintas ventas. Queremos conocer esa distribución en intervalos de 1.000€.
Podemos conseguir esta información agrupando por el campo numérico de ventas, que recoge los importes de cada venta.
En vista diseño crearemos una tabla dinámica como la siguiente:
Como podemos observar, hemos llevado el campo Ventas al área Campos de fila, y de nuevo el campo Ventas al área Campos de datos; éste último lo hemos configurado para que la función de agrupamiento sea Cantidad.
Hemos conseguido conocer cuántas ventas existen por cada importe distinto, pero queremos agruparlas en intervalos de 1000€.
Seleccionamos una celda cualquiera de la columna Ventas e invocamos al diálogo Agrupamiento por cualquiera de los dos métodos vistos anteriormente.
Podemos mejorar el resultado. Definamos el Inicio > Manualmente en con el valor 1000.
Agrupar números de pedido
En el siguiente ejemplo verás una tabla dinámica que tiene la información de varios pedidos de compra que se han hecho a una empresa.
Lo que deseamos hacer es analizar la información en grupos de 10.
Para hacer este tipo de agrupación debo hacer clic derecho sobre cualquiera de los elementos de la columna que contiene los números de pedido y seleccionar la opción Agrupar.
Se mostrará el cuadro de diálogo Agrupar el cual me permitirá indicar el número inicial y final así como el tamaño de la agrupación que deseo.
Al pulsar el botón Aceptar podremos observar la información agrupada de acuerdo a la especificación hecha.
Agrupar edades por tramos
Esta herramienta nos puede resultar muy útil cuando necesitamos generar información por tramos, ya sea cifras de negocios, ventas, edad … etc.
Para el ejemplo de hoy voy a usar una lista de edad que pretendo agrupar por tramos de quince en quince años.
Una vez tenemos el listado, insertamos la tabla dinámica.
Con la tabla dinámica generada, vamos a mostrar en etiqueta de fila las edades y también en valores, resumiendo el campo de valores contando los elementos.
Una vez que ya tenemos la tabla con los elemento correctamente introducidos, tan solo tenemos que ir al comando «Agrupar», situado en la pestaña, y se desplegará un cuadro de diálogo para que podamos agrupar la información según nos convenga.
En este caso quiero mostrar las edades entre 0 y 85 años y que los tramos sean de 15 en 15 años.
El resultado es una sencilla forma de resumir datos en vuestros registros de objetivos y en los informes que tengáis que presentar.
Agrupar campos de fecha y hora
El comportamiento de la agrupación es distinta si se realiza sobre campos de texto, de número o de fecha/hora.
En este último caso la agrupación nos permitirá agrupar por años, meses, intervalos de X días, horas, etc.
Con la agrupación de tiempo, las relaciones entre los campos relacionados con el tiempo se detectan y agrupan de forma automática al agregar filas con campos de hora a una tabla dinámica.
Una vez agrupados, se puede arrastrar el grupo a una tabla dinámica e iniciar el análisis.
Agrupar fechas por meses y años
Vamos a diseñar una tabla dinámica para que nos muestre la información agrupada por años y por meses.
Primero crearemos una tabla dinámica que nos muestre la Suma de Ventas por Fecha.
A diferencia de cuando agrupamos datos de texto, para agrupar fechas bastará con situarnos en una celda cualquiera del campo Fecha.
Seguidamente, por alguno de los dos métodos vistos anteriormente, activaremos el diálogo Agrupación.
En este diálogo podremos configurar como necesitemos la agrupación de fechas.
De forma predeterminada, sugerirá agrupar por meses, pero podemos activar uno o más Intervalos de agrupación.
Nos permite seleccionar el Inicio y el Fin de los intervalos Automáticamente o Manualmente en fechas determinadas.
También podemos agrupar por Número de días.
Por ejemplo, si deseamos agrupar por semanas, lo haremos por 7 días; en este caso nos podría interesar determinar que inicie Manualmente en una fecha que sea lunes.
Si dejásemos la agrupación simplemente por Meses, si hubiera información de años diferentes, nos agruparía la información de todas las ventas de Enero, Febrero, etc. de cualquier año.
Así que en esos casos, como en este ejemplo, activaremos Meses y Años.
Tras Aceptar la tabla dinámica se mostrará como podemos ver a continuación.
Se ha agregado una nueva etiqueta de campo, denominada Años.
Bajo la etiqueta del campo original de Fecha ahora se muestran los meses.
Por regla general, al activar más de una opción de agrupamiento, en nuestro caso, meses y años, la unidad de menor jerarquía en el agrupamiento es la que heredará el nombre del campo original, Fecha, mientras que las jerárquicamente superiores presentarán etiquetas propias, Meses, Trimestres, Años.
Unidades disponibles para fechas y horas
Cuando tienes un dato de tipo fecha en una tabla dinámica es posible agrupar la información por cualquiera de las siguientes unidades de tiempo: Años, trimestres, meses, días, horas, minutos y segundos.
Las nuevas etiquetas de campos pueden reorganizarse también en el área Campos de fila, Campos de columna, o incluso en Campos de página.
Por ejemplo podemos mostrar Años en filas y Meses en columnas.
Mostrar subtotales por grupos
Podemos acceder a la configuración del campo Años desde la vista diseño de la tabla dinámica.
Para ello, desde la vista diseño haremos doble clic sobre la etiqueta Años en el área Campos de fila.
Se presentará el diálogo Campo de datos.
Seleccionando la opción Personalizado y eligiendo de la lista la opción Suma se mostrarán los subtotales para la etiqueta de campo seleccionada.
Mostrar elementos sin datos
En ocasiones puede ser necesario mostrar una categoría aunque no existan registros asociados a ella.
Para ilustrar este ejemplo, a propósito, hemos eliminado los datos del ejercicio 2014 en los datos origen, y hemos actualizado la tabla dinámica.
Hemos desactivado también los subtotales por grupos.
La tabla dinámica mostrará los datos del 2013 y los del 2015 y 16, ya que no disponemos de datos del año 2014.
No obstante nos gustaría que se mostrase el año aunque no tengamos datos.
Para poder conseguir este comportamiento, desde la vista diseño haremos doble clic sobre la etiqueta Años en el área Campos de fila.
Se presentará el diálogo Campo de datos, en el que activamos la opción Mostrar elementos sin datos.
La tabla dinámica muestra la información del año 2014.
Al estar mostrando los elementos sin datos, también automáticamente se presentan elementos para fechas anteriores a la inicial y para fechas posteriores a la final, que quizás deseamos ocultar.
Lo resolvemos simplemente filtrando el campo Años para que no los muestre.
Desagrupar elementos agrupados
Si una agrupación ya no es necesaria, puede eliminarse sin borrar los datos de origen.
Haga clic con el botón derecho en cualquier elemento del grupo.
Seleccione Desagrupar.
Para desagrupar los elementos que tengamos agrupados en nuestra tabla dinámica debemos seleccionar dicho grupo e ir a la ficha Opciones y dentro del grupo Agrupar pulsar el botón Desagrupar.
Ventajas de agrupar selecciones
Crear grupos o agrupar campos en tablas dinámicas es un recurso más que te permiten las tablas dinámicas de Excel a la hora de poder organizar y leer mejor los datos.
Como has podido ver, estas agrupaciones son muy fáciles de crear, y pueden ayudarte a analizar mejor los datos, y a establecer un nuevo criterio para poder agrupar los datos de otra forma.
Puedes agrupar datos en tablas dinámicas sin necesidad de cambiar los datos en la base de datos, sin necesidad de tener que copiar los datos y tener que trabajarlos de otra forma.
Una de las ventajas más grandes es que esta agrupación está siempre disponible en la lista de campos; cuando crear una agrupación, se crea un campo de tabla dinámica nuevo el cual puedes elegir en la lista de campos.
Puedes ver la agrupación creada en tu tabla dinámica en cualquier momento o quitarla sobre la marcha.
Una vez hayas creado un grupo, puedes usarlo o no en tu tabla dinámica.
Y poner o quitar la agrupación cuando lo necesites.
Esto no implica ni realiza cambios en la base de datos como ya te hemos explicado, es un recurso más que puedes usar para hacer una agrupación de datos con un criterio diferente.
Otra de las ventajas es la capacidad de poder compilar los datos y poder hacerlo en un concepto que no está recogido en tu base de datos.
Agrupar datos es un recurso que tienes a tu disposición, muy fácil de realizar, y útil como hemos visto en algunos casos.
No afecta a tu base de datos y permite en tu análisis el poder establecer un concepto o criterio nuevo que te permitan condensar los datos.
Lo más importante es que la agrupación de datos crea nuevos campos y que por tanto puedes gestionar desde la lista de campos de tabla dinámica.
La lista de campos después de agrupar
Tras crear una tabla dinámica, verá la Lista de campos.
Puede cambiar el diseño de la tabla dinámica agregando y organizando sus campos.
La lista de campos tiene una sección de campos donde selecciona los campos que desea mostrar en la tabla dinámica y una sección de áreas, en la parte inferior, donde puede organizar los campos como desee.
Los campos del área Filtros se muestran como filtros de informe de máximo nivel por encima de la tabla dinámica.
Los campos del área Filas se muestran como Etiquetas de fila en la parte izquierda de la tabla dinámica.
Los campos del área Valores se muestran como valores numéricos resumidos en la tabla dinámica.
Para eliminar un campo de la tabla dinámica, arrastre el campo fuera de la sección de áreas.
También puede quitar campos al hacer clic en la flecha abajo situada junto al campo y seleccionando Quitar campo.
El menú de campos de tabla dinámica, o lista de campos de tabla dinámica, se puede abrir haciendo un solo clic en la tabla dinámica, en cualquier celda.
Si cerraste el menú de campos de tabla dinámica en algún momento, puedes activar y desactivar el menú de campos de tabla dinámica en cualquier momento desde el menú de Excel haciendo clic en “Analizar tabla dinámica” y clicando arriba a la derecha en “lista de campos”.
Aspectos que conviene revisar
- Selecciona los elementos originales y no el campo de grupo cuando vayas a crear otra agrupación.
- En una selección discontinua, mantén pulsada la tecla correspondiente al sistema operativo.
- Comprueba el nuevo campo creado en la lista de campos.
- Ordena la tabla dinámica cuando necesites interpretar los grupos según un valor de referencia.
- Actualiza la tabla dinámica si los grupos desaparecen después de cambiarles el nombre.
- Incluye el año al agrupar meses cuando existan fechas de varios años.
- Filtra las categorías adicionales si se muestran fechas anteriores o posteriores que no necesitas.
- Desagrupa los elementos desde el menú contextual o desde la ficha Opciones.
