行ごとのExcelピボットテーブルの合計。 ''トップ10 ''データの表示。詳細データ表示

ピボットテーブルの厄介な点の1つは、データが合計される総計が常にテーブルの下部にあることです。つまり、数値を確認するためだけにテーブル全体をめくる必要があります。総計を見つけやすい一番上に移動しましょう。

ピボットテーブルは、データを要約して意味のある情報を強調表示するための優れたツールですが、総計を見つけやすい一番上に移動する機能が組み込まれていません。総計を一番上に移動する非常に一般的な方法を説明する前に、まず、ピボットテーブルからデータを取得するように特別に設計されたGETPIVOTDATA関数を使用してこれを行う方法を見てみましょう。

この関数は次のように使用できます：= GETPIVOTDATA（ "Sum of Amount"; $ B $ 5）、ロシア語版のExcel = GET.DATA.PERSONAL.TABLE（ "Sum by field"; $ B $ 5）またはこのように：= GETPIVOTOATA（ "Amount"; $ B $ 5）、ロシア語版のExcel GET.DATA.COMPLISHED.TABLES（ "Amount"; $ In $ 5）。

どちらの関数も、必要なデータを強調表示し、データを上下左右に移動するときに総計を追跡します。セルアドレス$ B $ 5を使用しましたが、ピボットテーブル内でセルを指定すると、常に合計が得られます。

最初の関数はSumof Amountフィールドを使用し、2番目の関数はAmountフィールドを使用します。ピボットテーブルのデータ領域に[金額]フィールドがある場合は、フィールドに[金額]という名前を付ける必要があります。ただし、[データ]領域で[金額]フィールドが複数回使用されている場合は、割り当てた名前またはデフォルトの名前を指定する必要があります（図4.5）。

これらのフィールドを変更するには、それらをダブルクリックする必要があります。ピボットテーブルをまだ完全に理解していない場合、これは混乱を招く可能性があります。幸い、Excel 2002以降では、セルに引数を配置し、マウスを使用して正しい関数構文を適用できるため、プロセスがはるかに簡単になります。任意のセルに=（等号）と入力し、総計を含むセルをクリックします。 Excelは必要な引数を自動的に挿入します。

残念ながら、関数ウィザードを使用するか、最初に= GETPIVOTDATA（））と入力してから総計を含むセルをクリックすると、Excelは別の関数GETPIVOTDATAをこのセルに入れようとして混乱を引き起こします。

おそらく、総計を取得する最も簡単で混乱の少ない方法は、ロシア語版のExcel = MAKC（PivGTCol）で= MAX（PivGTCol）関数を使用することです。ここで、総計を含む列の名前はPivGTColです。

さらに、LARGE関数とSMALL関数を使用して、数値の大きさに基づいてピボットテーブルから数値のコレクションを取得できます。たとえば、次の数式は、ピボットテーブルから2番目に大きい数値を選択します。= LARGE（PivGTCol; 2）、ロシア語版のExcel = LARGE（PivGTCol; 2）。

ピボットテーブルのすぐ上に複数の行を追加し、そこにこれらの数式を配置すると、ピボットテーブルの一番下までスクロールしなくても、このタイプの情報をすぐに確認できます。

ピボットテーブルを操作するための特別なツールバーがあります。 ピボットテーブル 、ピボットテーブルの編集と書式設定に関連する基本的な操作をすばやく実行できます。

ドロップダウンメニュー ピボットテーブル ツールバーには、パネル自体で実行される操作やその他のコマンドが含まれています。たとえば、サブメニューを使用します数式計算フィールドを作成し、計算の順序を設定できます。

チーム オプション ピボットテーブルの名前、表示およびフォーマットオプションを定義したり、テーブルが外部データソース上に構築されている場合は外部データオプションを構成したりできます。

データ抽出。

ピボットテーブルのデータを強調表示するには、2つのモードがあります。テーブルの一部を選択するか、通常の強調表示を使用できます。デフォルトでは、ピボットテーブルのすべての部分に対して選択モードが有効になっています。このモードでは、フィールド見出しをクリックすると、フィールド要素と一緒に選択され、フィールド要素をクリックすると、この要素に対応するデータも選択されます。さらに、ピボットテーブルの一部を強調表示すると、テーブルヘッダーとデータ自体の両方が強調表示されます。ツールバーで適切なツールを選択することにより、ヘッダーのみまたはデータのみを選択できます ピボットテーブル :

