Добавление линий тренда в диаграмму

Исходные данные

Ф.И.О.	Всего начислено	Всего удержано	Сумма к выдаче	Отдел	Дата
Ведомость зарплаты сотрудников за 1-й квартал
Иванов И.А.		487,80	4012,20		1-й кв. 1998
Малаев В.П.		441,12	3458,88		1-й кв. 1998
Федоров Е.Н.		505,52	3894,48		1-й кв. 1998
Климов Ф.Ф.		484,60	3515,40		1-й кв. 1998
Осипов Ю.Я.		537,72	4112,28		1-й кв. 1998
Ведомость зарплаты сотрудников за 2-й квартал
Иванов И.А.		899,96	6800,04		2-й кв. 1998
Малаев В.П.		724,48	5375,52		2-й кв. 1998
Федоров Е.Н.		685,84	5114,16		2-й кв. 1998
Климов Ф.Ф.		587,64	4212,36		2-й кв. 1998
Осипов Ю.Я.		660,08	4939,92		2-й кв. 1998

 

В колонке Сумма к выдаче в исходной таблице необходимо ввести формулу для расчета заработной платы.

Методика выполнения

1. Необходимо разместить исходные ведомости на одном рабочем листе Excel (табл. 3.3).

Таблица 3.3

Размещение исходных ведомостей на рабочем столе

Ф.И.О.	Всего начислено	Всего удержано	Сумма к выдаче	Отдел	Дата
Иванов И.А.		487,80	4012,20		1-й кв. 1998
Малаев В.П.		441,12	3458,88		1-й кв. 1998
Федоров Е.Н.		505,52	3894,48		1-й кв. 1998
Климов Ф.Ф.		484,60	3515,40		1-й кв. 1998
Осипов Ю.Я.		537,72	4112,28		1-й кв. 1998
Иванов И.А.		899,96	6800,04		2-й кв. 1998
Малаев В.П.		724,48	5375,52		2-й кв. 1998
Федоров Е.Н.		685,84	5114,16		2-й кв. 1998
Климов Ф.Ф.		587,64	4212,36		2-й кв. 1998
Осипов Ю.Я.		660,08	4939,92		2-й кв. 1998

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

2. В меню Данные следует выбрать команду Сводная таблица, по которой на экран выводится окно Мастер сводных таблиц.

3. С помощью мыши перетащить поля из списка в правой части окна Мастер сводных таблиц следующим образом:

· в область Строки – Ф.И.О.;

· в область Столбца – Дата;

· в область Данных – Всего начислено, Всего удержано, Сумма к выдаче.

4. Разместите результат построения сводной таблицы на отдельном рабочем листе, озаглавив его Ведомость зарплаты за полугодие.

Задание 2. Создать на основе исходных данных (табл. 3.4) сводную таблицу таким образом, чтобы данные о каждом продавце находились на «отдельной странице», учитывая то, что в первую очередь нас интересуют показатели оборота.

Таблица 3.4

Исходные данные

Автосалон «Счастливое колесо»
Продавец	Марка	Год выпуска	Оборот	Дата
Довгаль	БМВ 520		10 500,00 т.р.	12.09.2005
Довгаль	VW Пассат		12 200,00 т.р.	14.09.2005
Петренко	Рено Сафран		17 000,00 т.р.	16.09.2005
Петренко	Пежо 605		11 000,00 т.р.	16.09.2005
Петренко	Мерседес 190		19 000,00 т.р.	15.09.2005
Петренко	БМВ 325		15 000,00 т.р.	15.09.2005
Петренко	Форд Мондео		12 996,00 т.р.	13.09.2005
Славин	Мерседес 180 С		11 000,00 т.р.	12.09.2005
Славин	Ауди 100		10 999,00 т.р.	15.09.2005
Славин	Опель Фонтера		16 000,00 т.р.	13.09.2005

 

 

Лабораторная работа № 4
Аппроксимация экспериментальных данных

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

 

Независимые переменные

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

Такого рода задачи приближения функций часто возникают:

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

· численном интегрировании, дифференцировании, решении дифференциальных уравнений и т. д.;

· необходимости вычисления значений функций в промежуточных точках рассматриваемого интервала;

· определении значений характерных величин процесса за пределами рассматриваемого интервала, в частности при прогнозировании.

Если для моделирования некоторого процесса, заданного таблицей, построить функцию, приближенно описывающую данный процесс на основе метода наименьших квадратов, она будет называться аппроксимирующей функцией (регрессией), а сама задача построения аппроксимирующих функций – задачей аппроксимации.

Аппроксимацией называется подбор аналитической формулы у = f(х) для установленной из опыта функциональной зависимости у = φ(х).

Аппроксимируемая функция у может зависеть от одной или нескольких переменных.

В Excel для построения регрессий имеются две возможности.

1. Добавление выбранных регрессий (линий тренда – trendlines) в диаграмму, построенную на основе таблицы данных для исследуемой характеристики процесса (доступно лишь при наличии построенной диаграммы).

2. Использование встроенных статистических функций рабочего листа Excel, позволяющих получать регрессии (линии тренда) непосредственно на основе таблицы исходных данных.

Добавление линий тренда в диаграмму

Для таблицы данных, описывающих некоторый процесс и представленных диаграммой, в Excel имеется эффективный инструмент регрессионного анализа, позволяющий:

· строить на основе метода наименьших квадратов и добавлять в диаграмму пять типов регрессий, которые с той или иной степенью точности моделируют исследуемый процесс;

· добавлять к диаграмме уравнение построенной регрессии;

· определять степень соответствия выбранной регрессии отображаемым на диаграмме данным.

На основе данных диаграммы Excel позволяет получать линейный, полиномиальный, логарифмический, степенной, экспоненциальный типы регрессий, которые задаются уравнением

y = y(x), (1)

где x – независимая переменная, которая часто принимает значения последовательности натурального ряда чисел (1; 2; 3; …) и производит, например, отсчет времени протекания исследуемого процесса (характеристики).

1. Линейная регрессия хороша при моделировании характеристик, значения которых увеличиваются или убывают с постоянной скоростью. Это наиболее простая в построении модель исследуемого процесса. Она строится в соответствии с уравнением

y = mx + b, (2)

где m – тангенс угла наклона линейной регрессии к оси абсцисс; b – координата точки пересечения линейной регрессии с осью ординат.

2. Полиномиальная линия тренда полезна для описания характеристик, имеющих несколько ярко выраженных экстремумов (максимумов и минимумов). Выбор степени полинома определяется количеством экстремумов исследуемой характеристики. Так, полином второй степени может хорошо описать процесс, имеющий только один максимум или минимум; полином третьей степени – не более двух экстремумов; полином четвертой степени – не более трех экстремумов и т. д.

В этом случае линия тренда строится в соответствии с уравнением

y = c₀ + c₁x + c₂x² + c₃x³ + c₄x⁴ + c₅x⁵ + c₆x⁶, (3)

где коэффициенты c₀, c₁, c₂, ..., c₆ – константы, значения которых определяются в ходе построения.

3. Логарифмическая линия тренда с успехом применяется при моделировании характеристик, значения которых вначале быстро меняются, а затем постепенно стабилизируются.

Данная линия тренда строится в соответствии с уравнением

y = c ln(x) + b, (4)

где коэффициенты b, с – константы.

4. Степенная линия тренда дает хорошие результаты, если значения исследуемой зависимости характеризуются постоянным изменением скорости роста. Примером такой зависимости может служить график равноускоренного движения автомобиля. Если среди данных встречаются нулевые или отрицательные значения, использовать степенную линию тренда нельзя.

Линия строится в соответствии с уравнением

y = c x^b, (5)

где коэффициенты b, с – константы.

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

Линия тренда строится в соответствии с уравнением

y = ce^bx, (6)

где коэффициенты b, с – константы.

Трендомназывается общая тенденция изменения данных в зависимости от времени.

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

Для добавления линии тренда к ряду данных следует:

· активизировать построенную на основе ряда данных диаграмму, т. е. щелкнуть в пределах области диаграммы. В главном меню появится пункт Диаграмма;

· после щелчка на этом пункте на экране появится меню, в котором следует выбрать команду Добавить линию тренда.

Эти же действия легко реализуются, если навести указатель мыши на график, соответствующий одному из рядов данных, и щелкнуть правой кнопкой мыши; в появившемся контекстном меню выбрать команду Добавить линию тренда. На экране появится диалоговое окно Линия тренда с раскрытой вкладкой Тип (рис. 4.1).

 

Рис. 4.1. Определение линии тренда (функции)

 

После этого необходимо выбрать на вкладке Тип необходимый тип линии тренда (по умолчанию выбирается тип Линейный). Для типа Полиномиальная в поле Степень следует задать степень выбранного полинома.

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

Рис. 4.2. Выбор параметров

 

При необходимости, перейдя на вкладку Параметры (рис. 4.2), можно для линии тренда задать следующие параметры:

1) изменить название линии трен­­да в поле Название аппроксимирующей (сглаженной) кривой;

2) задать количество периодов (вперед или назад) для прогноза в поле Прогноз.

