كيفية حساب المتوسط المرجح في Excel؟

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

لحساب المتوسط المرجح للأسعار، استخدم مزيجًا من دالتي SUMPRODUCT وSUM على النحو التالي:

حدد خلية فارغة، مثل F2، وأدخل الصيغة التالية:

=SUMPRODUCT(C2:C18,D2:D18)/SUM(C2:C18)

ثم اضغط على مفتاح Enter للحصول على النتيجة.

ملاحظة: في هذه الصيغة، C2:C18 تشير إلى العمود Weight (الوزن)، وD2:D18 تشير إلى العمود Price (السعر). قم بتعديل هذه النطاقات حسب الحاجة لتنسيق بياناتك الخاص. تقوم دالة SUMPRODUCT بضرب كل وزن في السعر المقابل له وتجمع النتائج، بينما تقوم دالة SUM بجمع الأوزان - مما ينتج عنه المتوسط المرجح الصحيح. تأكد من استخدام نطاقات ذات طول متساوٍ وتأكد من عدم وجود خلايا غير متطابقة أو فارغة في بياناتك، حيث يمكن أن يؤدي ذلك إلى أخطاء في الحساب.

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

إذا واجهت خطأ مثل #VALUE!، تحقق مرتين من أن كل خلية مرجعية تحتوي على قيمة رقمية وأن النطاقات متسقة. أيضًا، تجنب تضمين أي صف رؤوس في نطاق الحساب الخاص بك لضمان الحصول على نتائج دقيقة. عند العمل مع مجموعات بيانات أكبر، ضع في اعتبارك استخدام النطاقات المسماة لتحقيق الوضوح وسهولة الصيانة.

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

للقيام بذلك، حدد خلية فارغة، مثل F8، وأدخل الصيغة التالية:

=SUMPRODUCT((B2:B18="Apple")*C2:C18*D2:D18)/SUMIF(B2:B18,"Apple",C2:C18)

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

ملاحظة: هنا، B2:B18 هو عمود الفاكهة، C2:C18 هو الوزن، وD2:D18 هو السعر. استبدل "Apple" بعنصر آخر حسب الحاجة. تعمل هذه الطريقة بشكل جيد لتصفية البيانات بناءً على شرط واحد؛ إذا كنت بحاجة إلى التصفية بناءً على معايير متعددة (مثل نوع الفاكهة والمورد)، فقد تكون هناك حاجة إلى عمود مساعد أو صيغة أكثر تقدمًا.

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

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

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

إليك كيفية إنشاء واستخدام ماكرو VBA للمتوسط المرجح:

1. انقر فوق Developer > Visual Basic (أو اضغط على Alt + F11) لفتح نافذة محرر Microsoft Visual Basic for Applications. بعد ذلك، انقر فوق Insert > Module، ثم انسخ الكود التالي في نافذة الوحدة الجديدة:

Sub WeightedAverageVBA()
    Dim rngCriteria As Range
    Dim rngWeight As Range
    Dim rngValue As Range
    Dim criteriaStr As String
    Dim totalWeighted As Double
    Dim totalWeight As Double
    Dim i As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rngCriteria = Application.InputBox("Select the range for criteria (optional, press Cancel to skip):", xTitleId, Type:=8)
    criteriaStr = Application.InputBox("Enter criteria for filtering (leave blank for all):", xTitleId, Type:=2)
    Set rngWeight = Application.InputBox("Select the Weight (numeric) range:", xTitleId, Type:=8)
    Set rngValue = Application.InputBox("Select the Value (e.g. Price) range:", xTitleId, Type:=8)
    
    totalWeighted = 0
    totalWeight = 0
    
    If rngCriteria Is Nothing Or criteriaStr = "" Then
        For i = 1 To rngWeight.Cells.Count
            If IsNumeric(rngWeight.Cells(i).Value) And IsNumeric(rngValue.Cells(i).Value) Then
                totalWeighted = totalWeighted + rngWeight.Cells(i).Value * rngValue.Cells(i).Value
                totalWeight = totalWeight + rngWeight.Cells(i).Value
            End If
        Next i
    Else
        For i = 1 To rngWeight.Cells.Count
            If rngCriteria.Cells(i).Value = criteriaStr Then
                If IsNumeric(rngWeight.Cells(i).Value) And IsNumeric(rngValue.Cells(i).Value) Then
                    totalWeighted = totalWeighted + rngWeight.Cells(i).Value * rngValue.Cells(i).Value
                    totalWeight = totalWeight + rngWeight.Cells(i).Value
                End If
            End If
        Next i
    End If
    
    If totalWeight = 0 Then
        MsgBox "Weighted average cannot be calculated: total weight is zero.", vbExclamation, xTitleId
    Else
        MsgBox "Weighted average: " & totalWeighted / totalWeight, vbInformation, xTitleId
    End If
End Sub

2. اضغط F5 (أو انقر فوق الزر Run) لتنفيذ.
ستتم مطالبتك بتحديد النطاقات خطوة بخطوة (نطاق المعايير - يمكن تخطيه إذا لم يكن مطلوبًا، نطاق الوزن، ونطاق القيم). يمكنك أيضًا إدخال معايير محددة لتصفية الحساب أو تركها فارغة للنظر في جميع البيانات. يدعم الماكرو النطاقات الديناميكية للبيانات، مما يجعله عمليًا إذا كانت جدولك يتزايد أو يتغير بانتظام.

أخيرًا، ستحصل على مربع رسالة يعرض نتيجة المتوسط المرجح.

نصائح:

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

إذا واجهت مشكلات تتعلق بالأذونات أو أمان الماكرو، تأكد من تمكين الماكرو في إعدادات Excel قبل تشغيل الكود.

مدى مع التقريب في Excel

معدل التغير في Excel

حساب معدل النمو السنوي / المركب في Excel

المتوسط المتحرك في Excel

إجمالي المجموع / المتوسط في Excel