メインコンテンツへスキップ

Kutools for Office — 1つのスイート、5つのツールで、もっと多くのことを成し遂げましょう。

Excelで加重平均を計算するにはどうすればよいですか?

Author Kelly Last modified

加重平均は、異なる項目が全体の結果に不均等に寄与する場合に一般的に使用されます。例えば、製品価格、重量、数量が含まれる買い物リストを分析する場合、Excelの通常のAVERAGE関数を使用すると単純な算術平均しか計算されず、項目がどれだけ頻繁に現れるかやどれだけ重いかは考慮されません。しかし、多くのビジネスや予算のケースでは、数量や重量を考慮した単位あたりの平均価格など、各項目の影響がその重要性に比例するように加重平均を計算する必要があります。この記事では、Excelでの加重平均の計算方法について説明し、特定の基準に基づく状況や、より動的または複雑な要件に対応するためのVBAやピボットテーブルを使ったさらなる手法も紹介します。

Excelで加重平均を計算する

Excelで指定された基準を満たす場合の加重平均を計算する

VBAコード – 動的な範囲や複数の基準に対する加重平均計算を自動化


Excelで加重平均を計算する

下のスクリーンショットに示すような買い物リストがあると仮定しましょう。ExcelのAVERAGE関数は重量や数量を考慮せずに平均価格を提供しますが、このような場合には、加重平均を計算することがより正確なアプローチです。これにより、重量または頻度が高い項目が最終結果に強い影響を与えるため、実際の単位あたりのコストをよりよく反映できます。

a screenshot showing the original data

加重平均価格を計算するには、次のようにSUMPRODUCT関数とSUM関数を組み合わせて使用します:

F2などの空白セルを選択し、次の数式を入力します:

=SUMPRODUCT(C2:C18,D2:D18)/SUM(C2:C18)

そして、Enterキーを押して結果を得ます。

a screenshot showing how to use the formula to calculate weighted average

注意: この数式では、C2:C18は「重量」列を、D2:D18は「価格」列を指します。自分のデータレイアウトに応じてこれらの範囲を調整してください。SUMPRODUCT関数は各重量を対応する価格で乗算し、結果を合計します。一方、SUM関数は重量の合計を計算します。これにより正しい加重平均が得られます。範囲の長さが等しいことを確認し、データ内に不一致や空のセルがないようにしてください。そうしないと計算エラーが発生する可能性があります。

計算された加重平均が表示される小数点以下の桁数が多すぎる、または少なすぎる場合、セルを選択してから、「 小数点以下の表示桁数を増やす ボタン a screenshot of the Increase Decimal button または 小数点以下の表示桁数を減らす ボタン a screenshot of the Decrease Decimal button をクリックして、必要に応じて表示される小数点以下の桁数を調整します。 ホーム タブで調整します。

a screenshot of selecting one of the decimal type

#VALUE! などのエラーが発生した場合、参照されている各セルに数値が含まれていることと、範囲が一貫していることを再確認してください。また、正確な結果を得るために、計算範囲にヘッダー行を含めないようにしてください。大きなデータセットを扱う場合は、明確さと保守の容易さのために名前付き範囲を使用することを検討してください。


Excelで指定された基準を満たす場合の加重平均を計算する

前の数式はすべての項目の加重平均価格を計算します。実際の分析では、特定のカテゴリ(例:リンゴのみの加重平均価格)を求めたい場合があります。そのような場合、条件に基づいて数式を強化することができます。

これを行うには、F8などの空白セルを選択し、次の数式を入力します:

=SUMPRODUCT((B2:B18="Apple")*C2:C18*D2:D18)/SUMIF(B2:B18,"Apple",C2:C18)

その後、Enterキーを押して指定された基準を満たす加重平均を計算します。この数式は、項目が条件(この場合は「リンゴ」)に一致する場合にのみ、各重量と価格のペアを乗算し、それらを合計し、その項目の重量の合計で割ります。

