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

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

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

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

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

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

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

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

إذا واجهت خطأً مثل #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. انقرالمطوّر > Visual Basic(أو اضغط)Alt + F11) لفتح نافذة محررMicrosoft Visual Basic for Applications. بعد ذلك، انقرإدراج > وحدة نمطية، ثم الصق الكود التالي في نافذة الوحدة الجديدة:

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(أو انقر زر) التشغيل) لتنفيذه.
سيُطلب منك اختيار النطاقات خطوة بخطوة (نطاق المعيار—يمكن تخطيه إذا لم يكن مطلوبًا، نطاق الأوزان، ونطاق القيم). يمكنك أيضًا إدخال معيار محدد لتصفية الحساب الخاص بك أو تركه فارغًا لتشمل جميع البيانات. ويُدعم النطاق الديناميكي في الماكرو، مما يجعله عمليًّا إذا كان جدولك ينمو أو يتغير بانتظام.

في النهاية، ستتلقى مربع رسالة يعرض نتيجة المتوسط المرجّح.

نصائح:

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

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

متوسط النطاق مع التقريب في Excel

متوسط معدل التغيّر في Excel

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

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

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