Herramienta para Interpolar y extrapolar con Excel®

English version  

Se pone en esta página, a disposición de alumnos e internautas,  un complemento de Excel que sirve para interpolar y extrapolar lineal, cuadrática y cúbicamente. Las funciones de este complemento son muy sencillas de usar y tienen ayuda contextual, a través de un archivo hlp y también de un archivo pdf.

Complemento de Excel  Interpolacion.xla  

Descarga del archivo

Si ya tiene instalada una versión anterior, conviene cargar el nuevo fichero como si no se tuviera alguno (saldrá un mensaje pidiendo el cambio).

        Versión 1.08  (24 de enero de 2011) se añade función INTERPO2N para interpolación lineal doble sin extrapolación. Se corrige la función CERCHALON.

Una vez descargado y descomprimido, se obtienen tres archivos que deberán guardarse juntos en cualquier directorio del disco duro. Para que funcione el complemento, una vez abierto Excel, pinchamos en Herramientas ! Complementos ! Examinar  aparece el subdirectorio Microsoft\Addins, pero buscamos el archivo en el subdirectorio donde le guardamos junto al dll (biblioteca de enlace dinámico) y el hlp (archivo de ayuda) y pinchamos en Aceptar. También se puede descargar un archivo de ayuda imprimible en formato pdf: Interpolacion.pdf

Ya se pueden utilizar las funciones que tiene dentro, que son la función Interpo para la interpolación lineal, y la función cercha (junto con otras adicionales derivadas), que engloba a todas las funciones de interpolación por splines cúbicos.

En el caso de querer utilizar las funciones de interpolación dentro de un cálculo de estabilidad, y querer asignar una pendiente inicial = GMc (spline forzado o sujeto) habrá que tener en cuenta si se introduce el valor de la escora en  grados o en radianes.

Otras Macros y funciones para Excel sobre Interpolación

Ejemplo de uso del complemento:

CERCHA
____________________________________________________
CERCHA(x ;Rango_xy ;"??" ;v1;v2 )
____________________________________________________
Argumentos obligatorios: x;Rango_xy
Argumentos opcionales: "??";v1;v2            (más abajo se explican estas claves)

Importante: deben respetarse las posiciones y los separadores (";" o "," según configuración Excel o Windows).

Esta función sirve para interpolar o extrapolar usando las cerchas o splines, que son funciones -en un principio polinomios cúbicos- que se adaptan por trozos a los puntos entre los que hay que interpolar, de tal manera, que entre parejas de puntos contiguos hay polinomios distintos (con excepciones). La pendiente y segunda derivada en los extremos de las cerchas, coincide con la siguiente y los valores en el principio de la primera y en el final de la última son configurables en función del tipo de cercha que se necesite, es decir, se establecen unas "condiciones en los extremos".

Nota muy importante: Los datos deben de estar ordenados en ascendente y las condiciones en los extremos se aplicarán, el primero (1ª? y v1) para el menor valor de Rango_xy (1ª columna) y (2ª? y v2) para el mayor valor de Rango_xy (1ª columna).
Ejemplo de utilización de la función CERCHA

Se adapta una cercha cúbica forzada en origen y final que pasa por los puntos dados. La pendiente en el punto inicial es -1,5 y la pendiente final es 1,5






Curva resultante e interpolación para un solo punto:

 

Funciones del complemento     Interpolacion.xla

INTERPO                 Interpolación lineal.

INTERPO2               Interpolación y extrapolación lineal doble

INTERPO2N            Interpolación lineal doble

CERCHA                 Interpolación por splines (cerchas) cúbicos

CERCHAC              Coeficientes de las cerchas.

CERCHACOEF      Coeficientes de las cerchas (ref. origen coord.)

CERCHAP               Pendiente en los puntos dados.

CERCHAPI              Pendiente inicial de la 1ª cercha.

CERCHAPF             Pendiente final de la última cercha.

CERCHACI              2ª derivada inicial de la 1ª cercha.

CERCHACF            2ª derivada final de la última cercha.

CERCHARA            Radios de curvatura en puntos.

CERCHARAXY       Coordenadas de los centros de curvatura.

CERCHACU            Segundas derivadas en puntos.

CERCHACUR         Curvatura en puntos.

CERCHAREA         Área entre la cercha y el eje X.

CERCHAMX           Momento estático con respecto al eje X.

CERCHAMY            Momento estático con respecto al eje Y.

CERCHAM2X         Momento segundo con respecto al eje X.

CERCHAM2Y          Momento segundo con respecto al eje Y.

CERCHAP2             Producto de inercia.

CERCHAXG            Coordenada longitudinal centroide.

CERCHAYG            Coordenada vertical del centroide.

CERCHALON          Longitud de la cercha.


Ejemplo de interpolación en la curva de estabilidad de un buque

Datos iniciales:

Puesto que la altura metacéntrica corregida por efecto de superficies libres (GMc), es la pendiente de la curva en el origen y al tender la curva de estabilidad a una recta en su parte final, nos encontramos con que conocemos la pendiente inicial de la curva y la curvatura final (2ª derivada). Con estas condiciones en los extremos usamos la combinación de claves "fn" (las dobles comillas las pone el asistente para fórmulas o nosotros en el editor de fórmulas). La "f" corresponde a la condición de cercha o spline forzado y la "n" a la de cercha o spline natural o "variational". Ver tipos de cerchas al final de esta página

En la fórmula de la celda E4 vemos que el valor de la pendiente está multiplicado por "PI()/180". Esto se debe  a que el valor de las X (escoras) está escalado en 180/PI(), con respecto a los radianes

