كيفية إنشاء قالب مخطط منحنى الجرس في Excel؟

إن إنشاء منحنى الجرس في Excel هو عملية متعددة الخطوات، ولكن بمجرد الإعداد، يمكنك تخصيص المخطط لمجموعات بيانات مختلفة أو حفظ التصميم لاستخدامه المتكرر. يمنحك هذا الحل تحكمًا كاملًا في البيانات، أساليب الحساب، والتنسيق البصري - وهو مثالي إذا كنت بحاجة إلى مخطط دقيق ومخصص لتحليل البيانات المتعمق.

المزايا: أقصى قدر من المرونة؛ جميع الخطوات واضحة للتعلم أو لأغراض التوثيق.
العيوب: يتطلب خطوات حساب يدوية وانتباهًا دقيقًا للصيغ.

اتبع هذه الخطوات التفصيلية:

1. افتح مصنف Excel جديدًا، وأنشئ عناوين الأعمدة في النطاق A1:D1 كما هو موضح في لقطة الشاشة أدناه. العناوين المقترحة هي: البيانات, التوزيع, المتوسط, الانحراف المعياري.

2. أدخل أرقام المصدر الخاصة بك تحت عمود "البيانات". لتحقيق أفضل مظهر لمنحنى الجرس، يجب أن تغطي بياناتك نطاقًا واسعًا وأن تكون كافية في العدد (يُوصى بما لا يقل عن 30-50 قيمة). في مثالنا، سنقوم بإدخال القيم من 10 إلى 100 في A2:A92.
لضمان ترتيب البيانات من الأصغر إلى الأكبر، حدد الخلايا المملوءة بالأرقام في العمود A وانقر البيانات > ترتيب من الألف إلى الياء. هذا يساعد في تشكيل المنحنى المرسوم بشكل صحيح.

3. احسب الإحصائيات الداعمة:

(1) في الخلية C2، أدخل الصيغة التالية لحساب المتوسط الحسابي (المتوسط) لمجموعة البيانات الخاصة بك:

هذه الوظيفة تأخذ متوسط النطاق المحدد. تأكد من أن النطاق الخاص بك يتطابق مع بياناتك الفعلية أو قم بالتعديل حسب الحاجة.

(2) في الخلية D2، احسب الانحراف المعياري، والذي يُستخدم لتشكيل عرض منحنى الجرس:

ملاحظة: في إصدارات Excel الحديثة، قد يتم اقتراح =STDEV.S() لحساب الانحراف المعياري للعينة.

(3) في B2، قم بتوليد التوزيع الاحتمالي لكل نقطة بيانات. بناءً على إصدار Excel، استخدم أحد هذه الصيغ:

أدخل الصيغة المناسبة في الخلية B2. ثم اسحب مقبض التعبئة التلقائية لأسفل لملء الصيغة لجميع صفوف البيانات (حتى B92 في هذا المثال). هذا ينشئ قيمة التوزيع (منحنى الجرس) لكل نقطة بيانات أصلية.

ملاحظة: إذا كانت مجموعة البيانات الخاصة بك تغطي نطاقًا مختلفًا، قم بتحديث جميع مراجع الخلايا في الصيغ وفقًا لذلك. غالبًا ما تنتج الأخطاء في تطبيق الصيغ بسبب النطاق غير الصحيح أو وضع الخلايا بطريقة خاطئة، لذا تحقق جيدًا من المراجع.

4. قم بتمييز كل من عمودي البيانات والتوزيع (مثل النطاق A2:B92). انتقل إلى إدراج > مخطط مبعثر (أو مخطط مبعثر ودونات في Excel 2013+) > مخطط مبعثر بخطوط ومعلمات ناعمة. هذا النوع من المخططات يبرز أفضل شكل لنموذج منحنى الجرس.

يعرض المخطط الآن منحنى الجرس الخاص بك، مشابهًا للمثال التالي:

من أجل الوضوح والجمالية، قد ترغب في إزالة عناصر المخطط غير الضرورية مثل خطوط الشبكة، تسميات المحاور، أو الرموز لتسليط الضوء على شكل الجرس. انقر بزر الماوس الأيمن على العنصر الذي ترغب في إزالته وحدد “حذف” أو قم بإلغاء تحديده من خيارات تنسيق المخطط.

لإعادة استخدام هذا المخطط مع بيانات أخرى، يجب حفظه كقالب:

