в)Решить эту же систему методом Гаусса
Метод Гаусса состоит в приведении расширенной матрицы системы
к виду 
,
используя арифметические вычисления.
При решении поставленной задачи работа в 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) Найдем погрешности по следующим формулам:
e1 =çy1* - yаp ê= ç9 - 10 ê = 1;
e2 =çy1* - yгеом ê= ç9 - 8,66 ç= 0,34;
e3 =çy1* - yгарм ê= ç9 - 7,47 ç= 1,5;
e4 =çy2* - yаp ê= ç7,47 - 10 ç= 2,528;
e5 =çy2* - yгеом ê= ç7,47 - 8,66 ç= 0,1,189;
e6 =çy3* - yар ê= ç6,33 - 10 ç= 3,67;
e7 =çy3* - yгаpм ê=ç6,33 - 7,5 ç= 1,167.
d) Найдем номер минимальной погрешности.
Самая малая погрешность e2 = 0,5. В списке зависимостей под номером 2 стоит зависимость у = abx. По данному методу зависимость, описывающая указанные экспериментальные данные (эмпирическая зависимость), будет иметь вид: у = abx.
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 |
