5.3

Organización y análisis de datos. Informes de tabla dinámica

Índice del tema:

1. Creación de un informe de tabla dinámica 2. Reajuste de un informe de tabla dinámica 3. Más elementos y aspectos de los informes de tabla dinámica

Las hojas de cálculo son un buen medio para almacenar y analizar datos pero, dependiendo de la cantidad y disposición de la información que contengan, no es siempre fácil darse cuenta de sus implicaciones a simple vista. Los informes de tabla dinámica, de forma sencilla y rápida, ordenan las distintas porciones de información, permiten cambiarlas de lugar para ver cómo se relacionan entre ellas con objeto de facilitar su análisis, y proporcionan resúmenes de los datos (sumas, promedios, cuentas, etc.) sin necesidad de usar fórmulas.

Los informes de tabla dinámica son tablas interactivas que combinan, comparan y resumen con rapidez grandes volúmenes de datos. Se pueden rotar sus filas y columnas para ver distintos resúmenes de los datos y se pueden visualizar los detalles de áreas específicas de interés. Conviene usar un informe de tabla dinámica cuando es preciso analizar totales relacionados, especialmente cuando se tiene una gran cantidad de cifras que sumar y se desean comparar varios hechos sobre cada una de estas cifras.

Nota: en lo sucesivo abreviaremos las menciones a "tabla dinámica" con el acrónimo TD y a "informe de tabla dinámica" con la expresión "informe TD".

Como los informes TD son interactivos, se puede modificar fácilmente la vista de los datos para obtener más detalles o calcular distintos resúmenes. En lugar de escribir fórmulas más o menos complejas se dispone de un asistente para crear una tabla interactiva que extraiga, organice y resuma automáticamente la información para hacer comparaciones, detecte patrones y relaciones, y analice tendencias.

En una TD se pueden realizar rápidamente las siguientes operaciones:

Agregar o quitar datos Consultar grandes cantidades de datos de muchas maneras diferentes y cómodas Reorganizar el diseño Ver un subconjunto de datos Mostrar los detalles necesarios

Calcular resúmenes

Así, podríamos decir que las TD agrupan en una sola utilidad la mayor parte de las funciones y posibilidades de las distintas utilidades vistas por separado en los temas anteriores: ordenar, filtrar, agrupar y esquematizar, obtener distintas clases de resúmenes, etc.

1. Creación de un informe de tabla dinámica

Recordemos por un momento lo ilustrado en la sección 5.2. Subtotales anidados del

tema anterior. Sobre nuestro conjunto de datos de ejemplo -referente a número de alumnos y grupos de centros en sus distintas etapas, familias y cursos- obtuvimos un resumen de subtotales de alumnos y grupos por centro y etapa. Para ello, previamente, debimos ordenar el conjunto de datos de forma que las distintas filas de datos de un mismo centro quedasen consecutivas. Vamos a reproducir ahora la obtención de estos resúmenes pero utilizando únicamente un informe TD que nos proporcionará, además, la posibilidad de realizar de forma muy sencilla nuevas operaciones con los datos.

Partamos de nuestro conjunto original de datos sin preocuparnos especialmente de su disposición y ordenación, más allá de las cuestiones elementales que hemos comentado más de una vez, relativas a colocar datos de la misma clase en cada columna y de poner títulos coherentes en el encabezado de cada una:

Para crear un informe TD de este conjunto de datos utilicemos la opción de menú Datos > Informe de tablas y gráficos dinámicos:

Esto da paso al Asistente para tablas y gráficos dinámicos, que consta de 3 pasos:

En el primer paso se atiende a las siguientes cuestiones:

1. ¿Dónde están los datos que desea analizar?; por el momento no prestaremos atención más que a la opción "en una Lista o base de datos de MS Office Excel",

ya que los datos que queremos analizar están en la propia hoja de cálculo de la que partimos; y

2. ¿Qué tipo de informe desea crear?; de la misma manera, no prestaremos atención más que a la opción Tabla dinámica.

Al pulsar Siguiente el asistente avanza al segundo paso, en el que se especifica Dónde están los datos que desea usar, es decir, en qué hoja y en qué celdas de la hoja están los datos. Si al iniciar el asistente ya teníamos seleccionada una celda con datos de una hoja, el asistente es capaz de localizar automáticamente el conjunto de datos completo (en definitiva, localiza todas las celdas adyacentes a la seleccionada que no están vacías o, siendo más concretos, todas las celdas vecinas a ella con datos hasta que se encuentra una fila o una columna completamente vacías):

Nota: obsérvese que en la hoja de ejemplo estaba seleccionada la celda A1.

Si se desea cambiar el conjunto o rango de datos propuesto por Excel, se puede

utilizar el botón Expandir/Contraer diálogo para comenzar a escoger otro rango. Si no es así, se puede hacer clic en Siguiente para avanzar al tercer paso, donde se responde a la pregunta ¿Dónde desea situar el informe de tabla dinámica?:

En principio seleccionaremos Hoja de cálculo nueva, de forma que se creará una nueva hoja en el libro actual donde se situará el informe. Si se prescinde por el momento de usar los botones Diseño y Opciones y se hace clic en Finalizar se crea la nueva hoja (véase Hoja1 en la siguiente figura) donde se definen diferentes áreas en las que más tarde se situarán los datos del informe:

Como se observa, se ha creado una hoja nueva (1) donde se situará el informe, se ha elaborado una lista de campos de TD (2) al detectarse las diferentes categorías de datos existentes a partir de los títulos de la columnas del conjunto de datos, y se han creado cuatro áreas vacías para el informe, donde se situarán los denominados:

Campos de fila y de columna (3,4) Campos de datos (o de valor) (5) Campos de página (6)

En las áreas de campos de fila y columna se situarán los campos de TD que nos convenga presentar alineados horizontal o verticalmente en el informe, mientras que en los campos de datos (o de valor) se situarán los campos de la TD de los que queremos obtener un resumen, ya sea un total, un promedio, etc.

Por ejemplo, un campo de fila puede ser la denominación del centro, un campo de columna puede ser la etapa y un campo de valor puede ser la suma de alumnos, lo que

nos proporciona un informe tabular sencillo del total de alumnos que hay en cada etapa de cada centro; algo así como:

Etapa (campo de columna)

Centro

(campo de fila) ESO BAC ...

Centro 1 Suma de

nº de Alumnos (campo de valor)

Suma de nº de Alumnos

Suma de nº de Alumnos

Centro 2 Suma de

nº de Alumnos Suma de

nº de Alumnos Suma de

nº de Alumnos

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

Los campos de página son similares a los de fila y columna, aunque a la hora de imprimir el informe se podrán separar fácilmente en distintas páginas. Si tenemos centros de distintas provincias un campo de página bien podría ser, por ejemplo, la provincia, de forma que los campos de fila, columna y datos quedan referidos a cada provincia en particular:

Provincia (campo de página)

Palencia

Etapa (campo de columna)

Centro

(campo de fila) ESO BAC ...

Centro 1

Suma de

nº de Alumnos (campo

de valor)

Suma de nº de Alumnos

Suma de nº de Alumnos

Centro 2

Suma de

nº de Alumnos

Suma de nº de Alumnos

Suma de nº de Alumnos

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

Nota: No debe preocuparnos ahora el comprender exactamente qué son y qué significan estos distintos tipos de campo, ya que lo comprenderemos a medida que se vaya definiendo la TD.

Coloquemos como campo de fila, en primer lugar, el CENTRO (recordemos que esta categoría hace referencia al código de centro). Para ello no hay más que hacer clic sobre este campo en la lista de campos y arrastrarlo hasta el área de campos de fila:

Una vez soltado este campo se obtiene:

es decir, se ha creado una fila por cada código de centro diferente existente en los

datos y, nótese, en la última fila se ha añadido un Total general (aunque no sepamos todavía para qué cometido tiene).

De forma análoga, arrastremos el campo DENOMINACIÓN al área de campos de fila:

Como resultado se obtiene:

es decir, tras cada código se añade la denominación del correspondiente centro y, además, debajo de la fila de cada centro se añade una de Total. Podemos preguntarnos ¿qué sentido tiene que aparezca una fila de Total para cada centro y una de Total general si no hemos manejado todavía hay ningún dato que pueda ser totalizado? El objetivo es dejar preparado el informe apara que, cuando se definan campos de valor, se sitúen automáticamente en estas filas los resúmenes necesarios (totales, promedios u otros cálculos) de los datos.

Nota: observemos que los campos CENTRO y DENOMINACIÓN se resaltan en negrita en la lista de campos de la TD cuando han pasado a formar parte del informe.

Repetimos la misma operación con ETAPA:

Nota: si la Lista de campos de TD desaparece en algún momento, no hay más que hacer clic en una celda de las áreas del informe TD para recuperarlo. Si, a pesar de esto, la lista sigue sin aparecer, se puede hacer clic en la celda con el botón secundario, con lo que aparece el menú

contextual del informe TD:

Haciendo clic en la opción Mostrar lista de campos la lista aparece de inmediato.

Coloquemos ahora GRUPOS como campo de Valor o campo de Datos, es decir, arrastremos este campo desde la lista de ellos hasta el área de datos de la TD:

Cuando acabamos de "soltar" este campo en e área de datos se genera una columna de Total de forma que, para cada centro y para cada etapa que se da en el mismo, se muestra el nº total de grupos:

Si nos desplazamos hasta el final de la TD observaremos el Total general, es decir, el nº total de grupos existentes en el conjunto de datos de origen:

Llegados a este punto fijémonos que, un poco más atrás, cuando se introdujo ETAPA en el área de campos de fila, apareció una fila de Total para cada DENOMINACIÓN (al igual que cuando introdujimos el campo DENOMINACIÓN apareció para cada CENTRO). Y fijémonos también que el total de cada CENTRO debe coincidir, obviamente, con el total de cada DENOMINACIÓN puesto que la relación entre código de centro y denominación es biunívoca. De modo que podemos prescindir absolutamente del campo de Total para DENOMINACIÓN ya que, al no aportar nada significativo, no hace si no disminuir la legibilidad al informe. ¿Cómo conseguimos retirarlo del informe TD? Sencillamente

haciendo clic con el botón secundario (derecho) del ratón y, en el menú contextual que aparece, eligiendo la opción Ocultar:

de modo que permanecen únicamente las filas de Total de CENTRO:

Reflexionemos un poco sobre lo realizado hasta el momento. Hemos conseguido, sin utilizar ningún cálculo explícito y de forma muy rápida, obtener los totales de los grupos existentes en cada etapa de cada centro, en cada centro, y en el conjunto de todos los centros y, aunque no lo habíamos mencionado todavía, como resultado colateral, los resultados se visualizan automáticamente ordenados por código de centro y por etapa.

Avancemos algo más. Añadamos el campo ALUMNOS al área de campos de valor o de datos:

Así obtenemos:

De modo que al situar este nuevo campo en el área de datos de la TD, ante la columna Total se ha introducido una nueva columna denominada Datos en la que, por cada etapa, se presentan dos filas de totales: Cuenta de GRUPOS y Cuenta de ALUMNOS. Es decir, ahora el informe presenta dos tipos de totales: totales de grupos y totales de alumnos.