5. حفظ منحنى الجرس كقالب مخطط:
أ. في Excel 2013 أو أحدث: انقر بزر الماوس الأيمن على مخطط منحنى الجرس المكتمل، وحدد حفظ كقالب من القائمة.
ب. في Excel 2007/2010: انقر فوق المخطط لتفعيل أدوات المخططثم اذهب إلى تصميم > حفظ كقالب.
هذا يتيح إنشاء أسرع لمنحنيات جرس جديدة لمجموعات بيانات أخرى في المستقبل دون تكرار كافة التنسيقات.

6. عندما يظهر مربع الحوار حفظ قالب المخطط، حدد اسمًا واضحًا في حقل اسم الملف (على سبيل المثال، "BellCurveTemplate")، وانقر حفظ. يتم تخزين هذا القالب في مجلد "القوالب" الافتراضي، وهو عادةً ما يكون قابلًا للوصول من خلال مربع حوار اختيار المخطط للمصنفات الجديدة.

نصائح لحل المشكلات:
- إذا كان خيار حفظ القالب غير متاح، تأكد من تحديد المخطط وأن لديك الأذونات المناسبة لكتابة الملفات في مجلد القوالب الافتراضي.
- إذا لم تكن المخططات المستقبلية تشبه منحنى الجرس المحفوظ الخاص بك، فتحقق مرة أخرى من بيانات الإدخال لضمان اكتمالها وتنسيقها الصحيح.

إذا كنت ترغب في تجاوز الحسابات اليدوية والصيغ المعقدة، توفر Kutools for Excel ميزة التوزيع الطبيعي / منحنى الجرس التي تنشئ مخططات منحنى الجرس الاحترافية بنقرات قليلة فقط. هذه الطريقة ذات قيمة خاصة عند العمل مع بيانات غير مألوفة أو عند الحاجة إلى رؤية إحصائية فورية دون معرفة عميقة بوظائف Excel.

المزايا: تقلل بشكل كبير من الوقت والمهارة المطلوبة لإنشاء منحنى الجرس أو المخططات المركبة. تتضمن خيارات إضافية مثل المدرج التكراري والمخططات المركبة لتحليل أكثر شمولًا.
العيوب: يتطلب تثبيت Kutools for Excel.

1. حدد النطاق الذي يحتوي على قيم البيانات الخاصة بك. تأكد من أن بياناتك رقمية ولا تحتوي على خلايا فارغة أو نصوص لتحقيق النتائج المثلى. انقر Kutools > Charts > Data Distribution > Normal Distribution / Bell Curve.

2. في مربع الحوار الذي يظهر، حدد مخطط التوزيع الطبيعي الخيار ضمن اختيار القسم. انقر موافق لإنشاء المخطط.

يتيح لك هذا الحوار أيضًا:
(1) إدخال عنوان المخطط اختياريًا للتسمية الفورية.
(2) إنشاء مدرج تكراري عن طريق تحديد فقط مخطط المدرج التكراري.
(3) الجمع بين المدرج التكراري ومنحنى الجرس في عرض واحد عن طريق تحديد كلا الخيارين ضمن اختيار.

إذا تم اختيار مخطط التوزيع الطبيعي الخيار فقط:

إذا تم تحديد كلا الخيارين مخطط التوزيع الطبيعي و مخطط المدرج التكراري للتأثير المركب:

احتياطات:
- تأكد من أن نطاق البيانات يحتوي فقط على إدخالات عددية صالحة.
- إذا لم يبدو المخطط الناتج كما هو متوقع، فتحقق من وجود أخطاء في البيانات أو عدم تطابق النطاقات.

بالمقارنة مع الحلول اليدوية، يعد استخدام Kutools for Excel مثاليًا للحصول على نتائج سريعة وموحدة، خاصة عند إنتاج المخططات للتقارير أو العروض التقديمية بأقل جهد.

بالنسبة للمستخدمين المتقدمين أو أولئك الذين يقومون بأتمتة التقارير المتكررة، يمكن لماكرو VBA بسيط إنشاء بيانات منحنى الجرس بسرعة من المعلمات المحددة من قبل المستخدم ورسم الرسم البياني تلقائيًا. هذا مفيد بشكل خاص عند التعامل مع بيانات متغيرة أو للتقارير المتكررة التي تتطلب تنسيقًا متسقًا.

المزايا: يمكن أن يفعل كل شيء من الحساب وإنشاء المخطط؛ جيد لمعالجة الدفعات.
العيوب: يتطلب بعض الإلمام بالماكرو وقد يتطلب أذونات أمان لتشغيل نصوص VBA.

