【Excel】ピボットテーブルで計算フィールドを追加して独自の指標を作る方法

ピボットテーブルを使えば、大量のデータを集計・分析できます。しかし、標準の集計項目だけでは、ビジネス上の独自の指標を算出できない場合があります。そんな時、ピボットテーブルの「計算フィールド」機能が役立ちます。この記事では、ピボットテーブルで計算フィールドを追加し、売上から利益率を計算するなど、独自の指標を作成する具体的な手順を解説します。

ピボットテーブルでのデータ分析において、標準の合計や平均といった集計だけでは物足りないと感じる場面があるでしょう。例えば、売上データから「粗利率」や「一人当たり売上」といった、独自の視点での指標を算出したい場合です。このようなニーズに応えるのが「計算フィールド」機能です。この機能を使えば、既存のフィールドを組み合わせて新しい計算項目を自由に作成できます。この記事を読めば、ピボットテーブルで独自の指標を効果的に作成し、より深いデータ分析が可能になります。

ピボットテーブルで計算フィールドを定義する理由

ピボットテーブルは、膨大なデータを素早く集計・分析するための強力なツールです。しかし、標準で用意されている「合計」「平均」「個数」といった集計方法だけでは、ビジネス上の特定のニーズに対応できないことがあります。例えば、売上データから「利益率」を計算したい場合、単純な合計だけでは目的を達成できません。利益率を算出するには、「売上」と「原価」のフィールドを使って、(売上 – 原価) / 売上 という計算を行う必要があります。このように、既存のフィールドを組み合わせて新たな意味を持つ指標を作り出すために、計算フィールド機能が活用されます。

計算フィールドを定義することで、以下のようなメリットが得られます。

• 独自の指標の作成: ビジネス特有のKPI(重要業績評価指標)や分析に必要な指標を自由に定義できます。
• 分析の深化: 標準の集計だけでは見えにくい、より詳細な傾向や関係性を明らかにできます。
• レポート作成の効率化: 必要な指標をピボットテーブル内で完結できるため、別途計算式を作成する手間が省けます。
• データの可視化: 新たな指標をピボットテーブルの行や列、値として配置し、グラフ化することで、より分かりやすいレポートを作成できます。

計算フィールドは、Excel 2019やMicrosoft 365のピボットテーブルで利用可能です。それ以前のバージョンでは機能が制限されている場合があるため注意が必要です。

ピボットテーブルで計算フィールドを作成する手順

ここでは、具体的な売上データを使って、「利益率」という計算フィールドを作成する手順を解説します。ここでは、以下のようなデータがあることを想定します。

• 商品名
• 地域
• 売上
• 原価

これらのデータから、各商品の「利益率」を計算フィールドとして追加します。

1. ピボットテーブルの作成
   まず、分析したいデータ範囲を選択し、「挿入」タブの「ピボットテーブル」をクリックしてピボットテーブルを作成します。既存のシートまたは新規ワークシートに配置してください。
2. ピボットテーブルフィールドリストの表示
   作成されたピボットテーブルのいずれかのセルを選択すると、画面右側に「ピボットテーブルのフィールド」作業ウィンドウが表示されます。このウィンドウで、フィールドの追加や設定を行います。
3. 計算フィールドの追加開始
   「ピボットテーブルのフィールド」作業ウィンドウの下部にある「フィールド、項目、およびセット」の右側にある「分析」タブ(または「オプション」タブ、Excelのバージョンにより名称が異なります)をクリックします。表示されたメニューから「フィールド、項目、およびセット」を選択し、さらに「計算フィールド」をクリックします。
4. 計算フィールドの設定ダイアログを開く
   「計算フィールドの挿入」ダイアログボックスが表示されます。このダイアログボックスで、新しいフィールドの名前と計算式を入力します。
5. フィールド名の入力
   「名前」ボックスに、新しく作成するフィールドの名前を入力します。「利益率」と入力しましょう。
6. 数式の入力
   「数式」ボックスに、計算式を入力します。ここでは、売上と原価を使って利益率を計算します。数式は以下のようになります。

   =(売上-原価)/売上

   数式を入力する際は、「フィールド名」リストから該当するフィールド名(「売上」や「原価」)をダブルクリックして挿入すると、手入力ミスを防げます。フィールド名は、Excelのバージョンによって角括弧 `[]` で囲まれる場合があります。

7. 計算フィールドの挿入
   数式を入力したら、「追加」ボタンをクリックします。これにより、計算フィールドがピボットテーブルに追加されます。
8. 計算フィールドの配置
   「ピボットテーブルのフィールド」作業ウィンドウに戻ると、「利益率」という新しいフィールドが「値」エリアに追加されているはずです。必要に応じて、このフィールドを「行」や「列」エリアにドラッグして配置することも可能です。
9. 表示形式の設定(重要)
   「利益率」フィールドは、デフォルトでは合計値や平均値として表示されます。通常、利益率はパーセンテージで表示したいので、表示形式を整える必要があります。ピボットテーブル内の「利益率」の値(例えば、「合計 / 利益率」と表示されているセル)を右クリックし、「値フィールドの設定」を選択します。表示されたダイアログボックスで、「値の表示形式」ボタンをクリックし、「パーセンテージ」を選択して「OK」をクリックします。

