Задания для самостоятельного выполнения

Лабораторная работа 6

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

Цели работы

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

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

Задание 1

На основании статистических данных о численности населения России на период с 1993 по 1999 годы сделать прогноз на 2001 и 2003 г.

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

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

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

	A	B	C	D	E	F
	Годы
	Численность населения, млн чел.	148,3	147,9	147,5	146,3

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

= ПРЕДСКАЗ(F1;B2:E2;B1:E1)

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

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

Задание 2

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

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

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

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

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

4. Ввести в ячейки Н1:L1 период времени с 2005 по 2013 г.
с шагом 2 года. Выделить диапазон H2:L2 и ввести формулу = ТЕНДЕНЦИЯ(B2:G2;B1:G1;H1:L1).

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

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

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

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

Задание 3

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

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

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

2. Заполнить ячейки исходными данными.

x	1,5			4,5			7,5
y

3. Построить диаграмму для экспериментальных точек. Тип диаграммы – График, но точки не соединены линиями.

4. Выделив график и щелкнув на нем правой клавишей мыши, вызвать контекстное меню и выбрать в нем строку Добавить линию тренда.

5. Добавить Линейный тренд, Полиномиальный 2-го и 3-го порядка, Степенной. В процессе построения тренда указать уравнение, которому подчиняется зависимая величина. Для этого при построении линии тренда на вкладке Параметры установить флажок в строке Показывать уравнение на диаграмме.

6. Выполнить для линий тренда: Поместить на диаграмму величину достоверности аппроксимации.

7. Оформить линии трендов по своему усмотрению, меняя цветовую гамму и форму маркеров.

8. Сформировать сводную таблицу для анализа качества приближения к экспериментальным точкам, указав в столбцах тип тренда, уравнение зависимости и значение достоверности аппроксимации.

9. Сделать вывод: какой из трендов дает наилучшее приближение к экспериментальным точкам?

10. Выполнить условное форматирование таблицы с учётом сделанного вывода.

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

Задания для самостоятельного выполнения

По следующим данным с помощью функции ПРЕДСКАЗ спрогнозировать стоимость киловатта электроэнергии до 2015 года.

 

Годы
Стоим. кВт,коп.

 

Выполнить графическую оценку данных, используя функции РОСТ и ТЕНДЕНЦИЯ.

Вар.
Годы	1996,1998, 2000,2002	1999,2001, 2002,2003	1997,1999, 2001,2003	1998,1999, 2000,2001	1996,1997, 1998,1999

Построить Линейный, Полиномиальный 2-го и 3-го порядка и Степенной тренды для следующих экспериментальных данных. Выбрать наилучший вид зависимости.

Вар.
	0,2	0,1	0,3	0,5	0,6	0,8	0,9	1,2	1,3	1,5	1,6	1,7
	0,2	0,6	0,8	0,9	1,2	1,3	1,5	1,6	1,7	1,9	2,0	2,5

Лабораторная работа 7