انتقل إلى المحتوى الرئيسي

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

في المهام اليومية، تعد علامات الألوان طريقة شائعة لتمييز البيانات المهمة وإبرازها بسرعة. ولكن، كيف نحسب أو نجمع بيانات الخلية بناءً على لون معين (لون التعبئة، لون الخط، التنسيق الشرطي)؟ افتراضيًا، لا يقدم 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 فيه؛ خاطئة يستخدم لحساب الخلايا ذات اللون المطابق.
  • مجموع الخلايا بناءً على لون الخلفية المحدد:
    انسخ أو اكتب الصيغة الواردة أدناه في الخلية المطلوبة للحصول على النتيجة. ثم اسحب مقبض التعبئة للأسفل للحصول على نتائج أخرى. انظر لقطة الشاشة:
    =colorfunction(G2,$B$2:$E$12,TRUE)  
    ملاحظات: في هذه الصيغة ، G2 هي الخلية المرجعية ذات لون الخلفية المحدد الذي تريد مطابقته؛ $B$2:$E$12 هو النطاق الذي تريد حساب عدد خلايا لون G2 فيه؛ الحقيقة يستخدم لجمع الخلايا ذات اللون المطابق.

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

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

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

في مجلة عد حسب اللون في مربع الحوار، يرجى تحديد العمليات:

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

النتيجة:

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

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

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

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

في مجلة عد حسب اللون في مربع الحوار، يرجى تحديد العمليات:

  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 لتشغيل هذا الرمز، سيظهر مربع مطالبة، يرجى تحديد نطاق البيانات الذي تريد حسابه وجمع الخلايا بناءً على التنسيق الشرطي. ثم اضغط OK، انظر لقطة الشاشة:
  2. في مربع مطالبة آخر، حدد لون التنسيق الشرطي المحدد الذي تريد حسابه وجمعه، ثم انقر فوقه OK زر ، انظر لقطة الشاشة:

النتيجة:

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


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

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

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

في مجلة عد حسب اللون في مربع الحوار، يرجى تحديد العمليات:

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

النتيجة:

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


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

  • إذا كان لون الخط أحمر فقم بإرجاع نص محدد
  • كيف يمكنك إرجاع نص معين إذا كان لون الخط أحمر في خلية أخرى كما هو موضح أدناه؟ في هذه المقالة ، سأقدم بعض الحيل للقيام ببعض العمليات بناءً على نص الخط الأحمر في Excel.
  • تصفية البيانات بألوان متعددة
  • عادةً ، في Excel ، يمكنك تصفية الصفوف بسرعة بلون واحد فقط ، ولكن هل فكرت يومًا في تصفية الصفوف بألوان متعددة في نفس الوقت؟ في هذا المقال سوف أتحدث عن حيلة سريعة لك للتعامل مع هذه المشكلة.
  • أضف اللون إلى القائمة المنسدلة
  • في برنامج Excel، يمكن أن يساعدك إنشاء قائمة منسدلة كثيرًا، وفي بعض الأحيان، تحتاج إلى ترميز قيم القائمة المنسدلة حسب اللون المحدد. على سبيل المثال، قمت بإنشاء قائمة منسدلة بأسماء الفاكهة، عندما أحدد Apple، أريد أن يتم تلوين الخلية باللون الأحمر تلقائيًا، وعندما أختار برتقالي، يمكن تلوين الخلية باللون البرتقالي.
  • تلوين الصفوف البديلة للخلايا المدمجة
  • من المفيد جدًا تنسيق صفوف بديلة بلون مختلف في بيانات كبيرة حتى يتسنى لنا مسح البيانات ضوئيًا ، ولكن في بعض الأحيان ، قد تكون هناك بعض الخلايا المدمجة في بياناتك. لتمييز الصفوف بالتناوب بلون مختلف للخلايا المدمجة كما هو موضح أدناه ، كيف يمكنك حل هذه المشكلة في Excel؟
Comments (237)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
嗨~版主好,我用了VBA 模塊,但是完全沒有動靜,沒有出現顏色儲存格的統計數量,Count欄一片空白~~請問是為什麼呢?跟office版本有關嗎?謝謝
This comment was minimized by the moderator on the site
嗨,MINA,
文章中的VBA代碼,微軟office版本基本上都可以適用,我這代碼可以正常使用。 如果你那邊還用不了,可以上傳你的文件,我們可以幫忙看看哪裡的問題,謝謝!
This comment was minimized by the moderator on the site
I am using =IF(D272>F272,D272-F272,if(F272>D272,F272-D272,"")) formula for subtraction, and I want it will coloured also??
This comment was minimized by the moderator on the site
Hi namrata,
Do you want to fill color for the result of your formula?
So, if the result is D272-F272, you want it, say, red; If the result is F272-D272, you want it, say, green; If blank, blank?
Amanda
This comment was minimized by the moderator on the site
I ran into problems when trying to run the function. Macro errors telling me: No RETURN() or HALT() function found on macro sheet. perhaps somebody could assist here. ThanksPaul
This comment was minimized by the moderator on the site
Hi, How to make a diagram based on the colors in the table? For example, I want to count all the red, green and yellow colors in the cells in a table and make a diagram. How to do this? Please
This comment was minimized by the moderator on the site
Is there a way to count different color backgrounds from conditional formatting? The current code as of 7/14/2020 counts them all as default yellow or not at all.
This comment was minimized by the moderator on the site
Hi Dusty,
You can try the Count by Color feature of Kutools for Excel. This feature will help you quickly calculate (count, sum, average, etc.) cells by cell background color or font color, no matter they are formatted by conditional formatting or solidly format.
This comment was minimized by the moderator on the site
Did anyone find a solution to auto-refresh? I have to manually refresh for it to update. Otherwise, it works great!
This comment was minimized by the moderator on the site
Hi Dennis,
By default, formulas are calculated in Excel until you are turning off the Automatic Formula Calculation. You can enable it by clicking Formulas > Calculation Options > Automatic.
This comment was minimized by the moderator on the site
Anyone have tips on a max by color VBA?
This comment was minimized by the moderator on the site
Hi Natasha,VBA is good but hard to apply. But below methods may solve your work easily too.
Method 1: Use Find & Replace feature to select and statistic the color cells(1) Press Ctrl + H keys to open the Find and Replace dialog, and then enable the Find tab.
(2) In the dialog, click Options to show advanced find options.
(3) Then click Format > Choose Format From Cell, and select one of the specified color cells.
(4) Click Find All. Now all cells with the same fill color are found out and listed at the bottom of the dialog.
(5) Select one of found cells, and press Ctrl + A to select all found cells, so that these cells are selected in the worksheet.
(6) Now you can get the count, average, sum, min, max, etc. of these cells in the task bar.
Note: If a certain statistic result cannot be found one the taskbar, you can right click the task bar, and then tick the specified item to show it.

Method 2: Kutools for Excel
Kutools for Excel supports 30-day free trial. Therefore, you can download it and try its Count by color feature to solve your problem with several clicks only.
This comment was minimized by the moderator on the site
awesome fix! count by color over an entire sheet was just what i was looking for and your VBA code was tighter than others that i have looked at. Works like a charm. Thank you, and again, well done.
This comment was minimized by the moderator on the site
I copied and paste but calculation result is "0" why?? I am using Office 2016.
This comment was minimized by the moderator on the site
I try the same you this command =COUNTBYCELLCOLOR is counting only fill color but is not count by condition formating. Please help to improve code thx.
This comment was minimized by the moderator on the site
I've copied and pasted as stated and have used this formula for over a year but recently saved the workbook to a new name and now the function doesn't work! I can't figure out what the deal is and I'm losing hair and sleep over it! Loading the original workbook, the formula works like a charm but going back to the new one, it doesn't! I've loaded VBA and tried re-creating the function but it doesn't work. Using Office 2019 - any help appreciated.
This comment was minimized by the moderator on the site
#NAME clearly indicates some keyword is not used in 2019, say, Interior.ColorIndex. Try to figure out changing the code from minimum lines to the full by adding one by one or search for the keywords in Excel/VBA in 2019
This comment was minimized by the moderator on the site
I should probably have also noted that I get a #NAME? error in the cell in which I try to use the function.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations