استفاده از ابزار Solver برای برنامه ریزی زمان‌بندی نیروی کار

04 اردیبهشت 1401

دقیقه

بسیاری از سازمان‌ها (مثل بانک‌ها، رستوران‌ها و شرکت‌های خدمات پستی) از نیازمندی‌های کاری خود در زمان‌های مختلف از روز آگاه هستند و نیازمند روشی هستند که به طرزی مؤثر نیروی کاری خود را برنامه‌ریزی کنند. با استفاده از مایکروسافت اکسل 2019  و ابزار Solver می‌توان به‌آسانی مشکل برنامه‌ریزی زمان بندی نیروی کاری را حل کرد.

آخرین به‌روزرسانی: 27 دی 1401

در سری مقاله های آموزش اکسل، در فصل گذشته به بکار گیری ابزار Solver برای مشخص‌کردن ترکیب بهینه محصولات پرداختیم، در این مقاله “استفاده از ابزار Solver برای برنامه ریزی زمان‌بندی نیروی کار” را مورد بررسی قرار می‌دهیم.

بسیاری از سازمان‌ها (مثل بانک‌ها، رستوران‌ها و شرکت‌های خدمات پستی) از نیازمندی‌های کاری خود در زمان‌های مختلف از روز آگاه هستند و نیازمند روشی هستند که به طرزی مؤثر نیروی کاری خود را برنامه‌ریزی کنند. با استفاده از مایکروسافت اکسل 2019  و ابزار Solver می‌توان به‌آسانی مشکل برنامه‌ریزی زمان بندی نیروی کاری را حل کرد.

زمان‌بندی کارآمد نیروی کار برای پاسخگویی به نیازمندی‌های موجود با ابزار Solver

بانک 24 در هفت روز هفته چک‌هایی را مورد پردازش قرار می‌دهد. تعداد کارمندانی که این بانک هر روز برای پردازش چک‌ها نیاز دارد در ردیف 14 از فایل Bank24.xlsx در تصویر 1-31 نشان‌داده ‌شده است. مثلاً برای روز سه‌شنبه 13 کارمند و برای روز چهارشنبه 14 کارمند نیازمندیم و به همین روال. همه کارمندان بانک پنج‌روز پشت‌سرهم کار می‌کنند. حداقل تعداد کارمندانی که بانک 24 می‌تواند داشته باشد تا درعین‌حال بتواند نیازمندی‌های کاری خود را برآورده سازد چقدر است؟

تصویر 1-31 داده‌هایی که برای حل مشکل برنامه‌ریزی نیروی کاری بانک از آنها استفاده می‌کنیم.

تصویر 1-31 داده‌هایی که برای حل مشکل برنامه‌ریزی نیروی کاری بانک از آنها استفاده می‌کنیم.

کار را با شناسایی سلول هدف، سلول‌های متغیر و محدودیت‌ها جهت مدل Solver که در ادامه توضیح داده شده‌اند شروع می‌کنیم:

  • سلول هدف تعداد کل کارمندان را کاهش بدهید.
  • سلول‌های متغیر تعداد کارمندانی هستند که هر روز از هفته (در اولین روز از پنج‌روز متوالی هفته)کار را شروع می‌کنند. هر سلول متغیر می‌بایست یک عدد صحیح غیرمنفی باشد.
  • محدودیت‌های هر روز از روزهای هفته، تعداد کارمندانی که کار می‌کنند می‌بایست بزرگ‌تر یا برابر تعداد کارمندان مورد نیاز باشد (تعداد کارمندان شاغل) > =(کارمندان موردنیاز)

برای ایجاد مدلی جهت این مسئله، نیازمند آن هستیم که تعداد کارمندان شاغل در هر روز را دنبال کنیم. کار را با واردکردن مقادیر آزمایشی تعداد کارمندانی که شیفت پنج‌روزه خود را در هر روز آغاز می‌کنند در محدوده سلولی A5:A11 شروع می‌کنیم. مثلاً در سلول A5 عدد یک را وارد می‌کنیم که مشخص‌کننده آن است که یک کارمند در روز دوشنبه شروع به کارکرده و از دوشنبه تا روز جمعه کار می‌کند. تعداد کارمندان موردنیاز هر روز را در محدوده C14:I14 درج کرده‌ایم.

برای دنبال‌کردن تعداد کارمندانی که هر روزکار می‌کنند عدد یک یا صفر را در سلول محدوده C5:I11 وارد کرده‌ایم. مقدار یک مشخص می‌کند که کارمندانی که کار را در روز تخصیص‌داده‌شده در ردیف سلول شروع کرده‌اند در روز (روزهای) مرتبط با ستون سلول کار می‌کنند. مثلاً عدد یک در سلول G5 مشخص می‌کند که کارمندانی که کار را در روز دوشنبه شروع کرده‌اند در روز جمعه کار می‌کنند؛ عدد صفر در سلول H5 مشخص می‌کند که کارمندانی که کار را در روز دوشنبه شروع کرده‌اند در روز یکشنبه کار نخواهند کرد.

با کپی‌کردن فرمول =SUMPRODUCT($A$5:$A$11,C5:C11) از سلول C12 به محدوده D12:I12 تعداد کارمندانی را که در هر روزی مشغول به کار هستند را محاسبه می‌کنیم. مثلاً این فرمول در سلول C12 به شکل =A5+A8+A9+A10+A11 ارزیابی می‌شود که برابر است با (تعداد افراد شروع‌کننده در روز دوشنبه)+(تعداد افراد شروع‌کننده در روز پنج‌شنبه)+(تعداد افراد شروع‌کننده در روز جمعه)+(تعداد افراد شروع‌کننده در روز شنبه)+(تعداد افراد شروع‌کننده در روز یکشنبه). جمع فوق دقیقاً معادل تعداد افرادی است که در روز دوشنبه کار می‌کنند.

پس از محاسبه تعداد کل کارمندان در سلول A3 با فرمول =SUM(A5:A11)، می‌توانیم مدل Solver نشان‌داده‌شده در تصویر 2-31 را وارد کنیم. (برای بازکردن کادر محاوره‌ای Solver Parameters می‌توانید در گروه گزینه‌های Analysis در تب Data کلیک کنید)

تصویر 2-31 کادر محاوره‌ای Solver Parameters که برای حل مسئله نیروی کاری تکمیل شده است.

تصویر 2-31 کادر محاوره‌ای Solver Parameters که برای حل مسئله نیروی کاری تکمیل شده است.

می‌خواهیم در سلول هدف (A3) تعداد کل کارمندان را کم کنیم. محدودیت C12:I12>=C14:I14 به ما اطمینان می‌دهد که تعداد کارمندان شاغل در هر روز حداقل به‌اندازه تعداد موردنیاز همان روز است. محدودیت A5:A11=integer به ما اطمینان می‌دهد که تعداد کارمندانی که کار خود را در هر روز شروع می‌کنند عدد صحیحی خواهد بود.

برای اضافه‌کردن این محدودیت بر روی دکمه Add در کادر محاوره‌ای Solver parameters کلیک کرده‌ایم و کادر محاوره‌ای Add Constraint را همان‌طور که در تصویر 3-31 نشان‌داده‌شده پرکرده‌ایم. در بخش Cell Reference محدوده A5:A11 را انتخاب کرده و در فهرست میانی int را انتخاب کرده‌ایم که محدودیت پیش‌فرض را به عدد صحیح تغییر می‌دهد.

تصویر 3-31 اضافه‌کردن محدودیت برای تعریف آن به‌عنوان عدد صحیح کارگرانی که کارشان را در هر روز شروع می‌کنند.

تصویر 3-31 اضافه‌کردن محدودیت برای تعریف آن به‌عنوان عدد صحیح کارگرانی که کارشان را در هر روز شروع می‌کنند.

توجه کنید که این مدل، مدلی خطی است چرا که سلول هدف با جمع زدن سلول‌های متغیر ایجاد و محدودیت نیز با مقایسه نتیجه کسب شده توسط جمع محصولات هریک از سلول‌های متغیر ضرب در یک ثابت (یک و یا صفر) با تعداد کارمندان موردنیاز ایجاد شده است؛ بنابراین موتور Simplex LP را انتخاب می‌کنیم.

ازآنجایی‌که نمی‌توان کار را با عدد منفی کارمندان در روز شروع کرد، گزینه Make Unconstrained Variable None-Negative را انتخاب می‌کنیم. پس از کلیک کردن بر دکمه Solve راه‌حل بهینه‌ای که پیش‌ازاین در تصویر 1-31 نشان‌داده‌شده بود را به دست می‌آوریم.

به تعداد کل 20 نفر کارمند نیازمندیم. یک کارمند کار را در روز دوشنبه شروع می‌کند، سه نفر در روز سه‌شنبه، چهار نفر در روز پنج‌شنبه، یک نفر در روز جمعه، دو نفر در روز شنبه، و نه نفر در روز یکشنبه کار را شروع می‌کنند. توجه کنید که این مدل دارای چندین راه‌حل بهینه است که از 20 کارمند بهره می‌برند. اگر دوباره Solver را بکار برید، ممکن است خیلی راحت یکی از این راه‌حل‌های بهینه جایگزین را پیدا کنید.

 

مسئله‌های این فصل

فرض کنید بانک 24 دارای 22 کارمند است و هدف آن است که کار آنها را چنان برنامه‌ریزی کند که بتوانند بیشترین روزهای آخر هفته را تعطیل باشند. چگونه می‌توان کار این کارمندها را به شکل موردنظر برنامه‌ریزی کرد؟

فرض کنید کارمندان بانک 24 در پنج‌روز اول کاری برای هر روز 150 دلار می‌گیرند و می‌توانند در یک روز 350 دلار اضافه‌کاری کنند. این بانک چگونه می‌تواند کار کارمندانش را برنامه‌ریزی کند.

تعداد اپراتورهای رزرو تلفنی که یک شرکت هواپیمایی در هر زمانی از روز نیاز دارد در جدول زیر نشان‌داده‌شده است. هر اپراتور در یکی از این شیفت‌های شش‌ساعته کار می‌کند: شیفت نیمه‌شب تا 6:00، شیفت 6:00 صبح تا ظهر، شیفت ظهر تا 6:00 بعدازظهر و شیفت 6:00 بعدازظهر تا نیمه‌شب. حداقل تعداد اپراتورهای موردنیاز چقدر است؟

در تصویر 4-31 تعداد افراد در گروه‌های آماری مختلفی که برنامه‌های مختلف تلویزیونی را تماشا کرده‌اند و هزینه یک تبلیغ 30 ثانیه‌ای در هر نمایش را (به هزار دلار) مشاهده می‌کنید مثلاً می‌بینید که برای یک تبلیغ 30 ثانیه‌ای در میان سریال Friends می‌بایست 160000 دلار هزینه کرد.

این برنامه توسط 6 میلیون نفر مرد در رده سنی میان 18 و35.3 میلیون مرد در رده سنی بین 36 تا 55، یک میلیون مرد در رده سنی بیش از 55، 9 میلیون زن در رده سنی بین 18 و 35.4 میلیون زن در رده سنی بین 36 و 55 و دو میلیون زن در رده سنی بالاتر از 55 تماشا شده است. این داده‌ها همچنین شامل تعداد افراد در هر گروه (به میلیون) است که تبلیغ کننده می خواهد مخاطب تبلیغ ها باشند. مثلاً تبلیغ‌کننده دوست دارد حداقل 60 میلیون از مردان رده سنی 18 تا 35 تبلیغش را تماشا کنند. ارزان‌ترین راه برای دستیابی به این اهداف کدام است؟

بانک Houston Credit Union در روزهای هفته از ساعت 9 صبح تا پنج بعدازظهر باز می‌باشد. به تحویلداران تمام وقتی که تمام روزکار می‌کنند (بدون صرف نهار) روزانه 200 دلار پرداخت می‌شود. به تحویلداران نیمه‌وقت که از ساعت 11 تا 1 بعدازظهر کار می‌کنند ممکن است روزانه 80 دلار پرداخت شود. تعداد تحویلدارانی که هر ساعت نیازمند است در تصویر 5-31 نشان‌داده‌شده است.یک مدل Solver خطی ایجاد کنید که مشخص کند این بانک چگونه می‌تواند هزینه‌های کار روزانه را به حداقل برساند.

تصویر 5-31 داده‌های مسئله شماره 6

تصویر 5-31 داده‌های مسئله شماره 6

 

فایل ها جانبی:
دانلود فایل نمونه
اشتراک گذاری در شبکه های اجتماعی

مایکروسافت اکسل (Excel)

loader

لطفا شکبیا باشید...