Este último paso nos ha podido dejar algo extrañados, preguntándonos: ¿qué significado tiene el título Cuenta de ... en los totales? ¿Y, aún más, por qué razón aparentemente "misteriosa" el total de alumnos es igual al total de grupos cuando esperaríamos que el total de alumnos en cada etapa fuese de decenas o centenas?

Las respuestas a ambas preguntas están relacionadas. Cuenta de ... significa que cada total que la TD ha calculado automáticamente hace referencia al nº de veces que aparece un elemento en el conjunto de datos. Así, Cuenta de GRUPOS: 4 en la fila:

significa que en los datos originales aparece 4 veces la etapa BAC del centro JUANA DE

PIMENTEL. Y, por ello, también Cuenta de ALUMNOS: 4 significa lo mismo. Es decir, que en los datos hay cuatro registros de este centro correspondientes a dicha etapa. Así pues, nuestra posible impresión original de que el total mostrado directamente en la TD es una suma de grupos y de alumnos es errónea.

Nota: recordemos del tema anterior que la función de análisis de datos Cuenta da como resultado el nº de veces que aparecen datos en un conjunto, no la suma de los mismos. Lo explicado en el párrafo anterior es consecuencia directa de esto.

De acuerdo, pero yo quiero obtener la suma del nº de alumnos, ya que es lo que tiene sentido para mi, y no el nº de veces que aparece una etapa en cada centro. ¿Cómo lo consigo? Es sencillo: sólo tengo que transformar la Cuenta en Suma. Para ello hago clic con el botón secundario sobre Cuenta de ALUMNOS, por ejemplo, en una de las filas -no importa cuál-, elijo la opción Configuración de campo:

Se abre entonces la ventana Campo de tabla dinámica, donde se puede configurar al detalle un campo de la TD. En particular, en la lista Resumir por: vemos que figura Cuenta; si escojo Suma en su lugar:

--->

obtengo precisamente el total de alumnos que necesito:

Nota: observemos que en el cuadro Resumir por de la ventana Campo de tabla dinámica aparece precisamente la lista de las funciones estudiadas en la sección 2.2. Funciones de resumen

para el análisis de datos del tema anterior, de modo que ya conocemos lo fundamental de su cometido.

Al convertir el resumen de ALUMNOS desde la función Cuenta a la función Suma podemos caer en la cuenta de lo siguiente: ¿podría suceder que la Cuenta del campo GRUPOS no dé realmente como resultado el total de grupos de la etapa? Si reflexionamos sólo un momento llegaremos a la conclusión de que, efectivamente, la Cuenta de

GRUPOS para cada etapa no tiene que coincidir necesariamente con el total de grupos existentes en la misma, aunque puede que en muchos casos coincida. Lo que sí coincidirá será la Suma de GRUPOS. Así, actuando como antes, podemos configurar el campo de resumen de GRUPOS como Suma de GRUPOS:

Nótese que precisamente en la etapa BAC del centro Juana de Pimentel se observa ahora que Suma de GRUPOS es 5 mientras que Cuenta de GRUPOS era 4. Esto sucede porque existen 4 filas de datos de BAC para este centro y en una o más de estas filas el número de grupos que figura en la columna GRUPOS es mayor que 1 (de hecho hay una fila donde el nº de grupos es 2) -téngase en cuenta que la etapa tiene a su vez varias modalidades y cada modalidad varios cursos-.

De modo que, a pesar de la sencillez de uso de la TD, no hay que perder nunca de vista el sentido de los datos y de las operaciones que manejamos. Pero, por otro lado, debemos subrayar la sencillez, la rapidez y la potencia que aporta el informe TD para la organización y análisis de los datos. Con simples operaciones mecánicas de "arrastre" de campos y de elección de opciones de menú, sumamente intuitivas, conseguimos ordenar los datos y obtener resúmenes de distintos tipos sin necesidad de acudir a operaciones de ordenación propiamente dichas, de filtrado o de introducción y tratamiento de fórmulas matemáticas.

2. Reajuste de un informe de tabla dinámica

En la sección anterior al mismo tiempo que creábamos el informe TD utilizábamos algunas operaciones básicas de modificación del informe, como el cambio de la función de resumen o la ocultación de un campo de resumen. Abundaremos en esta sección en los diferentes tipos de operaciones que podemos realizar para modificar y refinar el informe.

2.1. Modificación del conjunto de datos de origen de la TD

Imaginemos que al conjunto de datos original se le han añadido más filas que incorporan datos de nuevos centros o nueva información de los centros existentes previamente. De forma natural nos pueden surgir varias cuestiones:

¿El informe TD "reconoce" e incorpora automáticamente los datos añadidos? Si fuera así, ¿actualiza automáticamente los resultados visualizados?

Pero si no es así, ¿cómo puedo, en primer lugar, modificar el conjunto de datos sobre el que opera la TD que ya tengo elaborada?

Para modificar el conjunto de datos sobre el que opera la TD podemos seleccionar alguna de sus celdas, hacer clic con el botón secundario para desplegar su menú contextual y seleccionar la opción Asistente para tablas dinámicas:

Nota: en este menú contextual vemos algunas opciones más, que trataremos más adelante.

Se trata del mismo asistente ya utilizado en la sección anterior. Ahora se inicia directamente en el tercer y último paso, pero haremos clic en el botón Atrás:

para retroceder al segundo paso:

Si en el campo Rango (de los datos que se desea usar) se especifica uno como A:I o $A:$I, que significa "tomar todas las filas de datos de las columnas comprendidas entre la A y la I" y se hace clic en Finalizar, se incorporan todos los datos nuevos que haya en el rango y se actualiza el informe TD:

Nota: en la imagen de ejemplo usada al crear la TD se veía que el rango de datos manejado inicialmente era: $A1:$292, lo que quería decir que sólo se usaban las filas de datos comprendidas entre la 1 y la 292 (ya que no había más datos). En el rango $A:$I, al no especificarse filas de inicio y fin, se obtiene una ventaja: cualquier dato que se incorpore más tarde en filas posteriores a la 292 ya estará intrínsecamente incluido en la TD.

Al emplear este nuevo rango existirán con seguridad filas de datos en blanco, sin datos. Piénsese que la hoja de cálculo de MS Excel contiene normalmente 65536 filas y no es ni mucho menos habitual que todas ellas estén en uso. Así pues, al actualizarse el informe TD se habrán incluido las nuevas filas en blanco (véase el sombreado en la imagen siguiente):

Observemos que estas filas, en cuyos campos figura el dato (en blanco), no contribuyen a los totales: Suma de GRUPOS y Suma de ALUMNOS permanecen vacíos y, por tanto, no alteran la suma, la cuenta, o el cálculo que corresponda.

Supongamos que ahora añadimos a los datos nuevas filas correspondientes a otro centro, a partir de la fila 292:

Recuperando una de las cuestiones anteriores: ¿el informe TD mostrará incluirá sin más los nuevos datos en los resultados? La respuesta es no. Si acudimos a Hoja 1 veremos que el informe no ha cambiado. Es necesario "ordenar" que el informe se actualice. Para

hacerlo no hay más que utilizar la opción Actualizar datos del menú de contexto de la TD:

lo que obliga a recalcular los resultados:

que son consiguientemente mostrados en el informe (véanse las filas sombradas correspondientes al nuevo centro añadido a los datos):

La discusión anterior es también válida cuando se modifica o se borra el contenido de las filas, no sólo cuando se añade.

Supongamos ahora que añadimos nuevas columnas al rango de datos. Por ejemplo, supongamos que añadimos una columna de LOCALIDAD:

¿Cómo conseguir que los datos de esta columna pasen a ser considerados por el informe TD? De manera análoga a como hicimos antes cuando modificamos el rango de filas, acudimos al asistente para tablas dinámicas y retrocedemos hasta su segundo paso:

y en éste escribimos el rango $A:$J en lugar de $A:$I, es decir, le añadimos la columna J.

Nota: en la imagen anterior el texto ALUMN_GRUP! que antecede a $A:$J no es más que la forma que tiene Excel de referirse a la hoja donde están los datos: ALUMN_GRUP es el nombre de la hoja.

Tras actualizar el informe como antes, mediante la opción Actualizar datos del menú de contexto de la TD:

el informe TD ya tiene en cuenta la nueva columna y ésta aparece automáticamente en la lista de campos de TD. Pero, ¿quiere esto decir que la TD mostrará inmediatamente los nuevos datos de LOCALIDAD? Negativo. Si queremos que se muestren en el informe, debemos añadir este campo al área de campos de fila:

de modo que se obtiene:

Nótese en la imagen anterior que además de incluirse la nueva columna, se añaden para cada localidad un par de filas de totales, Suma de GRUPOS y Suma de ALUMNOS, para la localidad. Como estos totales son los mismos que para el centro, podemos prescindir de ellos. Recordemos que ello puede hacerse ocultando estos campos de resumen mediante la opción Ocultar del menú contextual de dichos campos.

Así pues, por el momento tenemos un informe que muestra, para cada centro, su código, denominación y localidad y, para cada una de sus etapas, proporciona el nº total de grupos y de alumnos:

Igual que hemos añadido el campo de fila LOCALIDAD, podríamos retirarlo del informe. Para ello no hay más que arrastrar en sentido inverso el campo desde su lugar en el área de filas hasta la lista de campos de TD:

o bien, hacer clic con el botón secundario sobre el campo de fila y usar la opción Ocultar:

de modo que dejamos la tabla como antes.

2.2. Filtrado del informe TD

¿Podemos mostrar u ocultar selectivamente y a conveniencia determinados elementos del informe TD? Efectivamente. Veamos algunos casos.

Recordemos que antes, al incluir todas las filas de la hoja en el informe TD, aparecían determinadas filas con datos (en blanco):

¿Cómo podemos evitar que éstas se muestren, ya que no aportan nada? Fijémonos

que en la cabecera de cada campo existe un botón con punta de flecha . Si se hace clic en él (por ejemplo, en la del campo CENTRO) se despliega la lista completa de valores que toma el campo:

--->

Se puede ocultar en el informe cualquiera de los valores de esta lista desmarcando la casilla asociada. En la figura anterior se ha desmarcado la casilla asociada a (en blanco), de forma que se consigue ocultar todas las referencias a un código de centro que toma el valor (en blanco), es decir, todas las referencias a las filas sin datos:

Nota: obsérvese en la imagen anterior que tras los datos del último código de centro (5009698) figuran ahora los totales generales y no los valores (en blanco).

De manera análoga podemos filtrar los elementos de cualquier otro campo, por ejemplo, el campo de fila ETAPA:

para quedarnos sólo con los elementos que convengan (sólo BAC y ESO, por ejemplo):

Tras filtrar un campo no se observa en éste ningún signo especial que indique su filtrado (no se aprecia un marcado del título del campo en color azul, como sí sucedía en el caso de los autofiltros), de modo que es preciso ser consciente de que se ha realizado tal filtrado. A veces es buena idea incluir un comentario de recordatorio. Para ello se puede, por ejemplo, seleccionar el campo filtrado y hacer uso de la opción de menú Insertar > Comentario:

