Resolución de ecuaciones utilizando Excel.pdf

Departamento de Ingeniería Química

GIAIQ (Grupo de Investigación Aplicada a la Ing. Química) Área: Informática Aplicada a la Ing. Química

Utilitarios de Computación

Docentes: Profesora Asociada: Dra. Marta Basualdo Auxiliar de cátedra: Ing. Javier Francesconi

Resolución de ecuaciones con Excel

http:\\www.modeladoeningenieria.edu.ar

Ing. Javier Francesconi Utilitarios de Computación 2

Resolución de ecuaciones utilizando Excel Resolver ecuaciones algebraicas complicadas forma parte de todo curso de ingeniería. Estas ecuaciones pueden representar relaciones de causa-efecto entre variables de un determinado sistema, o pueden ser el resultado de aplicar principios físicos a problemas específicos. En ambos casos, el resultado provee generalmente un detallado conocimiento del problema en estudio. Por ejemplo, la relación entre presión, volumen y temperatura para muchos gases reales puede ser determinada por la ecuación de estado de Van der Waal’s, la cual viene expresada por:

( )2

aP V b RTV

+ − =

donde P es la presión absoluta, V es el volumen molar, T es la temperatura absoluta, R es la constante del gas ideal (0.082054 litros atm / mol °K), a y b son constantes únicas para cada gas particular. Si a y b son iguales a cero la ecuación anterior se reduce a la ecuación de estado del gas ideal. Suponiendo que estamos trabajando con un gas real donde a y b son conocidos. Si la presión y el volumen son especificados, es muy fácil resolver la ecuación anterior para la temperatura. Similarmente, es fácil resolver la ecuación anterior para la presión si la temperatura y el volumen son especificados. La dificultad se presenta, sin embargo, al resolver la ecuación anterior para el volumen si la temperatura y la presión son conocidas. Dado que la ecuación no es lineal en términos del volumen (debido al termino V 2 en el denominador del lado izquierdo de la ecuación). De echo , la solución involucra encontrar una raíz positiva de la ecuación cúbica, la cual requiere algún juicio en la selección de la raíz correcta.

Caracterización de ecuaciones algebraicas no lineales Repasemos algunos conceptos de álgebra. Una ecuación se dice lineal si es posible reordenarla de modo que la variable representando la cantidad desconocida aparece solo a la primera potencia. Por ejemplo, la ley de Ohm, escrita de la forma ∆V iR= , es lineal con respecto a las tres variables (∆V i R, , ). Las ecuaciones lineales tienen una y solo una raíz siendo esta siempre real. Las ecuaciones lineales son muy fáciles de resolver usando técnicas de álgebra elemental. Por otra parte, un ecuación es no lineal si no se puede reordenar de manera tal que la variable representante de la cantidad desconocida aparezca solo a la primera potencia. Esto es, si x representa una cantidad desconocida, una ecuación no lineal puede incluir a x elevado a alguna potencia distinta de uno, o puede estar incluida como log x o sen x , etc. Las ecuaciones no lineales generalmente no pueden ser resultas utilizando las técnicas de álgebra elemental (hay excepciones como la bien conocida formula para obtener las raíces de la ecuación cuadrática). Esta deben ser resueltas gráfica o numéricamente. Además, éstas pueden tener múltiples raíces reales, o bien presentar raíces complejas.

Una ecuación polinomial es un caso especifico de ecuación no lineal. Una ecuación polinómica puede incluir potencias de x pero no términos tales como log x , sen x , xe , etc. La potencia mas alta de x se denomina grado u orden del polinomio.

Las raíces de las ecuaciones polinomiales presentan las siguientes características: 1. Un polinomio de grado n puede tener no mas de n raíces reales. 2. Si el grado de un polinomio es impar, siempre habrá al menos una raíz real. 3. Las raíces complejas siempre existen de a pares. Cada par de raíces complejas consiste de complejos

conjugados; es decir, x u iv1 = + y x u iv2 = − . Por ejemplo, si reordenamos la ecuación de Van der Walls’s obtenemos

PV RT Pb V aV ab3 2 0− + + − =( ) observando la ecuación, podemos concluir que existirá al menos una raíz real.

Solución Gráfica Soluciones graficas aproximadas son fáciles de obtener, particularmente utilizando una planilla de cálculo.

Básicamente, el procedimiento es escribir un ecuación de la forma ( )f x = 0 y luego graficar f x( )en función de x. Los puntos donde ( )f x corta el eje x (es decir, los valores de x que hacen ( )f x igual a 0) son las raíces reales de la ecuación. Generalmente, estos puntos pueden ser leídos directamente desde el grafico o pueden ser interpolados de los valores tabulados encontrando el punto donde ( )f x = 0.




EEjjeerrcciicciioo 55..11:: RReessoolluucciióónn ggrrááffiiccaa ddee uunnaa EEccuuaacciióónn PPoolliinnoommiiaall.. Consideremos la ecuación

f x x x( ) = − − =2 3 5 05 2 tiene al menos una raíz real y posiblemente más. Preparar en Excel una planilla de cálculo en la cuál ( )f x se tabula como función de x sobre el intervalo − ≤ ≤10 10x . Graficar los datos como un grafico x-y y determinar las raíces reales presentes en el intervalo.

La planilla de cálculo con los valores tabulados y el correspondiente grafico x-y se muestra en la figu.... Notar que en Excel se usa la fórmula, =2*A2^5-3*A2^2-5 para generar el contenido de la celda B2. Esta formula (con direcciones de celdas relativas) se usó para generar todo los valores remanentes en la columna B. De los valores tabulados, podemos ver que la función atraviesa el eje x en un punto entre x=1 y x=2.

Fig. 1

El grafico x-y de la Fig. 1 no es muy útil porque el rango de los valores y es demasiado grande. De esta

manera, si se calcula la función sobre el intervalo 1 2≤ ≤x (Fig. 2), se logra observar la raíz con mayor exactitud . Los nuevos resultados muestran que la raíz es cercana a x=1.4.

Fig. 2




Resolución de ecuaciones usando la herramienta Buscar Objetivo de Excel Veamos como se pueden obtener soluciones más precisas en forma más rápida y fácil usando la herramienta

Buscar Objetivo de Excel. Esta herramienta permite solucionar rápidamente ecuaciones algebraicas usando técnicas iterativas (prueba y error) de búsqueda de raíces, basadas en una serie de refinamientos sucesivos derivados de una aproximación inicial. El procedimiento a seguir consiste en:

1. Ingresar una valor inicial en una celda de la planilla de cálculo. 2. Ingresar una formula para la ecuación, en la forma ( )f x = 0, en otra celda. Dentro de esta formula, se

expresa la cantidad desconocida x como la dirección de celda conteniendo el valor inicial. 3. Seleccionar la función Buscar Objetivo desde el menú Herramientas. 4. Cuando aparece la ventana Buscar Objetivo, ingresar la siguiente información

a) La dirección de la celda que contiene la formula en el lugar Definir la celda: b) El valor 0 en la casilla con el valor: c) La dirección de celda que contiene el valor inicial en la casilla denominada para cambiar la celda:.

Luego presionar Aceptar.

Una nueva caja de dialogo denominada Estado de la búsqueda de objetivo aparecerá, mostrándonos si Excel ha sido capaz de resolver el problema. Si una solución ha sido obtenida, el valor de la raíz aparecerá en la celda que originalmente contenía el valor inicial. La celda que contiene la formula mostrará un valor cercano a cero (pero generalmente no será exactamente cero). Este último valor también aparecerá dentro dela ventana Estado de la búsqueda de objetivo. La probabilidad de obtener una solución mejorará si el valor inicial es lo más cercano posible a la raíz deseada. Es decir, es deseable usar un valor aproximado a la raíz como valor inicial, este valor puede ser generalmente establecido usando técnicas graficas o tabulares. Un mensaje de precaución se generará si el cálculo no converge o si un valor inapropiado (tal como raíz cuadrada de un numero negativo) esta cercano a generarse durante el curso del computo. EEjjeerrcciicciioo 55..22:: RReessoolluucciióónn ddee uunnaa eeccuuaacciióónn uussaannddoo llaa hheerrrraammiieennttaa BBuussccaarr OObbjjeettiivvoo

En el ejemplo anterior encontramos que la ecuación f x x x( ) = − − =2 3 5 05 2 tiene una raíz real aproximadamente x=1.4. Haremos uso ahora de la función Buscar Objetivo para obtener una solución más precisa. Seleccionaremos el valor de x=1.4 como primera aproximación dado que ya conocemos que este es un valor aproximado de la raíz.

Fig. 3

La Fig. 3 muestra la Planilla de Excel con el valor inicial (1.4) ingresado en la celda B3, y la formula para f(x) ingresada en la celda B5. (Ver la formula de Excel para f(x) mostrada en la barra de formula). El valor numérico -0.12352 aparece en la celda B5, es el valor de f(x) evaluada en x=1.4.

Nuestro objetivo es determinar el valor de x que hace a f(x) igual a cero. Seleccionamos Buscar Objetivo del menú Herramientas. La Fig. 4 muestra la caja de dialogo Buscar Objetivo. La información que ha sido ingresada en la ventana indica que el valor de f(x) en la celda B5 será conducido a cero (o aproximadamente cero) cambiando el valor de x de la celda B3.




Fig. 4

La solución resultante se muestra en la Fig. 5. La ventana Estado de la búsqueda de objetivo, la cual ha reemplazado la caja de dialogo previa, indica que una solución ha sido encontrada y el valor obtenido en la formula es -3.5625x10-5 (aproximadamente cero). El correspondiente valor de x, el cual es la solución deseada, ahora aparece en la celda B3. Esto es, Excel ha encontrado una solución en x=1.404085. Este valor es consistente con la solución aproximada obtenida anteriormente. El correspondiente valor de f(x), -3.6E-5, aparece en la celda B5.

Fig. 5

EEjjeerrcciicciioo 55..33

Las constantes de Van der Waals’s para el dióxido de carbono han sido determinadas en a=3.592 litros2 atm / mol2 y b=0.04267 litros /mol. Resolver la ecuación de Van der Waals’s para el volumen molar del dióxido de carbono bajo las siguientes condiciones: a) P=1 atm y T=300 °K b) P=10 atm y T=400 °K c) P=0.1 atm y T=300 °K En todos los casos comparar la solución con la obtenida usando la ley de los gases ideales PV=RT. Recordar que R=0.082054 litros atm /mol k. EEjjeerrcciicciioo 55..44

