Solver был бесплатной надстройкой со времен Lotus 1-2-3.
Excel не была первой программой для работы с электронными таблицами. Lotus 1-2-3 не была первой программой для работы с электронными таблицами. Первой программой для работы с электронными таблицами была VisiCalc в 1979 году. VisiCalc, разработанная Дэном Бриклином и Бобом Франкстоном, была опубликована Дэном Филстрой. Сегодня Дэн руководит Frontline Systems. Его компания написала Solver, используемый в Excel. Также был разработан целый набор аналитического программного обеспечения, работающего с 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: http://mrx.cl/solver77.
Спасибо Дэну Филстра и Frontline Systems за этот пример. Уолтер Мур иллюстрировал американские горки XL.
Смотреть видео
- Solver был бесплатной надстройкой со времен Lotus 1-2-3.
- Solver - продукт основателя Visicorp Дэна Филстры.
- Решатель в вашем Excel - это уменьшенная версия мощных решателей
- Узнайте больше о профессиональных решателях: http://mrx.cl/solver77
- Чтобы установить Solver, введите alt = "" + T, затем I. Проверьте Solver.
- Решатель находится в правой части вкладки «Данные».
- Вы хотите иметь объективную ячейку, которую вы пытаетесь минимизировать или максимизировать.
- Вы можете указать несколько ячеек ввода.
- Вы можете указать ограничения, включая те, которые вы не ожидали:
- Никаких полулюдей: используйте INT для целого числа
- Solver найдет оптимальное решение, но могут быть и другие проблемы
- Как только вы получите решение Solver, вы сможете настроить его.
Стенограмма видео
Изучите Excel из подкаста, выпуск 2036 - Введение в Solver!
Хорошо, я подкастирую всю эту книгу, нажмите «i» в правом верхнем углу, чтобы перейти к списку воспроизведения, где вы можете воспроизвести все видео!
С возвращением в netcast, я Билл Джелен. Мы недавно говорили о каком-то анализе «что-если», например, о поиске целей, с одной входной ячейкой, которую вы меняете, но что, если у вас есть что-то более сложное? Есть отличный инструмент под названием Solver, Solver существует уже давно, я гарантирую, что если у вас есть Excel и вы работаете в Windows, у вас есть Solver, просто он, вероятно, не включен. Итак, чтобы включить его, вам нужно перейти к alt = "" T, а затем I, поэтому T для Тома, I для мороженого, и установите этот флажок для Solver, нажмите OK, и через пару секунд у вас будет Вкладка Solver здесь с правой стороны. Хорошо, и мы собираемся создать здесь модель, которую решатель сможет решить, у нас есть парк развлечений, мы пытаемся выяснить, сколько сотрудников запланировать. Все работают пять дней подряд, так чтоНа самом деле семь возможных графиков, когда вы не работаете: воскресенье понедельник, понедельник вторник, вторник среда. Мы должны выяснить, сколько сотрудников включить в каждый из этих графиков.
И здесь просто простая математика, выполнение некоторых СУММПРОДУКТОВ, количество сотрудников, умноженное на воскресенье, чтобы вычислить, сколько людей было там в воскресенье, понедельник, вторник, среду. И что мы узнали, управляя этим парком развлечений, нам нужно много людей в субботу и воскресенье. 30 человек в субботу и воскресенье, в будние дни понедельник, вторник, вроде медленно, 12 сотрудников смогут это сделать. Хорошо, просто подойдя сюда и просто возясь, понимаете, пытаясь вычислить правильные числа, вы можете просто продолжать подключать вещи, но с семью различными вариантами это займет вечность, хорошо.
Теперь в Solver у нас есть ряд ячеек ввода, а в бесплатной версии Solver, я думаю, у вас может быть их сотня? Я не знаю, есть какое-то число, и если вам нужно выйти за рамки этого, есть Premium Solver, который вы можете получить от Frontline Systems. Хорошо, у нас есть несколько входных ячеек, у нас есть несколько ячеек ограничений, а затем вам нужно привести все это к окончательному числу. Итак, в моем случае я пытаюсь минимизировать заработную плату в неделю, поэтому я хочу попробовать и оптимизировать это зеленое число, хорошо, вот что мы собираемся делать!
Решатель, вот целевая ячейка, это зеленая ячейка, и я хочу установить ее на минимальное значение, выяснить укомплектование персоналом, которое дает мне минимальное значение, путем изменения этих синих ячеек. А вот и ограничения, хорошо, поэтому первое ограничение состоит в том, что общая сумма расписания должна быть> = красной секции, и мы можем сделать все это как одно ограничение. Посмотрите, как это круто, все эти ячейки должны быть> = эти соответствующие ячейки здесь, здорово, нажмите «Добавить», хорошо, но есть другие вещи, о которых вы бы даже не подумали. Например, Solver на этом этапе может решить, что лучше всего иметь 17 человек в этом расписании, 43 человека в расписании и -7 человек в этом расписании. Хорошо, поэтому мы должны сообщить Solver, что эти входные ячейки должны быть целыми числами, нажмите «Добавить». А еще мы не можем допустить, чтобы кто-то не появился,и они вернут нам свою зарплату, да? Итак, мы собираемся сказать, что эти ячейки должны быть> = 0, нажмите «Добавить», мы вернемся назад, у нас есть три ограничения.
Есть три разных способа решения, и этот основан на линейной математике, поэтому мы можем просто перейти на Simplex LP. Если этот не работает, обязательно попробуйте два других, у меня были случаи, когда Simplex сообщал, что не может найти решение, и один из двух других работает. У Frontline Systems есть отличные руководства по Solver, я просто пытаюсь познакомить вас с вашим первым сегодня здесь, я не заявляю, что я эксперт по Solver. Однажды у меня появился Решатель, который не работал, и я отправил записку в Frontline Systems, и вау, я получил обратно это замечательное 5-страничное письмо, верно, от самого Дэна Филстры, президента Solver! И все началось: «Дорогой Билл, приятно тебя слышать!» А потом перешел на 4,9 страницы, и все это было в значительной степени выше моей головы, хорошо. Но ты знаешь, я достаточно знаю о Солвере, чтобы пройти через это, хорошо,поэтому мы собираемся нажать здесь на Решить, решение нашло: «Все ограничения и условия оптимальности выполнены». Я собираюсь сохранить это, я могу создать несколько отчетов, но сейчас этого делать не нужно. О, я действительно могу сохранить сценарий, вчера я смеялся над сценариями, может быть, Solver сможет создать для меня новый сценарий, поэтому мы нажмем ОК.
Хорошо, и, конечно же, это сэкономило нам деньги, мы написали 2584 раньше, а теперь мы получили 2040. Итак, нам нужно много людей в понедельник и вторник, хорошо, некоторые люди, 2 человека в среду, четверг, и затем пятница суббота. Что ж, это потрясающе, я бы никогда не придумал случайно такой набор ответов, хорошо, но означает ли это, что это лучший ответ? Что ж, это означает, что это минимальная заработная плата, но я, вероятно, смогу придумать другой набор ответов, которые по-прежнему будут иметь эту минимальную заработную плату. Есть другие способы сделать это, это может быть немного лучший график. Например, сейчас у нас 28 человек в среду и четверг, а когда нам нужно только 15 и 18, это много людей. Подумайте, кто работает в парках развлечений, это ребята из колледжа на каникулах дома,это будет проблемой, если у нас будет столько лишних людей. А в понедельник вторник мы мертвы ровно там, где хотим быть. Это означает, что если кого-то я назову больным, теперь нам придется, вы знаете, вызвать кого-нибудь и заплатить ему полтора времени, потому что они уже отработали пять других дней.
Хорошо, просто с помощью небольшой математики, если бы я убрал 8 из понедельника вторника и сделал бы 10, возьму эти 8 и добавлю их к среде, четверг, хорошо. Теперь у меня есть решение Solver с точно таким же ответом, 2040, они получили нужное количество людей. Я просто балансирую график, и теперь у нас есть 8 дополнительных, 8 дополнительных, 3 дополнительных и 2 дополнительных, и именно то, что нам нужно на выходных, что, как вы знаете, представляет собой сценарий полного штата. Для меня это немного лучше, чем то, что придумал Solver, означает ли это, что решатель отказал? Нет, абсолютно нет, потому что я бы никогда не подошел так близко без Solver. Как только Solver дал мне ответ, да, я смог его немного подправить и добраться до цели, хорошо. Совет № 37, «40 величайших советов по Excel всех времен», приближающийся к концу первых 40, отличное небольшое введение в Solver.Руководство по всем подкастам из этой серии находится здесь: «MrExcel XL - 40 величайших советов по работе с Excel за все время», вы можете получить электронную книгу всего за 10 долларов, распечатать книгу за 25 долларов, нажмите «i» вверху -правый угол!
Хорошо, резюмируйте: Solver, если вы используете версии Excel для Windows, Lotus 1-2-3, он есть, он создан основателем Visicorp Дэном Филстра. Это бесплатная версия мощных решателей, вот ссылка, по которой можно ознакомиться с мощными решателями, которые будут в комментариях на YouTube. Вероятно, они просто не установлены, alt = "" TI, установите флажок Решатель, посмотрите в правой части вкладки Данные, чтобы найти Решатель. Хорошо, у вас должна быть целевая ячейка, которую вы пытаетесь минимизировать или максимизировать или установить на значение, один диапазон входных ячеек. Укажите ограничения, в том числе то, чего не ожидаете, например, я должен был сказать «Нет полулюдей» и «Нет отрицательных людей». Solver найдет оптимальное решение, но могут быть и другие, которые связаны, и вы можете настроить его, чтобы получить лучшее решение.
Хорошо, вот и все, я хочу поблагодарить вас за то, что заглянули, увидимся в следующий раз на еще одной сетевой трансляции от!
Скачать файл
Загрузите образец файла здесь: Podcast2036.xlsx
