نصائح Excel: حساب/جمع الخلايا حسب اللون (لون الخلفية، لون الخط، التنسيق الشرطي)

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

حساب وجمع الخلايا بناءً على لون الخلفية

• باستخدام دالة معرفة من قبل المستخدم
• باستخدام ميزة قوية – Kutools لـ Excel
• باستخدام الفلتر ودالة SUBTOTAL

حساب وجمع الخلايا بناءً على لون الخط

• باستخدام دالة معرفة من قبل المستخدم
• باستخدام ميزة سهلة – Kutools لـ Excel

حساب وجمع الخلايا بناءً على لون التنسيق الشرطي

• باستخدام كود VBA
• باستخدام ميزة ذكية – Kutools لـ Excel

فيديو: حساب وجمع الخلايا بناءً على اللون

حساب وجمع الخلايا بناءً على لون الخلفية

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

حساب وجمع الخلايا حسب لون الخلفية باستخدام دالة معرفة من قبل المستخدم

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

الخطوة 1: افتح محرر وحدة VBA وقم بنسخ الكود

1. اضغط على Alt + F11 لفتح نافذة Microsoft Visual Basic for Applications.
2. في النافذة المفتوحة، انقر فوق إدراج > وحدة لإنشاء وحدة فارغة جديدة.
3. ثم قم بنسخ ولصق الكود أدناه في الوحدة الفارغة.
   كود VBA: حساب وجمع الخلايا بناءً على لون الخلفية

   Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean = False) As Variant
   'Updateby Extendoffice
       Dim rCell As Range
       Dim lCol As Long
       Dim vResult As Double
       lCol = rColor.Interior.ColorIndex
       vResult = 0
       If SUM Then
           For Each rCell In rRange
               If rCell.Interior.ColorIndex = lCol Then
                   vResult = vResult + rCell.Value
               End If
           Next rCell
       Else
           For Each rCell In rRange
               If rCell.Interior.ColorIndex = lCol Then
                   vResult = vResult + 1
               End If
           Next rCell
       End If
       ColorFunction = vResult
   End Function
   

الخطوة 2: إنشاء صيغ لحساب وجمع الخلايا حسب لون الخلفية

بعد لصق الكود أعلاه، أغلق نافذة الوحدة، ثم قم بتطبيق الصيغ التالية:

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

  =colorfunction(G2,$B$2:$E$12,FALSE) 

  ملاحظة: في هذه الصيغة، G2 هي الخلية المرجعية ذات لون الخلفية المحدد الذي تريد مطابقته؛ $B$2:$E$12 هو النطاق الذي تريد فيه حساب عدد الخلايا بلون G2؛ يتم استخدام FALSE لحساب الخلايا ذات اللون المطابق.
  
• جمع الخلايا بناءً على لون خلفية محدد:
  انسخ أو اكتب الصيغة المقدمة أدناه في الخلية المرغوبة للحصول على النتيجة. ثم اسحب مقبض التعبئة لأسفل للحصول على نتائج أخرى. انظر لقطة الشاشة:
  

  =colorfunction(G2,$B$2:$E$12,TRUE)  

  ملاحظة: في هذه الصيغة، G2 هي الخلية المرجعية ذات لون الخلفية المحدد الذي تريد مطابقته؛ $B$2:$E$12 هو النطاق الذي تريد فيه حساب عدد الخلايا بلون G2؛ يتم استخدام TRUE لجمع الخلايا ذات اللون المطابق.
  

حساب وجمع الخلايا حسب لون الخلفية باستخدام ميزة قوية

بالنسبة لأولئك الذين لا يعرفون البرمجة، قد يبدو VBA معقدًا للغاية. هنا، سنقدم أداة قوية - Kutools لـ Excel، ميزة Count by Color الخاصة بها تتيح لك بسهولة حساب (عد، جمع، متوسط، إلخ.) بناءً على لون الخلفية بنقرات قليلة فقط. المثير للإعجاب أن ميزة Count by Color تتجاوز مجرد ألوان الخلفية – حيث يمكنها أيضًا التمييز والحساب بناءً على ألوان الخطوط والتنسيق الشرطي.

بعد تنزيل وتثبيت Kutools لـ Excel، اختر أولاً نطاق البيانات الذي تريد حساب أو جمع الخلايا بناءً على لون خلفية معين. بعد ذلك، انتقل إلى Kutools Plus واختر Count by Color.

في مربع الحوار Count by Color، يرجى تحديد العمليات:

1. اختر التنسيق القياسي من قائمة منسدلة طريقة اللون؛
2. حدد الخلفية من قائمة نوع العد المنسدلة، ويمكنك معاينة النتائج الإحصائية لكل لون خلفية في مربع الحوار؛
3. أخيرًا، انقر فوق إنشاء تقرير لتصدير النتائج المحسوبة إلى مصنف جديد.

النتيجة:

الآن ستحصل على مصنف جديد بالإحصائيات. انظر لقطة الشاشة:

حساب وجمع الخلايا حسب لون الخلفية باستخدام الفلتر ودالة SUBTOTAL

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

الخطوة 1: تطبيق دالة SUBTOTAL

اختر خلايا فارغة لإدخال دالة SUBTOTAL.

• لحساب جميع الخلايا التي لها نفس لون الخلفية، يرجى إدخال الصيغة:

  =SUBTOTAL(102, F2:F16)

• لجمع جميع الخلايا التي لها نفس لون الخلفية، يرجى إدخال الصيغة:

  =SUBTOTAL(109, F2:F16)

• ملاحظة: في الصيغ أعلاه، 102 يمثل حساب القيم الرقمية في قائمة مصفاة بينما يستثني الخلايا المخفية؛ 109 يمثل جمع القيم في قائمة مصفاة مستثنيًا الخلايا المخفية؛ F2:F16 هو النطاق الذي سيتم عليه حساب العدد أو الجمع.
• 

الخطوة 2: فلترة الخلايا بناءً على لون معين

1. اختر رأس الجدول، وانقر بيانات > فلتر. انظر لقطة الشاشة:
   
2. انقر على أيقونة فلتر الأيقونة  في خلية الرأس لعمود المبلغ وانقر فلترة حسب اللون واللون المحدد الذي ستقوم بالحساب بناءً عليه بالتتابع. انظر لقطة الشاشة:
   

النتيجة:

بعد الفلترة، تقوم صيغ SUBTOTAL تلقائيًا بحساب وجمع الخلايا الملونة في عمود المبلغ . انظر لقطة الشاشة:

حساب وجمع الخلايا بناءً على لون الخط

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

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

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

الخطوة 1: افتح محرر وحدة VBA وقم بنسخ الكود

1. اضغط على Alt + F11 لفتح نافذة Microsoft Visual Basic for Applications.
2. في النافذة المفتوحة، انقر فوق إدراج > وحدة لإنشاء وحدة فارغة جديدة.
3. ثم قم بنسخ ولصق الكود أدناه في الوحدة الفارغة.
   كود VBA: حساب وجمع الخلايا بناءً على لون الخط

   Function ProcessByFontColor(pRange1 As Range, pRange2 As Range, FunctionType As String) As Double
   'Updateby Extendoffice
       Application.Volatile
       Dim rng As Range
       Dim xTotal As Double
       Dim xCount As Double
       xTotal = 0
       xCount = 0
       For Each rng In pRange1
           If rng.Font.Color = pRange2.Font.Color Then
               If UCase(FunctionType) = "SUM" Then
                   xTotal = xTotal + rng.Value
               ElseIf UCase(FunctionType) = "COUNT" Then
                   xCount = xCount + 1
               End If
           End If
       Next
       If UCase(FunctionType) = "SUM" Then
           ProcessByFontColor = xTotal
       ElseIf UCase(FunctionType) = "COUNT" Then
           ProcessByFontColor = xCount
       Else
           ProcessByFontColor = CVErr(xlErrValue)
       End If
   End Function
   

الخطوة 2: إنشاء صيغ لحساب وجمع الخلايا حسب لون الخط

بعد لصق الكود أعلاه، أغلق نافذة الوحدة، ثم قم بتطبيق الصيغ التالية:

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

  =ProcessByFontColor($B$2:$E$12,G2, "COUNT")

  ملاحظة: في هذه الصيغة، G2 هي الخلية المرجعية ذات لون الخط المحدد الذي تريد مطابقته؛ $B$2:$E$12 هو النطاق الذي تريد فيه حساب عدد الخلايا بلون G2.
  
• جمع الخلايا بناءً على لون خط محدد:
  انسخ أو اكتب الصيغة المقدمة أدناه في الخلية المرغوبة للحصول على النتيجة. ثم اسحب مقبض التعبئة لأسفل للحصول على نتائج أخرى. انظر لقطة الشاشة:
  

  =ProcessByFontColor($B$2:$E$12,G2, "SUM")  

  ملاحظة: في هذه الصيغة، G2 هي الخلية المرجعية ذات لون الخط المحدد الذي تريد مطابقته؛ $B$2:$E$12 هو النطاق الذي تريد فيه حساب عدد الخلايا بلون G2.
  

حساب وجمع الخلايا بناءً على لون الخط باستخدام ميزة سهلة

هل ترغب في حساب أو جمع قيم الخلايا بسهولة في Excel بناءً على لون الخط؟ اكتشف ميزة Count by Color في Kutools لـ Excel! باستخدام هذه الأداة الذكية، يصبح حساب وجمع الخلايا حسب لون الخط المحدد أمرًا سهلاً. اكتشف كيف يمكن أن تغير Kutools تجربتك مع Excel.

بعد تنزيل وتثبيت Kutools لـ Excel، اختر أولاً نطاق البيانات الذي تريد حساب أو جمع الخلايا بناءً على لون خط معين. بعد ذلك، انقر فوق Kutools Plus > Count by Color لفتح مربع الحوار Count by Color.

في مربع الحوار Count by Color، يرجى تحديد العمليات:

1. اختر التنسيق القياسي من قائمة منسدلة طريقة اللون؛
2. حدد الخط من قائمة نوع العد المنسدلة، ويمكنك معاينة النتائج الإحصائية لكل لون خط في مربع الحوار؛
3. أخيرًا، انقر فوق إنشاء تقرير لتصدير النتائج المحسوبة إلى مصنف جديد.

النتيجة:

الآن، لديك مصنف جديد يعرض الإحصائيات التفصيلية بناءً على لون الخط. انظر لقطة الشاشة:

حساب وجمع الخلايا بناءً على لون التنسيق الشرطي

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

حساب وجمع الخلايا المنسقة شرطيًا باستخدام كود VBA

حساب وجمع الخلايا المنسقة شرطيًا في Excel ليس أمرًا مباشرًا باستخدام الوظائف المدمجة. ومع ذلك، يمكنك إنجاز هذه المهمة باستخدام كود VBA. دعنا نراجع كيفية استخدام VBA لهذا الغرض:

الخطوة 1: افتح محرر وحدة VBA وقم بنسخ الكود

1. اضغط على Alt + F11 لفتح نافذة Microsoft Visual Basic for Applications.
2. في النافذة المفتوحة، انقر فوق إدراج > وحدة لإنشاء وحدة فارغة جديدة.
3. ثم قم بنسخ ولصق الكود أدناه في الوحدة الفارغة.
   كود VBA: حساب وجمع الخلايا بناءً على لون التنسيق الشرطي

   Sub SumCountByConditionalFormat()
   'Updateby Extendoffice
       Dim sampleColor As Range
       Dim selectedRange As Range
       Dim cell As Range
       Dim countByColor As Long
       Dim sumByColor As Double
       Dim refColor As Long
       Set selectedRange = Application.InputBox("Select a range to evaluate:", _
                                                "Kutools for Excel", _
                                                Type:=8)
       If selectedRange Is Nothing Then Exit Sub
       Set sampleColor = Application.InputBox("Select a conditional formatting color:", _
                                              "Kutools for Excel", _
                                              Type:=8)
       If Not sampleColor Is Nothing Then
           refColor = sampleColor.Cells(1, 1).DisplayFormat.Interior.color
           For Each cell In selectedRange
               If cell.DisplayFormat.Interior.color = refColor Then
                   countByColor = countByColor + 1
                   sumByColor = sumByColor + cell.Value
               End If
           Next cell
           MsgBox "Count: " & countByColor & vbCrLf & _
                  "Sum: " & sumByColor, _
                  vbInformation, "Results based on Conditional Format Color"
       End If
   End Sub
   

الخطوة 2: تنفيذ هذا الكود VBA

1. بعد لصق الكود، اضغط F5 لتشغيل هذا الكود، ستظهر مربع مطالبة، يرجى اختيار نطاق البيانات الذي تريد حساب وجمع الخلايا بناءً على التنسيق الشرطي. ثم انقر موافق, انظر لقطة الشاشة:
   
2. في مربع مطالبة آخر، اختر لون التنسيق الشرطي المحدد الذي تريد حسابه وجمعه، وانقر موافق الزر، انظر لقطة الشاشة:
   

النتيجة:

الآن، سيتم عرض النتيجة، والتي تشمل كل من العدد والمجموع للخلايا باللون المحدد للتنسيق الشرطي، في المربع المنبثق. انظر لقطة الشاشة:

حساب وجمع الخلايا المنسقة شرطيًا باستخدام ميزة ذكية

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

بعد تنزيل وتثبيت Kutools لـ Excel، اختر أولاً نطاق البيانات الذي تريد حساب أو جمع الخلايا بناءً على لون التنسيق الشرطي المحدد. بعد ذلك، انقر فوق Kutools Plus > Count by Color لفتح مربع الحوار Count by Color.

في مربع الحوار Count by Color، يرجى تحديد العمليات:

1. اختر التنسيق الشرطي من قائمة منسدلة طريقة اللون؛
2. حدد الخلفية من قائمة نوع العد المنسدلة، ويمكنك معاينة النتائج الإحصائية لكل لون تنسيق شرطي في مربع الحوار؛
3. أخيرًا، انقر فوق إنشاء تقرير لتصدير النتائج المحسوبة إلى مصنف جديد.

النتيجة:

الآن، لديك مصنف جديد يعرض الإحصائيات التفصيلية بناءً على لون التنسيق الشرطي. انظر لقطة الشاشة:

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