Resolución do sistema de ecuacións en Microsoft Excel

A capacidade de resolver sistemas de ecuacións a miúdo pode ser útil non só no estudo, senón tamén na práctica. Ao mesmo tempo, non todos os usuarios de PC saben que Excel ten as súas propias opcións para resolver ecuacións lineais. Descubrimos como usar este kit de ferramentas do procesador de táboas para realizar esta tarefa de varias maneiras.

Opcións de decisión

Calquera ecuación pode considerarse resolta só cando se atopan as súas raíces. Excel ten varias opcións para atopar raíces. Vexamos cada un deles.

Método 1: método de matriz

O xeito máis común de resolver un sistema de ecuacións lineais con ferramentas Excel é empregar o método matricial. Consiste en construír unha matriz de coeficientes de expresión e logo en crear unha matriz inversa. Tratemos de usar este método para resolver o seguinte sistema de ecuacións:


14x1+2x2+8x4=218
7x1-3x2+5x3+12x4=213
5x1+x2-2x3+4x4=83
6x1+2x2+x3-3x4=21

1. Enchemos a matriz con números, que son os coeficientes da ecuación. Estes números deberán estar ordenados de xeito secuencial, tendo en conta a situación de cada raíz á que corresponden. Se nunha expresión unha das raíces está ausente, entón neste coeficiente considérase igual a cero. Se o coeficiente non está indicado na ecuación, pero hai unha raíz correspondente, considérase que o é 1. Denota a táboa resultante como vector A.



2. Por separado, escribe os valores despois do signo igual. Denomínalos co seu nome común, como vector B.



3. Agora, para atopar as raíces da ecuación, primeiro temos que atopar a matriz inversa da existente. Afortunadamente, Excel ten un operador especial deseñado para solucionar este problema. É chamado MOBR. Ten unha sintaxe bastante sinxela:

   = MOBR (matriz)

   Argumento Array - Esta é, de feito, o enderezo da táboa de orixe.

   Así, seleccionamos na folla unha rexión de celas baleiras, que é de tamaño igual ao rango da matriz orixinal. Fai clic no botón "Función de inserción"situado preto da liña de fórmulas.



4. Posta en marcha Asistentes de funcións. Vai á categoría "Matemático". Na lista que apareza, busque o nome MOBR. Despois de atopalo, seleccióneo e fai clic no botón "Está ben".



5. Iníciase a xanela do argumento da función MOBR. Só ten un campo no número de argumentos: Array. Aquí necesitas especificar o enderezo da nosa táboa. Para estes efectos, estableza o cursor neste campo. Despois mantemos presionado o botón esquerdo do rato e seleccionamos a área da folla na que se atopa a matriz. Como podes ver, os datos das coordenadas da colocación introdúcense automaticamente no campo da xanela. Despois de completar esta tarefa, o máis obvio sería facer clic no botón "Está ben"pero non te apresuras. O feito é que facer clic neste botón equivale a usar o comando Introduza. Pero cando se traballa con matrices despois de completar a entrada da fórmula, non faga clic no botón Introduzae faga un conxunto de atallos de teclado Ctrl + Maiúscula + Intro. Realiza esta operación.



6. Así, despois disto, o programa realiza os cálculos e, na saída da área seleccionada anteriormente, temos unha matriz inversa á dada.



7. Agora necesitaremos multiplicar a matriz inversa pola matriz B, que consta dunha columna de valores situada despois do signo igual en expresións. Para multiplicar as táboas en Excel tamén se chama unha función separada MÚLTIPLE. Esta afirmación ten a seguinte sintaxis:

   = MULTIPLE (Array1; Array2)

   Seleccionamos o rango, no noso caso, composto por catro celas. A continuación, volva a executar Asistente de característicasfacendo clic na icona "Función de inserción".



8. En categoría "Matemático"lanzado Asistentes de funcións, selecciona o nome MUMNOZH e fai clic no botón "Está ben".



9. A xanela do argumento da función está activada. MÚLTIPLE. No campo "Array1" introduza as coordenadas da nosa matriz inversa. Para iso, como a última vez, configure o cursor no campo e co botón esquerdo do rato presionado selecciona a táboa correspondente co cursor. Realizamos unha acción similar para introducir coordenadas no campo Array2, só esta vez selecciona os valores da columna B. Despois de que se realicen as accións anteriores, de novo non temos présa para presionar o botón "Está ben" ou clave Introduzae escriba unha combinación de teclas Ctrl + Maiúscula + Intro.



10. Tras esta acción, as raíces da ecuación móstranse na cela seleccionada anteriormente: X1, X2, X3 e X4. Serán dispostos en serie. Así, podemos dicir que resolvemos este sistema. Para verificar a corrección da solución, é suficiente substituír estas respostas ao sistema de expresión orixinal no canto das raíces correspondentes. Se se observa a igualdade, isto significa que o sistema de ecuacións presentado resólvese correctamente.

Lección: Matriz inversa en Excel

Método 2: selección de parámetros

A segunda forma coñecida de resolver o sistema de ecuacións en Excel é usando o método de selección de parámetros. A esencia deste método é buscar desde o contrario. É dicir, en función dun resultado coñecido, buscamos un argumento descoñecido. Utilizemos a ecuación cuadrática como exemplo

3x ^ 2 + 4x-132 = 0

1. Aceptar o valor x por igual 0. Calculamos o valor que lle corresponde f (x)aplicando a seguinte fórmula:

   = 3 * x ^ 2 + 4 * x-132

   No canto de valor "X" substitúa a dirección da cela onde está situado o número 0tomado por nós para x.



2. Vai á lapela "Datos". Fai clic no botón "E se a análise". Este botón está situado na cinta da caixa de ferramentas. "Traballa con datos". Ábrese unha lista despregable. Elixe unha posición nel "Selección de parámetros ...".



3. Iníciase a xanela de selección de parámetros. Como podes ver, consta de tres campos. No campo Establecido na cela especifique a dirección da cela na que se atopa a fórmula f (x)calculado por nós un pouco antes. No campo "Valor" introduza o número "0". No campo "Cambiar os valores" especifique a dirección da cela na que está situado o valor xpreviamente aceptado por nós para 0. Despois de realizar estes pasos, faga clic no botón "Está ben".



4. Despois diso, Excel realizará o cálculo seleccionando un parámetro. Isto informará pola xanela de información aparecida. Nela fai clic no botón "Está ben".



5. O resultado do cálculo da raíz da ecuación estará na cela que asignamos no campo "Cambiar os valores". No noso caso, como vemos, x será igual 6.

Este resultado tamén se pode comprobar substituíndo este valor na expresión a resolver no canto do valor x.

Lección: Selección de parámetros en Excel

Método 3: Método de cramer

Agora imos tratar de resolver o sistema de ecuacións mediante o método Cramer. Por exemplo, tome o mesmo sistema que se usou en Método 1:


14x1+2x2+8x4=218
7x1-3x2+5x3+12x4=213
5x1+x2-2x3+4x4=83
6x1+2x2+x3-3x4=21

1. Como no primeiro método, compoñemos unha matriz A a partir dos coeficientes das ecuacións e da táboa B a partir dos valores que seguen o signo igual.



2. A continuación, facemos catro táboas máis. Cada unha delas é unha copia da matriz. A, só estas copias teñen unha columna substituída por unha táboa B. A primeira táboa ten a primeira columna, a segunda táboa ten a segunda, etc.



3. Agora necesitamos calcular os determinantes para todas estas táboas. O sistema de ecuacións só terá solucións se todos os determinantes teñen un valor distinto de cero. Para calcular este valor, Excel ten de novo unha función separada - MOPRED. A sintaxis desta afirmación é a seguinte:

   = MOPRED (matriz)

   Así, como a función MOBR, o único argumento é a referencia á táboa que se está a procesar.

   Así, selecciona a cela na que se mostrará o determinante da primeira matriz. A continuación, fai clic no botón familiar dos métodos anteriores "Función de inserción".



4. A xanela está activada Asistentes de funcións. Vai á categoría "Matemático" e entre a lista de operadores destacamos o nome MOPRED. Despois diso, faga clic no botón "Está ben".



5. Iníciase a xanela do argumento da función MOPRED. Como podes ver, só ten un campo: Array. Neste campo introducimos a dirección da primeira matriz transformada. Para iso, estableza o cursor no campo e seleccione o intervalo de matrices. Despois diso, faga clic no botón "Está ben". Esta función mostra o resultado nunha cela, non nunha matriz, polo que para obter o cálculo non é necesario recorrer a presionar unha combinación de teclas. Ctrl + Maiúscula + Intro.



6. A función calcula o resultado e móstrao nunha cela pre-seleccionada. Como vemos, no noso caso o determinante é -740é dicir, non é igual a cero, o que nos convén.



7. Do mesmo xeito, calculamos os determinantes para as outras tres táboas.



8. Na fase final, calculamos o determinante da matriz primaria. O procedemento desenvólvese segundo o mesmo algoritmo. Como podes ver, o determinante da táboa primaria tamén é cero, o que significa que a matriz é considerada non dexenerada, é dicir, o sistema de ecuacións ten solucións.



