textarchive.ru

Главная > Лабораторная работа


Лабораторная работа № 4

Использование MicrosoftOfficeExcel для анализа данных и решение задач оптимизации

Цель работы: изучить встроенные в Excel возможности анализа данных на примере проведения регрессионного анализа. Ознакомиться со стандартными надстройками Excel на примере надстройки «Принятие решений», использовать её для решения задачи линейного программирования.

Методика выполнения работы

Статистический анализ данных, моделирование и прогноз

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

Регрессионный анализ

Регрессионный анализ позволяет получить функциональную зависимость между некоторой случайной величиной Y и некоторыми влияющими на Y величинами X. Такая зависимость получила название уравнения регрессии. Различают простую (парную) и множественную регрессию линейного и нелинейного типа.

Пример простой линейной регрессии:

y=m1x+b.

Пример множественной линейной регрессии:

y=m1x1+m2x2+... + mkxk+b. (1)

Для оценки степени связи между величинами используется коэффициент множественной корреляции R Пирсона (корреляционное отношение), который может принимать значения от 0 до 1. R=0 если между величинами нет никакой связи и R=1, если между величинами имеется функциональная (детерминированная) связь. В большинстве случаев R принимает промежуточные значения от 0 до 1. Величина R2 называется коэффициентом детерминации.

Задачей построения регрессионной зависимости является нахождение вектора коэффициентов M модели (1) при котором коэффициент R принимает максимальное значение.

Для оценки значимости R применяется F-критерий Фишера, вычисляемый по формуле:

где n - размер выборки (количество экспериментов); k - число коэффициентов модели. Если F превышает некоторое критическое значение для данных n и k и принятой доверительной вероятности, то величина R считается существенной. Таблицы критических значений F приводятся в справочниках по математической статистике.

Таким образом, значимость R определяется не только его величиной, но и соотношением между количеством экспериментов и количеством коэффициентов (параметров) модели. Действительно, корреляционное отношение для n=2 для простой линейной модели равно 1 (через 2 точки на плоскости можно всегда провести единственную прямую). Однако, если экспериментальные данные являются случайными величинами, доверять такому значению R следует с большой осторожностью. Обычно для получения значимого R и достоверной регрессии стремятся к тому, чтобы количество экспериментов существенно превышало количество коэффициентов модели (n>>k).

Для построения линейной регрессионной модели необходимо:

1) подготовить список из n строк и m столбцов, содержащий экспериментальные данные (столбец, содержащий выходную величину y должен быть либо первым, либо последним в списке);

2) обратиться к меню Сервис/Анализ данных/Регрессия

Если пункт "Анализ данных" в меню "Сервис" отсутствует, то следует обратиться к пункту "Надстройки" того же меню и установить флажок "Пакет анализа".

3) в диалоговом окне "Регрессия" задать:

  • входной интервал Y;

  • входной интервал X;

  • выходной интервал - верхняя левая ячейка интервала, в который будут помещаться результаты вычислений (рекомендуется разместить на новом рабочем листе);

4) нажать "Ok" и проанализировать результаты.

Пример использования множественной линейной регрессии

Предположим, что застройщик оценивает стоимость группы небольших офисных зданий в традиционном деловом районе.

Застройщик может использовать множественный регрессионный анализ для оценки цены офисного здания в заданном районе на основе следующих переменных.

y - оценочная цена здания под офис;

x1 - общая площадь в квадратных метрах;

x2 - количество офисов;

x3 - количество входов (0,5 входа означает вход только для доставки корреспонденции);

x4 - время эксплуатации здания в годах.

В этом примере предполагается, что существует линейная зависимость между каждой независимой переменной (x1, x2, x3 и x4) и зависимой переменной (y), то есть ценой здания под офис в данном районе. Исходные данные показаны на рисунке.

Н
астройки для решения поставленной задачи показаны на рисунке окна "Регрессия". Результаты расчетов размещены на отдельном листе в трех таблицах

В итоге мы получили следующую математическую модель:

y = 52318 + 27,64*x1 + 12530*x2 + 2553*x3 - 234,24*x4.

Теперь застройщик может определить оценочную стоимость здания под офис в том же районе. Если это здание имеет площадь 2500 квадратных метров, три офиса, два входа и время эксплуатации - 25 лет, можно оценить его стоимость, используя следующую формулу:

y = 27,64*2500 + 12530*3 + 2553*2 - 234,24*25 + 52318 = 158 261 у.е.

В регрессионном анализе наиболее важными результатами являются:

  • коэффициенты при переменных и Y-пересечение, являющиеся искомыми параметрами модели;

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

  • F-критерий Фишера (в рассмотренном примере он значительно превосходит критическое значение, равное 4,06);

  • t-статистика – величины, характеризующие степень значимости отдельных коэффициентов модели.

На t-статистике следует остановиться особо. Очень часто при построении регрессионной модели неизвестно, влияет тот или иной фактор x на y. Включение в модель факторов, которые не влияют на выходную величину, ухудшает качество модели. Вычисление t-статистики помогает обнаружить такие факторы. Приближенную оценку можно сделать так: если при n>>k величина t-статистики по абсолютному значению существенно больше трех, соответствующий коэффициент следует считать значимым, а фактор включить в модель, иначе исключить из модели. Таким образом, можно предложить технологию построения регрессионной модели, состоящую из двух этапов:

1) обработать пакетом "Регрессия" все имеющиеся данные, проанализировать значения t-статистики;

2) удалить из таблицы исходных данных столбцы с теми факторами, для которых коэффициенты незначимы и обработать пакетом "Регрессия" новую таблицу.

Для примера рассмотрим переменную x4. В справочнике по математической статистике t-критическое с (n-k-1)=6 степенями свободы и доверительной вероятностью 0,95 равно 1,94. Поскольку абсолютная величина t, равная 17,7 больше, чем 1,94, срок эксплуатации - это важная переменная для оценки стоимости здания под офис. Аналогичным образом можно протестировать все другие переменные на статистическую значимость. Ниже приводятся наблюдаемые t-значения для каждой из независимых переменных:

Общая площадь

5,1

Количество офисов

31,3

Количество входов

4,8

Срок эксплуатации

17,7

Все эти значения имеют абсолютную величину большую, чем 1,94; следовательно, все переменные, использованные в уравнении регрессии, полезны для предсказания оценочной стоимости здания под офис в данном районе.

Поиск решения

Excel имеет несколько программ-надстроек, выполняющих решение различных задач. Одной из надстроек является "Поиск решения", позволяющая решать оптимизационные задачи в Excel. Чаще всего это задачи линейного программирования (ЛП).

Общая формулировка задачи ЛП: найти неотрицательное решение X системы линейных уравнений AX=B, при котором целевая функция f=CX принимает максимальное (минимальное) значение, где A — матрица коэффициентов; B — объемы ресурсов.

Экономический смысл системы AX=B заключается в задании ограничений на расходуемые ресурсы.

Экономический смысл целевой функции f=CX заключается в максимальной прибыли или минимальной себестоимости, получаемой от оптимального решения X. Например, если X — вектор объемов выпуска продукции, а С - вектор прибыли, получаемой от единицы каждого вида продукции, то f — суммарная прибыль от выпуска всей продукции.

Рассмотрим работу надстройки "Поиск решения" на примере задачи о рационе кормления животных. Требуется составить такой рацион кормления животных тремя видами корма, при котором они получат необходимое количество питательных веществ A и B и себестоимость кормов будет минимальна. Цены кормов, требуемое количество питательных веществ и их содержание в каждом корме показаны в таблице.

Питательные вещества

Корм 1

Корм 2

Корм 3

Требуемое количество

(ед. пит. вещества)

А (ед./кг)

10

6

12

50

Б (ед./кг)

7

10

11

45

Цена корма (руб/кг)

2,20

1,95

2,87

 

Если обозначить X=(x1, x2, x3) — искомое количество кормов, то задача ЛП формулируется так:

Найти решение X системы

при котором целевая функция

принимает минимальное значение.

Математическую формулировку задачи необходимо оформить в виде таблицы, отражающей основные зависимости.

Ячейки таблицы имеют следующий смысл:

  • диапазон A1:C2 — содержит матрицу A;

  • диапазон D1:D2 — содержит вектор ресурсов В;

  • диапазон A6:C6 — содержит вектор цен С;

  • диапазон A4:C4 — содержит вектор решений X, начальные значения которого заданы нулю и который будет оптимизирован программой;

  • диапазон E1:E2 — содержит выражения, вычисляющие произведение AX;

  • ячейка E6 — содержит выражение, вычисляющее f=CX.

Вызов программы поиска решения выполняется через меню "Сервис\Поиск решения...". В открывшемся окне "Поиск решения" необходимо установить следующие параметры:

  • "Установить целевую ячейку" — E6;

  • установить переключатель "Равной минимальному значению";

  • в поле "изменяя ячейки" указать диапазон A4:C4;

  • в области "Ограничения" нажать кнопку "Добавить" и в окне "Добавление ограничений" ввести ограничения: E1>=D1 и E2>=D2;

  • нажать кнопку "Параметры..." и в открывшемся окне установить флажки "Линейная модель", "Неотрицательные значения" и выбрать переключатель "Оценка" — "Линейная".

Для запуска программы необходимо в окне "Поиск решения" нажать кнопку "Выполнить". Результаты вычислений будут записаны в изменяемые ячейки таблицы. В итоге таблица должна иметь следующий вид.

Таким образом, животных следует кормить первым кормом в количестве 0,38 кг, третьим — 3,85 кг и не использовать второй корм вообще. При таком рационе затраты на кормление одного животного составят 11,88 руб.

Пример постановки задачи линейного программирования

Акционерное общество производит и реализует оптовыми партиями соответственно по цене 45 руб. и 30 руб. книги и настольные календари. Постоянные затраты общества (управленческие расходы, содержание офиса и т.п.) составляют 202200 руб. в квартал, переменные затраты (стоимость бумаги, услуги типографии и т.п.) – соответственно 15.75 руб. и 12.3 руб. в расчете на одну книгу и один календарь. Налог на добавленную стоимость составляет 16.67 % цены изделия.

Опыт реализации изделий в предыдущие плановые периоды показал, что в течение квартала можно реализовать не более 100000 книг и 40000 календарей.

Требуется на планируемый квартал определить объем производства и реализации изделий (структуру производства и реализации), при котором общество получит максимальную прибыль, а суммарные затраты на производство и реализацию изделий не будут превосходить 2000000 руб.

  1. Формирование математической модели

Обозначим через х1(х2) неизвестный объем производства и реализации книг (календарей). Суммарные затраты Z на производство и реализацию изделий можно выразить в виде функции неизвестных объемов реализации изделий:

Z(x1,x2)=202200+15.75x1+12.3x2. (1.1)

Ожидаемая сумма выручки общества V, рассматриваемая как функция неизвестных объемов производства и реализации, составит:

V(x1,x2)=45x1+30x2. (1.2)

Сумма налога на добавленную стоимость Nтакже выражается в виде функции неизвестных объемов реализации:

N(x1,x2)=0.1667*(45x1+30x2)=7.5x1+5x2. (1.3)

Прибыль акционерного общества P можно записать в виде функции неизвестных объемов x1и x2следующим образом:

P(x1,x2)=V(x1,x2)-Z(x1,x2)-N(x1,x2) = 45x1+30x2-202200 –157.5x1-123x2-7.5x1-5x2=

=21.75x1+12.7x2-202200. (1.4)

Таким образом, математическая модель ситуации записывается следующим образом:

21.75x1+12.7x2-202200max, (1.5)

202200+15.75x1+12.3x22000000, (1.6)

0 x1 100000, 0 x2 40000. (1.7)

Модель (1.5)–(1.7) можно записать в следующем виде:

21.75x1+12.7x2max, (1.8)

15.75x1+12.3x21797800, (1.9)

x1 100000, (1.10)

x2 40000, (1.11)

x1 0, x2 0. (1.12)

  1. Ввод условий задачи:

    1. Сформировать таблицу в диапазоне ячеек A1:F11, приведенную на рис. 1.

