7.5.2. Пример
Суть метода заключается в следующем.
-
Сначала выделяется наиболее существенная регулярная составляющая ряда. Для этого необходимо представить имеющиеся данные в виде графика и хотя бы визуально оценить ее вид.
-
Все множество гладких функций можно описать относительно небольшим набором математических функций. Наиболее простые из них:
линейная ;
логарифмическая ;
степенная ;
экспоненциальная .
Здесь а0, а1, а2 – некоторые числовые коэффициенты.
-
Для всех указанных функций в Excel имеется возможность строить так называемые лини тренда.
При выполнении лабораторной работы используется программа Excel «Временные ряды.xls». С помощь кнопки «Генерация» произвести генерацию данных. При этом программа случайным образом выбирает вид регулярной составляющей и выводит полученные данные на лист Excel. Фрагмент данных приведен в таблице.
В | С |
1 | 781,4921 |
2 | 1179,071 |
3 | 1021,827 |
4 | 1613,392 |
5 | 1330,227 |
6 | 1097,985 |
… | … |
… | … |
Скопировать эти данные в новую книгу и на их основе построить диаграмму. Тип диаграммы – график. Для получения линии тренда:
Выделить построенную диаграмму – выбрать пункт «Диаграмма» главного меню – Добавить линию тренда – Выбрать тип линии – Через закладку «Параметры» поставить галочку на пункт «Показывать уравнение на диаграмме» - Ok.
Если выбран линейный тренд, для представленных данных уравнение имеет вид y=193,87x+234,50.
-
С помощью полученного уравнения рассчитать значение параметра ряда в тех же точках. Для этого в соседнем с данными столбце (D) ввести формулу =193,87*В1+234,5 и скопировать ее на весь столбец. Должно получиться следующее:
В | С | D |
1 | 781,4921 | 428,37 |
2 | 1179,071 | 622,24 |
3 | 1021,827 | 816,11 |
4 | 1613,392 | 1009,98 |
5 | 1330,227 | 1203,85 |
… | … | … |
… | … | … |
-
Получить разность между рассчитанными и имеющимися данными.
Для рассматриваемого примера должно получиться следующее:
B | C | D | E |
1 | 781,4921 | 428,37 | 353,1221 |
2 | 1179,071 | 622,24 | 556,8315 |
3 | 1021,827 | 816,11 | 205,7171 |
4 | 1613,392 | 1009,98 | 603,4116 |
5 | 1330,227 | 1203,85 | 126,3771 |
… | … | … | … |
… | … | … | … |
-
По данным столбца Е построить диаграмму остатков (тип диаграммы – график). Для представленных данных получается примерно следующее (рис. 7.10):
Рис. 7.10. Результат удаления из временного ряда регулярной составляющей
-
Оценить качество выбранной линии тренда можно как визуально, так и с помощью суммы остатков. Эта сумма должна быть близкой к нулю. В данном случае она будет равна -0,97. Визуальная оценка состоит в том, что график остатков должен быть похож на искаженную синусоиду.
-
Если полученная диаграмма непохожа на синусоиду (например, большая часть данных смещена относительно оси Х), то это означает, что вид регулярной составляющей выбран неправильно. Поэтому следует повторить этапы 3-7 с трендом другого вида.
В некоторых случаях визуальная оценка между конкурирующими трендами затруднена. Поэтому среди них следует выбрать тот, у которого сумма остатков меньше.
-
Выделение остатков завершает выделение регулярной составляющей ряда. Вторым этапом является выделение циклической составляющей ряда.
-
Для этого используется одна из периодических функций – обычно синусоида, уравнение которой в общем виде выглядит следующим образом:
, (7.18)
где b0, b1, b3, b3 – некоторые числовые коэффициенты.
Коэффициенты отвечают за следующие параметры синусоиды:
b0 – за сдвиг синусоиды по оси Y.
b1 – за размах значений синусоиды.
b3 – за сдвиг синусоиды по оси Х.
b3 – за растяжение синусоиды по оси Х.
-
Для определения параметров синусоиды используется встроенное в Excel средство «Поиск решения».
-
Для организации вычислений с его помощью производится следующее:
Создается строка коэффициентов, в нее вводятся их начальные значения и по указанному выше уравнению рассчитываются значения остатков. Например:
E | F | G | H | I | J | K |
353,1221 | 287,6553 |
| b0 | b1 | b2 | b3 |
556,8315 | 504,8826 |
| 0 | 600 | 0 | 0,5 |
205,7171 | 598,497 |
|
|
|
| 2306951 |
603,4116 | 545,5785 |
|
|
|
|
|
126,3771 | 359,0833 |
|
|
|
|
|
-299,735 | 84,672 |
|
|
|
|
|
… | … |
|
|
|
|
|
… | … |
|
|
|
|
|
При этом в столбец F введена формула
=$H$2+$I$2*SIN($J$2+$K$2*B1),
которая затем скопирована на весь столбец F.
-
Для нахождения точных значений коэффициентов синусоиды:
а) Вычисляется функция СУММКВРАЗН, в качестве аргументов которой указываются столбцы E и F. Пусть это вычисление произведено в ячейке К3 и его начальный результат приведен в предыдущей таблице.
б) В ячейки I2, J2 и K2 вводятся примерные значения коэффициентов синусоиды. Их можно оценить следующим образом:
b1 – определяет размах синусоиды. Из предыдущего рисунка он примерно равен 600;
b2 – определяет сдвиг синусоид по оси Х. Из предыдущего рисунка он, примерно, равен 0;
b3 – определяет растяжение синусоиды по оси Х. Из предыдущего рисунка он равен, примерно, 0,5.
Эти значения и введены в предыдущую таблицу.
в) Курсор устанавливается на ячейку К3 и вызывается средство «Поиск решения». В появившемся окне переключатель установить в положение «Минимальное значение», а в поле «Изменяя ячейки» указать I2:K2 и, затем «Выполнить». Если все было сделано правильно, то для данных чисел получается следующее:
E | F | G | H | I | J | K |
353,1221 | 291,9481 |
| b0 | b1 | b2 | b3 |
556,8315 | 455,3418 |
| 1,014694 | 510,8945 | 0,116407 | 0,491859 |
205,7171 | 510,7799 |
|
|
|
| 1970646 |
603,4116 | 445,1186 |
|
|
|
|
|
126,3771 | 273,9255 |
|
|
|
|
|
-299,735 | 37,78814 |
|
|
|
|
|
-159,236 | -207,308 |
|
|
|
|
|
При этом уточнились как сами коэффициенты, так и рассчитанные по ним значения.
-
По данным столбцов E и F построить совместную диаграмму следующего вида (рис. 7.11)
Рис. 7.11. Результат сглаживания сезонной составляющей с помощью формулы (7.18)
Ее внешний вид позволяет визуально оценить качество аппроксимации остатков синусоидой.
-
В столбце G рассчитать остатки второго уровня, т.е. разность между остатками и синусоидой. Для этого в ячейку G1 вводится формула =E1-F1, которая затем копируется на весь столбец.
-
По данным столбца строится диаграмма остатков второго уровня (рис. 7.12).
Рис. 7.12. Диаграмма остатков второго уровня
Если регулярная составляющая была подобрана и рассчитана правильно и, если также правильно была рассчитана периодическая составляющая, то диаграмма должна иметь вид случайного недетерминированного ряда. Признаком случайности является значение суммы этих остатков. Она должна быть много меньшей, чем сумма остатков первого уровня. В данном случае она оказалась равной 0,002332, что намного меньше суммы остатков первого уровня (-0.97).
-
Используя уравнения отдельных составляющих, можно составить общее уравнение ряда. В данном случае оно будет выглядеть следующим образом:
y=235,515+193,87*x+510,89*Sin(0,116+0,49*x).
Полученное уравнение можно использовать для целей прогноза. Для этого достаточно подставить в него то значение Х, для которого мы хотим узнать значение Y.
- Содержание
- VII. Статистические методы 167
- Введение
- I. Общие методы работы
- 1.1. Работа с формулами
- 1.1.1. Общие сведения
- Вычисления сложных выражений
- 1.1.2. Задание
- 1.2. Математические функции
- 1.2.1. Общие сведения
- 1.2.2. Пример
- 1.2.3. Варианты заданий
- 1.3. Вычисления с условиями
- 1.3.1. Общие сведения
- 1.4. Работа со справочниками
- 1.4.1. Общие сведения
- 1.4.2. Варианты заданий
- «Разносортица»
- «Маршрутное такси»
- «Гостиница»
- «Автовокзал»
- «Книжное издательство»
- «Продукты»
- «Коттеджи»
- «Гастроли»
- «Туристическое агентство»
- «Комплектующие»
- «Авиаперевозки»
- «Винный погребок»
- «Сберкасса»
- «Мебельная фабрика»
- 16. «Сага о таре»
- 1.5. Работа с диаграммами
- 1.5.1. Общие сведения
- 1.5.2. Задание на построение диаграммы
- 1. Изменение настроек параметров диаграммы:
- 3. Форматирование рядов данных и их элементов:
- 4. Форматирование осей диаграммы:
- 5. Форматирование сетки, стен и основания:
- 6. Форматирование легенды:
- 1.5.3. Варианты заданий
- 1.6. Собственные функции
- 1.6.1. Общие сведения
- 1.6.2. Общие сведения о Visual Basic for Excel
- Математические операции
- Математические функции
- 1.6.3. Варианты заданий
- II. Численные методы
- 2.1. Решение алгебраических уравнений Средство «Подбор параметра»
- 2.1.1. Общие сведения
- 2.1.2. Пример
- 2.1.3. Варианты заданий
- 2.2. Решение систем уравнений
- 2.2.1. Общие сведения
- 2.2.2. Реализация расчетов в Excel
- 2.2.3. Варианты заданий
- 2.3. Задачи оптимизации
- 2.3.1. Общие сведения
- 2.3.2. Пример
- 2.3.3. Варианты заданий
- III. Базы данных в ms Excel
- Каждое из последующих заданий необходимо выполнять на отдельном листе!!!
- Сортировка
- 3.1.1. Общие сведения
- 3.1.2. Варианты заданий
- Фильтрация данных
- 3.2.1. Общие сведения
- Варианты заданий
- Средство «Итоги»
- 3.3.1. Общие сведения
- Сводные таблицы
- 3.5. Функции для работы с базами данных
- 3.6. Консолидация данных
- 3.6.2. Варианты заданий
- 3.7. Контрольная работа по теме «Базы данных в Excel»
- 3.7.1. Указания
- 2. Скопируйте указанный файл в свою рабочую папку и вся дальнейшая работа должна производиться только с этой копией.
- 3.7.2. Варианты заданий
- Вариант 12
- Вариант 13
- Вариант 14
- Вариант 15
- IV. Макросы в ms Excel
- 4.1. Макросы для автоматизации работ
- 4.1.1. Пример
- 4.2. Вычислительные макросы
- 4.2.1. Пример 1. Расчет точки безубыточности
- 4.2.2. Пример 2. Моделирование процесса налогообложения [8]
- 4.3. Использование макросов для создания интерфейса
- V. Технология создания информационной системы средствами ms Excel
- 5.1. Постановка задачи
- 5.2. Требования к системе
- 5.3. Общая архитектура ис
- 5.3.1. Проектирование общей архитектуры
- 5.3.2. Создание общей архитектуры
- 5.3.2.1. Создание объектов ис
- 5.3.2.2. Организация переходов между объектами
- 5.3.2.3. Этапы создания интерфейса
- 5.4. Организация работы с базой данных
- 5.4.1. Заполнение таблиц модельными данными
- 5.4.2. Работа с данными
- 5.4.3. Сортировка
- 5.4.4. Поиск данных
- 5.4.5. Отчеты
- 5.4.5.1. Использование функций
- 5.4.5.2. Использование сводных таблиц
- 5.4.5.3. Использование элементов управления
- 5.4.5.5. Использование встроенных функций
- Функция должна быть в англоязычном варианте.
- 5.4.5.6. Варианты заданий
- 5.4.6. Расчет заработной платы
- 5.4.6.1. Постановка задачи
- 5.4.6.2. Интерфейс расчета заработной платы
- 5.4.6.3. Реализация расчетов
- VI. Экономические расчеты
- 6.1. Задачи на проценты
- 6.1.1. Общие сведения
- 6.1.2. Пример.
- 6.1.3. Варианты заданий
- 6.2. Финансовые функции
- 6.2.1. Общие сведения
- Бс(Ставка, Кпер, Плт, Пс, Тип).
- 6.2.3. Варианты заданий
- 6.3. Анализ межотраслевого баланса (модель Леонтьева)
- Основные понятия
- Математическая модель межотраслевого баланса
- 6.3.4. Варианты заданий
- 6.4. Задача об эквивалентности ставок [1]
- 6.4.1. Основные формулы
- 6.4.2. Постановка задачи
- 6.4.3. Варианты заданий
- 6.5. Методы анализа проектов (использование средства «Подбор параметра»)
- 6.5.1. Термины и определения
- 6.5.2. Примеры
- Варианты заданий
- 6.6. Выбор оптимального портфеля инвестиций
- 6.6.1. Основные определения
- 6.6.2. Пример
- 6.6.3. Варианты заданий
- 6.7. Вычисление налогов
- 6.7.1. Предварительные замечания
- 6.7.2. Пример.
- 6.7.3. Варианты заданий
- 6.8. Моделирование динамических процессов
- 6.8.1. Общие сведения
- 6.8.2. Порядок выполнения работы
- 6.8.3. Пример
- Результаты должны отражать основные закономерности процесса
- 6.8.4. Варианты заданий
- Производство в условиях постоянного спроса
- Конкуренция
- Сезонное производство
- Рыночные отношения
- Взаимопоставки
- Цены в условиях ограниченного объема выпуска
- Северный завоз
- Два пароходства
- Последовательные перевозки
- Антимонопольная система
- Конъюнктура
- Количество информации в Интернет
- Валютная интервенция
- Реклама
- VII. Статистические методы
- 7.1. Определение характеристик случайных величин
- 7.1.1. Содержание работы
- 7.1.2. Варианты заданий
- 7.2. Дисперсионный анализ
- 7.2.1. Общие сведения
- 7.2.1. Пример
- 7.2.3. Методы, применяемые после дисперсионного анализа
- 7.2.4. Варианты заданий
- 7.3. Регрессионный анализ
- 7.3.1.Общие сведения
- 7.3.2. Порядок выполнения работы
- 7.3.3. Проверка уравнения регрессии на адекватность
- 7.3.4. Использование уравнения для прогноза
- 7.4. Кластерный анализ
- 7.4.1. Общие положения.
- 7.4.2. Примеры
- 7.4.3. Формализация процесса кластеризации
- 7.4.4. Порядок выполнения работы
- 7.4.5. Задания
- 7.5. Анализ временных рядов
- 7.5.1. Общие сведения
- 7.5.2. Пример
- Литература
- Приложения
- Технология генерации модельных данных
- Приложение 2 Районы и города Чувашии в цифрах [9]
- Тексты макросов Текст макроса для кластерного анализа
- Текст макроса для решения систем дифференциальных уравнений
- Текст макроса для генерации временного ряда
- Приложение 4 Транспорт и связь
- Статистические данные по регионам рф [5]