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

  • 2021-03-1

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

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

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

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

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

How to create a normal distribution bell curve in Excel

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

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

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

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

The 68-95-99.7 rule

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

شروع شدن

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

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

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

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

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

Find the mean

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

Round up the mean value

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

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

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

Find the standard deviation

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

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

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

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

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

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

Set up the x-axis values for the curve

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

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

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

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

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

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

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

NORM.DIST function in Excel

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

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

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

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

Create a scatter plot with smooth lines

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

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

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

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

Set up the label table

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

Set up the label table step 2

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

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

Set up the label table step 3

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

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

Set up the label table step 4

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

Set up the label table step 5

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

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

Excel select data

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

Add data series in Excel

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

Add the X and Y values from the helper table

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

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

Change the chart type of the label series

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

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

Build a combo chart in Excel

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

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

Modify the horizontal axis scale

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

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

Tweak the Minimum and Maximum Bounds values

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

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

Insert the default data labels

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

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

Insert and reposition the custom labels

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

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

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

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

Recolor the data markers

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

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

Remove the borders around the data markers

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

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

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

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

Add vertical lines

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

Gaussian curve in Excel

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

ثبت دیدگاه

مجموع دیدگاهها : 0در انتظار بررسی : 0انتشار یافته : ۰
قوانین ارسال دیدگاه
  • دیدگاه های ارسال شده توسط شما، پس از تایید توسط تیم مدیریت در وب منتشر خواهد شد.
  • پیام هایی که حاوی تهمت یا افترا باشد منتشر نخواهد شد.
  • پیام هایی که به غیر از زبان فارسی یا غیر مرتبط باشد منتشر نخواهد شد.