ピボットテーブルで値を計算する

ピボットテーブルで値を計算するときは、次の方法を単独で、または複数を組み合わせて使用できます。

• 値フィールドの集計関数    値領域に表示されるデータは、ピボットテーブルの基になるソース データを集計した結果です。 たとえば、次のようなソース データがあるとします。

• 次のピボットテーブルとピボットグラフを作成します。 ピボットテーブル内のデータからピボットグラフを作成すると、そのピボットグラフの値には、関連するピボットテーブル レポートでの計算が反映されます。

• このピボットテーブルには、"月" 列フィールドのアイテムとして "3 月" と "4 月" があります。 "地域" 行フィールドのアイテムは "北部"、"南部"、"東部"、"西部" です。 "4 月" の列と "北部" の行が交差する位置の値は、ソース データのレコードのうち "月" の値が "4 月" で、"地域" の値が "北部" であるものの売上高の合計です。

• ピボットグラフでは、"地域" フィールドが項目フィールドとなっており、"北部"、"南部"、"東部"、"西部" が項目として表示されます。 "月" フィールドは系列フィールドであり、アイテム "3 月"、"4 月"、"5 月" が系列として凡例に表示されます。 "値" フィールドの "売上合計" には、各地域の各月の売上合計を表すデータ マーカーが表示されます。 たとえば、あるデータ マーカーは、その縦 (値) 軸上の位置から、"北部" 地域の "4 月" の売上合計であることがわかります。

• 値フィールドを計算するには、以下の集計方法を使用できます。これらは、オンライン分析処理 (OLAP) ソース データを除くすべての種類のソース データに対して使用できます。

  関数	集計方法
  SUM	値の合計。 これは、数値データの既定の関数です。
  COUNT	データ値の個数。 COUNT 集計関数の機能は、COUNTA 関数と同じです。 COUNT は、数値以外のデータに対する既定の関数です。
  AVERAGE	数値の平均値。
  MAX	最大の値。
  MIN	最小の値。
  PRODUCT	数値の積。
  数値の個数	数値であるデータ値の個数。 "数値の個数" の集計方法は、COUNT 関数と同じです。
  StDev	母集団の推定標準偏差 (母集団のサブセットを標本とする)。
  StDevp	母集団の標準偏差 (集計されるデータ全体が母集団であるとする)。
  Var	母集団の推定分散 (母集団のサブセットを標本とする)。
  Varp	母集団の分散 (集計されるデータ全体が母集団であるとする)。

• ユーザー設定の計算    ユーザー設定の計算では、データ エリア内の他のアイテムやセルに基づいて値を表示します。 たとえば、"売上合計" データ フィールドの値を "3 月" の売上に対するパーセンテージで表示したり、"月" フィールドのアイテムの累計として表示することができます。

  値フィールドのユーザー設定の計算で使用できる関数を以下に示します。

  関数	計算結果
  計算しない	フィールドに入力された値が表示されます。
  総計に対する比率	値は、レポートのすべての値またはデータ要素の総計に対する比率として表示されます。
  列集計に対する比率	各列または各系列のすべての値が、その列または系列の合計に対する比率として表示されます。
  行集計に対する比率	行または項目ごとの値が、行または項目の集計の比率として表示されます。
  基準値に対する比率	値を、[基準フィールド] の [基準アイテム] の値に対する比率で表示します。
  親行集計に対する比率	値は次のように計算されます。 (アイテムの値) / (行の親アイテムの値)
  親列集計に対する比率	値は次のように計算されます。 (アイテムの値) / (列の親アイテムの値)
  親集計に対する比率	値は次のように計算されます。 (アイテムの値) / (選択された [基準フィールド] の親アイテムの値)
  基準値との差分	値を、[基準フィールド] の [基準アイテム] の値との差分として表示します。
  基準値に対する比率の差	値を、[基準フィールド] の [基準アイテム] の値に対する比率の差として表示します。
  累計	[基準フィールド] 内の連続するアイテムの値が、類型として表示されます。
  比率の累計	値は [基準フィールド] 内の連続するアイテムについて計算され、累計が比率として表示されます。
  昇順での順位	特定のフィールド内の選択された値の順位を表示します。最小のアイテムが 1 と表示され、以降値が大きくなるにつれて順位が高くなります。
  降順での順位	特定のフィールド内の選択された値の順位を表示します。最大のアイテムが 1 と表示され、以降値が小さくなるにつれて順位が高くなります。
  インデックス	値は次のように計算されます。 ((セルの値) x (総計)) / ((行の総計) x (列の総計))

• 数式    集計関数やユーザー設定の計算で望みどおりの結果が得られない場合は、独自の数式を集計フィールドや集計アイテムの中で作成できます。 たとえば、売上手数料が地域ごとに異なる場合に、売上手数料を計算する数式を集計アイテムに追加します。 このようにすると、レポートの小計と総計に手数料を自動的に加算することができます。

レポートで使用できる計算方法やオプションは、ソース データが OLAP データベースからのものか、それ以外からのものかによって異なります。

• OLAP ソースのデータを基にした計算    ピボットテーブルを OLAP キューブから作成すると、OLAP サーバーで事前に計算された集計値が Excel に結果として表示されます。 この事前計算された値の計算方法をピボットテーブルで変更することはできません。 たとえば、データ フィールドや小計の計算に使用される集計関数を変更することや、集計フィールドや集計アイテムを追加することはできません。

  また、OLAP サーバー側で集計フィールド ("計算メンバー" と呼ばれます) が定義されている場合は、そのフィールドが [ピボットテーブルのフィールド リスト] に表示されます。 この他に表示される集計フィールドおよび集計アイテムとしては、Visual Basic for Applications (VBA) マクロによって作成されてブックに保存されたものがありますが、そのようなフィールドやアイテムを変更することはできません。 別の種類の計算が必要な場合は、OLAP データベース管理者にお問い合わせください。

  OLAP のソース データに対しては、小計や総計を計算するときに非表示アイテムの値を含めるか除外するかを指定できます。

