Resolución de ecuaciones utilizando Excel

7/21/2019 Resolucin de ecuaciones utilizando Excel

1/21

Departamento de Ingeniera QumicaGIAIQ (Grupo de Investigacin Aplicada a la Ing. Qumica)rea: Informtica Aplicada a la Ing. Qumica

Utilitarios de Computacin

Docentes:Profesora Asociada: Dra. Marta BasualdoAuxiliar de ctedra: Ing. Javier Francesconi


2/21

Resolucin de ecuaciones conExcel

http:\\www.modeladoeningenieria.edu.ar

Ing. Javier Francesconi Utilitarios de Computacin 2

Resolucin de ecuaciones utilizando Excel

Resolver ecuaciones algebraicas complicadas forma parte de todo curso de ingeniera. Estas ecuaciones puedenrepresentar relaciones de causa-efecto entre variables de un determinado sistema, o pueden ser el resultado de aplicar

principios fsicos a problemas especficos. En ambos casos, el resultado provee generalmente un detallado conocimientodel problema en estudio.

Por ejemplo, la relacin entre presin, volumen y temperatura para muchos gases reales puede ser determinadapor la ecuacin de estado de Van der Waals, la cual viene expresada por:

( )2a

P V b RTV

+ =

donde Pes la presin absoluta, Ves el volumen molar, Tes 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 laecuacin anterior se reduce a la ecuacin de estado del gas ideal. Suponiendo que estamos trabajando con un gas real

donde a y b son conocidos. Si la presin y el volumen son especificados, es muy fcil resolver la ecuacin anteriorpara la temperatura. Similarmente, es fcil resolver la ecuacin anterior para la presin si la temperatura y el volumenson especificados. La dificultad se presenta, sin embargo, al resolver la ecuacin anterior para el volumen si latemperatura y la presin son conocidas. Dado que la ecuacin no es lineal en trminos del volumen (debido al termino

V2

en el denominador del lado izquierdo de la ecuacin). De echo , la solucin involucra encontrar una raz positiva dela ecuacin cbica, la cual requiere algn juicio en la seleccin de la raz correcta.

Caracterizacin de ecuaciones algebraicas no lineales

Repasemos algunos conceptos de lgebra. Una ecuacin se dice lineal si es posible reordenarla de modo que lavariable 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 solouna raz siendo esta siempre real. Las ecuaciones lineales son muy fciles de resolver usando tcnicas de lgebraelemental.

Por otra parte, un ecuacin es no lineal si no se puede reordenar de manera tal que la variable representante dela cantidad desconocida aparezca solo a la primera potencia. Esto es, si x representa una cantidad desconocida, una

ecuacin no lineal puede incluir a x elevado a alguna potencia distinta de uno, o puede estar incluida como logx osen x , etc. Las ecuaciones no lineales generalmente no pueden ser resultas utilizando las tcnicas de lgebra elemental(hay excepciones como la bien conocida formula para obtener las races de la ecuacin cuadrtica). Esta deben serresueltas grfica o numricamente. Adems, stas pueden tener mltiples races reales, o bien presentar racescomplejas.

Una ecuacin polinomial es un caso especifico de ecuacin no lineal. Una ecuacin polinmica puede incluir

potencias dex pero no trminos tales como logx , sen x , xe , etc. La potencia mas alta de x se denomina grado uorden del polinomio.

Las races de las ecuaciones polinomiales presentan las siguientes caractersticas:1. Un polinomio de grado npuede tener no mas de nraces reales.2. Si el grado de un polinomio es impar, siempre habr al menos una raz real.3. Las races complejas siempre existen de a pares. Cada par de races complejas consiste de complejos

conjugados; es decir,x u iv1= + yx u iv2 = .

Por ejemplo, si reordenamos la ecuacin de Van der Wallss obtenemos

PV RT Pb V aV ab3 2 0 + + =( ) observando la ecuacin, podemos concluir que existir al menos una raz real.

Solucin Grfica

Soluciones graficas aproximadas son fciles de obtener, particularmente utilizando una planilla de clculo.

Bsicamente, el procedimiento es escribir un ecuacin de la forma ( )f x = 0y luego graficar f x( )en funcin 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 races reales de la

ecuacin. Generalmente, estos puntos pueden ser ledos directamente desde el grafico o pueden ser interpolados de losvalores tabulados encontrando el punto donde ( )f x = 0.


3/21




Ejjeerrccicio5.1:ResolucciinngrfficaddeunaaEcuuacinPolinoommiall..

Consideremos la ecuacin

f x x x( )= =

2 3 5 0

5 2

tiene al menos una raz real y posiblemente ms. Preparar en Excel una planilla de clculo en la cul ( )f x se tabulacomo funcin de x sobre el intervalo 10 10x . Graficar los datos como un grafico x-y y determinar las racesreales presentes en el intervalo.

La planilla de clculo con los valores tabulados y el correspondiente grafico x-y se muestra en la figu.... Notarque en Excel se usa la frmula, =2*A2^5-3*A2^2-5 para generar el contenido de la celda B2. Esta formula (condirecciones de celdas relativas) se us para generar todo los valores remanentes en la columna B. De los valorestabulados, podemos ver que la funcin 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 estamanera, si se calcula la funcin sobre el intervalo 1 2 x (Fig. 2), se logra observar la raz con mayor exactitud .Los nuevos resultados muestran que la raz es cercana a x=1.4.

Fig. 2


4/21




Resolucin de ecuaciones usando la herramienta Buscar Objetivo de Excel

Veamos como se pueden obtener soluciones ms precisas en forma ms rpida y fcil usando la herramientaBuscar Objetivode Excel. Esta herramienta permite solucionar rpidamente ecuaciones algebraicas usando tcnicasiterativas (prueba y error) de bsqueda de races, basadas en una serie de refinamientos sucesivos derivados de unaaproximacin inicial.

El procedimiento a seguir consiste en:1. Ingresar una valor inicial en una celda de la planilla de clculo.

2. Ingresar una formula para la ecuacin, en la forma ( )f x = 0, en otra celda. Dentro de esta formula, seexpresa la cantidad desconocida x como la direccin de celda conteniendo el valor inicial.

3. Seleccionar la funcin Buscar Objetivodesde el men Herramientas.4. Cuando aparece la ventana Buscar Objetivo, ingresar la siguiente informacin

a) La direccin 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 direccin 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 bsqueda de objetivoaparecer, mostrndonos si Excel

ha sido capaz de resolver el problema. Si una solucin ha sido obtenida, el valor de la raz aparecer en la celda queoriginalmente contena el valor inicial. La celda que contiene la formula mostrar un valor cercano a cero (perogeneralmente no ser exactamente cero). Este ltimo valor tambin aparecer dentro dela ventana Estado de labsqueda de objetivo.

La probabilidad de obtener una solucin mejorar si el valor inicial es lo ms cercano posible a la raz deseada.Es decir, es deseable usar un valor aproximado a la raz como valor inicial, este valor puede ser generalmenteestablecido usando tcnicas graficas o tabulares.

Un mensaje de precaucin se generar si el clculo no converge o si un valor inapropiado (tal como razcuadrada de un numero negativo) esta cercano a generarse durante el curso del computo.

EEjjeerrcciicciioo55..22::RReessoolluucciinnddeeuunnaaeeccuuaacciinn uussaannddoollaahheerrrraammiieennttaaBBuussccaarrOObbjjeettiivvooEn el ejemplo anterior encontramos que la ecuacin f x x x( )= =2 3 5 05 2 tiene una raz real

aproximadamente x=1.4. Haremos uso ahora de la funcin Buscar Objetivopara obtener una solucin ms precisa.

Seleccionaremos el valor de x=1.4 como primera aproximacin dado que ya conocemos que este es un valoraproximado de la raz.

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 numrico -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 Objetivodelmen Herramientas. La Fig. 4 muestra la caja de dialogo Buscar Objetivo. La informacin que ha sido ingresada en laventana indica que el valor de f(x) en la celda B5 ser conducido a cero (o aproximadamente cero) cambiando el valorde x de la celda B3.


5/21




Fig. 4

La solucin resultante se muestra en la Fig. 5. La ventana Estado de la bsqueda de objetivo, la cual hareemplazado la caja de dialogo previa, indica que una solucin 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 solucin deseada, ahora aparece en la

celda B3. Esto es, Excel ha encontrado una solucin en x=1.404085. Este valor es consistente con la solucinaproximada obtenida anteriormente. El correspondiente valor de f(x), -3.6E-5, aparece en la celda B5.

Fig. 5

EEjjeerrcciicciioo55..33Las constantes de Van der Waalss para el dixido de carbono han sido determinadas en a=3.592 litros2atm /

mol2y b=0.04267 litros /mol. Resolver la ecuacin de Van der Waalss para el volumen molar del dixido de carbonobajo las siguientes condiciones:

a) P=1 atm y T=300 Kb) P=10 atm y T=400 Kc) P=0.1 atm y T=300 K

En todos los casos comparar la solucin con la obtenida usando la ley de los gases idealesPV=RT. Recordar queR=0.082054 litros atm /mol k.

Ejjeerrccicio5.4Las constantes de Van der Waalss para un compuesto orgnico son las siguientes a=40.0 litros 2atm / mol2y

b=0.2 litros / mol. Resolver la ecuacin de Estado de Van der Waalss para el volumen molar de este compuesto bajolas siguientes condiciones.

a) P=1 atm, T=300 Kb) P=10 atm, T= 400 Kc) P=0.1 atm , T =300 K

En cada caso, compare la solucin obtenida con los resultados obtenidos para el CO2 y los resultados usando la ley delos gases ideales.


6/21




Resolviendo ecuaciones en Excel usando SOLVER.

Las ecuaciones algebraicas pueden ser tambin resueltas usando la herramienta SOLVER de Excel. La ventajade usar SOLVER es que se pueden especificar restricciones (es decir, condiciones auxiliares) en la variableindependiente. Por ejemplo, se puede indicar que la variable independiente no tome valores negativos o requerir que lavariable independiente se encuentre entre limites definidos. Sin embargo, el procedimiento para el uso del SOLVER esmuy similar al de Buscar Objetivo.

SOLVER es un complemento de Excel, para instalarlo elegir Complementosdel men Herramientas. Luegoseleccionar 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 ecuacin con SOLVER, se procede como sigue.

1. Ingresar un valor inicial en una de las celdas dela planilla de clculo.

2. Ingresar una ecuacin para f(x) en otra celda, expresada como una formula de Excel. Dentro de la formula,expresar la cantidad desconocida x con la direccin de la celda conteniendo el valor inicial.1. Seleccionar Solverdel men Herramientas.2. Cuando la ventana Parmetros de Solveraparezca, ingresar la siguiente informacin.

a) La direccin de la celda que contiene la formula para f(x) en la casilla Celda Objetivo.b) Seleccionar Valores de: en la opcin 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 direccin 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 botn Agregar bajo el encabezado

Sujeta a las siguientes restricciones:. Luego proveer la siguiente informacin dentro la caja de dialogoAgregar Restriccini) La direccin de la celda que contiene el valor inicial para x en el lugar Referencia de la celda:.

ii) Especificar el tipo de restriccin ( es decir, o ) desde el men desplegable.iii) Ingresar el valor lmite en el rea de datos Restriccin. Luego seleccin Aceptarpara regresar ala ventana Parmetros de Solver. Se pueden cambiar o borrar cualquiera de las restriccionesdespus que han sido incorporadas.

e) Cuando toda la informacin ha sido ingresada correctamente, seleccionar Resolver. Este iniciar elprocedimiento de clculo.

Una nueva ventana denominada Resultados de Solver aparecer, diciendo si SOLVER ha sido capaz deresolver el problema. Si la solucin ha sido obtenida, el valor de la raz aparecer en la celda original que contena elvalor inicial. La celda que contiene la frmula mostrar un valor que es cero o muy cercano a cero.

Se debe comprender que SOLVER y Buscar Objetivo usan diferente procedimientos matemticos paraalcanzar la solucin.

EEjjeerrcciicciioo55..55::RReessoolluucciinnddeeuunnaaeeccuuaacciinnppoolliinnoommiiaalluuttiilliizzaannddooSSOOLLVVEERREl ejemplo previo se resolvi la ecuacin polinomialf x x x( )= =2 3 5 05 2


7/21




y se encontr una raz real positiva cercana a x=1.4. Se utilizar ahora la utilidad Solver par encontrar la raz de dichaecuacin con el agregado de una restriccin,x 0 .

La Fig. muestra la planilla de Excel que contiene el valor inicial en la celda B3 y una formula para la ecuacindada en la celda B5. Se puede ver la ventana Parmetros de Solverla cual se obtiene seleccionando Solverdesde elmen Herramientas. La direccin de la celda de la ecuacin (B5) ha sido ingresada en el rea Celda Objetivo. Notar

que en la opcin Valor de la celda Objetivose ingresa el valor Valores de: 0. La direccin de celda que contiene elvalor inicial (B3) se ingresa en la opcin Cambiando las Celdas. Falta ingresar la restriccin en x, para que no tomevalores negativos.

Fig. 7

Para agregar la restriccin, debemos seleccionar el botn Agregar dentro de la seccin Sujetas a lassiguientes restricciones. Se ingresa la direccin de la celda de la variable independiente (B3) en el rea de datosizquierda, se seleccionar la restriccin en el centro del rea de datos, y finalmente proveer el valor lmite (0) en el ladoderecho. Una vez que las restriccin ha sido ingresadas correctamente, seleccionar el botn Aceptar al pie de laventana y se regresa la ventana Parmetros de Solver.

Fig. 8

Seleccionando Resolver desde la ventana anterior se inicia el procedimiento de resolucin. Obtenida lasolucin, los valores resultantes aparecen en las celdas B3 y B5,. La solucin deseada es x=1.404186, resultando unvalor para f(x)=3.59x10-8. Adems, la ventana Resultados de Solver remplazar la ventanaParmetros de Solver.

Nuestra respuesta la ventana actual es Utilizar solucin de Solver seguido por Aceptar. Ninguna de las opcionesInformes oguardar escenario deben ser seleccionadas dado que se alcanzado la solucin buscada. SOLVER incluyeun nmero de caractersticas adicionales que sern discutidas en prximos captulos.


8/21




Fig. 9

EEjjeerrcciicciioo55..66Resolver la ecuacin de Van de Waals para el volumen molar (V) de un compuesto orgnico a 10 atm de

presin y 400K. Las constantes son a=40.0 litros2 atm / mol2 y b=0.2 litros/mol . Comparar la respuesta con losresultados obtenidos anteriormente.

EEjjeerrcciicciioo55..77Para adquirir determinados bienes de valor se utilizan crditos bancarios. El crdito otorgado por una entidad

bancaria es devuelto por el consumidor pagando una cuota mensual fija por un perodo de tiempo determinado, la cualincluye los intereses correspondientes. Si P es la cantidad total del prstamo inicial, la cantidad del monto a pagarmensualmente A puede ser determinada por

A P i i

i

n

n=

+

+

( )

( )

1

1 1

donde i es la tasa de inters mensual, expresado en forma fraccional (no como porcentaje), y n es el numero total depagos.

Suponiendo un prstamo de $ 10.000a) Si la tasa de inters anual es del 8 % (equivalente a una tasa de inters mensual de 0.08/12=0.006667) y el

prstamo 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 inters mensual correspondiente.c) Si se desea pagar $350 cada mes a una tasa de inters mensual de 0.006667, que cantidad de pagos

(cuantos meses) sern necesarios para pagar el prstamo?d) En cuales de las opciones anteriores ser necesario el uso de la funcin Solver de Excel.

Resolucin de sistemas de ecuaciones

Las ecuaciones algebraicas simultaneas surgen en muchas aplicaciones diferentes. Hay muchas aplicacionesen diversos campos como conduccin del calor, mecnica del slido, circuitos elctricos, difusin molecular, ymecnica de fluidos. De hecho, la resolucin de ecuaciones algebraicas simultneas es una de las tcnicas mascomnmente utilizadas en el campo de la ingeniera.

Los sistemas de ecuaciones simultneas que se presentan en ingeniera pueden ser linealeso no lineales. En un

sistema de ecuaciones lineales, ninguna de la cantidades desconocidas est elevada a una potencia o aparece enargumentos dentro de funciones trigonomtricas, funciones logartmicas, raz cuadrada, etc; esto es, el efecto de cadacantidad desconocida es directamente proporcional. De esta manera, la ecuaciones lineales son ms fciles de resolverque las ecuaciones no lineales. Adems, las tcnicas usadas para resolver sistemas de ecuaciones lineales son diferentesde las utilizadas para sistemas no lineales.

Comenzaremos repasando conceptos de lgebra matricial y su aplicacin a la resolucin a sistemas deecuaciones lineales. Un sistema de ecuaciones puede ser escrito en trminos generales usando notacin algebraicatradicional. Si tenemos un sistema de n ecuaciones algebraicas lineales simultaneas con n incgnitas, el mismo puedeser representado de la siguiente manera:

a x a x a x a x b

a 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


9/21




Dentro de estas ecuaciones, los trminos aij y bi representan valores conocidos, mientras que xj representa

las variables incgnitas. Los coeficientes aij tienen doble subndice, el primero (i) represente el nmero de ecuacin (es

decir, el nmero de fila), mientras que el segundo (j) representa el nmero de incgnitas (es decir, cantidad decolumnas).

Notacin Matricial

Una matriz es un arreglo bidimensional de nmeros. Los elementos de una matriz estn caracterizados por unnmero de fila y uno de columna. Una matriz de una sola columna se llama vector. El sistema de ecuaciones dadoanteriormente queda representado en forma matricial

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

A

a a a a

a a a a

a a a a

X

x

x

x

B

b

b

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

Multiplicacin de Matrices

Si Aes una matriz m n (m filas y n columnas) y B es una matriz n p , la matriz producto AB C= estadefinida por

a b c i m j pik kjk

n

ij

=

= = =1

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

La matriz resultante Ccontendr mfilas y p columnas.

Es requisito para la multiplicacin que el nmero de columnas del primer multiplicando sea igual al nmero de filas delsegundo 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

=

= =1

1 2, , ,K

de esta forma queda representado el sistema de ecuacin lineales.La multiplicacin 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 numerode columnas. Esto es, si Ay 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 nmero de filas y columnas que A y B.

Multiplicacin escalar

Involucra multiplicar cada elemento de una matriz m n por una constante k, es decirk a cij ij=

Matrices Especiales

Matriz IdentidadLa matriz identidad es anloga a la constante 1 en el lgebra ordinaria. Es una matriz cuadrada, que contiene n filas y mcolumnas.


10/21




I=

1 0 0

0 1 0

0 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 A1 , se obtiene

A AX A B =1 1 Pero A AX1 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 determinandola inversa, A1 , de los coeficientes de la matriz Ay luego formando el producto A B1 . El producto resultante ser elvector, X, cuyas componentes son las cantidades desconocidas.

Operaciones Matriciales en Excel

La operaciones matriciales puede ser fcilmente resueltas en Excel, haciendo uso de arreglos dado que unamatriz 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 operacin que es resuelta en el arreglo producir la misma salida

para todas las celdas dentro del arreglo. Adems, un arreglo puede ser especificado como un nico argumento en unafuncin. Esto es equivalente a especificar las celdas individuales como argumentos mltiples.

Un arreglo se representa como un bloque de celdas cerradas entre corchetes ({}). Por ejemplo, {A1:C3} serefiere a un arreglo que esta compuesto del bloque de celdas extendindose desde la celda A1 a la celda C3. Los

corchetes son agregados automticamente por Excel como una parte de la especificacin arreglo, es decir es, no sedeben incluir los corchetes cuando escribimos un arreglo.

Para especificar una operacin 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 bloque2. Ingresar una formula matricial. Se debe incluir el rango de celdas dentro de la formula. Sin embargo,

despus de ingresada la frmula, no se debe cerrar la frmula con corchetes.3. Presionar Ctrl-May-Enter. esto causar que la frmula aparecer dentro de un par de corchetes, esto

identifica el bloque de celdas como un arreglo. La operacin indicada es resuelta.

Ejjeerrccicio5.8:Sumaa,diiffeerrencciiaymullttipplicacciinndemaatrricesennExxcceelSe 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 7

2 5

1 8

4 4

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


11/21




Fig. 10

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

Fig. 11

Si se observa la formula mostrada en la barra de formula, esta fue ingresada mientras el marcador de celdaestaba posicionado en la celda B6, aunque la formula se aplica al bloque entero de celdas. Los corchetes son agregados

posteriormente en forma automtica, despus de presionar Ctrl-May-Enter. Similarmente, la diferencia de matices sedetermina seleccionando el bloque de celdas desde F6 a G7. La formula =B3:C4-F3:G4 ser luego ingresada, sincerrarla entre corchetes. Los corchetes se agregaran automticamente. El resultado de la diferencia de matrices segenerar en las celdas F6:G7.

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

Resolucin de Ecuaciones Simultaneas por Inversin Matricial en Excel

Un camino para resolver el sistema de ecuaciones lineales simultaneas en Excel es usar el mtodo de inversinmatricial descrito anteriormente. Excel incluye una librera de funciones matriciales; MINVERSE, para determinar lainversa de la matriz, MMULT, para resolver la multiplicacin matricial. El procedimiento general consiste en

determinar primero A

1

usando la funcin MINVERSE y luego obtener el producto A

1

B usando la funcin MMULT.El resultado obtenido ser el vector X.

El procedimiento detallado es el siguiente.


12/21




1. Ingresar los elementos de la matriz A en un bloque de celdas de n n 2. Ingresar el lado derecho de la ecuacin B dentro de otro bloque ( una nica columna) de n celdas.

3. Identificar donde se desea obtener la matriz A1. 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 delparntesis, por ejemplo, =MINVERSA(A1:B2). Luego presionar Ctrl-Mayt-Enter simultneamente. Los

elementos de A1 aparecern dentro del bloque.4. Identificar donde se desea obtener la solucin X. Se debe considerar un bloque de n celdas de una nica

columna. Luego seleccionar este bloque de celdas y ingresar la frmula =MMULT(A-1;B). Incorporando elrango de la matriz inversa dentro de los parntesis seguido por punto y coma, seguido por el rango del ladoderecho de la ecuacin; por ejemplo, =MMULT(F1:G2,D1:D2). Luego presionar Ctrl-May-Enter. Loselementos de X (valores incgnitas) aparecern dentro del bloque.

Si se desea se puede verificar la solucin calculando el producto original AX y luego comparando el resultado conel lado derecho B.

EEjjeerrcciicciioo55..99Considerando el siguiente sistema de ecuaciones simultaneas.

234

832

21

21

=

=+

xx

xx

Se utilizar Excel para determinar la inversa de la matriz de coeficientes y luego resolver el sistema deecuaciones. La Fig. 12 muestra una planilla de Excel que contiene la matriz de coeficientes en la celdas A4:B5, y el

vector de trminos independientes (B) en las celdas G4:G5. Se ha reservado espacio para la matriz inversa (A1 ), elvector solucin (X), y la matriz producto (AX). Estas cantidades sern determinadas a partir de la informacin 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 obtenidosusando la funcin MINVERSA, como se muestra en la barra de formula.

Fig. 13

En la Fig. 14 se observa el vector solucin buscado en las celdas I4;I5.El vector solucin se obtienenmultiplicando la inversa de la matriz de coeficientes por el lado derecho de la ecuacin; esto es, formando la matriz

producto la solucin deseada1A B . La matriz producto fue obtenida usando la funcin MMULT, como se muestra en

la barra de formula. Se puede observar que la solucin buscada es 1 1x = , 2 2x = .


13/21




Fig. 14

Finalmente para verificar el resultado obtenido se forma el producto AX y se lo compara con el vectororiginal, B . La Fig. 15 muestra el producto resultante en las celdas K4:k5. Estos valores fueron obtenidos usando lafuncin MMULT, como puede verse en la barra de formula. Evidentemente, los valores calculados coinciden con el

vector original B , asegurando que la solucin calculada es la correcta.

Fig. 15

EEjjeerrcciicciioo55..1100Considerando las siguientes matrices cuadradas

6 0 3 5 5 7

1 4 9 0 9 28 5 2 4 1 0

A B

= =

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

b) B + A = Cc) A B = Cd) 2A = C

EEjjeerrcciicciioo55..1111Resolver los siguientes sistemas de ecuaciones simultaneas. En cada caso, verificar si la solucin es correcta.

(a)

1 2 3

1 2 3

1 2 3

2 3 17

3 2 02 3 7

x x x

x x xx x x

+ =

+ =+ + =

(b)

1 2 4

1 2 3 4

1 2 3 4

1 2 3

0.1 0.5 2.7

0.5 2.5 0.4 4.7

0.2 0.1 0.4 3.6

0.2 0.4 0.2 1.2

x x x

x x x x

x x x x

x x x

+ + =

+ =

+ + =

+ + =


14/21




(c)

1 2 4 5

2 3 5

1 2 3 4

1 3 4 5

1 2 3 4 5

11 3 2 51

4 2 15

3 2 7 15

4 4 10 20

2 5 3 13 92

x x x x

x x x

x x x x

x x x x

x x x x x

+ + + + =

+ + + =

+ + + + =

+ + + + =

+ + + + =

Resolucin de sistemas de ecuaciones utilizando SOLVER de Excel

La herramienta Solver de Excel representa otro mtodo para obtener las solucin de un sistema de ecuaciones.Este mtodo puede ser usada tanto para sistema lineales como no lineales. Se mostrar a continuacin 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 incgnitas. 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 funcin

2 2 2

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

a cero; esto es, encontrar los valores de 1 2, , , nx x xK que hacen a la ecuacin (1) igual a cero. Dado que todos los

trminos del lado derecho de la ecuacin (1) son potencias de dos, sern mayores o iguales a cero. Por consiguiente, elnico modo que y puedas ser igual a cero es que cada una de las ecuaciones individuales sean cero. De esta manera,

los valores1 2

, , ,n

x x xK que hacen ay igual a cero sern la solucin del sistema de ecuaciones dado. La estrategia

general usada con Solver es definir una funcin objetivo que consista de la suma de los cuadrados de las ecuaciones

individuales, como esta indicada en le ecuacin (1), y luego determinar los valores 1 2, , , nx x xK que causan la funcin

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 clculo.

2) Ingresar las ecuaciones para 1 2, , nf f fK y la funcin 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 Parmetros de Solver, ingresar la siguiente informacin:(a) La direccin 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 dedatos.

(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 botn

Agregar bajo el encabezado Sujetas a las siguientes restricciones. Luego ingresar la siguienteinformacin dentro de la ventana Agregar restriccin para cada una de las variables independientes:(i) La direccin de la celda que contiene el valor inicial de la variable independiente en el rea

Referencia de la celda.

(ii) El tipo de restriccin ( , ) desde el men desplegable.(iii) El valor limite en el rea Restriccin(iv) Seleccionar Aceptar para regresar a la ventana Parmetros de Solver o seleccionar Agregar

para una nueva restriccin.


15/21




(e) Cuando toda la informacin requerida ha sido ingresada correctamente, seleccionar Resolver.Esto iniciar el procedimiento de resolucin.

Una nueva ventana denominada Resultados de Solver mostrar si se ha obtenido una solucin alproblema. Si una solucin ha sido encontrada, los valores obtenidos de las variables independientes se colocarn en lasceldas que originariamente contenan los valores iniciales. La celda que contiene la funcin objetivo mostrar un valor

igual a cero o muy cercano a cero.

EEjjeerrcciicciioo55..1122::RReessoolluucciinnddeeEEccuuaacciioonneessSSiimmuullttaanneeaassLLiinneeaalleess.Determinar la solucin del siguiente sistema de ecuaciones.

1 2 3

1 2 3

1 2 3

3 2 4

2 3

2 3

x x x

x x x

x x x

+ =

+ =

+ =

Para claridad, se referir a la primera ecuacin 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 0

2 3 0

2 3 0

f x x x

g x x x

h 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 acero.

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

Si 0f = , 0g= , y 0h= , luego y tambin ser igual a cero. Para cualquier otro valor de ,f gy h (tanto

positivos como negativos), sin embargo, y ser mayor que cero. Por lo tanto, se puede resolver el sistema deecuaciones 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 funcin objetivo y a cero.

Fig. 16

La planilla de Excel que contiene la configuracin inicial se muestra en la Fig. 16, y la correspondienteformulas 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


16/21




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

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

Fig. 17

El prximo paso es seleccionar Solver del men Herramientas, y luego ingresar la direccin de funcinobjetivo (B11) y el rango de las variables independientes (B3:B5) en la ventana Parmetros de Solver, como se muestraen la Fig. 18. La especificacin para la funcin objetivo es igualarla a cero. Seleccionando el botn Resolver se inicia el

procedimiento de solucin.

Fig. 18

Una vez que la solucin ha sido encontrada, los valores resultantes son mostrados en sus respectivas celdasdentro de la planilla, y la ventana Resultados de Solver aparece, como se muestra en la Fig. 19. La solucin del

problema corresponde a los valores 1 1x = , 2 2x = y 3 3x = . Los valores correspondientes para ,f g, h y la funcinobjetivo y son aproximadamente cero.


17/21




Fig. 19

Las ecuaciones dadas en el ejemplo precedente eran todas lineales. Solver puede tambin obtener la solucin aproblemas no linealessiguiendo el mismo procedimiento usado para problemas lineales. Sin embargo, los problemas nolineales puede tener ms de una solucin, y las soluciones no necesariamente sern reales. Consiguientemente esdeseable restringir el rango de las variables independientes, como se ilustra en el siguiente ejemplo.

Ejjeerrccicio5.13::Resoluccinnddeeccuuacciioonessiimmultaaneeaassnoolliinneaalleess.Determinar la solucin de las siguientes dos ecuaciones simultaneas no lineales usando Solver. Restringir el

rango de las variables independientes a valores positivos.2 2

1 2 1 2

2 2

1 2 1 2

2 5 7 40

3 4 2 28

x x x x

x x x x

+ + =

+ + =

Se referir a la primera ecuacin como ( )1 2,f x x y a la segunda ecuacin como ( )1 2,g x x . Usando estanotacin, las ecuaciones dadas pueden ser escritas como

2 2

1 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 x

g 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 funcin objetivo

2 2

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

Luego se usar Solver para determinar los valores 1x y 2x que fuerzan a la funcin objetivo y

consiguientemente a f y g, igual a cero. Estos valores representarn la solucin a las ecuaciones dadas.

La Fig. 20 muestra la planilla de Excel que contiene la expresin 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 funcin 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 .


18/21




Fig. 20

Seleccionando Solver del men Herramientas, resulta la ventana Parmetros de Solver mostrada en la Fig. 22.La direccin de la funcin objetivo (B9) se ingresa en la casilla superior, seguida por la especificacin que la funcintome el valor cero. El rango de las variables independientes (B3:B4) es luego ingresado en el rea denominadaCambiando las celdas.

Fig. 21


19/21




Fig. 22

Para incluir las restricciones de no negatividad, se selecciona el Agregar cerca del fondo de la ventanaParmetros de Solver. La ventana Agregar Restriccin aparecer, como se muestra en la Fig. 23. Dentro de esta

ventana, se ingresa la direccin de celda, el tipo de restriccin ( , ), y el valor de la restriccin (en este caso 0) paracada variable independiente.

Fig. 23

Despus que ambas restriccin han sido ingresadas, la ventana Parmetros de Solver reaparecer, con las

restricciones incluidas, como se muestra en la Fig. 24. Luego se selecciona el botn Resolver para iniciar elprocedimiento de resolucin.


20/21




Fig. 24

Una vez que la solucin has sido encontrada, los valores resultantes son mostrados en las celdas B3 y B4dentro de la planilla, y la ventana Resultados de Solver aparecer, como se muestra en la Fig. 25. La solucin obtenida

para las ecuaciones dadas es aproximadamente 1 2.70x = y 2 3.37x = . Adems, los valores correspondientes para

las ecuaciones f ,g, y la funcin objetivo y son muy cercanos a cero.

Fig. 25

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

a)

31 2

2

1 2 1

4 3 20

2 / 50

x x

x x x

=

+ =


21/21



Ing Javier Francesconi Utilitarios de Computacin 21

b)1 1 2

2

1 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 0

cos 2ln sen 3

3ln sen cos 2

x x x

x x x

x x x

+ =

+ + =

+ =

Documents

Resolución de ecuaciones utilizando Excel