Presupuesto de viaje con Excel

sábado, agosto 04, 2007

Desde el 7 al 29 de setiembre estaré de viaje con mis hijas. Pasaremos nuestras vacaciones en la Argentina donde visitaremos Puerto Madryn para hacer el avistaje de ballenas en la península de Valdés, Bariloche y San Martín de los Andes, las Cataratas del Iguazú y por supuesto Buenos Aires, mi ciudad natal.

Cuento todo esto por dos motivos. El primero es que durante esas tres semanas el blog estará inactivo. El segundo es para sugerir cómo se puede construir con Excel un modelo sencillo pero efectivo para programar el presupuesto de un viaje (o de cualquier otra actividad).
Los "ingredientes" de nuestro "plato" serán: tablas dinámicas, validación de datos y rangos dinámicos definidos con nombres y la función DESREF.
También usaremos un evento para validar fechas.

La tabla dinámica funciona como un generador de reportes que nos permitirá agrupar los gastos según los distintos conceptos que hayamos definido (tipo de gastos como vuelos, alojamiento, excursiones, etc.; o gastos por tramos).

Usaremos Validación de Datos para generar listas desplegables en los distintos campos de la lista que será la base de la tabla dinámica.

Aquí pueden descargar el archivo con el ejemplo del presupuesto de viajepresupuesto de viaje.

Empezamos por crear la lista, las base de datos que servirá a la tabla dinámica (en la hoja BD en nuestro ejemplo). Aquí definimos que campos queremos que contenga la tabla. En nuestro ejemplo definimos (empezando por la columna A): Tipo de Gasto, Fecha, Tramo, Detalle, Moneda, Suma, Peso, Dólar, Euro.

Agregamos las últimas cuatro columnas bajo la suposición que tendremos gastos en distintas moneda. En la columna Moneda pondremos la denominación de la moneda en la cual realizamos el gasto (peso, dólar o euro); en las últimas tres convertimos cada suma a su equivalente con fórmulas que mostraremos luego.

En esta hoja hemos agregado también un evento que abre un calendario cuando queremos introducir una fecha en alguna celda de la columna B. Esta técnica la hemos mostrado en la nota sobre validación de fechas en Excel.

Ahora creamos una hoja que llamamos "Parámetros" donde tenemos los valores de tipo de gastos que servirá a la lista desplegable de la validación de datos. Además tenemos un cuadro de cambio cruzado de monedas para las conversiones.



En esta hoja definimos los nombres:

cambio =parametros!$C$2:$F$5
db_range =DESREF(BD!$A$1;0;0;CONTARA(BD!$A:$A);CONTARA(BD!$1:$1))
monedaH=parametros!$C$2:$F$2
monedaV=parametros!$C$2:$C$5
Tipo_de_Gasto=DESREF(parametros!$A$3;0;0;CONTARA(parametros!$A:$A)-1;1)



Antes de crear la hoja con el reporte, introducimos algunos datos en la hoja BD:





Ahora ya podemos crear nuestro reporte, usando una tabla dinámica que ponemos en la hoja Reporte. Por ejemplo si queremos ver el presupuesto por tipo de gasto:



o por tramo



de acuerdo a los campos que arrastremos a la zona de campos de fila.


Technorati Tags:

22 comments:

Anónimo,  04 agosto, 2007 18:22  

muxas gracias...esto me servira muchisimo..pero ahora tengo un problema que necesito me ayudes a solucionar. soy un novato en excel y por ende no podria crear macros, la verdad es que acabo de descargar un manual de como trabajar en visual basic y lo estoy leyendo, pero necesito que por favor y con urgencia me ayudes con mi problema, creo que es algo sumamente simple, se trata de crear un numero de serie o correlativo, similar a lo que se hace con facturas o boletas. la idea es dejar un libro que al abrirlo pueda arrojarme un nº, por ejemplo 100, pero que al momento que yo quiera abrirlo nuevamente me asigne el nº 101, y asi constantemente. esto es para enumerar mis informes tecnicos que estoy realizando diariamente.
espero me puedas ayudar.
mi correo electronico es gino_cid@yahoo.es agradecido de antemano por tu gestion

Jorge L. Dunkelman 05 agosto, 2007 21:55  

Hola,
la respuesta va por mail.

Anónimo,  07 agosto, 2007 15:12  

Hola, estoy trabajando con power builder y necesito hacer funcionar un grafico de excel dentro de mi codigo.

Me gustaria saber, como puedo importar una sentencia sql a un grafico excel, para asi poder hacerlo visible, porque de momento solo me salen los datos que le meto de inicio, pero claro, necesito hacerlo dinamico.

El codigo en visual basic, supongo que sera muy parecido... puff ayuda!!

Muchas gracias

Ah!! mi correo: dont.call.me.funny.bunny@hotmail.com

Anónimo,  09 agosto, 2007 18:00  

Hola que tal queria consultarte como tengo que hacer para sumar los montos correspondientes a fechas especificas.

Si yo tengo por ejemplo 10-10-05 $1000, 20-10-05 $2000, 24-10-05 $1000, .... y despues viene noviembre 02-11-05 $2000, 03-11-05 $4000, ..... .
Para poder ver cuanto suma por mes como tendria que hacer.

Te agradesco mucho.

Mi mail es gastonclarens@hotmail.com

Jorge L. Dunkelman 09 agosto, 2007 21:45  

