Tablas Dinámicas 4, Campos Calculados

Hemos visto varias entradas sobre Tablas Dinámicas en Excel 2003/2007 para presentar y resumir de forma flexible la información de grandes bases de datos.

A la hora de trabajar con tablas dinámicas, podemos necesitar campos adicionales obtenidos combinando datos de la tabla original. Un ejemplo típico de esta situación es el margen de un producto, pedido, proyecto,...

Imaginemos la siguiente base de datos que sirve de origen a nuestra tabla dinámica.

Se trata de información cualitativa y cuantitativa de una serie de pedidos. Uno de esos campos es el margen del pedido. Por su propia naturaleza el margen es un % que no se podrá combinar a la hora de hacer agrupaciones con las posibles opciones que ofrece la herramienta Configuración de campo de valor, presente en la lista de campos.

Cualquier opción que elijamos (suma, promedio, cuenta, máximo, producto,...) no es valida, ya que el margen de cada posible agrupación en la tabla dinámica debe calcularse como combinación de las sumas de beneficio y ventas y eso no se puede construir con esta opción. Imaginemos que configurásemos un promedio para el margen, tendríamos el siguiente resultado:

Esto obviamente es erróneo ya que el margen de una agrupación no es el promedio de los márgenes individuales.

Para construir campos adicionales que combinen la información de la tabla de origen y la presenten de manera adecuada a cualquier combinación que elijamos utilizamos los Campos Calculados.

Para crear el campo Margen (MG) en nuestra tabla dinámica, hacemos click en cualquier celda perteneciente a la tabla dinámica y acudimos a la ficha Herramientas de Tabla Dinámica. Pestaña Opciones y seleccionamos el comando Cálculos (en Excel 2010) o Fórmulas (en Excel 2007).

En la ventana que nos aparece, podremos nombrar el nuevo campo e introducir la fórmula que lo calcula utilizando los datos presentes en la tabla de datos original.

De este modo tendremos el margen correcto para cualquier agrupación que presentemos en la tabla.

Como de costumbre, mi consejo es la experimentación para terminar de sacarle todo el partido a esta utilidad. Eso si, tened en cuenta que podéis crear cualquier campo calculado con una condición, los datos de partida deben estar presentes en la base de datos de origen.

Tablas Dinámicas 3 (Excel 2007)

Como -comentaba -en la- entrada -Tablas -Dinámicas 1 (Excel- 2003), la -forma de construir tablas dinámicas es muy similar en Excel 2007, si bien tiene bastantes mejoras.

Partiendo de la misma tabla de la que disponíamos en las dos entradas anteriores, abrimos lo que se asemeja al antiguo Asistente para Tablas y Datos Dinámicos. Para ello ejecutamos el icono Tabla Dinámica presente en el bloque Tablas de la pestaña Insertar.

Esto abre una ventana en la que seleccionamos el origen de los datos, (que será el rango que ocupa toda la tabla) y la posición en la que se situará la tabla. En este caso colocaremos la tabla dinámica junto con la tabla origen de datos.

Esto nos lleva a la misma representación de la tabla dinámica que aparecía en Excel 2003, pero con una ventana llamada lista de campos de Tabla Dinámica.

La construcción de la tabla dinámica puede hacerse igual que hacíamos en Excel 2003 (por el método de arrastre a la tabla) o también arrastrándolos a los cuadros que se sitúan debajo de la frase Arrastrar Campos entre las Áreas Siguientes.

Así llevamos el campo deporte de la lista al cuadro de Rotulos de fila que se situa en la parte inferior de la Lista de campos de tabla dinámica.

Luego el campo Ventas al área Sumatorio de Valores..

El campo ciudad al filtro del informe:


Llevamos los tipos de tienda a los Rótulos de Columna.

Igual que comentábamos en Excel 2003, podemos utilizar los filtros presentes en los campos de tabla para afinar más en la presentación de los datos. Con una importante novedad; en Excel 2007 tenemos la opción de seleccionar varios valores a la hora de filtrar.

Si quisiéramos mostrar el Coste y Beneficio independientemente del tipo de Tienda:

La información presente en las tablas puede elegirse en el campo configuración de campo de valor, en él como ya pasaba en versiones anteriores de Excel, podemos elegir entre suma, cuenta, promedio… Para hacer estas selecciones, podemos acceder al campo configuración del campo de la cinta de opciones de la pestaña opciones, o como se ve en la imagen siguiente, a través de activar el desplegable en el campo, dentro de la ventana lista de campos de tabla dinámica.

