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, chm y también de un archivo pdf.

Complemento de Excel: "Interpolacion.xla"  

Versión 1.9 con archivo de ayuda HLP

Versión 1.9 con archivo de ayuda CHM

Versión 2.1 para Excel 2010 y posteriores  (también sirve para las anteriores pero se pierde la descripción de argumentos)

Archivo de ayuda InterpolacionV2.pdf

El archivo con ayuda HLP funciona bien en Windows XP con Excel 2003. Para Windows 7 y Excel 2007 y superiores instalar el archivo con ayuda CHM.

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.09  (22 de febrero de 2016). Se modifica el archivo "Interpolacion.hlp" y se añade el archivo "Interpolacion.chm".

        Versión 2.0  (6 de abril de 2016. Se añaden nuevas funciones y se crea un nuevo sistema de ayuda contextual con descripción de funciones y conexión con archivo de ayuda Chm.

Versión 2.1 (Febrero de 2020. Revisada la función Cercharea y similares para poder trabajar adecuadamente con los intervalos de integración)

Instalación de la versión 2.x

Los archivos, una vez descomprimidos, se deberán copiar en el directorio:

C:\Program Files\Microsoft Office\Root\Office16\Library      

C:\Archivos de Programa\Microsoft Office\Root\Office16\Library   

Este directorio es una "Ubicación de confianza" en las rutas de Excel y es donde se busca al archivo "Interpolacion.chm". El archivo Interpolacion.xla podrá ir en el directorio donde Excel busca los complementos.

Importante: Al ser descargado a través de Internet, el archivo chm quedará bloqueado por el sistema operativo. Habrá que desbloquearlo pinchando con el botón derecho del ratón y en propiedades.

Para activar el complemento en Excel 2007 y posteriores hay que pinchar en Archivo | Opciones | Complementos | Ir | Examinar. Después hay que buscar el directorio donde se guardo el complemento y Aceptar.

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 (sin usar código VBA, solo funciones básicas de Excel)

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.

 

Nuevas funciones:

CERCHAK              Cercha de Akima (poca oscilación).

CERCHAKCO         Coeficientes de las cerchas de Akima. 

CERCHAKD            Pendiente o segunda derivada en el spline de Akima.

CERCHAKIN           Área entre la cercha de Akima y el eje X.

CERCHAS              Interpolación y extrapolación por spline.

CERCHASCO         Coeficientes de las cerchas.

CERCHASD            Pendiente o segunda derivada en el spline.

CERCHASIN           Área entre la cercha y el eje X.

CERCHAH              Cercha de Hermite.

CERCHAHCO         Coeficientes del spline de Hermite.

CERCHAH2D          Segunda derivada en el spline de Hermite.

CERCHAHIN           Área entre la cercha de Hermite y el eje X.


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

"Clave" 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

Clave: 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 1 de abril de 20226