شرح تابع LINEST

درجه اهمیت تابع: کاربردی

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

=LINEST( آماره, ثابت خطی, مجموعه داده های مستقل, مجموعه داده های وابسته  )

ورودی های این تابع به شرح زیر هستند

مجموعه داده های وابسته:  یک بازه از سلول ها (یک آرایه) حاوی اعداد است که نمایانگر مقادیر مشاهده شده برای متغیر وابسته y است.

مجموعه داده های مستقل:  یک ورودی اختیاری از یک بازه از سلول ها (یک آرایه) حاوی اعداد است که نمایانگر مقادیر مشاهده شده برای متغیر مستقل x است. در صورتی که آرایه تنها یک ستون داشته باشد، آنگاه رگرسیون خطی یک متغیره و در صورتی که بیش از یک ستون برای آرایه لحاظ شده باشد، رگرسیون خطی چند متغیره در نظر گرفته خواهد شد.

ثابت خطی : یک ورودی اختیاری است که داده منطقی می پذیرد. در صورتی که ورودی برابر با True (یا مقدار یک) در نظر گرفته شود، آنگاه مقدار ثابت در رگرسیون خطی (عدد ثابتی که در انتخاب معادله خط رگرسیون وجود دارد) به صورت عادی محاسبه خواهد شد و در صورتی که این ورودی False (برابر با مقدار صفر) قرار داده شود آنگاه عدد ثابت در معادله خط صفر در نظر گرفته خواهد شد. در صورتی که ورودی تعیین نشود به صورت پیش فرض True در نظر گرفته خواهد شد.

آماره: یک ورودیی اختیاری است که داده منطقی می پذیرد. در صورتی که ورودی برابر با False (یا عدد صفر) در نظر گرفته شود آنگاه تنها شیب‌های خط برای متغیرهای مستقل بازگردانده خواهد شد (آماره های بیشتر ارائه نخواهد شد) و در صورتی که برابر با True (یا عدد یک) در نظر گرفته شود، آنگاه به همراه مقادیر مربوط به شیب متغیرهای مستقل در برازش خطی، آماره های بیشتر در خصوص رگرسیون خطی نیز ارائه خواهد شد. در صورتی که تعیین نشود به صورت پیش فرض False در نظر گرفته خواهد شد.

 

آنچه که تابع باز می گرداند

تابع LINEST یک آرایه از اعداد مربوط به اطلاعات آماری رگرسیون خطی چند متغیره را باز می گرداند که شامل موارد زیر است.

m_i : شیب مربوط به متغیر مستقل i ام است (بسته به تعداد متغیرهای مستقل، i از یک ۱ الی n متغیر می تواند باشد)

b : مقدار عدد ثابت معادله خط رگرسیون (زمانی تمامی متغیرهای مستقل برابر با صفر در نظر گرفته شود، آنگاه مقدار متغیر وابسته برابر با b خواهد شد)

se_{i :} مقادیر خطای استاندارد برای ضرایب شیب متغیرهای مستقل (m ها)

Seb : مقدار خطای استاندارد برای ثابت b (در صورتی که ورودی ثابت خطی برابر با False در نظر گرفته شود، آنگاه خطای #NA را برای این آماره باز خواهد گرداند)

r2 : مقدار ضریب تعیین (Coefficient of determination) مربوط به معادله رگرسیون خطی

Se_y : انحراف استاندارد برای تخمین y

F : آماره F یا مقدار F – مشاهده شده

df : تعداد درجات آزادی

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

SSresid : مجموع مربعات خطاهای پیش بینی (Residual sum of squares)

توجه شود که خروجی های این تابع آرایه ای به صورت یک ماتریس است که در مثال تشریح شده است.

نکات

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

• 
• این تابع یک مجموعه از اعداد که به صورت آرایه هستند را باز می گرداند، بنابراین باید این تابع را به صورت آرایه ای نوشت. برای نوشتن توابع آرایه ای، ابتدا بایست محدوده خروجی (تعداد سلول هایی که می خواهید در آن خروجی ها قرار داده شوند) را انتخاب کنید، سپس در ریبون فرمول نویسی، فرمول مربوطه را نوشته و در نهایت سه کلید Ctrl + shift + Enter را همزمان بزنید. پس از آن مشاهده خواهید کرد که تابع به صورت خودکار در درون یک آکولاد باز و بسته قرار داده شده است و خروجی های آن در سلول های انتخابی قرار داده شده اند.

• برای دانلود مجموعه کامل راهنمای توابع به همراه فایل های مثال در اکسل به لینک کتاب «راهنما و مثال های توابع مایکروسافت اکسل» رجوع شود.

مثال های تابع LINEST

مثال ۱

در زیر ما از این تابع استفاده مقادیر آماره های مختلف مربوط به معادله خط رگرسیونی برازش شده مقادیر متغیر وابسته (y) بر سه متغیر مستقل x1 ، x2 و x3 را مشاهده می کنیم. توجه شود که خروجی ایت تابع یک ماتریس است و ما در ستون G مقادیر مربوط به این ماتریس را مرتب شده نشان داده ایم تا بهتر درک شود.

 

برای یادگیری نرم افزار اکسل به صورت کاربردی به دوره «آموزشی اکسل کاربردی» رجوع شود.

برای یادگیری برنامه نویسی به زبان VBA به دوره «آموزش VBA در اکسل» مراجعه نمایید.

خطاهای معمول

#REF! : در صورتی که اندازه مجموعه داده ها باهم برابر نباشند.

#VALUE! : در صورتی که مقادیر در مجموعه داده ها غیر عددی باشند یا برای ورودی های «ثابت خطی» و «آماره» داده منطقی صحیح یا غلط قرار داده نشود.