ピボットテーブルには、事前に定義した構造をレポートに提供する複数のレイアウトがありますが、これらのレイアウトをカスタマイズすることはできません。 ピボットテーブル レポートのレイアウトをより柔軟に設計する必要がある場合は、セルをワークシートの数式に変換し、ワークシートで使用できるすべての機能を最大限に活用してこれらのセルのレイアウトを変更できます。 セルをキューブ関数を使用する数式に変換するか、GETPIVOTDATA 関数を使用できます。 セルを数式に変換すると、これらのカスタマイズされたピボットテーブルの作成、更新、保守のプロセスが大幅に簡略化されます。
セルを数式に変換すると、これらの数式はピボットテーブルと同じデータにアクセスし、更新して最新の結果を確認できます。 ただし、レポート フィルターの例外である可能性があることを除き、フィルター処理、並べ替え、レベルの展開と折りたたみなど、ピボットテーブルの対話型機能にはアクセスできなくなります。
注
オンライン分析処理 (OLAP) ピボットテーブルを変換するとき、引き続きデータを更新して最新のメジャー値を取得できますが、レポートに表示される実際のメンバーを更新することはできません。
ピボットテーブルをワークシートの数式に変換する一般的なシナリオについて説明します
ピボットテーブルのセルをワークシートの数式に変換して、変換されたセルのレイアウトをカスタマイズする操作の一般的な例を次に示します。
セルの配置変更と削除
スタッフ用に毎月作成する必要がある定期レポートがあるとします。 レポート情報のサブセットのみが必要で、カスタマイズされた方法でデータをレイアウトすることをお勧めします。 好きなデザイン レイアウトのセルを移動して配置し、月次スタッフ レポートに不要なセルを削除してから、好みに合わせてセルとワークシートを書式設定するだけです。
行と列を挿入する
たとえば、地域と製品グループ別に分類された過去 2 年間の販売情報を表示し、追加の行に拡張解説を挿入するとします。 行を挿入してテキストを入力するだけです。 さらに、元のピボットテーブルに存在しない地域および製品グループ別の売上を表示する列を追加します。 列を挿入し、数式を追加して必要な結果を取得し、列を下に入力して各行の結果を取得するだけです。
複数のデータ ソースを使用する
本番データベースとテスト データベースの間で結果を比較して、テスト データベースが期待どおりの結果を生成していることを確認するとします。 これら 2 つの結果を比較するには、セルの数式を簡単にコピーし、テスト データベースをポイントするように接続引数を変更できます。
セル参照を使用してユーザー入力を変更する
たとえば、レポート全体をユーザー入力に基づいて変更するとします。 キューブ数式の引数をワークシートのセル参照に変更し、それらのセルに異なる値を入力して異なる結果を導き出すことができます。
不均一な行または列のレイアウトを作成する (非対称レポートとも呼ばれる)
[実績売上高] という 2008 年の列と [予測売上高] という名前の 2009 年の列を含むレポートを作成する必要があるが、他の列は必要ないとします。 対称レポートを必要とするピボットテーブルとは異なり、これらの列だけを含むレポートを作成できます。
独自のキューブ式と MDX 式を作成する
たとえば、7 か月の 3 人の特定の販売員による特定の製品の売上を示すレポートを作成するとします。 MDX 式と OLAP クエリに精通している場合は、キューブ式を自分で入力できます。 これらの数式は非常に複雑になることがありますが、数式オートコンプリートを使用すると、作成を簡略化して精度を高めることができます。 詳細については、「数式オートコンプリートを使用する」を参照してください。
セルをキューブ関数を使う数式に変換する
注
この手順では、オンライン分析処理 (OLAP) ピボットテーブルのみを変換できます。
ピボットテーブルを保存して後で使用するには、ピボットテーブルを変換する前に、[ファイル]>名前を付けて保存] をクリックしてブックのコピーを作成することをお勧めします。 詳細については、「 ファイルの保存」を参照してください。
次の操作を実行して、変換後のセルの再配置を最小限に抑えられるようにピボットテーブルを準備します。
- 目的のレイアウトに最も近いレイアウトに変更します。
- レポートのフィルター処理、並べ替え、再設計などの操作を行って、必要な結果を取得します。
ピボットテーブルをクリックします。
[ オプション] タブの [ ツール ] グループで [OLAP ツール] をクリックし、[ 数式に変換] をクリックします。
レポート フィルターがない場合は、変換操作が完了します。 1 つ以上のレポート フィルターがある場合は、[ 数式に変換 ] ダイアログ ボックスが表示されます。ピボットテーブルの変換方法を決定します。
ピボットテーブル全体を変換する[レポート フィルターの変換] チェック ボックスをオンにします。
これにより、すべてのセルがワークシートの数式に変換され、ピボットテーブル全体が削除されます。
レポート フィルターはそのまま使用して、ピボットテーブルの行ラベル、列ラベル、値の領域のみを変換します[レポート フィルターの変換] チェック ボックスがオフになっていることを確認します。 これが既定値です。
これにより、すべての行ラベル、列ラベル、値領域のセルがワークシートの数式に変換され、元のピボットテーブルが保持されますが、レポート フィルターのみが含まれるため、引き続きレポート フィルターを使用してフィルター処理できます。注
ピボットテーブル形式が 2000 から 2003 以前のバージョンの場合は、ピボットテーブル全体のみを変換できます。
[変換] をクリックします。
変換操作では、最初にピボットテーブルが更新され、最新のデータが使用されるようにします。
変換操作が行われている間、メッセージがステータス バーに表示されます。 操作に時間がかかり、別の時間に変換する場合は、Esc キーを押して操作を取り消します。注
- 非表示のレベルにフィルターが適用されたセルは変換できません。
- [値フィールドの設定] ダイアログ ボックスの [値の表示の種類] タブで作成されたユーザー設定の計算が含まれるフィールドのセルは変換できません。 ([ オプション] タブの [ アクティブなフィールド ] グループで、[ アクティブなフィールド] をクリックし、[ 値フィールドの設定] をクリックします。)
- 変換されたセルの場合、セルの書式設定は保持されますが、ピボットテーブル スタイルはピボットテーブルにのみ適用できるため、ピボットテーブル スタイルは削除されます。
GETPIVOTDATA 関数を使用してセルを変換する
OLAP 以外のデータ ソースを使用する場合、すぐに新しいピボットテーブル バージョン 2007 形式にアップグレードしたくない場合、または複雑でない Cube 関数の使用を回避したい場合に、数式で GETPIVOTDATA 関数を使用すると、ピボットテーブルのセルをワークシートの数式に変換できます。
[オプション] タブの [ピボットテーブル] グループの [GETPIVOTDATA の生成] コマンドがオンになっていることを確認します。
注
[GETPIVOTDATA の生成] コマンドは、[Excel のオプション] ダイアログ ボックスの [数式の操作] セクションの [数式] カテゴリで、[ピボットテーブル参照に GETPIVOTTABLE 関数を使用する] オプションを設定またはクリアします。
ピボットテーブルで、各数式で使用するセルが表示されていることを確認します。
ピボットテーブルの外にあるワークシートのセルに、レポートのデータを含める時点まで目的の数式を入力します。
ピボットテーブルの数式で使用するピボットテーブルのセルをクリックします。 ピボットテーブルからデータを取得する数式に GETPIVOTDATA ワークシート関数が追加されます。 この関数は、レポート レイアウトが変更された場合、またはデータを更新した場合に、正しいデータを引き続き取得します。
数式の入力を完了して、Enter キーを押します。
注
GETPIVOTDATA 数式で参照されているセルをレポートから削除すると、数式は #REF! を返します。