شروع جشنواره نوروزی و تخفیف های ویژه 1402 (جهت مشاهده تخفیف ها اینجا کلیک کنید)
سبد خرید
0

هیچ محصولی در سبد خرید نیست.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

آموزش استفاده از Solver در اکسل

ابزار Solver در اکسل
۵/۵ - (۱ امتیاز)

کاربرد افزونه Solver

در مقالات قبل راجع به ابزار Goal Seek صحبت کردیم و توضیح دادیم که برای تغییر نتیجه یک فرمول، با تغییر یک متغیر میتونیم از این ابزار استفاده کنیم. مثلا با تغییر حقوق پایه، حقوق دریافتی رو برسونیم به مقدار دلخواه. در اون مقاله توضیح دادیم که شرط اصلی استفاده از این ابزار، تغییر یک متغیر هست و حل مسئله با بیش از یک متغیر با این ابزار امکان پذیر نیست. حالا سوال این هست که اگر تعداد متغیرها بیشتر بشه باید چکار کرد؟
وقتی متغیرهای یک مسئله بیش از یکی باشه، مسئله به یک مدل چند متغیره تبدیل میشه که باید با روش های ریاضی مثل سیمپلکس حل بشه. برای اینکه بتونیم این قبیل مسائل رو در اکسل حل کنیم باید از افزونه مرتبط با این کار استفاده کنیم. اسم این افزونه Solver هست که در واقع یکی دیگه از ابزارهای What If Analysis بشمار میره. در واقع از این افزونه برای شبیه سازی و بهینه سازی مدل های مختلف مهندسی و تجاری استفاده میشه. در واقع این افزونه برای حل مسائل برنامه ریزی خطی و مسائل بهینه سازی خطی بکاربرده میشه و بخاطر همین به ابزار حل برنامه ریزی خطی شناخته میشه. البته برخی مسائل غیرخطی رو نیز میتونه حل کنه.

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

افزونه Solver در اکسل موجود هست و فقط باید فعالسازی بشه و به منوها اضافه بشه. در ادامه نحوه فعالسازی و استفاده از این افزونه برای حل مسائل بهینه سازی رو تشریح خواهیم کرد:

اضافه کردن افزونه به اکسل

افزونه Solver از ورژن ۲۰۰۳ تا کنون در اکسل وجود داره، فقط باید فعال کنیم تا بتونیم ازش استفاده کنیم. مسیر فعالسازی مشابه فعالسازی افزونه Data Analysis هست. پس:

  1. از قسمت File گزینهExcel Options رو کلیک میکنیم.
  2. از قسمت Add-Ins مطابق شکل ۱ روی گزینه Go کلیک میکنیم.

فعال سازی افزونه Solver

شکل ۱ – افزونه Solver

  1. از پنجره باز شده، گزینه Solver Add-In رو تیک زده و Ok میکنیم.

فعال کردن افزونه Solver در اکسل

شکل ۲ – افزونه Solver- فعال کردن افزونه در اکسل

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

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

قبل از اینکه بتونیم از این افزونه استفاده کنیک، اول باید مسئله رو مدلسازی کنیم. منظور از مدلسازی هم تعیین تابع هدف و محدودیت های مسئله با هدف بهینه سازی است. در ادامه با چند مثال این مسئله رو توضیح میدیم:

مثال اول – مسئله غیر خطی ساده

مسئله: فرض کنیم که یک مرکز خدماتی داریم که میخوایم خدمت جدیدی رو به مشتریان ارائه بدیم. برای این خدمت جدید. برای این کار باید یک سری تجهیزات خردای کنیم با مبلغ ۴۰ میلیون تومان که میتونیم در ۱۲ ماه پرداخت کنیم. حالا میخوایم بدونیم که به چند مشتری خدمت بدیم و با چه دریافتی، میتونیم این هزینه رو در دوازده ماه جبران کنیم.

هدف: محاسبه کمترین هزینه بابت هر سرویس که به مشتری ارائه میشه که بتونه هزینه سرمایه گذاری در ۱۲ ماه رو جبران کنه.

برای این مسئله، مدل زیر رو طراحی میکنیم:

مدلسازی مسئله

شکل ۳- افزونه Solver – مدلسازی مسئله

حالا ببینیم که این مدل رو چطور به افزونه معرفی و حل میکنیم.

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

تابع هدف:

در این مسئله، تعداد ماه های بازپرداخت مبلغ سرمایه گذاری هست. در این مثال میخواهیم این مقدار رو معادل ۱۲ قرار بدیم. گزینه های دیگر در این افزونه، ماکزیمم یا مینیمم کردن مقدار تابع هدف هست. پس بصورت کلی میتونیم یک تابع هدف رو مینیمم، ماکزیمم و یا معادل یک مقدار خاص قرار بدیم.

