CC BY
8
METHODS OF SOLVING THE SYSTEM OF EQUATIONS WITH THE USE OF MS EXCEL APPLICATION AND FUNDAMENTALS OF WORKING IN THEM Mamirova N.A.1, Karimova M.K.2, Barotova Sh.Sh.3
'Ma mirova Nasiba Abdurasulovna - Lecturer; 2Karimova Munojot Kalandarovna - Lecturer; 2Barotova Shahnoza Shamsiddinovna - Lecturer, DEPARTMENT OF SPECIAL SCIENCES, NAVOI CITY VOCATIONAL SCHOOL, NAVOI, REPUBLIC OF UZBEKISTAN
Abstract: in this article we will study the process of solving systems of algebraic equations in MS Excell. As a result, we can see that complex systems of algebraic equations can be easily worked in this program. Keywords: MS Excel, system, system of algebraic equations.
МЕТОДЫ РЕШЕНИЯ СИСТЕМЫ УРАВНЕНИЙ С ИСПОЛЬЗОВАНИЕМ ПРОГРАММЫ MS EXСEL И ОСНОВЫ РАБОТЫ В НИХ
1 2 3
Маъмирова Н.А. , Каримова М.К. , Баротова Ш.Ш.
'Маъмирова Насиба Абдурасуловна. - преподаватель; 2Каримова Муножот Каландаровна - преподаватель; 2Баротова Шахноза Шамсиддиновна - преподаватель, кафедра специальных наук, Навоийская городская профессиональная школа, г. Навои, Республика Узбекистан
Аннотация: в этой статье мы изучим процесс решения систем алгебраических уравнений в MS Excel. В результате мы видим, что сложные системы алгебраических уравнений легко решаются в этой программе.
Ключевые слова: MS Excel, система, система алгебраических уравнений.
UDC 33'. 225.3
Let us be given a system of linear equations with three variables: 2x -+- 5u -+- 7,z = 9
4 x — и -+- z = 4 3x — 2u -I- 4z = S
To solve this system, we create the following matrix table in the EXCEL spreadsheet:
Table 1. Expression in MS Excel
A B C D
10 2 5 2 9
11 4 -1 1 4
12 3 -2 4 5
Table 2. Expression in MS Excel
A B C D E F
18 =D10 =B10 =A18*B19*C20 =A20*B19*C18
19 =D11 =B11 =C10 =A19*B20*C18 =A19*B18*C20
20 =D12 =B12 =C11 =A20*B18*C19 =A18*B20*C19
21 =C12 =E18+E19+E20 =F18+F19+F20
22 =E21-F21 Dx
We denote this basic determinant by the letter D. To organize the auxiliary determinant, we place the elements of column 4 instead of the elements of column 1:
Table 3. Expression in MS Excel
A B C D E F
23 =A10 =D10 =C10 =A23*B24*C25 =A25*B24*C23
24 =A11 =D11 =C11 =A24*B25*C23 =A24*B23*C25
25 =A12 =D12 =C12 =A25*B23*C24 =A23*B25*C24
26 =E23+E24+E25 =F23+F24+F25
27 =E26-F26 Du
To organize the auxiliary Du determinant, we place the elements of column 4 instead of the elements of column 2:
Table 4. Expression in MS Excel
A B C D E F
28 =A10 =B10 =D10 =A28*B29*C30 =A30*B29*C28
29 =A11 =B11 =D11 =A29*B30*C28 =A29*B29*C30
30 =A12 =B12 =D12 =A30*B28*C29 =A28*B30*C29
31 =E28+E29+E30 =F28+F29+F30
32 =E31-F31 Dz
To make the auxiliary determinant Dz, we place the elements of column 4 instead of the elements of column
2:
Table 5. Expression in MS Excel
E F G
10 X= =E22/E17
11 Y= =E27/E17
12 Z= E32/E17
The solution of the new system of equations is automatically calculated when other coefficients are introduced instead of the coefficients in the range A10-D12. By changing the given coefficients, a system of linear equations of any three variables can be solved in a short time. The result is a table for solving the system of equations shown in Figure 1.
Fig. 1. System solution of equations
Solve the system of equations:
2 x —
X1 X2
+ 3 x — 2;
The sequence of solving the system of equations in an Excel spreadsheet processor is as follows:
■ We enter the elements of the matrix A in the spreadsheet.
■ To find the inverse matrix :
Separate the elements of the matrix A and press the CTRL key to select the areas where the elements of the inverse matrix should appear in the spreadsheet. Then click on the "Вставка функции (fx)" icon, select the "МОБР" function in the next window of the math section of the dialog box, and click OK. Again, separate the elements of the matrix A, determine the address of the array, and press CTRL + Shift + Enter at the same time (Figure 2 shows this process). The result is the elements of the inverse matrix A-1.
□ Microsoft Excel - Книга!
Файл Правка Вид Вставка Формат Сервис ¿анные Окно Справка
Arial Суг
. ж к 'I üiiig% OÜÜ « i» I m m □
/= Расширенным фильтр... Отобразить все А
A в с D E F G H I J к L M
1
2
3 2 -1 2 -1 0,625 0,125 -0,375
4 A= 1 2 -1 B= 3 A-'= -0,5 0,5 0,5
5 1 -1 3 2 -0,375 0,125 0,625
6
7
8
Fig. 2. Inverse matrix elements.
We multiply the elements of the inverse matrix A-1 by the elements of the vector B. As a result of these steps we find an X vector whose coordinates are the solutions of the system. We separate the elements of the inverse matrix A-1. Pressing the Ctrl key separates the X vector elements from which the vector elements B must then be calculated. Click the "Вставка функции (fx)" icon in the Mathematics section of the dialog box, select Мумножм in the next window, and click OK. First select the elements of the matrix A, then the elements of the array b and press Ctrl + Shift + Enter.
Microsoft Excel - Книга!
файл Правка Вид Вставка Формат Сервис Данные Окно Справка
и ла.д|7а1*-а /и- 18,е'ЙШИШИ'И*
¿Ама1Суг - 10 т | Ж К Ч | Е ЩШ Й I 9 ''' ООО *& $ | Й Й | Ш - & - Расширенный фильтр,,. От зсе А . | ф | ^ (р |
03
fx )=MyMHO?K(J3:L5;G3:G5)}
A в с D E F G H I J К L M N 0 P
1
2
3 2 -1 2 -1 0,625 0,125 -0,375 x,= ■1
4 A= 1 2 -1 B= 3 А" - -0,5 0,5 0,5 хг= 3
5 1 -1 3 2 -0,375 0,125 0,625 Хз= 2
В
7
3
Fig. 3. The result of a system solution
If the inverse matrix does not appear in the range A3:B4, then press the mouse on the formula bar and press CTRL + SHIFT + ENTER again. The result is a transposed matrix АТ in the range A3:B4. To determine the product of AT*B matrices, do the following:
■ Divide the block of cells (АТВ vector) for the resulting matrix and its size is n x 1. For example, separate the block of cells E4:E5
■ Press the Вставка функции;
■ In the resulting Мастер функций dialog box, select Математические in the Категория workspace, and select the function name in the select workspace Мумножм.
References / Список литературы
1. SuxanovA.P. Information and progress. M., 1998. 265 p.
2. Xudoyorov Sh.J., Arzieva S. Methodology for solving a system of linear equations in Excel. Current issues in the use of ICT in education in secondary schools. Proceedings of the Republican Scientific-Practical Conference. Part I. Navoi, 2016. 157-158 pages.
