Аналитическое моделирование в Exсel 2010. (Лекция 3)

1. Тема 3. Аналитическое (количественное) моделирование

1. Принципы решения хорошо
структурированных проблем
2. Оптимизация в условиях полной
определенности
3. Принятие решений в условиях
неопределенности и риска
1
Лектор доц.
Н.А. Мещерякова

2.

Методы моделирования
систем
Методы активизации
интуиции, опыта,
знаний и навыков
специалистов
1
2
3
4
5
6
2
Методы мозгового штурма
Метод «Дельфи»
Сценарные методы
Экспертные методы
Формальные методы
Концептуальное моделирование
Логико-математическое
моделирование
7
8
Статистическое моделирование
Аналитическое моделирование
(количественные методы)
Имитационное моделирование
1
9
10
11
Методы структуризации
Графическое моделирование
12
Интуитивное моделирование
13
Морфологические методы

3. Классификация проблем

по Саймону и Ньюэллу
1. хорошо структурированные или
количественно выраженные проблемы;
2. неструктурированные
выраженные проблемы;
или качественно
3. слабоструктурированные (смешанные
проблемы), которые содержат количественные и
качественные проблемы.
3

4.

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

5.

Оптимизация в условиях полной
определенности (математическое
программирование)
Метод линейной оптимизации (модель линейного
программирования)
Транспортные задачи и логистика; задачи о
назначениях и отборе
Оптимальное управление запасами
Планирование и анализ проектов (сетевое
моделирование)
Оптимизация в условиях
неопределенности и риска
5

6.

Метод линейной оптимизации
F(x) = c1х1 + с2х2 + … + сnxn → max (min)
a1х1 + a2х2 + … + anxn <= (>=) bi
xi>=0
Шаг 1. Построить таблицу с исходными данными
Шаг 2. Выделить ячейки для переменных решения
Шаг 3. Ввести формулу для расчета целевой функции
Шаг 4. Ввести формулы для расчета использования
ресурсов
Шаг 5. Запустить «Поиск решения»
Шаг 6. Добавить изменяемые ячейки и ограничения
6
Шаг 7. Нажать кнопку «Найти решение»

7.

8.

9. Местоположение «Поиск решения» в меню

10. В Excel 2003

11. Пример 1. Распределение премии

Задача
Необходимо распределить премию в сумме 100 000
руб. между сотрудниками отдела
пропорционально их должностным окладам.

12. Шаг 1-2. Построить таблицу с исходными данными и формулами

13. Шаг 3. Запустить  «Поиск решения» 

Шаг 3. Запустить «Поиск решения»

14. Шаг 3. Запустить  «Поиск решения» (в Excel 2003)

Шаг 3. Запустить «Поиск
решения» (в Excel 2003)

15. Шаг 3. Добавление ограничений

1 - Целевая ячейка, в которой должен получиться желаемый результат.
2 - Варианты оптимизации: максимальное возможное значение,
минимальное возможное значение или конкретное значение.
3 - Изменяемых ячеек может быть несколько: отдельные ячейки или
диапазоны.
4 - Ограничения задаются с помощью кнопки Добавить. Ограничения
обеспечивают получение правильного результата. Ограничения
можно задавать как для отдельных ячеек, так и для диапазонов
(допустимо использование знаков =, >=, ….

16. Добавление ограничений

Ограничение можно задать поразному: либо установить явно,
воспользовавшись
кнопкой Добавить, либо
поставить флажок Сделать
переменные без ограничений
неотрицательными
В Exсel 2003 флажок в
диалоговом
окне Параметры Поиска
решения, которое
открывается при нажатии
на кнопку Параметры

17. Шаг 5. Нажать кнопку  «Найти решение» (Выполнить) в Exсel 2010

Шаг 5. Нажать кнопку «Найти
решение» (Выполнить) в Exсel 2010

18. Шаг 5. Нажать кнопку  «Найти решение» (Выполнить) до версии Exсel 2010

Шаг 5. Нажать кнопку «Найти решение»
(Выполнить) до версии Exсel 2010

19.

Таблица транспортных издержек для
перевозок единицы груза
Потребители
Поставщики
А1
В1
В2
…
Вn
Запасы
c11
c12
…
c1n
а1
А2
c21
c22
…
c2n
а2
…
…
…
…
…
…
Аm
cm1
cm2
…
cmn
аm
…
Потребность
19
b1
b2
bn

20.

Таблица перевозок
Потребители
Поставщики
А1
В1
В2
…
Вn
x11
x12
…
x1n
Объемы
перевозок от …
n
x1 j
j 1
А2
x21
x22
…
x2n
n
x2 j
j 1
…
…
…
…
…
Аm
xm1
xm2
…
xmn
…
n
x mj
j 1
Объемы перевозок к
…
m
xi1
i 1
m
xi 2
i 1
…
m
xim
i 1
В MS Excel: СУММПРОИЗВ(В2:Е5;В8:Е11)
20

21.

21

22.

Принятые обозначения
Символ
Q
EOQ (Q*)
N
D
S
С
Н
h
Р
D
L
Т
I
ROP
SS
22
Значение
объем заказа, количество единиц
экономичный объем заказа (economic order Quantity)
число заказов в год
годовой (дневной) спрос, количество единиц
издержки заказа или затраты переналадки
стоимость единицы товара, изделия
затраты хранения единицы товара в год
издержки хранения в год, процентов от стоимости
скорость производства, штук в единицу времени
скорость потребления, штук в единицу времени
время выполнения заказа, доставки и т.п.
издержки выполнения заказа, доставки и т.п.
наличие товара на складе, количество единиц
точка перезаказа (reorder point)
страховой запас, безопасный резерв (safety stock)

23.

Необходимые формулы
Годовые издержки хранения TH H *
Годовые издержки заказа
TS
Q
( H Ñ* h)
2
D
S
Q
Полные годовые складские издержки
T
Экономичный размер заказа EOQ( Q* )
23
HQ DS
2
Q
2DS
H

24.

ΔTH = H * SS
ΔTH – дополнительные издержки хранения
безопасного резерва,
SS – величина безопасного резерва (шт.),
H - удельная издержка хранения
24