viernes, 5 de agosto de 2011

Tablas Dinámicas y Elementos Calculados

Descargar el fichero: TDelementocalculado.xlsx

En un post anterior habíamos hablado de Campos Calculados de una Tabla Dinámica de #Excel. Ahora vamos a ver un caso un tanto especial en el que necesitamos calcular la variación porcentual de cierta magnitud económica entre los años 2010 y 2011. El problema es que la información de partida no distingue entre ambos años, sino que simplemente existe un campo (una columna) con la fecha. Lo vamos a resolver por dos métodos. Primero emplearemos fórmulas matriciales y en segundo lugar lo haremos creando un Elemento calculado en una Tabla dinámica.

Puede consultar el post donde hablábamos de:

Tablas dinámicas y Campos Calculados

Datos de origen (Hoja1)

Partimos de una pequeña base de datos, de únicamente dos columnas:

  1. Fecha. El primer día de cada mes de los años 2010 y 2011. En total 24 registros (filas)
  2. Importe. Es el valor de cierta magnitud económica. Podría se por ejemplo la facturación de una empresa


Resolución con fórmulas matriciales

En la propia Hoja1 vamos a resolver nuestro caso aplicando una función matricial. Para saber cómo se ha de trabajar con este tipo de funciones puede consultar el post siguiente:



La celda F5 es:

{=SUMA(Importe*(AÑO(Fecha)=F$4)*(MES(Fecha)=$E5))}

La fórmula está entre llaves lo que indica que es una fórmula matricial. Cuando nosotros escribimos la fórmula no ponemos las llaves, esto lo hace Excel, al validar la fórmula. Las fórmulas matriciales no se validan pulsando ENTER, se han de pulsar simultáneamente las tres teclas siguientes:

CONTROL + MAYÚSCULAS + ENTER

La tecla de MAYÚSCULAS es la tecla SHIFT.

Los rangos empleados son:
  • Fecha =Hoja1!$B$5:$B$28
  • Importe =Hoja1!$C$5:$C$28
La fórmula se ha de copiar al rango F5:G16.

Para crear la columna H, simplemente aplicamos la fórmula que nos da la variación porcentual entre los valores del año 2010 y los valores del año 2011, que es lo que estamos buscando. La celda H5 es:

=+G5/F5-1

Luego se la dota con formato de porcentaje, y obtenemos un 20% que es el incremento experimentado entre el importe del año 2010 (1.000) y el importe del año 2011 (1.200), para el mes de enero. Se copia hacia abajo y se obtienen los porcentajes de variación por cada mes.

Añadimos columnas para el año y el mes (Hoja2)

No es muy ortodoxo añadir a una base de datos nuevos campos (columnas) que se calculan con información ya contenida en la propia base de datos. Pese a ésta recomendación general, en este caso, añadiremos la columna Mes y la columna Año, que se calculan utilizando las fórmulas de Excel MES y AÑO. Estas fórmulas al aplicarse a una fecha válida nos proporcionan precisamente el mes y el año de esa fecha.

Es imprescindible que estas fórmulas se apliquen a una fecha válida en Excel. Por este motivo hemos necesitado que la columna de Fecha sea una fecha válida, y hemos tenido que tomar el primer día de cada uno de los meses considerados. Así la primera fecha es el 1-enero-2010, y no hubiera valido haber puesto simplemente enero-2010 sin indicar el día. Una fecha válida debe indicar el día, el mes y el año aunque luego en el formato que demos únicamente pidamos que se muestre el mes y el año.


En la columna B aparece la fecha en formato mmm/aaaa. Son fechas válidas ya que aunque no veamos el día,  esto es por el formato, pero la fecha está introducida como dia-mes-año.

La celda D5 es:

=MES(B5)

La celda E5 es:

=AÑO(B5)

Resolución con Tabla Dinámica

Primero creamos una Tabla dinámica como la siguiente.


Hemos puesto el Mes en rótulos de fila, el Año en rótulos de columna y el Importe como datos en Valores.

En Excel 2007, con el cursor situado en la tabla dinámica pinchamos arriba sobre la pestaña "Herramientas de tabla dinámica". Luego sobre Fórmulas.


Deseamos elegir Elemento calculado pero vemos que aparece deshabilitado, en color gris. Para que podamos tener disponible esta opción debemos situar el cursor del ratón exactamente sobre la celda B4 o C4 de la tabla dinámica que es donde se encuentran los indicadores de los años 2010 y 2011.

Ahora si podemos insertar un Elemento calculado.

Aparecerá la ventana donde podemos construir nuestro elemento calculado.



Al elemento calculado le denominaremos Var.% ya que representará la variación porcentual. La fórmula que pondremos es:

='2011'/ '2010'-1

Pero no debemos escribir la fórmula tecleando los años sino eligiendo los Elementos con el ratón. Elegimos el elemento del año 2011, pulsamos luego sobre el símbolo de dividir (/), pulsamos sobre el elemento del año 2010, y finalmente restamos uno.

Con ello se genera la columna correspondiente al elemento calculado Var.%. La nueva columna se formatea en formato de porcentaje y tendremos ya creada nuestra Tabla dinámica con elemento calculado.