1. تحضير بياناتك.
   إذا كنت تمتلك بالفعل مجموعة بيانات (مثلًا في النطاق A2:A92)، احسب المتوسط، الانحراف المعياري، بداية النطاق/نهايته باستخدام صيغ Excel:
   احصل على المتوسط:

   =AVERAGE(A2:A92)

   احصل على الانحراف المعياري:

   =STDEV.P(A2:A92)

   احصل على بداية النطاق:

   =Mean-3*Standard Deviation

   بما أن المتوسط موجود في B2، الانحراف المعياري في C2، يجب أن تكون هذه الصيغة: =B2-3*C2
   احصل على نهاية النطاق:

   =Mean+3*Standard Deviation

   بما أن المتوسط موجود في B2، الانحراف المعياري في C2، يجب أن تكون هذه الصيغة: =B2+3*C2
   للخطوة، استخدم 1 أو 0.1. كلما كانت قيمة الخطوة أصغر، أصبح المنحنى أنعم.

   

2. تشغيل الكود
   1. اضغط Alt + F11 لفتح محرر VBA.
   2. إدراج وحدة جديدة ولصق كود GenerateBellCurve.

      Sub GenerateBellCurve()
      'Updated by Extendoffice 2025/07/24
          Dim xMean As Double
          Dim xStdev As Double
          Dim xStart As Double
          Dim xEnd As Double
          Dim xStep As Double
          Dim xRow As Integer
          Dim ws As Worksheet
          Dim chartObj As ChartObject
          Dim xValue As Double
      
          On Error Resume Next
          xTitleId = "KutoolsforExcel"
          
          Set ws = Worksheets.Add
          ws.Name = "BellCurve"
          
          xMean = Application.InputBox("Enter mean value:", xTitleId, 50, Type:=1)
          xStdev = Application.InputBox("Enter standard deviation:", xTitleId, 10, Type:=1)
          xStart = Application.InputBox("Enter range start (e.g. 10):", xTitleId, xMean - 3 * xStdev, Type:=1)
          xEnd = Application.InputBox("Enter range end (e.g. 100):", xTitleId, xMean + 3 * xStdev, Type:=1)
          xStep = Application.InputBox("Enter step interval (e.g. 1):", xTitleId, 1, Type:=1)
      
          ws.Range("A1:B1").Value = Array("X", "Normal Distribution")
          
          xRow = 2
          For xValue = xStart To xEnd Step xStep
              ws.Cells(xRow, 1).Value = xValue
              ws.Cells(xRow, 2).Value = WorksheetFunction.Norm_Dist(xValue, xMean, xStdev, False)
              xRow = xRow + 1
          Next
      
          Set chartObj = ws.ChartObjects.Add(Left:=300, Width:=500, Top:=10, Height:=300)
          With chartObj.Chart
              .ChartType = xlXYScatterSmooth
              .SetSourceData Source:=ws.Range("A1:B" & xRow - 1)
              .HasTitle = True
              .ChartTitle.Text = "Bell Curve"
              .Axes(xlCategory).HasTitle = True
              .Axes(xlCategory).AxisTitle.Text = "X"
              .Axes(xlValue).HasTitle = True
              .Axes(xlValue).AxisTitle.Text = "Probability Density"
          End With
      
          ws.Activate
      End Sub

   3. اضغط F5 لتشغيل الماكرو.
3. أدخل القيم المطلوبة عند المطالبة
   سيطلب الماكرو ما يلي:
   1. قيمة المتوسط: حدد الخلية التي تحتوي على قيمة المتوسط التي قمت بحسابها. يمكنك إدخال القيمة يدويًا إذا كنت تتذكرها.
   2. الانحراف المعياري: حدد الخلية التي تحتوي على الانحراف المعياري.
   3. بداية النطاق: حدد الخلية التي تحتوي على بداية النطاق.
   4. نهاية النطاق: حدد الخلية التي تحتوي على نهاية النطاق
   5. الخطوة: أدخل 1 أو 0.1. أو حدد الخلية التي تحتوي على قيمة الخطوة.

بمجرد الانتهاء، سيتم إنشاء ورقة عمل جديدة باسم BellCurve.

• العمود A يحتوي على قيم محور X (نطاق البيانات).
• العمود B يحتوي على قيم كثافة الاحتمال المحسوبة باستخدام NORM.DIST().
• سيتم إدراج مخطط مبعثر ناعم (منحنى الجرس) مباشرة في ورقة العمل.

  

نصيحة: إذا حدث خطأ، أعد التحقق من مدخلات المعلمات وتأكد من أن لديك أذونات لإضافة أوراق العمل والمخططات. احفظ دائمًا عملك قبل تشغيل نصوص VBA لأن الماكرو قد لا يمكن التراجع عنه.