【Googleスプレッドシート】ピボットテーブルの計算フィールド追加!計算列の作成

ピボットテーブルで売上データを集計しているとき、既存のフィールドだけでは不足する計算が必要になることがあります。例えば「単価×数量」や「売上-原価」のような独自の計算をピボットテーブル内で直接行いたい場合です。この記事では、Googleスプレッドシートのピボットテーブルで計算フィールドを追加する方法と、元データに計算列を作成するテクニックを解説します。どちらの方法を使えばよいか迷っている方も、実務に応じた選択ができるようになります。

計算フィールドを使えば、元データを変更せずにピボットテーブル内で新しい計算結果を表示できます。一方、元データに計算列を追加する方法は、ピボットテーブル以外の分析でも活用できるメリットがあります。両者の違いを理解し、目的に合った方法を選びましょう。

ピボットテーブルの計算フィールドの概要

計算フィールドは、ピボットテーブルの値フィールドとして設定するカスタム計算です。例えば「売上高-原価」のような数式を入力すると、各セルで行ごとに計算が実行されます。元のデータシートに列を追加する必要がなく、ピボットテーブル上だけで計算結果を得られるのが特徴です。ただし、計算フィールドで使える関数はSUM、AVERAGE、COUNT、MAX、MINなどの集計関数が基本で、ピボットテーブルの構造上、個々の行を直接参照するような計算はできません。また、計算フィールドは値フィールドとしてのみ追加でき、行ラベルや列ラベルとしては利用できません。

一方、元データに計算列を追加する方法は、元の表に新しい列を作成し、数式で計算した結果を格納します。その列をピボットテーブルの行ラベルや列ラベル、値フィールドとして自由に使えるため、より柔軟な分析が可能です。ただし、元データを更新する必要があるため、元のデータ構造を変更しても問題ない場合に適しています。

計算フィールドを追加する手順

ここでは、売上データのサンプルを使って計算フィールドを追加する手順を説明します。元データには「商品名」「単価」「数量」「原価」の列があると仮定します。

1. ピボットテーブルを作成する
   元データを選択し、メニューから「データ」→「ピボットテーブル」をクリックします。新しいシートにピボットテーブルが作成されます。
2. 値フィールドに計算フィールドを追加する
   ピボットテーブルエディタの「値」セクションにある「追加」ボタンをクリックし、一覧の一番下にある「計算フィールド」を選択します。
3. 数式を入力する
   「計算フィールドの編集」ダイアログで、「名前」に任意の名前(例「粗利」)を入力し、「数式」欄に「=売上高 – 原価」と入力します。フィールド名はダブルクリックで選択できます。数式内では集計関数(SUMやAVERAGEなど)は不要で、フィールド名をそのまま使います。
4. 設定を確定する
   「保存」ボタンをクリックすると、ピボットテーブルに計算フィールドが値として表示されます。値の集計方法は通常の値フィールドと同じくSUMやAVERAGEなどに変更できます。

計算フィールドの数式には、任意のフィールド名と四則演算子、SUMやAVERAGEなどの集計関数を組み合わせられます。例えば「=SUM(売上高) – SUM(原価)」のように記述することも可能ですが、通常は「=売上高 – 原価」で自動的にSUMが適用されます。

計算フィールドの編集と削除

1. 編集する
   ピボットテーブルエディタの「値」セクションで、該当の計算フィールドの名前をクリックし、「フィールドの変更」を選びます。ダイアログで数式や名前を編集して「保存」します。
2. 削除する
   同じく「値」セクションで計算フィールドの名前の右側にある「×」ボタンをクリックします。確認ダイアログで「削除」を選べば完了です。

元データに計算列を作成してピボットテーブルで使う手順

元データに直接計算列を追加する方法もよく使われます。この方法なら、計算結果を行ラベルや列ラベルとして使えるため、より詳細な分析ができます。

1. 元データに新しい列を挿入する
   データシートで、任意の列の右側に新しい列を追加します。例えば「粗利」という列見出しを入力します。
2. 数式を入力する
   2行目以降の各セルに「=単価のセル * 数量のセル – 原価のセル」のような数式を入力します。ARRAYFORMULAを使えば一括計算も可能です。例:「=ARRAYFORMULA(E2:E * F2:F – G2:G)」。
3. ピボットテーブルを更新する
   ピボットテーブルが元データを参照している場合、新しく追加した列が自動的にフィールド一覧に追加されます。もし反映されない場合は、ピボットテーブルエディタのデータ範囲を確認し、範囲を広げて更新します。
4. 計算列をピボットテーブルに追加する
   行ラベル、列ラベル、値など、任意のセクションにドラッグして配置します。値として使う場合は集計方法を指定できます。

この方法の利点は、計算結果をピボットテーブル以外の表やグラフでも再利用できることです。また、計算フィールドではできないIF関数やTEXT関数などの条件分岐や文字列操作も自由に行えます。

注意点と制限事項

計算フィールドでSUM関数を使うときの誤解

計算フィールドの数式では、通常「=売上高-原価」のようにフィールド名を指定すると、ピボットテーブルが自動的にSUMで集計します。しかし、明示的に「=SUM(売上高)-SUM(原価)」と書いても同じ結果になるので安心してください。ただし、AVERAGEを使いたい場合は「=AVERAGE(売上高)-AVERAGE(原価)」のように書く必要があります。

計算フィールドでは行ラベルや列ラベルに追加できない

計算フィールドは値フィールドとしてのみ使用可能です。計算結果を行方向や列方向の分類に使いたい場合は、元データに計算列を追加する方法を選んでください。例えば、利益率の範囲でグループ分けしたい場合などに便利です。

元データの計算列を更新するときの注意

元データに計算列を追加した場合、元のデータに行を追加するたびに数式を自動的に拡張する必要があります。ARRAYFORMULAを使うと便利ですが、データ範囲が変わるたびに数式の範囲を修正しなければならない場合もあります。テーブル形式(Ctrl+T)に変換しておくと、数式が自動的に伝播するのでおすすめです。

計算フィールドと元データの計算列の比較

まとめ

ピボットテーブルに計算フィールドを追加すると、元データを変更せずに簡単な計算結果を値として表示できます。一方、元データに計算列を作成すれば、より複雑な計算や行ラベルとしての利用が可能になります。どちらの方法を選ぶかは、計算の複雑さやデータの管理方法によって判断してください。まずは計算フィールドを試してみて、要件に合わなければ計算列に切り替えるとよいでしょう。Googleスプレッドシートのピボットテーブルを活用して、データ分析の幅を広げてください。