Etiqueta tablas dinámicas

¿Cómo creamos campos calculados a partir de datos externos?

Las tablas dinámicas nos permiten calcular, resumir y analizar datos, de manera que podemos hacer comparaciones o ver patrones y tendencias en ellos.

Sin embargo, puede ocurrir que las funciones de resumen no nos proporcionen los resultados que necesitamos. Entonces podemos crear nuestras propias fórmulas en los campos calculados. Los campos calculados son una columna más de la tabla dinámica. En ellos podemos trabajar con fórmulas simples o bien recibir resultados de cálculos de funciones de la hoja de cálculo.

En nuestro ejemplo, vamos a acceder a una base de datos externa con cerca de 21.000 registros de ventas de 100 productos distintos. Cada venta supone una comisión a la empresa del 5%. Queremos saber el importe del pago de comisiones de venta por producto. Vamos a usar los valores de uno de los campos de la base de datos. Por tanto, una tabla dinámica con un campo calculado es una buena solución pues usamos los datos de un campo en la fórmula a calcular.

En primer lugar, conectamos el libro de trabajo con la base de datos. A continuación, creamos la tabla dinámica y añadimos el campo calculado. Por último, damos formato a la tabla dinámica.

Tabla dinámica y datos externos

Queremos un resumen de las ventas de cien productos que incluya el importe de la comisión de las ventas (un 5%). Contamos con una base de datos de veinte y un mil registros.

Conectamos el libro de trabajo con la base datos para construir la tabla dinámica.

Puede parecer que la base de datos no contiene demasiados registros y que podrían estar en una hoja del libro de trabajo. Hay dos buenas razones para que no sea así: primera, la velocidad de procesado es más rápida, y segunda, la integridad de los datos.

Si en lugar de veinte y un mil registros la base datos contuviera un millón, las ventajas serían evidentes.

¿Cómo lo hacemos?

Una vez conectados el libro de trabajo y la base de datos construimos la tabla dinámica y, dentro de ella, creamos el campo calculado.

Paso 1: conectamos con la base de datos

Abrimos el menú >> Tabla dinámica desde la barra de menús (menú >> Insertar)

Nos aparece la ventana >>> Crear tabla dinámica

Elegimos la opción >>> Utilizar una fuente de datos externa:

Seleccionamos la fuente de datos externa:

Abrimos la conexión y elegimos la celda inicial para crear la tabla dinámica:

Ya hemos conseguido la conexión con la base de datos. Aparece la ventana para crear la tabla dinámica con los campos que podemos usar.

Paso 2: creamos la tabla dinámica

Para construir la tabla dinámica, arrastramos el campo PRODUCTOS a la sección Filas:

A continuación arrastramos el campo VENTAS a la sección Valores:

Podemos configurar el campo de valor con otras operaciones (Promedio, Recuento, Máximo, Mínimo…), pero para este caso nos interesa la suma.

Una vez configurados los valores, la tabla dinámica está lista:

Paso 3: creamos el campo calculado

Abrimos el menú >>> Campos, elementos y conjuntos desde la barra de menús (menú >>> Analizar)

Elegimos la opción >>> Campo calculado:

Aparece la ventana >>> Insertar campo calculado:

Damos un nombre al campo calculado. En este caso, COMISION VENTAS.

Creamos la fórmula para el campo calculado. Como queremos el importe de las comiones por producto, la fórmula es el campo VENTAS por el 5%.

Y conseguimos el campo calculado con las comisiones por ventas.

Paso 4: damos formato a la tabla

Para presentar un informe de las comisones pagadas fácil de interpretar, vamos a dar formato a los valores.

Con el botón derecho del ratón conseguimos el menú auxiliar de Excel:

Elegimos el menú >> Configuración de campo de valor:

Al hacer clic en la opción, nos aparece la ventana >>> Configuración de campo de valor:

Damos un nombre personalizado a la columna de VENTAS:

También damos nombre a la columna COMISION:

Finalmente hacemos lo mismo con la columna PRODUCTOS:

Aplicamos formato moneda a los valores:

Y por último, rotulamos los encabezados para que destaquen: