Uso excel o VBA ?

Gordon · el 6 de noviembre

Hola a todos.

Hace tiempo que me registré en este foro, y desde entonces he estado buscando y leyendo
en busca de solución a mis dudas, pero la verdad es que no me he aclarado mucho,
por eso me he decidido a publicar este mensaje, a ver si alguien puede aclararme alguna de las dudas que tengo.

Mi problema es que me gustaría automatizar una fórmula que usa valores actuales con valores obtenidos de los 52 valores anteriores, de manera que sólo tenga que introducir
los nuevos valores semanales y el "programa" me devuelva los datos ya calculados.

Mi duda principal es que no se cómo sería más fácil de implementar, si usando un libro de excel con alguna macro,
o usar VBA.

Les explicaré en detalle la tarea que quiero automatizar y así pueden hacerse una idea.

Una vez por semana necesito calcular unos valores para los que utilizo un libro de
excel con varias hojas ( una para cada producto que tengo que calcular) en total 8.

El libro está organizado de la siguiente manera:

Hoja 1-> Muestra los resultados finales para los 8 productos.

   Columna A contine la Fecha a la que corresponden los valores
        Columnas [B->I] contiene los valores finales de los 8 productos calculados, correspondientes a la celda L4 de las hojas de producto, por medio
   de la orden "+PRODUCTOX!L4"

Hojas 2 a 9

Cada una está dedicada a cada producto.

En ella estan todos los datos ( valores actuales introducidos por mí y los últimos 52 valores anteriores)
y las celdas con las fórmulas para calcular el valor final del producto.

   Los 52 valores estan organizados en columna descendiente de más actuál a más antiguo. ( Celdas B11-B62)
   Siendo la fila 10 dedicada al valor que yo introduzco en la semana en curso.
   Las Celdas G4 hasta K4 estan dedicadas a las operaciones intermedias.
   La celda L4 es el resultado final.

Para cada producto ( cada nueva semana ) realizo las siguientes acciones en la hoja correspondiente:

1.- Desplazo la fila 10 una posición hacia abajo para introducir los datos de la nueva semana.
2.- Elimino la recien creada fila 63.
3.- Introduzco los datos nuevos de la semana en la fila 10.
4.- Debido a que he tenido que desplazar una fila hacia abajo, tengo que corregir los rangos de datos de las celdas G4-L4 para que
me calcule la fómula correctamente. Pongo un ejemplo:

           La celda G4 me devuelve el MIN(F11:F62) pero después de hacer el desplazamiento de fila dicho rango cambia a MIN(F12:F63)
           con lo que me veo obligado a corregir el rango para poder hacer el cálculo.
           Así con cada celda de calculo parcial de cada hoja.

Una vez que he corregido todos los rangos por incluir la nueva fila, es cuando finalmente obtengo los resultados que necesito.

Como ya dije al principio, mi intención es automatizar tanto como me sea posible, e idealmente limitar mi acción a la introducción de los datos para el cálculo semanal con un selector de producto
que reconozca el producto al que pertenece la entrada y lo mueva a la hoja correspondiente, realice los cálculos y muestre los resultados en la Hoja 1.

He visto posts donde hablan de macros pero no se si con una macro sería posible hacer todo esto o tendría que hacer varias, o si es mejor hacerlo en VBA directamente.
Esa es mi duda principal, una vez que alguien me aconseje, yo ya me encargaría de buscar la forma de hacer todo esto. Mi intención no es que alguien me lo haga y
me lo sirva en bandeja, al contrario, me gustaría aprender algo por el camino.

Espero que alguien pueda orientarme un poco.

Un saludo a todos.

GabrielRaigosa · el 7 de noviembre

A lo mejor, se entenderia mejor el problema si pasa un archivo, modelo reducido con algo de los datos, esas 8 hojas, si las estructuras son identicas, la union de ellas es muy facil con excel 2010/2013/2016 usando power query.

Tal vez reduciendo 8 hojas a 1 sola base de datos se comience a simplificar el problema.

Pero sin datos, me perece a mi, que es perder el tiempo proponer soluciones, ya que sucede con mucha frecuencia, que la situación real difiere de lo que cada quien pueda imaginar.

Saludos,

Gabriel

Gordon · el 7 de noviembre

Buenos dias Gabriel, muchas gracias por responder.

