domingo, 7 de febrero de 2010

Elementos calculados en Tablas dinámicas.

Hace ya algún tiempo expliqué algunas de las herramientas de las que disponíamos al trabajar con Tablas dinámicas; en concreto, mencioné los campos calculados.
En este post usaremos otra de las posibilidades de las tablas dinámicas, los elementos calculados.
Supongamos que tenemos una base de datos con los registros de dos vendedores A y B en dos meses, 'ene-09' y 'ene-10' e importes de ventas; lo que necesitamos es obtener por cada vendedor su total por mes, así como la variación en euros y la variación porcentual entre ambos meses.
Nuestros datos son:


Crearemos nuestra Tabla dinámica tomando el rango A1:C19, y como estructura de datos mandaremos el campo 'Vendedor' al área de filas, el campo 'Importe' al área de datos y el campo 'Meses' al área de columnas:


Una vez creada nuestra estructura, de acuerdo a nuestro gusto o necesidades, terminaremos de incluir toda la información solicitada, es decir, Diferencia entre 'ene-10' y 'ene-09' en terminos globales y porcentuales.
Para ello nos iremos, con el campo 'Meses' seleccionado, en la versión Excel 2007, a Herramientas de Tabla dinámica > Grupo de Herramientas > Fórmulas > Elemento calculado


Definimos cómo queremos que sea este nuevo elemento cálculado, que deberá ser la diferencia entre el elemento 'ene-10' y el elemento 'ene-09' del campo 'Meses':


De igual forma, aprovecharemos el nuevo elemento creado 'Dif' para obtener nuestro último Elemento calculado como variación porcentual entre ambos elementos:


Hemos terminado, tenemos en nuestra Tabla dinámica la estructura de campos deseada, con los diferentes datos requeridos:


Los datos a interpretar serían, para el vendedor A,que ha obtenido unas ventas en enero del 2009 de 20.916,93 eur, y de 24.149,39 eur, que la diferencia entre ambos es de 3.232,46 eur que supone una variación positiva del 15,45%.

