توليد رقم عشوائي بناءً على متوسط وانحراف معياري معينين في Excel

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

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

توليد رقم عشوائي بناءً على متوسط وانحراف معياري معينين

كود VBA - توليد أرقام عشوائية بمتوسط وانحراف معياري محددين

---

توليد رقم عشوائي بناءً على متوسط وانحراف معياري معينين

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

1. أولاً، أدخل متوسطك المستهدف والانحراف المعياري في خليتين فارغتين منفصلتين. من أجل الوضوح والتنظيم، لنفترض أنك تستخدم الخلية B1 للمتوسط المطلوب والخلية B2 للانحراف المعياري المطلوب. انظر إلى لقطة الشاشة:

2. لإنشاء البيانات العشوائية الأولية، انتقل إلى الخلية B3 وأدخل الصيغة التالية:

=NORMINV(RAND(),$B$1,$B$2)

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

نصيحة: داخل الصيغة =NORMINV(RAND(),$B$1,$B$2):

• RAND() ينتج احتمالاً عشوائيًا مختلفًا بين 0 و 1 في كل مرة يتم فيها إعادة حساب ورقة العمل.
• $B$1 يشير إلى قيمة المتوسط التي قمت بتحديدها.
• $B$2 يشير إلى الانحراف المعياري المطلوب.

بالنسبة للإصدارات الحديثة من Excel (2010 وما بعده)، يُفضل استخدام =NORM.INV(RAND(),$B$1,$B$2)، وهي تعمل بنفس الطريقة ولكنها تعكس تحديث أسماء الدوال.

3. للتحقق من أن الأرقام التي تم توليدها تتطابق إحصائيًا مع المتوسط والانحراف المعياري المطلوبين، استخدم الصيغ التالية لحساب القيم الفعلية للعينة التي تم إنشاؤها. في الخلية D1، احسب متوسط العينة باستخدام:

=AVERAGE(B3:B16)

في D2، احسب الانحراف المعياري للعينة باستخدام:

=STDEV.P(B3:B16)

نصيحة:

• B3:B16 هو مجرد نطاق مثال. قم بتعديل النطاق حسب عدد القيم العشوائية التي قمت بإنشائها في الخطوة 2.
• كلما زاد حجم العينة العشوائية، كلما كانت القيم الفعلية للمتوسط والانحراف المعياري أقرب إلى القيم المحددة بسبب قانون الأعداد الكبيرة.

4. لضبط السلسلة بحيث تتطابق تمامًا مع المتوسط والانحراف المعياري المطلوبين، قم بتطبيع القيم العشوائية الأولية. في الخلية D3، أدخل الصيغة التالية:

=$B$1+(B3-$D$1)*$B$2/$D$2

اسحب مقبض التعبئة لأسفل خلال جميع الصفوف التي تحتوي على أرقام عشوائية. تقوم هذه الصيغة بتقييس القيم الأولية وتضبطها بدقة لتتناسب مع المتوسط والانحراف المعياري في B1 و B2.

نصيحة:

• B1 هو المتوسط المطلوب.
• B2 هو الانحراف المعياري المطلوب.
• B3 هو الرقم العشوائي الأصلي.
• D1 هو متوسط تلك القيم العشوائية الأصلية.
• D2 هو الانحراف المعياري لتلك القيم العشوائية الأصلية.

يمكنك الآن التأكد من أن المجموعة النهائية من القيم تلبي المتطلبات عن طريق إعادة حساب متوسطها وانحرافها المعياري لأغراض ضمان الجودة والتوثيق.

5. في الخلية D17، احسب متوسط مجموعة الأرقام العشوائية النهائية باستخدام الصيغة التالية:

=AVERAGE(D3:D16)

ثم في الخلية D18، احسب الانحراف المعياري باستخدام الصيغة التالية:

=STDEV.P(D3:D16)

نصيحة: D3:D16 يشير إلى نطاق الأرقام العشوائية النهائية الخاصة بك.

استكشاف الأخطاء وإصلاحها:

• إذا ظهر خطأ #VALUE!، فراجع جميع نطاقات الخلايا المشار إليها وتأكد من عدم وجود صيغ تشير إلى خلايا فارغة أو غير صالحة.
• إذا كانت الصيغة تتغير باستمرار عند كل إعادة حساب، حدد الأرقام العشوائية النهائية، انسخها، واستخدم لصق خاص > القيم لمنع المزيد من التحديثات.
• تذكر أن مولدات الأرقام العشوائية في Excel تعتمد على إعادة الحساب، لذلك يجب حفظ النتائج الثابتة عندما يكون الاتساق مهمًا.

---

كود VBA - توليد أرقام عشوائية بمتوسط وانحراف معياري محددين

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

هذا النهج مناسب لـ:

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

مقارنةً بالطرق القائمة على الصيغ، يمكن لـ VBA أيضًا السماح بإجراء تعديلات ديناميكية أو دمجها مع عمليات عمل أكثر تعقيدًا، لكن كن على دراية بأن الماكرو يجب أن تكون مفعلة في جدول البيانات وقد تحتاج إلى حفظ واضح في صيغة .xlsm "المؤتمتة".

1. على شريط Excel، انقر على أدوات المطور (إذا لم تكن مرئية، قم بتفعيلها عبر ملف > الخيارات > تخصيص الشريط)، ثم حدد Visual Basic. في نافذة Visual Basic for Applications، انقر على إدراج > وحدة، وقم بنسخ الكود التالي في نافذة الوحدة الفارغة:

Sub GenerateRandomNumbersWithMeanStd()
    Dim outputRange As Range
    Dim meanValue As Double, stdDevValue As Double
    Dim numItems As Long, i As Long
    Dim xTitleId As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set outputRange = Application.InputBox("Select the output range", xTitleId, Type:=8)
    meanValue = Application.InputBox("Enter the mean value", xTitleId, "", Type:=1)
    stdDevValue = Application.InputBox("Enter the standard deviation", xTitleId, "", Type:=1)
    
    If outputRange Is Nothing Or meanValue = 0 Or stdDevValue = 0 Then
        MsgBox "Please ensure you have specified all required parameters.", vbExclamation, "KutoolsforExcel"
        Exit Sub
    End If
    
    numItems = outputRange.Count
    Randomize
    
    For i = 1 To numItems
        outputRange.Cells(i).Value = Application.WorksheetFunction.NormInv(Rnd, meanValue, stdDevValue)
    Next i
End Sub

2. انقر فوق الزر تشغيل (أو اضغط على F5) لتشغيل الماكرو. ستظهر مربع حوار يطلب منك تحديد النطاق حيث تريد إخراج الأرقام العشوائية (على سبيل المثال، حدد A1:A100 لـ100 قيمة). بعد ذلك، سيتم سؤالك لإدخال المتوسط والانحراف المعياري المطلوبين. سيقوم الماكرو بتعبئة النطاق بالأرقام العشوائية التي تتطابق مع مواصفاتك.

نصائح واستكشاف الأخطاء وإصلاحها:

• VBA يستخدم دالة NormInv في Excel لتوليد أرقام موزعة طبيعيًا - دائمًا قم بمراجعة ما إذا كان إصدارك يدعم هذا؛ بالنسبة لنسخ Excel القديمة، قد تكون الدالة بحاجة لأن تكون NORMINV.
• يتم تعيين البذور العشوائية باستخدام Randomize للحصول على نتائج مختلفة في كل تشغيل.
• إذا كنت تريد نتائج قابلة للتكرار، قم بتعليق أو إزالة سطر Randomize.
• سيقوم الماكرو بتجاوز أي بيانات موجودة في النطاق المحدد للإخراج، لذا تأكد من اختيار منطقة فارغة إذا لزم الأمر.
• إذا قمت بإدخال قيم غير مناسبة (على سبيل المثال، انحراف معياري سالب أو صفر)، لن يكمل الماكرو وستظهر رسالة تحذير.

---

مقالات ذات صلة:

• توليد أرقام عشوائية بدون تكرار في Excel
• توليد أرقام عشوائية موجبة أو سالبة في Excel
• إيقاف تغيير الأرقام العشوائية في Excel
• توليد نعم أو لا عشوائي في Excel