de modo que se abre un cuadro de texto donde se puede escribir y dar formato al comentario:

Para prescindir del filtrado de un campo no hay más que marcar la casilla de verificación (Mostrar todo), con lo que reaparecen en la TD todos los elementos de dicho campo:

--->

Y de la misma manera que se filtra un campo de fila se puede hacer con un campo de datos, por ejemplo, con la Suma de GRUPOS:

--

>

En este caso, al filtrar uno de los campos de datos de forma que quede sólo otro, desaparece la columna Datos (situada tras ETAPA en las imágenes anteriores) y aparece el único campo de datos restante sobre de la fila de títulos de campos de fila (véase Suma de ALUMNOS encima de CENTRO).

Imaginemos por un momento que en la TD tenemos tres campos de datos. Un campo de datos más que antes, el promedio de las ratios, por ejemplo:

En este caso, al filtrar uno de los campos de datos (uno de los totales) todavía se siguen viendo los otros dos en su posición original:

---

>

Ahora ha desaparecido el elemento Suma de GRUPOS de la lista de elementos de Datos:

De forma que no podríamos recuperar la Suma de GRUPOS marcando su correspondiente elemento en esta lista. Esto sucede porque Suma de GRUPOS no es realmente un elemento de un campo, sino un campo de datos. Así pues, la forma de que vuelva a aparecer sería arrastrarlo desde la lista de campos como hemos visto antes.

2.3. Reorganización del informe TD

Aunque hemos mencionado al principio la existencia de los campos de columna, hasta el momento no los hemos utilizado, limitándonos a emplear únicamente campos de fila y de datos. Pasemos ahora algunos de los campos a disposición de columna.

Recuperemos el informe TD en el que teníamos dos campos de datos: Suma de GRUPOS y Suma de ALUMNOS. Si arrastramos el título del campo de datos, Datos, hacia la derecha, hacia la zona de Total:

lo que obtenemos es una disposición de los totales en formato de columna:

lo que podría resultar más conveniente para la presentación de los datos o un posible tratamiento posterior.

Hasta aquí no hemos usado todavía en realidad campos de columna, sino que hemos dispuesto los totales en forma de columna. A continuación sí convertiremos un campo de fila en campo de columna. Lo haremos con ETAPA. Si arrastramos ETAPA hacia la derecha, hacia Total:

conseguimos que los distintos valores de la ETAPA (BAC, ESO, etc.) se organicen en forma de columna:

Ahora ETAPA está situado en el área de campos de columna y esta disposición puede resultar también muy conveniente según el uso que necesitemos de los datos.

Nota: podríamos haber conseguido esta misma disposición directamente cuando creamos la TD si hubiésemos arrastrado el campo ETAPA al área de los campos de columna:

Puesto que con esta disposición tenemos las filas de total duplicadas, podríamos ocultar los totales de centro:

para obtener un informe TD bastante "limpio" y legible:

....

Todavía podríamos poner los campos de datos en disposición de columna:

para obtener esta distribución:

en la que para cada centro se tienen, por un lado las sumas de grupos para cada etapa en un conjunto de columnas y, por otro, la suma de alumnos. O podríamos llevar ETAPA a la izquierda de Datos:

para obtener la suma de grupos y de alumnos para cada etapa en disposición de columna:

Como ya hemos comentado, el utilizar una u otra disposición del informe TD dependerá del objetivo que queramos alcanzar al analizar los datos.

Por otro lado, si interesase que la lista de centros se presentase según un orden alfabético en lugar de por un orden basado en el código de centro, no habría más que colocar el campo DENOMINACIÓN a la izquierda de CENTRO:

en cuyo caso obtenemos:

2.4. Ordenación de los datos en el informe TD

Comentamos poco después de crear la TD que la información aparece automáticamente ordenada, aunque no hayamos necesitado configurar esto explícitamente. Recordemos justamente la ilustración anterior, donde los centros se presentan ordenados alfabéticamente de forma ascendente por su denominación nada más situar el campo DENOMINACIÓN en primer lugar. Este orden predeterminado, no obstante, puede ser configurado para cada campo. Reordenemos, como ejemplo, los distintos elementos o valores que toma el campo ETAPA. Utilizamos la opción Configuración de campo de su menú contextual:

y en la ventana Campo de tabla dinámica hacemos clic en el botón Avanzado:

Probablemente esté seleccionada la auto-ordenación (alfabética) Ascendente, de modo que las etapas se presentan en el orden BAC-CF-ESO-IP. Pero si ponemos la auto-ordenación en modo Manualmente podremos arrastrar los elementos de este campo para ordenarlos a conveniencia; en la siguiente imagen desplazamos ("arrastramos") ESO a la izquierda de BAC:

de forma que obtenemos:

Véase que la ordenación de los elementos de ETAPA afecta tanto a la columna de grupos como a la de alumnos.

De la misma manera se podrían ordenar cualesquiera otros elementos del resto de campos.

2.5. Campos de página

Aunque ya hemos mencionado previamente este tipo de campos, todavía no hemos hecho uso de ellos. Constituyen un poderoso método de organización de la información en el informe TD.

Para introducir este tipo de campos supongamos que queremos organizar el informe TD por turnos de enseñanza. Así, podríamos arrastrar el campo TURNO a la zona de campos de fila:

de modo que resultaría:

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

Nota: por conveniencia para la representación de la imagen anterior se ha ordenado el campo TURNO manualmente, arrastrando los elementos N y V por encima de M. En el orden predeterminado, ascendente, la secuencia de turnos sería D-M-N-V en lugar de D-N-V-M. Recordemos: D=distancia, M=matinal, N=nocturno, V=Vespertino.

