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

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

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

حالا وقتی به تب می رویم "داده ها"، روی روبان در جعبه ابزار "تحلیل و بررسی"ما یک دکمه جدید خواهیم دید - "تحلیل داده ها".

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

معمولاً از تحلیل رگرسیون برای پیش‌بینی مقادیر یک متغیر استفاده می‌شود. هدف آن توسعه یک مدل آماری است که مقادیر متغیر وابسته یا پاسخ را از روی مقادیر حداقل یک متغیر مستقل یا توضیحی پیش بینی کند. در این یادداشت، ما یک رگرسیون خطی ساده را در نظر خواهیم گرفت - یک روش آماری که به ما امکان می دهد مقادیر متغیر وابسته را پیش بینی کنیم. Yتوسط مقادیر متغیر مستقل ایکس. یادداشت های زیر یک مدل رگرسیون چندگانه را توصیف می کنند که برای پیش بینی مقادیر متغیر مستقل طراحی شده است Yبا مقادیر چندین متغیر وابسته ( X 1، X 2، …، X k).

دانلود یادداشت در قالب یا فرمت، نمونه ها در قالب

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

جایی که ρ 1 ضریب همبستگی است. اگر ρ 1 = 0 (بدون خود همبستگی)، D≈ 2; اگر ρ 1 ≈ 1 (خودهمبستگی مثبت)، D≈ 0; اگر ρ 1 = -1 (خودهمبستگی منفی)، D ≈ 4.

در عمل، استفاده از معیار دوربین واتسون مبتنی بر مقایسه مقدار است Dبا ارزش های نظری انتقادی d Lو d Uبرای تعداد معینی از مشاهدات nتعداد متغیرهای مستقل مدل ک(برای رگرسیون خطی ساده ک= 1) و سطح معنی داری α. اگر D< d L ، فرضیه استقلال انحرافات تصادفی رد می شود (از این رو، یک خودهمبستگی مثبت وجود دارد). اگر D > d U، فرضیه رد نمی شود (یعنی خود همبستگی وجود ندارد). اگر d L< D < d U دلیل کافی برای تصمیم گیری وجود ندارد. زمانی که مقدار محاسبه شده است Dپس از 2 تجاوز کند d Lو d Uاین خود ضریب نیست که مقایسه می شود Dو عبارت (4- D).

برای محاسبه آمار دوربین واتسون در اکسل، به جدول پایین در شکل 1 می رویم. 14 برداشت موجودی. عدد در عبارت (10) با استفاده از تابع = SUMMQDIFF(array1, array2) و مخرج = SUMMQ(آرایه) محاسبه می شود (شکل 16).

برنج. 16. فرمول های محاسبه آمار دوربین واتسون

در مثال ما D= 0.883. سوال اصلی این است: چه مقداری از آمار دوربین واتسون را باید به اندازه کافی کوچک در نظر گرفت تا نتیجه گیری شود که یک خودهمبستگی مثبت وجود دارد؟ لازم است که مقدار D را با مقادیر بحرانی مرتبط کنیم ( d Lو d U) بسته به تعداد مشاهدات nو سطح اهمیت α (شکل 17).

برنج. 17. مقادیر بحرانی آمار دوربین واتسون (قطعه جدول)

بنابراین، در مشکل حجم فروش در فروشگاهی که کالا را به منزل شما تحویل می دهد، یک متغیر مستقل وجود دارد ( ک= 1)، 15 مشاهده ( n= 15) و سطح معنی داری α = 0.05. از این رو، d L= 1.08 و دU= 1.36. از آنجا که D = 0,883 < d L 08/1 =، بین باقیمانده ها همبستگی مثبت وجود دارد، روش حداقل مربعات را نمی توان اعمال کرد.

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

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

کاربردتی - معیارهای شیببا بررسی اینکه آیا شیب جمعیت β 1 برابر با صفر است، می توان تعیین کرد که آیا رابطه آماری معنی داری بین متغیرها وجود دارد یا خیر. ایکسو Y. اگر این فرضیه رد شود، می توان استدلال کرد که بین متغیرها ایکسو Yیک رابطه خطی وجود دارد فرضیه های صفر و جایگزین به صورت زیر فرموله می شوند: H 0: β 1 = 0 (بدون رابطه خطی)، H1: β 1 ≠ 0 (رابطه خطی وجود دارد). الف- مقدماتی تی- آمار برابر است با تفاوت بین شیب نمونه و شیب فرضی جمعیت، تقسیم بر خطای استاندارد برآورد شیب:

(11) تی = (ب 1 – β 1 ) / Sb 1

جایی که ب 1 شیب رگرسیون مستقیم بر اساس داده های نمونه است، β1 شیب فرضی جمعیت عمومی مستقیم است، ، و آمار تست تیاین دارد تی- توزیع با n - 2درجه آزادی.

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

برنج. 18. نتایج برنامه تی

چون تعداد فروشگاه ها n= 14 (نگاه کنید به شکل 3)، مقدار بحرانی تی-آمار در سطح معناداری α = 0.05 را می توان با فرمول پیدا کرد: تی ال=STUDENT.INV(0.025;12) = -2.1788 که در آن 0.025 نیمی از سطح معنی داری و 12 = است. n – 2; t U\u003d STUDENT.INR (0.975، 12) \u003d +2.1788.

از آنجا که تی-statistics = 10.64 > t U= 2.1788 (شکل 19)، فرضیه صفر H 0رد می شود. از طرف دیگر، آر-ارزش برای ایکس\u003d 10.6411، محاسبه شده با فرمول \u003d 1-STUDENT.DIST (D3, 12, TRUE) تقریباً برابر با صفر است، بنابراین فرضیه H 0دوباره رد می شود این حقیقت که آر- ارزش تقریباً صفر است، به این معنی که اگر هیچ رابطه خطی واقعی بین اندازه فروشگاه و فروش سالانه وجود نداشت، یافتن آن با استفاده از رگرسیون خطی تقریباً غیرممکن بود. بنابراین بین میانگین فروش سالانه فروشگاه و اندازه فروشگاه رابطه خطی معناداری از نظر آماری وجود دارد.

برنج. 19. آزمون فرضیه شیب جمعیت عمومی در سطح معنی داری 05/0 و 12 درجه آزادی.

کاربرداف - معیارهای شیبیک رویکرد جایگزین برای آزمایش فرضیه‌های مربوط به شیب رگرسیون خطی ساده استفاده از آن است اف-شاخص. به یاد بیاورید که اف- معیار برای آزمایش رابطه بین دو واریانس استفاده می شود (به جزئیات مراجعه کنید). هنگام آزمایش فرضیه شیب، معیار خطاهای تصادفی واریانس خطا (مجموع مجذور خطاها تقسیم بر تعداد درجه آزادی) است. اف-تست از نسبت واریانس توضیح داده شده توسط رگرسیون استفاده می کند (یعنی مقادیر SSRتقسیم بر تعداد متغیرهای مستقل ک، به واریانس خطا ( MSE=S Yایکس 2 ).

الف - مقدماتی اف-آمار برابر است با میانگین مجذور انحرافات ناشی از رگرسیون (MSR) تقسیم بر واریانس خطا (MSE): اف = MSR/ MSE، جایی که MSR=SSR / کMSE =SSE/(n– k – 1)، kتعداد متغیرهای مستقل در مدل رگرسیونی است. آمار تست افاین دارد اف- توزیع با کو n– k – 1درجه آزادی.

برای سطح اهمیت معین α، قاعده تصمیم به صورت زیر فرموله می شود: اگر F > FU، فرضیه صفر رد می شود. در غیر این صورت رد نمی شود. نتایج که در قالب یک جدول خلاصه از تحلیل واریانس ارائه شده است، در شکل 1 نشان داده شده است. 20.

برنج. 20. جدول تحلیل واریانس برای آزمون فرضیه معنی دار بودن آماری ضریب رگرسیون.

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

برنج. 21. نتایج برنامه اف- معیارهای به دست آمده با استفاده از بسته ابزار تجزیه و تحلیل اکسل

آماره F 113.23 است و آر-مقدار نزدیک به صفر (سلول اهمیتاف). اگر سطح معناداری α 0.05 باشد، مقدار بحرانی را تعیین کنید اف-توزیعات با یک و 12 درجه آزادی را می توان از فرمول بدست آورد F U\u003d F. OBR (1-0.05؛ 1؛ 12) \u003d 4.7472 (شکل 22). از آنجا که اف = 113,23 > F U= 4.7472 و آر-مقدار نزدیک به 0< 0,05, нулевая гипотеза H 0منحرف می شود، یعنی اندازه یک فروشگاه ارتباط نزدیکی با حجم فروش سالانه آن دارد.

برنج. 22. آزمون فرضیه شیب جمعیت عمومی در سطح معنی داری 05/0، با یک و 12 درجه آزادی.

فاصله اطمینان حاوی شیب β 1 .برای آزمایش فرضیه وجود رابطه خطی بین متغیرها، می توانید یک فاصله اطمینان حاوی شیب β 1 بسازید و مطمئن شوید که مقدار فرضی β 1 = 0 به این بازه تعلق دارد. مرکز فاصله اطمینان حاوی شیب β 1 شیب نمونه است ب 1 ، و مرزهای آن کمیت ها هستند b 1 ±t n –2 Sb 1

همانطور که در شکل نشان داده شده است. 18، ب 1 = +1,670, n = 14, Sb 1 = 0,157. تی 12 \u003d STUDENT.OBR (0.975، 12) \u003d 2.1788. از این رو، b 1 ±t n –2 Sb 1 = 2.1788 ± 1.670 * 0.157 = 0.342 ± 1.670، یا + 1.328 ≤ β 1 ≤ +2.012. بنابراین، شیب جمعیت با احتمال 0.95 در محدوده 1.328 + تا 2.012 + قرار دارد (یعنی از 1،328،000 تا 2،012،000 دلار). از آنجایی که این مقادیر بزرگتر از صفر هستند، رابطه خطی آماری معناداری بین فروش سالانه و منطقه فروشگاه وجود دارد. اگر فاصله اطمینان حاوی صفر باشد، هیچ رابطه ای بین متغیرها وجود نخواهد داشت. علاوه بر این، فاصله اطمینان به این معنی است که هر 1000 متر مربع. فوت منجر به افزایش میانگین فروش 1,328,000 تا 2,012,000 دلار می شود.

استفادهتی - معیارهای ضریب همبستگی.ضریب همبستگی معرفی شد r، که معیاری از رابطه بین دو متغیر عددی است. برای تعیین اینکه آیا بین دو متغیر رابطه آماری معناداری وجود دارد یا خیر می توان از آن استفاده کرد. اجازه دهید ضریب همبستگی بین جمعیت های هر دو متغیر را با نماد ρ نشان دهیم. فرضیه های صفر و جایگزین به صورت زیر فرموله می شوند: H 0: ρ = 0 (بدون همبستگی)، H 1: ρ ≠ 0 (همبستگی وجود دارد). بررسی وجود همبستگی:

جایی که r = + ، اگر ب 1 > 0, r = – ، اگر ب 1 < 0. Тестовая статистика تیاین دارد تی- توزیع با n - 2درجه آزادی.

در مشکل فروشگاه های زنجیره ای آفتابگردان r2= 0.904 و ب 1- 1.670+ (شکل 4 را ببینید). از آنجا که ب 1> 0، ضریب همبستگی بین فروش سالانه و اندازه فروشگاه است r= +√0.904 = +0.951. بیایید این فرضیه صفر را آزمایش کنیم که بین این متغیرها همبستگی وجود ندارد تی- آمار:

در سطح معناداری 0.05 = α، فرضیه صفر باید رد شود زیرا تی= 10.64 > 2.1788. بنابراین می توان ادعا کرد که از نظر آماری رابطه معناداری بین فروش سالانه و اندازه فروشگاه وجود دارد.

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

برآورد انتظارات ریاضی و پیش بینی مقادیر فردی

در این بخش روش هایی برای تخمین پاسخ مورد انتظار بحث می شود Yو پیش بینی ارزش های فردی Yبرای مقادیر داده شده متغیر ایکس.

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

جایی که , = ب 0 + ب 1 X i– متغیر مقدار پیش بینی شده Yدر ایکس = X i, S YXمیانگین مربعات خطا است، nحجم نمونه است، ایکسمن- مقدار داده شده متغیر ایکس, µ Y|ایکس = ایکسمن- انتظارات ریاضی از یک متغیر Yدر ایکس = Х i,SSX=

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

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

بنابراین میانگین حجم فروش سالیانه در تمامی فروشگاه های با مساحت 4000 متر مربع می باشد. فوت، با احتمال 95 درصد در محدوده 6.971 تا 8.317 میلیون دلار قرار دارد.

فاصله اطمینان را برای مقدار پیش بینی شده محاسبه کنید.علاوه بر فاصله اطمینان برای انتظار ریاضی از پاسخ برای مقدار معینی از متغیر ایکس، اغلب لازم است که فاصله اطمینان برای مقدار پیش بینی شده را بدانیم. اگرچه فرمول محاسبه چنین فاصله اطمینانی بسیار شبیه فرمول (13) است، اما این بازه حاوی یک مقدار پیش بینی شده است و نه تخمینی از پارامتر. فاصله زمانی برای پاسخ پیش بینی شده Yایکس = شیبرای مقدار خاصی از متغیر ایکسمنبا فرمول تعیین می شود:

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

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

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

مشکلات مرتبط با تحلیل رگرسیون:

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

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

محقق نباید با خرد کردن اعداد - محاسبه شیب، شیب و ضریب همبستگی مختلط غافل شود. او به دانش عمیق تری نیاز دارد. بیایید این را با یک مثال کلاسیک برگرفته از کتاب های درسی نشان دهیم. Anscombe نشان داد که هر چهار مجموعه داده نشان داده شده در شکل. 23 پارامترهای رگرسیون یکسانی دارند (شکل 24).

برنج. 23. چهار مجموعه داده مصنوعی

برنج. 24. تحلیل رگرسیون چهار مجموعه داده مصنوعی. تمام شده با بسته تحلیلی(برای بزرگنمایی تصویر روی تصویر کلیک کنید)

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

برنج. 25. نمودارهای پراکنده برای چهار مجموعه داده

نمودارهای پراکنده و نمودارهای باقیمانده نشان می دهد که این داده ها با یکدیگر متفاوت هستند. تنها مجموعه ای که در امتداد یک خط مستقیم توزیع می شود مجموعه A است. نمودار باقیمانده های محاسبه شده از مجموعه A هیچ الگوی ندارد. این را نمی توان برای مجموعه های B، C، و D گفت. نمودار پراکندگی ترسیم شده برای مجموعه B یک الگوی درجه دوم را نشان می دهد. این نتیجه با نمودار باقیمانده ها که شکل سهمی دارد تأیید می شود. نمودار پراکندگی و نمودار باقیمانده نشان می دهد که مجموعه داده B حاوی یک نقطه پرت است. در این شرایط، لازم است که اطلاعات پرت را از مجموعه داده ها حذف کرده و تجزیه و تحلیل را تکرار کنید. تکنیک تشخیص و حذف موارد پرت از مشاهدات، تحلیل تأثیر نامیده می شود. پس از حذف موارد پرت، نتیجه ارزیابی مجدد مدل ممکن است کاملاً متفاوت باشد. نمودار پراکندگی ترسیم شده از مجموعه داده D وضعیت غیرعادی را نشان می دهد که در آن مدل تجربی به شدت به یک پاسخ وابسته است ( X 8 = 19, Y 8 = 12.5). چنین مدل‌های رگرسیونی باید به‌ویژه با دقت محاسبه شوند. بنابراین، نمودارهای پراکندگی و باقیمانده ابزاری ضروری برای تحلیل رگرسیون هستند و باید جزء لاینفک آن باشند. بدون آنها، تحلیل رگرسیون معتبر نیست.

برنج. 26. قطعه های باقیمانده برای چهار مجموعه داده

چگونه از مشکلات در تحلیل رگرسیون جلوگیری کنیم:

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

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

برنج. 27. بلوک نمودار یک یادداشت

از مطالب کتاب لوین و همکاران آمار برای مدیران استفاده شده است. - م.: ویلیامز، 2004. - ص. 792-872

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

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

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

بسته 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(در نماد ما این است) و باقی مانده است.

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

به تب "فایل" بروید.
به بخش "تنظیمات" بروید.
پنجره Excel Options باز می شود. به بخش فرعی "افزونه ها" بروید.
در قسمت پایین پنجره ای که باز می شود، سوئیچ را در بلوک "Management" به موقعیت "Excel Add-ins" تغییر می دهیم، اگر در موقعیت دیگری قرار دارد. بر روی دکمه "برو" کلیک کنید.
پنجره افزونه های اکسل باز می شود. کادر کنار «بسته تحلیل» را علامت بزنید. بر روی دکمه "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 است،

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، D، F در برگه انتخاب کنید.

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

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

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

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

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 میلیون دلاری آنها بسیار زیاد است.

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

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