كيفية حساب المتوسط حسب اللون في برنامج excel (مثال خطوة بخطوة)

في كثير من الأحيان قد ترغب في متوسط القيم في Excel بناءً على لونها.

على سبيل المثال، لنفترض أن لدينا مجموعة البيانات التالية ونريد حساب متوسط قيمة الخلية بناءً على ألوان الخلية:

أسهل طريقة للقيام بذلك هي كتابة كود VBA في Excel.

قد يبدو هذا أمرًا مخيفًا إذا لم تكن على دراية بـ VBA، لكن العملية بسيطة والمثال التالي خطوة بخطوة يوضح بالضبط كيفية القيام بذلك.

الخطوة 1: أدخل البيانات

أولاً، قم بإدخال قيم البيانات في برنامج Excel:

الخطوة 2: إظهار علامة تبويب المطور في Excel

بعد ذلك، نحتاج إلى التأكد من أن علامة التبويب “المطور” مرئية في الشريط العلوي لبرنامج Excel.

للقيام بذلك، انقر فوق علامة التبويب ملف ، ثم خيارات ، ثم تخصيص الشريط .

ضمن القسم الذي يحمل عنوان علامات التبويب الرئيسية ، حدد المربع الموجود بجوار المطور ، ثم انقر فوق موافق :

الخطوة 3: إنشاء ماكرو باستخدام VBA

بعد ذلك، انقر فوق علامة التبويب “المطور” الموجودة على الشريط العلوي، ثم انقر فوق رمز Visual Basic :

بعد ذلك، انقر فوق علامة التبويب “إدراج” ، ثم انقر فوق “الوحدة النمطية” من القائمة المنسدلة:

بعد ذلك، الصق الكود التالي في محرر الكود الخاص بالوحدة:

 Function AvgCellsByColor(CellRange As Range, CellColor As Range)

Dim CellColorValue As Integer
Dim RunningAvg As Long
Dim RunningSum As Long
Dim RunningCount As Long

CellColorValue = CellColor.Interior.ColorIndex
Set i = CellRange

For Each i In CellRange
    If i.Interior.ColorIndex = CellColorValue Then
    RunningSum = RunningSum + i.Value
    RunningCount = RunningCount + 1
End If
Next i

AvgCellsByColor = RunningSum / RunningCount

End Function

توضح لقطة الشاشة التالية كيفية القيام بذلك:

ثم أغلق محرر VB.

الخطوة 4: استخدم الماكرو للمتوسط حسب اللون

أخيرًا، يمكننا استخدام الماكرو الذي أنشأناه لحساب متوسط قيم الخلايا بناءً على اللون.

أولاً، املأ الخلايا C2:C4 بالألوان التي تريد حساب القيمة المتوسطة لها.

ثم اكتب الصيغة التالية في الخلية D2 :

 =AvgCellsByColor( $A$2:$A$11 , C2 )

اسحب هذه الصيغة واملأها في كل خلية متبقية في العمود D وستقوم الصيغة تلقائيًا بحساب متوسط القيمة لكل خلية ذات ألوان خلفية محددة:

على سبيل المثال، يمكننا أن نرى أن متوسط قيمة الخلايا ذات الخلفية الخضراء الفاتحة هو 17.67 .

يمكننا تأكيد ذلك عن طريق حساب متوسط كل خلية يدويًا بخلفية خضراء فاتحة:

متوسط الخلايا على خلفية خضراء فاتحة: (20 + 13 + 20) / 3 = 17.67 .

وهذا يتوافق مع القيمة المحسوبة بواسطة صيغتنا.

مصادر إضافية

تشرح البرامج التعليمية التالية كيفية تنفيذ العمليات الشائعة الأخرى في Excel:

كيفية حساب المتوسط حسب المجموعة في إكسيل
كيفية حساب المتوسط الشهري في إكسيل
كيفية حساب المتوسط حسب يوم الأسبوع في إكسل