sábado, octubre 14, 2006

Tablas Dinámicas en Excel – Campos y Elementos calculados.

Las tablas dinámicas son un tipo de objeto que "reside" en la hoja de cálculos pero el rango ocupado por la tabla tiene un comportamiento distinto a los rangos normales de Excel. El rango ocupado por la tabla dinámica no puede ser modificado directamente en la hoja. Por ejemplo, no podemos agregar líneas o columnas, o fórmulas en las distintas celdas.

Cuando queremos hacer este tipo de modificaciones en una tabla dinámica tenemos dos posibilidades:

1 - seleccionar la tabla y copiarla a otra ubicación (ya sea en la misma hoja, en otra o en otro cuaderno) usando Edición – Pegado Especial – Valores. Este método tiene la ventaja de dar mucha flexibilidad en el manejo de los datos (formatos, fórmulas, etc); pero tiene la gran desventaja de romper el vínculo dinámico entre la tabla y los datos originales.

2 – Crear campos y/o elementos calculados. De esto nos ocuparemos en esta nota.

Para crear los ejemplos hemos modificado la tabla de datos de la nota anterior, agregándole los campos "mes" y "unidades".

Basándonos en nuestra tabla de datos hemos creado esta tabla dinámica





En el área de Página hemos puesto los meses, de manera que podemos ver los datos de cada mes con un clic.

Ahora digamos que queremos calcular el precio promedio de las unidades vendidas (ventas / unidades = precio promedio) y también agrupar los meses por bimestres (enero + febrero = bimestre 1).

El precio promedio consiste en crear un nuevo campo, dividiendo los elementos de un campo por los de otro. Esto es un campo calculado. Los pasos a dar son los siguientes:

1 – cliqueamos en algún lugar de la tabla y en el asistente de Tablas Dinámicas activamos Fórmulas – Campos Calculados




2 - En la ventanilla "nombre" anotamos "Precio Promedio" y en la ventanilla "Fórmula" =Ventas/Unidades (con la ventanilla activada, hacemos doble clic a Ventas, luego anotamos el símbolo "/" y luego doble clic a Unidades)



3 - Después de pulsar "Aceptar" y realizar algunos ajustes al formato, obtenemos esta tabla dinámica



Como ven, hemos agregado un nuevo campo a la tabla: "Precio Promedio".


Para demostrar el uso de elementos calculados, reorganizamos nuestra tabla dinámica poniendo los meses como campos de fila y los departamentos en el área de Página




Para crear el elemento calculado "Bimestre 1" procedemos de la siguiente manera:

1 - Seleccionamos la celda A4 (donde aparece "Mes", el nombre del campo con cuyos elementos crearemos un elemento calculado), en el asistente de Tablas Dinámicas activamos el menú Fórmulas – Elementos Calculados

2 - En el diálogo que se abre seleccionamos "Mes" en la ventanilla "Campos"; en la ventanilla "elementos" vemos los meses (los elementos del campo). En la ventanilla nombre escribimos Bimestre 1; luego seleccionamos la ventanilla Fórmula y anotamos =enero+febrero (lo que se hace con un doble clic sobre el nombre del elemento). Luego apretamos Enter y volvemos a seleccionar el campo Mes




3 - Repetimos la operación para el Bimestre 2 (marzo+abril) y pulsamos "Aceptar".

Excel tiene dos problemas relacionados con los elementos calculados:
1 - Son agregados automáticamente al final de la lista de elementos del campo
2 – El total general incluye los elementos calculados, por lo tanto da como resultado el doble de lo que debería ser.




El primer problema lo resolvemos moviendo los elementos a la posición deseada. Una de las formas de hacer esto es copiar Bimestre 1 y pegarlo inmediatamente debajo de "febrero" (o escribir manualmente Bimestre 1). Excel reorganiza la tabla automáticamente



