Campo calculado en tabla dinámica de Excel: cómo crearlo

Un campo calculado es una fórmula que se añade a una tabla dinámica para obtener un dato nuevo a partir de los campos que ya tiene resumidos. Sirve, por ejemplo, para sacar el precio medio de un artículo, calcular una comisión sobre el importe vendido o aplicar un margen, sin tocar la tabla de origen. Al terminar este tutorial sabrás crearlo, modificarlo y saber cuándo conviene resolver el cálculo de otra manera. Los campos calculados están disponibles en Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024 y Microsoft 365, y también en Excel para Mac y Excel para la web, salvo en tablas dinámicas basadas en cubos OLAP, donde esta opción no aparece.

Qué es un campo calculado y para qué sirve

Imagina la hoja de pedidos de una tienda con el vendedor, la cantidad vendida y el importe de cada línea. Al llevarla a una tabla dinámica, tienes el total de importe por vendedor, pero no el precio medio por artículo (Importe partido por Cantidad) ni un margen del 30% sobre el Importe. Podrías añadir una columna en la tabla de origen, pero eso obliga a tocar los datos cada vez que cambien las reglas.

El campo calculado resuelve esto dentro de la propia tabla dinámica: defines una fórmula que combina los campos que ya están resumidos y el resultado aparece como una columna más del informe. No modifica la tabla de origen y se refresca junto con el resto de la tabla al pulsar Analizar tabla dinámica > Actualizar o Alt+F5.

La regla que tienes que tener clara es esta: el campo calculado trabaja siempre sobre los valores que la tabla dinámica tiene resumidos. Por eso no puede leer celdas externas, no promedia por sí mismo y no usa los totales generales como dato. Se trata de una operación matemática entre campos ya sumados, no de una fórmula que recorre las filas del origen.

Dónde está el campo calculado en la cinta

Cuando hay una tabla dinámica en la hoja, aparece una pestaña llamada Analizar tabla dinámica. Es ahí donde se crea el campo calculado.

Al pulsar Campo calculado se abre un cuadro con tres zonas: una lista con los campos del origen, un campo para escribir el nombre del nuevo campo y un campo más grande para la fórmula. Abajo, los botones Agregar, Modificar y Eliminar, y los clásicos Aceptar y Cancelar.

La fórmula del cuadro: nombres de campos y operadores

La fórmula se escribe con el nombre de cada campo entre corchetes, por ejemplo [Importe], y se combinan con operadores: +, -, * y /. Para añadir un campo a la fórmula tienes el botón Insertar campo, que evita escribirlo a mano y se asegura de que el nombre coincide con el que la tabla dinámica reconoce.

No se pueden usar referencias a celdas (=B2*0,10) ni a totales de la propia tabla dinámica (=Total general*0,10). El cuadro solo entiende operaciones entre los campos que ya forman parte del informe.

Lo que un campo calculado puede y no puede hacer

• Puede: usar los operadores +, -, * y / entre campos del informe, y anidar la función SUMA y la función SI.
• Puede: refrescarse al actualizar la tabla dinámica si los datos de origen cambian.
• No puede: leer una celda externa de la hoja ni mezclar campos de tablas dinámicas distintas.
• No puede: usar la mayoría de funciones de hoja: PROMEDIO, MAX, BUSCARV, etc. no están admitidas.
• No puede: tomar como dato el total general ni un subtotal de la tabla dinámica; solo opera con los campos arrastrados a Valores.
• No puede: aparecer en una tabla dinámica creada desde un cubo OLAP (orígenes como SQL Server Analysis Services); ahí se usan medidas del propio cubo.

Crear un campo calculado paso a paso

Vamos a usar la hoja de pedidos de la tienda. La hoja Pedidos tiene cuatro columnas: Producto, Vendedor, Cantidad e Importe, con los datos de los pedidos del mes.

1. Selecciona una celda dentro del rango de datos A1:D21.
2. Abre el asistente: Insertar > Tablas > Tabla dinámica y acepta la ubicación en una hoja nueva.
3. En la lista de campos, arrastra Vendedor a Filas e Importe a Valores. La tabla dinámica muestra el total de Importe por vendedor.
4. Abre el cuadro: Analizar tabla dinámica > Cálculo > Campos, elementos y conjuntos > Campo calculado.
5. En Nombre escribe Comisión y en Fórmula escribe =Importe*0,10. Pulsa Agregar y luego Aceptar.
6. En la lista de campos aparece ahora Comisión; arrástrala a Valores, debajo de Suma de Importe.

Con estos datos, la tabla dinámica queda así: Suma de Importe y Comisión calculada al 10% para cada vendedor.

Una comisión que cambia si se supera un objetivo

Sobre la misma tabla, supón que la comisión es del 10% cuando el vendedor supera 1.000 en Importe y del 5% en caso contrario. Aquí entra la función SI.

1. Abre otra vez: Analizar tabla dinámica > Cálculo > Campos, elementos y conjuntos > Campo calculado.
2. En Nombre escribe Comisión (sustituye al anterior) y en Fórmula escribe =SI(Importe>1000;Importe*0,10;Importe*0,05).
3. Pulsa Agregar y Aceptar. La nueva Comisión sustituye a la anterior en la zona de Valores.

Con la hoja de pedidos del ejemplo ningún vendedor llega a 1.000, así que todos cobran el 5%.

Comprobar que el cálculo es correcto

Para verificar el resultado, ve a la hoja Pedidos y calcula la comisión manualmente con una fórmula sobre el origen.

