كيفية حساب عدد التكرارات في إكسيل

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

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

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

1. مقدمة منهجية حول تحليل تكرار البيانات في إكسيل

1.1 أهمية قياس التكرارات في تحليل البيانات الإحصائية

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

علاوة على ذلك، يلعب رصد العناصر الأكثر شيوعاً وتكراراً دوراً حاسماً في اتخاذ القرارات القائمة على البيانات (Data-Driven Decision Making)؛ إذ تعتمد استراتيجيات سلاسل الإمداد، على سبيل المثال، على تحديد المنتجات ذات معدلات التكرار الشرائي المرتفعة لتطبيق نماذج المخزون الآمن بدقة. وفي مجال إدارة الجودة، يسهم قياس التكرار في تتبع الشكاوى وتصنيف العيوب التشغيلية بحسب أولوية ظهورها وفق مبدأ باريتو (قاعدة 80/20)، مما يوجه الموارد المؤسسية المحدودة نحو معالجة المشكلات ذات الأثر التراكمي الأكبر.

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

1.2 نظرة عامة على الأدوات والدوال الرياضية المتاحة في إكسيل

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

ومع تطور محرك الحسابات في الإصدارات الحديثة من إكسيل (Microsoft 365 وExcel 2021 وما تلاها)، أحدثت مصفوفات الدوال الديناميكية (Dynamic Array Formulas) نقلة نوعية في التعامل مع التكرارات، وتحديداً مع ظهور دالة UNIQUE التي تتيح عزل القيم المنفصلة وتمريرها مباشرة إلى دوال العد التكراري دون الحاجة للتدخل اليدوي. كما توفر أدوات التلخيص التفاعلية، وعلى رأسها الجداول المحورية (Pivot Tables)، مساراً بصرياً وتحليلياً فائق الكفاءة لتلخيص ملايين نقاط البيانات وتجميع التكرارات وتوليد النسب المئوية في ثوانٍ معدودة دون كتابة معادلة رياضية واحدة.

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

2. الاستخدام الأساسي للدالة COUNTIF لحساب التكرارات الفردية

2.1 بنية الدالة COUNTIF وصيغتها الرياضية

تعد الدالة COUNTIF الأداة الأساسية والأكثر شيوعاً في إكسيل لحساب عدد الخلايا التي تستوفي معياراً محدداً ضمن نطاق معين. تعتمد الدالة في بنائها الهيكلي على صيغة ثنائية الوسائط تأخذ الشكل القياسي التالي: =COUNTIF(range, criteria). تمثل الوسيطة الأولى (range) النطاق الجغرافي للخلايا المراد مسحها والتحقق من محتواها، بينما تعبر الوسيطة الثانية (criteria) عن المعيار أو الشرط الذي يحدد ما إذا كانت الخلية ستدخل ضمن التعداد التراكمي أم سيتم استبعادها.

من الأهمية بمكان في البناء البرمجي للمعادلات فهم الفارق الجوهري بين المراجع النسبية والمراجع المطلقة عند تحديد وسيطة النطاق. فعند الرغبة في تعميم معادلة التكرار على طول عمود تحليلي موازٍ، يجب تثبيت حدود النطاق المصدر باستخدام رمز علامة الدولار (مثل $A$2:$A$100)، وذلك لمنع انزلاق النطاق نحو الأسفل أثناء عملية السحب والتعبئة التلقائية. إن إغفال التثبيت المطلق يؤدي إلى حدوث أخطاء حسابية صامتة، حيث يتغير مجال البحث تدريجياً مع كل صف جديد، مما يسفر عن نتائج تكرار منقوصة وغير دقيقة علمياً.

لتوضيح ذلك بمثال عملي بسيط: إذا كان لدينا جدول مبيعات يحتوي على أسماء مندوبي التسويق في العمود A من الصف 2 إلى الصف 50، ونرغب في حساب عدد العمليات التي أنجزها المندوب “أحمد”، فإننا نصيغ المعادلة كالتالي: =COUNTIF($A$2:$A$50, "أحمد"). في هذه الحالة، يقوم المحرك الحسابي بمسح الخلايا الـ 49 بدقة ومقارنة القيمة النصية لكل خلية بالمعيار المحدد، ليعيد النتيجة الرقمية الإجمالية التي تمثل تكرار هذا الاسم في السجل المحدد.

2.2 تطبيق الدالة على البيانات النصية والرقمية

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

أما عند تطبيق الدالة على البيانات الرقمية والزمنية وقيم الوقت، فإن المعايير تتجاوز المطابقة التامة لتشمل المعاملات المنطقية للمقارنة الرياضية. فلحساب عدد المعاملات التي تتجاوز قيمتها 5000 دولار، تُكتب المعادلة بالصيغة: =COUNTIF($B$2:$B$100, ">5000")، حيث يُدمج المعامل المنطقي مع الرقم داخل علامات التنصيص. وعند الربط مع مراجع الخلايا التي تحتوي على قيم العتبة الرقمية، يُستخدم معامل الربط النصي (Ampersand &) لدمج الرمز المنطقي مع مرجع الخلية، مثل: =COUNTIF($B$2:$B$100, ">=" & D1).

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

2.3 التعامل مع الخلايا الفارغة والقيم المنطقية

يمثل التعامل مع الفراغات والقيم المنطقية داخل مصفوفات البيانات تحدياً برمجياً يتطلب فهماً دقيقاً لسلوك محرك إكسيل الداخلي. عند الرغبة في حساب تكرار الخلايا التي تحتوي على أي محتوى نصي مع استبعاد الخلايا الفارغة تماماً، يمكن تمرير رمز النجمة كمعيار نصي: =COUNTIF(A2:A100, "*"). تضمن هذه الصياغة حصر النصوص واستبعاد الأرقام والفراغات المطلقة، بينما يؤدي استخدام المعيار "" إلى عد كافة الخلايا غير الفارغة بصرف النظر عما إذا كانت تحتوي على نصوص أو أرقام أو أخطاء حسابية.

وعلى النقيض من ذلك، إذا تطلب التحليل الإحصائي حصر الفراغات الصريحة داخل نطاق البيانات، يمكن استخدام المعيار "" ضمن المعادلة: =COUNTIF(A2:A100, "")، أو الاستعانة بالدالة المتخصصة COUNTBLANK(A2:A100). تجدر الإشارة هنا إلى أن الخلايا التي تحتوي على سلاسل نصية فارغة ناتجة عن معادلات مسبقة (مثل IF(condition, value, "")) ستُعامل كخلايا فارغة بواسطة COUNTIF عند استخدام معيار الفراغ، ولكنها قد تُعامل كنصوص عند استخدام أدوات أخرى، وهو تمايز بنيوي يجب الانتباه إليه بدقة.

