Herramienta para Interpolar y extrapolar con Excel®
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):
"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
Página actualizada el día 1 de abril de 20226