4. Построение линий трендов

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

• линейная;

• логарифмическая;

• полиномиальная;

• степенная;

• экспоненциальная.

Возможно также построение линий трендов на основе скользящих средних.

Построение линий трендов выполняется в следующей последовательности:

• по фактическим значениям ряда динамики с помощью мастера диаграмм построить соответствующий график;

• вызвать контекстное меню для кривой фактических значений, щелкнув по ней правой кнопкой мыши;

• выбрать команду Добавить линию тренда;

• в появившемся диалоговом окне Линия тренда задать тип линии тренда, например, линейный;

• раскрыть вкладку Параметры;

• указать длительность прогноза;

• включить переключатели: показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации¹ (R²);

• нажать кнопку ОК.

Чтобы удалить линию тренда, следует выделить ее и нажать [Del].

Контрольные вопросы

1. Какие встроенные статистические функции можно использовать при проведении статистического анализа средствами Excel?

2. Каковы правила записи аргументов функции РАНГ?

3. Для каких целей используется дополнение «Пакет анализа»?

4. Как можно вызвать мастер функций?

5. Какова последовательность работы с мастером функций?

6. Каков формат записи статистических функций при работе с БД?

7. Как загрузить дополнение «Пакет анализа»? Что надо предпринять, если в меню Excel отсутствует команда Сервис—Анализ данных?

8. Как следует подготовить исходные данные для проведения регрессионного анализа средствами дополнения «Пакет анализа»?

9. Как построить линию тренда и определить прогнозное значение исследуемого показателя?

10. Что показывает величина R²?

Практическая часть

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

2. Создать новый лист Лист10. Присвоить ему имя Статистика.

3. На листе Статистика создать таблицу 3.9 «Исходные данные для статистической обработки».

Таблица 3.9

Исходные данные для статистической обработки

Дата	Производство продукции, кг
	цех 1	цех 2	цех 3	Всего
1.04
…
14.04

Данные в таблицу 3.9 ввести, используя ссылки на данные таблицы «Производство продукции с шифром 0103 в апреле месяце», расположенной на листе Апрель. Последний столбец расcчитать.

4. Выполнить статистическую обработку данных по производству продукции в отдельных цехах и в целом по предприятию за период с 01.04 по 14.04 (таблица 3.10). Для этого использовать мастер функций, а также инструмент «Описательная статистики» дополнения «Пакет анализа». Результаты обработки расположить на листе Статистика.

Таблица 3.10

Результаты статистической обработки данных по производству продукции

Показатели	Цех 1	Цех 2	Цех 3	В целом по предприятию
Среднее значение
Минимальное значение
Максимальное значение
Размах вариации
Дисперсия
Стандартное отклонение
Коэффициент вариации,%
Количество объектов

5. Используя функцию Ранг, определить ранг значений показателя «Производство продукции в целом по предприятию». Результаты показать в таблице 3.11. Таблицу разместить на том же самом рабочем листе. Исходные данные в таблицу 3.11 ввести, используя ссылки на данные таблицы 3.9.

6. Создать новый лист Лист11. Присвоить ему имя Тренд.

Таблица 3.11

Ранг значений показателя «Производство продукции в целом по предприятию»

Дата	Производство продукции в целом по предприятию, кг	Ранг
1.04
…
14.04

7. На листе Тренд подготовить исходные данные для построения тренда в виде матрицы, первый столбец (X) которой содержит номера периодов (числа: 1,2,…,14), а второй (Y) — значения показателя «Производство продукции в целом по предприятию». В первой строке матрицы задать метки: «Дни» и «Производство, всего». Значения показателя ввести, используя ссылки на данные таблицы 3.9.

8. Выполнить регрессионный анализ, используя инструмент «Регрессия» дополнения «Пакет анализа». В качестве входного интервала Y указать столбец значений анализируемого показателя, а в качестве входного интервала X — столбец, содержащий номера периодов. Включить переключатель Метки. Результаты анализа вывести на текущий рабочий лист, указав адрес верхней левой ячейки выходного списка. Определить коэффициенты регрессии (тренда).

9. Рассчитать теоретические значения динамического ряда, используя формулы Excel. Сделать прогноз развития производства на последующие три дня.

10. С помощью мастера диаграмм изобразить график производства продукции в целом по предприятию за период с 01.04 по 14.04.

11. Построить линии трендов с использованием линейной, полиномиальной и экспоненциальной функций. Для каждой из этих функций показать на графике уравнение кривой и величину R².

12. По величине R² определить кривую, которая наиболее точно воспроизводит характер изменения показателя за исследуемый период. Эту кривую оставить на графике, остальные кривые удалить.

1 В качестве разделителя аргументов могут использоваться различные символы в зависимости от настроек Excel и/или Windows.

2 Ранг числа — порядковый номер числа относительно других чисел в списке аргументов.

3 Для переключения режимов абсолютной и относительной адресации можно использовать [F4].

1 Форматирование данных может осуществляться перед вводом исходных данных и формул.

2 Перед вводом дробных чисел следует выяснить, каким символом отделяется целая часть числа от дробной. В зависимости от настроек Excel и/или Windows это может быть точка или запятая.

1 Для этого необходимо выделить первый интервал ячеек для консолидации на соответствующем листе ЭТ и нажать в окне Консолидация кнопку Добавить. Затем повторить те же действия для других интервалов ячеек, которые необходимо консолидировать.

1 Чтобы названия показателей уместились целиком каждое в отдельной ячейке, следует для интервала ячеек А3:А10 включить перенос по словам.

1 Чтобы выделить несколько несмежных интервалов ячеек, надо при выделении интервалов удерживать нажатой [Ctrl].

1 Достаточно указать одну ячейку списка. После подачи команды сортировки Excel автоматически выделит весь список. Однако в этом случае итоговая строка списка (если она не отделена от списка пустой строкой) также будет включена в сортировку.

2 Ключом сортировки является столбец, по которому Excel должен отсортировать данные.

1 Символы * и ? знакомы Вам по операционным системам MS DOS и Windows, здесь они играют ту же роль.

2 Если перед подачей команды область БД была выделена, то исходный диапазон будет указан автоматически.

1 Выбранные для группирования элементы (дата и время) должны соответствовать форматам Excel.

1 В качестве разделителя аргументов функции может использоваться запятая (зависит от настроек Windows и/или Excel).

2 Вложенная функция — функция, значение которой выступает в качестве аргумента основной функции. Например, функция МИН(СУММ(B2:B15);CУММ(C2:C15)) позволяет вычислить минимальное значение из двух чисел, первое из которых является суммой чисел, находящихся в интервале B2:B15, второе — суммой чисел, находящихся в интервале C2:C15.

1 Исходные данные для регрессионного анализа должны быть предварительно подготовлены на рабочем листе в виде матрицы.

1 R² показывает степень приближения тренда к фактическим значениям динамического ряда.