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

sábado, octubre 14, 2006

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: ,

60 comments:

Anónimo,  22 abril, 2007 22:28  

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

agracedido

Jorge L. Dunkelman 23 abril, 2007 16:10  

Acabo de actualizar el enlace.

Anónimo,  14 mayo, 2007 12:42  

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

Guillermo 08 agosto, 2007 01:24  

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.

Jorge L. Dunkelman 08 agosto, 2007 17:56  

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.

María 24 diciembre, 2007 01:24  

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

Jorge L. Dunkelman 24 diciembre, 2007 18:09  

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.

Anónimo,  04 enero, 2008 02:59  

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?

Jorge L. Dunkelman 04 enero, 2008 17:25  

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

ecuindex 25 abril, 2008 00:16  

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

Jorge L. Dunkelman 25 abril, 2008 16:09  

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

Anónimo,  22 julio, 2008 20: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.

Jorge L. Dunkelman 24 julio, 2008 23:30  

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.

Felix cabrejos,  21 agosto, 2008 20:12  

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 ?

Jorge L. Dunkelman 21 agosto, 2008 22:19  

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

Felix Cabrejos,  22 agosto, 2008 18:23  

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

Jorge L. Dunkelman 22 agosto, 2008 23:19  

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.

JL 10 septiembre, 2008 18:41  

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

Jorge L. Dunkelman 10 septiembre, 2008 19:32  

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?

JL 12 septiembre, 2008 21:28  

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

saludos y gracias,

Anónimo,  28 enero, 2009 17:04  

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

Jorge L. Dunkelman 28 enero, 2009 18:33  

Fijate en la técnica que muestro en esta nota

Lina 28 enero, 2009 21:26  

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?

Jorge L. Dunkelman 29 enero, 2009 11:29  

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?

Anónimo,  30 enero, 2009 15:50  

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

Un saludo,

Luis Campos

Anónimo,  10 marzo, 2009 22:05  

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

Jorge L. Dunkelman 11 marzo, 2009 21:34  

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.

Anónimo,  13 octubre, 2009 23:43  

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)

Jorge L. Dunkelman 14 octubre, 2009 17:42  

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.

Anónimo,  13 abril, 2010 18:06  

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

Jorge L. Dunkelman 13 abril, 2010 21:01  

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".

Anónimo,  13 mayo, 2010 13:32  

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.

Jorge L. Dunkelman 13 mayo, 2010 15:14  

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.

Anónimo,  18 agosto, 2010 06: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

Jorge L. Dunkelman 18 agosto, 2010 07:16  

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.

gastord 20 agosto, 2010 02:13  

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)

Jorge L. Dunkelman 20 agosto, 2010 07:54  

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

Anónimo,  19 febrero, 2011 05:48  

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

Jorge L. Dunkelman 19 febrero, 2011 08:03  

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.

Anónimo,  22 febrero, 2011 19:51  

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.

Anónimo,  30 junio, 2011 21:54  

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

Jorge L. Dunkelman 30 junio, 2011 22:54  

Ale,

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

quisap 10 agosto, 2011 13:55  

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 !

Jorge L. Dunkelman 11 agosto, 2011 11:07  

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

Anónimo,  03 septiembre, 2012 01:07  

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?

Jorge L. Dunkelman 03 septiembre, 2012 07:14  

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

Javi Velasco 10 abril, 2013 15:54  

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

Anónimo,  25 julio, 2013 14:35  

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

Perdicas Perdicas 06 noviembre, 2013 00:10  

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

Jorge Dunkelman 06 noviembre, 2013 07:22  

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.

Sara Barrio,  23 febrero, 2014 23:46  

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.

Jorge Dunkelman 24 febrero, 2014 12:02  

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.

Anónimo,  24 abril, 2014 22:34  

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

Jorge Dunkelman 25 abril, 2014 07:15  

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.

Alejandro Clavijo 18 marzo, 2015 16:22  

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

Jorge Dunkelman 18 marzo, 2015 19:20  

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

Rafael De León 26 junio, 2015 19:25  

Muy buen post, muchas gracias colega :)!

Unknown 17 noviembre, 2016 23:14  

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??

Jorge Dunkelman 18 noviembre, 2016 07:02  

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

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP