كيفية حساب المتوسط حسب اللون في إكسيل (مثال خطوة بخطوة)

تُعدّ معالجة البيانات وتحليلها في جداول البيانات الإلكترونية، ولا سيما برنامج مايكروسوفت إكسيل (Microsoft Excel)، ركيزةً أساسية في مختلف البيئات المؤسسية والأكاديمية والبحثية. ومع تزايد تعقيد مجموعات البيانات الحديثة، يلجأ العديد من المحللين والمستخدمين إلى توظيف تقنيات التمييز البصري، وعلى رأسها الترميز اللوني (Color Coding)، لتصنيف المدخلات وتحديد الأولويات وفرز المؤشرات الحرجة بشكل فوري وسهل القراءة للمستخدم البشري. غير أن هذا النمط من التخصيص البصري سرعان ما يفرض تحدياً إجرائياً وتقنياً كبيراً عندما يرغب المحلل في الانتقال من مرحلة العرض النوعي إلى مرحلة الاستخلاص الرياضي والإحصائي، وتحديداً عند محاولة حساب المتوسط الحسابي للقيم المرمزة بلون محدد دون غيرها.

تكمن الجذوة الأساسية لهذه المعضلة في البنية المعمارية الداخلية لبرنامج إكسيل؛ حيث صُمم البرنامج تاريخياً ليفصل بصورة قاطعة بين «قيمة الخلية» (Cell Value) وبين «سماتها التنسيقية والمظهرية» (Formatting Attributes). فالخوارزميات القياسية والدوال الرياضية المدمجة في ورقة العمل تتعامل مع المحتوى الرياضي المودع في الذاكرة الحسابية، متجاهلةً تماماً طبقة العرض التنسيقي التي تراها العين البشرية كألوان التعبئة وخلفيات الخلايا. ويترتب على ذلك غياب دالة قياسية جاهزة مثل AVERAGEIF قادرة بصورة افتراضية على قراءة خلفية الخلية ومعاملتها كمعيار شرطي مستقل.

يهدف هذا الدليل المنهجي المتقدم إلى تفكيك هذه الإشكالية عبر تقديم حلول برمجية وتحليلية دقيقة ومستدامة. سنناقش بالتفصيل المعرفي والتطبيقي كيفية بناء دالة مخصصة معرّفة من قبل المستخدم (User-Defined Function) باستخدام لغة التطبيقات المرئية الأساسية (Visual Basic for Applications – VBA)، مع تفنيد الحدود التقنية المرتبطة بالتلوين اليدوي في مقابل التنسيق الشرطي (Conditional Formatting)، واستعراض أفضل الممارسات المنهجية التي تضمن للباحثين والمحللين دقة النتائج الإحصائية وموثوقيتها، مع الحفاظ على سلامة النماذج الحسابية وأداء البرنامج في مختلف الظروف التشغيلية.

1. المقدمة التأسيسية: أهمية التحليل الإحصائي للبيانات المرمزة لونياً في إكسيل

1.1 مفهوم الترميز اللوني ودوره في إدارة البيانات المعقدة

يمثل الترميز اللوني داخل جداول البيانات أداة اتصال بصري محورية تسهم في خفض الحمل الإدراكي (Cognitive Load) الواقع على المحلل البشري أثناء مراجعة كميات هائلة من الأرقام والمدخلات المعقدة. ففي العديد من المجالات التطبيقية، مثل إدارة المشروعات المالية، والتحليلات المحاسبية، وتتبع الاستبيانات السلوكية، وتدقيق التكاليف التشغيلية، يُستخدم اللون للإشارة السريعة إلى الحالات النوعية؛ كأن يُخصص اللون الأخضر للدلالة على المهام المنجزة أو المبيعات المحققة للأهداف، واللون الأحمر للتنبيه بالخسائر أو المؤشرات التي تستوجب التدخل الفوري، واللون الأصفر لتحديد الحالات الانتقالية أو المتوسطة. إن هذا التوظيف البصري يتيح للمديرين وصنّاع القرار فهم السياق العام لمصفوفة البيانات بمجرد النظرة الأولى، مما يعزز سرعة اتخاذ القرارات التنفيذية.

مع ذلك، تنشأ فجوة تقنية ومنهجية واضحة بين هذا التنسيق البصري الفعّال وبين المتطلبات الحسابية والإحصائية الدقيقة. فالتمييز البصري يسهل استيعابه من قبل الجهاز العصبي الإدراكي للإنسان، ولكنه يظل بالنسبة لمحرك المعالجة الحسابية في إكسيل مجرد إعدادات تنسيقية سطحية ليس لها وزن كمي. وحين تبرز الحاجة المنهجية لاستخراج مؤشرات كمية ترتبط بهذه الفئات المرمزة—مثل استخراج المتوسط الحسابي (Arithmetic Mean) للأرقام المظللة بلون محدد دون غيره—يجد المحلل نفسه مقيداً بعدم قدرة الدوال المعتادة على تحويل اللون إلى معيار فرز رياضي تلقائي.

إن استخراج المتوسط الحسابي للبيانات المرمزة لونياً يكتسب أهمية استثنائية عندما تتداخل الفئات التحليلية داخل النطاق نفسه؛ حيث قد تتجاور أرقام تنتمي إلى مستويات خطورة مختلفة أو قطاعات جغرافية متعددة لا يُمكن فصلها مكانياً في أعمدة مستقلة بسبب قيود التصميم الجدولي المعتمد. من هنا تظهر الضرورة العلمية لتطوير آليات تسمح بتحويل هذه المتغيرات المظهرية إلى محددات شرطية تُمكن المحرك الحسابي من جمع عناصر الفئة وتحديد عددها بدقة متناهية، لاستخلاص مؤشر النزعة المركزية المعبر عنها إحصائياً.

1.2 قصور الدوال المدمجة التقليدية أمام الخصائص المظهرية للخلايا

عند محاولة تطبيق الدوال الحسابية القياسية مثل دالة AVERAGE أو الدوال الإحصائية الشرطية كدالتي AVERAGEIF وAVERAGEIFS، يصطدم المستخدم بحقيقة أن وسائط هذه الدوال (Arguments) مصممة لاستقبال شروط تستند حصرياً إلى القيم المنطقية والنصية والعددية، ولا تمتلك أي قدرة مدمجة على تقييم الخصائص المظهرية مثل لون التعبئة (Fill Color) أو لون الخط (Font Color). فمحرك الحساب في إكسيل يقيم التعبيرات المنطقية عبر مقارنة البيانات المخزنة، وبالتالي فإن صياغة شرط يعتمد على أن تكون خلفية الخلية صفراء أو زرقاء تفشل فورياً لعدم وجود مرجع نسبي أو اسمي للون ضمن الصيغ القياسية للورقة.

يعود هذا العجز الهيكلي إلى النموذج البرمجي الكامن وراء بنية مايكروسوفت إكسيل، والذي يفصل فصلاً صارماً بين طبقة تخزين القيم (Data Layer) وطبقة واجهة المستخدم التنسيقية (Presentation Layer). فالخلية في ذاكرة البرنامج تتألف من عدة خصائص مستقلة؛ منها خاصية القيمة الحسابية، وخاصية الصيغة الرياضية، وخاصية التنسيق المظهري التي تشمل نوع الخط والحجم والحدود ولون التعبئة الداخلي. إن شجرة الحساب الرياضي (Calculation Engine) ترصد وتتابع فقط التغييرات الواقعة في طبقة القيم والصلات التشعبية بينها، في حين أن طبقة التنسيق تُعالج بصورة منفصلة بواسطة بطاقة الرسوميات وواجهة العرض الرسومية لإكسيل لتسهيل التفاعل البصري.

نظراً لهذا الانفصال الهيكلي، فإن الوسائل التقليدية المتاحة تصبح عاجزة تماماً عن تحقيق المطلوب ما لم يتم اللجوء إلى أدوات التحايل اليدوي، مثل فرز البيانات حسب اللون وتطبيق دالة المجموع الفرعي SUBTOTAL، أو استخدام تقنيات ماكرو إكسيل 4 القديمة المعقدة (XLM Macros) عبر نطاقات التسمية ودالة GET.CELL. ولكن الحل الأكثر مرونة واستدامة وتوافقاً مع أفضل المعايير التقنية يتمثل في الاستعانة ببيئة لغة البرمجة المدمجة في حزمة أوفيس، وهي لغة فيجوال بيسك للتطبيقات Visual Basic for Applications (VBA)، والتي تتيح للمستخدم النفاذ إلى الكائنات البرمجية الداخلية للخلية، بما فيها خصائص التعبئة الداخلية وتجاوز القيود الافتراضية للدوال المدمجة.

2. الأسس المعرفية لتطوير الدوال المخصصة (UDF) عبر بيئة VBA

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

تُعرف الدوال المعرّفة من قبل المستخدم (User-Defined Functions – UDF) بأنها إجراءات برمجية مخصصة تُكتب بلغة VBA لتقوم بوظائف حسابية أو منطقية غير متوفرة ضمن مكتبة الدوال القياسية للبرنامج. تختلف الدالة المخصصة (Function) اختلافاً جوهرياً عن الماكرو الإجرائي العادي (Subroutine)؛ فالماكرو الإجرائي يُنفذ سلسلة من الأوامر والعمليات على مصحف العمل، كنسخ النطاقات، وتعديل التنسيقات، وحفظ الملفات، ولكنه لا يُرجع قيمة مباشرة إلى الخلية التي يُستدعى منها. في المقابل، تُصمم الدوال المخصصة لتستقبل مدخلات ومحددات محددة من ورقة العمل، ثم تجري معالجات منطقية وحسابية داخلية، لتعيد في النهاية قيمة مفردة تُعرض داخل الخلية تماماً كأي دالة قياسية مثل SUM أو VLOOKUP.

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

2.2 فهم خوارزمية التعرف على لون خلفية الخلية برمجياً

لتمكين برنامج إكسيل من قراءة لون الخلية وحساب المتوسط بناءً عليه، يجب على المطور البرمجي التعامل مع الكائن البرمجي الممثل للنطاق وهو الكائن Range. يحتوي هذا الكائن في هيكل الكائنات التابع لـ VBA على خاصية فرعية تُدعى Interior، وهي المسؤولة عن إدارة كافة سمات التعبئة الداخلية للخلية. وتتفرع من هذه الخاصية خيارات لتحديد اللون؛ أهمها خاصية Interior.Color وخاصية Interior.ColorIndex. تعتمد خاصية ColorIndex على فهرس يتألف من 56 لوناً أساسياً في لوحة ألوان إكسيل التقليدية، في حين توفر خاصية Color دعماً لنظام الألوان الحقيقي (RGB – TrueColor) المكون من 16 مليون لون عبر قيم رقمية طويلة، مما يجعلها أدق بكثير عند التعامل مع درجات ألوان مخصصة.

تقوم الخوارزمية البرمجية المنوطة بحساب المتوسط على مسح نطاق الخلايا المستهدف خطوة بخطوة؛ حيث يُجرى فحص منطقي لكل خلية عبر حلقة تكرارية، ومقارنة القيمة اللونية لخاصية التعبئة للخلية الحالية مع القيمة اللونية لخلية مرجعية يتم تمريرها إلى الدالة كمعيار للمقارنة. إذا تطابقت القيمة اللونية بين الخلية المفحوصة والخلية المرجعية، يُوجه البرنامج لجمع قيمة تلك الخلية الرياضية إلى متغير تراكمي مخصص للمجموع (Running Total)، بالتزامن مع زيادة متغير عددي تراكمي مخصص لتعداد الخلايا المطابقة بمقدار وحدة واحدة (Count Incrementor).

تتطلب هذه الخوارزمية معالجة دقيقة للمتغيرات الرياضية؛ إذ يجب تهيئة المتغيرات الصفرية والتأكد من استبعاد الخلايا غير الرقمية أو الفارغة حتى لا تؤدي إلى أخطاء في النوع البرمجي (Type Mismatch). وفي نهاية مسار الحلقة التكرارية، يُحسب المتوسط الحسابي عبر قسمة المجموع التراكمي على العدد الإجمالي للخلايا المحققة للشرط اللوني، مع إدراج تدابير وقائية تضمن منع انهيار الدالة في حال كانت النتيجة صفرية، لتفادي أخطاء القسمة الرياضية غير المعرفة.

3. الخطوة الأولى: إعداد وتنظيم مصفوفة البيانات في ورقة العمل

3.1 بناء الهيكل الجدولي وتوزيع القيم الرقمية

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

يُفضل تنظيم السجلات في صفوف متتالية ومستمرة، وتجنب دمج الخلايا (Merged Cells) تماماً داخل نطاق البيانات المراد تحليله؛ نظراً لأن دمج الخلايا يُربك محرك مسح النطاقات في VBA ويؤدي إلى تخطي بعض القيم أو احتسابها بصورة مكررة. كما يُنصح بإضافة رأس جدولي واضح لكل عمود يحدد وحدة القياس وطبيعة المؤشر، مما يُيسر لاحقاً مقارنة النتائج التي ستستخرجها الدالة المخصصة مع النتائج اليدوية للتحقق من الموثوقية وصحة العمليات الرياضية التراكمية.

3.2 تطبيق التنسيق اللوني اليدوي على الخلايا المستهدفة

بعد اكتمال بناء الهيكل الرقمي، تأتي خطوة تطبيق التنسيق اللوني على الخلايا المستهدفة. يجب هنا التمييز الصارم بين التلوين اليدوي باستخدام أداة تعبئة الألوان (Fill Color) الموجودة في شريط الأدوات الرئيسي، وبين التنسيق الشرطي الآلي. الشفرة البرمجية الأساسية التي سنعتمدها في هذا الدليل مصممة لقراءة درجات الألوان المطبقة يدوياً، حيث يقوم المحلل باختيار مجموعات من الخلايا وتلوينها بدرجات لونية مميزة لتمثيل حالات محددة؛ كاللون الأخضر للأداء المرتفع، واللون البرتقالي للأداء المتوسط، واللون الرمادي للفئات المستبعدة مؤقتاً من المؤشرات القياسية.

لضمان نجاح المعالجة البرمجية، يجب تثبيت خلية مرجعية خارجية لكل لون مستخدم في مكان مستقل خارج نطاق البيانات الرئيسي؛ كأن يُخصص جدول ملخص جانبي يضم عموداً يسمى «اللون المرجعي» وعموداً مجاوراً للنتيجة الإحصائية. يتعين تلوين هذه الخلية المرجعية بنفس درجة اللون الدقيقة التي تم استخدامها في تلوين خلايا النطاق، ويُفضل استخدام درجات الألوان القياسية (Standard Colors) التي يوفرها البرنامج لتفادي الخلط البصري بين درجات التدرج اللوني المتقاربة جداً؛ إذ إن العين البشرية قد تعجز عن التمييز بين درجتين من درجات الأزرق، لكن خوارزمية VBA ستتعامل معهما كقيمتين مختلفتين تماماً ولن تحتسبهما ضمن نفس المتوسط.

4. الخطوة الثانية: تمكين وتفعيل علامة تبويب المطور (Developer Tab)

4.1 الوصول إلى خيارات تخصيص شريط الأدوات

