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

jueves, marzo 13, 2008

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:

13 comments:

Jim McLean 14 marzo, 2008 14:26  

Jorge, he de decir que yo vivo mucho mejor desde que hace dos años aprendí contigo a hacer tablas dinámicas. Yo las uso para casi todo: balances, informes financieros, reports de ventas, etc. Sigo teniendo algún problema con los acumulados a una fecha concreta (yeartodate) si utilizo datos que pertenecen a fechas plurianuales. Si utilizo agrupar filas la suma aparece acumulada al año y no a la fecha que quiero comparar. Acabo siempre creando una tabla auxiliar. ¿Habría alguna manera de solventar este problema?. Gracias Jim

Jorge L. Dunkelman 15 marzo, 2008 08:39  

Hola Jim,
no entiendo del todo la consulta. Si quieres comparar un mismo período entre dos años tendrías que crear en tu base de datos una columna auxiliar con el año (por ejemplo, usando la función YEAR()) y otra con el mes (con MONTH())
Luego puedes construir la tabla dinámica incluendo estos dos campos y creando la comparación con campos calculados.

danielmadv 16 marzo, 2008 16:30  

Una variante interesante de su tabla dinámica podría ser poner el campo Años en el apartado de "Coloque campos de página aquí". De esta forma podremos elegir de que año queremos ver los promedios.

Hernan 19 marzo, 2008 17:33  

Hola Jorge.Realmente muy buena tu pagina me sirvo muchisimo hasta ahora, pero me surgio un problema que no encuentro solucion y es crear 2 listas desplegables doblemente ligadas. te doy un ejemplo en una columna el codigo con lista desplegable el cual al seleccionar un item de la lista me arroja la descripcion en otra columna, pero si yo de la columna descripcion quiero seleccionar un item para que me aparezca el codigo en la primera columna como deberia de hacer? muchas gracias..se que es un poco engorroso espero que entiendas

Hernan 19 marzo, 2008 17:36  

Hola Jorge.Realmente muy buena tu pagina me sirvo muchisimo hasta ahora, pero me surgio un problema que no encuentro solucion y es crear 2 listas desplegables doblemente ligadas. te doy un ejemplo en una columna el codigo con lista desplegable el cual al seleccionar un item de la lista me arroja la descripcion en otra columna, pero si yo de la columna descripcion quiero seleccionar un item para que me aparezca el codigo en la primera columna como deberia de hacer? muchas gracias..se que es un poco engorroso espero que entiendas

Jorge L. Dunkelman 19 marzo, 2008 20:45  

Hola Hernan
no logro entender del todo el planteo. Te sugiero que me mandes el archivo con el problema y una descripción.

Hernan 20 marzo, 2008 16:24  

Hola Jorge, te adjunto la dir imagen para que puedas ver mas en detalle lo que quiero hacer:

http://i8.photobucket.com/albums/a24/CiscoFran/
hernanexcel.jpg

En la columna B, tengo un desplegable con codigos que cuando selecciono uno, me hace referencia a una descripcion en la columna D.

Lo que quiero hacer, es tambien poder hacer referencia desde la descripcion al codigo. Es decir poder tener un desplegable en columna D donde una vez que yo eliga la descripcion me aparezca el codigo en la columna B.

La idea es poder hacer un pedido y que uno pueda buscar el producto tanto por codigo como por descripcion. Si necesitas el archivo por favor pasame tu mail y te lo mando.
Desde ya muchisimas gracias por todo.
(NOTA: utilice la funcion =buscarv)

Jorge L. Dunkelman 20 marzo, 2008 22:32  

Hernán

mandame el archivo, por favor. El correo electrónico figura debajo de la foto: jorgedun@gmail.com

Anónimo,  23 marzo, 2008 10:28  

