Руководитель:
г. Саратов, МОУ «СОШ №8» Волжского района г. Саратова
Использование средства Поиск решения табличного процессора MS Excel при решении экономических задач
Всем хорошо известна востребованность экономических знаний сегодня. Самостоятельность в принятии решений, эффективный анализ проблемы, отстаивание собственной точки зрения – эти качества приобретёт каждый, работая в одной команде над решением экономической задачи.
Для решения тех или иных экономических задач хорошо было бы использовать различные высокотехнологичные компьютерные программы. К сожалению, для многих современных организаций это не представляется возможным, т. к. у каждой из них существует свой ряд особенностей производства, а разработка таких программ для каждого предприятия в отдельности повлечет за собой большие материальные затраты.
В своей работе я хотела бы наглядно продемонстрировать, что для индивидуального предпринимателя или небольшой фирмы хорошей альтернативой специализированным программам может оказаться использование обычного табличного процессора MS Excel, в котором есть все необходимые средства и возможности для выполнения экономических, производственных, оптимизационных расчетов. С помощью MS Excel каждая организация сможет решать такие задачи, во-первых, экономя массу времени, а, во-вторых, сохраняя свой бюджет. Именно индивидуальный подход к решению достаточно сложных задач и наличие надежных алгоритмов позволяют получить решение быстро и эффективно.
Цель моей работы – убедиться в том, что табличный процессор MS Excel является мощным средством при решении задач оптимизации.
Задача моей работы – решить сбалансированную и несбалансированную задачи о назначениях с помощью табличного процессора MS Excel на конкретном примере.
В простейшем случае задача о назначениях формулируется следующим образом: имеется несколько различных видов работ и столько же сотрудников, претендующих на выполнение этих работ. На выполнение каждого вида работ назначается только один сотрудник, при этом каждый сотрудник может выполнить любую работу, но за свою плату и свое время. Необходимо так распределить работы между сотрудниками, чтобы минимизировать суммарные денежные затраты или суммарное время выполнения всех работ.
При решении задач о назначениях полезна следующая таблица. Здесь коэффициенты сij равны стоимости назначения сотрудник сотрудника i на выполнение работы j. Переменные xij — это переменные будущего решения задачи. Они определяются так: хij = 1, если сотрудник i назначается на выполнение работы j, и хij = 0, если работник i не назначается на выполнение работы j.
Задачи о назначениях делятся на сбалансированные и несбалансированные. Если количества сотрудников и работ равны, то такая задача о назначениях называется сбалансированной, в противном случае задача будет несбалансированной.
Причем несбалансированные задачи о назначениях подразделяются на задачи с избытком предложения и избытком спроса. Сбалансированной также будет задача, если на некоторые процессы требуются несколько работников, но при этом общие количества требуемых и имеющихся работников совпадают. Такая задача называется задачей о назначениях для коллективной работы.
Рассмотрим таблицу для такой работы. Построим математическую модель для задачи о назначениях: вначале определим количество двоичных переменных, а затем определим целевую функцию.
Эту целевую функцию следует минимизировать, если коэффициенты cij выражают стоимости назначения работника i на выполнение работы j. Ограничения для этой модели я рассмотрю для сбалансированных и несбалансированных задач в отдельности.
Запишем ограничения для сбалансированной задачи (без условия коллективной работы). Имеем п ограничений в виде равенств для каждого сотрудника и п ограничений в виде равенств для каждой работы.
Теперь запишем ограничения для сбалансированной задачи о назначениях для коллективной работы. Имеем п ограничений в виде равенств для каждого сотрудника и т ограничений в виде равенств для каждой работы.
В несбалансированных задачах о назначениях обычно количество сотрудников значительно больше работ, т. е. спрос больше предложения. Именно такую задачу я и рассмотрю.
Сначала я рассмотрю задачу без условия коллективной работы. Пусть имеется m работ и п сотрудников, при этом т меньше п. Тогда все вакансии будут заполнены, но не все работники получат работу.
Имеем п ограничений в виде неравенств для каждого сотрудника и т ограничений в виде равенств для каждой работы. Запишем ограничения для несбалансированной задачи о назначениях для коллективной работы. Получаем п ограничений в виде неравенств для каждого сотрудника и m ограничений в виде равенств для каждой работы.
Решение задачи о назначениях я буду рассматривать на примере бригад бетонщиков строительной компании «Кронверк», бригадиром одной из которых работает мой дядя . В целях решения поставленной задачи я проконсультировалась со своим дядей о работе бетонщиков этого предприятия и о том, как происходит распределение работ между сотрудниками.
И вот, что я узнала. Распределение работ между сотрудниками происходит по следующему принципу: бригадир каждой бригады внимательно изучает объект и определяет сроки, за которые его бригада сможет выполнить работы. Затем после определенных расчетов бригада закрепляется за тем объектом, у которого бригадир определил самый маленький срок выполнения работ. Все это я подробно рассмотрю на примерах.
Мой дядя рассказал мне, как в 2006 году при начале строительства микрорайона 1А в поселке Солнечный распределили бетонные работы на строящихся объектах между имеющимися бригадами бетонщиков. Тогда распределение объектов между бригадами заняло немало времени, т. к. расчеты велись практически вручную. Используя полученные навыки, я выполнила те же самые расчеты средствами табличного процессора MS Excel и получила такие же результаты, но времени потратила гораздо меньше.
Итак, строительной фирме «Кронверк» необходимо было выполнить бетонные работы на 4 строящихся объектах в микрорайоне 1А поселка Солнечный. В фирме на тот момент как раз имелось 4 свободных бригады бетонщиков, которые могли выполнить эту работу. Бригадиры каждой бригады, включая моего дядю, побывали на этих объектах, оценили объемы работ и рассчитали сроки, за которые они могли выполнить работы.
Сроки выполнения работ каждой бригадой приведены на этом слайде. Перед руководством фирмы встала задача распределения бригад по объектам таким образом, чтобы суммарный срок выполнения всех работ был минимальным. Поскольку количества бригад и объектов одинаковы, следовательно, мы имеем сбалансированную задачу о назначениях.
Составляем математическую модель сбалансированной задачи. Имеем 16 двоичных переменных. Целевую функцию можно записать следующим образом.
Теперь запишем ограничения. Задача у нас сбалансированная, поэтому имеем 8 простых ограничений в виде равенств.
Далее на основании записанной математической модели создаем на рабочем листе Excel табличную модель.
В ячейку с целевой функцией F18 вводим формулу для подсчета. В диапазонах F12:F15 и В16:Е16 вычисляются суммы значений переменных соответственно по строкам и столбцам матрицы решения.
Запускаем средство Поиск решения. В одноименном диалоговом окне задаем адрес целевой ячейки, устанавливаем переключатель минимальному значению, указываем адрес диапазона изменяемых ячеек, задаем ограничения. В диалоговом окне Параметры поиск решения устанавливаем флажки Линейная модель и Автоматическое масштабирование, флажок Неотрицательные значения можно не устанавливать. Наконец, в диалоговом окне Поиск решения щелкаем на кнопке Выполнить и получаем следующее решение.
Найденное решение показывает, что первую бригаду надо отправить на 3 объект, вторую бригаду на 4 объект, третью бригаду (бригаду моего дяди) на 1 объект и четвертую — на 2 объект. Общая продолжительность всех работ составит 175 рабочих дней.
Т. к. число объектов совпало с количеством бригад, то для того, чтобы решить несбалансированную задачу о назначениях (с избытком спроса и избытком предложения), я создала две вымышленные ситуации.
Первая ситуация: предположим, что пока руководство решало, какую бригаду бетонщиков послать на какой объект, на предыдущем объекте освободилась от работ еще одна бригада бетонщиков и выразила готовность также подключиться к работе на одном из четырех объектов. Бригадир этой бригады оценил работы на каждом объекте и подсчитал, что работы на первом объекте его бригада выполнит за 29 рабочих дней, на втором объекте за 40 дней, на третьем объекте за 48 дней и на четвертом — за 59 рабочих дней. Сроки выполнения работ приведены на слайде.
Сейчас количество бригад больше количества объектов, поэтому имеем несбалансированную задачу о назначениях с избытком предложений. Очевидно, что некоторые бригады не будут привлекаться к работам. Составляем математическую модель данной задачи. Определяем уже 20 двоичных переменных.
Целевую функцию можно записать в следующем виде. Записываем ограничения. Задача несбалансированная, поэтому имеем 4 простых ограничений в виде равенств и 5 ограничений в виде неравенств.
Строим табличную модель для этой задачи.
Для решения данной задачи диалоговое окно Поиск решения должно иметь такой вид: слайд. Основные отличия этого диалогового окна заключаются в том, что между изменяемыми ячейками стоит теперь знак «<=».После щелчка на кнопке Выполнить диалогового окна Поиск решения получаем следующее решение.
В результате этого решения первая и пятая бригады остаются безработными, вторая бригада отправляется на 2 объекта (1 и 3 объекты), третья — на 2-й объект, а четвертая — на 4-й. При наших данных о сроках выполнения работ первый объект остается не задействованным, т. е. заявка осталась невыполненной. Общая продолжительность всех работ составит 195 рабочих дня, что больше на 20 дней решения сбалансированной задачи.
Вторая ситуация: предположим, что пока руководство решало, какую бригаду бетонщиков послать на какой объект, большая часть бетонщиков одной из 4 бригад (пусть это будет бригада №2) заболела и бригада не смогла приступить к выполнению работы. Осталось 3 бригады и 4 объекта, на которых нужно выполнить данный вид работы. Таким образом, получаем новые сроки выполнения работ (в рабочих днях).
Т. к. количество бригад меньше количества объектов, мы имеем несбалансированную задачу о назначениях с избытком спроса. Здесь следует рассмотреть два варианта:
· необходимо выполнить все заявки, но при этом некоторые бригады будут вынуждены выполнить несколько заявок;
· каждая бригада может выполнить только одну заявку, но при этом часть заявок останется невыполненной.
В соответствии с этим нужно использовать два различных варианта ограничений, используемых при решении задачи. Составляем математическую модель. Определяем 12 двоичных переменных.
Целевую функцию записываем в следующем виде. Записываем ограничения. Задача несбалансированная, поэтому имеем 4 простых ограничений в виде равенств и 3 ограничения в виде неравенств.
Строим табличную модель для этой задачи. Для решения данной задачи 1 варианта диалоговое окно Поиск решения должно иметь следующий вид. После щелчка на кнопке Выполнить диалогового окна Поиск решения получаем такое решение.
В результате этого решения первая бригада вновь остается безработной, третья бригада отправляется сразу на 3 объекта (1, 3 и 4 объекты), а четвертая — на 2-й. При наших данных все объекты задействованы, т. е. заявка выполнены. Общая продолжительность всех работ составит 173 рабочих дня, что меньше на 2 дня решения сбалансированной задачи.
Для решения данной задачи 2 варианта диалоговое окно Поиск решения должно иметь такой вид. После щелчка на кнопке Выполнить диалогового окна Поиск решения получаем следующее решение.
В результате этого решения первая бригада отправляется на 2 объект, третья бригада отправляется на 1 объект, а четвертая — на 3-й. При наших данных 4 объект остается не задействованным, т. е. заявка не выполнена. Общая продолжительность всех работ составит 117 рабочих дня, что меньше на 58 дней решения сбалансированной задачи.
В ходе выполнения данной работы я неоднократно убедилась в том, что табличный процессор MS Excel имеет все необходимые средства для выполнения экономических, производственных и оптимизационных расчетов и экономит массу времени.
Представленные в данной работе решения задачи о назначениях различных видов на примере строительной фирмы «Кронверк» с помощью MS Excel напрямую доказывают это.
