Excel не была первой программой для работы с электронными таблицами. Lotus 1-2-3 не была первой программой для работы с электронными таблицами. Первой программой для работы с электронными таблицами была VisiCalc в 1979 году. VisiCalc, разработанная Дэном Бриклином и Бобом Франкстоном, была опубликована Дэном Филстрой. Сегодня Дэн руководит Frontline Systems. Его компания написала Solver, используемый в Excel. Frontline Systems также разработала целый набор аналитического программного обеспечения, которое работает с Excel.
Если у вас есть Excel, у вас есть Solver. Возможно, он не включен, но он у вас есть. Чтобы включить Решатель в Excel, нажмите Alt + T, а затем I. Установите флажок рядом с Надстройкой Решателя.
Чтобы успешно использовать Solver, вы должны построить модель рабочего листа, состоящую из трех элементов:
- Должна быть одна ячейка цели. Это ячейка, которую вы хотите минимизировать, развернуть или установить на определенное значение.
- Ячеек ввода может быть много. Это одно из фундаментальных улучшений по сравнению с Goal Seek, который может работать только с одной входной ячейкой.
- Могут быть ограничения.
Ваша цель - разработать требования к расписанию для парка развлечений. Каждый сотрудник будет работать пять дней подряд, а затем у него будет два выходных. Есть семь различных способов назначить кого-то на пять дней подряд и два выходных. Они показаны текстом в формате A4: A10 на рисунке ниже. Синие ячейки в B4: B10 - это ячейки ввода. Здесь вы указываете, сколько человек у вас работает по каждому графику.
Ячейка «Цель» - это общая заработная плата за неделю, показанная в B17. Это прямая математика: общее количество людей из B11, умноженное на зарплату в 68 долларов на человека в день. Вы попросите Solver найти способ минимизировать еженедельную заработную плату.
В красном поле показаны значения, которые не изменятся. Именно столько людей вам нужно, чтобы работать в парке каждый день недели. Вам нужно как минимум 30 человек в загруженные выходные дни, но всего 12 человек в понедельник и вторник. Оранжевые ячейки используют СУММПРОИЗВ, чтобы вычислить, сколько людей будет запланировано на каждый день, на основе входных данных в синих ячейках.
Значки в строке 15 показывают, нужно ли вам больше людей или меньше людей, или у вас точно нужное количество людей.
Сначала я попытался решить эту проблему без Solver. Я ходил с 4 сотрудниками каждый день. Это было здорово, но в воскресенье у меня не хватило людей. Итак, я начал увеличивать графики, чтобы получить больше сотрудников по воскресеньям. Я получил кое-что, что работает: 38 сотрудников и 2584 доллара еженедельной заработной платы.
Конечно, есть более простой способ решить эту проблему. Щелкните значок Solver на вкладке Data. Сообщите Solver, что вы пытаетесь установить заработную плату в B17 на минимум. Входные ячейки - B4: B10.
Ограничения делятся на очевидные и не столь очевидные категории.
Первое очевидное ограничение - это то, что D12: J12 должен быть >= D14:J14.
Но если вы попытаетесь запустить Solver сейчас, вы получите странные результаты с дробным числом людей и, возможно, с отрицательным числом людей, работающих по определенному графику.
Хотя вам кажется очевидным, что вы не можете нанять 0,39 человека, вам нужно добавить ограничения, чтобы сообщить Solver, что B4: B10 - это, >= 0а B4: B10 - целые числа.
Выберите Simplex LP в качестве метода решения и нажмите Решить. Через несколько мгновений Solver представит одно оптимальное решение.
Solver находит способ покрыть штат парка развлечений, используя 30 сотрудников вместо 38. Экономия в неделю составляет 544 доллара - или более 7000 долларов в течение лета.
Обратите внимание на пять звездочек под «Необходимыми сотрудниками» на рисунке выше. График, предложенный Solver, точно соответствует вашим потребностям на пять из семи дней. Побочным эффектом является то, что в среду и четверг у вас будет больше сотрудников, чем вам действительно нужно.
Я могу понять, как Solver придумал это решение. Вам нужно много людей в субботу, воскресенье и пятницу. Один из способов привлечь людей в этот день - дать им выходной в понедельник и вторник. Вот почему Solver предоставил 18 человек понедельник и вторник выходными.
Но то, что Solver предложил оптимальное решение, не означает, что других столь же оптимальных решений не существует.
Когда я только гадал о кадрах, у меня действительно не было хорошей стратегии.
Теперь, когда Solver дал мне одно из оптимальных решений, я могу надеть логическую шляпу. Наличие 28 сотрудников студенческого возраста в среду и четверг, когда вам нужно всего 15 или 18 сотрудников, приведет к неприятностям. Делать будет мало. Кроме того, при точном подсчете количества человек в течение пяти дней вам придется вызывать кого-то на сверхурочную работу, если кто-то другой заболеет.
Я верю Solver, что мне нужно 30 человек, чтобы сделать эту работу. Но держу пари, что смогу перестроить этих людей, чтобы выровнять график и обеспечить небольшой буфер в другие дни.
Например, если дать кому-то выходной в среду и четверг, это также гарантирует, что человек будет на работе в пятницу, субботу и воскресенье. Итак, я вручную перемещаю некоторых рабочих из строки понедельник, вторник в строку среды, четверга. Я продолжаю вручную вставлять различные комбинации и придумываю решение, показанное ниже, которое имеет те же расходы на заработную плату, что и Solver, но лучшие нематериальные активы. Ситуация с избытком персонала теперь существует на четыре дня вместо двух. Это означает, что вы можете справиться с отсутствием с понедельника по четверг, не вызывая кого-либо на выходных.
Плохо ли, что я смог придумать лучшее решение, чем Solver? Нет. Дело в том, что я не смог бы добраться до этого решения без использования Solver. Как только Solver предоставил мне модель, минимизирующую затраты, я смог использовать логику нематериальных активов, чтобы сохранить тот же фонд заработной платы.
Если вам нужно решить проблемы, которые более сложны, чем может решить Solver, обратите внимание на премиальные решатели Excel, доступные от Frontline Systems.
Спасибо Дэну Филстра и Frontline Systems за этот пример. Уолтер Мур иллюстрировал американские горки XL.
