توليد رقم عشوائي بمتوسط وانحراف معياري معينين في 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 دالة Excel NormInv لتوليد أرقام موزعة توزيعًا طبيعيًا. تأكد دائمًا مما إذا كانت نسختك تدعم هذه الدالة؛ ففي إصدارات Excel القديمة، قد تحتاج إلى استخدام الدالةNORMINV.
• يتم ضبط البذرة العشوائية باستخدامRandomize للحصول على نتائج متنوعة في كل تشغيل.
• إذا كنت ترغب في الحصول على نتائج قابلة للتكرار، فقم بإلغاء تعليق السطرRandomize أو حذفه.
• سيقوم الماكرو باستبدال أي بيانات موجودة مسبقًا في منطقة القائمة المحددة، لذا تأكد من اختيار منطقة فارغة عند الحاجة.
• إذا أدخلت قيمًا غير مناسبة (مثل انحراف معياري سالب أو صفر)، فسيتوقف الماكرو عن العمل ويعرض رسالة تحذير.

---

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

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