Цифр технологии cfaf5a3ea6d16c316b8a26974d117a476dc6803c. Перечень лабораторных работ
 Скачать 119.21 Kb.
|
Применение Excel для расчета относительных показателей. |
| ПРОГНОЗ ЗНАЧЕНИЙ ЧИСЛОВЫХ ВЕЛИЧИН С ПОМОЩЬЮ СТАТИСТИЧЕСКИХ ФУНКЦИЙ Excel Цели работы Освоить статистические функции Excel для прогнозирования значений числовых последовательностей. Научиться использовать функции Excel для обработки экспериментальных данных при помощи трендов. Задание 1 На основании статистических данных о численности населения России на период с 1993 по 1999 годы сделать прогноз на 2001 и 2003 г. ^ Порядок выполнения работы Дать рабочему листу название «Прогноз». Заполнить ячейки рабочего листа Excel следующими статистическими данными, выделив их цветом: A B C D E F 1 Годы 1993 1995 1997 1999 2 Численность населения, млн чел. 148,3 147,9 147,5 146,3 Сделать предположение о численности населения России в 2001 году с помощью функции ПРЕДСКАЗ, которая позволяет вычислить теоретическое значение зависимой переменной (в данном случае это численность населения) в фиксированной точке аргумента (т.е. для определенного года). Для этого в ячейку F1 ввести дату – 2001, а в ячейку F2 формулу = ПРЕДСКАЗ(F1;B2:E2;B1:E1) Аналогичным способом предсказать численность российских граждан в 2003 году, но уже учитывая рассчитанное ранее значение в 2001 г. Используя условное форматирование, выделить столбцы с минимальными и максимальными показателями, построить график. Задание 2 С помощью функций ТЕНДЕНЦИЯ и РОСТ предсказать изменение численности населения на периоды с 2001 по 2015 г.г. и смоделировать на период с 1985 по 1993 г. Порядок выполнения работы Дать рабочему листу название «Тенденция». Копировать часть таблицы «Прогноз». Спрогнозировать дальнейшую динамику изменения численности на период с 2003 по 2013 г.г. при помощи функции ТЕНДЕНЦИЯ. Эта функция позволяет предсказать значения зависимой переменной для целого диапазона значений независимой переменной по линейному закону. Ввести в ячейки Н1:L1 период времени с 2005 по 2013 г. с шагом 2 года. Выделить диапазон H2:L2 и ввести формулу = ТЕНДЕНЦИЯ(B2:G2;B1:G1;H1:L1). Завершить нажатием комбинации клавиш Ctrl + Shift + Enter. Вычислить с помощью функции ТЕНДЕНЦИЯ предположительную численность населения России на период с 1987 по 1993 г.г. Аналогичным образом спрогнозировать изменение численности населения с помощью функции РОСТ по экспоненциальному закону. По заданным экспериментальным точкам и полученным модельным данным построить диаграмму типа График в виде плавной линии. Сделать вывод о поведении линейной и экспоненциальной модели изменения численности населения. Задание 3 Построить диаграмму, содержащую заданные точки и линейный, экспоненциальный и полиномиальный тренды с соответствующими уравнениями. Оценив достоверность аппроксимации, выбрать лучший вид уравнения. ^ Порядок выполнения работы Дать рабочему листу название «Регрессия». Заполнить ячейки исходными данными. x 1,5 2 3 4,5 5 6 7,5 y 12 7 3 11 17 18 23 3. Построить диаграмму для экспериментальных точек. Тип диаграммы – График, но точки не соединены линиями. 4. Выделив график и щелкнув на нем правой клавишей мыши, вызвать контекстное меню и выбрать в нем строку Добавить линию тренда. 5. Добавить Линейный тренд, Полиномиальный 2-го и 3-го порядка, Степенной. В процессе построения тренда указать уравнение, которому подчиняется зависимая величина. Для этого при построении линии тренда на вкладке Параметры установить флажок в строке Показывать уравнение на диаграмме. 6. Выполнить для линий тренда: Поместить на диаграмму величину достоверности аппроксимации. 7. Оформить линии трендов по своему усмотрению, меняя цветовую гамму и форму маркеров. 8. Сформировать сводную таблицу для анализа качества приближения к экспериментальным точкам, указав в столбцах тип тренда, уравнение зависимости и значение достоверности аппроксимации. 9. Сделать вывод: какой из трендов дает наилучшее приближение к экспериментальным точкам? 10. Выполнить условное форматирование таблицы с учётом сделанного вывода. ^ Отчет о лабораторной работе должен содержать электронный документ с указанием фамилии и группы студента, времени выполнения, названия, цели лабораторной работы, выполненных заданий описательной части работы и задания по предложенному преподавателем варианту. ^ Задания для самостоятельного выполнения По следующим данным с помощью функции ПРЕДСКАЗ спрогнозировать стоимость киловатта электроэнергии до 2015 года. Годы 1996 1997 1998 1999 2000 2001 2002 2003 Стоим. кВт,коп. 4 13 24 38 50 70 80 98 Выполнить графическую оценку данных, используя функции ^ РОСТ и ТЕНДЕНЦИЯ. Построить Линейный, Полиномиальный 2-го и 3-го порядка и Степенной тренды для следующих экспериментальных данных. Выбрать наилучший вид зависимости. |