Анализ и прогноз рядов наблюдений
Цель работы
Научиться использовать электронные таблицы для выбора оптимального решения поставленной задачи и проверки правильности построения математической модели.
Теоретическое введение
Трудно найти область знаний или хозяйственной деятельности, г де не приходилось бы принимать решения, основанные на знании поведения объекта в пространстве признаков или времени. В большинстве случаев это решение принимается на основании модели[1], базирующейся на знании предыдущих состояний объекта, на умении прогнозировать его поведение в будущем (или прошлом).
Excel предоставляет пользователю широкие возможности построения таких моделей и прогнозирования поведения объекта. Это прежде всего методы построения линий трендов для известных значений временных рядов[2], методы статистического анализа данных наблюдений, методы линейного и динамического программирования («Поиск решения») и др.
Рассмотрим применение этих методов на нескольких примерах, имеющих отношение к деятельности торговых предприятий.
Практическая часть
Постановка задачи
Составить прогноз затрат на питание населения в зависимости от дохода.
1. Построение линии тренда расходов на продукты питания в зависимости от уровня дохода семьи
2. Использование процедуры «Поиск Решения» для подбора коэффициентов функции аппроксимации данных наблюдений.
Алгоритм решения задачи. В таблице 6 приведены статистические данные опроса (январь 2001 года) населения по их затратам на приобретение продуктов питания.
Таблица 6 – Исходные данные
| Доход на члена семьи | Затраты на питание |
1. Для анализа приведенных данных постройте диаграмму рассеяния в корреляционном поле - Затраты семьи на приобретение продуктов - доходы семьи.
Выделите таблицу данных; выполните команду ВСТАВКАЮДИАГРАММА. В открывшемся диалоговом окне выберите пиктограмму «точечная».
Выполните указания «мастера диаграмм» и построить диаграмму, иллюстрирующую затраты семьи на продукты питания в зависимости от дохода.
2. Для построения линии тренда:
- выделите кривую (после щелчка мышью на кривой на ней должны появиться маркеры выделения);
- выполните команду ДИАГРАММА ðДОБАВИТЬ ЛИНИЮ ТРЕНДА
При построении линии тренда на вкладке «линия тренда» выполните следующие действия (рисунок 29):
- выберите наиболее подходящий тип кривой аппроксимации ( в нашем примере - это аппроксимация – «логарифмическая»)
- Установите флажки «Показывать уравнение на диаграмме» и «Поместить на диаграмме величину достоверности аппроксимации R2»
 |
При необходимости в группе списков «Прогноз» установить необходимую величину шага прогноза (необходимая величина шага прогноза вводится либо с клавиатуры, либо использованием стрелок).
После того как построены линии тренда и соответствующие им уравнения, не составляет труда вычислить новое значение Y (зависимой переменной) для нового значения Х (независимой переменной).
3. Вычислите теоретическую численность по формуле
( )
(смотри мастер функций), отклонение - модуль разности теоретических и фактических значений функции и погрешность - максимальное отклонение. В результате будет получена таблица 7.
4. Подберите значения коэффициентов а и b более точно, используя сервисную функцию Excel Поиск решения. В отличие от Подбора параметра - Поиск решения может для достижения нужного результата изменять или подбирать подходящие значения во многих ячейках (смотри процедуру Сервис - Поиск решения). После выполнения задачи произойдет изменение значений ячеек в соответствии с найденным решением.
Обратите внимание, что коэффициенты а и b изменились, а погрешность уменьшилась.
Таблица 7 – Расчетные данные
| a | b | № пп | Доход на члена семьи | Затраты на питание | Теоретические затраты на питание | Откло-нение |
| 496,4005 | 2743,728 | 500р. | 330р. | 341р. | 11,20623 | |
| 750р. | 540р. | 542р. | 2,479298 | |||
| 1 000р. | 700р. | 685р. | 14,71519 | |||
| 1 250р. | 800р. | 796р. | 3,946629 | |||
| 1 500р. | 890р. | 887р. | 3,442125 | |||
| 1 750р. | 980р. | 963р. | 16,92166 | |||
| 2 000р. | 1050р. | 1029р. | 20,63661 | |||
| 2 250р. | 1100р. | 1088р. | 12,16906 | |||
| 2 500р. | 1 140р. | 1140р. | 0,131949 | |||
| 2 750р. | 1180р. | 1187р. | 7,443966 | |||
| 3 000р. | 1210р. | 1231р. | 20,63645 | |||
| Погрешность | 20,63661 |
5. Определите затраты на питания в зависимости от доходов:
 |
