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

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

وظایف اصلی و انواع رگرسیون

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

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

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

1. انتخاب متغیرهای مستقل مهم (Х1، Х2، ...، Xk).
2. انتخاب نوع عملکرد
3. ساخت تخمین برای ضرایب.
4. ساخت فواصل اطمینان و توابع رگرسیون.
5. بررسی اهمیت برآوردهای محاسبه شده و معادله رگرسیون ساخته شده.

انواع مختلفی از تحلیل رگرسیون وجود دارد:

• زوج (1 متغیر وابسته و 1 متغیر مستقل)؛
• چندگانه (چند متغیر مستقل).

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

1. خطی، نشان دادن یک سخت اتصال خطیبین متغیرها
2. غیر خطی - معادلاتی که می توانند شامل توان ها، کسرها و توابع مثلثاتی باشند.

دستورالعمل های ساخت مدل

برای انجام یک ساختار داده شده در اکسل، باید دستورالعمل ها را دنبال کنید:

برای محاسبه بیشتر، باید از تابع "Linear()" استفاده شود که مقادیر Y، X مقادیر، Const و آمار را مشخص می کند. پس از آن، مجموعه نقاط روی خط رگرسیون را با استفاده از تابع "Trend" تعیین کنید - مقادیر Y، X-values، مقادیر جدید، Const. با کمک پارامترها را تنظیم کنیدمقدار مجهول ضرایب را بر اساس شرایط داده شده مسئله محاسبه کنید.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

بسته MS Excel به شما این امکان را می دهد که هنگام ساخت یک معادله رگرسیون خطی، بیشتر کارها را خیلی سریع انجام دهید. درک چگونگی تفسیر نتایج بسیار مهم است. برای ساخت یک مدل رگرسیون، Tools\Data Analysis\Regression را انتخاب کنید (در Excel 2007 این حالت در بخش Data/Data Analysis/Regression قرار دارد). سپس نتایج به دست آمده را برای تجزیه و تحلیل در یک بلوک کپی کنید.