نحوه استفاده از جستجوی هدف و حل کننده اکسل برای حل متغیرهای ناشناخته

اکسل می تواند متغیرهای ناشناخته را حل کند، یا برای یک سلول با جستجوگر هدف یا چندین سلول با حل کننده. ما به شما نشان خواهیم داد که چگونه کار می کند.

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

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

نحوه استفاده از Goal Seek در اکسل

جستجوی هدف قبلاً در اکسل تعبیه شده است. در زیر تب Data، در منوی What-If Analysis قرار دارد:

برای این مثال، از یک مجموعه اعداد بسیار ساده استفاده خواهیم کرد. ما سه چهارم تعداد فروش و یک هدف سالانه داریم. می‌توانیم از Goal Seek استفاده کنیم تا بفهمیم که اعداد در Q4 برای رسیدن به هدف چقدر باید باشند.

همانطور که مشاهده می کنید، مجموع فروش فعلی 114706 دستگاه است. اگر بخواهیم تا پایان سال 250000 بفروشیم، در سه ماهه چهارم چند عدد باید بفروشیم؟ جستجوی هدف اکسل به ما خواهد گفت.

در اینجا نحوه استفاده از Goal Seek، مرحله به مرحله آورده شده است:

1. روی Data > What-If Analysis > Goal Seek کلیک کنید. پنجره Goal Seek ظاهر می شود.
2. قسمت “برابر” معادله خود را در قسمت Set Cell قرار دهید. این عددی است که اکسل سعی خواهد کرد آن را بهینه کند. در مورد ما، این مجموع اعداد فروش ما در سلول A5 است.
3. مقدار هدف خود را در قسمت To value تایپ کنید. ما در مجموع به دنبال فروش 250000 دستگاه هستیم، بنابراین “250000” را در این زمینه قرار می دهیم.
4. در قسمت By change cell به اکسل بگویید کدام متغیر را حل کند. ما می خواهیم ببینیم که فروش ما در سه ماهه چهارم باید چقدر باشد. بنابراین به اکسل می‌گوییم که سلول D2 را حل کند. وقتی آماده حرکت شد به این شکل خواهد بود:
5. OK را بزنید تا هدفتان حل شود. وقتی خوب به نظر می رسد، فقط OK را بزنید. اکسل زمانی که Goal Seek راه حلی پیدا کرد به شما اطلاع می دهد.
6. دوباره روی OK کلیک کنید، مقداری را می بینید که معادله شما را در سلولی که برای با تغییر سلول انتخاب کرده اید حل می کند.

در مورد ما، راه حل 135294 واحد است. البته، می‌توانستیم با کم کردن مجموع در حال اجرا از هدف سالانه به این موضوع پی ببریم. اما Goal Seek را می توان در سلولی که قبلاً داده در آن وجود دارد نیز استفاده کرد. و این مفیدتر است.

توجه داشته باشید که اکسل داده های قبلی ما را بازنویسی می کند. این ایده خوبی است که Goal Seek را روی یک کپی از داده های خود اجرا کنید. همچنین ایده خوبی است که روی داده های کپی شده خود یادداشت کنید که با استفاده از Goal Seek ایجاد شده است. شما نمی خواهید آن را برای داده های فعلی و دقیق اشتباه بگیرید.

بنابراین Goal Seek یک ویژگی مفید اکسل است، اما آنقدرها هم چشمگیر نیست. شما می توانید آن را فقط در یک سلول در یک زمان استفاده کنید. اگر می خواهید از Goal Seek اکسل روی چندین سلول به طور همزمان استفاده کنید، به ابزار بسیار قدرتمندتری نیاز دارید. خوشبختانه، یکی از این ابزارها با اکسل ارائه می شود. بیایید نگاهی به افزونه Solver بیندازیم.

حل کننده اکسل چه کاری انجام می دهد؟

به طور خلاصه، Solver مانند یک نسخه چند متغیره از Goal Seek است. اگر می‌پرسید چگونه از Goal Seek در اکسل برای چندین سلول به طور همزمان استفاده کنید، این همان است. یک متغیر هدف می گیرد و تعدادی متغیر دیگر را تنظیم می کند تا زمانی که به پاسخ مورد نظر شما برسد.

می تواند مقدار حداکثر یک عدد، مقدار حداقل یک عدد یا یک عدد دقیق را حل کند. و در محدوده کار می کند، بنابراین اگر یک متغیر را نمی توان تغییر داد، یا فقط می تواند در یک محدوده مشخص تغییر کند، Solver آن را در نظر می گیرد.

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

نحوه بارگیری افزونه حل کننده

اکسل به طور پیش فرض Solver ندارد. این یک افزونه است، بنابراین ابتدا باید آن را بارگیری کنید. خوشبختانه، از قبل روی رایانه شما موجود است.

به File > ( More… >) Options > Add-Ins بروید. سپس روی Go در کنار Manage: Excel Add-Ins کلیک کنید.

اگر این منوی کشویی چیزی غیر از «افزونه‌های اکسل» می‌گوید، باید آن را تغییر دهید:

در پنجره ایجاد شده، چند گزینه را مشاهده خواهید کرد. مطمئن شوید که کادر کنار Solver Add-In علامت زده شده است و OK را بزنید.

اکنون دکمه Solver را در گروه Analysis در تب Data خواهید دید:

اگر قبلا از Data Analysis Toolbak استفاده کرده اید، دکمه Data Analysis را مشاهده خواهید کرد. اگر نه، Solver به خودی خود ظاهر می شود.

اکنون که افزونه را بارگیری کردید، بیایید نحوه استفاده از آن را بررسی کنیم.

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

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

1. روی Data > Solver کلیک کنید. پنجره Solver Parameters را در زیر مشاهده خواهید کرد. (اگر دکمه حل کننده را نمی بینید، به بخش قبلی در مورد نحوه بارگیری افزونه حل کننده مراجعه کنید.)
2. هدف سلول خود را تنظیم کنید و هدف خود را به اکسل بگویید. هدف در بالای پنجره حل کننده قرار دارد و دارای دو بخش است: سلول هدف و انتخاب حداکثر، کوچک کردن یا یک مقدار خاص. اگر Max را انتخاب کنید، اکسل متغیرهای شما را طوری تنظیم می کند که بیشترین تعداد ممکن را در سلول هدف شما به دست آورد. Min برعکس است: حل کننده عدد هدف را به حداقل می رساند. Value Of به شما امکان می دهد یک عدد خاص را برای Solver مشخص کنید تا به دنبال آن باشد.
3. سلول های متغیری را که اکسل می تواند تغییر دهد انتخاب کنید. سلول های متغیر با فیلد By Changing Variable Cells تنظیم می شوند. روی فلش کنار فیلد کلیک کنید، سپس برای انتخاب سلول هایی که Solver باید با آنها کار کند، کلیک کرده و بکشید. توجه داشته باشید که اینها همه سلول هایی هستند که می توانند متفاوت باشند. اگر نمی خواهید سلولی تغییر کند، آن را انتخاب نکنید.
4. محدودیت هایی را روی متغیرهای چندگانه یا فردی تنظیم کنید. در نهایت به محدودیت ها می رسیم. اینجاست که Solver واقعا قدرتمند است. به جای تغییر هر یک از سلول های متغیر به هر عددی که می خواهد، می توانید محدودیت هایی را مشخص کنید که باید رعایت شوند. برای جزئیات، به بخش نحوه تنظیم محدودیت ها در زیر مراجعه کنید.
5. هنگامی که همه این اطلاعات در جای خود قرار گرفتند، حل را بزنید تا پاسخ خود را دریافت کنید. اکسل داده های شما را برای گنجاندن متغیرهای جدید به روز می کند (به همین دلیل است که توصیه می کنیم ابتدا یک کپی از داده های خود ایجاد کنید).

همچنین می‌توانید گزارش‌هایی را تولید کنید که در مثال Solver خود در زیر به طور خلاصه به آن نگاه خواهیم کرد.

نحوه تنظیم محدودیت ها در حل کننده

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

برای اضافه کردن یک محدودیت، روی دکمه افزودن در کنار لیست محدودیت کلیک کنید. شما یک پنجره جدید دریافت خواهید کرد. سلول (یا سلول‌هایی) را که باید در قسمت Cell Reference محدود شود، انتخاب کنید، سپس یک عملگر را انتخاب کنید.

در اینجا اپراتورهای موجود هستند:

• <= (کمتر از یا مساوی)
• = (برابر با)
• => (بزرگتر یا مساوی)
• int (باید یک عدد صحیح باشد)
• bin (باید 1 یا 0 باشد)
• همه متفاوت

AllDifferent کمی گیج کننده است. مشخص می کند که هر سلول در محدوده ای که برای Cell Reference انتخاب می کنید باید تعداد متفاوتی داشته باشد. اما همچنین مشخص می کند که باید بین 1 و تعداد سلول ها باشند. بنابراین اگر سه سلول دارید، در نهایت به اعداد 1، 2 و 3 خواهید رسید (اما نه لزوماً به این ترتیب)

در نهایت مقدار محدودیت را اضافه کنید.

مهم است که به خاطر داشته باشید که می توانید چندین سلول را برای Cell Reference انتخاب کنید. برای مثال، اگر می‌خواهید شش متغیر دارای مقادیر بیش از 10 باشند، می‌توانید همه آنها را انتخاب کنید و به Solver بگویید که باید بزرگ‌تر یا مساوی 11 باشند. لازم نیست برای هر سلول محدودیتی اضافه کنید.

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

یک مثال حل کننده

برای اینکه ببینیم همه اینها چگونه کار می کنند، از افزونه Solver برای محاسبه سریع استفاده می کنیم. در اینجا داده هایی است که ما با آن شروع می کنیم:

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

در اینجا محدودیت هایی وجود دارد که ما استفاده خواهیم کرد:

• هیچ شغلی نمی تواند کمتر از چهار ساعت باشد.
• کار 4 باید بیشتر از 12 ساعت باشد.
• کار 5 باید کمتر از یازده ساعت باشد.
• کل ساعات کار باید برابر با 40 باشد.

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

در اینجا نحوه تنظیم آن در Solver آمده است:

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

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

بیایید Solve را بزنیم و ببینیم چه می شود.

حل کننده راه حلی پیدا کرد! همانطور که در سمت چپ پنجره بالا مشاهده می کنید، درآمد ما 130 دلار افزایش یافته است. و تمام محدودیت ها برآورده شده است.

برای حفظ مقادیر جدید، مطمئن شوید که Keep Solver Solution علامت زده شده است و OK را بزنید.

اگر اطلاعات بیشتری می‌خواهید، می‌توانید گزارشی را از سمت راست پنجره انتخاب کنید. همه گزارش‌هایی را که می‌خواهید انتخاب کنید، به اکسل بگویید که آیا می‌خواهید مشخص شوند یا نه (من آن را توصیه می‌کنم) و OK را بزنید.

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

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

حل گزینه های پیشرفته

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

واضح ترین روش حل است:

شما می توانید بین GRG Nonlinear، Simplex LP و Evolutionary یکی را انتخاب کنید. اکسل توضیح ساده ای در مورد زمان استفاده از هر یک ارائه می دهد. توضیح بهتر نیاز به دانش آماری و رگرسیون اکسل دارد.

برای تنظیم تنظیمات اضافی، فقط دکمه Options را فشار دهید. می توانید به اکسل در مورد بهینه بودن اعداد صحیح بگویید، محدودیت های زمانی محاسبه را تنظیم کنید (مفید برای مجموعه داده های عظیم)، و نحوه انجام محاسبات GRG و روش های حل تکاملی را تنظیم کنید.

باز هم، اگر معنی هر یک از اینها را نمی دانید، نگران آن نباشید. اگر می خواهید بیشتر بدانید که از کدام روش حل استفاده کنید، Engineer Excel مقاله خوبی دارد که آن را برای شما بیان می کند. اگر حداکثر دقت را می‌خواهید، احتمالاً Evolutionary راه خوبی است. فقط توجه داشته باشید که زمان زیادی طول خواهد کشید.

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

اکنون که با اصول اولیه حل متغیرهای ناشناخته در اکسل راحت هستید، دنیای کاملاً جدیدی از محاسبه صفحه گسترده برای شما باز است. Goal Seek می تواند با انجام برخی محاسبات سریعتر به شما در صرفه جویی در زمان کمک کند و Solver قدرت زیادی به توانایی های محاسبه Excel اضافه می کند.

مسئله این است که با آنها راحت شوید. هرچه بیشتر از آنها استفاده کنید، مفیدتر خواهند بود.

منبع مقاله