La capacitat de solucionar sistemes d’equacions sovint pot ser útil no només a l’escola, sinó també a la pràctica. Al mateix temps, no tots els usuaris de PC saben que Excel té les seves pròpies solucions per a equacions lineals. Anem a descobrir com es pot utilitzar aquesta eina de processador tabular per aconseguir aquesta tasca de diverses maneres.
Solucions
Qualsevol equació es pot considerar resolta només quan es troben les seves arrels. A Excel, hi ha diverses opcions per trobar les arrels. Vegem-ne cadascun.
Mètode 1: Mètode matricial
La manera més habitual de resoldre un sistema d’equacions lineals amb eines d’Excel és utilitzar el mètode matricial. Consisteix a construir una matriu a partir dels coeficients de les expressions, i després de crear una matriu inversa. Tractem d’utilitzar aquest mètode per resoldre el següent sistema d’equacions:
14x1+2x2+8x4=218
7x1-3x2+5x3+12x4=213
5x1+x2-2x3+4x4=83
6x1+2x2+x3-3x4=21
- Omplim la matriu amb els nombres que són coeficients de l’equació. Aquests números s'han d’ordenar de forma seqüencial en ordre, tenint en compte la ubicació de cada arrel a la qual corresponen. Si en alguna expressió manca una de les arrels, en aquest cas es considera que el coeficient és igual a zero. Si el coeficient no està indicat a l’equació, però l’arrel corresponent està present, es considera que el coeficient és igual a 1. Denota la taula resultant com a vector A.
- Per separat, escrivim els valors després del signe igual. Denoteu-los per nom com a vector B.
- Ara, per trobar les arrels de l’equació, en primer lloc, hem de trobar la matriu, la inversa de l’existent. Afortunadament, a Excel hi ha un operador especial dissenyat per resoldre aquest problema. Es diu MOBR. Té una sintaxi bastant simple:
= MBR (matriu)Argument "Matriu" - Aquesta és, de fet, l’adreça de la taula font.
Per tant, seleccionem al full una regió de cel·les buides, que és igual a la mida del rang de la matriu original. Feu clic al botó "Insereix la funció"situat a prop de la barra de fórmules.
- Córrer Màsters de funcions. Vés a la categoria "Matemàtica". A la llista busquem el nom "MOBR". Després de trobar-lo, seleccioneu-lo i feu clic al botó. "D'acord".
- S'inicia la finestra d'argument de la funció. MOBR. Només té un camp segons el nombre d’arguments - "Matriu". Aquí heu d’especificar l’adreça de la nostra taula. A aquests efectes, establiu el cursor en aquest camp. A continuació, mantenim premut el botó esquerre del ratolí i seleccioneu la zona del full en què es troba la matriu. Com podeu veure, les dades de les coordenades de la ubicació s'introdueixen automàticament al camp de la finestra. Un cop finalitzada aquesta tasca, el més obvi seria fer clic a un botó. "D'acord"però no tingueu pressa. El fet és que fer clic en aquest botó equival a utilitzar la comanda Introduïu. Però quan es treballa amb matrius després de completar l’entrada de la fórmula, no feu clic al botó. Introduïui produeix un conjunt de tecles de drecera Ctrl + Maj + Retorn. Realitzeu aquesta operació.
- Per tant, després d’aquest moment, el programa realitza càlculs i en la sortida de l’àrea preseleccionada tenim la inversa de la matriu.
- Ara haurem de multiplicar la matriu inversa per la matriu. Bque consisteix en una columna de valors situada després del signe igual en expressions. Per a la multiplicació de taules en Excel també té una funció separada, que es diu Mòmia. Aquesta declaració té la següent sintaxi:
= MUMNOGUE (Array1; Array2)Seleccioneu l’interval, en el nostre cas que consti de quatre cel·les. A continuació, executeu de nou Auxiliar de funcionsfent clic a la icona "Insereix la funció".
- A la categoria "Matemàtica"corrent Màsters de funcionsseleccioneu el nom "MUMNOZH" i feu clic al botó "D'acord".
- S'activa la finestra d’argument de funció. Mòmia. Al camp "Massive1" introduïu les coordenades de la nostra matriu inversa. Per fer-ho, com a la darrera vegada, establiu el cursor al camp i, amb el botó esquerre del ratolí premut, seleccioneu la taula corresponent amb el cursor. Es realitza una acció similar per fer les coordenades al camp "Massiv2", només aquesta vegada seleccionem els valors de la columna. B. Un cop fetes les accions anteriors, de nou no tenim pressa per prémer el botó "D'acord" o tecla Introduïu, i escriviu la combinació de tecles Ctrl + Maj + Retorn.
- Després d’aquesta acció, les arrels de l’equació apareixen a la cel·la prèviament seleccionada: X1, X2, X3 i X4. Es disposaran en sèrie. Així, podem dir que hem resolt aquest sistema. Per tal de verificar la correcció de la solució, n'hi ha prou amb substituir les respostes donades al sistema d’expressió original en lloc de les arrels corresponents. Si es manté la igualtat, això significa que el sistema d’equacions presentat es resol correctament.
Lliçó: Matriu inversa Excel
Mètode 2: selecció de paràmetres
El segon mètode conegut per resoldre el sistema d’equacions d’Excel és l’ús del mètode de selecció de paràmetres. L’essència d’aquest mètode és cercar el contrari. És a dir, a partir d’un resultat conegut, cercarem un argument desconegut. Utilitzem l’equació quadràtica per exemple.
3x ^ 2 + 4x-132 = 0
- Accepta el valor x per igual 0. Calculeu el valor corresponent f (x)aplicant la següent fórmula:
= 3 * x ^ 2 + 4 * x-132En lloc de valor "X" substituïu l'adreça de la cel·la on es troba el número 0per nosaltres x.
- Aneu a la pestanya "Dades". Premeu el botó "Anàlisi" què passa si. Aquest botó es col·loca a la cinta de la caixa d'eines. "Treballant amb dades". S'obrirà una llista desplegable. Trieu una posició en ella "Selecció de paràmetres ...".
- S'inicia la finestra de selecció de paràmetres. Com podeu veure, consta de tres camps. Al camp "Instal·la en una cel·la" especifiqueu l'adreça de la cel·la on es troba la fórmula f (x)calculat per nosaltres una mica abans. Al camp "Valor" introduïu el número "0". Al camp "Canvi de valors" especifiqueu l'adreça de la cel·la on es troba el valor xanteriorment adoptat per nosaltres 0. Després de realitzar aquestes accions, feu clic al botó "D'acord".
- Després d'això, Excel realitzarà un càlcul mitjançant la selecció de paràmetres. Això informarà la finestra d’informació apareguda. Hauria de fer clic al botó "D'acord".
- El resultat del càlcul de l’arrel de l’equació es trobarà a la cel·la assignada al camp "Canvi de valors". En el nostre cas, com veiem x serà igual a 6.
Aquest resultat també es pot comprovar substituint aquest valor en l’expressió resolta en comptes del valor x.
Lliçó: Selecció de paràmetres d'Excel
Mètode 3: Mètode Cramer
Ara intentarem resoldre el sistema d’equacions pel mètode Kramer. Per exemple, prenguem el mateix sistema en què s’utilitzava Mètode 1:
14x1+2x2+8x4=218
7x1-3x2+5x3+12x4=213
5x1+x2-2x3+4x4=83
6x1+2x2+x3-3x4=21
- Com en el primer mètode, fem la matriu A a partir dels coeficients de les equacions i de la taula B dels valors que segueixen el signe igual.
- A més, fem quatre taules més. Cadascun d’ells és una còpia de la matriu. A, només aquestes còpies tenen una columna al seu torn substituïda per una taula B. A la primera taula és la primera columna, en la segona taula és la segona, i així successivament.
- Ara hem de calcular els determinants de totes aquestes taules. El sistema d’equacions només tindrà solucions si tots els determinants tenen un valor diferent de zero. Per calcular aquest valor a Excel, hi ha una funció independent: MEPRED. La sintaxi d’aquesta afirmació és la següent:
= MEPRED (matriu)Així, com la funció MOBR, l'únic argument és la referència a la taula que s'està processant.
Per tant, seleccioneu la cel·la en què es mostrarà el determinant de la primera matriu. A continuació, feu clic al botó conegut dels mètodes anteriors. "Insereix la funció".
- Finestra activada Màsters de funcions. Vés a la categoria "Matemàtica" i entre la llista d’operadors, seleccioneu-hi el nom MOPRED. Després, feu clic al botó "D'acord".
- S'inicia la finestra d'argument de la funció. MEPRED. Com podeu veure, només té un camp - "Matriu". Introduïu l’adreça de la primera matriu transformada en aquest camp. Per fer-ho, establiu el cursor al camp i, a continuació, seleccioneu l'interval de matrius. Després, feu clic al botó "D'acord". Aquesta funció mostra el resultat en una sola cel·la, en comptes d’una matriu, de manera que per obtenir el càlcul no cal recórrer a prémer una combinació de tecles. Ctrl + Maj + Retorn.
- La funció calcula el resultat i el mostra en una cel·la preseleccionada. Com veiem, en el nostre cas, el determinant és -740, és a dir, no és igual a zero que ens convé.
- De la mateixa manera, calculem els determinants de les altres tres taules.
- En l'etapa final, calculem el determinant de la matriu primària. El procediment és tot el mateix algorisme. Com veiem, el determinant de la taula primària també és diferent de zero, el que significa que la matriu es considera no degenerada, és a dir, el sistema d'equacions té solucions.
- Ara és el moment de trobar les arrels de l’equació. L'arrel de l'equació serà igual a la relació entre el determinant de la matriu transformada corresponent i el determinant de la taula primària. Així, dividint al seu torn els quatre determinants de les matrius transformades pel nombre -148que és el determinant de la taula original, obtenim quatre arrels. Com podeu veure, són iguals als valors 5, 14, 8 i 15. Per tant, són exactament iguals a les arrels que hem trobat utilitzant la matriu inversa en mètode 1que confirma la correcció de la solució del sistema d'equacions.
Mètode 4: Mètode de Gauss
El sistema d’equacions també es pot resoldre aplicant el mètode de Gauss. Per exemple, prenem un sistema d’equacions més simple a partir de tres incògnites:
14x1+2x2+8x3=110
7x1-3x2+5x3=32
5x1+x2-2x3=17
- De nou, sempre escrivim els coeficients a la taula. Ai els membres lliures després del signe igual - A la taula B. Però aquesta vegada portarem les dues taules, ja que necessitarem això per treballar més. Una condició important és la de la primera cel·la de la matriu A el valor no era zero. En cas contrari, reorganitzeu les línies.
- Copieu la primera fila de les dues matrius connectades a la línia inferior (per a més claredat, podeu saltar una fila). A la primera cel·la, que es troba a la línia encara més baixa que l'anterior, introduïu la següent fórmula:
= B8: E8- $ B $ 7: $ E $ 7 * (B8 / $ B $ 7)Si heu organitzat les matrius de manera diferent, llavors les adreces de les cel·les de la fórmula tindran un significat diferent, però podreu calcular-les comparant-les amb les fórmules i imatges que es donen aquí.
Després d’introduir la fórmula, seleccioneu tota la fila de cel·les i premeu la combinació de tecles Ctrl + Maj + Retorn. La fórmula de la matriu s’aplicarà a la fila i s’omplirà de valors. Per tant, restem de la segona línia de la primera multiplicada per la relació dels primers coeficients de les dues primeres expressions del sistema.
- Després, copieu la cadena resultant i enganxeu-la a la línia següent.
- Seleccioneu les dues primeres línies després de la línia que falta. Premeu el botó "Còpia"que es troba a la cinta de la pestanya "Inici".
- Saltem la línia després de la darrera entrada al full. Seleccioneu la primera cel·la de la línia següent. Feu clic al botó dret del ratolí. Al menú contextual obert, moveu el cursor a l’element "Enganxa especial". A la llista addicional en execució, seleccioneu la posició "Valors".
- A la línia següent, introduïu la fórmula de matriu. Es resta de la tercera fila del grup de dades anterior, la segona fila multiplicada per la relació del segon coeficient de la tercera i segona fila. En el nostre cas, la fórmula serà la següent:
= B13: E13- $ B $ 12: $ E $ 12 * (C13 / $ C $ 12)Després d’introduir la fórmula, seleccioneu tota la sèrie i utilitzeu la tecla de drecera Ctrl + Maj + Retorn.
- Ara cal executar el funcionament invers segons el mètode de Gauss. Salteu tres línies de la darrera entrada. A la quarta línia, introduïu la fórmula de la matriu:
= B17: E17 / D17Per tant, dividim l'última fila calculada per nosaltres en el seu tercer coeficient. Després d’escriure la fórmula, seleccioneu tota la línia i premeu la combinació de tecles Ctrl + Maj + Retorn.
- Aixequem la línia i introduïm la següent fórmula de matriu:
= (B16: E16-B21: E21 * D16) / C16Pressionem la combinació habitual de tecles per aplicar la fórmula matricial.
- Ens elevem una línia més amunt. En ella introduïm la fórmula matricial de la següent forma:
= (B15: E15-B20: E20 * C15-B21: E21 * D15) / B15De nou, seleccioneu tota la línia i utilitzeu la drecera Ctrl + Maj + Retorn.
- Ara observem els números que van resultar a l’última columna de l’últim bloc de files, calculats anteriorment per nosaltres. Són aquests números (4, 7 i 5) seran les arrels d’aquest sistema d’equacions. Podeu comprovar-ho substituint-los per valors. X1, X2 i X3 en expressions.
Com podeu veure, a Excel, el sistema d’equacions es pot resoldre de diverses maneres, cadascuna de les quals té els seus propis avantatges i desavantatges. Però tots aquests mètodes es poden dividir en dos grans grups: matriu i utilitzant l’eina de selecció de paràmetres. En alguns casos, els mètodes matricials no sempre són adequats per resoldre un problema. En particular, quan el determinant de la matriu és zero. En altres casos, l’usuari és lliure de decidir quina opció considera més convenient per a ell mateix.
