ピボットテーブルアイテム。受け取った資料はどうしますか。詳細データの表示と非表示

ピボットテーブルの主な要素。この作業を進める前に、ピボットテーブルがどの要素で構成されているかを知ることは不必要ではないと思います。そのために、図2の例を検討してください。ページフィールドは、元のリストまたはテーブルのフィールドであり、ページの向きの領域ピボットテーブル.

この例では、スコープは地域ごとに要約するために使用できるページマージンです。別のページフィールドアイテムを指定すると、ピボットテーブルが再計算され、そのアイテムに関連付けられている合計が表示されます。ページフィールド値-ページフィールド要素は、テーブルの元のリストのフィールドまたは列のレコードまたは値を結合します。言い換えると、これは、実際には、新しいリージョンの新しいデータソースへの移行があるたびに、このフィールドを切り替えることによるものです。

たとえば、複数レベルのデータの書式設定を定義したり、ピボットテーブルに複数レベルの小計があるなどの場合があります。また、テーブルスタイルと同様に、企業のガイドラインや個人の好みなど、特定のニーズに合わせて独自のスタイルを作成できます。ただし、ピボットテーブルはテーブルよりも複雑であるため、フォーマットを定義するために使用できるテーブル要素が多くなります。

たとえば、小計の複数のレベルの書式を定義したり、ピボットテーブルのさまざまなレベルで交代を定義したりできます。派生プロパティ-既存の属性から派生した元のデータセットに新しい属性を追加するために使用できます。その値は、プロパティのキーまたは生成された属性の名前のいずれかです。数値に設定されている場合、属性名の文字数の合計がこの数値を超えると、属性は垂直方向に表示されます。

不変のピボットテーブルの場合のみ。
編集可能なピボットテーブルの場合のみ。
エントリ数を超えると、対応するメッセージが表示されます。

ピボットテーブルレポートを使用して、要約データを要約、分析、調査、および表示できます。

行フィールドは、ピボットテーブルの行領域に配置された元のリストまたはテーブルのフィールドです。この例では、ProductsとVendorは文字列フィールドです。たとえば営業担当者などの内部行フィールドは、製品グループ内部フィールドなどのデータ領域外部行フィールドと完全に一致します。したがって、ここでは特定のルールがトレースされます。上位レベルのフィールドの各要素は、いわば下位レベルのすべてのフィールドを完成させ、製品パラメータはその下のすべてのデータ（製品販売者、これらの販売者による製品販売に関するデータ）を制限します。列フィールドは、列領域に配置されるソースリストまたはテーブルのフィールドです。

バージョンが異なる場合、外観が少し異なる場合がありますが、特に明記されていない限り、機能は同じです。ピボットテーブルは、大量のデータをすばやく要約するためのインタラクティブな方法です。ピボットテーブルを使用して、数値データを詳細に分析し、データに関する予期しない質問に答えることができます。ピボットテーブルは特別に設計されています。

数値データをサブタイプ化して集計し、カテゴリとサブカテゴリごとにデータを要約し、カスタム計算と数式を作成します。データレイヤーを展開および折りたたんで結果に焦点を合わせ、関心のある領域ごとに要約データから詳細をドリルします。行を列に移動するか、列を行に移動して、ソースデータのさまざまな要約を表示します。フィルタリング、並べ替え、グループ化、条件付き書式最も有用で興味深いデータのサブセットであり、必要な情報のみに集中できます。簡潔で魅力的で注釈付きのオンラインレポートまたは印刷されたレポートを提示する。多くのユーザーフレンドリーな方法で大量のデータをクエリします。 ..。たとえば、左側の家計支出の簡単なリストと、右側のリストに基づくピボットテーブルを次に示します。

この例では、Quartersは2つのフィールド要素KB2とKB3を含む列フィールドです。列の内部フィールドには要素が含まれ、対応するデータ領域は内部フィールドの上にある列の外部フィールドです。この例では、1つの列フィールドのみを示しています。

データ領域は、合計を含むピボットテーブルの部分です。データ領域のセルには、行または列のフィールド項目の合計が表示されます。データ領域の各セルの値は、元のデータに対応しています。この例では、セルC6は、同じ製品名、ディストリビューター、および特定の四半期の肉、Myastorg LLP、およびKB2を含むすべての生データレコードを要約しています。フィールドアイテムは、ピボットテーブルフィールドのサブカテゴリです。この例では、値Meat andSeafoodはProductsフィールドのフィールドアイテムです。

ピボットテーブルを操作する方法。ピボットテーブルを作成し、そのデータソースを選択し、ピボットテーブルフィールドリストのフィールドを配置し、初期レイアウトを選択した後、ピボットテーブルレポートの操作中に次のタスクを実行できます。次の手順に従ってデータを調べます。

データを展開および折りたたんで、値に関連する基本情報を表示します。

フィールドとアイテムの並べ替え、フィルタリング、グループ化。
サマリー関数を変更し、カスタム計算と数式を追加します。

