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

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

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

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

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

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

برای ترسیم یک منحنی گاوسی ، باید دو چیز را بدانید:

• میانگین (همچنین به عنوان اندازه گیری استاندارد شناخته می شود). این مرکز منحنی را تعیین می کند - که به نوبه خود ، موقعیت منحنی را مشخص می کند.
• انحراف استاندارد (SD) اندازه گیری ها. این گسترش داده های شما در توزیع عادی یا به زبان انگلیسی ساده ، منحنی را چقدر گسترده می کند. به عنوان مثال ، در منحنی زنگ که در بالا نشان داده شده است ، یک انحراف استاندارد از میانگین نشان دهنده دامنه بین نمرات امتحان 53 و 85 است.

هرچه SD پایین تر باشد ، منحنی بلندتر و داده های شما کمتر پخش می شود و بالعکس.

شایان ذکر است که قانون 68-95-99. 7 که می تواند برای هر منحنی توزیع عادی اعمال شود ، به این معنی که تقریباً 68 ٪ از داده های شما در یک SD به دور از میانگین ، 95 ٪ در دو SD و 99. 7 ٪ در داخل قرار می گیرند. سه SD

اکنون که ملزومات را می دانید ، بیایید از تئوری به تمرین حرکت کنیم.

شروع شدن

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

مرحله شماره 1: میانگین را پیدا کنید.

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

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

فرمول زیر را در هر سلول خالی (F1 در این مثال) در کنار داده های واقعی خود (ستون های A و B) تایپ کنید تا میانگین نمرات امتحان در مجموعه داده ها را محاسبه کنید:

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

مرحله شماره 2: انحراف استاندارد را پیدا کنید.

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

مجدداً ، فرمول تمام مقادیر را از محدوده سلول مشخص شده (B2: B201) انتخاب می کند و انحراف استاندارد آن را محاسبه می کند - فقط فراموش نکنید که خروجی را نیز جمع کنید.

مرحله شماره 3: مقادیر محور x را برای منحنی تنظیم کنید.

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

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

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

بیایید یک شمارش را در یک شروع کنیم (زیرا هیچ راهی وجود ندارد که دانش آموز بتواند نمره امتحان منفی را بدست آورد) و تا 150 نفر را طی کنیم - مهم نیست که 150 یا 1500 باشد - برای تنظیم یک جدول یاور دیگر.

1. هر سلول خالی را در زیر داده های نمودار (مانند E4) انتخاب کنید و "1" را تایپ کنید ، مقداری که فاصله اول را مشخص می کند.
2. به برگه خانه بروید.
3. در گروه ویرایش ، "پر کردن" را انتخاب کنید."
4. تحت "سری در" ، "ستون" را انتخاب کنید."
5. برای "مقدار مرحله" ، نوع "1."این مقدار افزایش هایی را که به طور خودکار اضافه می شود تعیین می کند تا اینکه اکسل به آخرین بازه برسد.
6. برای "Stop Value" ، "150" را تایپ کنید ، مقداری که برای آخرین بازه است ، و روی "OK" کلیک کنید."

به طور معجزه آسایی ، 149 سلول در ستون E (E5: E153) با مقادیر 2 تا 150 پر شده اند.

توجه: سلولهای داده اصلی را همانطور که در تصاویر نشان داده شده است پنهان نکنید. در غیر این صورت ، این تکنیک کار نخواهد کرد.

مرحله شماره 4: مقادیر توزیع عادی را برای هر مقدار محور x محاسبه کنید.

اکنون ، مقادیر توزیع عادی را پیدا کنید-احتمال اینکه یک دانش آموز نمره امتحانی خاصی را که توسط یک مقدار خاص محور X نشان داده شده است-برای هر یک از فواصل. خوشبختانه برای شما ، اکسل اسب کار برای انجام همه این محاسبات را برای شما دارد: عملکرد Norm. dist.

فرمول زیر را در سلول در سمت راست (F4) از اولین فاصله خود (E4) تایپ کنید:

در اینجا نسخه رمزگشایی شده برای کمک به شما در تنظیم تنظیم شده است:

شما مقادیر متوسط و SD را قفل می کنید تا بتوانید با زحمت فرمول فواصل باقی مانده را اجرا کنید (E5: E153).

اکنون ، روی دسته پر کلیک کنید تا فرمول را در بقیه سلول ها کپی کنید (F5: F153).

مرحله شماره 5: یک طرح پراکندگی با خطوط صاف ایجاد کنید.

سرانجام ، زمان ساخت منحنی زنگ فرا رسیده است:

1. هر مقدار را در جدول یاور حاوی مقادیر محور X- و Y انتخاب کنید (E4: F153).
2. به برگه درج بروید.
3. روی دکمه "Insert Scatter (X ، Y) یا نمودار حباب" کلیک کنید.
4. "پراکندگی با خطوط صاف" را انتخاب کنید.

مرحله شماره 6: جدول برچسب را تنظیم کنید.

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

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

برای آن ، جدول یاور دیگری را به شرح زیر تنظیم کنید:

ابتدا میانگین مقدار (F1) را در کنار سلول مربوطه در ستون X-Value (I5) کپی کنید.

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

به عبارت ساده ، فرمول مجموع مقادیر انحراف استاندارد قبلی را از میانگین کم می کند. اکنون ، دسته پر را به سمت بالا بکشید تا فرمول را در دو سلول باقیمانده کپی کنید (I2: I3).

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

به همین روش ، فرمول دو سلول دیگر را اجرا کنید (i7: i8).

سرانجام ، مقادیر برچسب محور y (J2: J8) را با صفرها پر کنید زیرا می خواهید نشانگرهای داده در محور افقی قرار بگیرند.

مرحله شماره 7: داده های برچسب را در نمودار وارد کنید.

اکنون تمام داده هایی را که تهیه کرده اید اضافه کنید. بر روی نمودار کلیک راست کنید و "داده را انتخاب کنید."

در کادر گفتگو که ظاهر می شود ، "افزودن را انتخاب کنید."

سلولهای مربوطه را از جدول Helper - I2: I8 برای "سری X" و J2: J8 برای "سری Y Series Y" برجسته کنید و روی "OK" کلیک کنید."

مرحله شماره 8: نوع نمودار سری برچسب را تغییر دهید.

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

بعد ، یک نمودار دسته کوچک موسیقی جاز را طراحی کنید:

1. به برگه دسته کوچک موسیقی جاز بروید.
2. برای سری "سری 2" ، "نوع نمودار" را به "پراکندگی" تغییر دهید."
   • توجه: مطمئن شوید که «Sersies1» به صورت «Scatter with Smooth Lines» باقی بماند. گاهی اوقات اکسل زمانی که یک ترکیب ترکیبی می‌سازید آن را تغییر می‌دهد همچنین مطمئن شوید که «Serie1» به محور ثانویه فشار نیاورده است - کادر تأیید کنار نوع نمودار نباید علامت‌گذاری شود.
3. روی "OK" کلیک کنید.

مرحله نهم: مقیاس محور افقی را تغییر دهید.

با تنظیم مقیاس محور افقی، نمودار را روی منحنی زنگ مرکز قرار دهید. بر روی محور افقی کلیک راست کرده و "Format Axis" را از منو انتخاب کنید.

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

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

مرحله 10: برچسب های داده های سفارشی را درج و در موقعیت قرار دهید.

همانطور که نمودار خود را صیقل می‌دهید، حتماً برچسب‌های داده سفارشی را اضافه کنید. ابتدا روی هر نقطه ای که نمایانگر سری “Series2” است کلیک راست کرده و “Add Data Labels” را انتخاب کنید.”

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

1. روی هر برچسب داده سری «Series2» کلیک راست کنید.
2. « Format Data Labels » را انتخاب کنید.”
3. در پنجره کار، به تب گزینه های برچسب بروید.
4. کادر "X Value" را علامت بزنید.
5. تیک کادر «Y Value» را بردارید.
6. در قسمت «موقعیت برچسب»، «بالا» را انتخاب کنید.

Also, you can now remove the gridlines (right-click on them >حذف).

مرحله 11: نشانگرهای داده را دوباره رنگ کنید (اختیاری).

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

1. روی هر برچسب داده سری «Series2» کلیک راست کنید.
2. روی دکمه «پر کردن» کلیک کنید.
3. رنگ خود را از پالت ظاهر شده انتخاب کنید.

همچنین، حاشیه های اطراف نقاط را بردارید:

1. دوباره روی همان نشانگر داده کلیک راست کرده و "Outline" را انتخاب کنید.
2. "بدون طرح کلی" را انتخاب کنید.

مرحله 12: خطوط عمودی را اضافه کنید (اختیاری).

به عنوان آخرین تنظیم، می توانید خطوط عمودی را به نمودار اضافه کنید تا به تأکید بر مقادیر SD کمک کند.

• نمودار نمودار را انتخاب کنید (به این ترتیب خطوط مستقیماً در نمودار درج می شوند).
• به برگه درج بروید.
• روی دکمه "شکل ها" کلیک کنید.
• "خط" را انتخاب کنید.”

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

عنوان نمودار را تغییر دهید، و منحنی زنگ بهبود یافته شما آماده است - داده های توزیع ارزشمند شما را نشان می دهد.

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