tablas dinamicas y campos calculados-ejemplos varios

Un estimado lector tiene problemas a la hora de totalizar y promediar datos en una tabla, dentro de la cual lleva ciertos registros sobre las ventas efectuadas en su Pyme. Me consulta sobre la forma de realizar estos cálculos mediante funciones o macros.... pero el tema se complica bastante y, si buscamos la solución por ese lado, estimo que nos encontraremos descartando una de las herramientas de análisis mas poderosas de Excel: las Tablas Dinámicas. Pero como todo en nuestra amada aplicación, tiene sus buenos secretos, los cuales una vez desentrañados nos generarán proyectos mas potentes y fáciles de mantener.

Vamos al trabajo y supongamos que tenemos la siguiente tabla:

es un registro de ventas común y corriente, donde visualizamos fecha, cantidad de artículos vendidos, vendedor e importe total de la venta.

[+/-] Ver el resto / Ocultar

Nos situamos en A1 con el cursor y luego nos dirigimos al menú Datos / Informe de Tablas Dinámicas, para iniciar el asistente. Una vez definido el rango, presionamos sobre el botón "diseño":

Y acomodamos los campos de esta forma:

noten que al campo "fecha" lo he colocado en la sección "página", para luego ir jugando un poco con esos datos

Presionamos Aceptar y obtendremos nuestra tabla totalizadora:

no dice mucho, todavía, pero algo es algo... ya tengo bien resumido el importe total de las ventas de cada vendedor, junto al total de artículos que vendió cada uno de ellos.

Si reacomodamos los campos en juego y damos un formato básico a los números, el panorama comienza a cambiar un poco:

definitivamente queda un poco mejor.

En B2 tengo el filtro que me permitirá visualizar la misma información, pero detallada por fecha. En la imagen de arriba se aprecia (Todas), ya que por defecto las Tablas Dinámicas tomarán toda la serie de datos. Pero les muestro un ejemplo, para que estén al tanto:

Si seleccionamos cualquiera de las tres fechas presentes en el ejemplo, obtendremos el siguiente resultado:

yep, solo las ventas correspondientes al 13/01/2011.

Volvemos a seleccionar (Todas) para continuar con nuestra tarea. Hasta el momento vimos la forma de "acomodar" los campos de distintas maneras y filtrar la información por fecha. Para un pequeño ejemplo mas, también sería factible "tirar" dentro de la tabla el campo "fecha", logrando este gran resumen:

totales por vendedor y por fecha

Y si cambio el orden vendedor/fecha por fecha/vendedor:

perfecto: totales x fecha y x vendedor

Bueno, la corto un rato con el tema de andar moviendo campos de un lado al otro, así continúo con el principal tópico de esta entrada: los campos calculados.

Si quisiéramos, mediante funciones, agregar algún campo calculado a nuestra tabla, nos encontraríamos ante muchos problemas. El primero y fundamental: Excel no lo reconocería como parte de la tabla dinámica y posteriormente nos veríamos obligados a actualizar las funciones a medida que incorporamos registros o realizamos modificaciones. 

Hacemos click dentro de la tabla dinámica, en el cuerpo central, y en la barra de herramientas de la tabla seleccionamos la siguiente opción:

de esta forma insertaremos un nuevo campo, totalmente personalizado.

Calcularemos las comisiones de cada vendedor, teniendo como base la cantidad de artículos vendidos.

A continuación Excel nos mostrará el formulario que debemos completar:

Analicemos: primero le dí un nombre mas o menos ilustrativo al nuevo campo (Comisiones). Luego, en Fórmula, ideé una sencilla función para calcular cuanto cobrará cada vendedor: dividí el campo importe sobre unidades, multiplicándolo luego por 17. Esto es solo un ejemplo, ya que la función podría ser cualquiera; aquí quiero mostrar como insertando el nombre de cada campo (haciendo doble click sobre la ventana "campos") Excel automáticamente realiza la fórmula solicitada:

si, ya tenemos nuestro nuevo campo calculado con los resultados de la función indicada.

Es muy sencillo: solo debo colocar en el formulario correctamente los nombres de cada campo, especificando que operación realizaré con ellos.

Lo mejor es que ahora existe el campo "Comisiones" y podría utilizarlo para agregar otro campo calculado. Imaginemos que no somos tan amarretes (tacaños, para los no argentinos que están leyendo esta entrada =) ) y decidimos abonar un plus a todas aquellas comisiones que superaron los $240. El nuevo campo calculado debería configurarse así:

observen como ingresé en el cuadro una función, haciendo mención al campo sobre el cual deseo actuar. aquí  se nota lo que indiqué mas arriba: estoy trabajando ahora sobre el campo "Comisión", el cual a su vez es un campo calculado. Esto es interminable.

Los resultados, como siempre en Excel, lindan con la perfección:

ahá, tal cual lo pedido: todas las comisiones que superan los $240 poseen un plus.

Para cerrar convenientemente el ejemplo, agreguemos otro campo calculado que nos arroje el total a abonar:

una simple suma

Y listo el pollo:

Así queda resuelto el problema planteado por mi lector, quien podrá ahora llevar de una forma totalmente automatizada la liquidación de comisiones, a través de una herramienta muy poderosa y sin la necesidad de utilizar macros.

Pero como es fin de año, y este muy probablemente sea mi ultimo post del 2010, agregaré un par de cosas mas, que siempre es bueno saber.

Podemos tener en una misma hoja de cálculos varias tablas dinámicas, lo que nos permitirá crear un excelente tablero de control, el cual se actualizará de forma automática cada vez que modifiquemos datos en los rangos de entrada.

Voy a colocar otra tabla (debajo de la existente), en donde tendré los datos discriminados por fecha. Me dirijo nuevamente al menú Datos / Tablas Dinámicas y distribuyo los campos así:

Presionamos Aceptar y le indicamos al asistente en donde se colocará la nueva tabla dinámica, que en este ejemplo va debajo de la anterior:

ahora, en una misma hoja, tengo a simple vista cuanto vendió y aboné a cada vendedor (primer tabla) y luego los ingresos y egresos por fecha (segunda tabla)

No olviden que podemos darle un buen retoque a los nombres de campos (desde el asistente) y jugar un poco para mejorar el aspecto:

dos informes automáticos, en una misma hoja.

Y hasta podríamos agregar algún campito calculado a esta última tabla, para ver que utilidades obtuvimos:

Y sí, logicamente se nos presenta el resultado esperado:

Well, well, well, parece que llegamos al final del post del final del año.

No.

Mejor agreguemos un gráfico dinámico (insertar / gráfico) y tomamos la primer tabla como entrada de datos, mostrando solamente el total vendido por cada uno:

también podemos incorporar gráficas (que se actualizarán automaticamente) para darle mas impacto visual a nuestro tablero de control.

Voy a dejar los "elementos calculados" para el 2011, ya que es otra herramienta fundamental que nos hará factible añadir agrupamientos a la tabla, como por ejemplo crear un "grupo de vendedores" y así ir viendo cuanto vendió cada persona individual y grupalmente. O bien hacer lo mismo con las fechas.

Pero bueno, será tema de otra entrada. Espero que les sea de utilidad.

Que tengan todos un muy felíz año nuevo. Gracias por todo.