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

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

برنج. 17. پنجره افزودنی ها

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

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

گزینه های اکسل دکمه آفیس

برنج. 18. نصب و راه اندازی تجزیه و تحلیل بستهدر اکسل 2007

برای نصب Analysis Pack روی دکمه کلیک کنید برو،در پایین پنجره باز پنجره نشان داده شده در شکل. 12. کادر کناری را علامت بزنید بسته تحلیلی.در برگه داده هادکمه ظاهر می شود تحلیل داده ها(شکل 19).

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

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

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

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

جعبه گفتگوی REGRESS

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

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

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

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

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

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

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

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

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

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

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

تابع LINEST

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

برنج. 22 یافتن توابع در اکسل 2003

اگر در میدان نامنام تابع ظاهر نمی شود، سپس روی مثلث کنار فیلد کلیک چپ کرده و پس از آن پنجره ای با لیستی از توابع ظاهر می شود. اگر این تابع در لیست نیست، روی مورد موجود در لیست کلیک چپ کنید توابع دیگر، یک جعبه گفتگو ظاهر خواهد شد. FUNCTION Master، که در آن با استفاده از اسکرول عمودی، عملکرد مورد نظر را انتخاب کرده و با مکان نما انتخاب کرده و بر روی آن کلیک کنید خوب(شکل 23).

برنج. 23. Function Wizard

برای جستجوی تابع در اکسل 2007، هر تبی را می توان در منو باز کرد، سپس برای انجام محاسبات، سلولی را که می خواهیم میانگین مقدار را با مکان نما در آن نمایش دهیم انتخاب کرده و کلید = را روی صفحه کلید فشار دهیم. سپس در قسمت Name تابع را مشخص کنید میانگین. پنجره محاسبه تابع مشابه پنجره اکسل 2003 است.

همچنین می توانید برگه فرمول ها را انتخاب کنید و روی دکمه در "" کلیک چپ کنید. درج تابع» (شکل 24)، یک پنجره ظاهر می شود FUNCTION Masterکه نمای آن شبیه اکسل 2003 است. همچنین در منو می توانید بلافاصله دسته بندی توابع (اخیرا استفاده شده، مالی، منطقی، متن، تاریخ و زمان، ریاضی، توابع دیگر) را انتخاب کنید که در آن جستجو خواهیم کرد. برای عملکرد مورد نظر

ویژگی های دیگر ارجاعات و آرایه ها ریاضی

برنج. 24 انتخاب تابع در اکسل 2007

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

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

(در صورت وجود چندین محدوده از مقادیر x)،

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

LINEST(مقادیر_y_مقادیر، مقادیر_x_شناخته_مقادیر_کنست، آمار)

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

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

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

Known_x یک مجموعه اختیاری از x است که قبلاً برای رابطه شناخته شده است.

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

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

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

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

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

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

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

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

آمار رگرسیون اضافی

ارزش شرح 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، به «نظرات» در انتهای این بخش مراجعه کنید.

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

یادداشت:

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

شیب (m): برای تعیین شیب یک خط که معمولا با m نشان داده می شود، باید دو نقطه از خط و ; شیب خواهد بود .

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

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

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

شیب: INDEX(LINEST(known_y's،known_x's)، 1)

مقطع Y: INDEX(LINEST(y'sknown_y's, known_x's)، 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 با ضریب ۰ و مقدار se برابر با ۰ شناسایی کرد. حذف یک یا چند ستون به‌عنوان زائد، مقدار 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 = 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 \u003d a 0 + a 1 x 1 + ... + a k x k.

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

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

اکسل دارای توابع داخلی است که می توان از آنها برای محاسبه پارامترهای یک مدل رگرسیون خطی استفاده کرد. اما افزونه Analysis ToolPak این کار را سریعتر انجام می دهد.

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

1. روی دکمه "Office" کلیک کنید و به تب "گزینه های اکسل" بروید. "افزونه ها".

2. در زیر، در زیر لیست کشویی، در قسمت "Management" یک کتیبه "Excel add-ins" وجود دارد (اگر وجود ندارد، روی کادر سمت راست کلیک کنید و انتخاب کنید). و یک دکمه Go کلیک.

3. فهرستی از افزونه های موجود باز می شود. "پکیج تجزیه و تحلیل" را انتخاب کنید و روی OK کلیک کنید.

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

حال به طور مستقیم به تحلیل رگرسیون می پردازیم.

1. منوی ابزار تحلیل داده را باز کنید. "Regression" را انتخاب کنید.

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

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

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

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

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

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

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

جدول آمار رگرسیونمقادیر داده شده است:

چندگانه آر – ضریب همبستگی چندگانه؛

آر- مربع– ضریب تعیین آر 2 ;

عادی شده است آر - مربع- تنظیم شده آر 2 تنظیم شده برای تعداد درجات آزادی؛

خطای استانداردخطای استاندارد رگرسیون است اس;

مشاهدات -تعداد مشاهدات n.

جدول تحلیل واریانسداده شده:

1. ستون df - تعداد درجات آزادی برابر است

برای رشته پسرفت df = ک;

برای رشته باقی ماندهdf = n – ک – 1;

برای رشته جمعdf = n– 1.

2. ستون SS-مجموع مجذور انحرافات، برابر است

برای رشته پسرفت ;

برای رشته باقی مانده ;

برای رشته جمع .

3. ستون ام‌اسواریانس های تعیین شده توسط فرمول ام‌اس = اس اس/df:

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

برای رشته باقی ماندهواریانس باقیمانده است.

4. ستون اف - ارزش محاسبه شده اف- معیارهای محاسبه شده با فرمول

اف = ام‌اس(پسرفت)/ ام‌اس(باقی مانده).

5. ستون اهمیت اف مقدار سطح معنی داری مربوط به محاسبه شده است اف-آمار .

اهمیت اف= FRIST( F-آمار، df(پسرفت)، df(باقی مانده)).

اگر اهمیت دارد اف < стандартного уровня значимости, то آر 2 از نظر آماری معنی دار است.

	ضرایب	خطای استاندارد	آمار t	مقدار p	95% پایین	95% برتر
Y	65,92	11,74	5,61	0,00080	38,16	93,68
ایکس	0,107	0,014	7,32	0,00016	0,0728	0,142

این جدول نشان می دهد:

1. شانس- مقادیر ضرایب آ, ب.

2. خطای استانداردخطاهای استاندارد ضرایب رگرسیون هستند S a, Sb.

3. t-آمار- مقادیر محاسبه شده تی - معیارهای محاسبه شده با فرمول:

t-statistic = ضرایب / خطای استاندارد.

4.آر-ارزش (اهمیت تی) مقدار سطح معنی داری مربوط به محاسبه شده است t-آمار.

آر-value= استودراسپ(تی-آمار، df(باقی مانده)).

اگر آر-معنی< стандартного уровня значимости, то соответствующий коэффициент статистически значим.

5. 95% پایین و 95% بالامرزهای پایین و بالای فاصله اطمینان 95 درصد برای ضرایب معادله رگرسیون خطی نظری هستند.

انصراف باقی مانده
مشاهده	y را پیش بینی کرد	باقی می ماند e
	72,70	-29,70
	82,91	-20,91
	94,53	-4,53
	105,72	5,27
	117,56	12,44
	129,70	19,29
	144,22	20,77
	166,49	24,50
	268,13	-27,13

جدول انصراف باقی ماندهنشان داد:

در یک ستون مشاهده- شماره مشاهده؛

در یک ستون پیش بینی کرد y مقادیر محاسبه شده متغیر وابسته هستند.

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

مثال 3.6.داده های موجود (واحدهای عربی) در مورد هزینه های غذا yو درآمد سرانه ایکسبرای نه گروه از خانواده ها:

ایکس
y

با استفاده از نتایج بسته تحلیل اکسل (رگرسیون)، وابستگی هزینه های مواد غذایی به ارزش درآمد سرانه را تجزیه و تحلیل می کنیم.

نتایج تحلیل رگرسیون معمولاً به صورت زیر نوشته می شود:

که در داخل پرانتز خطاهای استاندارد ضرایب رگرسیون وجود دارد.

ضرایب رگرسیون آ = 65,92 و ب= 0.107. جهت ارتباط بین yو ایکسعلامت ضریب رگرسیون را تعیین می کند ب= 0.107، یعنی رابطه مستقیم و مثبت است. ضریب ب 0.107 = نشان می دهد که با افزایش درآمد سرانه 1 ارب. واحدها هزینه های غذا 0.107 تبدیل افزایش می یابد. واحدها

اجازه دهید اهمیت ضرایب مدل به دست آمده را تخمین بزنیم. اهمیت ضرایب ( الف، ب) در مقابل بررسی می شود تی- تست:

مقدار p ( آ) = 0,00080 < 0,01 < 0,05

مقدار p ( ب) = 0,00016 < 0,01 < 0,05,

از این رو ضرایب ( الف، ب) در سطح 1% و حتی بیشتر از آن در سطح 5% معنی دار هستند. بنابراین، ضرایب رگرسیون معنی دار بوده و مدل برای داده های اصلی مناسب است.

نتایج تخمین رگرسیون نه تنها با مقادیر بدست آمده از ضرایب رگرسیون، بلکه با مقداری از مجموعه آنها (فاصله اطمینان) نیز سازگار است. با احتمال 95 درصد، فواصل اطمینان برای ضرایب (38.16 - 93.68) برای آو (0.0728 - 0.142) برای ب

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

ارزش آر 2 = 0.884 به این معنی است که ضریب درآمد سرانه می تواند 88.4 درصد از تغییرات (پراکندگی) در مخارج مواد غذایی را توضیح دهد.

اهمیت آر 2 توسط F-آزمون: اهمیت اف = 0,00016 < 0,01 < 0,05, следовательно, آر 2 در سطح 1% و حتی بیشتر از آن در سطح 5% معنی دار است.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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