Автоматизация решения задачи о назначениях в MS Excel. Линейная модель. Поиск решения. Автоматизация решения задачи о назначениях в ПЭР
Лабораторная работа №5
Условие задачи
Имеется 6 исполнителей, которые могут выполнять 6 различных работ.
Известны затраты, связанные с назначением i - го исполнителя на j – ю работу, задаваемые матрицей (c i j ) размерностью 66 ( i, j 16 ).
Необходимо произвести назначение исполнителей на работы таким образом, чтобы общие затраты всех назначений были бы минимальными, при условии, что каждый исполнитель может быть назначен только на одну работу и за каждой работой может быть закреплен только один исполнитель.
Автоматизация решения задачи о назначениях в MS Excel
i\j |
1 |
2 |
3 |
4 |
5 |
6 |
1 |
3 |
8 |
1 |
6 |
4 |
1 |
2 |
5 |
7 |
8 |
5 |
3 |
1 |
3 |
4 |
5 |
3 |
2 |
2 |
1 |
4 |
6 |
3 |
7 |
4 |
5 |
1 |
5 |
7 |
2 |
8 |
8 |
6 |
1 |
6 |
6 |
4 |
8 |
7 |
3 |
1 |
Данная задачa, которая является совершенно полной и может быть решена с использованием модуля «Поиск решения» электронных таблиц MS Excel. Для этого расположим данные на листе Excel как показано на рис.1.
рис.1
Матрица затрат задана массивом чисел C4:H9, матрица назначений (переменных) – массивом C13:h28, формулы для целевой функции и основных ограничений – по строкам и столбцам матрицы переменных – указаны в соответствующих ячейках на рис.2.
рис.2
В результате, после ввода всех формул, массивов и ограничений окно модуля «Поиск решения» выглядит так, как показано на рис. 3
рис.3
После ввода необходимых параметров «Линейная модель» модуль «Поиск решения» запускается на решение задачи, а его результат представлен на рис.4.
рис.4
Автоматизация решения задачи о назначениях в ПЭР
Для решения этой задачи в среде ПЭР необходимо в главном меню программы выбрать пункт «Задача о назначениях».
Далее в режиме ввода новой задачи ввести в ПЭР все необходимые параметры задачи.
После чего ввести числовые данные задачи.
После того, как данные задачи будут сформированы необходимо войти в режим решения задачи о назначениях функционального меню ПЭР и вывести таблицы всех итераций решения данной задачи. Начальная таблица приведена на рис.4
рис.4
На первой итерации ПЭР «получает» приведенную матрицу.
Специальными стрелками в последних строке и столбце таблицы первой итерации обозначены вертикальные и горизонтальные лини зачеркивания.
рис.5
рис.6
рис.7
На первой итерации ПЭР «получает» приведенную матрицу.
Специальными стрелками в последних строке и столбце таблицы первой итерации обозначены вертикальные и горизонтальные лини зачеркивания.
рис.8
Однако, сам план назначений в табличной форме выводится отдельно как показано на рис.9
рис.9
Автоматизация решения задачи о назначениях в программе Venger
Программа Venger разработана специально для автоматизации процесса решения задачи о назначениях по алгоритму венгерского метода и предназначена для использования в среде ОС Windows.
На итерации «Решение» отображается исходная матрица эффективностей назначений, в которой красным цветом выделены элементы, соответствующие единичным элементам матрицы назначений.
Выделенные элементы являются слагаемыми оптимального значения целевой функции данной задачи.
рис.10
Вывод: Я
v Приобретение навыков построения математической модели задачи о назначениях.
v Приобретение навыков автоматизированного решения задачи о назначениях в среде программ Microsoft Excel и ПЭР.
v Приобретение навыков автоматизированного решения задачи о назначениях по алгоритму венгерского метода в среде программы Venger.
Лабораторная работа № 5 Постановка и решения задачи о назначении.
Цель: Научиться составлять модели и решать задачи о назначении. Решение задачи о назначении (Венгерский алгоритм). Проверка решения с помощью Excel.
Решение задачи о назначениях в Excel с использованием настройки Поиск решения
Задача о назначенияхяляется частным видом линейнойоптимизационной задачи. Наиболее частозадача о назначенияхпредставляется следующим образом:
Имеются nрабочих иmвидов работ. Стоимостьcijвыполненияi-м рабочимj-той работы приведена в таблице, где под строкой понимается рабочий, а под столбцом — работа. Необходимо составить план работ так чтобы все работы были выполнены, каждый рабочий был занят только на одной работе, а суммарная стоимость выполнения всех работ была бы минимальной.
Решение задачи о назначенияхочень похоже на решениетранспортной задачи. Особеность лишь в том, что плановые переменные могут принимать только значения 0 или 1 и в каждом столбце и строке может быть только одно ненулевое значение.
После чего следует выбрать в Excelпункт менюДанные/Поиск решения, в окнеПоиск решениявыбрать целевую ячейку, изменяемые ячейки и добавить ограничения. Как правила используются ограничения следующего вида:
Неотрицательность значений изменяемых ячеек;
Суммы значений изменяемых ячеек для каждой строки и столбца должны быть равны 1;
Иногда бывает необходимо задать целечисленные ограничения на изменяемые ячейки.
Далее следует нажать кнопку Выполнить, после чего будет получено решениезадачи о назначениях.
Довольно часто задача о назначенияхбывает представлена в так называемом несбалансированном виде (количество работ не равно количеству работников). В этом случае для приведениязадачи о назначенияхк сбалансированному виду следует добавить в таблицу одну или несколько фиктивных работ или работников.
Задание 1.Решение задачи о назначениях.
Имеются n рабочих и m видов работ. Стоимость cij выполнения i-м рабочим j-той работы приведена в таблице, где под строкой понимается рабочий, а под столбцом — работа. Необходимо составить план работ так чтобы все работы были выполнены, каждый рабочий был занят только на одной работе, а суммарная стоимость выполнения всех работ была бы минимальной.
В результате должен получится следующий результат:
2.Математическая модель задачи.
Переменными xi,j обозначим назначение с i—го рабочего на j—ую пункт работу. xi,j может принимать значения 1 (назначен) и 0 (не назначен). сi,j – стоимость выполнения i-м рабочим j-той работы. ,. Так как количество рабочих превышает количество работ, то не всем рабочим будет назначена работа.
3.Решение задачи средствами ms Excel.
В качестве переменных хij будем использовать диапазон B10:E14. Для значения целевой функции будем использовать ячейку С18 в которую введем формулу =СУММПРОИЗВ(B2:E6;B10:E14). Функция СУММПРОИЗВ перемножает соответствующие элементы заданных массивов и возвращает сумму произведений. Для вычисления ограничений задачи используется функция СУММ. Функция СУММ суммирует все числа в интервале ячеек.
Далее выбираем пункт меню Данные/Поиск решения:
Открывается диалоговое окно Поиск решения. В нём указываем, что нам необходимо установить ячейку С18 минимальному значению, изменяя ячейки B10:E14. Далее нажимаем кнопку Добавить для добавления ограничений. И добавляем следующие ограничения:
(неотрицательность)
(работы)
(работники)
После ввода каждого ограничения нажимаем кнопку Добавить. После ввода последнего ограничения нажимаем кнопку OK. И диалоговое окно Поиск решения принимает следующий вид:
В параметрах ввести
Нажимаем кнопку Выполнить. И перед нами открывается диалоговое окно Результаты поиска решения:
Выбираем создание отчётов всех типов. После нажатия кнопки OK в рабочей книге появляются новые листы с названиями: «Отчет по результатам 2», «Отчет по устойчивости 2», «Отчет по пределам 2». Получаем следующие результаты:
Назначайте задачи с @упоминаниями в Excel для Интернета
Здравствуйте, участники программы предварительной оценки Office. Меня зовут Скотт Макфадден, я руководитель программы в команде Excel. Я очень рад сообщить, что теперь вы можете назначать задачи с помощью @mentions на своих листах в Excel для Интернета.
Назначение задач
При совместной работе с другими пользователями теперь можно использовать @упоминания в комментариях для создания и назначения задач на рабочих листах. Люди, которым вы назначаете задачи, будут получать уведомления по электронной почте, сообщая другим, что вам нужно, чтобы они действовали.
Как это работает
- Щелкните правой кнопкой мыши ячейку, содержащую информацию, которую вы хотите прокомментировать, и выберите Новый комментарий .
- Напишите свой комментарий и введите @, а затем имя члена команды, которого вы хотите отметить.
- Нажмите зеленую стрелку или нажмите Ctrl + Enter , чтобы опубликовать комментарий.
- Установите флажок Назначить , чтобы преобразовать комментарий в задачу.
Сценарии, которые можно попробовать
- Переназначить задачу
- Введите @ , затем имя члена команды, которому вы хотите переназначить задачу, в поле ответа, а затем установите флажок Переназначить на .
- Щелкните зеленую стрелку или нажмите Ctrl + Enter , чтобы опубликовать комментарий.
Человек, которому вы передали задачу, будет уведомлен по электронной почте о том, что ему была назначена задача.
- Решить задачу
- Наведите указатель мыши на кружок над комментарием и нажмите Разрешить цепочку .
- Повторно открыть решенную задачу
- Наведите курсор на ячейку, содержащую комментарий, и нажмите Повторно открыть цепочку .
Известные проблемы
Если вы назначаете задачу с помощью Excel в Интернете, она отслеживается на протяжении всех взаимодействий и повторных назначений, поэтому вы можете просмотреть ее историю. Однако, поскольку в настоящее время задачи доступны только корпоративным пользователям в Excel для Интернета, вы пока не можете переназначать задачи, созданные в Excel для Интернета с помощью Excel для Windows. Вы можете ответить на комментарий, разрешить или повторно открыть задачу и просмотреть историю комментариев, но не сможете просмотреть историю задачи.
Например, если вы назначите задачу в Excel для Интернета, а затем откроете документ в Excel для Windows, вы увидите комментарий, но не задачу. Если вы повторно откроете документ в Excel в Интернете, вы увидите задачу и сможете взаимодействовать с ней.
Требования
Чтобы увидеть Задачи в Интернете, файл Excel должен храниться в OneDrive для бизнеса.
Доступность
Эта функция доступна в Excel для Интернета. Мы работаем над тем, чтобы в будущем добавить эту функцию в Excel для рабочего стола Windows.
Обратная связь
Существует несколько способов отправить отзыв и предложение:
- Щелкните Справка > Обратная связь .
- Добавьте #AssignTasks в свой отзыв, чтобы мы могли легко найти информацию об этой функции.
Узнайте, какую еще информацию вы должны включить в свой отзыв, чтобы он был действенным и дошел до нужных людей. Мы рады услышать от вас!
Подпишитесь на рассылку новостей Office Insider и получайте последнюю информацию о функциях программы предварительной оценки на свой почтовый ящик раз в месяц!
Назначение задач с @упоминаниями в Excel
Привет, участники программы предварительной оценки Office. Меня зовут Скотт Макфадден, я менеджер по продукту в команде Excel. Я рад сообщить, что теперь вы можете назначать задачи с @упоминаниями на листах в Excel для Windows и Excel для Mac!
Назначать задачи
Мы добавили эту популярную функцию (ранее доступную только в Excel для Интернета) на ваши рабочие столы Windows и Mac.
Эта функция позволяет вам и вашей команде удобно создавать и назначать задачи прямо из рабочего листа Excel, используя @упоминания в комментариях. Люди, которым вы назначаете задачи, будут получать уведомления по электронной почте, сообщая им, что им необходимо принять меры.
Как это работает
- Щелкните правой кнопкой мыши ячейку, содержащую информацию, которую вы хотите прокомментировать, и выберите Новый комментарий .
- Напишите свой комментарий и введите @ , а затем имя члена команды, которого вы хотите отметить.
- Установите флажок Назначить , чтобы преобразовать ваш комментарий в задачу.
- Щелкните зеленую стрелку или нажмите Ctrl + Enter , чтобы опубликовать свой комментарий и назначить задачу.
Сценарии, которые можно попробовать
- Переназначить задачу
- Введите @ , затем имя члена команды, которому вы хотите переназначить задачу, в поле ответа, а затем установите флажок Переназначить на .
- Щелкните зеленую стрелку или нажмите Ctrl + Enter ; человек, которому вы переназначили задачу, будет уведомлен по электронной почте о том, что ему была назначена задача.
- Решить задачу
- Наведите курсор на кружок над комментарием и нажмите Разрешить цепочку .
- Повторно открыть решенную задачу
- Наведите указатель мыши на ячейку, содержащую комментарий, и щелкните Повторно открыть цепочку .
Требования
Для просмотра задач в Excel файл Excel должен храниться в OneDrive или SharePoint.
Доступность
Эта функция доступна для всех пользователей в Интернете и для пользователей бета-канала:
- Windows: версия 2208 (сборка 15504.10000) или более поздняя
- Mac: версия 16.66 (сборка 22090700) или более поздняя
Еще нет? Это скорее мы, а не вы.
Функции выпускаются в течение некоторого времени, чтобы обеспечить бесперебойную работу. Мы выделяем функции, которых у вас может не быть, потому что они медленно выпускаются для большего числа инсайдеров. Иногда мы удаляем элементы, чтобы улучшить их на основе ваших отзывов.