a screenshot showing how to use formula to calculate weighted average if meeting given criteria

注意: ここでは、B2:B18は「果物」列、C2:C18は「重量」、D2:D18は「価格」です。「リンゴ」を他の項目に置き換えてください。この方法は1つの条件でフィルタリングする場合にうまく機能します。複数の条件(例:果物の種類と仕入れ先)でフィルタリングする必要がある場合は、ヘルパー列またはより高度な数式が必要になる場合があります。

数式を適用した後、明瞭にするために小数点以下の桁数を調整したい場合があります。結果のセルを選択し、「 小数点以下の表示桁数を増やす a screenshot of the Increase Decimal button または 小数点以下の表示桁数を減らす a screenshot of the Decrease Decimal button2 ボタンを「 ホーム 」タブで使用して、表示される小数点以下の桁数を変更します。

a screenshot of selecting one of the decimal type2

数式が予期しない結果を返した場合、基準がターゲット範囲内で一致していることを確認し、数値列に空白セルやテキストエントリがないか確認してください。


VBAコード – 動的なデータ範囲や複数の基準に対応する加重平均計算を自動化

状況によっては、頻繁にサイズが変わる範囲、欠損値を含む範囲、または一度に複数の基準を適用する必要がある範囲で加重平均を計算する必要があります。手動で数式や範囲を更新する代わりに、VBAマクロで計算を自動化することで時間を節約し、特に大規模なデータセットや定期的に更新されるデータセットを扱う際にエラーのリスクを軽減できます。

以下は、加重平均用のVBAマクロを作成して使用する方法です:

1. 「開発」>「Visual Basic」(またはAlt + F11を押す)をクリックしてMicrosoft Visual Basic for Applicationsエディタウィンドウを開きます。次に、「挿入」>「モジュール」をクリックし、新しいモジュールウィンドウに以下のコードを貼り付けます:

Sub WeightedAverageVBA()
    Dim rngCriteria As Range
    Dim rngWeight As Range
    Dim rngValue As Range
    Dim criteriaStr As String
    Dim totalWeighted As Double
    Dim totalWeight As Double
    Dim i As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rngCriteria = Application.InputBox("Select the range for criteria (optional, press Cancel to skip):", xTitleId, Type:=8)
    criteriaStr = Application.InputBox("Enter criteria for filtering (leave blank for all):", xTitleId, Type:=2)
    Set rngWeight = Application.InputBox("Select the Weight (numeric) range:", xTitleId, Type:=8)
    Set rngValue = Application.InputBox("Select the Value (e.g. Price) range:", xTitleId, Type:=8)
    
    totalWeighted = 0
    totalWeight = 0
    
    If rngCriteria Is Nothing Or criteriaStr = "" Then
        For i = 1 To rngWeight.Cells.Count
            If IsNumeric(rngWeight.Cells(i).Value) And IsNumeric(rngValue.Cells(i).Value) Then
                totalWeighted = totalWeighted + rngWeight.Cells(i).Value * rngValue.Cells(i).Value
                totalWeight = totalWeight + rngWeight.Cells(i).Value
            End If
        Next i
    Else
        For i = 1 To rngWeight.Cells.Count
            If rngCriteria.Cells(i).Value = criteriaStr Then
                If IsNumeric(rngWeight.Cells(i).Value) And IsNumeric(rngValue.Cells(i).Value) Then
                    totalWeighted = totalWeighted + rngWeight.Cells(i).Value * rngValue.Cells(i).Value
                    totalWeight = totalWeight + rngWeight.Cells(i).Value
                End If
            End If
        Next i
    End If
    
    If totalWeight = 0 Then
        MsgBox "Weighted average cannot be calculated: total weight is zero.", vbExclamation, xTitleId
    Else
        MsgBox "Weighted average: " & totalWeighted / totalWeight, vbInformation, xTitleId
    End If
End Sub

2を押して F5 (または Run button 実行ボタン)をクリックして実行します。
範囲を選択するよう求められます(基準範囲—必要ない場合はスキップ可能、重量範囲、値範囲)。特定の基準を入力して計算をフィルタリングすることも、すべてのデータを考慮するために空白のままにすることもできます。このマクロは動的なデータ範囲をサポートしており、テーブルが定期的に拡大または変更される場合に実用的です。

最後に、加重平均の結果をリストアップしたメッセージボックスが表示されます。

ヒント:

  • このアプローチは繰り返される加重平均分析を自動化し、さらに追加のフィルタリングや出力オプションを処理できるように拡張できます。
  • 選択した範囲の長さが等しく、データ型が一貫していることを確認してください。
  • 例として示した基本的なエラーハンドリングを含めてください(有効な重量が見つからない場合や重量の合計がゼロの場合など)。
  • フィルターされた/表示されている行のみに適用したい場合は、特殊なセル列挙を使用してコードをさらに強化できます。

権限やマクロのセキュリティの問題が発生した場合、コードを実行する前にExcelの設定でマクロが有効になっていることを確認してください。


関連記事:


最高のオフィス業務効率化ツール

🤖 Kutools AI Aide:データ分析を革新します。主な機能:Intelligent Executionコード生成カスタム数式の作成データの分析とグラフの生成Kutools Functionsの呼び出し……
人気の機能重複の検索・ハイライト・重複をマーキング空白行を削除データを失わずに列またはセルを統合丸める……
スーパーLOOKUP複数条件でのVLookup複数値でのVLookup複数シートの検索ファジーマッチ……
高度なドロップダウンリストドロップダウンリストを素早く作成連動ドロップダウンリスト複数選択ドロップダウンリスト……
列マネージャー指定した数の列を追加列の移動非表示列の表示/非表示の切替範囲&列の比較……
注目の機能グリッドフォーカスデザインビュー強化された数式バーワークブック&ワークシートの管理オートテキスト ライブラリ日付ピッカーデータの統合セルの暗号化/復号化リストで電子メールを送信スーパーフィルター特殊フィルタ(太字/斜体/取り消し線などをフィルター)……
トップ15ツールセット12 種類テキストツールテキストの追加特定の文字を削除など)50種類以上のグラフガントチャートなど)40種類以上の便利な数式誕生日に基づいて年齢を計算するなど)19 種類の挿入ツールQRコードの挿入パスから画像の挿入など)12 種類の変換ツール単語に変換する通貨変換など)7種の統合&分割ツール高度な行のマージセルの分割など)… その他多数
Kutoolsはお好みの言語で利用可能 ― 英語、スペイン語、ドイツ語、フランス語、中国語、その他40以上の言語に対応!

Kutools for ExcelでExcelスキルを強化し、これまでにない効率を体感しましょう。 Kutools for Excelは300以上の高度な機能で生産性向上と保存時間を実現します。最も必要な機能はこちらをクリック...


Office TabでOfficeにタブインターフェースを追加し、作業をもっと簡単に

  • Word、Excel、PowerPointでタブによる編集・閲覧を実現
  • 新しいウィンドウを開かず、同じウィンドウの新しいタブで複数のドキュメントを開いたり作成できます。
  • 生産性が50%向上し、毎日のマウスクリック数を何百回も削減!

全てのKutoolsアドインを一つのインストーラーで

Kutools for Officeスイートは、Excel、Word、Outlook、PowerPoint用アドインとOffice Tab Proをまとめて提供。Officeアプリを横断して働くチームに最適です。

Excel Word Outlook Tabs PowerPoint
  • オールインワンスイート — Excel、Word、Outlook、PowerPoint用アドインとOffice Tab Proが含まれます
  • 1つのインストーラー・1つのライセンス —— 数分でセットアップ完了(MSI対応)
  • 一括管理でより効率的 —— Officeアプリ間で快適な生産性を発揮
  • 30日間フル機能お試し —— 登録やクレジットカード不要
  • コストパフォーマンス最適 —— 個別購入よりお得