يأتي برنامج مايكروسوفت إكسيل افتراضياً مع إخفاء علامة تبويب المطور (Developer Tab)، وذلك لحماية المستخدمين المبتدئين من العبث غير المقصود بالأدوات المتقدمة ووحدات التحكم البرمجية. وللوصول إلى بيئة كتابة الأكواد، يتعين على المحلل أولاً إظهار هذا التبويب على الشريط الرئيسي. يتم ذلك من خلال النقر على قائمة «ملف» (File) الموجودة في الزاوية العلوية لنافذة إكسيل، ثم التوجه إلى أسفل القائمة واختيار «خيارات» (Options)، لتفتح نافذة جديدة تتضمن كافة الإعدادات والتهيئات التشغيلية المتقدمة للبرنامج.

من اللوحة الجانبية لنافذة خيارات إكسيل، يختار المستخدم قسم «تخصيص الشريط» (Customize Ribbon). في هذه الواجهة، تُعرض قائمتان رئيسيتان: القائمة اليمنى التي تضم الأوامر الشائعة، والقائمة اليسرى المخصصة لعرض كافة علامات التبويب الرئيسية المفعلة وغير المفعلة في واجهة التطبيق. في هذه المرحلة أيضاً، يجب الانتباه إلى مراجعة إعدادات الأمان الخاصة بوحدات الماكرو، وذلك من خلال النقر على «مركز التوثيق» (Trust Center) ثم «إعدادات مركز التوثيق» والتأكد من تمكين تشغيل الماكرو مع الإشعارات المناسبة حتى لا تُحجب الشفرات البرمجية تلقائياً عند إعادة فتح المصنف.

4.2 إظهار واجهة المطور والتحقق من توافر الأدوات البرمجية

داخل نافذة «تخصيص الشريط»، يبحث المستخدم في القائمة اليمنى تحت عنوان «علامات التبويب الرئيسية» (Main Tabs) عن خيار «المطور» (Developer)، والذي يكون بجواره مربع اختيار فارغ في الإعدادات الافتراضية. يقوم المستخدم بالنقر على المربع لوضع علامة الاختيار، ثم الضغط على زر «موافق» (OK) في أسفل النافذة لتثبيت هذا التعديل وإغلاق لوحة الإعدادات.

بمجرد إتمام هذه الخطوة، سيلاحظ المستخدم ظهور علامة تبويب جديدة باسم «المطور» (Developer) ضمن شريط الأدوات العلوي للبرنامج، مستقرة بجوار علامات التبويب التقليدية مثل «عرض» و«تعليمات». يتيح هذا التبويب للمحلل الوصول السريع إلى حزمة من الأدوات البرمجية القوية، وفي مقدمتها زر «Visual Basic»، وزر «الماكرو» (Macros)، ومحددات الأمان، بالإضافة إلى أدوات إدراج عناصر التحكم في النماذج التفاعلية (ActiveX Controls). يوفر توافر هذه الأدوات الأساس للانتقال إلى المرحلة التالية لبناء الدالة المخصصة بشكل احترافي.

5. الخطوة الثالثة: ولوج محرر Visual Basic وإنشاء وحدة برمجية جديدة

5.1 فتح بيئة التطوير المتكاملة (VBA Editor)

تُمثل بيئة التطوير المتكاملة لتطبيقات فيجوال بيسك (Microsoft Visual Basic for Applications Editor) منصة البرمجة المدمجة التي تسمح للمطورين بإنشاء وتعديل الشفرات البرمجية والتعامل المباشر مع الهيكل البرمجي لكائنات إكسيل. يمكن فتح هذه البيئة بطريقتين؛ الأولى بالنقر المباشر على أيقونة «Visual Basic» الواقعة في أقصى الجانب الأيسر من علامة تبويب «المطور»، أو استخدام الاختصار المباشر والسريع عبر لوحة المفاتيح بالضغط المتزامن على مفتاحي Alt + F11، مما يؤدي فوراً إلى انبثاق نافذة المحرر المستقلة عن نافذة ورقة العمل الرئيسية.

عند فتح المحرر، يظهر للمستخدم إطار عمل احترافي يحتوي على عدة نوافذ فرعية، أبرزها نافذة «متصفح المشروع» (Project Explorer) التي تقع عادةً في الجزء العلوي الأيسر. يعرض متصفح المشروع هيكلاً شجرياً يضم كافة مصنفات العمل المفتوحة حالياً وأوراق العمل التابعة لها، بالإضافة إلى كائن المصنف العام (ThisWorkbook). من الضروري هنا التأكد من تحديد المصنف المستهدف بالتحليل البرمجي قبل كتابة أي كود، تفادياً لإدراج الدوال البرمجية داخل مصنفات أخرى غير مخصصة أو داخل بيئة القوالب الافتراضية للبرنامج، وهو ما قد يتسبب في تشتت الدوال وفقدانها عند مشاركة الملف مع محللين آخرين.

5.2 إدراج وحدة نمطية قياسية (Standard Module)

لضمان إتاحة الدالة المخصصة للاستخدام العام عبر كافة أوراق العمل التابعة للمصنف النشط، يجب كتابتها داخل ما يُسمى «الوحدة النمطية القياسية» (Standard Module). ولإدراج هذه الوحدة، ينقر المستخدم على قائمة «إدراج» (Insert) الموجودة في شريط القوائم العلوي لمحرر VBA، ثم يختار من القائمة المنسدلة خيار «وحدة نمطية» (Module). تظهر على الفور نافذة تحرير بيضاء فارغة تمثل مساحة كتابة الكود البرمجي المستقلة، وتحمل افتراضياً اسماً مثل Module1.