これで、ピボットテーブル上に独自の指標である「利益率」が表示されるようになります。この手順は、他の様々な計算フィールドを作成する際にも応用できます。

計算フィールド作成時の注意点とよくある失敗

計算フィールドは非常に便利ですが、いくつか注意すべき点や、よくある失敗例があります。これらを理解しておくことで、よりスムーズに計算フィールドを作成し、意図した結果を得ることができます。

数式エラーが発生する場合

原因

数式内のフィールド名が間違っている、または存在しないフィールド名を指定している場合に発生します。また、Excelのバージョンによってフィールド名の表記(角括弧の有無など)が異なることがあります。

対処法

「ピボットテーブルのフィールド」作業ウィンドウの「フィールド名」リストから、正確なフィールド名をダブルクリックして数式に挿入し直してください。Excel 2019以降では、フィールド名は通常 `[フィールド名]` のように角括弧で囲まれます。

ゼロ除算エラーが発生する場合

原因

計算フィールドの数式で、分母がゼロになる可能性がある場合(例:「売上」がゼロの場合に「(売上-原価)/売上」を計算しようとする)に発生します。Excelでは `#DIV/0!` エラーとなります。

対処法

IF関数を使ってゼロ除算を回避します。例えば、利益率の計算で「売上」がゼロの場合はエラーとせず、0%と表示したい場合は、数式を以下のように変更します。

=IF(売上=0, 0, (売上-原価)/売上)

IF関数の構文は「IF(条件, 条件が真の場合の値, 条件が偽の場合の値)」です。

意図しない集計結果になる場合

原因

計算フィールドの「集計方法」が、意図したものではない場合に起こります。例えば、利益率の平均を計算したいのに、デフォルトで合計値になっていたりします。

対処法

「ピボットテーブルのフィールド」作業ウィンドウで、「値」エリアにある計算フィールド(例:「合計 / 利益率」)を右クリックし、「値フィールドの設定」を選択します。「集計」タブで、目的に合った集計方法(合計、平均、最大、最小、個数など)を選択してください。

表示形式が正しくない場合

原因

計算フィールドの値が、数値やパーセンテージではなく、単なる総計として表示されてしまうことがあります。これは、計算フィールドの集計方法が「合計」になっている場合に起こりがちです。

対処法

上記「意図しない集計結果になる場合」と同様に、「値フィールドの設定」を開き、「値の表示形式」ボタンをクリックします。ここで「パーセンテージ」や「通貨」、「数値」など、適切な表示形式を選択してください。

計算フィールドが更新されない

原因

元のデータソースが更新されたにも関わらず、ピボットテーブルが自動更新されていない、または手動更新されていない場合に、計算フィールドの値も古いままになっていることがあります。

対処法

ピボットテーブル内の任意の場所を右クリックし、「更新」を選択してください。または、「ピボットテーブル分析」タブ(または「オプション」タブ)の「更新」グループにある「すべて更新」をクリックします。

複雑な数式によるエラー

原因

複数のフィールドや関数を組み合わせた複雑な数式を入力した場合、構文エラーや論理エラーが発生しやすくなります。特に、ネストしたIF関数などは注意が必要です。

対処法

数式は段階的に作成し、その都度ピボットテーブルで結果を確認することをお勧めします。複雑な数式は、Excelの標準機能で一度作成し、その結果を一時的な列としてデータソースに追加してからピボットテーブルで集計する方が、管理しやすい場合もあります。

計算フィールドと計算アイテムの違い

ピボットテーブルには、「計算フィールド」の他に「計算アイテム」という機能も存在します。これらは似ていますが、用途が異なります。

両者の主な違いは、計算対象となる要素です。

項目	計算フィールド	計算アイテム
計算対象	既存のフィールド(列)	既存のアイテム(行や列の項目)
目的	新しい指標(例:利益率、一人当たり売上)を作成する	既存の項目間の比較や差分(例:前月比、地域別売上差)を算出する
数式例	=(売上-原価)/売上	=(地域B売上-地域A売上)/地域A売上
追加場所	「値」エリアに集計結果として表示されることが多い	特定のフィールドのアイテムとして表示される

例えば、「地域」フィールドで「東京」「大阪」といったアイテムがあり、その売上を比較したい場合は「計算アイテム」を使用します。一方、「売上」と「原価」という2つのフィールドから「利益率」という新しい指標を作りたい場合は、「計算フィールド」を使用します。

どちらの機能を使うべきかは、分析したい内容によって判断してください。一般的には、新しい指標の定義には計算フィールドが、既存項目間の比較や比率計算には計算アイテムが適しています。

まとめ

本記事では、Excelのピボットテーブルで「計算フィールド」を追加し、独自の指標を作成する方法を解説しました。計算フィールドを使えば、売上から利益率を算出したり、目標達成率を計算したりと、ビジネス分析に不可欠な独自の指標を柔軟に定義できます。数式の入力方法や、ゼロ除算エラーへの対処法、表示形式の設定方法などを理解することで、より精度の高い分析が可能になります。

今回学んだ計算フィールドの作成手順を基に、ぜひご自身の業務で必要な独自の指標をピボットテーブルに追加してみてください。さらに高度な分析を行いたい場合は、「計算アイテム」機能との使い分けも検討すると良いでしょう。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】