ピボットテーブルは、従来、OLAP キューブや、テーブル間の豊富な接続を持つその他の複雑なデータ ソースを使用して構築されてきました。 ただし、Excel では、複数のテーブルを自由にインポートし、テーブル間に独自の接続を構築できます。 この柔軟性は強力ですが、関連のないデータを簡単にまとめ、奇妙な結果につながります。
このようなピボットテーブルを作成したことはありますか? リージョン別の購入の内訳を作成することを目的としているため、購入金額フィールドを [値] 領域に削除し、販売地域フィールドを [列ラベル ] 領域にドロップしました。 しかし、結果は間違っています。
解決するにはどうすればよいでしょうか。
問題は、ピボットテーブルに追加したフィールドが同じブック内にある可能性がありますが、各列を含むテーブルは関連しないということです。 たとえば、各販売地域を一覧表示するテーブルと、すべてのリージョンの購入を一覧表示する別のテーブルがあるとします。 ピボットテーブルを作成して正しい結果を取得するには、2 つのテーブル間にリレーションシップを作成する必要があります。
リレーションシップを作成すると、ピボットテーブルによって購入テーブルのデータがリージョンの一覧と正しく結合され、結果は次のようになります。
Excel には、このような関係の問題を自動的に検出して修正するための Microsoft Research (MSR) によって開発されたテクノロジが含まれています。
自動検出の使用
自動検出では、ピボットテーブルを含むブックに追加した新しいフィールドがチェックされます。 新しいフィールドがピボットテーブルの列ヘッダーと行ヘッダーに関連しない場合は、ピボットテーブルの上部にある通知領域に、リレーションシップが必要である可能性があることを知らせるメッセージが表示されます。 また、Excel では、新しいデータを分析して、潜在的なリレーションシップを見つけます。
引き続きメッセージを無視し、ピボットテーブルを操作できます。ただし、[ 作成] をクリックすると、アルゴリズムが機能し、データが分析されます。 新しいデータの値、ピボットテーブルのサイズと複雑さ、および既に作成したリレーションシップによっては、このプロセスに数分かかることがあります。
このプロセスは、次の 2 つのフェーズで構成されます。
-
リレーションシップの検出。 分析が完了したら、推奨されるリレーションシップの一覧を確認できます。 取り消さない場合、Excel は自動的にリレーションシップを作成する次の手順に進みます。
-
リレーションシップの作成。 リレーションシップが適用されると、確認ダイアログが表示され、[ 詳細 ] リンクをクリックして、作成されたリレーションシップの一覧を表示できます。
検出プロセスを取り消すことはできますが、作成プロセスを取り消すことはできません。
MSR アルゴリズムは、モデル内のテーブルを接続するために、"可能な限り最適な" リレーションシップセットを検索します。 アルゴリズムは、列名、列のデータ型、列内の値、ピボットテーブル内の列を考慮して、新しいデータに対して考えられるすべてのリレーションシップを検出します。
次に、Excel は、内部ヒューリスティックによって決定される、最高の "品質" スコアを持つリレーションシップを選択します。 詳細については、「リレーションシップの 概要」および「リレーションシップの トラブルシューティング」を参照してください。
自動検出によって正しい結果が得られない場合は、リレーションシップの編集、削除、または手動で新しいリレーションシップの作成を行うことができます。 詳細については、「2 つのテーブル間のリレーションシップを作成する」または「ダイアグラム ビューでのリレーションシップの作成」を参照してください。
ピボット テーブルの空白行 (不明なメンバー)
ピボットテーブルは関連するデータ テーブルをまとめるため、キーまたは一致する値によって関連付けることができないデータがテーブルに含まれている場合は、そのデータを何らかの方法で処理する必要があります。 多次元データベースでは、一致しないデータを処理する方法は、一致する値を持たないすべての行を Unknown メンバーに割り当てることです。 ピボットテーブルでは、不明なメンバーが空白の見出しとして表示されます。
たとえば、店舗別に売上をグループ化するピボット テーブルを作成したが、販売テーブル内の一部のレコードにストア名が一覧表示されていない場合、有効なストア名を持たないすべてのレコードがグループ化されます。
行が空白になる場合は、2 つの選択肢があります。 複数のテーブル間にリレーションシップのチェーンを作成することによって機能するテーブル リレーションシップを定義することも、空白行が発生するピボットテーブルからフィールドを削除することもできます。