• Tweet
• Email

• Tweet
• Email

Hola ¿Cómo están?

Descargue el archivo: 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 esta 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 éstos 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.

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 2010, y se genera con la fórmula:

=ALEATORIO.ENTRE(FECHA(2010;1;1);FECHA(2010;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.

Al cambiar los datos en la base de datos nuestra tabla dinámica se actualiza al pulsar sobre la opción Actualizar. En la gestión empresarial no dispondremos de datos aleatorios en la base de datos pero éstos si serán cambiantes y podremos actualizar fácilmente nuestros informes ya que la tabla dinámica no pierde el vínculo dinámico con la base de datos.

Espero este aporte les sea de mucha utilidad.

¡Saludos y Éxito!

El Equipo de EXCELLENTIAS.COM