Как составить математическую модель в excel

Как составить математическую модель в excel

Как имитировать случайное попадание дротика в мишень? И вообще получить число случайно?

Алгоритм
построения модели Далекая мишень

Работающая модель

Здесь можно поэксперементировать с работающей моделью:
• сделать ряд одиночных выстрелов;
• произвести целую серию выстрелов с попаданием 100 игл в мишень и посмотреть, сколько игл попадает в каждый из квадрантов.

• Число попаданиий в I квадрант: &nbsp 0
• Число попаданиий в II квадрант: &nbsp 0
• Число попаданиий в III квадрант: &nbsp 0
• Число попаданиий в IV квадрант: &nbsp 0 Бросить 100 игл

Подготовить поле на листе Excel

Подготовим диаграмму для визуализации данных.
В поле диаграммы будем строить точки из таблицы, в которой датчиком случайных чисел генерируется два числа: X и Y — координаты точки, в которую попадает игла дартса.

Функция-генератор случайных чисел СЛЧИС() ( англ. Rand() )

Примерный код функции СЛЧИС()
• (iseed – начальное значение генератора – произвольное, желательно большое, целое число,
  4.6566128752458E-10 = 1/2147483647, 2147483647 = 2 31 — 1 )
• a = 16807 * iseed
• b = a — 2147483647 * Int(a * 4.6566128752458E-10)
• iseed = b
• СЛЧИС = b * 4.6566128752458E-10

Использование функции СЛЧИС() для определения координат точки попадания

Введем в ячейки I4 и J4 функцию СЛЧИС() .
Протянем формулы вниз, и увидим на диаграмме точки попадания дарта в мишень.

Почему модельный дротик все время попадает в верхний правый квадрант?

Поэтому для того, чтобы получить случайное число, изменяющееся от -1 до 1, необходимо
• случайное число ξ , сгенерированное функцией СЛЧИС() умножить на 2 :
  2 * ξ ,
• а затем из модифицированного случайного числа 2 * ξ вычесть 1:
  2 * ξ — 1 .

Тогда координаты попадания в квадратную мишень со стороной 2 дм нужно моделировать как
• X = 2 * ξ — 1
• Y = 2 * ξ — 1
• Запишем формулу = 2 * СЛЧИС() — 1 в ячейки I4 и J4 .

Как смоделировать 100 попаданий?

Для того, чтобы смоделировать сразу N=100 попаданий, протянем формулы из ячеек I4 и J4 на сто ячеек вниз до строки 103. Получим модель результата игры «Далекая мишень».

Подсчет попаданий дротика в заданный квадрант

Этот файл содержит:
• рабочую модель с выстрелом из 100 игл.
• рабочую модель с выстрелом из 400 игл.
• рабочую модель с выстрелом из 1600 игл.
• рабочую модель с выстрелом из 6400 игл.

Как оценить ожидаемое число событий и дисперсию.

В нашем случае:
• вероятность попасть в определенный квадрант — 1/4 (так как квадрантов 4)
  p = 1/4
• так как по условию в мишень попали 100 раз, то N = 100
• ожидаемое количество попаданий в выбранный квадрант ‹n›= 100 * 1/4 = 25
• вероятность не попасть в определенный квадрант — 3/4
  q = 3/4
• дисперсия ‹D_n›= 100×1/4×3/4 = 18,75
• cтандартное отклонение ‹s_n›= 18,75 ½ = 4,33 Итак, ожидаемое количество попаданий в выбранный квадрант: 25±4,33

Какой разброс числа попаданий от ожидаемых 25-ти мы можем получить?

Для начала ответим на вопросы.
1. Каким вообще может быть число попаданий в заданный квадрант?
   Практически каждый ответит на него: от 0 до 100 .
   И действительно, нет никаких причин, запрещающих игроку, специально не целящегося в определенное место мишени, 100 раз подряд попасть в заданный квадрант. Так же как нет причин для него все 100 раз промахнуться!
   Однако на практике мы этого НИКОГДА НЕ НАБЛЮДАЕМ!
2. С какой вероятностью дротик не попадет в заданный квадрант?
   Не трудно посчитать.
   • Вероятность не попасть в заданный квадрант — 3/4 или 0,75
   • Вероятность не попасть в заданный квадрант за два броска — 3/4×3/4 или (3/4) 2 , а это 9/16 или ≈0,56
   • Вероятность не попасть в заданный квадрант за три броска — 3/4×3/4×3/4 или (3/4) 3 , а это 27/64 или ≈0,42
   • Вероятность не попасть в заданный квадрант за сто бросков — (3/4) 100 , а это ≈3,2×10 -13 или 0,00000000000032 , т.е. почти 0.
3. С какой вероятностью дротик попадет в заданный квадрант?
   • Вероятность попасть в заданный квадрант — 1/4 или 0,25
   • Вероятность попасть в заданный квадрант два раза подряд — 1/4×1/4 или (1/4) 2 , а это 1/16 или ≈0,06
   • Вероятность попасть в заданный квадрант три раза подряд — 1/4×1/4×1/4 или (1/4) 3 , а это 1/64 или ≈0,015
   • Вероятность попасть в заданный квадрант все сто раз — (1/4) 100 , а это ≈6,2×10 -61 , т.е. 0.

Итак,
• вероятность выпадения определенного события не меняется (в нашем случае вероятность для дротика попасть в заданный квадрант все время 1/4);
• вероятность многократного повторения ОДНОГО И ТОГО ЖЕ определенного события с увеличением количества испытаний уменьшается. Поэтому вероятность при 100 бросках все время попадать в заданное место (1/4) 100 , т.е. 0 .

Для построения частотной диаграммы возьмем две оси:
• на одной оси отложим возможные результаты нашего эксперимента ось ‹n›

Частотная диаграмма числа попаданий в I квадрант.

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

Чем больше число испытаний, тем ближе функция распределения нашей случайной величины (число попаданий в заданный квадрант) к нормальной функции распределения.

Таким образом, уже при 10 тыс. испытаний мы наблюдаем иллюстрацию Центральной предельной теоремы теории вероятности.

Центральная предельная теорема
Сумма большого числа примерно одинаковых случайных величин с произвольными функциями распределения всегда имеет приблизительно нормальную функцию распределения.

Итак, что мы сделали?
1. Построили по ситуации «Далекая мишень» математическую модель с использованием ГЕНЕРАТОРА СЛУЧАЙНЫХ ВЕЛИЧИН.
2. Многократно обсчитали эту модель:
   • находили среднее число попаданий в заданный квадрант
   • вычисляли стандартное отклонение
3. На основе полученных многократных вычислений определили вероятностные характеристики рассматриваемого процесса. По сути мы применили метод Монте-Карло.

Применение надстройки Монте-Карло для статистического моделирования

1. Подключите надстройку «Моделирование Монте-Карло».
2. Откройте файл «21 Darts.xlsx», который был описан на раскрывающейся панели Моделирование ситуации Далекая мишень в рабочей книге MS Excel или создайте свою модель в рабочей книге MS Excel.
3. Введите в поле Целевые ячейки ячейку,в которой вычисляется число попаданий в I квадрант.
   В нашем случае, это ячейка K2 .
4. Запустите надстройку, нажав кнопку Выполнить моделирование.

Надстройка
• провела 10 тысяч испытаний;
• вычислила среднее значение — 25;
• нашла стандартное отклонение при вычислении средней величины — 4,33;
• вычислила ошибку оценки среднего значения при 10 тысячах испытаниях — 0,085;
• вывела максимальное и минимальное значения, которае выпадали при проведении 10 тысяч испытаний;
• вывела медианное значение случайной величины. При следующем запуске надстройки данные будут другими. Так и должно быть, ведь мы моделируем случайные величины.

Где используют статистическое моделирование

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

Очевидно, что отношение площади фигуры (S_фигуры) к площади всего поля (S_поля) равно отношению числа попаданий в фигуру (N _{в фигуру}) к общему числу бросков (N_всего):

где ΔS_фигуры — точность вычисления площади фигуры. Эта величина зависит от числа бросков.
Чем больше дротиков мы бросим, тем точнее определим площадь фигуры.

Построение математической модели задачи и ее решение в MS Excel

Шарик бросают вертикально вверх с верхней площадки башни со скоростью V1. Ветер, дующий со скоростью V2, относит его в сторону.

· создать математическую модель движения шарика от начала падения до удара о землю;

· подготовить компьютерную реализацию математической модели в среде электронных таблиц.

В ходе проведения компьютерных экспериментов определить:

· как влияет изменение скорости V1 (шаг изменений 1 м/с) на дальность падения L;

