Cómo agregar y usar campos calculados de tablas dinámicas de Excel

Por lo general, una vez que haya creado una tabla dinámica, necesita expandir su análisis e incluir más datos/cálculos en él.

Si necesita un nuevo punto de datos que se pueda obtener utilizando un punto de datos existente en la tabla dinámica, no es necesario volver atrás y agregarlo a los datos de origen.En su lugar, puede utilizarCampos calculados de tabla dinámicapara hacer esto.

¿Qué es un campo calculado de tabla dinámica?

Comencemos con un ejemplo básico de una tabla dinámica.

Suponga que tiene un conjunto de datos de minoristas y crea una tabla dinámica como esta:

La tabla dinámica anterior resume las ventas y los valores de ganancias del minorista.

Ahora, ¿qué pasaría si también quisiera saber cuáles fueron los márgenes de beneficio de esos minoristas (el margen de beneficio es "beneficio" dividido por "ventas")?

Hay varias maneras de hacer esto:

1. Vuelva al conjunto de datos original y agregue este nuevo punto de datos.Entonces puede insertar una nueva columna en los datos de origen y calcular el margen de beneficio en ella.Una vez hecho esto, debe actualizar los datos de origen de la tabla dinámica para obtener esta nueva columna como parte de ella.
   • Si bien este enfoque funciona, debe volver manualmente al conjunto de datos y hacer los cálculos.Por ejemplo, es posible que desee agregar otra columna para calcular las ventas promedio por unidad (ventas/cantidad).Nuevamente, debe agregar esta columna a los datos de origen y luego actualizar la tabla dinámica.
   • Este método también infla tu tabla dinámica a medida que le agregas nuevos datos.
2. Agregue cálculos fuera de las tablas dinámicas.Elija esta opción si es poco probable que cambie la estructura de su tabla dinámica.Sin embargo, si cambia la tabla dinámica, es posible que los cálculos no se actualicen en consecuencia y le den resultados erróneos o errores.Como se muestra a continuación, calculé el margen de beneficio cuando hay minoristas en la fila.Pero cuando lo cambio de cliente a área, la fórmula sale mal.
3. Calcular campos utilizando tablas dinámicas.este esUse los datos de la tabla dinámica existente y calcule elmanera más eficiente.Piense en los campos calculados como columnas virtuales que agrega usando columnas existentes en su tabla dinámica.El uso de campos calculados en tablas dinámicas tiene muchos beneficios (como veremos más adelante):
   • No requiere que trabaje con fórmulas o actualice los datos de origen.
   • Es extensible porque automáticamente tiene en cuenta cualquier dato nuevo que pueda agregar a la tabla dinámica.Después de agregar un campo calculado, puede usarlo como cualquier otro campo en una tabla dinámica.
   • Es fácil de actualizar y administrar.Por ejemplo, si una métrica cambia o necesita cambiar un cálculo, puede hacerlo fácilmente desde la propia tabla dinámica.

Agregar campos calculados a la tabla dinámica

Veamos cómo agregar un campo calculado de una tabla dinámica a una tabla dinámica existente.

Suponga que tiene una tabla dinámica similar a esta y desea calcular el margen de beneficio de cada minorista:

Estos son los pasos para agregar un campo calculado de tabla dinámica:

Después de agregar el campo calculado, aparecerá como uno de los campos en la lista Campos de tabla dinámica.

Ahora puede usar este campo calculado como cualquier otro campo de tabla dinámica (tenga en cuenta que no puede usar un campo calculado de tabla dinámica como filtro de informe o segmentación).

Como mencioné antes, el beneficio de usar un campo calculado de tabla dinámica es que puede cambiar la estructura de la tabla dinámica y se ajustará automáticamente.

Por ejemplo, si arrastro y suelto una región en el área de la fila, obtendrá resultados como los siguientes, donde se informan los valores de margen de beneficio para el minorista y la región.

 

En el ejemplo anterior, utilicé una fórmula simple (=Beneficio/Ventas) para insertar el campo calculado.Sin embargo, también puede usar algunas fórmulas avanzadas.

