Excel 에서 필터링된 셀/목록의 평균을 계산하는 방법은？

필터링된 데이터 집합에 직접 AVERAGE 함수를 적용하면 필터링으로 숨겨진 셀을 포함하여 모든 셀의 평균을 계산합니다. 따라서 표시된 행만 평균을 구하려는 경우 잘못된 결과가 나올 수 있습니다. 필터링된(표시된) 데이터의 정확한 평균을 얻으려면 Excel 의 SUBTOTAL 함수를 사용하는 것이 효율적인 해결책입니다。 SUBTOTAL 함수는 필터링 및 숨겨진 행에 따라 계산을 조정할 수 있어 이 목적에 적합합니다。

필터링된 결과만 대상으로 평균을 계산하려면 다음 단계를 따르세요：

• 평균을 계산하려는 열에서 필터링된 모든 데이터를 포함하는 범위를 식별합니다(이 예에서는 값이)C12:C24에 있다고 가정합니다)。
• 빈 셀에 다음 수식을 입력하세요：

=SUBTOTAL(1,C12:C24)

이 수식은 제한된 범위의 표시된(필터링된) 셀만 평균을 계산합니다()C12:C24)。 매개변수1은 SUBTOTAL 함수가 AVERAGE 함수를 사용하도록 지정하며, SUBTOTAL 은 자동으로 필터링으로 숨겨진 행을 무시합니다。

수식을 입력한 후Enter를 누르세요。 아래와 같이 표시된 행만의 평균값이 즉시 표시됩니다：

숨겨지거나 필터링된 셀/행/열을 무시하고 표시된 셀만 합계/개수/평균 계산

Excel 의 일반적인 SUM, COUNT 또는 AVERAGE 함수는 필터나 수동 숨김 여부와 관계없이 제한된 범위 내 모든 셀을 사용하여 계산합니다. 이러한 상황을 보다 강력하게 처리하려면 Kutools for Excel 을 사용할 수 있습니다。 전용SUMVISIBLE,COUNTVISIBLE， 그리고AVERAGEVISIBLE함수를 통해 필터링 및 수동으로 숨겨진 셀， 행， 열을 모두 제외하고 실제 표시된 셀만을 대상으로 합계， 개수， 평균을 쉽게 계산할 수 있습니다。 이 기능은 복잡한 스프레드시트에서 오류를 방지하고， 복잡한 수식이나 사용자 지정 코드를 사용하는 것보다 저장 시간합니다。

AGGREGATE 함수로 필터링된 데이터/목록 평균 계산

Excel 2010 이상 버전을 사용 중이라면AGGREGATE함수가 SUBTOTAL 보다 필터링된 데이터의 평균 계산에 더 많은 유연성을 제공하며， 오류 및 숨겨진 행 처리에 대한 추가 옵션도 포함되어 있습니다。 다음은 이를 적용하는 방법입니다：

• 빈 셀에 다음 수식을 입력하세요(필터링된 데이터가 C12:C24 에 있다고 가정함)：

=AGGREGATE(1,5, C12:C24)

• 첫 번째 인수()1)는 SUBTOTAL 과 마찬가지로 AVERAGE 함수를 지정합니다。
• 두 번째 인수()5)는 AGGREGATE 함수가 필터링으로 숨겨진 행과 오류를 무시하도록 지시합니다。

수식 입력 후Enter를 눌러 필터 범위에서 표시된 행의 평균을 즉시 표시하세요。 행 숨김 방식이나 다른 집계 함수에 맞게 조정하려면 두 번째 매개변수만 적절히 변경하면 됩니다。

실제 표시된 셀만 평균을 계산하는 VBA 매크로

더 고급적이거나 맞춤화된 요구 사항이 있는 경우 간단한 VBA 매크로를 사용해 범위 선택에서 표시된(숨겨지지 않고 필터링되지 않은) 셀만 평균을 계산할 수 있습니다。 특히 여러 방식으로 데이터를 숨기는 시트에서 유용합니다。 다음은 이를 수행하는 방법입니다：

1. Excel 에서개발자탭으로 이동하여Visual Basic을 선택해 VBA 편집기를 엽니다。 편집기에서삽입>모듈을 클릭하여 새 모듈을 만듭니다。
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를 선택한 후실행을 클릭하세요。 메시지가 표시되면 대상 데이터 범위을 선택합니다。 그러면 매크로가 현재 표시된(필터링되지 않고 숨겨지지 않은) 숫자 셀만의 평균을 계산하여 표시합니다。

가중 평균

평균/연평균 성장률 계산

이동/롤링 평균

누적 합계 /평균

평균 변화율