حل معادله در اکسل – آموزش گام به گام

حل معادله در اکسل

$$\large 2x + 3 = 0$$

منظور از حل این معادله، پیدا کردن مقدار یا مقادیری از $$x$$ است که طرف چپ را به صفر تبدیل کنند. به این ترتیب دو طرف با یکدیگر معادل (برابر) خواهند شد. به چنین مقداری از $$x$$، «ریشه معادله» (Equation Root) نیز گفته می‌شود. واضح است که ریشه این معادله برابر با $$\dfrac{-3}{2}$$ است.

یک معادله خطی به صورت کلی و استاندارد را به شکل زیر نشان می‌دهند. در این حالت $$a$$ را ضریب و $$b$$ را مقدار ثابت می‌نامند.

$$\large ax + b = 0$$

ریشه چنین معادله‌ای به صورت زیر بدست خواهد آمد.

$$\large x = \dfrac{-b}{a}$$

رابطه ۱: ریشه معادله مرتبه یک

$$\large ax^2 + bx + c = 0$$

که در این حالت با توجه به وضعیت و علامت $$\Delta = b^2 - 4ac$$ نسبت به تعداد ریشه‌های حقیقی و نحوه محاسبه آن‌ها تصمیم می‌گیریم.

$$\large \Delta > 0 , \;\; x_1 = \dfrac{-b + \sqrt{\Delta}}{2a} , \;\; x_2 = \dfrac{-b - \sqrt{\Delta}}{2a}$$

$$\large \Delta = 0 , \;\; x_1 = x_2 = \dfrac{-b }{2a}$$

رابطه ۲: ریشه‌های معادله درجه ۲

$$\large x^2 - 1 = 0$$

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

برای آنکه بتوانیم یک معادله خطی (مرتبه اول) را حل کنیم، باید مسئله را به درستی در اکسل وارد کنیم. فرض کنید ریشه معادله خطی زیر مورد نظر است.

$$\large ax + b = 0$$

فیلم آموزش توابع و فرمول‌نویسی در Excel در فرادرس

کلیک کنید

چنین معادله‌ای را باید ابتدا به صورتی در سلول‌ها معرفی کنیم. پارامتری بودن رابطه بالا به ما این کمک را می‌کند که با وارد کردن مقادیر مختلف برای $$a$$ و $$b$$ و همچنین فرمول ریشه این معادله، بتوانیم پاسخ را به سادگی دریافت کنیم.

در تصویر زیر، یک کاربرگ با مقادیر $$a=2, b = 3$$ را مشاهده می‌کنید.

هدف پیدا کردن مقداری از $$x$$‌ است که اگر در سلول C2 نوشته شود، مقدار سلول D2 را صفر می‌کند. یک راه حل می‌تواند مقدار دهی و سعی و خطا باشد. البته این روش، بسیار وقت‌گیر بوده و ممکن است در زمان کوتاه به پاسخ صحیح نیز نرسیم. ولی اگر در سلول C2 فرمول کلی حل معادله خطی را وارد کنیم، به سادگی حل معادله در اکسل بدست خواهد آمد. بنابراین فرمول زیر را در سلول C2 براساس رابطه ۲ وارد می‌کنیم.

$$\large = -B2 / A2$$

به این ترتیب مقدار C2 برابر با ۱٫۵- خواهد بود. تصویر ۲، نتیجه محاسبات ریشه و همچنین مقدار معادله را نشان می‌دهد. همانطور که مشخص است، مقدار عبارت که در سلول D2 نوشته شده به ازاء $$x=1.5$$ برابر با صفر خواهد بود.

به عنوان یک مثال دیگر برای حل معادله در اکسل رابطه زیر را به صورت یک تساوی (Equation) در نظر می‌گیریم. واضح است که مقدار $$a=3 , b = -6$$ است.

$$\large 3x - 6 = 0$$

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

برای حل معادله در اکسل با مرتبه ۲، ابتدا مسئله را معرفی کرده و پارامترهای آن را به عنوان سلول‌ها مشخص می‌کنیم. در تصویر ۴، چنین کاری صورت گرفته است.

حال کافی است که ریشه‌ها را به ترتیب برای $$x_1 , x_2$$ در سلول‌های D2 , D3 مشخص کنیم. برای این کار از رابطه ۲ استفاده کرده و محاسبات را صورت می‌دهیم. در تصویر ۵، این کار انجام شده و ریشه‌های معادله درجه ۲ زیر بدست آمده است. همانطور که انتظار داشتیم، ریشه‌ها برابر با ۱ و ۱- هستند.

$$\large x^2 - 1 = 0$$

این بار معادله‌ای به صورت زیر را در نظر بگیرید. کافی است مقادیر مربوط به پارامترها را که در سلول‌های A2 تا C2 وجود دارند را مقدار دهی کرده تا ریشه‌های معادله بدست آیند.

$$\large 2x^2 + 5x + 2 = 0$$

ریشه‌ها برابر با 0٫۵- و ۲- خواهند بود. به تصویر ۶ توجه کنید. مشخص است که $$a=2, b = 5 , c = 2$$ است.

واضح است که ریشه‌ها را به کمک فرمول زیر بدست آورده‌ایم. به پرانتزها و محل قرارگیر آن‌ها توجه کنید.

 = (-B2 +SQRT(B2^2 -4*A2 *C2)) / (2*A2) 

ریشه دوم نیز به شکل زیر است.

 = (-B2 - SQRT(B2^2- 4*A2*C2)) / (2*A2) 

حل معادله در اکسل با ابزار Goal Seek

البته همیشه این امکان وجود ندارد و ممکن است که مقدار دهی باعث همگرایی در پاسخ نباشد. در این هنگام Goal Seek با پیغام خطا مواجه شده و به کاربر اعلام می‌دارد که به پاسخ مناسب نرسیده است.

$$\large 3x - 6 = 0$$

همانطور که می‌بینید، سه پارامتر یا گزینه در این پنجره وجود دارد.

پارامتر Set cell، به آدرس سلولی اشاره دارد که به عنوان سلول وابسته به کار رفته و مقدار معادله را نشان می‌دهد. بخش To value نیز مقدار مورد نظر برای سلول وابسته را مشخص می‌کند. از آنجایی که ریشه معادله مد نظر است، مقدار سلول وابسته باید صفر شود. بنابراین این پارامتر را ۰ در نظر گرفته‌ایم. آخرین گزینه و مهم‌ترین بخش در Goal Seek، By changing cell است. این گزینه مربوط به سلولی است که به عنوان پیش‌نیاز برای سلول وابسته شناخته شده و قرار است ریشه را نشان دهد.

فیلم آموزش Goal Seek در اکسل در فرادرس

کلیک کنید

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

با فشردن دکمه OK، عملیات مقدار دهی صورت گرفته و در صورت همگرایی به پاسخ، پنجره‌ای مانند زیر ظاهر می‌شود. وجود پاسخ توسط اکسل با عبارت found a solution مشخص شده است.

همانطور که در تصویر ۸ دیده می‌شود، مقدار هدف (Target value) صفر بوده و مقدار جاری (Current value) نیز صفر است. بنابراین به پاسخ رسیده‌ایم. مقدار پاسخ در سلول پیش‌نیاز نمایش داده شده (C2) و صفر بودن سلول وابسته (D2) نیز موید بدست آوردن ریشه معادله است.

نکته: به یاد داشته باشید که برای استفاده از Goal Seek‌ همواره باید یک سلول وابسته و پیش‌نیاز وجود داشته باشد در غیر اینصورت امکان پیدا کردن مقدار هدف برای یک فرمول وجود ندارد.

در گام بعدی به سراغ معادله درجه ۲ خواهیم رفت و سعی می‌کنیم که بوسیله این تکنیک، ریشه (ریشه‌های) معادله زیر را پیدا کنیم.

$$\large 2x^2 + 5x + 2 = 0$$

ابتدا ریشه اول را بدست خواهیم آورد. تنظیم‌های Goal Seek را طبق تصویر 9 انجام می‌دهیم. باز هم می‌بینید که از همان پارامترها یعنی آدرس سلول وابسته، مقدار هدف برای سلول وابسته و آدرس سلول پیش‌نیاز استفاده کرده‌ایم.

پس از فشردن دکمه OK اولین ریشه به این ترتیب حاصل می‌شود. مشخص است که مقدار ریشه باید برابر با ۰٫۵- یا نزدیک به آن باشد.

