میانگین گیری بدون سلول های پنهان

Question

سوزی یک کاربرگ دارد که اطلاعات کمی در آن وجود دارد. برای او غیرعادی نیست که ستون ها را در کاربرگ پنهان کند. او به فرمولی نیاز دارد که میانگین طیف وسیعی از سلول ها را، همه در یک ردیف، برگرداند، در حالی که سلول های پنهان را نادیده می گیرد. برای مثال، اگر سوزی B7:G7 را انتخاب کند، می خواهد میانگین سلول های موجود در آن محدوده قابل مشاهده باشد (ستون های D و E، در این مورد، پنهان هستند).

هیچ تابع ذاتی در اکسل برای محاسبه چنین میانگینی وجود ندارد. اگر می خواهید میانگین طیفی از سلول ها را در یک ستون بیابید، می توانید از تابع SUBTOTAL به این صورت استفاده کنید:

=SUBTOTAL(101,A7:A12)

با این حال، زمانی که محدوده ارائه شده در یک ردیف باشد، تابع SUBTOTAL مقدار دقیقی را بر نمی گرداند. در صورت تمایل می توانید از سلول های کمکی برای انجام محاسبات استفاده کنید. فقط موارد زیر را در سلول B8 قرار دهید:

=(CELL("width",B7)>0)+0

سلول B8 را در محدوده C8:G8 کپی کنید. نتیجه این است که هر سلول در محدوده B8:G8 بر اساس مخفی بودن یا نبودن ستون دارای 0 یا 1 خواهد بود. سپس می توانید از فرمول زیر برای تعیین میانگین استفاده کنید:

=SUMIFS(B7:G7,B8:G8,">0")/SUM(B8:G8)

میانگین را فقط برای ستون هایی محاسبه می کند که سلول های B8:G8 حاوی مقدار 1 هستند (آنها پنهان نیستند).

البته، ممکن است نتوانید از سلول های کمکی استفاده کنید و ممکن است ایجاد یک تابع تعریف شده توسط کاربر برای محاسبه میانگین مفیدتر باشد. موارد زیر خیلی سریع کار می کند:

Function AverageVisible(rng As Range)
    Dim rCell As Range
    Dim iCount As Integer
    Dim dTtl As Double

    iCount = 0
    dTtl = 0
    For Each rCell In rng
        If rCell.ColumnWidth > 0 _
          And rCell.RowHeight > 0 _
          And Not IsEmpty(rCell) _
          And IsNumeric(rCell.Value) Then
              dTtl = dTtl + rCell
              iCount = iCount + 1
        End If
    Next
    If iCount > 0 Then
        AverageVisible = dTtl / iCount
    Else
        AverageVisible = 0
    End If
End Function

برای استفاده از تابع، به سادگی این فرمول را در سلولی که می خواهید میانگین را در آن قرار دهید قرار دهید:

=AverageVisible(B7:G7)

این تابع تمام سلول های محدوده را بررسی می کند (به این معنی که می توانید از آن در ردیف ها، ستون ها یا در واقع هر محدوده ای استفاده کنید) تا مطمئن شود که پنهان نیست و خالی نیست. اگر سلول حاوی مقدار عددی باشد، برای محاسبه میانگین از آن استفاده می شود. اگر محدوده ای که مشخص می کنید حاوی مقادیری نباشد که بتوان میانگین آن را محاسبه کرد، آنگاه تابع مقدار 0 را برمی گرداند.

هر بار که کاربرگ دوباره محاسبه می شود، این تابع به طور خودکار اجرا می شود. اگر ستون های پنهان را تغییر دهید، اکسل به طور خودکار دوباره محاسبه نمی شود. بنابراین، شما باید پس از پنهان کردن یا بازکردن ستون ها، دوباره محاسبه را انجام دهید.