معادله رگرسیون چندگانه در اکسل. رگرسیون خطی سریع در اکسل: Trendline

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

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

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

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

ابزار Regression از تابع استفاده می کند 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 یک ردیف داشته باشد، هر ردیف از آرایه Known_x به عنوان یک متغیر جداگانه تفسیر می شود.

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

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

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

کنست - بولی، که نشان می دهد آیا ثابت 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)

شکل زیر ترتیب برگرداندن آمار رگرسیون اضافی را نشان می دهد (شکل 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'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=m 1 x+m 2 x 2 +m 3 x 3 +b

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

تجزیه و تحلیل رگرسیون در مایکروسافت اکسل بیشترین است راهنمای کاملاستفاده از MS Excel برای حل مشکلات تحلیل رگرسیون در زمینه هوش تجاری. کنراد کارلبرگ به وضوح مسائل نظری را توضیح می دهد که دانش آنها به شما کمک می کند از بسیاری از اشتباهات هم هنگام انجام تحلیل رگرسیون خودتان و هم هنگام ارزیابی نتایج تجزیه و تحلیل های انجام شده توسط افراد دیگر جلوگیری کنید. همه چیز از همبستگی ساده و آزمون t گرفته تا تحلیل کوواریانس چندگانه بر اساس آن است نمونه های واقعیو همراهی کرد توصیف همراه با جزئیاتمراحل گام به گام مربوطه

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

کنراد کارلبرگ. تجزیه و تحلیل رگرسیون در مایکروسافت اکسل. - م.: دیالکتیک، 2017. - 400 ص.

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

فصل 1. برآورد تنوع داده ها

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

اکسل دو تابع را ارائه می دهد که واریانس را برمی گرداند: VARP.D() و VARP.V():

• اگر مقادیری که باید پردازش شوند یک جمعیت را تشکیل می دهند از تابع VAR.G() استفاده کنید. یعنی مقادیر موجود در محدوده تنها مقادیری هستند که شما به آنها علاقه دارید.
• اگر مقادیری که باید پردازش شوند نمونه ای از یک جمعیت بزرگتر را تشکیل می دهند، از تابع VAR.V() استفاده کنید. فرض بر این است که مقادیر اضافی وجود دارد که می توانید واریانس آنها را نیز تخمین بزنید.

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

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

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

مجموع میانگین مربعات تعیین شده برای نمونه تخمین کمتری از واریانس جامعه ارائه می دهد. واریانس محاسبه شده به این روش نامیده می شود آوارهارزیابی: معلوم می شود که برای حذف سوگیری و به دست آوردن یک تخمین بی طرفانه، کافی است مجموع انحرافات را بر مجذور تقسیم کنیم. n، جایی که nحجم نمونه است و n - 1.

ارزش n - 1عدد (تعداد) درجات آزادی نامیده می شود. وجود داشته باشد راه های مختلفمحاسبه این مقدار، اگرچه همه آنها شامل کم کردن مقداری از حجم نمونه یا شمارش تعداد دسته‌هایی است که مشاهدات در آن قرار می‌گیرند.

ماهیت تفاوت بین توابع DISP.G() و DISP.V() به شرح زیر است:

• در تابع VARI.G() مجموع مربع ها بر تعداد مشاهدات تقسیم می شود و بنابراین تخمین بایاس واریانس یعنی میانگین واقعی را نشان می دهد.
• در تابع VAR.B() مجموع مربع ها بر تعداد مشاهدات منهای 1 تقسیم می شود، یعنی. با تعداد درجات آزادی، که تخمین دقیق تر و بی طرفانه تری از واریانس جامعه ای که نمونه از آن استخراج شده است، به دست می دهد.

انحراف معیار (انگلیسی) انحراف معیار، SD) - بله ریشه دوماز پراکندگی:

انحرافات مربعی مقیاس اندازه گیری را به متریک دیگری تبدیل می کند که مربع اصلی است: متر - به متر مربع، دلار - به دلار مربع و غیره. انحراف معیار جذر واریانس است و بنابراین ما را به واحدهای اصلی بازمی گرداند. که راحت تره

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

فرض کنید شما داده هایی را در مورد قد 25 مرد بالغ که به طور تصادفی انتخاب شده اند در هر یک از 50 ایالت جمع آوری می کنید. در مرحله بعد، میانگین قد مردان بالغ در هر ایالت را محاسبه می کنید. 50 مقدار میانگین حاصل می تواند به نوبه خود مشاهدات در نظر گرفته شود. از این، شما می توانید انحراف معیار آنها را محاسبه کنید، که این است خطای استاندارد میانگین. برنج. 1. به شما امکان می دهد توزیع 1250 ارزش فردی اصلی (داده های قد 25 مرد در هر یک از 50 ایالت) را با توزیع مقادیر متوسط ​​50 ایالت مقایسه کنید. فرمول تخمین خطای استاندارد میانگین (یعنی انحراف معیار میانگین، نه مشاهدات فردی):

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

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

توافقی در آمار در مورد استفاده از یونانی و حروف لاتینبرای نشان دادن کمیت های آماری مرسوم است که پارامترهای جمعیت عمومی را با حروف یونانی و آمار نمونه را با حروف لاتین تعیین کنید. بنابراین، اگر در مورد انحراف معیار جمعیت صحبت می کنیم، آن را به صورت σ می نویسیم. اگر انحراف استاندارد نمونه در نظر گرفته شود، از علامت s استفاده می کنیم. در مورد نمادها برای میانگین ها، آنها به خوبی با یکدیگر همخوانی ندارند. میانگین جمعیت با حرف یونانی μ نشان داده می شود. با این حال، نماد X به طور سنتی برای نشان دادن میانگین نمونه استفاده می شود.

z-scoreموقعیت مشاهده را در توزیع در واحدهای انحراف معیار بیان می کند. به عنوان مثال، z = 1.5 به این معنی است که مشاهده 1.5 انحراف استاندارد از میانگین فاصله دارد، به سمت مقادیر بالاتر. مدت، اصطلاح z-scoreبرای ارزیابی های فردی استفاده می شود، به عنوان مثال. برای اندازه گیری های نسبت داده شده به عناصر تکی نمونه. برای چنین آماری (به عنوان مثال، میانگین دولتی)، از این اصطلاح استفاده می شود. مقدار z:

که در آن X میانگین مقدار نمونه است، μ مقدار میانگین جمعیت عمومی است، خطای استاندارد میانگین مجموعه نمونه ها است:

جایی که σ خطای استاندارد جمعیت عمومی است (اندازه گیری های فردی)، nحجم نمونه است.

فرض کنید شما یک مربی گلف هستید. شما قادر به اندازه گیری محدوده ضربه برای مدت طولانی بوده اید و می دانید که میانگین 205 یارد و انحراف استاندارد 36 یارد است. به شما یک باشگاه جدید پیشنهاد شده است، که مدعی است برد شما را 10 یارد افزایش می دهد. شما از هر یک از 81 باشگاه بعدی می خواهید که با یک باشگاه جدید امتحان کنند و محدوده خود را ثبت کنند. معلوم شد که میانگین برد یک ضربه با چماق جدید 215 یارد است. احتمال اینکه اختلاف 10 یارد (215 - 205) صرفاً به دلیل خطای نمونه گیری باشد چقدر است؟ یا به عبارت دیگر، احتمال اینکه در یک آزمون بزرگتر، یک باشگاه جدید نسبت به میانگین بلندمدت فعلی 205 یارد، افزایش برد را نشان ندهد چقدر است؟

ما می توانیم این را با ایجاد یک مقدار z آزمایش کنیم. خطای استاندارد میانگین:

سپس z-value:

ما باید این احتمال را پیدا کنیم که میانگین نمونه 2.5σ از میانگین جامعه فاصله داشته باشد. اگر احتمال کم است، پس تفاوت ها به دلیل شانس نیست، بلکه به دلیل کیفیت باشگاه جدید است. هیچ تابع آماده ای در اکسل برای تعیین احتمال z-score وجود ندارد. با این حال، می توانید از فرمول =1-NORM.ST.DIST(z-value, TRUE) استفاده کنید، که در آن NORM.ST.DIST() ناحیه زیر منحنی نرمال را در سمت چپ مقدار z برمی گرداند (شکل 2) .

برنج. 2. تابع NORM.S.DIST() ناحیه زیر منحنی را در سمت چپ مقدار z برمی گرداند. برای بزرگنمایی تصویر، روی آن کلیک راست کرده و انتخاب کنید باز کردن تصویر در تب جدید

آرگومان دوم تابع NORM.S.DIST() می تواند دو مقدار بگیرد: TRUE - تابع ناحیه زیر منحنی را در سمت چپ نقطه مشخص شده توسط آرگومان اول برمی گرداند. FALSE - تابع ارتفاع منحنی را در نقطه ای که توسط آرگومان اول ارائه شده است برمی گرداند.

اگر میانگین (μ) و انحراف معیار (σ) جمعیت مشخص نباشد، از مقدار t استفاده می شود (نگاه کنید به ). ساختارهای z- و t-score از این جهت متفاوت هستند که انحراف استاندارد s بدست آمده از نتایج نمونه برای یافتن مقدار t به جای مقدار شناخته شده پارامتر جمعیت σ استفاده می شود. منحنی نرمال یک شکل دارد و شکل توزیع مقادیر t بسته به تعداد درجات آزادی df (از انگلیسی. درجه آزادی) نمونه ای که نشان می دهد. تعداد درجات آزادی نمونه می باشد n - 1، جایی که n- اندازه نمونه (شکل 3).

برنج. 3. شکل توزیع های t که وقتی پارامتر σ ناشناخته است به وجود می آیند با شکل توزیع نرمال متفاوت است.

اکسل دو تابع برای توزیع t دارد که توزیع Student نیز نامیده می شود: STUDENT.DIST () ناحیه زیر منحنی را در سمت چپ برمی گرداند. مقدار t داده شده استو STUDENT.DIST.PH() در سمت راست.

فصل 2. همبستگی

همبستگی اندازه گیری وابستگی بین عناصر مجموعه ای از جفت های مرتب شده است. همبستگی مشخص می شود ضرایب همبستگی پیرسون- ر. ضریب می تواند مقادیری در محدوده -1.0 تا +1.0 داشته باشد.

جایی که S xو Syانحراف معیار متغیرها هستند ایکسو Y, سکسی– کوواریانس:

در این فرمول کوواریانس بر انحراف معیار متغیرها تقسیم می شود ایکسو Y، در نتیجه اثرات مقیاس بندی مربوط به واحد را از کوواریانس حذف می کند. اکسل از تابع ()CORREL استفاده می کند. نام این تابع حاوی عناصر واجد شرایط G و C نیست که در نام توابعی مانند STDEV()، VARV() یا COVARIANCE() استفاده می شود. اگرچه ضریب همبستگی نمونه یک تخمین مغرضانه ارائه می‌کند، اما دلیل سوگیری با واریانس یا انحراف معیار متفاوت است.

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

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

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

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

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

فصل 3 رگرسیون ساده

اگر دو متغیر به هم مرتبط باشند، به طوری که مقدار ضریب همبستگی بزرگتر از مثلاً 0.5 باشد، می توان مقدار مجهول یک متغیر را از مقدار شناخته شده دیگری پیش بینی کرد (با کمی دقت). برای به دست آوردن مقادیر پیش بینی شده قیمت، بر اساس داده های داده شده در شکل. 5، می توانید از هر یک از چندین راه ممکن استفاده کنید، اما تقریباً مطمئناً از روش نشان داده شده در شکل استفاده نخواهید کرد. 5. با این حال، شما باید آن را بخوانید، زیرا هیچ راه دیگری نمی تواند رابطه بین همبستگی و پیش بینی را به وضوح به وضوح نشان دهد. روی انجیر 5، در محدوده B2:C12، یک نمونه تصادفی از ده خانه است و داده هایی را در مورد مساحت هر خانه (به فوت مربع) و قیمت فروش آن ارائه می دهد.

برنج. 5. پیش بینی قیمت فروش یک خط مستقیم را تشکیل می دهد

میانگین، انحراف معیار و ضریب همبستگی (محدوده A14:C18) را بیابید. مساحت z-scores را محاسبه کنید (E2:E12). به عنوان مثال، سلول E3 حاوی فرمول: =(B3-$B$14)/$B$15 است. امتیازهای z-score قیمت پیش بینی شده (F2:F12) را محاسبه کنید. به عنوان مثال، سلول F3 حاوی فرمول: =E3*$B$18 است. تبدیل z-score به قیمت دلار (H2:H12). در سلول HZ، فرمول این است: =F3*$C$15+$C$14.

توجه داشته باشید که مقدار پیش‌بینی‌شده همیشه به سمت میانگین که 0 است، تمایل دارد. هر چه ضریب همبستگی به صفر نزدیک‌تر باشد، امتیاز z پیش‌بینی‌شده به صفر نزدیک‌تر است. در مثال ما، ضریب همبستگی بین منطقه و قیمت فروش 0.67 است، و قیمت پیش بینی شده 1.0 * 0.67 است، یعنی. 0.67. این مربوط به مقدار بیش از مقدار متوسط، برابر با دو سوم انحراف استاندارد است. اگر ضریب همبستگی برابر با 0.5 بود، قیمت پیش‌بینی شده 1.0 * 0.5 خواهد بود، یعنی. 0.5. این مربوط به مازاد بر مقدار بالاتر از مقدار متوسط ​​است که تنها برابر با نیمی از انحراف استاندارد است. هر گاه مقدار ضریب همبستگی با ایده آل متفاوت باشد، یعنی. بزرگتر از -1.0 و کمتر از 1.0، تخمین متغیر پیش‌بینی‌کننده باید به مقدار میانگین آن نزدیک‌تر باشد تا برآورد متغیر پیش‌بینی‌کننده (مستقل) به خودش. این پدیده را رگرسیون به میانگین یا به سادگی رگرسیون می نامند.

چندین تابع در اکسل برای تعیین ضرایب معادله خط رگرسیون وجود دارد (در اکسل به آن خط روند می گویند) y=kx + ب. برای تعیین کعملکرد را ارائه می دهد

=SLOPE (مقادیر_y_مقادیر؛ مقادیر_x_شناخته شده)

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

= INTERCUT (مقادیر_y_مقادیر؛ مقادیر_x_شناخته شده)

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

توجه داشته باشید که تعداد مقادیر x و y ارائه شده به توابع SLOPE() و INTERCEPT() به عنوان آرگومان باید یکسان باشد.

تحلیل رگرسیون از روش دیگری استفاده می کند شاخص مهم- R 2 (R-square) یا ضریب تعیین. تعیین می کند که چه سهمی در تغییرپذیری کلی داده ها توسط رابطه بین ایجاد می شود ایکسو در. اکسل تابع QVPIRSON() را برای خود دارد که دقیقا همان آرگومان های تابع CORREL() را می گیرد.

دو متغیر با ضریب همبستگی غیر صفر بین آنها گفته می شود که واریانس را توضیح می دهند یا واریانس را توضیح می دهند. به طور معمول، واریانس توضیح داده شده به صورت درصد بیان می شود. بنابراین آر 2 81/0 = یعنی 81 درصد از واریانس (پراکندگی) دو متغیر توضیح داده شده است. 19 درصد باقی مانده به دلیل نوسانات تصادفی است.

اکسل یک تابع TREND دارد که محاسبات را ساده می کند. تابع ()TREND:

• مقادیر شناخته شده ای را که شما ارائه می دهید را می گیرد ایکسو ارزش های شناخته شده در;
• شیب خط رگرسیون و ثابت (بخش) را محاسبه می کند.
• مقادیر پیش بینی را برمی گرداند دربا اعمال معادله رگرسیون به مقادیر شناخته شده تعیین می شود ایکس(شکل 7).

تابع TREND() یک تابع آرایه است (اگر قبلاً با چنین توابعی برخورد نکرده اید، توصیه می کنم).

برنج. 7. استفاده از تابع ()TREND به شما امکان می دهد در مقایسه با استفاده از یک جفت تابع ()SLOPE و INTERCEPT، محاسبات را سرعت بخشیده و ساده کنید.

برای وارد کردن تابع TREND() به عنوان فرمول آرایه در سلول های G3:G12، محدوده G3:G12 را انتخاب کنید، فرمول TREND (SZ:S12;VZ:B12) را وارد کنید، کلیدها را فشار داده و نگه دارید. و تنها پس از آن کلید را فشار دهید . توجه داشته باشید که فرمول در بریس های فرفری محصور شده است: (و). اینگونه است که اکسل به شما می گوید که این فرمول به عنوان یک فرمول آرایه درک می شود. خودتان پرانتزها را وارد نکنید: اگر بخواهید خودتان آنها را به عنوان بخشی از یک فرمول وارد کنید، اکسل ورودی شما را به عنوان یک رشته متن معمولی در نظر می گیرد.

تابع TREND() دو آرگومان دیگر دارد: new_values_xو پایان. اولی به شما امکان می‌دهد یک پیش‌بینی برای آینده بسازید، و دومی می‌تواند خط رگرسیون را مجبور به عبور از مبدا کند (مقدار TRUE به اکسل می‌گوید که از ثابت محاسبه‌شده، مقدار FALSE - ثابت = 0 استفاده کند). اکسل به شما این امکان را می دهد که یک خط رگرسیون روی یک نمودار بکشید تا از مبدا عبور کند. با رسم نمودار پراکندگی شروع کنید، سپس روی یکی از نشانگرهای سری داده کلیک راست کنید. مورد را در منوی زمینه که باز می شود انتخاب کنید. خط روند را اضافه کنید; یک گزینه را انتخاب کنید خطی; در صورت لزوم، پانل را به پایین اسکرول کنید، کادر را علامت بزنید یک تقاطع راه اندازی کنید; مطمئن شوید که جعبه متن مربوط به آن روی 0.0 تنظیم شده است.

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

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

جایی که rCB . دبلیو- ضریب همبستگی بین متغیرهای کالج (کالج) و کتاب (کتاب) با تأثیر حذف شده (مقدار ثابت) متغیر ثروت (ثروت). rCB- ضریب همبستگی بین متغیرهای کالج و کتاب. rCW- ضریب همبستگی بین متغیرهای دانشکده و رفاه. rb.w.- ضریب همبستگی بین متغیرهای کتاب و رفاه.

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

برنج. 8. همبستگی جزئی به عنوان همبستگی باقیمانده

برای ساده کردن محاسبه ماتریس ضرایب همبستگی (B16: E19)، از بسته تحلیل اکسل (منو) استفاده کنید. داده ها –> تحلیل و بررسی –> تحلیل داده ها). به طور پیش فرض، این بسته در اکسل فعال نیست. برای نصب آن، از طریق منو بروید فایل –> گزینه ها –> افزونه ها. در پایین پنجره ای که باز می شود گزینه هابرتری داشتنمیدان را پیدا کنید کنترل، انتخاب کنید افزونه هابرتری داشتن، کلیک برو. کادر کنار افزونه را علامت بزنید بسته تحلیلی. A را کلیک کنید تحلیل داده ها، یک گزینه را انتخاب کنید همبستگی. $B$2:$D$13 را به عنوان فاصله ورودی مشخص کنید، کادر را علامت بزنید برچسب ها در خط اول، $B$16:$E$19 را به عنوان بازه خروجی مشخص کنید.

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

که در آن H - قد (قد)، W - وزن (وزن)، A - سن (سن)؛ شاخص ضریب همبستگی نیمه جزئی از پرانتز استفاده می کند تا مشخص کند کدام متغیر و از کدام متغیر حذف می شود. در این حالت، علامت W(H.A) نشان می دهد که اثر متغیر Age از متغیر Height حذف می شود، اما از متغیر Weight حذف نمی شود.

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

فصل 4. تابع LINEST().

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

LINEST(B2:B21,A2:A21,TRUE,TRUE)

برنج. 9. تابع LINEST(): الف) محدوده D2:E6 را انتخاب کنید، ب) فرمول را همانطور که در نوار فرمول نشان داده شده است وارد کنید، ج) کلیک کنید

تابع LINEST() برمی گرداند:

• ضریب رگرسیون (یا شیب، سلول D2)؛
• بخش (یا ثابت، سلول E3)؛
• خطاهای استانداردضریب رگرسیون و ثابت (محدوده D3:E3).
• ضریب تعیین R2 برای رگرسیون (سلول D4).
• خطای استاندارد تخمین (سلول E4)؛
• آزمون F برای رگرسیون کامل (سلول D5).
• تعداد درجات آزادی برای مجموع باقیمانده مربع ها (سلول E5)؛
• مجموع رگرسیون مربع ها (سلول D6)؛
• مجموع مربع باقی مانده (سلول E6).

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

خطای استاندارددر مورد ما، این انحراف معیار محاسبه شده برای خطاهای نمونه است. یعنی این وضعیتی است که عموم مردم یک آمار دارند و نمونه آماری دیگر. با تقسیم ضریب رگرسیون بر خطای استاندارد مقدار 2.092/0.818 = 2.559 به شما می رسد. به عبارت دیگر، ضریب رگرسیون 2.092 دو و نیم خطای استاندارد با صفر فاصله دارد.

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

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

STUDENT.DIST.PH (آزمون t = 2.559، تعداد درجات آزادی = 18)

به طور کلی تعداد درجات آزادی = n - k - 1 که n تعداد مشاهدات و k تعداد متغیرهای پیش بینی کننده است.

این فرمول مقدار 0.00987 را برمی گرداند یا تا 1% گرد شده است. این به ما می گوید: اگر ضریب رگرسیون برای جامعه 0٪ باشد، احتمال به دست آوردن نمونه ای 20 نفره که مقدار محاسبه شده ضریب رگرسیون برای آن 2.092 است، 1٪ متوسط ​​است.

آزمون F (سلول D5 در شکل 9) همان عملکردی را در رابطه با رگرسیون کامل انجام می دهد که آزمون t در رابطه با ضریب رگرسیون زوجی ساده. از آزمون F برای آزمایش اینکه آیا ضریب تعیین R2 برای رگرسیون واقعاً به اندازه کافی بزرگ است برای رد این فرضیه استفاده می شود که مقدار آن 0.0 در جامعه است، که نشان دهنده عدم وجود واریانس توضیح داده شده توسط متغیر پیش بینی و پیش بینی است. . هنگامی که تنها یک متغیر پیش بینی وجود دارد، آزمون F دقیقاً برابر با مجذور آزمون t است.

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

آمار R2نسبت واریانس توضیح داده شده را کمی می کند.

خطای استاندارد برآوردروی انجیر جدول 4.9 مقادیر پیش بینی شده متغیر Weight را نشان می دهد که بر اساس رابطه آن با متغیر Height به دست آمده است. محدوده E2:E21 حاوی مقادیر باقیمانده برای متغیر Weight است. به طور دقیق تر، این باقیمانده ها خطا نامیده می شوند - از این رو اصطلاح خطای استاندارد برآورد در زیر آمده است.

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

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

(قد * 2.092 - 3.591) ± 2.092 * 21.118

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

ضریب تعیین R 2 نسبت را بیان می کند مبلغ کلمربع های مرتبط با رگرسیون مقدار (1 - R 2) نسبت مجموع مجموع مربع های مرتبط با باقیمانده ها - خطاهای پیش بینی را بیان می کند. آزمون F را می توان با استفاده از تابع LINEST (سلول F5 در شکل 11)، با استفاده از مجموع مربع ها (محدوده G10:J11)، با استفاده از کسرهای واریانس (محدوده G14:J15) به دست آورد. فرمول ها در فایل اکسل پیوست قابل مطالعه است.

برنج. 11. محاسبه معیار F

هنگام استفاده از متغیرهای اسمی، از کدگذاری ساختگی استفاده می شود (شکل 12). برای رمزگذاری مقادیر، استفاده از مقادیر 0 و 1 راحت است. احتمال F با استفاده از تابع محاسبه می شود:

F.DIST.PH(K2;I2;I3)

در اینجا، تابع F.DIST.RT() احتمال به دست آوردن یک آزمون F را به دنبال توزیع F مرکزی (شکل 13) برای دو مجموعه داده با درجات آزادی داده شده در سلول های I2 و I3 برمی گرداند. همان مقدار داده شده در سلول K2 است.

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

برنج. 13. توزیع F مرکزی برای λ = 0

فصل 5 رگرسیون چندگانه

وقتی از یک رگرسیون زوجی ساده با یک متغیر پیش‌بینی‌کننده به یک رگرسیون چندگانه منتقل می‌شوید، یک یا چند متغیر پیش‌بینی‌کننده را اضافه می‌کنید. مقادیر متغیر پیش‌بینی‌کننده را در ستون‌های مجاور ذخیره کنید، مانند ستون‌های A و B برای دو پیش‌بینی‌کننده، یا A، B و C برای سه پیش‌بینی‌کننده. قبل از وارد کردن فرمولی که شامل تابع LINEST() است، پنج سطر و به تعداد متغیرهای پیش بینی کننده ستون و یک ستون دیگر برای ثابت انتخاب کنید. در مورد رگرسیون با دو متغیر پیش بینی، می توان از ساختار زیر استفاده کرد:

LINEST(A2: A41; B2: C41;; TRUE)

به همین ترتیب، در مورد سه متغیر:

LINEST(A2:A61;B2:D61;;TRUE)

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

برنج. 14. رگرسیون چندگانه

R2 رگرسیون چندگانه (نشان داده شده در سلول F13) از R2 هر رگرسیون ساده (E4, H4) بیشتر است. رگرسیون چندگانه از چندین متغیر پیش بینی به طور همزمان استفاده می کند. در این مورد، R 2 تقریبا همیشه افزایش می یابد.

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

نمایش نتایج برگردانده شده توسط LINEST() برای رگرسیون چندگانه (شکل 15). ضرایب رگرسیون به عنوان بخشی از نتایج برگردانده شده توسط LINEST () نمایش داده می شود. به ترتیب معکوس متغیرها(G–H–I با C–B–A مطابقت دارد).

برنج. 15. ضرایب و خطاهای استاندارد آنها به ترتیب معکوس در کاربرگ نمایش داده می شود.

اصول و رویه‌های مورد استفاده در تحلیل رگرسیون با یک متغیر پیش‌بینی‌کننده به راحتی برای محاسبه متغیرهای پیش‌بینی‌کننده متعدد تطبیق داده می‌شوند. به نظر می رسد که بسیاری از این سازگاری به حذف تأثیر متغیرهای پیش بینی بر یکدیگر بستگی دارد. مورد دوم با همبستگی های خصوصی و نیمه خصوصی مرتبط است (شکل 16).

برنج. 16. رگرسیون چندگانه را می توان از طریق رگرسیون زوجی باقیمانده ها بیان کرد (به فرمول های موجود در فایل اکسل مراجعه کنید)

در اکسل، توابعی وجود دارند که اطلاعاتی در مورد توزیع های t و F ارائه می دهند. توابعی که نام آنها شامل یک قسمت DIST است، مانند STUDENT.DIST() و F.DIST، آزمون t- یا F را به عنوان آرگومان می گیرند و احتمال مشاهده مقدار مشخص شده را برمی گردانند. توابعی که نام آنها شامل یک قسمت OBR است، مانند STUDENT.INV() و F.INV()، یک مقدار احتمال را به عنوان آرگومان می گیرند و یک مقدار معیار مربوط به احتمال مشخص شده را برمی گردانند.

از آنجایی که ما به دنبال مقادیر بحرانی توزیع t هستیم که لبه‌های نواحی دم آن را قطع می‌کند، 5% را به عنوان آرگومان به یکی از توابع ()STUDENT.INV ارسال می‌کنیم که مقداری مطابق با این احتمال را برمی‌گرداند. (شکل 17 و 18).

برنج. 17. آزمون t دو دم

برنج. 18. آزمون تی تک دم

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

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

رویکردی برای ارزیابی تأثیر پیش‌بینی‌کننده‌ها بر اساس مقایسه مدل‌ها.روی انجیر 19 نتایج یک تحلیل رگرسیونی را نشان می دهد که سهم متغیر رژیم غذایی را در معادله رگرسیون آزمایش می کند.

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

نتایج LINEST() (محدوده H2:K6) مربوط به چیزی است که من آن را مدل کامل می نامم، که متغیر LDL را در رژیم غذایی، سن و HDL پس می زند. در محدوده H9:J13، محاسبات بدون در نظر گرفتن متغیر پیش بینی کننده رژیم غذایی ارائه می شود. من آن را مدل محدود می نامم. در مدل کامل، 49.2 درصد از واریانس در متغیر وابسته LDL توسط متغیرهای پیش بینی توضیح داده شده است. در مدل محدود، تنها 30.8 درصد از LDL با سن و HDL توضیح داده می شود. از دست دادن R 2 به دلیل حذف متغیر Diet از مدل 0.183 است. در محدوده G15:L17، محاسباتی انجام شد که نشان می‌دهد تنها با احتمال 0.0288 تأثیر متغیر رژیم غذایی تصادفی است. در 97.1 درصد باقی مانده، رژیم غذایی بر LDL تأثیر دارد.

فصل 6. مفروضات و هشدارها در مورد تحلیل رگرسیون

اصطلاح "فرض" به طور دقیق تعریف نشده است و نحوه استفاده از آن نشان می دهد که اگر این فرض برآورده نشود، نتایج کل تجزیه و تحلیل حداقل مشکوک یا احتمالاً نامعتبر است. در واقع اینطور نیست، اگرچه قطعاً مواردی وجود دارد که نقض فرض اساساً تصویر را تغییر می دهد. مفروضات اصلی عبارتند از: الف) باقیمانده های متغیر Y معمولاً در هر نقطه ای از X در امتداد خط رگرسیون توزیع می شوند. ب) مقادیر Y در هستند وابستگی خطیاز مقادیر X؛ ج) واریانس باقیمانده در هر نقطه X تقریباً یکسان است. د) هیچ رابطه ای بین باقیمانده ها وجود ندارد.

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

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

برنج. 20. رگرسیون لجستیک

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

در اصطلاح آماری، رد فرضیه صفر در حالی که در واقع درست است، خطای نوع یک نامیده می شود.

در افزونه تحلیل داده هاابزاری مناسب برای تولید اعداد تصادفی، که به کاربر امکان می دهد شکل توزیع مورد نظر (مثلاً Normal، Binomial یا Poisson) و همچنین میانگین و انحراف استاندارد را مشخص کند.

تفاوت بین توابع خانواده ()STUDENT.DIST.شروع با نسخه های اکسلدر سال 2010، سه شکل مختلف از یک تابع موجود است که نسبت توزیع را به سمت چپ و/یا راست یک مقدار آزمون t داده شده برمی گرداند. تابع ()STUDENT.DIST نسبت مساحت زیر منحنی توزیع را به سمت چپ مقدار t-test که مشخص کرده‌اید برمی‌گرداند. فرض کنید شما 36 مشاهده دارید، بنابراین تعداد درجه آزادی برای تجزیه و تحلیل 34 و مقدار آزمون t 1.69 است. در این مورد، فرمول

STUDENT.DIST(+1.69;34;TRUE)

مقدار 0.05 یا 5% را برمی گرداند (شکل 21). آرگومان سوم STUDENT.DIST() می تواند TRUE یا FALSE باشد. اگر روی TRUE تنظیم شود، تابع ناحیه تجمعی زیر منحنی را در سمت چپ آزمون t داده شده، که به صورت کسری بیان می‌شود، برمی‌گرداند. اگر FALSE باشد، تابع ارتفاع نسبی منحنی را در نقطه مربوط به آزمون t برمی گرداند. سایر نسخه های تابع STUDENT.DIST () - STUDENT.DIST.PX() و STUDENT.DIST.2X() - فقط مقدار آزمون t و تعداد درجه آزادی را به عنوان آرگومان می گیرند و به آرگومان سوم نیاز ندارند. .

برنج. 21. ناحیه سایه دار تیره تر در دم سمت چپ توزیع مربوط به نسبت سطح زیر منحنی به سمت چپ مقدار تست t مثبت بزرگ است.

برای تعیین مساحت سمت راست آزمون t، از یکی از فرمول ها استفاده کنید:

1 - STUDENT.DIST (1, 69; 34; TRUE)

STUDENT.DIST.PH (1.69;34)

مساحت کل زیر منحنی باید 100% باشد، بنابراین با کم کردن کسری مساحت سمت چپ مقدار آزمون t که توسط تابع برگردانده شده است، کسری مساحت در سمت راست مقدار آزمون t به دست می‌آید. ممکن است ترجیح داده شود که مستقیماً کسری مساحت مورد نظر خود را با استفاده از تابع STUDENT.DIST.RH () بدست آورید، جایی که RH به معنای دنباله سمت راست توزیع است (شکل 22).

برنج. 22. 5% منطقه آلفا برای تست جهت

استفاده از توابع STUDENT.DIST() یا STUDENT.DIST.PH() به این معنی است که شما یک فرضیه کاری جهت دار انتخاب کرده اید. فرضیه کار جهت دار، همراه با تنظیم مقدار آلفا روی 5٪، به این معنی است که شما همه 5٪ را در دم سمت راست توزیع ها قرار می دهید. تنها در صورتی باید فرضیه صفر را رد کنید که احتمال به دست آمدن مقدار آزمون t شما 5 درصد یا کمتر باشد. فرضیه های جهت دار معمولاً منجر به آزمون های آماری حساس تری می شوند (به این حساسیت بیشتر، قدرت آماری بیشتر نیز گفته می شود).

با یک آزمایش بدون جهت، مقدار آلفا در همان سطح 5٪ باقی می ماند، اما توزیع متفاوت خواهد بود. از آنجا که شما باید دو نتیجه را مجاز کنید، احتمال مثبت کاذب باید بین دو دنباله توزیع توزیع شود. به طور کلی پذیرفته شده است که این احتمال به طور مساوی توزیع شود (شکل 23).

با استفاده از همان مقدار t-test به دست آمده و همان تعداد درجه آزادی مانند مثال قبلی، از فرمول استفاده کنید.

STUDENT DIST.2X(1.69;34)

بدون دلیل خاصی، تابع STUDENT.DIST.2X () کد خطای #NUM! را برمی گرداند، اگر مقدار آزمون t منفی به عنوان اولین آرگومان آن داده شود.

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

فصل 7 استفاده از رگرسیون برای آزمایش تفاوت‌های بین میانگین‌های گروهی

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

ساده ترین روش برای رمزگذاری یک متغیر اسمی است کدنویسی ساختگی(شکل 24).

برنج. 24. تحلیل رگرسیون بر اساس کدگذاری ساختگی

هنگام استفاده از کدنویسی ساختگی از هر نوع، قوانین زیر باید رعایت شود:

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

فرمول موجود در سلول‌های F2:H6 =LINEST(A2:A22;C2:D22;;TRUE) آمار رگرسیون را برمی‌گرداند. برای مقایسه، در شکل. 24 نتایج تحلیل واریانس سنتی را نشان می دهد که توسط ابزار برگردانده شده است تحلیل واریانس یک طرفهروبناها تحلیل داده ها.

کدگذاری افکت هادر نوع دیگری از کدنویسی به نام کدگذاری افکت ها،میانگین هر گروه با میانگین میانگین گروه مقایسه می شود. این جنبه از کدگذاری افکت ها به دلیل استفاده از -1 به جای 0 به عنوان کد برای گروهی است که کد یکسانی را در همه بردارهای کد دریافت می کند (شکل 25).

برنج. 25. کدگذاری اثر

هنگامی که از رمزگذاری ساختگی استفاده می شود، مقدار ثابت برگردانده شده توسط LINEST() میانگین گروهی است که کدهای صفر اختصاص داده شده در همه بردارها (معمولاً گروه کنترل) را نشان می دهد. در مورد کدگذاری افکت ها، ثابت برابر با میانگین کلی (سلول J2) است.

عمومی مدل خطی - راه مفیدمفهوم سازی اجزای مقدار متغیر حاصل:

Y ij = μ + α j + ε ij

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

Y ij = Y̅ + a j + e ij

ایده این است که هر مشاهده Y ij را می توان به عنوان مجموع سه جزء زیر مشاهده کرد: میانگین کلی، μ. اثر پردازش j و j ; مقدار e ij، که نشان دهنده انحراف نمره فردی Y ij از مقدار ترکیبی میانگین کلی و اثر jپردازش (شکل 26). هدف از معادله رگرسیون به حداقل رساندن مجموع مجذورهای باقیمانده است.

برنج. 26. مشاهدات تجزیه شده به اجزای یک مدل خطی عمومی

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

برنج. 27. بسته تجزیه و تحلیل واریانس دو طرفه ابزار با تکرارها

ابزار تحلیل واریانس استفاده شده در این شکل از این جهت مفید است که میانگین و واریانس متغیر حاصل و همچنین مقدار شمارنده را برای هر گروه موجود در طرح برمی‌گرداند. جدول تحلیل واریانسدو پارامتر را نشان می دهد که در خروجی نسخه یک طرفه ابزار ANOVA نیستند. به منابع تنوع توجه کنید نمونهو ستون هادر سطرهای 27 و 28. منبع تنوع ستون هابه جنسیت اشاره دارد منبع تنوع نمونهبه هر متغیری اشاره دارد که مقادیر آن ردیف های مختلفی را اشغال می کند. روی انجیر 27، مقادیر گروه CourseLech1 در خطوط 2-6، گروه CourseLech2 در خطوط 7-11 و گروه CourseLech3 در خطوط 12-16 قرار دارند.

نکته اصلی این است که هر دو جنسیت (با ستون‌های برچسب‌گذاری شده در سلول E28) و درمان (با برچسب نمونه در سلول E27) به عنوان منابع تغییرات در جدول ANOVA گنجانده شده‌اند. میانگین مردان با میانگین زنان متفاوت است و این باعث ایجاد تنوع می شود. میانگین ها برای سه درمان نیز متفاوت است - در اینجا منبع دیگری از تنوع است. منبع سومی نیز به نام تعامل وجود دارد که به اثر ترکیبی متغیرهای جنسیت و درمان اشاره دارد.

فصل 8

تجزیه و تحلیل کوواریانس یا ANCOVA (Analysis of Covariation)، سوگیری را کاهش می دهد و قدرت آماری را افزایش می دهد. اجازه دهید به شما یادآوری کنم که یکی از راه های ارزیابی قابلیت اطمینان معادله رگرسیونتست های F هستند:

F = MS Regression/MS Residual

که در آن MS (میانگین مربع) میانگین مربع است و شاخص های رگرسیون و Residual به ترتیب مولفه های رگرسیون و باقیمانده را نشان می دهند. MS Residual با استفاده از فرمول محاسبه می شود:

MS Residual = SS Residual / df Residual

که در آن SS (مجموع مربعات) مجموع مربع ها و df تعداد درجات آزادی است. وقتی کوواریانس را به معادله رگرسیونی اضافه می کنید، مقداری از مجموع مجذورات در SS ResiduaI نیست، بلکه در رگرسیون SS گنجانده می شود. این منجر به کاهش SS Residual l و در نتیجه MS Residual می شود. هرچه MS Residual کوچکتر باشد، آزمون F بزرگتر است و احتمال رد فرضیه صفر مبنی بر اینکه تفاوتی بین میانگین وجود ندارد بیشتر است. در نتیجه، نوسانات متغیر حاصل را دوباره توزیع می‌کنید. در آنالیز واریانس، زمانی که کوواریانس در نظر گرفته نمی شود، تغییرپذیری دچار خطا می شود. اما در ANCOVA، بخشی از متغیری که قبلاً به خطا نسبت داده شده بود، به متغیر کمکی اختصاص داده می شود و بخشی از رگرسیون SS می شود.

مثالی را در نظر بگیرید که در آن همان مجموعه داده ابتدا با ANOVA و سپس با ANCOVA تجزیه و تحلیل می شود (شکل 28).

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

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

محدوده A2:C21 حاوی داده های اولیه است که به تابع LINEST() برای انجام تجزیه و تحلیل با استفاده از کدگذاری افکت ها ارسال می شود. نتایج تابع LINEST() در محدوده E2:F6 نشان داده می شود، جایی که سلول E2 ضریب رگرسیون مرتبط با بردار ضربه را نشان می دهد. سلول E8 شامل آزمون t = 0.93 است و سلول E9 پایایی این آزمون t را آزمایش می کند. مقدار در سلول E9 نشان می دهد که اگر میانگین گروه ها در جامعه برابر باشند، احتمال مواجهه با اختلاف میانگین های گروهی مشاهده شده در این آزمایش 36 درصد است. تنها تعداد کمی این نتیجه را از نظر آماری معنی دار می دانند.

روی انجیر شکل 29 نشان می دهد که وقتی یک متغیر کمکی به تحلیل اضافه می شود چه اتفاقی می افتد. در این مورد، سن هر موضوع را به مجموعه داده اضافه کردم. ضریب تعیین R 2 برای معادله رگرسیونی که از متغیر کمکی استفاده می کند 80/0 است (سلول F4). مقدار R2 در محدوده F15:G19، که در آن من نتایج ANOVA به دست آمده را بدون استفاده از متغیر کمکی بازتولید کردم، تنها 0.05 است (سلول F17). بنابراین، یک معادله رگرسیونی که شامل یک متغیر کمکی است، مقادیر متغیر نمره شناختی را بسیار دقیق‌تر از استفاده از بردار تأثیر به تنهایی پیش‌بینی می‌کند. برای ANCOVA، احتمال رسید تصادفیمقدار F-test نمایش داده شده در سلول F5 کمتر از 0.01٪ است.

برنج. 29. ANCOVA تصویر کاملا متفاوتی را به ارمغان می آورد

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

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

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

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 یک شاخص نسبتاً بالای تأثیر در نظر گرفته می شود.

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

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

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

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

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

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

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

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

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

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

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

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

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

پارامترهای رگرسیون مورد نیاز را در پنجره تنظیم کنید پسرفت، همانطور که در تصویر نشان داده شده است:

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

این نتایج با نتایج محاسبات مستقل در .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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