جستجو

ترفندهای کامپیوتری

حضرت علی (ع): «زَکوةُ العِلمِ نَشرُهُ؛ زکات علم نشر آن است».
وبلاگ "ترفندها" (سایت پی‌سی‌ترفند) برای عمل به این حدیث شریف و با هدف ارتقاء دانش و مهارت‌های مرتبط با رایانه در ۳۰ مهرماه ۱۳۹۱ ایجاد شده است. به امید آنکه مفید واقع شود.
ربات پی‌سی‌ترفند در تلگرام:
telegram.me/pctarfand_bot

خلاصه آمار

تبليغات

پربحث ترين ها

۰

توابع کاربردی رگرسیون خطی در اکسل

اکسل

توابع کاربردی رگرسیون خطی در اکسل

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

توابع مورد استفاده برای برازش منحنی را بر اساس نوع Trendline‌ها می‌توان به ۵ دسته تقسیم کرد که در شکل فوق نشان داده شده است. در این مطلب ما قصد داریم این توابع را معرفی کنیم.

معادله منتج از برازش خطی یا Linear بصورت Y=m.x+b است که در آن m، شیب خط و b، عرض از مبدا می‌باشد. به عنوان مثال داده‌های زیر را در نظر بگیرید:

B A  
y x 1
2 1 2
5 2 3
8 4 4
15 9 5
30 13 6

۱- تابع Slope:

محاسبه شیب رگرسیون خطی. که برای مثال فوق شیب خط تقریبا 2.15 می‌باشد.

=SLOPE(Known Y values, Known X values)
=SLOPE(B2:B6,A2:A6) = 2.14981

۲- تابع Intercept:

محاسبه عرض از مبدا رگرسیون خطی. که برای مثال بالا تقریبا 0.469- می‌باشد.

=INTERCEPT(Known Y values, Known X values)
=INTERCEPT(B2:B6,A2:A6) = -0.46887

یعنی با توجه به نتایج توابع Slope و Intercept، معادله رگرسیون خطی مثال فوق برابر است با: 

y = 2.14981*x -0.46887

۳- تابع Forecast:

برای پیش‌بینی y متناظر با یک x جدید (مثلا x=۱۵) بر مبنای رگرسیون خطی.

=FORECAST(New X Value, Known Y values, Known X values)
=FORECAST(15,B2:B6,A2:A6) = 31.77821

۴- تابع Trend:

این تابع مشابه تابع Forrecast است با این تفاوت که این امکان نیز وجود دارد که y متناظر با x جدید را در صورتی که عرض از مبدا برابر با صفر (۰) است پیش‌بینی کند.

=TREND(Known Y Values, Known X Values, New X Value, Const)

ثابت Const می‌تواند دو حالت باشد:

الف) اگر صرف نظر شود یا کلمه True وارد شود که در این حالت عرض از مبدا در نظر گرفته می‌شود، که خروجی آن مشابه تابع Forecast خواهد شد:

=TREND(B2:B6,A2:A6,15) = 31.77821   یا   
=TREND(B2:B6,A2:A6,15,TRUE) = 31.77821

ب) در حالت False، اکسل عرض از مبدا را صفر قرار می‌دهد b=0. یعنی معادله برابر است با y = mx. در اینحالت شیب برابر است با 2.0996.

=TREND(B2:B6,A2:A6,15,FALSE) = 31.49446

۵- تابع Correl:

تابع محاسبه ضریب همبستگی بین دو آرایه: همبستگی نوعی رابطه کمی است که ممکن است بین متغیرهای مختلف وجود داشته باشد. شناخت همبستگی می‌تواند در تحلیل بسیاری از مسائل مالی و اقتصادی و ... راهگشا بوده و امکان قضاوت صحیح و آگاهانه‌ای را فراهم نماید. ضریب همبستگی در محدوده‌ای بین ١ تا ١- بوده (مقدار صفر حاکی از عدم همبستگی می‌باشد) و بیانگر نوع و مقدار (شدت و ضعف) همبستگی بین متغیرهای هر گروه می‌باشد.

=CORREL(Array1,Array2)
=CORREL(B2:B6,A2:A6) = 0.97674

۶- تابع Pearson:

کارکردی مشابه تابع Correl دارد.

=PEARSON(Array1,Array2)

۷- تابع RSQ:

تابع محاسبه پارامتر R2 در برازش خطی: برای مقایسه و قضاوت در مورد شدت ارتباط بین ضریب همبستگی محاسبه شده در دو یا چند بررسی مختلف، نیازمند اطلاع از مجذور ضریب همبستگی در هر گروه می‌باشیم. تابع RSQ مجذور مقدار ضریب همبستگی را باز می‌گرداند. یعنی اگر جواب تابع Correl یا Pearson را به توان دو برسانید با جواب این تابع یکی خواهد بود.

=RSQ(Known Y Values, Known X Values)
=RSQ(B2:B6,A2:A6) = 0.95403 = 0.976742

۸- تابع STEYX:

محاسبه خطای استاندارد Y پیش‌بینی شده.

=STEYX(Known Y Values, Known X Values)
=STEYX(B2:B6,A2:A6) = 2.76243

۹- تابع Linest:

این تابع برای برازش یک مدل رگرسیون خطی ساده و یا خطی چندمتغیره (یک متغیر وابسته با بیش از یک متغیر مستقل) بکار می‌رود.

=LINEST(Known Y Values, Known X Values, Const, Stats)

اگر بخواهید یک برازش خطی برای چند نقطه انجام دهید و شیب، عرض از مبداء و ضریب رگرسیون آن را بدست بیاورید بجای استفاده از سه تابع Slope ،Intercept و RSQ، می‌توان هر سه مورد ذکر شده به علاوه چندین فاکتور دیگر را با استفاده از تابع Linest تعیین کرد. البته امکان استفاده از این تابع بصورت آرایه‌ای نیز وجود دارد.

آرگومان Stats در تابع Linest، مربوط به نمایش ضرایب آماری است و زمانیکه در حالت True باشد نتایج آن ظاهر می‌شود. اگر از این آرگومان صرف نظر شود یا در حالت False باشد خروجی فرمول بستگی به حالات ثابت Const دارد:

الف) اگر از آرگومان Const صرف نظر شود یا برابر با True باشد، در این حالت عرض از مبدا در نظر گرفته می‌شود. چنانچه در اینحالت بصورت معمولی از این تابع استفاده کنیم خروجی آن معادل خروجی تابع Slope خواهد بود.

=LINEST(B2:B6,A2:A6) = 2.14981   یا   
=LINEST(B2:B6,A2:A6,TRUE) = 2.14981

اما اگر بصورت آرایه‌ای استفاده شود، خروجی آن معادل خروجی توابع Slope و Intercept است. (مطابق تصویر زیر)

برای آشنایی با فرمول نویسی آرایه‌ای به این مطلب مراجعه کنید. جهت استفاده از تابع Linest در حالت آرایه‌ای کافی است دو سلول خالی مجاور هم را انتخاب کنید و پس از نوشتن فرمول فوق کلیدهای Ctrl+Shift+Enter را بفشارید.

ب) در حالت False، اکسل عرض از مبدا را صفر قرار می‌دهد b=0. یعنی معادله برابر است با y = mx. بنابراین خروجی فرمول برابر است با شیب منحنی خطی برازش شده در حالتی که عرض از مبدا صفر باشد.

=LINEST(B2:B6,A2:A6,FALSE) = 2.100

دو حالت فوق در صورتی است که ثابت Stats در نظر گرفته نشود یا False باشد. اما اگر این ثابت True‌ باشد خروجی آن در حالت آرایه‌ای (۵ سطر دوتایی را انتخاب کنید و پس از نوشتن فرمول زیر کلیدهای Ctrl+Shift+Enter را بفشارید)، مطابق تصویر زیر می‌شود:

=LINEST(B2:B6,A2:A6,TRUE,TRUE)

اعداد نمایش داده شده در تصویر فوق فاکتورهایی هستند که در جدول زیر نشان داده شده است:

جدول ۱
m b
sem seb
r2 sey
F df
ssreg ssresid

فاکتورهای فوق در جدول زیر توضیح داده شده است:

جدول ۲
m
مقدار شیب (m)
b
مقدار عرض از مبداء (b)
sem
مقدار خطای استاندارد شیب (m)
seb

مقدار خطای استاندارد عرض از مبداء (b)

(خروجی N/A# در صورتیکه ثابت [const] برابر با FALSE باشد.)

r2
مقدار پارامتر R2
sey

محاسبه خطای استاندارد Y پیش‌بینی شده.

F

پارامتر F که بیانگر این است که آیا رابطه‌ای بین xها و yها وجود دارد یا به طور شانسی ایجاد شده است.

df
تعداد درجه آزادی
ssreg

مجموع مربعات رگرسیون

ssresid
مجموع مربعات باقیمانده‌ها

هر یک از فاکتورهای محاسبه شده در جدول فوق را می‌توان بدون استفاده از فرمول‌نویسی آرایه‌ای هم محاسبه کرد برای اینکار باید از تابع Index استفاده کرد که در این مطلب معرفی شده است. برای تعیین هر فاکتور باید شماره ردیف و ستون آن فاکتور را در جدول ۱ دانست. مثلا فاکتور r2 در ردیف ۳، ستون ۱ قرار دارد بنابراین:

=INDEX(LINEST(B2:B6,A2:A6, TRUE, TRUE), 3 , 1) = 0.95403

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

منابع:

chandoo.org

excelfunctions.net

 

عضویت در خبرنامه:

نظرات (۰)
هیچ نظری هنوز ثبت نشده است

ارسال نظر

قبل از ارسال نظر به نکات زیر توجه کنید:

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

۲- قبل از مطرح کردن سوالتان، مطلب فوق را با دقت مطالعه کنید. اگر پاسخ سوالتان را پیدا نکردید در سایت جستجو کنید، ممکن است در مطلب دیگر پاسخ سوالتان را پیدا کنید.

۳- تنها به سوالاتی پاسخ داده می‌شود که از نحوه حل آن‌ها اطلاع داشته باشم.

۴- نظراتی با مضمون زیر، صرفا تایید و نمایش داده می‌شود و به آن‌ها پاسخی داده نمی‌شود.

الف) در مورد پاسخ سوال شما اطلاعی نداشته باشم،

ب) پاسخ سوال شما در مطلب فوق وجود داشته باشد.

ارسال نظر آزاد است، اما اگر قبلا در بیان ثبت نام کرده اید می توانید ابتدا وارد شوید.
شما میتوانید از این تگهای html استفاده کنید:
<b> یا <strong>، <em> یا <i>، <u>، <strike> یا <s>، <sup>، <sub>، <blockquote>، <code>، <pre>، <hr>، <br>، <p>، <a href="" title="">، <span style="">، <div align="">
تجدید کد امنیتی
X بستن