وفيما يتعلق بالقيم المنطقية البوليانية (TRUE و FALSE)، فإن الدالة COUNTIF تتعامل معها بكفاءة عند تمريرها كمعايير مباشرة دون علامات تنصيص، مثل =COUNTIF(A2:A100, TRUE)، أو حتى عند وضعها بين علامات تنصيص "TRUE"؛ إذ يمتلك محرك إكسيل قدرة التحويل الضمني بين التمثيلات النصية والمنطقية لهذه الثوابت، مما يضمن دقة حصر مؤشرات التحقق الشرطي وحالات الإنجاز الثنائية في قواعد البيانات التشغيلية.

3. الدمج المتقدم بين الدالتين UNIQUE وCOUNTIF لإنشاء ملخصات تكرار ديناميكية

3.1 آلية عمل الدالة UNIQUE في استخراج القيم الفريدة

أحدث إطلاق محرك الحسابات الديناميكي في إكسيل ثورة جذرية في منهجيات معالجة البيانات من خلال مفهوم “المصفوفات المنسكبة” (Spill Arrays). وتأتي دالة UNIQUE في طليعة هذه الدوال المتقدمة، حيث تعمل على فحص نطاق البيانات المدخل بالكامل وتجريده من كافة العناصر المكررة لتعيد مصفوفة عمودية أو أفقية تتألف حصراً من القيم المستقلة غير المتكررة دون الحاجة إلى اللجوء لأدوات التصفية المتقدمة أو الأكواد البرمجية المعقدة.

تتميز الدالة UNIQUE بالقدرة على التكيف التلقائي مع تمدد وتقلص مجموعات البيانات؛ فعند كتابة المعادلة بالصيغة: =UNIQUE(A2:A100) في خلية مفردة (لتكن D2 مثلاً)، تفيض النتائج تلقائياً في الخلايا المجاورة للأسفل لتغطي كامل عدد القيم الفريدة المستخرجة. وإذا أُضيفت سجلات جديدة إلى النطاق المصدر، يُعاد تقييم المعادلة آنياً وتتمدد المصفوفة المنسكبة تلقائياً لتستوعب المدخلات الجديدة دون أي تدخل يدوي لإعادة صياغة النطاقات.

يتيح هذا السلوك الديناميكي التخلص النهائي من الطرق التقليدية المرهقة التي كانت تعتمد على استخراج القوائم الفريدة يدوياً عبر أمر “إزالة التكرارات” (Remove Duplicates) الثابت، مما يوفر بيئة عمل مؤتمتة بالكامل ومهيأة للربط المباشر مع دوال التحليل الإحصائي الأخرى لبناء لوحات قياس وتقارير تكرارية تتحدث ذاتياً وفورياً.

3.2 بناء جدول تكراري متكامل خطوة بخطوة

لبناء جدول توزيع تكراري آلي فائق المرونة، نبدأ أولاً بإعداد هيكل التحليل في ورقة العمل من خلال تحديد عمود البيانات المصدرية، وليكن العمود A2:A1000 الذي يضم أسماء الفئات الإنتاجية في مصنع صناعي. في الخلية D2، نقوم بكتابة دالة استخراج القيم الفريدة: =UNIQUE(A2:A1000)، لتتولد لدينا على الفور قائمة نقية تحتوي على كافة الفئات دون أي تكرار، ممتدة من الخلية D2 حتى نهاية القيم المنسكبة.

في الخطوة التالية، ننتقل إلى الخلية المجاورة E2 لحساب تكرار كل فئة من الفئات المستخرجة، وهنا نوظف مشغل نطاق الانسكاب الفائق (Spill Range Operator #). نكتب المعادلة التالية: =COUNTIF($A$2:$A$1000, D2#). إن استخدام علامة الهاشتاج (#) بعد مرجع الخلية الأولى للمصفوفة المنسكبة يوجه الدالة COUNTIF لتنفيذ عملية العد التكراري لكل عنصر موجود في مصفوفة UNIQUE دفعة واحدة، مما يولد مصفوفة تكرارات موازية تفيض تلقائياً على كامل طول النطاق دون الحاجة لسحب المعادلة يدوياً إلى الخلايا السفلية.

يتميز هذا النموذج المعماري بتناغم كلي وترابط ديناميكي مستمر؛ ففي حال ظهور فئة إنتاجية جديدة كلياً في العمود A، ستلتقطها دالة UNIQUE تلقائياً وتدرجها في قائمة القيم المستقلة، وبدوره سيتوسع نطاق الانسكاب في D2#، مما يدفع دالة COUNTIF المنسكبة في E2 إلى حساب تكرار الفئة الجديدة آنياً وتحديث الجدول التكراري بأكمله في أجزاء من الثانية.

3.3 تحليل وتفسير مخرجات الجدول التكراري

بعد اكتمال بناء الجدول التكراري الديناميكي، ينتقل المحلل إلى مرحلة استنطاق الأرقام وتحويل التكرارات المطلقة إلى مؤشرات إحصائية نسبية ذات دلالة عملية. يمكن إضافة عمود ثالث في الخلية F2 لحساب “التكرار النسبي المئوي” (Relative Frequency) بقسمة مصفوفة التكرارات على المجموع الكلي للبيانات باستخدام المعادلة المنسكبة: =E2# / SUM(E2#) وتنسيق الناتج كنسبة مئوية، مما يتيح معرفة الحصة النسبية التي يشغلها كل عنصر من إجمالي النشاط التشغيلي.

لتحديد العناصر الأكثر والأقل تكراراً بصورة فورية، يمكن دمج مخرجات الجدول مع دالة SORT المتقدمة لإعادة ترتيب التكرارات تنازلياً. على سبيل المثال، يمكن صياغة مصفوفة موحدة ترتب الفئات التكرارية من الأعلى إلى الأدنى باستخدام المعادلة: =SORT(HSTACK(UNIQUE(A2:A1000), COUNTIF(A2:A1000, UNIQUE(A2:A1000))), 2, -1)، مما يفرز البيانات فورياً بحسب عمود التكرار الثاني ويبرز الفئات المهيمنة على نحو واضح وجلي.

ختاماً لهذه المرحلة التحليلية، يتم ربط المصفوفات التكرارية الناتجة بمخططات بيانية تفاعلية (Dynamic Charts) مثل المخططات الشريطية (Bar Charts) أو الدائرية (Pie Charts). تضمن هذه المخططات المرتبطة بنطاقات الانسكاب تحديث أشرطتها وقطاعاتها تلقائياً مع تغير وتوسع البيانات المصدرية، مما يمنح الإدارة التنفيذية أداة بصرية حية لمراقبة التغيرات الهيكلية في تردد البيانات دون الحاجة لإعادة ضبط نطاقات المخططات يدوياً في كل دورة تقريرية.

4. حساب التكرارات بناءً على معايير متعددة باستخدام الدالة COUNTIFS

4.1 الصيغة التركيبية للدالة COUNTIFS ومتغيراتها

عندما تتجاوز متطلبات التحليل الإحصائي حدود المعيار الفردي وتتطلب تقييم ظواهر مشروطة بعوامل وظروف متعددة متزامنة، تبرز الدالة COUNTIFS كحل قياسي قوي ومصمم خصيصاً لهذا الغرض. تتيح هذه الدالة تقييم ما يصل إلى 127 زوجاً من النطاقات والمعايير ضمن صيغة تركيبية واحدة، تأخذ الشكل الرياضي الموسع: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...).

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

من المحاذير المنهجية الجوهرية عند صياغة معادلات COUNTIFS ضرورة التطابق التام في الأبعاد الهندسية لكافة النطاقات المدخلة؛ إذ يجب أن تحتوي جميع وسائط criteria_range على نفس العدد الدقيق من الصفوف والأعمدة. وأي خلل في تماثل أبعاد النطاقات (كأن يكون النطاق الأول A2:A100 والنطاق الثاني B2:B90) سيؤدي مباشرة إلى إطلاق خطأ القيمة الشهير (#VALUE!)، نظراً لعجز المحرك الحسابي عن إنشاء تقاطع مصفوفي متكافئ بين السجلات غير المتطابقة في الطول.

4.2 تطبيقات عملية على الشروط المتعددة المتقاطعة

تتعدد التطبيقات المهنية لدالة COUNTIFS لتغطي قطاعات الأعمال المتنوعة، وتحديداً في مجالات التحليل الزمني والجغرافي والوظيفي. لنفترض وجود قاعدة بيانات للموارد البشرية تضم أعمدة للفرع الجغرافي (العمود A)، والقسم الوظيفي (العمود B)، وسنوات الخبرة (العمود C)، وتاريخ التعيين (العمود D). لحساب عدد الموظفين في فرع “دبي”، التابعين لقسم “تكنولوجيا المعلومات”، والذين تتجاوز خبرتهم 5 سنوات، نصيغ المعادلة كالتالي:

=COUNTIFS($A$2:$A$500, "دبي", $B$2:$B$500, "تكنولوجيا المعلومات", $C$2:$C$500, ">5")

تتجلى القوة التحليلية للدالة أيضاً عند تقييد التكرارات بنطاقات زمنية محددة، وهو متطلب جوهري في إعداد التقارير المالية والربع سنوية. لحساب عدد المبيعات المنجزة لعميل معين بين تاريخين محددين (مثلاً من 1 يناير 2024 إلى 31 مارس 2024)، نكرر النطاق الزمني كمعيارين منفصلين لتمثيل الحد الأدنى والأعلى للفترة:

=COUNTIFS($A$2:$A$1000, "الشركة المتحدة", $D$2:$D$1000, ">=2024-01-01", $D$2:$D$1000, "<=2024-03-31")

تسهم هذه الصياغة المتقاطعة في تصفية البيانات بدقة مجهرية عبر استبعاد كافة السجلات الخارجية وتوفير حصر تكراري دقيق يلبي متطلبات الرقابة والتدقيق في مختلف العمليات الإدارية والمالية.

4.3 محاكاة المعامل المنطقي (OR Logic) في حساب التكرارات

نظراً لأن الدالة COUNTIFS مبنية داخلياً على منطق (AND)، فإنها تعجز بصورتها الافتراضية المباشرة عن معالجة الشروط الانفصالية المتبادلة (OR Logic)؛ كأن نطلب حساب عدد العمليات التي تمت في فرع “الرياض” أو فرع “جدة”. لحل هذا التحدي البنيوي دون الحاجة لكتابة معادلات مكررة متباعدة، نلجأ إلى تقنية تمرير المصفوفات الثابتة (Array Constants) داخل معيار الدالة.

تتم هذه التقنية بتضمين الخيارات البديلة بين قوسي مصفوفة {"الرياض", "جدة"} داخل وسيطة المعيار، لتصبح المعادلة: COUNTIFS($A$2:$A$500, {"الرياض", "جدة"}). في هذه الحالة، ستعيد الدالة مصفوفة ثنائية تحتوي على تكرار فرع الرياض كعنصر أول وتكرار فرع جدة كعنصر ثانٍ. ولتجميع هذين التكرارين في رقم إجمالي واحد يمثل شرط (OR)، نغلف المعادلة بأكملها بدالة المجموع SUM أو SUMPRODUCT كالتالي:

=SUM(COUNTIFS($A$2:$A$500, {"الرياض", "جدة"}))

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

5. استخدام الجداول المحورية (Pivot Tables) لحساب التكرارات تلقائياً

5.1 إنشاء جدول محوري لتلخيص تكرارات الأعمدة

تعتبر الجداول المحورية (Pivot Tables) الأداة التحليلية الأكثر فاعلية وقوة في إكسيل للتعامل مع تلخيص التكرارات الإحصائية دون كتابة أي صيغ رياضية يدوية. تبدأ العملية بتحديد نطاق البيانات المصدرية، مع التأكد التام من خلو الصفوف العلوية من أي دمج وتسمية كافة الأعمدة برؤوس واضحة وغير مكررة، ثم التوجه إلى علامة التبويب “إدراج” (Insert) واختيار “جدول محوري” ليتم إنشاؤه في ورقة عمل جديدة أو محددة.

لتوليد ملخص التكرارات لمتغير معين (مثل أسماء المنتجات)، يقوم المحلل بسحب حقل اسم المنتج من قائمة الحقول وإفلاته في منطقة “الصفوف” (Rows)، ثم سحب نفس الحقل وإفلاته مرة ثانية في منطقة “القيم” (Values). بمجرد إفلات الحقل النصي في منطقة القيم، يتعرف إكسيل تلقائياً على طبيعته غير الرقمية ويطبق عليه دالة العد (Count of Product) بشكل افتراضي، مستعرضاً قائمة فورية بكافة المنتجات وتكرار كل منها على حدة.

في حال كان الحقل المسحوب إلى منطقة القيم حقلاً رقمياً (مثل المعرف الرقمي للموظف أو كود الصنف)، فإن إكسيل يميل افتراضياً إلى تطبيق دالة المجموع (Sum). لتصحيح ذلك وتحويله إلى عد تكراري، ينقر المستخدم بزر الفأرة الأيمن على أي رقم في عمود القيم، ويختار “تلخيص القيم حسب” (Summarize Values By)، ثم يحدد خيار “العد” (Count)، ليتحول الجدول فوراً من جمع الأرقام إلى حساب وتلخيص تكرار ظهور تلك الأكواد في قاعدة البيانات.

5.2 تخصيص عرض التكرارات والنسب المئوية

تتجاوز قدرات الجداول المحورية مجرد إظهار التكرارات المطلقة لتتيح تمثيلاً تحليلياً متقدماً للتوزيعات النسبية والتراكمية. بالنقر بزر الفأرة الأيمن على عمود التكرارات واختيار “إظهار القيم كـ” (Show Values As)، يمكن للمحلل اختيار “% من الإجمالي الكلي” (% of Grand Total)؛ مما يحول الأرقام التكرارية الخام إلى نسب مئوية دقيقة تعكس الحصة الحجمية لكل بند من النشاط الكلي للمنظومة.

كما تدعم الجداول المحورية إمكانية التجميع التلقائي (Grouping) للبيانات المتكررة وفق أبعاد هيكلية مخصصة. ففي حقول التواريخ، يمكن تجميع التكرارات بضغطة زر واحدة لتظهر موزعة بحسب السنوات، أو الأرباع السنوية، أو الأشهر، أو الأيام، مما يوفر رؤية تتبعية لتكرار الأحداث عبر الزمن. وفي الحقول الرقمية المتصلة (مثل الرواتب أو المبالغ المالية)، يمكن تجميع الأرقام في فئات ومجموعات محددة المدى (مثل: من 1000 إلى 2000، ومن 2001 إلى 3000) لحساب التكرارات الفئوية وتوليد التوزيعات التكرارية الطبيعية (Histograms).

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

5.3 المقارنة التحليلية بين الدوال والجداول المحورية

تخضع المفاضلة بين استخدام الدوال الرياضية (مثل COUNTIF و COUNTIFS) واستخدام الجداول المحورية لاعتبارات هندسية تتعلق بحجم البيانات، وسرعة المعالجة، وطبيعة التحديث المطلوبة. توفر الجداول المحورية كفاءة معالجة فائقة وسرعة استثنائية في تلخيص قواعد البيانات الضخمة التي تحتوي على مئات الآلاف من السجلات، حيث يتم معالجة البيانات عبر محرك الذاكرة المؤقتة (Pivot Cache)، مما يقلل العبء الحسابي الواقع على المعالج مقارنة بالدوال الرياضية الثقيلة.

في المقابل، تمتاز الحلول المعتمدة على الدوال بخاصية التحديث التلقائي واللحظي (Real-time Calculation)؛ فبمجرد تعديل أو إضافة أي قيمة في السجلات المصدرية، تنعكس التكرارات المحسوبة بالمعادلات فورياً في خلايا المخرجات. أما الجداول المحورية، فإنها تظل محتفظة باللقطة السابقة للبيانات وتتطلب إجراء عملية “تحديث” يدوية أو برمجية (Refresh) لقراءة التعديلات الجديدة، مما قد يشكل عائقاً في لوحات المعلومات التشغيلية اللحظية.

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

6. تقنيات حساب التكرارات الحساسة لحالة الأحرف (Case-Sensitive Counting)

6.1 القيود المنهجية للدوال الافتراضية في معالجة النصوص

تستند الدوال الإحصائية والشرطية القياسية في إكسيل (مثل COUNTIF و COUNTIFS و VLOOKUP و MATCH) إلى محرك مقارنة نصية يتجاهل بطبيعته حالة الأحرف اللاتينية (Case-Insensitive)؛ حيث تعامل هذه الدوال النصوص المكتوبة بأحرف كبيرة (Uppercase) وتلك المكتوبة بأحرف صغيرة (Lowercase) كقيم متطابقة تماماً. فعلى سبيل المثال، لا ترى دالة COUNTIF أي فارق بين الرمز الكودي "CODE-A" والرمز "code-a" أو "Code-A".

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

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

6.2 توظيف الدالة EXACT مع دالة SUMPRODUCT

تعد الدالة EXACT الأداة القياسية المتخصصة في إكسيل لإجراء المقارنات النصية الحساسة لحالة الأحرف؛ حيث تقارن بين سلسلتين نصيتين وتعيد القيمة المنطقية TRUE في حال التطابق التام فقط (بما في ذلك حالة الأحرف والمسافات)، والقيمة FALSE في حال وجود أي اختلاف مهما كان طفيفاً. لكن الدالة EXACT بطبيعتها مصممة لمقارنة قيمتين مفردتين، ولا تستطيع وحدها حساب التكرارات عبر نطاق مصفوفي واسع.

لحل هذه المعضلة، يتم دمج الدالة EXACT داخل دالة الضرب المصفوفي المتقدمة SUMPRODUCT. تعمل دالة EXACT على مقارنة القيمة المستهدفة (مثلاً "Apple") بكامل النطاق المصدر $A$2:$A$100، لتنتج مصفوفة داخلية من القيم البوليانية (TRUE و FALSE). ثم يتم استخدام المعامل الأحادي المزدوج (Double Unary --) لتحويل هذه القيم المنطقية إلى مصفوفة رقمية تتكون من الواحد الصحيح (1) للقيم المتطابقة، والصفر (0) للقيم غير المتطابقة.

تأخذ المعادلة التركيبية النهائية الشكل التالي:

=SUMPRODUCT(--EXACT("Apple", $A$2:$A$100))

تقوم دالة SUMPRODUCT بعد ذلك بجمع الآحاد الناتجة داخل المصفوفة الحسابية، لتعيد التكرار الدقيق والحصري للكلمة بحالتها المحددة، مستبعدة تماماً أي تكرارات أخرى تختلف في حالة الأحرف مثل "apple" أو "APPLE"، مما يحقق دقة متناهية في الفرز والتحليل الإحصائي للبيانات الحساسة.

6.3 استخدام المعاملات المصفوفية الحديثة للعد الحساس للحالة

مع إدخال المحرك المصفوفي الديناميكي، أصبح بالإمكان تبسيط وتطوير صياغات العد الحساس لحالة الأحرف بالاعتماد على دالة SUM التقليدية المدمجة مع EXACT دون الحاجة الإلزامية لدالة SUMPRODUCT في الإصدارات الحديثة. تُكتب المعادلة ببساطة كالتالي:

=SUM(--EXACT(C2, $A$2:$A$1000))

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

من أفضل الممارسات المتبعة في هذا السياق اختبار أداء المعادلات الحساسة للحالة عند التعامل مع جداول تتجاوز 50,000 سجل، والتأكد من تثبيت النطاقات المصدرية لتفادي الاستهلاك الزائد للذاكرة. كما يجب توثيق هذه المعادلات في دليل المعايير الخاص بالمؤسسة نظراً لأن المستخدمين العاديين قد يخلطون بين سلوك COUNTIF الافتراضي وسلوك هذه الدوال المصفوفية المتخصصة.

7. حساب التكرارات الجزئية والأنماط النصية باستخدام الرموز البديلة (Wildcards)

7.1 دلالات واستخدامات الرموز البديلة في إكسيل

توفر الرموز البديلة (Wildcard Characters) في إكسيل مرونة استثنائية عند البحث وحساب التكرارات للنصوص غير المتطابقة كلياً، أو البيانات التي تتبع نمطاً نصياً معيناً دون مطابقة حرفية تامة. يدعم إكسيل ثلاثة رموز بديلة رئيسية تؤدي وظائف محددة بدقة في بناء معايير البحث الإحصائي:

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

تعمل هذه الرموز البديلة بتوافق كامل داخل وسائط المعايير في دوال التكرار القياسية مثل COUNTIF و COUNTIFS، مما يحولها من أدوات مطابقة جامدة إلى محركات بحث نمطي قادرة على التقاط الهياكل النصية المعقدة.

7.2 بناء معايير مطابقة النصوص الجزئية

تتعدد أشكال المعايير النمطية وفقاً للموضع المستهدف للمقطع النصي المراد حساب تكراره. لحساب عدد السجلات التي تبدأ ببادئة محددة، مثل حساب المنتجات التي تبدأ بكود التصنيع "EXP-"، نضع رمز النجمة في نهاية المعيار: =COUNTIF(A2:A500, "EXP-*"). تضمن هذه المعادلة عد أي نص يبدأ بهذه الحروف الأربعة بصرف النظر عن طبيعة المحارف التي تليه أو عددها.

وعلى النقيض من ذلك، لحساب النصوص التي تنتهي بلاحقة معينة (مثل أسماء النطاقات التي تنتهي بالامتداد ".org")، يُوضع رمز النجمة في مقدمة المعيار: =COUNTIF(A2:A500, "*.org"). أما للبحث عن كلمة أو مقطع نصي يقع في أي موضع داخل الخلية (سواء في البداية أو الوسط أو النهاية)، يُحاط المقطع بالنجمات من الجانبين: =COUNTIF(A2:A500, "*مستعجل*")، لحصر كافة السجلات التي تشتمل على هذا الوصف الحرج.

عند بناء نماذج ديناميكية ترتبط بقيم مدخلة في خلايا متغيرة، يتم استخدام معامل الربط النصي (&) لدمج الرموز البديلة مع مرجع الخلية. فإذا كانت الكلمة المستهدفة مدخلة في الخلية C2، نصيغ المعادلة كالتالي:

=COUNTIF($A$2:$A$500, "*" & C2 & "*")

أما إذا أردنا حساب تكرار الأكواد المكونة من خمسة محارف وتبدأ بالحرف “A” وتنتهي بالحرف “Z” مع وجود ثلاثة محارف متغيرة في الوسط، نوظف علامة الاستفهام: =COUNTIF(A2:A500, "A???Z")، مما يحقق عزلاً دقيقاً للأنماط الهيكلية المنضبطة.

7.3 معالجة التحديات المرتبطة بالرموز البديلة

تفرض المعالجة بالرموز البديلة تحديات فنية عند احتواء البيانات المصدرية على علامات النجمة أو الاستفهام كجزء أصيل من النص الفعلي (مثل أسماء بعض المنتجات التي تحمل الرمز * للدلالة على التميز، أو التساؤلات المنتهية بعلامة ?). إذا تم تمرير المعيار "*" مباشرة، ستفهمه الدالة كأمر بعدّ كافة الخلايا النصية، ولن تخص بالعد تلك التي تحتوي على الرمز الحرفي نفسه.

لحل هذه المعضلة والبحث عن رمز النجمة الحقيقي، نستخدم رمز التلدة (~) يليه الرمز المستهدف مباشرة، فتُكتب المعادلة كالتالي: =COUNTIF(A2:A500, "*~**") لحساب الخلايا التي تتضمن نجمة فعلية في أي موضع، أو =COUNTIF(A2:A500, "*~?*") لحساب الخلايا التي تشتمل على علامة استفهام حقيقية.

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

8. النماذج المصفوفية المتقدمة: توظيف الدالة SUMPRODUCT لحساب التكرارات المعقدة

8.1 الأسس الرياضية لدالة SUMPRODUCT في العمليات المنطقية

تعتبر الدالة SUMPRODUCT إحدى أقوى وأعرق الدوال التحليلية في بيئة إكسيل؛ حيث تمتلك القدرة الأصلية على معالجة المصفوفات الرياضية وإجراء العمليات المنطقية التكرارية دون الحاجة للضغط على مفاتيح الإدخال المصفوفي التقليدية (Ctrl + Shift + Enter). تقوم الفلسفة الرياضية للدالة على ضرب المصفوفات المتناظرة عنصراً بعنصر، ثم جمع حواصل الضرب التراكمية لتوليد قيمة عددية مفردة تمثل المحصلة النهائية.

عند توظيف الدالة لحساب التكرارات المعقدة، تُحول الشروط المنطقية المطبقة على النطاقات إلى مصفوفات ثنائية منطقية من القيم (TRUE و FALSE). ونظراً لأن العمليات الحسابية تتطلب قيماً عددية، يتم استخدام المعامل الأحادي المزدوج (Double Unary Operator --) أو ضرب الشروط المنطقية ببعضها البعض لإجبار محرك إكسيل على تحويل TRUE إلى 1، و FALSE إلى 0.

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

8.2 حساب التكرارات المعتمدة على دوال أخرى داخلية

تبرز القوة المطلقة لدالة SUMPRODUCT عند الحاجة لحساب التكرارات بناءً على خصائص مشتقة من البيانات لا تتوفر كأعمدة مساعدة في الجدول الأصلي. على سبيل المثال، إذا أردنا حساب عدد المعاملات التي تمت في يوم “الجمعة” حصراً من عمود يحتوي على تواريخ كاملة (العمود A2:A500)، تعجز دالة COUNTIF عن استخلاص أيام الأسبوع ضمن معيارها المباشر، بينما تنجز SUMPRODUCT المهمة بسلاسة عبر دمج دالة WEEKDAY داخلها:

=SUMPRODUCT(--(WEEKDAY($A$2:$A$500) = 6))

وبالمثل، لحساب عدد السجلات التي يتجاوز فيها طول النص المدخل في عمود الأسماء 15 محرفاً (لرصد أخطاء إدخال البيانات أو قياس كفاءة الحقول)، ندمج دالة LEN داخل التركيب المصفوفي كالتالي:

=SUMPRODUCT(--(LEN($B$2:$B$500) > 15))

كما يمكن تطبيق شروط متعددة تجمع بين الشهور وأطوال النصوص والشروط الرقمية المتقاطعة في معادلة واحدة بالغة الأناقة: =SUMPRODUCT((MONTH($A$2:$A$500) = 5) * (YEAR($A$2:$A$500) = 2024) * ($C$2:$C$500 > 10000))، حيث يعمل مشغل الضرب (*) كمعامل عطف منطقي (AND) يحول كافة الشروط إلى أصفار وآحاد مصفوفية تُجمع تلقائياً لتعكس التكرار الصافي المستوفي لكافة المتغيرات.

8.3 حساب عدد القيم الفريدة الكلي داخل نطاق معين

قبل ظهور دالة UNIQUE في الإصدارات الحديثة، كانت المسألة الرياضية الأكثر شهرة في تاريخ إكسيل هي كيفية حساب إجمالي عدد القيم الفريدة (Total Unique Values Count) دون تكرار داخل نطاق يحتوي على مدخلات مكررة. وقد ابتكر خبراء التحليل الصيغة المصفوفية الكلاسيكية الخالدة التي تعتمد على المفهوم الرياضي المقلوب لتردد العناصر:

=SUMPRODUCT(1 / COUNTIF(data_range, data_range))

يعتمد المنطق الرياضي لهذه المعادلة العبقرية على أن كل عنصر يظهر في النطاق بمعدل $N$ من المرات، ستقوم دالة COUNTIF بحساب تكراره كـ $N$. وعند قسمة الرقم 1 على $N$ لكل ظهور من ظهورات العنصر، فإننا نجمع الكسر $\frac{1}{N}$ لعدد $N$ من المرات، ليكون الناتج الإجمالي لهذا العنصر مساوياً تماماً للعدد الصحيح (1). وبتكرار هذه العملية على كافة العناصر، يؤول المجموع التراكمي في SUMPRODUCT إلى العدد الإجمالي للقيم الفريدة بدقة رياضية مطلقة.

ومع ذلك، تواجه هذه الصيغة الكلاسيكية خطأ القسمة على صفر (#DIV/0!) في حال وجود أي خلية فارغة ضمن النطاق. ولتأمين المعادلة ومعالجة الفراغات بمرونة، يتم تعديل الصياغة بإضافة سلسلة نصية فارغة لوسائط الدالة وتطبيق دالة التحقق من الفراغ كالتالي:

=SUMPRODUCT((data_range "") / (COUNTIF(data_range, data_range & "") + (data_range = "")))

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

9. حساب عدد تكرار كلمة أو حرف معين داخل خلية نصية مفردة

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

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

يقوم هذا المنطق الرياضي على مبدأ “القياس التفاضلي لطول النص” (Differential Length Analysis). وتتلخص فكرته في الخطوات المتسلسلة التالية:

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

9.2 صياغة المعادلات لحساب تكرار الحروف والكلمات

لحساب تكرار حرف فردي واحد (وليكن الحرف “أ”) داخل الخلية A2، نطبق المعادلة المباشرة التالية المستندة إلى المنطق التفاضلي:

=LEN(A2) - LEN(SUBSTITUTE(A2, "أ", ""))

في هذه الحالة، بما أن طول الحرف المحذوف يساوي واحداً صحيحاً، فإن فارق الطول يمثل عدد مرات تكرار الحرف داخل الخلية بدقة تامة.

أما عند الرغبة في حساب تكرار “كلمة” كاملة تتألف من عدة أحرف (مثل كلمة “بيانات” المكونة من 6 أحرف) داخل الخلية A2، فإن حذف الكلمة سيقلص طول النص بمقدار 6 محارف في كل مرة تظهر فيها. لذا، يجب قسمة الفارق الإجمالي للأطوال على الطول الفعلي للكلمة المبحوث عنها لضبط الناتج الحسابي، كما في المعادلة التالية:

=(LEN(A2) - LEN(SUBSTITUTE(A2, "بيانات", ""))) / LEN("بيانات")

تجدر الإشارة إلى أن الدالة SUBSTITUTE حساسة بطبيعتها لحالة الأحرف (Case-Sensitive) في النصوص اللاتينية. فإذا أردنا حساب تكرار كلمة “Excel” بصرف النظر عن كتابتها بأحرف كبيرة أو صغيرة، يجب توحيد النص الأصلي وكلمة البحث باستخدام دالة LOWER أو UPPER قبل إجراء عملية الاستبدال، لتصبح المعادلة كالتالي:

=(LEN(A2) - LEN(SUBSTITUTE(LOWER(A2), LOWER("Excel"), ""))) / LEN("Excel")

9.3 تطبيقات عملية في تحليل البيانات النصية غير المهيكلة

تتعدد التطبيقات التحليلية لهذه التقنية النصية في بيئات الأعمال الفعلية؛ ومن أبرزها حساب عدد العناصر المدرجة ضمن خلية واحدة مفصولة بفواصل (Comma-Separated Values). لحساب عدد العناصر داخل الخلية A2 التي تحتوي على قائمة أسماء مفصولة بفاصلة، نقوم بحساب عدد الفواصل وإضافة الرقم (1) للحصول على إجمالي العناصر:

=IF(ISBLANK(A2), 0, LEN(A2) - LEN(SUBSTITUTE(A2, ",", "")) + 1)

وفي مجال تحليل استطلاعات الرأي والتعليقات المفتوحة للعملاء، تفيد هذه المعادلات في قياس الكثافة اللفظية (Keyword Density) لبعض الكلمات الدلالية الحرجة (مثل: “عطل”، “تأخير”، “ممتاز”)، لتقييم مستويات الرضا وتحديد الأنماط السلوكية الأكثر تكراراً في آراء المستهلكين.

كما يمكن تعميم هذه المعادلة عبر مصفوفة العمود بأكمله لحساب إجمالي تكرار كلمة معينة داخل مئات الخلايا النصية المجمعة دفعة واحدة، بتغليف الصياغة داخل دالة SUMPRODUCT كالتالي:

=SUMPRODUCT((LEN(A2:A100) - LEN(SUBSTITUTE(LOWER(A2:A100), LOWER("جودة"), ""))) / LEN("جودة"))

يوفر هذا النموذج أداة قوية لاستخلاص الإحصاءات النصية من السجلات المجمعة بكفاءة ودون الحاجة لتجزئة النصوص إلى أعمدة منفصلة.

10. استخدام أداة Power Query لتحليل وتجميع التكرارات في البيانات الضخمة

10.1 استيراد البيانات وتحويلها عبر محرر Power Query

تمثل أداة Power Query المحرك الأقوى في إكسيل لعمليات الاستخراج والتحويل والتحميل (ETL)، وهي مصممة للتعامل بكفاءة خارقة مع مجموعات البيانات الضخمة التي قد تؤدي معالجتها بالمعادلات التقليدية إلى تجميد المصنف وبطء النظام. تبدأ العملية باستيراد البيانات من مصادر متعددة (مثل مصنفات إكسيل الأخرى، قواعد بيانات SQL، ملفات CSV، أو الجداول الداخلية) عبر تبويب “بيانات” (Data) واختيار “من جدول/نطاق” (From Sheet/Table).

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

كما يتيح المحرر فصل الأعمدة المدمجة (Split Columns) التي تحتوي على قيم متعددة مفصولة بمحددات، وتحويلها إلى صفوف مستقلة عبر أمر التبديل المصفوفي (Unpivot)، مما يهيئ البيانات غير المهيكلة ويحولها إلى جدول معياري جاهز لعمليات الحصر الإحصائي المتقدم.

10.2 تطبيق خاصية تجميع الصفوف (Group By) لحساب التكرارات

تعد خاصية “تجميع حسب” (Group By) الأداة المركزية في Power Query لحساب التكرارات وتلخيص البيانات. بعد تحديد العمود المستهدف في واجهة المحرر، ينقر المستخدم على خيار “تجميع حسب” من علامة التبويب الرئيسية، ليفتح مربع حوار متقدم يتيح خيارين أساسيين: التجميع البسيط (Basic) والتجميع المتقدم (Advanced).

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

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

10.3 تحميل النتائج وتحديثها آلياً في ورقة العمل

بعد اكتمال بناء نموذج التجميع التكراري وضبط خطوات الاستعلام، يقوم المحلل بالنقر على “إغلاق وتحميل إلى” (Close & Load To) لاختيار مسار تصدير النتائج. يتيح Power Query تصدير الجدول الملخص إلى ورقة عمل جديدة كجدول بيانات إكسيل منظم وأنيق، أو تحميله كنموذج بيانات داخلي فقط (Data Model) للربط مع أدوات Power Pivot وعرض النتائج في لوحات معلومات تفاعلية دون إثقال خلايا الورقة.

تكمن الميزة الاستراتيجية لبيئة Power Query في قابليتها الكاملة للأتمتة والتحديث الذاتي؛ إذ يتم تسجيل كافة خطوات التحويل كمسار خوارزمي بلغة الاستعلام M Language. وعند إضافة سجلات جديدة إلى الملفات المصدرية أو تعديل البيانات القائمة، لا يتطلب الأمر سوى النقر على زر “تحديث الكل” (Refresh All) في إكسيل، ليعيد المحرك تنفيذ كافة خطوات التنظيف والتجميع وحساب التكرارات وتحميل المخرجات المحدثة بصورة فورية وموثوقة بالكامل.

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

11. أتمتة حساب التكرارات عبر لغة البرمجة VBA والدوال المخصصة (UDF)

11.1 تطوير دالة مخصصة (User-Defined Function) لحساب التكرار

توفر لغة البرمجة فيجوال بيسك للتطبيقات (VBA) أفقاً غير محدود لتطوير حلول برمجية مخصصة تتجاوز أي قيود وظيفية تفرضها الدوال الافتراضية. يمكن للمطورين بناء دوال معرفة من قبل المستخدم (UDF) لحساب التكرارات وفق خوارزميات ومنطق أعمال معقد لا يمكن تمثيله بالصيغ التقليدية في ورقة العمل.

لإنشاء دالة مخصصة تقوم بحساب تكرار النصوص بحساسية صارمة لحالة الأحرف وبمرونة فائقة، يتم فتح محرر الأكواد (Alt + F11)، وإدراج وحدة نمطية جديدة (Standard Module)، وكتابة الكود البرمجي التالي:

Function CountExactMatches(SearchRange As Range, TargetValue As String) As Long
    Dim Cell As Range
    Dim Counter As Long
    Counter = 0
    For Each Cell In SearchRange
        If StrComp(Cell.Value, TargetValue, vbBinaryCompare) = 0 Then
            Counter = Counter + 1
        End If
    Next Cell
    CountExactMatches = Counter
End Function

بعد حفظ المصنف بصيغة تدعم وحدات الماكرو (.xlsm أو .xlsb)، تصبح الدالة CountExactMatches متاحة للاستدعاء المباشر في أي خلية داخل المصنف بنفس أسلوب الدوال القياسية: =CountExactMatches(A2:A500, "TARGET"). تمتاز هذه الدالة بالاعتماد على دالة المقارنة الثنائية StrComp مع خيار vbBinaryCompare، مما يضمن دقة المطابقة الحرفية الصارمة لكافة السجلات بكفاءة برمجية عالية وسهولة في الاستخدام.

11.2 استخدام كائن Dictionary في VBA لعد التكرارات بسرعة فائقة

عند الحاجة لحساب وتلخيص تكرارات مئات الآلاف من الصفوف في تقرير منفصل بضغطة زر واحدة، يعتبر كائن القاموس البرمجي (Scripting.Dictionary) الخيار الأكثر كفاءة وسرعة على الإطلاق في بيئة VBA. يعتمد هذا الكائن على بنية بيانات قائمة على أزواج “المفتاح والعنصر” (Key-Item Pairs)، حيث تكون المفاتيح فريدة وغير قابلة للتكرار، بينما تُستخدم العناصر لتخزين العداد التكراري المرتبط بكل مفتاح.

تقوم الخوارزمية البرمجية بمسح نطاق البيانات المصدرية لمرة واحدة فقط (Single Pass Algorithm) بزمن تعقيد حوسبي خطي $O(n)$. وفيما يلي النموذج البرمجي لتطبيق هذه العملية وطباعة النتائج فورياً:

Sub GenerateFrequencyReport()
    Dim Dict As Object
    Dim DataArray As Variant
    Dim i As Long
    Dim LastRow As Long
    Dim Key As Variant
    Dim OutputRow As Long

    Set Dict = CreateObject("Scripting.Dictionary")
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    DataArray = Range("A2:A" & LastRow).Value

    For i = 1 To UBound(DataArray, 1)
        If Not IsEmpty(DataArray(i, 1)) Then
            If Dict.Exists(DataArray(i, 1)) Then
                Dict(DataArray(i, 1)) = Dict(DataArray(i, 1)) + 1
            Else
                Dict.Add DataArray(i, 1), 1
            End If
        End If
    Next i

    OutputRow = 2
    Range("D1").Value = "القيمة المستقلة"
    Range("E1").Value = "التكرار"
    For Each Key In Dict.Keys
        Cells(OutputRow, "D").Value = Key
        Cells(OutputRow, "E").Value = Dict(Key)
        OutputRow = OutputRow + 1
    Next Key
End Sub

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

11.3 مقارنة كفاءة الحلول البرمجية مقابل الصيغ الحسابية

تقدم الحلول البرمجية عبر VBA مزايا جوهرية للمؤسسات التي تدير مصنفات ضخمة ومعقدة؛ إذ تسهم في تقليل حجم الملف وتوفير استهلاك موارد المعالج بشكل كبير. فعند استبدال آلاف الخلايا الممتلئة بمعادلات COUNTIFS بمخرجات رقمية ثابتة يولدها كود ماكرو، يتحرر محرك إكسيل من عبء إعادة الحساب المستمرة عند كل نقرة أو تعديل في الورقة، مما يمنع تجمد البرامج ويضمن سلاسة العمل اليومي.

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

ومع ذلك، تتطلب الحلول البرمجية معرفة متخصصة لصيانتها وتطويرها، وتفرض اشتراطات أمان خاصة تتطلب موافقة المستخدم على تشغيل وحدات الماكرو في المؤسسة. لذا، تعتبر VBA الحل المثالي للمهام المتكررة الضخمة، وعمليات المعالجة الليلية الدورية، والأدوات الإدارية المغلقة، بينما تظل الدوال المباشرة الحل الأنسب للمهام التحليلية السريعة والتقارير التشاركية السحابية عبر منصة Excel for the Web.

12. أفضل الممارسات واستكشاف الأخطاء وإصلاحها في حساب التكرارات

12.1 معالجة الاختلافات غير المرئية في البيانات النصية

تعد مشكلات النصوص غير المرئية والاختلافات التنسيقية الطفيفة السبب الأول وراء عدم دقة نتائج حساب التكرارات في إكسيل. غالباً ما تحتوي السجلات المستوردة من أنظمة خارجية على مسافات بادئة أو لاحقة خفية، أو مسافات غير منقطعة (Non-breaking spaces برمز ASCII 160) التي لا تلتقطها دالة المسافات العادية. لضمان التطابق التام، يُنصح بتمرير البيانات عبر معادلة تنظيف مزدوجة تجمع بين TRIM و CLEAN واستبدال المسافة غير المنقطعة:

=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

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

ومن أفضل الممارسات أيضاً توحيد تنسيق الحقول الرقمية والتأكد من عدم تخزين الأرقام كنصوص (Numbers Stored as Text) في بعض الخلايا، إذ تعامل دالة COUNTIF القيمة الرقمية 100 كنص مختلف عن الرقم 100 في بعض سياقات المقارنة المنطقية المتقدمة، مما يستوجب تحويل العمود بالكامل إلى نوع بيانات موحد باستخدام خاصية “نص إلى أعمدة” (Text to Columns).

12.2 تحسين أداء المعادلات وسرعة إعادة الحساب

عند بناء نماذج إحصائية في مصنفات ضخمة، يرتكب العديد من المستخدمين خطأً كارثياً بالإشارة إلى أعمدة كاملة داخل الدوال المصفوفية الثقيلة مثل: =SUMPRODUCT(--(A:A="Target")). يجبر هذا الاستدعاء محرك إكسيل على فحص أكثر من 1,048,576 صفاً في الذاكرة حتى لو كانت البيانات الفعلية لا تتجاوز بضع مئات من الصفوف، مما يتسبب في بطء شديد وتراجع حاد في كفاءة الجهاز.

لتفادي هذا التدهور في الأداء، يجب حصر نطاقات البحث بدقة (مثل A2:A1000)، أو استخدام الجداول الرسمية في إكسيل (Excel Tables – ListObjects) ومراجعها المهيكلة (Structured References)، مثل: =COUNTIF(SalesTable[Region], "الشرقية"). تمتاز الجداول الرسمية بأن نطاقاتها تتمدد وتنكمش تلقائياً وبشكل حصري مع حجم البيانات الفعلي، مما يوفر أقصى كفاءة حسابية ممكنة ويمنع استهلاك موارد المعالجة في فحص الخلايا الفارغة.

وفي المصنفات المالية والتحليلية المعقدة جداً، يمكن التحكم في خيارات حساب المصنف عبر التوجه إلى “خيارات إكسيل” -> “الصيغ” (Formulas) وتحويل الحساب من “تلقائي” (Automatic) إلى “يدوي” (Manual). يتيح هذا الخيار للمحلل كتابة وتعديل مئات المعادلات بحرية تامة دون إعادة حساب الورقة بعد كل نقرة، ثم الضغط على المفتاح F9 لتنفيذ عملية إعادة الحساب الشاملة دفعة واحدة عند اكتمال إعداد التقرير.

12.3 قائمة تدقيق شاملة للتحقق من صحة النتائج التكرارية

لضمان الجودة والامتثال في التقارير الإحصائية الموجهة للإدارة العليا أو الجهات الرقابية، يجب تطبيق بروتوكول تدقيق صارم يتألف من خطوات التحقق المنهجية التالية:

• مطابقة المجموع التراكمي: يجب التحقق دائماً من أن حاصل جمع مصفوفة التكرارات المستخرجة (SUM(Frequencies)) يساوي تماماً العدد الإجمالي للصفوف غير الفارغة في الجدول المصدر (COUNTA(SourceRange)). أي تباين رقمي يشير فوراً إلى وجود سجلات مفقودة أو شروط غير مستوفاة في دالة التكرار.
• الفحص البصري بالتنسيق الشرطي: تطبيق قواعد التنسيق الشرطي (Conditional Formatting) لتمييز العناصر المكررة (Highlight Duplicate Values) بصرياً بألوان متباينة، ومراجعة النطاق للتأكد من تطابق المخرجات الإحصائية مع التوزيع اللوني في ورقة العمل.
• استكشاف أخطاء المعادلات وحلها منهجياً:
  • خطأ #VALUE!: يعبر غالباً عن عدم تطابق أطوال النطاقات في دوال COUNTIFS أو تمرير وسائط غير صالحة. الحل: مراجعة تماثل أبعاد النطاقات المصدرية.
  • خطأ #SPILL!: يظهر عند وجود خلايا مشغولة بنصوص أو بيانات تعترض مسار تمدد المصفوفة المنسكبة (مثل UNIQUE أو COUNTIF#). الحل: تفريغ النطاق المكاني أسفل المعادلة للسماح بانسكاب النتائج.
  • خطأ #NAME?: ينجم عن كتابة اسم الدالة بصورة خاطئة أو استخدام دوال مصفوفية حديثة على إصدارات إكسيل قديمة غير مدعومة. الحل: التحقق من الهجاء وترقية الإصدار أو استخدام الصيغ الكلاسيكية البديلة.

خاتمة

إن إتقان حساب التكرارات في برنامج إكسيل يمثل جسراً جوهرياً يعبر بمحلل البيانات من مرحلة المعالجة الحسابية البسيطة إلى آفاق التحليل الإحصائي المتقدم وذكاء الأعمال الرصين. وكما استعرضنا في هذا الدليل الشامل، فإن إكسيل لا يقدم حلاً أحادياً جامداً لهذه المسألة، بل يوفر منظومة أدوات هندسية متكاملة تتدرج من دالة COUNTIF السلسة للبيانات اليومية، مروراً بالتناغم المنسكب بين UNIQUE و COUNTIFS للتقارير الحية، ومرونة SUMPRODUCT في المعالجات المنطقية الحساسة لحالة الأحرف، وصولاً إلى القوة الحوسبية الفائقة لأداة Power Query والحلول البرمجية المخصصة عبر VBA.

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

References

• Alexander, M., Kusleika, D., & Walkenbach, J. (2019). Excel 2019 Bible. John Wiley & Sons.
• Microsoft Corporation. (2024). COUNTIF function. Microsoft Support. https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34
• Microsoft Corporation. (2024). COUNTIFS function. Microsoft Support. https://support.microsoft.com/en-us/office/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842
• Microsoft Corporation. (2024). Dynamic array formulas and spilled array behavior. Microsoft Learn. https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b0f-a318-4ba8-b0a4-0fb72e823463
• Microsoft Corporation. (2024). UNIQUE function. Microsoft Support. https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e
• Microsoft Corporation. (2024). Power Query documentation. Microsoft Learn. https://learn.microsoft.com/en-us/power-query/
• Walkenbach, J. (2015). Excel VBA Programming For Dummies (4th ed.). John Wiley & Sons.
• Winston, W. (2021). Microsoft Excel Data Analysis and Business Modeling (Office 2021 and Microsoft 365) (7th ed.). Microsoft Press.