كيفية حساب أو جمع الخلايا بناءً على لون الخلية في ورقة Google؟
في العمل اليومي على الجداول، قد تواجه مواقف تحتاج فيها إلى حساب قيم الخلايا أو جمعها وفقًا للون خلفية الخلية المحدد، كما هو موضح في لقطة الشاشة أدناه. على سبيل المثال، قد ترغب في حساب أو جمع فقط تلك الخلايا التي تم تمييزها بلون معين لتحليل البيانات بسرعة حسب الفئة أو الحالة. سيتناول هذا الدليل كيفية تحقيق ذلك ليس فقط في Google Sheets، الذي يفتقر إلى دعم مدمج لهذه الحسابات القائمة على اللون، ولكن أيضًا في Microsoft Excel، الذي يقدم عدة طرق مختلفة، من الميزات المدمجة إلى الأدوات المتقدمة.
فهم كيفية التعامل مع تحليل البيانات القائم على اللون يمكن أن يجعل عملك أكثر كفاءة، خاصة عندما تُستخدم الألوان لتمييز الحالات أو الأولويات أو الفئات. سنناقش أيضًا حلولًا مختلفة، ونقارن سيناريوهات استخدامها، ونوفر نصائح عملية وأيضًا تنبيهات للأخطاء لضمان سير مهامك بسلاسة.
- حساب قيم الخلايا بناءً على لون الخلية باستخدام البرنامج النصي في ورقة Google
- جمع قيم الخلايا بناءً على لون الخلية باستخدام البرنامج النصي في ورقة Google
- حساب أو جمع قيم الخلايا حسب لون الخلية باستخدام Kutools لـ Excel في Microsoft Excel
حساب قيم الخلايا بناءً على لون الخلية باستخدام البرنامج النصي في ورقة Google
Google Sheets لا توفر خيارًا مباشرًا لحساب الخلايا بناءً على لون الخلفية. ومع ذلك، يمكنك تحقيق ذلك باستخدام برنامج Apps Script مخصص. يعمل هذا البرنامج النصي كدالة معرفة من قبل المستخدم، مما يتيح لك الرجوع إليه مثل صيغة. إليك كيفية إعداد واستخدام البرنامج النصي:
1. انقر فوق أدوات > محرر البرامج النصية للوصول إلى بيئة البرمجة. انظر لقطة الشاشة:
2. في نافذة المشروع، حدد ملف > جديد > ملف البرنامج النصي لفتح وحدة كود جديدة، كما هو موضح:
3. عند المطالبة، أدخل اسمًا لشفرتك الجديدة وقم بتأكيدها. قم بتسمية البرنامج النصي بطريقة ذات معنى لتساعد في تحديد غرضه لاحقًا.
4. انقر فوق موافق، ثم انسخ والصق الكود التالي لتحل محل أي كود نموذجي في الوحدة. تأكد من لصقه بالضبط كما هو مقدم.
function countColoredCells(countRange,colorRef) {
var activeRg = SpreadsheetApp.getActiveRange();
var activeSht = SpreadsheetApp.getActiveSheet();
var activeformula = activeRg.getFormula();
var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
var BackGround = activeSht.getRange(colorRefAddress).getBackground();
var countCells = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
countCells = countCells + 1;
return countCells;
};
5. احفظ هذا الملف النصي، عد إلى الورقة الخاصة بك واستخدم الدالة الجديدة تمامًا مثل أي صيغة أخرى في Google Sheets. أدخل: =countcoloredcells(A1:E11,A1) في خلية فارغة لحساب الخلايا في النطاق A1:E11 المتطابقة مع لون A1. اضغط على Enter للحصول على النتيجة. إذا تمت مطالبتك بالأذونات، قم بتفويض البرنامج النصي للعمل في الورقة الخاصة بك.
ملاحظة: A1:E11 هو نطاق بياناتك؛ A1 هي الخلية المرجعية للون المراد حسابه. تأكد من أن الخلايا المرجعية لها اللون نفسه وتجنب الخلايا المدمجة لأفضل موثوقية.
6. لحساب ألوان أخرى، كرر الصيغة مع الخلية المرجعية للون المختلف حسب الحاجة. إذا تغير نطاقك، قم بتعديل النطاق في الصيغة وفقًا لذلك.
إذا تلقيت خطأ أو نتيجة غير متوقعة، تحقق مرتين من أن البرنامج النصي قد تم حفظه وأنك استخدمت الخلية المرجعية للون الصحيح. تعتمد الدوال القائمة على Apps Script إعادة الحساب فقط إذا تغيرت الدالة أو معلماتها - إذا قمت لاحقًا بإعادة تلوين خلاياك، أعد إدخال الصيغة أو اضغط على Enter مرة أخرى لتحديث.
جمع قيم الخلايا بناءً على لون الخلية باستخدام البرنامج النصي في ورقة Google
يتطلب جمع قيم الخلايا بناءً على لون الخلية المحدد في Google Sheets نهجًا مشابهًا باستخدام برنامج Apps Script. يكون هذا مفيدًا بشكل خاص في الجداول المالية، سجلات الحالة، أو أي سيناريو حيث تمثل الألوان فئات تحتوي على بيانات رقمية.
1. في Google Sheets، افتح محرر البرامج النصية عبر أدوات > محرر البرامج النصية. عند التواجد في نافذة المشروع، حدد ملف > جديد > ملف البرنامج النصي لإضافة وحدة كود جديدة. أدخل اسمًا فريدًا في المطالبة لمساعدتك في تتبع الغرض، مثل "SumColoredCells." قم بالتأكيد لإنشاء الوحدة.
2. انقر فوق موافق، وفي نافذة وحدة الكود الجديدة، استبدل أي كود افتراضي عن طريق لصق البرنامج النصي المقدم لجمع الخلايا الملونة. تأكد بعناية من نسخ كل الكود، لأن فقدان بعض الأحرف يمكن أن يؤدي إلى أخطاء نحوية.
function sumColoredCells(sumRange,colorRef) {
var activeRg = SpreadsheetApp.getActiveRange();
var activeSht = SpreadsheetApp.getActiveSheet();
var activeformula = activeRg.getFormula();
var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
var sumValues = activeSht.getRange(countRangeAddress).getValues();
var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
var BackGround = activeSht.getRange(colorRefAddress).getBackground();
var totalValue = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
if ((typeof sumValues[i][k]) == 'number')
totalValue = totalValue + (sumValues[i][k]);
return totalValue;
};
3. بعد حفظ البرنامج النصي، عد إلى الورقة الخاصة بك وأدخل الصيغة =sumcoloredcells(A1:E11,A1) في خلية فارغة، ثم اضغط على Enter. تقوم هذه الصيغة بجمع القيم في A1:E11 حيث يتطابق لون الخلفية مع A1. عند استخدام هذه الدالة، تأكد من أن جميع الخلايا المستهدفة للجمع هي قيم رقمية؛ سيتم تجاهل القيم غير الرقمية.
ملاحظة: A1:E11 يمثل نطاق بياناتك، وA1 يوفر المرجع للون. ستقوم الصيغة بجمع القيم الرقمية المرئية فقط - تأكد من أن الخلايا المدمجة أو الأخطاء داخل النطاق لا تؤثر على المجاميع.
4. يمكنك تكرار العملية المذكورة أعلاه لجمع القيم لفئات الألوان المختلفة عن طريق تغيير الخلية المرجعية للون في الصيغة. إذا تم تحديث بياناتك أو قمت بتغيير ألوان الخلفيات، تذكر تحديث الصيغة للحصول على النتائج المحدثة.
إذا كانت النتيجة تساوي صفرًا أو قيمة خطأ، تحقق من أن النطاق يحتوي على أرقام وأن التطابق اللوني دقيق. أيضًا، إعادة الحساب ليست تلقائية إذا تغير لون الخلية فقط - قم بتحرير الخلية التي تحتوي على الصيغة لإجبار التحديث.
حساب أو جمع قيم الخلايا حسب لون الخلية باستخدام Kutools لـ Excel في Microsoft Excel
عند العمل في Microsoft Excel، يعد حساب أو جمع الخلايا حسب اللون متطلبًا شائعًا، خاصة ضمن تقارير إدارة المشاريع أو المخزون أو مراقبة الجودة. يقدم Kutools لـ Excel أداة Count by Color المخصصة، والتي تتيح لك الحصول على العدد والمجموع حسب لون الخلفية أو لون الخط مباشرة - وهذا مفيد بشكل خاص لنطاقات البيانات الكبيرة وعندما تحتاج إلى نتائج سريعة وقابلة للتكرار.
بمجرد تثبيت Kutools لـ Excel، تابع الخطوات التالية:
1. قم بتحديد النطاق الذي ترغب في حسابه أو جمعه حسب اللون، ثم انقر فوق Kutools Plus > Count by Color. راجع لقطة الشاشة أدناه للحصول على الإرشادات:
2. ستظهر نافذة الحوار Count by Color. ضبط التنسيق القياسي تحت Color method، واختيار Background لـ Count type. تحقق بعناية من المعاينة والخيارات:
3. انقر فوق إنشاء تقرير لإنشاء ورقة عمل جديدة تحتوي على تفصيل العد والمجموع لكل لون موجود في نطاقك. يتضمن هذا التقرير كلًا من عدد ومجموع الخلايا الملونة، مما يتيح الإشارة السهلة أو المزيد من التحليل.
ملاحظة: يمكن أن تحسب هذه الميزة أيضًا القيم بناءً على التنسيق الشرطي أو لون الخط. استخدم قواعد التنسيق الشرطي للتحليل الديناميكي؛ وإلا فإن الأداة تعمل بشكل أفضل مع ملء الألوان الثابتة. تتطلب أي تغييرات في ألوان الخلايا المصدر تشغيل أداة Count by Color لتحديث النتائج. إذا واجهت مشكلات، تأكد من أن Kutools نشطة ومحدثة.
انقر فوق Download وجرّب Kutools لـ Excel الآن مجانًا!
أفضل أدوات الإنتاجية لمكتب العمل
عزز مهاراتك في Excel باستخدام Kutools لـ Excel، واختبر كفاءة غير مسبوقة. Kutools لـ Excel يوفر أكثر من300 ميزة متقدمة لزيادة الإنتاجية وتوفير وقت الحفظ. انقر هنا للحصول على الميزة الأكثر أهمية بالنسبة لك...
Office Tab يجلب واجهة التبويب إلى Office ويجعل عملك أسهل بكثير
- تفعيل تحرير وقراءة عبر التبويبات في Word، Excel، PowerPoint، Publisher، Access، Visio وProject.
- افتح وأنشئ عدة مستندات في تبويبات جديدة في نفس النافذة، بدلاً من نوافذ مستقلة.
- يزيد إنتاجيتك بنسبة50%، ويقلل مئات النقرات اليومية من الفأرة!
جميع إضافات Kutools. مثبت واحد
حزمة Kutools for Office تجمع بين إضافات Excel وWord وOutlook وPowerPoint إضافة إلى Office Tab Pro، وهي مثالية للفرق التي تعمل عبر تطبيقات Office.
- حزمة الكل في واحد — إضافات Excel وWord وOutlook وPowerPoint + Office Tab Pro
- مثبّت واحد، ترخيص واحد — إعداد في دقائق (جاهز لـ MSI)
- الأداء الأفضل معًا — إنتاجية مُبسطة عبر تطبيقات Office
- تجربة كاملة لمدة30 يومًا — بدون تسجيل، بدون بطاقة ائتمان
- قيمة رائعة — وفر مقارنة بشراء الإضافات بشكل منفرد