يعد التمييز بين الوحدة النمطية القياسية وبين صفحات الأكواد التابعة لأوراق العمل كائنات مفصلية في إدارة الأكواد؛ فالكود المكتوب داخل صفحة ورقة عمل معينة (مثل Sheet1) غالباً ما يكون مقيداً بالأحداث المرتبطة بتلك الصفحة فقط (Worksheet Events)، ولا يمكن استدعاؤه بسهولة كدالة رياضية في خلايا أخرى. أما الوحدات النمطية العامة، فهي المساحة المعيارية لتضمين الدوال المعرفة من قبل المستخدم (UDF)، حيث تصبح الدالة المسجلة فيها مرئية ومتاحة للاستدعاء عبر محرك الصيغ في كامل المصنف كأنها جزء أصيل من مكتبة إكسيل الرياضية.

6. الخطوة الرابعة: كتابة الشفرة البرمجية للدالة AvgCellsByColor وشرح مكوناتها

6.1 كتابة الكود البرمجي المخصص لحساب المتوسط الحسابي للون

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

Function AvgCellsByColor(CellRange As Range, CellColor As Range) As Double
    Application.Volatile
    Dim TargetCell As Range
    Dim TargetColorIndex As Long
    Dim RunningSum As Double
    Dim MatchingCount As Long

    TargetColorIndex = CellColor.Interior.ColorIndex
    RunningSum = 0
    MatchingCount = 0

    For Each TargetCell In CellRange
        If TargetCell.Interior.ColorIndex = TargetColorIndex Then
            If IsNumeric(TargetCell.Value) And Not IsEmpty(TargetCell.Value) Then
                RunningSum = RunningSum + TargetCell.Value
                MatchingCount = MatchingCount + 1
            End If
        End If
    Next TargetCell

    If MatchingCount > 0 Then
        AvgCellsByColor = RunningSum / MatchingCount
    Else
        AvgCellsByColor = 0
    End If
End Function

تتضمن الشفرة البرمجية السابقة تعريفاً للدالة AvgCellsByColor التي تتطلب مدخلين إجباريين: الأول هو CellRange ويمثل نطاق الخلايا الرقمية المحتوية على البيانات، والثاني هو CellColor ويمثل الخلية المرجعية المحتوية على لون المعيار المطلوب مطابقته. أُعلن عن نوع القيمة المرجعة من الدالة بكونها Double لضمان استيعاب الكسور العشرية ونتائج القسمة الدقيقة دون حدوث تقريب جبري مضلل، كما أُعلنت المتغيرات التراكمية باستخدام أفضل ممارسات تحديد النوع لتفادي استنزاف موارد الذاكرة.

6.2 التحليل المنطقي لحلقة التكرار وحساب المتوسط داخل الكود

يعتمد البناء المنطقي للكود على مسح كل عنصر داخل النطاق المحدد باستخدام حلقة التكرار For Each… Next. في البداية، تقرأ الدالة الفهرس اللوني للخلية المرجعية وتخزنه في المتغير TargetColorIndex. ثم تشرع الحلقة في المرور على كل خلية ضمن النطاق؛ فإذا تطابق لون خلفية الخلية المفحوصة مع اللون المستهدف، ينتقل الكود إلى التحقق من شرط ثانوي مهم للغاية وهو التأكد من أن محتوى الخلية ذو طبيعة عددية وليس نصاً أو خلية فارغة عبر استخدام IsNumeric وIsEmpty.

تُعد هذه الخطوة التحققية ضرورية لتجنب تعطل المعالج أثناء محاولة جمع سلاسل نصية ملونة قد توجد عرضاً ضمن نطاق البيانات. إذا استوفت الخلية كافة الشروط، تُضاف قيمتها إلى المتغير التراكمي للمجموع RunningSum، وتتم زيادة عداد الخلايا MatchingCount بقيمة واحد صحيح. بعد اكتمال مسح كافة خلايا النطاق وانتهاء دورة الحلقة التكرارية، تخضع النتيجة لفحص شرطي أخير عبر جملة If… Then… Else لتقييم قيمة العداد؛ فإذا كان العداد أكبر من صفر، يُحسب المتوسط الرياضي عبر قسمة المجموع على التعداد، أما إذا كان العداد صفراً (أي لم يُعثر على أي خلية متطابقة لونياً ورقمياً)، تُعيد الدالة القيمة صفر بدلاً من توليد خطأ القسمة على الصفر البرمجي، مما يضمن استقرار ورقة العمل وثبات المخرجات التحليلية.

7. الخطوة الخامسة: تطبيق الدالة عملياً واستخراج النتائج داخل ورقة العمل

7.1 صياغة المعادلة واستدعاء الدالة المخصصة

بعد الانتهاء من كتابة وتأمين الشفرة البرمجية داخل محرر VBA، ينتقل المحلل مجدداً إلى واجهة ورقة العمل في إكسيل (ويمكن استخدام الاختصار Alt + F11 للتبديل السريع بين بيئة الأكواد وورقة البيانات). لتطبيق المعادلة واستخراج النتائج، يحدد المستخدم الخلية المستهدفة التي يرغب في ظهور المتوسط الإحصائي داخلها، ثم يبدأ بكتابة علامة التساوي = تليها اسم الدالة التي تم إنشاؤها للتو:

