Macro para cálculo de presupuestos

También suelo trabajar -a diario- con presupuestos y su comparación con el resultado real.
También suelo hacerlo en base a una tabla que me muestra cada concepto en filas y los valores para cada mes en columnas.
Sin embargo no utilizo macro alguna para cambiar, mes a mes los valores presupuestados y reales a comparar
(A veces me pregunto si cuando piden una "macro" saben de qué están hablando.
Un macro es un conjunto de instrucciones, generalmente escritas en Visual Basic for Aplications (VBA), que ejecuta una serie de instrucciones. De hecho es todo un Programa de computación.
Como tal, insume más recursos y mantenimiento que las funciones incluidas en MS Excel.
Muchas veces, una fórmula resuelve el problema arrojando el resultado automáticamente sin necesidad de ejecutar "macro" alguna)
Bien, este me parece que es tu caso.
Y tiene una solución simple e interesante. Sigue estos pasos:
1.- Selector de mes:
En base a los nombre de los meses que tienes en tu Hoja 2 armaremos un selector de mes sin necesidad de programar una macro.
Selecciona el rango de celdas donde están los nombres de los meses (supondré: B5:M5) y define un nombre de rango para ellos. Seleccionado el rango y eligiendo "Insertar" | "Nombre" | "Definir", ingresa un nombre para ese rango, como por ejemplo: "ListaMeses"
Ahora, en la hoja donde quieres hacer la selección (Hoja 3), elige una celda al lado de Período, por ejemplo, en B5 y clickea en "Datos" | "Validación...". En la ficha "Configuración", selecciona del menú descolgable bajo "Permitir" la opción "Lista". Más abajo, te permitirá indicar el rango donde está tu lista de opciones (el nombre de rango definido previamente. Esto es sumamente práctico porque tu lista está en otra hoja del libro). Ingresa en la casilla inferior:
=listameses
Asegúrate que esté tildada la casilla de "Celda con Lista desplegable" (suele estarlo normalmente).
Un par de solapas más te permitirán, si quieres, asignarle mensajes al usuario para que seleccione los datos o mensajes de error personalizados.
Click en Aceptar para activar esta función en esa celda.
OK, Ahora la celda B5 contendrá el nombre del mes cuya información deseas traer.
2.- Buscando los datos de ese mes
Si tus datos de ejecutado estuvieran en el rango: A5:N160, ingresa la siguiente fórmula en la primer celda -en la Hoja 3- donde deberá traer tal valor:
=BUSCARV($A6,'Hoja 1'!$A$5:$N$160,coincidir('Hoja 1'!$A$5:$M$5,$B$5,0),0)
[Considera si sueles usar comas o punto y coma para separar argumentos de las funciones. Yo usé ";"]
Asígnale el formato deseado y, luego, copia esta celda y pégala en las restantes de esta misma columna, una por cada cuenta o concepto a mostrar.
Si la función BUSCARV te fuera familiar, notarás que el primer argumento es el número de identificación del concepto o cuenta. El segundo argumento es el rango donde está tu tabla de datos de gastos reales.
El tercer dato es el número de columna de donde debe tomar los datos. Para ello la función COINCIDIR, busca el nombre del mes seleccionado en la celda B5 dentro de la fila de los encabezados de columnas. Así si el mes a mostrar fuese Enero, este estará en la segunda columna empezando desde la columna "A", por lo tanto devolverá: 2.
Esa misma fórmula, cambiándole la referencia de hoja (es decir de 'Hoja 1' a 'Hoja 2') traerá los datos presupuestados.
Luego procede como con la anterior.
---
Bien, Andrea, la ventaja de usar fórmulas en lugar de macros, es que estas se actualizan automáticamente, sin necesidad de que recuerdes que debes ejecutarlo cada vez que cambies de mes. De esta manera, simplemente eliges el mes que quieres ver y sus valores serán mostrados en las celdas correspondientes.
Espero que esto ayude a resolver tu problema. Si así fuera, agradeceré un comentario y que la finalices.
(Recuerda que mantener cierto número de respuestas pendientes impide que otros usuarios puedan consultarme)
En caso contrario, puedes preguntarme nuevamente
Aclarando qué entendí mal o qué faltó.

Hola Fernando ;-)
Te agradezco por el tiempo que te has tomado en responder mi consulta pero
todavía no resuelvo me sale de respuesta #N/A.
En la hoja 1=Ejec
b3:m65 son los montos b3 el es monto del codigo 0101 del mes de enero
y m65 es el monto para el codigo 0907 para el mes de Diciembre
(B2=Enero, c2=febrero, d2=marzo....m2=diciembre)
y de A3=0101 ....A65=0907.
La Hoja 2=presup es similar solo con gastos presupuestados
la fórmula que utilizo adaptando a la que me indicas es la siguiente
=BUSCARV(B5;Ejec!B3:M65;COINCIDIR($D$3;Ejec!$B$2:$M$2;0);0)
donde b5= en la hoja 3 es igual al codigo 0101 b67=0907
Ejec!B3:M65 rango en la hoja ejecutado,
El coincidir le cambie el orden del que me diste según la ayuda del excel donde d3 es la lista desplegable de los meses . ahora según lo que entiendo coincidir me devuelve el numero de la columna del mes que elegí
con eso ya se en que columna buscar ahora como se en que fila buscar, ya que b5 es la fila que debo buscar, pero nunca le digo busca esta fila en el rango A3: A65 de la hoja 1=Ejec, no se si me entiendes
ya que ya le di formato pero me sigue saliendo #N/A.
bye
Andrea

OK. Creo que estamos más cerca. Sólo que habrá que reescribir la fórmula que adaptaste para que funcione bien, pues veo que te tomaste algunas libertades para modificarla ;)
Conceptualmente, BUSCARV es una fórmula que devuelve un valor dentro de una tabla de datos * cuya primera columna será la clave de búsqueda *
Así si tus códigos (0101-0907) están en la columna "A" (por lo que mencionas) esta columna debe ser la primera del rango de búsqueda que le indicas como segundo argumento en la fórmula.
Asimismo, la función COINCIDIR -como ya notaste- será la encargada de indicar el tercer argumento de buscarv, es decir el número de columna de dónde deberá tomar los datos.
No entiendo por qué invertiste el orden de sus argumentos. El primer argumento de COINCIDIR es el rango de búsqueda. Nota que en mi ejemplo se inicia desde la Columna A, asumiendo que "Enero" está en la columna B. De esas manera cuando lo encuentre devolverá 2.
El segundo argumento es el texto a buscar, que en tu caso está dado por el selector de meses.
Es MUY IMPORTANTE que el nombre de los meses esté escrito de igual manera en todas estas celdas (tanto en "Ejec" como en la hoja del Presupuesto. Caso contrario COINCIDIR devolverá #N/A (no disponible) y por tanto toda la fórmula dará ese ese resultado.
Por lo tanto, Andrea, considerando estas explicaciones y los rangos que mencionas en tu pregunta, la fórmula debería quedar como sigue:
=BUSCARV($A3;Ejec!$A$3:$M$65;COINCIDIR(Ejec!$A$2:$M$2;$D$3;0);0)
$A3, como dijiste, es la celda donde está el primer código a buscar.
Nota que también agregué $ al rango de búsqueda, de lo contrario al copiar la fórmula a otras celdas este se modificará y no estaría buscando los datos en la misma base.
Si sigues estrictamente mis sugerencias, esta fórmula debería darte le resultado adecuado. Luego puedes copiarla al resto de las celdas donde necesitas el resto de los datos.
Un beso!
Fer

Hola Fer
Te agradezco tu respuesta me ayudaste bastante al final me quedo así
para el ejecutado
=BUSCARV($B5,Ejec!$A$3:$M$65,COINCIDIR($D$3,Ejec!$B$2:$M$2,0)+1,0)
y para el presupuestado
=BUSCARV($B5,Presup!$A$3:$M$65,COINCIDIR($D$3,Presup!$B$2:$M$2,0)+1,0)
Como te das cuenta le aumente 1 ya que el coincidir me devolvía una columna menos,
supongo que es por el rango, ya que devuelve la columna según el rango dado y no de toda la hoja, lo bueno es que ya salio y te agradezco tu ayuda
Pero el problema no acaba ahí sino, que según el mes que elija en esa misma hoja me debe mostrar los valores acumulados es decir si eligió febrero me muestra todos los demás que ya esta resuelto y al costado
El valor Acumulado del Ejecutado y el valor Acumulado del presupuesto
Si elegí Febrero me debe mostrar en el acumulado del ejecutado la
suma del (ejecutado de enero + ejecutado de febrero) cada uno para su respectivo
código, y así para los otros meses
me entiendes no fer
bye
Saludos
Andrea, ah! Y otro beso para ti ;-)

Tal como te decía si hubieras utilizado un rango desde la Columna A para la función coincidir, no hubiese sido necesario que agregues 1 a la función. De todos modos lo resolviste y eso es lo que cuenta.
Sigo sin entender porque haces la búsqueda desde la celda B5, cuando me dijiste que los códigos estaban en la columna A. ("... y de A3=0101 ... A65=0907...")
En fin, si funciona está todo bien.
Respecto a tu segunda consulta, te comento cuál es mi forma de resolver este problema común a ambos.
Lo primero que deberías hacer, si no lo tuvieras así, es asegurarte que los meses encabezados de cada columna en Ejec y Presup, sean realmente fechas. Es decir, dónde dice "Enero" deberías tener 01/01/2003. Desde luego, puedes darle formato de fecha ("mmmm") para que te muestre sólo el nombre de mes, si es que así te gusta más. De todos modos seguirá siendo una fecha válida para el objetivo de acumular meses.
Una vez, que hayas hecho esta corrección utiliza una fila libre encima de donde están los meses (o puede ser una muy por debajo del final de tu tabla) y coloca la siguiente fórmula en ella:
=SI(B2<='Hoja 3'!$D$3;"si";"no")
B2 es donde tienes la fecha para Enero y D3 es el selector de me que usaste en la hoja donde comparas ejecutado vs prespuestado.
Copia esta fórmula para el resto de los meses.
Así notarás que los meses que deben ser considerados en el acumulado, mostrarán "si" en la parte superior mientras que los que no debes considerar dirán "no".
Ahora utiliza una columna auxiliar en ambas hojas (Por ejemplo la columna "Z") y coluca esta fórmula allí:
=SUMAR.SI($B$1:$M$1;"si";B3:M3)
La serie de "si"-"no", supose, está en la fila 1. La fórmula suma horizontalmente los valores de la fila 3 que tengan en la fila 1 la palabra "si".
Luego copia y pega esta celda para el resto de los códigos.
Dado que esto te armará el acumulado siempre en la misma columna de cada una de las dos hojas, aquella donde debes mostrarlos puede tener una referencia directa a estas celdas. Por ejemplo=
=ejec!Z3
Es un truco, lo sé, pero a mi me resulta práctico. Ojalá que a vos también.
Otro beso!
Fer

Lo que paso fue que en la hoja Ejecutado es donde el código
0101... al 0907 va en el rango de A3:A65
Pero en la Hoja
Final donde junto todo el código esta en la columna B
en el rango B5:B67
Bueno respecto a la pregunta que te hice,
seguí los pasos que me diste pero el problema es que si selecciono
Febrero en La Hoja 3
Me
Aparece en Ejec
Si si no si no no no si no no no si
Según lo que me doy cuenta hace la comparación como si fueran texto
a pesar que le he dado tipo de formato fecha del tipo dd-mm-aa
en la hoja de Ejec, Presup y en la Lista de D3 (en la HOja 3)
A que se debe, puedes aconsejarme que hacer
Gracias
Fer
Andrea, otro beso más

Apostaría que aquellas columnas posteriores a Febrero que muestran "si" en la fórmula, no tienen una fecha real en la celda del encabezado. Asegúrate de colocarle -repito: en ambas hojas- un fecha real. Por ejemplo siempre el primero de cada mes : 1/4/2003
Si escribes "Abril" como texto y le das formato de fecha, seguirá mostrando "Abril" pero no será una fecha real que pueda ser comparada con aquella que seleccionaste en D3. Dar formato de fecha NO convierte un texto en fecha. De hecho, leerá cero allí que, desde luego, es menor que cualquier fecha y por eso pondrá "si".
Revisa esto en ambas hojas y obtendrás que buscas.
Siguen besos
Fer

Pero prefiero si me mandas el archivo a [email protected] y le hago las modificaciones que me pidas.
De cualquier modo, si no me lo envías en estos días, te contesto sobre esta pregunta en cómo hacerlo.