Comprendo lo que me dice.

Adjunto a este mensaje le envío el archivo excel del que hablo.

Lo he realizado en Excel2016.

Comentarle que los valores que yo introduzco semanalmente sólo son tres:

1.- La fecha en formato mm/dd/aaaa

2.- Variable Long de la columna C y

3.- Variable Short de la columna D

El resultado final es COT VALUE correspondiente a la celda L4 de cada hoja de producto y son mostrados en la hoja COT RESULTS por razones de presentación.

Espero que este todo más claro ahora, si tiene usted cualquier otra pregunta, con gusto la responderé.

Un saludo y muchas gracias por su ayuda.

Gordon.

COT INDEX.xlsx

GabrielRaigosa · el 7 de noviembre

Solo una idea para empezar,

El desplazamiento de los rangos, al insertar una fila nueva, la fila 10 se puede evitar, si revisa el adjunto, vera que con la funcion indirecto se pueden dejar los rangos "fijos", y ya no seria necesario cambiar rangos, ni eliminar la ultima fila, así seria posible dejar los datos historicos, si es que en algun momento se quieren revisar.

Usted es el que tiene la información y sabe que el lo que ira necitando.

Situaciones como estas siempre de dejan inquitudes pues no siempre tenemos la información completa de los procesos que esta realizando el usuario, incluso uno de ellos es la obtención de esas tablas, ¿son acaso alguna consulta web?, ¿deben ser digitados manualmente?

En el adjunto le dejo un ejemplo donde pude ver como usando la funcion indirecto puede eviat el desplazamiento de los rangos, solo inserta la fila 10, coloca sus datos en cada hoja y revisa que todo quede tal cual como lo necesita en la hoja "uno", no es necesario eliminar la ultima fila en ninguna ya que los rangos siempre seran los mismos.

COT INDEX mod 01.xlsx

Gordon · el 7 de noviembre

Hola Gabriel,

he visto la modificación del archivo y funciona perfectamente, muchisimas gracias.

Con gusto yo le explico los detalles si eso ayuda.

Los datos de las tablas corresponden al número de operaciones de compra y venta de los valores de la bolsa. Los datos se actualizan cada semana, y se publican en la forma de un archivo excel llamado annuals.xls y de la totalidad de los datos publicados yo extraigo dos ( para cada producto ) que son los necesarios para usar la fórmula que necesito con cada uno de los 8 productos correspondientes a las hojas de mi archivo. Dichos datos son los correspondientes a las columnas L y M del archivo Muestra annuals.xls

Una vez que tengo los dos datos los introduzco manualmente en mi hoja excel y realizo el proceso que expliqué sobre el desplazamiento de la linea 10 para la introducción de los nuevos datos y mi anterior problema de tener que voolver a corregir los rangos de los calculos parciales, asunto ya solucionado gracias a su amabilidad.

La fórmula que aplico es la siguiente

COT VALUE =100 x ( ( CurrentNet- MinimumNet) / ( MaximumNet - MinimunNet) )

Donde :

Columna CurrentNet = Columna C- Columna D de mi archivo.

MinimumNet = El valor más pequeño de la columna CurrentNet de los anteriores 52 ( que son las tablas de mi archivo correspondientes a las filas 11 hasta 62 de cada hoja.

Maximim Net = El valor más alto de los anteriores 52

Por aclarar con un ejemplo en caso de que mi explicación no este clara:

Esta semana en annuals.xls yo tomaria los valores correspondientes a la fila que envío en el archivo Muestra annuals.xlsx

Una vez los tengo voy a mi archivo, la hoja correspondiente a USDX y desplazo el valor de la semana pasada ( todavía en la fila 10 ) hacia abajo, despejando la línea para introducir el valor de L en la columna C de mi archivo y el valor de la columna M en la columna D.

Entonces calculo el valor de CurrentNet. Y sobre los anteriores 52 CurrentNet hago la busqueda del Maximo y Minimo para completar la fórmula.

Y eso lo hago para las otras 7 hojas.

Iba a subir el archivo annuals.xls pero su tamaño es de 23Mb y sobrepasa con creces el tamaño permitido.

Espero haber aclarado las dudas y pueda hacerse ahora una idea clara de lo que intento hacer.

Saludos.

Muestra annuals.xlsx