En la primera pestaña tenemos los tipos de cálculo posibles para resumir los datos del campo seleccionado.

Cada vez que seleccionamos una celda dentro de la tabla dinámica, aparece la pestaña de Herramientas de Tablas Dinámicas. Esta pestaña se compone de dos subpestañas, Opciones y Diseño.

En la pestaña opciones, tendremos ordenados a lo largo de distintos compartimentos de la cinta de opciones, herramientas para configurar la tabla. Algunas de ellas son muy útiles y les dedicaremos futuras entradas en más detalle. Por ejemplo la opción de creación de Campos Calculados, o los Gráficos Dinámicos.

La pestaña de diseño como su propio nombre indica afecta a temas fundamentalmente estéticos.

Las entradas sobre tablas dinámicas que he expuesto en esta serie, tienen una intención introductoria. El tema de las Tablas dinámicas, es muy amplio y requeriría de muchas más indicaciones. Como he dicho anteriormente, dedicaré futuras entradas a profundizar en este tema. Hasta entonces mi consejo es la práctica y la investigación de forma autodidacta. No obstante, como siempre, podéis hacerme llegar las dudas que os hayan podido quedar tras en este tema.

Tablas Dinámicas 2 (Excel 2003)

En esta- entrada- continuamos -con -el mismo -ejemplo -visto en Tablas Dinámicas 1 (Excel 2003). Continuaremos explorando las opciones en la presentación y resumen de la información en las Tablas Dinámicas de Excel 2003. La siguiente entrada tratará de las modificaciones y mejoras en Excel 2007.

En la entrada anterior, Tablas Dinámicas 1 (Excel 2003), habíamos ignorado la barra de herramientas que aparecía utilizando el asistente para tablas y datos dinámicos.

Nos fijaremos en el comando Dar formato al informe, que aparece resaltado en la imagen al lado del desplegable Tabla dinámica.

Ejecutando ese comando, aparece una ventana en la que tenemos una gran variedad de formatos predefinidos para presentar la información. Seleccionamos por ejemplo uno de los situados al final de la lista.


La pega de estos diseños predefinidos es que hay que completar el formato de los números para que aparezcan los separadores de miles y los decimales, alineándo los números a la derecha. (Como se ve en la imágen siguiente, resultado de la acción anterior, los números aparecen centrados y sin puntos ni comas). Para mejorar esta presentación, vamos a la opción de menú Formato y seleccionamos la opción Celdas.


Esto nos abre la típica ventana de formato de celdas, en la que elegimos la opción deseada. (Considero que no es necesario profundizar en ello).


Una vez arreglado el tema del formato de los números, el aspecto de la tabla es el siguiente:

Hay otra manera de manipular los resultados de la tabla que no habíamos explorado hasta el momento es utilizando el comando diseño presente en el asistente para tablas dinámicas.

Al comando Diseño, llegamos situando la celda activa dentro de la tabla dinámica. Esto hace que aparezca el asistente para tablas dinámicas. Expandiendo la lista desplegable Tabla dinámica, podremos seleccionar el asistente.

Y una vez en el asistente, podremos ejecutar el comando Diseño...

Obtenemos la ventana diseño, en la que aparece la representación de la tabla dinámica con el diseño actual, y la lista de campos disponibles que podremos manipular arrastrándolos a las distintas áreas de la tabla.

En nuestro caso, vamos a modificar la tabla de modo que además de la suma de ventas, aparezca la suma de costes y la suma de beneficios.

Esto dará como resultado la tabla con el diseño de la información deseado.

Recordemos que disponemos de distintos filtros presentes en los campos página, filas y columnas que hacen que podamos ir al minimo detalle en la presentación de los datos. En la imagen vemos los datos para los deportes de Fútbol, en la ciudad de Barcelona, tienda Tipo B.

Por último, una función muy útil de las tablas dinámicas es que podemos ver el máximo detalle para un conjunto de información seleccionado. Si hacemos doble click sobre la celda que contiene el dato nos aparece una nueva pestaña con el desglose de toda las líneas que componen ese dato.

Es aconsejable eliminar dicha pestaña una vez analizada la información, así no saturamos el libro con hojas que no vamos a utilizar.

Tablas Dinámicas 1 (Excel 2003)

Las tablas dinámicas son informes modificables que pueden resumir grandes cantidades de información. Se pueden alterar sus filas y columnas para ver diferentes resúmenes de los datos. Aplicar filtros, centrarse en determinada información…

Pueden crearse también gráficos dinámicos para representar toda esa información gráficamente, e incluso se puede colocar un informe de tabla dinámica en una web, de modo que los visitantes puedan ver los datos e interactuar con ellos desde la propia web. Pero sobre estos temas hablaré en futuras entradas.

Creación de una tabla dinámica:

La creación de una tabla dinámica es un proceso de gran utilidad pero que requiere de cierta práctica. El mecanismo es muy similar tanto en Excel 2003 como Excel 2007. Comenzaré con la creación en Excel 2003 para la que dedicaré dos entradas una para tablas simples y la siguiente para las tablas con un grado de complejidad mayor. Posteriormente, dedicaré una entrada para el Excel 2007. Para ello utilizaré siempre el mismo ejemplo de Tabla Dinámica con los mismos datos.

Habitualmente utilizaremos tablas de considerables dimensiones, pero para este ejemplo, como es habitual, utilizaremos una simplificación con intención didáctica.

Supongamos que tenemos una cadena de tiendas de deportes, presente en varias ciudades, en algunas de ellas con varias localizaciones. Hemos dividido las ventas según los distintos deportes, y disponemos también de los costes y el beneficio resultante en un día determinado. Como se puede intuir, hay muchas maneras de presentar y resumir toda la información disponible.

Partimos de un libro de excel en el que en la primera pestaña (Datos de Origen), tenemos la tabla con la información, los requisitos de esa tabla son elementales. Una serie de columnas con encabezado en la primera fila. Para la tabla dinámica, creamos la pestaña Tabla Dinámica.

Lo mejor para crear una tabla dinámica en Excel 2003 es utilizar el asistente para tablas y gráficos dinámicos. Este asistente se encuentra en Datos / Informes de Tablas y Gráficos Dinámicos.


Esto hará que aparezca la ventana con el primer paso del asistente. Por el momento seleccionaremos las opciones de Lista o base de datos de Microsoft Office Excel (que en este caso está en la pestaña Origen de Datos), y Tabla dinámica.
.

Hecho esto pulsamos Siguiente, lo que nos lleva al paso 2 del asistente. En él, introducimos el rango donde están los datos a tratar.
.

Pulsando sobre siguiente, accedemos al sguiente paso en el que establecemos el lugar en el que se situará la tabla.

.

En este caso, la esquina superior izquierda se situará en la celda B2 de la pestaña Tabla Dinámica.

Pulsando sobre Finalizar, habremos terminado de definir el marco que rige la tabla dinámica. En este punto, podemos definir cómo queremos que se nos presente la información. Para ello aparece la representación de la tabla dinámica con la lista de campos disponibles (columnas de la tabla Origen de Datos).


Estos campos se pueden llevar a la tabla haciendo click sobre el campo y sin soltar, arrastrarlo hasta el área de la tabla donde queremos que aparezcan. En este caso arrastramos el campo Deporte y lo llevamos a los campos de fila.
.

Así, tenemos en nuestra tabla la lista con todos los deportes posibles (apareciendo una sola vez).

Del mismo modo podemos llevar el campo Ventas a el área de datos, con lo que obtendremos el resumen de ventas por deporte.

.

Podemos incluir más criterios por los que mostrar la información arrastrando otro campo a la zóna de los campos de página, esa zona se encuentra colocando el campo en la celda superior izquierda de la tabla dinámica.


En nuestro caso hemos arrastrado el campo ciudad, podremos elegir la ciudad para la que queremos ver el resumen anterior, o si lo queremos ver para todas. Para ello, sólo debemos utilizar el filtro practicable del campo Ciudad.
.

Así podríamos ver la información anterior individualizada por ciudad, pero también podríamos añadir más campos en el caso de que estuviéramos interesados en una información más detallada. Por ejemplo, llevando el campo Tienda a la zona Campos en Columna obtenemos el desglose de las ventas por deporte y tienda, para la ciudad de Madrid.

.

Observe que tanto en los campos fila, columna y página podemos utilizar los filtros disponibles y acotar la información de la tabla. Por ejemplo, la información anterior para el tipo de tiendas A de todas las ciudades.

Hasta ahora hemos jugado con las posibilidades de la tabla dinámica sin utilizar la opción de diseño, ni los formatos preestablecidos. De estos temas me ocuparé en la siguiente entrada. De momento considero que para aquellos que no han trabajado aún con tablas dinámicas, experimentar con las posibilidades ofrecidas hasta ahora da suficiente juego como para estar ocupados durante un tiempo.