Решение системы линейных уравнений в MS Excel

Вычисления с массивами

До сих пор мы говорили о вычислениях при помощи формул, в которых результат размещается в одну ячейку. Существуют формулы, результатом вычисления которых является множество значений, размещаемых в интервал. Такой интервал называют массивом, а соответствующие формулы - формулами массивов. Для работы с такими формулами существуют особые правила. Например, можно записать формулу:

=B3:B12 - D3:D12

Формула вычисляет разность значений ячеек в двух столбцах. Если формула записана в ячейку F3, то после нажатия Enter только в ней будет выведен результат, равный разности значений B3 и D3. Все разности в данном примере можно вычислить двумя способами:

1. Выделить интервал для записи массива (F3:F12) и нажать Shift+Ctrl+Enter.

2. Вычислить по формуле массива в одной ячейке и скопировать формулу на все ячейки массива за маркер указателя.

Указанные способы могут не дать требуемого результата при вычислениях с некоторыми стандартными функциями, результатом которых является массив. Более надежным является следующий способ:

1. Выделить интервал для записи массива.

2. Вызвать Мастера функций и записать функцию.

3. Завершить второй этап работы с Мастером функций нажатием Shift+Ctrl+Enter, а не Ok.

Если интервал не был предварительно выделен, или в третьем пункте нажата Ok, то исправить положение можно выделением интервала для массива после вычисления, установкой курсора в поле содержимого ячейки строки формул и нажатием Shift+Ctrl+Enter.

Краткая теория из курса алгебры:

Пусть дана система линейных уравнений (1). Матричный способ решения систем линейных уравнений используется в тех случаях, когда число уравнений равно числу переменных.

(1)

Введем обозначения. Пусть А – матрица коэффициентов при переменных, B – вектор свободных членов, X – вектор значений переменных. Тогда X = A^-1 × B, где А^-1 – матрица, обратная А. Причем обратная матрица А^-1 существует, если определитель матрицы А не равен 0. Произведение исходной матрицы А и обратной А^-1 должно быть равно единичной матрице:

А^-1А=АА^-1=Е.

Задание: Решить систему линейных уравнений:

Технология работы:

Пусть на диапазоне А11:С13, задана исходная матрица А, составленная из коэффициентов системы. Сначала найдите определитель матрицы А. Для этого в ячейке F15 необходимо вызвать Мастер функций, В категории " Ссылки и массивы " найдите функцию МОПРЕД(), задайте ее аргумент A11:С13. Получили результат 344. Так как определитель исходной матрицы А не равен 0, т.е. существует обратная ей матрица, поэтому следующим этапом и будет нахождение обратной матрицы. Для этого выделите диапазон А15:С17, где будет размещаться обратная матрица. Вызвав Мастера функций, в категории " Ссылки и массивы " найдите функцию МОБР(), задайте ее аргумент A11:С13 и нажмите Shift+Ctrl+Enter. Чтобы проверить правильность обратной матрицы, умножьте ее на исходную с помощью функции МУМНОЖ(). Вызовите эту функцию, предварительно выделив диапазон А19:А21. В качестве аргументов укажите исходную матрицу А, т.е. диапазон А11:С13 и обратную матрицу, т.е. диапазон А15:С17 и нажмите Shift+Ctrl+Enter. Получили единичную матрицу. Таким образом, обратная матрица найдена верно. Теперь для нахождения результата, выделите для него диапазон F18:F20. Вызовите функцию МУМНОЖ(), используя Мастера функций, укажите два массива-диапазона, которые будете перемножать − обратную матрицу и столбец свободных членов, т.е. А15:С17 и Е11:Е13 и нажмите Shift+Ctrl+Enter. Результат показан на рисунке 6.

Теперь можно произвести проверку правильности найденных решений х₁, х₂ и х₃. Для этого, выполните вычисление каждого уравнения, используя найденные значения х₁, х₂ и х₃. Например, в ячейке G11 подсчитайте значение , при этом результат должен быть равен 3. Введем следующую формулу =A11*$F$18+B11*$F$19+C11*$F$20. Скопируйте эту формулу в две ячейки, расположенные ниже, т.е. в G12 и G13. Снова получите столбец свободных членов. Таким образом, решение системы линейных уравнений выполнено верно (рис.80).

Рисунок 80 - Решение системы линейных уравнений

Варианты индивидуальных заданий

Задание № 1. Средствами Microsoft Excel вычислить значение выражения:

Таблица 16 – Индивидуальные варианты лабораторной работы

№ В	Выражение
	; а=0.83, х=1.2
	; а=0.12, х=0.36
	; a=0.394, b=0.124, x=0.842
	; a=0.124, b=1.14, x=2.125
	; x=0.117, a=0.832
	; a=0.124, b=0.338, x=0.817
	; x=0.156, b=1.255
	; x=0.117, a=2.346
	; b=0.2, x=0.114
	; x=2.512, a=0.135
	; x=2.126, b=2.438, a=0.324
	; x=0.834, b=0.242, a=0.125
	; a=0.344, x=0.125
	; a=2.124, b=0.835, a=0.54
	; x=0.875

Задание № 2. Средствами Microsoft Excel решить систему линейных уравнений:

Таблица 17 – Индивидуальные варианты лабораторной работы

№ В	Система линейных уравнений	№ В	Система линейных уравнений

Вопросы для самоконтроля

1. Для чего предназначен мастер функций?

2. Как начать работу с Мастером функций?

3. Как выбрать нужную функцию?

4. Сколько категорий функций имеется в Excel?

5. Как задаются аргументы для выбранной функции?

6. Как перейти в режим показа формул?

7. Каковы основные типичные ошибки в формулах?

8. Как облегчить поиск связей формулы с ячейками, на которые формула содержит ссылки?

9. Что называется массивом? Формулой массива?

10. Какие способы можно использовать для вычислений, результатами которых является массив? Какой из способов является более надежным?

Дата добавления: 2014-10-31