Jorge, estoy sorprendido de lo que pude lograr con excel y todo gracias a tu Blog. Tengo la siguiente consulta:
Arme una hola de calculo que contiene las siguientes columnas(fecha, # de proyecto, horas dedicadas al proyecto, y etapa del proyecto). Hice una tabla dinamica que calcula el total de horas segun el # de proyecto. Pero no calcula las horas dedicadas al proyecto segun la etapa (Municipal, cambios, diseño, etc). Como lograr que las distintas etapas pasen a ser encabezado de la T.D. y que figuren las horas segun el proyecto.
Gracias.

Jorge L. Dunkelman 24 marzo, 2008 22:43  

De aceurdo a lo que entiendo de tu descripción, no hay ningún motivo para que no calcule las horas según etapa. Te sugiero que me mandes el archivo para que vea dónde pueda estar el problema.

Diego 26 marzo, 2008 17:41  

Estimado Jorge,

Soy nuevo con esto de los blogs, por eso es que te mande esto mismo a tu correo de gmail. Pero ahora encontre como hacerlo por aca. Si ya viste el mail perdon, pero te molesto porque realmente necesito solucionar esto.
Te copio lo del mail.... y gracias!!

Antes que nada te comento que estoy asombrado y hasta emocionado con tu blog… la verdad no conocía de su existencia…

Soy amante de Excel…. Lo uso muchísimo y me encanta! Y me vuelvo loco queriendo resolver cosas raras… pero muchas veces me enfrento a situaciones que no puedo resolver…

Y ahí tengo que solicitar ayuda a algún experto….

Hasta ahora me movía con una pagina que se llama soloexpertos.com que me pareció muy buena….

Pero esta vez, aun nadie me pudo ayudar….
Por eso es que no detuve mi búsqueda y acá estoy, ante otro experto!
Estuve navegando por el blog, pero no supe donde colgar mi pregunta…. Por eso estoy comunicándome por acá….

Te cuento cual es mi problema:
Tengo una base de datos donde se completan las recorridas que hacen los supervisores con el nombre, fecha, hora, que servicio visitaron y a que empleado o empleados visitaron en dicha oportunidad.
Lo que yo necesito calcular es cuantas veces en el mes fueron a cada servicio cada uno de los supervisores sin tener en cuenta cuantos empleados visitaron cada día.


Superv Dia Hora Serv. Empleado
Leonardo 13/03/08 21:40 A DELGADO, Renzo Iván
Leonardo 13/03/08 22:55 C CABRERA, Miguel Alejandro
Leonardo 13/03/08 23:00 C EYL, Sebastián David
Leonardo 14/03/08 22:40 E PETRACCI, Jorge Adalberto
Leonardo 14/03/08 22:50 E GOMEZ, Juan
Leonardo 15/03/08 23:10 C BENITES, Guillermo Enrique
Leonardo 15/03/08 23:15 C FARRIOL, Matías Martín José
Leonardo 16/03/08 23:45 D MARTINEZ, Luis Alberto
Roberto 17/03/08 12:40 E PETRACCI, Jorge Adalberto
Roberto 17/03/08 12:50 E GOMEZ, Juan

En el ejemplo de arriba, lo que necesito es que cuando pregunte por el servicio "C" la formula me responda que fue visitada por el supervisor Leonardo en 2 oportunidades (1 vez el dia 13/3 y la otra el 15/3) y en el caso del servicio "E" fue visitado 1 vez por Leonardo y otra vez por Roberto.
Este tema lo encaré con la utilización de una tabla dinámica. Pero me clavé con este tema ya que la tabla no detecta que un supervisor fue a un servicio una vez en un día aunque vea a dos empleados, por ende, me cuenta 2 cuando querría que me cuente solo 1.


Espero puedas ayudarme!!!
Muchas gracias!!!! Y felicitaciones de nuevo!!!!


Diego,

Jorge L. Dunkelman 26 marzo, 2008 22:30  

Hola Diego

la solución que se me ocurre es usar Filtro Avanzado, con la opción Registros Únicos. La explicación y la descripción de la técnica exceden el marco de este comentario, por lo que te mandaré las explicaciones por mail.
No he recibido ningún mail tuya en mi cuenta de Gmail, por lo que te pido me mandes tu dirección de manera que pueda identificar que se trata de ti.

Diego 26 marzo, 2008 22:39  

Jorge, ante todo muchas gracias por tu respuesta!
Te cuento que te lo mande a jorgedun@gmail.com desde mi casilla diegocassullo@yahoo.com.ar
Espero ansioso tu respuesta!
Saludos
Diego,

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP