رگرسیون چندگانه در افزونه اکسل. ساخت یک معادله رگرسیون چندگانه در اکسل

پردازش داده های آماری نیز می تواند با استفاده از افزونه انجام شود بسته تحلیلی(شکل 62).

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

پنجره نشان داده شده در شکل 63.

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

ابزار رگرسیون از تابع استفاده می کند LINEST.

جعبه گفتگوی REGRESS

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

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

ثابت - صفر کادر را علامت بزنید تا خط رگرسیون از مبدا عبور کند.

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

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

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

باقیمانده ها کادر را انتخاب کنید تا باقیمانده ها در جدول خروجی قرار گیرند.

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

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

Fit Plot برای رسم مقادیر پیش‌بینی‌شده در مقابل مقادیر مشاهده‌شده، کادر را علامت بزنید.

نمودار احتمال عادیبرای ترسیم احتمال عادی، کادر را علامت بزنید.

عملکرد LINEST

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

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

معادله یک خط مستقیم:

y=m 1 x 1 +m 2 x 2 +…+b (در مورد چندین محدوده از مقادیر x)،

در جایی که مقدار وابسته y تابعی از مقدار مستقل x است، مقادیر m ضرایب مربوط به هر متغیر مستقل x است و b یک ثابت است. توجه داشته باشید که y، x و m می توانند بردار باشند. عملکرد LINESTیک آرایه (mn;mn-1;…;m 1 ;b) را برمی گرداند. LINESTهمچنین ممکن است آمار رگرسیون اضافی را برگرداند.

LINEST(مقادیر_y-مقادیر؛ مقادیر-x-مشخصات؛ هزینه؛ آمار)

مقادیر Known_y - مجموعه ای از مقادیر y که قبلاً برای رابطه y=mx+b شناخته شده اند.

اگر آرایه Known_y یک ستون داشته باشد، هر ستون از آرایهknown_x به عنوان یک متغیر جداگانه تفسیر می شود.

اگر آرایه Known_y یک ردیف داشته باشد، هر ردیف از آرایه know_x به عنوان یک متغیر جداگانه تفسیر می شود.

مقادیر Known_x - مجموعه ای اختیاری از مقادیر x که قبلاً برای رابطه y=mx+b شناخته شده اند.

آرایه Known_x می تواند شامل یک یا چند مجموعه از متغیرها باشد. اگر فقط از یک متغیر استفاده شود، آرایه های_known_y_values ​​و شناخته شده_x_values ​​می توانند به هر شکلی باشند - تا زمانی که ابعاد یکسانی داشته باشند. اگر از بیش از یک متغیر استفاده شود، باید y's یک بردار باشد (یعنی یک ردیف بالا یا یک ستون عرض).

اگر array_known_x حذف شود، این آرایه (1;2;3;...) به اندازه array_known_y در نظر گرفته می شود.

Const یک مقدار بولی است که مشخص می کند آیا ثابت b باید 0 باشد یا خیر.

اگر آرگومان "const" درست باشد یا حذف شود، ثابت b به طور معمول ارزیابی می شود.

اگر آرگومان "const" FALSE باشد، مقدار b برابر 0 در نظر گرفته می شود و مقادیر m به گونه ای انتخاب می شوند که رابطه y=mx برآورده شود.

Statistics یک مقدار بولی است که نشان می دهد آیا آمار رگرسیون اضافی باید برگردانده شود یا خیر.

اگر آمار درست باشد، LINEST آمار رگرسیون اضافی را برمی‌گرداند. آرایه برگشتی به این صورت خواهد بود: (mn;mn-1;...;m1;b:sen;sen-1;...;se1;seb:r2;sey:F;df:ssreg;ssresid).

اگر آمار FALSE یا حذف شده باشد، LINEST فقط ضرایب m و ثابت b را برمی گرداند.

آمار رگرسیون اضافی. (جدول 17)

مقدار	شرح
se1,se2,...,sen	مقادیر خطای استاندارد برای ضرایب m1,m2,...,mn.
seb	خطای استاندارد برای ثابت b (seb = #N/A اگر 'const' FALSE باشد).
r2	عامل تعیین. مقادیر واقعی y با مقادیر بدست آمده از معادله خط مستقیم مقایسه می شود. بر اساس نتایج مقایسه، ضریب جبر محاسبه شده و از 0 به 1 نرمال شده است. اگر برابر با 1 باشد، با مدل همبستگی کامل وجود دارد، یعنی تفاوتی بین مقادیر واقعی و تخمینی وجود ندارد. از y در غیر این صورت، اگر ضریب جبر 0 باشد، استفاده از معادله رگرسیون برای پیش بینی مقادیر y فایده ای ندارد. برای اطلاعات بیشتر در مورد نحوه محاسبه r2، در انتهای این بخش به «نظرات» مراجعه کنید.
sey	خطای استاندارد برای برآورد y.
اف	آماره F یا مقدار مشاهده شده F. از آماره F برای تعیین تصادفی بودن رابطه مشاهده شده بین متغیرهای وابسته و مستقل استفاده می شود.
df	درجه آزادی. درجات آزادی برای یافتن مقادیر بحرانی F در یک جدول آماری مفید هستند. برای تعیین سطح اطمینان مدل، باید مقادیر جدول را با آماره F ارائه شده توسط LINEST مقایسه کنید. برای کسب اطلاعات بیشتر در مورد محاسبه df به «نظرات» در انتهای این بخش مراجعه کنید. مثال 4 زیر استفاده از F و df را نشان می دهد.
ssreg	مجموع رگرسیون مربع ها.
ssresid	جمع باقیمانده مربع ها برای اطلاعات بیشتر در مورد محاسبه ssreg و ssresid، به «نظرات» در انتهای این بخش مراجعه کنید.

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

یادداشت:

هر خط مستقیمی را می توان با شیب و تقاطع آن با محور y توصیف کرد:

شیب (m): برای تعیین شیب یک خط که معمولاً با m نشان داده می شود، باید دو نقطه از خط (x 1 ,y 1) و (x 2 ,y 2) بگیرید. شیب برابر با (y 2 -y 1) / (x 2 -x 1) خواهد بود.

تقاطع Y (b): تقاطع y یک خط که معمولاً با b نشان داده می شود، مقدار y برای نقطه ای است که خط با محور y قطع می کند.

معادله خط مستقیم به شکل y=mx+b است. اگر مقادیر m و b مشخص باشد، هر نقطه از خط را می توان با جایگزین کردن مقادیر y یا x در معادله محاسبه کرد. همچنین می توانید از تابع TREND استفاده کنید.

اگر فقط یک متغیر مستقل x وجود داشته باشد، می توانید شیب و قطع y را مستقیماً با استفاده از فرمول های زیر بدست آورید:

شیب: INDEX(LINEST(known_y، شناخته شده_x)، 1)

مقطع Y: INDEX(LINEST(شناخته‌های_y، شناخته شده_xها)، 2)

دقت تقریب با استفاده از خط مستقیم محاسبه شده توسط تابع LINEST به درجه پراکندگی داده ها بستگی دارد. هر چه داده ها به یک خط مستقیم نزدیکتر باشند، مدل مورد استفاده LINEST دقیق تر است. تابع LINEST از روش حداقل مربعات برای تعیین بهترین تناسب با داده ها استفاده می کند. هنگامی که فقط یک متغیر مستقل x وجود دارد، m و b با استفاده از فرمول های زیر محاسبه می شوند:

که در آن x و y میانگین های نمونه هستند، به عنوان مثال x = AVERAGE (شناخته‌های_x) و y = AVERAGE (شناخته‌های_y).

توابع برازش LINEST و LGRFPRIBL می توانند منحنی مستقیم یا نمایی را محاسبه کنند که به بهترین وجه با داده ها مطابقت دارد. با این حال، آنها به این سوال پاسخ نمی دهند که کدام یک از این دو نتیجه برای حل مشکل مناسب تر است. همچنین می‌توانید تابع TREND (مقادیر_y-مقادیر؛ مقادیر_x-مقدار) TREND را برای یک خط مستقیم یا تابع GROWTH(مقادیر_y-مقادیر؛ مقادیر_x-مقدار_شناس) را برای یک منحنی نمایی محاسبه کنید. این توابع، اگر آرگومان new_x_values ​​را مشخص نکنید، آرایه ای از مقادیر y محاسبه شده را برای مقادیر x واقعی طبق یک خط مستقیم یا منحنی برمی گردانند. سپس می توانید مقادیر محاسبه شده را با مقادیر واقعی مقایسه کنید. همچنین می توانید نمودارهایی برای مقایسه بصری بسازید.

هنگام انجام تجزیه و تحلیل رگرسیون، مایکروسافت اکسل برای هر نقطه، مجذور تفاوت بین مقدار y پیش بینی شده و مقدار y واقعی را محاسبه می کند. مجموع این مجذور تفاوت ها را مجموع مجذورات باقیمانده (ssresid) می گویند. سپس مایکروسافت اکسل مجموع مجذورات (sstotal) را محاسبه می کند. اگر const = TRUE یا اگر این آرگومان مشخص نشده باشد، مجموع مجذورات برابر با مجموع مجذور تفاوت مقادیر y واقعی و میانگین مقادیر y خواهد بود. اگر const = FALSE باشد، مجموع مربع ها برابر با مجموع مربع های مقادیر y واقعی خواهد بود (بدون کسر میانگین y از ضریب y). پس از آن، مجموع رگرسیون مربع ها را می توان به صورت زیر محاسبه کرد: ssreg = sstotal - ssresid. هر چه مجموع باقیمانده مربع ها کوچکتر باشد، مقدار ضریب جبر r2 بزرگتر است، که نشان می دهد معادله به دست آمده با استفاده از تحلیل رگرسیون چقدر روابط بین متغیرها را توضیح می دهد. ضریب r2 برابر با ssreg/sstotal است.

در برخی موارد، یک یا چند ستون X (بگذارید مقادیر Y و X در ستون‌ها باشند) هیچ ارزش پیش‌بینی اضافی در ستون‌های X دیگر ندارند. به عبارت دیگر، حذف یک یا چند ستون X می‌تواند به مقادیر Y منجر شود. با همان دقت محاسبه می شود. در این حالت، ستون‌های X اضافی از مدل رگرسیون حذف خواهند شد. این پدیده را "هم خطی" می نامند زیرا ستون های زائد X را می توان به عنوان مجموع چندین ستون غیر زائد نشان داد. LINEST همخطی بودن را بررسی می کند و در صورت یافتن هر ستون X اضافی را از مدل رگرسیون حذف می کند. ستون های X حذف شده را می توان در خروجی LINEST با ضریب 0 و مقدار se 0 شناسایی کرد. حذف یک یا چند ستون به عنوان زائد، مقدار df را تغییر می دهد زیرا به تعداد ستون های X که واقعاً برای اهداف پیش بینی استفاده می شوند بستگی دارد. برای جزئیات بیشتر در مورد محاسبه df به مثال 4 زیر مراجعه کنید. هنگامی که df به دلیل حذف ستون های اضافی تغییر می کند، مقادیر sey و F نیز تغییر می کنند. اغلب استفاده از هم خطی توصیه نمی شود. با این حال، اگر برخی از ستون های X حاوی 0 یا 1 به عنوان یک شاخص است که نشان می دهد موضوع آزمایش در یک گروه جداگانه قرار دارد، باید استفاده شود. اگر const = TRUE یا اگر این آرگومان مشخص نشده باشد، LINEST یک ستون X اضافی را برای شبیه سازی نقطه تقاطع وارد می کند. اگر ستونی با مقادیر 1 برای مردان و 0 برای زنان وجود دارد و ستونی با مقادیر 1 برای زنان و 0 برای مردان وجود دارد، آخرین ستون حذف می شود زیرا مقادیر آن را می توان از زیر به دست آورد. ستون "نشانگر مرد".

محاسبه df برای مواردی که X ستون ها به دلیل همخطی بودن از مدل حذف نمی شوند به این صورت است: اگر k ستون شناخته شده_x وجود داشته باشد و const = TRUE یا مشخص نشده باشد، df = n - k - 1. اگر const = FALSE، سپس df = n -k. در هر دو مورد، حذف ستون های X به دلیل همخطی بودن، مقدار df را 1 افزایش می دهد.

فرمول هایی که آرایه ها را برمی گرداند باید به عنوان فرمول آرایه وارد شوند.

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

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

الگوریتم اصلی مورد استفاده در تابع LINEST، با الگوریتم اصلی توابع متفاوت است شیبو بخش خط. تفاوت بین الگوریتم ها می تواند منجر به نتایج متفاوتی برای داده های نامشخص و خطی شود. به عنوان مثال، اگر نقاط داده آرگومان Known_y 0 و نقاط داده آرگومانknown_x 1 باشد، آنگاه:

عملکرد LINESTمقداری برابر با 0 برمی گرداند. الگوریتم تابع LINESTبرای برگرداندن مقادیر مناسب برای داده های خطی استفاده می شود که در این صورت حداقل یک پاسخ می توان یافت.

توابع SLOPE و INTERCEPT خطای #DIV/0! را برمی‌گردانند. الگوریتم توابع SLOPE و INTERCEPT برای یافتن تنها یک پاسخ استفاده می شود و در این حالت ممکن است چندین پاسخ وجود داشته باشد.

علاوه بر محاسبه آمار برای انواع دیگر رگرسیون، LINEST می تواند برای محاسبه محدوده سایر انواع رگرسیون با وارد کردن توابع متغیرهای x و y به عنوان یک سری متغیر x و y برای LINEST استفاده شود. به عنوان مثال، فرمول زیر:

LINEST(y- مقادیر، x-values^COLUMN($A:$C))

با یک ستون از مقادیر Y و یک ستون از مقادیر X برای محاسبه تقریب مکعبی (چند جمله ای درجه 3) به شکل زیر کار می کند:

y=m 1 x+m 2 x 2 +m 3 x 3 +b

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

مثال 1

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

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

		تعداد افرادی که رفتند	حقوق
			30000 روبل
			35000 روبل
			40000 روبل
			45000 روبل
			50000 روبل
			55000 روبل
			60000 روبل

برای مسئله تعیین وابستگی تعداد کارگران بازنشسته به میانگین حقوق در 6 شرکت، مدل رگرسیون به شکل معادله Y = a 0 + a 1 x 1 +…+a k x k است که x i متغیرهای تأثیرگذار هستند. ، a i ضرایب رگرسیون، a 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 مربع باشد<0,5, то такой анализа регрессии в Excel нельзя считать резонным.

تجزیه و تحلیل نسبت

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

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

رگرسیون چندگانه

این اصطلاح به یک معادله اتصال با چندین متغیر مستقل از شکل اشاره دارد:

y \u003d f (x 1 + x 2 + ... x m) + ε، که در آن y ویژگی مؤثر (متغیر وابسته) است، و x 1، x 2، ... x m عوامل عامل (متغیرهای مستقل) هستند.

تخمین پارامتر

برای رگرسیون چندگانه (MR) با استفاده از روش حداقل مربعات (OLS) انجام می شود. برای معادلات خطی به شکل Y = a + b 1 x 1 +…+b m x m + ε، سیستمی از معادلات عادی می سازیم (به زیر مراجعه کنید)

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

از اینجا دریافت می کنیم:

که در آن σ واریانس ویژگی مربوطه منعکس شده در شاخص است.

LSM برای معادله MP در مقیاس قابل استانداردسازی قابل اعمال است. در این حالت معادله را بدست می آوریم:

که در آن t y، t x 1، ... t xm متغیرهای استاندارد شده ای هستند که مقادیر میانگین آنها 0 است. β i ضرایب رگرسیون استاندارد شده است و انحراف استاندارد 1 است.

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

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

فرض کنید جدولی از پویایی قیمت یک محصول خاص N در 8 ماه گذشته وجود دارد. لازم است در مورد توصیه خرید دسته آن با قیمت 1850 روبل در تن تصمیم گیری شود.

	شماره ماه	نام ماه	قیمت کالای N
			1750 روبل در هر تن
			1755 روبل در هر تن
			1767 روبل در هر تن
			1760 روبل در هر تن
			1770 روبل در هر تن
			1790 روبل در هر تن
			1810 روبل در هر تن
			1840 روبل در هر تن

برای حل این مشکل در صفحه گسترده اکسل، باید از ابزار تجزیه و تحلیل داده ها که قبلاً در مثال بالا شناخته شده است استفاده کنید. بعد، بخش "Regression" را انتخاب کنید و پارامترها را تنظیم کنید. لازم به یادآوری است که در قسمت "Input Y interval" باید محدوده ای از مقادیر برای متغیر وابسته (در این مورد قیمت یک محصول در ماه های خاص سال) و در "ورودی" وارد شود. فاصله X" - برای متغیر مستقل (تعداد ماه). با کلیک بر روی "Ok" عمل را تأیید کنید. در یک برگه جدید (اگر چنین نشان داده شده بود)، ما داده هایی را برای رگرسیون دریافت می کنیم.

بر اساس آنها، یک معادله خطی به شکل y=ax+b می سازیم، که در آن پارامترهای a و b ضرایب سطر با نام شماره ماه و ضرایب و ردیف "تقاطع Y" از برگه با نتایج تحلیل رگرسیون. بنابراین، معادله رگرسیون خطی (LE) برای مسئله 3 به صورت زیر نوشته می شود:

قیمت محصول N = 11.714* شماره ماه + 1727.54.

یا در نماد جبری

y = 11.714 x + 1727.54

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

برای تصمیم گیری در مورد مناسب بودن معادله رگرسیون خطی حاصل، از ضرایب همبستگی چندگانه (MCC) و ضرایب تعیین و همچنین آزمون فیشر و آزمون دانشجو استفاده می شود. در جدول اکسل با نتایج رگرسیون به ترتیب با نام های R متعدد، R-square، F-statistic و t-statistic ظاهر می شوند.

KMC R امکان ارزیابی تنگی رابطه احتمالی بین متغیرهای مستقل و وابسته را فراهم می کند. ارزش بالای آن نشان دهنده رابطه نسبتاً قوی بین متغیرهای "تعداد ماه" و "قیمت کالا N بر حسب روبل در هر 1 تن" است. با این حال، ماهیت این رابطه ناشناخته باقی مانده است.

مجذور ضریب تعیین R 2 (RI) یک مشخصه عددی از سهم پراکندگی کل است و پراکندگی کدام بخشی از داده های تجربی را نشان می دهد. مقادیر متغیر وابسته با معادله رگرسیون خطی مطابقت دارد. در مسئله مورد بررسی، این مقدار برابر با 8/84 درصد است، یعنی داده های آماری با دقت بالایی توسط SD به دست آمده توصیف می شوند.

آماره F که آزمون فیشر نیز نامیده می شود، برای ارزیابی اهمیت یک رابطه خطی، رد یا تایید فرضیه وجود آن استفاده می شود.

(معیار دانش آموز) به ارزیابی اهمیت ضریب با یک جمله مجهول یا آزاد یک رابطه خطی کمک می کند. اگر مقدار معیار t > t cr باشد، فرضیه بی اهمیت بودن جمله آزاد معادله خطی رد می شود.

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

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

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

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

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

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

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

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

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

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

1. به تب "فایل" بروید.
2. به بخش "تنظیمات" بروید.
3. پنجره اکسل Options باز می شود. به بخش فرعی «افزونه‌ها» بروید.
4. در قسمت پایین پنجره ای که باز می شود، سوئیچ را در بلوک "Management" به موقعیت "افزونه های اکسل" تغییر می دهیم، اگر در موقعیت دیگری قرار دارد. بر روی دکمه "برو" کلیک کنید.
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 یک شاخص نسبتاً بالای تأثیر در نظر گرفته می شود.

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

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

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

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

روش رگرسیون خطی به ما اجازه می دهد تا یک خط مستقیم را توصیف کنیم که به بهترین وجه با یک سری از جفت های مرتب شده (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. به عبارت دیگر، احتمال رد فرضیه صحیح در مورد بی اهمیت بودن ضریب مجهول 12/0 درصد است.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

بسته تحلیلی

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

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

فعال کردن آنالیز پک

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

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

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

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

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

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

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

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

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

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

بنابراین، 0,865 - این R2- ضریب تعیین، نشان می دهد که 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 دریافت خواهیم کرد.