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:
Datos de origen (Hoja1)
Partimos de una pequeña base de datos, de únicamente dos columnas:
- Fecha. El primer día de cada mes de los años 2010 y 2011. En total 24 registros (filas)
- 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:
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:
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'.
Bajo este listado aparece el siguiente comentario.
- 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.
Caso práctico propuesto
Descargue el archivo siguiente.
Disponemos de una base de datos con dos campos: Fecha e Importe de ventas.
Otro archivo para practicar.
¿Aceptas el reto?
Ánimo, a ver si lo consigues.
Hola Adolfo,
ResponderEliminarBuenos días...
Buenísimo las explicaciones!!!
Un abrazo...
Martin
Estimado Adolfo
ResponderEliminarsi 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
Hola Marco.
ResponderEliminarSe 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.
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.
ResponderEliminarOjalá 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
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.
ResponderEliminarGracias.
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.
ResponderEliminarSi me hicierais conocedor del método a seguir les estaría muy agradecidos.
Saludos
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.
ResponderEliminarUn saludo y gracias por la información
Hola Perdicas.
ResponderEliminarConsultando 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.
Buenas tardes y muchas gracias por compartir tus conocimientos, son de gran ayuda.
ResponderEliminarUna 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
Yo tengo un inconveniente similar, necesito calcular el mínimo pero hace los cálculos sumando.
EliminarOtra opción pensé que era mandar el campo a la sección de valores y calcular el mínimo, para luego usar ese valor en el campo calculado pero solo aparecen los valores iniciales de la tabla origen de datos
Estimado al intentar hacer el ejercicio me genera un error al poner la formula {=SUMA(Importe*(AÑO(Fecha)=F$4)*(MES(Fecha)=$E5))} meda el valor del primer mes en toda la selecion, sera la formula que esta con algun error o soy yo. que estoy haciendo algo mal.
ResponderEliminarpor favor brindame su ayuda.
Hola Josue.
EliminarSeguramente el error se ha producido al introducir la fórmula. Se trata de una fórmula matricial, no debes poner las llaves de inicio y final { }, aparecerán ellas automáticamente cuando la fórmula esté introducida correctamente. Para validar una fórmula matricial no se presiona ENTER, se han de presionar tres teclas: CONTROL+SHIFT+ENTER. Presiona primero la tecla CONTROL, y sin soltarla presiona la tecla SHIFT (que es la flechita de mayúsculas), y finalmente sin soltar las anteriores, debes presionar ENTER. De esta forma ya verás como te funciona correctamente.
Un saludo.
Adolfo buenos dias (hora de Venezuela :P) ante todo felicitaciones por su blog, tengo una consulta a ver si me puedes ayudar puesto que soy un novato, tengo una tabla donde tengo con datos como efectivo, debito, gastos, y depositos bancarios, todos estan organizados cronologicamente la primera columna $A son las fechas, y las siguientes tienen los criterios antes mencionados, deseo hacer en otra hoja los totales pero con un alista desplegable que por ejemplo, seleccione enero 2015 y me de los totales, o marzo 2015 y me de los totales, como se podría abordar ese requerimiento, saludos y gracias de antemano.
ResponderEliminarBuenas tardes Adolfo, tengo una tabla dinámica en la que tengo en las filas los distintos clientes (y algun campo mas) y en las columnas el año y el mes en el que se han producido las ventas de dichos clientes. El problema es que excel por defecto subtotaliza por años cerrando el año a 31 de diciembre. Estoy intentando definir 2 periodos de subtotalizacion (a modo de comparativo), uno que vaya por ejemplo desde el 1 de abril de 2014 hasta el 31 de marzo del 2015 y otro segundo periodo que vaya desde el 1 de abril del 2015 hasta el 31 de marzo del 2016 pero no hay manera.
ResponderEliminarComo los subtotales que me coloca en las columnas no me sirven de nada, lo que hago es ocultarlos y fuera de la tabla dinámica crearme una columna para el periodo 1 y otra para el periodo 2 para posteriormente aplicarle un sumatorio, lo que ocurre es que como no va ligado a la tabla dinámica el comportamiento de las sumas no es el correcto.
También he intentado crear un campo calculado para que realice el calculo de la suma de los meses que yo elija, pero como sabes, en los campos calculados de las tablas dinámicas solo puedo indicar meses o años, pero no la combinación de los dos, y es lo que necesito para hacer abril2014+mayo2014+etc. Se te ocurre alguna forma de resolverlo?
Gracias.
Me respondo yo mismo.
EliminarHe encontrado una solución al problema
Lo que hago es convertir todas las fechas del tipo dd/mm/aaaa en un formato de mes-año (por ejemplo diciembre-2014), pero esa fecha el sistema no la tiene que interpretar como fecha sino como texto. A continuación creo la tabla dinamica y pongo las fechas en las columnas (al no reconocer las fechas como tipo fecha ya no me separa el año y el mes) y el orden de las columnas no es el correcto, ya que por ejemplo primero me coloca abril-2015 que diciembre-2014, entonces lo que hago es situarme sobres las columnas y utilizar la opción "mover" del menú contextual para colocar las columnas en el orden que quiero. Una vez todas las columnas están en el orden deseado, lo que hago es crear un campo calculado de la columna mes-año llamado "PERIODO 1" donde en la formula le indico los meses-año que quiero que sume y a continuación creo otro campo calculado para el "PERIODO 2" donde elijo los otros meses-año. De esta forma tengo los subtotales de los 2 periodos (de año no natural) y el total que me genera automáticamente la tabla combinada. El único pequeño problema es que cuando despliego las filas (contienen los clientes y otro valor en el detalle) existe un poco de ralentización, quizás sea porque hay unas 7000 filas.
Nueva anotación sobre el problema de los periodos. No creo un campo calculado sino un elemento calculado, ya que el campo calculado no me permite especificar que rango de columnas sumar y en cambio el elemento calculado sí que me lo permite.
EliminarBuenas tardes, creo un campo calculado de la variación de ventas por mes, pero se desconfigura si agrego bajo los filtros a la tabla dinámica? mientras que sin filtros me funciona bien. Me podría ayudar.
ResponderEliminarSaludos.
Buen día,
ResponderEliminarAdolfo excelente tutorial de variación, pero tengo una duda, en los campos calculados se encuentra alguna formula que me la haga mes a mes, es decir que tome el año 2010 y realice la variación mes "1/2-1";"2/3-1";"3/4-1"; y asi sucesivamente....
Quedo atento a sus comentarios.
Gracias
Hola buen día, tengo una pregunta, lo que pasa es que agrego mi variación e porcentaje y me lo pone en pesos, cambio el formato y me cambia también lo de pesos a porcentaje, como se puede modificar el formato del elemento calculado?
ResponderEliminarAdolfo, hay posibilidad de insertar una fórmula de contar.si en la fórmula del campo calculado? Lo que quiero es que lo cuente en otra tabla
ResponderEliminar