Excel で別の列の条件に基づいて列の平均を計算するには、どうすればよいですか？
多くの実務的なExcel シナリオでは、ある列の値を別の列の一致するエントリに基づいてグループ化またはフィルタリングし、その平均を計算する必要があります。たとえば、営業担当者別や地域別に平均売上を算出したい場合があります（下のスクリーンショット参照）。こうした計算は、サマリーレポート作成、パフォーマンス分析、データレビューなどで頻繁に活用されます。本記事では、この目的を達成するためのいくつかの効果的な方法をご紹介し、お客様のニーズとスキルレベルに最も適したアプローチを選べるようにサポートします。
数式を使用して別の列の同じ値に基づき列の平均を計算する
Kutools for Excel を使用して別の列の同じ値に基づき列の平均を計算する
PivotTable を使用してグループごとの平均を計算する
VBA マクロでグループ化された平均計算を自動化する
数式を使用して別の列の同じ値に基づき列の平均を計算する
Excel で別の列に基づいてグループの平均を計算する最も簡単な方法の一つは、AVERAGEIF や AVERAGEIFS などの条件付き数式を使うことです。この方法は、特定の都市や営業担当者など、特定の条件に対するターゲット結果を求めたいときに特に効果的です。
1。結果を表示したい空白セルを選択し、次の数式を入力してEnterキーを押してください。
=AVERAGEIF(B2:B13,E2,C2:C13)
パラメーターの説明：上記の数式において、B2:B13は条件をチェックする範囲（例：都市名や営業担当者）、E2は比較対象となる特定の値（例：「Owenton」）、C2:C13は平均を計算する数値が含まれる範囲です。
Enter キーを押すと、E2 セルで指定されたグループ（例：「Owenton」）の平均売上が即座に表示されます。
条件列の各一意の値について平均を計算する必要がある場合は、条件セル（E2）の値を適宜変更するか、一意のエントリのリストがある場合は数式を下方向にコピーしてください。
実用的なヒント：大規模なデータセットや多数の一意のグループを扱う際は、「重複の削除」機能や Office 365 およびExcel 2021 で利用可能な UNIQUE 関数を使って一意の値リストを作成し、この数式と組み合わせることで、すべてのグループ平均を一度に計算するプロセスを大幅に高速化できます。数式をコピーする際には、数式内の範囲が意図したデータ全体を正しくカバーしており、整合性が保たれていることを再度ご確認ください。
一般的なエラーとトラブルシューティング：
- #DIV/0! エラーが表示された場合は、条件値が実際に「範囲を選択してください」内に存在するかどうかをご確認ください。
- 数値範囲には有効な数値のみを含めるようにしてください。文字列や空白セルが含まれていると、計算結果に影響を与える可能性があります。
Kutools for Excel を使用して別の列の同じ値に基づき列の平均を計算する
すべての一意の値について、数式を繰り返し入力したり手動でフィルタリングしたりすることなく自動的に平均を計算したいなら、Kutools for Excel がこの作業を効率化します。特に大規模なリストや複雑なデータセットを扱う際にその真価を発揮します。
1。グループ列と平均を算出したい数値列の両方を含むデータ範囲全体を選択し、次にKutools>結合と分割>高度な行のマージをクリックします。
2。「行の結合 Based on Column」ダイアログボックスで、次の手順に従ってください。
- グループ化したい列（例：都市や営業担当者）を選択し、主キーボタンをクリックして、その列をグループ化フィールドとして設定してください。
- 平均を算出したい数値列を選択し、次に計算 > 平均をクリックします。
ヒント：日付などの他の列については、その値をどのように結合するか（例：カンマで連結）を指定できます。 - 操作を実行するには、OKをクリックしてください。
Kutools は、選択したキーに基づいてデータを瞬時にグループ化し、各グループの数値列の平均を返します。
Kutools のバッチ処理機能は、月次レポートや部門別サマリー、その他のマルチグループ集計など、定期的にグループ統計を分析するユーザーに最適です。アドインであるKutools は元のデータ構造を一切変更せず、プレビュー機能で適用前にグループ化の内容をしっかり確認できます。
注意点とヒント：
- ツールを実行する前に、範囲を選択し、その中に空の行がないことを確認してください。
- グループ化された平均値を他の場所で使いたい場合は、計算後に結果をコピー＆ペーストして転記してください。
- 非常に大規模なデータセットでは、グループ化列と数値列が正しく割り当てられていることを確認し、混乱を防ぎましょう。
Kutools for Excel— 300 以上の必須ツールでExcel を強化し、作業をより迅速・簡単に。AI 機能を活用して、スマートなデータ処理と生産性の飛躍的な向上を実現します。今すぐ入手
PivotTable を使用してグループごとの平均を計算する
ピボットテーブルは、数式やサードパーティ製アドインを一切使わずに、データを集計・グループ化・分析できる強力な組み込み機能を提供します。これにはグループごとの平均値の計算も含まれており、さまざまなカテゴリごとの平均や合計をインタラクティブに表示したい場合に最適です。小規模から非常に大規模なデータセットまで幅広く対応可能です。
PivotTable でグループ平均を計算するための設定方法：
- データセット内の任意のセルを選択し、挿入 > ピボットテーブルをクリックします。ダイアログボックスで、ピボットテーブルを新しいワークシートに配置するか、既存のワークシートに配置するかを選択し、OKをクリックします。
- ピボットテーブルのフィールドペインで、グループ化したい列を行エリアにドラッグしてください（例：「都市」や「営業担当者」）。
- 平均を算出したい数値列（例：「売上」）を値エリアにドラッグします。既定ではExcel が合計を計算することがありますが、これを変更するには、値フィールドをクリックして値フィールドの設定を開き、平均を選択してください。
ピボットテーブルは、各グループごとの平均値を即座に表示します。必要に応じて、レポートを簡単にフィルタリング・並べ替え・書式設定できます。この方法はユーザーフレンドリーで、数式エラーのリスクを回避できます。
メリット：インタラクティブで、大容量データを効率的に処理でき、複数の統計情報を同時に集計できます。
デメリット：結果は単純なリストではなく、PivotTable 形式で表示されます。ソースデータが変更された場合は、定期的な更新が必要です。
ヒント：PivotTable 内の任意の集計セルをダブルクリックすると、そのグループの元データを含む新しいワークシートが開き、詳細の監査や不整合のトラブルシューティングが簡単にできるようになります。
一般的な問題点：
- 平均が取得できない場合は、値フィールドの設定が「平均」に設定されていることを確認してください。
- ソースデータに、ピボットテーブルのレイアウトを崩す原因となる余分な空白行や列がないかご確認ください。
VBA マクロでグループ化された平均計算を自動化する
多数のグループの平均を頻繁に計算する必要がある場合や、サマリーを自動生成したい場合は、VBA マクロを作成することで手作業を大幅に削減できます。VBA は、データ構造が一定であることが予想される場合や、ボタン一発で繰り返しサマリーレポートを生成したい場合に特に役立ちます。
開始する前に、ワークブックを保存し、マクロを有効にしてください。以下に、使い始める手順を示します。
1。クリックして開発者ツール > Visual Basicを選択し、VBA エディターを開きます。エディター内で、挿入 > 標準モジュールをクリックして、新しいコードモジュールを作成してください。以下のコードをそのモジュールに貼り付けてください。
Sub GroupAverageSummary()
Dim srcSheet As Worksheet
Dim dstSheet As Worksheet
Dim dict As Object
Dim groupCol As Range, valueCol As Range
Dim lastRow As Long
Dim i As Long
Dim groupKey As Variant
Dim sumArr As Object, countArr As Object
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set srcSheet = ActiveSheet
Set dict = CreateObject("Scripting.Dictionary")
' Prompt user to select group (criteria) column
Set groupCol = Application.InputBox("Select the group (criteria) column:", xTitleId, Type:=8)
If groupCol Is Nothing Then Exit Sub
' Prompt user to select value column
Set valueCol = Application.InputBox("Select the value column to average:", xTitleId, Type:=8)
If valueCol Is Nothing Then Exit Sub
Set sumArr = CreateObject("Scripting.Dictionary")
Set countArr = CreateObject("Scripting.Dictionary")
For i = 1 To groupCol.Rows.Count
groupKey = groupCol.Cells(i, 1).Value
If groupKey <> "" And IsNumeric(valueCol.Cells(i, 1).Value) Then
If Not dict.Exists(groupKey) Then
dict.Add groupKey, 0
sumArr.Add groupKey, 0
countArr.Add groupKey, 0
End If
sumArr(groupKey) = sumArr(groupKey) + valueCol.Cells(i, 1).Value
countArr(groupKey) = countArr(groupKey) + 1
End If
Next
' Output result to a new worksheet
Set dstSheet = Worksheets.Add
dstSheet.Name = "Group Average Summary"
dstSheet.Cells(1, 1).Value = "Group"
dstSheet.Cells(1, 2).Value = "Average"
i = 2
For Each groupKey In dict.Keys
dstSheet.Cells(i, 1).Value = groupKey
dstSheet.Cells(i, 2).Value = sumArr(groupKey) / countArr(groupKey)
i = i + 1
Next
End Sub2。コードを挿入したら、VBA エディターを閉じてExcel に戻り、Alt+F8を押してください。リストからGroupAverageSummaryを選択し、実行をクリックすると、マクロがグループ（条件）列と値（数値）列の選択を促します。選択が完了すると、自動的に「Group Average Summary」という名前の新しいワークシートが作成され、各ユニークなグループとその平均値が表示されます。
パラメーターと操作に関する注意事項：
- グループ列と値列が同じ長さで、かつ有効なデータのみを含んでいることをご確認ください（部分的な選択は避けてください）。
- このマクロは、必要に応じてさらに高度なグループ化や追加の集計統計情報にも対応できるようカスタマイズ可能です。
- ワークシート内にすでに「グループ平均サマリー」という名前のシートが存在する場合、マクロはデフォルトのワークシート名で新規シートを作成します。
トラブルシューティング：
- 「添字が範囲外です」などのメッセージが表示された場合は、選択範囲が正しく対応しており、かつ同じワークシート上にあることをご確認ください。
- 最良の結果を得るには、値列に数値データのみが含まれていることをご確認ください。数値範囲内の文字列や空白セルは、マクロによってスキップされます。
このマクロは、バッチ処理や自動化レポート、または新しいデータや更新されたデータセットを頻繁に集計する必要がある状況に最適です。
デモ：Kutools for Excel を使用して、別の列の同じ値に基づいて列の平均値を計算する
最高の Office 業務効率化ツール
| 🤖 | KUTOOLS AI アシスタント：次に基づいてデータ分析を革新します：インテリジェント実行 ｜ コード生成｜ カスタム数式作成 ｜ データ分析とチャート生成｜ 拡張機能呼び出し… |
| 人気の機能：検索・ハイライト、または重複をマーキング ｜ 空白行を削除する ｜ データを失うことなく列の結合またはセルを ｜ 数式を使用しない四捨五入... | |
| スーパー LOOKUP：複数条件 VLookup ｜ 複数値 VLookup ｜ 複数シート間 VLookup ｜ ファジーマッチ.... | |
| 高度なドロップダウンリスト：ドロップダウンリストをすばやく作成 ｜ 連動型ドロップダウンリスト ｜ 複数選択可能なドロップダウンリスト.... | |
| 列マネージャー：指定した数の列を追加｜列の移動｜非表示列の表示状態を切り替え｜範囲および列の比較... | |
| 注目の機能：グリッドフォーカス ｜ デザインビュー ｜強化された数式バー ｜ ワークブックとシートマネージャー ｜ リソースライブラリ（オートテキスト）｜ 日付ピッカー ｜ ワークシートの統合 ｜ 暗号化／セルの復号化 ｜ リストからメール送信 ｜ スーパーフィルター ｜ 特殊フィルタ（太字のフォントを持つセルをフィルタリング／斜体／取り消し線。。。） 。。。 | |
| トップ15 ツールセット：12 テキストツール（テキストの追加、特定の文字を削除、...）｜ 50+チャートタイプ（ガントチャート、...）｜ 40+実用的関数（誕生日に基づいて年齢を計算します、...）｜ 19 挿入ツール（QR コードを挿入、パスから画像を挿入、...）｜ 12 変換ツール（単語に変換する、為替レートの変換、...）｜ 7 結合と分割ツール（高度な行のマージ、セルの分割、...）｜さらに多数 |
Kutools for Excel でExcel スキルを強化し、これまでにない効率を体験しましょう。Kutools for Excel は、生産性を高め、時間を大幅に節約できる高度な機能を300 以上提供します。最も必要な機能を今すぐ入手するにはこちらをクリック。。。
Office Tab は Office にタブインターフェースをもたらし、作業を大幅に簡単にします
- Word、Excel、PowerPoint でタブを使った編集と閲覧を有効にします。Publisher、Access、Visio、Project でもご利用いただけます。
- 複数のドキュメントを、新しいウィンドウではなく、同じウィンドウ内の新しいタブで開いたり作成したりできます。
- 日々の生産性を50％も向上させ、毎日数百回ものマウスクリックを削減します！
すべてのKutools アドインが、たった1 つのインストーラーで完結。
Kutools for Officeスイートには、Excel ・Word ・Outlook ・PowerPoint 用のアドインと Office Tab Pro が含まれており、複数の Office アプリを横断して作業するチームに最適です。
- オールインワンスイート— Excel、Word、Outlook、PowerPoint 用アドイン＋Office Tab Pro
- インストーラー1 つ、ライセンス1 つ— 数分でセットアップ可能（MSI 対応）
- 連携してさらにパワーアップ— Office アプリ全体で生産性が向上
- 30 日間のフル機能トライアル— 登録不要、クレジットカード不要
- 最高のお得感— 個別アドイン購入よりお得