Las constantes de Van der Waals’s para un compuesto orgánico son las siguientes a=40.0 litros2 atm / mol2 y b=0.2 litros / mol. Resolver la ecuación de Estado de Van der Waals’s para el volumen molar de este compuesto bajo las siguientes condiciones. a) P=1 atm, T=300 °K b) P=10 atm, T= 400 °K c) P=0.1 atm , T =300 °K En cada caso, compare la solución obtenida con los resultados obtenidos para el CO2 y los resultados usando la ley de los gases ideales.




Resolviendo ecuaciones en Excel usando SOLVER. Las ecuaciones algebraicas pueden ser también resueltas usando la herramienta SOLVER de Excel. La ventaja

de usar SOLVER es que se pueden especificar restricciones (es decir, condiciones auxiliares) en la variable independiente. Por ejemplo, se puede indicar que la variable independiente no tome valores negativos o requerir que la variable independiente se encuentre entre limites definidos. Sin embargo, el procedimiento para el uso del SOLVER es muy similar al de Buscar Objetivo.

SOLVER es un complemento de Excel, para instalarlo elegir Complementos del menú Herramientas. Luego seleccionar Solver de la ventana resultante (Fig. 6). Una vez que la herramienta SOLVER ha sido instalada, permanecerá instalada ha menos que sea removida repitiendo el procedimiento descrito.

Fig. 6

. Para resolver una ecuación con SOLVER, se procede como sigue.

1. Ingresar un valor inicial en una de las celdas dela planilla de cálculo. 2. Ingresar una ecuación para f(x) en otra celda, expresada como una formula de Excel. Dentro de la formula,

expresar la cantidad desconocida x con la dirección de la celda conteniendo el valor inicial. 1. Seleccionar Solver del menú Herramientas. 2. Cuando la ventana Parámetros de Solver aparezca, ingresar la siguiente información.

a) La dirección de la celda que contiene la formula para f(x) en la casilla Celda Objetivo. b) Seleccionar Valores de: en la opción Valor de la celda objetivo:. Luego ingresar 0 dentro de la

casilla correspondiente. En otras palabras indicar que la celda objetivo sea igual a cero. c) Ingresar la dirección de la celda que contiene el valor inicial para x en la etiqueta Cambiando las

celdas:. d) Si se desea agregar restricciones al rango de x, hacer click en el botón Agregar bajo el encabezado

Sujeta a las siguientes restricciones:. Luego proveer la siguiente información dentro la caja de dialogo Agregar Restricción i) La dirección de la celda que contiene el valor inicial para x en el lugar Referencia de la celda:. ii) Especificar el tipo de restricción ( es decir, ≤ o ≥ ) desde el menú desplegable. iii) Ingresar el valor límite en el área de datos Restricción. Luego selección Aceptar para regresar a

la ventana Parámetros de Solver. Se pueden cambiar o borrar cualquiera de las restricciones después que han sido incorporadas.

e) Cuando toda la información ha sido ingresada correctamente, seleccionar Resolver. Este iniciará el procedimiento de cálculo.

Una nueva ventana denominada Resultados de Solver aparecerá, diciendo si SOLVER ha sido capaz de

resolver el problema. Si la solución ha sido obtenida, el valor de la raíz aparecerá en la celda original que contenía el valor inicial. La celda que contiene la fórmula mostrará un valor que es cero o muy cercano a cero.

Se debe comprender que SOLVER y Buscar Objetivo usan diferente procedimientos matemáticos para alcanzar la solución. EEjjeerrcciicciioo 55..55:: RReessoolluucciióónn ddee uunnaa eeccuuaacciióónn ppoolliinnoommiiaall uuttiilliizzaannddoo SSOOLLVVEERR

El ejemplo previo se resolvió la ecuación polinomial f x x x( ) = − − =2 3 5 05 2




y se encontró una raíz real positiva cercana a x=1.4. Se utilizará ahora la utilidad Solver par encontrar la raíz de dicha ecuación con el agregado de una restricción, x ≥ 0.

La Fig. muestra la planilla de Excel que contiene el valor inicial en la celda B3 y una formula para la ecuación dada en la celda B5. Se puede ver la ventana Parámetros de Solver la cual se obtiene seleccionando Solver desde el menú Herramientas. La dirección de la celda de la ecuación (B5) ha sido ingresada en el área Celda Objetivo. Notar que en la opción Valor de la celda Objetivo se ingresa el valor Valores de: 0. La dirección de celda que contiene el valor inicial (B3) se ingresa en la opción Cambiando las Celdas. Falta ingresar la restricción en x, para que no tome valores negativos.

Fig. 7

Para agregar la restricción, debemos seleccionar el botón Agregar dentro de la sección Sujetas a las siguientes restricciones. Se ingresa la dirección de la celda de la variable independiente (B3) en el área de datos izquierda, se seleccionar la restricción≥ en el centro del área de datos, y finalmente proveer el valor límite (0) en el lado derecho. Una vez que las restricción ha sido ingresadas correctamente, seleccionar el botón Aceptar al pie de la ventana y se regresa la ventana Parámetros de Solver.

Fig. 8

Seleccionando Resolver desde la ventana anterior se inicia el procedimiento de resolución. Obtenida la solución, los valores resultantes aparecen en las celdas B3 y B5,. La solución deseada es x=1.404186, resultando un valor para f(x)=3.59x10-8. Además, la ventana Resultados de Solver remplazará la ventana Parámetros de Solver. Nuestra respuesta la ventana actual es Utilizar solución de Solver seguido por Aceptar. Ninguna de las opciones Informes o guardar escenario deben ser seleccionadas dado que se alcanzado la solución buscada. SOLVER incluye un número de características adicionales que serán discutidas en próximos capítulos.




Fig. 9


Resolver la ecuación de Van de Waal’s para el volumen molar (V) de un compuesto orgánico a 10 atm de presión y 400K. Las constantes son a=40.0 litros2 atm / mol2 y b=0.2 litros/mol . Comparar la respuesta con los resultados obtenidos anteriormente. EEjjeerrcciicciioo 55..77

Para adquirir determinados bienes de valor se utilizan créditos bancarios. El crédito otorgado por una entidad bancaria es devuelto por el consumidor pagando una cuota mensual fija por un período de tiempo determinado, la cual incluye los intereses correspondientes. Si P es la cantidad total del préstamo inicial, la cantidad del monto a pagar mensualmente A puede ser determinada por

A Pi i

i

n

n=+

+ −

( )

( )1

1 1

donde i es la tasa de interés mensual, expresado en forma fraccional (no como porcentaje), y n es el numero total de pagos.

Suponiendo un préstamo de $ 10.000 a) Si la tasa de interés anual es del 8 % (equivalente a una tasa de interés mensual de 0.08/12=0.006667) y el

préstamo debe pagarse en 36 meses, cual será el monto que se debe pagar cada mes. b) Si se desea pagar 350 cada mes por 36 meses, cual es la tasa de interés mensual correspondiente. c) Si se desea pagar $350 cada mes a una tasa de interés mensual de 0.006667, que cantidad de pagos

(cuantos meses) serán necesarios para pagar el préstamo? d) En cuales de las opciones anteriores será necesario el uso de la función Solver de Excel.

Resolución de sistemas de ecuaciones Las ecuaciones algebraicas simultaneas surgen en muchas aplicaciones diferentes. Hay muchas aplicaciones

en diversos campos como conducción del calor, mecánica del sólido, circuitos eléctricos, difusión molecular, y mecánica de fluidos. De hecho, la resolución de ecuaciones algebraicas simultáneas es una de las técnicas mas comúnmente utilizadas en el campo de la ingeniería.

Los sistemas de ecuaciones simultáneas que se presentan en ingeniería pueden ser lineales o no lineales. En un sistema de ecuaciones lineales, ninguna de la cantidades desconocidas está elevada a una potencia o aparece en argumentos dentro de funciones trigonométricas, funciones logarítmicas, raíz cuadrada, etc; esto es, el efecto de cada cantidad desconocida es directamente proporcional. De esta manera, la ecuaciones lineales son más fáciles de resolver que las ecuaciones no lineales. Además, las técnicas usadas para resolver sistemas de ecuaciones lineales son diferentes de las utilizadas para sistemas no lineales.

Comenzaremos repasando conceptos de álgebra matricial y su aplicación a la resolución a sistemas de ecuaciones lineales. Un sistema de ecuaciones puede ser escrito en términos generales usando notación algebraica tradicional. Si tenemos un sistema de n ecuaciones algebraicas lineales simultaneas con n incógnitas, el mismo puede ser representado de la siguiente manera:

a x a x a x a x ba x a x a x a x b

a x a x a x a x b

n n

n n

n n n nn n n

11 1 12 2 13 3 1 1

21 1 22 2 23 3 2 2

1 1 2 2 3 3

+ + + + =+ + + + =

+ + + + =

L

L

LL

L




Dentro de estas ecuaciones, los términos aij y bi representan valores conocidos, mientras que x j representa las variables incógnitas. Los coeficientes aij tienen doble subíndice, el primero (i) represente el número de ecuación (es decir, el número de fila), mientras que el segundo (j) representa el número de incógnitas (es decir, cantidad de columnas).

Notación Matricial Una matriz es un arreglo bidimensional de números. Los elementos de una matriz están caracterizados por un

número de fila y uno de columna. Una matriz de una sola columna se llama vector. El sistema de ecuaciones dado anteriormente queda representado en forma matricial

AX B= donde la matriz A y los vectores X y B vienen dados por

A

a a a aa a a a

a a a a

X

xx

x

B

bb

b

n

n

n n n nn n n

=

=

=

11 12 13 1

21 22 23 2

1 2 3

1

2

1

2

L

L

L L

L

L L

Operaciones Matriciales Multiplicación de Matrices

Si A es una matriz m n× (m filas y n columnas) y B es una matriz n p× , la matriz producto AB C= esta definida por

a b c i m j pik kjk

n

ij=∑ = = =

11 2 1 2, , , , , ,K K

La matriz resultante C contendrá m filas y p columnas. Es requisito para la multiplicación que el número de columnas del primer multiplicando sea igual al número de filas del segundo multiplicando. ( notar que A tiene n columnas y B tiene n filas) Si A es una matriz m n× (m filas y n columnas) y X es un vector que contiene n elementos (n filas), la matriz producto AX B= esta definida por

a x b i mij jj

n

j=∑ = =

11 2, , ,K

de esta forma queda representado el sistema de ecuación lineales. La multiplicación matricial no es conmutativa, esto es, el producto BA no es generalmente igual a AB. Suma y resta de matrices

Dos matrices pueden ser sumadas o restadas si ambas contienen el mismo numero de filas y el mismo numero de columnas. Esto es, si A y B son ambas matrices m n× , la matriz suma A+B=C queda definida por

a b cij ij ij+ = igualmente la diferencia vendrá dada por

a b cij ij ij− = La matriz resultante C tendrá el mismo número de filas y columnas que A y B. Multiplicación escalar Involucra multiplicar cada elemento de una matriz m n× por una constante k , es decir

k a cij ij=

Matrices Especiales Matriz Identidad La matriz identidad es análoga a la constante 1 en el álgebra ordinaria. Es una matriz cuadrada, que contiene n filas y m columnas.




I =

1 0 00 1 00 0 1

Tiene la importante propiedad que IA AI A= = Inversa de una Matriz

Es una matriz cuadrada que contiene n filas y n columnas, con la propiedad A A AA I− −= =1 1

Haciendo usa de la matriz identidad y la matriz inversa podemos resolver un sistema de ecuaciones lineales. Consideremos el siguiente sistema

AX B= Si se premultiplica por A−1 , se obtiene

A AX A B− −=1 1 Pero A AX−1 puede ser escrita como IX X= . Finalmente tenemos

X A B= −1 Se concluye que un sistema de ecuaciones algebraicas simultaneas lineales AX B= puede ser resuelto determinando la inversa, A−1 , de los coeficientes de la matriz A y luego formando el producto A B−1 . El producto resultante será el vector, X , cuyas componentes son las cantidades desconocidas.

Operaciones Matriciales en Excel La operaciones matriciales puede ser fácilmente resueltas en Excel, haciendo uso de arreglos dado que una

matriz es representada como un arreglo en Excel. Un arreglo es un bloque de celdas que se referencia colectivamente, en la misma manera como una única celda. Cualquier operación que es resuelta en el arreglo producirá la misma salida para todas las celdas dentro del arreglo. Además, un arreglo puede ser especificado como un único argumento en una función. Esto es equivalente a especificar las celdas individuales como argumentos múltiples.

Un arreglo se representa como un bloque de celdas cerradas entre corchetes ({}). Por ejemplo, {A1:C3} se refiere a un arreglo que esta compuesto del bloque de celdas extendiéndose desde la celda A1 a la celda C3. Los corchetes son agregados automáticamente por Excel como una parte de la especificación arreglo, es decir es, no se deben incluir los corchetes cuando escribimos un arreglo.

Para especificar una operación matricial dentro de la planilla de Excel se procede de la siguiente manera: 1. Seleccionar el bloque de celdas que representan el arreglo. Luego mover el marcado de celda a la celda

superior izquierda del bloque 2. Ingresar una formula matricial. Se debe incluir el rango de celdas dentro de la formula. Sin embargo,

después de ingresada la fórmula, no se debe cerrar la fórmula con corchetes. 3. Presionar Ctrl-May-Enter. esto causará que la fórmula aparecerá dentro de un par de corchetes, esto

identifica el bloque de celdas como un arreglo. La operación indicada es resuelta. EEjjeerrcciicciioo 55..88:: SSuummaa,, ddiiffeerreenncciiaa yy mmuullttiipplliiccaacciióónn ddee mmaattrriicceess eenn EExxcceell

Se pretende determinar la matriz suma A+B, la matriz diferencia A-B , y el producto 3*A de las siguientes dos matrices:

A B=−

=

−

4 72 5

1 84 4

La planilla de Excel correspondiente se muestra en la Fig. 10, dentro de la planilla los elementos de las matrices A y B fueron ingresado en forma normal. Se han agregado líneas verticales que representan las matrices, utilizando la propiedad borde de celdas del menú formato.




Fig. 10

Para determinar la matriz suma, primero se selecciona el bloque de celdas desde B6 a C7. El marcador de celdas aparecerá sobre la celda B6. luego se ingresa la formula =B3:C4+F3:G4 (sin incluir corchetes) y se presionan las teclas Ctrl-May-Enter simultáneamente. La matriz suma resultante, aparecerá dentro de las celdas B6 a la C7, como se muestra en la Fig. 11.

Fig. 11

Si se observa la formula mostrada en la barra de formula, esta fue ingresada mientras el marcador de celda estaba posicionado en la celda B6, aunque la formula se aplica al bloque entero de celdas. Los corchetes son agregados posteriormente en forma automática, después de presionar Ctrl-May-Enter. Similarmente, la diferencia de matices se determina seleccionando el bloque de celdas desde F6 a G7. La formula =B3:C4-F3:G4 será luego ingresada, sin cerrarla entre corchetes. Los corchetes se agregaran automáticamente. El resultado de la diferencia de matrices se generará en las celdas F6:G7.

Finalmente, el producto escalar se determina seleccionando el bloque de celdas desde B9 a la C10, ingresando las formulas =3*B3:C4, y luego presionando las teclas Ctrl-May-Enter.

Resolución de Ecuaciones Simultaneas por Inversión Matricial en Excel Un camino para resolver el sistema de ecuaciones lineales simultaneas en Excel es usar el método de inversión

matricial descrito anteriormente. Excel incluye una librería de funciones matriciales; MINVERSE, para determinar la inversa de la matriz, MMULT, para resolver la multiplicación matricial. El procedimiento general consiste en determinar primero A−1 usando la función MINVERSE y luego obtener el producto A−1B usando la función MMULT. El resultado obtenido será el vector X.

El procedimiento detallado es el siguiente.




1. Ingresar los elementos de la matriz A en un bloque de celdas de n n× 2. Ingresar el lado derecho de la ecuación B dentro de otro bloque ( una única columna) de n celdas. 3. Identificar donde se desea obtener la matriz A−1 . Debe ser otro bloque de n n× . Luego resaltar este bloque de

celdas, y ingresar la formula =MINVERSA(). Incorporando el rango de los coeficientes de la matriz dentro del paréntesis, por ejemplo, =MINVERSA(A1:B2). Luego presionar Ctrl-Mayt-Enter simultáneamente. Los elementos de A−1 aparecerán dentro del bloque.

4. Identificar donde se desea obtener la solución X. Se debe considerar un bloque de n celdas de una única columna. Luego seleccionar este bloque de celdas y ingresar la fórmula =MMULT(A-1;B). Incorporando el rango de la matriz inversa dentro de los paréntesis seguido por punto y coma, seguido por el rango del lado derecho de la ecuación; por ejemplo, =MMULT(F1:G2,D1:D2). Luego presionar Ctrl-May-Enter. Los elementos de X (valores incógnitas) aparecerán dentro del bloque.

Si se desea se puede verificar la solución calculando el producto original AX y luego comparando el resultado con el lado derecho B. EEjjeerrcciicciioo 55..99

Considerando el siguiente sistema de ecuaciones simultaneas.

234832

21

21

−=−=+

xxxx

Se utilizará Excel para determinar la inversa de la matriz de coeficientes y luego resolver el sistema de ecuaciones. La Fig. 12 muestra una planilla de Excel que contiene la matriz de coeficientes en la celdas A4:B5, y el vector de términos independientes (B) en las celdas G4:G5. Se ha reservado espacio para la matriz inversa ( A−1), el vector solución ( X ), y la matriz producto ( AX ). Estas cantidades serán determinadas a partir de la información dada. Cada matriz o vector ha sido nombrado y puesto entre bordes para hacer mas legible la planilla.

Fig. 12

La Fig. 13 muestra los elementos de la matriz inversa en las celdas D4:E5. Estos valores fueron obtenidos usando la función MINVERSA, como se muestra en la barra de formula.

Fig. 13

En la Fig. 14 se observa el vector solución buscado en las celdas I4;I5.El vector solución se obtienen

multiplicando la inversa de la matriz de coeficientes por el lado derecho de la ecuación; esto es, formando la matriz producto la solución deseada 1A B− . La matriz producto fue obtenida usando la función MMULT, como se muestra en la barra de formula. Se puede observar que la solución buscada es 1 1x = , 2 2x = .




Fig. 14

Finalmente para verificar el resultado obtenido se forma el producto AX y se lo compara con el vector original, B . La Fig. 15 muestra el producto resultante en las celdas K4:k5. Estos valores fueron obtenidos usando la función MMULT, como puede verse en la barra de formula. Evidentemente, los valores calculados coinciden con el vector original B , asegurando que la solución calculada es la correcta.

Fig. 15


Considerando las siguientes matrices cuadradas 6 0 3 5 5 71 4 9 0 9 2

8 5 2 4 1 0A B

− − − = − = − −

Representar cada matriz como un arreglo y resolver las siguientes operaciones matriciales. a) A + B = C b) B + A = C c) A – B = C d) 2A = C


Resolver los siguientes sistemas de ecuaciones simultaneas. En cada caso, verificar si la solución es correcta.

(a) 1 2 3

1 2 3

1 2 3

2 3 173 2 02 3 7

x x xx x xx x x

− + =+ − =+ + =

(b)

1 2 4

1 2 3 4

1 2 3 4

1 2 3

0.1 0.5 2.70.5 2.5 0.4 4.7

0.2 0.1 0.4 3.60.2 0.4 0.2 1.2

x x xx x x xx x x xx x x

− + + =− + − = −+ − + =+ − + =




(c)

1 2 4 5

2 3 5

1 2 3 4

1 3 4 5

1 2 3 4 5

11 3 2 514 2 15

3 2 7 154 4 10 202 5 3 13 92

x x x xx x x

x x x xx x x xx x x x x

+ + + + =+ + + =

+ + + + =+ + + + =+ + + + =

Resolución de sistemas de ecuaciones utilizando SOLVER de Excel La herramienta Solver de Excel representa otro método para obtener las solución de un sistema de ecuaciones.

Este método puede ser usada tanto para sistema lineales como no lineales. Se mostrará a continuación como Solver puede ser usada para resolver un sistema de ecuaciones. Estas ecuaciones no necesitan ser necesariamente lineales. Suponemos un sistema representado como

( )( )

( )

1 1 2

2 1 2

1 2

, , , 0

, , , 0

, , , 0

n

n

n n

f x x x

f x x x

f x x x

=

=

=

K

K

M

K

De esta manera, tenemos un sistema de n ecuaciones con n incógnitas. Se desea encontrar los valores

1 2, , , nx x xK que hacen a cada una de las ecuaciones igual a cero. Un camino para hacer esto es forzar la función

2 2 21 2 ny f f f= + + +K (1)

a cero; esto es, encontrar los valores de 1 2, , , nx x xK que hacen a la ecuación (1) igual a cero. Dado que todos los términos del lado derecho de la ecuación (1) son potencias de dos, serán mayores o iguales a cero. Por consiguiente, el único modo que y puedas ser igual a cero es que cada una de las ecuaciones individuales sean cero. De esta manera,

los valores 1 2, , , nx x xK que hacen a y igual a cero serán la solución del sistema de ecuaciones dado. La estrategia general usada con Solver es definir una función objetivo que consista de la suma de los cuadrados de las ecuaciones individuales, como esta indicada en le ecuación (1), y luego determinar los valores 1 2, , , nx x xK que causan la función objetivo igual a cero. Para resolver el sistema d ecuaciones simultaneas con Solver se procede de la siguiente manera: 1) Ingresar un valor inicial aproximado para cada variable independiente ( 1 2, , , nx x xK ) en celdas separadas dentro

de la planilla de cálculo. 2) Ingresar las ecuaciones para 1 2, , nf f fK y la función objetivo y en celdas separadas, expresadas como

formulas de Excel. Dentro de estas formulas, expresar las cantidades desconocidas 1 2, , , nx x xK como las direcciones de celda que contienen los valores iniciales.

3) Seleccionar la herramienta Solver del menú. 4) En la ventana Parámetros de Solver, ingresar la siguiente información:

(a) La dirección de la celda que contiene la formula para y en el lugar Celda Objetivo. (b) Seleccionar Valores de: en Valor de la celda objetivo. Ingresar 0 dentro del área de ingreso de

datos. (c) Ingresar el rango de celdas que contienen los valores iniciales 1 2, , , nx x xK en el área

denominada Cambiando las celdas. (d) Si se desea agregar restricciones al rango de variables independientes, hacer click en el botón

Agregar bajo el encabezado Sujetas a las siguientes restricciones. Luego ingresar la siguiente información dentro de la ventana Agregar restricción para cada una de las variables independientes: (i) La dirección de la celda que contiene el valor inicial de la variable independiente en el área

Referencia de la celda. (ii) El tipo de restricción ( ,≤ ≥ ) desde el menú desplegable. (iii) El valor limite en el área Restricción (iv) Seleccionar Aceptar para regresar a la ventana Parámetros de Solver o seleccionar Agregar

para una nueva restricción.




(e) Cuando toda la información requerida ha sido ingresada correctamente, seleccionar Resolver. Esto iniciará el procedimiento de resolución.

Una nueva ventana denominada Resultados de Solver mostrará si se ha obtenido una solución al problema. Si una solución ha sido encontrada, los valores obtenidos de las variables independientes se colocarán en las celdas que originariamente contenían los valores iniciales. La celda que contiene la función objetivo mostrará un valor igual a cero o muy cercano a cero. EEjjeerrcciicciioo 55..1122:: RReessoolluucciióónn ddee EEccuuaacciioonneess SSiimmuullttaanneeaass LLiinneeaalleess. Determinar la solución del siguiente sistema de ecuaciones.

1 2 3

1 2 3

1 2 3

3 2 42 3

2 3

x x xx x xx x x

+ − =− + =+ − = −

Para claridad, se referirá a la primera ecuación como ( )1 2 3, ,f x x x , a la segunda como

( )1 2 3, ,g x x x , a la tercera como ( )1 2 3, ,h x x x . Esto es, se puede escribir el sistema dado como

1 2 3

1 2 3

1 2 3

3 2 4 02 3 0

2 3 0

f x x xg x x xh x x x

= + − − == − + − == + − + =

Se desea encontrar los valores de 1 2,x x y 3x que causen ( )1 2 3, ,f x x x , ( )1 2 3, ,g x x x , y ( )1 2 3, ,h x x x sean igual a cero.

Formando la suma 2 2 2y f g h= + +

Si 0f = , 0g = , y 0h = , luego y también será igual a cero. Para cualquier otro valor de ,f g y h (tanto positivos como negativos), sin embargo, y será mayor que cero. Por lo tanto, se puede resolver el sistema de

ecuaciones dado encontrado los valores 1 2,x x y 3x que hacen a y igual a cero. Se usará la herramienta de Excel

Solver para determinar los valores de 1 2,x x y 3x que conducen la función objetivo y a cero.

Fig. 16

La planilla de Excel que contiene la configuración inicial se muestra en la Fig. 16, y la correspondiente formulas de cada celda en la Fig. 17. Las celdas B3,B4, y B5 contienen los valores asumidos inicialmente para

1 2,x x y 3x . La celdas B7, B8, y B9 contienen la formulas para ( )1 2 3, ,f x x x , ( )1 2 3, ,g x x x , y ( )1 2 3, ,h x x x ; y




finalmente la celda B11 contiene la formula para y . Los valores dentro de estas celdas en la Fig. 16 están calculados

con los valores iniciales para 1 2,x x y 3x .

Fig. 17

El próximo paso es seleccionar Solver del menú Herramientas, y luego ingresar la dirección de función objetivo (B11) y el rango de las variables independientes (B3:B5) en la ventana Parámetros de Solver, como se muestra en la Fig. 18. La especificación para la función objetivo es igualarla a cero. Seleccionando el botón Resolver se inicia el procedimiento de solución.

Fig. 18

Una vez que la solución ha sido encontrada, los valores resultantes son mostrados en sus respectivas celdas dentro de la planilla, y la ventana Resultados de Solver aparece, como se muestra en la Fig. 19. La solución del problema corresponde a los valores 1 1x = , 2 2x = y 3 3x = . Los valores correspondientes para ,f g , h y la función objetivo y son aproximadamente cero.




Fig. 19

Las ecuaciones dadas en el ejemplo precedente eran todas lineales. Solver puede también obtener la solución a problemas no lineales siguiendo el mismo procedimiento usado para problemas lineales. Sin embargo, los problemas no lineales puede tener más de una solución, y las soluciones no necesariamente serán reales. Consiguientemente es deseable restringir el rango de las variables independientes, como se ilustra en el siguiente ejemplo. EEjjeerrcciicciioo 55..1133:: RReessoolluucciióónn ddee eeccuuaacciioonneess ssiimmuullttaanneeaass nnoo lliinneeaalleess..

Determinar la solución de las siguientes dos ecuaciones simultaneas no lineales usando Solver. Restringir el rango de las variables independientes a valores positivos.

2 21 2 1 2

2 21 2 1 2

2 5 7 40

3 4 2 28

x x x x

x x x x

+ − + =

− + + =

Se referirá a la primera ecuación como ( )1 2,f x x y a la segunda ecuación como ( )1 2,g x x . Usando esta notación, las ecuaciones dadas pueden ser escritas como

2 21 2 1 2 1 2

2 21 2 1 2 1 2

( , ) 2 5 7 40 0( , ) 3 4 2 28 0

f x x x x x xg x x x x x x

= + − + − == − + + − =

De esta manera, se buscan los valores de 1x y 2x que hacen a ( )1 2,f x x y ( )1 2,g x x igual a cero. Siguiendo con el procedimiento se formará la función objetivo

2 21 2( , )y x x f g= +

Luego se usará Solver para determinar los valores 1x y 2x que fuerzan a la función objetivo y consiguientemente a f y g , igual a cero. Estos valores representarán la solución a las ecuaciones dadas.

La Fig. 20 muestra la planilla de Excel que contiene la expresión del problema inicial. A las variables 1x y 2x se le han asignado el valor inicial de 1, como se muestra en las celdas B3 y B4. Los valores correspondientes a f , g , y a la función objetivo y se muestran en las celdas B6, B7, y B9, respectivamente. Estos valores se obtienen evaluando

las formulas mostradas en la Fig. 21, con los valores iniciales de 1x y 2x .




Fig. 20

Seleccionando Solver del menú Herramientas, resulta la ventana Parámetros de Solver mostrada en la Fig. 22. La dirección de la función objetivo (B9) se ingresa en la casilla superior, seguida por la especificación que la función tome el valor cero. El rango de las variables independientes (B3:B4) es luego ingresado en el área denominada Cambiando las celdas.

Fig. 21




Fig. 22

Para incluir las restricciones de no negatividad, se selecciona el Agregar cerca del fondo de la ventana Parámetros de Solver. La ventana Agregar Restricción aparecerá, como se muestra en la Fig. 23. Dentro de esta ventana, se ingresa la dirección de celda, el tipo de restricción (≤ ,≥ ), y el valor de la restricción (en este caso 0) para cada variable independiente.

Fig. 23

Después que ambas restricción han sido ingresadas, la ventana Parámetros de Solver reaparecerá, con las restricciones incluidas, como se muestra en la Fig. 24. Luego se selecciona el botón Resolver para iniciar el procedimiento de resolución.




Fig. 24

Una vez que la solución has sido encontrada, los valores resultantes son mostrados en las celdas B3 y B4 dentro de la planilla, y la ventana Resultados de Solver aparecerá, como se muestra en la Fig. 25. La solución obtenida para las ecuaciones dadas es aproximadamente 1 2.70x = y 2 3.37x = . Además, los valores correspondientes para las ecuaciones f , g , y la función objetivo y son muy cercanos a cero.

Fig. 25


Resolver los siguientes sistemas de ecuaciones algebraicas no lineales usando la herramienta Solver de Excel.

a) 3

1 22

1 2 1

4 3 20

2 / 50

x x

x x x

− =

+ =




b) 1 1 2

21 2

(2 ) ln ( ) 0.3

ln ( ) cos (3 ) 2

sen x x x

x x

+ = −

− =

c) 1 2 3

1 2 3

1 2 3

sen cos ln 0cos 2ln sen 33ln sen cos 2

x x xx x xx x x

+ − =+ + =− + =

Documents

Resolución de ecuaciones utilizando Excel.pdf