نحوه رسم پسماندهای رگرسیون رگرسیون خطی سریع در اکسل: Trendline. رگرسیون خطی در اکسل

رگرسیون چیست؟

دو متغیر پیوسته را در نظر بگیرید x=(x 1، x 2، ..، x n)، y=(y 1، y 2، ...، y n).

بیایید نقاط را روی نمودار پراکندگی دو بعدی قرار دهیم و بگوییم که داریم رابطه خطیاگر داده ها با یک خط مستقیم تقریب شوند.

اگر فرض کنیم که yبستگی دارد به ایکس، و تغییرات در yناشی از تغییرات در ایکس، می توانیم یک خط رگرسیون تعریف کنیم (رگرسیون yبر ایکس) که رابطه مستقیم بین این دو متغیر را به بهترین نحو توصیف می کند.

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

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

خط رگرسیون

معادله ریاضی که یک خط رگرسیون خطی ساده (جفتی) را ارزیابی می کند:

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

Yمتغیر وابسته یا پاسخ است. این ارزشی است که ما برای آن انتظار داریم y(به طور متوسط) اگر مقدار آن را بدانیم ایکس، یعنی مقدار پیش بینی شده است y»

• آ- عضو رایگان (عبور) از خط ارزیابی؛ این مقدار Y، چه زمانی x=0(عکس. 1).
• ب- شیب یا شیب خط برآورد شده؛ مقداری است که با آن Yاگر افزایش دهیم به طور متوسط ​​افزایش می یابد ایکسبرای یک واحد
• آو بضرایب رگرسیون خط تخمینی نامیده می شوند، اگرچه این اصطلاح اغلب فقط برای استفاده می شود ب.

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

عکس. 1. خط رگرسیون خطی که تقاطع a و شیب b را نشان می دهد (مقدار افزایش Y زمانی که x یک واحد افزایش می یابد)

روش حداقل مربعات

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

ساده ترین روش برای تعیین ضرایب آو باست روش حداقل مربع(MNK).

تناسب با در نظر گرفتن باقیمانده ها (فاصله عمودی هر نقطه از خط، به عنوان مثال باقیمانده = قابل مشاهده) ارزیابی می شود. y- پیش بینی کرد y، برنج. 2).

خط بهترین تناسب طوری انتخاب می شود که مجموع مجذورهای باقیمانده حداقل باشد.

برنج. 2. خط رگرسیون خطی با باقیمانده های نشان داده شده (خطوط نقطه چین عمودی) برای هر نقطه.

مفروضات رگرسیون خطی

بنابراین، برای هر مقدار مشاهده شده، باقیمانده برابر است با اختلاف و مقدار پیش بینی شده مربوطه، هر باقیمانده می تواند مثبت یا منفی باشد.

می توانید از باقیمانده ها برای آزمایش مفروضات زیر در پشت رگرسیون خطی استفاده کنید:

• باقیمانده ها معمولاً با میانگین صفر توزیع می شوند.

اگر مفروضات خطی بودن، نرمال بودن و/یا واریانس ثابت مشکوک باشند، می‌توانیم یک خط رگرسیون جدیدی را که این فرضیات برای آن برآورده می‌شوند، تبدیل یا محاسبه کنیم (مثلاً از تبدیل لگاریتمی و غیره استفاده کنیم).

مقادیر غیرعادی (غیرطبیعی) و نقاط تأثیر

یک مشاهده «تاثیرگذار»، اگر حذف شود، یک یا چند تخمین پارامتر مدل را تغییر می‌دهد (یعنی شیب یا فاصله).

یک نقطه پرت (مشاهده ای که با بیشتر مقادیر موجود در مجموعه داده در تضاد است) می تواند یک مشاهده "تأثیرگذار" باشد و هنگام مشاهده یک نمودار پراکندگی دوبعدی یا نموداری از باقیمانده ها به خوبی از نظر بصری تشخیص داده شود.

هم برای مشاهدات پرت و هم برای مشاهدات «تاثیرگذار» (نقاط) از مدل‌ها استفاده می‌شود، هم با گنجاندن آنها و هم بدون آنها، به تغییر برآورد (ضرایب رگرسیون) توجه کنید.