а) Постройте на одной диаграмме совмещенные графики роста численности населения на основе статистических и теоретических данных (рисунок 30).
б) Проанализировав данные таблицы и графики, сделайте вывод об адекватности предложенной математической модели реальному процессу (т.е. вывод о правильности описания роста населения формулой (1)).
Варианты заданий для самостоятельной работы
Задача 1 «Кафе». На основании экспериментальных данных таблицы: определить при какой входной плате выручка владельца кафе будет оптимальной (таблица 7).
Таблица 7 – Экспериментальные данные для задачи «Кафе»
| Входная плата X, $ | Среднее число посетителей | ||||
| a | b | c | d | e | |
| 1,5 | 17,5 | 19,7 | 24,2 | ||
| 24,5 | |||||
| 2,5 | 21,4 | ||||
| 12,4 | 11,6 | 18,8 | 17,5 | ||
| 3,5 | |||||
| 9,2 | 8,9 | ||||
| 6,5 | 14,5 | 12,4 |
Примечание. Самостоятельно выберите тип линии тренда (по наилучшему значению критерия R2)
Задача 2 «Продажа жалюзи». В зависимости от солнечных дней и географического положения региона стремление людей приобрести жалюзи меняется, но не менее важным фактором является цена за установку. Возьмем несколько городов, находящихся в разных климатических условиях. Выясните оптимальную цену на установку для каждого из городов (таблица 8).
Таблица 8 – Экспериментальные данные для задачи «Продажа жалюзи»
| Цена за установку, $ | Количество проданных жалюзи размером 1,5*2,0 в среднем за день, шт. | |||
| Красноярск | Норильск | Краснодар | Минусинск | |
| 10,5 | 11,2 | |||
| 2,4 | 12,3 | |||
| 1,6 | 6,5 | |||
| 11,5 | 0,7 | 8,1 | ||
| 8,5 | 13,8 | 10,5 | ||
| 7,5 | 3,4 |
Лабораторная работа 8
Методы обработки и анализа экономической
информации в EXCEL
Цель работы
Получить представление о задачах анализа экономической информации и изучить основные информационные технологии решения задач бизнес-анализа; овладеть методами обработки и анализа экономической информации с помощью инструментов электронного процессора MS Excel.
Теоретическое введение
Задачи бизнес-анализа требуют применения информационной технологии выполнения сортировки, фильтрации и консолидации данных в электронных таблицах, формирования разнообразных итогов и сводов, а также выполнения экономических расчетов в таблицах для данных, удовлетворяющих заданным условиям. Например, обработка всевозможных прайс-листов компьютерных фирм является типичной задачей этого класса.
Для решения подобных задач в популярном электронном процессоре EXCEL необходимо представить электронную таблицу в виде списка, или базы данных. При этом списками в EXCEL называются таблицы, строки которых содержат однородную информацию. В терминологии EXCEL список называют также базой данных (БД), при этом строки таблицы — это записи базы данных, а столбцы — поля записей.
Чтобы превратить таблицу EXCEL в список, или базу данных, необходимо присвоить столбцам однострочные имена, которые будут использоваться как имена полей записей базы данных. Следует иметь в виду, что строка имен полей может состоять из нескольких строк заголовков, размещенных в одной строке таблицы EXCEL, как это показано в таблице 9.
Таблица 9 – Исходная таблица списочных данных
| Кредитор | Сумма кредита, тыс. руб. | Годовая ставка процента, % | Число дней, на которые выдан кредит |
| Ветров А.Р. | 400 000 | ||
| Захаров Л.Д. | 78 000 | ||
| Иванов В.А. | 10 000 | ||
| Каримов Р.А | 80 000 | ||
| Морозов К.Н. | 65 000 | 29,50 | |
| Петров А.А. | 50 000 | ||
| Сидоров П.Р. | 350 000 | 28,50 | |
| Черкасов К.Г. | 99 000 | ||
| ИТОГО: | 1 132 000 |
При создании списка на рабочем листе EXCEL необходимо соблюдать следующие правила:
• на одном рабочем листе не следует помещать более одного списка, поскольку некоторые операции, работают в определенный момент только с одним списком;
• следует отделять список от других данных рабочего листа хотя бы одним свободным столбцом или одной свободной строкой;
• имена полей списка должны располагаться в первой строке таблицы;
• для имён полей следует использовать форматирование заголовков столбцов, отличное от того, которое использовалось для данных списка;
• чтобы отделить имена полей от данных, следует поместить рамку по нижнему краю ячеек строки с именами столбцов. Нельзя использовать пустые строки или пунктирные линии;
• каждый столбец списка должен содержать во всех строках однотипные данные;
• не следует вводить дополнительные пробелы в начале ячеек данных, поскольку они влияют на сортировку и поиск.
Сортировка списков
Сортировку данных в заданном порядке в EXCEL можно выполнить с помощью команды Сортировка (меню Данные), предварительно выделив весь список с заголовками столбцов (кроме итоговых строк таблицы). Включение заголовков столбцов в область выделения позволяет использовать их в качестве ключей сортировки.
 |
