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

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

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

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

۱- تابع 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:

تابع محاسبه پارامتر R² در برازش خطی: برای مقایسه و قضاوت در مورد شدت ارتباط بین ضریب همبستگی محاسبه شده در دو یا چند بررسی مختلف، نیازمند اطلاع از مجذور ضریب همبستگی در هر گروه می‌باشیم. تابع 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)

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

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

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

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

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

منابع:

chandoo.org

excelfunctions.net