Técnicas Avanzadas de Tablas Dinámicas en Excel y Google Sheets

Este tutorial muestra técnicas avanzadas de tablas dinámicas en Excel y Google Sheets.

 

 

Haga clic aquí para ver un tutorial más breve sobre la creación de una tabla dinámica. O siga leyendo para explorar las características más complejas de Tablas Dinámicas en Excel.

Datos de Origen de laTabla Dinámica
Tablas Dinámicas Recomendadas
Actualizar Datos de una Tabla Dinámica
Agrupar Campos de una Tabla Dinámica
Filtros de Tablas Dinámicas
Creación y Uso de Segmentación de Datos con Tablas Dinámicas
Formatear Valores de Tablas Dinámicas
Ocultar y Mostrar Subtotales de Tablas Dinámicas
Campos Calculados de la Tabla Dinámica
Mover Tabla Dinámica
Insertar Gráfico Dinámico
Opciones de la tabla dinámica
Google Sheets

Consejo: Prueba a utilizar algunos atajos cuando trabajes con tablas dinámicas.

 

¿Qué Son los “Datos de Origen” en una Tabla Dinámica?

Los datos de origen de una tabla dinámica son los datos a partir de los cuales se va a crear la tabla. Estos datos deben almacenarse en la estructura o formato de tabla correcto para poder crear una tabla dinámica con éxito. La tabla de datos tiene que constar de cabeceras de columna (“campo”) con nombres lógicos que expliquen los datos que hay debajo de cada columna y filas de registros.

Considere la siguiente hoja de cálculo:

 

Los datos se organizan en columnas, cada una con un nombre de campo único. Cada fila en Excel se conoce como un registro único, y esos registros empiezan directamente debajo de la fila de cabecera. Los datos no pueden contener filas o columnas en blanco. Con los datos configurados correctamente, puedes crear la tabla dinámica. Elija entre las Tablas dinámicas recomendadas o diseñe una nueva tabla dinámica con los campos que necesite.

 

Tablas Dinámicas Recomendadas

Excel tiene una función para recomendar automáticamente una tabla dinámica basada en sus datos.

1. Seleccione cualquier celda de sus datos de origen y, a continuación, en la cinta de opciones, vaya a Insertar > Tablas > Tablas dinámicas recomendadas.

 

2. Elija una tabla dinámica de la lista de la izquierda y haga clic en Aceptar.

 

3. Esto crea una tabla dinámica en una nueva hoja. A continuación, puede ajustarla en la ventana Campos de la tabla dinámica. En el ejemplo anterior, Excel utilizó la primera columna de datos para las etiquetas de las filas. Esto no tiene mucho sentido Puede eliminar este campo(Maíz) de las etiquetas de fila arrastrándolo al cuadro Valores.

 

4. A continuación, puede arrastrar un campo más apropiado(Región, Vendedor o Fecha de venta) al cuadro Filas.

 

La tabla dinámica cambia en consecuencia.

 

Actualizar los Datos de la Tabla Dinámica

Si sus datos de origen cambian, necesita actualizar su tabla dinámica; no cambiará automáticamente.

En la cinta de opciones, vaya a Datos > Actualizar todo.

 

Las cifras de la tabla dinámica coinciden ahora con los datos de origen editados.

 

Agrupar Campos de Tabla Dinámica

Agrupar Campos de Fila y Columna

Puede agrupar campos de fila y columna si tiene grupos lógicos en sus datos.

Considere la siguiente tabla dinámica.

 

1. En la tabla dinámica, mantenga pulsada la tecla CTRL y seleccione los valores de campo que desea agrupar. A continuación, en la cinta de opciones, vaya a Análisis de tabla dinámica > Agrupar > Agrupar selección.

 

2. Las dos etiquetas de fila seleccionadas (aquí, Dallas y San Francisco) se agrupan en un nuevo grupo denominado Grupo1.

 

3. Si sus datos tienen formato de columnas, puede agruparlos por columnas. Seleccione las columnas en las que desea agrupar.

 

4. A continuación, en la cinta de opciones, vaya a Análisis de tabla dinámica > Grupo > Crear Grupo de Selección

 

Renombrar Grupos

1. Puede renombrar fácilmente un grupo. Seleccione la celda con la etiqueta y, en la barra de fórmulas, escriba un nuevo nombre para el grupo.

 

2. Puede hacerlo con cada grupo de filas que se cree.

 

3. También puede cambiar el nombre de sus grupos de columnas.

 

Agrupar por Fecha

1. Si mueve la Fecha de Venta a la sección Etiquetas de Fila de la tabla dinámica, observe que la tabla dinámica agrupa automáticamente la Fecha de Venta por Año, Trimestre y Mes. Las fechas reales del pedido aparecen debajo de cada mes para ese trimestre y año.

 

2. Cada grupo tiene un icono de expansión(+). Si hace clic en este icono, como se muestra en el gráfico anterior, cambia a un icono de colapso(-).

 

3. El área Filas de los Campos de la Tabla Dinámica  refleja que el campo Fecha de Venta (con Años y Trimestres y Meses automáticos) han sido creados y añadidos a la tabla dinámica.

 

4. Puede eliminar cualquiera de los campos de fecha generados automáticamente (años, trimestres o meses), pero si elimina el año, por ejemplo, los demás campos de fecha agregarán los datos anuales. En otras palabras, todos los pedidos de febrero se incluyen en Trimestre1 > febrero (es decir, la suma de febrero de 2020, febrero de 2021 y febrero de 2022).
   Del mismo modo, si elimina el campo Trimestres, entonces sólo se muestran los meses – por lo que todos los pedidos de febrero, independientemente del año, se muestran en febrero.

 

5. Para cambiar la forma en que se agrupa la tabla dinámica, seleccione el campo de fila en la tabla dinámica y, a continuación, en la cinta de opciones, vaya a Análisis tabla dinámica > Agrupar > Crear Selección de grupo.

 

6. Elija la agrupación que desea mostrar. Por ejemplo, puede que solo quiera Meses y Días.

 

7. Esto eliminaría el año, por lo que los pedidos del 1 de mayo, por ejemplo, se realizarían ese día, independientemente del año del pedido

 

8. Si sólo incluye una opción en la agrupación, se elimina la posibilidad de desglosar.

 

9. Sin embargo, puede seleccionar el campo en la tabla dinámica y volver a hacer clic en Crear grupo de selección en la cinta de opciones para volver a añadir otras opciones de agrupación.

 

Desagrupar Datos

Puede que desee eliminar los grupos que se han creado en su tabla dinámica.

Haga clic en un campo de la tabla dinámica que forme parte de un grupo y, a continuación, en la cinta de opciones, vaya a Analizar tabla dinámica > Grupo > Desagrupar.

 

Se elimina la agrupación de ese campo.

 

Filtros de Tabla Dinámica

Cuando se añaden campos a una tabla dinámica, Excel añade automáticamente botones de filtro.

 

1. Haga clic en cualquiera de los elementos(Región) para filtrar su tabla dinámica por uno de los valores que se encuentran en esa lista.

 

2. Para borrar el filtro del campo Región, haga clic en el botón de filtro y, a continuación, en (Todas). Haga clic en Aceptar.

 

3. Para filtrar por más de un valor, marque Seleccionar varios elementos y elija los elementos que desea filtrar. Haga clic en Aceptar para aplicar el filtro a la tabla dinámica.

 

Crear y Utilizar Segmentadores con Tablas Dinámicas

Los Segmentadores son otra forma de filtrar una tabla dinámica.

1. En la cinta de opciones, vaya a Analizar Segmentación de Datos > Filtrar > Insertar Segmentación de Datos.

 

2. Seleccione el campo para el que desea insertar un segmentador y, a continuación, haga clic en Aceptar.

 

Se muestran automáticamente todas las opciones de datos disponibles.

 

3. Haga clic en el valor superior (por ejemplo, Boston) y, a continuación, mantenga pulsada la tecla CTRL para seleccionar los valores adicionales. La tabla dinámica se actualiza dinámicamente para reflejar el filtro.

 

4. Si desea seleccionar valores adyacentes, haga clic en el primer valor de- segmentador de datos y, manteniendo pulsada la tecla MAYÚS, haga clic en el último valor que necesite.

 

5. Para eliminar la cortadora, haga clic sobre ella y pulse la tecla ELIMINAR . Esto también elimina cualquier filtro que el divisor haya aplicado a su tabla dinámica.

 

Formato de los Valores de la Tabla Dinámica

Cuando se crea una tabla dinámica, los valores se formatean como un formato numérico general.

 

Para dar formato a los números, lo mejor es utilizar las opciones de formato del cuadro Valores de los campos de la tabla dinámica.

1. En el pequeño menú desplegable junto al campo en el cuadro Valores, haga clic en Configuración de campo de valor…

 

2. A continuación, haz clic en Formato de número.

 

3. Elija en la lista Categoría y haga clic en Aceptar para aplicar el formato al campo correspondiente.

 

4. Vuelva a pulsar Aceptar para regresar a la lista Campos de la tabla dinámica.
   En esta fase, también puede cambiar el nombre de la etiqueta que se muestra en la tabla dinámica.

 

5. Repita los pasos anteriores para el resto de los campos en los que desee aplicar un formato numérico.

 

Ocultar y Mostrar Subtotales de Tablas Dinámicas

Por defecto, hay un Gran Total en la parte inferior de una tabla dinámica; si se han aplicado agrupaciones, también se añaden subtotales para cada grupo.

Para eliminar los subtotales:

1. En la cinta de opciones, vaya a Diseño > Presentación > Subtotales.

 

2. Haga clic en No mostrar subtotales.

 

Los subtotales se eliminan de la tabla dinámica.

 

También puede configurar los subtotales para que se muestren en la parte superior o inferior de cada grupo.

 

3. Para mostrar u ocultar el Total General, en la cinta de opciones, vaya a Diseño > Diseño > Totales Generales.

 

4. Elija la opción que desee para Totales generales. Por ejemplo, para ocultar todos los totales generales, haga clic en Desactivado para Filas y Columnas.

 

Campos Calculados de Tabla dinámica

Puede añadir campos calculados  a su tabla dinámica.

1. En la cinta de opciones, vaya a Analizar tabla dinámica > Cálculos > Campos, Elementos y Conjuntos > Campo Calculado.

 

2. En el campo Nombre, escriba el nombre del campo y, a continuación, en el campo Fórmula introduzca la fórmula del nuevo campo. Haga clic en Sumar para que el nuevo campo se incluya en la lista Campos que aparece a continuación.

 

3. Haga clic en Aceptar para añadir el campo a la tabla dinámica.

 

Mover Tabla Dinámica

Cuando crea una tabla dinámica, se le da la opción (si no utiliza una tabla dinámica recomendada) de insertar la tabla dinámica en una hoja nueva o en la hoja en la que está trabajando actualmente.

Si cambia de opinión sobre la ubicación, siempre puede mover la tabla después de haberla creado.

1. En la cinta de opciones, vaya a Analizar tabla dinámica > Acciones > Mover tabla dinámica.

 

2. Elija dónde desea colocar la tabla dinámica. En este ejemplo, mueva la tabla dinámica existente a la hoja de cálculo Datos de ventas.

 

3. Haga clic en Aceptar para mover la tabla dinámica.

 

Insertar Gráfico Dinámico

Puede crear un gráfico basado en su tabla dinámica.

1. Haga clic en su tabla dinámica y, a continuación, en la cinta de opciones, vaya a Análisis de tabla dinámica > Gráfico dinámico.

 

2. En el cuadro de diálogo Insertar gráfico, elija el tipo de gráfico y, a continuación, el Estilo. Por último, haz clic en Aceptar para insertar el gráfico en la hoja de cálculo.

 

3. Se coloca un gráfico en la hoja de cálculo. Para mover el gráfico, haga clic en el centro del gráfico para que el cursor se convierta en una pequeña cruz negra. A continuación, arrastre el gráfico a la ubicación deseada.

 

Opciones por Defecto de Tabla Dinámica

Opciones de Tabla

Hay una serie de opciones por defecto que se establecen cuando se crea una tabla dinámica. Puede cambiar estas opciones en Opciones de tabla dinámica.

1. Haga clic en su tabla dinámica y, a continuación, en la cinta de opciones, vaya a Analizar tabla dinámica > Tabla dinámica > Opciones > Opciones.

 

2. Cambie el Nombre de tabla dinámica en el primer campo si lo necesita. Esto podría resultar útil si su archivo tiene varias tablas dinámicas.

 

3. Haz clic en la pestaña que necesites y, a continuación, configura las opciones de esa pestaña para la tabla dinámica.
   En la pestaña Totales y Filtros, por ejemplo, puedes activar o desactivar los totales generales para filas y columnas. Marque Permitir varios filtros por campo para que esta sea la opción por defecto de su tabla pivotante.

 

Estilos de Tabla Dinámica

Cuando se crea una tabla dinámica, se aplica automáticamente un estilo por defecto. Puede cambiar el estilo de la tabla para modificar el aspecto de su tabla dinámica.

Aplicar un Estilo de Tabla Dinámica

1. Seleccione la tabla dinámica y, en la cinta de opciones, vaya a Diseño > Estilos de tabla dinámica. Haga clic en las flechas hacia arriba o hacia abajo para mostrar las opciones de estilo preestablecidas en el cuadro de estilo. A continuación, haga clic en el estilo que desee.

 

2. Alternativamente, para ver más estilos, haga clic en el botón Más (la flecha inferior hacia abajo).

 

Aparecerá una lista de estilos disponibles.

 

3. Elija el que desee para aplicarlo a su tabla dinámica.

 

Crear un Estilo Personalizado

También puede crear un estilo personalizado.

1. En la parte inferior de la lista de estilos, haga clic en Nuevo estilo de tabla dinámica.

 

2. En el campo Nombre, escriba el nombre de su estilo y, a continuación, en la lista Elemento de tabla, elija la parte a la que desea dar formato. Haz clic en Formato.

 

3. Ajuste los formatos de fuente, borde y/o relleno y, a continuación, haga clic en Aceptar.

 

4. Observe que en la lista de elementos de la tabla, los elementos que ha formateado aparecen ahora en negrita. En la parte derecha del cuadro se muestra una vista previa de su estilo. Haga clic en Aceptar para añadir su nuevo estilo a la lista Estilos.

 

5. Haga clic en el cuadro Estilos de tabla dinámica y elija un estilo personalizado para aplicarlo a su tabla dinámica.

 

6. Para modificar o eliminar un estilo personalizado, haz clic con el botón derecho y elige otra opción de estilo.

 

Opciones de Estilo

Cuando se aplica un estilo de tabla dinámica, se aplican automáticamente una serie de opciones predeterminadas, como Encabezados de fila y Encabezados de columna. En la pestaña Diseño, grupo Opciones de estilo de tabla dinámica, también tiene disponibles Filas y Columnas con bandas.

 

1. Para aplicar el formato de filas, marque la casilla Filas en bandas. Observe que la vista previa en Estilos de tabla dinámica muestra las filas formateadas con colores alternos para todos los estilos, mientras que el estilo que se aplica actualmente a su tabla dinámica formatea su tabla dinámica con estos colores.

 

2. Marque la casilla Columnas con bandas para aplicar también el formato de columna a su tabla dinámica.
3. También puede utilizar las opciones de estilo para eliminar el formato de los encabezados de fila y de columna.

 

 

Técnicas Avanzadas de Tablas Dinámicas en Google Sheets

Las tablas dinámicas de Google Sheets no son tan avanzadas como las de Excel. Puedes cambiar el rango de datos y añadir un filtro a tu tabla dinámica.

Actualizar o Cambiar el Rango de Datos

1. Asegúrate de que el cursor está en una celda de la tabla dinámica y, a continuación, en el editor de tablas dinámicas, haz clic en el botón Seleccionar intervalo de datos.

 

2. Haga clic en la casilla con el rango seleccionado. A continuación, puede seleccionar el rango de datos en el que desea que se base la tabla pivotante.

 

3. Haga clic en Aceptar para aplicar el rango nuevo o actualizado a la tabla dinámica.

 

Añadir Filtro

Considera la siguiente tabla dinámica de Google Sheets.

 

1. En el editor de la tabla dinámica, haga clic en el botón Añadir situado junto a Filtros y, a continuación, seleccione el campo por el que desea filtrar (por ejemplo, Región).

 

2. Haga clic en Borrar para desmarcar todos los valores y, a continuación, marque el valor por el que desea filtrar. Haga clic en Aceptar.

 

Se aplica el filtro elegido. Aquí solo se incluyen en la tabla los datos de la región de Boston.