El problema del total general lo podemos tratar de dos maneras. Una es eliminar el total general para las columnas en Opciones de Tablas.
La otra es crear un nuevo elemento, Total, que suma Bimestre 1 y Bimestre 2



Obteniendo



En resumen:

Un campo calculado es un nuevo campo creado realizando operaciones con campos existentes.

Un elemento calculado es un nuevo elemento en un campo, creado con los elementos existentes del campo.

Para eliminar los campos o elementos calculados tenemos que usar el asistente de Tablas Dinámicas. Por ejemplo, para eliminar el campo Precio Promedio abrimos el diálogo de Fórmulas en Campos Calculados




En la ventanilla Nombre elegimos el campo que queremos eliminar y luego pulsamos el botón Eliminar.



Categorìas: Manejo de Datos_


Technorati Tags: ,

64 comentarios:

  1. el enlace del ejemplo esta roto.
    seria tan amable de actualizar

    agracedido

    ResponderBorrar
  2. Gracias por tus comentarios, me han resultado de gran ayuda. Los ejemplos son de los mejores publicados en la Web

    ResponderBorrar
  3. Hola, muy buenos tus ejemplos. Quisiera hacerte una pregunta: ¿Como puedo hacer para obtener el promedio de dias transcurridos entre dos fechas (inicio/fin) tomando como fuente de datos un elemento de fecha inicio y fecha fin de una tabla dinamica. Intenté generar un campo calculado pero tengo problemas con la diferencia entre fechas porque el excel las suma. Desde ya muchas gracias.

    ResponderBorrar
  4. Hola Guillermo,
    mandame tu archivo para que vea que es lo que quieres hacer (jorgedun@gmail.com).
    Me parece que ves suma en lugar de promedio debido a que estas dividiendo cada fecha por 1 (por cada fecha hay un día), pero tal vez no entiendo tu planteo.

    ResponderBorrar
  5. Hola, muy buena la explicacion. Me gustaría saber si se puede crear una fórmula pero que me lo calcule sólo en el subtotal o total, no en todas las filas.
    Muchas gracias.
    María

    ResponderBorrar
  6. Hola María
    no se puede. Pero siempre puedes copiar la tabla con la opción Pegado Especial--Valores y luego poner fórmulas donde te hagn falta.

    ResponderBorrar
  7. Jorge, buena la explicacion, pero me gustaria saber si se puede replicar el calculo de la opciones que indica % de columna, en un campo calculado, para replicar esta participacion?

    ResponderBorrar
  8. No entiendo del todo tu consulta, pero si puedes usar un campo calculado dentro de otro campo calculado.
    Por ejemplo, si tienes una campo calculado llamado Margen para obtener el margen de ganancia (Ventas-Costos), puedes generar otro campo calcular Margen%=Margen/Ventas

    ResponderBorrar
  9. necesito de su ayuda, tengo armadas varias tablas, en todas coincide el nombre "latintrust" pero en algunas me sale "latintrust" y en otras "latintrust 2". quiero que en todas me quede solo "latintrust" no se que hacer, saludos

    ResponderBorrar
  10. Pareciera ser que alguna de las tablas de base de la tabla dinámica, el campo "latintrust" aparece dos veces.

    ResponderBorrar
  11. Qué tal Jorge, felicitaciones por tu página antes que nada. Tengo la siguiente duda, en una tabla dinámica cree un elemento calculado que me muestra un subtotal, pero me encuentro con que también me incluye todos los demás elementos que se encuentran en el grupo... cuando lo que yo requiero es nomas que me haga el cálculo con los "Totales", pues mi base de datos tiene +65000 registros y se tarda muchísimos haciendo los cálculos por separado. ¿Hay forma de delimitar el alcance del elemento calculado o siempre sucede esto? Muchas gracias.

    ResponderBorrar
  12. Efectivamente, ese es uno de los inconvenientes con los elementos calculados.
    El tema es demasiado amplio para un comentario. Espero poder publicar dentro de poco una nota sobre el tema.

    ResponderBorrar
  13. Hola Jorge excekente tu pagina , tengo una pregunta relacionada con el total de columna que aparece en los campos calculados .. lo que necesito es que me sume los valores del campo calculado no que me realize la formula a nivel de totales hay alguna manera de hacer esto ?

    ResponderBorrar
  14. Quieres evitar los totales sólo del campo calculado o de todos los campos?

    ResponderBorrar
  15. Hola JOrge solo de los campos calculados es decir : tenemos columas A y B , la columna c es un campo calculado que tiene la siguiente formula C = (B-A)/C la tabla dinamica me calcula los totales como sumatoria es decir sumatoria de columnas A y B . Yo deseo la sumatoria de C pero lo que me sale en los totales de C es la aplicacion de la formula a nivel de totales

    ResponderBorrar
  16. Lo que te sugiero hacer es agregar una columna auxiliar en la tabla que sirve de base para la tabla dinámica, usando (B-A)/C. Luego agregas este campo a la tabla dinámica, en lugar de usar un campo calculado.

    ResponderBorrar
  17. Jorge, espero que te encuentres muy bien. Gracias por todo el apoyo que significa contar con este tipo de ayuda.
    He estado trabajando con TD durante un buen tiempo, ahora tengo una oportundiad para aprender respecto a que tengo un campo de texto en el rango de la TD, en este campo puedo ingresar la cantidad de caracteres que necesito (con un máximo de 250) sin embargo al actualizar la TD el texto queda truncado. ¿existe alguna forma que no suceda lo descrito?
    Muchas gracias de antemano.

    José Luis Arellano.
    jlarellanog@gmail.com

    ResponderBorrar
  18. Hola José Luis

    en teoría las celdas pueden contener hasta 255 caracteres. Hice una búsqueda rápida en la Internet y no encontré nada sobre el tema. Puedes mandarme el archivo?

    ResponderBorrar
  19. JOrge, gracias por responder.
    Ya te envié la planilla al correo gmail.

    saludos y gracias,

    ResponderBorrar
  20. Hola,
    Gracias por la ayuda que me está suponiendo tu blog.
    A ver si sé explicar mi problema. Tengo una tabla dinámica en la que me aparece la tipología de llamadas de cada uno de los usuarios de un servicio teléfonico. Tengo desglosado en ella el número de veces que cada "Usuario" ha seleccionado cada una de las tipologías. Lo que quiero es que me aparezca en otra columna el porcentaje que supone cada una de las tipologías sobre el número total de tipologías de cada Usuario. He conseguido añadir una columna con el porcentaje de cada tipología sobre el total de los Usuarios, pero eso no me sirve. Quiero la distribución del porcentaje por cada usuario.
    Por favor, ¿me puedes ayudar?
    Muchas gracias,

    Luis Campos

    ResponderBorrar
  21. Jorge, tengo el mismo problema que te planteo Jorge Luis Arellano, tengo campos de mas de 250 caracteres que vienen a la tabla dinamica cortados. Tiene solucion esto?

    ResponderBorrar
  22. Hola Lina
    no, el máximo que puede contener la celda es 255 caracteres (incluyendo espacios). Una solución puede ser pasarse a Excel 2007. Pero, por qué poner tanto texto en una celda?

    ResponderBorrar
  23. Muchas gracias por tu ayuda con el tema de los porcentajes.
    Eres un maestro!

    Un saludo,

    Luis Campos

    ResponderBorrar
  24. Hola, estoy trabajando con una tabla de 10 mil registros en donde tengo 3 columnas: ciudad, período, venta. Cuando quiero que la tabla calcule la variación de venta ((Venta actual/venta año anterior)-1)*100, al crear un elemento calculado para obtener el porcentaje de variación entre la venta de distintos períodos, la tabla se queda pegada.
    Me podrías ayudar a que no se quede pegada la tabla y calcule ese porcentaje de variación.
    Muchas gracias de antemano.
    Tomás Hermosilla

    ResponderBorrar
  25. Efectivamente, es uno de los problemas con los elementos calculados. En mi opinión es prefericle agregar una campo a la tabla de de origen.

    ResponderBorrar
  26. Hola muy interesante tus comentarios tengo la siguiente consulta:
    -Necesito sacar variaciones pocentuales de 2 columnas base para la tabla dinamica lo realice y luego procedo a soliictar la tabla dinamica pero como hago para que ese campo me lo muestre tal cual lo puse en la base sin ningun tipo de operacion (digase suma, cuenta o cualquier otra)

    ResponderBorrar
  27. Podés usar campos calculados lo que te evita tener que hacer los cálculos en columnas auxiliares en la base de datos (de esto se trata la nota). Si lo hacés calculando las variaciones en una columna auxiliar, dependen si los valores son únicos o no. Por ejemplo, digamos que en tu base de datos tenés tres columnas: Sucursal, Ventas 2007 y Ventas 2008. Querés calcular la variación de las ventas entre los años por sucursal. Si cada sucursal aparece en una única línea en la base de datos, podés usar casi cualquier operación (SUMA, MAX, MIN...por supuesto CONTAR no). Si una sucursal puede aparecer en más de una línea tendrías que usar PROMEDIO.

    ResponderBorrar
  28. Buenos días Jorge, excelente tu página y magnífica tu buena voluntad para ayudar y transmitir conocimiento !
    Consulta, como puedo utilizar el valor de una celda en la fórmula de un "Campo Calculado"?
    Existe algún truco? pregunto porque según MS-Excel esto no es posible...
    Gracias y saludos, Claudio

    ResponderBorrar
  29. No, no existe ningún truco. Pero casi siempre se puede encontrar un rodeo para lograr lo que estás buscando. Lo importante no es "congelarse" en una idea y pensar un poco más "lateralmente".

    ResponderBorrar
  30. Hola Jorge,

    gracias por adelantado por tu tiempo y por este excelente blog.

    La consulta... tengo una tabla dinámica cuyo origen de datos es una vista Oracle y diseñada con varios campos en la parte 'filas'. Cuando creo elementos calculados a partir de los elementos de la última fila, se crean dichos dichos elementos para cada una de las posibles combinaciones de las filas anteriores. Con lo cual se están mostrando combinaciones 'ficticias' no existentes en el origen de datos.

    ¿Alguna idea o referencia para evitar esta situación?

    Gracias de nuevo.

    ResponderBorrar
  31. No soy gran amigo de los elementos calculados y el que describes es uno de los motivos.
    Puedes crear un campo auxiliar en la tabla de origen que contenga los resultados buscados y agregarla a la tabla dinámica.

    ResponderBorrar
  32. Felicides por la pagina, tengo el problema comentado lineas arriba, cuando creo un elemento calculado me aparecen todas los datos, esten o no en el grupo

    ResponderBorrar
  33. Así es. Como pongo en el comentario del 13/5/10, la mejor solución es crear un campo adicional en la tabla de datos, donde calculas el valor del elemento para cada fla.

    ResponderBorrar
  34. Hola Jorge, primero que nada te felicito por el blog, es Excelente!!! tengo una duda desde hace bastante tiempo, espero poder explicarme.
    Siempre armo tablas dinámicas, y armo el Margen Bruto a partir de un campo calculado asi: (VTA-COSTO)/COSTO. Hay alguna forma de obtener los mismos resultados efectuando este calculo ya en la tabla de origen de los datos?? (para evitar la creacion siempre de un campo calculado)

    ResponderBorrar
  35. Tal como sugerís, agregando el campo en la tabla de datos.
    Gracias por los conceptos.

    ResponderBorrar
  36. Hola Jorge, tengo una tabla dinamica en la que los datos de los campos estan como "cuenta" y no como "suma". Lo que requiero es hacer un campo calculado que pueda sumar dos campos que esten como "cuenta" ya que lo que está pasando es que el campo calculado hace la operacion tomando los datos de los campos como si estuvieran en "suma". No sé si me hice entender

    ResponderBorrar
  37. Supongo que los campos contienen números y eso hace que el campo calculado los sume y no el resultado de la cuenta. De todas maneras, no puedes hacer operaciones con campos definidos como CUENTA.

    ResponderBorrar
  38. Me ha parecido muy interesante tu artículo y los ejemplos que utilizas.
    Siempre es de una gran ayuda contar con tus explicaciones cuando tienes dudas, por ello te agradezco mucho tu trabajo y dedicación.

    ResponderBorrar
  39. Hola Jorge,
    Por favor, tengo una duda respecto a un elemento calculado:
    Necesito el % de la variación por ejemplo: (Monto 2011/Monto 2010 - 1), lo calcula bien pero en el total general suma los % en vez de calcularlo del total.
    Cómo puedo cambiar eso? necesito que aparezca el mismo cálculo en el total.
    Muchas gracias!
    Ale

    ResponderBorrar
  40. Ale,

    creo que la técnica que muestro en esta nota te puede ayudar.

    ResponderBorrar
  41. Hola Jorge,
    tengo una tabla con un montón Elementos Calculados. Ahora necesito hacer otra tabla, bastante diferente, pero a la que le tengo que añadir los mismos elementos. Hay alguna forma de copiarlos, de una tabla a otra sin necesidad de volver a formularlos todos?
    Muchas gracias por adelantado y mi más sincera enhorabuena por tu blog !

    ResponderBorrar
  42. La única forma que se me ocurre es usando una macro.

    ResponderBorrar
  43. Hola, me parece bastante interesante y muy oportunas las soluciones que planteas.
    Mi problema es el siguiente, ya tengo una tabla dinámica y esta tiene dos columnas (digamos columna C y D) deseo agregar otra columna que me realice la división entre las dos columnas de la tabla dinámica, (C/D). ¿Qué solución podrías darme?

    ResponderBorrar
  44. Bien, ese es exactamente el tema de la nota: campos calculados.

    ResponderBorrar
  45. Muchas gracias! me ha servido de gran ayuda, muy claro y muy didáctico.

    ResponderBorrar
  46. MUchas gracias. Ha sido de gran ayuda para compensar la falta de explicaciones en el MS Excel.

    ResponderBorrar
  47. Hola, no sé si has visto mi anterior comentario. Ante todo gracias por la información que brindas. 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

    ResponderBorrar
  48. De acuerdo la base de conocimientos de Microsoft el número de elementos calculados está limitado sólo por la memoria disponible. Pero según mi experiencia, el trabajo con elementos calculados es siempre problemático. Es preferible crear una columna auxiliar en la base de datos.

    ResponderBorrar
  49. Hola Jorge, muchísimas gracias de nuevo por el blog.
    Estoy comenzando a usar esto de los campos calculados y tengo un problema, lo que necesito es que me calcule el acumulado del Stock de productos pero el dato inicial (stock actual) me aparece en otra celda, es decir, tengo una tabla dinámica con los pedidos previstos por producto y por semana y además tengo el stock inicial de cada producto. Añado un campo calculado para que me haga un acumulativo del stock de ese producto pero a la hora de sumar los pedidos en el primer dato/pedido debería tener en cuenta el stock inicial y no sé cómo hacerlo.
    Gracias de antemano.
    Sara.

    ResponderBorrar
  50. SI usas Excel 2010 es muy sencillo crear acumulados, tal como muestro en esta nota.
    Si usas versiones anteriores te sugiero que me envíes el archivo o un ejemplo para darme una idea más acabada de cómo están organizados tus datos.

    ResponderBorrar
  51. Hola.
    Es posible crear un campo calculado con una condicional. Ej. Si(Mes="Febrero",1,0)
    De antemano gracias

    ResponderBorrar
  52. En principio si, pero no en tu caso. En los campos calculados Excel evalúa los textos como 0, por lo que siempre verás cero en tu ejemplo.
    La solución sería crear un campo auxiliar (columna) en la tabla de datos.

    ResponderBorrar
  53. Jorge, gracias por tus sabios consejos
    Consulta: Como podría trasladar el ejemplo del cálculo de bimestres si los meses los tuviera en columnas y no en filas?.
    Saludos

    ResponderBorrar
  54. Se crean los elemenetos calculados de la misma manera y luego se ubica el campo "Mes" en el área de las columnas.

    ResponderBorrar
  55. Muy buen post, muchas gracias colega :)!

    ResponderBorrar
  56. Hola buen dia, yo me encuentro con el siguiente problema yo tengo los nombres de los venderos en las filas y en las columnas los países, le ingreso el % de ventas de centroamerica en relacion a la venta total, pero me lo incorpora en al final antes del total General por columnas, el cual tambien lo suma por ejemplo si tengo en Honduras $800 y en los demas países $0.00 el porcentaje es del 100% y la formula me da eso, pero en mi Gran Total debería de ser $800 pero no me da el Gran Total $801, como puedo mover la columna del elemento calculado??

    ResponderBorrar
  57. Fijate lo que pongo en el enlace Ayuda (en la barra superior de blog) y enviame el cuaderno.

    ResponderBorrar
  58. Hola Jorge, gusto en saludarte, te quería consultar como se hace un elemento calculado en Excel 2016, en mi caso aparece bloqueado, y leyendo por ahí indican que es por protección de la data y bla bla bla pero no dan una solución de cual es el procedimiento para crear un elemento calculado y por eso es mi consulta si existe una formula en DAX o cualquier otro procedimiento alterno para poder agregar un elemento calculado??

    para darte mas informacion de lo que estoy haciendo: la data esta dentro del mismo excel pero la paso por powerquery para filtrarla y prepararla para la tabla dinámica; lo que estoy haciendo es un simple Estado de Resultados:

    Ingresos xxx.xx
    Costos xxx.xx
    Resultado Bruto xxx.xx <---aquí es donde quiero agregar el campo calculado en la tabla dinámica (ingreso - Costos);
    Gastos operativos xxx.xx y así sucesivamente.

    pero estoy trancado porque no consigo como agregar el elemento calculado, de antemano muchas gracias por la ayuda y cualquier informacion que puedas darme para poder avanzar.

    Saludos!

    ResponderBorrar
  59. Hola Miguel, el tema de los elementos calculados (que tienen sus bemoles) y la alternativa creando con DAX merece una nota aparte. En estos días estoy de viaje (trabajo, no placer) de manera que no puedo prometer cuando la publicaré. Pero ¿por qué no lo hacer directamente con Power Query con una columna calculada?

    ResponderBorrar
  60. Gracias Jorge por responder, estaré atento a tu nota aparte; en cuanto a tu recomendación no logro visualizar como hacer un sub total en una fila con una columna; jaja luego me explicas como haerlo.... o no se si lo que hice para salir del paso, es lo que me estas recomendando y fue crear una tabla, aparte, dentro de powerquery donde agrupe toda la informacion en una linea y luego se la anexe a la tabla principal y así el sub total era una linea mas, desde mi punto de vista creo que no es lo correcto por el retrabajo.

    como siempre agradecido por tus sugerencias y éxito en el viaje!

    saludos,

    ResponderBorrar
  61. Bien, no me queda claro como están organizados tus datos. Si Costos e Ingresos están en columnas separadas, no hay problema. Si están en la misma columna (supongamos una columna donde los valores son Ingresos y Costos y la segunda columna los valores) entonces tendrías que "pivotear" la colimna Ingresos-Costos en Power Query para obtener dos columnas, hacer una columna con la doferencia y volve a "despivotear" la consulta.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.