كيفية جمع القيم المقابلة لنفس التاريخ في Excel؟

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

• جمع القيم المقابلة بناءً على نفس التاريخ باستخدام صيغة
• جمع القيم المقابلة بناءً على نفس التاريخ باستخدام Kutools لـ Excel
• جمع القيم المقابلة بناءً على نفس التاريخ باستخدام كود VBA
• جمع القيم المقابلة بناءً على نفس التاريخ باستخدام جدول محوري

جمع القيم المقابلة بناءً على نفس التاريخ باستخدام صيغة

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

1. أدخل الصيغة التالية في خلية فارغة حيث تريد ظهور الإجمالي لتاريخ معين (على سبيل المثال، الخلية E2):

=SUMIF($A$2:$A$13,D2,$B$2:$B$13)

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

جمع القيم المقابلة بناءً على نفس التاريخ باستخدام Kutools لـ Excel

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

بعد تثبيت Kutools لـ Excel، يرجى المتابعة على النحو التالي:

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

2. انتقل إلى Kutools > دمج & تقسيم > دمج متقدم للصفوف.

3. في مربع الحوار، حدد عمود التاريخ وقم بتعيينه كمفتاح رئيسي. بعد ذلك، حدد العمود الذي سيتم جمعه (مثل "طلب") وقم بتعيين عملية الجمع له في القسم حساب. يقوم هذا الإعداد بإبلاغ Kutools بجمع الصفوف حسب التواريخ المتطابقة ودمج القيم العددية الخاصة بهم عن طريق جمعها.

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

جمع القيم المقابلة بناءً على نفس التاريخ باستخدام كود VBA

بالنسبة للمستخدمين الذين يسعون إلى نهج أوتوماتيكي أو قابل للبرمجة، تقدم VBA (Visual Basic for Applications) طريقة مرنة لجمع القيم حسب التاريخ وتصدير الملخص إلى نطاق جديد. تعد VBA مثالية لمعالجة المهام المتكررة، ومعالجة مجموعات البيانات الكبيرة، أو دمج التلخيص في سير عمل أوسع - خاصة عندما لا تكون الصيغ أو الميزات المدمجة كافية لاحتياجاتك.

1. انقر فوق Developer Tools > Visual Basic لفتح محرر Microsoft Visual Basic for Applications. في النافذة، انقر فوق Insert > Module والصق الكود أدناه في الوحدة:

Sub SumValuesByDate()
    Dim SourceRange As Range
    Dim OutputRange As Range
    Dim Dict As Object
    Dim Cell As Range
    Dim iRow As Long
    Dim LastRow As Long
    Dim ws As Worksheet
    Dim kDate As Variant
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = Application.ActiveSheet
    
    ' Prompt for source data range containing dates and values
    Set SourceRange = Application.InputBox("Select the source data range (dates in first column, values in second):", xTitleId, ws.Range("A2:B13").Address, Type:=8)
    
    If SourceRange Is Nothing Then Exit Sub
    
    ' Prompt for output location
    Set OutputRange = Application.InputBox("Select the cell to output summary (top-left cell):", xTitleId, "E1", Type:=8)
    
    If OutputRange Is Nothing Then Exit Sub
    
    Set Dict = CreateObject("Scripting.Dictionary")
    
    ' Loop through data and sum by date
    For iRow = 1 To SourceRange.Rows.Count
        kDate = SourceRange.Cells(iRow, 1).Value
        If kDate <> "" And IsDate(kDate) Then
            If Dict.Exists(kDate) Then
                Dict(kDate) = Dict(kDate) + SourceRange.Cells(iRow, 2).Value
            Else
                Dict.Add kDate, SourceRange.Cells(iRow, 2).Value
            End If
        End If
    Next
    
    ' Write header
    OutputRange.Cells(1, 1).Value = "Date"
    OutputRange.Cells(1, 2).Value = "Sum"
    
    ' Write result to output range
    iRow = 2
    For Each kDate In Dict.Keys
        OutputRange.Cells(iRow, 1).Value = kDate
        OutputRange.Cells(iRow, 2).Value = Dict(kDate)
        iRow = iRow + 1
    Next
End Sub

2. انقر فوق الزر أو اضغط على F5 لتشغيل ماكرو.

3. سيظهر مربع حوار يطالبك باختيار نطاق بياناتك الأصلي (تأكد من أن التاريخ موجود في العمود الأول والقيمة في العمود الثاني). بعد ذلك، يتيح لك مربع حوار آخر تحديد مكان إخراج النتائج (مثل الخلية E1).

4. بعد التأكيد، سيتم إنشاء جدول ملخص للتواريخ ومجموع القيم المقابلة لهم بدءًا من الخلية المختارة للإخراج.

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

جمع القيم المقابلة بناءً على نفس التاريخ باستخدام جدول محوري

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

1. حدد نطاق بياناتك الأصلي، بما في ذلك الرؤوس (مثل A1:B13).

2. انتقل إلى علامة التبويب Insert وحدد PivotTable. في مربع الحوار، تأكد من نطاق بياناتك واختر مكان وضع الجدول المحوري (ورقة عمل جديدة أو ورقة عمل موجودة).

3. في قائمة حقول الجدول المحوري، اسحب حقل التاريخ إلى منطقة الصفوف، واطلب (أو حقل القيم الخاص بك) إلى منطقة القيم. سيقوم Excel تلقائيًا بجمع القيم لكل تاريخ فريد.

4. لتحديث نتائج الجدول المحوري عند تغيير بياناتك الأصلية، انقر بزر الماوس الأيمن على الجدول المحوري وحدد Refresh.