نحوه رسم نمودار رگرسیون چندگانه در اکسل رگرسیون در اکسل

ساخت یک رگرسیون خطی، تخمین پارامترهای آن و اهمیت آنها در هنگام استفاده از بسته تحلیل اکسل (رگرسیون) بسیار سریعتر انجام می شود. اجازه دهید تفسیر نتایج به دست آمده را در حالت کلی در نظر بگیریم ( کمتغیرهای توضیحی) مطابق مثال 3.6.

جدول آمار رگرسیونمقادیر داده شده است:

چندگانه آر – ضریب همبستگی چندگانه؛

آر- مربع– ضریب تعیین آر 2 ;

عادی شده است آر - مربع- تنظیم شده آر 2 تنظیم شده برای تعداد درجات آزادی؛

خطای استانداردخطای استاندارد رگرسیون است اس;

مشاهدات -تعداد مشاهدات n.

جدول تحلیل واریانسداده شده:

1. ستون df - تعداد درجات آزادی برابر است

برای رشته پسرفت df = ک;

برای رشته باقی ماندهdf = n – ک – 1;

برای رشته جمعdf = n– 1.

2. ستون SS-مجموع مجذور انحرافات، برابر است

برای رشته پسرفت ;

برای رشته باقی مانده ;

برای رشته جمع .

3. ستون ام‌اسواریانس های تعیین شده توسط فرمول ام‌اس = اس اس/df:

برای رشته پسرفت- واریانس عاملی؛

برای رشته باقی ماندهواریانس باقیمانده است.

4. ستون اف - ارزش محاسبه شده اف- معیارهای محاسبه شده با فرمول

اف = ام‌اس(پسرفت)/ ام‌اس(باقی مانده).

5. ستون اهمیت اف مقدار سطح معنی داری مربوط به محاسبه شده است اف-آمار .

اهمیت اف= FRIST( F-آمار، df(پسرفت)، df(باقی مانده)).

اگر اهمیت دارد اف < стандартного уровня значимости, то آر 2 از نظر آماری معنی دار است.

این جدول نشان می دهد:

1. شانس- مقادیر ضرایب آ, ب.

2. خطای استانداردخطاهای استاندارد ضرایب رگرسیون هستند S a, Sb.

3. t-آمار- مقادیر محاسبه شده تی - معیارهای محاسبه شده با فرمول:

t-statistic = ضرایب / خطای استاندارد.

4.آر-ارزش (اهمیت تی) مقدار سطح معنی داری مربوط به محاسبه شده است t-آمار.

آر-ارزش = استودراسپ(تی-آمار، df(باقی مانده)).

اگر آر-معنی< стандартного уровня значимости, то соответствующий коэффициент статистически значим.

5. 95% پایین و 95% بالامرزهای پایین و بالای فاصله اطمینان 95 درصد برای ضرایب معادله رگرسیون خطی نظری هستند.

جدول انصراف باقی ماندهنشان داد:

در یک ستون مشاهده- شماره مشاهده؛

در یک ستون پیش بینی کرد y مقادیر محاسبه شده متغیر وابسته هستند.

در یک ستون باقی ه تفاوت بین مقادیر مشاهده شده و محاسبه شده متغیر وابسته است.

مثال 3.6.داده های موجود (واحدهای عربی) در مورد هزینه های غذا yو درآمد سرانه ایکسبرای نه گروه از خانواده ها:

با استفاده از نتایج بسته تحلیل اکسل (رگرسیون)، وابستگی هزینه های مواد غذایی به ارزش درآمد سرانه را تجزیه و تحلیل می کنیم.

نتایج تحلیل رگرسیون معمولاً به صورت زیر نوشته می شود:

که در داخل پرانتز خطاهای استاندارد ضرایب رگرسیون وجود دارد.

ضرایب رگرسیون آ = 65,92 و ب= 0.107. جهت ارتباط بین yو ایکسعلامت ضریب رگرسیون را تعیین می کند ب= 0.107، یعنی رابطه مستقیم و مثبت است. ضریب ب 0.107 = نشان می دهد که با افزایش درآمد سرانه 1 ارب. واحدها هزینه های غذا 0.107 تبدیل افزایش می یابد. واحدها

اجازه دهید اهمیت ضرایب مدل به دست آمده را تخمین بزنیم. اهمیت ضرایب ( الف، ب) در مقابل بررسی می شود تی- تست:

مقدار p ( آ) = 0,00080 < 0,01 < 0,05

مقدار p ( ب) = 0,00016 < 0,01 < 0,05,

از این رو ضرایب ( الف، ب) در سطح 1% و حتی بیشتر از آن در سطح 5% معنی دار هستند. بنابراین، ضرایب رگرسیون معنی دار بوده و مدل برای داده های اصلی مناسب است.

نتایج تخمین رگرسیون نه تنها با مقادیر بدست آمده از ضرایب رگرسیون، بلکه با مقداری از مجموعه آنها (فاصله اطمینان) نیز سازگار است. با احتمال 95 درصد، فواصل اطمینان برای ضرایب (38.16 - 93.68) برای آو (0.0728 - 0.142) برای ب

کیفیت مدل با ضریب تعیین ارزیابی می شود آر 2 .

ارزش آر 2 = 0.884 به این معنی است که ضریب درآمد سرانه می تواند 88.4 درصد از تغییرات (پراکندگی) در مخارج مواد غذایی را توضیح دهد.

اهمیت آر 2 توسط F-آزمون: اهمیت اف = 0,00016 < 0,01 < 0,05, следовательно, آر 2 در سطح 1% و حتی بیشتر از آن در سطح 5% معنی دار است.

در مورد رگرسیون خطی زوجی، ضریب همبستگی را می توان به صورت تعریف کرد . مقدار به دست آمده از ضریب همبستگی نشان می دهد که رابطه بین هزینه های غذایی و درآمد سرانه بسیار نزدیک است.

28 اکتبر

عصر بخیر، خوانندگان عزیز وبلاگ! امروز در مورد رگرسیون های غیر خطی صحبت خواهیم کرد. حل رگرسیون های خطی را می توان در لینک مشاهده کرد.

این روش عمدتاً در مدل سازی و پیش بینی اقتصادی استفاده می شود. هدف آن مشاهده و شناسایی رابطه بین دو شاخص است.

انواع اصلی رگرسیون های غیر خطی عبارتند از:

• چند جمله ای (دومتر، مکعب)؛
• هذلولی
• قدرت؛
• تظاهرات؛
• لگاریتمی

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