Прогнозированиезаключается в том, чтобы каким-либо образом определить значение Y₀ переменной Y при таком наборе значений и факторов t₀, x₁₀, x₂₀,…, x_m0, которого нет в исходной таблице данных.

Временной шаг, с которым представлены в таблице данные, называется шагом, или периодом,прогнозирования;

3) вывести в область диаграммы уравнение линии тренда, для чего следует включить флажок показать уравнение на диаграмме;

4) вывести в область диаграммы значение достоверности аппроксимации R2, для чего следует включить флажок поместить на диаграмму величину достоверности аппроксимации (R^2);

5) задать точку пересечения линии тренда с осью Y, для чего следует включить флажок пересечение кривой с осью Y в точке.

Для того чтобы начать редактирование уже построенной линии тренда, существует три способа.

1. Воспользоваться командой Выделенная линия тренда из меню Формат, предварительно выбрав линию тренда.

2. Выбрать команду Формат линии тренда из контекстного меню, которое вызывается щелчком правой кнопки мыши по линии тренда.

3. Двойным щелчком по линии тренда.

 

Рис. 4.3. Определение формата линии тренда

 

На экране появится диалоговое окно Формат линии тренда (рис. 4.3), содержащее три вкладки: Вид, Тип, Параметры, причем содержимое последних двух полностью совпадает с аналогичными вкладками диалогового окна Линия тренда (см. рис. 4.1, 4.2). На вкладке Вид можно задать тип линии, ее цвет и толщину.

Задание 1. С таблицей данных о прибыли автотранспортного предприятия за 2000–2007 гг. (табл. 4.1) необходимо выполнить следующие действия:

1) построить диаграмму;

2) в диаграмму добавить линей­ную и полиномиальную (квадра­­тичную и кубическую) линии тренда;

3) вывести уравнения получен­ных линий тренда, а также величины достоверности аппроксимации R2 для каждой из них;

4) используя уравнения линий тренда, получить табличные данные по прибыли предприятия для каждой линии тренда за 2000–2009 гг.;

5) составить прогноз по прибыли предприятия на 2008 и 2009 гг.

 

Таблица 4.1

Исходные данные

№ п/п	Год	Прибыль, тыс. руб.
		159 500
		167 800
		165 400
		178 600
		178 900
		184 700
		184 000
		185 500
		?
		?

Методика выполнения

1. В диапазон ячеек A3:C13 рабочего листа Excel вводим исходную таблицу (рис. 4.4).

2. Выделив диапазон ячеек В4:С11, строим диаграмму.

3. Активизируем построенную диаграмму и по описанной выше методике после выбора типа линии тренда в диалоговом окне Линия тренда поочередно добавляем в диаграмму линейную, квадратичную и кубическую линии тренда. В этом же диалоговом окне открываем вкладку Параметры, в поле Название аппроксимирующей(сглаженной) кривойвводим наименование добавляемого тренда, а в поле Прогноз вперед на периодов задаем значение 2, так как планируется сделать прогноз по прибыли на два года вперед. Для вывода в области диаграммы уравнения регрессии и значения достоверности аппроксимации R2 включаем флажки Показывать уравнение на экране и Поместить на диаграмму величину достоверности аппроксимации (R^2). Для лучшего визуального восприятия изменяем тип, цвет и толщину построенных линий тренда, для чего воспользуемся вкладкой Вид диалогового окна Формат линии тренда.

 

 

Рис. 4.4. Исходная таблица

 

4. Для получения табличных данных по прибыли предприятия для каждой линии тренда за 2000–2007 гг. воспользуемся уравнениями линий тренда, представленными на рис. 4.5. Для этого в ячейки диапазона D3:F3 вводим текстовую информацию о типе выбранной линии тренда: Линейный тренд, Квадратичный тренд, Кубический тренд. Далее вводим в ячейку D4 формулу линейной регрессии и, используя маркер заполнения, копируем эту формулу c относительными ссылками в диапазон ячеек D5:D13. Следует отметить, что каждой ячейке с формулой линейной регрессии из диапазона ячеек D4:D13 в качестве аргумента стоит соответствующая ячейка из диапазона A4:A13. Аналогично для квадратичной регрессии заполняется диапазон ячеек E4:E13, а для кубической регрессии – диапазон ячеек F4:F13. Таким образом, составлен прогноз по прибыли предприятия на 2008 и 2009 гг. с помощью трех трендов. Полученная таблица значений представлена на рис. 4.6.

 

Рис. 4.5. Динамика прибыли предприятия, линейная,
квадратичная и кубическая линии тренда

 

 

Рис. 4.6. Прогнозирование прибыли предприятия