هنگام انجام یک تجزیه و تحلیل، به طور خودکار نقاط پرت را کنار نگذارید، زیرا صرفاً نادیده گرفتن آنها می تواند نتایج را تحت تأثیر قرار دهد. همیشه علل این پرت ها را مطالعه کنید و آنها را تجزیه و تحلیل کنید.

فرضیه رگرسیون خطی

هنگام ساخت یک رگرسیون خطی، این فرضیه صفر بررسی می شود که شیب کلی خط رگرسیون β برابر با صفر است.

اگر شیب خط صفر باشد، هیچ رابطه خطی بین و وجود ندارد: تغییر تأثیر نمی گذارد

برای آزمایش فرضیه صفر مبنی بر اینکه شیب واقعی صفر است، می توانید از الگوریتم زیر استفاده کنید:

محاسبه آمار آزمون برابر با نسبت، که از توزیع با درجه آزادی تبعیت می کند، که در آن خطای استاندارد ضریب

,

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

معمولاً اگر سطح معناداری به دست آمده باشد، فرض صفر رد می شود.

نقطه درصد توزیع با درجات آزادی که احتمال آزمون دو طرفه را می دهد کجاست

این فاصله ای است که شامل شیب کلی با احتمال 95٪ است.

برای نمونه‌های بزرگ، فرض کنید می‌توانیم با مقدار 1.96 تقریبی کنیم (یعنی آمار آزمون به طور معمول توزیع می‌شود)

ارزیابی کیفیت رگرسیون خطی: ضریب تعیین R2

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

اگر چنین است، بیشتر تغییرات با رگرسیون توضیح داده می شود و نقاط نزدیک به خط رگرسیون قرار می گیرند، یعنی. خط به خوبی با داده ها مطابقت دارد.

نسبت واریانس کل که با رگرسیون توضیح داده می شود نامیده می شود ضریب تعیین، معمولاً به صورت درصد بیان می شود و نشان داده می شود R2(در رگرسیون خطی زوجی، این مقدار است r2، مجذور ضریب همبستگی)، به شما امکان می دهد کیفیت معادله رگرسیون را به صورت ذهنی ارزیابی کنید.

تفاوت درصدی از واریانس است که با رگرسیون قابل توضیح نیست.

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

اعمال خط رگرسیون برای پیش بینی

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

با جایگزین کردن آن مقدار در معادله خط رگرسیون، میانگین را برای قابل مشاهده‌هایی که مقدار مشخصی دارند، پیش‌بینی می‌کنیم.

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

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

طرح های رگرسیون ساده

طرح های رگرسیون ساده حاوی یک پیش بینی پیوسته هستند. اگر 3 مورد با مقادیر پیش بینی P وجود داشته باشد، مانند 7، 4 و 9، و طرح شامل یک اثر مرتبه اول P باشد، ماتریس طراحی X خواهد بود.

و معادله رگرسیون با استفاده از P برای X1 به نظر می رسد

Y = b0 + b1 P

اگر یک طرح رگرسیون ساده دارای یک اثر مرتبه بالاتر بر روی P باشد، مانند یک اثر درجه دوم، مقادیر ستون X1 در ماتریس طراحی به توان دوم افزایش می یابد:

و معادله شکل خواهد گرفت

Y = b0 + b1 P2

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

مثال: تحلیل رگرسیون ساده

این مثال از داده های ارائه شده در جدول استفاده می کند:

برنج. 3. جدول داده های اولیه.

داده ها بر اساس مقایسه سرشماری های سال های 1960 و 1970 در 30 شهرستان به طور تصادفی انتخاب شده اند. نام شهرستان ها به عنوان نام های مشاهده ای نشان داده می شود. اطلاعات مربوط به هر متغیر در زیر ارائه شده است:

برنج. 4. جدول مشخصات متغیر.

هدف پژوهش

برای این مثال، همبستگی بین نرخ فقر و قدرت پیش‌بینی‌کننده درصد خانواده‌هایی که زیر خط فقر هستند، تحلیل می‌شود. بنابراین، متغیر 3 (Pt_Poor) را به عنوان یک متغیر وابسته در نظر خواهیم گرفت.

می توان یک فرضیه را مطرح کرد: تغییر جمعیت و درصد خانواده هایی که زیر خط فقر هستند با هم مرتبط هستند. منطقی به نظر می رسد انتظار داشته باشیم که فقر منجر به خروج جمعیت شود، بنابراین بین درصد افراد زیر خط فقر و تغییر جمعیت همبستگی منفی وجود دارد. بنابراین، ما با متغیر 1 (Pop_Chng ) به عنوان یک متغیر پیش بینی رفتار خواهیم کرد.

مشاهده نتایج

ضرایب رگرسیون

برنج. 5. ضرایب رگرسیون Pt_Poor در Pop_Chng.

در تقاطع ردیف Pop_Chng و Param. ضریب غیر استاندارد برای رگرسیون Pt_Poor در Pop_Chng -0.40374 است. این بدان معناست که به ازای هر واحد کاهش جمعیت، نرخ فقر 40374/0 افزایش می یابد. حد اطمینان 95 درصد بالا و پایین (پیش‌فرض) برای این ضریب غیر استاندارد صفر را شامل نمی‌شود، بنابراین ضریب رگرسیون در سطح p معنی‌دار است.<.05 . Обратите внимание на не стандартизованный коэффициент, который также является коэффициентом корреляции Пирсона для простых регрессионных планов, равен -.65, который означает, что для каждого уменьшения стандартного отклонения численности населения происходит увеличение стандартного отклонения уровня бедности на.65.

توزیع متغیرها

ضرایب همبستگی در صورت وجود مقادیر پرت بزرگ در داده‌ها، می‌توانند به‌طور قابل‌توجهی بیش‌ازحد برآورد یا دست‌کم‌گرفته شوند. اجازه دهید توزیع متغیر وابسته Pt_Poor را بر اساس شهرستان بررسی کنیم. برای این کار یک هیستوگرام از متغیر Pt_Poor می سازیم.

برنج. 6. هیستوگرام متغیر Pt_Poor.

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

برنج. 7. هیستوگرام متغیر Pt_Poor.

این قضاوت تا حدودی ذهنی است. قاعده کلی این است که اگر یک مشاهده (یا مشاهدات) در بازه زمانی (میانگین 3 ± انحراف استاندارد) قرار نگیرد، باید موارد پرت را در نظر گرفت. در این مورد، ارزش آن را دارد که تجزیه و تحلیل با و بدون پرت تکرار شود تا مطمئن شوید که آنها تأثیر جدی بر همبستگی بین اعضای جامعه ندارند.

طرح پراکنده

اگر یکی از فرضیه ها پیشینی در مورد رابطه بین متغیرهای داده شده باشد، بررسی آن در نمودار پراکندگی مربوطه مفید است.

برنج. 8. Scatterplot.

نمودار پراکندگی یک همبستگی منفی واضح (65/0-) بین دو متغیر را نشان می دهد. همچنین فاصله اطمینان 95% را برای خط رگرسیون نشان می دهد، یعنی با احتمال 95% خط رگرسیون از بین دو منحنی چین عبور می کند.

معیارهای اهمیت

برنج. 9. جدول حاوی معیارهای اهمیت.

آزمون ضریب رگرسیون Pop_Chng تایید می کند که Pop_Chng به شدت با Pt_Poor، p مرتبط است.<.001 .

نتیجه

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

واگذاری خدمات. با کمک سرویس آنلاین می توانید پیدا کنید:

• پارامترهای معادله رگرسیون خطی y=a+bx، ضریب همبستگی خطی با آزمون اهمیت آن.
• تنگی اتصال با استفاده از شاخص‌های همبستگی و تعیین، برآورد حداقل مربعات، پایایی استاتیکی مدل‌سازی رگرسیون با استفاده از آزمون F فیشر و آزمون تی دانشجویی، فاصله اطمینان پیش‌بینی برای سطح معناداری α

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

مثال. برای ساخت یک مدل رگرسیون زوجی، متغیر وابسته (توضیح داده شده) و توضیحی را انتخاب کنید. دادن . معادله رگرسیون جفت نظری را تعیین کنید. کفایت مدل ساخته شده را ارزیابی کنید (تفسیر R-square، t-statistics، F-statistics).
راه حلبر اساس خواهد بود فرآیند مدلسازی اقتصادسنجی.
مرحله 1 (مرحله بندی) - تعیین اهداف نهایی مدل سازی، مجموعه ای از عوامل و شاخص های شرکت کننده در مدل و نقش آنها.
مشخصات مدل - تعریف هدف مطالعه و انتخاب متغیرهای اقتصادی مدل.
تکلیف موقعیتی (عملی). برای 10 شرکت در منطقه، ما وابستگی تولید به ازای هر کارگر y (هزار روبل) را به سهم کارگران بسیار ماهر در تعداد کل کارگران x (بر حسب درصد) مطالعه می کنیم.
مرحله 2 (پیشینی) - تجزیه و تحلیل پیش مدل از ماهیت اقتصادی پدیده مورد مطالعه، تشکیل و رسمی سازی اطلاعات پیشینی و مفروضات اولیه، به ویژه، مربوط به ماهیت و پیدایش داده های آماری اولیه و باقیمانده تصادفی مولفه ها در قالب یک سری فرضیه.
در حال حاضر در این مرحله می‌توان در مورد وابستگی آشکار سطح مهارت کارگر و خروجی او صحبت کرد، زیرا هرچه کارگر با تجربه‌تر باشد، بهره‌وری او بالاتر می‌رود. اما چگونه می توان این وابستگی را ارزیابی کرد؟
رگرسیون جفتیک رگرسیون بین دو متغیر - y و x است، یعنی مدلی از شکل:

جایی که y متغیر وابسته است (علامت حاصل)؛ x یک متغیر مستقل یا توضیحی (عامل علامت) است. علامت "^" به این معنی است که هیچ وابستگی عملکردی دقیقی بین متغیرهای x و y وجود ندارد، بنابراین، تقریباً در هر مورد جداگانه، مقدار y از دو عبارت تشکیل شده است:

جایی که y مقدار واقعی ویژگی مؤثر است. y x مقدار نظری ویژگی مؤثر است که بر اساس معادله رگرسیون یافت می شود. ε یک متغیر تصادفی است که انحرافات مقدار واقعی ویژگی حاصل از مقدار نظری یافت شده توسط معادله رگرسیون را مشخص می کند.
ما به صورت گرافیکی وابستگی رگرسیونی بین خروجی هر کارگر و نسبت کارگران بسیار ماهر را نشان خواهیم داد.

مرحله 3 (پارامتریزاسیون) - مدل سازی واقعی، به عنوان مثال. انتخاب شکل کلی مدل، شامل ترکیب و شکل روابط بین متغیرهای موجود در آن. به انتخاب نوع وابستگی تابعی در معادله رگرسیون، پارامترسازی مدل می گویند. انتخاب کنید معادله رگرسیون زوجی، یعنی تنها یک عامل بر نتیجه نهایی y تأثیر می گذارد.
مرحله چهارم (اطلاعاتی) - جمع آوری اطلاعات آماری لازم، یعنی. ثبت مقادیر عوامل و شاخص های شرکت کننده در مدل. نمونه شامل 10 شرکت صنعتی است.
مرحله 5 (شناسایی مدل) - تخمین پارامترهای مدل ناشناخته با استفاده از داده های آماری موجود.
برای تعیین پارامترهای مدل از LSM - روش حداقل مربعات. سیستم معادلات عادی به صورت زیر خواهد بود:
a n + b∑x = ∑y
a∑x + b∑x 2 = ∑y x
برای محاسبه پارامترهای رگرسیون، یک جدول محاسباتی می سازیم (جدول 1).

ایکس	y	x2	y2	x y
10	6	100	36	60
12	6	144	36	72
15	7	225	49	105
17	7	289	49	119
18	7	324	49	126
19	8	361	64	152
19	8	361	64	152
20	9	400	81	180
20	9	400	81	180
21	10	441	100	210
171	77	3045	609	1356

داده ها را از جدول 1 (ردیف آخر) می گیریم، در نتیجه داریم:
10a + 171b = 77
171 a + 3045 b = 1356
این SLAE با روش کرامر یا روش ماتریس معکوس حل می شود.
ما ضرایب رگرسیون تجربی را دریافت می کنیم: b = 0.3251، a = 2.1414
معادله رگرسیون تجربی به شکل زیر است:
y = 0.3251 x + 2.1414
مرحله 6 (تأیید مدل) - مقایسه داده های واقعی و مدل، تأیید کفایت مدل، ارزیابی صحت داده های مدل.
تجزیه و تحلیل با استفاده از

