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

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

عدّ وجمع الخلايا بناءً على لون الخلفية

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

عدّ وجمع الخلايا بناءً على لون الخط

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

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

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

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

عدّ وجمع الخلايا بناءً على لون الخلفية

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

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

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

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

1. اضغطAlt + F11 لفتح نافذةمايكروسوفت فيجوال بيسك للتطبيقات.
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 for Excel، التي تأتي مع ميزةالإحصاء حسب اللون، لتُمكّنك من إجراء عمليات الحساب (مثل العد، الجمع، المتوسط، وغيرها) بسهولة تامة بناءً على لون الخلفية، وببضع نقرات فقط! والأكثر إثارة أن ميزةالإحصاء حسب اللونلا تقتصر على لون الخلفية فحسب، بل تمتد أيضًا لتشمل لون الخط، بل وحتى التنسيقات الشرطية!

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

في مربع الحوارإحصاء حسب اللون، يُرجى تحديد العمليات:

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 لفتح نافذةمايكروسوفت فيجوال بيسك للتطبيقات.
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 استنادًا إلى لون الخط؟ اكتشف ميزةKutools لـ Excel's إحصاء حسب اللون! مع هذه الأداة الذكية، يصبح عدّ وجمع الخلايا حسب لون الخط المعيّن أمرًا في غاية السهولة. اكتشف كيف يمكن لـKutools أن يُحسّن تجربتك مع Excel.

بعدتنزيل وتثبيت Kutools لـ Excel، اختر أولًا نطاق البيانات الذي تريد عدّ خلاياه أو جمعها استنادًا إلى لون خط معيّن. بعد ذلك، انقر فوقKUTOOLS PLUS > إحصاء حسب اللونلفتح مربع الحوارإحصاء حسب اللون.

في مربع الحوارإحصاء حسب اللون، يُرجى تحديد العمليات:

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

النتيجة:

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

عد وجمع الخلايا استنادًا إلى لون استخدم تنسيق الشروط

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

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

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

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

1. اضغطAlt + F11 لفتح نافذةمايكروسوفت فيجوال بيسك للتطبيقات.
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هو الحل الأمثل لك. فميزةإحصاء حسب اللونالخاصة به تُنجز هذه المهمة بنقراتٍ قليلة فقط. اكتشف الكفاءة والدقة التي توفرها لك Kutools في سير عملك!

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

في مربع الحوارإحصاء حسب اللون، يُرجى تحديد العمليات:

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

النتيجة:

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

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