コマンドを使用して、ピボットテーブルのパーツを選択する方法を設定することもできます。 選択する ドロップダウンメニュー ピボットテーブル ツールバーにあります。ここでは、コマンドを選択することにより、テーブル全体を選択できます。 テーブル全体 ..。通常の選択モードに切り替える（つまり、パーツではなくテーブルのセルを選択する）には、ボタンを押します。同じメニューで。ピボットテーブルの構造を更新および変更した後、その領域の形式を保持するには、ボタン 選択を有効にする 押す必要があります。

フィールド、フィールドアイテム、および合計は、ピボットテーブルから削除できます。これを行うには、削除するテーブルの部分を選択します（ボタン 選択を有効にする 押す必要があります）そしてコマンドを選択します消去コンテキストメニューから。

元のデータを更新しています。

元のテーブルのデータを変更した場合は、そのデータに基づいてピボットテーブルを更新する必要があります。これを行うには、ボタンをクリックしますツールバー ピボットテーブル またはコマンドを選択します データを更新 ドロップダウンメニューから ピボットテーブル ..。行（または範囲統合の場合は列）を追加した場合も、データが更新されます。中身元の範囲。

行または列が追加されている場合に元の範囲の場合、ボタンをクリックしてピボットテーブルウィザードを呼び出す必要があります ピボットテーブルウィザード ツールバーにあります。ウィザードの2番目のステップに戻ると、元の範囲をオーバーライドできます。

一般および小計。

コマンドを選択する オプション ドロップダウンメニューから ピボットテーブル 、ピボットテーブルに合計が必要かどうかを行および/または列で指定できます。ピボットテーブルにページフィールドがあり、このフィールドの値の一部が非表示になっている場合、ここで、合計にページフィールドの非表示の値に対応するデータが含まれているかどうかを明確にすることもできます。チェックボックスの場合 小計の非表示ページアイテム（非表示の値を含む） 無効にすると、ページフィールドのすべての値に一致するピボットテーブルを表示するときに、非表示の値は総計に含まれません。

もしもピボットテーブル複数の列指向または複数のページ指向のフィールドがある場合、ユーザーはそのようなフィールドごとに小計を定義できます。

フィールドの小計を定義するには、フィールドをダブルクリックするか、アイコンを選択します。 ピボットテーブルフィールド ツールバーにあります。フィールドの小計を選択できます

自動-金額はフィールドの要素ごとに計算されます。自動合計は、外部フィールド（この例ではこのフィールド）に対してのみ選択できます。年);
リストから選択された標準関数を使用して計算されます（外部フィールドと内部フィールドの両方に対して、そのような合計をいくつか選択できます）。
中間結果を完全に放棄します。

小計を定義した場合は、各フィールド項目に小計列（行）が追加されます。合計が複数ある場合は、各項目の追加の列の数に合計の数を掛けます。
データのグループ化。

すべてのフィールド要素ではなく、フィールド内の一部のデータグループの合計が必要になる場合があります（たとえば、フィールド要素は月の名前であり、合計は四半期ごとに合計する必要があります）。この場合、フィールド要素を選択してアイコンをクリックすると、フィールド要素をグループ化できます。 グループ ツールバーにあります。もう1つのフィールドがあり、その要素はグループヘッダーになります。さらに、グループ化されていない各要素は独立したグループ、その名前は要素の名前と同じです。上記の例では、4つの要素を含む1つの列フィールドがあります。 1月, 2月, 行進と 4月..。 3つの要素（ 1月, 2月, 行進）グループ化されています。エレメント 4月グループには含まれていませんでした。その結果、別のフィールドが登場しました月2 2つの要素を含む： グループ1と 4月..。分野月まだ月の名前が含まれています： 1月, 2月, 行進と 4月..。分野月2外部フィールド、つまり内部フィールドをグループ化します月..。新しいフィールドの小計を定義でき、必要な合計はデータグループごとに計算されます。フィールドの要素のグループ化を解除するには、必要なグループを選択してアイコンをクリックするだけです。 グループ化を解除する ツールバーにあります。グループ化されたアイテムが残っていない場合、追加の外側のマージンは消えます。

計算されたフィールドとフィールドメンバー。

通常、テーブル構造は、単一のデータ範囲の場合は元のリストのフィールドヘッダーから、またはオブジェクトから作成されます。 ライン, 桁, 意味, ページ複数の範囲がある場合。多くの場合、ソーステーブルに含まれるデータを分析するために使用できる十分なフィールドがありません。たとえば、ピボットテーブルには次の2つのデータフィールドがあります。番号と和、およびこれら2つのフィールドの商として決定される製品の平均価格を決定する必要があります。この場合、独自に作成する必要があります 計算フィールド -ピボットテーブルの他のフィールドの値に基づいて計算を実行できるようにするユーザー作成の数式を使用するピボットテーブルフィールド。これを行うには、コマンドを順番に選択するだけで十分です数式 , 計算フィールド ドロップダウンメニューから ピボットテーブル ..。リストから選択できるフィールドを使用して、計算フィールドの独自の数式を作成できます。ピボットテーブルウィザードを使用して、ピボットテーブルの目的の領域に新しいフィールドを配置できます。

レポートには、追加のフィールドだけでなく、フィールド要素も必要な場合があります。これは、既存のフィールド要素のデータを何らかの方法で分析するフィールドが必要な場合に発生します。たとえば、行フィールドまたは列フィールドには要素が含まれます番号と和、およびこれら2つの要素の商として決定される製品の平均価格を決定する必要があります。このような場合、計算フィールドを作成するのではなく、 計算項目 -他のピボットテーブルフィールド要素の値に基づいて計算を実行できるようにするユーザー定義の数式を使用するピボットテーブルフィールド要素。計算項目を作成するには、新しいオブジェクトを追加するフィールドまたはフィールド項目を選択し、コマンドを順番に選択する必要があります数式 , 計算項目 ドロップダウンメニューから ピボットテーブル ..。その後、新しい要素の名前を指定し、それを計算する式を設定する必要があります。追加のアイテムを計算する数式は、既存のピボットテーブルフィールドアイテムの名前を使用します。複数のフィールドに同じ名前の要素がある場合は、要素名の前にフィールド名を挿入する必要があります。計算オブジェクトを作成する前に、選択した（または選択したアイテムの）フィールドに新しいアイテムが追加されます。

Excelをデータ処理ツール（アドイン）で補完するため レポートマネージャー と 解決策を見つける ）、ダイアログボックスで アドオン メニュー サービス 適切なラジオボタンを確認して、 わかった （図1）。

選択したアドインが見つからないというプロンプトが表示された場合は、そのアドインがインストールされていない可能性があります。この場合、インストールディスケットからアドインをインストールする必要があります。

図1。ダイアログウィンドウ アドオン。

最終報告書の作成

割り当ての目的： 小計を作成することを学びます。

たとえば、会社がその部門のためにプリンターとスキャナーを購入したデータを含むテーブルの値の分析を実行してみましょう（表1）。この問題は通常の数式を使用することで解決できますが、合計の自動計算機能を使用すると、その実装を大幅に簡素化できます。

表1

製品		名前	価格	番号- の	和
	マトリックス
	マトリックス
	マトリックス
	ジェット	エプソンスタイラス1520
	ジェット	エプソンスタイラス3000
	ジェット	エプソンスタイラス1500
	ジェット	エプソンスタイラス写真700
	レーザ	HP Laser Jet 4000
	レーザ	HP Laser Jet 5000
	レーザ	HP Laser Jet Color 8500
	シート	パラゴンページ630
	シート	簡単なパラゴンページ
	タブレット	パラゴン800IIEP
	タブレット	ScanExpress 6000 SP
	タブレット	パラゴン1200SP
	タブレット	ScanExpress A3 P

合計の自動計算を使用して、購入に費やされた金額を、すべてのプリンターについて個別に、およびすべてのスキャナーについて個別に検出します。このため：

まだソートされていない場合は、最初に製品タイプでテーブルデータをソートします。 2番目の並べ替え基準として、たとえば、次のように設定できます。名前;

テーブルまたは少なくとも1つのテーブルセルを選択します。

メニューに データ チームを選ぶ結果 . ダイアログボックスが開きます小計（図2）。

落ちる 変更するたびに vワークシートで各データが変更された後、中間合計を計算する列見出しを選択します。（あなたの場合、要素を選択する必要があります 製品）。合計を決定するときにデータを合計するために、リストフィールドから手術機能を選択和。このダイアログボックスでは、合計の計算にセルが使用される列も指定する必要があります。私たちの場合、商品の量と購入に費やされた金額の指標を要約するために、フィールドで 合計を追加 行の横にあるチェックボックスをオンにします数量と和.

関数を実行した結果、テーブルに行が追加され、各グループの合計が個別に表示されます（図3）。テーブルに挿入された最後の行には、総計に関する情報が含まれています。

図3。この表は、合計の自動計算機能の使用例です。

ダイアログで選択した各列グループのデータ小計、次の機能を実行できます。

関数	予定
	すべての値を追加し、総計を示します
値の数	グループ内の要素の数を決定します
	グループの算術平均を決定します
最大	グループ内の最大値と列全体の最大値を決定します
	定義最小値各グループおよび列全体
仕事	グループ内のすべての値の積と列全体の値の積を決定します
数字の数	グループ内の数値を含むセルの数と、すべての列グループの数値を含むセルの総数を決定します
偏りのない偏差	データがサンプリングされた場合の母集団の標準偏差の値を決定します
オフセットたわみ	データが母集団を構成する場合、母集団の標準偏差を指定します
偏りのない分散	データがサンプリングされた場合の分散の値を決定します
変位分散	データが母集団を形成する場合、母集団の分散値を指定します

合計を計算するとき、テーブルは構造化されていました-画面を見るとこれを確認できます。構造レベルの助けを借りて、データのより良い視覚的制御を提供することができます。画面に要約データのみを表示するには、構造の第2レベルのボタンをクリックします。その結果、第3レベルのデータ（個々の値）が非表示になります（図4、5）。個々の値をもう一度表示するには、第3レベルのボタンをクリックします

図4。要約データを表示するオプション（第3レベルは非表示）。

図5。要約データを表示するオプション。

入力した小計を削除するには、コマンドを選択するだけです結果メニューに データ ボタンをクリックします すべて削除する .

自分で計算する :

プリンターとスキャナーの平均価格。

プリンターとスキャナーの名前の種類の数。

各タイプの製品の購入に費やされた金額（図6）

図6。各タイプの製品の購入に費やされた金額。

デフォルトでは、合計値を含む行は、個々のデータグループ値を含む行の下に挿入されます。グループデータの前に合計を表示する場合は、このオプションを無効にする必要があります。結果ダイアログのデータの下小計 .

印刷時に、合計を含むデータの各グループを個別のページに表示できます。これを行うには、オプションを設定します ページの終わり ダイアログ内のグループ間小計 .

統合報告書の作成

割り当ての目的： 統合レポートを作成する方法を学びます。

統合機能テーブルのさまざまな領域にあるデータの合計を計算する必要がある場合に使用されます。統合機能を使用すると、自動小計機能の場合と同じように、連続していないセル範囲の値に対して同じ操作を実行できます。統合するセルの範囲は、同じワークシート、異なるシート、および異なるワークブックの両方に配置できます。

前の例を例として使用します。 プリンターデータとスキャナーデータのみを2つのワーカーに分離しますシート、名前を「 Sheet1」から「プリンタ」および「 Sheet2「v」 スキャナー”.

プリンターとスキャナーの購入に関する統合レポートを作成します（データはワークブックのさまざまなシートに配置されます）。このため：

選択する " Sheet3「そしてそれに新しい名前を付けなさい」 報告する」。新しいテーブルの「ヘッダー」を作成します（図8）。

