Задание 1
Необходимо средствами MS Excel подобрать подходящий вариант аппроксимации (линейная, логарифмическая, степенная, полиномиальная, экспоненциальная функция) для заданных табличным способом данных, доказать оптимальность выбора путем сравнения коэффициентов достоверности и аппроксимации для каждого варианта.
Исходные данные
Год
2001
2002
2003
2004
2005
2006
2007
Объем производства (млн.т)
7,07
5,1
3
2,1
2,33
4,13
7
Обработанные данные представлены в таблице ниже:
№
Название аппроксимации
Уравнение
Величина достоверностиаппроксимации R2
1
Линейная
y = -0,1007x + 206,22
0,0109
2
Экспоненциальная
y = (3×1022)e-0,0252x
0,0119
3
Логарифмическая
y = -202,65lnx + 1545,1
0,011
4
Полиномиальная
y = 0,5471x2— 2193x + 2000000
0,9786
5
Степенная
y = (5×10167)x-50,615
0,012
Наиболее оптимальная аппроксимация для исходных данных – полиномиальная кривая (квадратная парабола), так как величина достоверности наиболее близка к единице. Общий вид графика близок к фактическому расположению исходных данных в виде точек на плоскости.
Построенные графики представлены ниже.
Линейная аппроксимация
/>
Экспоненциальная аппроксимация
/>
Логарифмическая аппроксимация
/>
Полиномиальная аппроксимация
/>
Степенная аппроксимация
/>
Задание 2
Построить прямую, параллельную оси абсцисс (Ох) и пересекающую ось ординат (Оу) в точке (0; 2) в диапазоне xÎ[-3; 3] с шагом D=0,5.
Так как абсцисса точки, через которую проходит прямая параллельная оси Ох равна 0, а ордината – 2, то уравнение прямой будет у=2.
Для построения прямой в MS Excel представим числовые данные в виде таблицы ниже, а также график функции. Шаг изменения х равен 0,5
Х
Y
-3
2
-2,5
2
-2
2
-1,5
2
-1
2
-0,5
2--PAGE_BREAK----PAGE_BREAK----PAGE_BREAK----PAGE_BREAK--
Ставрополь
Краснодар
Товар 1
7
10
4
2
Товар 2
10
40
32
20
Товар 3
70
75
65
50
Товар 4
15
40
25
20
В столбце «Итого» находятся формулы суммарного объема перевозок по каждому товару:
=СУММ(B20:E20),
=СУММ(B21:E21),
=СУММ(B22:E22),
=СУММ(B23:E23).
В столбце «Max» находятся формулы для расчета предельных объемов перевозок:
=СУММ(B4:E4),
=СУММ(B5:E5),
=СУММ(B6:E6),
=СУММ(B7:E7).
В ячейке В25 находится формула целевой функции:
=СУММПРОИЗВ(B20:E23;B12:E15)
/>
Система ограничений для данной задачи представлена ниже на рисунке:
/>
/>