Р
ис. 1. Таблица для ввода условий задачи линейного программирования

    1. В ячейку D6 ввести формулу =СУММПРОИЗВ(B$3:C$3;B6:C6).

    2. В ячейку D9 ввести формулу =СУММПРОИЗВ(B$3:C$3;B9:C9) и размножить по столбцу в ячейках D10 и D11.

  1. Решение задачи

    1. В меню выбрать «Сервис - Поиск решения». В поле «Установить целевую ячейку:» набрать $D$6. В поле «Равной:» установить маркер в «Максимальному значению».

    2. В поле «Изменяя ячейки» ввести $B$3:$C$3.

    3. Установить курсор-прямоугольник в поле «Ограничения». Нажать на кнопку «Добавить». В поле «Ссылка на ячейку:» ввести $B$3. Выбрать знак >=. В поле «Ограничение» ввести =$B$4. Нажать кнопку «Добавить».

    4. В поле «Ссылка на ячейку:» ввести $С$3. Выбрать знак >=. В поле «Ограничение» ввести =$С$4. Нажать кнопку «Добавить».

    5. В поле «Ссылка на ячейку:» ввести $D$9. Выбрать знак <=. В поле «Ограничение» ввести =$F$9. Нажать кнопку «Добавить»

    6. В поле «Ссылка на ячейку:» ввести $D$10. Выбрать знак <=. В поле «Ограничение» ввести =$F$10. Нажать кнопку «Добавить»

    7. В поле «Ссылка на ячейку:» ввести $D$11. Выбрать знак <=. В поле «Ограничение» ввести =$F$11. Нажать кнопку OK. В результате диалоговое окно Поиск решения должно быть заполнено также как на рис. 2.

Рис. 2. Диалоговое окно Поиск решения

    1. Нажать кнопку «Параметры». Установить линейную модель. Нажать кнопку OK. Нажать кнопку «Выполнить».

    2. В диалоговом окне «Результаты поиска решения», приведенном на рис. 3, установить маркер на опцию «Сохранить найденное решение» и выбрать в окне «Тип отчета – Результаты». Нажать OK.

Рис. 3. Диалоговое окно Результаты поиска решения



Скачать документ

Похожие документы:

  1. Методика выполнения работы

    Лабораторная работа
    ... построение трендовой модели средствами Excel. МетодикавыполненияработыРабота с макросами Макрос — это программа, автоматизирующая ...
  2. Методика выполнения работы общие сведения

    Лабораторная работа
    ... оформления слайдов и с основными объектами оформления. Методикавыполненияработы Общие сведения PowerPoint - это программа ... макета таблицы и ее форматирования. Порядок выполненияработы Разработать содержание презентации согласно «Заданию ...
  3. 1 ОБЩАЯ МЕТОДИКА ВЫПОЛНЕНИЯ РАБОТЫ

    Литература
    ... …………………………………..……………………… 4 1 ОБЩАЯ МЕТОДИКАВЫПОЛНЕНИЯРАБОТЫ. АНАЛИЗ КОНСТРУКЦИИ ДЕТАЛИ………………………………………...5 1.1 Состав курсовой работы ………………………………………5 1.2 Последовательность выполнения курсовой работы…………7 1.3 Характеристика детали ...
  4. Методика выполнения творческих проектов

    Курсовая
    ... игры. Цель: изучить методикувыполнения творческих проектов по технологии ... является методикавыполнения творческих проектов по технологии. Курсовая работа состоит ... Описание проекта. Собственная оценка выполненнойработы. Вариант оформления и содержания ...
  5. Методика выполнения измерений расхода и количества пара отпускаемого в паровые системы теплоснабжения от источника тепла рд 153-34 0-11 343-00

    Документ
    ... -ТЕХНИЧЕСКОЙ ПОЛИТИКИ И РАЗВИТИЯ МЕТОДИКАВЫПОЛНЕНИЯ ИЗМЕРЕНИЙ РАСХОДА И КОЛИЧЕСТВА ПАРА ... технологического режима и анализа работы паровой системы теплоснабжения, ... на СИ; — проведение наладочных работ; — введение измерительных систем в эксплуатацию ...

Другие похожие документы..