Presupuesto de viaje con Excel

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 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: MS Excel

Contar valores únicos en un rango de Excel

En ciertas situaciones necesitamos saber cuantos valores únicos hay en un rango. Supongamos una hoja Excel donde tenemos esta tabla:



Si queremos calcular las ventas promedio por agente, tenemos que saber primero cuántos agentes hay en nuestra lista. En nuestro ejemplo hay cuatro agentes, Pedro, Roberto, Juan Carlos y Alberto, en doce filas de la tabla. Pero en la vida real nos enfrentamos con tablas que tienen cientos o miles de filas y por lo tanto necesitamos una forma más práctica de contar.
Para contar cuantos valores únicos hay en una lista usamos esta fórmula matricial:

={SUMA(1/CONTAR.SI(rango;rango))}

donde rango es un nombre que define el rango de celdas donde queremos contar los valores únicos

En nuestro caso rango = A2:A13, nuestra fórmula será

={SUMA(1/CONTAR.SI($A$2:$A$13,$A$2:$A$13))}



Esta es una buena oportunidad para volver a explicar cómo funcionan las fórmulas matriciales.

En primer lugar recordemos que al introducir estas fórmulas en una celda, apretamos simultáneamente Ctrl+Mayúsculas(Shift)+Enter. Los corchetes aparecen al introducir la fórmula de esta manera, y no deben ser puestos por el usuario.

Para ver los pasos del cálculo de la fórmula matricial, hay que pulsar el botón "Ver Detalle".

En nuestra fórmula matricial, la función CONTAR.SI crea una matriz de resultados que es número de veces que cada nombre aparece, como vemos en la columna B donde hemos introducido esta formula (no matricial)
=CONTAR.SI($A$2:$A$13,A2)



En la columna C calculamos la inversa de los valores de la columna B, por ejemplo en C2 ponemos la fórmula =1/B2

Luego sumamos todos los valores de la columna C, lo que nos da el número de valores únicos en el rango.

Todo esto es hecho en una sola fórmula, usando la técnica de fórmulas matriciales. El archivo con el ejemplo se puede descargar aquí.

Technorati Tags: MS Excel

Agrupar controles botones de opción en hojas Excel.

En la entrada sobre Agregando controles en hojas de cálculo mostré como agregar controles, tal como botones de opción o barras de desplazamiento, en una hoja de Excel.

Últimamente he recibido varias consultas sobre como crear grupos de botones de opción.
Cuando agregamos varios botones de opción en una hoja, Excel acepta una sola celda vinculada para todos los controles. Es decir, Excel agrupa todos los botones de opción en un solo grupo, no importa dónde o como los ubiquemos. Inclusive si definimos para cada botón una celda vinculada distinta, veremos que en todos los controles aparece como celda vinculada la última que hemos definido.

La técnica para superar este inconveniente depende de qué tipo de control estemos usando. Excel permite agregar dos tipos de controles directamente en la hoja: los controles de la barra de Formularios y los del cuadro de controles (controles ActiveX).




Supongamos que queremos crear un formulario para definir nuevos clientes. En este formulario definimos el nombre del cliente, el tipo (mayorista o minorista) y el tipo de crédito (15, 30 o 45 días). Nuestro formulario se verá así


Si usamos los controles de la barra de formularios, el truco consiste en poner cada uno de los grupos de botones de opción dentro de controles "cuadro de grupo" distintos. Para crear el formulario seguimos estos pasos:

1 – Ponemos un fondo gris al rango B2:F20
2 – Seleccionamos la celda C4 y ponemos "Nombre"
3 – Combinamos y centramos el rango D4:E4, y le quitamos el fondo gris
4 – Abrimos la barra de formularios y arrastramos dos cuadros de grupo



5 - Dentro de uno de los cuadro de grupo ponemos los botones de tipo de cliente. En el otro ponemos los botones de tipo de crédito. Cambiamos los encabezamientos directamente en el control.
6 – Los botones de tipo de cliente están ligados a la celda H3; los de tipo de crédito a la celda H4.
7 – Agregamos un control "botón" que nos servirá para activar una macro que agregue el nuevo cliente a nuestra base de datos (la hoja "Clientes")

En la celda I3 ponemos la fórmula =ELEGIR(H3;"Mayorista";"Minorista"); en la celda I4 la fórmula =ELEGIR(H4;"15 días";"30 días";"45 días")

Si usamos los controles ActiveX de la barra Cuadro de Controles, el truco consiste en cambiar la definición del Groupname en la propiedades del botón y darle a los botones del grupo el mismo Groupname.



En nuestro ejemplo los botones de tipo de cliente tienen como Groupname "Tipo" y los del tipo de crédito "Crédito".
Otro punto importante es que a cada control le definimos una celda vinculada distinta, a diferencia de los controles de la barra de formularios.

Sobre cómo definir los distintos controles, pueden consultar mi nota mencionada al principio.

En general es más fácil trabajar con los controles de la barra de formularios que con los controles ActiveX de la barra de cuadro de controles. La contrapartida es que los controles ActiveX tienen más posibilidades y son más flexibles a nuestras necesidades.

La hoja con los ejemplos y las macros se pueden descargar aquí.