Capítulo 12. Analizar datos con tablas dinámicas

. . . . . . . . . . . .

Un informe de tabla dinámica (o simplemente una tabla dinámica) es una tabla interactiva que organiza y resume datos sin procesar. La tabla dinámica permite resumir, filtrar y visualizar datos desde diversas perspectivas para un mejor análisis de los mismos.

En esta parte. . .

• Añadir un filtro de informe de segmentación de datos

• Crear tablas y gráficos dinámicos

• Crear y dar formato a informes de tablas dinámicas

. . . . . . . . . . . .

Estructura de una tabla dinámica

Mientras una tabla sirve para presentar y analizar detalles de los datos, una tabla dinámica permite presentar y analizar datos de forma resumida. Además, con una tabla dinámica (que puedes ver en la figura 12-1), puedes ver los datos resumidos desde varias perspectivas organizadas en niveles jerárquicos detallados.

Figura 12-1

Los elementos que aparecen en una tabla dinámica son los siguientes:

• Panel de tareas de lista de campos de tabla dinámica. Aparece cuando indicas que quieres crear una tabla dinámica desde datos de origen. El panel de tareas incluye etiquetas de campo (títulos de columna) de los datos de origen y de las casillas, que corresponden a áreas de la tabla dinámica.

• Etiquetas de fila. Los campos que quieres resumir en filas.

• Elemento de campo. Un elemento individual en las áreas de fila o columna de la tabla dinámica.

• Etiquetas de columna. Los campos que quieres resumir en columnas.

• Valores. Los datos que se resumen (por ejemplo, suma del precio total).

• Filtro de informe. Uno o varios campos en el panel de tareas Lista de campos de tabla dinámica utilizado para filtrar el informe de la tabla dinámica.

• Segmentación de datos. Un filtro interactivo, parecido al filtro de informe, pero con mayor funcionalidad.

Recuerda: Para elaborar un informe de tabla dinámica, debes elegir al menos un campo del área de datos y uno del área de fila o columna. La elección depende de cómo quieras visualizar los datos.

Recuerda: Los datos de origen utilizados en la tabla dinámica se almacenan en una zona de la memoria del sistema conocida como Caché dinámico. Si tienes suficiente memoria y los datos de origen provienen de una fuente de datos externa, puedes crear una tabla dinámica a partir de datos de origen con más filas y columnas de las que Excel soporta. Esto solo es posible si tras realizar el resumen, los datos se ajustan a los límites de fila y columna de Excel.

Añadir un filtro de informe de segmentación de datos

La segmentación de datos filtra de manera parecida al filtro de informe de una tabla dinámica tradicional, pero con las ventajas siguientes:

• Puedes añadir una segmentación de datos a más de una tabla dinámica y así poder filtrar varias tablas dinámicas a la vez.

• Una segmentación de datos muestra la lista de elementos que se han filtrado o no filtrado. Un filtro de informe solo puede mostrar un único elemento filtrado a la vez.

• Puedes arrastrar una segmentación de datos en una hoja de cálculo o copiar a otra hoja de cálculo del libro. Un filtro de informe ocupa un área fija en la tabla dinámica y no puedes moverlo.

A no ser que tengas que mantener la compatibilidad con versiones anteriores de Excel, la segmentación de datos es preferible al filtro de informe.

Crear una segmentación de datos nueva

Puedes crear una o varias segmentaciones a la vez. Cada segmentación de datos representa un campo en la lista de campos de la tabla dinámica y muestra los elementos en el campo. Puedes crear una segmentación de datos para un campo tanto si está siendo utilizado por una tabla dinámica o no. Para crear una o varias segmentaciones de datos:

1. Clica en cualquier parte de la tabla dinámica y en la ficha Herramientas de tabla dinámica - Opciones.

2. En el grupo Ordenar y filtrar, clica en el botón Insertar segmentación de datos. Aparece el cuadro de diálogo Insertar segmentación de datos (mira la figura 12-2).

Figura 12-2

3. Selecciona las casillas de verificación para el campo o campos que quieras utilizar para filtrar la tabla dinámica. Aparecen una o varias segmentaciones de datos en la hoja de cálculo (como en la figura 12-1, donde se han creado segmentaciones de datos Provincia y CC.AA.).

Copiar, mover, eliminar una segmentación de datos y cambiar su tamaño

Al crear una segmentación de datos, puedes elegir su ubicación y tamaño de la manera siguiente:

• Para mover una segmentación de datos, simplemente señala la segmentación de datos hasta que el puntero se convierta en una flecha con una cruz negra. Después, mantén pulsado el botón izquierdo del ratón y arrastra la segmentación de datos hacia la nueva ubicación.

• Para copiar una segmentación de datos en otra hoja de cálculo, haz un clic derecho en la segmentación de datos y selecciona Copiar en el menú (o selecciona la segmentación de datos mediante un clic en el área de encabezado y pulsando Ctrl+C). Selecciona la nueva hoja de cálculo, haz un clic derecho en cualquier parte y selecciona Pegar en la galería Opciones de pegado en el menú (o pulsa Ctrl+V).

• Para eliminar una segmentación de datos, selecciónala mediante un clic en el área de encabezado y selecciona Quitar.

• Para cambiar el tamaño de una segmentación de datos, señala una esquina o la parte central de un borde lateral hasta que el puntero se convierta en una flecha doble. A continuación, mantén pulsado el botón izquierdo del ratón y arrástrala para cambiar el tamaño.

Filtrar una tabla dinámica con segmentación de datos

Como puedes ver en la figura 12-1, una segmentación de datos muestra una lista con todos los elementos de un campo. Si hay más elementos de los que la segmentación de datos puede mostrar simultáneamente, puedes utilizar la barra de desplazamiento de la derecha de la segmentación de datos, o cambiar el tamaño de la segmentación de datos para ver los elementos adicionales. Para utilizar una segmentación de datos para filtrar una tabla dinámica:

• Para filtrar un único elemento, clica en el elemento en la segmentación de datos.

• Para filtrar elementos no consecutivos, clica en el primer elemento en la segmentación de datos, mantén pulsado Ctrl y selecciona los otros elementos.

• Para filtrar elementos consecutivos, clica en el primer elemento en la segmentación de datos, mantén pulsado Mayús y clica en el último elemento.

• Para quitar todos los filtros de una segmentación de datos, clica en el botón Borrar filtro en la parte derecha del encabezado de la segmentación de datos (mira la figura 12-1).

Excel excluye todos los elementos no filtrados de la tabla dinámica. Los elementos filtrados y los no filtrados tienen colores de fondo distintos.

Dar formato a una segmentación de datos

Normalmente, cuando incluyes una segmentación de datos en un informe, sueles cambiar el formato predeterminado. Un informe típico contiene uno o varios gráficos dinámicos y segmentaciones asociadas. Como los gráficos dinámicos están asociados a la tabla dinámica, al filtrar la tabla dinámica, también se filtra el gráfico dinámico. Consulta el apartado “Crear y utilizar un gráfico dinámico”.

Para dar formato a una segmentación de datos:

1. Clica en el encabezado de la segmentación de datos para seleccionarla. Aparece la ficha contextual Herramientas de segmentación de datos en la Cinta de opciones.

2. Clica en la ficha Opciones. Selecciona un estilo de la galería en el grupo Estilos de segmentación de datos. Clica en la flecha de la parte inferior derecha de la galería para ver todos los estilos disponibles.

3. Selecciona un estilo nuevo de la galería. El formato de la segmentación de datos cambia al nuevo estilo.

4. Si no te conviene ninguno de los estilos disponibles, selecciona Nuevo estilo de segmentación de datos en la parte inferior de la galería de estilos. Aparece el cuadro de diálogo Nuevo estilo de segmentación de datos (mira la figura 12-3).

Figura 12-3

5. Escribe un nombre para el estilo en el cuadro Nombre.

6. Selecciona el elemento al que se le va a dar formato en el cuadro de lista de elementos. Ahora clica en Formato para ver el cuadro de diálogo Formato de elemento de segmentación de datos.

7. Selecciona las opciones de formato disponibles que aparecen en el cuadro. Las opciones son Fuente, Bordes y Relleno.

8. Clica en Aceptar para volver al cuadro de diálogo Nuevo estilo de segmentación de datos. Repite los pasos 6 y 7 para los otros elementos de la segmentación de datos a los que quieras dar formato.

9. Una vez cambiado el formato, clica en Aceptar para salir del cuadro de diálogo Nuevo estilo rápido de segmentación de datos. El estilo nuevo aparece en la sección personalizada de la galería de estilos.

Muchas veces hay que cambiar el formato de los elementos a un estilo existente, en lugar de crear un estilo nuevo. En el cuarto paso del proceso anterior, en lugar de elegir Nuevo estilo de segmentación de datos, haz un clic derecho en el estilo que quieras cambiar y selecciona Duplicar en el menú. Aparece el cuadro de diálogo Modificar estilo rápido de segmentación de datos. Continúa a partir del quinto paso para seleccionar los elementos que hay que modificar.

Recuerda: No puedes cambiar directamente un estilo integrado, pero puedes crear un duplicado de un estilo integrado y hacer los cambios en el duplicado.

Asociar una segmentación de datos con otras tablas dinámicas

Puedes asociar una segmentación de datos de una tabla dinámica con otras tablas dinámicas del libro. Las otras tablas dinámicas deben compartir los mismos datos de origen que la tabla dinámica original. La manera más sencilla de asociar una segmentación de datos con otra tabla dinámica es hacer una o varias copias de la tabla dinámica original. Si ya has creado varias copias de una tabla dinámica antes de añadir la segmentación de datos, esta se asocia de forma predeterminada solo con la tabla dinámica utilizada para crear la segmentación de datos.

Para asociar una segmentación de datos con otra copia de una tabla dinámica:

1. Crea una o varias copias de la tabla dinámica. Consulta el apartado “Copiar una tabla dinámica”.

2. Selecciona la segmentación de datos que quieras asociar con la copia o copias de la tabla dinámica.

3. Clica en la ficha Herramientas de segmentación de datos - Opciones y en el botón Conexiones de tabla dinámica en el menú contextual. También puedes hacer un clic derecho sobre la segmentación de datos y elegir Conexiones de tabla dinámica en el menú contextual. Aparece el cuadro de diálogo Conectar tabla dinámica.

4. En el cuadro de diálogo, activa la casilla de verificación que aparece junto al nombre de cada tabla dinámica que quieras asociar con la segmentación de datos. El cuadro de diálogo también muestra la hoja de cálculo de cada tabla dinámica en la lista.

5. Clica en Aceptar para finalizar.

Cambiar el resumen de datos de una tabla dinámica

Al añadir un valor numérico al área de valores de una tabla dinámica, se utiliza la función Sumar de forma predeterminada para resumir los datos. Si se añade un campo de texto al área de los valores, Excel utiliza la función Contar para resumir los datos de texto. La función Contar es la única manera de resumir datos de texto, pero hay diversas opciones para resumir datos numéricos. Para seleccionar una función de resumen distinta para un campo numérico, utiliza uno de los métodos siguientes:

• Haz un clic derecho en el campo numérico en el área de valores de la tabla dinámica, selecciona Resumir datos por en el menú contextual y una de las opciones en el menú desplegable. Aparece el resumen nuevo del campo seleccionado.

• Selecciona cualquier celda del campo numérico en el área de valores de la tabla dinámica, clica en la ficha Opciones de tabla dinámica en la Cinta de opciones y en el botón Resumir datos y selecciona una opción en el menú.

Puedes ver diferentes resúmenes para el mismo campo numérico en la tabla dinámica (por ejemplo Suma y Promedio). Para crear un resumen nuevo, arrastra la etiqueta de campo de la lista de campos en el panel de tareas a la casilla Valores (puede tener el mismo campo en repetidas ocasiones). Cada vez que se arrastra el campo a la casilla Valores, se añade el campo al área de valores de la tabla dinámica utilizando el resumen predeterminado. Para cambiar la forma predeterminada, utiliza el procedimiento indicado para cambiar el resumen.

Copiar una tabla dinámica

A veces se quiere utilizar una tabla dinámica desde distintas perspectivas, por ejemplo para analizar los datos de la tabla dinámica directamente, para hacer referencia a datos concretos de una tabla dinámica en una fórmula o para visualizar distintos gráficos dinámicos. Tener que arrastrar constantemente los campos dentro y fuera de la tabla dinámica para ver los datos desde una perspectiva diferente es muy aburrido. Para evitarlo, puedes crear una o varias copias de una tabla dinámica y distribuir los campos como se quiera en cada copia. Para copiar una tabla dinámica, utiliza cualquiera de los métodos siguientes:

• La manera más rápida para crear una copia de una tabla dinámica es copiar la hoja de cálculo donde está la tabla dinámica. Puedes hacer tantas copias como quieras. Consulta en el capítulo 3 el apartado “Copiar o mover una hoja de cálculo”.

• Si hay varias tablas dinámicas en una hoja de cálculo, pero solo quieres copiar una:

1. Clica en cualquier parte de la tabla dinámica y en la ficha Herramientas de tabla dinámica - Opciones.

2. En el grupo Acciones, clica en la flecha del botón Seleccionar y selecciona Toda la tabla dinámica. La tabla dinámica queda seleccionada en su totalidad.

3. Haz un clic derecho en la tabla dinámica y selecciona Copiar en el menú contextual o pulsa Ctrl+C.

4. Selecciona la hoja de cálculo de destino y la ubicación para la celda de la parte superior izquierda del área de encabezado de la tabla dinámica.

5. Haz un clic derecho y selecciona Pegar de la galería de Opciones de pegado en el menú contextual.

Crear y utilizar un gráfico dinámico

Si trabajas con una tabla dinámica, puede serte difícil detectar tendencias y modelos, incluso cuando los datos de la tabla están muy resumidos. En tal caso, puedes crear un gráfico dinámico, y puedes hacerlo al mismo tiempo que se crea una tabla dinámica.

Los gráficos dinámicos son parecidos a los gráficos estándar de Excel, pero tienen algunas diferencias:

• El gráfico dinámico es interactivo. Puedes filtrar y ajustar los campos directamente. Cualquier cambio que se hace en el gráfico dinámico se refleja en las tablas dinámicas subyacentes.

• Los subtotales y totales en la tabla dinámica no se incluyen en el gráfico.

• Las series de gráficos se basan en datos de columna en el área de valores del gráfico. Las series no pueden estar en filas. Además, las etiquetas de columna se utilizan para los nombres de series (mira la figura 12-1).

• Los elementos de campo en el área de las filas de la tabla dinámica se utilizan para etiquetas de categorías (también lo ves en la figura 12-1).

• Los gráficos dinámicos no aceptan dispersión X Y ni tipos de gráfico de cotizaciones ni de burbujas.

Si la terminología de este apartado no te suena, consulta en el capítulo 10 el apartado “Estructura de un gráfico”.

Crear un gráfico dinámico

Sigue los pasos del apartado “Crear un informe de tabla dinámica” a continuación. Pero en el segundo paso, selecciona la flecha del botón de la tabla dinámica y selecciona Gráfico dinámico en el menú. Un gráfico dinámico no puede existir sin una tabla dinámica asociada, así que al crearlo, se crea también una tabla dinámica. Al crear un gráfico dinámico, se utiliza el tipo de gráfico (2-D gráfico de columna) y un formato de gráfico predeterminado. Si quieres cambiar el tipo de gráfico, sigue los pasos del capítulo 10 “Cambiar el tipo de gráfico de un gráfico existente o series de datos”. El gráfico dinámico está en blanco hasta que no se añaden campos a la tabla dinámica. Para crear un gráfico dinámico después de haber creado una tabla dinámica:

1. Clica en cualquier parte de la tabla dinámica y luego en el botón Gráfico dinámico en la ficha Herramientas de tabla dinámica - Opciones.

2. En el cuadro de diálogo Insertar gráfico, selecciona la categoría de gráfico en la parte izquierda y el estilo de gráfico a la derecha. Clica en Aceptar para crear el gráfico.

Utilizar un gráfico dinámico

Una vez creado, puedes utilizar el gráfico dinámico de manera interactiva. El gráfico incluye botones de campo incrustados y la posición de los botones de campo corresponde vagamente con la ubicación de los campos en la tabla dinámica. Mira la figura 12-4.

En ella se ha utilizado el botón Producto en el gráfico para filtrar las categorías bebidas. Puedes ocultar algunos o todos los botones en el gráfico. Para ocultar todos los botones, selecciona el gráfico y clica en la ficha Herramientas del gráfico dinámico - Analizar en la Cinta de opciones y luego en el botón de división Botones de campo. Para ocultar botones de Fila, Columna, Valores o área de Filtro de informe de la tabla dinámica, clica en la flecha del botón de división Botones de campo y desactiva las casillas de verificación correspondientes en el menú.

En un gráfico, el área que corresponde al área de fila de la tabla dinámica se llama campo de eje (el eje horizontal) y el área que corresponde al área de columna de la tabla dinámica se llama campo de leyenda.

Figura 12-4

Crear un informe de tabla dinámica

Los datos para la tabla dinámica pueden provenir de una fuente de datos externa (como una base de datos) o de un rango o tabla de Excel. Consulta el capítulo 11 “Trabajar con tablas”. Aunque puedes crear una tabla dinámica a partir de un rango común de una hoja de cálculo (no de una tabla), se recomienda utilizar una tabla. Si la tabla se expande o contrae, tras actualizar la tabla dinámica, los datos para la tabla dinámica se basan en el nuevo rango de tabla.

Para crear un informe de tabla dinámica a partir de una tabla:

1. Mueve el puntero de celda sobre cualquier celda de la tabla.

2. Clica en la ficha Insertar en la Cinta de opciones y en el botón Tabla dinámica. Aparece el cuadro de diálogo Crear tabla dinámica y selecciona el área de datos de la tabla. El nombre de la tabla aparece en la casilla Tabla o rango.

3. En la mitad inferior del cuadro de diálogo, especifica dónde hay que ubicar la tabla dinámica y clica en Aceptar. Si se selecciona la opción Hoja de cálculo existente, clica en la casilla Ubicación y luego en la celda de la hoja de cálculo que corresponderá a la esquina superior izquierda de la tabla dinámica. Aparece el panel de tareas Lista de campos de tabla dinámica y el área en la hoja de cálculo dónde se ubicará la tabla dinámica. Además, aparecen las fichas contextuales Herramientas de tabla dinámica en la Cinta de opciones.

4. En el panel de tareas Lista de campos, selecciona las casillas de verificación de las etiquetas de campo de los campos que quieras incluir o arrastra las etiquetas de campo hasta las casillas de la parte inferior del panel de tareas.

Estas casillas corresponden a las áreas de etiquetas en la tabla dinámica, donde aparecerán los campos seleccionados (como puedes ver en la figura 12-1)

Si seleccionas las casillas de verificación, Excel selecciona las casillas en la parte inferior del panel de tareas para colocar las etiquetas de campo en función de lo siguiente:

• Las etiquetas de campo para campos numéricos (como “Precio total”) se colocan en la casilla Valores.

• Las etiquetas de campo para campos no numéricos se colocan en la casilla Etiquetas de fila.

Para invalidar el comportamiento predeterminado, arrastra las etiquetas de campo desde la lista en la ventana superior a las casillas correspondientes en la parte inferior del panel de tareas.

Cuando hayas colocado las etiquetas de campo en las casillas (o hayas arrastrado manualmente las etiquetas a las casillas), los datos de campo aparecen en las áreas de etiqueta de la tabla dinámica correspondiente en la hoja de cálculo. De forma predeterminada, las etiquetas de campo en la casilla Valores suman los datos que aparecen en el área de valores de la tabla dinámica.

La figura 12-1 muestra la naturaleza jerárquica de los datos resumidos en una tabla dinámica y la relación entre la organización de los campos en el panel de tareas y la visualización de la hoja de cálculo.

Hay indicadores + y – junto a cada encabezado de nivel en la hoja de cálculo. Esto permite expandir o cerrar un nivel de detalle en el resumen de la tabla dinámica. Además, al crear una tabla dinámica, aparece la tabla dinámica con una vista compacta de forma predeterminada. La vista compacta se parece a la que aparece en la figura 12-1, con la salvedad de que no se muestran los encabezados de campo (etiquetas). Para ver las etiquetas de campo, clica en la flecha desplegable a la derecha de Etiquetas de fila o de columna. Lee también el apartado “Modificar una estructura de un informe”.

Dar formato a un informe de tabla dinámica

Al principio, la tabla dinámica tiene un estilo predeterminado, pero puedes cambiar y elegir entre otro estilo predeterminado o crear un estilo nuevo. También puedes dar formato según el contenido de una celda o campo de una tabla dinámica.

Aplicar un estilo nuevo a la tabla dinámica

Para dar un nuevo estilo predeterminado a una tabla dinámica:

1. Clica en cualquier celda de la tabla dinámica y en la ficha Diseño.

2. Selecciona un estilo nuevo de la galería Estilos de tabla dinámica. Visualiza las opciones de la galería mediante las flechas de desplazamiento superiores en la parte derecha de la galería, o despliega una casilla mediante la flecha de debajo de las flechas de desplazamiento. Para previsualizar un estilo coloca el puntero del ratón sobre el estilo.

3. Además, o como alternativa al segundo paso, puedes seleccionar un estilo y ver las opciones del grupo Estilos de tabla dinámica. Activa o desactiva las casillas de verificación correspondientes.

Recuerda: Los estilos de la galería de estilos de tabla dinámica se basan en el tema aplicado al libro. Por lo tanto, si cambias el tema del libro, el estilo de tabla dinámica seleccionado y los estilos de la galería también cambian para ajustarse al nuevo tema. Completa esta información en el apartado “Dar formato con temas” en el capítulo 1.

Dar formato según los valores de datos

Dar formato a una tabla dinámica según los valores de datos (dar formato condicional) se parece a dar formato a una celda o rango de una tabla no dinámica según el contenido de la celda o rango. Si lo necesitas, vuelve a leer los apartados “Dar formato según los contenidos de la celda o del rango” y “Dar formato a una rango mediante visualizaciones comparativas” del capítulo 8.

Tras seleccionar una celda o rango de celdas en el área de valores y aplicar una regla de formato condicional, aparece una etiqueta inteligente con opciones de formato (un icono pequeño a la derecha de la selección); mira la figura 12-5.

Figura 12-5

Al clicar en la etiqueta inteligente, aparecen las opciones siguientes:

• Celdas seleccionadas. Es la opción predeterminada. El formato condicional solo se aplica a la celda seleccionada.

• Todas las celdas con valores “Suma de nombredecampo”. Nombredecampo es el nombre de un campo de valores (por ejemplo Preciototal), Excel aplica el formato condicional a todas aquellas celdas con valores “Suma de nombredecampo” en la tabla dinámica, sin tener en cuenta el nivel jerárquico del campo, e incluye subtotales y totales generales. Si la visualización de los subtotales o totales generales está activada, es útil para resúmenes que no son sumas (de lo contrario los subtotales y totales generales sesgarán cualquier formato condicional según el rango de los valores en el campo). Un ejemplo de un resumen útil para esta opción es “Promedio de nombredecampo”.

• Todas las celdas con valores “Suma de nombredecampo” para el “Campodefila” y “Campodecolumna”. Nombredecampo es el nombre de un campo de valores (por ejemplo, Preciototal), Campodefila es el nombre de un campo de fila, y Campodecolumna es el nombre de un campo de columna. Excel aplica el formato condicional a todas las celdas con valores “Suma de nombredecampo”en la tabla dinámica para los campos de fila y columna que corresponden a las celdas que se han seleccionado para el formato condicional y excluye los subtotales. “Campodecolumna” aparece desactivada si la tabla dinámica no incluye campos de columnas.

En la lista anterior, se sobreentiende que el campo de valores se resume mediante la función Suma (de forma predeterminada). El nombre actual para la opción depende de la función de resumen aplicada al campo. Si los valores son promedio, por ejemplo, el nombre sería “Todas las celdas con valores ‘Promedio de nombredecampo’”.

Recuerda: Si seleccionas alguna de las dos últimas opciones de la lista anterior, el formato condicional se extiende para añadir los datos nuevos tras actualizar la tabla dinámica.

Recuerda: El formato se aplica a una tabla dinámica con el orden siguiente: formato directo (por ejemplo, formato que se da a partir del grupo Fuente en la ficha Inicio de la Cinta de opciones) prevalece sobre el estilo de tabla dinámica y el formato condicional prevalece sobre el formato directo.

Cambiar el formato de número de un campo de una tabla dinámica

Cuando creas un informe de tabla dinámica, no se conserva ningún formato de número especial que puedas haber aplicado a los datos originales. Si aplicas un formato de divisas a los datos originales, por ejemplo, y luego utilizas esos datos en la tabla dinámica, Excel no conserva el formato de la divisa en el informe de tabla dinámica.

Para cambiar el formato de número para un campo en un informe de tabla dinámica:

1. Selecciona cualquier celda del campo en el cual quieras cambiar el formato de número. Casi siempre, se utiliza esta opción para cambiar el formato de número en el área de valores de la tabla dinámica, pero puedes cambiar los formatos de campos numéricos o de datos en las áreas de fila y columna.

2. Haz un clic derecho y selecciona Configuración de campo de valor en el menú contextual (el nombre en el menú contextual depende de si cambias el formato de número del campo de una fila o de una columna o de un campo de valores). Aparece el cuadro de diálogo Configuración de campo de valor.

3. Clica en el botón Formato de número. Aparece el cuadro de diálogo Formato de celdas.

4. En la casilla Categoría, selecciona una categoría de formato de número y selecciona un formato en la parte derecha del cuadro de diálogo Formato de celdas.

5. Clica en Aceptar para salir del cuadro de diálogo Formato de celdas y luego en Aceptar otra vez para salir del cuadro de diálogo Configuración de campo de valor.

Una manera rápida de cambiar el formato de número de celdas en un campo de valores es hacer un clic derecho y elegir Formato de número, lo que abre el cuadro de diálogo Formato de celdas.

Recuerda: Tras activar una opción de formato de número, mediante las opciones Configuración de campo de valor en el menú contextual, el formato se aplica a los datos nuevos que aparecen tras actualizar la tabla dinámica. Si quieres activar el formato solo a una celda o a un grupo de celdas (número, fuente, fondo, etc.), selecciona la celda o rango, haz un clic derecho, selecciona Formato de celdas en el menú contextual y activa las diferentes opciones del cuadro de diálogo.

Agrupar elementos de una tabla dinámica

Esta opción permite agrupar elementos específicos en un único campo de un informe de tabla dinámica. Si uno de los campos en los datos de origen está compuesto por fechas, por ejemplo, la tabla dinámica muestra una fila o columna separada para cada fecha, verás que agrupar las fechas en meses, trimestres o años, es la manera más eficaz de crear un informe de tabla dinámica con un campo de fecha.

Recuerda: Si agrupas por fecha o número, el campo que selecciones para agrupar no puede tener elementos en blanco. También, si agrupas por fecha, todas las celdas en los datos de origen deben portar una fecha reconocida por Excel o formatos de tiempo.

Crear un grupo de fechas

Para crear un grupo de fechas en una tabla dinámica:

1. Haz un clic derecho en el campo de fecha que quieras agrupar y selecciona Agrupar en el menú contextual. Aparece el cuadro de diálogo Agrupar (mira la figura 12-6).

2. Selecciona los grupos que quieras. Al seleccionar los grupos debes tener en cuenta lo siguiente:

• Para seleccionar más de un grupo, clica en las opciones del grupo. Vuelve a clicar en la opción del grupo para desactivarlo.

• Para agrupar semanal o quincenalmente, selecciona la opción Días y especifica 7 o 14, respectivamente en el cuadro Número de días.

• Si has seleccionado un grupo de varios días y luego otro grupo (por ejemplo, meses, trimestres o años), el grupo de varios días queda ignorado, porque no puedes agrupar entre límites de grupo. Por ejemplo, si quieres agrupar por semana o por mes, los días de la semana pueden abarcar dos meses, así que es imposible acomodar a ambos grupos simultáneamente.

• En el cuadro de diálogo, Excel selecciona automáticamente las fechas de inicio y fin para el grupo basado en el rango de datos en la tabla dinámica.

3. Para especificar manualmente la fecha de inicio o de fin o ambas, desactiva la casilla de verificación junto a Comenzar en o Terminar en y escribe una fecha nueva en la casilla de texto. Por ejemplo, si agrupas por semana y quieres estar seguro de que la semana se agrupa de domingo a sábado, en la casilla de texto Comenzar en, escribe la fecha del domingo anterior a la fecha de inicio seleccionada por Excel (a no ser que la fecha de inicio de Excel sea domingo).

4. Clica en Aceptar para crear los grupos.

Figura 12-6

Si el campo de fecha abarca varios años y seleccionas agrupar por mes o trimestre o ambos, debes incluir el grupo de los años. Si no incluyes el grupo de los años, cada mes o trimestre se resume para todos los años, no para cada año.

Crear un grupo de números

Si pones un campo numérico (o un campo de texto donde aparezca un número) en las áreas de fila o columna de la tabla dinámica, puedes crear un grupo de números. Por ejemplo, si tienes un campo de fila con muchos números de pedidos o facturas, quizá quieras crear un informe más compacto. Para crear un grupo de números:

1. Haz un clic derecho en el campo donde quieras crear el grupo de números y selecciona Agrupar en el menú. Aparece el cuadro de diálogo Agrupar y se activan automáticamente los números de inicio y fin para el grupo.

2. Para especificar el número de inicio o de fin o ambos, desactiva la casilla de verificación junto a Comenzar en o Terminar en y escribe un número nuevo en la casilla de texto.

3. En la casilla de texto Por, escribe el número por el que hay que agrupar.

4. Clica en Aceptar para crear el grupo.

Crear un grupo de texto

Un grupo de texto te puede ser útil, por ejemplo, si quieres agrupar varios países en los territorios de venta. Para crear un grupo de texto:

1. Selecciona las celdas del campo donde quieras agrupar. Si los elementos que se quieren agrupar no están uno al lado del otro, puedes hacer una selección múltiple manteniendo pulsado Ctrl y seleccionando los elementos que conformarán el grupo.

2. Haz un clic derecho en cualquier celda en la selección y selecciona Agrupar en el menú contextual. Se crea un grupo con un título genérico, como Grupo1, Grupo2, etc.

3. Para cambiar el nombre del grupo por un nombre con más sentido, clica en la celda con el nuevo nombre de grupo y escribe un nombre nuevo en la barra de fórmulas.

Modificar un informe de tabla dinámica

Una tabla dinámica es un tipo de rango especial y (excepto a la hora de dar formato), no puedes manipularla como un rango normal. Por ejemplo, no puedes insertar o quitar filas, editar resultados o mover celdas. Si intentas hacerlo, aparece un mensaje de error. Sin embargo, puedes hacer ciertos cambios en el diseño o estructura de la tabla dinámica, algunos de los cuales se describen a continuación.

Modificar un diseño de informe

Al crear una tabla dinámica, se utiliza un nuevo diseño compacto de forma predeterminada, donde el campo de las filas y columnas está oculto. Este diseño te será útil si tienes muchos campos en el informe, porque minimiza el espacio horizontal en la hoja de cálculo y facilita la lectura del informe. También puedes utilizar uno de los diseños siguientes:

• Contorno. Este diseño es parecido al diseño compacto, pero muestra los encabezados de campo individuales.

• Tabular. En este diseño, el primer elemento en un campo de subnivel no aparece en una fila aparte (se ve en la figura 12-1), sino en la misma fila como su elemento de campo de nivel superior (pariente).

Para cambiar el diseño del informe, clica en cualquier parte de la tabla dinámica, y luego en el botón Diseño de informe en la ficha Diseño. Selecciona una opción de diseño en el menú.

En todos los diseños estándar de tablas dinámicas, de manera predeterminada, los nombres de los elementos de campo de parientes no se repiten cuando el pariente tiene varios hijos. En la figura 12-1, debido al hecho de que los elementos de las subcategorías Botellas y Cajas, Cascos y Neumáticos y Tubos tienen hijos en el campo “Nombredeproducto”, las celdas entre cada elemento de la subcategoría están vacías (por ejemplo, hay celdas vacías entre Botellas y Cajas y Limpiadores, Cascos y Paquetes de hidración, etc.). La consecuancia de esas celdas vacías es que no puedes utilizar búsquedas u otras fórmulas que funcionen con una tabla de datos tradicional.

En Excel 2010, puedes rellenar las filas vacías de la tabla dinámica mediante la repetición de los nombres de los elementos parientes. Clica en cualquier lugar de la tabla dinámica y en la ficha Herramientas de la tabla dinámica-Diseño. Clica en el botón Diseños de informe y selecciona Repetir todas las etiquetas del informe en el menú.

Recuerda: La opción Repetir todas las etiquetas del informe se aplica a todos los campos de la tabla dinámica. También puedes utilizar la opción con el diseño de informe tabular y los subtotales desactivados.

Modificar una estructura de un informe

El poder de una tabla dinámica reside en su habilidad de mostrar datos resumidos desde muchas perspectivas mediante la reorganización de campos en las áreas de fila, columna y valores del informe y añadiendo filtros de informe en el área de filtros de informe. Para reorganizar la estructura del informe para ver los datos desde una perspectiva distinta, utiliza las técnicas siguientes:

• Para añadir campos a un informe, arrastra las etiquetas de campo desde la lista de campo en el panel de tareas hasta las casillas con etiquetas apropiadas debajo de la lista de campo. Para quitar un campo del informe, arrastra las etiquetas de campo fuera de la casilla de etiquetas apropiada o desactiva la casilla de verificación junto al nombre de campo en la lista de campo.

• Para mover campos entre las áreas de fila, columna y de filtro de informe del informe, arrastra las etiquetas de campo entre las casillas de etiquetas en el panel de tareas.

• Para reorganizar los niveles en las áreas de fila o columna del informe, clica y arrastra en la etiqueta de campo en la casilla de etiqueta de fila o columna sobre o debajo de las etiquetas de campo apropiadas en la misma casilla de etiqueta.

También puedes mover campos con un clic en las etiquetas de campo en las casillas de etiquetas del panel de tareas y seleccionando la opción mover en el menú.

Si tienes una tabla dinámica grande, pueden aparecer retrasos tras añadir, quitar o mover campos en la tabla, porque las actualizaciones pueden necesitar algún tiempo para cargase. Añadir, quitar o mover muchos campos a la vez puede ocasionar múltiples retrasos, lo que resulta molesto. Para resolver esto, selecciona la casilla de verificación Aplazar actualización del diseño en la parte inferior del panel de tareas. Para actualizar la tabla dinámica tras haber completado los cambios estructurales, clica en el botón Actualizar a la derecha de la casilla de verificación Aplazar actualización del diseño.

Mostrar y ocultar subtotales y totales generales