رگرسیون و تحلیل همبستگی - روشهای تحقیق آماری. اینها رایج ترین راه ها برای نشان دادن وابستگی یک پارامتر به یک یا چند متغیر مستقل هستند.

در زیر، با استفاده از مثال‌های عملی عینی، این دو تحلیل بسیار محبوب در میان اقتصاددانان را بررسی خواهیم کرد. ما همچنین مثالی از به دست آوردن نتایج در هنگام ترکیب آنها خواهیم داد.

تجزیه و تحلیل رگرسیون در اکسل

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

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

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

• خطی (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 این کار را سریعتر انجام می دهد.

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

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

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

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

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

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

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



تجزیه و تحلیل همبستگی در اکسل

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

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

ضریب همبستگی r نشان داده می شود. از +1 تا -1 متغیر است. طبقه بندی همبستگی ها برای حوزه های مختلف متفاوت خواهد بود. وقتی مقدار ضریب 0 باشد، هیچ رابطه خطی بین نمونه ها وجود ندارد.

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

تابع CORREL برای یافتن ضرایب جفت شده استفاده می شود.

وظیفه: تعیین کنید که آیا رابطه ای بین زمان کار ماشین تراش و هزینه نگهداری آن وجود دارد یا خیر.

مکان نما را در هر سلولی قرار دهید و دکمه fx را فشار دهید.

1. در دسته «آماری»، تابع CORREL را انتخاب کنید.
2. آرگومان "آرایه 1" - اولین محدوده مقادیر - زمان ماشین: A2: A14.
3. آرگومان "آرایه 2" - دومین محدوده مقادیر - هزینه تعمیرات: B2:B14. روی OK کلیک کنید.

برای تعیین نوع اتصال، باید به عدد مطلق ضریب نگاه کنید (هر زمینه فعالیت مقیاس خاص خود را دارد).

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

ضرایب حاصل در ماتریس همبستگی نمایش داده می شود. شبیه این یکی:

تحلیل همبستگی-رگرسیون

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

مثال:

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

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

1) ستون های دارای داده را انتخاب کنید ایکسو Y(آنها باید به این ترتیب باشند!)

2) تماس بگیرید جادوگر نمودارو در یک گروه انتخاب کنید تایپ کنید – خط چینو بلافاصله فشار دهید آماده;

3) بدون لغو انتخاب نمودار، آیتم منوی اصلی ظاهر شده را انتخاب کنید نمودار، که در آن باید مورد را انتخاب کنید خط روند را اضافه کنید;

4) در محاوره ای که ظاهر می شود خط روندبرگه تایپ کنیدانتخاب کنید خطی;

5) برگه گزینه هاسوئیچ را می توان فعال کرد معادله را روی نمودار نشان دهید، که به شما امکان می دهد معادله رگرسیون خطی (4.4) را مشاهده کنید که در آن ضرایب (4.5) محاسبه می شود.

6) در همان تب می توانید سوئیچ را فعال کنید مقدار اطمینان تقریبی را روی نمودار قرار دهید (R^2). این مقدار مجذور ضریب همبستگی (4.3) است و نشان می دهد که معادله محاسبه شده چقدر وابستگی تجربی را توصیف می کند. اگر آر 2 نزدیک به وحدت است، سپس معادله رگرسیون نظری وابستگی تجربی را به خوبی توصیف می کند (نظریه به خوبی با آزمایش موافق است) و اگر آر 2 نزدیک به صفر است، پس این معادله برای توصیف وابستگی تجربی مناسب نیست (نظریه با آزمایش موافق نیست).

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

§4.3. انواع اصلی رگرسیون غیر خطی

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

سهمویوابستگی به ارزش Yاز ارزش ایکسوابستگی بیان شده توسط یک تابع درجه دوم (پارابولای مرتبه دوم) نامیده می شود:

این معادله نامیده می شود رگرسیون سهموی Yبر ایکس. گزینه ها آ, ب, باتماس گرفت ضرایب رگرسیون سهموی. محاسبه ضرایب رگرسیون سهموی همیشه دست و پا گیر است، بنابراین توصیه می شود از رایانه برای محاسبات استفاده کنید.

