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: Excel, Tablas Dinamicas en Excel
el enlace del ejemplo esta roto.
ResponderBorrarseria tan amable de actualizar
agracedido
Acabo de actualizar el enlace.
ResponderBorrarGracias por tus comentarios, me han resultado de gran ayuda. Los ejemplos son de los mejores publicados en la Web
ResponderBorrarHola, 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.
ResponderBorrarHola Guillermo,
ResponderBorrarmandame 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.
Todah Rabbah
ResponderBorrarHola, 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.
ResponderBorrarMuchas gracias.
María
Hola María
ResponderBorrarno se puede. Pero siempre puedes copiar la tabla con la opción Pegado Especial--Valores y luego poner fórmulas donde te hagn falta.
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?
ResponderBorrarNo entiendo del todo tu consulta, pero si puedes usar un campo calculado dentro de otro campo calculado.
ResponderBorrarPor ejemplo, si tienes una campo calculado llamado Margen para obtener el margen de ganancia (Ventas-Costos), puedes generar otro campo calcular Margen%=Margen/Ventas
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
ResponderBorrarPareciera ser que alguna de las tablas de base de la tabla dinámica, el campo "latintrust" aparece dos veces.
ResponderBorrarQué 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.
ResponderBorrarEfectivamente, ese es uno de los inconvenientes con los elementos calculados.
ResponderBorrarEl tema es demasiado amplio para un comentario. Espero poder publicar dentro de poco una nota sobre el tema.
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 ?
ResponderBorrarQuieres evitar los totales sólo del campo calculado o de todos los campos?
ResponderBorrarHola 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
ResponderBorrarLo 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.
ResponderBorrarJorge, espero que te encuentres muy bien. Gracias por todo el apoyo que significa contar con este tipo de ayuda.
ResponderBorrarHe 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
Hola José Luis
ResponderBorraren 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?
JOrge, gracias por responder.
ResponderBorrarYa te envié la planilla al correo gmail.
saludos y gracias,
Hola,
ResponderBorrarGracias 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
Fijate en la técnica que muestro en esta nota
ResponderBorrarJorge, 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?
ResponderBorrarHola Lina
ResponderBorrarno, 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?
Muchas gracias por tu ayuda con el tema de los porcentajes.
ResponderBorrarEres un maestro!
Un saludo,
Luis Campos
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.
ResponderBorrarMe 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
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.
ResponderBorrarHola muy interesante tus comentarios tengo la siguiente consulta:
ResponderBorrar-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)
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.
ResponderBorrarBuenos días Jorge, excelente tu página y magnífica tu buena voluntad para ayudar y transmitir conocimiento !
ResponderBorrarConsulta, 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
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".
ResponderBorrarHola Jorge,
ResponderBorrargracias 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.
No soy gran amigo de los elementos calculados y el que describes es uno de los motivos.
ResponderBorrarPuedes crear un campo auxiliar en la tabla de origen que contenga los resultados buscados y agregarla a la tabla dinámica.
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
ResponderBorrarAsí 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.
ResponderBorrarHola Jorge, primero que nada te felicito por el blog, es Excelente!!! tengo una duda desde hace bastante tiempo, espero poder explicarme.
ResponderBorrarSiempre 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)
Tal como sugerís, agregando el campo en la tabla de datos.
ResponderBorrarGracias por los conceptos.
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
ResponderBorrarSupongo 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.
ResponderBorrarMe ha parecido muy interesante tu artículo y los ejemplos que utilizas.
ResponderBorrarSiempre es de una gran ayuda contar con tus explicaciones cuando tienes dudas, por ello te agradezco mucho tu trabajo y dedicación.
Hola Jorge,
ResponderBorrarPor 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
Ale,
ResponderBorrarcreo que la técnica que muestro en esta nota te puede ayudar.
Hola Jorge,
ResponderBorrartengo 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 !
La única forma que se me ocurre es usando una macro.
ResponderBorrarHola, me parece bastante interesante y muy oportunas las soluciones que planteas.
ResponderBorrarMi 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?
Bien, ese es exactamente el tema de la nota: campos calculados.
ResponderBorrarMuchas gracias! me ha servido de gran ayuda, muy claro y muy didáctico.
ResponderBorrarMUchas gracias. Ha sido de gran ayuda para compensar la falta de explicaciones en el MS Excel.
ResponderBorrarHola, 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.
ResponderBorrarUn saludo y gracias por la información
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.
ResponderBorrarHola Jorge, muchísimas gracias de nuevo por el blog.
ResponderBorrarEstoy 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.
SI usas Excel 2010 es muy sencillo crear acumulados, tal como muestro en esta nota.
ResponderBorrarSi 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.
Hola.
ResponderBorrarEs posible crear un campo calculado con una condicional. Ej. Si(Mes="Febrero",1,0)
De antemano gracias
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.
ResponderBorrarLa solución sería crear un campo auxiliar (columna) en la tabla de datos.
Jorge, gracias por tus sabios consejos
ResponderBorrarConsulta: Como podría trasladar el ejemplo del cálculo de bimestres si los meses los tuviera en columnas y no en filas?.
Saludos
Se crean los elemenetos calculados de la misma manera y luego se ubica el campo "Mes" en el área de las columnas.
ResponderBorrarMuy buen post, muchas gracias colega :)!
ResponderBorrarHola 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??
ResponderBorrarFijate lo que pongo en el enlace Ayuda (en la barra superior de blog) y enviame el cuaderno.
ResponderBorrarHola 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??
ResponderBorrarpara 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!
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?
ResponderBorrarGracias 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.
ResponderBorrarcomo siempre agradecido por tus sugerencias y éxito en el viaje!
saludos,
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