9. Agora toca atopar as raíces da ecuación. A raíz da ecuación será igual á relación do determinante da matriz transformada correspondente ao determinante da táboa primaria. Así, dividindo á súa vez os catro determinantes das matrices transformadas polo número -148, que é o determinante da táboa orixinal, obtemos catro raíces. Como podes ver, son iguais aos valores 5, 14, 8 e 15. Así coinciden exactamente coas raíces que atopamos empregando a matriz inversa en método 1, o que confirma a corrección da solución do sistema de ecuacións.

Método 4: Método Gauss

O sistema de ecuacións tamén se pode resolver aplicando o método Gauss. Por exemplo, tome un sistema máis sinxelo de ecuacións a partir de tres incógnitas:


14x1+2x2+8x3=110
7x1-3x2+5x3=32
5x1+x2-2x3=17

1. Unha vez máis, anotamos os coeficientes nunha táboa Ae os termos gratuítos localizados despois do signo igual - para a mesa B. Pero esta vez, achegaremos ambas as táboas, xa que o necesitaremos para que funcione no futuro. Unha condición importante é que na primeira cela da matriz A o valor era diferente. Se non, debes reorganizar as liñas en lugares.



2. Copie a primeira liña de dúas matrices conectadas á liña seguinte (para maior claridade, pode saltar unha liña). Na primeira cela, que está situada na liña incluso inferior á anterior, introducimos a seguinte fórmula:

   = B8: E8- $ B $ 7: $ E $ 7 * (B8 / $ B $ 7)

   Se dispuxes as matrices doutro xeito, as direccións das células de fórmulas terán un significado diferente, pero podes calculalas comparalas coas fórmulas e imaxes que se dan aquí.

   Despois de introducir a fórmula, seleccione toda a fila de celas e prema a combinación de teclas Ctrl + Maiúscula + Intro. Aplicarase unha fórmula de matriz á liña e encherase de valores. Así, subtraemos da segunda liña a primeira, multiplicada pola relación dos primeiros coeficientes das dúas primeiras expresións do sistema.



3. Despois diso, copia a cadea resultante e pégaa na liña seguinte.



4. Seleccione as dúas primeiras liñas despois da liña que falta. Fai clic no botón Copiarsituado na cinta da lapela "Casa".



5. Saltamos a liña despois do último rexistro da folla. Seleccione a primeira cela na seguinte liña. Fai clic co botón dereito do rato No menú contextual que se abre, mova o cursor a "Inserción especial". Na lista adicional lanzada, seleccione a posición "Valores".



6. Na seguinte liña, introduza a fórmula de matriz. Resta da terceira fila o grupo de datos anterior da segunda fila, multiplicado pola relación do segundo coeficiente da terceira e segunda filas. No noso caso, a fórmula terá a seguinte forma:

   = B13: E13- $ B $ 12: $ E $ 12 * (C13 / $ C $ 12)

   Despois de introducir a fórmula, seleccione toda a liña e use o atallo de teclado Ctrl + Maiúscula + Intro.



7. Agora debes realizar unha marcha atrás segundo o método Gauss. Saltamos tres liñas do último disco. Na cuarta liña introducimos a fórmula da matriz:

   = B17: E17 / D17

   Así, dividimos a última liña calculada por nós polo seu terceiro coeficiente. Despois de escribir a fórmula, seleccione toda a liña e prema a combinación de teclas Ctrl + Maiúscula + Intro.



8. Subimos a unha liña e introducimos a seguinte fórmula de matriz:

   = (B16: E16-B21: E21 * D16) / C16

   Prememos o atallo de teclado habitual para aplicar a fórmula de matriz.



9. Subimos unha liña máis arriba. Para el, introducimos a fórmula de matriz do seguinte formulario:

   = (B15: E15-B20: E20 * C15-B21: E21 * D15) / B15

   De novo selecciona toda a liña e aplica o atallo de teclado Ctrl + Maiúscula + Intro.



10. Agora miramos os números que apareceron na última columna do último bloque de filas que calculamos anteriormente. Trátase destes números (4, 7 e 5) serán as raíces deste sistema de ecuacións. Podes comprobalo substituíndoos en vez de valores X1, X2 e X3 en expresión.

Como podes ver, en Excel, o sistema de ecuacións pódese resolver de varias maneiras, cada unha delas ten as súas propias vantaxes e desvantaxes. Pero todos estes métodos pódense dividir condicionalmente en dous grandes grupos: matriz e usando a ferramenta de selección de parámetros. Nalgúns casos, os métodos de matriz non sempre son adecuados para resolver un problema. En particular, cando o determinante da matriz é igual a cero. Noutros casos, o propio usuario é libre de decidir que opción considera máis conveniente para si mesmo.