Formato del grupo de informes de tabla dinámica.

En esta publicación, veremos algunas técnicas de formato de tabla dinámica que ayudarán a que nuestro informe se vea perfecto.

Objetivo

Nuestro objetivo es crear el siguiente informe de tabla dinámica.

Hay algunos puntos de formato a tener en cuenta sobre el informe. En primer lugar, los grupos de regiones, como Medio Oeste y Noreste, están en la misma columna que los representantes, pero los nombres de los representantes aparecen en su propia columna. Observe también que los encabezados de los grupos de regiones aparecen en sus propias filas con un tamaño de fuente no estándar (grande y en negrita). Entre los grupos de regiones hay una fila de hoja de cálculo en blanco. No hay botones +/-. Las columnas de ventas, recuento y promedio tienen formato numérico. Los subtotales de la región se presentan debajo de los datos de la región. El informe está ordenado de forma ascendente por región y, dentro de cada región, por iniciales del representante.

Todo este formato debe permanecer intacto cada vez que se actualiza la tabla dinámica, incluso si aparecen nuevas regiones en la fuente de datos. Si aparece una nueva región como Este en los datos del próximo período, cuando actualicemos la tabla dinámica necesitamos que la nueva región aparezca con el formato de región personalizado (fuente grande y negrita) y debe aparecer en el orden alfabético correcto. Si hay filas nuevas, también se deben conservar los formatos de números.

Ahora, veamos los pasos detallados para crearlo.

Detalles

Los pasos que cubriremos son:

• Definir la estructura del informe
• Quitar botones +/-
• Definir clasificación
• Diseño tabular
• eliminar subtotales
• encabezados de fila
• Diseño personalizado para la región.
• Formato de grupo de regiones personalizado
• Anchos de columna
• Diseño

Comenzamos insertando los campos en el área correcta del informe.

Definir la estructura del informe

Seleccionamos cualquier celda de la tabla de datos y luego seleccionamos Insertar Tabla dinámica. Luego insertamos los campos Región, Representante y Nombre del representante en el área Filas. Luego hacemos clic y arrastramos el campo Monto al área Valores tres veces.

En este punto, nuestra pequeña tabla dinámica se ve así.

A continuación, debemos arreglar los números para que reflejen los valores deseados. La primera columna de importación, actualmente denominada Suma del importación, debe reflejar la importación total de las ventas. Los valores están bien pero necesitamos cambiar la etiqueta del informe. Simplemente escribimos el encabezado de columna deseada Ventas en la celda. Para controlar el formato, hacemos clic derecho en cualquier celda de valor en la columna y seleccionamos Formato de número (no Formato de celdas). Seleccionamos número, sin decimales, coma.

La siguiente columna, Suma del import2, debe reflejar el recuento del número de transacciones. Entonces, hacemos clic derecho en cualquier celda de valor en esa columna y seleccionamos Resumir valores por… recuento. Escribimos la etiqueta de columna deseada Count en la celda del encabezado de la columna. Hacemos clic derecho en cualquier número de la columna y seleccionamos Formato de número… número, sin decimales, coma.

La siguiente columna, Suma del import3, debe reflejar las ventas promedio por representante. Entonces, hacemos clic derecho en cualquier celda de valor en esa columna y seleccionamos Resumir valores por… promedio. Escribimos la etiqueta deseada, Promedio, en la celda del encabezado. Hacemos clic derecho en cualquier número de la columna y seleccionamos Formato de número… número, sin decimales, coma.

Por último, cambiamos el encabezado Etiquetas de fila a Rep y nuestro informe actualizado se ve un poco mejor.

Quitar botones +/-

Necesitamos eliminar los pequeños botones mostrar/ocultar, expandir/contraer, +/-. Afortunadamente, esto es fácil de hacer con el siguiente ícono de cinta:

• Herramientas de tabla dinámica Analizar Botones +/-

Ah, nuestro informe se ve un poco mejor.

Definir clasificación

Dado que la clasificación predeterminada de una tabla dinámica es Manual y queremos que la clasificación sea ascendente, incluso si aparecen nuevas regiones o representantes en la fuente de datos en períodos futuros, debemos definir explícitamente el orden de clasificación.

Seleccionamos cualquier celda de región en el informe y luego usamos el menú desplegable Rep y seleccionamos Ascendente. A continuación, seleccionamos cualquier celda de representante en el informe y luego usamos el menú desplegable Rep y seleccionamos Ascendente. Acabamos de decirle a la tabla dinámica que ordene de forma ascendente por región y, dentro de cada región, por representante. Esto es bueno para que a medida que aparezcan nuevas regiones o representantes en nuestra fuente de datos, aparezcan automáticamente en el orden alfabético esperado en el informe.

Diseño tabular

Ahora necesitamos cambiar el diseño del informe de su formato predeterminado, Compacto, a Tabular. Esto se hace fácilmente seleccionando el siguiente comando de la cinta:

• Herramientas de tabla dinámica Diseño Diseño de informe Mostrar en forma tabular

Nos estamos acercando… el informe actualizado se muestra a continuación.

eliminar subtotales

A continuación, debemos eliminar los subtotales de representantes, como las filas JUB Total y TPS Total. Para hacerlo, hacemos clic derecho en cualquier celda de iniciales de representante en el informe, como la celda JUB, y desmarcamos el elemento Subtotal “Rep”. El informe actualizado ahora parece un poco más limpio.

encabezados de fila

Para desactivar la fuente en negrita para los valores de región y rep, desmarcamos la siguiente casilla de verificación de la cinta:

• Herramientas de tabla dinámica Diseño Encabezados de fila

Nuestro informe actualizado sigue a continuación.

Diseño personalizado para la región.

A continuación, debemos hacer que los encabezados del grupo de regiones aparezcan en su propia fila. Para hacer esto, cambiaremos el diseño del campo de región. Abrimos el cuadro de diálogo Configuración de campo para el campo de región haciendo clic derecho en cualquier valor de región, como la celda Medio Oeste, y luego seleccionando el elemento Configuración de campo.

En la pestaña de diálogo Diseño e impresión, optamos por mostrar las etiquetas de los elementos en forma de esquema.

A continuación se muestra el informe actualizado, que presenta la etiqueta del grupo de regiones de alcance en su propia fila.

Colocamos los campos de región y representante en la misma columna marcando la casilla de verificación Mostrar etiquetas del siguiente campo en la misma columna (forma compacta) del cuadro de diálogo Configuración de campo.

El informe actualizado, que ahora incluye región y representante en la misma columna, aparece a continuación.

Creamos la fila en blanco entre los grupos de regiones seleccionando la casilla de verificación Insertar línea en blanco después de cada etiqueta de elemento del cuadro de diálogo Configuración de campo.

Formato de grupo de regiones personalizado

A continuación, necesitamos crear un formato no estándar para las filas de la región. Al hacerlo, debemos tener en cuenta que cualquier región futura deberá adoptar este formato automáticamente. Para hacer esto, pasamos el mouse sobre el borde superior de cualquier celda de región, como la celda que contiene el valor Medio Oeste. Cuando pasa el mouse cerca de la parte superior de la celda, el cursor cambia a una flecha hacia abajo. Cuando lo haga, hacemos clic con el mouse y se seleccionarán todas las celdas de la región, como se ilustra a continuación.

Ahora, cualquier formato que apliquemos se aplicará a todas las celdas de región existentes y las futuras. Aumentamos el tamaño de fuente y seleccionamos negrita, como se muestra a continuación.

Anchos de columna

Ahora queremos crear anchos de columnas iguales y evitar que Excel los cambie cada vez que actualizamos el informe.

Después de configurar los anchos de columna como deseamos, en caso nuestro, 12, abrimos el cuadro de diálogo Opciones de tabla dinámica seleccionando el siguiente icono de cinta:

• Herramientas de tabla dinámica Analizar Opciones

En la pestaña de diálogo Diseño y formato, desmarcamos la casilla de verificación Autoajustar anchos de columna al actualizar.

estilo

Ahora, los toques cosméticos finales. Seleccionamos el estilo deseado del siguiente grupo de cintas:

• Herramientas de tabla dinámica Diseño Estilos de tabla dinámica

En nuestro caso seleccionamos el icono Pivot Style Medium 13.

Luego, cambiamos la etiqueta del informe Nombre del representante a Nombre, aumentamos el tamaño de fuente de la fila del encabezado y cambiamos la fuente de la fila del encabezado a negrita.

El informe resultante se muestra a continuación.

Ahora que hemos seleccionado el formato siguiendo los pasos anteriores, el formato permanecerá vigente mientras actualicemos el informe, incluso si aparecen nuevas regiones o representantes en un período posterior. Es bueno poder aplicar un formato tan específico a los informes de tabla dinámica. Las opciones flexibles de formato y diseño disponibles nos ayudan a que nuestros informes tengan el aspecto perfecto.

Si tiene otros consejos de formato de tabla dinámica que le gustaría compartir, publique un comentario a continuación… ¡gracias!

Recursos adicionales

• Archivo de descarga de muestra: PivotTableGroup
• Universidad de Excel Volumen 3