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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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