در پیش‌بینی با استفاده از رگرسیون‌های غیرخطی، نکته اصلی یافتن ضریب همبستگی است که به ما نشان می‌دهد آیا رابطه نزدیکی بین دو پارامتر وجود دارد یا خیر. به عنوان یک قاعده، اگر ضریب همبستگی نزدیک به 1 باشد، یک ارتباط وجود دارد و پیش بینی کاملاً دقیق خواهد بود. یکی دیگر از عناصر مهم رگرسیون های غیر خطی، میانگین خطای نسبی ( آ ) اگر در فاصله باشد<8…10%, значит модель достаточно точна.

در این مورد، شاید ما بلوک نظری را تمام کنیم و به محاسبات عملی برویم.

ما یک جدول فروش خودرو برای یک دوره 15 ساله داریم (بیایید آن را با X نشان دهیم)، تعداد مراحل اندازه گیری آرگومان n خواهد بود، ما همچنین برای این دوره ها درآمد داریم (بیایید آن را نشان دهیم Y)، باید پیش بینی کنیم درآمد در آینده چقدر خواهد بود. بیایید جدول زیر را بسازیم:

برای مطالعه باید معادله (وابستگی Y به X) را حل کنیم: y=ax 2 +bx+c+e. این یک جفت رگرسیون درجه دوم است. در این مورد، ما از روش حداقل مربعات برای یافتن آرگومان های مجهول - a, b, c استفاده می کنیم. منجر به سیستمی از معادلات جبری به شکل زیر خواهد شد:

برای حل این سیستم مثلا از روش کرامر استفاده می کنیم. می بینیم که مبالغ موجود در سیستم، ضرایب مجهولات است. برای محاسبه آنها، چندین ستون را به جدول اضافه می کنیم (D، E، F، G، H) و آنها را با توجه به معنای محاسبات امضا می کنیم - در ستون D، x، در E را به یک مکعب، در F به شکل مربع مربع می کنیم. توان چهارم، در G شاخص های x و y را ضرب می کنیم، در H x را مربع می کنیم و با y ضرب می کنیم.

جدولی از فرم پر شده با موارد لازم برای حل معادله ظاهر می شود.

بیایید یک ماتریس تشکیل دهیم آ سیستمی متشکل از ضرایب مجهولات در سمت چپ معادلات. بیایید آن را در سلول A22 قرار دهیم و آن را صدا کنیم. A=". ما از سیستم معادلاتی که برای حل رگرسیون انتخاب کرده ایم پیروی می کنیم.

یعنی در سلول B21 باید مجموع ستونی را که نشانگر X را به توان چهارم رساندیم - F17 قرار دهیم. بیایید فقط به سلول - "=F17" مراجعه کنیم. بعد، ما به مجموع ستونی که X در آن مکعب شده است - E17 نیاز داریم، سپس به شدت طبق سیستم پیش می رویم. بنابراین، ما باید کل ماتریس را پر کنیم.

مطابق با الگوریتم کرامر، ماتریس A1 مشابه A را جمع آوری می کنیم که به جای عناصر ستون اول، عناصر قسمت های سمت راست معادلات سیستم در آن قرار می گیرند. یعنی مجموع ستون X مجذور Y، مجموع ستون XY و مجموع ستون Y.

ما همچنین به دو ماتریس دیگر نیاز خواهیم داشت - بیایید آنها را A2 و A3 بنامیم که در آن ستون دوم و سوم از ضرایب سمت راست معادلات تشکیل شده است. تصویر به این صورت خواهد بود.

با پیروی از الگوریتم انتخاب شده، باید مقادیر تعیین کننده ها (دترمیناتورها، D) ماتریس های به دست آمده را محاسبه کنیم. بیایید از فرمول MOPRED استفاده کنیم. نتایج در سلول های J21:K24 قرار خواهند گرفت.

ما ضرایب معادله را مطابق با کرامر در سلول های روبروی تعیین کننده های مربوطه بر اساس فرمول محاسبه می کنیم: آ(در سلول M22) - "=K22/K21"؛ ب(در سلول M23) - "=K23/K21"؛ با(در سلول M24) - "=K24 / K21".

معادله رگرسیون درجه دوم جفت مورد نظر خود را بدست می آوریم:

y=-0.074x2 +2.151x+6.523

اجازه دهید تنگی رابطه خطی را با شاخص همبستگی تخمین بزنیم.

برای محاسبه، یک ستون J اضافی به جدول اضافه کنید (بیایید آن را y* بنامیم). محاسبه به صورت زیر خواهد بود (طبق معادله رگرسیونی که دریافت کردیم) - "=$m$22*B2*B2+$M$23*B2+$M$24".بیایید آن را در سلول J2 قرار دهیم. تنها چیزی که باقی می ماند این است که نشانگر تکمیل خودکار را به سمت سلول J16 بکشید.

برای محاسبه مجموع (میانگین Y-Y) 2، ستون های K و L را با فرمول های مربوطه به جدول اضافه کنید. ما میانگین ستون Y را با استفاده از تابع AVERAGE محاسبه می کنیم.

در سلول K25 ما فرمول محاسبه شاخص همبستگی را قرار می دهیم - "=ROOT(1-(K17/L17))".

می بینیم که مقدار 0.959 بسیار نزدیک به 1 است، به این معنی که یک رابطه غیر خطی نزدیک بین فروش و سال وجود دارد.

باقی مانده است که کیفیت برازش معادله رگرسیون درجه دوم (شاخص تعیین) به دست آمده را ارزیابی کنیم. با فرمول مربع شاخص همبستگی محاسبه می شود. یعنی فرمول موجود در سلول K26 بسیار ساده خواهد بود - "=K25*K25".

ضریب 0.920 نزدیک به 1 است که نشان دهنده تناسب با کیفیت بالا است.

آخرین مرحله محاسبه خطای نسبی است. بیایید یک ستون اضافه کنیم و فرمول را در آنجا وارد کنیم: "=ABS((C2-J2)/C2)، ABS — ماژول، مقدار مطلق. بیایید نشانگر را به پایین بکشیم و در سلول M18 مقدار متوسط ​​(AVERAGE) را نمایش می دهیم، قالب درصد را به سلول ها اختصاص می دهیم. نتیجه به دست آمده - 7.79٪ در مقادیر خطای قابل قبول است<8…10%. Значит вычисления достаточно точны.

در صورت لزوم می توانیم بر اساس مقادیر به دست آمده یک نمودار بسازیم.

