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

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

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

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

اصول ایجاد منحنی زنگ در اکسل

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

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

• میانگین – مقدار متوسط ​​مجموعه داده (مرکز منحنی را نشان می دهد)
• انحراف استاندارد – میزان پراکندگی نقاط داده را نسبت به میانگین اندازه گیری می کند (گسترش منحنی را نشان می دهد)

یافتن میانگین

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

=AVERAGE(B2:B16) را تایپ کنید تا میانگین برگه علامت داده شده در بالا را بیابید. متوجه خواهید شد که مقدار 53.93 را می دهد.

اگر یک مقدار عدد کامل می خواهید، که معمولاً می خواهید، می توانید از تابع ROUND استفاده کنید. برای انجام این کار، تایپ کنید:

=ROUND(AVERAGE(B2:B16),0)

حالا میانگین می شود 54.

پیدا کردن انحراف معیار

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

• STDEV.P زمانی استفاده می شود که می دانید داده های شما کامل است، یعنی یک جمعیت است.
• STDEV.S زمانی استفاده می‌شود که داده‌های شما ناقص باشد، یعنی نمونه‌ای از یک جامعه داشته باشید.

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

=STDEV.P(B2:B16)

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

=ROUND(STDEV.P(B2:B16),0)

28 میگیری

مرتب سازی داده ها به ترتیب صعودی

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

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

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

1. یافتن توزیع نرمال

زمان محاسبه توزیع نرمال نقاط داده است. در اکسل، می توانید با استفاده از تابع NORM.DIST، توزیع نرمال را پیدا کنید، که به متغیرهای زیر نیاز دارد:

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

برای محاسبه توزیع نرمال نمرات آزمون ما:

1. =NORM.DIST( را در یک سلول جدید تایپ کنید (سلول C2 در مورد ما.)
2. مقادیر مورد نیاز را با کاما بین مقادیر همانطور که در نحو نشان داده شده است وارد کنید.
3. برای x، B2 را وارد کنید، که اولین نقطه داده، یعنی 12 را می دهد.
4. برای میانگین، D2 را وارد کنید که میانگین داده های ما را نشان می دهد و F4 را فشار دهید. F4 مقدار میانگین نشان داده شده با علامت دلار ($D$2) را قفل می کند، به طوری که وقتی این فرمول را برای مقادیر مختلف در داده های ما کپی می کنید، مقدار میانگین ثابت می ماند.
5. برای انحراف استاندارد، E2 را وارد کنید تا انحراف استاندارد را دریافت کنید و با فشار دادن F4 مقدار آن را قفل کنید.
6. در نهایت، به جای مقدار منطقی تجمعی، FALSE را تایپ کنید و پرانتزها را ببندید. با تایپ FALSE، تابع چگالی احتمال عادی (PDF) را دریافت می کنید. توجه: برای به دست آوردن تابع توزیع نرمال تجمعی (CDF) باید TRUE را تایپ کنید.
7. پس از دریافت توزیع نرمال برای مقدار اول، به سادگی آن را بکشید تا توزیع نرمال برای مقادیر باقی مانده از مجموعه داده های خود را بدست آورید.

2. ایجاد یک طرح پراکندگی

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

1. مجموعه داده (نمرات دانش آموزان) و توزیع نرمال آنها را انتخاب کنید.
2. به Insert > Scatter Diagram بروید.
3. Scatter with Smooth Lines را انتخاب کنید.
4. و منحنی زنگ خود را دارید.

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

3. سفارشی کردن منحنی زنگ

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

با خاموش کردن کلید Legend می‌توانید Series1 نوشته شده در پایین منحنی زنگ را حذف کنید. برای اینکه به منحنی زنگ خود شکل بهتری بدهید، می توانید حداکثر و حداقل مقدار نمودار زنگ خود را تعریف کنید. برای این کار، روی x-axis دوبار کلیک کنید و برگه Axis Options را خواهید دید که از آنجا می توانید تغییرات مورد نیاز را انجام دهید.

استفاده از منحنی های زنگ در اکسل

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

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

منبع مقاله