كيف تُحسب متوسط الخلايا أو القائمة المُرشَّحة في Excel؟

عند تطبيق دالة AVERAGE مباشرةً على مجموعة بيانات مُرشَّحة، فإنها تحسب المتوسط لجميع الخلايا في النطاق المحدد — بما في ذلك الخلايا المخفية بسبب التصفية — مما يؤدي إلى نتائج غير دقيقة إذا كان هدفك حساب متوسط الصفوف المرئية فقط. وللحصول على المتوسط الحقيقي للبيانات المرشَّحة (أي البيانات المرئية فحسب)، تُعد دالة SUBTOTAL في Excel الحل الأمثل، إذ تتكيف مع عوامل التصفية وتتجاهل الصفوف المخفية تلقائيًا، مما يجعلها الخيار المثالي لهذا السيناريو.

لحساب المتوسط للنتائج المُرشَّحة فقط، اتبع الخطوات التالية:

• حدد النطاق الذي يشمل جميع بياناتك المُرشَّحة في العمود الذي تريد حساب متوسطه (في هذا المثال، افترض أن القيم موجودة في)C12:C24 من عمود المبلغ).
• في خلية فارغة، أدخل الصيغة التالية:

=SUBTOTAL(1,C12:C24)

تحسب هذه الصيغة متوسط الخلايا المرئية (المُرشَّحة) في النطاق المحدد ()C12:C24). يُوجِّه المعامل1 دالة SUBTOTAL لاستخدام دالة AVERAGE، وتتجاهل دالة SUBTOTAL تلقائيًا أي صفوف مخفية نتيجة التصفية.

بعد كتابة الصيغة، اضغطEnter. ستظهر لك قيمة المتوسط للصفوف المرئية فقط فورًا، كما هو موضح أدناه:

جمع/عد/حساب متوسط الخلايا المرئية فقط مع تجاهل الخلايا/الصفوف/الأعمدة المخفية أو المُرشَّحة

دوالSUM أوCOUNT أوAVERAGE العادية في Excel تحسب جميع الخلايا ضمن نطاق معيّن، بغض النظر عمّا إذا كانت مرئية أو مخفية بسبب المرشحات أو الإخفاء اليدوي. وللحصول على نتائج أكثر دقة وموثوقية في مثل هذه الحالات، جرّبKutools لـ Excel! بفضل دواله المتخصصةSUMVISIBLE،COUNTVISIBLE، وAVERAGEVISIBLE، يمكنك بسهولة حساب المجاميع والعد والمتوسطات **للخلايا المرئية فعليًا** في أي نطاق—مع استبعاد تلقائي للخلايا والصفوف والأعمدة المُرشَّحة أو المخفية يدويًا. هذه الميزة الذكية تساعدك على تجنّب الأخطاء في جداول البيانات المعقدة وتوفير الوقت مقارنةً باستخدام صيغ معقّدة أو أكواد مخصصة.

حساب متوسط البيانات/القائمة المُرشَّحة باستخدام دالة AGGREGATE

إذا كنت تستخدم Excel 2010 أو إصدارًا أحدث، فإن دالةAGGREGATE توفر مرونةً أكبر من دالة SUBTOTAL لحساب المتوسطات على البيانات المُرشَّحة، مع خيارات إضافية للتعامل مع الأخطاء والصفوف المخفية. إليك كيفية تطبيقها:

• في خلية فارغة، أدخل الصيغة التالية (بافتراض أن بياناتك المُرشَّحة موجودة في C12:C24):

=AGGREGATE(1,5, C12:C24)

• الوسيط الأول ()1) يُحدِّد دالة AVERAGE، تمامًا كما في دالة SUBTOTAL.
• الوسيط الثاني ()5) يُوجِّه دالة AGGREGATE لتجاهل الصفوف المخفية (المُرشَّحة) والأخطاء.

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

ماكرو VBA لحساب المتوسط للخلايا المرئية فعليًا فقط

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

1. انتقِل إلى علامة التبويبDeveloper في Excel، ثم حدِّدVisual Basic لفتح محرر VBA. داخل المحرر، انقر فوقInsert > Module لإنشاء وحدة جديدة.
2. انسخ والصق كود VBA التالي في نافذة الوحدة:

Sub AverageVisibleCells()
    Dim rng As Range
    Dim cell As Range
    Dim sum As Double
    Dim count As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select range to average only visible cells", xTitleId, rng.Address, Type:=8)
    
    sum = 0
    count = 0
    
    For Each cell In rng
        If Not cell.EntireRow.Hidden And cell.Rows.Hidden = False And cell.Columns.Hidden = False Then
            If cell.DisplayFormat.Hidden = False And IsNumeric(cell.Value) And cell.Value <> "" Then
                sum = sum + cell.Value
                count = count + 1
            End If
        End If
    Next cell
    
    If count > 0 Then
        MsgBox "Average of visible cells is: " & sum / count, vbInformation, xTitleId
    Else
        MsgBox "No visible numeric cells found.", vbExclamation, xTitleId
    End If
End Sub

3. أغلق محرر VBA، ثم عُد إلى ورقة العمل الخاصة بك واضغطAlt+F8، وحدّدAverageVisibleCells، ثم انقر فوقRun. عند المطالبة، حدد نطاق البيانات الخاص بك. سيقوم الماكرو بعد ذلك بحساب وعرض متوسط الخلايا الرقمية المرئية حاليًا (أي غير المُرشَّحة وغير المخفية) فقط.

المتوسط المرجّح

حساب متوسط/معدل النمو السنوي المركب

المتوسط المتحرك/المتدحرج

المجموع التراكمي /المتوسط

متوسط معدل التغيّر