راه حل سیستم معادلات در مایکروسافت اکسل
توانایی حل معادلات سیستم اغلب می تواند مفید باشد نه تنها در مدرسه، بلکه در عمل. در عین حال، هر کاربر کامپیوتر نمی داند که اکسل راه حل های خاصی برای معادلات خطی دارد. بیایید نحوه استفاده از این ابزار پردازشگر جدولی را برای انجام این کار به روشهای مختلف پیدا کنیم.
محتوا
راه حل ها
هر معادله تنها زمانی حل می شود که ریشه هایش پیدا شود. در اکسل چندین گزینه برای یافتن ریشه وجود دارد. بیایید به هر کدام از آنها نگاه کنیم.
روش 1: روش ماتریس
شایع ترین روش برای حل یک معادله خطی با ابزار اکسل، استفاده از روش ماتریس است. این شامل ایجاد یک ماتریس از ضرایب عبارات و سپس ایجاد یک ماتریس معکوس است. بیایید سعی کنیم از این روش برای حل معادلات زیر استفاده کنیم:
14 x1 +2 x2 +8 x4 =218
7 x1 -3 x2 +5 x3 +12 x4 =213
5 x1 + x2 -2 x3 +4 x4 =83
6 x1 +2 x2 + x3 -3 x4 =21
- ما ماتریس را با اعداد که ضرایب معادله هستند پر می کنیم. این اعداد باید به ترتیب به ترتیب مرتب شوند، با توجه به محل هر ریشه که با آن مطابقت دارند. اگر در برخی از بیان یکی از ریشه ها از دست رفته باشد، در این صورت ضریب برابر با صفر است. اگر ضریب در معادله نشان داده نشده است، اما ریشه مربوطه در آن وجود دارد، در نظر گرفته شده است که ضریب برابر با 1 است . ما جدول نتیجه را به عنوان بردار A نشان می دهیم .
- به طور جداگانه، ارزش ها را بعد از علامت برابر می نویسیم. ما آنها را با نام مشترک به عنوان بردار ب نامگذاری می کنیم .
- برای پیدا کردن ریشه های معادله، ابتدا باید معکوس را از ماتریس موجود پیدا کنیم. خوشبختانه در اکسل یک اپراتور ویژه وجود دارد که برای حل این مشکل طراحی شده است. این نام MBR است . این یک نحو نسبتا ساده دارد:
=МОБР(массив)استدلال آرایه ، در واقع، آدرس جدول منبع است.
بنابراین، بر روی ورق یک منطقه از سلول های خالی را انتخاب می کنیم، که برابر با اندازه ماتریس اصلی است. با کلیک بر روی دکمه "درج تابع" ، در نزدیکی نوار فرمول واقع شده است.
- اجرای جادوگر تابع به رده "ریاضی" بروید . در لیست ما به دنبال نام "MOBR" هستیم . پس از پیدا کردن آن، آن را انتخاب کنید و بر روی دکمه "OK" کلیک کنید.
- پنجره استدلال عملکرد MOBR راه اندازی شده است. این تنها یک فیلد با تعداد استدلالها دارد - "Array" . در اینجا شما باید آدرس جدول ما را مشخص کنید. برای این منظور، مکان نما را در این زمینه قرار دهید. سپس دکمه سمت چپ ماوس را نگه دارید و منطقه را روی ورق که در آن ماتریس واقع شده است انتخاب کنید. همانطور که می بینید، داده ها در مختصات مکان به طور خودکار در قسمت پنجره وارد می شوند. پس از اتمام این وظیفه، بیشترین آشکار بودن آن بر روی دکمه «OK» است ، اما عجله نداشته باشید. واقعیت این است که فشار دادن این دکمه معادل استفاده از دستور Enter است . اما هنگام کار با آرایه ها پس از اتمام ورودی یک فرمول، نباید بر روی دکمه Enter کلیک کنید، بلکه یک میانبر صفحهکلید Ctrl + Shift + Enter را ایجاد کنید . این عمل را انجام دهید.
- بنابراین، بعد از این، برنامه محاسبات را انجام می دهد و در خروجی در منطقه قبلا انتخاب شده معکوس از ماتریس دارد.
- حالا ما باید ماتریس معکوس را با ماتریس B بسط دهیم که شامل یک ستون از مقادیر است که پس از علامت برابر در عبارات قرار دارد. برای ضرب جداول در اکسل، یک تابع جداگانه به نام MUMM وجود دارد . این بیانیه دارای نحو زیر است:
=МУМНОЖ(Массив1;Массив2)محدوده را انتخاب کنید، در مورد ما شامل چهار سلول است. سپس با کلیک کردن بر روی نماد "Insert Function" ، جادوگر تابع را اجرا کنید .
- در بخش "ریاضیات" کارشناسی ارشد توابع ، نام "MUMNAGE" را انتخاب کرده و روی دکمه "OK" کلیک کنید.
- پنجره آرگومان تابع فعال شده است . MUMMY . در قسمت "Array1" ما مختصات ماتریس معکوس را وارد می کنیم. برای انجام این کار، مانند زمان گذشته، مکان نما را در حوزه قرار دهید و دکمه سمت چپ ماوس را پایین نگه دارید، جدول مربوطه با مکان نما را انتخاب کنید. ما یک عمل مشابه برای اضافه کردن مختصات در فیلد "Array2" انجام می دهیم ، فقط این بار ما مقادیر ستون B را انتخاب می کنیم . پس از انجام مراحل فوق، ما عجله نداریم تا دکمه "OK" یا کلید Enter را فشار داده و Ctrl + Shift + Enter را تایپ کنید .
- پس از این عمل، ریشه های معادله در سلول قبلا انتخاب شده نمایش داده می شود: X1 ، X2 ، X3 و X4 . آنها در مجموعه ای مرتب می شوند. بنابراین می توان گفت که ما این سیستم را حل کرده ایم. به منظور بررسی صحیح راه حل، کافی است که پاسخهای داده شده را به جای ریشه های مربوطه جایگزین کنید. اگر برابری مشاهده شود، این بدان معنی است که سیستم ارائه معادلات به درستی حل شده است.
روش 2: انتخاب پارامترها
دومین روش شناخته شده برای حل معادلات سیستم در Excel، استفاده از روش انتخاب پارامتر است. ماهیت این روش جستجوی مخالف است. یعنی، بر اساس یک نتیجه شناخته شده، ما برای یک استدلال نامعلوم جستجو می کنیم. به عنوان مثال، از معادله درجه دوم استفاده کنیم.
3x^2+4x-132=0
- مقدار x برابر با 0 باشد. مقدار مربوطه f (x) را با استفاده از فرمول زیر محاسبه کنید:
=3*x^2+4*x-132به جای مقدار "X"، آدرس سلول را که در آنجا عدد 0 قرار دارد جایگزین می کند، که ما برای x آن را جایگزین می کنیم .
- به برگه "Data" بروید. با کلیک بر روی دکمه "تجزیه و تحلیل از" چه اگر " . " این دکمه بر روی روبان در جعبه ابزار "کار با داده" قرار گرفته است. یک لیست کشویی باز می شود. ما در آن موقعیت "انتخاب پارامتر ..." را انتخاب می کنیم .
- پنجره انتخاب پارامتر شروع می شود. همانطور که می بینید، از سه زمینه تشکیل شده است. در فیلد "Set in a cell" ، آدرس سلولی که فرمول f (x) قرار دارد را نشان می دهد ، که ما کمی قبل از آن محاسبه کردیم. در فیلد "Value" شماره "0" را وارد کنید. در قسمت "تغییر ارزش ها"، آدرس سلول که در آن مقدار x واقع شده است، قبلا به عنوان 0 نشان داده شده است . پس از انجام این اقدامات، بر روی دکمه "OK" کلیک کنید.
- پس از آن، اکسل یک محاسبه با استفاده از انتخاب پارامتر را انجام می دهد. این پنجره اطلاعات ظاهر را اعلام می کند. باید بر روی دکمه "OK" کلیک کنید.
- نتيجه محاسبه ريشه معادله در سلولي است که ما در حوزه "تغيير ارزش ها" اختصاص داده ايم. در مورد ما، همانطور که می بینیم، x برابر با 6 است .
این نتیجه همچنین می تواند با جایگزینی این مقدار در عبارت حل شده به جای مقدار x بررسی شود .
روش 3: روش کرامر
اکنون سعی خواهیم کرد سیستم معادلات را با روش کرامر حل کنیم. به عنوان مثال، اجازه دهید همان سیستم را که در روش 1 استفاده شد
14 x1 +2 x2 +8 x4 =218
7 x1 -3 x2 +5 x3 +12 x4 =213
5 x1 + x2 -2 x3 +4 x4 =83
6 x1 +2 x2 + x3 -3 x4 =21
- همانطور که در روش اول ما یک ماتریس A را از ضرایب معادلات و یک جدول B از مقادیری که پس از علامت برابر ظاهر می شود تشکیل می دهیم.
- علاوه بر این ما چهار جدول دیگر را انجام می دهیم. هر یک از آنها یک کپی از ماتریس A است ، فقط برای این نسخه ها، یک ستون به نوبه خود با یک جدول B جایگزین می شود . در جدول اول اولین ستون است، در جدول دوم دوم است و غیره.
- حالا ما باید عوامل تعیین کننده برای همه جداول را محاسبه کنیم. سیستم معادلات فقط اگر همه تعیین کننده ها دارای مقدار دیگری از صفر باشند، راه حل خواهند داشت. برای محاسبه این مقدار در اکسل یک تابع جداگانه وجود دارد - MEPRED . نحو این بیانیه به شرح زیر است:
=МОПРЕД(массив)بنابراین، همانطور که با عملکرد MOBR ، تنها استدلال مرجع جدول پردازش شده است.
بنابراین، سلول را انتخاب کنید که تعیین کننده ماتریس اول نمایش داده می شود. سپس روی دکمه "Insert function" آشنا از روش های قبلی کلیک کنید.
- جادوگر تابع فعال شده است. ما به دسته "ریاضی" تبدیل می کنیم و در میان لیست اپراتورهای ما نام "MOPRED" را در آنجا انتخاب می کنیم . پس از آن، بر روی دکمه "OK" کلیک کنید.
- پنجره arguments از تابع MOPRED راه اندازی شده است . همانطور که می بینید، تنها یک فیلد وجود دارد - "آرایه" . در این زمینه آدرس اولین ماتریس تبدیل شده را وارد کنید. برای انجام این کار، مکان نما را در قسمت تنظیم کنید و سپس محدوده ماتریس را انتخاب کنید. پس از آن، بر روی دکمه "OK" کلیک کنید. این تابع نتیجه را در یک سلول تک، به جای یک آرایه نمایش می دهد، بنابراین برای محاسبه ای که لازم نیست به استفاده از کلید ترکیبی Ctrl + Shift + Enter برسید .
- تابع نتیجه را محاسبه و آن را در یک سلول پیش انتخاب شده نمایش می دهد. همان طور که می بینیم، در مورد ما، تعیین کننده است -740 ، یعنی، آن صفر است که برای ما مناسب است.
- به طور مشابه، عوامل دیگری را برای سه جدول دیگر محاسبه می کنیم.
- در مرحله نهایی، تعیین کننده ماتریس اولیه محاسبه می شود. این الگوریتم همان الگوریتم است. همانطور که می بینیم، تعیین کننده جدول اولیه نیز غیر صفر است، به این معنی که ماتریس در نظر گرفته نشده غیر تولید شده است، یعنی سیستم معادلات دارای راه حل است.
- اکنون وقت آن ریشه های معادله است. ریشه معادله برابر با نسبت تعیین کننده ماتریس تبدیل شده مربوط به تعیین کننده جدول اولیه است. به این ترتیب، هر یک از چهار عامل تعیین کننده ماتریس های تبدیل شده توسط شماره -148 ، که تعیین کننده اصلی جدول است، تقسیم می شود، چهار ریشه را می گیریم. همانطور که می بینید، آنها برابر با مقادیر 5 ، 14 ، 8 و 15 هستند . بنابراین، آنها دقیقا با ریشه هایی که ما با استفاده از ماتریس معکوس در روش 1 پیدا کردیم ، درستی راه حل سیستم معادلات را تایید می کنیم.
روش 4: روش گاوس
سیستم معادلات نیز با استفاده از روش گاوس حل می شود. به عنوان مثال، اجازه دهید یک معادلات ساده تر از سه ناشناخته را بیابیم:
14 x1 +2 x2 +8 x3 =110
7 x1 -3 x2 +5 x3 =32
5 x1 + x2 -2 x3 =17
- مجددا ضریبهای جدول A را ضبط می کنیم و بعد از علامت برابر در جدول B قرار می گیرد. اما این بار ما هر دو جداول را به هم متصل می کنیم، از آنجایی که ما برای کار بیشتر نیاز داریم. یک وضعیت مهم این است که در سلول اول ماتریس A مقدار غیر صفر است. در مورد مخالف، شما باید خطوط را دوباره مرتب کنید.
- ردیف اول دو ماتریس متصل را به خط زیر کپی کنید (برای وضوح، شما می توانید یک ردیف را رد کنید). در سلول اول که در خط حتی پایین تر از قبل قرار دارد، فرمول زیر را وارد کنید:
=B8:E8-$B$7:$E$7*(B8/$B$7)اگر ماتریس ها را به صورت متفاوتی مرتب کنید، آدرس سلول های فرمول شما معنای دیگری خواهد داشت، اما شما می توانید با مقایسه آنها با فرمول ها و تصاویری که در اینجا ارائه شده است، آنها را محاسبه کنید.
پس از وارد شدن فرمول، کل ردیف سلول ها را انتخاب کنید و ترکیبی کلید Ctrl + Shift + Enter را فشار دهید . فرمول آرایه به ردیف اعمال خواهد شد و با مقادیر پر خواهد شد. به این ترتیب، از خط دوم اول از ضریب اول دو عبار اول سیستم ضرب شدیم.
- پس از آن، رشته حاصل را کپی کنید و آن را در خط زیر قرار دهید.
- دو خط اول بعد از خط گمشده را انتخاب کنید. بر روی دکمه "کپی" که روی نوار در تب "Home" قرار دارد کلیک کنید.
- بعد از آخرین ورودی روی صفحه، خط را پر می کنیم. اولین سلول در خط بعدی را انتخاب کنید. با کلیک بر روی دکمه راست موس کلیک کنید. در منوی زمینه باز، ماوس را بر روی مورد "Paste Special" قرار می دهیم . در لیست اضافی در حال اجرا، گزینه "ارزش ها" را انتخاب کنید.
- در خط بعدی، فرمول آرایه را وارد کنید. این ردیف دوم را از ردیف سوم گروه داده های قبلی حذف می کند، ضرب در نسبت ضریب دوم ردیف سوم و دوم. در مورد ما فرمول زیر خواهد بود:
=B13:E13-$B$12:$E$12*(C13/$C$12)پس از وارد شدن به فرمول، کل ردیف را انتخاب کنید و از میانبر صفحه کلید Ctrl + Shift + Enter استفاده کنید .
- اکنون لازم است که معکوس را با توجه به روش گاوس اجرا کنیم. سه خط از آخرین ورود را امتحان کنید. در خط چهارم، فرمول آرایه را وارد کنید:
=B17:E17/D17بنابراین، آخرین ردیف ما را به ضریب سوم آن تقسیم می کنیم. پس از تایپ فرمول، کل خط را انتخاب کنید و ترکیبی کلید Ctrl + Shift + Enter را فشار دهید .
- ما خط را بالا میبریم و فرمول زیر را به آن اضافه میکنیم:
=(B16:E16-B21:E21*D16)/C16ما برای استفاده از فرمول آرایه، کلید ترکیبی معمول را فشار می دهیم.
- ما یک خط دیگر را بالا می بریم. در اینجا فرمول آرایه فرم زیر را وارد می کنیم:
=(B15:E15-B20:E20*C15-B21:E21*D15)/B15باز هم، تمام خط را انتخاب کنید و از میانبر صفحه کلید Ctrl + Shift + Enter استفاده کنید .
- در حال حاضر ما به اعدادی که در آخرین ستون آخرین بلوک ردیف آمده اند، که توسط ما محاسبه شده است نگاه می کنیم. این شماره ها ( 4 ، 7 و 5 ) ریشه های این سیستم معادلات خواهد بود. شما می توانید این را با جایگزینی آنها با مقادیر X1 ، X2 و X3 در عبارات بررسی کنید.
همانطور که می بینید، در Excel، سیستم معادلات را می توان در تعدادی از راه حل حل کرد، که هر کدام دارای مزایا و معایب خاص خود است. اما همه این روش ها را می توان به دو گروه بزرگ تقسیم کرد: ماتریس و با استفاده از ابزار انتخاب پارامتر. در برخی موارد، روش های ماتریس همیشه برای حل مشکل مناسب نیستند. به طور خاص وقتی تعیین کننده ماتریس صفر است. در موارد دیگر، کاربر می تواند تصمیم بگیرد که کدام گزینه برای او مناسب تر باشد.