در تصویر 10 مشاهده می‌کنید که مقدار مورد انتظار برای معادله برابر با صفر است ولی نتیجه تقریبا با صفر برابر (0٫000272) است. بنابراین ریشه مقداری تقریبی است. از طرفی مقدار ریشه نیز در سلول D2 حاصل شده که ۰٫۴۹۹۹- را نشان می‌دهد که باز هم تقریبا با ۰٫۵- برابر است.

برای پیدا کردن ریشه دوم، کمی اکسل را کمک می‌کنیم. به این معنی که حدس اولیه برای ریشه دوم را در سلول D3 می‌نویسیم. اکسل همیشه از مقداری که در سلول پیش‌نیاز معرفی شده به عنوان نقطه شروع استفاده می‌کند. می‌دانیم که ریشه دوم برابر با ۲- است. پس برای مثال مقدار سلول پیش‌نیاز را مثلا ۳- قرار می‌دهیم. سپس با استفاده از Goal Seek به جستجو ریشه می‌پردازیم. این بار اکسل باز هم به صورت تقریبی به ریشه دست پیدا می‌کند.

نتیجه برای سلول وابسته مقدار 0٫000765 (نزدیک به صفر) و مقدار ریشه ۲٫00025- (نزدیک به ۲) است.

نکته: توجه داشته باشید که دو بار فرمول مربوط به معادله درجه ۲ را نوشته‌ایم. یکبار در سلول E2 (برای پیدا کردن ریشه اول) و یکبار هم در سلول E3 (برای پیدا کردن ریشه دوم). بنابراین یکبار سلول D2 و یکبار هم سلول D3 را پیش‌نیاز قرار داده‌ایم.

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

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

به منظور نصب و راه‌اندازی Solver، از فهرست File گزینه Options و قسمت Add-ins را انتخاب و از کادر سمت راست در قسمت Manage گزینه Excel Add-ins را انتخاب کرده و دکمه …Go‌ را کلیک کنید. سپس در پنجره Add-ins گزینه Solver را انتخاب و دکمه OK را بزنید.

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

حل معادله خطی مرتبه یک در اکسل با Solver

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

$$\large 3x - 6 = 0$$

پنجره Solver را باز کرده و تنظیم‌ها را مطابق با تصویر 13 اجرا خواهیم کرد.

مشخص است که بخش Set Objective، به سلولی اشاره می‌کند که تابع هدف (معادله) در آن نوشته شده به همین علت آدرس سلول D2 را برای آن انتخاب کرده‌ایم. از طرفی قرار است مقدار این فرمول به صفر برسد زیرا به ریشه‌های آن احتیاج داریم. بنابراین در قسمت TO گزینه Value of را صفر در نظر می‌گیریم. از طرفی مقداری که ریشه را نشان می‌دهد در سلول C2 قرار گرفته است. در نتیجه با معرفی آن در بخش By changing variable cells تعیین پارامترهای ابزار Solver تکمیل می‌شود.

پس از فشردن دکمه OK، افزونه Solver محاسبات را انجام داده و نتیجه را نشان می‌دهد. به تصویر 14 توجه کنید.

گزینه‌های این پنجره به صورت زیر هستند.

• Keep Solver Solution: با انتخاب این گزینه که به طور پیش‌فرض نیز فعال است، نتیجه حاصل از اجرای Solver در سلول‌ها باقی می‌ماند.
• Restore Original Values: این گزینه مقادیر قبلی سلول‌ها را به قبل از اجرای Solver باز می‌گرداند.
• Reports: این بخش به منظور بررسی نتایج حل مسائل «برنامه‌ریزی خطی» (Linear Programming) اختصاص داشته و تحلیل حساسیت و کران‌های پاسخ را مشخص می‌کند.
• Return to Solver Parameters Dialog: بازگشت به پنجره تعیین پارامترهای Solver را پس از فشردن کلید OK موجب می‌شود.
• Outlier Reports: نقاط پرت یا «دورافتاده» (Outlier) را در گزارش نهایی ارائه می‌کند. البته این مورد برای برازش خط مفید است.

با این کار جواب دقیق برای معادله حاصل شده و در سلول D2 مقدار ۲ و در سلول E2 مقدار صفر را مشاهده می‌کنید. در بخش توضیحات که در کادر پایین پنحره تصویر 16 دیده می‌شود، مشخص شده است که پاسخ (ریشه) پیدا شده و همه قیدهای مربوط به بهینه‌سازی حاصل شده‌اند. البته مشخص است که برای بدست آوردن ریشه معادله خطی، هیچ قیدی در نظر گرفته نشده بود.

حل معادله غیرخطی در اکسل با Solver

در این بخش باز هم به معادله درجه ۲ می‌پردازیم و ریشه‌های آن را به کمک Solver مشخص می‌کنیم. البته از آنجایی که تکنیک Simplex LP، برای معادلات خطی مناسب است، ممکن است هنگام استفاده از آن برای پیدا کردن ریشه معادلات غیر خطی (مانند معادله درجه ۲) به مشکل برخورد کرده و به جواب مناسب نرسیم. به همین دلیل از گزینه GRG Nonlinear برای پیدا کردن ریشه‌ها استفاده خواهیم کرد. معادله زیر را در نظر بگیرید.

$$\large 2x^2 + 5x + 2 = 0$$

فیلم آموزش داشبورد مدیریتی و گزارش‌گری در Excel در فرادرس

کلیک کنید

باز هم برای مشخص کردن این معادله در کاربرگ  اکسل به مانند تصویر 11 استفاده کرده و پارامترهای معادله خطی را به صورت مقادیر درون سلول‌ها نمایش می‌دهیم. در تصویر 17، تنظیم‌هایی که برای معرفی و حل معادله در اکسل با Solver لازم داریم را نمایش داده‌ایم.

همانطور که در این تصویر مشاهده می‌کنید، برای حل معادله در اکسل به کمک Solver، پارامترها و ترتیب تعیین آن‌ها با شماره مشخص شده‌اند. در زمانی که گزینه GRG Nonlinear را انتخاب کردید با فشردن دکمه Options، به پنجره تنظیم‌های تکنیک‌های قابل استفاده در Solver دست پیدا می‌کنید. در تصویر 18 پنجره مناسب برای پارامترهای عمومی و در تصویر 19 پارامترهای مناسب برای روش GRG را مشاهده می‌کنید.

در تصویر ۱۷ می‌بینید که گزینه مقیاس‌بندی خودکار (Use Automatic Scaling) را غیر فعال کرده‌ایم. اگر می‌خواهید نتایج حاصل از جایگذاری و مقدار دهی را در روش GRG مشاهده کنید، گزینه Show Iteration Results را فعال کنید.

نکته: از آنجایی که دو ریشه وجود دارد، تنظیم گزینه‌های مربوط به تصویر ۱۷ و ۱۸ ضروری است. در غیر این صورت ممکن است Solver به جواب بهینه نرسد.

با توجه به اینکه معادله درجه ۲، حداکثر دو ریشه دارد، برای حل معادله در اکسل باید دوبار از Solver استفاده کرده و با پارامترهای یکسان دو جواب برای معادله را بدست آورد. واضح است که معادله دارای ریشه‌هایی برابر با ۲- و ۰٬۵- است. ولی نتیجه به وسیله تکنیک GRG ریشه‌هایی برابر با 1٫999997- و ۰٫۵- را ارائه کرده است. این مقادیر را در تصویر 20 مشاهده می‌کنید.

مشخص است که مقدار سمت چپ معادله نیز به صفر نزدیک شده ولی برابر با نیست. اگر گزینه Answer را در بخش Report (مانند تصویر 16) انتخاب می‌کردید، گزارشی از مراحل کار و نتایج حاصل شده توسط GRG در یک کاربرگ جدید ظاهر می‌شد. در تصویر 21 نمونه‌ای از این گزارش را مشاهده می‌کنید.

در تصویر 21، تابع هدف (Objective Cell) و همچنین سلول‌های پیش‌نیاز (Variable Cells) به همراه مقادیر اولیه (Original Value) و پاسخ نهایی (Final Value) مشخص شده‌اند. مطالعه این گزارش، شما را از زمان اجرای الگوریتم (Solution Time) و همچنین تعداد تکرار (Iterations) آگاه می‌سازد.

خلاصه و جمع‌بندی