次の手順に従って、フォームのレイアウトとフィールドのレイアウトを変更します。

フィールド項目は、ソースデータのフィールドまたは列のレコードを表します。フィールド項目は、行または列の見出しとして、およびページフィールドのドロップダウンリストに表示されます。データフィールドは、データを含むソースリストまたはテーブルのフィールドです。この例では、Order Amountフィールドは、OrderAmountフィールドまたは列の元のデータを要約するデータフィールドです。データフィールドは通常、統計や販売数量などの数値のグループを要約しますが、現在のデータはテキストである場合もあります。

フィールドを追加、変更、削除します。
フィールドまたは要素の順序を変更します。

次の手順に従って、列、行、および小計の配置を変更します。列と行の見出しをオンまたはオフにするか、空白行を表示または非表示にします。

行の上または下に小計を表示します。
更新時に列幅を調整します。
列フィールドを行領域に移動するか、行フィールドから列領域に移動します。
外側の行と列のセルを結合または展開します。

次の手順に従って、スペースとエラーの表示を変更します。

デフォルトでは、ピボットテーブルは合計値の要約関数を使用してテキストデータを要約し、合計値の要約関数を使用して数値データを要約します。ピボットテーブルの構造の変更外観フィールドボタンまたはフィールドアイテムの名前をドラッグすることで、シート上でピボットテーブルを直接編集できます。また、要素名を選択する必要があるフィールド内の要素の順序を変更してから、セルの境界線へのポインターを設定することもできます。

エラーと空白セルの表示方法を変更します。
データなしでアイテムとラベルを表示する方法を変更します。

次の手順に従ってフォーマットを変更します。

セルと範囲を手動および条件付きでフォーマットします。
ピボットテーブル形式の一般的なスタイルを変更します。
フィールドの数値形式を変更します。

グラフの種類や、タイトル、凡例の配置、データラベル、グラフの位置などの他のオプションを変更することもできます。ただし、いくつかの違いがあります。ただし、トレンドライン、データラベル、エラーバー、およびその他のデータセットへの変更は保存されません。

ポインタが矢印に変わったら、フィールドセルを新しい場所にドラッグします。フィールドを削除するには、フィールドボタンを詳細領域の外にドラッグします。フィールドを削除すると、ピボットテーブル内のすべての依存値が非表示になりますが、元のデータには影響しません。提供されているすべてのピボットテーブル構造化ツールを使用する必要がある場合、または現在のテーブルに以前にソースデータのすべてのフィールドが含まれていない場合は、ピボットテーブルウィザードを使用する必要があります。

標準のグラフは、適用後にこの書式が失われることはありません。後続の行の各セルには、列ヘッダーに対応するデータが含まれている必要があり、同じ列にデータ型を混在させないでください。たとえば、同じ列に通貨の値と日付を混在させないでください。さらに、データ範囲内に空白の行または列があってはなりません。名前付き範囲が拡張されてより多くのデータが含まれる場合、ピボットテーブルの更新には新しいデータが含まれます。ソースデータに、[データ]タブの[アウトライン]グループの[合計]コマンドを使用して作成した自動小計と総計が含まれている場合は、ピボットテーブルを作成する前に、同じコマンドを使用して小計と総計を削除します。

ピボットテーブルにページフィールドの大きなグループが含まれている場合、それらは行または列に配置できます。さらに、ピボットテーブルの構造を変更しても元のデータには影響しないことに注意してください。

仕事の終わり-

このトピックは次のセクションに属しています。

MicrosoftExcelを使用したピボットテーブルを使用した表形式の情報の処理

行と列を入れ替えることで、表示することで元のデータの新しい合計を作成できます別のページデータをフィルタリングできます。...つまり、これらのテーブルを使用すると、さまざまなソースからのデータを組み合わせることができます。...ピボットテーブルの作成ピボットテーブルを作成する前に、まずこのテーブルのデータを設定する必要があります。。

データベース、オンライン分析キューブなどの外部データソースからデータをプルできます。テキストファイル..。たとえば、要約して分析する販売レコードのデータベースを保存できます。ピボットテーブルアイテムをワークシート式に変換するを参照してください。たとえば、リレーショナルデータベースやテキストファイルからのデータ。

別のピボットテーブルをデータソースとして使用する

新しいピボットテーブルごとに、追加のメモリとディスクスペースが必要です。ただし、既存のピボットテーブルを同じブック内の新しいピボットテーブルのソースとして使用する場合は、両方が同じキャッシュを使用します。キャッシュを再利用すると、ブックのサイズが小さくなり、メモリに保存されるデータが少なくなります。場所の要件1つのピボットテーブルを別のピボットテーブルのソースとして使用するには、両方が同じブックに含まれている必要があります。元のピボットテーブルが別のブックにある場合は、新しいピボットを表示するブックの場所にソースをコピーします。

このトピックに関する追加の資料が必要な場合、または探しているものが見つからなかった場合は、ワークベースで検索を使用することをお勧めします。

