Interpolación de valores
Muy buenas.
Espero saber explicarme bien y tener una solución a mi problema. El caso es que tengo una serie de valores en excel, en concreto 28, colocados en dos filas de 14. Cada valor de la fila inferior digamos que hace referencia a su inmediato superior (es decir como una tabla cualquiera que aparece en los libros de texto). Por otro lado tengo otro valor (valor2) independiente de los anteriores, que estará dentro del rango de valores de la fila superior, pero puede que no tenga su correspondencia con la fila inferior, por lo que me gustaría poder interpolar el valor2 dentro de los valores colocados en las dos filas. Lo he intentado a la "vieja usanza" y me sale una fórmula demasiado larga para saber si tengo todos los datos correctos. Espero haberme explicado bien y gracias por adelantado.
Espero saber explicarme bien y tener una solución a mi problema. El caso es que tengo una serie de valores en excel, en concreto 28, colocados en dos filas de 14. Cada valor de la fila inferior digamos que hace referencia a su inmediato superior (es decir como una tabla cualquiera que aparece en los libros de texto). Por otro lado tengo otro valor (valor2) independiente de los anteriores, que estará dentro del rango de valores de la fila superior, pero puede que no tenga su correspondencia con la fila inferior, por lo que me gustaría poder interpolar el valor2 dentro de los valores colocados en las dos filas. Lo he intentado a la "vieja usanza" y me sale una fórmula demasiado larga para saber si tengo todos los datos correctos. Espero haberme explicado bien y gracias por adelantado.
1 respuesta
Respuesta de Cadipas spain
1
La verdad es que con esa explicación, no lo tengo muy claro y quisiera ayudarte.
Si te parece dime tu correo y te envío el mio para intercambio de ficheros. Si me envías un fichero de ejemplo, lo puedo ver más claro y podemos buscar una solución.
Si te parece dime tu correo y te envío el mio para intercambio de ficheros. Si me envías un fichero de ejemplo, lo puedo ver más claro y podemos buscar una solución.
Hola experto.
Te dejo un ejemplo de la tabla con los datos:
Pm/Py 0 0.5 1.0 1.5 2.0 2.5 3.0 3.5 4.0 4.5 5.0 5.5 6.0 >6.5
Pr/Py 0 0.09 0.18 0.25 0.32 0.38 0.43 0.47 0.51 0.53 0.54 0.56 0.56 0.57
(Para un valor Pm/Py >= 6.5, su valor correspondiente de Pr/Py será siempre 0.57)
Estos datos son fijos. Es una tabla de referencia. Para cada valor de Pm/Py esta su correspondiente valor en Pr/Py.
En otro apartado aparte se calcula un valor de Pm/Py, que puede ser cualquier valor y que se tiene que comparar con los valores de la tabla anterior, y el problema esta en si sale un valor que no tenga correspondencia con la fila inferior. Por ejemplo, si el resultado de Pm/Py obtenido fuera 0.57, se tendría que interpolar entre 0.5 (que le corresponde el valor 0.09) y 1.0 (que le corresponde el valor 0.18), obteniendo como resultado un valor entre 0.09 y 0.18 como es lógico.
La fórmula para interpolar no es problema, ya la tengo, aunque si me pudieras decir alguna, no estaría de más, ya que seguramente sea mejor que la mía.
Mi problema es que no se cómo relacionar esa fórmula para cada valor de la tabla. Es decir, cómo hacer que habiendo calculado el valor Pm/Py, me haga la interpolación con los valores que le correspondan de la tabla, obteniendo así el valor Pr/Py para cualquier valor de Pm/Py calculado.
Espero haberme explicado bien, y que ahora entiendas mejor lo que pretendo conseguir.
Un saludo.
Te dejo un ejemplo de la tabla con los datos:
Pm/Py 0 0.5 1.0 1.5 2.0 2.5 3.0 3.5 4.0 4.5 5.0 5.5 6.0 >6.5
Pr/Py 0 0.09 0.18 0.25 0.32 0.38 0.43 0.47 0.51 0.53 0.54 0.56 0.56 0.57
(Para un valor Pm/Py >= 6.5, su valor correspondiente de Pr/Py será siempre 0.57)
Estos datos son fijos. Es una tabla de referencia. Para cada valor de Pm/Py esta su correspondiente valor en Pr/Py.
En otro apartado aparte se calcula un valor de Pm/Py, que puede ser cualquier valor y que se tiene que comparar con los valores de la tabla anterior, y el problema esta en si sale un valor que no tenga correspondencia con la fila inferior. Por ejemplo, si el resultado de Pm/Py obtenido fuera 0.57, se tendría que interpolar entre 0.5 (que le corresponde el valor 0.09) y 1.0 (que le corresponde el valor 0.18), obteniendo como resultado un valor entre 0.09 y 0.18 como es lógico.
La fórmula para interpolar no es problema, ya la tengo, aunque si me pudieras decir alguna, no estaría de más, ya que seguramente sea mejor que la mía.
Mi problema es que no se cómo relacionar esa fórmula para cada valor de la tabla. Es decir, cómo hacer que habiendo calculado el valor Pm/Py, me haga la interpolación con los valores que le correspondan de la tabla, obteniendo así el valor Pr/Py para cualquier valor de Pm/Py calculado.
Espero haberme explicado bien, y que ahora entiendas mejor lo que pretendo conseguir.
Un saludo.
Como yo no domino el tema de interpolar y para que no haga cosas raras, dime que entiendes tú por interpolar y pásame la fórmula que quieres que yo aplique, de tal forma que si yo te tengo que dar el valor próximo inferior y lo opero con esa fórmula, me daría el valor correspondiente. ¿Podría ser algo así?
Otro tema sería ¿Qué versión utilizas de excel? Yo utilizo 2003 0 2010, con cual quieres que lo haga.
Puedes probar con esta fórmula: yo he puesto en A1 el valor de Pm/Py y en la A2 busco el valor que tú quieres, la tabla la he colocado en B1:O2 = el resultado que me da el el valor más cercano inferior, por ejemplo se le pingo a buscar en A1
3 me localiza 0.43,
3.2 me localiza 0.43
3.5 me localiza 0.47
Etc...
La fórmula para lolocar en A2 es:
=BUSCARH(A1;B1:O2;2;VERDADERO)
Otro tema sería ¿Qué versión utilizas de excel? Yo utilizo 2003 0 2010, con cual quieres que lo haga.
Puedes probar con esta fórmula: yo he puesto en A1 el valor de Pm/Py y en la A2 busco el valor que tú quieres, la tabla la he colocado en B1:O2 = el resultado que me da el el valor más cercano inferior, por ejemplo se le pingo a buscar en A1
3 me localiza 0.43,
3.2 me localiza 0.43
3.5 me localiza 0.47
Etc...
La fórmula para lolocar en A2 es:
=BUSCARH(A1;B1:O2;2;VERDADERO)
Muy buenas otra vez y gracias por tu paciencia.
En primer lugar decirte que yo uso el Excel 2003.
En segundo lugar, la definición de interpolar que yo pretendo conseguir es la siguiente:
Pm/Py 0 0.5 1.0 1.5 2.0 2.5 3.0 3.5 4.0 4.5 5.0 5.5 6.0 >6.5
Pr/Py 0 0.09 0.18 0.25 0.32 0.38 0.43 0.47 0.51 0.53 0.54 0.56 0.56 0.57
Si por ejemplo yo tengo un valor de Pm/Py=2.3, (Valor entre 2.0 y 2.5) tengo que conseguir el valor que le corresponda en la fila Pr/Py (que estará entre 0.32 y 0.38). Para conseguir este valor, se aplica (o por lo menos yo aprendí así) la fórmula matemática siguiente (para este caso):
(2.5-2.0)/(0.38-0.32)=(2.5-2.3)/(0.38-X) Siendo X el valor que se quiere calcular.
Despejando la ecuación Nos quedaria:
X=0.32-((2.5-2.3)*(0.38-0.32))/(2.5-2.0)); (Esta es la formula que yo aplico)
Esta fórmula se aplica a la pareja de valores de (2.0 y 2.5), ya que el valor que nosotros tenemos es 2.3; Ahora habría que aplicar esta fórmula para el resto de parejas de valores de la tabla, es decir: (0 y 0.5), (0.5 y 1.0), (1.0 y1.5) ... hasta (6.0 y 6.5), comprobando a su vez que el valor que hemos calculado (2.3 en nuestro caso), entre que valores está, es decir, que excel sepa que 2.3 esta entre 2.0 y 2.5, para que sepa que valores tiene que aplicar en la fórmula.
El problema de la fórmula BUSCAR que tu me expones es que da el valor más cercano inferior, y lo que yo necesito es que me de el valor que le corresponda en la fila Pr/Py.
No se si me habré explicado bien porque al final me estoy liando hasta yo. De todas formas gracias de antemano por tu paciencia, y si no me he explicado bien no te preocupes demasiado, ya que tengo la posibilidad de hacer una fórmula larguísima con todos los valores. La pregunta era para saber si se puede optimizar de alguna forma esa fórmula.
Gracias por tu tiempo y atención. Un saludo desde España también.
En primer lugar decirte que yo uso el Excel 2003.
En segundo lugar, la definición de interpolar que yo pretendo conseguir es la siguiente:
Pm/Py 0 0.5 1.0 1.5 2.0 2.5 3.0 3.5 4.0 4.5 5.0 5.5 6.0 >6.5
Pr/Py 0 0.09 0.18 0.25 0.32 0.38 0.43 0.47 0.51 0.53 0.54 0.56 0.56 0.57
Si por ejemplo yo tengo un valor de Pm/Py=2.3, (Valor entre 2.0 y 2.5) tengo que conseguir el valor que le corresponda en la fila Pr/Py (que estará entre 0.32 y 0.38). Para conseguir este valor, se aplica (o por lo menos yo aprendí así) la fórmula matemática siguiente (para este caso):
(2.5-2.0)/(0.38-0.32)=(2.5-2.3)/(0.38-X) Siendo X el valor que se quiere calcular.
Despejando la ecuación Nos quedaria:
X=0.32-((2.5-2.3)*(0.38-0.32))/(2.5-2.0)); (Esta es la formula que yo aplico)
Esta fórmula se aplica a la pareja de valores de (2.0 y 2.5), ya que el valor que nosotros tenemos es 2.3; Ahora habría que aplicar esta fórmula para el resto de parejas de valores de la tabla, es decir: (0 y 0.5), (0.5 y 1.0), (1.0 y1.5) ... hasta (6.0 y 6.5), comprobando a su vez que el valor que hemos calculado (2.3 en nuestro caso), entre que valores está, es decir, que excel sepa que 2.3 esta entre 2.0 y 2.5, para que sepa que valores tiene que aplicar en la fórmula.
El problema de la fórmula BUSCAR que tu me expones es que da el valor más cercano inferior, y lo que yo necesito es que me de el valor que le corresponda en la fila Pr/Py.
No se si me habré explicado bien porque al final me estoy liando hasta yo. De todas formas gracias de antemano por tu paciencia, y si no me he explicado bien no te preocupes demasiado, ya que tengo la posibilidad de hacer una fórmula larguísima con todos los valores. La pregunta era para saber si se puede optimizar de alguna forma esa fórmula.
Gracias por tu tiempo y atención. Un saludo desde España también.
Me parece que esta mal despejada la POR, pero bueno ya veo la idea por donde va, dejame algo de tiempo y si puedo te contesto en 1 hora, y si no puedo, tendrá que ser más tarde.
He estado unos días off line, pero ya he regresado.
Te había realizado una macro para calcularlo pero al final, he dado con una fórmula y te la adjunto. Tiene aclaraciones, yo he colocado en la celda A1 el valor y la interpolación te la indico en la A2, la tabla lo he colocado en B1:O2 y el resultado es
A2=(BUSCARH(ENTERO(A1*2)/2+0,5;B1:O2;2))-(((ENTERO(A1*2)/2+0,5)-A1)*((BUSCARH(ENTERO(A1*2)/2+0,5;B1:O2;2))-(BUSCARH(A1;B1:O2;2)))/((ENTERO(A1*2)/2+0,5)-(ENTERO(A1*2)/2)))
Evidentemente todo en una linea.
Si te resulta incomodo o necesitas aplkicarlo a otros libros, te podía crear una función de interpolar, pero eso me lo tienes que indicar.
Si quieres que te envíe el libro de muestra con la fórmula y la tabla, pon tú correo y te envío el fichero,.
Te había realizado una macro para calcularlo pero al final, he dado con una fórmula y te la adjunto. Tiene aclaraciones, yo he colocado en la celda A1 el valor y la interpolación te la indico en la A2, la tabla lo he colocado en B1:O2 y el resultado es
A2=(BUSCARH(ENTERO(A1*2)/2+0,5;B1:O2;2))-(((ENTERO(A1*2)/2+0,5)-A1)*((BUSCARH(ENTERO(A1*2)/2+0,5;B1:O2;2))-(BUSCARH(A1;B1:O2;2)))/((ENTERO(A1*2)/2+0,5)-(ENTERO(A1*2)/2)))
Evidentemente todo en una linea.
Si te resulta incomodo o necesitas aplkicarlo a otros libros, te podía crear una función de interpolar, pero eso me lo tienes que indicar.
Si quieres que te envíe el libro de muestra con la fórmula y la tabla, pon tú correo y te envío el fichero,.
La función podría ser algo así:
Function interpolarh(valor As Double, tabla As Range, paso As Double) As Double
v1 = Int(valor / paso) * paso
For Each valores In tabla
If valores = v1 Then
col = valores.Column
Exit For
End If
Next
If col <> Empty Then
fila = tabla.Row
f1 = Cells(fila, col)
g1 = Cells(fila, col + 1)
f2 = Cells(fila + 1, col)
g2 = Cells(fila + 1, col + 1)
interpolarh = g2 - ((g1 - valor) * (g2 - f2) / (g1 - f1))
End If
End Function
'-----------------
De tal forma que le indecamos el valor, el rango donde tiene que buscar ese valor y le marcamos el paso, el paso es la diferencia entre valores a buscar, en nuestro caso 0.5
Si necesitas aclarariones al respecto, ya sabes donde estoy.
Function interpolarh(valor As Double, tabla As Range, paso As Double) As Double
v1 = Int(valor / paso) * paso
For Each valores In tabla
If valores = v1 Then
col = valores.Column
Exit For
End If
Next
If col <> Empty Then
fila = tabla.Row
f1 = Cells(fila, col)
g1 = Cells(fila, col + 1)
f2 = Cells(fila + 1, col)
g2 = Cells(fila + 1, col + 1)
interpolarh = g2 - ((g1 - valor) * (g2 - f2) / (g1 - f1))
End If
End Function
'-----------------
De tal forma que le indecamos el valor, el rango donde tiene que buscar ese valor y le marcamos el paso, el paso es la diferencia entre valores a buscar, en nuestro caso 0.5
Si necesitas aclarariones al respecto, ya sabes donde estoy.