• OLAP 以外のソース データを基にした計算    Excel では、ピボットテーブルのソース データが他の種類の外部データやワークシート データに基づく場合、数値データを含む値フィールドの計算には Sum 集計関数が使用され、テキストを含むデータ フィールドの計算には Count 集計関数が使用されます。 データをさらに詳しく分析してカスタマイズするために、Average、Max、Min などの別の集計関数を選択することもできます。 また、レポートの要素や他のワークシートのデータを使用する数式を独自に作成することもできます。そのためには、集計フィールドを作成するか、集計アイテムをフィールド内に作成します。

数式を作成できるのは、レポートのソース データが OLAP 以外の場合のみです。 OLAP データベースに基づくレポートでは数式を使用できません。 ピボットテーブルで数式を使用するときは、次に示す数式の構文規則と数式の動作に注意してください。

• ピボットテーブルの数式要素    集計フィールドや集計アイテム用に作成する数式では、ワークシートの他の数式と同様に、演算子と式を使用できます。 定数を使用したり、レポートのデータを参照したりすることはできますが、セル参照や定義された名前を使用することはできません。 引数としてセル参照や定義された名前を必要とするワークシート関数を使用することはできません。また、配列関数も使用できません。

• フィールド名とアイテム名    Excel では、レポートのフィールドやアイテムに付けられた名前を使用して、これらの要素を数式内で識別します。 次の例では、範囲 C3:C9 のデータを参照するのに、"乳製品" というフィールド名が使用されています。 乳製品の売上高に基づいて新しい製品の売上高を推定するための集計アイテムを "種類" フィールドに作成する場合、数式は =Dairy * 115% のようになります。

  

  注: ピボットグラフの場合、フィールド名はピボットテーブルのフィールド リストに表示され、アイテム名は各フィールドのドロップダウン リストに表示されます。 これらの名前は、グラフのヒントに表示される系列名およびデータ要素名とは異なるので、注意してください。

• 個々のレコードではなく、合計の結果に対して作用する数式    集計フィールドの数式は、数式で使用されているフィールドの基になるデータの合計に対して作用します。 たとえば、集計フィールドの数式を「=Sales * 1.2」と入力すると、各種類および地域の売上の合計に 1.2 が乗算されます。つまり、個々の売上に 1.2 を乗算した後、その結果を合計するわけではありません。

  集計アイテムの数式は、個々 のレコードに作用します。 たとえば、集計アイテムの数式を「=Dairy *115%」と入力すると、乳製品の個々の売上に 115% を乗算した後で、その結果を値領域全体で合計した金額が計算されます。

• 数式に含まれるスペース、数値、記号    複数のフィールドが含まれる名前では、フィールドを任意の順序で指定できます。 上の例では、セル範囲 C6:D6 の名前として '4 月北部' と '北部 4 月' のどちらでも使用できます。 名前が複数の単語で構成される場合、または数値や記号が含まれる場合は、単一引用符で囲みます。

• 合計    数式で合計を参照することはできません (たとえば、例の "3 月計" や "4 月計"、"総計" などの行は参照できません)。

• フィールド名を使用したアイテムの参照    フィールド名を使用してアイテムを参照できます。 アイテム名は、地域[北部] のように角かっこで囲みます。 レポート内の異なる 2 つのフィールドに同じ名前のアイテムが存在する場合は、この形式を使うことで #NAME? エラーを防ぐことができます。 たとえば、レポートの "種類" フィールドと "分類" フィールドの両方に "肉類" というアイテムがある場合は、アイテムを参照するときにそれぞれ、種類[肉類]、分類[肉類]と指定することで、#NAME? エラーを防ぐことができます。

• アイテムの位置による参照    現在アイテムが並べ替えられて表示されている位置を使用してアイテムを参照できます。 種類[1] は "乳製品"、種類[2] は "魚介類" になります。 この方法で参照したアイテムは、アイテムの位置を変更したり、異なるアイテムの表示と非表示を切り替えるたびに変わります。 非表示のアイテムは、このインデックスの対象にはなりません。

  相対位置を使用してアイテムを参照できます。 アイテムの位置は、数式が含まれる集計アイテムに対して相対的に決定されます。 現在の選択範囲が "南部" の場合、地域[-1] は "北部" を示し、現在の選択範囲が "北部" の場合、地域[+1] は "南部" を示します。 たとえば、集計アイテムで =地域[-1] * 3% などの数式を使用できます。 フィールドの先頭のアイテムより前、または末尾のアイテムより後ろの位置を指定した場合は #REF! エラーが表示されます。

ピボットグラフで数式を使用するには、関連するピボットテーブルで数式を作成します。ピボットテーブルでデータの個々の値を確認してから、結果をピボットグラフでグラフとして見ることができます。

たとえば、次に示すピボットグラフは、各地域の販売員別売上高を表しています。

各販売員の売上高が 10% 増加したと仮定した場合の売上を計算するには、関連するピボットテーブルに集計フィールドを作成し、数式を「=売上 * 110%」と入力します。

結果は、次の図に示すように、すぐにピボットグラフに表示されます。

北部地域の売上高から輸送費として 8% を差し引いた金額を別のデータ マーカーとして表示するには、"地域" フィールドに集計アイテムを作成し、数式を「=北部 – (北部 * 8%)」のように入力します。

結果のグラフは次のようになります。

集計アイテムを "販売員" フィールドに作成した場合は、凡例で表される系列として表示され、グラフでは各項目のデータ要素の 1 つとして表示されます。