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

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

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

در اکسل 2007، برای فعال کردن بسته تجزیه و تحلیل، باید برو به مسدود کردن کلیک کنید گزینه های اکسلبا کلیک بر روی دکمه در گوشه سمت چپ بالا و سپس " گزینه های اکسل"در پایین پنجره:

برای ساخت یک مدل رگرسیون، باید مورد را انتخاب کنید سرویس\تجزیه و تحلیل داده\رگرسیون. (در اکسل 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(در نماد ما این است) و باقی مانده ها.

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

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

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

Y=a 0 +a 1 x 1 +...+a k x k

جایی که آ - پارامترهای رگرسیون (ضرایب)، ایکس - عوامل تاثیرگذار، ک - تعداد فاکتورهای مدل

اطلاعات اولیه

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

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

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

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

بسته تحلیلی

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

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

بسته تحلیلی را فعال کنید

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

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

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

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

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

پس از فعال سازی افزونه Analysis Pack، همیشه در تب منوی اصلی در دسترس خواهد بود داده هازیر لینک تحلیل داده ها

در پنجره ابزار فعال تحلیل داده هااز لیست احتمالاتی که جستجو و انتخاب می کنیم پسرفت

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

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

سلول‌های کلیدی با رنگ زرد پر شده‌اند؛ اینها مواردی هستند که قبل از هر چیز باید به آنها توجه کنید؛ سایر پارامترهای اهمیت نیز مهم هستند، اما تجزیه و تحلیل دقیق آنها احتمالاً به یک پست جداگانه نیاز دارد.

بنابراین، 0,865 - این R 2- ضریب تعیین، نشان می دهد که 86.5 درصد از پارامترهای محاسبه شده مدل، یعنی خود مدل، وابستگی و تغییرات پارامتر مورد مطالعه را توضیح می دهد. Yاز عوامل مورد مطالعه - X ها. اگر اغراق شده است، پس این نشان دهنده کیفیت مدل استو هر چه بالاتر باشد بهتر است. واضح است که نمی تواند بیشتر از 1 باشد و زمانی که R 2 بالای 0.8 باشد خوب در نظر گرفته می شود و اگر کمتر از 0.5 باشد، منطقی بودن چنین مدلی را می توان با خیال راحت زیر سوال برد.

حالا بیایید به ادامه مطلب برویم ضرایب مدل:
2079,85 - این یک 0- ضریبی که نشان می دهد در صورتی که همه عوامل استفاده شده در مدل برابر با 0 باشند Y چقدر خواهد بود، قابل درک است که این وابستگی به عوامل دیگری است که در مدل توضیح داده نشده است.
-0,0056 - یک 1- ضریبی که وزن تأثیر عامل x 1 را بر Y نشان می دهد، یعنی تعداد شرکت ها در یک مدل معین بر شاخص جمعیت فعال اقتصادی با وزن تنها 0.0056- (درجه تأثیر نسبتاً کمی) تأثیر می گذارد. ). علامت منفی نشان می‌دهد که این تأثیر منفی است، یعنی هر چه تعداد شرکت‌ها بیشتر باشد، جمعیت از نظر اقتصادی کمتر فعال می‌شود، مهم نیست که این معنی چقدر متناقض باشد.
-0,0026 - یک 2- ضریب تأثیر حجم سرمایه گذاری در سرمایه بر اندازه جمعیت فعال اقتصادی؛ بر اساس مدل، این تأثیر نیز منفی است.
0,0028 - یک 3- ضریب تأثیر درآمد جمعیت بر اندازه جمعیت فعال اقتصادی، در اینجا تأثیر مثبت است، یعنی طبق مدل، افزایش درآمد به افزایش اندازه جمعیت فعال اقتصادی کمک می کند.

بیایید ضرایب محاسبه شده را در مدل جمع آوری کنیم:

Y = 2079.85 - 0.0056x 1 - 0.0026x 2 + 0.0028x 3

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

تخمین و پیش بینی مدل

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

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

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

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

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

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

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

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

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

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

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

• سهموی
• آرام بخش
• لگاریتمی؛
• نمایی;
• نمایشی؛
• هذلولی
• رگرسیون خطی.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ŷ - مقدار مورد انتظار y برای مقدار معین x،

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 +…+аkxk است که xi متغیرهای تأثیرگذار هستند، ai ضرایب رگرسیون و k تعداد عوامل است.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

مورد «کاربرگ جدید» را علامت بزنید و روی «تأیید» کلیک کنید.

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

مطالعه نتایج و نتیجه گیری

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

SP = 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

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

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

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

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

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

اطلاعات اولیه:

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

در گزارش لحاظ شود
محاسبه پارامترهای معادله رگرسیون
مطالب نظری
معادله رگرسیون در مقیاس استاندارد
ضریب همبستگی چندگانه (شاخص همبستگی چندگانه)
ضرایب کشش جزئی
ارزیابی مقایسه ای تأثیر عوامل تحلیل شده بر مشخصه حاصل (d - ضرایب تعیین جداگانه)

بررسی کیفیت معادله رگرسیون ساخته شده
اهمیت ضرایب رگرسیون b i (آمار t. آزمون دانشجویی)
اهمیت معادله به عنوان یک کل (آمار F. آزمون فیشر). ضریب تعیین
آزمون های F جزئی

سطح اهمیت 0.005 0.01 0.025 0.05 0.1 0.25 0.4

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

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

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

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

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

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

• خطی (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 = a 0 + a 1 x 1 +…+ a k x k.

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

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

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

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

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

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

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

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

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

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

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

مثال:

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