Antes de mostrarle un ejemplo del uso de una fórmula avanzada para crear un campo calculado de tabla dinámica, aquí hay algunas cosas que debe saber:

• No puede usar referencias o rangos con nombre al crear campos calculados de tabla dinámica.Esto excluirá muchas fórmulas como BUSCARV, ÍNDICE, DESPLAZAMIENTO, etc.Sin embargo, puede utilizar fórmulas que funcionan sin referencias (por ejemplo, SUMA, SI, CONTAR, etc...).
• Puede utilizar constantes en fórmulas.Por ejemplo, si quisiera saber el crecimiento de ventas pronosticado que se espera aumente en un 10 %, usaría la fórmula =Ventas*1.1 (donde 1.1 es una constante).
• Siga el orden de precedencia en las fórmulas que generan campos calculados.Como práctica recomendada, utilice paréntesis para asegurarse de no tener que recordar la precedencia.

Ahora, veamos un ejemplo de cómo crear un campo calculado usando una fórmula avanzada.

Suponga que tiene el conjunto de datos que se muestra a continuación y necesita mostrar las ventas pronosticadas en una tabla dinámica.

Para los valores de pronóstico, usaría un aumento de ventas del 300 % para minoristas grandes (más de 5 millones de ventas) y un aumento de ventas del 300 % para minoristas pequeños y medianos (menos de 10 millones de ventas).

Nota: Las cifras de ventas aquí son falsas y se han utilizado para ilustrar los ejemplos de este tutorial.

Aquí está cómo hacerlo:

Esto agregará una nueva columna a la tabla dinámica con valores de pronóstico de ventas.

Problema de campo calculado de tabla dinámica

Los campos calculados son una característica sorprendente que realmente aumenta el valor de las tablas dinámicas con cálculos de campo, al tiempo que mantiene todo escalable y manejable.

Sin embargo, hay un problema con los campos calculados de la tabla dinámica que debe comprender antes de usarlos.

Supongamos que tengo una tabla dinámica como la que se muestra a continuación y uso un campo calculado para obtener las cifras de ventas previstas.

Tenga en cuenta que los subtotales y los totales son incorrectos.

Si bien estos deberían agregar valores de pronóstico de ventas separados para cada minorista, en realidad, sigue la misma fórmula de campo calculado que creamos.

Entonces, para South Total, mientras que el valor debería ser 22,824,000, South Total lo informa incorrectamente como 22,287,000.Esto sucede porque usa la fórmula 21,225,800*1.05 para obtener el valor.

Desafortunadamente, no puede corregir este problema.

La mejor manera de manejar esto es eliminar los subtotales y los totales generales de la tabla dinámica.

También puedes pasar Presentado por Debra.Algunas soluciones innovadoras para hacer frente a este problema.

¿Cómo modificar o eliminar un campo calculado de una tabla dinámica?

Después de crear un campo calculado de tabla dinámica, puede modificar la fórmula o eliminarla mediante los siguientes pasos:

¿Cómo obtener una lista de todas las fórmulas de campo calculadas?

Si crea muchos campos calculados de tabla dinámica, no tiene que preocuparse por realizar un seguimiento de las fórmulas utilizadas en cada campo.

Excel le permite crear rápidamente una lista de todas las fórmulas utilizadas para crear campos calculados.

Estos son los pasos para obtener rápidamente una lista de todas las fórmulas de campo calculadas:

• Seleccione cualquier celda en la tabla dinámica.
• Vaya a Herramientas de tabla dinámica -> Analizar -> Campos, elementos y conjuntos -> Fórmulas de lista.

Una vez que haga clic en la fórmula de la lista, Excel insertará automáticamente una nueva hoja de cálculo con los detalles de todos los campos/elementos calculados que utilizó en la tabla dinámica.

Esta puede ser una herramienta muy útil si tienes que enviar trabajo a un cliente o compartirlo con tu equipo.