Hay varias formas de hacerlo. La más inmediata sería usando la función SUMAPRODUCTO. Supongamos por ejemplo, que en el rango A1:A100 están las fechas y en el rango B1:B100 los montos. Para sumar los montos del mes de enero usaríamos =SUMAPRODUCTO((MES(A1:A100)=1*(B1:B100)*1)
Puedes consultar mis nota sobre Contar condicional con más de un criterio usando SUMAPRODUCTO o Sumar y contar con varias condiciones en Excel:SUMAR.SI, CONTAR.SI y SUMAPRODUCTO

Anónimo,  09 agosto, 2007 23:58  

Hola Jorge. Gracias por tanta ayuda valiosa. Tengo un problema que no se como resolverlo. Cómo puedo convertir un listado de artículos que agrupa cantidades en uno (que puede ser en una hoja distinta) que repita cada artículo las veces que figuren en el primero?
Ej. Si tengo en una hoja un listado así:
4 Oficina
2 Entrada
3 Corredor

y quiero que se genere una que se vea así:
1 Oficina
1 Oficina
1 Oficina
1 Oficina
1 Entrada
1 Entrada
1 Corredor
1 Corredor
1 Corredor

Es para poder exportar este listado a un programa de etiquetación.
Te agradezco muchísimo de antemano.

Anónimo,  10 agosto, 2007 00:00  

Oops. Olvidé darte mis datos. Me llamo Ernesto y mi correo es ereinoso@cae.org.ec
Mi inquietud era la de generar el listado unitario de artículos.
Muchas gracias.

Jorge L. Dunkelman 11 agosto, 2007 10:22  

Tendrías que usar macros. Pero para que me haga una idea más precisa del problema, sería bueno que me mandes una copia del archivo.

Jorge L. Dunkelman 11 agosto, 2007 10:24  

Ernesto, mandame el amil a jorgedun@gmail.com

Juaran 29 agosto, 2007 09:25  

Buenas, ¿cómo podría validar usando más de una definición de nombre? es decir, ¿ podría poner en origen del criterio de validación algo tal que así =Tipo_de_Gasto Y Tipo_de_Ingreso ?
un saludo y muchas gracias.

Jorge L. Dunkelman 29 agosto, 2007 20:36  

Depende de lo que quieres hacer. Si lo que quieres es generar una lista desplegable, la respuesta es no.
Si quieres validar el valor en la celda de acuerdo a un criterio lógico, la respuesta es si. En esta caso tienes que tener dos nombres que contengan una fórmula que de un resultado lógico (VERDADERO o FALSO). Luego, de acuerdo a lo que quieras lograr, usarías la función Y o al funcion O para "unir" las dos condiciones.

Juaran 30 agosto, 2007 12:16  

ok! Quería generar una lista desplegable pero ya veo que no es posible. Muchas gracias

Irene 24 septiembre, 2007 22:16  

Hola me parece muy interesante lo que haceis y aplaudo vuestra iniciativa.
Ahora la pregunta:
tengo una hoja en la que quiero automatizar una tarea se trata de crear macro que permita de algun modo (tecla intro, combinacion de teclas o como sea) que se vayan actualizando el numero que se encuentra en la celda de uno en uno y de forma correlativa.
Gracias por anticipado.
Saludos
Peter

Anónimo,  27 septiembre, 2007 20:52  

Buenas Tardes, espero q tus vacaciones hayan sido excelentes. Queria consultarte has escrito algo sobre formulas tridimensionales? bueno y viene la 2da pregunta existen estas formulas? necesito usar un SUMAR.SI de un libro hacia otro sin necesidad de tener abierto el libro origen. Muchas Gracias por tu ayuda.

Atte MARCH

Jorge L. Dunkelman 04 octubre, 2007 21:52  

Tendrías que usar un tipo especial de macros que se llaman "Eventos". Posiblemente publique algo sobre el tema en breve.

Jorge L. Dunkelman 04 octubre, 2007 22:08  

Mola March, en principio Excel no acepta fórmulas tridimensionales. Prometo excribir una nota sobre el tema (no antes de terminar de responder a los 50 comentarios y otro número similar de mails que se han acumulado en mis vacaciones).

Anónimo,  11 octubre, 2007 20:40  

Buenas tardes lo saluda MARCH, muchas gracias por el dato, entonces esperare con ansias su publicacion, mientras voy a investigar en la red.
De ser necesario le dejo mi correo smaylodon@gmail.com

Saludos

Roger 02 febrero, 2008 17:32  

Tengo una consulta no se si es con validación de datos u otra alternativa puede ser visual basic, la consulta es la siguiente quiero que una celda (B5) que despliegue una lista tipo validación, pero donde pueda escoger más de una opción y se vea reflejado en la celda, validación sólo me permite seleccionar un valor de la lista desplegable pero yo necesito escoger 1 o más, y como digo que se vea reflejado en la celda, Gracias de antemano por tu ayuda a todas en tu blog, espero tu respuesta para que me digas si es posible o no.

Jorge L. Dunkelman 02 febrero, 2008 18:37  

Hola Roger
se puede programar un control para que permita seleccionar más de un valor simultáneamente, pero lo que se puede hacer es poner más de un valor en una celda.

Roger 02 febrero, 2008 19:34  

Hola, pero me podrías dar el comando o indicarme si ya lo hiciste antes por favor o algo de referencia, gracias.

Santandereano1970 28 abril, 2008 19:42  

Señor Jorge es usted un re...duro
Ojalà algùn dìa pueda concoerlo y estrecharle esa mano y personalmente decirle Muchas gracias..

mi correo es carlop5@hotmail.com

Mabel Gonzalez 23 agosto, 2011 03:30  

Muchas Gracias por ser tan generoso con sus conocimientos.. en el 2011.. me sigo beneficiando de sus publicaciones..
Saludos!
Gracias!

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP