محاسبه فاصله اطمینان در اکسل

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

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

محاسبه فاصله اطمینان

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

دو روش برای انجام این کار در مایکروسافت اکسل وجود دارد:

• اپراتور هنجار اعتماد - در مواردی که پراکندگی مشخص است استفاده می شود.
• اپراتور TRUST.STUDENTزمانی که واریانس ناشناخته است.

در زیر گام به گام هر دو روش را در عمل تجزیه و تحلیل خواهیم کرد.

روش 1: بیانیه TRUST.NORM

این تابع برای اولین بار در نسخه اکسل 2010 در زرادخانه برنامه معرفی شد (قبل از این نسخه، اپراتور جایگزین آن شد "قابل اعتماد”). اپراتور در رده "آماری" گنجانده شده است.

فرمول تابع هنجار اعتماد به نظر می رسد که:

=ДОВЕРИТ.НОРМ(Альфа;Станд_откл;Размер)

همانطور که می بینیم، تابع دارای سه آرگومان است:

• "آلفا" شاخصی از سطح اهمیت است که به عنوان مبنای محاسبه در نظر گرفته می شود. سطح اطمینان به صورت زیر محاسبه می شود:
  • 1-"Альфа". این عبارت در صورت مقدار قابل استفاده است "آلفا" به صورت ضریب ارائه می شود. مثلا، 1-0,7،0,3 XNUMX،XNUMX =، که در آن 0,7=70%/100%.
  • (100-"Альфа")/100. اگر سطح اطمینان را با مقدار در نظر بگیریم این عبارت اعمال خواهد شد "آلفا" در درصد مثلا، (100-70) / 100 = 0,3.
• "انحراف معیار" - به ترتیب، انحراف معیار نمونه داده های تجزیه و تحلیل شده.
• "اندازه" اندازه نمونه داده است.

توجه داشته باشید: برای این تابع، وجود هر سه آرگومان پیش نیاز است.

اپراتور "قابل اعتماد”، که در نسخه های قبلی برنامه استفاده می شد، حاوی همان آرگومان ها است و عملکردهای مشابهی را انجام می دهد.

فرمول تابع قابل اعتماد به شرح زیر است:

=ДОВЕРИТ(Альфа;Станд_откл;Размер)

در خود فرمول هیچ تفاوتی وجود ندارد، فقط نام اپراتور متفاوت است. در اکسل 2010 و نسخه های بعدی، این اپراتور در دسته Compatibility قرار دارد. در نسخه های قدیمی برنامه، در قسمت استاتیک توابع قرار دارد.

مرز فاصله اطمینان با فرمول زیر تعیین می شود:

X+(-)ДОВЕРИТ.НОРМ

جایی که Х مقدار متوسط ​​در محدوده مشخص شده است.

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

وظیفه ما بدست آوردن مقدار فاصله اطمینان با سطح اطمینان 95 درصد است.

1. ابتدا یک سلول را برای نمایش نتیجه انتخاب کنید. سپس روی دکمه کلیک می کنیم "درج تابع" (در سمت چپ نوار فرمول).
2. پنجره Function Wizard باز می شود. با کلیک بر روی دسته فعلی توابع، لیست را گسترش داده و روی خط موجود در آن کلیک کنید "آماری".
3. در لیست پیشنهادی، روی اپراتور کلیک کنید "هنجار اعتماد"، سپس فشار دهید OK.
4. ما پنجره ای را با تنظیمات آرگومان های تابع می بینیم که دکمه را فشار می دهیم OK.
   • در این زمینه "آلفا" سطح اهمیت را نشان می دهد. وظیفه ما سطح اطمینان 95% را در نظر می گیرد. با جایگزینی این مقدار به فرمول محاسبه ای که در بالا در نظر گرفتیم، عبارت زیر را به دست می آوریم: (100-95)/100. ما آن را در قسمت آرگومان می نویسیم (یا می توانید بلافاصله نتیجه محاسبه را برابر با 0,05 بنویسید).
   • در این زمینه "std_off" با توجه به شرایطمان عدد 8 را می نویسیم.
   • در قسمت "Size" تعداد عناصر مورد بررسی را مشخص کنید. در مورد ما، 10 اندازه گیری انجام شد، بنابراین عدد 10 را می نویسیم.
5. برای جلوگیری از پیکربندی مجدد عملکرد هنگام تغییر داده ها، می توانید آن را خودکار کنید. برای این کار از تابع “بررسی". اشاره گر را در قسمت ورودی اطلاعات آرگومان قرار دهید "اندازه"، سپس روی نماد مثلث در سمت چپ نوار فرمول کلیک کنید و روی مورد کلیک کنید “ویژگی های بیشتر…”.
6. در نتیجه پنجره دیگری از Function Wizard باز می شود. با انتخاب دسته بندی "آماری"، روی تابع کلیک کنیدبررسی"، سپس اوکی.
7. صفحه پنجره دیگری با تنظیمات آرگومان های تابع نمایش داده می شود که برای تعیین تعداد سلول ها در یک محدوده مشخص که حاوی داده های عددی هستند استفاده می شود.

   فرمول تابع بررسی اینگونه نوشته شده است: =СЧЁТ(Значение1;Значение2;...).

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

8. در نتیجه اقدامات انجام شده، نتیجه محاسبات برای اپراتور در سلول انتخاب شده نمایش داده می شود. هنجار اعتماد. در مشکل ما، مقدار آن برابر است با 4,9583603.
9. اما این هنوز نتیجه نهایی در وظیفه ما نیست. در مرحله بعد، باید مقدار متوسط ​​را در یک بازه زمانی مشخص محاسبه کنید. برای این کار باید از تابع “قلب"A که وظیفه محاسبه میانگین را در محدوده مشخصی از داده ها انجام می دهد.

   فرمول عملگر به صورت زیر نوشته شده است: =СРЗНАЧ(число1;число2;...).

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

10. در دسته بندی "آماری" یک اپراتور خسته کننده را انتخاب کنید "قلب" کلیک کنید و OK.
11. آرگومان های تابع در مقدار آرگومان "عدد" محدوده ای را مشخص کنید که شامل تمام سلول ها با مقادیر تمام اندازه گیری ها می شود. سپس کلیک می کنیم باشه.
12. در نتیجه اقدامات انجام شده، مقدار متوسط ​​به طور خودکار محاسبه می شود و با تابع جدید درج شده در سلول نمایش داده می شود.
13. اکنون باید مرزهای CI (فاصله اطمینان) را محاسبه کنیم. بیایید با محاسبه مقدار حاشیه سمت راست شروع کنیم. سلولی را که می خواهیم نتیجه را در آن نمایش دهیم انتخاب می کنیم و با استفاده از عملگرها نتایج به دست آمده را جمع می کنیم.قلب" و "هنجارهای اعتماد”. در مورد ما، فرمول به صورت زیر است: A14+A16. پس از تایپ آن را فشار دهید وارد.
14. در نتیجه، محاسبه انجام می شود و نتیجه بلافاصله در سلول با فرمول نمایش داده می شود.
15. سپس به روشی مشابه، محاسبه را انجام می دهیم تا مقدار مرز سمت چپ CI را به دست آوریم. فقط در این مورد ارزش نتیجه "هنجارهای اعتماد” شما نیازی به اضافه کردن ندارید، بلکه از نتیجه به دست آمده با استفاده از عملگر کم می کنید.قلب". در مورد ما، فرمول به صورت زیر است: =A16-A14.
16. پس از زدن Enter نتیجه را در سلول داده شده با فرمول دریافت می کنیم.

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

• برای تعیین مرز مناسب CI، فرمول کلی به صورت زیر خواهد بود:

  =СРЗНАЧ(B2:B11)+ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)).

• به طور مشابه، برای حاشیه سمت چپ، فقط به جای یک پلاس، باید یک منفی قرار دهید:

  =СРЗНАЧ(B2:B11)-ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)).

روش 2: اپراتور TRUST.STUDENT

حال بیایید با عملگر دوم برای تعیین فاصله اطمینان - آشنا شویم TRUST.STUDENT. این تابع نسبتاً اخیراً با شروع نسخه اکسل 2010 در برنامه معرفی شده است و هدف آن تعیین CI مجموعه داده انتخابی با استفاده از توزیع Student با واریانس ناشناخته است.

فرمول تابع TRUST.STUDENT به شرح زیر است:

=ДОВЕРИТ.СТЬЮДЕНТ(Альфа;Cтанд_откл;Размер)

بیایید کاربرد این عملگر را در مثال همان جدول تحلیل کنیم. فقط در حال حاضر ما انحراف معیار را با توجه به شرایط مشکل نمی دانیم.

1. ابتدا سلولی را که قصد داریم نتیجه را در آن نمایش دهیم انتخاب کنید. سپس بر روی آیکون کلیک کنید "درج تابع" (در سمت چپ نوار فرمول).
2. پنجره Function Wizard از قبل شناخته شده باز می شود. یک دسته را انتخاب کنید "آماری"، سپس از لیست توابع پیشنهادی، روی اپراتور کلیک کنید "دانشجوی مورد اعتماد"، سپس - OK.
3. در پنجره بعدی باید آرگومان های تابع را تنظیم کنیم:
   • در "آلفا" مانند روش اول، مقدار 0,05 (یا "100-95)/100" را مشخص کنید.
   • بریم سر بحث. "std_off". از آنجایی که با توجه به شرایط مشکل، مقدار آن برای ما ناشناخته است، باید محاسبات مناسب را انجام دهیم که در آن اپراتور "STDEV.B”. روی دکمه افزودن تابع و سپس روی مورد کلیک کنید “ویژگی های بیشتر…”.
   • در پنجره بعدی Function Wizard، عملگر " را انتخاب کنید.STDEV.B” در رده "آماری" کلیک کنید و OK.
   • وارد پنجره تنظیمات آرگومان های تابع می شویم که فرمول آن به شکل زیر است: =СТАНДОТКЛОН.В(число1;число2;...). به عنوان اولین آرگومان، محدوده ای را مشخص می کنیم که شامل تمام سلول های ستون "Value" (بدون احتساب سرصفحه) باشد.
   • اکنون باید به پنجره با آرگومان های تابع بازگردید.TRUST.STUDENT”. برای این کار روی کتیبه ای به همین نام در قسمت ورودی فرمول کلیک کنید.
   • حالا بیایید به آخرین استدلال "اندازه" برویم. مانند روش اول، در اینجا می توانید به سادگی محدوده ای از سلول ها را مشخص کنید یا عملگر را وارد کنید "بررسی". آخرین گزینه را انتخاب می کنیم.
   • پس از پر شدن همه آرگومان ها، روی دکمه کلیک کنید OK.
4. سلول انتخاب شده مقدار فاصله اطمینان را با توجه به پارامترهایی که ما مشخص کردیم نمایش می دهد.
5. در مرحله بعد، ما باید مقادیر مرزهای CI را محاسبه کنیم. و برای این باید مقدار متوسط ​​محدوده انتخاب شده را بدست آورید. برای انجام این کار، ما دوباره تابع "قلب". الگوریتم اقدامات مشابه آنچه در روش اول توضیح داده شد است.
6. دریافت مقدار "قلب"، می توانید شروع به محاسبه مرزهای CI کنید. خود فرمول ها با فرمول های استفاده شده با " تفاوتی ندارندهنجارهای اعتماد”:
   • حاشیه سمت راست CI=AVERAGE+اطمینان دانشجو
   • کران چپ CI=اعتماد متوسط-دانشجو

نتیجه

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