Ver originales (Francés)

Interpolación de Excel por polinomio de grado 2

Resuelto
Heinoss -  
 Zbob -
Hola,

Actualmente estoy trabajando en la adaptación de un programa de Excel de mi empresa que permite interpolar valores corregidos de un dispositivo de medición a partir de los datos del certificado de calibración, a otros tipos de dispositivos.

Para ello, el libro modela los valores mediante un polinomio de tercer grado cuyos coeficientes son determinados por el método de mínimos cuadrados (según la persona que creó el libro base).

El problema es que ciertos dispositivos solo están calibrados en tres valores y el uso de un polinomio de tercer grado ya no es posible. Por lo tanto, debo reducirme a un polinomio de grado 2.

El problema es que las fórmulas para calcular los coeficientes del polinomio son verdaderas "fábricas de gas" que no consigo adaptar.

Por lo tanto, me gustaría saber si Excel no dispone de una función que permita obtener directamente estos coeficientes.

¡Gracias a usted!

Configuración: Windows XP / Internet Explorer 8.0

7 respuestas

  1.
    green day Mensajes publicados 26374 Fecha de registro   Estado Moderador, Colaborador de seguridad Última intervención   2 166
     
    Hola,

    Si entiendo bien, quieres aproximar tus puntos a un polinomio de segundo grado y obtener la ecuación de la curva, ¿verdad?

    Es posible hacerlo con Excel. En primer lugar, seleccionas los puntos que deseas graficar (elegir un gráfico de dispersión está bien), luego, una vez trazado, con el título del gráfico, el nombre de los ejes, etc., haces clic derecho sobre la curva y seleccionas "agregar una línea de tendencia" o algo así, y ahí tendrás diferentes tipos de aproximaciones, incluidas las curvas de segundo grado.
    Después, en las opciones, desmarcas la casilla "mostrar la ecuación de la curva" en la curva.

    Así obtendrás tus coeficientes.

    @+
    --
    La fórmula sagrada del positivismo: el amor como principio, el orden como base y el progreso como fin. (Auguste Comte)
    4
    1. fgfd
       
      gracias Green Day
      que Dios te guíe por su camino
      0
    2. Zbob
       
      ¡Buen trabajo!
      0
  2. Raymond PENTIER Mensajes publicados 58216 Fecha de registro   Estado Colaborador Última intervención   17 482
     
    Tan pronto como cambias tus datos, todo se actualiza ...
    --
    ¡Está bien, la jubilación! Especialmente en las Antillas ... :-)
    ☻ Raymond ♂
    1
  3. Heinoss
     
    Hola Green Day y gracias por tu respuesta.

    De hecho, había pensado en una solución de este tipo, pero el problema es que todo el procedimiento debe estar automatizado.
    En resumen, el operador solo tiene que ingresar los valores de calibración y dejar que el sistema haga el resto, y me preocupa que incluso al lograr automatizar esto, sea bastante pesado en su ejecución.
    0
    1. green day Mensajes publicados 26374 Fecha de registro   Estado Moderador, Colaborador de seguridad Última intervención   2 166
       
      ¡Arf! No sé hacer procedimientos automáticos en Excel, lo siento :/
      0
  6. ccm81 Mensajes publicados 11033 Estado Miembro 2 434
     
    Hola

    Por tres puntos de abscisas diferentes, y de los cuales al menos dos ordenadas son diferentes, pasa una parábola única
    de ecuación y = ax² + bx + c
    si se respetan estas condiciones, no es un problema de interpolación
    con un cálculo matricial relativamente simple se puede determinar a, b, c sin pasar por una curva de tendencia, ver
    https://www.cjoint.com/?0FcjoakxIdl
    de lo contrario, es efectivamente un problema de interpolación

    buena continuación
    0
  7. Heinoss
     
    Hola a los dos,

    Y lamento haber tardado tanto en responder (fin de semana largo de por medio)

    Hablé de interpolación porque el programa debe, a partir de los coeficientes encontrados, calcular los valores corregidos correspondientes a todas las lecturas posibles que se pueden obtener en el dispositivo.

    Pero, por lo demás, el problema es efectivamente el que describe ccm81.

    El único inconveniente es que tengo la obligación de mantener la misma "forma" de cálculos que el programa a partir del cual trabajo y que mi predecesor no utilizó un sistema matricial.

    Así que me encuentro, para un polinomio de grado 3, con fórmulas de cálculo de coeficientes del tipo:

    a =(COVARIANCE(E45:E51;C45:C51)-COVARIANCE(B45:B51;C45:C51)*COVARIANCE(B45:B51;E45:E51)/VAR(B45:B51;B52)-E6*(VAR(E45:E51;E52)-COVARIANCE(B45:B51;E45:E51)*COVARIANCE(B45:B51;E45:E51)/VAR(B45:B51;B52)))/(COVARIANCE(E45:E51;I45:I51)-COVARIANCE(B45:B51;I45:I51)*COVARIANCE(B45:B51;E45:E51)/VAR(B45:B51;B52))

    así que es difícil de descifrar en su estado, aunque cuando miro los rangos de datos utilizados reconozco un poco la fórmula de cálculo en el caso de una regresión lineal: a = COV(XY)/V(X) b = E(Y) - aE(X)

    En fin, sigue siendo un buen lío bastante oscuro para mí y no he encontrado en internet un método de cálculo que dé este tipo de resultados para grados superiores a 1.
    0
  8. Heinoss
     
    Bueno, en realidad pude seguir el método propuesto por ccm81, así que problema resuelto.

    ¡Muchas gracias!
    0