إنشاء رقم عشوائي بناءً على متوسط وانحراف معياري معين في 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