Excel не була першою програмою електронних таблиць. Lotus 1-2-3 не була першою програмою електронних таблиць. Першою програмою електронних таблиць був VisiCalc у 1979 р. Розроблений Деном Брикліном та Бобом Франкстоном VisiCalc опублікував Dan Fylstra. Сьогодні Ден керує Frontline Systems. Його компанія написала вирішувач, що використовується в Excel. Frontline Systems також розробив цілий набір аналітичного програмного забезпечення, яке працює з 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 в якості методу розв’язання і натисніть «Вирішити». За кілька хвилин 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 американські гірки.
