كيفية حساب متوسط النطاق الديناميكي في 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 تشير إلى صف صالح، وإلا ستحصل على نتائج غير متوقعة أو "NA".

(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

أحيانًا تقوم بتمييز الخلايا بلون التعبئة، ثم تقوم بعد/جمع هذه الخلايا أو حساب متوسط هذه الخلايا لاحقًا. أداة Count by Color الخاصة بـ 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 لقيمة معيارية، أو التكرار عبر عدة أوراق عمل لدمج النطاقات قبل أخذ المتوسط.

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

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