در نرم‌افزار اکسل علاوه بر ترسیم نمودارهای زیبا و گرافیکی، می‌توانید رابطه‌ی بین داده‌های خروجی و داده‌های ورودی را با ترسیم خط تمایل یا منحنی کالیبراسیون پیدا کنید. یافتن رابطه‌ی خطی بین متغیر X و Y یکی از موضوعاتی است که در علوم مختلف بسیار کاربرد دارد.
در اکسل برای محاسبه کردن شیب خط تمایل از تابع SLOPE و برای یافتن محل برخورد با محور Y که عرض از مبداء گفته می‌شود، از تابع INTERCEPT استفاده می‌شود.
در این مقاله به روش رسم کردن خط یا منحنی که از میان نقاط داده عبور میکند و یافتن معادله‌ی آن در نرم‌افزار Excel مجموعه‌ی آفیس مایکروسافت می‌پردازیم.
آشنایی با منحنی کالیبراسیون و خط تمایل
در علوم مختلف برای کالیبره کردن یا اندازه‌گیری برخی کمیت‌ها و پیدا کردن معادله یا رابطه‌ی بین خروجی و ورودی، از منحنی کالیبراسیون و Trend Line استفاده می‌شود. دو مفهوم متفاوت و در عین حال مشابه. به مثال‌های زیر توجه کنید:
رابطه‌ی طول فنر و وزنه و رابطه‌ی جریان و ولتاژ
با آویزان کردن وزنه‌های مختلف از فنر، طول فنر بیشتر می‌شود یا با افزایش ولتاژ منبع تغذیه، میزان جریان الکتریکی که از سیمی از مدار عبور میکند، بیشتر می‌شود.
برای یافتن رابطه‌ی بین این موارد، نموداری رسم می‌شود که محور افقی یا ورودی، میزان وزنه یا ولتاژ است و محور عمودی یا خروجی، طول فنر یا میزان جریان الکتریکی است. با چند مرتبه تغییر وزنه و ولتاژ منبع تغذیه، چند داده‌ی جدید اندازه‌گیری می‌شود. در نهایت خطی از میان نقاط نمودار عبور می‌دهیم و رابطه‌ی بین ولتاژ و جریان یا طول فنر و وزنه را به صورت تقریبی محاسبه می‌کنیم. به این خط ترند لاین یا خط تمایل گفته می‌شود.
کالیبراسیون ترمومتر دیجیتال با دمای آب
با استفاده از منحنی کالیبراسیون می‌توانید عمل کالیبره کردن را هم انجام بدهید. به عنوان مثال اگر یک دماسنج دیجیتال داشته باشید که دما را با عددی نامفهوم نمایش می‌دهد، می‌توانید جدولی ساده طراحی کنید که دمای مربوط به هر عدد در آن ذکر شده است.
به عنوان مثال می‌توانید عدد مربوط به دمای ترکیب آب و یخ که ۰ درجه‌ی سانتی‌گراد است و عدد مربوط به آب در حال جوشیدن که دمای آن در شرایط استاندارد، ۱۰۰ درجه‌ی سانتی‌گراد است را قرائت کنید، روشن می‌شود که اعداد مابین، هر یک به چه دمایی مربوط می‌شوند. البته این در حالتی است که ترمومتر دیجیتال رفتار خطی داشته باشد. برای یافتن دمای مربوط به اعداد میانی، می‌بایست نقطه‌ی ابتدا و انتها را به هم متصل کنید و به این ترتیب هر نقطه‌ی این خط، یک عدد و دمای مربوط به آن را نشان می‌دهد.
توجه کنید که منحنی کالیبراسیون یا خط تمایل و به عبارت دقیق‌تر، منحنی تمایل، گاهی یک خط ساده است، گاهی منحنی درجه ۲ است، گاهی منحنی لگاریتیمی یا رابطه‌ای نمایی دارد و همین‌طور موارد دیگر. خوشبختانه در تمام حالت‌ها برای ترسیم منحنی و یافتن معادله‌ی آن می‌توانید از نرم‌افزار Excel مجموعه‌ی آفیس استفاده کنید. البته برای منحنی‌های پیچیده و امور تخصصی‌تر، استفاده از نرم‌افزاری مثل MATLAB توصیه می‌شود.
برای استفاده از فرمول منحنی تمایل و یافتن اعداد، می‌توان از توابعی مثل SLOPE و INTERCEPT در اکسل استفاده کرد.
ترسیم نمودار ورودی و خروجی و Trend Line در اکسل
برای توضیح روش کار، یک مثال ساده را بررسی می‌کنیم.. محور x نمودار یا ورودی و محور y یا خروجی را در یک جدول ساده وارد می‌کنیم.
برای رسم کردن نمودار، اعداد سری X را با روش کلیک و درگ، انتخاب کنید. به عبارت دیگر و روی اولین سلول سری X کلیک کرده و موس را تا رسیدن به آخرین سلول، حرکت بدهید و سپس کلید چپ موس را رها کنید.
و سپس کلید Ctrl را نگه دارید و این بار هم با روش کلیک و درگ، سلول‌های سری Y را انتخاب کنید.
اکنون روی تب Insert نوار ابزار بالای صفحه، کلیک کنید.
در بخش Charts یا نمودارها، روی نمودار نقاط یا Scatter کلیک کنید و سپس روی اولین حالت کلیک کنید.
به این ترتیب نمودار نقاط رسم می‌شود.
در صورت نیاز می‌توانید موارد مختلف مثل رنگ، فونت، عنوان نمودار و عنوان سری‌ها و غیره را سفارشی کنید که در ادامه مورد بررسی قرار می‌گیرد.
برای انتخاب سری داده، روی یکی از نقاط نمودار کلیک کنید و نتیجه این است که Excel تمام نقاط سری را انتخاب میکند.
برای اضافه کردن ترند لاین، روی یکی از نقاط نمودار راست‌کلیک کرده و گزینه‌ی Add Trendline را انتخاب کنید.
به این ترتیب خط صافی که از میان نقاط عبور کرده، نمایان می‌شود.
در سمت راست منوی Format Trendline نیز اضافه می‌شود. برای رویت کردن معادله‌ی خط نمودار که در واقع رابطه‌ی بین خروجی و ورودی مسأله است، تیک گزینه‌ی Display Equation on chart را بزنید. از طرف دیگر برای بررسی میزان خطا که یک کمیت آماری است، Display R-squared value on chart را تیک بزنید.
هر چه مقدار R-squared یا R2 به عدد ۱ نزدیک‌تر شود، به این معنی است که خط ترسیم شده، به نقاط نمودار نزدیک‌تر است. طبعاً اگر این عدد به ۰ نزدیک شود، خطا زیاد است و رابطه‌ی بین خروجی و ورودی، چیزی شبیه به معادله‌ی نمایش داده شده نیست.
در اکسل ترسیم منحنی‌های تمایل با معادله‌های مختلف امکان‌پذیر است. همان‌طور که در تصویر فوق مشاهده می‌کنید، منحنی نمایی یا Exponential و حالت لگاریتمی یا Logarithmic و همین‌طور چندجمله‌ای یا Polynomial که درجه‌ی چندجمله‌ای آن در فیلد Order قابل تعریف است، حالت توانی یا Power و پاره خط آزاد یا Moving، همگی قابل انتخاب است.
در این مثال ساده، همان‌طور که مشاهده می‌کنید تقریب رابطه‌ی خطی بین ورودی و خروجی بسیار به واقعیت نزدیک است و لذا مقدار R2 بسیار به ۱ نزدیک است.
حال برای تغییر عنوان نمودار، روی بلوک عنوان کلیک کنید و سپس متن درون آن را انتخاب کنید.
عنوان جدید و مناسب را وارد کنید.
برای اضافه کردن عنوان برای توضیح محور x و y نیز ابتدا روی نمودار کلیک کنید و سپس در نوار ابزار بالای صفحه، روی تب Design در بخش Chart Tools کلیک کنید.
حال روی Add a Chart Element کلیک کنید.
سپس از Axis Titles و Primary Horizontal برای افزودن بلوک نام و توضیحات محور افقی استفاده کنید.
اکنون می‌توانید روی این بلوک جدید کلیک کنید.
و با انتخاب کردن متن و تایپ عنوان و توضیح جدید، محور ایکس را توصیف کنید.
برای افزودن عنوان محور عمودی نیز از منوی Add a Chart Element و گزینه‌ی Axis Titles و سپس Primary Vertical استفاده کنید.
به این ترتیب عنوان محور عمودی نیز نمایان می‌شود.
نام محور عمودی را نیز تغییر بدهید.
اکنون عناوین و معادله‌ی خط، همگی تکمیل شده است. در صورت نیاز می‌توانید فونت بلوک‌های مختلف و رنگ‌ها را نیز تغییر بدهید.
محاسبه‌ی معادله خط Trend Line با توابع SLOPE و INTERCEPT و CORREL
برای انجام محاسبات اضافی، از سلول‌هایی زیر جدول استفاده می‌کنیم. اولین تابع SLOPE است که برای محاسبه کردن شیب ترند لاین کاربرد دارد. این تابع سری داده‌های خروجی و داده‌های ورودی را به عنوان دو آرگومان دریافت میکند و خروجی آن، شیب خط است.
ابتدا سلولی را با کلیک کردن، انتخاب کنید که در این مثال از A15 استفاده می‌کنیم.
سپس در منوی Formulas بالای صفحه، روی More Functions و سپس Statistical کلیک کنید که حاوی توابع آماری است. در لیست توابع اسکرول کنید و روی SLOPE کلیک کنید.
پنجره‌ی تابع موردبحث نمایان می‌شود. در فیلد Known_ys می‌بایست داده‌های خروجی و در فیلد Known_xs می‌بایست داده‌های ورودی را تایپ یا انتخاب کنید. لذا روی اولین فیلد کلیک کنید و سپس با روش ساده‌ی کلیک و درگ، سلول‌های ستون Y را انتخاب کنید.
برای فیلد بعدی هم همین کار را انجام بدهید با این تفاوت که سری X یا سلول‌های وروردی می‌بایست انتخاب شود.
در نهایت روی OK کلیک کنید.
روش دیگر برای فرمول‌نویسی و استفاده از SLOPE، نوشتن فرمول به صورت مستقیم است. کافی است روی سلول موردنظر کلیک کنید و فرمول زیر را تایپ کنید. اولین آرگومان سلول‌های خروجی و دومین آرگومان سلول‌های ورودی است.
=SLOPE(C3:C12,B3:B12)
به این ترتیب شیب خط محاسبه می‌شود.
تابع بعدی برای محاسبه‌ی معادله‌ی خط، تابع INTERCEPT است که نقطه‌ی برخورد خط با محور Y را مشخص میکند. این تابع هم دو آرگومان مشابه تابع SLOPE دارد.
روش فرمول‌نویسی نیز کاملاً مشابه است: ابتدا روی سلول موردنظر کلیک کنید و سپس از مسیر Formulas > More Functions > Statistical > INTERCEPT در نوار ابزار بالای صفحه، تابع INTERCEPT را انتخاب کنید.
در پنجره‌ی نمایش داده شده، روی فیلد محور y و سپس x کلیک کنید و برای هر یک سری داده‌ی مربوطه را انتخاب کنید.
و در نهایت روی OK کلیک کنید.
در مثال مال فرمول زیر در سلول B15 قرار می‌گیرد:
=INTERCEPT(C3:C12,B3:B12)
و نتیجه نقطه‌ی برخورد با محور Y است، که در معادله‌ی خط y = 1.0584x - 0.2313 روی نمودار نیز دیده می‌شود.
آخرین تابع آماری، تابع CORREL است که میزان همبستگی را محاسبه میکند.
برای استفاده از این تابع هم می‌توانید روی سلولی کلیک کرده و سپس از مسیر Formulas > More Functions > Statistical > CORREL، پنجره‌ی تعریف آرگومان‌های تابع را باز کنید.
در ادامه در فیلد Array1 یکی از سری‌ها را انتخاب کنید. تابع همبستگی مثل دو تابع SLOPE و INTERCEPT، تفاوتی بین سری y و x قائل نمی‌شود بلکه صرفاً میزان همبستگی این دو سری داده را محاسبه میکند. لذا می‌توانید جای سری‌ها را جابجا کنید.
در ادامه در فیلد Array2 کلیک کنید و سری داده‌ی دیگر را انتخاب کنید.
روی OK کلیک کنید. فرمول نهایی به این صورت خواهد بود که در این مثال آن را در سلول C15 قرار داده‌ایم:
=CORREL(B3:B12,C3:C12)
خروجی تابع همبستگی می‌بایست به توان ۲ برسد تا عددی مشابه R2 به دست آید. لذا اگر می‌بایست فرمول فوق را کمی تغییر بدهید.
بنابراین روی سلولی که فرمول در آن تایپ شده کلیک کنید و سپس در نوار فرمول بالای صفحه یا داخل سلول، در انتهای فرمول، ^2 را اضافه کنید.
=CORREL(B3:B12,C3:C12)^2
و سپس Enter را فشار دهید.
به این ترتیب آنچه روی نمودار درج شده با محاسبه از طریق تابع CORREL یکسان می‌شود.
محاسبه‌ی خروجی یا Y با وارد کردن ورودی یا X و برعکس
اکنون با مشخص شدن فرمول معادله‌ی خط، می‌توان مقدار خروجی را برای ورودی دلخواه محاسبه کرد یا حالت برعکس را انجام داد و با وارد کردن Y یا خروجی، مقدار ورودی مربوطه یا X را محاسبه کرد.
برای این کار دو جدول ساده ایجاد کنید:
خروجی با رابطه‌ی زیر محاسبه می‌شود:
Y-result = SLOPE * X-entry + INTERCEPT
لذا در سلول Y Result از فرمول فوق استفاده شده است:
و اگر بخواهید X را بر حسب Y محاسبه کنید، فرمول به این صورت خواهد بود:
X-result = (Y-entry-INTERCEPT)/SLOPE
از این فرمول نیز در جدول بعدی برای محاسبه‌ی ایکس استفاده می‌شود:
لذا به راحتی می‌توان ورودی و خروجی موردنظر را محاسبه کرد.
howtogeekسیاره‌ی آی‌تی
سلام. ببخشید در نمودار اکسل چگونه می تونم نمودارهارو تا محور ایکس امتداد بدم؟ یعنی رسم امتداد نمودار و قطع کردن محور ایکس توسط انها
سلام
در تنظیمات نمودار بخش محورها میشه رنج محور ایکس و وای رو به صورت دستی تنظیم کرد. حالت Auto رو غیرفعال کنید و دستی مینیممم ماکسیمم محورها رو وارد کنید.
سلام خسته نباشید
ببخشید میخاستم ببینم اولا چگونه میشه معادل خطی چند بخشی رو با یسری نقاط رسم کرد و اینکه چه جوری میشه منحنی رگرسیونی که گرفتیم رو امتداد بدیم تا محور y رو قطع کنه و مقدار اون رو بدست بیاریم
سلام
در مورد امتداد و در واقع عرض از مبداء یه تیک اون بقل داره که بزنید می‌تونید عرض از مبداء رو دستی وارد کنید اما اگه نکنید، معادله خط رو می‌تونید تیک بزنید که بده و در معادله خط به جای x صفر قرار بدید تا تقاطع با محور y مشخص بشه چنده.
با سلام. من میخوام یه نمودار خطی با اعداد متغیر دارم و یه نمودار خطی راست( در قالب نمودار مبنا یا هدف) . میخواستم ببینم چجوری میتونم محل تلاقی این دو نمودار را بدست بیارم؟