Pero probemos, en su lugar, a arrastrarlo a la zona de campos de página:

En este caso se tiene:

de modo que si hacemos clic en la punta de flecha situado a la derecha de (Todas) se despliega una lista que contiene las distintos valores que puede tomar el turno; a ellos se le añade la opción (Todas) y (en blanco):

---

>

Si se selecciona la opción (Todas) el informe TD incluye los datos de todos los turnos sin distinción de los mismos. La opción (en blanco) corresponde a las filas vacías incluidas en el rango de datos, de modo que si se selecciona ésta el informe es de esperar que esté completamente vacío. Y si se selecciona cualquiera de las otras, por ejemplo, V(espertino) en el informe se presentan sólo los datos correspondientes a tal turno:

Así pues, un campo de página actúa como un filtro que limita la información que presenta el informe TD a la relacionada con la opción elegida en la lista desplegable.

Nota: obsérvese en la imagen anterior que sólo aparece la ETAPA CF, ya que en los centros que tienen este turno sólo se imparten ciclos formativos.

Pero no es ésta su única ventaja ni la más importante, ya que este filtrado también se podría haber conseguido situando TURNO como campo de fila y luego seleccionando el

elemento V en la lista que se despliega al hacer clic en :

La mayor ventaja que suponen los campos de fila es la que veremos a continuación. Para sacar provecho de ella mostremos primero la barra de herramientas Tabla dinámica:

Al hacer uso de esta opción de menú, en el pie de la hoja de cálculo se muestra una barra de herramientas que proporciona acceso rápido a muchas de las funciones de TD que ya hemos ido utilizado hasta el momento a base de menús de contexto:

Pero ahora nos fijaremos sólo en la opción de menú de esta barra denominada Tabla dinámica. Al hacer clic en ella se despliega un menú:

cuya última opción es Mostrar páginas... Al seleccionarla se abre una ventana del mismo nombre que muestra una lista de todos los campos de página que hay en el informe TD (sólo uno en nuestro informe de ejemplo, TURNO):

Al pulsar Aceptar se crean tantas hojas como elementos o valores toma el campo TURNO -D, M, N, V y (en blanco)- y en cada una de ellas se presenta el informe TD correspondiente a sólo los datos de dicho turno. El nombre de cada hoja creada coincide con cada uno de los valores que toma TURNO. Por ejemplo, en la siguiente figura se muestra el correspondiente al turno D:

Y el informe correspondiente a la hoja V sería exactamente el mismo que el mostrado antes.

Así pues, el uso de campos de página es muy útil cuando se desea disgregar un informe complejo en informes más simples según determinados campos. Estos informes disgregados se pueden presentar o imprimir por separado.

3. Más elementos y aspectos de los informes TD

3.1. Campos calculados

Supongamos que queremos incluir en el informe TD las ratios promedio que tienen los grupos de un centro en cada etapa. Ya vimos en el Tema 5.1 cómo incluir una columna más en los datos de origen, titulada RATIO, en cuyas celdas se introducía una simple fórmula de división: nº de alumnos/nº de grupos. De la misma forma, para que las ratios figuren en nuestro informe TD podríamos ahora añadir una columna como la antedicha en la hoja de cálculo donde están los datos, después ampliar el rango de datos de origen que maneja la TD para que incluya esta nueva columna y, finalmente, añadir el campo RATIO a la zona de campos de datos.

Sin embargo, existe una solución mucho más sencilla que no implica introducir cálculos en la hoja de los datos de origen ni ampliar el rango que maneja la TD. Se trata de usar los denominados campos calculados. Estos son campos de una TD que aplican una fórmula creada a conveniencia por el usuario para realizar cálculos utilizando el contenido de otros campos de la TD.

Para crear un campo calculado hay que utilizar, en la barra de herramientas TD, la opción de menú Tabla dinámica > Fórmulas > Campo calculado:

que da paso a la ventana Insertar campo calculado:

en cuyo cuadro de texto Fórmula se puede escribir una fórmula que haga uso de los valores de otros Campos de la TD; estos se pueden ver y seleccionar en la lista

correspondiente situada en la parte inferior. En el cuadro de texto Nombre se puede dar al campo calculado el nombre que convenga que, en el momento en que se pulse Aceptar, pasa a ser un campo más de la TD.

Siguiendo con nuestro ejemplo definamos un nuevo campo denominado RATIO cuya fórmula sea = ALUMNOS / GRUPOS (podemos escribir el texto de la fórmula directamente o bien seleccionando el nombre de cada campo utilizado en la fórmula de la lista Campos y pulsando Insertar campo):

Finalizada la escritura de la fórmula debemos hacer clic en el botón Sumar y el nuevo campo calculado, RATIO, queda añadido a la TD:

Nota: el nombre del botón Sumar no implica que se realice una operación de adición; su significado es más bien "añadir" (añadir el campo calculado a la TD).

Al hacer clic en Aceptar el campo RATIO se incluye automáticamente en la zona de campos calculados (junto con Suma de GRUPOS y Suma de ALUMNOS):

y el campo calculado aparece además en la lista de campos de la TD:

Al ser RATIO un campo calculado, sólo puede ser situado en la zona de campos de datos, no en las de fila, columna o página. Por lo demás, puede ser manejado como cualquier otro campo.

Posiblemente una forma más conveniente de reorganizar la TD una vez introducido este campo calculado, es arrastrar el campo de columna ETAPA antes de Datos, de modo que, para cada etapa, quedan adyacentes el nº total de grupos, el nº de alumnos y la

correspondiente ratio, haciéndose más evidente la relación entre ellos:

Obsérvese que en el anterior informe TD aparecen valores como #¡DIV/0!. ¿Qué significado tienen? Este tipo de valores se muestran en una hoja de cálculo cuando se produce un error en el cálculo de una fórmula. En particular, este error significa que ha tenido lugar una división por cero. En la imagen anterior resulta bastante claro el origen de este error. En la etapa BAC del centro situado en la fila 12 no hay grupos ni alumnos, de modo que al calcular la TD el campo RATIO se ha producido una división por cero.

Para saber más ...

Si deseas conocer los tipos de errores que se pueden presentar en una hoja de cálculo, acude a la página Buscar y corregir errores en fórmulas del sitio Microsoft Office

Online y fíjate en el apartado Corregir un valor de error, donde se explica el significado y posible origen de los distintos valores posibles de error.

3.2. Opciones de configuración de la TD

La aparición del error en el campo calculado del apartado anterior nos dará pie a la personalización de las opciones de configuración de la TD.

Al manifestarse tal error podemos formularnos razonablemente la pregunta: ¿es posible ocultar los errores que muestra la TD? Si deseamos presentar electrónicamente este informe, o imprimirlo, puede resultar un poco embarazoso el explicar que contiene

errores, aunque se trate de valores totalmente naturales y no necesariamente originados por un uso equivocado de los datos. Es más, a veces podría resultar conveniente que se muestren pues son índice de algún hecho particular que se deriva de los datos de origen y que, de otra forma, podría pasar desapercibido.

Pero retomemos nuestra intención primera de ocultar estos errores. Efectivamente podremos disimularlos acudiendo a las opciones de la TD. Éstas están accesibles en el menú de contexto de la tabla:

o también en la barra de herramientas:

Ambas proporcionan acceso a la ventana Opciones de tabla dinámica:

Una de las opciones es precisamente Para valores erróneos, mostrar:, que permite sustituir el valor del error por el texto que nos convenga, que debemos escribir en el cuadro de texto situado a su derecha:

Si el cuadro se deja en blanco el error desaparece del informe:

De manera análoga a cómo se substituyen los errores, se podrían sustituir las celdas vacías del informe con un valor de nuestra elección. Esto se puede hacer mediante la opción Para celdas vacías, mostrar:.

En la ventana de opciones aparece también el Nombre que Excel asigna automáticamente a la TD cuando esta es creada. Puede ser cambiado a voluntad, si bien no tiene mucha importancia salvo cuando se deba distinguir de otras TD y ser usado de alguna manera muy específica. No abundaremos en ello.

No entraremos a describir el resto de las opciones de configuración. Simplemente apuntaremos que la opción Actualizar al abrir no está marcada de forma predeterminada. En consecuencia el informe TD no se actualiza cada vez que se abre el libro .xls aunque hayan cambiado los datos de origen. En consecuencia, para actualizar el informe debemos proceder como fue explicado anteriormente. Si la opción se marca, cada vez que se abre el libro Excel "lee" de nuevo los datos de origen y recalcula el informe TD.

3.3. Ajuste del formato externo del informe

Cuando se crea el informe TD el formato en el que se presenta es un formato básico, bastante sencillo. Su aspecto puede variarse utilizando la opción Tablas dinámica > Dar formato al informe... de la barra de herramientas:

Ésta da acceso a la ventana Autoformato , donde se puede elegir entre un conjunto de formatos predefinidos, denominados Tabla 1, Tabla 2, etc:

Una vez seleccionado uno y pulsado Aceptar, el formato es aplicado al informe:

3.4. Formato de los campos del informe

A pesar de que existan formatos predefinidos pro Excel para el informe TD, en numerosas ocasiones conviene modificar a conveniencia el formato que presentan algunos datos del informe.

Por ejemplo, si deseamos resaltar la columna de ratios, podríamos seleccionar todas las columnas del campo de total Suma de RATIO y aplicarles color de relleno. Para seleccionar todas estas columnas no hay más que situar el puntero sobre el borde superior del campo, hasta que aparezca una flechita que apunta hacia la parte inferior, y hacer clic; quedan sombreadas las columnas para indicar que se hallan seleccionadas y en ese momento ya se puede aplicar el color:

con el resultado:

De la forma análoga se podrían modificar el tipo de fuente, la alineación del texto, etc.

Por otra parte, el formato numérico de un campo de datos se puede ajustar mediante la opción de menú de contexto Configuración de campo:

En la ventana Campo de la tabla dinámica se elige Número:

y en la ventana Formato de celdas (que es la misma con la que se aplica formato a cualquier celda de una hoja de cálculo) puede especificarse el tipo de categoría de nº que debe mostrase, por ejemplo un número con 1 posición decimal:

---

>

Así, los valores de ratio que antes se mostraban en el informe con un nº variable de decimales quedan ahora ajustados a un sólo decimal.

Tablas Dinámicas y Campos calculados

Descargar el fichero: TDcampocalculado.xlsx

Las Tablas Dinámicas han supuesto la revolución en Hojas de Cálculo de los

últimos años. Permiten generar informes rápidos y flexibles. Si usted llega a conocer

bien su funcionamiento puede cambiar radicalmente la gestión de su departamento

o unidad de negocio.

En este artículo vamos a crear una Tabla Dinámica partiendo de una base de datos.

En la tabla dispondremos de los costes de diferentes departamentos de la empresa

para el año 2010 y la previsión para 2011. Crearemos un campo calculado que nos

permita observar el incremento de cada departamento en estos años.

La base de datos de partida es sencilla.

Coste por Proyecto y Departamento

En Excel 2007 vamos al menú Insertar y luego Tabla Dinámica. Siguiendo unos

sencillos pasos llegamos a crear una tabla dinámica como la que se muestra en la