La suma de la columna E para los pedidos de López es 3,75 + 0,80 + 3,20 + 1,88 = 9,63, que coincide con la Comisión de López en la tabla dinámica.

Modificar y borrar un campo calculado

Un campo calculado se cambia desde el mismo cuadro donde se creó.

1. Abre Analizar tabla dinámica > Cálculo > Campos, elementos y conjuntos > Campo calculado.
2. En el desplegable Nombre elige el campo que quieres tocar (por ejemplo, Comisión).
3. Edita la Fórmula y pulsa Modificar para guardar el cambio, o Eliminar para borrarlo del informe. Termina con Aceptar.

Al pulsar Aceptar, la tabla dinámica aplica el cambio. Si después modificas los datos de la hoja Pedidos, la tabla dinámica no se entera sola: tienes que actualizarla con Analizar tabla dinámica > Actualizar o Alt+F5. El campo calculado se recalcula junto con el resto del informe en ese momento.

Por qué el campo calculado da resultados que no esperas

Tres causas reales que aparecen una y otra vez al trabajar con campos calculados, con la hoja que falla en cada caso.

El campo muestra el mismo total en todas las filas

Si todas las filas de la tabla dinámica muestran la misma Comisión, la fórmula probablemente apunta a un total en vez de a un campo del informe. Por ejemplo, escribiste =Total*0,10 o pegaste un nombre que la tabla dinámica interpreta como una etiqueta de fila. Repasa que la fórmula use los nombres de campo del origen entre corchetes ([Importe]) y que el botón Insertar campo los haya añadido, no escritos a mano.

Aquí la Comisión es 32,75 en todas las filas, que es la comisión total, no la de cada vendedor. La fórmula estaba mal construida.

Quiero un promedio y siempre sale una suma

Los campos calculados solo operan sobre sumas. Aunque arrastres un campo a Valores como Promedio, el campo calculado que combines con él usará la suma de ese campo, no su promedio. Para un promedio real, tienes dos opciones: calcularlo en la tabla de origen (por ejemplo, =PROMEDIO(Importe/Cantidad) con la tabla como tabla de Excel) o usar Mostrar valores como en el campo de valor, sin crear un campo calculado.

La fórmula no se actualiza al cambiar los datos

Si cambias un Importe en la hoja Pedidos y la tabla dinámica sigue mostrando la Comisión antigua, falta refrescar. Selecciona una celda de la tabla dinámica y pulsa Alt+F5, o ve a Analizar tabla dinámica > Actualizar. El campo calculado se recalcula junto con el resto del informe.

Cuando un campo calculado no es la herramienta adecuada

El campo calculado solo sirve para operaciones entre campos ya sumados. Si necesitas algo distinto, el camino cambia.

Leer una celda externa: el cálculo en el origen de datos

Si la comisión depende de un porcentaje guardado en una celda de la hoja, o de un tipo de cambio, o de un valor que cambia fuera de la tabla dinámica, mejor añade una columna en la tabla de origen y conviértela en tabla de Excel (Inicio > Estilos > Dar formato como tabla). La columna nueva se rellena con una fórmula que lee la celda externa, y la tabla dinámica la arrastra a Valores como un campo más. Al actualizar, recoge los cambios sin tocar nada más.

Porcentaje sobre el total: "Mostrar valores como"

Si lo que quieres es ver cada vendedor como porcentaje del total, no hace falta campo calculado. Haz clic en el campo Suma de Importe dentro de la tabla dinámica, elige Mostrar valores como y selecciona % del total general. La columna pasa a mostrar 18%, 59%, 23% y 100% en el total.

Sacar el dato a una celda con IMPORTARDATOSDINAMICOS

Si necesitas el dato en una celda concreta para combinarlo con otras fórmulas de la hoja, usa la función IMPORTARDATOSDINAMICOS (en inglés GETPIVOTDATA). En A3 está la celda que contiene la palabra «Importe» dentro de la tabla dinámica, y A1 es una celda de la propia tabla:

=IMPORTARDATOSDINAMICOS("Importe";A3;"Vendedor";"López")

Esto devuelve el total de Importe de López a la celda donde está la fórmula. A partir de ahí puedes multiplicar, restar o encadenar con otras funciones como SI o BUSCARV, porque ya estás en una celda normal de la hoja.

Lo que ya sabes hacer con un campo calculado

• Está en Analizar tabla dinámica > Cálculo > Campos, elementos y conjuntos > Campo calculado y se crea con un nombre, una fórmula entre corchetes y el botón Agregar.
• La fórmula admite los operadores +, -, *, /, la función SUMA y la función SI; nada de referencias a celdas, promedios ni totales.
• Solo trabaja sobre los campos arrastrados a Valores y no aparece en tablas dinámicas basadas en cubos OLAP.
• Se modifica desde el mismo cuadro con Modificar o se elimina con Eliminar, y se actualiza con Alt+F5 al cambiar los datos.
• Cuando el cálculo depende de una celda externa, de un promedio real o de sacar el dato a una celda, conviene usar la tabla de origen, Mostrar valores como o IMPORTARDATOSDINAMICOS.

Siguiente paso: crea un campo calculado que use SI sobre dos campos a la vez, por ejemplo =SI(Importe>500;Importe*0,10;Importe*0,05) combinado con Cantidad para premiar también a quien vende más unidades, y comprueba el resultado contra un cálculo manual en la hoja Pedidos.