Letysite.ru

IT Новости с интернет пространства
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Оптимизационные задачи в excel

Решение задач оптимизации в помощью электронных таблиц Excel

Как организовать дистанционное обучение во время карантина?

Помогает проект «Инфоурок»

Решение задач оптимизации с помощью электронных таблиц Excel

Учитель информатики и ИКТ

Кабанова Татьяна Витальевна

Приморского района Санкт-Петербурга

Тема. Решение задач оптимизации в Excel .

Тип урока: обобщение и систематизация знаний.

образовательные — обобщение и систематизация знаний по теме «Обработка числовой информации»

закрепление знаний об общих принципах работы табличного процессора Microsoft Excel;

практическое применение изученного материала, приобретение навыков в составлении таблиц разного типа;

развитие умения выбирать наиболее оптимальную структуру таблицы, создать и оформить таблицу;

формирование представления о вычислениях в электронной таблице как наиболее важных в изучении информатики и широко применяемых на практике.

развивающие — продолжить развитие логического мышления, глубины и гибкости ума.

развитие познавательного интереса, речи и внимания учащихся;

развитие способности логически рассуждать;

формирование информационной культуры и потребности приобретения знаний;

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

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

воспитательные — продолжать воспитывать информационную культуру, общечеловеческие качества личности школьника

воспитание творческого подхода к работе, желания экспериментировать;

воспитание трудолюбия, чувства уважения к науке;

продолжить воспитывать культуру общения;

продолжить формировать чувство долга, настойчивости, дисциплинированность; продолжить формирование творческих, исследовательских качеств учащихся;

продолжить воспитывать эстетический вкус.

Организационный этап. (Цель: настроить учащихся на работу на уроке)

Сегодня мы продолжаем изучение темы «Обработка числовой информации». На предыдущих занятиях мы узнали основные информационные единицы электронной таблицы, типы и форматы данных, используемых в Excel , основные функций, используемых при записи формул, общие правила подготовки электронной таблицы, а так же графические возможности табличного процессора, учились решать задачи с использованием ЭТ.

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

II . Актуализация знаний и фронтальный опрос

– Итак, что мы с вами знаем и умеем делать: создавать редактировать и формировать табличный документ, выполнять вычисления по формулам, применять относительные и абсолютные ссылки, использовать функции. Давайте вспомним и повторим то, что нам уже знакомо.

Вопросы

Ответ

Как называется документ, созданный в электронных таблицах?

Что является основным элементом электронной таблицы?

Что не может включать в себя формула в электронных таблицах?

Как записывают в формуле адрес ячейки, если необходимо отменить принцип относительной адресации при переносе формулы?

Перед именем столбца и номером строки ставится символ $

Какой формат числа вы примените для отображения:

Времени начала уроков?

Дней рождения знакомых?

Порядковых номеров в списке?

Каким будет результат вычислений в ячейке С1?

Какие виды адресации ячеек вы знаете?

Относительная, абсолютная, смешанная.

В каких случаях необходимо использовать абсолютные адреса ячеек в формулах?

Для указания фиксированного адреса ячейки.

В ячейке электронной таблицы С5 записана формула =B5*А5. Какая формула будет получена из нее при копировании в ячейку С6?

Дан фрагмент электронной таблицы:

Значение ячейки С1 вычисляется по формуле = В1+ $A$1. Чему будет равно после копирования формулы значение в ячейке С3?

Перечислите области деятельности человека, к которым можно отнести использование возможностей табличного процессора MS Excel ?

Это – наука, производство, бухгалтерия, торговля, статистика, экология

Таким образом, можно сделать вывод: области применения электронных таблиц очень разнообразны, без них не может обойтись практически ни один современный специалист.

III. Изложение нового материала

Человек, совершая те или иные деяния, стремился вести себя таким образом, чтобы результат, достигаемый как следствие некоторого поступка, оказался в определенном смысле наилучшим. Двигаясь из одного пункта в другой, он стремился найти кратчайший среди возможных путь. Строя жилище, он искал такую его геометрию, которая при наименьшем расходе топлива, обеспечивала приемлемо комфортные условия существования. Занимаясь строительством кораблей, он пытался придать им такую форму, при которой вода оказывала бы наименьшее сопротивление. Можно легко продолжить перечень подобных примеров.

Задачи на отыскание оптимального решения называются задачами оптимизации. Применяемые в процессе оптимизации методы получили название методов оптимизации. При постановке и решении задач оптимизации возникают два вопроса: что и как оптимизировать?

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

Понятие «наилучший, оптимальный» может быть выражено количественными критериями – минимум затрат, минимум времени, максимум прибыли и т.д. Для решения таких задач в ЭТ используется надстройка Поиск решения.

При решении задач оптимизации с помощью MS Excel применяют алгоритм:

Решение задач оптимизации в помощью электронных таблиц Excel

Как организовать дистанционное обучение во время карантина?

Помогает проект «Инфоурок»

Решение задач оптимизации с помощью электронных таблиц Excel

Учитель информатики и ИКТ

Кабанова Татьяна Витальевна

Приморского района Санкт-Петербурга

Тема. Решение задач оптимизации в Excel .

Тип урока: обобщение и систематизация знаний.

образовательные — обобщение и систематизация знаний по теме «Обработка числовой информации»

закрепление знаний об общих принципах работы табличного процессора Microsoft Excel;

практическое применение изученного материала, приобретение навыков в составлении таблиц разного типа;

развитие умения выбирать наиболее оптимальную структуру таблицы, создать и оформить таблицу;

формирование представления о вычислениях в электронной таблице как наиболее важных в изучении информатики и широко применяемых на практике.

развивающие — продолжить развитие логического мышления, глубины и гибкости ума.

развитие познавательного интереса, речи и внимания учащихся;

развитие способности логически рассуждать;

формирование информационной культуры и потребности приобретения знаний;

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

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

воспитательные — продолжать воспитывать информационную культуру, общечеловеческие качества личности школьника

воспитание творческого подхода к работе, желания экспериментировать;

воспитание трудолюбия, чувства уважения к науке;

продолжить воспитывать культуру общения;

продолжить формировать чувство долга, настойчивости, дисциплинированность; продолжить формирование творческих, исследовательских качеств учащихся;

продолжить воспитывать эстетический вкус.

Организационный этап. (Цель: настроить учащихся на работу на уроке)

Сегодня мы продолжаем изучение темы «Обработка числовой информации». На предыдущих занятиях мы узнали основные информационные единицы электронной таблицы, типы и форматы данных, используемых в Excel , основные функций, используемых при записи формул, общие правила подготовки электронной таблицы, а так же графические возможности табличного процессора, учились решать задачи с использованием ЭТ.

Читать еще:  Утилиты для оптимизации андроид

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

II . Актуализация знаний и фронтальный опрос

– Итак, что мы с вами знаем и умеем делать: создавать редактировать и формировать табличный документ, выполнять вычисления по формулам, применять относительные и абсолютные ссылки, использовать функции. Давайте вспомним и повторим то, что нам уже знакомо.

Вопросы

Ответ

Как называется документ, созданный в электронных таблицах?

Что является основным элементом электронной таблицы?

Что не может включать в себя формула в электронных таблицах?

Как записывают в формуле адрес ячейки, если необходимо отменить принцип относительной адресации при переносе формулы?

Перед именем столбца и номером строки ставится символ $

Какой формат числа вы примените для отображения:

Времени начала уроков?

Дней рождения знакомых?

Порядковых номеров в списке?

Каким будет результат вычислений в ячейке С1?

Какие виды адресации ячеек вы знаете?

Относительная, абсолютная, смешанная.

В каких случаях необходимо использовать абсолютные адреса ячеек в формулах?

Для указания фиксированного адреса ячейки.

В ячейке электронной таблицы С5 записана формула =B5*А5. Какая формула будет получена из нее при копировании в ячейку С6?

Дан фрагмент электронной таблицы:

Значение ячейки С1 вычисляется по формуле = В1+ $A$1. Чему будет равно после копирования формулы значение в ячейке С3?

Перечислите области деятельности человека, к которым можно отнести использование возможностей табличного процессора MS Excel ?

Это – наука, производство, бухгалтерия, торговля, статистика, экология

Таким образом, можно сделать вывод: области применения электронных таблиц очень разнообразны, без них не может обойтись практически ни один современный специалист.

III. Изложение нового материала

