в)Решить эту же систему методом Гаусса

Метод Гаусса состоит в приведении расширенной матрицы системы

 

к виду ,

 

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

При решении поставленной задачи работа в EXCEL должна выглядеть так:

 

После введения в таблицу EXCEL расширенной матрицы системы в качестве разрешающего элемента выбираем первый коэффициент первого уравнения (Если в системе первый коэффициент равен нулю, то необходимо переставить уравнения местами). Получаем новые значения для первого уравнения копированием формулы .

 

 

Для вычисления нового значения в его ячейку вводим формулу , т.е. = старое значение -новое значение с закреплением номера строки * старое значение с закреплением наименования столбца.

 

 

Копируем формулу на все второе уравнение и на третье, четвертое уравнения.

 

 

Затем алгоритм повторяется. Выбираем второй разрешающий элемент в ячейке В8. Выделим блок ячеек для новой матрицы и в ячейке В13 для нового значения элемента вводим формулу.

 

 

Копируем эту формулу в ячейки А13 и С13:Е13. В ячейку В14 вводим формулу =В9-В$13*$B9. Копируем формулу в ячейки третьего и четвертого уравнения.

 

 

Заданную формулу из ячейки В14 «протащить мышкой» в диапазон первого уравнения нельзя, так как будет выдано сообщение о циклической ошибке – ссылке формулы на саму себя. Поэтому сначала командами «Копировать» и «Вставить» формулу ячейки В14 копируем в ячейку А12, а затем методом «протаскивания» заполняем всю первую строку.

 

 

 

Появившееся в ячейках второй строки выделение убираем с помощью контекстного меню.

 

 

Далее выбираем третий разрешающий элемент и вводим для него формулу в блоке ячеек для новой матрицы, копируем формулу в ячейки третьей строки.

 

 

Для вычисления значений элементов четвертой строки в ячейку С20 (элемент ) вводим формулу C15-C$19*$C15 и копируем её во все ячейки строки.

 

 

Далее не «протягиванием», а копированием и вставкой вводим формулу для элемента и «протягиваем её на ячейки первого и второго уравнений.

 

 

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

 

 

Для определения значений оставшихся коэффициентов введем в ячейку D24 формулу вычисления элемента и скопируем её на все оставшиеся ячейки.

 

 

Полученный в ячейках Е22:Е25 результат является решением системы.

 

 

Решение задач линейного программирования.

Задачи линейного программирования решаются в надстройке EXCEL «Поиск решения»

Задание 1.

Решить транспортную задачу.

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

 

	b1=190	b2=120	b3=60
a1=100
a2=200
a3=120

 

Если модель является открытой, то есть в данной задаче суммарные запасы груза 420, а суммарные потребности 370, необходимо ввести фиктивного потребителя с потребностями 50 единиц груза при нулевых стоимостях перевозок. Добавим столбец для .

 

	b1=190	b2=120	b3=60	b4=50
a1=100
a2=200
a3=120

 

Составим план перевозок, обеспечивающий минимальную стоимость перевозок и определим минимальную стоимость перевозок.

Решение.

Задача решается с помощью надстройки «Поиск решения». Вводим матрицу стоимостей перевозок в диапазон В2:Е4.

 

 

Диапазон F2:I4 оставляем для результата вычисления плана перевозок

 

 

В столбец J и строку 5вводим формулы для вычисления сумм по строкам и столбцам матрицы F2:I4. В ячейку J5 записываем целевую функцию =СУММПРОИЗВ(В2:Е4;F2:I4).

 

 

В результате таблица вычислений будет иметь вид:

 

 

Открываем диалоговое окно «Поиск решений». Укажем адрес целевой ячейки J5, равное минимальному значению; изменяя ячейки F2:I4; в ограничениях необходимо указать все неравенства для строк и для столбцов.

 

 

 

Результат решения задачи:

 

 

Необходимо перейти к дробному формату:

 

 

Таким образом, стоимость перевозок 1090 руб.

 

Задание 2.

Определить min функции

при следующих ограничениях

 

.

Решение.

Для переменных оставляются ячейки А2, А3 и А4; в ячейку В2 вводится формула целевой функции Z; в ячейках С2 и С3 – левые части ограничений.

 

 

Открываем диалоговое окно «Поиск решений» и в водим данные:

· в качестве целевой ячейки указываем ячейку целевой функции В2;

· для решении задачи указываем ячейки А2:А4 в окне «Изменяемые ячейки».

 

 

· в окне «Ограничения» вводятся данные из системы ограничений, для этого используем клавишу «Добавить». Автоматически появляется знак $.

 

 

В окне «Добавление ограничений» вводим

 

 

Клавиша «Добавить» вводит заданное выражение и предлагает ввести следующее.

В результате окно заполненное «Поиск решения» имеет вид:

 

 

После нажатия клавиши «Выполнить»

 

 