Puede que quieras visualizar el informe con los subtotales sobre un grupo o debajo de él, o por el contrario se quieren ocultar. También puedes ver los totales generales solo en filas o columna, u ocultarlos. Para cambiar el modo de visualización de los subtotales, clica en cualquier parte en la tabla dinámica y en el botón Subtotales en la ficha Diseño y selecciona una opción en el menú. Para cambiar el modo de visualización de los totales generales, clica en cualquier parte de la tabla dinámica y en el botón Totales generales en la ficha Diseño y selecciona una opción en el menú.

Para desactivar los subtotales solo en un campo, asegúrate de que los subtotales están activados para todo el informe, haz un clic derecho en un elemento del campo y selecciona Subtotal nombredecampo en el menú contextual. Nombredecampo es el nombre del campo cuyos subtotales se desactivan (por ejemplo, “Nombredecampo”). Para ver los subtotales solo en un campo, asegúrate de que los subtotales están desactivados para todo el informe, haz un clic derecho en un elemento del campo y selecciona Subtotal nombredecampo en el menú contextual.

Expandir y contraer niveles de elemento de campo

Expandir niveles de elemento de campo en el informe de tabla dinámica te permite ver el elemento del subnivel en el resumen del informe. Contraer niveles de elemento de campo te permite ocultar detalles de los subniveles en el resumen del informe. Cada elemento de campo de nivel tiene un botón +/-, que expande o contrae todos los subniveles que están debajo del elemento de campo. También puedes expandir o contraer a otros niveles de detalles como se muestra a continuación:

• Para expandir todos los elementos de un subnivel en un campo, haz un clic derecho en un elemento del campo, selecciona Expandir o contraer en el menú contextual, y selecciona Expandir todo el campo en el menú flotante. Para contraer todos los elementos de subnivel en un campo, repite lo anterior, pero selecciona Contraer todo el campo en el menú flotante (mira la figura 12-7).

• Para expandir un elemento de campo debajo de un subnivel específico, haz un clic derecho en un elemento del campo, selecciona Expandir o contraer en el menú contextual y selecciona el nombre del campo de la parte inferior en el menú flotante.

Figura 12-7

Actualizar un informe de tabla dinámica

Si cambias los datos de origen utilizados para crear el informe de tabla dinámica, la tabla dinámica no se actualiza automáticamente. Tienes que actualizar la tabla dinámica manualmente. Para ello, clica en una celda de la tabla dinámica y luego en Actualizar en la ficha Opciones, o haz un clic derecho en cualquier parte de la tabla dinámica y selecciona Actualizar en el menú contextual.

Cambiarle el nombre a una tabla dinámica

Al crear una tabla dinámica, se le asigna un nombre genérico, como Tabladinámica1, Tabladinámica2, etc. Se recomienda dar un nombre con más sentido a las tablas dinámicas, especialmente si hay varias tablas en un mismo libro. Cambiarle el nombre a una tabla dinámica es sencillo. Clica en cualquier parte de la tabla dinámica y luego en la ficha Herramientas de tabla dinámica - Opciones. En la parte izquierda de la ficha Opciones está el grupo Tabla dinámica. Escribe un nombre en la casilla Nombre de tabla dinámica.

Utilizar cálculos personalizados

Los cálculos personalizados son una lista de cálculos predeterminados, como totales o cocientes, que se utilizan para realizar análisis de datos muy eficaces. Para añadir un cálculo personalizado a una tabla dinámica:

1. Añade el campo donde haya que crear el cálculo personalizado en el área de valores de una tabla dinámica. Para ver un resumen estándar y un cálculo personalizado para el mismo campo, añade el campo al área de valores dos veces.

2. Clica en cualquier parte del campo de valor correspondiente y selecciona un cálculo personalizado mediante una de las opciones siguientes:

• Clica en la ficha Herramientas de tabla dinámica - Opciones en la Cinta de opciones, clica en el botón Mostrar valores como y selecciona una opción de la lista.

• Haz un clic derecho en el campo de valor, selecciona Mostrar valores como en el menú y selecciona una opción en el menú desplegable.

3. Si es necesario, selecciona un campo base o un campo base y un elemento base (aunque algunos cálculos personalizados no requieren acción alguna por parte del usuario tras hacer una selección, otros requieren un campo base o un campo base y un elemento base).

En la figura 12-8, el campo Total Trim aparece en varias ocasiones en la tabla dinámica para mostrar cómo se utilizan los cálculos personalizados que necesitan un campo base o un elemento base. Al clicar en el título, puedes cambiar el nombre de cada campo. Para ello, escribe el nombre nuevo en la barra de fórmulas.

Figura 12-8

La figura 12-8 muestra un resumen por cliente, por producto y por zona: Excel reinicia el total para cada elemento pariente nuevo; esta característica no puede ser modificada. Por ejemplo, el cliente es el pariente del producto y este de la zona.

Si quisieras crear un total de productos que no se reinicie al principio de cada cliente, tendrías que crear un campo que combine cliente y producto en un único campo, que deberías crear en la fuente de datos de la tabla dinámica.

La figura 12-8 también muestra un cálculo personalizado de promedio. El campo base para el cálculo es el cliente y el producto. También se puede seleccionar un elemento en concreto.

Recuerda: Todos los cálculos personalizados se basan en la suma estándar agregada a un campo de valor.