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.