La vida (en Excel) es más sencilla con Tablas Dinámicas

En las últimas semanas he recibido varias consultas que tienen un común denominador: no conocer o no apreciar cabalmente las ventajas de usar tablas dinámicas.

Queremos manejar una serie de datos, para lo cual los vamos registrando en una hoja. Luego en otra hoja del cuaderno montamos un resumen de los datos, lo que por lo general nos llevará a armar fórmulas bastante complicadas.
Pero la vida en Excel puede ser mucho más sencilla si sabemos explotar las posibilidades de tablas dinámicas.

Veamos el caso de un lector que quiere manejar un registro de tasas de cambios del dólar y del euro frente al peso chileno. En una planilla se van registrando las tasas de cambio por día. El problema de mi lector, tal como lo describe en su consulta es:

…en otra planilla … tengo un cuadro [con] los promedios de cada mes, entonces cuando están llenos los campos del mes de agosto por ejemplo se llena la casilla con el valor promedio de agosto, y asi una serie de indicadores…
Mi idea es que exista una casilla en la cual se pueda elegir año, mes y dia...y busque los valores segun los datos proporcionados, o sea que si elijo en año 2006, me muestre en pantalla el promedio de enero de 2006 en su respectiva casilla, el promedio de febrero de 2006 en su respectiva casilla etc. y si lo cambio a 2007 haga lo mismo y asi con con "n" años mas

Esta tarea es sencilla si usamos tablas dinámicas y en esta nota mostraremos cómo hacerlo.
En una hoja de Excel ponemos los datos de tipo de cambio del peso chileno frente al euro y al dólar en los años 2006 y 2007. Los datos los descargamos gratuitamente del sitio Oanda. El nombre de la hoja será "BD" (base de datos)





Hemos puesto los datos del dólar (USD) y del euro (EUR) en dos columnas contiguas para evitar tener que duplicar las líneas con las fechas. Esto nos obligará a hacer una pequeña manipulación en nuestra tabla dinámica.

Una vez que hemos completado nuestra base de datos, armamos la tabla dinámica con el menú Datos—Informe de tablas y gráficos dinámicos



Arrastramos el campo Fechas al área de campos de filas y los campos USD y EUR al área de datos



Ahora empezamos a hacer nuestras manipulaciones en la tabla. Primero hacemos clic sobre "Datos" y lo arrastramos sobre "Total"



Como ven, ha desaparecido la columna Total, que no necesitamos. También eliminamos el total de las columnas abriendo el menú Opciones de Tabla y quitando la marca de las opciones Totales Generales de filas y columnas.

Ahora agrupamos las filas por mes y por año con el menú Agrupar de las tablas dinámicas



y eligiendo las opciones "meses" y "años"



En este momento nuestra tabla nos muestra la suma de las tasas de cambio de cada mes para moneda. Para cambiar la función a "promedio", abrimos el menú Configuración de campo y elegimos la función promedio



Hacemos lo mismo para el campo de USD. Ahora cambiamos el formato de los números y ya tenemos nuestra tabla con los promedios por año y por mes. Cinco minutos de trabajo y éste es el resultado



Si queremos agregar promedios anuales, abrimos el menú de configuración de campo para "Fecha"



y marcamos "Subtotales"



Todo lo que nos queda por hacer es definir el rango de la tabla en forma dinámica, como esta explicado en la nota del enlace. De esta manera podemos seguir agregando datos a nuestra base de datos, sin necesidad de redefinir el rango de la tabla dinámica.

Technorati Tags: MS Excel