Осталось нажать клавишу «ОК», закрывающую окно «Результаты поиска решения».

 

 

Ответ: (2, 0, 0), min z=2.

 

Аппроксимация функции.

4.1. линейная интерполяция ФУНКЦИИ

Пример 4.1.1.

Найти приближенное значение таблично заданной функции:

X	-1,5	-1
Y			4.5

в точке = - 0,25

Уравнение прямой, проходящей через три точки, имеет вид:

Запишем формулу для искомого значения

Выделим отрезок , содержащий x. В данном случае x = - 0,25, x Î [-1,0].

Следовательно

Подставим в формулу, получим: отсюда y = 5,125.

Расчеты в программе EXCEL:

X	-1,5	-1
Y			4,5
Х0	-0,25
У0	5,125	=(B5-C2)/(D2-C2)*(D3-C3)+C3

 

 

4.2. ОПРЕДЕЛЕНИЕ ПАРАМЕТРОВ ЛИНЕЙНОЙ ЗАВИСИМОСТИ.

Пример 4.2.1.

Дана таблица экспериментальных данных:

 

X	1,5	2,3	3,1	3,9	4,7	5,5	6,3
Y	6,5	8,1	9,7	11,3	12,9	14,5	16,1

 

Показать, что зависимость линейная, определить параметры этой зависимости.

Решение:

а) Построим график по заданным точкам.

б) Докажем аналитически, что данная зависимость является линейной.

Если разделенные разности P1 первого порядка для каждой i-той пары точек имеют близкие между собой значения, то данная зависимость является линейной,

X	Y	P1
1,5	6,5
2,3	8,1
3,1	9,7
3,9	11,3
4,7	12,9
5,5	14,5
6,3	16,1

.

Следовательно, данная зависимость имеет вид: .

в) Для определения параметров а и b данной зависимости методом наименьших квадратов необходимо решить систему уравнений

В данном случае получим

В расчетной таблице это выглядит так:

 

X	Y	Х*Х	Х*У
1,5	6,5	2,25	9,75
2,3	8,1	5,29	18,63
3,1	9,7	9,61	30,07
3,9	11,3	15,21	44,07
4,7	12,9	22,09	60,63
5,5	14,5	30,25	79,75
6,3	16,1	39,69	101,43
27,3	79,1	124,39	344,33

 

Последняя строка таблицы содержит значения сумм по каждому столбцу. Система уравнений решается через определение обратной матрицы.

 

Матрица	коэффициентов		Св. чл.
124,39	27,3		344,33
27,3			79,1
Обратная матрица:		Решение
0,055804	-0,21763		a=
-0,21763	0,991629		b=	3,5

 

Для проверки найденного решения в формуле при вычислении F(x) необходимо закреплять адреса ячеек, где находятся значения a и b.

 

X	Y	F(X)
1,5	6,5	6,5
2,3	8,1	8,1
3,1	9,7	9,7
3,9	11,3	11,3
4,7	12,9	12,9
5,5	14,5	14,5
6,3	16,1	16,1

 

Для определения параметров линейной зависимости в EXCEL используется функция ЛИНЕЙН(известные значения у; известные значения х; 1; 0). Работа в EXCEL оформляется следующим образом.

Вводятся заданные значения х и у.

Для вычисления значений параметров а и b выделяются две ячейки. Вызывается функция ЛИНЕЙН, для первого параметра выделяются значения столбца у, для второго параметра – столбца х, третий и четвертый параметры остаются пустыми. Вводится функция тремя клавишами + + .

Для проверки надо вычислить значения у для каждого значения х при полученных а и b.

 

 

 

 

 

4.3. ОПРЕДЕЛЕНИЕ ПАРАМЕТРОВ КВАДРАТИЧНОЙ ЗАВИСИМОСТИ.

Пример 4.1.

Дана таблица экспериментальных данных:

x	0,5		1,5		2,5		3,5		4,5
y

 

Определить вид и параметры зависимости y=F(x).

Решение:

а) Построим график зависимости У от Х по заданным точкам.

 

б) Докажем аналитически, что данная зависимость не линейная. Для этого вычислим , если они не имеют близкие между собой значения, проверим значения .

Если значения близки между собой значения, то данная зависимость квадратичная, т.е. .

Расчетная таблица выглядит следующим образом:

 

n	x	y	p1	p2
	0,5
	1,5
	2,5
	3,5
	4,5

 

Перейдем к линейной зависимости . Для этого вычтем из уравнения, записанного для i – й пары точки, уравнение, записанное для первой пары точки . Получим , где i не равно 1.

Обозначим и .

В таблицах EXCEL это будет выглядеть так:

 

n	x	y	p1	p2
	0,5				t	Z
					1,5
	1,5
					2,5
	2,5
					3,5
	3,5
					4,5
	4,5
					5,5

 

