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

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

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

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

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

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

• في الصيغ 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 الحقيقية وليس التواريخ المنسقة كنص لتجنب الأخطاء أو عدم التطابق. إذا كانت الصيغة تعيد نتائج غير متوقعة، قم بمراجعة تنسيق التاريخ.
• إذا كانت مجموعة بياناتك كبيرة، ففكر في استخدام الجداول المحورية أو VBA لأداء أفضل وأسهل صيانة.

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

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

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

2. في نافذة تطبيق تنسيق التاريخ، اختر نمط التنسيق الذي يتوافق مع متطلبات العد (مثل الشهر، السنة، يوم الأسبوع، اليوم، إلخ)، ثم انقر موافق. على سبيل المثال، حدد "مارس" لحساب حسب الشهر.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

خطوات التشغيل الكاملة:

• قم بعمل نسخة احتياطية لملف العمل قبل تشغيل أي ماكرو لأول مرة.
• انقر على Developer > Visual Basic لفتح محرر VBA.
• انقر على Insert > 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 تحتوي على كل فترة وعدد التكرارات داخلها.

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

• إذا واجهت تحذير أمان الماكرو، قم بضبط إعدادات الماكرو في File > Options > Trust Center > Macro Settings.
• تأكد من أن عمود التاريخ يحتوي على قيم تاريخ Excel صالحة؛ قد تنتج السلاسل النصية أو التنسيقات المختلطة أحجامًا غير دقيقة أو أخطاء.
• الماكرو مرن—أدخل "Quarter" لتصنيف العد بسرعة حسب السنة والربع، أو "Week" لتلخيص على أساس أسبوعي.
• إذا كنت ترغب في تخصيص الإخراج (مثل إضافة المزيد من التفاصيل)، يمكنك تعديل الماكرو لمعالجة أعمدة إضافية أو قواعد حسابية.

هذا الحل قوي لإعداد تقارير دفعية أو تحليل دوري ولكنه يفترض معرفة أساسية بـ VBA وإدارة ملفات العمل المناسبة. إذا كنت ترغب في دمج التلخيص البصري، فكر في استخدام الجداول المحورية و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) (لـ Excel2013 وما بعده).
• تأكد دائمًا من أن جميع قيم التواريخ الخاصة بك ذات تنسيق تاريخ Excel صالح للحصول على نتائج دقيقة.

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

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

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

Countif بيانات مفلترة/قائمة مع معايير في Excel