 |
Трендовый анализ и прогнозирование
|
|
|
|
При обработке статистических данных часто возникает необходимость графического представления их в виде плавной кривой линии, проходящей как можно ближе к заданным точкам, т.е. аппроксимирования данных. Эта кривая называется линией наилучшего соответствия или линией тренда.
Если линию тренда продолжить «в будущее» по оси времени, то можно будет прогнозировать ситуацию на определенный период времени.
Excel имеет специальный аппарат для графического анализа моделей, в том числе возможность построениялиний тренда, которые могут использоваться для анализа и прогнозирования данных.
Упражнение 4.3. Проанализировать спроспродукции (шт.) по месяцам: построить линию тренда, составить прогноз на следующий месяц и вывести уравнение на график.
| Месяц | Спрос(шт.) |
| январь | |
| февраль | |
| март | |
| апрель | |
| май | |
| июнь | |
| июль |
Порядок действий:
1) Построить таблицу спроса продукции в течение года.
2) Построить диаграмму (тип -
График с маркерами);
3) Построить линию тренда:
§ выделить ряд данных - щёлкнуть по линии графика;
§ выполнить команду контекстного меню Добавить линию тренда;
§ выбрать вид тренда в диалоговом окне:
полиномиальный 4-ой степени;
4) Форматировать линию тренда:
§ выделить линию тренда;
§ щёлкнуть правой кнопкой мыши - выбрать команду Формат линии тренда;
§ установить пунктирный вид линии тренда - вкладка Вид;
5) Выполнить прогнозирование на один месяц вперед:
§ выделить линию тренда - щёлкнуть по линии тренда;
§ выполнить команду контекстного меню Формат линии тренда - Параметры;
§ установить параметры:
- «Прогноз: вперед на 1 период»;
- «Показывать уравнение на диаграмме»;
|
|
|
- «Поместить на диаграмму величину достоверности
аппроксимации (R^2)».
6) Проверить применение другого типа линии тренда с разными параметрами к линии графика.
Результат выполнения упражнения 4.2 представлен на рис.8.
Рис.8. Построение линии тренда (упр.4.3.)
Задача 4.1. Построить круговую диаграмму выручки от продаж различных товаров (лабораторная работа №1, стр.20). Выделить сектор наибольшей выручки, проставить процентный доход от продаж.
Задача 4.2. Треугольник на плоскости задан координатами своих вершин. Изобразить его на диаграмме.
|
Задача 4.4. Построить для заданных значений х график функции y=F(x) (можно воспользоваться данными табл. 1) и линию тренда. С помощью уравнения линии тренда найти значения функции в точках х = 3,5; 4,0; 4,5.
Адресация
Формулы, как известно, могут ссылаться как на отдельные ячейки, так и на блоки ячеек, которые могут находиться на данном рабочем листе либо на других листах рабочей книги.
При ссылке на ячейку можно использовать описанный ранее способ: А1, В4, С13 и т.д. Такая адресация называется относительной. При её использовании Excel запоминает адреса других ячеек формулы относительно текущей ячейки.
При перемещении формулы в новое место ссылки в формуле не изменяются.
При копировании формулы относительные адреса ссылки
автоматически настраиваются на новое местоположение в таблице.
Абсолютные адреса используются в тех случаях, когда нужно сохранить ссылку на конкретную ячейку или блок при копировании формул.
Задать абсолютный адрес можно, поставив знак $ перед именем столбца и номером строки, например: $A$1
Сделать адрес абсолютным возможно и так:
|
|
|
§ выделить ячейку с формулой;
§ установить курсор мыши на адресе ячейки в строке формул;
§ выделить его, дважды щелкнув клавишей мыши;
§ нажать клавишу
Смешанная адресация используется, когда необходимо зафиксировать либо столбец ($A1), либо строку (A$1) в адресе ячейки. В этом случае при копировании один параметр адреса изменяется, другой - нет.
Установить смешанную адресацию можно также с помощью клавиши F4, каждое последующее на-
жатие её задаёт один из возможных типов адресации.
Упражнение 5.1. Вычислить значения x+y и x+ky при заданных x, y, k.
Рис.9. Иллюстрация упражнения 5.1.
Упражнение 5.2.
Вычислить возраст сотрудников в заданной таблице.
Порядок вычислений:
1) ввести в ячейку D1 текущую дату -
§ курсор установить на D1;
§ вызвать Мастер функций - fx;
§ выбрать группу Дата и Время;
§ функцию Сегодня, ОК;
2) ввести в ячейку D5 формулу вычисления возраста:
= D$1 – С5;
скопировать формулу из D5 на блок D6:D9.
3) задать возраст в формате года –
§ выделить столбец Возраст;
§ выполнить команду Формат ячеек /Число/(Все форматы);
§ установить формат – ГГ.
Упражнение 5.3. Вычислить значения функции двух переменных Z = X2 + Y, где X = 2, 4, 6, 8… Y = 1, 3, 5, 7, …
Решение.
1) 
ввести значения Х в столбец А, начиная с ячейки А2, автозаполнением;
2) ввести значения Y в строку 1, начиная с ячейки В1, автозаполнением;
3) ввести в ячейку В2 формулу: = А2^2+B1;
4) отредактировать её для заполнения на всю таблицу:
=$А2^2+B$1
5)
 |
заполнить формулой столбец В, затем всю таблицу.
Результат:
Задача 5.1. В таблице «Анализ спроса и продаж» (лаб. работа №1, стр.20) подсчитать выручку от продаж в долларах США, задав предварительно в качестве справки курс доллара к рублю.
Задача 5.2. Вывести на рабочем листе таблицу умножения
чисел от 1 до 10.
Задача 5.3. Имеется список персонала с соответствующим каждому окладом.
a) Всем сотрудникам нужно начислить премию в размере 20% оклада, имея в виду, что процент премии может измениться.
b) Если оклад меньше 300 руб., то премия увеличивается на 5%.
c) Премиальный фонд ограничен суммой 200 руб. Каждому сотруднику начислить премию пропорционально его окладу и так, чтобы премия была кратна 10 руб.
G Указание. Применить Сервис/Подбор параметра и
|
|
|
функцию округления.
d) Подсчитать зарплату каждому сотруднику.
Задача 5.4. Числа Фибоначчи определяются рекуррентной формулой: F1=0, F2=1, … Fn= Fn-1+Fn-2, где n>2.
Рассчитать первые 14 чисел Фибоначчи.
Задача 5.5.
Задача 5.6. На предприятии персонал работает по графику: 12-часовая дневная смена, 12-часовая ночная смена, затем двое суток отдыха. Составить скользящий график для 8 человек на март (для 1-го - день, ночь, отдых, отдых, день,…; для 2-го - ночь, отдых, отдых, день,… и т.д.).
|
|
|