فایل نمونه پیوست شده است - LINK!

رگرسیون در اکسل

پردازش داده های آماری را می توان با استفاده از بسته تحلیلی افزودنی در زیر آیتم منوی "سرویس" انجام داد. در اکسل 2003، اگر باز کنید سرویس، ما نمی توانیم برگه را پیدا کنیم تحلیل داده ها، سپس روی دکمه سمت چپ ماوس کلیک کنید تا برگه باز شود افزودنی هاو نقطه مقابل بسته تحلیلیبا کلیک بر روی دکمه سمت چپ ماوس، یک تیک بزنید (شکل 17).

برنج. 17. پنجره افزودنی ها

پس از آن، منو سرویسبرگه ظاهر می شود تحلیل داده ها.

در اکسل 2007 برای نصب تجزیه و تحلیل بستهباید روی دکمه OFFICE در گوشه سمت چپ بالای برگه کلیک کنید (شکل 18a). بعد، روی دکمه کلیک کنید گزینه های اکسل. در پنجره ای که ظاهر می شود گزینه های اکسلروی مورد کلیک چپ کنید افزودنی هاو در قسمت سمت راست لیست کشویی مورد مورد نظر را انتخاب کنید بسته تحلیلی.بعد، بر روی کلیک کنید خوب.

برنج. 18. نصب و راه اندازی تجزیه و تحلیل بستهدر اکسل 2007

برای نصب Analysis Pack روی دکمه کلیک کنید برو،در پایین پنجره باز پنجره نشان داده شده در شکل. 12. کادر کناری را علامت بزنید بسته تحلیلی.در برگه داده هادکمه ظاهر می شود تحلیل داده ها(شکل 19).

از بین موارد پیشنهادی، مورد را انتخاب کنید " پسرفت” و با دکمه سمت چپ ماوس روی آن کلیک کنید. بعد روی OK کلیک کنید.

پنجره نشان داده شده در شکل. 21

ابزار تحلیل « پسرفت» برای جا دادن یک نمودار به مجموعه ای از مشاهدات با استفاده از روش حداقل مربعات استفاده می شود. رگرسیون برای تجزیه و تحلیل اثر بر روی یک متغیر وابسته واحد از مقادیر یک یا چند متغیر مستقل استفاده می شود. به عنوان مثال، عملکرد ورزشی یک ورزشکار تحت تأثیر عوامل مختلفی از جمله سن، قد و وزن است. می توان میزان تأثیر هر یک از این سه عامل را بر عملکرد یک ورزشکار محاسبه کرد و سپس از داده های به دست آمده برای پیش بینی عملکرد ورزشکار دیگر استفاده کرد.

ابزار Regression از تابع استفاده می کند LINEST.

جعبه گفتگوی REGRESS

اگر سطر اول یا ستون اول محدوده ورودی شامل عناوین باشد، برچسب ها را انتخاب کنید. اگر هدر وجود ندارد، این کادر را پاک کنید. در این صورت سرصفحه های مناسب برای داده های جدول خروجی به صورت خودکار تولید می شوند.

سطح قابلیت اطمینان کادر را انتخاب کنید تا یک سطح اضافی در جدول کل خروجی گنجانده شود. در قسمت مربوطه، علاوه بر سطح اطمینان 95% پیش فرض، سطح اطمینانی را که می خواهید اعمال کنید وارد کنید.

ثابت - صفر کادر را علامت بزنید تا خط رگرسیون از مبدا عبور کند.

محدوده خروجی یک مرجع به سلول سمت چپ بالای محدوده خروجی وارد کنید. حداقل هفت ستون برای جدول خروجی نتایج اختصاص دهید که شامل: نتایج تحلیل واریانس، ضرایب، خطای استاندارد محاسبه Y، انحرافات استاندارد، تعداد مشاهدات، خطاهای استاندارد برای ضرایب خواهد بود.

کاربرگ جدید این کادر را علامت بزنید تا یک کاربرگ جدید در کتاب کار باز شود و نتایج تجزیه و تحلیل از سلول A1 شروع شود. در صورت لزوم، یک نام برای برگه جدید در فیلد مقابل موقعیت دکمه رادیویی مناسب وارد کنید.

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

باقیمانده ها کادر را برای قرار دادن باقیمانده ها در جدول خروجی انتخاب کنید.

باقیمانده های استاندارد شده، کادر بررسی را برای گنجاندن باقیمانده های استاندارد شده در جدول خروجی انتخاب کنید.

نمودار باقیمانده برای رسم باقیمانده ها برای هر متغیر مستقل کادر را علامت بزنید.

Fit Plot برای رسم مقادیر پیش‌بینی‌شده در مقابل مقادیر مشاهده‌شده، کادر را علامت بزنید.

نمودار احتمال عادیبرای ترسیم احتمال عادی، کادر را علامت بزنید.

تابع LINEST

برای انجام محاسبات، سلولی را که می خواهیم مقدار میانگین را با مکان نما در آن نمایش دهیم انتخاب کرده و کلید = را روی صفحه کلید فشار دهید. بعد در قسمت Name مثلا تابع مورد نظر را مشخص کنید میانگین(شکل 22).

برنج. 22 یافتن توابع در اکسل 2003

اگر در میدان نامنام تابع ظاهر نمی شود، سپس روی مثلث کنار فیلد کلیک چپ کرده و پس از آن پنجره ای با لیستی از توابع ظاهر می شود. اگر این تابع در لیست نیست، روی مورد موجود در لیست کلیک چپ کنید توابع دیگر، یک جعبه گفتگو ظاهر خواهد شد. FUNCTION Master، که در آن با استفاده از اسکرول عمودی، عملکرد مورد نظر را انتخاب کرده و با مکان نما انتخاب کرده و بر روی آن کلیک کنید خوب(شکل 23).

برنج. 23. Function Wizard

برای جستجوی تابع در اکسل 2007، هر تبی را می توان در منو باز کرد، سپس برای انجام محاسبات، سلولی را که می خواهیم میانگین مقدار را با مکان نما در آن نمایش دهیم انتخاب کرده و کلید = را روی صفحه کلید فشار دهیم. سپس در قسمت Name تابع را مشخص کنید میانگین. پنجره محاسبه تابع مشابه پنجره اکسل 2003 است.