セルを選択 A 7 (“製品」）、そしてメニューで データ チームを選ぶ統合 . 開いた同名のダイアログボックスで、統合するセルの範囲と操作（機能）の種類を指定します（図7）。

図7。ダイアログウィンドウ統合

ボックスに確認してください関数関数です和 ,

ボックスをクリックします リンク 次に、シートタブで「 プリンター」、タイトルなどのプリンタに関する情報を含むセルのブロックを選択し、ボタンをクリックします追加 . スキャナーのデータについても同じ手順に従います。

ボックスにセット 名前として使用 オプション トップラインのキャプション と 左の列の値 名前で統合を設定すると、同じラベルの行の値は、セルの連続していない範囲に配置されている場合でも、合計されます、

クリック わかった , 結果の統合レポートでは、「 タイプ” と " 名前” 非表示にすることができます（図8）

図8。連結報告書

元のセル範囲の値を変更した場合は、統合を繰り返す必要があります。ソーステーブルの構造（セル範囲）が変更されない場合は、統合されたデータを元のデータにリンクすることで、この手順の継続的な繰り返しを回避できます。この場合、統合されたセル範囲には、元のデータへの外部参照を持つセルが含まれます。元のセル範囲のデータが変更されると、統合されたデータも自動的に変更されます。統合データと元のデータ間のリンクを確立するには、オプションを設定します ソースデータへのリンクを作成する ダイアログボックスで統合（図7） .

この場合、ボタンを押した後 わかった元のデータと統合結果の間に動的なドロップが設定され、データが自動的に更新されるようになります。リンクの作成による統合には別の利点があります。この場合、データは構造化機能を使用して統合され、構造の2番目のレベルは、統合された値が計算される個々の値を示します。

元のデータが同じブック内にある場合、更新は自動的に行われます。ただし、他のブックにある元のデータの場合、コマンドを使用してデータの更新を強制する必要があります接続メニュー編集 .

ピボットテーブルとピボットグラフの作成

ピボットテーブルは、大量のデータを分析するために設計されています。彼らの助けを借りて、分析されたテーブルのデータは、それらの間の依存関係を最もよく反映する形で選択的に提示することができます。

ピボットテーブルは、Excelに保存されたデータを分析するために使用されます。つまり、作業するには、図9の次のテーブルからワークシートにコピーすることを提案するデータが必要です。

図9。ピボットテーブルのデータ。

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

チームを選択する ピボットテーブル メニュー データ またはボタンをクリックします ピボットテーブル ピボットテーブルとグラフウィザードを起動するためのツールバー。画面にダイアログボックスが表示されます。 ピボットテーブルウィザード （図10） .

米。 10ピボットテーブルウィザード。

グループで 見つかったデータに基づいてテーブルを作成します ：使用可能なデータソースに対応するラジオボタンを選択します。この例では、データはMicrosoftExcelリストに表示されます。

グループで 生成されたレポートのタイプ： ピボットテーブルをインストールします。ボタンをクリックします さらに遠く -ウィンドウが表示されます：

フィールドで範囲：(図11）元のデータを含む範囲を指定します。これを行うには、2つの方法があります。キーボードを使用してセル範囲のアドレスまたは名前を入力するか、マウスを使用してワークシート内のセル範囲を選択します。

作成したピボットテーブルの場所を選択します（図12）。同じシート上の場所を選択した場合は、場所の範囲または開始セルのみを指定します。

ボタンをクリックします レイアウト…。 ピボットテーブルを作成するためのダイアログボックスが表示されます（図13）。

レイアウトをマークアップするときは、その領域のフィールドボタンをマウスでドラッグします。

ボタンをドラッグします名前地域へ ライン , ボタン メーカー地域へ ページ 、ボタン クォーター地域へ桁、ボタン ひっくり返すと量地域へ データ （図14）。

ボタンをクリックします わかった 、その後準備 ..。ピボットテーブルを受け取ります（図15）。

図15要約表。

ピボットテーブルオプションの構成。

作成済みのピボットテーブルの任意のセルを右クリックして、アイテムを選択します テーブルパラメータ。 画面にウィンドウが表示されます ピボットテーブルオプション （図16）。

図16。ピボットテーブルパラメータウィンドウ。

このダイアログボックスのボックス名前ピボットテーブルの名前が設定されます。チェックボックス 合計列 と 行ごとの合計 Oピボットテーブルが印刷されることを意味します総計と フィールド合計..。チェックボックス オートフォーマット デフォルトがピボットテーブルに適用されることを意味します オートフォーマット..。チェックボックス 非表示の値を含める 合計に非表示のセルの値が含まれることを意味します。セルをすべての外側の行と列のキャプションと組み合わせるにはチェックボックスをオンにする必要があります ヘッダーセルをマージする ..。ピボットテーブルが更新されたとき、またはレイアウトが変更されたときに、ピボットテーブルのセル形式が変更されないようにするには、チェックボックスをオンにします。 フォーマットを続ける ..。分野で エラー表示の場合 , 空のセルの場合は、 エラーやデータの欠落が発生した場合にセルに表示される値を入力します。これらのフィールドは、対応するチェックボックスが存在する場合にのみ使用できます。チェックボックスをオンにします テーブルと一緒にデータを保存する 作成元の元のデータをコピーする与えられたテーブル常にそれらにアクセスできます。チェックボックスをオンにします 展開が許可されました ピボットテーブルのデータ領域をダブルクリックして詳細データを表示できるようにします。チェックボックスをオンにします更新 オープニング時 ピボットテーブルを含むExcelブックを開いたときに、ピボットテーブルを更新します。ピボットテーブルを作成するときに外部ソースを使用する場合は、グループに必要なパラメータを設定します 外部データ .

ボタンをクリックしてウィンドウを閉じます わかった .

データのフィルタリング。

クリック わかった 、追加のシートがワークブックに表示されています。 Excelは、データの各グループを個別のワークシートに配置します。

詳細データの表示。

詳細を表示するフィールド要素をダブルクリックします。画面にウィンドウが表示されます 詳細を表示 （図18）。

図18。詳細データを表示するためのウィンドウ。

表示したいパーツを含むフィールドが表示されたリストから選択し、ボタンをクリックします わかった ..。対応する行または列がピボットテーブルに挿入されます。

図19。詳細なピボットテーブルデータ。

詳細データの表示を停止するには、データフィールド項目をもう一度ダブルクリックします。

データの並べ替え。

ピボットテーブルのフィールドは、名前に従って昇順で自動的に並べ替えられます。並べ替え順序は後で変更できます。

フィールドでデータを並べ替える総計..。これを行うには、並べ替えるデータを選択し、メニューから選択します データ エレメント 並べ替え ..。画面にダイアログボックスが表示されます。 並べ替え ..。グループ内の範囲を設定します 並び替え 、およびグループ内選別スイッチを設定する意味 ..。ボタンをクリックします わかった （図20）。

図20データ並べ替えウィンドウ。

テーブルを更新（変更）するときにデータフィールドを名前で自動的に並べ替えるには、並べ替えるフィールドのボタンをダブルクリックして、表示されるウィンドウを表示します。ボタンをクリックします さらに遠く ..。画面にウィンドウが表示されます（図21）。グループで 並べ替えオプション ラジオボタンを昇順または降順で設定します。リストで、フィールドを使用して、要素が並べ替えられるフィールドを選択します。窓を閉めて。

図21。追加のパラメータウィンドウ。

ダイアログボックスで、ピボットテーブルアイテムをデータ領域のセル値で手動で並べ替える 高度なピボットテーブルフィールドオプション グループで 並べ替えオプション スイッチを設定する 手動で 、次にウィンドウを閉じます。並べ替えるフィールドを選択し、コマンドを選択します 並べ替え メニュー データ . フィールドに表示されるウィンドウで選別の上ソートするデータ域のフィールドを指定します。並べ替えを開始します。ピボットテーブルに、日曜日、月曜日、火曜日など、通常はアルファベット順ではなく時系列で並べ替えられるアイテムが含まれている場合。 Excelは自動的にカスタムの並べ替え順序を使用します。 Excelには複数の並べ替えリストが含まれており、タブを使用しています リスト 窓 パラメーター （エレメント パラメーター メニュー サービス ) 独自のソートリストを定義できます。

''トップ10 ''データの表示。

作成されたピボットテーブルでは、指定された数のアイテムを最大値または最小値でのみ反映できます。

アイテムが選択的に表示されるデータフィールドをダブルクリックします。

表示されるウィンドウで ピボットテーブルフィールドの計算 ボタンをクリックします さらに遠く ..。画面にウィンドウが表示されます 高度なピボットテーブルフィールドオプション .

グループで 表示オプション スイッチを設定する自動（図21）。フィールドで画面表示するアイテムの数とリストを指定します画面目的の並べ替えオプションを選択します。最大また 一番小さい ..。リスト内 フィールドを使用するアイテムが表示されるデータフィールドを選択します。ボタンをクリックします わかった ..。窓を閉めて。