كيف يمكن حساب متوسط نطاق ديناميكي في Excel؟

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

لإعداد هذا، اختر خلية فارغة، مثل الخلية C4، وأدخل الصيغة التالية:

=IF(C2=0,"NA",AVERAGE(A2:INDEX(A:A,C2)))

ثم اضغط على مفتاحEnter لرؤية متوسط الناتج.

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

ملاحظات:

(1) في هذه الصيغة=IF(C2=0,"NA",AVERAGE(A2:INDEX(A:A,C2))): A2 تمثّل الخلية الأولى في النطاق الذي تريد حساب متوسطه، وC2تشير إلى الخلية التي تحتوي على رقم الصف الخاص بالخلية الأخيرة في النطاق المستهدف. غيّر هذه المراجع وفقًا لهيكل بياناتك عند الحاجة. تأكد من أن الخلية C2 تشير إلى صف صالح، وإلا فستحصل على نتائج غير متوقعة أو «#N/A».

(2) كبديل، يمكنك استخدام:

=AVERAGE(INDIRECT("A2:A"&C2))

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

نصيحة عملية: عندما تستمر بياناتك في النمو (مثل إضافة صفوف جديدة يوميًا)، يمكنك استخدام دالة COUNTA أو COUNT لتعيين مرجع الخلية العلوى تلقائيًا—مما يضمن أن نطاقك الديناميكي يشمل دائمًا أحدث الإدخالات.

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

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

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

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

2. في الخلية الفارغة التي تختارها، أدخل الصيغة التالية:

=AVERAGE(INDIRECT(G2))

هنا،G2 هي خلية المعايير التي يُدخل فيها المستخدم اسم رأس الصف أو العمود—إما كتابةً أو اختيارًا. وعندما تتغيّر الخلية G2 (مثلًا من "Region1" إلى "Region2")، تحسب الصيغة المتوسط للنطاق المقابل ديناميكيًا. تأكد دائمًا من أن القيمة المُدخلة في G2 تتطابق تمامًا مع الأسماء المعرّفة (بما في ذلك حالة الأحرف) لتجنب ظهور أخطاء مثل #REF!.

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

العد/الجمع/حساب المتوسط التلقائي للخلايا حسب تعبئة اللون في Excel

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

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

على سبيل المثال، قد ترغب في حساب متوسط آخر N صفوف في العمود A، حيث يقوم المستخدم بإدخال قيمة N، أو حساب متوسط القيم من صفوف غير متصلة يحددها المستخدم نطاق محدود.

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

Sub DynamicAverage_LastNRows()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long
    Dim N As Long
    Dim result As Double
    Dim xTitleId As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = Application.ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    N = Application.InputBox("How many last rows to average?", xTitleId, 5, Type:=1)
    
    If N <= 0 Or N > lastRow - 1 Then
        MsgBox "Invalid input for N!", vbExclamation
        Exit Sub
    End If
    
    Set rng = ws.Range("A" & lastRow - N + 1, "A" & lastRow)
    result = Application.WorksheetFunction.Average(rng)
    
    MsgBox "Average of the last " & N & " rows in column A: " & result, vbInformation
End Sub

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

لحساب المتوسط بشروط أكثر تعقيدًا (مثلًا استنادًا إلى معايير أو من أوراق عمل متعددة)، يمكنك تعديل رمز VBA وفقًا لذلك—على سبيل المثال، بإضافة مربعات إدخال (InputBoxes) لقيمة معيّنة، أو الحلقة عبر عدة أوراق عمل لـ نطاق الدمج قبل حساب المتوسط.

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

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