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

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

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

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

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

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

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

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

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

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

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

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

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

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

سيظهر PivotTable كما في لقطة الشاشة أدناه:

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

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

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

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

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

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

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

• قم بعمل نسخة احتياطية من ملفك قبل تشغيل أي ماكرو لأول مرة.
• انقر فوق 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، وانقر تشغيل.
• ستظهر رسالة تطالبك باختيار نطاق التاريخ لتحليله. حدد العمود أو النطاق ذي الصلة الذي يحتوي على تواريخك.
• ستظهر رسالة ثانية تسألك عن الفترة التي تريد التجميع حسبها: أدخل "Year"، "Quarter"، "Month"، أو "Week" (غير حساس لحالة الأحرف).
• سيقوم الماكرو بإنشاء ورقة عمل جديدة تسمى Occurrence_Summary تسرد كل فترة وعدد التكرارات داخلها.

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

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

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

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

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

Countif بيانات مُرشحة/قائمة بشروط في Excel