✅ Cómo agregar y usar un campo calculado de tabla dinámica de Excel

Tabla de contenido

¿Qué es un campo calculado con tabla dinámica?
Agregar un campo calculado a la tabla dinámica
Un problema con los campos calculados de la tabla dinámica
¿Cómo modificar o eliminar un campo calculado de tabla dinámica?
¿Cómo obtener una lista de todas las fórmulas de campo calculadas?

A menudo, una vez que crea una tabla dinámica, es necesario que amplíe su análisis e incluya más datos / cálculos como parte de él.

Si necesita un nuevo punto de datos que pueda obtenerse mediante el uso de puntos de datos existentes en la tabla dinámica, no es necesario que vuelva atrás y lo agregue a los datos de origen. En su lugar, puede utilizar un Campo calculado de tabla dinámica para hacer esto.

Descargue el conjunto de datos y sígalo.

¿Qué es un campo calculado con 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 se muestra a continuación:

La tabla dinámica anterior resume los valores de ventas y ganancias de los minoristas.

Ahora, ¿qué sucede si también desea saber cuál fue el margen de beneficio de estos minoristas (donde el margen de beneficio es "Beneficio" dividido por "Ventas")?

Hay un par de formas de hacer esto:

Regrese al conjunto de datos original y agregue este nuevo punto de datos. Por lo tanto, puede insertar una nueva columna en los datos de origen y calcular el margen de beneficio en ella. Una vez que haga esto, debe actualizar los datos de origen de la tabla dinámica para obtener esta nueva columna como parte de ella.
- Si bien este método es una posibilidad, deberá volver manualmente al conjunto de datos y realizar los cálculos. Por ejemplo, es posible que deba agregar otra columna para calcular la venta promedio por unidad (Ventas / Cantidad). Nuevamente, tendrá que agregar esta columna a sus datos de origen y luego actualizar la tabla dinámica.
- Este método también hincha su tabla dinámica a medida que le agrega nuevos datos.
Agregue cálculos fuera de la tabla dinámica. Esta puede ser una opción si es poco probable que cambie la estructura de su tabla dinámica. Pero si cambia la tabla dinámica, es posible que el cálculo no se actualice en consecuencia y podría dar resultados incorrectos o errores. Como se muestra a continuación, calculé el margen de beneficio cuando había minoristas en la fila. Pero cuando lo cambié de clientes a regiones, la fórmula dio un error.
Usando un campo calculado de tabla dinámica. Este es el forma más eficiente para utilizar los datos existentes de la tabla dinámica y calcular la métrica deseada. Considere el campo calculado como una columna virtual que ha agregado utilizando las columnas existentes de la tabla dinámica. Hay muchos beneficios de usar un campo calculado de tabla dinámica (como veremos en un minuto):
- No requiere que maneje fórmulas o actualice los datos de origen.
- Es escalable, ya que contabilizará automáticamente cualquier dato nuevo que pueda agregar a su tabla dinámica. Una vez que agregue un campo Calcular, puede usarlo como cualquier otro campo en su tabla dinámica.
- Es fácil de actualizar y administrar. Por ejemplo, si las métricas cambian o necesita cambiar el cálculo, puede hacerlo fácilmente desde la propia tabla dinámica.

Agregar un campo calculado a la tabla dinámica

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

Suponga que tiene una tabla dinámica como se muestra a continuación y desea calcular el margen de beneficio para cada minorista:

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

Seleccione cualquier celda de la tabla dinámica.
Vaya a Herramientas de tabla dinámica -> Analizar -> Cálculos -> Campos, elementos y conjuntos.
En el menú desplegable, seleccione Campo calculado.
En el cuadro de diálogo Insertar archivado calculado:
- Asígnele un nombre ingresándolo en el campo Nombre.
- En el campo Fórmula, cree la fórmula que desee para el campo calculado. Tenga en cuenta que puede elegir entre los nombres de campo que se enumeran a continuación. En este caso, la fórmula es "= Beneficio / Ventas". Puede ingresar manualmente los nombres de los campos o hacer doble clic en el nombre del campo que aparece en el cuadro Campos.
Haga clic en Agregar y cierre el cuadro de diálogo.

