Урок 22. Тема урока: Понятие аппроксимации. Методы аппроксимации. Реализация метода наименьших квадратов в ms Excel для функции предложения. Упражнение.

Задание: Рассчитайте коэффициенты линейной функции Методом наименьших квадратов для аппроксимации экспериментально полученных данных.

Образец

Метод расчета:

Нормальная система МНК: ; гдеk=0,…,m

При n=7 и m=1 получаем:

Где

Ход выполнения:

1. Откройтефайл «Upr14» из папки «Primer».

2. В ячейках В3:Н4введеныэкспериментальныеданные. На их основе построенаточечная диаграмма, по которой видно, что соединить все точки одной прямой невозможно. Ваша задача:найти коэффициентыуравнения прямой таким образом, чтобысуммарасстоянийот точекдоэтойпрямойбыланаименьшей.

3. Заполнитеподготовленный шаблон:

   1. В ячейку В5 введитеформулурассчитывающую х²: =В3*В3;

   2. Ячейки С5:Н5заполните этой формулой черезмаркер заполнения;

   3. В ячейку В6 - формулу рассчитывающую произведение у и х: =В3*В4;

   4. Ячейки С6:Н6заполните этой формулой черезмаркер заполнения;

   5. В ячейку I3 введитеформулусуммирующую данные из ячеек слева от нее: =СУММ(B3:H3);

   6. Ячейки I4:I6заполните этой формулой черезмаркер заполнения;

4. Сформируйтесистему нормальных уравнений. В подготовленную форму введите:

   1. В ячейку А8 введитеформулу: =Н2;

   2. В ячейках A9иD8формулу: =I3;

   3. В ячейку G8 формулу: =I4;

   4. Ячейки D9 иG9заполните самостоятельно.

5. Решите систему уравнений методом подстановки:

   1. Запишите получившуюся систему: .

   2. Выразите из первого уравнения а₀: ;

   3. Подставьте значение а₀ во второе уравнение: ;

   4. Найдите а₁: ;

   5. Используя ссылкина ячейки, содержащие соответствующие данные в ячейкеС12получимформулу: =(G8*A9-G9*A8)/(D8*A9-A8*D9);

   6. Значение а0 находится поформуле пункта 5.2_. Эту формулусоставьтеивведитев ячейкуС11самостоятельно.

Контроль: Полученныезначениядолжнысовпадатьс коэффициентами уравнения прямой, приведенными награфике.

6. ЗакройтеMS Excel, сохранив файл всвоейпапке под именем Upr21.

Урок 24. Тема урока: Прогнозирование функции объема продаж, заданного таблицей наблюдений, графическим способом, заложенным в ms Excel. Упражнение.

Задание: Организуйте прогноз на следующий год на основании тренда построенного для точечного графика, отображающего фактические данные продаж за предыдущие годы.

Образец

Ход выполнения:

1. Откройтефайл «Upr15» из папки «Primer».

2. На основе фактических данных постройте точечную диаграмму.

Внимание! Нельзядополнить линиями тренда ряды данных наобъемныхдиаграммах,нормированныхдиаграммах,лепестковыхдиаграммах,круговыхикольцевыхдиаграммах.

Образец 2

3. В зависимости от характера данных подберитеи постройтедлякаждогографикасвоюлиниютрендаи выведите на егоуравнение:

   1. Данные продажи муки имеют нестабильнорастущийхарактер, значит наиболее подходящий вид линии тренда –линейный.

   2. Правым щелчкоммышки вызовитеконтекстное менюграфика продажи муки и выберите пунктДобавить линию тренда:

• В открывшемся окне Линия трендана вкладкеТипопределителинейныйтип линии тренда;

Образец 3

• Задайтепараметрылиниитрендана соответствующей вкладке:названиеаппроксимирующей (сглаженной кривой) -автоматическое;прогнозвперед на –1 периоди закажитепоказыватьуравнениена диаграмме;

• Ок.

3. Данные продажи масла характеризуется постоянной скоростью роста. Такие данные лучше всего аппроксимируются степеннымтипомлиниитренда.

4. Правым щелчкоммышки вызовитеконтекстное менюграфика продажи масла и выберите пунктДобавить линию тренда:

• Определите степеннойтип линии тренда;

• И задайтепараметрылиниитренда с прогнозом вперед на –1 период иотображением уравнения на диаграмме;

• Ок.

5. Данные продажи сахара вначале быстро увеличиваются, а затем постепенно стабилизируется. В этом случае данные хорошо аппроксимируются логарифмическимтипомлиниитренда.

6. Добавьтелиниютрендастепенноготипа спрогнозомвперед на –1 периодиотображениемна диаграммеуравнения.

4. Поместитепостроенную диаграмму справа от таблицы данных.

5. Отформатируйтеполученную диаграмму.

   1. Изменитеобластьдиаграммы;

   2. Задайте Основные линиисеткиОси хвпараметрахдиаграммы;

   3. Отформатируйтеобластьпостроениядиаграммы;

   4. ОпределитешрифтыподписейобеихосейArial,10 пт,полужирногоначертания;

   5. Изменителиниютрендаданных продажи муки:

• Выделителиниютрендаданных продажи муки;

• Вызовите ееконтекстное меню;

• ВыберитепунктФорматирование линии тренда;

• Установите Вид линиитренда на свой вкус.

1. Остальные линии трендаотформатируйте самостоятельно.

1. Подберите взаиморасположениеиразмеры элементов диаграммы.

2. ЗакройтеMS Excel, сохранив файл всвоейпапке под именем Upr22.