· как влияет изменение высоты Н (шаг изменений 1 м) на время падения t;

· как влияет высота Н (шаг изменений 1 м) на дальность падения L.

Исходные данные

Построим модель движения шарика.

1) Сначала шарик совершает равнозамедленное движение вверх.

Максимальная высота подъема:

Время подъема шарика:

2) Свободное падение с высоты H+h. Применяя уравнение свободного падения, получаем (H+h) = gt22/2, где t2 — время падения.

Выражая t2, получаем:

3) Время шарика в пути

4) Учитываем боковой ветер. Расстояние L, на которое сместится шарик после падения, равно:

Математическая модель построена.

Строим модель в MS Office Excel (лист Задание1).

1) Организуем расположение данных и формул:

математический модель задача excel

Результат вычислений с заданными исходными значениями:

2) Проанализируем, как влияет изменение скорости V1 (шаг изменений 1 м/с) на дальность падения L. Результаты анализа представим в графическом виде.

Как видим, зависимость дальности падения от начальной скорости линейная. Достоверность аппроксимации равна 1.

Уравнение зависимости: y = 0,1835x + 2,7024.

Для прогноза значений дальности падения L вне диапазона значений скорости V1 применим полученное уравнение и вычислим L, например при V1 = 29 м/с.

3) Проанализируем, как влияет изменение высоты Н (шаг изменений 1 м) на время падения t.

Аппроксимация графика привела к квадратичной зависимости.

Уравнение зависимости: y = -0,0015×2 + 0,1353x + 3,7878

Использование этого уравнения позволяет прогнозировать значения t вне диапазона H.

4) Проанализируем, как влияет высота Н (шаг изменений 1 м) на дальность падения L.

Аппроксимация графика привела к квадратичной зависимости.

Уравнение зависимости: y = -0,0008×2 + 0,0751x + 2,1043

Использование уравнения, приведенного на графике, позволяет прогнозировать значения L вне диапазона H.

Дана наклонная плоскость, по которой скатывается шарик:

Угол начальный 200

Угол конечный 400

Угол начальный 150

Угол конечный 350

Сопротивлением воздуха пренебрегаем.

Построим модель движения шарика.

На начальном этапе шарик движется по наклонной плоскости длиной L1, расположенной под углом . Коэффициент трения при движении шарика по наклонной плоскости описывается величиной kтр1. Затем шарик движется по наклонной плоскости вверх. Коэффициент трения kтр2.

При спуске с наклонной плоскости и отсутствии дополнительных сил ускорение равно a1 = g(sin — kтр1 · cos), где g — ускорение свободного падения; kтр1 — коэффициент трения. Поскольку начальная скорость шарика равна нулю, скорость шарика v = a1t. Путь, который пройдёт шарик, равен L1 = a1t2/2. Отсюда t = . Значит, скорость шарика в момент прохождения отрезка пути L1 составит v = a1t = .

Далее шарик движется по наклонной плоскости вверх. При подъеме по наклонной плоскости и отсутствии дополнительных сил a2 = g(sin + kтр2 · cos), где g — ускорение свободного падения; kтр2 — коэффициент трения. Поскольку у шарика уже есть начальная скорость v, пройденный путь составит: L2 = vt2 + a2t22/2. Нам необходимо найти максимальный пройденный путь. В момент остановки шарика ускорение равно 0. Время подъёма. t2 = v / a2. Тогда пройденный путь равен L2 = vt2 = v2/a2.

Математическая модель построена.

Строим модель в MS Office Excel (лист Задание2).

1) Формулы ячеек:

Результат вычислений с начальными значениями:

2) Определим, как влияет изменение значения угла на скорость движения шарика в момент нахождения его в конце первой наклонной плоскости.

В данном случае зависимость получилась квадратичная (полиномиальная второй степени).

Уравнение зависимости: y = -0,0011×2 + 0,1521x + 1,756

Использование уравнения позволяет прогнозировать значения скорости при других углах . Например, при = 450 скорость равна 6,37 м/с.

3) Определим, как влияет изменение значения угла на длину пробега шарика L2.

В данном случае зависимость получилась полиномиальная 3 степени.

Уравнение зависимости: y = -410-5×3 + 0,0044×2 — 0,2027x + 5,6974.

Использование уравнения позволяет прогнозировать значения пути при других углах . Например, при = 400 скорость равна 6,37 м/с.

Задание 3

Дана электрическая цепь:

Исходные данные:

Е = 12 В; R1 = 12 Ом; R2 = 24 Ом

R3 = 12 Ом; R4 = 16 Ом; R5 = 20 Ом.

· создать математическую модель цепи;

· определить, как влияет изменение значения R4 (таблица) на ток, протекающий в цепи, с построением диаграммы и определением уравнения зависимости;

· спрогнозировать по полученному уравнению величину тока при R4 = 150 Ом;

· определить, как влияет изменение значения R2 (таблица) на ток, протекающий в цепи, с построением диаграммы и определением уравнения зависимости;

· спрогнозировать по полученному уравнению величину тока при R2 = 110 Ом;

· подобрать значение R1, при котором значение протекающего в цепи тока уменьшится на 15 %, и записать его в одну из ячеек;

· подобрать значение R3, при котором падение напряжения на нём увеличится на 10 %, и записать его в одну из ячеек.

Таблица значений сопротивления (Ом):

Строим математическую модель цепи.

Резисторы 1, 2 связаны параллельно, для них эквивалентное сопротивление будет

Резисторы 4, 5 связаны параллельно, для них эквивалентное сопротивление будет

Участки 12, 3 и 45 подключены последовательно.

Эквивалентное сопротивление цепи:

Ток в цепи определяется законом Ома:

Математическая модель построена.

Строим модель в MS Office Excel (лист Задание3).

Расчеты по формулам приводят к следующим результатам:

2) Определим, как влияет изменение значения R4 (таблица) на ток, протекающий в цепи. Результаты анализа представим в графическом виде.

Наиболее точное уравнение аппроксимации является полиномом 6 степени: y = 410-12×6 — 10-9×5 + 210-7×4 — 110-5×3 + 0,0006×2 — 0,0155x + 0,5576.

С помощью этого уравнения можно предсказать величину тока при других значениях R4. Ток в цепи убывает с ростом R4, стремясь к определенному пределу.

Предсказываемое программой Excel уравнение аппроксимации нельзя использовать для прогноза значения параметра, сильно выходящего за аппроксимируемый диапазон. Так, попытка спрогнозировать ток в цепи при R4 = 150 Ом приводит к неправильному значению силы тока. В этом случае следует пользоваться расчетной формулой.

3) Определим, как влияет изменение значения R2 (таблица) на ток, протекающий в цепи. Результаты анализа представим в графическом виде.

Наиболее точное уравнение аппроксимации является полиномом 6 степени:

y = 310-12×6 — 110-9×5 + 110-7×4 — 110-5×3 + 0,0004×2 — 0,011x + 0,5304.

Ток в цепи убывает с ростом R2, стремясь к определенному пределу.

При R2 = 110 Ом это уравнение даёт прогноз -5,30 Ом, что неверно. Следовательно, необходимо пользоваться точными расчетными формулами, поскольку величина 110 Ом выходит за границы диапазона сопротивлений.

4) Далее необходимо узнать значение R1, при котором ток в цепи снизится на 15%. Воспользуемся подбором параметра.

Для того, чтобы ток в цепи снизился на 15%, нужно установить сопротивление R1 = 30,26 Ом.

5) Определим значение R3, при котором падение напряжения на нём увеличится на 10%.

Падение напряжение на R3 равно произведению общего тока I на R3:

Воспользуемся инструментом «Поиск решения».

При сопротивлении R3, равном 14,19 Ом, падение напряжения на нём увеличится на 10%.

Список литературы

1. Кашаев С. Офисные решения с использованием Microsoft Excel 2007 и VBA. — СПб.: Питер, 2009. — 352 с.

2. Леонов В. Функции Excel 2010. — СПб.: Эксмо, 2011. — 560 с.

3. Мачула В. Г. Excel 2007 на практике. — Ростов-на-Дону: Феникс, 2009. — 160 с.

Городецкая н.В. Математическое моделирование в ms excel Учебное пособие

Городецкая Н.В. Математическое моделирование в MS Excel: Учеб. пособие. Екатеринбург: Изд-во ГОУ ВПО «Рос. гос. проф.-пед. ун-т», 2007. 64 с.

Учебное пособие содержит лабораторные работы, ориентированные на знакомство с одной из технологий – математическое моделирование на основе среды Microsoft Excel.

Лабораторные работы включают необходимый теоретический материал и непосредственные инструкции по освоению математического моделирования в среде Microsoft Excel.

Пособие может быть использовано для преподавания дисциплины «Математическое моделирование» у студентов специальности 050501 Профессиональное обучение (информатика, вычислительная техника и компьютерные технологии) (030500.06), специализации «Компьютерные технологии».

Практикум подготовлен при финансовой поддержке Российского гуманитарного научного фонда в рамках научно–исследовательского проекта «Психолого–педагогические и технологические условия применения адаптивных методических систем в дистанционных образовательных технологиях» (№ 06−06−00475а).

Рецензенты: д-р физ.-мат. наук, проф. В.Е. Третьяков (ГОУ ВПО «Уральский государственный университет»); д-р пед. наук, проф. Л.И. Долинер (ГОУ ВПО «Российский государственный профессионально-педагогический университет»)

© ГОУ ВПО «Российский

педагогический университет», 2007

© Н.В. Городецкая, 2007

Оглавление

Преподавателю: как использовать это пособие 4

Тому, кто хочет научиться 4

Лабораторная работа 1 7

Лабораторная работа 2 26

Лабораторная работа 3 33

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

Лабораторная работа 5 56

Литература 64

Преподавателю: как использовать это пособие

Данная серия лабораторных работ предназначена для знакомства обучаемых с технологией использования математического моделирования для решения задач. В качестве конкретного инструментального средства выбрана среда MicrosoftExcel.

Для использования данного пособия в обучении необходимо:

иметь дискету, прилагаемую к пособию, для установки рабочих файлов (без них работа с пособием невозможна);

установить на компьютере полную версию MicrosoftExcel(с возможностью осуществлятьПоиск решения);

создать (в случае отсутствия) в корневом каталоге одного из дисков папку Учебнаяи скопировать в папкуУчебнаяпапкуМАТ_МОД, содержащую учебные файлы с прилагаемой дискеты.

Тому, кто хочет научиться

Если Вы решили с помощью этого пособия познакомиться с технологией использования математического моделирования в среде MicrosoftExcel, рекомендуется:

расположиться перед включенным компьютером с установленной полной версией MicrosoftExcel;

выполнять лабораторные работы как можно более точно, поскольку тексты лабораторных работ представляют собой в некотором роде инструкции, соблюдение которых обеспечит Вам успешную и комфортную работу;

соблюдать следует следующие правила:

текст, который никак не выделен, следует только читать;

определения, отмеченные значком , необходимо запомнить;

обращать внимание на текст, помеченный значком ;

практические задания, отмеченные словом «Задание», следует обязательно и в полном объеме выполнять на компьютере;

контрольные задания следует также выполнять самостоятельно; если Вы справитесь с ними без помощи преподавателя, это означает, что Вы усвоили материал;

на контрольные вопросы нужно отвечать устно – они подготовят Вас к компьютерным тестовым вопросам;

для повторения пройденного материала следует использовать резюме;

делать краткий конспект — это поможет Вам ускорить усвоение материала;

отвечать на все вопросы, приведенные в конце каждой лабораторной работы;

приглашать преподавателя тогда, когда это предлагается сделать в тексте лабораторной работы;

если Вы занимаетесь без преподавателя, выполняйте полностью все задания лабораторных работ, отвечайте устно на вопросы.

В книге приняты следующие обозначения:

— этот символ используется для выделения определений;

— так помечаются важные замечания;

— резюме;

— при встрече с таким символом следует пригласить преподавателя (консультанта) и показать ему результаты выполнения заданий. Если Вы работаете самостоятельно, просто пропустите текст, помеченный этим символом;

Создание модели в Excel

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

Формализация постановки задачи начинается с ввода обозначений для будущей модели

— пусть намечается выпуск 3-х видов продукции: П1, П2, П3, (j=1, …, 3);

— для производства продукции используется шесть видов деталей (ресурсов) (i=1, …, 6);

— известны нормы расхода деталей (ресурсов) на изготовление единицы каждого вида продукции a _ij [ед.детал./ед.прод.];

— каждый вид деталей (ресурса) ограничен b _i [ед.детал.] наличием на складе;

— предполагается, что от реализации единицы продукции, предприятие получает прибыль P _j [ден.ед./ед.прод.].

Исходные данные оптимизационной задачи сведены в таблице 1.

Таблица 1 с исходными данными

Введем обозначения для построения математической модели:

Через X _j – обозначим неизвестное, которое показывает возможное количество выпускаемой продукции j-ого вида, при использовании (a_ij) существующих норм единицы деталей (ресурса) на выпуск определенного вида продукции, при ограничениях на общее использование ресурса b_i. Добавим дополнительное условие, которое показывает, что изготовляемая продукция должна быть выпущена всех видов. Это значит, что все переменные X_j должны быть не менее 1 (X₁? 1, X₂? 1,X₃? 1). Отметим, что, если не имеет значение, какие виды продукции следует выпускать при поиске оптимального использования ресурсов, то в ограничениях следует указать не 1, а 0. Обозначив через Z величину суммарной прибыли при организации выпуска продукции, можно записать следующую систему уравнений:

1?X1 + 0?X2 + 1?X3? 450	Шасси
4?X1 + 2?X2 + 1?X3? 340	Динамик
1?X1 + 1?X2 + 1?X3? 500	Блок питания
1?X1 + 0?X2 + 1?X3? 125	Монитор
2?X1 + 1?X2 + 1?X3? 270	Электронная плата
0?X1 + 1?X2 + 1?X3? 470	Процессор
X1? 1, X2? 1, X3? 1	Ограничения для выпуска продукции

Создание модели в Excel

1. Открыть табличный процессор Excel.

Подготовить начальную таблицу для размещения описаний задачи, исходных данных, ограничений, коэффициентов целевой функции, место для проведения вычислений и сохранения результатов. Начальная таблица, как она будет выглядеть в Excel, представлена на рис. 1. Столбец D введен для занесения результатов в ячейки D5:D10. В этих ячейках будут отображаться результаты вычислений, т.е. сколько на самом деле будет задействовано единиц каждого вида ресурса для выпуска всей номенклатуры продукции. Добавить в таблицу новые обозначения, которые понадобятся для ввода начальных значений и вывода результатов. Для этой цели:

· в строке 11 (ячейки E11:G11), ввести коэффициенты для целевой функции ее название — «Прибыль от реализации единицы продукции»;

· в строке 12 создать заголовок – «Значения X_j при решении задачи», ячейки E12:G12 понадобятся для ввода формул;

· ячейку E13 можно выделить, в которой будет формироваться результат, поэтому в строке 13 сделана запись – «Конечная прибыль от реализации продукции», это и есть значение целевой функции.

Рис. 2. Начальная таблица в Excel

1. Ввести формулы в таблицу с начальными данными. Для решения оптимизационной задачи методом линейного программирования необходимо математическую модель записать в терминах Excel, т.е. в определенные ячейки предварительно необходимо ввести формулы. В таблице 2 указаны координаты ячеек для рассматриваемого примера, математическая запись уравнений и формулы, которые введены в соответствующие ячейки в табличном процессоре Excel.

Таблица 2. Перечень формул для установки в ячейках таблицы Excel

Ячейка	Математическая запись	Формула
D5	1?X1 + 0?X2 + 1?X3	=$E$13*E5+$F$13*F5+$G$13*G5
D6	4?X1 + 2?X2 + 1?X3	=$E$13*E6+$F$13*F6+$G$13*G6
D7	1?X1 + 1?X2 + 1?X3	=$E$13*E7+$F$13*F7+$G$13*G7
D8	1?X1 + 0?X2 + 1?X3	=$E$13*E8+$F$13*F8+$G$13*G8
D9	2?X1 + 1?X2 + 1?X3	=$E$13*E9+$F$13*F9+$G$13*G9
D10	0?X1 + 1?X2 + 1?X3	=$E$13*E10+$F$13*F10+$G$13*G10
E11	X1? 1	>=1
G11	X2? 1	>=1
F11	X3? 1	>=1
E14	450·X1 + 125·X2 + 340·X3	=E12*E13+F12*F13+G12*G13

2. Работа с надстройкой Excel – Поиск решения

· Подключение: Меню – Сервис – Поиск решения, если такая строка отсутствует, то необходимо нажать на строку с командой: Надстройки, и поставить отметку против строки Поиск решения (рис. 2).

Рис. 2. Подключение инструмента для решения задач оптимизации

· Ввести начальные значения X_j в ячейки E13:G13. Например, по двадцать единиц каждого вида изделия.

· Выделить курсором ячейку E14 с целевой функцией.

· Выбрать команду в меню Сервис-Поиск решения.

· В открывшемся диалоговом окне – Поиск решения, заполнить окна: Изменяя ячейки и Ограничения (можно проверить в диалоговом окне факт установки курсора на целевой ячейке, если требуется, то ее положение можно изменить). На рис. 3 показано всплывающее диалоговое окно Поиск решения, в котором проведены все перечисленные подготовительные действия.

Рис. 3. Всплывающее диалоговое окно с введенными данными

· Установить диапазон ячеек в строке всплывающего окна: Изменяя ячейки, в которых будет отображаться результат с количеством номенклатуры X_j изделий. В рассматриваемом примере, это будут ячейки E13:G13, которые должны быть фиксированными (перед координатами ячее ставится знак $).

· Отметить селекторную кнопку: Равной максимальному значению, т.к. определяется максимальное использование ресурсов.

· В окно с наименованием Ограничения последовательно ввести все ограничения для уравнений модели. В данном примере ячейки C5:C10 содержат количество деталей на складе, которые потребуются для выпуска всей номенклатуры продукции, а в ячейки D5:D10 были внесены формулы модели по каждому виду комплектующей, следовательно, суммарное количество используемых деталей не должно превышать величину, указанную в правой части уравнения. Для ввода ограничений, необходимо нажать на кнопку . После выполненного действия будет открыто диалоговое окно с наименованием Добавление ограничений, которое показано на рис. 4. В окне видно, что вычисляемое значение в ячейке D5 должно быть меньше или равно установленной величины в ячейке C5.

Рис. 4. Диалоговое окно для ввода ограничений

· Если требуется ввести еще ограничения, то нажать на кнопку , в противном случае нажать на кнопку .

· Ввести ограничения на выпуск номенклатуры продукции в ячейки E13:G13 (в примере всего три вида продукции). Так, в качестве примера, на рис. 5 показано диалоговое окно для добавления ограничений, в котором указано, что вычисляемое значение в ячейке G13 должно быть более 1 единицы (это условие записано в исходной таблице для изделия – Компьютеры).

Рис. 5. Диалоговое окно с ограничением на выпускаемую продукцию

· Ввести ограничения на форму представление результатов. В данной постановке задачи подразумевается, что количество изделий не может быть дробной величиной, а должны отображаться только целыми числами, следовательно, при выполнении расчетов, это обстоятельство необходимо учитывать. На рис. 6 показано диалоговое окно для добавления ограничений, в котором для ячейки E13 (в ней отображается количество единиц изделия), установлено условие ‘ цел ’, что означает целочисленное решение. В раскрывающемся списке выбирается необходимое условие.

Рис. 6. Установка условия целочисленного решения в диалоговом окне

· Установить параметры для оптимизационной задачи, для чего в диалоговом окне: Поиск решения, нажать на кнопку . После того, как откроется диалоговое окно с наименованием Параметры поиска решения, представленное на рис. 7.

Рис. 7. Диалоговое окно для установки параметров поиска решения

· В окне установить пометку: Линейная модель, и закрыть кнопкой ОК.

· Провести вычисления, для чего в диалоговом окне Поиск решения, нажать на кнопку . В том случае, если все данные введены правильно, а в вычисляемых ячейках существуют формулы, описанные в данной методике, то появится диалоговое окно с наименованием: Результаты поиска решения, которое представлено на рис. 8.

Рис. 8. Диалоговое окно с сообщением о завершении поиска оптимального решения по поставленной задаче

Программа формирует три типа отчетов: Результаты, Устойчивость и Пределы. Если отметить любой из них или все вместе, а затем вызвать, то можно провести анализ исходных данных и конечных результатов, об этом будет сказано ниже. В том случае, если в окне Результаты поиска решения появится сообщение: «Ошибка в модели. Проверьте правильность значений в ячейках и ограничениях», то отчеты получить невозможно, а следует открыть окно Поиск решения (рис. 4), и проверить правильность установки знаков ограничений, наименования ячеек, в которых должна быть вычислена целевая функция и установлены начальные значения изменяемых ячеек. На рисунке 9 показан лист Excel, который содержит исходные данные и результаты решения оптимизационной задачи методом линейного программирования по заданным начальным значениям и тем условиям, которые были установлены, в соответствии с постановкой задачи.

Рис. 9. Результаты решения оптимизационной задачи методом линейного программирования

Понравилась статья? Добавь ее в закладку (CTRL+D) и не забудь поделиться с друзьями: