ピボットテーブルの使用

先ほど、セルについて言及するときに、ピボットテーブル通常のリンクの代わりに、GET.DATA.PUMP.TABLE関数が返されます（を参照）。興味のある方 なので この不便を克服するために、私は言及された注記を参照することをお勧めします。あなたが疑問に思っているなら、なぜこれがどのように発生するかであり、関数GET DATA.COMPLETE.TABLEの良い面も何ですか。次に、本Jelen、Alexanderの断片を提案します。（第15章）。検討中の手法により、ピボットテーブルのユーザーに頭痛の種となる多くの問題、特に次の問題に対処できます。

ピボットテーブルが更新されると、以前に適用されていた書式は表示されなくなります。数値形式は失われます。列幅の調整結果が消えます。
存在しません簡単な方法非対称ピボットテーブルを作成します。唯一のオプションは名前付きセットを使用することですが、この方法はデータモデルピボットテーブルを使用しているユーザーのみが使用でき、通常のピボットテーブルは使用できません。
Excelはテンプレートを記憶できません。ピボットテーブルを何度も再作成する必要がある場合は、再グループ化して、計算フィールドとメンバーを適用し、他の多くの同様のタスクを実行する必要があります。

実際、ここで説明するすべてが新しいものではありません。さらに、Excel 2002以降、同様の手法が使用されています。ただし、ユーザーとのコミュニケーションでは、ユーザーに精通しているのは1％未満であることが示されています。ユーザーが持っている唯一の質問は、奇妙なGET DATA OFPANEL.TABLE関数をオフにする方法です。それは残念だ…

形式でメモをダウンロードするか、形式で例をダウンロードします

さて、順番に始めましょう。

問題のある関数を放棄する方法GETDATA.PERSONAL TABLES

GET PIVOTTABLE DATA機能は、多くのユーザーにとって長い間頭痛の種でした。突然、警告なしに、Excel2002でピボットテーブルの動作が変更されました。データを参照するピボットテーブルの外部で数式の作成を開始するとすぐに、この関数はどこからともなく出てきます。

図に示すピボットテーブルで仮定します。 1、2015年と2014年のデータを比較する必要があります。

図： 1.元のピボットテーブル

見出し「％Height」をセルD3に追加します。
フォーマットをセルC3からセルD3にコピーします。
セルD4に等号を入力します。
セルC4をクリックします。
除算演算を表す/（スラッシュ）記号を入力します。
セルB4をクリックします。
-1を入力し、キーの組み合わせを押します同じセルにとどまる。結果をパーセンテージでフォーマットします。西部地域では収入が43.8％減少したことがわかります（図2）。あまり良い結果ではありません。
最初の数式の入力が終了したら、セルD4を選択します。
セルの右下隅にある小さな正方形をダブルクリックします。この正方形は、数式をコピーしてレポートの列全体に入力するために使用できる入力ハンドルを表します。

画面をちらっと見ながら数式のコピーを終えると、何かがおかしいことに気付くでしょう。各地域では、年間で43.8％の減少が見られました（図3）。

図： 3.数式を列のすべてのセルにコピーし終えると、各領域で43.8％の低下が見られます。

これは実際には起こりそうにありません。上記の手順を実行した後、Excelは数式\u003d C4 / B4-1を作成することを誰もが教えてくれます。セルD4に戻り、数式バーに注目してください（図4）。ほんの少しの悪魔！単純な式\u003d C4 / B4-1はもう存在しません。その代わりに、プログラムは複雑な構造をGET.DATA.PUMP.TABLE関数に置き換えます。この数式がセルD4で正しい結果をもたらすのに、下のセルにコピーした後、機能しないのはなぜですか？

何が起こったのかに対するユーザーの最初の反応は次のようになります：「私のレポートを台無しにしたこの奇妙なGET.DATA.PERSONAL.TABLE構造は何ですか？」ほとんどのユーザーは、この機能をすぐに削除したいと思うでしょう。「なぜマイクロソフトはこの機能を私たちに提供したのか」という質問をする人もいます。

このようなものはありませんでした excelの時間 2000. GET.DATA.PERSONAL.TABLES関数に定期的に遭遇し始めたので、私はそれが嫌いでした。あるセミナーで誰かが私にそれを大義のためにどのように使うことができるかと尋ねたとき、私は唖然としました。私はそのような質問をしたことがありません！私の意見では、そしてほとんどのExcelユーザーの意見では、GET.DATA.PUMP.TABLE関数は、善の力とは何の関係もない悪の産物でした。幸い、この機能を無効にする方法は2つあります。

数式を入力して、GET PIVOTTABLEDATA関数をブロックします。GETPIVOTTABLE関数が表示されないようにする簡単な方法があります。これを行うには、マウスまたはカーソルキーを使用せずに数式を作成する必要があります。次の手順に従ってください。

セルD4に移動し、\u003d（等号）と入力します。
C4と入力します。
/（除算のスラッシュ）を入力します。
B4と入力します。
-1を入力します。
クリック入る.

これで、通常のを作成しました excelの数式、列の下のセルにコピーでき、正しい結果を得ることができます（図5）。ご覧のとおり、ピボットテーブル内のデータを参照する、ピボットテーブル外の領域に数式を作成できます。そして、これが可能であると信じていない人は、彼らに彼ら自身で記述された行動を実行させてください。

図： 5.キーボードから\u003d C4 / B4-1と入力するだけで、数式は必要に応じて機能します

一部のユーザーは、数式を入力する通常の順序に不快感を覚えます。さらに、提案されたオプションはより面倒です。あなたがこれらのユーザーの1人である場合、2番目の方法はあなたのためです...

GET PANEL.TABLEDATA機能を無効にします。GET PANEL.TABLEDATA機能を永続的に無効にすることができます。メニューリボンをクリックします ファイル → オプション..。開いたウィンドウで オプションExcel 貢献に行く数式オプションの横にあるチェックボックスをオフにします 機能を使用するピボットテーブルリンクのGetPivotData..。クリック OK.

代替オプション。ピボットテーブルをクリックし、表示されるコンテキストタブをクリックします分析ボタンの横にあるドロップダウンリストをクリックします オプション..。横のチェックボックスをオフにします GetPivotDataを作成します （図7）。チェックボックスはデフォルトでオンになっています。

マイクロソフトがGETPANEL.TABLES機能を提供したのはなぜですか。この機能がひどい場合、Microsoftがデフォルトで有効にしたのはなぜですか？新しいバージョンのExcelでこの機能のサポートを維持することに関心があるのはなぜですか？彼らはユーザーの感情を知っていますか？そして、楽しい部分に移ります...

GET PIVOTTABLEDATAを使用してピボットテーブルを改善する

ピボットテーブルは人類の偉大な発明です。ピボットテーブルは数回クリックするだけで作成されるため、高度なフィルター、BDSUMM関数、およびデータテーブルを適用する必要がありません。ピボットテーブルを使用すると、大量のデータから1ページのレポートを作成できます。これらの利点は、書式設定が不十分であることや、ピボットテーブルを次の値に変換する必要があることなど、ピボットテーブルのいくつかの欠点を覆い隠します追加のカスタマイズ..。図では図8は、ピボットテーブルを作成するための一般的なプロセスを示しています。この場合、すべてが初期データから始まります。ピボットテーブルを作成し、考えられるすべての手法を使用してカスタマイズおよび改善します。ピボットテーブルを値に変換して、最終的なフォーマットを行うことがあります。

Rob Colley（Microsoftの開発者）によって提案され、以下で説明する新しいピボットテーブル方法論は、上記のプロセスの改善の結果です。この場合、プリミティブピボットテーブルが最初に作成されます。このテーブルをフォーマットする必要はありません。次に、1ステップの比較的時間のかかるプロセスに従って、最終レポートを格納する適切にフォーマットされたシェルを作成します。その後、GET.DATA.PUMP.TABLE関数を使用して、シェル内のレポートにデータをすばやく入力します。新しいデータを受け取ったら、それをシートに配置し、プリミティブピボットテーブルを更新して、シェルにあるレポートを印刷できます（図9）。この手法には、否定できない多くの利点があります。たとえば、レポートが生成された直後にフォーマットすることを心配する必要はありません。ピボットテーブルを作成するプロセスは、ほぼ完全に自動化されます。

次のセクションでは、過去数か月の実際の数値と将来の月の目標を表示する動的レポートを作成する方法について説明します。

プリミティブピボットテーブルを作成します。初期データ（図10）は、会社の支店がある各地域の計画および実際の指標に関する情報を含むトランザクションの形式で表示されます。計画数値は月レベルで、実際数値は個々の日レベルで詳細に示されます。計画された指標は1年先に作成され、実際の指標は過去数か月に作成されます。レポートは毎月更新されるため、ピボットテーブルのデータソースのサイズが大きくなり、新しいデータが下部に追加されると、このプロセスは大幅に簡素化されます。古いバージョンでは Excelの作成同様のデータソースは、OFFSET関数を使用した名前付きダイナミックレンジを使用して実装されました（詳細を参照）。 Excel 2013で作業している場合は、データセルの1つを選択し、Ctrl + Tキーの組み合わせ（テーブルの作成）を押すだけです。これにより、新しい行と列が追加されると自動的に展開される名前付きデータセットが作成されます。

それでは、ピボットテーブルを作成しましょう。 GET PIVOTTABLE DATA関数は十分に強力ですが、実際のピボットテーブルに表示される値のみを返すことができます。この関数は、ピボットテーブルにないアイテムを計算するためにキャッシュスキャンを実行できません。

ピボットテーブルを作成します。

チームを選ぶ インサート → ピボットテーブル次にダイアログで ピボットテーブルの作成クリック OK.
ピボットテーブルフィールドリストで、フィールドを選択します日付..。日付のリストがピボットテーブルの左側に表示されます（図11）。
A4などの任意の日付セルを選択します。コンテキストタブ分析コンテキストタブのセットにあります ピボットテーブルの操作、ボタンをクリックします フィールドによるグループ化 （詳細を見る）。ダイアログボックスで グループ化 選択肢一つを選択してください月（図12）。クリック OK..。月の名前は、ピボットテーブルの左側に表示されます（図13）。
フィールドをドラッグします日付ピボットテーブルの列領域に移動します。
フィールドをドラッグします インジケータ ピボットテーブルフィールドリストの[列]領域に移動します。
フィールドを選択領域ピボットテーブルの左側の列に表示されます。
フィールドを選択所得ピボットテーブルの値領域に表示されます。

図： 11.フィールドでグループ化することから始めます日付

この時点で、ピボットテーブルはかなり原始的に見えます（図14）。私はレタリングが本当に好きではありません行名そして列名..。の合計を表示することは実用的ではありません 1月の計画 そして Janファクト D列など。しかし、心配しないでください外観このピボットテーブルは、あなた以外の誰にも表示されないためです。この時点から、新しく作成されたピボットテーブルとなるデータソースであるレポートラッパーを作成します。

レポートシェルの作成。ブックに空白のシートを挿入します。ピボットテーブルツールをしばらく脇に置いて、通常のExcelツールに移りましょう。私たちの仕事は、数式と書式設定を使用して、マネージャーに見せることを恥じない美しいレポートを作成することです。

次のように進めます（図15）。

セルA1に、レポートの名前を入力します- 地域別の計画および実際の指標.
タブに移動します メイン、ボタンをクリックします セルスタイル フォーマットを選択 見出し1.
セルA2に、数式\u003d EON MONTHS（TODAY（）; 0）を入力します。この関数は、当月の最終日を返します。たとえば、2014年8月14日にこれらの行を読むと、セルA2には2014年8月31日の日付が表示されます。
セルA2を選択します。 Ctrl + 1のキーの組み合わせを押して、ダイアログボックスを表示します セル形式..。タブ内数アイテムをクリックします すべてのフォーマット..。次のようにカスタム番号形式を入力します 「今月から」MMMM「計画指標」 （図16）。その結果、計算された日付はテキストのようになります。
セルA5にタイトルを入力します領域.
列Aの残りのセルに地域の見出しを入力します。地域の見出しは、ピボットテーブルに表示されている地域の名前と一致する必要があります。
必要に応じて、部門ごとの合計の列にラベルを追加します。
レポートの下部に行を追加します 会社の合計.
セルB4に、数式\u003d DATE（YEAR（$ A $ 2）; COLUMN（A1）; 1）を入力します。この数式は、2014年1月1日、2104年1月2日など、現在の年の12か月すべての最初の日付を返します。
セルB4を選択します。キーの組み合わせCtrl + 1を押して、ウィンドウを開きます セル形式..。タブ内数セクションで すべてのフォーマット カスタム番号形式を入力します うーん..。この形式では、3文字の月の名前が表示されます。テキストをセルの右側に揃えます。
セルB4の内容を範囲C4：M4にコピーします。月の名前の行がピボットテーブルの上部に表示されます。
セルB5に、数式\u003d IF（MONTH（B4））を入力します。<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово 事実、そして現在と未来のために- 予定.
セルN5にタイトルを追加します結果..。セルO4へ- 結果、O5 - 予定、P5- ％偏差.
部門の合計、会社の合計行、総計列、および分散％列の計算に使用される通常のExcel式を入力します。
1. セルB8に、数式\u003d SUM（B6：B7）を入力し、行内の他のセルにコピーします。
2. セルN6に、数式\u003d SUM（B6：M6）を入力し、それを列の他のセルにコピーします。
3. セルP6に、数式\u003d IFERROR（（N6 / O10）-1; 0）を入力し、列内の他のセルにコピーします。
4. セルB13に、数式\u003d SUM（B10：B12）を入力し、行内の他のセルにコピーします。
5. セルB17に数式\u003d SUM（B15：B16）を入力し、行の他のセルにコピーします。
6. セルB19に、数式\u003d SUM（B6：B18）/ 2を入力し、行の他のセルにコピーします。
見出し4スタイルを列Aのラベルと行4および5の見出しに適用します。
セルB6：O19の範囲については、数値形式＃## 0を選択します。
列Pのセルには、数値形式0.0％を選択します。

これで、図に示すレポートシェルの作成が完了しました。 15.このレポートには、必要なすべてのフォーマットが含まれています。次のセクションでは、GET PIVOTTABLEDATA関数を使用してレポートを完成させる方法を示します。

図： 15.数式を追加する前にラッパーを報告するGET.DATA.PUMP.TABLES

GET PIVOTTABLE DATA関数を使用して、レポートシェルにデータを入力します。これからは、GET PANEL.TABLEDATA関数を使用することのすべての利点を体験できるようになります。この機能を有効にするチェックボックスをオフにした場合は、対応する設定に戻り、チェックボックスを返します（図6または7の説明を参照）。

レポートシェルのセルB6を選択します。このセルは、北東地域と1月の実際の数値に対応しています。

\u003d（等号）と入力して、数式の入力を開始します。
ピボットテーブルからシートに移動し、北東地域に対応するセルと1月の実際の数値-C12をクリックします（図17）。
キーを押す入る数式の入力を終了して、レポートシェルに戻ります。その結果、ExcelはセルB6に関数GET PIVOTTABLEDATAを追加します。セルには$ 277,435の値が表示されます。

この数値は、後で編集する数式の結果と比較するときに必要になるため、覚えておいてください。プログラムによって生成される式の形式は次のとおりです。\u003d GET.DATA.PERSONAL.TABLE（ "Income"; '図11-14'！$ A $ 3; "Region"; "North-East"; "Date" ; 1; "インジケーター"; "ファクト"）。これまでにGETPIVOTTABLE DATA関数を無視した場合は、詳しく見てみましょう。図では 18この式は、ヒントとともに編集モードで表示されます。

関数の引数：

Data_field。ピボットテーブルの値領域のフィールド。注意：この場合、フィールドが使用されます所得、だがしかし 収入フィールドの金額.
ピボットテーブル。このパラメータを使用すると、Microsoftは「どのピボットテーブルを使用しますか？」と尋ねます。ピボットテーブルセルの1つを指定するだけで十分です。エントリ '図。 11-14 '！$ A $ 3は、データが入力されるピボットテーブルの最初のセルを指します。この場合、ピボットテーブルに関連する任意のセルを指定できるため、引数は変更しないでください。セルアドレス$ A $ 3はすべての点で適しています。
フィールド1; 要素1.自動生成された式で、フィールドの名前が選択されます領域、およびフィールド値として- 北東..。これは、GETDATA.PUMP.TABLE関数を使用するときに発生する問題の理由が存在する場所です。自動選択された値はハードコーディングされているため、コピーできません。したがって、レポート領域全体で数式をコピーする場合は、手動で数式を変更する必要があります。 Northeastを$ A6の形式のセル参照に置き換えます。列Aの前にドル記号を配置することにより、数式を列のセルにコピーするときに参照の行部分を変更できるかどうかを判断します。
フィールド2; 項目2。この引数のペアはフィールドを定義します日付値は1です。元のピボットテーブルが月ごとにグループ化されている場合、月フィールドは元のフィールド名を保持します日付..。月の数値は1で、1月に対応します。数十または数百のレポートセルで指定される巨大な数式を作成するときに、このような値を使用することはほとんどお勧めできません。フィールド値を計算する式を使用することをお勧めします日付、セルB4の数式のように。この場合の1の代わりに、式MONTH（$ 4）を使用できます。 4の前のドル記号は、数式がフィールドに値を割り当てることができることを示します日付数式が行のセルにコピーされるため、他の月に基づきます。
フィールド3; 要素3。この場合、フィールド名は自動的に割り当てられます。 インジケータ およびフィールド値事実..。これらの値は1月には正しいですが、それ以降の月については、フィールド値を計画に変更する必要があります。フィールドのハードコードされた値を変更します事実リンクB $ 5へ。
フィールド4; 要素4。これらの引数は使用されません。フィールドは終わりました。

新しい式を図に示します。 19. 1分で、単一の値で機能するように設計されたハードコードされた数式の代わりに、データセット内のすべてのセルにコピーできる柔軟な数式が作成されました。キーを押す入る数式を編集する前と同じ結果が得られます。編集された数式は次の形式を取ります：\u003d GET.PUMP.TABLE DATA（ "Income"; '図11-14'！$ A $ 3; "Region"; $ A6; "Date"; MONTH（B $ 4） ; "インジケーター"; B $ 5）

図： 19.編集が完了すると、数式GET.DATA.PERSONAL.TABLEは、範囲のすべてのセルにコピーするのに適しています。

結果が計算される列B：Mの空白セルに数式をコピーします。レポートに実数が含まれているので、列幅を最終的に調整できます。

次のステップでは、GET.PUMP.TABLE式を設定して、最終的なターゲットを計算します。数式をセルO6にコピーするだけの場合、エラーメッセージ#REF！このエラーの理由は、結果セルO4では月の名前ではありません。 GET PIVOTTABLE DATA関数が正しく動作するようにするには、必要な値がピボットテーブルに含まれている必要があります。しかし、元のピボットテーブルでは、フィールド インジケータ 列領域の2番目のフィールドであるデータ列です 計画概要 実際には存在しません。フィールドを移動する インジケータ 列領域の最初になるようにします（図20）。

図： 20.列が表示されるように、列領域のフィールドのレイアウトを調整します 概略計画

図と比較してください。 14.そこで、COLUMNのエリアでは、フィールドが最初でした日付、これにより、最初は列が日付ごとにグループ化され、毎月内に計画/事実ごとにグループ化されました。これで、最初は[インジケーター]フィールドになり、要約では、列が最初になります予定、内部を月、次にすべての列でソート事実.

レポートシェルのシートに戻り、セルO6に立って、\u003d（等号）と入力し、北東地域の計画合計に対応するピボットテーブルのシートのセルN12を参照します。クリック入る..。式\u003d GET.DATA.PERSONAL.TABLES（ "Income"; '図11-14'！$ A $ 3; "Region"; "North-East"; "Indicator"; "Plan"）を取得します。編集する：\u003d GET。 DATA.SUMMARY.TABLES（ "Income"; ’図11-14'！$ A $ 3; "Region"; $ A6; "Indicator"; O $ 5）。この数式を列Oの他のセルにコピーします（図21）。ピボットテーブルレポートのさまざまな領域を移動しても、シェルは正しく機能することに注意してください。もちろん、いくつかのピボットフィールドを非アクティブにすると、シェルはこれに対処しません...

図： 21.マネージャーに提示できる最終報告書

これで、動的ピボットテーブルの値を使用する適切にフォーマットされたレポートラッパーができました。レポートの最初の作成にはかなりの時間がかかりましたが、更新には数分しかかかりませんでした。

レポートを更新します。今後数か月のデータでレポートを更新するには、次の手順に従います。

元のデータセットの下に実際の図を挿入します。ソースデータはテーブル形式であるため、テーブル形式は新しいデータ行に自動的に伝播されます。また、元のピボットテーブルの定義を拡張します（Excelファイルでは、すでに1年間の実際の数値を追加しています）。
ピボットテーブルに移動します。右クリックして選択します更新..。ピボットテーブルの外観は変わりますが、問題ありません。
レポートシェルに移動します。原則として、レポートの更新はすべて完了していますが、結果をテストしても問題はありません。たとえば、セルA2の数式を次のように変更します。\u003d EON MONTHS（TODAY（） +31 ; 0）、そして何が起こるかを見てください。

毎月新しい実際の売上データを追加することで、フォーマットや数式などの再作成について心配する必要がありません。説明されているレポートの更新プロセスは非常に単純なので、月次レポートの作成中に発生した問題を永遠に忘れてしまいます。唯一の問題は、会社が再編成された場合に発生する可能性があり、その結果、新しい地域がピボットテーブルに表示される可能性があります。数式が正しく機能することを確認するには、レポートの総計がピボットテーブルの合計と一致することを確認してください。新しい領域が表示されたら、それをラップされたシートに追加し、適切な数式をドラッグします。

次のように言うとは思いませんでした。「GET.DATA.PUMP.TABLE関数は最大の祝福です。以前は彼女なしでどうやって存在したのですか？」

オリジナルでは、Jelenaの初期データは、2015年7月にのみ次の数式が正しく機能するように配置されていました。このメモに添付されているExcelファイルで、元のデータといくつかの数式を変更して、日付に関係なくすべてが機能するようにしました。添付のExcelファイルを試してみます。残念ながら、式は複雑でなければなりませんでした。

先ほど、通常のリンクではなくピボットテーブルセルを参照すると、GET.DATA.PIVOTTABLE関数が返されるという事実について説明しました（を参照）。興味のある方 なので この不便を克服するために、私は言及された注記を参照することをお勧めします。あなたが疑問に思っているなら、なぜこれがどのように発生するかであり、関数GET DATA.COMPLETE.TABLEの良い面も何ですか。次に、本Jelen、Alexanderの断片を提案します。（第15章）。検討中の手法により、ピボットテーブルのユーザーに頭痛の種となる多くの問題、特に次の問題に対処できます。

ピボットテーブルが更新されると、以前に適用されていた書式は表示されなくなります。数値形式は失われます。列幅の調整結果が消えます。
非対称のピボットテーブルを作成する簡単な方法はありません。唯一のオプションは名前付きセットを使用することですが、この方法はデータモデルピボットテーブルを使用しているユーザーのみが使用でき、通常のピボットテーブルは使用できません。
Excelはテンプレートを記憶できません。ピボットテーブルを何度も再作成する必要がある場合は、再グループ化して、計算フィールドとメンバーを適用し、他の多くの同様のタスクを実行する必要があります。

形式でメモをダウンロードするか、形式で例をダウンロードします

さて、順番に始めましょう。

問題のある関数を放棄する方法GETDATA.PERSONAL TABLES

図に示すピボットテーブルで仮定します。 1、2015年と2014年のデータを比較する必要があります。

図： 1.元のピボットテーブル

見出し「％Height」をセルD3に追加します。
フォーマットをセルC3からセルD3にコピーします。
セルD4に等号を入力します。
セルC4をクリックします。
除算演算を表す/（スラッシュ）記号を入力します。
セルB4をクリックします。
-1を入力し、キーの組み合わせを押します同じセルにとどまる。結果をパーセンテージでフォーマットします。西部地域では収入が43.8％減少したことがわかります（図2）。あまり良い結果ではありません。
最初の数式の入力が終了したら、セルD4を選択します。
セルの右下隅にある小さな正方形をダブルクリックします。この正方形は、数式をコピーしてレポートの列全体に入力するために使用できる入力ハンドルを表します。

図： 3.数式を列のすべてのセルにコピーし終えると、各領域で43.8％の低下が見られます。

Excel 2000の時代には、このようなことは何も起こりませんでした。定期的にGET.DATA.PERSONAL.TABLES関数に遭遇し始めたとき、私はそれが嫌いでした。あるセミナーで誰かが私にそれを大義のためにどのように使うことができるかと尋ねたとき、私は唖然としました。私はそのような質問をしたことがありません！私の意見では、そしてほとんどのExcelユーザーの意見では、GET.DATA.PUMP.TABLE関数は、善の力とは何の関係もない悪の産物でした。幸い、この機能を無効にする方法は2つあります。

セルD4に移動し、\u003d（等号）と入力します。
C4と入力します。
/（除算のスラッシュ）を入力します。
B4と入力します。
-1を入力します。
クリック入る.

これで、正しい結果を得るために下の列セルにコピーできる通常のExcel数式が作成されました（図5）。ご覧のとおり、ピボットテーブル内のデータを参照する、ピボットテーブル外の領域に数式を作成できます。そして、これが可能であると信じていない人は、彼らに彼ら自身で記述された行動を実行させてください。

図： 5.キーボードから\u003d C4 / B4-1と入力するだけで、数式は必要に応じて機能します

GET PIVOTTABLEDATAを使用してピボットテーブルを改善する

ピボットテーブルは人類の偉大な発明です。ピボットテーブルは数回クリックするだけで作成されるため、高度なフィルター、BDSUMM関数、およびデータテーブルを適用する必要がありません。ピボットテーブルを使用すると、大量のデータから1ページのレポートを作成できます。これらの利点は、書式設定が不十分であることや、追加のカスタマイズのためにピボットテーブルを値に変換する必要があることなど、ピボットテーブルのいくつかの欠点を覆い隠します。図では図8は、ピボットテーブルを作成するための一般的なプロセスを示しています。この場合、すべてが初期データから始まります。ピボットテーブルを作成し、考えられるすべての手法を使用してカスタマイズおよび改善します。ピボットテーブルを値に変換して、最終的なフォーマットを行うことがあります。

次のセクションでは、過去数か月の実際の数値と将来の月の目標を表示する動的レポートを作成する方法について説明します。

プリミティブピボットテーブルを作成します。初期データ（図10）は、会社の支店がある各地域の計画および実際の指標に関する情報を含むトランザクションの形式で表示されます。計画数値は月レベルで、実際数値は個々の日レベルで詳細に示されます。計画された指標は1年先に作成され、実際の指標は過去数か月に作成されます。レポートは毎月更新されるため、ピボットテーブルのデータソースのサイズが大きくなり、新しいデータが下部に追加されると、このプロセスは大幅に簡素化されます。古いバージョンのExcelでは、このようなデータソースは、OFFSET関数を使用した名前付きダイナミックレンジを使用して作成されていました（詳細を参照）。 Excel 2013で作業している場合は、データセルの1つを選択し、Ctrl + Tキーの組み合わせを押します（テーブルを作成します）。これにより、新しい行と列が追加されると自動的に展開される名前付きデータセットが作成されます。

ピボットテーブルを作成します。

チームを選ぶ インサート → ピボットテーブル次にダイアログで ピボットテーブルの作成クリック OK.
ピボットテーブルフィールドリストで、フィールドを選択します日付..。日付のリストがピボットテーブルの左側に表示されます（図11）。
A4などの任意の日付セルを選択します。コンテキストタブ分析コンテキストタブのセットにあります ピボットテーブルの操作、ボタンをクリックします フィールドによるグループ化 （詳細を見る）。ダイアログボックスで グループ化 選択肢一つを選択してください月（図12）。クリック OK..。月の名前は、ピボットテーブルの左側に表示されます（図13）。
フィールドをドラッグします日付ピボットテーブルの列領域に移動します。
フィールドをドラッグします インジケータ ピボットテーブルフィールドリストの[列]領域に移動します。
フィールドを選択領域ピボットテーブルの左側の列に表示されます。
フィールドを選択所得ピボットテーブルの値領域に表示されます。

図： 11.フィールドでグループ化することから始めます日付

この時点で、ピボットテーブルはかなり原始的に見えます（図14）。私はレタリングが本当に好きではありません行名そして列名..。の合計を表示することは実用的ではありません 1月の計画 そして Janファクト D列など。ただし、このピボットテーブルの外観について心配する必要はありません。あなた以外の誰もそれを見ることができないからです。この時点から、新しく作成されたピボットテーブルとなるデータソースであるレポートシェルを作成します。

次のように進めます（図15）。

セルA1に、レポートの名前を入力します- 地域別の計画および実際の指標.
タブに移動します メイン、ボタンをクリックします セルスタイル フォーマットを選択 見出し1.
セルA2に、数式\u003d EON MONTHS（TODAY（）; 0）を入力します。この関数は、当月の最終日を返します。たとえば、2014年8月14日にこれらの行を読むと、セルA2には2014年8月31日の日付が表示されます。
セルA2を選択します。 Ctrl + 1のキーの組み合わせを押して、ダイアログボックスを表示します セル形式..。タブ内数アイテムをクリックします すべてのフォーマット..。次のようにカスタム番号形式を入力します 「今月から」MMMM「計画指標」 （図16）。その結果、計算された日付はテキストのようになります。
セルA5にタイトルを入力します領域.
列Aの残りのセルに地域の見出しを入力します。地域の見出しは、ピボットテーブルに表示されている地域の名前と一致する必要があります。
必要に応じて、部門ごとの合計の列にラベルを追加します。
レポートの下部に行を追加します 会社の合計.
セルB4に、数式\u003d DATE（YEAR（$ A $ 2）; COLUMN（A1）; 1）を入力します。この数式は、2014年1月1日、2104年1月2日など、現在の年の12か月すべての最初の日付を返します。
セルB4を選択します。キーの組み合わせCtrl + 1を押して、ウィンドウを開きます セル形式..。タブ内数セクションで すべてのフォーマット カスタム番号形式を入力します うーん..。この形式では、3文字の月の名前が表示されます。テキストをセルの右側に揃えます。
セルB4の内容を範囲C4：M4にコピーします。月の名前の行がピボットテーブルの上部に表示されます。
セルB5に、数式\u003d IF（MONTH（B4））を入力します。<МЕСЯЦ($A$2); " Факт " ; " План "). Содержимое ячейки В5 выровняйте по правому краю. Скопируйте это содержимое в диапазон ячеек С5:М5. В результате для прошедших месяцев будет отображаться слово 事実、そして現在と未来のために- 予定.
セルN5にタイトルを追加します結果..。セルO4へ- 結果、O5 - 予定、P5- ％偏差.
部門の合計、会社の合計行、総計列、および分散％列の計算に使用される通常のExcel式を入力します。
1. セルB8に、数式\u003d SUM（B6：B7）を入力し、行内の他のセルにコピーします。
2. セルN6に、数式\u003d SUM（B6：M6）を入力し、それを列の他のセルにコピーします。
3. セルP6に、数式\u003d IFERROR（（N6 / O10）-1; 0）を入力し、列内の他のセルにコピーします。
4. セルB13に、数式\u003d SUM（B10：B12）を入力し、行内の他のセルにコピーします。
5. セルB17に数式\u003d SUM（B15：B16）を入力し、行の他のセルにコピーします。
6. セルB19に、数式\u003d SUM（B6：B18）/ 2を入力し、行の他のセルにコピーします。
見出し4スタイルを列Aのラベルと行4および5の見出しに適用します。
セルB6：O19の範囲については、数値形式＃## 0を選択します。
列Pのセルには、数値形式0.0％を選択します。

図： 15.数式を追加する前にラッパーを報告するGET.DATA.PUMP.TABLES

レポートシェルのセルB6を選択します。このセルは、北東地域と1月の実際の数値に対応しています。

\u003d（等号）と入力して、数式の入力を開始します。
ピボットテーブルからシートに移動し、北東地域に対応するセルと1月の実際の数値-C12をクリックします（図17）。
キーを押す入る数式の入力を終了して、レポートシェルに戻ります。その結果、ExcelはセルB6に関数GET PIVOTTABLEDATAを追加します。セルには$ 277,435の値が表示されます。

関数の引数：

Data_field。ピボットテーブルの値領域のフィールド。注意：この場合、フィールドが使用されます所得、だがしかし 収入フィールドの金額.
ピボットテーブル。このパラメータを使用すると、Microsoftは「どのピボットテーブルを使用しますか？」と尋ねます。ピボットテーブルセルの1つを指定するだけで十分です。エントリ '図。 11-14 '！$ A $ 3は、データが入力されるピボットテーブルの最初のセルを指します。この場合、ピボットテーブルに関連する任意のセルを指定できるため、引数は変更しないでください。セルアドレス$ A $ 3はすべての点で適しています。
フィールド1; 要素1.自動生成された式で、フィールドの名前が選択されます領域、およびフィールド値として- 北東..。これは、GETDATA.PUMP.TABLE関数を使用するときに発生する問題の理由が存在する場所です。自動選択された値はハードコーディングされているため、コピーできません。したがって、レポート領域全体で数式をコピーする場合は、手動で数式を変更する必要があります。 Northeastを$ A6の形式のセル参照に置き換えます。列Aの前にドル記号を配置することにより、数式を列のセルにコピーするときに参照の行部分を変更できるかどうかを判断します。
フィールド2; 項目2。この引数のペアはフィールドを定義します日付値は1です。元のピボットテーブルが月ごとにグループ化されている場合、月フィールドは元のフィールド名を保持します日付..。月の数値は1で、1月に対応します。数十または数百のレポートセルで指定される巨大な数式を作成するときに、このような値を使用することはほとんどお勧めできません。フィールド値を計算する式を使用することをお勧めします日付、セルB4の数式のように。この場合の1の代わりに、式MONTH（$ 4）を使用できます。 4の前のドル記号は、数式がフィールドに値を割り当てることができることを示します日付数式が行のセルにコピーされるため、他の月に基づきます。
フィールド3; 要素3。この場合、フィールド名は自動的に割り当てられます。 インジケータ およびフィールド値事実..。これらの値は1月には正しいですが、それ以降の月については、フィールド値を計画に変更する必要があります。フィールドのハードコードされた値を変更します事実リンクB $ 5へ。
フィールド4; 要素4。これらの引数は使用されません。フィールドは終わりました。

図： 19.編集が完了すると、数式GET.DATA.PERSONAL.TABLEは、範囲のすべてのセルにコピーするのに適しています。

結果が計算される列B：Mの空白セルに数式をコピーします。レポートに実数が含まれているので、列幅を最終的に調整できます。

図： 20.列が表示されるように、列領域のフィールドのレイアウトを調整します 概略計画

図： 21.マネージャーに提示できる最終報告書

レポートを更新します。今後数か月のデータでレポートを更新するには、次の手順に従います。

元のデータセットの下に実際の図を挿入します。ソースデータはテーブル形式であるため、テーブル形式は新しいデータ行に自動的に伝播されます。また、元のピボットテーブルの定義を拡張します（Excelファイルでは、すでに1年間の実際の数値を追加しています）。
ピボットテーブルに移動します。右クリックして選択します更新..。ピボットテーブルの外観は変わりますが、問題ありません。
レポートシェルに移動します。原則として、レポートの更新はすべて完了していますが、結果をテストしても問題はありません。たとえば、セルA2の数式を次のように変更します。\u003d EON MONTHS（TODAY（） +31 ; 0）、そして何が起こるかを見てください。

次のように言うとは思いませんでした。「GET.DATA.PUMP.TABLE関数は最大の祝福です。以前は彼女なしでどうやって存在したのですか？」

ピボットテーブルの場合、GET.DATA.PIVOTTABLE関数は、ピボットテーブルレポートに格納されているデータを返す関数です。

関数にすばやくアクセスするには、セル（\u003d）に等号を入力し、ピボットテーブルで必要なセルを選択する必要があります。 Excelは、GET PIVOTTABLEDATA関数を自動的に生成します。

GetPivotDataの作成を無効にする

GET PIVOTTABLE関数の自動生成をオフにするには、ピボットテーブルで任意のセルを選択し、タブをクリックします。 ピボットテーブルの操作-\u003eオプショングループに ピボットテーブル。タブの横にある下矢印をクリックします オプション。ドロップダウンメニューで、チェックボックスをオフにします 作成するGetPivotData。