تعیین تابع هدف

شکل ۴- افزونه Solver – تعیین تابع هدف

تعریف متغیرها:

متغیرها، سلول هایی هستن که در تابع هدف و محدودیت ها استفاده شدن و در واقع با تغییر این مقادیر هست که به هدف تعیین شده برای تابع هدف میرسیم. افزونه Solver در اکسل تا حداکثر ۲۰۰ متغییر رو پشتیبانی میکنه. در این مثال متغیرها به شرح زیر است:

  • تعداد مشتریان (که حداکثر میتونه ۵۰ تا باشه)
  • مبلغ دریافتی از هر مشتری بابت ارائه خدمت جدید

تعیین متغیرها

شکل ۵ – افزونه Solver – تعیین متغیرها

نکته:
اگر متغیرهای مسئله در سلول های پراکنده قرار گرفتن، میتونیم با انتخاب اولین سلول و بعد با نگه داشتن Ctrl سلول های دیگه رو به متغیرها اضافه کنیم.

 

تعیین محدودیت ها:

هر تابع هدفی، یک سری محدیدت ها داره که بر اساس انها مسئله رو حل میکنیم. مثلا محدویت های ما در این مسئله عبارتند از:

  • تعداد مشتری ها از ۵۰ تا نباید بیشتر باشه،
  • میزان سرمایه گذاری برابر است با ۴۰ میلیون.

برای اضافه کردن محدودیت های مسئله روی گزینه Add کلیک میکنیم و محدودیت های مسئله رو یکی یکی اضافه میکنیم. مثلا برای اولین محدودیت یعنی میزان سرمایه گذاری برابر است با ۴۰ میلیون، طبق شکل ۶ عمل میکنیم:

اضافه کردن محدودیت اول مسئله

شکل ۶ – افزونه Solver- اضافه کردن محدودیت اول مسئله

حالا دوباره روی Add کلیک میکنیم و محدودیت دوم یعنی حداکثر تعداد مشتریان رو هم مطابق شکل ۷ وارد میکنیم.

اضافه کردن محدودیت دوم به افزونه

شکل ۷-افزونه Solver – اضافه کردن محدودیت دوم به افزونه

در قسمتی که بزرگتر، کوچکتر بودن محدودیت ها رو تعیین میکنیم، سه حالت دیگه هم وجود داره که در ادامه توضیح میدهیم:

Int: وقتی محدودیت ها در حالت Int باشه یعنی که متغیر ها نمیتونن مقدار اعشاری داشته باشن و حتما پاسخ مسئله، عدد صحیح خواهد بود.

Bin: اگر مقادیر متغیر ها فقط صفر و یک میتونه باشه، اونها رو در حالت Bin قرار میدیم.

Dif: اگر مقادیر متغیرها نباید با هم برابر باشه و مقادیر متفاوتی باید باشه، اونها رو در حالت Dif قرار میدیم.

برای حذف و تغییر محدودیت ایجاد شده هم به ترتیب از کلید های Delete و Change استفاده میکنیم.

حالا که تابع هدف، محدودیت ها و متغیرها رو وارد کردیم، باید بریم سراغ حل مسئله.

حل مسئله با استفاده از Solver:

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

حل مدل

شکل ۸- افزونه Solver – حل مدل

برای اینکه این مسئله رو بتونیم حل کنیم، یک مقدار اولیه به متغیرها میدیم که تابع هدف، مقدار اولیه داشته باشه. مثلا هر دو سلول B3 و B4 رو مقدار ۱ میذاریمو بعد روی دکمه Solve کلیک میکنیم. در ادامه پنجره مطابق شکل ۹ نمایش داده خواهد شد که پاسخی پیدا شده برای مسئله. همونطور که در شکل ۹ مشخص هست، مقادیر یافت شده برای مدل مورد نظر، تعداد مشتری ۵۰ و هزینه دریافتی بابت هر سرویس معادل حدودا ۶۹ هزار هست. وقتی روی Answer کلیک میکنیم، همه پاسخ های مسئله در قالب یک شیت جدید ارائه میشه و میتونیم تحلیل جواب ها رو ببینیم.

ارائه پاسخ مدل تعیین شده توسط Solver در اکسل

شکل ۹ – افزونه Solver – ارائه پاسخ مدل تعیین شده

مثال دوم – مربع جادویی

مربع جادویی در واقع مربعی است که هر ستون و ردیفش و که جمع بزنیم باید به یک عدد مشخص برسه. مثلا یک مربع ۳*۳ رو در نظر بگیرید که میخواهیم هر ردیف و ستونش و که جمع زدیم بشه ۱۵. با این شرط که عدد تکررای نداشته باشیم.

قطعا این سوال با سعی و خطا هم حل میشه. اما Solver خیلی سریع تر پاسخ رو پیدا خواهد کرد:

مدل رو مطابق شکل ۱۰ آماده میکنیم و برای شروع اعداد دلخواه رو داخل مربع مورد نظر وارد میکنیم.

تعیین مدل مورد نظر برای مربع جادویی جهت حل با Solver

شکل ۱۰ – افزونه Solver – تعیین مدل مورد نظر برای مربع جادویی

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

در ادامه و در قسمت متغیر ها، کل محدوده B2:D4 رو به عنوان متغیر به افزونه معرفی میکنیم.

محدودیت های مدل رو هم بصورت زیر معرفی میکنیم:

  • مقادیر حتما باید صحیح باشند پس Int هستند
  • مقادیر تکراری قابل قبول نیستند پس Dif رو تعیین میکنیم.
  • مقادیر جمع ستونی و ردیفی، هر یک جداگانه باید برابر با عدد ۱۵ باشند.
  • جمع قطری مربع نیز باید برابر با ۱۵ باشه.

پس با توجه به توضیحات بالا، پارامترهای Solver رو مشابه زیر تنظیم میکنیم:

محدودیت های مربع جادویی

شکل ۱۱- افزونه Solver – محدودیت های مربع جادویی

حالا دکمه Solve رو میزنیم و مدل رو حل میکنیم.

مربع جادویی حل شده

شکل ۱۲- مربع جادویی حل شده

مثال سوم – مسئله برنامه ریزی خطی

در ادامه یک مسئله بهینه سازی حمل و نقل با یک هدف خطی رو خواهیم دید. مدل های پیچیده تر بهینه سازی از این نوع مدل هایی هستند که شرکت های مختلفی برای صرفه جویی های مالی استفاده میکنن.

مسئله: میخواهیم هزینه ارسال محصولات از دو انبار به چهار مشتری رو کمینه کنیم. هر انبار محدودیت عرضه و هر مشتری تقاضای مشخصی داره.

هدف: کمینه کردن هزینه های ارسال با توجه به اینکه از ظرفیت هر انبار تجاوز نکنیم و تقاضای هر مشتری پاسخ داده بشه.

داده های مسئله به شرح زیر است:

داده های یک مدل بهینه سازی خطی

شکل – ۱۳- داده های یک مدل بهینه سازی خطی

حالا باید مدل رو فرموله کنیم. برای این کار باید به این سه سوال پاسخ بدیم:

  1. چه تصمیم هایی باید گرفته بشه؟ میخوایم تعداد بهینه محصول که از هر انبار به مشتری ارسال میشه رو محاسبه کنیم. این مقادیر متغیرهای مدل هستن. یعنی محدوده B8:E9.
  2. چه محدودیت هایی وجود داره؟ موجودی هر انبار، سلول های H8:H9، میزان ارسال نباید از این حد تجاوز کنه. محدودیت بعدی، میزان سفارش هر مشتری هست که حتما باید تامین بشه . محدوده های B11:E11.
  3. تابع هدف چی هست؟ کمینه شدن هزینه ارسال محصولات به مشتریان. که این رو در سلول B13 قرار میدیم.

حالا این محدودیت ها و تابع هدف رو فرمولی میکنیم مطابق شکل ۱۴.

فرموله کردن محدودیت ها و تابع هدف با توجه به مفهوم مسئله

شکل ۱۴- فرموله کردن محدودیت ها و تابع هدف با توجه به مفهوم مسئله

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

نام محدوده مورد نظر محدوده ها پارامترهای مدل
Products_Shipped B8:E9 متغیرهای مدل
Available H8:H9 محدودیت موجودی
Total_Shipped F8:F9 محدودیت ارسال شده از انبار
Ordered B11:E11 محدودیت سفارش مشتریان
Total_Received B10:E10 محدودیت محصولات دریافتی مشتریان
Shipping_Cost B13 تابع هدف

حالا باید پارامترهای مدل رو در افزونه Solver مشخص کنیم. مطابق شکل ۱۵

تعیین پارامترهای مدل در افزونه Solver

شکل ۱۵- تعیین پارامترهای مدل در افزونه

چون میدونیم مدلی که داریم حل میکنیم خطی هست. Lp رو انتخاب میکنیم. اگر نمیدونیم که مدلمون از چه نوعی هست فعلا بذارید همون Grg بمونه و مدل رو با همون روش حل کنید.

بعد از زدن Solve مدل حل میشه و جواب ها مطابق شکل ۱۶ نمایش داده میشن. این نتایج نشون میده که پاسخ مشتری ۱ و ۲ از انبار شماره ۱ و پاسخ مشتری شماره ۳ رو از هر دو انبار و مابقی رو از انبار ۲ بدیم، هزینه انتقال در کمترین حالت خواهد بود.

جواب متغیرهای مدل خطی بعد از حل توسط Solver

شکل ۱۶ – افزونه Solver – جواب متغیرهای مدل خطی بعد از حل

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

دانلود فایل اکسل افزونه Solver در اکسل

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

کلیدواژه : Add-ins
133

من سامان چراغی هستم. دانش آموخته مقطع فوق لیسانس دانشگاه تربیت مدرس در رشته مهندسی صنایع. از سال 1388 اکسل و برنامه نویسی VBA رو به صورت حرفه ای شروع کردم.

دیدگاه کاربران
  • احدی ۱۷ مرداد ۱۴۰۱ / ۱۱:۴۵ ب٫ظ

    سلام
    ی پروفیل ۶ متری داریم و چند تا اندازه برش ک از پروفیل ۶ متری برش بزنیم
    فرمولی هست ک دور ریزها رو ب حداقل برسونه؟
    باتشکر

    • آواتار
      حسنا خاکزاد ۱۸ مرداد ۱۴۰۱ / ۸:۴۰ ق٫ظ

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

      • Negar ۱۱ شهریور ۱۴۰۲ / ۱۲:۰۰ ب٫ظ

        سلام ممنون از مقاله خوبتون
        فقط اینکه من عین مسایل بالارو باهمین اعداد حل میکنم جواب متفاوت از شما بهم میده چرا؟و وقتیم answer رو میزنم فقط همون جواب نماش داده شده رو نشون میده نه همه جواب های ممکن!!

        • آواتار
          حسنا خاکزاد ۱۱ شهریور ۱۴۰۲ / ۵:۵۶ ب٫ظ

          درود بر شما
          کدوم مثال؟

  • علی ۲۰ اردیبهشت ۱۴۰۱ / ۱۰:۱۲ ق٫ظ

    سلام. خسته نباشید. آیا می شود سلول های متغیری را که با solver حل شده اند را مثل سایر فرمولهای اکسل به بقیه سلولها انتقال داد؟- چون در باکس فرمول چیزی نمی آورد.

    • آواتار
      حسنا خاکزاد ۲۱ اردیبهشت ۱۴۰۱ / ۱۰:۰۵ ق٫ظ

      درود
      منظور value سلول هست؟
      که خب قابل انتقال هست بعد از حل مدل

  • فریده ۱۵ مرداد ۱۳۹۹ / ۱۰:۰۷ ب٫ظ

    باسلام
    برای محاسبه A.B.C R در فرمول زیر چگونه باید در SOLVER نوشته بشه. . این مقادیر برای هر سری باید جداگانه بدست آید
    ))A*I1+B*S1+C)-T)^2=R

    مقادیر I.S T در هر سری متفاوته و و مقادیر آنها موجود است.به طور مثال
    ))A1*32+B1*546+C1)-60)^2=R1
    ))A2*53+B2*487+C2)-87)^2=R2
    ))A3*42+B3*422+C3)-84)^2=R3

    • آواتار
      حسنا خاکزاد ۱۶ مرداد ۱۳۹۹ / ۹:۲۹ ب٫ظ

      درود
      روش تخصیص متغیر و محدودیت و تابع هدف شرح داده شده در مقاله
      مطالعه کنید و با مسئله خودتون تطبیق بدید!

  • سحر ۱۷ اردیبهشت ۱۳۹۹ / ۱:۴۰ ب٫ظ

    سلام وقتتون بخیر من زمانی که می خوتهم solver اجرا کنم خطای ماکرو می دهد چگونه رفع کنم این خطا ؟

    • آواتار
      حسنا خاکزاد ۲۰ اردیبهشت ۱۳۹۹ / ۱۱:۲۳ ق٫ظ

      درود
      شما دسترسی به کدهای Solver رو ندارید.
      احتمالا خطا از جای دیگه ای هست. کد دیگه ای در فایل موجوده شاید

  • mohsen alihosseini ۲۸ بهمن ۱۳۹۸ / ۵:۴۲ ب٫ظ

    با سلام و احترام
    آیا می توان در یک محدوده مشخص اکسل که در برخی سلول ها عدد وارد شده است تعریف کنیم که سلول های دارای عدد را رنگی نمایش دهد و بعد از مشخص کردن آن سلول ها با رنگ، عدد ها را پاک کنیم ولی سلول بصورت رنگی بماند وجود دارد؟ لطفا راهنمایی فرمایید

    • سامان چراغی ۲۸ بهمن ۱۳۹۸ / ۶:۳۳ ب٫ظ

      سلام
      از دو روش میتونید این کار رو انجام بدید:
      ۱- استفاده از VBA
      ۲- با استفاده از Goto Special و انتخاب گزینه Constant و انتخاب تیک Numbers، یکجا همه سلول های حاوی عدد رو انتخاب کنید و رنگ مورد نظر رو اعمال کنید.

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

توسط
تومان