受け取った資料をどうしますか：

この資料が役に立った場合は、ソーシャルネットワークのページに保存できます。

このセクションのすべてのトピック：

ピボットテーブルの作成
ピボットテーブルを作成します。ピボットテーブルを作成する前に、まず本、ページの一部を選択してこのテーブルのデータを設定する必要があります。データベースまたはリストの場合は、n

変更はピボットテーブルに影響します。メンバーをグループ化またはグループ解除するか、計算フィールドまたは計算メンバーを1つに作成すると、両方が影響を受けます。別のピボットテーブルから独立したピボットテーブルが必要な場合は、元のピボットテーブルをコピーする代わりに、元のデータソースに基づいて新しいピボットテーブルを作成できます。

既存のピボットテーブルレポートのソースデータの変更

これに関連する潜在的なメモリの問題に注意してください。ソースデータを変更すると、分析に使用できるデータが異なる場合があります。たとえば、テストデータベースから本番データベースに簡単に切り替えることができます。

ピボットテーブルの詳細とアクション
ピボットテーブルとそのテーブルで実行されるアクションの詳細。詳細データは、ピボットテーブルのサブカテゴリです。これらのピボットテーブルアイテムは、一意のテーブルアイテムまたは

詳細なピボットテーブルデータを表示または非表示
詳細なピボットテーブルデータを表示または非表示にします。以前に非表示にされた詳細データを表示したり、既存のデータを非表示にするには、フィールド要素、詳細データを選択する必要があります

更新によって発生した新しいデータを表示します。ピボットテーブルを更新すると、表示可能なデータも変更される可能性があります。フィールドリストのすべての新しいフィールドを表示し、レポートにフィールドを追加できます。問題は、開始情報が2年を区別せず、単に日付のあるフィールドがあることです。 2つの方法で解決します。

まず、行列式を使用し、次に、ピボットテーブルで計算されたアイテムを作成してこれを行います。あなたは私たちが話した投稿をチェックすることができます。まず、小さな2列のデータベースから始めます。

合計24レコードの金額。
これはある経済的価値の価値です。
これは、たとえば、会社の請求である可能性があります。

行列式による解決。

ピボットテーブルフィールドアイテムを表示または非表示にする
ピボットテーブルフィールドアイテムを表示または非表示にします。フィールド要素を表示または非表示にする必要がある場合は、まず、表示するフィールドを選択するか、

ピボットテーブルフィールドに最大または最小のアイテムを表示する
ピボットテーブルフィールドに最大または最小の項目を表示します。最大要素または最小要素を表示するには、要素が将来表示されるフィールドを選択する必要があります。

同じシート1で、行列関数を使用してケースを解決します。このような関数の操作方法については、次の投稿を参照してください。数式は中括弧で囲まれており、行列数式であることを示しています。それがコピーされ、月ごとの変動のパーセンテージが取得されます。

年と月の列を追加しました。データベース自体にすでに含まれている情報を使用して計算される新しいフィールドをデータベースに追加することは、あまり正統ではありません。これらの式は、有効な日付に適用され、その日付の正確な月と年を示します。

ピボットテーブルの詳細へのアクセスを無効にする
ピボットテーブルの詳細へのアクセスを無効にします。ピボットテーブルのデータ領域のセルにポインタを置いてダブルクリックすると、ソースデータの概要リストが表示されます

ピボットテーブルでのデータのグループ化とグループ化解除
ピボットテーブルでのデータのグループ化とグループ化解除。ピボットテーブルでは、日付、時刻、数値、および選択したアイテムをグループ化できます。たとえば、月を組み合わせて平方を要約できます。

このため、[日付]列を有効な日付にする必要があり、問題の各月の最初の日を取得する必要がありました。有効な日付は日、月、年を示す必要がありますが、指定された形式では、月と年のみを要求します。日は表示されませんが、これは形式ですが、日付は日-月-年として入力されるため、これらは有効です。

まず、以下のようなピボットテーブルを作成します。月のラベルを行のラベルに、年を列の列に、合計データを値に配置します。これで、計算されたアイテムを挿入できます。デザイン要素を作成できるウィンドウが表示されます。

ピボットテーブルフィールドのアイテムをグループ化およびグループ解除します
ピボットテーブルフィールドのアイテムをグループ化およびグループ解除します。要素をグループ化およびグループ解除するには、グループ化された要素を選択する必要があります。次に、パネルの[グループ化]ボタンをクリックします

ピボットテーブルで番号をグループ化およびグループ解除します
ピボットテーブルで番号をグループ化およびグループ解除します。番号をグループ化およびグループ解除するには、アイテムをグループ化するボックスで番号を選択します。次にボタンを押します

ただし、年を入力して数式を作成する必要はありませんが、マウスで項目を選択します。新しい列はパーセンテージでフォーマットされており、計算されたアイテムを使用してピボットテーブルが作成されています。計算項目と計算フィールドの両方のピボットテーブルで作成されたすべての数式を含むリストを作成できます。これを行うには、カーソルをピボットテーブルに置き、[ピボットテーブルツール]、[数式]、および[数式リストの作成]を選択します。

