Solver - це безкоштовна надбудова з часів Lotus 1-2-3
Excel не була першою програмою електронних таблиць. Lotus 1-2-3 не була першою програмою електронних таблиць. Першою програмою електронних таблиць був VisiCalc у 1979 р. Розроблений Деном Брикліном та Бобом Франкстоном VisiCalc опублікував Dan Fylstra. Сьогодні Ден керує Frontline Systems. Його компанія написала вирішувач, що використовується в Excel. Він також розробив цілий набір аналітичного програмного забезпечення, яке працює з Excel.
Якщо у вас є Excel, у вас є Solver. Можливо, його не ввімкнено, але у вас він є. Щоб увімкнути Solver в Excel, натисніть alt = "" + T, а потім I. Додайте галочку поруч із Solver.
Щоб успішно використовувати Solver, вам потрібно створити модель аркуша, яка має три елементи:
- Повинна бути одна клітина цілі. Це комірка, яку ви або хочете мінімізувати, розгорнути або встановити певне значення.
- Вхідних комірок може бути багато. Це одне фундаментальне покращення порівняно з Goal Seek, яке може мати справу лише з однією вхідною коміркою.
- Можуть бути обмеження.
Ваша мета - створити вимоги до розкладу для парку розваг. Кожен працівник працюватиме п’ять днів поспіль, а потім матиме два вихідних. Існує сім різних способів запланувати когось на п’ять днів поспіль та два вихідних. Вони показані як текст у форматі A4: A10. Сині клітинки в B4: B10 - це вхідні комірки. Тут ви вказуєте, скільки людей у вас працює за кожним графіком.
Осередок цілі - це загальна заробітна плата за тиждень, показана у В17. Це пряма математика: Загальна кількість людей від В11 разів більше 68 зарплат на людину на день. Ви попросите Solver знайти спосіб мінімізації щотижневого фонду оплати праці.
Червоне поле відображає значення, які не змінюватимуться. Саме стільки людей вам потрібно працювати в парку кожного дня тижня. У напружені вихідні дні вам потрібно щонайменше 30 людей, але в понеділок та вівторок - лише 12. Помаранчеві клітини використовують SUMPRODUCT, щоб розрахувати, скільки людей буде заплановано щодня, виходячи із введених даних у синіх клітинках.
Значки в рядку 15 вказують, чи потрібно вам більше людей, чи менше людей, чи у вас точно потрібна кількість людей.
По-перше, я спробував вирішити це без Solver. Я ходив із 4 працівниками щодня. Це було чудово, але в неділю мені не вистачало людей. Отже, я почав збільшувати графіки, які дали б мені більше недільних працівників. У підсумку я отримав щось, що працює: 38 співробітників і $ 2584 щотижневої оплати праці.
Клацніть піктограму Розв'язувач на вкладці Дані. Скажіть Solver, що ви намагаєтесь встановити мінімальний фонд оплати праці в B17. Вхідні комірки - B4: B10.
Обмеження поділяються на очевидні та не дуже очевидні категорії.
Перше очевидне обмеження полягає в тому, що D12: J12 має бути> = D14: J14.
Але якщо ви спробували запустити Solver зараз, ви отримаєте химерні результати, коли у вас буде незначна кількість людей і, можливо, від’ємна кількість людей, які працюють за певним графіком.
Хоча вам здається очевидним, що ви не можете найняти 0,39 людей, вам потрібно додати обмеження, щоб повідомити Solver, що B4: B10>> 0, а B4: B10 - цілі числа.
Виберіть Simplex LP як метод вирішення і виберіть Solve. За кілька хвилин 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, ви можете його налаштувати.
Стенограма відео
Дізнайтеся Excel з подкасту, серія 2036 - Вступ до розв’язувача!
Добре, я підкастую всю цю книгу, клацніть “i” у верхньому правому куті, щоб перейти до списку відтворення, де ви зможете відтворити всі відео!
Ласкаво просимо до мережі, я Білл Джелен. Нещодавно ми говорили про якийсь аналіз What-If, як, наприклад, Goal Seek, з однією вхідною коміркою, яку ви змінюєте, але що, якщо у вас є щось більш складне? Існує чудовий інструмент під назвою Solver, Solver існує вже давно, я гарантую, якщо у вас є Excel і ви працюєте в Windows, у вас є Solver, він, напевно, не ввімкнений. Отже, щоб увімкнути його, вам потрібно перейти до alt = "" T, а потім я, тобто T для Тома, я для морозива, і встановити цей прапорець для Solver, натиснути OK, і через пару секунд у вас буде вкладку Розв'язувач тут з правого боку. Гаразд, і ми збираємося встановити тут модель, яку може вирішити вирішувач, у нас є парк розваг, ми намагаємось визначити, скільки співробітників запланувати. Кожен працює п’ять днів поспіль, тожs насправді сім можливих розкладів, де ви виїжджаєте, неділя понеділок, понеділок вівторок, вівторок середа. Ми повинні з’ясувати, скільки співробітників слід розмістити у кожному з цих графіків.
Отже, тут є лише невелика математична математика, яка робить ПІДГОТОВКИ, кількість працівників у неділю, щоб з’ясувати, скільки людей було в неділю, понеділок, вівторок, середу. І те, що ми дізналися завдяки роботі цього парку розваг, - це те, що нам потрібно багато людей у суботу та неділю. 30 людей у суботу та неділю, протягом тижня понеділок, вівторок, дещо повільно, 12 співробітників зможуть це зробити. Гаразд, просто приїхавши сюди і просто закрутившись, ви знаєте, намагаючись зрозуміти правильні цифри, ви можете продовжувати підключати речі, але, маючи сім різних варіантів, це займе вічно, гаразд.
Зараз у Solver - це те, що ми маємо серію вхідних комірок, і у безкоштовній версії Solver, я думаю, ви можете мати, це сотня? Я не знаю, є якийсь номер, і якщо вам доведеться виходити за межі цього, є Premium Solver, який ви можете отримати від Frontline Systems. Добре, отже, у нас є кілька вхідних комірок, у нас є деякі комірки обмеження, і тоді вам доведеться все це звести до остаточного числа. Тож у моєму випадку я намагаюся мінімізувати фонд заробітної плати на тиждень, так що зелене число - це те, що я хочу спробувати оптимізувати, добре, от і ось що ми будемо робити!
Вирішувач, ось цільова комірка, це зелена клітина, і я хочу встановити це мінімальне значення, з’ясувати штат, який приносить мені мінімальне значення, змінивши ці сині клітинки. А далі ось обмеження, добре, тому перше обмеження полягає в тому, що загальний графік повинен бути> = червоний розділ, і ми можемо зробити все це як одне обмеження. Поспостерігайте, як це круто, всі ці клітинки повинні бути> = ці відповідні клітинки тут, приголомшливо, клацніть Додати, добре, але тоді є й інші речі, про які ви б не думали. Наприклад, Solver в цей момент може вирішити, що найкраще мати 17 людей за цим графіком, 43 людей за графіком та -7 людей за цим графіком. Гаразд, тому ми повинні сказати Solver, що ці вхідні комірки повинні бути цілими числами, натисніть Додати. А також, ми не можемо мати, щоб хтось не з'явився,і вони повернуть нам свою зарплату, так? Отже, ми скажемо, що ці клітинки мають бути> = 0, натисніть Додати, ми повертаємось зараз, у нас є три обмеження.
Існує три різні способи вирішення, і цей - за лінійною математикою, тому ми можемо просто піти на Simplex LP. Якщо цей не працює, то неодмінно спробуйте інші два, у мене були випадки, коли Сімплекс каже, що не може знайти рішення, і один із двох інших працює. Frontline Systems має чудові навчальні посібники з Solver, я просто намагаюсь вас сьогодні пройти через ваш перший, я не проголошую бути експертом Solver. Одного разу у мене був вирішувач, який не спрацював, і я надіслав записку в Frontline Systems, і нічого собі, я отримав цей чудовий 5-сторінковий лист назад, від самого Дана Філстри, президента Solver! І все почалося: «Дорогий Білл, приємно почути тебе!» А потім продовжив 4,9 сторінки, це все було майже повністю над моєю головою, добре. Але ви знаєте, я знаю достатньо про Solver, щоб пройти через це, добре,тож ми збираємось натиснути тут на «Розв’язати», він знайшов рішення, «Усі обмеження та умови оптимальності задоволені». Я збираюся це зберегти, я можу створювати деякі звіти, не потрібно це робити зараз. О, я можу насправді зберегти сценарій, я вчора знущався із сценаріїв, можливо, Solver зміг би створити для мене новий сценарій, тож ми натиснемо OK.
Гаразд, і, звичайно, це заощадило нам гроші, ми писали 2584 раніше, а зараз це дійшло до 2040. Отже, нам потрібно багато людей у понеділок та вівторок, добре, деякі люди, 2 людини у середу четвер, і потім п’ятниця субота. Ну, це чудово, я ніколи не міг би просто випадково придумати цей набір відповідей, добре, але чи означає це, що це найкраща відповідь? Ну, це означає, що це мінімальний фонд заробітної плати, але я, мабуть, можу придумати інший набір відповідей, який все одно міг би мати цей мінімальний фонд оплати праці. Є й інші способи зробити це, це може бути трохи кращий графік. Як, наприклад, зараз у нас в середу та четвер є 28 людей, коли нам потрібно лише 15 та 18, це багато людей. Подумайте, хто працює в парках розваг, це діти коледжу вдома на перерву,це буде проблемою, якщо у нас буде стільки зайвих людей. А у понеділок у вівторок ми навіть мертві, саме там, де хочемо бути. Отже, це означає, що якщо хтось, кого я збираюся зателефонувати хворому, тепер нам доведеться, знаєте, зателефонувати комусь і заплатити йому півтора часу, тому що вони вже відпрацювали ще п’ять днів.
Гаразд, так просто з невеликою простою математикою тут, якби я взяв 8 з понеділка у вівторок, і зробив це 10, і взяв ці 8 і додав їх до середи четвер, добре. Зараз у мене є рішення Solver з точно такою ж відповіддю, 2040 рік, вони отримали потрібну кількість людей. Я просто збалансую графік, і тепер у нас є 8 додаткових, 8 додаткових, 3 додаткові та 2 додаткові, і саме те, що нам потрібно на вихідних, це, ви знаєте, повний сценарій персоналу. Для мене це трохи краще, ніж те, що придумав Solver, чи означає це, що Solver не вдалося? Ні, абсолютно ні, тому що я ніколи б не наблизився до цього без Солвера. Одного разу Солвер дав мені відповідь, так, я зміг трохи його налаштувати і потрапити туди, добре. Порада №37, “40 найкращих підказок Excel усіх часів”, що наближається до кінця перших 40, чудовий маленький вступ до Solver.Посібник з усіх подкастів цієї серії знаходиться тут, “MrExcel XL - 40 найкращих підказок Excel усіх часів”, ви можете отримати електронну книгу всього за 10 доларів, друкувати книгу за 25 доларів, клацніть на “i” вгорі -правий кут руки!
Добре, резюме: Вирішувач, якщо ви перебуваєте у версіях Windows для Excel, Lotus 1-2-3, він є, його створив засновник Visicorp Ден Філстра. Це безкоштовна версія важких рішень, ось посилання для перевірки важких режимів, яке буде вказано в коментарях YouTube. Цілком ймовірно, що вони просто не встановлені, alt = "" TI, поставте галочку Solver, подивіться праворуч на вкладці Data, щоб знайти Solver. Добре, ви повинні мати об’єктивну комірку, яку ви намагаєтеся звести до мінімуму, розгорнути або встановити значення, один діапазон вхідних комірок. Вкажіть обмеження, включаючи те, чого не можна було б очікувати, як-от я повинен був сказати “Немає напівлюдів” та “Нема негативних людей”. Вирішувач знайде оптимальне рішення, але можуть бути й інші, які пов’язані, і ви можете налаштувати його, щоб отримати краще рішення.
Гаразд, ось у вас все, я хочу подякувати вам за заїзд, ми побачимось наступного разу для чергової трансляції від!
Завантажити файл
Завантажте зразок файлу тут: Podcast2036.xlsx