Команда Сортировка открывает одноименное диалоговое окно, в котором можно задать до трех ключей с указанием порядка сортировки. Сортировка выполняется сначала по первому ключу, затем в строках с совпадающим значением первого ключа — по второму ключу, наконец, в строках с одинаковыми значениями первого и второго ключа — по третьему ключу. Например, для примера в таблице 1 сортировка списка производится по возрастанию Числа дней, на которые выдан кредит, а при одинаковом числе дней — по возрастанию Годовой ставки процента, требует задания параметров сортировки, как показано на рисунке 31.
Рисунок 31 - Образец задания параметров сортировки в MS Excel
Результат выполнения такой сортировки списка приведен в таблице 10.
Таблица 10 – Результат сортировки исходных списочных данных в MS Excel
| Кредитор | Сумма кредита, тыс. руб. | Годовая ставка процента, % | Число дней, на которые выдан кредит |
| Иванов В.А. | 10 000 | ||
| Сидоров П.Р. | 350 000 | 28,50 | |
| Захаров Л.Д. | 78 000 | ||
| Морозов К.Н. | 65 000 | 29,50 | |
| Петров А.А. | 50 000 | ||
| Черкасов К.Г. | 99 000 | ||
| Каримов Р.А | 80 000 | ||
| Ветров А.Р. | 400 000 | ||
| ИТОГО: | 1 132 000 |
Следует иметь в виду, что последовательность стандартной сортировки определяется содержанием и типом данных (например, от наименьшего отрицательного к наибольшему положительному числу или от наиболее ранней даты и времени к поздней), поэтому в случае необходимости другого порядка сортировки, определяемого пользователем, используют кнопку Параметры... в диалоговом окне Сортировка (в этом случае можно, например, отсортировать список по месяцам календаря: январь, февраль, март и т. д.).
Фильтрация списков
Если в списке необходимо выбрать часть информации в соответствии с некоторым условием, то следует использовать фильтр.
Для простых условий используют автофильтр, для использования которого сначала выделяют область списка или весь список, причем обязательно с заголовками столбцов (при этом имена столбцов списка EXCEL преобразует в имена полей записей базы данных). Затем используют команду Данные, Фильтр, Автофильтр, которая в строке заголовков таблицы отображает кнопки с раскрывающимися списками значений.
Рассмотрим применение автофильтра на примере прайс-листа компьютерных фирм, который приведен в таблице 11.
Таблица 11 – Фрагмент прайс-листа компьютерных фирм
| Тип | Cache, kb | ОЗУ | HDD | Видео | Примечание | Цена, USD |
| Pentium 133 | 1 Mb, 14" | Intel Triton, Quantum | ||||
| Pentium 133 | 1 Mb, 14" | Intel Triton, Quantum | ||||
| Pentium 166 | 1 Mb, 14" | Intel Triton, Quantum | ||||
| Pentium 166 ММХ | 1 Mb, 14" | Intel Triton, Quantum | ||||
| Pentium 166 ММХ | 2 Mb, 15" | Intel 430TX, Quantum | ||||
| Pentium 200 ММХ | 1 Mb, 14" | Intel Triton, Quantum | ||||
| Pentium 200 ММХ | 2Mb, 15" | Intel 430TX, Quantum | ||||
| Pentium 233 ММХ | 2 Mb, 15" | Intel 430TX, Quantum | ||||
| Pentium II 233 | 4 Mb, 15" | Intel 430LX, VA AGP Quantum | ||||
| Pentium II 300 | 4 Mb, 15" | Intel 430LX, VA AGP Quantum |
Автофильтр предполагает использование критериев поиска типа сравнение двух типов: по точному или шаблонному значению и по условию oтбopa.
Точное значение для сравнения выбирается из раскрывающегося списка для указанного поля, формируемого EXCEL при выполнении команды Данные, Фильтр, Автофильтр.
При выборе сравнения по условию задается критерий oтбopa, состоящий из двух предложений, связанных между собой логической связкой И либо ИЛИ. При этом каждое предложение имеет стандартную структуру:
< реляционный оператор > < значение >.
Тип реляционного оператора выбирается из предлагаемого перечня (больше, больше или равно, равно, содержит, начинается с и т.д.).
Выбор конкретного значения условия в одном из столбцов приводит к скрытию всех строк таблицы с другими значениями этого столбца. Последовательное применение этого механизма позволяет создать условия, объединенные логическим умножением (И).
Для задания сложного условия для значений элементов одного из столбцов в команде Автофильтр выбирается строка (Условие...), в которой можно ввести два условия для одного столбца и обьединить их логическими функциями И/ИЛИ. Задание условий Пользовательского автофильтра для прайс-листа компьютерных фирм, приведенного в таблице 11, показано на рисунке 32, а результаты выполнения такого фильтра приведены в таблице 12.
Рисунок 32 - Образец задания условий Пользовательского автофильтра в MS Excel
Таблица 12 – Результаты выполнения Пользовательского автофильтра в MS Excel
| Тип | Cache | ОЗУ | HDD | Видео | Примечание | Цена, USD |
| Pentium 166 ММХ | 1 Mb, 14" | Intel Triton, Quantum | ||||
| Pentium 166 ММХ | 2 Mb, 15" | Intel 430TX, Quantum | ||||
| Pentium 200 ММХ | 1 Mb, 14" | Intel Triton, Quantum | ||||
| Pentium 200 ММХ | 2Mb, 15" | Intel 430TX, Quantum | ||||
| Pentium 233 ММХ | 2 Mb, 15" | Intel 430TX, Quantum |
Автофильтр не позволяет вводить условия для разных столбцов, объединенных логическим сложением (ИЛИ), а также более сложные логические условия. Для фильтрации списка или базы данных по сложным критериям, включая вычисляемый критерии с использованием любой функции EXCEL, а также для получения результатов фильтрации в другом месте рабочего листа, вне исходного списка, применяется команда Расширенный фильтр из меню Данные.
Прежде чем выполнять вызов диалогового окна Расширенный фильтр, необходимо сформировать диапазон условий, который для удобства доступа целесообразно разместить перед исходным списком. Диапазон условий должен включать заголовки строк исходного списка и хотя бы одну строку с критериями отбора записей списка (таблица 13).
Таблица 13 - Образец обработки прайс-листа компьютерных фирм с диапазоном критериев для команды Расширенный фильтр в MS Excel
| A | B | C | D | E | F | G | |
| Тип | Cache | ОЗУ | HDD | Видео | Примечание | Цена, USD | |
| >1200 | <900 | ||||||
| Фрагмент базы данных прайс-листа компьютерных фирм | |||||||
| Тип | Cache | ОЗУ | HDD | Видео | Примечание | Цена USD | |
| Pentium 133 | 1 Mb, 14" | Intel Triton, Quantum | |||||
| Pentium 133 | 1 Mb, 14" | Intel Triton, Quantum | |||||
| Pentium 166 | 1 Mb, 14" | Intel Triton, Quantum | |||||
| Pentium 166 ММХ | 1 Mb, 14" | Intel Triton, Quantum | |||||
| Pentium 166 ММХ | 2 Mb, 15" | Intel 430TX, Quantum | |||||
| Pentium 200 ММХ | 1 Mb, 14" | Intel Triton, Quantum | |||||
| Pentium 200 ММХ | 2Mb, 15" | Intel 430TX,Quantum | |||||
| Pentium 233 ММХ | 2 Mb, 15" | Intel 430TX, Quantum | |||||
| Pentium II 233 | 4 Mb, 15" | Intel 430LX, VA AGP Quantum | |||||
| Pentium II 300 | 4 Mb, 15" | Intel 430LX, VA AGP Quantum |
Значения условий фильтрации, размещенные в одной строке диапазона, или области, критериев, объединяются логической функцией И, а значения условий в разных строках связываются функцией ИЛИ. Диапазон условий должен отделяться от исходного списка по крайней мере одной пустой строкой. Для примера прайс-листа компьютерных фирм на рисунке 33 показано формирование диапазона условий фильтрации, размещенных на одном листе с исходным списком. При этом диапазон критериев расположен на рисунке вверху списка на одном рабочем листе с исходным списком фильтрации.
 |
