Excel でフィルター済みのセルやリストの平均を計算するには、どうすればよいですか？

フィルター済みデータセットに直接 AVERAGE 関数を適用すると、フィルターで非表示になっているセルも含めて、指定範囲内のすべてのセルの平均が計算されてしまいます。そのため、フィルター後に表示されている可視行のみの平均を求めたい場合には、誤った結果となってしまいます。こうした課題に対しては、Excel の SUBTOTAL 関数が効率的かつ正確な解決策となります。SUBTOTAL 関数は、フィルターや非表示行を自動的に考慮して計算を行うため、フィルター済み（可視）データの平均を正しく取得するのに最適です。

フィルター結果のみの平均を計算するには、以下の手順に従ってください：

• 平均を計算したい列のフィルター済みデータすべてを含む範囲を特定します（この例では、「金額」列の値が)C12:C24にあると仮定します）。
• 空白セルに次の数式を入力します：

=SUBTOTAL(1,C12:C24)

この数式は、制限された範囲（)C12:C24）に含まれる可視セル（フィルター済み）の平均を計算します。パラメーター1は SUBTOTAL 関数に AVERAGE 関数を使用するよう指示し、SUBTOTAL 関数は自動的にフィルターで非表示になった行を無視します。

数式を入力したら、Enterキーを押してください。すると、可視行のみの平均値が即座に表示されます（下図参照）：

非表示またはフィルターされたセル／行／列を無視して、可視セルのみを合計／カウント／平均

通常の SUM、COUNT、または AVERAGE 関数は、フィルターや手動での非表示に関係なく、指定範囲内のすべてのセルを対象に計算を行います。こうした課題に対応する強力なソリューションとして、Kutools for Excelをご活用ください。専用のSUMVISIBLE、COUNTVISIBLE、およびAVERAGEVISIBLE 関数を使えば、フィルター適用後や手動で非表示にされたセル・行・列を自動的に除外し、実際に表示されているセルのみを対象に、合計・件数・平均を簡単に算出できます。この機能により、複雑なスプレッドシートでの計算ミスを防ぎ、煩雑な数式やカスタムコードに頼るよりも大幅に時間を節約できます。

AGGREGATE 関数でフィルター済みデータ／リストの平均を計算

Excel 2010 以降をご利用の場合、AGGREGATE 関数を使えば、SUBTOTAL 関数よりもフィルター済みデータの平均計算でさらに高い柔軟性を実現できます。エラーや非表示行の処理に関する追加オプションも搭載されています。以下にその使い方をご紹介します：

• 空白セルに次の数式を入力します（フィルター済みデータが C12:C24 にあると仮定）：

=AGGREGATE(1,5, C12:C24)

• 第1 引数（)1）では、SUBTOTAL 関数と同様に AVERAGE 関数を指定します。
• 第2 引数（)5）は、AGGREGATE 関数にフィルターで非表示になった行やエラーを無視するよう指示します。

数式を入力した後、Enterキーを押すと、フィルタリング範囲内の可視行の平均が即座に表示されます。行の非表示方法や使用する集計関数に応じて調整したい場合は、第2 パラメーターを適宜変更してください。

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を選択し、実行をクリックしてください。プロンプトが表示されたら、対象のデータ範囲を選択すると、マクロが現在表示されている（フィルターや非表示でない）数値セルの平均値のみを計算・表示します。

加重平均

平均／年次複合成長率の計算

移動平均／ローリング平均

累積合計／累積平均

平均変化率