Excelのピボットテーブルで加重平均を計算する方法【補助列を活用】

この記事では、Excelのピボットテーブルで加重平均を計算する方法を詳しく解説します。ピボットテーブル内で加重平均を求めるのは少し複雑です。通常、Excelのワークシート上では関数を使って加重平均を簡単に算出できますが、ピボットテーブルの中ではExcel関数を直接使用できません。そのため、別の手法を活用する必要があります。

練習用ワークブックのダウンロード

この記事で使用している練習用ワークブックは、以下からダウンロードできます。実際に手を動かしながら学習することで、理解がより深まります。

ピボットテーブルで加重平均を求める簡単な方法

補助列（ヘルパー列）を追加して加重平均を計算する

加重平均とは、平均を求める対象となるそれぞれの数値に「重み」を設定して計算する平均のことです。この計算方法により、各数値が全体の平均に対してどれだけ重要かを反映できます。すべての数値に同じ重みを割り当てる一般的な平均と比べ、加重平均の方が実態に即した正確な結果を得られると言われています。

通常、Excelで加重平均を計算するには、SUMPRODUCT関数とSUM関数を組み合わせて使用します。しかし、ピボットテーブル内では関数が使えないため、ここでは別のアプローチを採用します。具体的には、ピボットテーブルの元データに補助列を追加し、そこから加重平均を算出します。

データセットの紹介

例として、食料品各商品の日別売上データを含むデータセットを使用します。このデータをもとに、ピボットテーブルで各食料品の加重平均価格を計算していきます。

それでは、具体的な手順を見ていきましょう。

ステップ1：補助列を追加する

• まず、上記の表に「売上金額」という補助列を新たに追加します。次に、この新しい列の最初のセルに以下の数式を入力します。

=D5*E5

• 数式を入力すると、下図のような結果が表示されます。続けて、フィルハンドル（＋）を使って、数式を列の残りのセルへコピーします。

• すると、以下のようにすべての行に売上金額が計算されます。

ステップ2：ピボットテーブルを作成する

• まず、データセット（B4:F14）内の任意のセルをクリックします。

• 次に、リボンの挿入 ＞ ピボットテーブル ＞ テーブル/範囲からを選択します。

• 「テーブル/範囲からピボットテーブルを作成」ダイアログが表示されます。テーブル/範囲の指定が正しければ、OKをクリックします。

• すると、新しいシートにピボットテーブルが作成されます。続いて、以下のスクリーンショットのようにピボットテーブルフィールドを設定してください。

• 設定が完了すると、次のようなピボットテーブルが表示されます。

ステップ3：集計フィールドで加重平均を分析する

• まず、ピボットテーブル内のセルを選択します。
• 次に、リボンのピボットテーブル分析 ＞ フィールド、アイテム＆セット ＞ 集計フィールドの順にクリックします。

• 「集計フィールドの挿入」ダイアログが表示されます。
• 名前欄に「Weighted Average（加重平均）」と入力します。
• 続いて、補助列の合計を重み（数量）で割る数式「売上金額 / 数量」を設定し、加重平均を求めます。
• 最後にOKをクリックします。

• これで、ピボットテーブルの小計行に、各食料品ごとの加重平均価格が表示されます。

関連記事：Excelで複数条件を満たす条件付き加重平均を計算する方法

まとめ

この記事では、ピボットテーブルで加重平均を計算する方法を段階的に解説しました。ポイントは、元データに補助列を追加してからピボットテーブルを作成し、集計フィールド機能を活用することです。手順自体はとてもシンプルなので、ぜひ実際の業務でも活用してみてください。記事についてご不明な点があれば、お気軽にお問い合わせください。

関連記事

• Excelで加重スコアリングモデルを作成する方法（4つの実例付き）
• Excelで変数に重みを割り当てる方法（3つの活用例）
• Excelで加重移動平均を計算する3つの方法
• Excelで百分率を使った加重平均を計算する2つの方法