После ввода условий в область критериев маркер выделения следует пометить в одну из ячеек списка и вызвать окно команды Расширенный фильтр (рисунок 33).
Рисунок 33 - Образец окна команды Расширенный фильтр в MS Excel
Результат выполнения условий фильтра для прайс-листа (таблица 11) приведен в таблице 14.
Таблица 14 – Результаты фильтрации прайс-листа компьютерных фирм с помощью Расширенного фильтра в MS Excel
| Тип | Cache | ОЗУ | HDD | Видео | Примечание | Цена, USD |
| Pentium 166 ММХ | 2 Mb, 15" | Intel 430TX, Quantum | ||||
| Pentium 200 ММХ | 2Mb, 15" | Intel 430TX, Quantum | ||||
| Pentium 233 ММХ | 2 Mb, 15" | Intel 430TX, Quantum |
В диапазон условий команды Расширенный фильтр можно ввести вычисляемые критерии. При этом следует придерживаться следующих правил:
• заголовок над вычисляемым критерием не должен совпадать ни с одним заголовком списка, он может быть даже пустой ячейкой;
• ссылки на ячейки списка в левой части формулы вычисляемого критерия должны быть относительными, а ссылки на ячейки списка в правой части формулы критерия и ссылки на ячейки вне списки должны быть абсолютными.
В качестве примера выберем в компьютерном прайс-листе (таблица 3) все компьютеры, цена которых выше средней для данного списка. Зададим условия фильтрации двумя способами.
Сначала вычислим среднее значение цены в какой-либо ячейке вне списка, например в A3 (рисунок 3). Введем в ячейку A3 формулу вычисления среднего значения цены по столбцу G: =CP3HAЧ(G6:G15), которая для данного примера дает значение средней цены 649,9. Если столбцу цен (G6:G15) присвоить имя блока Цена, то формула вычисляемого критерия получит следующий вид: =СРЗНАЧ(Цена).[3] Для задания вычисляемого критерия в ячейку А1 введем заголовок Цена выше средней, а в ячейку А2 введем следующую формулу: =G6>$A$3, где G6 - первая ячейка столбца G, содержащего цены компьютеров. После выполнения команды Расширенный фильтр (при этом в поле Диапазон условий следует ввести ссылку на ячейки $А$1:$А$2, содержащие критерии oт6oрa записей списка) список будет содержать строки с ценой выше средней, как показано в таблице 15.
Таблица 15 – Список компьютеров, цена которых выше средней в прайс-листе исходного списка
| Тип | Cache | ОЗУ | HDD | Видео | Примечание | Цена (USD) |
| Pentium 200 ММХ | 2Mb, 15" | Intel 430TX, Quantum | ||||
| Pentium 233 ММХ | 2 Mb, 15" | Intel 430TX, Quantum | ||||
| Pentium II 233 | 4 Mb, 15" | Intel 430LX, VA AGP Quantum | ||||
| Pentium II 300 | 4 Mb, 15" | Intel 430LX, VA AGP Quantum |
Другой способ задания вычисляемого критерия не требует предварительного вычисления средней цены в отдельной ячейке прайс-листа. В ячейке А1 (рисунок 3) удалим заголовок, сделав ее пустой, а в ячейку А2 введем формулу: =G6>CP3HAЧ($G$6:$G$15), которая отфильтрует ячейки столбца G со значениями больше средней цены в прайс-листе.