نصائح Excel: عدد/مجموع الخلايا حسب اللون (الخلفية، الخط، التنسيق الشرطي)
في المهام اليومية، تعد علامات الألوان طريقة شائعة لتمييز البيانات المهمة وإبرازها بسرعة. ولكن، كيف نحسب أو نجمع بيانات الخلية بناءً على لون معين (لون التعبئة، لون الخط، التنسيق الشرطي)؟ افتراضيًا، لا يقدم Excel ميزة مباشرة للعد أو الجمع حسب اللون. ومع ذلك، مع بعض الحيل والأساليب غير المباشرة، لا يزال بإمكاننا تحقيق ذلك. سوف تستكشف هذه المقالة كيفية حساب أو جمع البيانات حسب اللون.
عد الخلايا وجمعها بناءً على لون الخلفية
- باستخدام الوظيفة المحددة من قبل المستخدم
- باستخدام ميزة قوية – Kutools for Excel
- باستخدام عامل التصفية والمجموع الفرعي
عد الخلايا وجمعها بناءً على لون الخلفية
على سبيل المثال، إذا كان لديك نطاق من البيانات حيث يتم ملء القيم بألوان خلفية مختلفة كما هو موضح في لقطة الشاشة أدناه. لحساب عدد الخلايا أو جمعها بناءً على لون معين، لا يقدم Excel ميزة مباشرة لحساب الخلايا أو جمعها بناءً على لون خلفيتها. ومع ذلك، مع القليل من البراعة وبعض التقنيات المفيدة، يمكنك إنجاز هذه المهمة. دعنا نستكشف بعض الطرق المفيدة في هذا القسم.
عد الخلايا وجمعها حسب لون الخلفية باستخدام وظيفة يحددها المستخدم
سنوضح لك هنا كيفية إنشاء واستخدام هذه الوظيفة المحددة من قبل المستخدم لحل هذه المهمة في Excel. يرجى القيام بالخطوات التالية:
الخطوة 1: افتح محرر وحدة VBA وانسخ الكود
- صحافة ALT + F11 مفاتيح لفتح ميكروسوفت فيسوال باسيك للتطبيقات نافذة.
- في النافذة المفتوحة، انقر فوق إدراج > وحدة لإنشاء وحدة فارغة جديدة.
- ثم انسخ والصق الكود أدناه في الوحدة الفارغة.
رمز 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أولاً، حدد نطاق البيانات الذي تريد حسابه أو جمع الخلايا بناءً على لون خلفية محدد. بعد ذلك، انتقل إلى كوتولس بلس واختر عد حسب اللون.
في مجلة عد حسب اللون في مربع الحوار، يرجى تحديد العمليات:
- أختار التنسيق القياسي من طريقة اللون قائمة منسدلة؛
- تحديد خلفيّة من نوع العد القائمة المنسدلة، ويمكنك معاينة النتائج الإحصائية لكل لون خلفية في مربع الحوار؛
- أخيرًا ، انقر فوق انشاء تقرير لتصدير النتائج المحسوبة إلى مصنف جديد.
النتيجة:
الآن سوف تحصل على كتاب عمل جديد بالإحصائيات. انظر لقطة الشاشة:
- • عد حسب اللون تدعم الميزة أيضًا حساب الخلايا وجمعها استنادًا إلى لون الخط القياسي أو لون الخلفية أو الخط من التنسيق الشرطي، ومجموعة من ألوان التعبئة والتنسيق الشرطي.
- مهتم بهذه الميزة، من فضلك انقر للتنزيل للحصول على نسخة تجريبية مجانية لمدة 30 يومًا.
عد الخلايا وجمعها حسب لون الخلفية باستخدام وظيفة Filter وSUBTOTAL
لنفترض أن لدينا جدول مبيعات الفاكهة كما هو موضح أدناه في لقطة الشاشة، وسوف نقوم بعد أو جمع الخلايا الملونة في الكمية العمود.
الخطوة 1: تطبيق الدالة SUBTOTAL
حدد الخلايا الفارغة لإدخال الدالة SUBTOTAL.
- لحساب جميع الخلايا التي لها نفس لون الخلفية، يرجى إدخال الصيغة:
=SUBTOTAL(102, F2:F16)
- لجمع كل الخلايا التي لها نفس لون الخلفية، يرجى إدخال الصيغة؛
=SUBTOTAL(109, F2:F16)
- ملاحظات: في الصيغ المذكورة أعلاه، 102 يمثل حساب القيم الرقمية في قائمة تمت تصفيتها مع استبعاد الخلايا المخفية؛ 109 يمثل جمع القيم في قائمة تمت تصفيتها باستثناء الخلايا المخفية؛ F2: F16 هو النطاق الذي سيتم حساب العدد أو المبلغ من خلاله.
الخطوة 2: تصفية الخلايا بناءً على لون معين
- حدد رأس الجدول، ثم انقر فوق البيانات > منقي. انظر لقطة الشاشة:
- انقر على منقي رمز في الخلية الرأسية الكمية العمود ، وانقر فوق تصفية حسب اللون واللون المحدد الذي ستحسب به على التوالي. انظر لقطة الشاشة:
النتيجة:
بعد التصفية، تقوم صيغ SUBTOTAL تلقائيًا بحساب الخلايا الملونة وجمعها في الكمية عمود. انظر لقطة الشاشة:
عد الخلايا وجمعها بناءً على لون الخط
هل تريد حساب الخلايا أو جمعها بناءً على لون الخط الخاص بها في Excel؟ لنفترض أن لديك البيانات، كما في لقطة الشاشة المقدمة، مع خلايا تحتوي على نصوص باللون الأحمر والأزرق والبرتقالي والأسود. لا يجعل برنامج Excel هذا الأمر سهلاً بشكل افتراضي. ولكن لا تقلق! في هذا القسم، سنعرض لك بعض الحيل البسيطة للقيام بذلك.
عد الخلايا وجمعها بناءً على لون الخط باستخدام وظيفة محددة من قبل المستخدم
لحساب الخلايا ومجموعها بألوان خطوط محددة، قد تساعدك الوظيفة المحددة من قبل المستخدم التالية على حل هذه المهمة. يرجى القيام بالخطوات التالية:
الخطوة 1: افتح محرر وحدة VBA وانسخ الكود
- صحافة ALT + F11 مفاتيح لفتح ميكروسوفت فيسوال باسيك للتطبيقات نافذة.
- في النافذة المفتوحة، انقر فوق إدراج > وحدة لإنشاء وحدة فارغة جديدة.
- ثم انسخ والصق الكود أدناه في الوحدة الفارغة.
رمز 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أولاً، حدد نطاق البيانات الذي تريد حسابه أو جمع الخلايا بناءً على لون خط معين. ثم اضغط كوتولس بلس > عد حسب اللون لفتح عد حسب اللون صندوق المحادثة.
في مجلة عد حسب اللون في مربع الحوار، يرجى تحديد العمليات:
- أختار التنسيق القياسي من طريقة اللون قائمة منسدلة؛
- تحديد الخط من نوع العد القائمة المنسدلة، ويمكنك معاينة النتائج الإحصائية لكل لون خط في مربع الحوار؛
- أخيرًا ، انقر فوق انشاء تقرير لتصدير النتائج المحسوبة إلى مصنف جديد.
النتيجة:
الآن، لديك مصنف جديد يعرض الإحصائيات التفصيلية بناءً على لون الخط. انظر لقطة الشاشة:
عد الخلايا وجمعها بناءً على لون التنسيق الشرطي
في Excel، يمكنك عادةً استخدام التنسيق الشرطي لتطبيق لون معين على الخلايا التي تستوفي معايير معينة، مما يجعل تصور البيانات أمرًا بديهيًا. ولكن ماذا لو كنت بحاجة إلى حساب أو جمع تلك الخلايا المنسقة خصيصًا؟ على الرغم من أن Excel لا يقدم طريقة مباشرة لذلك، فإليك طرقًا للالتفاف حول هذا القيد.
عد الخلايا المنسقة بشكل مشروط وجمعها باستخدام كود VBA
لا يعد حساب الخلايا المنسقة شرطيًا وجمعها في Excel أمرًا سهلاً باستخدام الوظائف المضمنة. ومع ذلك، يمكنك إنجاز هذه المهمة باستخدام كود VBA. دعنا نتعرف على كيفية استخدام VBA لهذا:
الخطوة 1: افتح محرر وحدة VBA وانسخ الكود
- صحافة ALT + F11 مفاتيح لفتح ميكروسوفت فيسوال باسيك للتطبيقات نافذة.
- في النافذة المفتوحة، انقر فوق إدراج > وحدة لإنشاء وحدة فارغة جديدة.
- ثم انسخ والصق الكود أدناه في الوحدة الفارغة.
رمز 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 هذا
- بعد لصق الرمز ، اضغط على F5 لتشغيل هذا الرمز، سيظهر مربع مطالبة، يرجى تحديد نطاق البيانات الذي تريد حسابه وجمع الخلايا بناءً على التنسيق الشرطي. ثم اضغط OK، انظر لقطة الشاشة:
- في مربع مطالبة آخر، حدد لون التنسيق الشرطي المحدد الذي تريد حسابه وجمعه، ثم انقر فوقه OK زر ، انظر لقطة الشاشة:
النتيجة:
الآن، سيتم عرض النتيجة، التي تتضمن عدد ومجموع الخلايا ذات لون التنسيق الشرطي المحدد، في المربع المنبثق. انظر لقطة الشاشة:
عد الخلايا المنسقة بشكل مشروط وجمعها باستخدام ميزة ذكية
إذا كنت تبحث عن طرق أخرى سريعة وسهلة لحساب الخلايا المنسقة شرطيًا وجمعها، كوتولس ل إكسيل هو الحل الأمثل لك. إنه عد حسب اللون يمكن لهذه الميزة حل هذه المهمة ببضع نقرات فقط. انغمس في اكتشاف الكفاءة والدقة التي يمكن أن توفرها Kutools لسير عملك.
بعد تنزيل وتثبيت Kutools لبرنامج Excel، أولاً، حدد نطاق البيانات الذي تريد حسابه أو جمع الخلايا بناءً على لون تنسيق شرطي محدد. ثم اضغط كوتولس بلس > عد حسب اللون لفتح عد حسب اللون صندوق المحادثة.
في مجلة عد حسب اللون في مربع الحوار، يرجى تحديد العمليات:
- أختار تنسيق مشروط من طريقة اللون قائمة منسدلة؛
- تحديد خلفيّة من نوع العد القائمة المنسدلة، ويمكنك معاينة النتائج الإحصائية لكل لون تنسيق شرطي في مربع الحوار؛
- أخيرًا ، انقر فوق انشاء تقرير لتصدير النتائج المحسوبة إلى مصنف جديد.
النتيجة:
الآن، لديك مصنف جديد يعرض الإحصائيات التفصيلية بناءً على لون التنسيق الشرطي. انظر لقطة الشاشة:
مقالات ذات صلة:
- إذا كان لون الخط أحمر فقم بإرجاع نص محدد
- كيف يمكنك إرجاع نص معين إذا كان لون الخط أحمر في خلية أخرى كما هو موضح أدناه؟ في هذه المقالة ، سأقدم بعض الحيل للقيام ببعض العمليات بناءً على نص الخط الأحمر في Excel.
- تصفية البيانات بألوان متعددة
- عادةً ، في Excel ، يمكنك تصفية الصفوف بسرعة بلون واحد فقط ، ولكن هل فكرت يومًا في تصفية الصفوف بألوان متعددة في نفس الوقت؟ في هذا المقال سوف أتحدث عن حيلة سريعة لك للتعامل مع هذه المشكلة.
- أضف اللون إلى القائمة المنسدلة
- في برنامج Excel، يمكن أن يساعدك إنشاء قائمة منسدلة كثيرًا، وفي بعض الأحيان، تحتاج إلى ترميز قيم القائمة المنسدلة حسب اللون المحدد. على سبيل المثال، قمت بإنشاء قائمة منسدلة بأسماء الفاكهة، عندما أحدد Apple، أريد أن يتم تلوين الخلية باللون الأحمر تلقائيًا، وعندما أختار برتقالي، يمكن تلوين الخلية باللون البرتقالي.
- تلوين الصفوف البديلة للخلايا المدمجة
- من المفيد جدًا تنسيق صفوف بديلة بلون مختلف في بيانات كبيرة حتى يتسنى لنا مسح البيانات ضوئيًا ، ولكن في بعض الأحيان ، قد تكون هناك بعض الخلايا المدمجة في بياناتك. لتمييز الصفوف بالتناوب بلون مختلف للخلايا المدمجة كما هو موضح أدناه ، كيف يمكنك حل هذه المشكلة في Excel؟
أفضل أدوات إنتاجية المكتب
عزز مهاراتك في Excel باستخدام Kutools for Excel، واختبر كفاءة لم يسبق لها مثيل. يقدم Kutools for Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية وتوفير الوقت. انقر هنا للحصول على الميزة التي تحتاجها أكثر...
يجلب Office Tab الواجهة المبوبة إلى Office ، ويجعل عملك أسهل بكثير
- تمكين التحرير والقراءة المبوبة في Word و Excel و PowerPointوالناشر والوصول و Visio والمشروع.
- فتح وإنشاء مستندات متعددة في علامات تبويب جديدة من نفس النافذة ، بدلاً من النوافذ الجديدة.
- يزيد من إنتاجيتك بنسبة 50٪ ، ويقلل مئات النقرات بالماوس كل يوم!
جدول المحتويات
- فيديو
- عد الخلايا وجمعها بناءً على لون الخلفية
- باستخدام الوظيفة المحددة من قبل المستخدم
- باستخدام ميزة قوية – Kutools for Excel
- باستخدام عامل التصفية والمجموع الفرعي
- عد الخلايا وجمعها بناءً على لون الخط
- باستخدام الوظيفة المحددة من قبل المستخدم
- باستخدام ميزة سهلة – Kutools for Excel
- عد الخلايا وجمعها بناءً على لون التنسيق الشرطي
- باستخدام كود VBA
- باستخدام ميزة ذكية – Kutools for Excel
- مقالات ذات صلة
- أفضل أدوات إنتاجية المكتب
- التعليقات