これにより、要求されたリストを受け取る新しいシートが作成されます。データセグメンテーションには、ピボットテーブルデータをフィルタリングするためにクリックできるボタンがあります。クイックフィルタリングに加えて、データセグメントはフィルタリングの現在の状態も示し、フィルタリングされたピボットテーブルレポートに正確に何が表示されているかを理解しやすくします。アイテムが選択されている場合、そのアイテムはフィルターに含まれ、そのアイテムのデータがレポートに表示されます。

ピボットテーブルで日付または時刻をグループ化およびグループ解除します
ピボットテーブルで日付または時刻をグループ化およびグループ解除します。日付または時刻をグループ化およびグループ解除するには、アイテムをグループ化するフィールドで日付または時刻を選択します。

ピボットテーブルページのフィールドアイテムのグループ化とグループ化解除
ピボットテーブルページフィールドの要素をグループ化およびグループ解除します。ピボットテーブルが外部データソースから作成されている場合は、ページフィールドが外部データをフェッチしていることを確認してください

ピボットテーブルのデータソースを更新または変更します。作業の過程で、作成したドキュメントに挿入する必要のある新しいデータが表示され、

ピボットテーブルのデータを更新する
ピボットテーブルのデータを更新します。ピボットテーブルのデータを更新するには、次の操作を行う必要があります。ピボットテーブルのセルを選択する必要があります。その内容が必要です。

ブックファイルを開くときにピボットテーブルの更新を無効にする
ブックファイルを開くときにピボットテーブルの更新を無効にします。ファイルを開くときにピボットテーブルの更新を無効にするには、更新が行われないピボットテーブルのセルを選択します

ピボットテーブルデータの並べ替え
ピボットテーブルデータの並べ替え。ピボットテーブルフィールドアイテムは、名前に従って昇順で自動的に並べ替えられます。アイテムを名前で並べ替えると、元のアイテムを復元できます

ピボットテーブルデータの要約と処理
ピボットテーブルのデータを合計して処理します。ピボットテーブルのデータフィールドは、サマリー関数のデフォルト以外の計算用に構成できます。複雑に実行するには

ピボットテーブルでの総計と小計の使用
ピボットテーブルで総計と小計を使用する。ピボットテーブルは、サマリー関数を使用して計算を実行するように構成できます。サマリー関数は、によって実行される計算のタイプです。

ピボットテーブルで小計を挿入または削除する
ピボットテーブルに小計を挿入または削除します。小計を挿入または削除するには、目的のフィールドのボタンへのポインタを設定する必要があります

ピボットテーブルのグラフを作成する
ピボットテーブルのグラフを作成します。ピボットテーブルのグラフを作成するには、[ピボットテーブル]ツールバーで、[ピボットテーブル]メニューから[選択]コマンドを選択します。それを確認します

この投稿では、Excelのシンプルで気の利いたピボットテーブルツールのコレクションを紹介します。英語でのヒントとコツと呼ばれるもの。ここでヒントを読んでください。誰が知っている、多分あなたは長い間あなたを苦しめてきた質問への答えを最終的に見つけるでしょう？

ヒント1.ピボットテーブルの自動更新

ピボットテーブルを自動的に更新したい場合があります。マネージャーのピボットテーブルを作成したとしましょう。マネージャーがあなたを彼のラップトップに連れて行ってくれない限り、あなたがそれを定期的に更新できることはありそうにありません。有効にすることができます自動更新ブックが開かれるたびに実行されるピボットテーブル：

ピボットテーブルを右クリックして、 ピボットテーブルオプション.
表示されるダイアログボックスで ピボットテーブルオプション タブを選択 データ.
チェックボックスをオンにします ファイルを開いたときに更新する.

図： 1.オプションを有効にします ファイルを開いたときに更新する

チェックボックス ファイルを開いたときに更新する ピボットテーブルごとに個別にインストールする必要があります。

メモを形式でダウンロードするか、形式の例をダウンロードします（ファイルにはVBAコードが含まれています）。

ヒント2.本のすべてのピボットテーブルの同時更新

ブックに複数のピボットテーブルが含まれている場合、それらを同時に更新すると問題が発生する可能性があります。これらの困難を克服するいくつかの方法があります：

方法1.ブックに含まれるピボットテーブルごとに、ブックを開いたときに自動的に更新される設定を選択できます（詳細については、ヒント1を参照してください）。

方法3：VBAコードを使用して、ブック内のすべてのピボットテーブルをオンデマンドで更新します。このアプローチでは、WorkbookオブジェクトのRefreshAllメソッドを使用します。この手法を使用するには、新しいモジュールを作成し、次のコードを入力します。

Sub Refresh_All（）

ThisWorkbook.RefreshAll

ヒント3.アイテムをランダムな順序で並べ替えます

図では図2は、ピボットテーブル内の領域のデフォルトの表示順序を示しています。地域は、西、北、中西部、南のアルファベット順に並べ替えられています。会社の規則で、最初に西部地域を表示し、次に中西部、北、および南地域を表示する必要がある場合は、手動で並べ替えます。セルC4に中西部に入り、キーを押すだけです入る..。地域の並べ替え順序が変わります。

ヒント4：ピボットテーブルをハードコードされた値に変換する

ピボットテーブルを作成する目的は、データを要約して表示することです。適切なフォーマット..。ピボットテーブルの元のデータは個別に保存されるため、「オーバーヘッド」が発生します。ピボットテーブルを値に変換すると、元のデータやピボットテーブルのキャッシュにアクセスしなくても、ピボットテーブルで取得した結果を使用できるようになります。ピボットテーブルがどのように変換されるかは、テーブル全体が影響を受けるか、テーブルの一部のみが影響を受けるかによって異なります。

ピボットテーブルの一部を変換するには、次の手順に従います。

コピーしたピボットテーブルデータを選択し、右クリックして選択します コピー （またはキーボードでCtrl + Cを入力します）。
ワークシートの任意の場所を右クリックして、コマンドを選択します ペースト （またはCtrl + Vを入力します）。

ピボットテーブル全体を変換する必要がある場合は、次の手順に従います。

ピボットテーブル全体を選択し、右クリックして選択します コピー..。ピボットテーブルにFILTERS領域が含まれていない場合は、Ctrl + Shift + *キーボードショートカットを使用してピボットテーブル領域を選択できます。
シートの任意の場所を右クリックして、コンテキストメニューからオプションを選択します 特別な貼り付け.
選択肢一つを選択してください その価値 をクリックします OK.

小計はオフラインデータセットではあまり役に立たないため、ピボットテーブルを変換する前に小計を削除することをお勧めします。すべての小計を削除するには、[デザイン]メニュー-\u003e [小計]-\u003e [小計を表示しない]に移動します。特定の小計を削除するには、合計が計算されるセルを右クリックします。コンテキストメニューで項目を選択します フィールドパラメータ とダイアログで フィールドパラメータ セクションで結果ラジオボタンを選択番号..。ボタンをクリックした後 OK 小計は削除されます。

ヒント5.LINEフィールドの空のセルを埋める

ピボットテーブルを変換すると、シートには値だけでなく、ピボットテーブルのデータ構造全体が表示されます。たとえば、図に示すデータ。 3は、表形式のピボットテーブルから派生したものです。

図： 3.左側の空のセルを埋めずに、この変換されたピボットテーブルを使用するのは問題があります

フィールドに注意してください領域そして 販売市場 このデータがピボットテーブルのROWS領域にある場合と同じ行構造を維持します。 Excel 2013は、ROWS領域のセルに値をすばやく入力する方法を提供します。ピボットテーブル領域をクリックして、メニューを移動します コンストラクタ -> レポートのレイアウト -> （図4）。次に、ピボットテーブルを値に変換できます。これにより、スペースのないデータテーブルが作成されます。

図： 4.コマンドを適用した後 すべてのアイテムラベルを繰り返します 空のセルはすべて埋められます

ヒント6：ピボットテーブルの数値フィールドのランク付け

多数のデータ項目を含むフィールドをソートおよびランク付けするプロセスでは、分析対象のデータ項目の数値ランクを決定することは必ずしも容易ではありません。さらに、ピボットテーブルが値に変換される場合、整数フィールドに表示される数値ランクを各データアイテムに割り当てると、生成されたデータセットの分析が大幅に容易になります。図のようなピボットテーブルを開きます。 5.同じインジケーターに注意してください- フィールド別金額販売量 -2回表示されます。メトリックの2番目のインスタンスを右クリックして、 追加の計算 -> 最大から最小への並べ替え （図6）

ランクを作成した後、フィールドのキャプションとフォーマットをカスタマイズできます（図14.9）。結果は、ランク付けされた素晴らしいレポートになります。

ヒント7：ピボットテーブルレポートのサイズを縮小する

ピボットテーブルレポートを生成すると、Excelはデータのスナップショットを取得し、ピボットテーブルキャッシュに保存します。ピボットテーブルキャッシュは、より高速なアクセスのためにデータソースのコピーを格納するメモリの特別な領域です。つまり、Excelはデータのコピーを作成し、それをブックに関連付けられたキャッシュに保存します。ピボットテーブルキャッシュは、ワークフローの最適化を提供します。フィールドのレイアウトの変更、新しいフィールドの追加、アイテムの非表示など、ピボットテーブルに加えられた変更はすべて高速であり、システムリソースの要件ははるかに控えめです。ピボットテーブルキャッシュの主な欠点は、ピボットテーブルを最初から作成するたびにブックファイルのサイズがほぼ2倍になることです。

元のデータを削除します。ブックに元のデータセットとピボットテーブルが含まれている場合、そのファイルサイズは2倍になります。したがって、元のデータを安全に削除できます。これは、ピボットテーブルの機能にはまったく影響しません。元のデータを削除した後、ワークブックファイルの圧縮バージョンを保存することを忘れないでください。元のデータを削除した後は、通常どおりピボットテーブルを使用できます。唯一の問題は、元のデータが不足しているためにピボットテーブルを更新できないことです。背景データが必要な場合は、総計領域の行と列の交点をダブルクリックします（図7では、これはセルB18です）。そうすることで、Excelはピボットテーブルキャッシュの内容を新しいワークシートにダンプします。

ヒント8：自動拡張可能なデータ範囲を作成する

確かに、ピボットテーブルレポートを毎日更新する必要がある状況に繰り返し遭遇しました。これは、新しいレコードがデータソースに絶えず追加されている場合に最もよく必要になります。このような場合、新しいレコードを新しいピボットテーブルに追加する前に、以前に使用した範囲を再定義する必要があります。ピボットテーブルの元のデータ範囲を再定義するのは簡単ですが、頻繁に行うと、非常に面倒になります。

この問題の解決策は、ピボットテーブルが作成される前であっても、元のデータ範囲をテーブルに変換することです。おかげで excelスプレッドシートデータの量に応じて自動的に拡大または縮小できる名前付き範囲を作成できます。コンポーネント、グラフ、ピボットテーブル、または数式を範囲にリンクして、データセットの変更を追跡することもできます。

説明されている手法を実装するには、ソースデータを選択し、タブにあるテーブルアイコンをクリックします。 インサート （図8）またはCtrl + T（英語T）を押します。クリック OK 開いたウィンドウで。ピボットテーブルのソースデータ範囲を再定義する必要はありませんが、ピボットテーブルの範囲にソースデータを追加するときは、ボタンをクリックする必要があることに注意してください。更新.

ヒント9：通常のテーブルとピボットテーブルを比較する

2つの異なるテーブルを比較する場合は、ピボットテーブルを使用すると便利です。これにより、時間を大幅に節約できます。 2011年と2012年の顧客の詳細を表示する2つのテーブルがあるとします（図9）。これらのテーブルの小さな寸法は、例としてのみここに示されています。実際には、はるかに大きいテーブルが使用されます。

比較により、ピボットテーブルが作成される単一のテーブルが作成されます。これらのテーブルに関連するデータにタグを付ける方法があることを確認してください。この例では、これは列を使用します 会計年度 （図10）。 2つのテーブルを結合した後、結果の結合されたデータセットを使用して、新しいピボットテーブルを作成します。ピボットテーブル列領域がテーブルタグ（テーブルの起点を示す識別子）として使用されるように、ピボットテーブルをフォーマットします。図に示すように。 11、年は列領域にあり、顧客の詳細は行領域にあります。データ領域には、各顧客の販売量が含まれています。

ヒント10：ピボットテーブルの自動フィルタリング

ご存知のように、ピボットテーブルでオートフィルターを使用することはできません。ただし、ピボットテーブルでオートフィルターを有効にするトリックがあります。この手法の背後にある原則は、ピボットテーブルの最後のヘッダー（図12のセルD3）の右側にマウスポインターを置き、リボンに移動して選択することです。 データ -> フィルタ..。これからは、ピボットテーブルにオートフィルターが表示されます。たとえば、平均取引率よりも高いすべての顧客を選択できます。オートフィルターは、ピボットテーブルに分析のレイヤーを追加します。

ヒント11.ピボットテーブルに表示されるデータセットを変換する

ピボットテーブルに変換された元のデータの最適なレイアウトは、表形式のレイアウトです。このタイプのレイアウトには、空の行または列がない、各列にヘッダーがあり、各フィールドに各行に値があり、列にデータの繰り返しグループが含まれていないという特徴があります。実際には、図に示すようなデータセットがよくあります。 13.ご覧のとおり、月の名前はテーブルの上部に沿って1行に表示され、列ラベルと実際のデータの2つの機能を果たします。このようなテーブルから作成されたピボットテーブルでは、これにより管理する12のフィールドが作成され、それぞれが個別の月を表します。

この問題を解消するために、いくつかの統合された範囲を持つピボットテーブルを中間ステップとして使用できます（詳細を参照）。マトリックススタイルのデータセットをピボットテーブルの作成により適したデータセットに変換するには、次の手順に従います。

手順1.すべての非列フィールドを1つの列に結合します。複数の統合範囲を持つピボットテーブルを作成するには、単一のディメンション列を作成します。この例では、月フィールドに関連しないすべてのものがディメンションと見なされます。したがって、フィールド 販売市場 そして サービスの説明 1つの列にマージする必要があります。フィールドを1つの列に結合するには、セミコロンを区切り文字として使用して2つのフィールドを連結する数式を入力するだけです。新しい列に名前を付けます。入力した数式が数式バーに表示されます（図14）。

図： 14.列の連結の結果 販売市場 そして サービスの説明

連結列を作成した後、数式を値に変換します。これを行うには、新しく作成された列を選択し、Ctrl + Cを押してから、コマンドを実行します。 ペースト -> 特別な貼り付け -> その価値..。列を削除できるようになりました 販売市場 そして サービスの説明 （図15）。

図： 15.削除された列 販売市場 そして サービスの説明

手順2.複数の統合範囲を持つピボットテーブルを作成します。次に、以前のバージョンのExcelの多くのユーザーに馴染みのあるピボットテーブルとグラフウィザードを呼び出す必要があります。 Alt + D + Pを押して、このウィザードを呼び出します。残念ながら、このキーボードショートカットは英語版のExcel 2013用です。ロシア語版では、キーボードショートカットのAlt + D + Nに対応しています。しかし、それは私には知られていない理由で機能しません。ただし、古き良きピボットテーブルウィザードをクイックアクセスツールバーに移動することはできます。を参照してください。ウィザードを開始した後、ラジオボタンを選択します 複数の統合範囲で..。クリック さらに..。スイッチを設定する ページフィールドを作成する をクリックします さらに..。動作範囲を定義し、をクリックします完了（詳細を見る）。ピボットテーブルを作成します（図16）。

手順3.総計行の行と列の交点をダブルクリックします。この時点で、いくつかの統合範囲を含むピボットテーブル（図16）がありますが、これはほとんど役に立ちません。行と総計列の交点にあるセルを選択してダブルクリックします（この例では、これはセルN88です）。図に示すような構造の新しいシートが得られます。 17.実際、このシートは元のデータの転置バージョンです。

手順4.行列を個別のフィールドに分割します。列を分割することは残っています ライン 別々のフィールドに（元の構造に戻ります）。列の直後に空の列を1つ追加します ライン..。列Aを強調表示してから、リボンタブに移動します データ ボタンをクリックします 列テキスト..。画面にダイアログボックスが表示されます テキスト配布ウィザード..。最初のステップで、ラジオボタンを選択します 区切られた[次へ]ボタンをクリックします。次のステップで、ラジオボタンを選択します セミコロン をクリックします完了..。 Ctrl + Tを押して、テキストをフォーマットし、タイトルを追加し、元のデータをテーブルに変換します（図18）。

図： 18.このデータセットは、ピボットテーブルの作成に最適です（図13と比較してください）。

ヒント12：ピボットテーブルに2つの数値形式を含める

ここで、正規化されたデータセットにより、分析が容易なピボットテーブルの作成が困難になる状況を考えてみましょう。例を図に示します。図１９は、各販売エリアの２つの異なる指標を含む表である。メトリックを識別する列Dに注目してください。

この表はかなり良いフォーマットの例ですが、それほど良いものではありません。一部のメトリックは数値形式で表示し、その他のメトリックはパーセンテージで表示する必要があることに注意してください。しかし、ソースデータベースでは、フィールド値タイプはDoubleです。データセットからピボットテーブルを作成する場合、2つの異なる数値形式を同じフィールドに割り当てることはできません。値..。ここには簡単な経験則があります。1つのフィールドが1つの数値形式に対応します。パーセンテージ形式が割り当てられているフィールドに数値形式を割り当てようとすると、パーセンテージ値がパーセント記号で終わる通常の数値に変わります（図20）。

この問題を解決するために、1.5より大きい任意の値を数値としてフォーマットするカスタム数値フォーマットが使用されます。値が1.5未満の場合は、パーセンテージとしてフォーマットされます。ダイアログボックスで セル形式 タブを選択 （すべての形式） とフィールドで タイプ 次のフォーマット文字列を入力します（図21）。[\u003e \u003d 1,5] $ ＃## 0; [<1,5]0,0%

図： 21.1.5未満の数値がパーセンテージとしてフォーマットされるカスタム数値フォーマットを適用します

結果を図1に示します。 22.ご覧のとおり、各インジケーターは正しくフォーマットされています。もちろん、このヒントのレシピは普遍的ではありません。むしろ、それは実験する方向を示しています。

ヒント13：ピボットテーブルの度数分布を作成する

Excel関数を使用して度数分布を生成したことがある場合 周波数、あなたはおそらくこれが非常に難しい作業であることを知っています。さらに、データ範囲を変更した後、すべてを最初からやり直す必要があります。このセクションでは、単純なピボットテーブルを使用して単純な度数分布を作成する方法を学習します。まず、行領域のデータを使用してピボットテーブルを作成します。図に注意してください。 23、フィールドは行領域にあります ボリューム 売上高.

[行]領域の任意の値を右クリックして、コンテキストメニューからオプションを選択します グループ..。ダイアログボックスで グループ化 （図24）度数分布の開始、終了、ステップを決定するパラメーターの値を決定します。 [OK]をクリックします。

図： 24.ダイアログボックスで グループ化 度数分布パラメーターを調整します

