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