آموزش ابزار solver در اکسل

یکی از ابزارهای بسیار کاربردی در نرم افزار اکسل، افزونه solver است. با کمک این ابزار می توان به راه حل های بهینه در حل مسائل برنامه ریزی خطی دست یافت. رسیدن به بهینه ترین راه حل می تواند شامل یافتن حداکثر سود، حداقل هزینه یا استفاده از بهترین تخصیص منابع در فرآیند تولید باشد.

نحوه فعال کردن ابزار solver در اکسل

ابزار Solver یک افزونه (Adds-in) در نرم افزار اکسل است که در هنگام نصب این نرم افزار، در دسترس است. با این حال، برای استفاده از این افزونه، ابتدا باید آن را فعال کرد. برای این کار باید مسیر زیر را دنبال کنیم:

۱) از منوی File گزینه Options را انتخاب می کنیم.

1 32 - آموزش ابزار solver در اکسل

۲) در قسمت Excel Option بر روی گزینه Add-ins کلیک  می کنیم.

۳) در پنجره باز شده در مقابل گزینه Excel Add-ins، بر روی گزینه Go کلیک می کنیم.

2 22 - آموزش ابزار solver در اکسل

۴) در پنجره باز شده، گزینه Solver Add-in را انتخاب و گزینه Ok را کلیک می کنیم.

3 19 - آموزش ابزار solver در اکسل

پس از انجام این مراحل، در بخش Data و در گروه Analyze گزینه ای به نام Solver اضافه خواهد شد.

29 2 - آموزش ابزار solver در اکسل

معرفی عناصر مدل بهینه سازی

در مسائل بهینه سازی، هدف یافتن مقادیری از متغیرها است که به ازای آن‌ها تابع هدف کمینه یا بیشینه می‌شود. البته بهینه‌سازی در اغلب مسائل کاربردی، با توجه به محدودیت‌ ها تعریف می شود و مسائل برنامه ریزی خطی یکی از انواع مسائل بهینه‌ سازی مقید است.

در مدل برنامه برنامه ریزی خطی سه عنصر اصلی وجود دارد:

  1. متغیرهای تصمیم‌ گیری (decision variables)
  2. تابع هدف (objective function)
  3. محدودیت‌ ها (constraints)

اجازه دهید در قالب یک مثال این عناصر را توضیح دهیم:

فرض کنید شرکت آلفا دستگاه تصفیه آب می فروشد. این شرکت در راستای معرفی دو محصول جدید خانگی و نیمه صنعتی خود، در تدارک یک کمپین تبلیغاتی به ارزش ۲۰۰ میلیون تومان است و علاقه مند است که بداند چگونه بودجه تبلیغاتی را بین دو بازار هدف  استان گیلان و استان تهران تخصیص دهد .

اولین عنصر یک مدل بهینه سازی، متغیر تصمیم گیری است. در این مثال، مبلغ تبلیغات برای هر ترکیبی از بازار محصول، مولفه ای است که شرکت باید درباره آن تصمیم گیری کند.

عنصر بعدی مدل، تابع هدف است، که در مدل باید حداکثر یا حداقل شود. در مسئله ما، شرکت می خواهد فروش خالص خود را به حداکثر برساند. توجه کنیم که هدف، تابع متغیرهای تصمیم گیری است.

عنصر نهایی مدل بهینه سازی، قید یا محدودیت است. به عبارت دیگر الزاماتی که یک تصمیم گیرنده باید آن را برآورده کند. در این مثال ما با چهار قید مواجه هستیم. قبل از هر چیز، کل هزینه تبلیغات نمی تواند از بودجه تبلیغات فراتر رود. قید دوم و سوم، حداقل افزایش فروش را در استان گیلان و تهران اعمال می کنند. اینگونه که در نتیجه تبلیغات، فروش خالص در گیلان و تهران باید به ترتیب حداقل ۳ و ۴ میلیون افزایش یابد. در نهایت قید چهارم تصریح می کند که افزایش فروش محصولات نیمه صنعتی، باید حداقل برابر با ۷۰ درصد فروش محصولات خانگی باشد.

ساخت مدل در اکسل

قبل از استفاده از افزونه Solver  لازم است مسئله را در اکسل مدلسازی کنیم.

بیایید با متغیرهای تصمیم گیری شروع کنیم. با در نظر گرفتن دو محصول در دو بازار هدف، چهار متغیر تصمیم داریم. برای نمونه، ترکیبی از متغیرهای تصمیم گیری را مانند تصویر زیر در نظر می گیریم.

5 20 - آموزش ابزار solver در اکسل

اکنون باید تابع هدف را مشخص کنیم.

گفتیم که در مثال ما، شرکت می خواهد مقدار بهینه ای از متغیرهای تصمیم گیری به گونه ای انتخاب شود که در مجموع منجر به حداکثر شدن فروش خالص شود. اگر بخواهیم افزایش فروش خالص را بر اساس متغیر های تصمیم گیری مسئله محاسبه کنیم، باید هر متغیر احتمالی را با مقدار اثربخشی فروش خود ضرب کنیم. اگر شرکت آلفا ۵۰ میلیون تومان برای تبلیغات نسخه خانگی در گیلان هزینه کند، و این هزینه منجر به ۵٪ فروش بیشتر شود، افزایش فروش خالص ناشی از این هزینه تبلیغات برابر با  ۲٫۵ میلیون تومان خواهد بود. به همین ترتیب، سه مبلغ هزینه دیگر در مقادیر اثر فروش مربوط به آنها ضرب می شود تا نهایت افزایش خالص فروش بدست آید.

 نکته: توجه کنیم به جای ضرب تک تک متغیرها و جمع آنها، بهتر است از تابع Sumproduct که حاصل جمع حاصل ضرب آرایه های متناظر را حساب می کند، استفاده کنیم. 

a3 - آموزش ابزار solver در اکسل

و در نهایت هر چهار محدودیت مسئله را باید در مدل خود وارد سازیم.

قید اول:  مبلغ کل هزینه تبلیغات باید کمتر از بودجه اختصاصی باشد.

قید دوم: در نتیجه تبلیغات در  استان گیلان فروش خالص حداقل ۳ میلیون تومان افزایش یابد.

قید سوم: در نتیجه تبلیغات در استان تهران فروش خالص حداقل ۴ میلیون تومان افزایش یابد.

قید چهارم: افزایش فروش  خالص محصولات نیمه صنعتی حداقل ۷۰ درصد فروش محصولات خانگی باشد.

 برای اجتناب از اشتباه و قابل درک بودن فرمول ها، بهتر است از تکنیک نام گذاری محدوده ها در اکسل استفاده کنیم.  

برای نمونه فروش خالص در استان گیلان اینگونه محاسبه می گردد:

a1 - آموزش ابزار solver در اکسل

بدین ترتیب توانستیم مدل خود را در اکسل طراحی کنیم:

a2 - آموزش ابزار solver در اکسل

نحوه استفاده از افزونه Solver

پس از فعال سازی افزونه Solver باید مراحل زیر را به ترتیب انجام دهیم.

  • در پنجره Solver و در مقابل گزینه Set Objective باید آدرس سلول هدف F10 را وارد کنیم و گزینه Max را انتخاب کنیم.
  • در مقابل گزینه By Changing Variable Cells  باید محدوده مربوط به مقادیر هزینه تبلیغات یا به عبارت دیگر متغیرهای تصمیم گیری (B6:C7) را وارد سازیم.
  • در قسمت مربوط به Subject to the Constraints می بایست محدودیت یا قید مسئله را وارد کنیم. برای این کار باید بر روی گزینه Add کلیک کنیم و محدودیت ها را وارد کنیم.
  • در نهایت باید بر روی Solve کلیک کنیم.

a - آموزش ابزار solver در اکسل

دقت کنیم که برای حل مسائل برنامه ریزی خطی باید روش Simplex را انتخاب کنیم.

28 2 - آموزش ابزار solver در اکسل

در ادامه پنجره ای مطابق تصویر زیر، نمایش داده خواهد شد. مشاهده می کنیم که Solver، مقادیر بهینه هزینه تبلیغات هر یک از محصولات در دو استان را محاسبه کرده است.  توجه کنیم که اگر روی Answer کلیک کنیم، پاسخ های مسئله در قالب صفحه جدید ارائه خواهد شد.

10 14 - آموزش ابزار solver در اکسل

همان گونه که مشاهده می کنید مقدار بهینه هزینه تبلیغات برای دو محصول خانگی و نیمه صنعتی در استان های گیلان و تهران برابر است با:

b - آموزش ابزار solver در اکسل

استفاده از Solver برای حداکثر کردن سود اقتصادی

اگر به یاد داشته باشید، در مطلب معرفی خالص ارزش حال (NPV) در اکسل گفتیم که در صورت وجود محدودیت در منابع مالی، باید از روش برنامه ریزی خطی و تکنیک بهینه سازی مقید در جهت حداکثر کردن سود اقتصادی استفاده کنیم.

به طور مثال فرض کنید می توانیم در چهار طرح سرمایه گذاری با سود اقتصادی مثبت (NPV>0) سرمایه گذاری کنیم. همچنین رقم کل سرمایه موجود جهت سرمایه گذاری برابر با ۸۰۰ میلیون تومان است.  بنابراین با توجه به بودجه خود ، باید طرح هایی را انتخاب کنیم که سود اقتصادی را حداکثر سازند.

مانند مثال بالا در گام اول باید متغیرهای تصمیم گیری را تعیین کنیم. این متغیرها را با یک شاخص عددی مشخص کنیم. مثلا می توانیم عدد یک را برای پذیرش سرمایه گذاری در طرح و عدد صفر را برای عدم پذیرش طرح در نظر بگیریم.

17 12 - آموزش ابزار solver در اکسل

در گام بعدی باید سلول هدف را محاسبه کنیم. در این مثال، حداکثر کردن خالص ارزش حال، مدنظر ما است.

20 10 - آموزش ابزار solver در اکسل

و در گام آخر، باید محدودیت مسئله را مشخص کنیم. در این مثال  دو محدودیت داریم که عبارت اند از:

۱) رقم کل بودجه ما نمی تواند از ۸۰۰ میلیون بیشتر باشد. برای این کار باید در یک سلول مقدار سرمایه مورد نیاز را با توجه به طرح های انتخاب شده محاسبه کنیم و آنرا کوچکتر و مساوی ۸۰۰ میلیون قرار دهیم.

21 9 - آموزش ابزار solver در اکسل

۲) قرار دادن شاخص های تصمیم گیری به صورت صفر و یک یا همان binary. (مانند تصویر زیر)

14 10 - آموزش ابزار solver در اکسل

 

27 2 - آموزش ابزار solver در اکسل

با کلیک بر روی گزینه Solve می بینیم که بهتر است در طرح های ۲،۱ و ۳ سرمایه گذاری کنیم. رقم کل بودجه استفاده شده برابر با ۷۲۰ میلیون و حداکثر سود اقتصادی برابر با ۵۱۵ میلیون خواهد شد.

19 8 - آموزش ابزار solver در اکسل

برای دانلود فایل اکسل این آموزش روی لینک زیر کلیک کنید.

فایل آموزش



64a0fe2c177a3c354edac6f459149b67?s=300&d=mm&r=g - آموزش ابزار solver در اکسل

الناز پورمحمد

دانش آموخته اقتصاد دانشگاه شهید بهشتی، علاقمند به مطالعات امکان سنجی، تحقیقات بازار و مدلسازی مالی
3 2 votes
Article Rating
عضویت
به دوستتان خبر دهید.
guest
0 Comments
Inline Feedbacks
View all comments
0
عالی میشه اگر نظر بدید.x
()
x