همچنین می توانید برگه فرمول ها را انتخاب کنید و روی دکمه در "" کلیک چپ کنید. درج تابع» (شکل 24)، یک پنجره ظاهر می شود FUNCTION Masterکه نمای آن شبیه اکسل 2003 است. همچنین در منو می توانید بلافاصله دسته بندی توابع (اخیرا استفاده شده، مالی، منطقی، متن، تاریخ و زمان، ریاضی، توابع دیگر) را انتخاب کنید که در آن جستجو خواهیم کرد. برای عملکرد مورد نظر

برنج. 24 انتخاب تابع در اکسل 2007

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

معادله یک خط مستقیم:

(در صورت وجود چندین محدوده از مقادیر x)،

در جایی که مقدار وابسته y تابعی از مقدار مستقل x است، مقادیر m ضرایب مربوط به هر متغیر مستقل x است و b یک ثابت است. توجه داشته باشید که y، x و m می توانند بردار باشند. تابع LINESTآرایه ای را برمی گرداند . LINESTهمچنین ممکن است آمار رگرسیون اضافی را برگرداند.

LINEST(مقادیر_y_مقادیر، مقادیر_x_شناخته_مقادیر_کنست، آمار)

مقادیر Known_y - مجموعه ای از مقادیر y که قبلاً برای رابطه شناخته شده اند.

اگر آرایه Known_y دارای یک ستون باشد، هر ستون از آرایهknown_x به عنوان یک متغیر جداگانه تفسیر می شود.

اگر آرایه Known_y یک ردیف داشته باشد، هر ردیف از آرایه Known_x به عنوان یک متغیر جداگانه تفسیر می شود.

Known_x یک مجموعه اختیاری از x است که قبلاً برای رابطه شناخته شده است.

آرایه Known_x می تواند شامل یک یا چند مجموعه از متغیرها باشد. اگر فقط از یک متغیر استفاده شود، آرایه های_known_y_values ​​و شناخته شده_x_values ​​می توانند به هر شکلی باشند - تا زمانی که ابعاد یکسانی داشته باشند. اگر بیش از یک متغیر استفاده می شود، باید شناخته شده_y یک بردار باشد (یعنی یک ردیف بالا یا یک ستون عرض).

اگر array_known_x حذف شود، این آرایه (1;2;3;...) به اندازه array_known_y در نظر گرفته می شود.

Const یک مقدار بولی است که مشخص می کند آیا ثابت b باید 0 باشد یا خیر.

اگر آرگومان "const" درست باشد یا حذف شود، ثابت b به طور معمول ارزیابی می شود.

اگر آرگومان "const" FALSE باشد، مقدار b برابر 0 در نظر گرفته می شود و مقادیر m به گونه ای انتخاب می شوند که رابطه ارضا شود.

Statistics یک مقدار بولی است که نشان می دهد که آیا آمار رگرسیون اضافی باید برگردانده شود یا خیر.

اگر آمار درست باشد، LINEST آمار رگرسیون اضافی را برمی‌گرداند. آرایه برگشتی به این صورت خواهد بود: (mn;mn-1;...;m1;b:sen;sen-1;...;se1;seb:r2;sey:F;df:ssreg;ssresid).

اگر آمار FALSE یا حذف شده باشد، LINEST فقط ضرایب m و ثابت b را برمی گرداند.

آمار رگرسیون اضافی

شکل زیر ترتیب برگشت آمار رگرسیون اضافی را نشان می دهد.

یادداشت:

هر خط مستقیمی را می توان با شیب و تقاطع آن با محور y توصیف کرد:

شیب (m): برای تعیین شیب یک خط که معمولا با m نشان داده می شود، باید دو نقطه از خط و ; شیب خواهد بود .

تقاطع Y (b): تقاطع y یک خط که معمولاً با b نشان داده می شود، مقدار y برای نقطه ای است که خط با محور y قطع می کند.

معادله خط مستقیم شکل دارد. اگر مقادیر m و b مشخص باشد، هر نقطه از خط را می توان با جایگزین کردن مقادیر y یا x در معادله محاسبه کرد. همچنین می توانید از تابع TREND استفاده کنید.

اگر فقط یک متغیر مستقل x وجود داشته باشد، می توانید شیب و قطع y را مستقیماً با استفاده از فرمول های زیر بدست آورید:

شیب: INDEX(LINEST(known_y's،known_x's)، 1)

