چگونه یک معادله رگرسیون در اکسل بسازیم. روشهای ریاضی در روانشناسی

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

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) محاسبه شود.

ساخت یک رگرسیون خطی، تخمین پارامترهای آن و اهمیت آنها در هنگام استفاده از بسته تحلیل اکسل (رگرسیون) بسیار سریعتر انجام می شود. اجازه دهید تفسیر نتایج به دست آمده را در حالت کلی در نظر بگیریم ( کمتغیرهای توضیحی) مطابق با مثال 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-آمار.

آر-value= استودراسپ(تی-آمار، 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% معنی دار است.

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

تحلیل همبستگی-رگرسیون درام‌اس برتری داشتن

1. یک فایل داده منبع در MS Excel ایجاد کنید (به عنوان مثال، جدول 2)

2. ساخت میدان همبستگی

برای ایجاد یک فیلد همبستگی در خط فرمانمنوی انتخاب کنید درج / نمودار. در کادر محاوره ای که ظاهر می شود، نوع نمودار را انتخاب کنید: خط چین; چشم انداز: طرح پراکنده، به شما امکان می دهد جفت مقادیر را با هم مقایسه کنید (شکل 22).

شکل 22 - انتخاب نوع نمودار

2. در منوی زمینهیک تیم انتخاب کنید یک خط روند اضافه کنید.

3. در کادر محاوره ای ظاهر شده، نوع نمودار (در مثال ما خطی) و پارامترهای معادله را مانند شکل 26 انتخاب کنید.

شکل 27 - زمینه همبستگی وابستگی بهره وری نیروی کار به نسبت سرمایه به نیروی کار

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

از ضریب تغییر تجهیزات

3. ساخت ماتریس همبستگی.

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

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

پس از کلیک بر روی OK، در کادر محاوره ای که ظاهر می شود، فاصله ورودی (در مثال ما A2: D26)، گروه بندی (در مورد ما بر اساس ستون ها) و پارامترهای خروجی را مشخص کنید، همانطور که در شکل 31 نشان داده شده است.

نتیجه محاسبات در جدول 4 ارائه شده است.

جدول 4 - ماتریس همبستگی

تجزیه و تحلیل رگرسیون تک متغیری

با استفاده از ابزار رگرسیون

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

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

اتصال بسته تحلیلی

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

1. به تب "فایل" بروید.
2. به بخش "تنظیمات" بروید.
3. پنجره Excel Options باز می شود. به بخش فرعی "افزونه ها" بروید.
4. در قسمت پایین پنجره ای که باز می شود، سوئیچ را در بلوک "Management" به موقعیت "Excel Add-ins" تغییر می دهیم، اگر در موقعیت دیگری قرار دارد. بر روی دکمه "برو" کلیک کنید.
5. پنجره افزونه های اکسل باز می شود. کادر کنار «بسته تحلیل» را علامت بزنید. بر روی دکمه "OK" کلیک کنید.

اکنون، هنگامی که به تب "داده ها" می رویم، روی نوار موجود در بلوک ابزار "Analysis"، دکمه جدیدی را مشاهده می کنیم - "تحلیل داده ها".

انواع تحلیل رگرسیون

چندین نوع رگرسیون وجود دارد:

• سهموی
• قدرت؛
• لگاریتمی؛
• نمایی;
• تظاهرات؛
• هذلولی
• رگرسیون خطی.

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

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

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

معادله رگرسیون خطی کلی به این صورت است: Y = a0 + a1x1 + ... + axk. در این فرمول، Y به معنای متغیری است که سعی داریم تأثیر عوامل را بر روی آن بررسی کنیم. در مورد ما، این تعداد خریداران است. مقدار x عوامل مختلفی است که بر متغیر تأثیر می گذارد. پارامترهای a ضرایب رگرسیون هستند. یعنی اهمیت یک عامل خاص را تعیین می کنند. شاخص k تعداد کل این عوامل را نشان می دهد.

تجزیه و تحلیل نتایج تجزیه و تحلیل

نتایج تحلیل رگرسیون به صورت جدول در محل مشخص شده در تنظیمات نمایش داده می شود.

یکی از شاخص های اصلی R-square است. این نشان دهنده کیفیت مدل است. در مورد ما، این ضریب 0.705 یا حدود 70.5٪ است. این سطح کیفی قابل قبولی است. رابطه کمتر از 0.5 بد است.

یکی دیگر شاخص مهمواقع در سلول در تقاطع ردیف "تقاطع Y" و ستون "ضرایب". در اینجا مشخص شده است که Y چه مقدار خواهد داشت و در مورد ما، این تعداد خریداران است، با سایر عوامل برابر با صفر. در این جدول ارزش داده شدهبرابر با 58.04 است.

مقدار در تقاطع ستون "متغیر X1" و "ضرایب" سطح وابستگی Y به X را نشان می دهد. در مورد ما، این میزان وابستگی تعداد مشتریان فروشگاه به دما است. ضریب 1.31 یک شاخص نسبتاً بالای تأثیر در نظر گرفته می شود.

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

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

سوال خود را در نظرات بپرسید و ماهیت مشکل را با جزئیات شرح دهید. کارشناسان ما سعی خواهند کرد در اسرع وقت پاسخ دهند.

آیا این مقاله به شما کمک کرد؟

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

ŷ مقدار مورد انتظار y در است مقدار را تنظیم کنیدایکس،

x - متغیر مستقل،

a - بخش در محور y برای یک خط مستقیم،

b شیب خط مستقیم است.

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

شکل بالا خطی را نشان می دهد که با معادله ŷ =2+0.5x توصیف شده است. پاره روی محور y نقطه تقاطع خط با محور y است. در مورد ما، a = 2. شیب خط، b، نسبت افزایش خط به طول خط، مقدار 0.5 دارد. شیب مثبت به این معنی است که خط از چپ به راست بالا می رود. اگر b = 0، خط افقی است، به این معنی که هیچ رابطه ای بین متغیرهای وابسته و مستقل وجود ندارد. به عبارت دیگر تغییر مقدار x تاثیری بر مقدار y ندارد.

ŷ و y اغلب اشتباه گرفته می شوند. نمودار مطابق معادله داده شده 6 جفت نقطه مرتب و یک خط را نشان می دهد

این شکل نقطه مربوط به جفت مرتب شده x = 2 و y = 4 را نشان می دهد. توجه داشته باشید که مقدار مورد انتظار y با توجه به خط در ایکس= 2 برابر ŷ است. ما می توانیم این را با معادله زیر تأیید کنیم:

ŷ = 2 + 0.5x = 2 + 0.5 (2) = 3.

مقدار y نقطه واقعی و مقدار ŷ مقدار y مورد انتظار با استفاده از آن است معادله خطیبرای مقدار معین x

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

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

برای استفاده از ابزار تحلیل رگرسیون ساخته شده در اکسل، باید افزونه را فعال کنید بسته تحلیلی. با کلیک بر روی برگه می توانید آن را پیدا کنید فایل –> گزینه ها(2007+)، در گفتگوی ظاهر شده گزینه هابرتری داشتنبه برگه بروید افزونه هادر زمینه کنترلانتخاب کنید افزونه هابرتری داشتنو کلیک کنید برودر پنجره ای که ظاهر می شود، کادر کناری را علامت بزنید بسته تحلیلی،کلیک خوب.

در برگه داده هادر گروه تحلیل و بررسیپدیدار خواهد شد دکمه جدید تحلیل داده ها.

برای نشان دادن نحوه عملکرد این افزونه، از داده های مقاله قبلی استفاده می کنیم، جایی که یک پسر و یک دختر در حمام یک میز را به اشتراک می گذارند. داده های مثال حمام ما را در ستون های A و B یک صفحه خالی وارد کنید.

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

پارامترهای رگرسیون مورد نیاز را در پنجره تنظیم کنید پسرفت، همانطور که در تصویر نشان داده شده است:

کلیک خوب.شکل زیر نتایج به دست آمده را نشان می دهد:

این نتایج با نتایجی که با محاسبات مستقل در مقاله قبلی به دست آوردیم مطابقت دارد.

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

انواع رگرسیون

خود این مفهوم توسط فرانسیس گالتون در سال 1886 وارد ریاضیات شد. رگرسیون اتفاق می افتد:

• خطی؛
• سهموی
• قدرت؛
• نمایی;
• هذلولی
• نمایشی؛
• لگاریتمی

مثال 1

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

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

برای مسئله تعیین وابستگی تعداد کارگران اخراج شده به میانگین حقوق در 6 شرکت، مدل رگرسیون به شکل معادله Y = a0 + a1 × 1 + ... + akxk است که xi عبارتند از: متغیرهای تأثیرگذار، ai ضرایب رگرسیون و k تعداد عوامل است.

برای این کار، Y نشانگر کارمندانی است که ترک کرده اند و عامل تأثیرگذار حقوق است که آن را با X نشان می دهیم.

استفاده از قابلیت های صفحه گسترده "اکسل"

تجزیه و تحلیل رگرسیون در اکسل باید قبل از اعمال توابع داخلی برای داده های جدولی موجود باشد. با این حال، برای این اهداف، بهتر است از افزونه بسیار مفید "Analysis Toolkit" استفاده کنید. برای فعال کردن آن نیاز دارید:

• از برگه "فایل" به بخش "گزینه ها" بروید.
• در پنجره ای که باز می شود، خط "افزونه ها" را انتخاب کنید.
• روی دکمه "Go" واقع در پایین، سمت راست خط "Management" کلیک کنید.
• کادر کنار نام «بسته تجزیه و تحلیل» را علامت بزنید و با کلیک روی «OK» اقدامات خود را تأیید کنید.

اگر همه چیز به درستی انجام شود، دکمه مورد نظر در سمت راست تب Data که در بالای کاربرگ اکسل قرار دارد ظاهر می شود.

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

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

• بر روی دکمه "تجزیه و تحلیل داده ها" کلیک کنید؛
• در پنجره ای که باز می شود، روی دکمه "Regression" کلیک کنید.
• در برگه ای که ظاهر می شود، محدوده مقادیر Y (تعداد کارمندانی که ترک می کنند) و X (حقوق آنها) را وارد کنید.
• ما اقدامات خود را با فشار دادن دکمه "Ok" تأیید می کنیم.

در نتیجه، برنامه به طور خودکار یک برگه جدید را پر می کند پردازنده صفحه گستردهداده های تحلیل رگرسیون توجه داشته باشید! اکسل این قابلیت را دارد که به صورت دستی مکان مورد نظر شما را برای این منظور تنظیم کند. به عنوان مثال، می تواند همان برگه ای باشد که مقادیر Y و X در آن هستند یا حتی یک کتاب جدید، به ویژه برای ذخیره چنین داده هایی طراحی شده است.

تجزیه و تحلیل نتایج رگرسیون برای R-square

که در داده های اکسلبه دست آمده در هنگام پردازش داده های مثال در نظر گرفته شده به شکل زیر است:

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

در مسئله مورد بررسی برای عضو آزاد با استفاده از ابزار اکسل به دست آمد که t = 169.20903 و p = 2.89E-12، یعنی احتمال صفر داریم که فرضیه صحیح در مورد بی اهمیت بودن عضو آزاد باشد. رد شد. برای ضریب مجهول t=5.79405 و p=0.001158. به عبارت دیگر، احتمال رد فرضیه صحیح در مورد بی اهمیت بودن ضریب مجهول 0.12 درصد است.

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

مشکل مصلحت خرید بلوک سهام

رگرسیون چندگانه در اکسل با استفاده از همان ابزار تحلیل داده انجام می شود. یک مشکل کاربردی خاص را در نظر بگیرید.

مدیریت NNN باید در مورد امکان خرید 20 درصد از سهام MMM SA تصمیم گیری کند. هزینه بسته (JV) 70 میلیون دلار آمریکا می باشد. متخصصان NNN داده های مربوط به تراکنش های مشابه را جمع آوری کردند. تصمیم بر این شد که ارزش بلوک سهام با توجه به پارامترهایی که به میلیون ها دلار آمریکا بیان می شود، ارزیابی شود:

• حساب های پرداختنی (VK)؛
• گردش مالی سالانه (VO)؛
• حساب های دریافتنی (VD)؛
• هزینه دارایی های ثابت (SOF).

علاوه بر این، پارامتر حقوق و دستمزد معوقه شرکت (V3 P) به هزار دلار آمریکا استفاده می شود.

راه حل با استفاده از صفحه گسترده اکسل

اول از همه، شما باید یک جدول از داده های اولیه ایجاد کنید. به نظر می رسد این است:

• با پنجره "تجزیه و تحلیل داده ها" تماس بگیرید.
• بخش "Regression" را انتخاب کنید؛
• در کادر "فاصله ورودی Y" محدوده مقادیر متغیرهای وابسته را از ستون G وارد کنید.
• روی نماد با یک فلش قرمز در سمت راست کادر "Input interval X" کلیک کنید و محدوده ای از تمام مقادیر را در برگه انتخاب کنید. ستون B,C، دی، اف.

«کاربرگ جدید» را انتخاب کرده و روی «تأیید» کلیک کنید.

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

بررسی نتایج و نتیجه گیری

"ما" از داده های گرد ارائه شده در بالا در صفحه گسترده اکسل، معادله رگرسیون جمع آوری می کنیم:

SP \u003d 0.103 * SOF + 0.541 * VO - 0.031 * VK + 0.405 * VD + 0.691 * VZP - 265.844.

در یک شکل ریاضی آشناتر، می توان آن را به صورت زیر نوشت:

y = 0.103*x1 + 0.541*x2 - 0.031*x3 +0.405*x4 +0.691*x5 - 265.844

داده های JSC "MMM" در جدول ارائه شده است:

با جایگزینی آنها در معادله رگرسیون، رقمی معادل 64.72 میلیون دلار آمریکا بدست می آید. این به این معنی است که سهام JSC MMM نباید خریداری شود، زیرا ارزش 70 میلیون دلاری آنها بسیار زیاد است.

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

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

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

برای کار نیاز به افزودنی دارد بسته تحلیلی، که باید در آیتم منو فعال شود سرویس\افزونه ها

در Excel 2007، برای فعال کردن Analysis Pack، روی Go to Block کلیک کنید گزینه های اکسل با فشار دادن دکمه سمت چپ گوشه بالاییو سپس دکمه گزینه های اکسل» در پایین پنجره:

برای ساخت یک مدل رگرسیون، مورد را انتخاب کنید سرویس\تجزیه و تحلیل داده\رگرسیون. (در اکسل 2007، این حالت در داده ها / تجزیه و تحلیل داده ها / رگرسیون). یک کادر محاوره ای ظاهر می شود که باید پر شود:

1) فاصله ورودی Y¾ حاوی پیوندی به سلول هایی است که حاوی مقادیر مشخصه به دست آمده است y. مقادیر باید در یک ستون باشند.

2) فاصله ورودی X¾ حاوی پیوندی به سلول هایی است که حاوی مقادیر فاکتورها هستند. مقادیر باید در ستون باشد.

3) امضا کنید برچسب هاتنظیم کنید که سلول های اول حاوی متن توضیحی (برچسب های داده) باشند.

4) سطح قابلیت اطمینان¾ سطح اطمینان است که به طور پیش فرض 95 درصد در نظر گرفته شده است. اگر این مقدار برای شما مناسب نیست، باید این ویژگی را فعال کنید و مقدار مورد نیاز را وارد کنید.

5) امضا کنید صفر ثابتاگر لازم باشد معادله ای ساخته شود که در آن متغیر آزاد ;

6) گزینه های خروجیتعیین کنید که نتایج باید در کجا قرار گیرند. حالت ساخت پیش فرض کاربرگ جدید;

7) مسدود کردن باقیبه شما امکان می دهد خروجی باقیمانده ها و ساخت نمودارهای آنها را درج کنید.

در نتیجه، اطلاعاتی نمایش داده می شود که حاوی تمام اطلاعات لازم است و در سه بلوک گروه بندی می شود: آمار رگرسیون, تحلیل واریانس, برداشت موجودی. بیایید آنها را با جزئیات بیشتری در نظر بگیریم.

1. آمار رگرسیون:

چندگانه آربا فرمول ( ضریب همبستگی پیرسون);

آر (ضریب تعیین);

عادی شده است آرمربع با فرمول محاسبه می شود (استفاده شده برای رگرسیون چندگانه);

خطای استاندارد اسبا فرمول محاسبه می شود ;

مشاهدات ¾ مقدار داده است n.

2. تحلیل واریانس، خط پسرفت:

پارامتر dfبرابر است متر(تعداد مجموعه عوامل ایکس);

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

پارامتر ام‌اسبا فرمول تعیین می شود؛

آمار افبا فرمول تعیین می شود؛

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

3. تحلیل واریانس، خط باقی مانده:

پارامتر dfبرابر است؛

پارامتر اس اسبا فرمول تعیین می شود ;

پارامتر ام‌اسبا فرمول تعیین می شود.

4. تحلیل واریانس، خط جمعشامل مجموع دو ستون اول است.

5. تحلیل واریانس، خط تقاطع Yحاوی مقدار ضریب، خطای استاندارد و تی-آمار.

پ- ارزش ¾ مقدار سطوح معنی داری مربوط به محاسبه شده است تی- آماردانان تعیین شده توسط STUDIST( تی-آمار؛ ). اگر پ-value فراتر می رود، سپس متغیر مربوطه از نظر آماری ناچیز است و می تواند از مدل حذف شود.

95% پایینو 95% برتر¾ مرزهای پایین و بالای فاصله اطمینان 95 درصد برای ضرایب معادله رگرسیون خطی نظری هستند. اگر مقدار احتمال اطمینان در بلوک ورودی داده به طور پیش‌فرض باقی مانده باشد، دو ستون آخر ستون‌های قبلی را کپی می‌کنند. اگر کاربر یک مقدار اطمینان سفارشی را وارد کرده باشد، دو ستون آخر حاوی مقادیر کران پایین و بالایی برای سطح اطمینان مشخص شده است.

6. تحلیل واریانس، سطرها حاوی مقادیر ضرایب هستند، خطاهای استاندارد, تی-آمارشناس، پ- مقادیر و فواصل اطمینان برای موارد مربوطه.

7. مسدود کردن برداشت موجودیحاوی مقادیر پیش بینی شده است y(در نماد ما این است) و باقی مانده است.