Crear lista de fórmulas

Podemos crear una lista con todas las fórmulas creadas en la tabla dinámica tanto de elementos calculados como de campos calculados. Para ello, sitúe el cursor en la tabla dinámica y elija 'Herramientas de tabla dinámica', Fórmulas, y 'Crear lista de fórmulas'.

Esto genera una nueva hoja en la que obtendremos el listado solicitado.


Bajo este listado aparece el siguiente comentario.

Notas:
  • Cuando una celda se actualiza con más de una fórmula, el valor lo establece la fórmula con la última orden de resolución.
  • Para cambiar el orden de resolución de varios elementos o campos calculados, en la ficha Opciones, en el grupo Herramientas, haga clic en Fórmulas y, a continuación, seleccione Orden de resolución.

9 comentarios:

Martin UY dijo...

Hola Adolfo,
Buenos días...
Buenísimo las explicaciones!!!
Un abrazo...


Martin

Marco dijo...

Estimado Adolfo
si quisiera que la suma de los totales generales por columna también muestren la variación porcentual, que debo hacer?, lo que sucede es que si muestro estos totales generales aparecerá una sumatoria de porcentajes cuando lo que necesito es la variación porcentual.
estaré muy agradecido por la respuesta

Adolfo Aparicio dijo...

Hola Marco.

Se a que te refieres. El motivo que me impulsó a quitar los totales generales por columna es que en la columna de porcentaje se dedica a totaliza (sumar) todos los porcentajes, en lugar de calcular el porcentaje de variación del año 2010 al 2011.

Una posible solución, pero que no es totalmente satisfactoria consiste en escribir en la celda D17 de la Hoja3 la siguiente fórmula:

=SUMAPRODUCTO(D5:D16;B5:B16)/SUMA(B5:B16)

El inconveniente de esta expresión es que no forma parte de la tabla dinámica y que se apoya en ella. Si la tabla dinámica cambia su estructura (que no sus datos) la fórmula no sirve. Si la estructura de la tabla dinámica se mantiene aunque cambien sus datos la fórmula propuesta si es válida.

Otra posibilidad es sumar en las celdas B17 y C17 los valores de los años 2010 y 2011, y luego en la celda D17 poner la siguiente expresión en formato porcentaje:

=+C17/B17-1

Aunque este método tiene el mismo inconveniente que el comentado anteriormente y además usa tres celdas ajenas a la tabla dinámica, que son las de la fila 17.

A ver, si dando vueltas al asunto somos capaces de encontrar una solución que sea interna a la propia tabla dinámica.

Un cordial saludo.

Marco dijo...

Hola Adolfo, efectivamente con esas fórmulas tengo la variación porcentual acumulada, pero la idea era tenerla dentro de la propia tabla dinámica para no estar condicionados a las celdas fuera de esta tabla.
Ojalá encuentres la solución- si existiera- yo la verdad aún no la he encontrado. Cualquier aporte en la solución te seré muy reconocido

gracias de todas formas
Saludos

Pierock dijo...

En caso de Contar con mas Opciones en Año? Por ejemplo 2009,2010,2011,2012,... como podria hacer eso ? para que se actualice correctamente la variacion entre dos años consecutivos.

Gracias.

Ramón Calvo Sánchez dijo...

Hay alguna forma de cambiar de manera simultánea el formato de varios filtros de una tabla dinámica?Tengo que cambiar muchos filtros del formato Cuenta al formato Suma y no quisiera tener que ir uno a uno.
Si me hicierais conocedor del método a seguir les estaría muy agradecidos.
Saludos

Perdicas Perdicas dijo...

Hola, Adolfo. Ante todo gracias por la información y ayuda que brindas desde tu blog. Mi pregunta era si existía algún límite en cuanto a la cantidad de registros de la tabla de partida de la tabla dinámica para la creación de elementos calculados. En mi caso he partido de una tabla de unos 3000 o más registros (filas) y no me ha dejado crearlos, o mejor dicho los crea pero no hace nada realmente con los datos, dice que hay demasiados registros. He partido de una tabla con menos registros para crear la tabla dinámica y si me deja crear elementos calculados. Me podrías informar al respecto.

Un saludo y gracias por la información

Adolfo Aparicio dijo...

Hola Perdicas.

Consultando la página de Microsoft dice:

Fórmulas de elementos calculados en un informe de tabla dinámica: El límite está en función de la memoria disponible.

Prueba con un ordenador que disponga de mayor memoria RAM. Mejor 8_Mb que 4 Mb.

Un saludo.

Gabriel Gonzalez dijo...

Buenas tardes y muchas gracias por compartir tus conocimientos, son de gran ayuda.
Una pregunta tengo las ventas de varios productos mes a mes y hago una tabla dinamica. en los totales le digo que me lo muestre como el promedio. lo que quiero realizar es coger ese promedio y multiplicarlo por dos. hago un campo calculado y en la formula coloco =PROMEDIO(VENTAS)*2, el resultado es que no toma el promedio sino la suma de las ventas. Gracias por la ayuda que me puedan prestar