183638 (596696), страница 5
Текст из файла (страница 5)
Система 7
решив которую, получаем а1 = 114949314, а2 = -3,46784603, а3 = 2,0307E-08.
Таким образом, квадратичная аппроксимация имеет вид
у = 114949314 - 3,46784603*х + 2,0307E-08*х2
Решение системы производили средствами MS Excel. Результаты представлены в таблице 5.
Таблица 1.5 - Решение системы уравнений в случае квадратичной аппроксимации
| A | B | C | D | E | |
| 1 | 22 | 9,45E+08 | 8,33E+16 | 9,45E+08 | |
| 2 | 9,45E+08 | 8,33E+16 | 1,19E+25 | 6,1E+16 | |
| 3 | 8,33E+16 | 1,19E+25 | 2,14E+33 | 1,19E+25 | |
| 4 | |||||
| 5 | 0,185801 | -5,1E-09 | 2,13E-17 | 1,15E+08 | а1 |
| 6 | -5,1E-09 | 2E-16 | -9,1E-25 | -3,46785 | а2 |
| 7 | 2,13E-17 | -9,1E-25 | 4,68E-33 | 2,03E-08 | а3 |
Шаг 4
Теперь аппроксимируем функцию экспоненциальной функцией . Для определения коэффициентов и прологарифмируем значения и используя итоговые суммы таблицы 3, расположенные в ячейках B24, D24, I24, J24 получим систему
Система 8
где .
Решив систему, найдем с = 16,45413618, а2 = 1,71028E-08.
После потенцирования получим а1 = 13993957,34.
Таким образом, экспоненциальная аппроксимация имеет вид
.
Решение системы проводили, пользуясь средствами Microsoft Excel. Результаты представлены в таблице 6.
Таблица 1.6 - Решение системы уравнений при экспоненциальной аппроксимации
| A | B | C | D | |
| 1 | 22 | 9,45E+08 | 378,1502 | |
| 2 | 9,45E+08 | 8,33E+16 | 1,7E+10 | |
| 3 | ||||
| 4 | 0,088583 | -1E-09 | 16,45414 | с |
| 5 | -1E-09 | 2,34E-17 | 1,71E-08 | а2 |
| 6 | 13993957 | а1 |
В ячейках А4:В5 записана формула {=МОБР(А1:В2)}.
В ячейках С4:С5 записана формула {=МУМНОЖ(А4:В5;С1:С2)}.
В ячейке С6 записана формула =EXP(C4).
Вычислим среднее арифметическое значение показателей х и у в таблице 1.7:
Таблица 1.7 - Среднее арифметическое показателей
| В | С | |
| 49 | Xcp | 35 673 235,85 |
| 50 | Ycp | 42 946 701,47 |
Шаг 5
Для того, чтобы рассчитать коэффициент корреляции и коэффициент детерминации, данные целесообразно расположить в виде таблицы 7, которая является продолжением таблицы 3.
Таблица 1.8 - Расчет коэффициентов корреляции и детерминации
| K | L | M | N | O | P | Q | R | |
| 1 | (Х-Хср) | (У-Уср) | произв | (Х-Хср)^2 | (У-Уср)^2 | линейная | квадратичная | экспон |
| 2 | 2,62E+06 | -4,65E+06 | -1,22E+13 | 6,88E+12 | 2,16E+13 | 5,89E+12 | 6,95E+14 | 1,29E+14 |
| 3 | -7,78E+06 | -1,51E+07 | 1,17E+14 | 6,05E+13 | 2,27E+14 | 6,17E+13 | 3,74E+13 | 2,86E+13 |
| 4 | -8,74E+06 | -1,60E+07 | 1,40E+14 | 7,64E+13 | 2,56E+14 | 6,98E+13 | 8,73E+13 | 2,26E+13 |
| 5 | 1,22E+07 | 4,91E+06 | 5,98E+13 | 1,48E+14 | 2,41E+13 | 6,56E+12 | 2,74E+15 | 2,60E+14 |
| 6 | 2,56E+07 | 1,83E+07 | 4,68E+14 | 6,54E+14 | 3,35E+14 | 9,13E+13 | 6,81E+15 | 4,56E+14 |
| 7 | 2,83E+07 | 2,10E+07 | 5,96E+14 | 8,02E+14 | 4,43E+14 | 1,21E+14 | 7,71E+15 | 4,92E+14 |
| 8 | 6,84E+06 | -4,33E+05 | -2,96E+12 | 4,68E+13 | 1,88E+11 | 5,12E+10 | 1,47E+15 | 1,84E+14 |
| 9 | 4,53E+07 | 3,80E+07 | 1,72E+15 | 2,05E+15 | 1,44E+15 | 3,93E+14 | 1,29E+16 | 6,29E+14 |
| 10 | 3,04E+07 | 2,31E+07 | 7,02E+14 | 9,23E+14 | 5,34E+14 | 1,45E+14 | 8,38E+15 | 5,17E+14 |
| 11 | 6,15E+07 | 5,42E+07 | 3,33E+15 | 3,78E+15 | 2,94E+15 | 8,00E+14 | 1,62E+16 | 5,49E+14 |
| 12 | 1,74E+08 | 1,67E+08 | 2,91E+16 | 3,04E+16 | 2,79E+16 | 7,61E+15 | 5,25E+15 | 8,90E+16 |
| 13 | 4,85E+06 | -2,43E+06 | -1,18E+13 | 2,35E+13 | 5,90E+12 | 1,61E+12 | 1,07E+15 | 1,57E+14 |
| 14 | -2,21E+07 | -2,94E+07 | 6,50E+14 | 4,90E+14 | 8,64E+14 | 2,35E+14 | 3,38E+15 | 1,68E+13 |
| 15 | -2,49E+07 | -3,22E+07 | 8,03E+14 | 6,22E+14 | 1,04E+15 | 2,83E+14 | 4,80E+15 | 3,69E+13 |
| 16 | -2,47E+07 | -3,20E+07 | 7,93E+14 | 6,13E+14 | 1,03E+15 | 2,79E+14 | 4,70E+15 | 3,53E+13 |
| 17 | -2,49E+07 | -3,22E+07 | 8,03E+14 | 6,21E+14 | 1,04E+15 | 2,82E+14 | 4,80E+15 | 3,69E+13 |
| 18 | -2,66E+07 | -3,39E+07 | 9,00E+14 | 7,07E+14 | 1,15E+15 | 3,12E+14 | 5,78E+15 | 5,27E+13 |
| 19 | -2,65E+07 | -3,37E+07 | 8,93E+14 | 7,01E+14 | 1,14E+15 | 3,10E+14 | 5,71E+15 | 5,15E+13 |
| 20 | -2,20E+07 | -2,93E+07 | 6,46E+14 | 4,85E+14 | 8,59E+14 | 2,34E+14 | 3,34E+15 | 1,62E+13 |
| 21 | -1,36E+07 | -2,09E+07 | 2,84E+14 | 1,85E+14 | 4,36E+14 | 1,19E+14 | 6,91E+14 | 2,72E+12 |
| 22 | -2,14E+07 | -2,86E+07 | 6,12E+14 | 4,57E+14 | 8,20E+14 | 2,23E+14 | 3,05E+15 | 1,27E+13 |
| 23 | -8,54E+06 | -1,58E+07 | 1,35E+14 | 7,30E+13 | 2,50E+14 | 6,81E+13 | 7,53E+13 | 2,38E+13 |
| 24 | 1,60E+08 | 3,73E-08 | 4,28E+16 | 4,39E+16 | 4,28E+16 | 1,16E+16 | 9,97E+16 | 9,27E+16 |
В ячейках К2 записана формула =(С2-$C$49). Затем она скопирована в ячейки К3:К23. Аналогично рассчитываются отклонения показателя у от его среднего значения уср, записанные в ячейках L2:L23.
В столбце М2:М23 записано произведение столбцов К2:К23 и L2:L23.
В столбце Р2 записана формула =($С$4+$С$5*B2-B2)^2. Столбец Р2:Р23 показывает квадрат отклонения предсказанного значения показателя у от его фактического. $C$4 и $C$5 вычисленные в таблице 4 коэффициенты линейного уравнения.
Аналогично вычисляются квадраты отклонений квадратичной и экспоненциальной функций в столбцах Q и R.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