=AvgCellsByColor(B2:B25, D2)

في هذه الصيغة النموذجية، يمثل المعامل الأول B2:B25 مصفوفة البيانات الرقمية التي تتضمن خلايا ذات ألوان خلفيات متعددة، بينما يمثل المعامل الثاني D2 الخلية المستقلة المرجعية التي تم تلوينها مسبقاً باللون المستهدف تحليله (كاللون الأزرق مثلاً). عند الضغط على مفتاح الإدخال (Enter)، يقوم محرك إكسيل بتمرير النطاقات مباشرة إلى دالة VBA المخصصة، لتقوم بالمعالجة الفورية وإرجاع المتوسط الحسابي للخلايا المحققة لشرط التطابق اللوني وعرضه في الخلية المعنية.

7.2 تثبيت النطاقات وتعميم المعادلة على الفئات اللونية المختلفة

في البيئات التحليلية العملية، نادراً ما يقتصر الهدف على استخراج المتوسط لفئة لونية واحدة؛ بل يتطلب التحليل عادةً استخراج المتوسطات الحسابية لعدة فئات لونية (مثل الأخضر، والأصفر، والأحمر) مجتمعة في جدول إحصائي ملخص. لتعميم الصيغة بطريقة احترافية وتفادي أخطاء إزاحة النطاقات عند سحب المعادلة عمودياً، يجب تثبيت مراجع النطاق المستهدف باستخدام إشارات الدولار $ لتحويل المرجع إلى مرجع مطلق (Absolute Reference)، كما في الشكل التالي:

=AvgCellsByColor($B$2:$B$25, D2)

عند تثبيت نطاق البيانات الرقمية $B$2:$B$25 والإبقاء على مرجع الخلية المرجعية D2 نسبياً، يُمكن للمحلل استخدام مقبض التعبئة التلقائي (AutoFill Handle) وسحب الصيغة إلى الأسفل باتجاه الخلايا D3 وD4، حيث تحتوي كل واحدة منها على لون مرجعي مختلف. ستُطبق الدالة الخوارزمية الحسابية نفسها على كل فئة لونية بصورة منفصلة، مستخرجة المتوسط الحسابي الدقيق لكل تصنيف على حدة. ولتعزيز الموثوقية الرياضية، يُنصح بالتحقق اليدوي من فئة واحدة على الأقل، بجمع عناصرها وقسمتها يدوياً، ومطابقة النتيجة مع مخرجات الدالة البرمجية للتأكد من انعدام أي انزياحات تنسيقية أو برمجية غير مقصودة.

8. الفروق الجوهرية: التلوين اليدوي مقابل التنسيق الشرطي (Conditional Formatting)

8.1 طبيعة تعامل VBA مع خصائص التنسيق الشرطي

من أكثر المفاهيم الخاطئة شيوعاً بين مستخدمي إكسيل الاعتقاد بأن الدالة المخصصة السابقة المبنية على خاصية Interior.ColorIndex أو Interior.Color قادرة على قراءة الألوان الناتجة عن قواعد التنسيق الشرطي (Conditional Formatting) تلقائياً. من الناحية المعمارية، يتعامل إكسيل مع التنسيق الشرطي كطبقة مظهرية ديناميكية ومؤقتة تطفو فوق التنسيق الأصلي للخلية. عندما تتلون الخلية بناءً على قاعدة شرطية، فإن خاصية التعبئة الأساسية للكائن Range.Interior لا تتغير قيمتها في الذاكرة الثابتة، وتظل محتفظة باللون الأصلي (أو تظل بدون تعبئة)، وبالتالي تفشل الدالة المخصصة في رصد هذا اللون وتعيد القيمة صفر.

للتعامل مع الألوان الناتجة عن التنسيق الشرطي، وفرت مايكروسوفت بدءاً من إصدار إكسيل 2010 كائناً برمجياً متقدماً يُدعى DisplayFormat، وتحديداً الخاصية Range.DisplayFormat.Interior.Color. يستطيع هذا الكائن قراءة اللون الفعلي الذي يظهر على الشاشة بغض النظر عما إذا كان ناتجاً عن تلوين يدوي أو قاعدة شرطية معقدة. ومع ذلك، فرضت مايكروسوفت قيداً صارماً ومقصوداً على هذا الكائن: لا يمكن استدعاء خاصية DisplayFormat داخل الدوال المخصصة (UDF) التي تعمل مباشرة من خلال صيغ ورقة العمل؛ حيث يؤدي استدعاؤها في صيغة خلية إلى إرجاع خطأ القيمة #VALUE! فوراً لأسباب تتعلق بإدارة أداء محرك الحساب وتفادي الحلقات التكرارية القاتلة في الواجهة الرسومية. ولا يُسمح باستخدام DisplayFormat إلا داخل وحدات الماكرو الإجرائية المستقلة (Subroutines) أو أدوات التحكم التي تُشغل بواسطة أزرار تفاعلية.

8.2 الحلول المنهجية للتعامل مع البيانات المنسقة شرطياً

نظراً للقيود البرمجية المفروضة على الدوال المخصصة أمام التنسيق الشرطي، يتعين على المحلل تبني استراتيجيات بديلة ومنهجية لمعالجة هذا السيناريو. يتمثل الحل الأكثر كفاءة وملاءمة في تجاوز اللون تماماً والاعتماد المباشر على الشروط والمعايير المنطقية الأصلية التي أدت إلى توليد ذلك التنسيق الشرطي. فبدلاً من محاولة قياس «اللون الأخضر» الذي يُمثل الخلايا الأكبر من 50، يجب صياغة الدالة القياسية المدمجة AVERAGEIFS بالاعتماد المباشر على الشرط الأصلي:

=AVERAGEIFS(B2:B25, B2:B25, ">50")

يتميز هذا الأسلوب الرياضي بالسرعة الفائقة والتوافق التام مع كافة إصدارات إكسيل دون الحاجة لتفعيل الماكرو أو كتابة أكواد برمجية مخصصة. أما في الحالات التي تكون فيها قواعد التنسيق الشرطي شديدة التعقيد والتداخل، فيمكن استخدام ماكرو إجرائي (Sub) يقوم بقراءة الألوان عبر خاصية DisplayFormat وترجمة كل لون إلى قيمة فئوية تُكتب في عمود مساعد مستقل (Helper Column)، أو نسخ التنسيقات وتثبيتها كلون يدوي ثابت. يوضح الجدول التالي مقارنة منهجية بين الأساليب المتاحة لمعالجة متوسطات البيانات الملونة:

المنهجية المتبعة	نوع التلوين المتوافق	متطلبات التشغيل	التوافق مع أداء المصنف
دالة VBA المخصصة (UDF)	التلوين اليدوي فقط	تفعيل الماكرو وحفظ الملف كـ .xlsm	مناسب للنطاقات المتوسطة؛ يتطلب تحديثاً يدوياً عند تعديل الألوان
الدوال الشرطية المدمجة (AVERAGEIFS)	البيانات المنسقة شرطياً	دوال قياسية مدمجة بدون متطلبات أمان	أعلى درجات الكفاءة والسرعة؛ تحديث فوري وتلقائي دائم
الفرز اللوني مع دالة SUBTOTAL	التلوين اليدوي والشرطي معاً	تطبيق التصفية واستخدام المعامل 101	تفاعل يدوي مطلوب؛ غير ملائم للتقارير الآلية المترابطة
ماكرو إجرائي مستقل (Sub)	التلوين اليدوي والشرطي (DisplayFormat)	تشغيل يدوي أو عبر زر تحكم تفاعلي	قوي ومفتوح؛ يكتب النتائج في خلايا ثابتة دون ديناميكية الصيغ

9. استكشاف الأخطاء الشائعة واستراتيجيات تحسين الأداء البرمجي

9.1 مشكلة تحديث الحساب التلقائي وتفعيل خاصية التطاير (Volatility)

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

لمعالجة هذه المعضلة برمجياً، يُضاف السطر البرمجي Application.Volatile في مستهل الشفرة البرمجية للدالة المخصصة. يؤدي هذا السطر إلى تحويل الدالة إلى «دالة متطايرة» (Volatile Function)، مما يجبر محرك الحساب على إعادة تقييمها واحتساب نتيجتها كلما طرأت عملية حسابية في أي مكان داخل ورقة العمل. ومع ذلك، يجب إدراك حقيقة مهمة: خاصية التطاير تجعل الدالة تعيد الحساب فقط عند تغير قيم الخلايا في المصنف أو عند الضغط اليدوي على مفتاح الحساب F9 أو توليفة Ctrl + Alt + F9، ولكنها لا تزال عاجزة عن العمل بمجرد تغيير اللون التنسيقي منفرداً في واجهة البرنامج دون إدخال قيمة جديدة. لذا يجب تدريب المستخدمين دائماً على الضغط على مفتاح F9 لإجبار المصنف على إعادة التدقيق وتحديث المتوسطات اللونية عند الانتهاء من التنسيق.

9.2 معالجة أخطاء الصفر، القيم غير الرقمية، وتفاوت الألوان

تتعرض النماذج الحسابية إلى الانهيار وتوليد رسائل أخطاء شائعة مثل #DIV/0! أو #VALUE! في حال غياب المعالجة الاستباقية للبيانات الشاذة داخل الشفرة البرمجية. يظهر خطأ القسمة على الصفر عندما يتم تمرير لون مرجعي لا يمتلك أي خلايا مطابقة داخل النطاق المستهدف؛ لذا فإن معالجة الكود بشرط احترازي If MatchingCount > 0 Then يضمن إرجاع قيمة رقمية معيارية كالصفر أو نص وصفي، بدلاً من ظهور رسائل الخطأ التي تفسد مظهر التقارير النهائية.

كذلك يجب التحوط لمسألة الفراغات والنصوص التي قد تقع داخل النطاق الملون؛ إذ إن وجود خلية ملونة تحتوي على نص وصفي كالملاحظات التوضيحية قد يؤدي إلى تعطل العمليات الرياضية التراكمية، ومن هنا تنبع أهمية استخدام الدالة الفاحصة IsNumeric لاستثناء أي مدخلات غير عددية من مصفوفة الجمع والتعداد. وأخيراً، لحماية هذا العمل البرمجي وضمان استمراريته، يجب الانتباه إلى حفظ المصنف الإكسيل بصيغة تدعم وحدات الماكرو، وهي صيغة Excel Macro-Enabled Workbook (*.xlsm)؛ حيث إن حفظ الملف بالصيغة القياسية الافتراضية (*.xlsx) يؤدي إلى الحذف التلقائي والفوري لكافة الوحدات النمطية والشفرات البرمجية المضمنة، وتدمير الدوال المخصصة عند إعادة فتح الملف.

10. الخاتمة: أفضل الممارسات المنهجية لإدارة وتحليل البيانات في إكسيل

10.1 التوصيات المعيارية لتوثيق وحفظ مصنفات العمل المتقدمة

يتطلب دمج الحلول البرمجية عبر VBA في بيئات الأعمال والمؤسسات الأكاديمية التزاماً صارماً بمبادئ التوثيق وإدارة المخاطر البرمجية. يُنصح دائماً بتضمين تعليقات توضيحية داخل الشفرة البرمجية (VBA Comments) باستخدام علامة الفاصلة العليا ' لتوضيح الغرض من كل متغير وطبيعة القيود المنطقية المطبقة؛ مما يُسهل على المحللين والمدققين الآخرين فهم هيكل الدالة وصيانتها مستقبلاً دون الحاجة لإعادة هندستها عكسياً.

علاوة على ذلك، يجب إنشاء ورقة عمل مستقلة داخل المصنف مخصصة لتمثيل «دليل الترميز والمصطلحات» (Data Dictionary & Style Guide)، تحتوي على جدول مرجعي رسمي يوضح الدلالة الدقيقة لكل لون مستخدم في المشروع، مع تثبيت رمز اللون السداسي (Hex Code) أو قيم RGB الدقيقة بجواره. هذا التوثيق يمنع التداخل اللوني الناتج عن استخدام شاشات عرض مختلفة أو قيام مستخدمين متعددين بتطبيق تدرجات متقاربة تُربك خوارزمية الدالة المخصصة. كما ينبغي اتباع استراتيجية حفظ دورية لإنشاء نسخ احتياطية مؤرخة من ملفات العمل بصيغة .xlsm لحماية البيانات الحساسة من أي تلف مفاجئ قد يصيب المشروع البرمجي.

10.2 مقارنة منهجية: متى نستخدم الألوان ومتى نعتمد على الأعمدة الفئوية؟

في ختام هذا الدليل المتقدم، تقتضي النزاهة المنهجية والتحليلية طرح سؤال جوهري: هل يُعد الاعتماد على الترميز اللوني لتخزين الفئات والبيانات ممارسة موصى بها علمياً؟ وفقاً لمبادئ هندسة البيانات ونظرية «البيانات المرتبة» (Tidy Data)، فإن استخدام التنسيق اللوني كحامل أساسي ووحيد للمعلومات الفئوية يُعتبر ممارسة غير مستحسنة في قواعد البيانات المتقدمة والمصفوفات الإحصائية الضخمة؛ نظراً لأنه يخل بمبدأ فصل المتغيرات عن المظاهر التنسيقية ويزيد من احتمالية الأخطاء غير المرئية.

تتمثل الممارسة الفضلى دائماً في تخزين التصنيفات بصورة واضحة كمتغيرات اسمية فئوية (Categorical/Nominal Variables) داخل عمود مستقل بذاته؛ مثل عمود «مستوى الأداء» ويحتوي صراحة على قيم نصية كـ (مرتفع، متوسط، منخفض). بعد بناء هذه البنية البيانية السليمة، يمكن للمحلل توظيف التنسيق الشرطي لعرض الألوان كأداة تحسين بصري فقط، مع الاعتماد في الحسابات الإحصائية وحساب المتوسطات على الدوال القياسية السريعة مثل AVERAGEIFS أو الجداول المحورية المتقدمة (Pivot Tables). أما إذا كان المحلل مجبراً على التعامل مع مصنفات تاريخية أو تقارير تشغيلية تعتمد بالفعل على التلوين اليدوي غير القابل للتعديل الهيكلي، فإن توظيف الدوال المخصصة عبر VBA كما فُصّلت في هذا الدليل يُمثل الحل التقني الأمثل والأكثر توازناً للجمع بين المرونة الحسابية والكفاءة الإحصائية.

المراجع

• Alexander, M., & Kusleika, D. (2019). Excel 2019 Power Programming with VBA. John Wiley & Sons. https://www.wiley.com/en-us/Excel+2019+Power+Programming+with+VBA-p-9781119514923
• Microsoft Corporation. (n.d.). Range.Interior property (Excel). Microsoft Learn. https://learn.microsoft.com/en-us/office/vba/api/excel.range.interior
• Microsoft Corporation. (n.d.). AVERAGEIFS function. Microsoft Support. https://support.microsoft.com/en-us/office/averageifs-function-489df123-da84-445b-977e-5653dad47472
• Walkenbach, J. (2015). Microsoft Excel 2016 Bible. John Wiley & Sons. https://www.wiley.com/en-us/Excel+2016+Bible-p-9781119067511
• Wickham, H. (2014). Tidy data. Journal of Statistical Software, 59(10), 1–23. https://doi.org/10.18637/jss.v059.i10