Задание 3. 1. Освоить статистические функции Excel для прогнозирования значений числовых последовательностей.

Практическая работа № 3

ПРОГНОЗ ЗНАЧЕНИЙ ЧИСЛОВЫХ ВЕЛИЧИН С ПОМОЩЬЮ СТАТИСТИЧЕСКИХ ФУНКЦИЙ Excel

Цели работы

1. Освоить статистические функции Excel для прогнозирования значений числовых последовательностей.

2. Научиться использовать функции Excel для обработки экспериментальных данных при помощи трендов.

Задание 1

На основании статистических данных о численности населения России на период с 2010 по 2013 годы сделать прогноз на 2014 и 2015 г.

Порядок выполнения работы

1. Дать рабочему листу название «Прогноз».

2. Заполнить ячейки рабочего листа Excel следующими статистическими данными, выделив их цветом:

3. Сделать предположение о численности населения России в 2014 году с помощью функции ПРЕДСКАЗ, которая позволяет вычислить теоретическое значение зависимой переменной (в данном случае это численность населения) в фиксированной точке аргумента (т.е. для определенного года). Для этого в ячейку F 1 ввести дату – 2001, а в ячейку F 2 формулу

= ПРЕДСКАЗ(F 1; B 2: E 2; B 1: E 1)

4. Аналогичным способом предсказать численность российских граждан в 2015 году, но уже учитывая рассчитанное ранее значение в 2014 г.

5. Используя условное форматирование, выделить столбцы с минимальными и максимальными показателями, построить график.

Задание 2

С помощью функций ТЕНДЕНЦИЯ и РОСТ предсказать изменение численности населения на периоды с 2015 по 2025 г.г. и смоделировать на период с 1987 по 1993 г.

Порядок выполнения работы

1. Дать рабочему листу название «Тенденция».

2. Копировать часть таблицы «Прогноз».

3. Спрогнозировать дальнейшую динамику изменения численности на период с 2015 по 2025 г.г. при помощи функции ТЕНДЕНЦИЯ. Эта функция позволяет предсказать значения зависимой переменной для целого диапазона значений независимой переменной по линейному закону.

4. Ввести в ячейки Н 1: R1 период времени с 2015 по 2025 г.
с шагом 1 год. Выделить диапазон H 2: R2 и ввести формулу = ТЕНДЕНЦИЯ(B 2: G 2; B 1: G 1; H 1: L 1).

Завершить нажатием комбинации клавиш Ctrl + Shift + Enter.

5. Вычислить с помощью функции ТЕНДЕНЦИЯ предположительную численность населения России на период с 1987 по 1993 г.г.

6. Аналогичным образом спрогнозировать изменение численности населения с помощью функции РОСТ по экспоненциальному закону.

7. По заданным экспериментальным точкам и полученным модельным данным построить диаграмму типа График в виде плавной линии. Сделать вывод о поведении линейной и экспоненциальной модели изменения численности населения.

Задание 3

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