61 comentarios:

  1. hay forma de calcular el 25% de un valor acumulado en una columna ? osea la tabla dinamica posee descripcion del articulo y total.Al total calculado automaticamente calcularle el 25%

    ResponderEliminar
  2. Buenos días,
    si es posible, insertando un campo calculado ,ver
    http://excelforo.blogspot.com/2009/08/tabla-dinamica-campos-calculados.html
    Suponiendo que tu base de datos tiene dos campos (Descripción e Importe),la idea sería crear un campo calculado en la TD con la fórmula '=Importe*25%'.
    Slds

    ResponderEliminar
  3. muchas gracias por tu respuesta y tu tiempo...
    deduzco que si mi tabla dinamica clacula el total como una suma de grupos no puedo calcular un porcentaje en un campo calculado ya que el mismo no me figura. te agradeceria que me confirmes esto

    ResponderEliminar
  4. Hola,
    cuando creas un Campo calculado en una Tabla dinámica, éste te aparecerá en tu Lista de campos, como uno más; por tanto si podrás calcular porcentajes sobre él...
    si no me explico con clarida, mándame un correo a
    excelforo@gmail.com
    En esta entrada en la que escribes, aunque habla de elementos y no cmapos calculados, puedes ver como se calculan porcentajes...
    Slds

    ResponderEliminar
  5. Hola,
    muchas gracias por el foro, me está ayudando mucho. En mi caso aparte de querer calcular la variación mensual también tengo una columna que me calcula el total por filas, es decir, el total acumulado en lo que va de año de los vendedores. Lo que querría saber yo es la aportación al total anual de ventas por cada vendedor, es decir dividir cada fila de mi columna total acumulado por vendedor entre el total de ventas .

    Muchas gracias

    ResponderEliminar
  6. Hola,
    gracias por tus comentarios.
    Respecto a tu duda, aunque en breve subiré un post dando una solución al problema, puedes analizar la siguiente entrada
    http://excelforo.blogspot.com/2009/07/opciones-de-un-campo-de-tabla-dinamica_28.html
    donde se analiza cómo obtener el resultado que quieres.
    Slds

    ResponderEliminar
  7. Hey, antes que nada se agradece el tiempo que dedicas a compartir tu conocimiento, mi duda...

    Tengo una tabla dinamica un tantito mas compleja que la que ejemplificas la cosa va asi:
    En columnas: Meses

    En filas: Empresas (A,B,C,D), Año (2010,2009,2008,2007), Tipo de dato (Real,Presupuesto) y Concepto (Concepto1,Concepto2,Concepto3)

    En valores: numeros, cualesquiera

    He creado un elemento calculado en el area de Tipo de dato al que llamo $ Diferencia (Real-Presupuesto), ahora, quisiera representar proporcionalmente el dato algo asi $ Diferencia/Presupuesto, cuando observo el detalle del nuevo elemento veo que se calcula correctamente, pero el total del mismo aparece como una suma y mas bien deberia ser un promedio, como corrijo esto?

    ResponderEliminar
  8. Hola Raziel,
    pues que yo sepa tiene difícil solución, ya que un pequeño inconveniente de los 'elementos calculados' es que no permite incluir campos configurados como promedios; además que sería un poco complicado para Excel entender que lo que queremos es que un elemento de un campo promedie y los demás elementos del mismo campo sumen...
    La solución que te propongo, no se cómo de complicada es tu base de datos, construyas una réplica de la Tabla dinámica con funciones, donde sí podrás configurar los elementos que necesites, así como sus subtotales.
    Más adelante subiré un post al blog con un ejemplo desarrollado... pero te adelanto que emplearé entre otras la función SUMAR.SI.CONJUNTO
    Espero que al menos te haya dado una pista de por donde ir.
    Slds

    ResponderEliminar
  9. Mmmm, o sea, me voy olvidando de la tabla dinamica y mejor voy pensando en una tabla dinamica "simulada" por formula??? No me digas esooo :( ni modos, crei que se podia lograr. Muchas gracias por tu comentario, si no me dices, seguro que seguiria en el hoyo.

    ResponderEliminar
  10. Holaaa!!!!, pues nada, que se me acaba de ocurrir que en la base de datos podria crear una columna auxiliar en el que calcule su valor proporcional y lo divida entre el numero de elementos correspondientes, entonces a la hora de que lo anexe a la tabla sumara el total como promedio, no estoy seguro de que funcione, para ser honesto no lo creo pero llegando a casa hago el test y comento.
    Saludos.

    ResponderEliminar
  11. Buenas tardes,

    Mi duda es la siguiente: En una tabla donde tengo Fila "Nombre" Columna "Edades de cartera, 5 rangos", Valores "Suma de cartera en su respectivo rango de edad", si filto 2 de los 5 rangos de edades muchos de los nombres me quedan con saldos en cero, quiero que no me muestre esos campos. ¿Que debo hacer? Nancy

    ResponderEliminar
  12. Hola Nancy,
    ya te contesté por mail...
    echa un vistazo a la solución que he subido

    http://excelforo.blogspot.com/2010/08/tablas-dinamicas-ocultar-registros-sin.html

    Saludos

    ResponderEliminar
  13. cules son las bases de la tabla dinamica

    ResponderEliminar
  14. Las bases de las tablas dinámicas son estructuras de cubos, esto es, componer tablas agrupando en función a campos definidos por el usuario para lograr resumir de acuerdo a un campo de datos numéricos.

    ResponderEliminar
  15. los elementos calculados se calculan tambien para Elementos sin datos que puedo hacer para que esto no suceda?

    ResponderEliminar
  16. Hola Jose Ignacio,
    los elementos calculados en Campos de Tablas dinámicas son creados por ti, así que si no quieres que aparezcan, simplemente deja de incluirlos en el nuevo elemento.
    En cualquier caso, no veo claro dónde quieres llegar, ya que si has creado un Elemento calculado sobre otros elementos de un mismo campo, es por que quieres realizar algún cálculo sobre aquellos, agrupando por algún criterio personalizado los elementos originales...
    Por favor, aclárame este punto (vía mail con un ejemplo) y trataré de darte una respuesta.
    Un saludo

    ResponderEliminar
  17. Escel pro , Me sirvio de mucho este post.

    Saludos.

    ResponderEliminar
  18. Hola, una consulta, imagínate por ejemplo que usando esos datos, quiero una tabla dinámica que tenga en las filas los vendedores y en columnas, el promedio de venta, y otra columna que sea el promedio + el max, y otra que sea el promedio menos el mínimo. Se puede hacer? como se hace?? gracias!

    ResponderEliminar
  19. Hola...
    en principio yo diría que no esposible trabajar de esa manera con tablas dinámicas, si bien es cieto que si añadimos dos campos a la tabla origen, si conseguirías lo que quieres.
    Los dos nuevos campos a anexar al origen de datos los llamaremos 'promedio+max' y 'promedio-min', con las siguientes fórmulas matriciales:
    'promedio+max': =PROMEDIO.SI([Vendedor];[@Vendedor];[Importe])+MAX(SI([Vendedor]=B2;[Importe]))
    y
    'promedio-min': =PROMEDIO.SI([Vendedor];[@Vendedor];[Importe])-MIN(SI([Vendedor]=B2;[Importe]))
    con estas fórmulas hemos conseguido que para cada Vendedor tengamos, repetidos, el valor promedio de ese vendedor más su máximo, o bien el promedio de ese vendedor menos su mínimo (lo que querías ocnseguir), luego sólo nos queda construir nuestra TD tal cual comentabas, añadiendo estos dos campos, resumido por promedio.
    La verdad no he encontrado otra forma, y no digo que no exista... ;-)
    Slds

    ResponderEliminar
  20. No entiendo como usar las fórmulas que me pasas? en donde puedo leer algo de eso?, vos decís de hacerlo en la tabla de la que creo la tabla dinámica o hacerlo en la tabla dinámica creando un nuevo campo?

    Gracias!

    ResponderEliminar
  21. Hola,
    lo que te he comentado es crear esos campos en la tabla origen de la que luego crearás una tabla dinámica, incluyendo estos campos nuevos.
    Si quieres envíame un fichero a
    excelforo@gmail.com
    y te reenvío el ejercicio que he hecho.
    Slds

    ResponderEliminar
  22. Tengo un problema con los elementos calculados...me hace +, - y * pero no hay manera de que me haga una división!!! Utilizo el signo corriente / .

    ¿A alguien le ha pasado algo similar? Que bajon...

    ResponderEliminar
    Respuestas
    1. Hola Gerard,
      revisa bien que estés trabajando sobre los 'elementos calculados', por que he probado con las versiones 2010 y 2007 y el operador funciona bien...
      Si sigues con problemas envíamelo a
      excelforo@gmail.com

      Slds

      Eliminar
  23. tengo problemas para realizar lo sguiente en una tabla dinamica como hago lo siguiente

    En el campo Venta Producto, si el valor es superior $1,500,000, este aparecera en color azul, si la venta de este producto es mayor a 2,000,000 aparecera en color verde

    ResponderEliminar
    Respuestas
    1. Hola,
      selecciona el campo Venta producto (todos los elementos mostrados) y desde Formato condicional configura las dos reglas que indicas.
      Realmente a la hora de dar formatos a una tabla dinámica no hay ninguna diferencia con una celda normal.
      Slds

      Eliminar
  24. Hola
    Buenos dias , quisiera consultar como hacer las siguiente formula o formato condicional en una celda tengo creada una lista de porcentajes del 0 al 100% , pero no se como hacer para que en otra celda automaticamente al elegig un porcentaje me salga ejemplo : ELIJO 100% EN OTRA CELDA AUTOMATICAMENTE APAREZCA PRODUCCION , ELIJO 0% Y ME APAREZACA EN LA OTRA NO INICIADO .

    ResponderEliminar
    Respuestas
    1. Hola Anyela,
      no sé cuantass opciones de porcentajes tienes en la lista entre 0% y 100%; si son pocos podrías aplicar un sencilla fórmula condicional.
      Por ejemplo, si la lista de % está en A1,y quieres que en B1 aparezcan los textos 'Producción' ó 'No iniciado', escribes en B1:
      =SI(A1=100%;"Producción";SI(A1=0%;"No iniciado";""))
      En general si tienes más opciones deberas añadirlas dentro de las condiciones.
      =SI(A1=100%;"Producción";SI(A1=0%;"No iniciado";SI(A1=5%;"Otros";...)))
      Slds

      Eliminar
  25. Tengo del 0% al 100% ... en una lista desplegable .. he probado y no me funciona y es muy trabajoso estar haciendo las condiciones de porcentaje en porcentaje .. No me resulto Y_Y

    ResponderEliminar
    Respuestas
    1. Hola Anyela,
      funcionar si que funciona, lo que puede ocurrir es que si tienes % desde 0% al 100%, no sea práctico.
      Lo que puedes hacer es contruir una tabla auxiliar donde a cada % le asignes un texto. Supongamos la tabla en las columnas D:E, donde en la columna D, en cada fila dispones los % y en la columna E los textos correspondientes a cada %.
      Así, si en A1 tienes la lista desplegable, en B1 escribes:
      =BUSCARV(A1;D:E;2;0)
      Slds

      Eliminar
  26. Es posible agregar en tablas dinamicas un resumen por campo (configuracion de campo) que sea diferente a los que aparecen?, necesito calcular el coeficiente de variacion, tengo las partes pero con el excel 2010 no tengo forma de armar la formula

    ResponderEliminar
    Respuestas
    1. Hola Martín,
      lamentablemente sólo es posible Resumir por las opciones desplegadas...
      de todas formas no parece tener mucho sentido aplicar a un campo tal forma de resumir, ya que ese coeficiente es un único valor para un rango, no es así???...
      para calcular el coeficiente de variación dentro de una tabla dínamica, quizá podrías contruir un campo calculado que haga la operación =Promedio(Campo1)/Desvest(Campo1)
      probablemente obtengas un dato analizable... aunque creo que tardaría menos haciéndolo únicamente con las funciones estandar (sin usar Tabla dinámica).
      Slds

      Eliminar
  27. Hola amigo quería hacerte una pregunta, te comento que yo no logro conseguir la forma de operar el valor total de una columna por ejemplo en tu ejemplo en la columna A de tu tabla dinámica tienes el valor total 41,817.93 y quisiera hacer un calculo donde el valor total únicamente sea divisible dentro de los datos de la columna ene 10 us sus valores 24,149.39, 24,333.86, yo ya intente colocando el número fijo al momento de operar el campo calculado y funciona pero yo lo que quiero es que si se filtran los datos pontele por semana este dato también sea variable y cambie según los filtros no se si me pueda ayudar de antemano agradezco tu atención

    ResponderEliminar
    Respuestas
    1. Hola Augusto,
      date cuenta que en este ejercicio se trabaja con 'Elementos calculados' y no con 'Campos calculados'; no se si será posible lo que creo que quieres hacer.
      En todo caso, el uso de Campos y Elementos calculados dentro de las tablas dinámicas sólo están recomendados en caso de única posibilidad, esto es, que no se puedan incorporar nuevas columnas con los cálculos al origen de datos.
      Slds

      Eliminar
  28. En el ejemplo que tu muestras te pasa lo mismo que a mi, y la fila total general está mal. El porcentaje de la ultima fila calcula la suma de los valores anteriores y no el porcentaje del total. ¿sabes como solucionarlo?

    ResponderEliminar
    Respuestas
    1. Hola Jesús Martín, que tal?
      El problema de elementos y campos calculados, y los Totales generales, en cuanto a sus incongruencias no es nuevo, pero de momento Microsoft no da una solución.
      Date cuenta que además al trabajar en este caso con elementos calculados no esposible aplicar Promedios, lo que quizá podría darnos el resultado correcto.
      Para obtener, por tanto, un resultado sobre la variación, deberías aplicar funciones... no creo sea posible con Tablas dinámicas.
      Un atento saludo

      Eliminar
  29. Hola,
    Estoy intentando usar la forma que propones para agregar elementos calculados en la tabla dinámica. Tu ejemplo es mi caso, por lo tanto lo use tal cual. El problema que encuentro es que los elementos calculados no respetan los filtros que agrego a la dinámica. Esto se puede resolver?.
    Muchas gracias por tu tiempo.
    Caro

    ResponderEliminar
    Respuestas
    1. Hola!!
      un elemento calculado, en principio, opera igual que cualquier otro elemento. Necesitaría ver tu tabla dinámica para ver a qué te refieres y cuál es tu problema.
      Si quiers puedes enviarme el fichero a
      excelforo@gmail.com

      Slds

      Eliminar
  30. Buenos dias, tengo una base de datos donde estan los clientes, los productos y las cantidades vendidas, sin embargo como logro calcular la cantidad de unidades totales y contar el numero diferentes de producto en en la fila donde me da los subtotales y me aparezca las dos operaciones a la vez

    ResponderEliminar
  31. Buenas tardes, mi consulta es por lo siguiente. Tengo la siguiente tabla dinamica...en las filas tengo como campo principal los meses, y como secundarios los dias. La columnas son los tecnicos responsables. Los datos son los minutos que estuvieron reparando maquinas. Yo necesitaría hacer un campo que me vaya acumulando los minutos dia a dia...se puede?

    ResponderEliminar
    Respuestas
    1. Hola diego,
      tendrías que añadir un nuevo campo 'Minutos' al área de valores, y luego 'Mostrar valores como' Total en..., o directamente hacer lo mismo sobre el campo que ya tienes de Minutos.

      Saludos

      Eliminar
  32. Buenas espero que me puedas ayudar con lo siguiente:
    Tengo que hacer la relación entre el dato X sobre el conteo (cuenta) del dato Y, es decir X/ conteo de Y.
    Pero por alguna razón que desconozco hace la relación X/suma(Y)
    Cómo puedo hacer con la data para obtener el dato que necesito.
    Gracias de antemano

    ResponderEliminar
    Respuestas
    1. Hola Mariangela,
      No sé si te refieres a elementos o campos calculados. En cualquier caso, debes saber que la forma de obtener resultados con estas figuras se comporta de diferente manera a las fórmulas en la hoja de cálculo... operando normalmente sobre los subtotales obtenidos... (y no existe forma de romper esa forma de trabajar interna de las TD).
      A veces lo más óptimo es añadir un campo auxiliar en el origen de datos...
      Saludos

      Eliminar
  33. Buenas, quisiera saber si se puede y como puedo calcular un % de todas las filas con referencia a una fila.
    Me explico, con tablas dinámicas hago una cuenta de Pérdidas y ganancias (contabilidad) y quiero saber que % representa cada una de las partidas en referencia a la cifra de negocios que es una de las partidas y para diferentes años. Ésta será el 100% y el resto en función de ella.
    Las columnas son los años, las filas son los epígrafes y los campos valor el saldo.
    Gracias

    ResponderEliminar
    Respuestas
    1. Hola Javier,
      existe una opción de cálculo llamado Mostrar valores como... que te ofrece diferentes operaciones, entre ellas hay una de % sobre la fila... quizá te pueda servir

      un saludo!

      Eliminar
  34. Hola Ismael.

    Tengo esta función en un campo calculado, pero me aparece #Valor.
    La función es esta: =SI(Rango>70;"Correcto";"Incorrecto")

    ¿Sabes por que sucede?

    Un saludo y gracias,

    ResponderEliminar
    Respuestas
    1. Hola,
      un campo calculado debería retornar valores numéricos para el correcto funcionamiento...
      además ten presente el funcionamiento de las fórmulas en los campos calculados de las tablas dinámicas, evaluará el dato acumulado del campo 'Rango'y sobre esto actuará.

      Prueba cambiando los textos por un cero y un uno... a ver qué pasa.

      Saludos

      Eliminar
  35. Buenos días, he probado de hacer un Elemento Calculado en mi tabla dinámica y me sale el siguiente error:

    "No está permitido usar los campos de datos múltiples de un mismo campo si el informe de tabla dinámica contiene elementos calculados."

    A alguien le ha pasado? Mi tabla tiene un campo de etiquetas de fila, uno de etiquetas de columna y uno como valores.. no tiene nada más.

    Muchas gracias!

    ResponderEliminar
    Respuestas
    1. Hola,
      a veces las TD se crean a partir de una anterior, que sí contenía elementos calculados en algunos de sus campos, y se hereda a la nueva.. con el consiguiente problema.
      Tendrás que buscar entre los diferentes campos si existe algún elemento calculado... y borrarlo si procede.
      Un saludo

      Eliminar
    2. Hasta que cantidad de filas y columnas puedo crear un elemento calculado??

      Eliminar
    3. Hola Daniel,
      que tal estás?, un placer saludarte igualmente.

      Un elemento calculado en una tabla dinámica se incorpora como un elemento más dentro de uno de los campos existentes, por tanto no existe un límite específico para tal cosa
      Slds

      Eliminar
  36. Buenas tardes estimado. Necesito crear un elemento calculado en una tabla y me aparece un cuadro de diálogo como el siguiente:

    "Si uno o varios campos de la tabla dinámica tienen elementos calculados, no se puede usar ningún campo en el área de datos dos o más veces, o en el área de datos y en otra área al mismo tiempo. Si está intentando agregar un campo, quite los elementos calculados y vuelva a agregarlo. Si está intentando agregar un elemento calculado, cambie el informe de tabla dinámica para que ningún campo se use más de una vez y, a continuación, agregue el elemento calculado."

    La tabla no tiene elementos calculados ni campos calculados tampoco. Podría ayudarme?

    Muchas gracias.

    ResponderEliminar
    Respuestas
    1. Hola pp dapra,
      las tablas dinámicas emplean una cache sobre la que trabajan, y diferentes tablas dinámicas generadas a partir del mismo origen de datos (misma base de datos) comparten esa memoria cache 8por defecto), lo que las hace dependiente.

      Podría ocurrir que tuvieras dos o más tablas dinámicas y en una de ellas sí existieran esos elementos calculados.. por lo que al intentarlo en esa tercera Tabla dinámica, aunque pienses no tiene elementos calculados, sí existan en su cache.

      Te recomendaría de todas formas, para evitar este problema, generaras una tabla dinámica independiente:
      http://excelforo.blogspot.com.es/2009/12/asistente-para-tablas-dinamicas-version.html

      Saludos

      Eliminar
  37. Buenos días,
    Tengo un problema, cuando creo un campo calculado en una tabla dinámica, el total me aparece como la suma cuando quiero promedio, lo cambio a promedio pero me sigue apareciendo la suma. He probado de todas la maneras, ¿existe alguna manera de cambiarlo?

    Gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      la operación que devuelve siempre un campo calculado es sobre la suma.. no es configurable...

      Solo generando campos auxiliares en el origen se consiguen buenos resultados basado en la premisa anterior

      Slds

      Eliminar
  38. Tengo una pregunta, cuando creo un campo calculado y tengo filtros adicionales no funcionan, las formular que no pertenecen al filtro seleccionado siempre quedan y dan error. Que podría suceder?

    ResponderEliminar
    Respuestas
    1. Hola Ronald,
      que tal estás?, un placer saludarte igualmente.

      Un campo calculado de una tabla dinámica siempre funcionará, estén o no los campos mostrados en la tabla
      Igualmente con los elementos calculados.. aunque los elementos sobre los que se base el cálculo no aparezcan por estar filtrados, el cálculo del elemento calculado operará correctamente

      Qué acción realizas cuando haces esos 'filtros adicionales' ???

      Saludos cordiales

      Eliminar
  39. Hola, muy buen tutorial de como implementar los campos calculados a una tabla dinamica.
    Yo tengo un caso particular, quiero calcular los porcentajes de de 2 variables.

    tengo una columna que me divide por 2 estados, SOLUCIONADO y EN CURSO, de los cuales CADA UNO TIENE 4 tipos de atención que son:
    Dentro del Plazo.
    Próximas por Vencer.
    Vencidas.
    Desfasadas.
    Las cuales quiero calcular el porcentaje de cumplimiento donde cumplimiento solo seria los casos atendidos dentro del plazo ..


    como puedo sacar el porcentaje al final sin agregar una columna de porcentaje por cada columna..

    ResponderEliminar
    Respuestas
    1. Hola,
      si he comprendido bien, podrías añadir una columna calculada en el origen de datos con el cálculo porcentual formulando con PROMEDIO.SI.CONJUNTO, CONTAR.SI.CONJUNTO O SUMAR.SI.CONJUNTO (depende del cálculo porcentual que necesites) condicionado a esos tipos de atención por Estado...
      Luego llevar ese nuevo campo a la tabla dinámica y resumirlo como proceda
      Saludos y espero te de alguna pista

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.