كيف يمكن عدّ عدد التكرارات لكل سنة أو ربع سنة أو شهر أو أسبوع في Excel؟

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

اختر خلية فارغة تريد عرض نتيجة العد فيها، ثم أدخل الصيغة التالية:

=SUMPRODUCT((MONTH($A$2:$A$24)=F2)*(YEAR($A$2:$A$24)=$E$2))

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

ملاحظات ونصائح:

• في الصيغةMONTH($A$2:$A$24)=F2 وYEAR($A$2:$A$24)=$E$2، يُعدّان معيارين يطابقان الشهر المحدد في الخلية F2 والسنة في الخلية E2. حدِّث النطاقات والمراجع (مثل)A2:A24،E2،F2) لتناسب تخطيط بياناتك.
• للحصول على عدد حسب الشهر فقط، مع تجاهل السنة، استخدم:
  =SUMPRODUCT(1*(MONTH($A$2:$A$24)=F2))
• تأكد من أن عمود التواريخ يحتوي على تواريخ فعلية في Excel، وليس تواريخ منسَّقة كنص، لتجنب الأخطاء أو عدم التطابق. وإذا أعادت صيغتك نتائج غير متوقعة، فتحقق مجددًا من تنسيق التواريخ.
• إذا كانت مجموعة بياناتك كبيرة، ففكّر في استخدام الجداول المحورية (PivotTables) أو VBA لتحقيق أداء أفضل وصيانة أكثر سهولة.

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

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

1. حدد العمود الذي يحتوي على تواريخك، ثم انقرKutools > تنسيق > تطبيق تنسيق التاريخ. سيظهر مربّع الحوار التالي:

2. في مربّع حوار تطبيق تنسيق التاريخ، اختر نمط التنسيق الذي يتوافق مع متطلبات العد الخاصة بك (مثل الشهر، السنة، يوم الأسبوع، اليوم، إلخ)، ثم انقرموافق (OK). على سبيل المثال، اختر «Mar» للعد حسب الشهر.

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

4. بعد ذلك، حدد النطاق الذي يحتوي على اسم المجموعة المحولة والبيانات المرتبطة بها (مثل أعمدة المبلغ أو الفئة). ثم انتقل إلىKutools > المحتوى > دمج متقدم للصفوف. ستظهر لك الواجهة التالية:

5. في مربّع حوار دمج متقدم للصفوف:
(1) اضبط عمود التواريخ كـمفتاح رئيسي (Primary Key)للتجميع حسبه.
(2) بالنسبة للعمود الذي تريد حساب عدّه (مثل المبلغ)، اضبط الحساب علىعدّ (Count).
(3) يمكنك اختيار طرق تجميع أو تركيب أخرى لأعمدة مختلفة (مثل دمج أسماء الفواكه بفاصلة).
(4) انقرموافق (OK)للمعالجة.

ستعرض بياناتك الآن عدد السجلات لكل فترة محددة. راجع لقطة الشاشة أدناه:

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

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

1. حدد جدول بياناتك، ثم انتقل إلىإدراج > جدول محوري (PivotTable). سيظهر مربع حوار إنشاء الجدول المحوري.

2. في المربّع الحواري، حدّد مكان إدراج الجدول المحوري (ورقة عمل جديدة أو موقع موجود مثل الخلية E1)، ثم انقرموافق (OK).

3. في لوحة حقول الجدول المحوري، اسحب حقلالتاريخإلى قسمالصفوف، واسحب حقلالمبلغ (أو الحقل المستهدف) إلى قسمالقيم. بشكل افتراضي، قد يتم جمع القيم.

يظهر الجدول المحوري كما في لقطة الشاشة أدناه:

4. غيّر حساب القيمة إلى عدد بالنقر بزر الماوس الأيمن على عنوان عمود القيمة (مثل: مجموع المبلغ)، ثم اختيارتلخيص القيم حسب > العد.

5. لتجميع البيانات حسب فترات إضافية (مثل الشهر أو السنة أو الربع)، انقر بزر الماوس الأيمن على أي خلية في عمود تسميات الصفوف، ثم اخترتجميع. من مربع الحوار، حدد معايير التجميع (مثل الشهور أو السنوات أو الأرباع)، ثم انقرموافق.

يعرض جدولك الآن الأعداد حسب الفترة (أو الفترات) المحددة:

ملاحظة:يؤدي التجميع حسب فترات متعددة (مثل الشهر والسنة) إلى إضافة مستويات إضافية في تسميات الصفوف. يمكنك إعادة ترتيب حقول التجميع (مثل نقل)السنواتأسفلالتاريخ) في لوحة حقول PivotTable لضبط طريقة عرض الملخص.

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

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

خطوات التنفيذ الكاملة:

• احفظ نسخة احتياطية من ملفك قبل تشغيل أي ماكرو لأول مرة.
• انقرالمطور > Visual Basic لفتح محرر VBA.
• انقرإدراج > وحدة برمجية (Module)، ثم انسخ والصق الكود أدناه في نافذة الوحدة البرمجية.

Sub CountOccurrencesByPeriod()
    Dim lastRow As Long
    Dim ws As Worksheet, summaryWs As Worksheet
    Dim periodType As String
    Dim dict As Object, key As Variant
    Dim dateRange As Range, cell As Range
    Dim outputRow As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = Application.ActiveSheet
    Set dateRange = Application.InputBox("Select date range:", xTitleId, Selection.Address, Type:=8)
    
    periodType = Application.InputBox("Count by (Year/Quarter/Month/Week):", xTitleId, "Month", Type:=2)
    
    If dateRange Is Nothing Or periodType = "" Then Exit Sub
    
    Set dict = CreateObject("Scripting.Dictionary")
    
    For Each cell In dateRange
        If IsDate(cell.Value) Then
            Select Case LCase(periodType)
                Case "year"
                    key = Year(cell.Value)
                Case "quarter"
                    key = "Q" & WorksheetFunction.RoundUp(Month(cell.Value) / 3, 0) & " " & Year(cell.Value)
                Case "month"
                    key = Format(cell.Value, "yyyy-mm")
                Case "week"
                    key = "W" & WorksheetFunction.WeekNum(cell.Value) & " " & Year(cell.Value)
                Case Else
                    key = Format(cell.Value, "yyyy-mm")
            End Select
            
            If dict.Exists(key) Then
                dict(key) = dict(key) + 1
            Else
                dict.Add key, 1
            End If
        End If
    Next cell
    
    Set summaryWs = Worksheets.Add(After:=ws)
    summaryWs.Name = "Occurrence_Summary"
    
    summaryWs.Range("A1").Value = "Period"
    summaryWs.Range("B1").Value = "Occurrences"
    
    outputRow = 2
    For Each key In dict.Keys
        summaryWs.Cells(outputRow, 1).Value = key
        summaryWs.Cells(outputRow, 2).Value = dict(key)
        outputRow = outputRow + 1
    Next key
    
    MsgBox "Summary completed in sheet 'Occurrence_Summary'.", vbInformation
End Sub

بعد إدخال الكود:

• عد إلى Excel واضغطAlt+F8، ثم اخترCountOccurrencesByPeriod، وانقرتشغيل (Run).
• سيظهر لك مربع حوار يطلب منك تحديد نطاق التواريخ المطلوب تحليله. اختر العمود أو النطاق ذا الصلة الذي يحتوي على تواريخك.
• سيطلب منك مربع حوار ثانٍ تحديد الفترة التي تريد التجميع بناءً عليها: أدخل «Year» (سنة)، أو «Quarter» (ربع سنة)، أو «Month» (شهر)، أو «Week» (أسبوع) — دون مراعاة حالة الأحرف.
• سيقوم الماكرو بإنشاء ورقة عمل جديدة باسمOccurrence_Summary، تسرد كل فترة وعدد المرات التي وقعت فيها.

استكشاف الأخطاء والنصائح:

• إذا ظهرت لك رسالة تحذير أمان الماكرو، فانتقل إلىملف > خيارات > مركز الثقة > إعدادات الماكرولتعديل إعدادات الماكرو.
• تأكد من أن عمود التواريخ يحتوي على قيم تواريخ صالحة في Excel، إذ قد تؤدي السلاسل النصية أو التنسيقات المختلطة إلى عدٍّ غير دقيق أو أخطاء.
• الماكرو مرن—أدخل «Quarter» للتجميع السريع حسب السنة والربع، أو «Week» لتلخيص البيانات أسبوعيًا.
• إذا رغبت في تخصيص الناتج—مثل إضافة تفاصيل إضافية—يمكنك تعديل الماكرو ليتعامل مع أعمدة إضافية أو قواعد حسابية أخرى.

يُعد هذا الحل خيارًا قويًّا للتقارير الدفعية أو التحليل الدوري، لكنه يتطلب معرفة أساسية بـ VBA وإدارة المصنفات بشكل صحيح. إذا كنت ترغب في دمج التلخيص البصري، ففكّر في استخدام PivotTables وVBA معًا.

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

السيناريو المناسب:لديك قائمة تواريخ (مثل بيانات مبيعات أو حضور) وترغب في عدّ عدد الإدخالات التي تقع في كل أسبوع من السنة. يُعدّ هذا الأسلوب مثاليًّا للتحليل المستمر، خاصةً عندما تتغيّر بياناتك بشكل متكرر، إذ يتم تحديث العد تلقائيًّا.

1. في عمود فارغ (مثل B2)، أدخل الصيغة التالية لحساب رقم الأسبوع المقابل لكل تاريخ في العمود A:

=WEEKNUM(A2,1)

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

2. أنشئ قائمة بأرقام الأسابيع التي تريد تلخيصها (مثل: 1، 2، 3، …). في خلية فارغة أخرى (مثل D2)، استخدم الصيغة التالية لعدّ تكرارات رقم أسبوع معيّن (بافتراض أن النطاق B2:B24 يحتوي على أرقام الأسابيع، وأن الخلية D2 تحتوي على الأسبوع الذي تريد البحث عنه):

=COUNTIF($B$2:$B$24, D2)

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

النصائح والاحتياطات:

• إذا كنت ترغب في العد حسب السنة والأسبوع معًا لتمييز الإدخالات عبر سنوات مختلفة، استخدم:

  =SUMPRODUCT((YEAR($A$2:$A$24)=$F$2)*(WEEKNUM($A$2:$A$24,1)=G2))

  حيث تحتوي الخلية F2 على السنة المستهدفة، وG2 على رقم الأسبوع المطلوب. عدّل نطاقات الأعمدة والمراجع حسب احتياجاتك.
• قد يختلف رقم الأسبوع الذي تُرجعه الدالةWEEKNUM حسب الإعداد الذي تستخدمه (نظامك، المعيار الأمريكي، المعيار الدولي ISO، أو يوم البدء الذي تحدده).
• إذا كنت تستخدم رقم الأسبوع وفقًا للمعيار الأوروبي ISO (حيث يبدأ الأسبوع يوم الاثنين، ويُعدّ الأسبوع الأول هو ذلك الذي يحتوي على أول خميس في السنة)، فاستخدم=ISOWEEKNUM(A2) (لـ Excel 2013 والإصدارات الأحدث).
• تأكد دائمًا من أن جميع قيم التواريخ لديك بتنسيق تواريخ Excel صالح للحصول على نتائج دقيقة.

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

عد عدد عطلات نهاية الأسبوع/أيام الأسبوع بين تاريخين في Excel

COUNTIF حسب التاريخ/الشهر/السنة ونطاق التاريخ في Excel

COUNTIF للبيانات/القوائم المصفاة باستخدام معايير في Excel