複数のピボットテーブルを同時にフィルタリングする

複雑なレポート、特に Microsoft Excel のダッシュボードを作成する場合、一度に複数のピボット テーブルを同時にフィルター処理する必要があることがよくあります。 これを実装する方法を見てみましょう。

方法 1: 同じデータ ソースでピボットをフィルター処理するための一般的なスライサー

ピボットが XNUMX つのソース データ テーブルに基づいて構築されている場合、最も簡単な方法は、ピボットを使用して同時にフィルタリングすることです。 一度にすべてのピボット テーブルに接続されたグラフィック ボタン フィルタです。

追加するには、集計の XNUMX つとタブで任意のセルを選択します 分析 チームを選ぶ スライスを貼り付け (分析 — スライサーを挿入). 開いたウィンドウで、データをフィルタリングする列のボックスをオンにして、 OK:

作成されたスライサーは、既定では、作成されたピボットのみをフィルター処理します。 ただし、ボタンを使用して 接続のレポート （連絡先の報告） タブ スライス (スライス) フィルタリングされたテーブルのリストに他の集計テーブルを簡単に追加できます。

方法 2. さまざまなソースの要約をフィルタリングするための一般的なスライス

ピボットが XNUMX つではなく、別のソース データ テーブルに従って作成された場合、上記の方法は機能しません。 接続のレポート 同じソースから作成された要約のみが表示されます。

ただし、データ モデルを使用すると、この制限を簡単に回避できます (この記事で詳しく説明します)。 テーブルをモデルにロードしてそこにリンクすると、フィルタリングが両方のテーブルに同時に適用されます。

入力データとして販売費と輸送費の XNUMX つのテーブルがあるとします。

それらのそれぞれについて独自の要約を作成し、共通のカットを持つ都市で同時にフィルタリングするというタスクに直面しているとします。

次のことを行います。

1. キーボード ショートカットを使用して元のテーブルを動的なスマート テーブルに変換する Ctrlキー+T またはコマンド ホーム – 表としてフォーマットする (ホーム — テーブルとしてフォーマット) そして彼らに名前を付けます tableProdaji и タブトランスポート タブ コンストラクタ （設計）.

2. ボタンを使用して、両方のテーブルを順番にモデルにロードします データ モデルに追加 [PowerPivot] タブで。

PowerPivot は XNUMX 対多のリレーションシップのみをサポートしていますが、リンクしている列にテーブルの XNUMX つが重複していない必要があるため、モデル内のこれらのテーブルを直接リンクすることはできません。 フィールドの両方のテーブルに同じものがあります 市町村 繰り返しがあります。 したがって、両方のテーブルから一意の都市名のリストを含む別の中間ルックアップ テーブルを作成する必要があります。 これを行う最も簡単な方法は、2016 バージョン以降 Excel に組み込まれている Power Query アドイン機能を使用することです (Excel 2010 ～ 2013 の場合は、Microsoft の Web サイトから無料でダウンロードできます)。

3. 「スマート」テーブル内の任意のセルを選択したら、ボタンを使用して Power Query にセルを XNUMX つずつ読み込みます。 テーブル/範囲から タブ 且つ (データ — テーブル/範囲から) 次に、Power Queryウィンドウでオンを選択します メイン チーム 閉じてロード – 閉じてロード (ホーム — クローズ & ロード — クローズ & ロード…) およびインポート オプション 接続を作成するだけ (接続のみ作成):

4. コマンドを使用して、両方のテーブルを XNUMX つに結合します データ – クエリの結合 – 追加 (データ — クエリの結合 — 追加). ヘッダー内の同じ名前の列は、互いに下に収まります (列のように) 市町村）、一致しないものは別の列に配置されます（ただし、これは重要ではありません）。

5. 列を除くすべての列を削除します 市町村タイトルを右クリックしてコマンドを選択する 他の列を削除する (他の列を削除) 次に、列見出しをもう一度右クリックしてコマンドを選択し、重複する都市名をすべて削除します。 重複を削除する (重複を削除):

6. 作成された参照リストは、経由でデータ モデルにアップロードされます。 ホーム — 閉じてロード — 閉じてロードイン (ホーム — クローズ & ロード — クローズ & ロード…) そしてオプションを選択してください 接続を作成するだけ (接続のみ作成) そして最も重要なこと！ – チェックボックスをオンにします このデータをデータ モデルに追加します (このデータをデータモデルに追加):

7. これで、Power Pivot ウィンドウ (タブ パワーピボット –ボタン マネジメント）、 切り替える チャートビュー (図表示) 作成された都市の中間ディレクトリを介して、売上高と輸送費のテーブルをリンクします (テーブル間でフィールドをドラッグします)。

8. ボタンを使用して、作成されたモデルに必要なすべてのピボット テーブルを作成できるようになりました。 要約表 （ピボットテーブル） on メイン （ホーム） PowerPivot ウィンドウのタブをクリックし、任意のピボット内の任意のセルを選択して、タブ上で 分析 スライスボタンを追加 スライスを貼り付け (分析 — スライサーの挿入) リストボックスでスライスすることを選択します 市町村 追加されたディレクトリで：

さて、おなじみのボタンをクリックすることで 接続のレポート on スライスタブ (スライサー — レポート接続) サマリーは関連するソース テーブルに基づいて構築されているため、すべてのサマリーが表示されます。 不足しているチェックボックスを有効にしてクリックする必要があります OK – スライサーは、選択したすべてのピボット テーブルを同時にフィルタリングし始めます。