Человек, совершая те или иные деяния, стремился вести себя таким образом, чтобы результат, достигаемый как следствие некоторого поступка, оказался в определенном смысле наилучшим. Двигаясь из одного пункта в другой, он стремился найти кратчайший среди возможных путь. Строя жилище, он искал такую его геометрию, которая при наименьшем расходе топлива, обеспечивала приемлемо комфортные условия существования. Занимаясь строительством кораблей, он пытался придать им такую форму, при которой вода оказывала бы наименьшее сопротивление. Можно легко продолжить перечень подобных примеров.

Задачи на отыскание оптимального решения называются задачами оптимизации. Применяемые в процессе оптимизации методы получили название методов оптимизации. При постановке и решении задач оптимизации возникают два вопроса: что и как оптимизировать?

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

Понятие «наилучший, оптимальный» может быть выражено количественными критериями – минимум затрат, минимум времени, максимум прибыли и т.д. Для решения таких задач в ЭТ используется надстройка Поиск решения.

При решении задач оптимизации с помощью MS Excel применяют алгоритм:

Технология решения задач оптимизации в Excel

Постановка задачи оптимизации

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

при которых она принимает экстремальное значение (максимальное или минимальное) и при условии, что на эти параметры наложены ограничения.
Эту функцию называют целевой функцией, а набор количественных значений между переменными, выражающих определенные требования к параметрам экономической задачи в виде уравнений или неравенств называют системой ограничений.
Совокупность соотношений, содержащих целевую функцию и ограничения на ее аргументы, называют математической моделью экономической задачи оптимизации.
Если целевая функция линейна и на ее аргументы наложены линейные ограничения, то такую задачу оптимизации называют задачей линейного программирования.
Существуют различные методы решения задач линейного программирования. В MS Excel для этой цели предназначен инструмент Поиск решения. Принцип его работы заключается в использовании итерационного способа подбора параметров целевой функции в сочетании с градиентным методом. Применение этого инструмента позволяет решать задачи оптимизации с высокой точностью. Технологическая последовательность решения задачи включает следующие шаги:

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

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

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

Оптимальный план выпуска продукции

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

Пример . Фирма производит два вида мороженого: сливочное и шоколадное. Для изготовления мороженого используются два исходных продукта: молоко и наполнители, расходы которых на 1 кг готового продукта и их суточные запасы приведены в таблице.

Суточный спрос на сливочное мороженое превышает спрос на шоколадное не более чем на 100 кг. Кроме того известно, что спрос на шоколадное мороженое не превышает 350 кг в сутки. Отпускная цена 1 кг сливочного мороженого 16 ден. ед., шоколадного – 14 ден. ед.

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

Шаг 1 – разработка математической модели

Введем обозначения: x1 – суточный объем производства сливочного мороженого, х2 — суточный объем производства шоколадного мороженого. Исходя из условия задачи целевая функция будет иметь вид

Оптимизационные задачи в excel

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

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

Кроме того, иногда интересует не конкретный результат, а минимально или максимально возможный. Например, как минимизировать затраты на содержание персонала или максимизировать прибыли от реализации продукции?

Такие задачи в Excel решают с помощью Поиска решения.

7.2.1 Задача линейного программирования

    Если математическая модель исследуемого процесса и ограничения на значения ее параметров линейны, то задача достижения цели является задачей линейного программирования.

Познакомимся с решением этих задач на следующем примере.

7.2.1.1 Составление штатного расписания

Усложним рассмотренную в предыдущей главе задачу. Пусть известно, что для нормальной работы больницы необходимо 5-7 санитарок, 8-10 медсестер, 10 врачей, 3 заведующих отделениями, главный врач, заведующий аптекой, заведующая хозяйством и заведующий больницей. Общий месячный фонд зарплаты должен быть минимален. Необходимо определить, какими должны быть оклады сотрудников больницы, при условии, что оклад санитарки не должен быть меньше прожиточного минимума 80 грн.

Читать еще:  Оптимизация кадров это

В качестве модели решения этой задачи возьмем, как и в первой главе, линейную. Запишем ее так:

В этом уравнении нам не известно число санитарок (N1), медсестер (N2), врачей (N3) и оклад санитарки (С).

Используя Поиск решения, найдем их.

Откройте созданный в предыдущей главе файл hospital.xls.

В меню Сервис активизируйте команду Поиск решения.

В окне Установить целевую ячейку укажите ячейку F12, содержащую модель.

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

Используя кнопку Добавить, опишите ограничения задачи.

Окончательно окно Поиска решения будет выглядеть так:

Опишите Параметры поиска, как показано на рис. 7.1.

Щелкните на кнопке ОК, а затем — Выполнить.

Решение приведено на рис. 7.2. Оно тривиально: чем меньше сотрудников и чем меньше их оклад, тем меньше месячный фонд заработной платы.

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

Для закрепления пройденного материала решим следующую задачу.

7.2.1.2 План выгодного производства

Предположим, что мы решили производить несколько видов конфет. Назовем их условно «A», «B» и «C». Известно, что реализация 10-и килограмм конфет «А» дает прибыль 9 грн., «В» — 10 грн. и «С» — 16 грн.

Рисунок 7. 1 — Описание параметров поиска решения

Рисунок 7. 2 — Решение задачи линейного программирования

Конфеты можно производить в любых количествах (сбыт обеспечен), но запасы сырья ограничены. Необходимо определить, каких конфет и сколько десятков килограмм необходимо произвести, чтобы общая прибыль от реализации была максимальной.

Нормы расхода сырья на производство 10 кг конфет каждого вида приведены ниже.

Введите исходные данные и формулы в электронную таблицу, как указано ниже.

В меню Сервис активизируйте команду Поиск решения и опишите его параметры, как указано на рис 7.3.

Не забудьте указать в Параметрах на Линейность модели.

Запустите Поиск решения. Если Вы сделали все верно, то решение будет таким, как на рис 7.4.

Из решения видно, что оптимальный план выпуска предусматривает изготовление 80 кг конфет «В» и 20 кг конфет «С». Конфеты «А» производить не стоит. Полученная Вами прибыль составит 400 грн.

7.2.1.3 Задачи книги Solvsamp.xls

Книга Solvsamp.xls, входящая в состав Excel, в папке ExamplesSolver содержит более сложные примеры использования средств процедуры Поиска решения.

Рисунок 7. 3 — Описание параметров поиска решения

Рисунок 7. 4 — План выгодного производства

Листы с примерами расчетов из этой книги можно использовать как образцы решения Ваших задач оптимизации. Чтобы изучить листы с задачами линейного программирования «Перевозка грузов» 1) , «График работы» и «Оборотный капитал», откройте книгу, перейдите на нужный лист, затем выполните команду Поиск решения из меню Сервис. Целевые ячейки, влияющие ячейки и ограничения на листах уже заданы.

7.2.2 Нахождение экстремума без ограничений

Книга Solverex.xls, входящая в состав Excel, содержит пример решения типичной задачи маркетинга: определения затрат на рекламу, при которых прибыль от реализации товара будет максимальной. Решение этой задачи позволяет определить: «Стоит ли вкладывать дополнительные средства в рекламу, чтобы увеличить прибыль?».

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

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

Исходные данные задачи состоят из 2-х блоков: «Планируемые показатели» и «Данные о продукции». Так, в 1-м квартале запланированы следующие показатели реализации:

  • сезонный фактор (ячейка В2) — равным 0.9 2) ;
  • затраты на заработную плату персонала (ячейка В9) — 8 тыс. грн;
  • затраты на рекламу (ячейка В10) — 10 тыс. грн.

Данные о продукции:

  • цена реализации (ячейка В17) — 40 грн;
  • себестоимость (ячейка В18) — 25 грн.

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

объем сбыта продукции 3) (ячейка В4) нелинейно зависит от сезонного фактора и затрат на рекламу

доход с оборота определяется как ожидаемое количество проданных единиц продукции (ячейка В4), умноженное на себестоимость продукции, поэтому в ячейку В5 введем формулу

фраза «себестоимость реализованной продукции» на языке математики выглядит как

очевидно, что валовая прибыль, имеющая в электронной таблице адрес В7, определяется как

накладные расходы фирмы будем исчислять в объеме 15% дохода с оборота, то есть в ячейку B11 введем формулу

валовые издержки рассчитаем как сумму затрат на заработную плату персонала, рекламу и накладные расходы, то есть

прибыль от продукции определим как валовую прибыль минус валовые издержки, то есть

Если вышеперечисленные данные и формулы ввести в таблицу, то результаты вычислений будут такими, как показано на рис. 7.5.

Активизируем Сервис=>Поиск решения и опишем условия решения задачи:

  • задайте ячейку B14, как содержащую целевую функцию 4) ;
  • определите цель оптимизации — Максимальное значение;
  • укажите ячейку, значение которой будет изменяться при поиске наилучшего решения 5) — B10;
  • при назначении параметров укажите на нелинейность модели. Для остальных параметров используйте установки по умолчанию, которые подходят для решения большинства задач.

    Щелкнув на кнопке Выполнить, Вы увидите следующий результат оптимизации (рис. 7.6).

    Как видно из полученного решения при, затратах на рекламу 17 093 грн. прибыль от реализации товара будет максимальной. Однако следует отметить, что максимизация прибыли не обязательно соответствует наивысшему значению рентабельности. Ее значение уменьшилось до 8%.

    Рисунок 7. 5 — Данные для поиска экстремума в задаче маркетинга

      Контрольное задание 7.2

      Покажите, что функция

      f = (x-1) 2 + y2 — 0,5cos(2z)

      имеет минимальное значение -0,5 при x=1; y=-7,9E-07 и z=3,14159.

      В качестве исходных данных для поиска минимума примите x=1, y=2 и z=3.

      Рисунок 7. 6 — Результат оптимизации

      7.2.3 Задача нелинейного программирования

      Если математическая модель исследуемого процесса или ограничения на значения ее параметров нелинейны, то задача достижения цели является задачей нелинейного программирования.

      Решите предыдущую задачу при условии, что затраты на рекламу не могут превышать 12 000 грн.

      Решение задачи — 14 722 грн.

      Отметим, что в этом случае с каждой вложенной гривны Вы получите 10 коп. дохода.

        Контрольное задание 7.3

        Покажите, что при ограничениях x + 2y = 0, y >= 0 функция

        f = x(2 — x) + 2y(2 — y)

        имеет максимальное значение 3 при x=1 и y=1.

        В качестве исходных данных для поиска минимума примите x=0 и y=0.

        1) Эту задачу иногда называют «Транспортная».

        2) Сезонный фактор отражает колебания спроса на товар в зависимости от времени года. Например, зимой чаще покупают теплые вещи.

        3) Объем сбыта определяется количеством (штуками) проданной продукции.

        4) Помните, что в адресе должна указываться ячейка, в которой содержится формула, а не числовое значение, дата, или текст.

        5) Помните, что переменные — это числовые значения, а не даты, формулы или текст.

        Читать еще:  Drivers for linux

        Оптимизация смен рабочих с помощью Поиска решений в EXCEL

        Решим задачу об оптимизации смен рабочих с помощью Поиска решений MS EXCEL 2010. В качестве примера разберем задачу из сборника «Методы оптимизации управления и принятия решений» авторы Зайцев М.Г. и Варюхин С.Е. (2008г.). Задача 2.43 «Проблема мастера».

        Сборник задач «Методы оптимизации управления и принятия решений» довольно популярен в качестве задачника для Вузов, поэтому ссылки на него встречаются повсеместно в интернете. Приятно, что для каждой задачи имеется ответ в конце книги — можно сравнить получившееся решение с решением, которое найдено авторами с помощью Поиска решения (в году так 2000-м, я полагаю).

        Условия задачи (оригинальные)

        Мастер должен выбрать несколько фрезеровщиков из 10 (Р1,Р2..Р10) для изготовления 8 видов деталей (Д1,Д2. Д8) для партии продукции:

        так, чтобы сократить общие затраты рабочего времени. Среднее количество деталей, которое каждый рабочий может обработать за смену, дано в таблице:

        а. Определить оптимальное распределение рабочих по операциям, принимая во внимание, что рабочие Р5 и Р9 не могут изготавливать детали Д5 и Д2 соответственно.b. Каков самый короткий срок выполнения этого заказа?

        Разбор условия задачи

        Скажу честно, но условия задачи не кажутся мне четко сформулированными. Вот мои комментарии:

        1) Рабочих называют сначала фрезеровщиками, затем просто рабочими. Зачем?

        2) Используется понятие «операции», то есть подразумевается некая определенная последовательность действий. То есть можно подумать, что сначала делается операция Д1 (изготовление детали 1), затем Д2 и т.д. Чтобы выяснить это, обратимся к ответу:

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

        3) В ответе целая и дробные части смены отделены точкой, но как известно, в России разделителем является запятая. Кроме того, очевидно, что дробная часть смены приведет к дробному количеству изготовленных деталей (это как то непонятно). Как следствие, в задаче планируется складывать 63,111 и 48,888 деталей, чтобы выполнить заказ. Что не очень логично.

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

        5) Зачем сказано, что «рабочие Р5 и Р9 не могут изготавливать детали Д5 и Д2»? Просто поставьте ноль, никакого смысла в этом условии нет.

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

        Решение

        Если вы не знакомы с Поиском решения, то для начала прочитайте статью Поиск решения MS EXCEL. Знакомство .

        Чтобы построить модель для Поиска решения (ПР) в EXCEL нужно определить 3 ее составляющих:

        • целевая функция (то, что мы будем оптимизировать, например, минимизировать трудозатраты), это формула в одной ячейке ( ячейку выделим красным );
        • переменные модели, это то что будет изменять ПР в ходе поиска ( ячейки выделим зеленым );
        • ограничения модели, например, Заказ должен быть выполнен ( ячейки выделим синим ).

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

        Теперь рассчитаем, сколько же деталей будет изготовлено при такой загрузке рабочих.

        Оказывается, в решении опечатка. При данном ответе не будет выполнен заказ — основное условие задачи. Оказывается, деталь Д6 также изготавливает рабочий Р4. После добавления нужного количества смен, получим правильный ответ — теперь заказ выполнен.

        Вернемся к таблице загрузки рабочих по сменам и еще раз внимательно посмотрим на решение, приведенное в задачнике.

        Оказывается, количество смен у каждого рабочего составляет от 1 до 4 (ровно)! Почему? Потому что, это дополнительное ограничение, про которое ничего не сказано в условии задачи. Получим это решение. Для этого создадим модель.

        Будем минимизировать общее количество смен всех рабочих (см. формулу в красной ячейке). В качестве ограничений потребуем выполнение заказа и загрузку каждого рабочего от 1 до 4 смен. Переменными являются ячейки, содержащие количества смен рабочих.

        Нажав кнопку Найти решение получим именно тот ответ, который дан в задачнике. Чтобы понять, откуда взялось ограничение про загрузку каждого рабочего от 1 до 4 смен, удалим эти ограничения и снова запустим Поиск решения. Вот ответ:

        Как видим, общее количество смен всех рабочих уменьшилось (35,78 против 37,23). Так зачем авторам потребовались эти дополнительные ограничения? Дело в том, что без этих ограничений мы получим тривиальный ответ, который очевиден и без Поиска решения! Сравните загрузку рабочих с их производительностью:

        Максимальная производительность выделена цветом и поразительно совпадает с найденным решением. На самом деле решение очевидно: чтобы сократить загрузку рабочих — возьми самых производительных (по типам деталей) и загрузи их! Этот факт приводит к мысли, что либо задача некорректно сформулирована, либо неправильно решена. Будем грешить на решение.

        Перед тем как решить задачу другим способом, ответим на второй вопрос задачи: «Каков самый короткий срок выполнения этого заказа?» (см. файл примера, лист б )

        Чтобы вычислить самый короткий срок выполнения заказа — просто уберем 2 доп. ограничения о количестве смен (от 1 до 4) в окне Поиска решения и изменим целевую функцию в красной ячейке J31 . Теперь будем МИНимизировать МАКСимальную длительность смен рабочих ( =МАКС(J21:J30) ).

        При этом считаем, что все 10 рабочих работают независимо друг от друга (на разных станках), поэтому время выполнения заказа будет равно максимальному количеству смен у одного из рабочих. Полученный ответ 3,89 совпадает с ответом в задачнике (менее 4-х дней).

        Альтернативное решение

        Теперь попробуем переформулировать условие задачи.

        Во-первых, предположим, что каждый рабочий за 8-ми часовую смену обязан сделать все 8 типов деталей (от Д1 до Д8). Т.е., в 1-й час каждый рабочий делает Д1, во 2-й час каждый рабочий делает Д2 и т.д. Производительность дана в таблице.

        Во-вторых, заказ нужно сделать за 1 смену.

        Вопрос: каких рабочих нужно взять, чтобы выполнить заказ минимальным количеством рабочих?

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

        Если для выполнения заказа мы берем рабочего, то ему присваивается значение 1, если нет, то 0.

        Поиск решения (по прежнему линейная задача) находит, что достаточно взять 7 рабочих, чтобы выполнить заказ. В этом случае, правда, заказ будет несколько перевыполнен по некоторым типам деталей.

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

      • Ссылка на основную публикацию
        Adblock
        detector