مقطع Y: INDEX(LINEST(y'sknown_y's, known_x's)، 2)

دقت تقریب با استفاده از خط مستقیم محاسبه شده توسط تابع LINEST به درجه پراکندگی داده ها بستگی دارد. هر چه داده ها به یک خط مستقیم نزدیکتر باشند، مدل مورد استفاده LINEST دقیق تر است. تابع LINEST از روش حداقل مربعات برای تعیین بهترین تناسب با داده ها استفاده می کند. هنگامی که فقط یک متغیر مستقل x وجود دارد، m و b با استفاده از فرمول های زیر محاسبه می شوند:

که در آن x و y میانگین های نمونه هستند، به عنوان مثال x = AVERAGE (شناخته‌های_x) و y = AVERAGE (شناخته‌های_y).

توابع برازش LINEST و LGRFPRIBL می توانند منحنی مستقیم یا نمایی را محاسبه کنند که بهترین تناسب با داده ها را دارد. با این حال، آنها به این سوال پاسخ نمی دهند که کدام یک از دو نتیجه برای حل مشکل مناسب تر است. همچنین می‌توانید تابع TREND (مقادیر_Y-مقادیر؛ مقادیر_x-مقدار) TREND را برای یک خط مستقیم یا تابع GROWTH(مقادیر_y_مقادیر؛ مقادیر_x_دانسته) را برای یک منحنی نمایی محاسبه کنید. این توابع، اگر از آرگومان new_x_values ​​حذف شوند، آرایه ای از مقادیر y محاسبه شده را برای مقادیر x واقعی طبق یک خط مستقیم یا منحنی برمی گرداند. سپس می توانید مقادیر محاسبه شده را با مقادیر واقعی مقایسه کنید. همچنین می توانید نمودارهایی برای مقایسه بصری بسازید.

هنگام انجام تجزیه و تحلیل رگرسیون، مایکروسافت اکسل برای هر نقطه، مجذور اختلاف بین مقدار y پیش بینی شده و مقدار y واقعی را محاسبه می کند. مجموع این مجذور تفاوت ها را مجموع مجذورات باقیمانده (ssresid) می گویند. سپس مایکروسافت اکسل مجموع مجذورات (sstotal) را محاسبه می کند. اگر const = TRUE یا اگر این آرگومان مشخص نشده باشد، مجموع مجذورات برابر با مجموع مجذور تفاوت مقادیر y واقعی و میانگین مقادیر y خواهد بود. اگر const = FALSE باشد، مجموع مربع ها برابر با مجموع مربع های مقادیر y واقعی خواهد بود (بدون کسر میانگین y از ضریب y). پس از آن، مجموع رگرسیون مربع ها را می توان به صورت زیر محاسبه کرد: ssreg = sstotal - ssresid. هر چه مجموع باقیمانده مربع ها کوچکتر باشد، مقدار ضریب جبر r2 بزرگتر است، که نشان می دهد معادله به دست آمده با استفاده از تحلیل رگرسیون چقدر روابط بین متغیرها را توضیح می دهد. ضریب r2 برابر با ssreg/sstotal است.

در برخی موارد، یک یا چند ستون X (با فرض اینکه مقادیر Y و X در ستون‌ها هستند) ارزش پیش‌بینی اضافی در سایر ستون‌های X ندارند. به عبارت دیگر، حذف یک یا چند ستون X می‌تواند به مقادیر Y منجر شود. با همان دقت محاسبه می شود. در این حالت، ستون‌های X اضافی از مدل رگرسیون حذف خواهند شد. این پدیده را "هم خطی" می نامند زیرا ستون های اضافی X را می توان به صورت مجموع چندین ستون غیر زائد نشان داد. LINEST همخطی بودن را بررسی می کند و در صورت یافتن هر ستون X اضافی را از مدل رگرسیون حذف می کند. ستون‌های X حذف‌شده را می‌توان در خروجی LINEST با ضریب ۰ و مقدار se برابر با ۰ شناسایی کرد. حذف یک یا چند ستون به‌عنوان زائد، مقدار df را تغییر می‌دهد زیرا بستگی به تعداد ستون‌های X دارد که واقعاً برای اهداف پیش‌بینی استفاده می‌شوند. برای جزئیات بیشتر در مورد محاسبه df به مثال 4 زیر مراجعه کنید. هنگامی که df به دلیل حذف ستون های اضافی تغییر می کند، مقادیر sey و F نیز تغییر می کنند. اغلب استفاده از هم خطی توصیه نمی شود. با این حال، اگر برخی از ستون های X حاوی 0 یا 1 به عنوان شاخصی باشد که نشان می دهد موضوع آزمایش در یک گروه جداگانه قرار دارد، باید از آن استفاده شود. اگر const = TRUE یا اگر این آرگومان مشخص نشده باشد، LINEST یک ستون X اضافی را برای شبیه سازی نقطه تقاطع وارد می کند. اگر ستونی با مقادیر 1 برای مردان و 0 برای زنان و ستونی با مقادیر 1 برای زنان و 0 برای مردان وجود داشته باشد، آخرین ستون حذف می شود زیرا مقادیر آن را می توان از زیر به دست آورد. ستون "نشانگر مرد".

محاسبه df برای مواردی که X ستون ها به دلیل همخطی بودن از مدل حذف نمی شوند به این صورت است: اگر k ستون شناخته شده_x وجود داشته باشد و const = TRUE یا مشخص نشده باشد، df = n - k - 1. اگر const = FALSE، سپس df = n -k. در هر دو مورد، حذف ستون های X به دلیل همخطی بودن، مقدار df را 1 افزایش می دهد.

فرمول هایی که آرایه ها را برمی گرداند باید به عنوان فرمول های آرایه وارد شوند.

به عنوان مثال، هنگام وارد کردن آرایه ای از ثابت ها به عنوان آرگومان شناخته شده_x_values، از یک نقطه ویرگول برای جدا کردن مقادیر در همان خط و یک دو نقطه برای جدا کردن خطوط استفاده کنید. کاراکترهای جداکننده ممکن است بسته به تنظیمات پنجره "زبان و استانداردها" در کنترل پنل متفاوت باشد.

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

الگوریتم اصلی مورد استفاده در تابع LINEST، با الگوریتم اصلی توابع متفاوت است شیبو بخش خط. تفاوت بین الگوریتم ها می تواند منجر به نتایج متفاوتی برای داده های نامشخص و خطی شود. به عنوان مثال، اگر نقاط داده آرگومان Known_y 0 و نقاط داده آرگومانknown_x 1 باشد، آنگاه:

تابع LINESTمقداری برابر با 0 برمی گرداند. الگوریتم تابع LINESTبرای برگرداندن مقادیر مناسب برای داده های خطی استفاده می شود که در این صورت حداقل یک پاسخ می توان یافت.

توابع SLOPE و INTERCEPT خطای #DIV/0! را برمی‌گردانند. الگوریتم توابع SLOPE و INTERCEPT برای یافتن تنها یک پاسخ استفاده می شود و در این حالت ممکن است چندین پاسخ وجود داشته باشد.

علاوه بر محاسبه آمار برای سایر انواع رگرسیون، LINEST می تواند برای محاسبه محدوده سایر انواع رگرسیون با وارد کردن توابع متغیرهای x و y به عنوان یک سری متغیر x و y برای LINEST استفاده شود. به عنوان مثال، فرمول زیر:

LINEST(y- مقادیر، x-values^COLUMN($A:$C))

با یک ستون از مقادیر Y و یک ستون از مقادیر X برای محاسبه تقریب مکعب (چند جمله ای درجه 3) به شکل زیر کار می کند:

این فرمول را می توان برای محاسبات سایر انواع رگرسیون تغییر داد، اما در برخی موارد، تنظیمات مربوط به مقادیر خروجی و سایر آمارها مورد نیاز است.

پردازش داده های آماری نیز می تواند با استفاده از افزونه انجام شود بسته تحلیلی(شکل 62).

از بین موارد پیشنهادی، مورد را انتخاب کنید " پسرفت” و با دکمه سمت چپ ماوس روی آن کلیک کنید. بعد روی OK کلیک کنید.

پنجره نشان داده شده در شکل. 63.

ابزار تحلیل « پسرفت» برای جا دادن یک نمودار به مجموعه ای از مشاهدات با استفاده از روش حداقل مربعات استفاده می شود. رگرسیون برای تجزیه و تحلیل اثر بر روی یک متغیر وابسته واحد از مقادیر یک یا چند متغیر مستقل استفاده می شود. به عنوان مثال، عملکرد ورزشی یک ورزشکار تحت تأثیر عوامل مختلفی از جمله سن، قد و وزن است. می توان میزان تأثیر هر یک از این سه عامل را بر عملکرد یک ورزشکار محاسبه کرد و سپس از داده های به دست آمده برای پیش بینی عملکرد ورزشکار دیگر استفاده کرد.

ابزار Regression از تابع استفاده می کند LINEST.

جعبه گفتگوی REGRESS

اگر سطر اول یا ستون اول محدوده ورودی شامل عناوین باشد، برچسب ها را انتخاب کنید. اگر هدر وجود ندارد، این کادر را پاک کنید. در این صورت سرصفحه های مناسب برای داده های جدول خروجی به صورت خودکار تولید می شوند.

سطح قابلیت اطمینان کادر را انتخاب کنید تا یک سطح اضافی در جدول کل خروجی گنجانده شود. در قسمت مربوطه، علاوه بر سطح اطمینان 95% پیش فرض، سطح اطمینانی را که می خواهید اعمال کنید وارد کنید.

ثابت - صفر کادر را علامت بزنید تا خط رگرسیون از مبدا عبور کند.

محدوده خروجی یک مرجع به سلول سمت چپ بالای محدوده خروجی وارد کنید. حداقل هفت ستون برای جدول خروجی نتایج اختصاص دهید که شامل: نتایج تحلیل واریانس، ضرایب، خطای استاندارد محاسبه Y، انحرافات استاندارد، تعداد مشاهدات، خطاهای استاندارد برای ضرایب خواهد بود.

کاربرگ جدید این کادر را علامت بزنید تا یک کاربرگ جدید در کتاب کار باز شود و نتایج تجزیه و تحلیل از سلول A1 شروع شود. در صورت لزوم، یک نام برای برگه جدید در فیلد مقابل موقعیت دکمه رادیویی مناسب وارد کنید.

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

باقیمانده ها کادر را برای قرار دادن باقیمانده ها در جدول خروجی انتخاب کنید.

باقیمانده های استاندارد شده، کادر بررسی را برای گنجاندن باقیمانده های استاندارد شده در جدول خروجی انتخاب کنید.

نمودار باقیمانده برای رسم باقیمانده ها برای هر متغیر مستقل کادر را علامت بزنید.

Fit Plot برای رسم مقادیر پیش‌بینی‌شده در مقابل مقادیر مشاهده‌شده، کادر را علامت بزنید.

نمودار احتمال عادیبرای ترسیم احتمال عادی، کادر را علامت بزنید.

تابع LINEST

برای انجام محاسبات، سلولی را که می خواهیم مقدار میانگین را با مکان نما در آن نمایش دهیم انتخاب کرده و کلید = را روی صفحه کلید فشار دهید. بعد در قسمت Name مثلا تابع مورد نظر را مشخص کنید میانگین(شکل 22).

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

معادله یک خط مستقیم:

y=m 1 x 1 +m 2 x 2 +…+b (در مورد چندین محدوده از مقادیر x)،

در جایی که مقدار وابسته y تابعی از مقدار مستقل x است، مقادیر m ضرایب مربوط به هر متغیر مستقل x است و b یک ثابت است. توجه داشته باشید که y، x و m می توانند بردار باشند. تابع LINESTآرایه ای را برمی گرداند (mn;mn-1;…;m 1 ;b). LINESTهمچنین ممکن است آمار رگرسیون اضافی را برگرداند.

LINEST(مقادیر_y_مقادیر، مقادیر_x_شناخته_مقادیر_کنست، آمار)

مقادیر Known_y - مجموعه ای از مقادیر y که قبلاً برای رابطه y=mx+b شناخته شده اند.

اگر آرایه Known_y دارای یک ستون باشد، هر ستون از آرایهknown_x به عنوان یک متغیر جداگانه تفسیر می شود.

اگر آرایه Known_y یک ردیف داشته باشد، هر ردیف از آرایه Known_x به عنوان یک متغیر جداگانه تفسیر می شود.

مقادیر Known_x - مجموعه ای اختیاری از مقادیر x که قبلاً برای رابطه y=mx+b شناخته شده اند.

آرایه Known_x می تواند شامل یک یا چند مجموعه از متغیرها باشد. اگر فقط از یک متغیر استفاده شود، آرایه های_known_y_values ​​و شناخته شده_x_values ​​می توانند به هر شکلی باشند - تا زمانی که ابعاد یکسانی داشته باشند. اگر بیش از یک متغیر استفاده می شود، باید شناخته شده_y یک بردار باشد (یعنی یک ردیف بالا یا یک ستون عرض).

اگر array_known_x حذف شود، این آرایه (1;2;3;...) به اندازه array_known_y در نظر گرفته می شود.

Const یک مقدار بولی است که مشخص می کند آیا ثابت b باید 0 باشد یا خیر.

اگر آرگومان "const" درست باشد یا حذف شود، ثابت b به طور معمول ارزیابی می شود.

اگر آرگومان "const" FALSE باشد، مقدار b برابر 0 در نظر گرفته می شود و مقادیر m به گونه ای انتخاب می شوند که رابطه y=mx برآورده شود.

Statistics یک مقدار بولی است که نشان می دهد که آیا آمار رگرسیون اضافی باید برگردانده شود یا خیر.

اگر آمار درست باشد، LINEST آمار رگرسیون اضافی را برمی‌گرداند. آرایه برگشتی به این صورت خواهد بود: (mn;mn-1;...;m1;b:sen;sen-1;...;se1;seb:r2;sey:F;df:ssreg;ssresid).

اگر آمار FALSE یا حذف شده باشد، LINEST فقط ضرایب m و ثابت b را برمی گرداند.

آمار رگرسیون اضافی. (جدول 17)

شکل زیر ترتیب برگرداندن آمار رگرسیون اضافی را نشان می دهد (شکل 64).

یادداشت:

هر خط مستقیمی را می توان با شیب و تقاطع آن با محور y توصیف کرد:

شیب (m): برای تعیین شیب یک خط، که معمولا با m نشان داده می شود، باید دو نقطه از خط (x 1 ,y 1) و (x 2 ,y 2) بگیرید. شیب برابر با (y 2 -y 1) / (x 2 -x 1) خواهد بود.

تقاطع Y (b): تقاطع y یک خط که معمولاً با b نشان داده می شود، مقدار y برای نقطه ای است که خط با محور y قطع می کند.

معادله خط مستقیم به شکل y=mx+b است. اگر مقادیر m و b مشخص باشد، هر نقطه از خط را می توان با جایگزین کردن مقادیر y یا x در معادله محاسبه کرد. همچنین می توانید از تابع TREND استفاده کنید.

اگر فقط یک متغیر مستقل x وجود داشته باشد، می توانید شیب و قطع y را مستقیماً با استفاده از فرمول های زیر بدست آورید:

شیب: INDEX(LINEST(known_y's،known_x's)، 1)

مقطع Y: INDEX(LINEST(y'sknown_y's, known_x's)، 2)

دقت تقریب با استفاده از خط مستقیم محاسبه شده توسط تابع LINEST به درجه پراکندگی داده ها بستگی دارد. هر چه داده ها به یک خط مستقیم نزدیکتر باشند، مدل مورد استفاده LINEST دقیق تر است. تابع LINEST از روش حداقل مربعات برای تعیین بهترین تناسب با داده ها استفاده می کند. هنگامی که فقط یک متغیر مستقل x وجود دارد، m و b با استفاده از فرمول های زیر محاسبه می شوند:

که در آن x و y میانگین های نمونه هستند، به عنوان مثال x = AVERAGE (شناخته‌های_x) و y = AVERAGE (شناخته‌های_y).

توابع برازش LINEST و LGRFPRIBL می توانند منحنی مستقیم یا نمایی را محاسبه کنند که بهترین تناسب با داده ها را دارد. با این حال، آنها به این سوال پاسخ نمی دهند که کدام یک از دو نتیجه برای حل مشکل مناسب تر است. همچنین می‌توانید تابع TREND (مقادیر_Y-مقادیر؛ مقادیر_x-مقدار) TREND را برای یک خط مستقیم یا تابع GROWTH(مقادیر_y_مقادیر؛ مقادیر_x_دانسته) را برای یک منحنی نمایی محاسبه کنید. این توابع، اگر از آرگومان new_x_values ​​حذف شوند، آرایه ای از مقادیر y محاسبه شده را برای مقادیر x واقعی طبق یک خط مستقیم یا منحنی برمی گرداند. سپس می توانید مقادیر محاسبه شده را با مقادیر واقعی مقایسه کنید. همچنین می توانید نمودارهایی برای مقایسه بصری بسازید.

هنگام انجام تجزیه و تحلیل رگرسیون، مایکروسافت اکسل برای هر نقطه، مجذور اختلاف بین مقدار y پیش بینی شده و مقدار y واقعی را محاسبه می کند. مجموع این مجذور تفاوت ها را مجموع مجذورات باقیمانده (ssresid) می گویند. سپس مایکروسافت اکسل مجموع مجذورات (sstotal) را محاسبه می کند. اگر const = TRUE یا اگر این آرگومان مشخص نشده باشد، مجموع مجذورات برابر با مجموع مجذور تفاوت مقادیر y واقعی و میانگین مقادیر y خواهد بود. اگر const = FALSE باشد، مجموع مربع ها برابر با مجموع مربع های مقادیر y واقعی خواهد بود (بدون کسر میانگین y از ضریب y). پس از آن، مجموع رگرسیون مربع ها را می توان به صورت زیر محاسبه کرد: ssreg = sstotal - ssresid. هر چه مجموع باقیمانده مربع ها کوچکتر باشد، مقدار ضریب جبر r2 بزرگتر است، که نشان می دهد معادله به دست آمده با استفاده از تحلیل رگرسیون چقدر روابط بین متغیرها را توضیح می دهد. ضریب r2 برابر با ssreg/sstotal است.

در برخی موارد، یک یا چند ستون X (با فرض اینکه مقادیر Y و X در ستون‌ها هستند) ارزش پیش‌بینی اضافی در سایر ستون‌های X ندارند. به عبارت دیگر، حذف یک یا چند ستون X می‌تواند به مقادیر Y منجر شود. با همان دقت محاسبه می شود. در این حالت، ستون‌های X اضافی از مدل رگرسیون حذف خواهند شد. این پدیده را "هم خطی" می نامند زیرا ستون های اضافی X را می توان به صورت مجموع چندین ستون غیر زائد نشان داد. LINEST همخطی بودن را بررسی می کند و در صورت یافتن هر ستون X اضافی را از مدل رگرسیون حذف می کند. ستون‌های X حذف‌شده را می‌توان در خروجی LINEST با ضریب ۰ و مقدار se برابر با ۰ شناسایی کرد. حذف یک یا چند ستون به‌عنوان زائد، مقدار df را تغییر می‌دهد زیرا بستگی به تعداد ستون‌های X دارد که واقعاً برای اهداف پیش‌بینی استفاده می‌شوند. برای جزئیات بیشتر در مورد محاسبه df به مثال 4 زیر مراجعه کنید. هنگامی که df به دلیل حذف ستون های اضافی تغییر می کند، مقادیر sey و F نیز تغییر می کنند. اغلب استفاده از هم خطی توصیه نمی شود. با این حال، اگر برخی از ستون های X حاوی 0 یا 1 به عنوان شاخصی باشد که نشان می دهد موضوع آزمایش در یک گروه جداگانه قرار دارد، باید از آن استفاده شود. اگر const = TRUE یا اگر این آرگومان مشخص نشده باشد، LINEST یک ستون X اضافی را برای شبیه سازی نقطه تقاطع وارد می کند. اگر ستونی با مقادیر 1 برای مردان و 0 برای زنان و ستونی با مقادیر 1 برای زنان و 0 برای مردان وجود داشته باشد، آخرین ستون حذف می شود زیرا مقادیر آن را می توان از زیر به دست آورد. ستون "نشانگر مرد".

محاسبه df برای مواردی که X ستون ها به دلیل همخطی بودن از مدل حذف نمی شوند به این صورت است: اگر k ستون شناخته شده_x وجود داشته باشد و const = TRUE یا مشخص نشده باشد، df = n - k - 1. اگر const = FALSE، سپس df = n -k. در هر دو مورد، حذف ستون های X به دلیل همخطی بودن، مقدار df را 1 افزایش می دهد.

فرمول هایی که آرایه ها را برمی گرداند باید به عنوان فرمول های آرایه وارد شوند.

به عنوان مثال، هنگام وارد کردن آرایه ای از ثابت ها به عنوان آرگومان شناخته شده_x_values، از یک نقطه ویرگول برای جدا کردن مقادیر در همان خط و یک دو نقطه برای جدا کردن خطوط استفاده کنید. کاراکترهای جداکننده ممکن است بسته به تنظیمات پنجره "زبان و استانداردها" در کنترل پنل متفاوت باشد.

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

الگوریتم اصلی مورد استفاده در تابع LINEST، با الگوریتم اصلی توابع متفاوت است شیبو بخش خط. تفاوت بین الگوریتم ها می تواند منجر به نتایج متفاوتی برای داده های نامشخص و خطی شود. به عنوان مثال، اگر نقاط داده آرگومان Known_y 0 و نقاط داده آرگومانknown_x 1 باشد، آنگاه:

تابع LINESTمقداری برابر با 0 برمی گرداند. الگوریتم تابع LINESTبرای برگرداندن مقادیر مناسب برای داده های خطی استفاده می شود که در این صورت حداقل یک پاسخ می توان یافت.

توابع SLOPE و INTERCEPT خطای #DIV/0! را برمی‌گردانند. الگوریتم توابع SLOPE و INTERCEPT برای یافتن تنها یک پاسخ استفاده می شود و در این حالت ممکن است چندین پاسخ وجود داشته باشد.

علاوه بر محاسبه آمار برای سایر انواع رگرسیون، LINEST می تواند برای محاسبه محدوده سایر انواع رگرسیون با وارد کردن توابع متغیرهای x و y به عنوان یک سری متغیر x و y برای LINEST استفاده شود. به عنوان مثال، فرمول زیر:

LINEST(y- مقادیر، x-values^COLUMN($A:$C))

با یک ستون از مقادیر Y و یک ستون از مقادیر X برای محاسبه تقریب مکعب (چند جمله ای درجه 3) به شکل زیر کار می کند:

y=m 1 x+m 2 x 2 +m 3 x 3 +b

این فرمول را می توان برای محاسبات سایر انواع رگرسیون تغییر داد، اما در برخی موارد، تنظیمات مربوط به مقادیر خروجی و سایر آمارها مورد نیاز است.

تأثیر برخی از مقادیر (مستقل، مستقل) را بر روی متغیر وابسته نشان می دهد. به عنوان مثال، چگونگی تعداد جمعیت فعال اقتصادی به تعداد شرکت ها، دستمزدها و سایر پارامترها بستگی دارد. یا: سرمایه گذاری های خارجی، قیمت انرژی و غیره چگونه بر سطح تولید ناخالص داخلی تأثیر می گذارد.

نتیجه تجزیه و تحلیل به شما امکان می دهد اولویت بندی کنید. و بر اساس عوامل اصلی، پیش بینی، برنامه ریزی توسعه حوزه های اولویت دار، تصمیم گیری های مدیریتی.

رگرسیون اتفاق می افتد:

خطی (y = a + bx)؛

سهمی (y = a + bx + cx 2)؛

نمایی (y = a * exp(bx));

توان (y = a*x^b)؛

هذلولی (y = b/x + a)؛

لگاریتمی (y = b * 1n(x) + a)؛

نمایی (y = a * b^x).

مثال ساخت یک مدل رگرسیون در اکسل و تفسیر نتایج را در نظر بگیرید. بیایید یک نوع رگرسیون خطی در نظر بگیریم.

وظیفه. در 6 شرکت، میانگین حقوق ماهانه و تعداد کارکنانی که ترک کردند، تجزیه و تحلیل شد. تعیین وابستگی تعداد کارکنان بازنشسته به میانگین حقوق ضروری است.

مدل رگرسیون خطی به شکل زیر است:

Y \u003d a 0 + a 1 x 1 + ... + a k x k.

جایی که a ضرایب رگرسیون، x متغیرهای تاثیرگذار و k تعداد عوامل است.

در مثال ما، Y نشانگر ترک کار است. عامل تأثیرگذار دستمزد (x) است.

اکسل دارای توابع داخلی است که می توان از آنها برای محاسبه پارامترهای یک مدل رگرسیون خطی استفاده کرد. اما افزونه Analysis ToolPak این کار را سریعتر انجام می دهد.

یک ابزار تحلیلی قدرتمند را فعال کنید:

1. روی دکمه "Office" کلیک کنید و به تب "گزینه های اکسل" بروید. "افزونه ها".

2. در زیر، در زیر لیست کشویی، در قسمت "Management" یک کتیبه "Excel Add-ins" وجود دارد (اگر وجود ندارد، روی کادر سمت راست کلیک کنید و انتخاب کنید). و یک دکمه Go کلیک.

3. فهرستی از افزونه های موجود باز می شود. "پکیج تجزیه و تحلیل" را انتخاب کنید و روی OK کلیک کنید.

پس از فعال شدن، افزونه در تب Data در دسترس خواهد بود.

حال به طور مستقیم به تحلیل رگرسیون می پردازیم.

1. منوی ابزار تحلیل داده را باز کنید. "Regression" را انتخاب کنید.

2. منویی برای انتخاب مقادیر ورودی و گزینه های خروجی (محل نمایش نتیجه) باز می شود. در فیلدهای مربوط به داده های اولیه، محدوده پارامتر توصیف شده (Y) و عامل موثر بر آن (X) را نشان می دهیم. بقیه ممکن است کامل شود یا نباشد.

3. پس از کلیک بر روی OK، برنامه محاسبات را در یک برگه جدید نمایش می دهد (می توانید بازه زمانی نمایش در برگه فعلی را انتخاب کنید یا خروجی را به یک کتاب کار جدید اختصاص دهید).

اول از همه به مربع R و ضرایب توجه می کنیم.

R-square ضریب تعیین است. در مثال ما، 0.755 یا 75.5٪ است. این بدان معناست که پارامترهای محاسبه شده مدل، رابطه بین پارامترهای مورد مطالعه را 75.5 درصد توضیح می دهد. هر چه ضریب تعیین بالاتر باشد، مدل بهتر است. خوب - بالای 0.8. ضعیف - کمتر از 0.5 (چنین تجزیه و تحلیل به سختی می تواند معقول در نظر گرفته شود). در مثال ما - "بد نیست".

ضریب 64.1428 نشان می دهد که اگر همه متغیرهای مدل مورد نظر برابر با 0 باشند Y چقدر خواهد بود. یعنی عوامل دیگری که در مدل توضیح داده نشده اند نیز بر مقدار پارامتر تحلیل شده تأثیر می گذارند.

ضریب -0.16285 وزن متغیر X را بر Y نشان می دهد. یعنی میانگین حقوق ماهانه در این مدل بر تعداد افراد ترک با وزن 0.16285- تأثیر می گذارد (این درجه تأثیر کمی است). علامت "-" نشان دهنده تأثیر منفی است: هر چه حقوق و دستمزد بالاتر باشد، ترک کار کمتر است. که منصفانه است.