Для определения параметров зависимости методом наименьших квадратов следует добавить в таблицу два столбца и вычислить суммы

 

n	x	y	p1	p2
	0,5				t	z	t^2	t*Z
					1,5		2,25
	1,5
					2,5		6,25
	2,5
					3,5		12,25
	3,5
					4,5		20,25
	4,5
					5,5		30,25
			Суммы	31,5		125,25

 

Матрица коэффициентов		Св.чл.
125,25	31,5
31,5
Обратная матрица	Решение
0,0667	-0,2333	а=
-0,2333	0,92778	b=	-1

 

Из уравнения можно определить

Проверка

x	y	F(x)
0,5
1,5
2,5
3,5
4,5

Ответ:

 

4. ОПРЕДЕЛЕНИЕ вида эмпирической зависимости.

Пример 4.

Дана таблица экспериментальных данных:

x
y

 

Установить вид зависимости и параметры этой зависимости.

Решение:

Будем выбирать вид зависимости из числа следующих:

Для определения вида зависимости вычислим средние величины:

а) ; ;

; _;

; .

b) Найдем значение, соответствующее. Так как значение имеется в таблице, то определяется из таблицы как значение соответствующего у, т.е. .

Далее найдем значение соответствующее . Этого значения нет в таблице, воспользуемся формулой линейной интерполяции ; так же находим соответствующее - .

c) Найдем погрешности по следующим формулам:

e₁ =çy₁^* - y_аp ê= ç9 - 10 ê = 1;

e₂ =çy₁^* - y_геом ê= ç9 - 8,66 ç= 0,34;

e₃ =çy₁^* - y_гарм ê= ç9 - 7,47 ç= 1,5;

e₄ =çy₂^* - y_аp ê= ç7,47 - 10 ç= 2,528;

e₅ =çy₂^* - y_геом ê= ç7,47 - 8,66 ç= 0,1,189;

e₆ =çy₃^* - y_ар ê= ç6,33 - 10 ç= 3,67;

e₇ =çy₃^* - y_гаpм ê=ç6,33 - 7,5 ç= 1,167.

d) Найдем номер минимальной погрешности.

Самая малая погрешность e₂ = 0,5. В списке зависимостей под номером 2 стоит зависимость у = ab^x. По данному методу зависимость, описывающая указанные экспериментальные данные (эмпирическая зависимость), будет иметь вид: у = ab^x.

e) При решении в EXCEL работа оформляется следующим образом:

Определить вид и параметры зависимости
x									e1
y									e2	0,3397
									e3	1,5
x	y		Средние	х	у		y*		e4	2,5279
			арифм						e5	1,1881
			геом	2,236068	8,660254		7,472136		e6	3,6667
			гармон	1,666667	7,5		6,333333		e7	1,1667
									min	0,3397
Для перехода к линейной зависимости
При переходе к t и z построим график зависимости z=A*t+В, если график – прямая линия, то вид зависимости y=f(x) определен верно.

 

5. ОПРЕДЕЛЕНИЕ ПАРАМЕТРОВ эмпирическОЙ ЗАВИСИМОСТИ

Используя данные задачи 4, найти методом наименьших квадратов параметры эмпирической зависимости.

Решение:

Итак, в результате решения задачи 4 определили, что зависимость имеет вид .

Необходимо найти значения параметров а и b в этой формуле. Для этого удобно воспользоваться формулами метода наименьших квадратов для определения параметров A и B линейной зависимости .

Логарифмируя левую и правую части уравнения , получим , т. о. Z = ln(у), T = х, А = ln(b), В = ln(a). Для обратного перехода к а и b малым необходимо вычислить

Полученные формулы называют формулами выравнивания. Теперь, используя метод наименьших квадратов можно определить А и В. затем найдем искомые значения а и b из формул выравнивания,

Пересчитаем табличные данные согласно формул выравнивания:

T=x
Z=ln(y)	Ln(5)=1,6	Ln(7)=1,9	Ln(9)=2,1	Ln(12)=2,3	Ln(15)=2,5

Для определения А и В методом наименьших квадратов

; ;

; ; .

Система для определения А и В записывается следующим образом:

После решения А = 0,27 и В =1,37, а из формул выравнивания

Таким образом, по данному методу зависимость, описывающая данные эксперимента, запишется в виде

 

 

x	y	T	Z	T^2	T*Z		Матрица коэффициентов	Св. чл.
			1,609438		1,609438					35,57281
			1,94591		3,89182					10,94553
			2,197225		6,591674		Обратная матрица	Решение
			2,484907		9,939627		0,1	-0,3	А=	0,273622
			2,70805		13,54025		-0,3	1,1	В=	1,36824
			10,94553		35,57281
							а	3,928429
x	y		проверка				в	1,314718
			5,2
			6,8
			8,9
			11,7
			15,4