Interpolación en Excel: características, procedimientos y ejemplos

La hoja de cálculo de Excel permite no soloproduce rápidamente varios cálculos, pero también resuelve problemas bastante complejos. Por ejemplo, se puede usar para realizar modelos matemáticos sobre la base de un conjunto de valores discretos de una función, incluido el hallazgo del valor intermedio de funciones por interpolación. En Excel, para este propósito, se proporcionan varias herramientas, que este artículo enseñará.

Método de interpolación: ¿qué es?

En matemáticas computacionales, este es el nombre del método para encontrar los valores intermedios desconocidos de la función Y (X) a partir de un conjunto discreto de los ya conocidos.

La interpolación de la función Y (X) puede llevarse a cabo solo para aquellos argumentos que están dentro del intervalo [X₀, X_n], tal que los valores de Y (X₀) e Y (X_n)

Si X no pertenece a [X₀, X_n], luego se puede usar el método de extrapolación.

En la formulación clásica del problema de interpolación, se requiere encontrar una función analítica aproximada φ (X) cuyos valores en los puntos de nodo de X_yo coincidir con los valores de Y (X_yo) de la tabla original, es decir, la condición φ (X_yo) = Y_yo (i = 0,1,2, ..., n).

Interpolación lineal en Excel

En el procesador de tablas más famoso de Microsoft, hay un operador muy útil, PRESCASE.

Considere los datos colocados en la tabla a continuación.

	A	B	C	D	E
1	x	f (x)
2	5	38
3	10	68
4	15	98
5	20	128
6	25	158
7	30	188

En la primera columna hay argumentos x, y en el segundo - los valores correspondientes de alguna función lineal f (x). Supongamos que queremos saber el valor del argumento x = 28. Para hacer esto:

• asigne cualquier celda vacía en la hoja del procesador de tabla, donde el resultado se generará a partir de las acciones realizadas, por ejemplo, C1;
• Haga clic en el icono "fx" ("Insertar función"), ubicado a la izquierda de la línea de fórmula;
• en la ventana "Masters of Functions", vaya a la categoría "Mathematical";
• encuentre el operador "PREDICCIÓN" y haga clic en "Aceptar".

Hay 3 campos en la ventana de argumentos. El primero se ingresa desde el teclado con el valor del argumento (en una tarea específica es 28). Para completar el campo "Valores conocidos _ y", haga clic en el icono con la flecha roja a la izquierda de la ventana correspondiente y seleccione el área correspondiente en la hoja. En el caso específico, esto es parte de la columna B con direcciones del rango B2: B7.

Del mismo modo, rellene el campo "Valores conocidos _ x" y haga clic en el botón "Aceptar".

Como resultado, la celda seleccionada C1 muestra el valor 176, que es el resultado del procedimiento de interpolación.

Método gráfico: preparación.

Interpolación en Excel, se presenta un ejemplo de ello.Arriba, no es la única forma de averiguar los valores desconocidos intermedios de la función Y (X) a partir de un conjunto discreto de ya conocidos. En particular, se puede aplicar el método gráfico. Puede ser útil si la tabla de uno de los argumentos no indica el valor correspondiente de la función, como en el que se presenta a continuación (ver celda con la dirección B9).

	A	B	C	D	E
1	x	f (x)
2	5	38
3	10	68
4	15	98
5	20	128
6	25	158
7	30	188
8	35	218
9	40
10	45	278
11	50	308

La interpolación en Excel en este caso comienza con el trazado. Para esto:

• en la pestaña "Insertar" seleccione el rango de la tabla;
• En el bloque de herramientas "Diagramas", seleccione el icono "Gráfico";
• En la lista que aparece, elija la que mejor se adapte para resolver un problema en particular.

Como la celda B9 está vacía, la gráfica esdesgarrado Además, hay una línea X adicional en ella, en la que no hay necesidad, y en el eje horizontal, en lugar de los valores de argumento, los puntos se enumeran en orden.

Interpolación Excel: Solución Gráfica

Vamos a tratar con el procesamiento del horario. Para ello, seleccione la línea azul continua y elimínela presionando el botón Eliminar, que se encuentra en el teclado.

Entonces

• seleccione el plano en el que se encuentra la gráfica;
• en el menú contextual, seleccione el botón "Seleccionar datos ...";
• en la ventana "Seleccionar fuente de datos", en el bloque derecho, haga clic en "Cambiar";
• haga clic en el icono con la flecha roja a la derecha del campo "Rango de etiquetas del eje";
• asignar un rango de A2: A11;
• haga clic en el botón "Aceptar";
• vuelve a abrir la ventana "Seleccionar fuente de datos";
• haga clic en "Celdas ocultas y vacías" en la esquina inferior izquierda;
• en la línea "Mostrar celdas vacías", cambie el interruptor a la posición "Línea" y presione "OK";
• Confirma estas acciones de la misma manera.

Si todo se hace correctamente, la brecha se eliminará y, al colocar el cursor sobre el punto deseado del gráfico, podrá ver los valores correspondientes del argumento y la función.

Usando la función especial ND

Ahora que ya sabes cómo interpolar.En Excel de forma gráfica o mediante el operador "PREDESKAZ", la solución de muchos problemas prácticos para usted no será fácil. Sin embargo, esto no es todo. El procesador tabular de Microsoft ofrece la posibilidad de encontrar un valor de función desconocido mediante la función ND.

Supongamos que el programa ya está construido, las etiquetas de escala correctas ya se han instalado en él. Vamos a tratar de cerrar la brecha. Para esto:

• seleccione en la tabla una celda en la que falta el valor de la función;
• seleccione el icono "Insertar función";
• en el "Asistente de funciones" en la ventana "Categorías", busque la línea "Lista alfabética completa" (en algunas versiones del procesador "Comprobar propiedades y valores");
• Haga clic en el registro "ND" y presione el botón "OK".

Después de esto, el valor de error "# N / A" aparece en la celda B9. Sin embargo, la interrupción en el horario se fija automáticamente.

Incluso puede hacerlo más fácil: ingrese los caracteres "# N / A" (sin comillas) del teclado en la celda B9.

Interpolación bilineal

La gama de tareas para las que puedesusar el modelado por funciones de una variable es bastante limitado. Por lo tanto, tiene sentido considerar cómo se utiliza la fórmula de doble interpolación en Excel. Los ejemplos pueden ser muy diferentes. Por ejemplo: hay una tabla (ver más abajo).

	A	B	C	D	E	F	G
1		200	400	600	800	1000	Span
2	20	10	20	160	210	260
3	30	40	60	190	240	290
4	40	130	180	230	280	330
5	50	180	230	280	330	380
6	60	240	290	340	390	440
7	70	310	360	410	460	510
8	80	390	440	490	540	590
9	90	750	800	850	900	950
10	Altura	278

Es necesario calcular la presión del viento en un tramo de 300 m a una altura de 25 m.

Las nuevas entradas se agregan a la tabla como se muestra en la figura (ver más abajo).

Como puede ver, agregó celdas para la altura y el rango en J1 y J2.

Mediante la sustitución secuencial inversa, "recogen" la mega-fórmula necesaria para encontrar la presión del viento en parámetros específicos. Para esto:

• copie el texto de la fórmula desde la celda con la dirección J17 a la celda J19;
• Reemplace en la fórmula la referencia a J15 con el valor en la celda J15: J7 + (J8-J7) * J11 / J13;
• repita estos pasos para obtener la fórmula requerida.

Usar spline

El método anterior es bastante engorroso, por lo que enen algunos casos se prefiere la interpolación spline. En Excel, su esencia radica en encontrar la función de interpolación φ (X) mediante fórmulas del mismo tipo para diferentes subconjuntos del argumento. A continuación, se lleva a cabo la unión de los valores de φ (X) y sus derivados sobre los valores límite de los argumentos de cada uno de los subconjuntos. Excel tiene funciones especiales para este propósito, y también es posible escribir macros en VBA. Sin embargo, deben crearse para una tarea específica, por lo que su estudio en términos generales no tiene sentido.

Ahora ya sabes cómo escribir una fórmula dobleEjecute correctamente la interpolación o encuentre el valor desconocido de una función lineal mediante operadores integrados o un gráfico. Esperamos que esta información le ayude a resolver muchos problemas prácticos.