siguiente imagen:

Disponemos de los costes del año 2010 y la previsión para 2011 por cada uno de los

departamentos. Los cuatro proyectos se han establecido como filtro de página en la

parte superior de la tabla dinámica.

Ahora deseamos disponer de una columna más que nos indique la variación

porcentual experimentada por los costes entre los años 2010 y 2011. Este objetivo se

podría lograr por varios métodos:

1. Escribiendo en la celda D5 la fórmula: =C5/B5-1. Esta fórmula nos da el

incremento en tanto por uno. Para verlo en porcentaje basta pulsar sobre el

icono de porcentaje (%).

2. Establecer la fórmula anterior pero vinculando sobre las celdas C5 y B5. En

este caso veremos que la fórmula utiliza la función

IMPORTARDATOSDINAMICOS. Esta forma de trabajar tiene la ventaja de que

ésta función apunta a la tabla dinámica y por tanto no perdemos el vínculo

dinámico con la base de datos.

3. Crear un campo calculado. Este es el método que utilizaremos en este

artículo.

Creación del campo calculado

En Excel 2007 con el cursor sobre la tabla dinámica veremos arriba una nueva opción

denominada:

Herramientas de tabla dinámica

Al pulsar sobre ella se abren un nuevo menú sobre el que pulsaremos sobre

Formulas.

La imagen anterior puede diferir de la que usted pueda ver en pantalla, ya que en

Excel 2007 la cinta de opciones muestra diferentes iconos, o los muestra más o

menos resumidos en función de la resolución de su pantalla y del tamaño de ventana

que utilice.

Al pulsar sobre Fórmulas elegimos Campo calculado.

Aparece una ventana denominada Insertar campo calculado en el que crearemos la

fórmula:

=’2011′/’2010′-1

La fórmula se crea introduciendo los campos (columnas) de la tabla dinámica. En

este caso calculamos el porcentaje de variación por la clásica fórmula:

Valor Final / Valor Inicial -1

Expresión que es igual a la siguiente:

(Valor Final – Valor Inicial) / Valor Inicial

En nuestro caso los costes del año 2010 son los valores iniciales y las previsiones para

2011 son los valores finales.

Esto genera una nueva columna que denominamos Var.% que recoge la variación

porcentual de los costes entre los años 2010 y 2011. Inicialmente los valores que nos

dan están en tanto por uno y hemos de ser nosotros los que debemos dar formato a

esos valores como Porcentaje de dos decimales.

Los campos calculados son muy útiles al trabajar con tablas dinámicas y tienen la

ventaja de que no perdemos el vínculo dinámico con la base de datos.

Gráfico Dinámico

Sitúa el cursor sobre la tabla dinámica y pulsa sobre la opción que verás arriba

denominada Herramientas de tabla dinámica. Luego pulsa sobre el icono que te

permite crear un gráfico dinámico tal y como se muestra en la siguiente imagen.

Elegimos el tipo de gráfico y de forma instantánea dispondremos de un gráfico muy

flexible con muchas opciones que podemos modificar.

Ejercicio propuesto

En la Hoja3 disponemos de una base de datos con 200 registros con los siguientes

campos: Fecha, Artículo, Facturación y Unidades. Nuestro objetivo es crear una

tabla dinámica agrupada por meses y trimestres en la que introducimos un campo

calculado que nos proporcione el precio medio de venta en cada mes.

Todos los datos de la base de datos son aleatorios. Así la fecha es un valor aleatorio

del primer semestre del año 2011, y se genera con la fórmula:

=ALEATORIO.ENTRE(FECHA(2011;1;1);FECHA(2011;6;30))

Los posibles artículos son cinco y se generan aleatoriamente con la fórmula:

=ELEGIR(ALEATORIO.ENTRE(1;5);”Art1″;”Art2″;”Art3″;”Art4″;”Art5″)

En Excel 2003 y anteriores para que no de error la fórmula ALEATORIO.ENTRE

debemos haber activado el complemento de Herramientas para análisis. Esto se

puede activar en el menú Herramientas, Complementos.

Agrupando las fechas simultáneamente por meses y por trimestres obtenemos la

tabla dinámica que se muestra en la imagen.

Ahora hemos de crear el campo calculado que insertará una nueva columna en la

tabla dinámica. Pretendemos calcular el precio medio, por tanto hemos de dividir la

facturación entre el número de unidades.

La tabla dinámica que obtenemos ya incorpora el campo calculado Precio medio.

Los resultados numéricos que usted obtenga serán diferentes de los que se muestran

en la anterior imagen, esto es debido a que la base de datos trabaja con valores

aleatorios.

Podemos ver cómo cambian los valores de la tabla dinámica al actualizarla. Para ello

pulse con el botón derecho del ratón sobre la tabla dinámica y elija Actualizar.

Excel 2010

Para crear un campo calculado en Excel 2010 sigue estos pasos:

1. Partimos de los datos originales

2. Creamos la tabla dinámica pulsando sobre: Insertar, Tabla dinámica, y

diseñamos la tabla según nuestras preferencias

3. La tabla dinámica ya esta creada. Ahora nos situamos con el cursor dentro de

cualquier celda de la tabla dinámica y veremos arriba una pestaña

denominada “Herramientas de tabla dinámica”. Esta pestaña tiene dos sub-

pestañas denominadas: Opciones y Diseño. Nos situamos en Opciones.

4. Pinchamos sobre Cálculos y luego sobre Campos, elementos y conjuntos, y

finalmente pinchamos sobre Campo calculado.

5. Luego se siguen los pasos vistos en Excel 2007 ya que el proceso de creación

del campo calculado es similar.