Tan pronto como agregue el campo calculado, aparecerá como uno de los campos en la lista de 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 el 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 filas, obtendrá el resultado como se muestra a continuación, donde se informa el valor del margen de beneficio para los minoristas y la región.

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

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

NO PUEDE usar referencias o rangos con nombre mientras crea un campo calculado de tabla dinámica. Eso descartaría muchas fórmulas como BUSCARV, ÍNDICE, DESPLAZAMIENTO, etc. Sin embargo, puede utilizar fórmulas que funcionen sin referencias (como SUM, IF, COUNT, etc.).
Puede utilizar una constante en la fórmula. Por ejemplo, si desea conocer las ventas previstas en las que se prevé un crecimiento del 10%, puede utilizar la fórmula = Ventas * 1,1 (donde 1,1 es constante).
El orden de precedencia se sigue en la fórmula que hace el campo calculado. Como práctica recomendada, utilice paréntesis para asegurarse de no tener que recordar el orden de precedencia.

Ahora, veamos un ejemplo del uso de una fórmula avanzada para crear un campo calculado.

Suponga que tiene el conjunto de datos como se muestra a continuación y necesita mostrar el valor de ventas pronosticado en la tabla dinámica.

Para el valor pronosticado, debe utilizar un aumento de ventas del 5% para los grandes minoristas (ventas superiores a 3 millones) y un aumento de las ventas del 10% para los minoristas pequeños y medianos (ventas por debajo de los 3 millones).

Nota: Los números de ventas aquí son falsos y se han utilizado para ilustrar los ejemplos de este tutorial.

He aquí cómo hacer esto:

Seleccione cualquier celda de la tabla dinámica.
Vaya a Herramientas de tabla dinámica -> Analizar -> Cálculos -> Campos, elementos y conjuntos.
En el menú desplegable, seleccione Campo calculado.
En el cuadro de diálogo Insertar archivado calculado:
- Asígnele un nombre ingresándolo en el campo Nombre.
- En el campo Fórmula, utilice la siguiente fórmula: = SI (Región = "Sur", Ventas * 1.05, Ventas * 1.1)
Haga clic en Agregar y cierre el cuadro de diálogo.

Esto agrega una nueva columna a la tabla dinámica con el valor de pronóstico de ventas.

Haga clic aquí para descargar el conjunto de datos.

Un problema con los campos calculados de la tabla dinámica

El campo calculado es una característica asombrosa que realmente mejora el valor de su tabla dinámica con cálculos de campo, mientras mantiene todo escalable y manejable.

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

Supongamos que tengo una tabla dinámica como se muestra a continuación, donde utilicé el campo calculado para obtener las cifras de ventas previstas.

Tenga en cuenta que los totales totales y subtotales no son correctos.

Si bien estos deben agregar el valor de pronóstico de ventas individual para cada minorista, en realidad, sigue la misma fórmula de campo calculada que creamos.

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

Desafortunadamente, no hay forma de que pueda corregir esto.

La mejor manera de manejar esto sería eliminar subtotales y totales generales de su tabla dinámica.

También puede pasar por algunas soluciones alternativas innovadoras que Debra ha demostrado para manejar este problema.

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

Una vez que haya creado un campo calculado de tabla dinámica, puede modificar la fórmula o eliminarla siguiendo estos pasos:

Seleccione cualquier celda de la tabla dinámica.
Vaya a Herramientas de tabla dinámica -> Analizar -> Cálculos -> Campos, elementos y conjuntos.
En el menú desplegable, seleccione Campo calculado.
En el campo Nombre, haga clic en la flecha desplegable (pequeña flecha hacia abajo al final del campo).
De la lista, seleccione el campo calculado que desea eliminar o modificar.
Cambie la fórmula en caso de que desee modificarla o haga clic en Eliminar en caso de que desee eliminarla.

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

Si crea una gran cantidad de campos calculados de tabla dinámica, no se preocupe por realizar un seguimiento de la fórmula utilizada en cada uno de ellos.

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 la lista de fórmulas de Todos los campos calculados:

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

Tan pronto como haga clic en Listar fórmulas, Excel insertará automáticamente una nueva hoja de trabajo que tendrá los detalles de todos los campos / elementos calculados que ha utilizado en la tabla dinámica.