جریمه و Solver تکاملی
17 خرداد 1401
دقیقه
هر چیزی در زندگی جنبه مثبت و منفی دارد و ابزار Solver تکاملی نیز از این مورد مستثنی نیست. جنبه مثبت Solver تکاملی آن است که به توابع ناهموار بهخوبی رسیدگی میکند. جنبه منفی آن این است که به محدودیتهایی که توابع غیرخطی سلولهای متغیر نیستند به خوبی رسیدگی نمی کند. سلول هدف را جریمه کنید تا نشان دهید نقض یک محدودیت مسئله نادرستی است.
آخرین بهروزرسانی: 27 دی 1401
در سری مقاله های آموزش اکسل، در فصل گذشته به موقعیت انبار کالا و موتورهای چند آغازه GRG و موتورهای تکاملی ابزار Solver پرداختیم، در این مقاله جریمه و Solver تکاملی را مورد بررسی قرار میدهیم.
نکات کلیدی در استفاده موفق از ابزار Solver تکاملی کداماند؟
پیشازاین گفتیم که ابزار Solver تکاملی میبایست برای یافتن راهحل مسائل بهینهسازی که در آنها سلول هدف و یا سلولهای متغیردرگیر توابع ناهمواری چون IF، ABS، MAX، MIN، COUNTIF، COUNTIFS، SUMIF، SUMIFS، AVERAGEIF و AVERAGEIFS هستندمورد استفاده قرار گیرند.پیش از حل یک مسئله با Solver تکاملی میبایست در کادر محاورهای Solver Parameters تنظیمات زیر را انجام دهید: (بر روی گزینه Solver در گروه Analysis در تب Data کلیک کنید تا این کادر محاورهای باز شود)
- روی گزینه Options کلیک کنید، تب Evolutionary را انتخاب کرده و میزان جهش را به 0.50 افزایش دهید.
- حداکثر زمان فاقد پیشرفت را به 3600 ثانیه تغییر دهید.
- بالاترین و پایین ترین حد معقول را در سلولهای متغیر مشخص کنید.
هر چیزی در زندگی جنبه مثبت و منفی دارد و ابزار Solver تکاملی نیز از این مورد مستثنی نیست. جنبه مثبت Solver تکاملی آن است که به توابع ناهموار بهخوبی رسیدگی میکند. جنبه منفی آن این است که به محدودیتهایی که توابع غیرخطی سلولهای متغیر نیستند بهخوبی رسیدگی نمی کند. برای اینکه بیشتر محدودیتها با Solver تکاملی مورد رسیدگی قرار گیرد میبایست اصطلاحاً سلول هدف را جریمه کنید تا نشان دهید نقض یک محدودیت مسئله نادرستی است. سپس قانون بقای اصلح ما را از شر هرگونه ناقض محدودیت رها می کند. سؤال بعدی این فصل نشان میدهد که چگونه میتوان از جریمهها با Solver تکاملی استفاده کرد.
چگونه میتوان از ابزار Solver تکاملی برای اختصاص دادن 80 کارگر به شغلی از چهار گروه شغلی در شرکت مایکروسافت استفاده کرد؟
میبایست 80 کارمند را به چهار گروه کاری تخصیص دهیم. سرگروه هریک از این گروهها صلاحیت کارمندان را با اعداد 0 تا 10 مشخص کرده است (عدد ده باصلاحیتترین کارمند است) هریک از کارمندان میزان رضایت خود از هر تخصیص وظیفه را نیز رتبهبندی کرده است (بازهم از عدد 0 تا 10) مثلاً به کارمند 1 برای گروه کاری 1 رتبه 9 داده شده و کارمند 1 به گروه کاری شماره 4 رتبه 7 داده است.
کار انجام شده برای این مسئله در فایلی به نام Assign.xlsx قرار گرفته (تصویر 1-37 را ببینید) اینجا میخواهیم حدود 18 تا 22 نفر را به هر گروه کاری تخصیص دهیم. در نظر داشته باشید که صلاحیت کاری دو بار مهمتر از رضایت کارمند است. چگونه میتوان کارمندان را به گروهای کاری تخصیص داد تا رضایت کل را به حداکثر رسانده و اطمینان حاصل کنیم که هر بخش تعداد کارمندان مورد نیازش را به دست آورده؟
در سلولهای A3:A82 میزان تخصیصهای آزمایشی کارمندان در گروههای کاری را واردمی کنیم. بهعنوانمثال برای شروع، تخصیص هر کارمند به گروه کاری شماره 1 راهحل قابل قبولی است. با کپیکردن فرمول =HLOOKUP(A3,Qual,B3+1) از سلول K3 به محدوده K3:K82 میتوانیم صلاحیت هریک از کارمندان برای شغل تخصیصدادهشده را جستجو کنیم. توجه کنید که Qual به محدوده C2:F82 ارجاع دارد، با کپیکردن فرمول=HLOOKUP(A3,Satis,B3+1) از سلول L3 به محدوده L4:L82 میتوانیم میزان رضایت هریک از کارمندان از شغل تخصیصدادهشده را جستجو کنیم. در اینجا Satis نام محدوده G2:J82 است.
برای رسیدگی به این واقعیت که هر بخش نیاز به 18 تا 22 کارمند دارد، میبایست بدانیم چند کارمند به هر گروه تخصیص داده شوند. میتوان این کار را در سلولهای N6:N9 با کپی کردن فرمول=COUNTIF($A$3:$A$82,M6) از سلول N6 به محدوده N7:N9 انجام داد. پس از آن میبایست در سلولهای O6:O9 مشخص کنید که آیا گروه کاری دارای تعداد نادرست کارمندان است یا نه و این کار را با کپیکردن فرمول=IF(OR(N6<18,N6>22),1,0) از سلول O6 به محدوده O7:O9 انجام میدهیم.
حالا نشان میدهیم چگونه سلول هدف را محاسبه کنیم. ابتدا برای محاسبه میزان کل صلاحیت و کیفیت کاری در سلول K1:L1 فرمول =SUM(K3:K82) را از سلول k1 به l1 کپی میکنیم. برای اینکه اطمینان حاصل کنیم که هر گروه کاری دارای 18 تا 22 کارمند است، میبایست سلول هدف را بهاصطلاح جریمه کنید.
در اینجا میزان جریمه 1000 را برای هر گروه کاری که کمتر از 18 یا بیشتر 22 کار کند دارد انتخاب کردهایم. هیچ قانون لازمالاجرایی برای مشخصکردن میزان جریمه مناسب وجود ندارد. در این وضعیت نرخ میانگین 5 است. این نرخ به ما سلول هدفی برابر با 1200=400+400*2 میدهد؛ بنابراین به نظر میرسد احتمالاً قراردادن تعداد نامناسب افراد در هر بخشی برای سلول هدف نفعی بیش از 1000 نخواهد داشت، پس در اینجا قانون بقای اصلح هر راه حلی که در آن گروه کاری کارمندان بیشتر و یا کمتری داشته باشد را از بین خواهد برد. جریمه مناسب نمیبایست زیاد بزرگ (مثلاً 100000) باشد چرا که برخی اوقات باعث میشود Solver مشکل اصلی را نادیده بگیرد. اگر جریمه خیلی کوچک باشد هم Solver به هدفی که در نظر دارید دست پیدا نمیکند.
در سلول O10 تعداد کل بخشهایی را که تعداد کارمندان درستی ندارند را با استفاده از فرمول =SUM(O6:O9) محاسبه کردهایم. بالاخره اکنون آمادهایم که سلول هدف در سلول O12 را با اضافهکردن دوبرابر تعداد کل صلاحیت به تعداد کل رضایت شغلی و کسر جریمه 1000 برای هریک از گروههایی که تعداد کارمندان صحیح ندارند محاسبه کنیم. سلول هدف نهایی O12 با استفاده از فرمول=2*K1+L1-1000*O10 محاسبه میشود.
اکنون میتوانید مدل Solver را برای این مسئله ایجاد کنید. برای انجام این کار نیاز به استفاده از روش Solver تکاملی دارید چرا که تابع COUNTIF و IF توابعی ناهموار از سلولهای متغیر هستند. این مدل در تصویر 2-37 نمایشدادهشده است .
در اینجا میزان مجموع گروه کاری و رضایت کارمندان را به حداکثر رسانده و جریمه تعداد نادرست کارمندان در یک گروه (سلول O12) را کم میکنیم. آنگاه محدودیت هریک از تخصیص کارمندان را به 1، 2، 3 و یا 4 تغییر میدهیم. این راهحل پیشازاین در تصویر 1-37 نشاندادهشده. هر گروه دارای تعداد صحیح کارمندان است، متوسط صلاحیت کارمندان 7.2 است، درحالیکه متوسط رضایت کاری کارمندان 6.3 است. متوسط نرخ صلاحیت در کل 80 نفر کارمند، 4.4 است و نرخ متوسط کل رضایت 5 است، بنابراین در طول فرایند تصادفی تخصیص، شرایط بسیار بهبودیافته است.
چنانچه موتور GRG Nonlinear را استفاده کرده باشید (حتی با انتخاب گزینه Multistart) ممکن است Solver به دلیل اینکه مدل دارای توابع غیرخطی است راهحل بهینه را انتخاب نکند. نکته دیگر درباره استفاده از روش Evolutionary آن است که تا حد امکان از سلولهای متغیر استفاده شود در این صورت Solver با صرف زمان کمتری برای یافتن راهحل بهینه به شما پاداش میدهد.
استفاده از قالببندی شرطی برای برجستهکردن رتبه هریک از کارمندان
میتوان از ویژگی قالببندی شرطی برای به رنگ قرمز در آوردن صلاحیت واقعی هریک از کارمندان (بر اساس وظیفه تخصیصدادهشده به او) و رضایت شغلی استفاده کرد. برای انجام این کار بهسادگی به سلول C3 رفته و محدوده C3:J82 را انتخاب میکنیم. آنگاه در تب Home و از فرمانهای بخش Conditional Formatting گزینه New Rule را انتخاب میکنیم، سپس گزینه Use A Formula To Determine Which Cells To Format را انتخاب کرده و کادر محاورهای را همانطور که در تصویر 3-37 نشاندادهشده تکمیل میکنیم.
این فرمول اگر و تنها اگر اولین کارمند به گروه شماره 1 تخصیصدادهشده باشد رنگ سلول C3 را قرمز میکند. میتوان این فرمول را در ردیفهای روبرو و پایین نیز کپی کرد تا میزان صلاحیت و رضایتمندی هر یک از کارمندان تنها دررابطهبا گروه کاری که در آنها هستند در قالببندی موردنظر مشخص شود.
مسئلههای این فصل:
از ابزار Solver تکاملی برای حل مسئلههای فصل 33 به نام “استفاده از ابزار Sovler برای بودجهبندی سرمایهای” استفاده کنید.
دو مسئله انبار در فصل 36 به نام “موقعیت انبار و موتورهای GRG Multistart و Evolutionary Solver” با فرض بر اینکه هر انبار میتواند حداکثر 120000 واحد ارسال کند را حل کنید.
در ایالت خیالی “Politicians Care About The USA” هشت حوزه انتخاباتی وجود دارد. به هر 15 شهر این ایالت یک حوزه انتخاباتی تخصیص داده میشود و به هر حوزه بین 150000 تا 250000 رأی تخصیصدادهشده. ترکیب انتخاباتی هر حوزه در جدول زیر نمایشدادهشده است. شهرها را بهگونهای به حوزهها تخصیص دهید تا تعداد حوزههایی که دموکراتها در آن برنده میشوند را به حداکثر برسانید. کار را با دادههای زیر آغاز کنید؟
مسئله تخصیص دادن کارمندها را با فرض اینکه رضایت کارمندان دوبرابر مهمتر از رتبهبندی رؤسا است حل کنید.
بیمارستان جنرال هاسپیتال کوک کانتی قصد دارد برنامه کاری 20 پرستار را برنامهریزی کند. هر پرستار میبایست چهار روز متوالی کارکرده و یکی از برنامههای کاری زیر به وی اختصاص داده میشود:
هر پرستار در هفته یا به بخش آیسییو فرستاده میشود یا بخش بیماران عادی. رضایت هر پرستار از وظیفهای که به محول شده در فایلی به نام Nursejackiedate.xlsx قرار داده شده. مثلاً اگر پرستار شماره 5 را به بخش آیسییو فرستاده باشند، به پرستار شماره 5 نمره عالی 10 برای کارکردن در برنامه شماره 3 داده شده است.
بخش آیسییو در هر روز به شش پرستار نیاز دارد و بخش بیماران عادی به پنج پرستار نیازمند است. برنامه کاری هر پرستار را بهگونهای تنظیم کنید که رضایت آنها به حداکثر برسد و درعینحال جوابگوی نیازهای بیمارستان هم باشند. میبایست بدانید کدام برنامه نیاز پرستاران را برای روزهای مختلف برآورده میکند. مثلاً پرستارانی که کار را در روزهای دوشنبه، جمعه، سهشنبه و یکشنبه آغاز میکنند در روز جمعه کار خواهند کرد.
فایلی به نام Problem6data.xlsx حاوی دادههای سیزده جلسه یکساعته شامل ده نفر است. سلول A1 مشخصکننده تعداد افراد شرکتکننده در هر جلسه است. جلسههای 6 و 7 نفره نمیبایست همزمان تشکیل شوند و البته یک فرد نمیتواند همزمان در دو جلسه شرکت کند. حداقل تعداد ساعتهای لازم برای برنامهریزی تمامی جلسات چند ساعت است؟