- •Старооскольский технологический институт им.А.А.Угарова
- •Шафоростова е.Н. Информационные технологии
- •Часть 1
- •220700- Автоматизация технологических процессов и производств
- •230400 – Информационные системы и технологии
- •Содержание
- •Предисловие
- •1. Порядок и правила выполнения практических работ
- •Технологии операций с векторами
- •Вычисление произведения вектора на число
- •Технологии операций с матрицами
- •Суммирование и вычитание матриц
- •Вычисление произведения матриц
- •Решение систем линейных уравнений Метод обратной матрицы
- •Метод наименьших квадратов
- •Применение технологий при решении экономических задач
- •Моделирование последовательностей и рядов Создание массива элементов числовой последовательности
- •Приближенное вычисление пределов числовых последовательностей
- •Применение последовательностей в экономических моделях
- •Применение рядов в экономических моделях
- •МоделированИе и исследованИе функций Способы задания функций
- •Технология построения графической модели функции
- •Вычисление предела функции
- •Вычисление корней функции одной переменной
- •Решение уравнений
- •Численное вычисление производной функции одного переменного
- •Вычисление локальных экстремумов функции
- •Технология получения математической модели функции по ее табличному представлению
- •Применение технологии исследования функций для решения экономических задач Кривые спроса и предложения, точка равновесия
- •Технология построения и исследования паутинной модели рынка
- •Вычисление предельных экономических показателей
- •Вычисление эластичности экономических показателей
- •Технология численного вычисления определенного интеграла
- •Технология приближенного вычисления
- •Технология точного вычисления
- •1.2. Задая для практической работы
- •1.3. Контрольные вопросы
- •Практическая работа №2 модели и технологии статического анализа
- •2.1 Теоретическое введение
- •Генерация случайной величины, распределенной по равномерному закону
- •Генерация случайных чисел в табличном процессоре
- •Вычисление числовых характеристик параметров случайных величин Вычисление числовых характеристик распределений вероятностей
- •Вычисление вероятности отдельных значений случайных величин Табличный закон распределения
- •Биноминальное распределение
- •Нормальный закон распределения
- •Технологии решения задач статистического анализа Выборочный метод и выборочная функция распределения
- •Построение выборочной функции распределения
- •Технологии вычисления основных статистических характеристик
- •Вычисление доверительного интервала для среднего значения
- •Технология проверки соответствия данных, полученных экспериментально, теоретическому распределению
- •Решение задач статистического анализа Технология решения задач дисперсионного анализа
- •Заполняемость гостиниц
- •Технологии решения задач корреляционного анализа
- •Данные наблюдений
- •Технология решения задач регрессионного анализа
- •2.2. Задания для практической работы
- •2.3.Контрольные вопросы
- •Вычисления по простым переменным ставкам
- •Вычисление накопленной суммы при реинвестировании по простым процентам
- •Дисконтирование по простым процентам
- •Финансовые расчеты по сложным процентам Вычисление наращения
- •Расчет номинальной и эффективной ставки процентов
- •Дисконтирование по сложной ставке процентов
- •Расчет стоимости ценных бумаг
- •Бз (Норма; Кпер; Выплата; Нз; Тип)
- •Бзраспис (Первичное; План)
- •Пз (Норма, Кпер, Выплата, Бс, Тип)
- •Норма (Кпер, Выплата, Пз, Бс, Тип, Предположение)
- •3.2. Задания для практической работы
- •3.3.Контрольные вопросы
- •Практическая работа №4. Численное решение уравнений средствами ms excel
- •4.1.Теоретическое введение
- •4.2. Задания для практической работы
- •4.3.Контрольные вопросы
- •Использование надстройки «Поиск решения»
- •Технология решения транспортной задачи линейного программирования
- •5.2.Задания для практической работы
- •5.3.Контрольные вопросы
- •Практическая №6 технология Решения задач дискретного программирования
- •6.1.Теоретическое введение
- •6.2. Задания для практической работы
- •6.3. Контрольные вопросы
- •Глоссарий
- •Список литературы
- •Учебное издание Шафоростова Елена Николаевна Информационные технологии
Технология решения задач регрессионного анализа
Важную роль при исследовании взаимосвязей между статистическими выборками кроме корреляционного и дисперсионного анализа играет регрессионный анализ. Регрессия позволяет проанализировать воздействие на какую-либо зависимую переменную одной или более независимых переменных и позволяет установить аналитическую форму (модель) этой зависимости в виде аппроксимирующего полинома.
Если рассматривается зависимость между одной зависимой переменной у и несколькими независимыми х1, х2, ..., хп, то речь идет о множественной линейной регрессии. В этом случае уравнение регрессии имеет вид
у = а0 + а1х1+а2х2+…+anxn,
где а1, а2,…, ап - коэффициенты при независимых переменных, которые нужно вычислить (коэффициенты регрессии); ао - константа. При построении регрессионной модели важнейшими моментами являются оценка ее адекватности (эффективности) и значимости, на основании которых можно судить о возможности применения в практике полученной модели.
Мерой оценки адекватности регрессионной модели является коэффициент детерминации R2 (R-квадрат), который определяет, с какой степенью точности полученное уравнение регрессии аппроксимирует исходные данные.
Значимость регрессионной модели оценивается с помощью критерия Фишера (F-критерия). Если величина f-критерия значима (р < 0,05), то регрессионная модель является значимой.
В табличном процессоре можно аппроксимировать экспериментальные данные линейным уравнением до 16-го порядка
у = а0 + а1х1+а2х2+…+a16x16.
Для вычисления коэффициентов регрессии служит инструмент Регрессия, который можно включить следующей последовательностью операций.
1. Выполнить команду Сервис/Анализ данных.
2. В раскрывшемся окне диалога Анализ данных выбрать из списка строку Регрессия - раскроется окно диалога Регрессия.
3. В группе Входные данные в поле Входной интервал у указать адресную ссылку ми диапазон, содержащий значения зависимой переменной, а в поле Входной интервал X- ссылку на диапазон, содержащий переменных, т.е. переменных, влияние которых на зависимую переменную у оценивается. Установить флажок Метки, если исходная таблица имеет названия столбцов и флаок Константа-ноль, если а0 = 0.
4.В группе Параметры выхода указать адресную ссылку на ячейку рабочего листа, которая будет являться верхней левой ячейкой результирующей таблицы.
Если необходимо получить визуальную картинку отличия экспериментальных точек от предсказанных регрессионной моделью, то установить флажок График подбора.
Если нужно получить график нормальной вероятности, то установить флажок График нормальной вероятности.
В выходном диапазоне после выполнения вычислений отображаются результаты дисперсионного анализа, коэффициенты регрессии, стандартная погрешность вычисления у, среднеквадратичные отклонения, количество наблюдений, стандартные погрешности для коэффициентов.
Значения коэффициентов регрессии размещаются в столбце Коэффициенты:
- у - пересечение а0;
- х1, - коэффициент а1;
- х2 - коэффициент а2 и т.д.
В столбце Р-Значение содержится оценка достоверности отличия соответствующих коэффициентов от нуля. Если Р > 0,05, то коэффициент можно считать нулевым. Это означает, что соответствующая независимая переменная практически не влияет на зависимую переменную.
Значение R-квадрат определяет, с какой степенью точности регрессионное уравнение будет аппроксимировать экспериментальные данные.
Если R-квадрат > 0,95, то точность аппроксимации высокая.
При 0,8 < R-квадрат < 0,95 аппроксимация удовлетворительная.
В случае когда R-квадрат < 0,6 точность аппроксимации недостаточна и модель требует улучшения.
Кроме инструмента Регрессия в табличном процессоре для получения параметров уравнения регрессии есть функция ЛИНЕЙН и функция ТЕНДЕНЦИЯ для получения значения у в требуемых точках.
Пример 2.16 Имеются статистические данные о затратах, связанных с рекламой по телевидению, с рекламой в метро и объеме реализации продукции в рублях, приведенные в таблице. Требуется найти регрессионные коэффициенты для независимых переменных Расходы на рекламу по телевидению и Расходы на рекламу в метро на объем реализации продукции и построить уравнение регрессии.
Решение
На рабочем листе в диапазон А1:С8 введем данные приведенной таблицы 2.9.
Таблица 2.9
Включим инструмент Регрессия.
В открывшемся диалоговом окне Регрессия установим параметры (рис. 2.24)
Рис. 2.24
- Входной интервал у - диапазон С1:С8;
- Входной интервал х - диапазон А1:В8;
- Флажок Метки;
- Выходной интервал - адрес D1;
- Флажок График нормальной вероятности;
- Флажок График остатков.
После щелчка на кнопке ОК в диапазон D1:L21 будет выведен результат регрессионного анализа (рис. 2.25).
Рис. 2.25
Полученные результаты и их интерпретация
- Коэффициент детерминации R-квадрат =0,974641 (аппроксимация высокая).
- Значимость F= 0,000643 (р < 0,05 - регрессионная модель значима).
- Y- пересечение а0= 2102438,6.
- а1, = 6,4004 - коэффициент при независимой переменной Затраты на рекламу по телевидению.
- а2 = -54,068 - коэффициент при независимой переменной Затраты на рекламу в метро.
С учетом полученных данных функциональная зависимость величины прибыли от затрат на рекламу запишется в виде полинома
у = 2102438,6 + 6,4004% - 54,068х2;
x1 – величина затрат на рекламу по телевидению;
х2 - величина затрат на рекламу в метро.
Используя полученное уравнение регрессии, можно решить задачу оптимизации прибыли или спрогнозировать ожидаемую прибыль при другом распределении средств на рекламу.
Пример 2.17 Организация может истратить на рекламу по телевидению и на рекламу в метро 170 000 руб. Требуется оптимальным образом распределить затраты на различные виды рекламы с целью получения максимальной прибыли.
Решение. Математическая модель
В качестве целевой функции возьмем уравнение регрессии, полученное в примере 2.16:
у=2102438,6 + 6,4004х1, - 54,068х2 => max.
Ограничения: х1 + х2 <= 170 000, х1 >= 0, х2 >= 0.
Используя инструмент Поиск решения, решим задачу. Модель и результат решения приведены на рис. 2.26.
Рис. 2.26