Calculo de un valor, dependiente de otros dos y un coeficiente.
Hola que tal.
Como me indicas, te hago la pregunta por aquí, para que puedas tenerla a mano.
Como te comenté, tengo una columna E en la que se debe calcular una operación de las columnas BB (antes era la G) y S, operando a su vez con el coeficiente que resulta de aplicar los ámbitos máximos (reflejados en esta tabla) de esos propios valores. El valor máximo del precio (columna BB) correspondería a la variable X, mientras que el valor máximo del peso (columna S), sería el valor Y.
También está en: http://www.subirimagenes.net/i/130819014757155187.jpg
Me explico: Para la celda E2, la operación sería 5 * 1 * 1.31, que sería el coeficiente que le correspondería de la tabla de abajo por tener el peso de hasta 1 kg. Y un precio de 5€ (<50€). Para la celda E3, el valor sería de 99.88 * 5 * 1.38, y para la celda E19 sería
de 505.33 * 3 * 1.11.
Espero haberte aclarado suficientemente el problema, y que puedas ayudarme otra vez más.
La tabla de los valores X e Y sería ésta:
También está en: http://www.subirimagenes.net/i/130819014757685129.jpg
Los ficheros te los mando por e-mail.
Gracias otra vez, por todo lo que me estás ayudando.
Saludos, Santi.
1 respuesta
Ayer tenía hecho el método con macros. Es perfectamente funcional aunque hoy he hecho otro que no usa macros.
En el método con macros es así:
En ThisWorkbook de VisualBasic introducimos este código
Private Sub Workbook_Open()
Gx = Array(0, 50, 100, 200, 500, 1000, 2000)
Gy = Array(0, 1, 2, 5, 10, 15, 20, 30)
Tabla = Array(1.305, 1.215, 1.155, 1.125, 1.110, 1.080, 1.065, _
1.335, 1.230, 1.165, 1.125, 1.110, 1.080, 1.065, _
1.375, 1.250, 1.175, 1.130, 1.110, 1.080, 1.065, _
1.410, 1.270, 1.185, 1.135, 1.115, 1.080, 1.065, _
1.450, 1.290, 1.195, 1.140, 1.115, 1.085, 1.065, _
1.475, 1.300, 1.200, 1.140, 1.115, 1.085, 1.065, _
1.495, 1.310, 1.205, 1.140, 1.115, 1.085, 1.065, _
1.565, 1.345, 1.220, 1.150, 1.120, 1.085, 1.065)
End SubSe introduce aquí para que la asígnación que es larga se haga solo al abrir el libro, no cada vez que se llame a la función
Y en un módulo nuevo este otro código
Const Ngx = 7, Ngy = 8
Public Gx(), Gy(), Tabla()
Function PrecioFinal(Precio, Peso As Double) As Double
Dim ix, iy As Integer
If Precio < 0 Or Peso < 0 Then
MsgBox ("No puede haber precio o peso negativo")
PrecioFinal = -99999999
Exit Function
End If
For ix = 1 To Ngx - 1
If Precio <= Gx(ix) Then Exit For
Next
For iy = 1 To Ngy - 1
If Peso <= Gy(iy) Then Exit For
Next
PrecioFinal = Precio * Peso * Tabla((iy - 1) * Ngx + ix - 1)Puede parecer complicada la última operación. Si Tabla hubiera sido un array de dos dimensiones sería
PrecioFinal = Precio * Peso * Tabla(iy-1, ix-1)
Pero entonces no sé si se podría hacer (y creo que no) la asignación inicial de todos los valores con la función Array() en una sola línea (aunque larga).
Cuando modificas el valor de la columna S o BB se recalcula la fórmula en E automáticamente. Eso no sucede si modificas los datos del array Tabla en VisualBasic. Y he visto que hay muchos problemas para actúalizar la columna E. Hay que modificar el array, guardar el fichero, volver a abrirlo, eliminar las fórmulas de la columna E y volver a escribirlas, demasiadas cosas. Luego voy a desechar este método, olvídalo, lo que pasa es que después de lo que me costó programarlo me resisto a no citarlo.
---------------------------------------------------
Este es el método bueno
Y ya que es conveniente que los coeficientes estén en una hoja u hoja de un libro aparte voy a hacer el libro sin macros.
Entonces he hecho que los coeficientes estén en un libro aparte llamado
"Coeficientes de tramos.xlsx"
Así puedes usar ese libro de coeficientes con distintos libros sin tener que meterlo en una hoja de cada libro.
Dentro del libro estará en la hoja llamada "Hoja1". Y lo que si he hecho es modificarla para que funcionen bien las fórmulas que usaré. He puesto un 0 en la esquina superior izquierda, un número muy grande como final de los tramos y un número algo superior (una millonésima) a los números que tenías como separadores, porque sino la función COINCIDIR hacía que esos separadores fueran al tramo siguiente y tenían que quedarse en el anterior. Seguramente se podría hacer sin ese artilugio, pero la fórmula sería mucho más complicada.
Y es resumen. La fórmula que calcula el precio final es esta:
=S2*BB2*INDICE('[Coeficientes de tramos.xlsx]Hoja1'!$B$2:$H$9; COINCIDIR(S2;'[Coeficientes de tramos.xlsx]Hoja1'!$A$1:$A$9;1); COINCIDIR(BB2;'[Coeficientes de tramos.xlsx]Hoja1'!$A$1:$H$1;1))
Es todo una línea, la copias en la barra de fórmulas de E2 y luego copias la celda E2 y la pegas en el resto de la columnna E.
No te mando el fichero porque automáticamente se pone mi directorio de usuario delante del nombre del fichero.
---------------------------------------------------------
Y este me parece que es el mejor
Y de este si te voy a mandar el libro. Consiste en poner los coeficientes en una hoja del libro, la llamaré HojaCoefs.
La fórmula será:
=S2*BB2*INDICE(HojaCoefs!$B$2:$H$9; COINCIDIR(S2;HojaCoefs!$A$1:$A$9;1); COINCIDIR(BB2;HojaCoefs!$A$1:$H$1;1))
Es todo una línea que debes poner en E2 y luego copiar esa celda y pegarla en la columna.
Bueno, pues a la tercera va la vencida. Ahora mismo te mando el libro.
