ピボットテーブルで値を計算する
ピボットテーブルで値を計算する
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2019 Excel 2016 その他…表示数を減らす
ピボットテーブルでは、値フィールドで集計関数を使用して、基になるソース データの値を組み合わせることができます。 集計関数やユーザー設定の計算で望みどおりの結果が得られない場合は、独自の数式を集計フィールドや集計アイテムの中で作成できます。 たとえば、売上手数料が地域ごとに異なる場合に、売上手数料を計算する数式を集計アイテムに追加します。 このようにすると、ピボットテーブルの小計と総計に手数料を自動的に加算することができます。
計算するもう 1 つの方法は、 target=”_blank” title=”Data Analysis Expressions (DAX)” rel=”noopener”>Data Analysis Expressions (DAX) 数式を使用して作成する Power Pivot のメジャーを使用することです。 詳細については、「 target=”_blank” title=”Power Pivot のメジャーを作成する” rel=”noopener”>Power Pivot のメジャーを作成する」を参照してください。
計算について Begin >>>
ピボットテーブルでは、さまざまな方法でデータを計算できます。 ここでは、使用できる計算方法、ソース データの種類による計算方法の違い、数式をピボットテーブルやピボットグラフで使用する方法について説明します。
__
使用できる計算方法
ピボットテーブルで値を計算するときは、次の方法を単独で、または複数を組み合わせて使用できます。
- 値フィールドの集計関数 値領域に表示されるデータは、ピボットテーブルの基になるソース データを集計した結果です。 たとえば、次のようなソース データがあるとします。
- 次のピボットテーブルとピボットグラフを作成します。 ピボットテーブル内のデータからピボットグラフを作成すると、そのピボットグラフの値には、関連するピボットテーブル レポートでの計算が反映されます。
このピボットテーブルには、"月 " 列フィールドのアイテムとして "3 月 " と "4 月 " があります。 "地域 " 行フィールドのアイテムは "北部 "、"南部 "、"東部 "、"西部 " です。 "4 月 " の列と "北部 " の行が交差する位置の値は、ソース データのレコードのうち "月 " の値が "4 月 " で、"地域 " の値が "北部 " であるものの売上高の合計です。
ピボットグラフでは、"地域 " フィールドが項目フィールドとなっており、"北部 "、"南部 "、"東部 "、"西部 " が項目として表示されます。 "月 " フィールドは系列フィールドであり、アイテム "3 月 "、"4 月 "、"5 月 " が系列として凡例に表示されます。 "値 " フィールドの "売上合計 " には、各地域の各月の売上合計を表すデータ マーカーが表示されます。 たとえば、あるデータ マーカーは、その縦 (値) 軸上の位置から、"北部 " 地域の "4 月 " の売上合計であることがわかります。
値フィールドを計算するには、以下の集計方法を使用できます。これらは、オンライン分析処理 (OLAP) ソース データを除くすべての種類のソース データに対して使用できます。
| 関数 | 集計方法 |
|---|---|
| SUM | 値の合計。 これは、数値データの既定の関数です。 |
| COUNT | データ値の個数。 COUNT 集計関数の機能は、 target=”_blank” title=”COUNTA 関数” rel=”noopener”>COUNTA 関数と同じです。 COUNT は、数値以外のデータに対する既定の関数です。 |
| AVERAGE | 数値の平均値。 |
| MAX | 最大の値。 |
| MIN | 最小の値。 |
| PRODUCT | 数値の積。 |
| 数値の個数 | 数値であるデータ値の個数。 "数値の個数" の集計方法は、 target=”_blank” title=”COUNT 関数” rel=”noopener”>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 つとして表示されます。
計算について End <<<
Windows、Mac Begin >>>
__
ピボットテーブル内に数式を作成する
重要: ピボットテーブルがオンライン分析処理 (OLAP) データ ソースに接続されている場合は、数式を作成できません。
最初に、集計フィールドと、フィールド内の集計アイテムのどちらを使用するかを決定します。 数式で別のフィールドのデータを使用する場合は、集計フィールドを使用します。 フィールド内の特定のアイテムのデータを数式で使用する場合は、集計アイテムを使用します。
集計アイテムの場合は、セルごとに異なる数式を入力できます。 たとえば、OrangeCounty という名前の集計アイテムの数式を「=Oranges * .25 」と入力して、すべての月にこの集計アイテムを追加した後で、6、7、8 月の数式を =Oranges *.5 に変更できます。
集計アイテムや数式が複数ある場合に、計算の順序を調整することができます。
集計フィールドを追加する
- ピボットテーブルをクリックします。
[ピボットテーブル ツール] が表示され、[分析] タブと [デザイン] タブが追加されます。
- [分析] タブの [計算] グループで、[フィールド/アイテム/セット] をクリックし、[集計フィールド] をクリックします。
[名前] ボックスに、フィールドの名前を入力します。
[数式] ボックスに、フィールドの数式を入力します。
別のフィールドのデータを数式で使用するには、[フィールド] ボックスでそのフィールドをクリックし、[フィールドの挿入] をクリックします。 たとえば、"売上" フィールドの値のそれぞれについて 15% の手数料を計算するには、「= 売上 * 15% 」と入力します。
- [追加] をクリックします。
フィールドに集計アイテムを追加する
- ピボットテーブルをクリックします。
[ピボットテーブル ツール] が表示され、[分析] タブと [デザイン] タブが追加されます。
- フィールド内のアイテムがグループ化されている場合は、[分析] タブの [グループ] グループで [グループ解除] をクリックします。
集計アイテムを追加するフィールドをクリックします。
[分析] タブの [計算] グループで、[フィールド/アイテム/セット] をクリックし、[集計アイテム] をクリックします。
[名前] ボックスに、集計アイテムの名前を入力します。
[数式] ボックスに、アイテムの数式を入力します。
アイテムのデータを数式で使用するには、[アイテム] ボックスの一覧でそのアイテムをクリックし、[アイテムの挿入] をクリックします (このアイテムは集計アイテムと同じフィールドからのものでなければなりません)。
- [追加] をクリックします。
集計アイテムのセルごとに異なる数式を入力する
- 数式を変更するセルをクリックします。
複数のセルの数式を変更する場合は、Ctrl キーを押しながら、変更する他のセルをクリックします。
- 数式バーに、数式の変更を入力します。
集計アイテムまたは数式が複数ある場合の計算順序を調整する
- ピボットテーブルをクリックします。
[ピボットテーブル ツール] が表示され、[分析] タブと [デザイン] タブが追加されます。
- [分析] タブの [計算] グループで、[フィールド/アイテム/セット] をクリックし、[解決の順序] をクリックします。
数式をクリックし、[上へ移動] または [下へ移動] をクリックします。
数式を計算する順序が正しく設定されるまで、この操作を続けます。
__
1 つのピボットテーブルで使用されているすべての数式を表示する
現在のピボットテーブルで使用されているすべての数式の一覧を表示できます。
- ピボットテーブルをクリックします。
[ピボットテーブル ツール] が表示され、[分析] タブと [デザイン] タブが追加されます。
- [分析] タブの [計算] グループで、[フィールド/アイテム/セット] をクリックし、[数式の一覧表示] をクリックします。
__
ピボットテーブルの数式を編集する
数式を編集する前に、その数式が集計フィールドと集計アイテムのどちらに含まれているかを確認します。 集計アイテムに含まれている数式の場合は、その集計アイテムの唯一の数式であるかどうかも確認します。
集計アイテムの場合は、セルごとに集計アイテムの数式を個別に編集できます。 たとえば、OrangeCalc という名前の集計アイテムの数式を「=Oranges * .25 」と入力して、すべての月にこの集計アイテムを追加した後で、6、7、8 月の数式を =Oranges *.5 に変更できます。
数式が集計フィールドと集計アイテムのどちらに含まれているかを確認する
- ピボットテーブルをクリックします。
[ピボットテーブル ツール] が表示され、[分析] タブと [デザイン] タブが追加されます。
- [分析] タブの [計算] グループで、[フィールド/アイテム/セット] をクリックし、[数式の一覧表示] をクリックします。
- 変更しようとしている数式が、数式の一覧の [集計フィールド] と [集計アイテム] のどちらに表示されているかを確認します。
1 つの集計アイテムに複数の数式がある場合は、そのアイテムの作成時に入力された既定の数式の列 B に、集計アイテム名が表示されます。集計アイテムの、既定以外の数式の場合は、列 B には集計アイテム名と、交差するアイテムの名前の両方が表示されます。
たとえば、"マイアイテム " という名前の集計アイテムに既定の数式があり、このアイテムに "マイアイテム 1 月売上 " という名前の別の数式があるとします。 ピボットテーブルでは、この数式は "マイアイテム" 行と "1 月" 列が交差する "売上" セルにあります。
- 次のいずれかの方法を使用して、編集を続行します。
集計フィールドの数式を編集する
- ピボットテーブルをクリックします。
[ピボットテーブル ツール] が表示され、[分析] タブと [デザイン] タブが追加されます。
- [分析] タブの [計算] グループで、[フィールド/アイテム/セット] をクリックし、[集計フィールド] をクリックします。
[名前] ボックスで、数式を変更する集計フィールドを選択します。
[数式] ボックスの数式を編集します。
[修正] をクリックします。
集計アイテムの単一の数式を編集する
集計アイテムを含むフィールドをクリックします。
[分析] タブの [計算] グループで、[フィールド/アイテム/セット] をクリックし、[集計アイテム] をクリックします。
[名前] ボックスで、集計アイテムを選択します。
[数式] ボックスの数式を編集します。
[修正] をクリックします。
集計アイテムの数式をセルごとに編集する
- 数式を変更するセルをクリックします。
複数のセルの数式を変更する場合は、Ctrl キーを押しながら、変更する他のセルをクリックします。
- 数式バーに、数式の変更を入力します。
ヒント: 集計アイテムや数式が複数ある場合に、計算の順序を調整することができます。 詳細については、「集計アイテムまたは数式が複数ある場合の計算順序を調整する」を参照してください。
__
ピボットテーブルの数式を削除する
注: ピボットテーブルから数式を削除すると、元に戻すことはできません。 数式を後で再び使用する可能性がある場合は、フィールドまたはアイテムをピボットテーブルの外にドラッグして非表示にすることができます。
- 数式が集計フィールドと集計アイテムのどちらに含まれているかを確認します。
集計フィールドは、[ピボットテーブルのフィールド リスト] に表示されます。 集計アイテムは、他のフィールド内のアイテムとして表示されます。
次のいずれかの操作を行います。
集計フィールドを削除するには、ピボットテーブル内の任意の場所をクリックします。
集計アイテムを削除するには、ピボットテーブルで、削除するアイテムが含まれているフィールドをクリックします。
[ピボットテーブル ツール] が表示され、[分析] タブと [デザイン] タブが追加されます。
- [分析] タブの [計算] グループで、[フィールド/アイテム/セット] をクリックし、[集計フィールド] または [集計アイテム] をクリックします。
[名前] ボックスで、削除するフィールドまたはアイテムを選択します。
[削除] をクリックします。
Windows、Mac End <<<
Web Begin >>>
Web 用 Excel でピボットテーブルの値を集計するには、Sum、Count、Average などの集計関数を使います。 既定では、値フィールドの数値に対して Sum 関数が使われます。 OLAP データ ソースに基づいてピボットテーブルを表示および編集することはできますが、Excel for the web でピボットテーブルを作成することはできません。
別の集計関数を選択する方法を次に示します:
ピボットテーブルの任意の場所をクリックし、[ピボットテーブル] > [フィールド リスト] を選択します。 ピボットテーブルを右クリックし、[フィールド リストの表示] を選択することもできます。
[ピボットテーブル フィールド] リストの [値] で、値フィールドの横の矢印をクリックします。
[値フィールドの設定] をクリックします。
必要な集計関数を選び、[OK] をクリックします。
注: 集計方法は、オンライン分析処理 (OLAP) のソース データに基づくピボットテーブルでは使えません。
| この集計方法を使う | 計算をする |
|---|---|
| 合計 | 値の合計。 数値を含む値フィールドに対して既定で使用されます。 |
| 個数 | 空でない値の数。 "個数" の集計方法は、 target=”_blank” title=”COUNTA 関数” rel=”noopener”>COUNTA 関数と同じです。 "個数" は、数値以外の値または空白を含む値フィールドに対して既定で使用されます。 |
| AVERAGE | 数値の平均値。 |
| MAX | 最大の値。 |
| MIN | 最小の値。 |
| PRODUCT | 数値の積。 |
| Count Numbers | 数値が含まれている値の個数 (空でない値を含む "Count" とは異なります)。 |
| StDev | 母集団の推定標準偏差 (母集団のサブセットを標本とする)。 |
| StDevp | 母集団の標準偏差 (集計されるデータ全体が母集団であるとする)。 |
| Var | 母集団の推定分散 (母集団のサブセットを標本とする)。 |
| Varp | 母集団の分散 (集計されるデータ全体が母集団であるとする)。 |
Web End <<<
iPad Begin >>>
iPad のピボットテーブルは、iPad バージョン 2.82.205.0 以降で Excel を実行しているお客様が利用できます。 この機能にアクセスするには、アプリがApp Storeを介して最新バージョンに更新されていることを確認してください。
Excel for iPadのピボットテーブルの値を集計するには、Sum 、Count 、Average などのサマリー関数を使用します。 既定では、値フィールドの数値に対して Sum 関数が使われます。 OLAP データ ソースに基づいてピボットテーブルを表示および編集することはできますが、Excel for iPadでピボットテーブルを作成することはできません。
別の集計関数を選択する方法を次に示します:
1. ピボットテーブル内の任意の場所をタップしてピボット テーブル タブに表示し、左にスワイプし、[ フィールド リスト ] を選択してフィールド リストを表示します。
2. [ピボットテーブル フィールド] リストの [値] で、値 フィールドの横にある省略記号をタップします。
3.[フィールド設定] をタップします。
4. 目的のサマリー関数を確認します。
注: 集計方法は、オンライン分析処理 (OLAP) のソース データに基づくピボットテーブルでは使えません。
| この集計方法を使う | 計算をする |
|---|---|
| 合計 | 値の合計。 数値を含む値フィールドに対して既定で使用されます。 |
| 個数 | 空でない値の数。 "個数" の集計方法は、 target=”_blank” title=”COUNTA 関数” rel=”noopener”>COUNTA 関数と同じです。 "個数" は、数値以外の値または空白を含む値フィールドに対して既定で使用されます。 |
| AVERAGE | 数値の平均値。 |
| MAX | 最大の値。 |
| MIN | 最小の値。 |
| PRODUCT | 数値の積。 |
| Count Numbers | 数値が含まれている値の個数 (空でない値を含む "Count" とは異なります)。 |
| StDev | 母集団の推定標準偏差 (母集団のサブセットを標本とする)。 |
| StDevp | 母集団の標準偏差 (集計されるデータ全体が母集団であるとする)。 |
| Var | 母集団の推定分散 (母集団のサブセットを標本とする)。 |
| Varp | 母集団の分散 (集計されるデータ全体が母集団であるとする)。 |
iPad End <<<
補足説明
Excel Tech Communityで、いつでも専門家に質問できます。また、コミュニティでは、サポートを受けられます。