180/PI() = 180 / 3,14159 = 57,296

En la siguiente imagen se pone un ejemplo de varias interpolaciones, que se pueden utilizar para representar una porción de la curva e incluso exportar a un archivo de texto, para posteriormente importar, por ejemplo, en Autocad.

El argumento Rango_xy de la fórmula se pone en referencia absoluta (pulsando F4) para evitar que al copiar la fórmula se "mueva" el Rango. En este caso también se pone en referencia absoluta la celda que tiene el valor del GMc, aunque se podría haber puesto el valor directamente en la fórmula. Para evitar el tener que poner referencias absolutas, se puede asignar un nombre al rango y poner el nombre en la fórmula.

El programa Excel® tiene un asistente para gráficos, pero en el caso de que se prefiera, por ejemplo, un gráfico en Autocad®, se puede hacer de la siguiente manera:

En un principio se copian los datos de x y de y en una hoja nueva, quedando juntos (quizás haya que utilizar la opción Pegado especial ! Valores).

Se guarda con formato "CSV" (delimitado por comas) (*.csv). A continuación se abre con Word y en el caso de que el delimitador de listas de Windows sea el punto y coma (como el separador de argumentos de las fórmulas de Excel) habrá que reemplazar (usando la función Edición ! Reemplazar) primero las comas decimales por puntos decimales (separador decimal de Autocad), a continuación los punto y coma por comas (separador de coordenadas de Autocad) y por último, se añade al principio, antes del primer dato, la orden Polilínea de Autocad.

El archivo queda de la siguiente manera. Aquí se ha utilizado la versión en inglés de Autocad 2000 y antes de la orden tiene un guión bajo y después un espacio.

A continuación se guarda el archivo con formato texto, pero con un truco. En el espacio para poner el nombre, se pone de la siguiente manera, es decir, entre dobles comillas y poniendo la extensión scr. Este sistema de las comillas nos permite guardar un archivo en Windows con un determinado formato o tipo y con un nombre y extensión a nuestro gusto. Si no se hace así, el archivo siempre tendrá una extensión adicional txt.

Con todo esto hemos creado un archivo de guión para Autocad. Ahora desde Autocad ! Herramientas (Tools) ! Ejecutar guión (Run script), seleccionamos el archivo creado y nos queda algo parecido a esto (aquí ya se han añadido los ejes y demás):


Argumentos opcionales

?? Tipos de cerchas o splines: (Hay que introducir 2, 1 ó ninguna letra). Utilización del complemento.

 Por defecto se trabajará con cerchas cúbicas, pero es posible indicar:

- Cerchas de primer grado (rectas): poner "PG" o "pg". (ver otras funciones del complemento, p.e. INTERPO e INTERPO2)

- Cerchas de segundo grado (cuadráticas): poner "SG" o "sg" (el número de filas debe de ser impar).

- Cerchas cúbicas

??: Por ejemplo (fp). Aquí se indica el tipo de condiciones en los extremos que se ponen al spline (ver más abajo). Da igual si se introducen en mayúsculas o en minúsculas y se podrán hacer combinaciones entre los distintos tipos, teniendo en cuenta que las opciones (h), (x) no admiten combinaciones por el tipo especial que tratan. Además, en caso de que Rango_xy solo tenga 2 ó 3 filas, la función asignará la opción (h) para el caso de 2 filas y (p) para el caso de 3 filas. Las comillas las pone el asistente o se escribirán en la barra de fórmulas (según se desee). Si falta una condición se considerará n, independientemente de que se introduzcan valores en v1 o v2. También, deben respetarse las posiciones, para poder asignar correctamente su valor a v2.

Condiciones en los extremos:

Natural (n), también llamada "variational". La curvatura en el extremo es nula. La cercha empieza o termina en una recta. Es la opción por defecto en CERCHA*, de tal modo, que si se desea que sea forzada al principio y natural al final, poner "f" o "f " o "fn" es equivalente, y también "Fn" o "FN", pues no se distingue entre mayúsculas y minúsculas. Un espacio en blanco equivale a una "n".

Forzada (f). Se asigna un valor a la pendiente. Cuando se emplee en una curva de estabilidad, si las escoras están en grados, la pendiente en el origen (GMc) deberá multiplicarse por PI()/180, siendo PI()/180 » 0.0174532 o dividirse por un radián expresado en grados » 57.29578

Curvatura (c). Se asigna un valor a la 2ª derivada.

Extrapolada (e), también llamada "Not-a-Knot". Una misma cúbica para el primer y segundo tramo (tres primeros puntos) y/o para el último y anteúltimo (tres últimos puntos).

Parabólica (p). La cercha (primer y/o último tramo) será una parábola. El resto de cerchas serán funciones cúbicas, si es que hay datos suficientes (al menos 4 filas). Por norma general cuando haya menos de 4 filas en Rango_xy, será este tipo el que se aplique. 

Periódica (x). La pendiente y la curvatura (en función de la 2ª derivada) al inicio serán las mismas que al final. No admite combinaciones de argumentos

Hermite (h). En caso de tener solamente dos puntos (2 filas en Rango_xy) y las pendientes en estos puntos, se interpolará con el spline cúbico de Hermite.


Uso de varias de las condiciones en los extremos

Que representadas estas interpolaciones y extrapolaciones nos dan este gráfico


Ejemplo de interpolación lineal en una tabla de hidrostáticas usando la función INTERPO del complemento.


Ejemplo de interpolación lineal doble

 

                    Volver

Página actualizada el día 02 de febrero de 2011