معادله (4.8) رگرسیون سهموی یک مورد خاص از یک رگرسیون عمومی تر به نام چند جمله ای است. چند جمله ایوابستگی به ارزش Yاز ارزش ایکسوابستگی بیان شده توسط چند جمله ای نامیده می شود n- مرتبه:

اعداد کجا هستند یک من (من=0,1,…, n) نامیده می شوند ضرایب رگرسیون چند جمله ای.

رگرسیون قدرت.

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

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

ln=ln آ+بلوگاریتم ایکس. (4.11)

این معادله یک خط مستقیم را در صفحه با محورهای مختصات لگاریتمی ln توصیف می کند. ایکسو ln. بنابراین، معیار کاربردی بودن رگرسیون توانی این شرط است که نقاط لگاریتم داده های تجربی ln x iو ln مننزدیکترین به خط مستقیم بودند (4.11).

رگرسیون نمایی

نمونه(یا نمایی) وابستگی به کمیت Yاز ارزش ایکسوابستگی شکل نامیده می شود:

(یا ). (4.12)

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

اگر لگاریتم دو طرف معادله رگرسیون توان را بگیریم، معادله را بدست می آوریم.

ln = ایکسلوگاریتم آ+ln ب(یا ln = k x+ln ب). (4.13)

این معادله وابستگی خطی لگاریتم یک کمیت ln به کمیت دیگر را توصیف می کند. ایکس. بنابراین، ملاک کاربردی بودن رگرسیون توان این است که داده‌های تجربی به اندازه یکسان باشند. x iو لگاریتمی با مقدار دیگری ln مننزدیکترین به خط مستقیم بودند (4.13).

رگرسیون لگاریتمی

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

=آ+بلوگاریتم ایکس. (4.14)

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

رگرسیون هایپربولیک

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

این معادله نامیده می شود معادله رگرسیون هذلولی Yبر ایکس. گزینه ها آو بتماس گرفت ضرایب رگرسیون هایپربولیکو با روش حداقل مربعات تعیین می شوند. استفاده از این روش به فرمول های زیر منجر می شود:

در فرمول های (4.16-4.17)، جمع بر روی شاخص انجام می شود مناز یک به تعداد مشاهدات n.

متاسفانه در برتری داشتنهیچ تابعی وجود ندارد که ضرایب رگرسیون هذلولی را محاسبه کند. در مواردی که به طور قطع مشخص نیست که مقادیر اندازه گیری شده با نسبت معکوس مرتبط هستند، توصیه می شود به جای معادله رگرسیون هذلولی به دنبال معادله رگرسیون توان بگردید، بنابراین در برتری داشتنروشی برای یافتن آن وجود دارد. اگر یک وابستگی هذلولی بین مقادیر اندازه گیری شده در نظر گرفته شود، ضرایب رگرسیون آن باید با استفاده از جداول محاسبه کمکی و عملیات جمع با استفاده از فرمول های (4.16-4.17) محاسبه شود.

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

برای این شما نیاز دارید:

1.برنامه اکسل را باز کنید

2. ستون هایی با داده ایجاد کنید. در مثال خود، ما یک خط رگرسیون یا رابطه بین پرخاشگری و شک به خود در دانش آموزان کلاس اول ایجاد می کنیم. این آزمایش شامل 30 کودک بود که داده ها در جدول اکسل ارائه شده است:

1 ستون - شماره موضوع

2 ستون - پرخاشگریدر امتیاز

3 ستون - اختلاف نظردر امتیاز

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

4. بنابراین ما یک خالی برای خط رگرسیون گرفتیم - به اصطلاح - طرح پراکنده. برای رفتن به خط رگرسیون، باید روی شکل حاصل کلیک کنید، روی تب کلیک کنید سازنده، در پانل پیدا کنید طرح بندی نمودار و انتخاب کنید م آ ket9 ، همچنین می گوید f(x)

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

6. باقی مانده است که نام نمودار، نام محورها را اضافه کنید. همچنین، در صورت تمایل، می توانید افسانه را حذف کنید، تعداد خطوط شبکه افقی را کاهش دهید (برگه چیدمان ، سپس خالص ). تغییرات و تنظیمات اصلی در تب انجام می شود چیدمان

خط رگرسیون در MS Excel ساخته شده است. حال می توان آن را به متن اثر اضافه کرد.