ピボットテーブルにフィールドを追加する場合 お客様 （図25）、注文のサイズ（ドル）に対する顧客トランザクションの頻度分布を取得します。

図： 25.これで、注文サイズ（ドル）に応じた顧客トランザクションの配布を自由に行うことができます。

この手法の利点は、ピボットテーブルレポートフィルターを使用して、次のような他の列に基づいてデータをインタラクティブにフィルター処理できることです。領域そして 販売市場..。ユーザーは、行領域の任意の数値を右クリックしてオプションを選択することにより、度数分布間隔をすばやく調整することもできます。 グループ..。表示をわかりやすくするために、ピボットグラフを追加できます（図26）。

ヒント14.ピボットテーブルを使用してデータセットをワークシートシート全体に分散する

アナリストは、多くの場合、地域、販売エリア、マネージャーなどごとに異なるピボットテーブルレポートを作成する必要があります。このタスクには通常、ピボットテーブルを新しいシートにコピーしてから、適切な領域とマネージャーを反映するようにフィルターフィールドを変更するという長いプロセスが含まれます。このプロセスは手動で行われ、分析ごとに繰り返されます。ただし、一般的に、Excelには個別のピボットテーブルの作成を任せることができます。パラメータを適用した結果フィルタフィールド領域のアイテムごとに、個別のピボットテーブルが自動的に作成されます。この機能を使用するには、フィルターフィールドを含むピボットテーブルを作成するだけです（図27）。ピボットテーブルとタブの任意の場所にカーソルを置きます分析チームグループで ピボットテーブル ドロップダウンをクリックします オプション （図28）。次に、ボタンをクリックします レポートフィルターページを表示する.

図： 28.ボタンをクリックします レポートフィルターページを表示する

表示されるダイアログボックス（図29）で、個別のピボットテーブルを作成するフィルターフィールドを選択できます。適切なフィルターフィールドを選択して、 OK.

図： 29.ダイアログボックス レポートフィルターページの表示

フィルタフィールドの要素ごとに、ピボットテーブルが作成され、別々のシートに配置されます（図30）。シートタブの名前はフィルターフィールドアイテムと同じであることに注意してください。パラメータに注意してください フィルタページを表示する フィルターフィールドに1つずつ適用できます。

ヒント15：ピボットテーブルを使用してデータセットを個々のブックに分散する

ヒント14では、特別なオプションを使用して分割しました 要約表 ワークブックのさまざまなシートの販売市場別。分割する必要がある場合 初期データ 別の本のさまざまな市場では、小さなVBAコードを使用できます。まず、フィルターをかけるフィールドをフィルターフィールド領域に配置します。フィールドを配置します 売上高 値の範囲に（図31）。提案されたVBAコードは、各FILTERアイテムを順番に選択し、関数を呼び出します 詳細を表示新しいデータシートを作成します。このシートは新しいワークブックに保存されます

コード VBA。

サブExplodeTable（）

PivotItemとしてのDimPvtItem

Dim PvtTable As PivotTable

PivotFieldとしての薄暗いstrfield

'スクリプトに従って変数を変更する

ConststrFieldName \u003d "Marketplace" ‘<—Изменение имени поля

Const strTriggerRange \u003d "A4" '<—Изменение диапазона триггера

'ピボットテーブルの名前を変更します（必要な場合）

SetPvtTable \u003d ActiveSheet.PivotTables（ "PivotTable1"） '<—Изменение названия сводной

‘選択したフィールドの各要素を循環します

PvtTable.PivotFields（strFieldName）.PivotItemsの各PvtItemに対して

PvtTable.PivotFields（strFieldName）.CurrentPage \u003d PvtItem.Name

範囲（strTriggerRange）.ShowDetail \u003d True

'仮シートに名前を割り当てる

ActiveSheet.Name \u003d "TempSheet"

「データを新しいブックにコピーし、一時シートを削除する

ActiveSheet.Cells.Copy

ActiveSheet.Paste

Cells.EntireColumn.AutoFit

Application.DisplayAlerts \u003d False

ActiveWorkbook.SaveAs _

ファイル名：\u003d ThisWorkbook.Path＆ "\\"＆PvtItem.Name＆ "。xlsx"

ActiveWorkbook.Close

シート（「テンプシート」）。削除

Application.DisplayAlerts \u003d True

このコードを新しいVBAモジュールに入力します。次の定数と変数の値を確認し、必要に応じて変更してください：

ConststrFieldName。データを区切るために使用されるフィールドの名前。言い換えれば、それはピボットテーブルのフィルター/ページ領域に収まるフィールドです。
ConststrTriggerRange。ピボットテーブルのデータ領域からの単一の数値を格納するトリガーセル。この場合、トリガーセルはA4です（図31を参照）。

VBAコードを実行した結果、各販売エリアのデータは個別のワークブックに保存されます。

ジェレンの本、アレクサンダーに基づくメモ。 ..。第14章。