كيف تحسب المتوسط لكل يوم أو شهر أو ربع سنة أو ساعة باستخدام جدول بيانات محوري في إكسل؟

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

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

2. في مربع الحوار «إنشاء جدول محوري» الظاهر، اخترورقة عمل موجودةإذا أردت وضع الملخص في ورقة العمل النشطة. حدّدالمكانباختيار الخلية التي تريد أن يظهر فيها الجدول المحوري، ثم انقر علىموافق.
ملاحظة: لعرض جدول محوري في ورقة عمل جديدة، اختر الخيارورقة عمل جديدةبدلاً من ذلك. تأكد من أن الموقع المختار لا يتداخل مع أي بيانات موجودة لتجنب تحذيرات الكتابة فوقها.

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

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

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

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

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

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

ملاحظة: للحصول على متوسط حسب الأسبوع أو الشهر أو السنة، يوفّر لك Kutools لـ Excel تطبيق تنسيق التاريخوTo Actual لتحويل الطوابع الزمنية إلى التنسيقات المطلوبة بنقرات قليلة فقط، مما يضمن تجميعًا متسقًا وحسابًا دقيقًا.

احصل الآن على نسخة تجريبية مجانية لمدة 30 يومًا بكامل الميزات!

2. حدد مجموعة البيانات بالكامل (بما في ذلك التواريخ المنسَّقة والقيم)، ثم من شريط Excel، اخترKutools > المحتوى > دمج متقدم للصفوف.

3. في مربع الحوار الذي يفتح، اختر عمود التاريخ/الوقت من القائمة وعيّنه كـمفتاح رئيسي، ثم اختر عمود القيم (مثل المبلغ) وعيّنه ليتمحسابهكـمتوسط. بعد ذلك، انقر علىموافقوسيقوم Kutools فورًا بحساب المتوسطات لكل تاريخ على حدة.

يتم حساب المتوسطات لفتراتك الزمنية المحددة فورًا، مما يبسّط التحليل. إذا عرض تجميع التواريخ شهورًا أو سنوات بدلًا من أيام، فستُجمَّع النتائج وفقًا لذلك. يمكنك أيضًا إعادة تنسيق التواريخ باستخدامKutools > تنسيق > تطبيق تنسيق التاريخ، ثم اختتم العملية باستخدامKutools > To Actual.

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

فيما يلي مثال لكيفية استخدام الصيغ لحساب القيم المتوسطة لكل يوم:

1. افترض أن بياناتك تحتوي على تواريخ في العمود A (A2:A100) وبيانات رقمية في العمود B (B2:B100). في عمود جديد (مثل الخلية C2)، أدخل الصيغة التالية لحساب المتوسط الخاص باليوم المحدد (مثل التاريخ الموجود في A2):

=AVERAGEIFS(B$2:B$100, A$2:A$100, A2)

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

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

1. لبدء الاستخدام، افتح محرر VBA بالنقر علىأدوات المطور > Visual Basic. عند ظهور نافذةMicrosoft Visual Basic for Applications، انقر علىإدراج > وحدة نمطية، ثم الصق الكود التالي في الوحدة النمطية:

Sub AverageByPeriod()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim periodCol As String, valueCol As String
    Dim dict As Object
    Dim cell As Range
    Dim periodKey As String
    Dim i As Long, lastRow As Long
    Dim sumDict As Object, countDict As Object
    
    Set ws = ActiveSheet
    periodCol = "A" ' Date/Time column
    valueCol = "B" ' Value column
    lastRow = ws.Cells(ws.Rows.Count, periodCol).End(xlUp).Row
    
    Set dict = CreateObject("Scripting.Dictionary")
    Set sumDict = CreateObject("Scripting.Dictionary")
    Set countDict = CreateObject("Scripting.Dictionary")
    
    For i = 2 To lastRow
        ' Grouping by month example; change to format for day/hour/quarter if needed
        periodKey = Format(ws.Cells(i, periodCol).Value, "yyyy-mm")
        
        If Not dict.Exists(periodKey) Then
            dict.Add periodKey, dict.Count + 1
            sumDict.Add periodKey, ws.Cells(i, valueCol).Value
            countDict.Add periodKey, 1
        Else
            sumDict(periodKey) = sumDict(periodKey) + ws.Cells(i, valueCol).Value
            countDict(periodKey) = countDict(periodKey) + 1
        End If
    Next i
    
    ws.Cells(1, 4).Value = "Period"
    ws.Cells(1, 5).Value = "Average"
    
    i = 2
    Dim k As Variant
    For Each k In dict.Keys
        ws.Cells(i, 4).Value = k
        ws.Cells(i, 5).Value = sumDict(k) / countDict(k)
        i = i + 1
    Next k
End Sub

2. بعد لصق الكود، انقر على زرلتنفيذه. سيقوم الماكرو بقراءة بياناتك من العمودين A وB بدءًا من الصف 2، وتجميعها حسب الفترة التي تحددها (مضبوطة حاليًّا على الشهر)، ثم يعرض المتوسط لكل مجموعة في العمودين D وE.

نصائح:

• لتجميع البيانات حسب اليوم، غيّر السطرFormat(..., "yyyy-mm-dd").
• للتجميع الفصلي، استخدم:periodKey = "Q" & WorksheetFunction.RoundUp(Month(ws.Cells(i, periodCol).Value) /[[PH_45]]) & "-" & Year(ws.Cells(i, periodCol).Value)
• تأكد دائمًا من أن أعمدتك ()periodCol،valueCol) تتوافق مع تخطيط بياناتك.

احتياطات:

• إذا ظهرت لك رسالة خطأ أو كانت النتائج فارغة، فتأكد من أن عمود التجميع خالٍ من الخلايا الفارغة والقيم غير التاريخية.
• عدّل تعيين الأعمدة حسب الحاجة—فإذا كانت بياناتك لا تبدأ بالأعمدة A وB، فحدّث قيمتيperiodCol وvalueCol وفقًا لذلك.
• احرص على حفظ عملك قبل تشغيل ماكروهات لتجنب إدخال تغييرات غير مقصودة على بياناتك.

المتوسط حسب السنة/الشهر/التاريخ في إكسل

المتوسط الزمني لليوم في إكسل

المتوسط الأسبوعي في إكسل