Microsoft Excelでピボットテーブルを操作する

ピボットテーブルは、Microsoft Excelの最も強力な機能の1つです。数回クリックするだけで大量のデータを分析および要約できます。この記事では、ピボットテーブルを調べ、それらが何であるかを理解し、それらを作成およびカスタマイズする方法を学びます。.

注：この記事はExcel 2010（Beta）を使用して書かれています。ピボットテーブルの概念は、長年にわたってほとんど変わっていませんが、それを作成する方法は、ほとんどすべてのExcelの反復で変わりました。 2010ではないバージョンのExcelを使用している場合は、この記事に記載されているものとは異なる画面が表示されます。.

ちょっとした歴史

スプレッドシートプログラムの初期の頃、ロータス1-2-3はルーストを支配しました。その優位性は非常に完全だったので、MicrosoftがLotusと競争するために彼ら自身のスプレッドシートソフトウェア（Excel）を開発することを煩わすのは時間の無駄だと人々は考えた。 2010年までの早送りと、スプレッドシート市場におけるExcelの優位性は、これまでのLotusの優位性を上回りますが、Lotus 1-2-3を実行しているユーザーの数はゼロに近づいています。どうしてそうなった？そのような劇的な運命の逆転を引き起こしたもの?

業界アナリストは2つの要因にそれを置きます：第一に、ロータスは「ウィンドウズ」と呼ばれるこの派手な新しいGUIプラットフォームが決して離陸することがない流行であると決めました。彼らは、Windows版のLotus 1-2-3を（とにかく数年間）作成することを断り、自分たちのDOS版のソフトウェアが誰にでも必要なものであると予測した。マイクロソフトは、当然のことながら、Windows専用のExcelを開発しました。次に、Microsoftは、Lotusが1-2-3で提供していなかったExcel用の機能を開発しました。 ピボットテーブル. Excel専用のPivotTables機能は非常に驚くほど便利であると考えられていたので、人々はそれを持っていないプログラム（1-2-3）に固執するよりもむしろ全く新しいソフトウェアパッケージ（Excel）を学ぶことを望んだ。この1つの機能は、Windowsの成功の誤判断とともに、Lotus 1-2-3の致命的な失敗、およびMicrosoft Excelの成功の始まりでした。.

ピボットテーブルについて

ピボットテーブルとは?

簡単に言うと、ピボットテーブルとは、データを簡単に分析できるように作成されたデータの要約です。手動で作成された概要とは異なり、Excelのピボットテーブルは対話型です。いったん作成したら、それがあなたが望んでいたあなたのデータへの正確な洞察を提供しないなら、あなたはそれを簡単に変えることができます。数回クリックするだけで、要約を「回転」させることができます。列見出しが行見出しになるように回転させることができます。できることは他にもたくさんあります。ピボットテーブルのすべての機能について説明するのではなく、単にそれらの機能を説明します。

ピボットテーブルを使用して分析したデータは、単なるものではありません。 どれか データ - それはする必要があります生データ、以前は未処理（要約なし） - 通常は何らかの種類のリスト。この例としては、過去6か月間の会社での販売取引のリストがあります。.

以下に示すデータを調べてください。

これは ではない 生データ。実際、それはすでにある種の要約です。セルB3では、30,000ドルが見られます。これは、明らかに1月のJames Cookの売上高の合計です。それで、生データはどこにありますか？どのようにして3万ドルという数字に到達したのでしょうか。この数値が生成された元の販売取引の一覧はどこにありますか？どこかで、誰かが過去6ヶ月間のすべての販売取引を上記の要約にまとめるのに苦労したに違いないことは明らかです。これはどのくらいかかりましたか？一時間？十?

おそらく、そうです。ご覧のとおり、上のスプレッドシートは実際には ではない ピボットテーブルそれは他の場所に保存された生データから手動で作成されました、そしてそれは確かにコンパイルするのに数時間かかりました。しかし、それはまさにそのような要約です。 できた ピボットテーブルを使用して作成する場合は、数秒で完了します。どうやって調べましょう…

販売取引の元のリストを追跡すると、次のようになります。

Excelのピボットテーブル機能を使用すると、マウスを数回クリックするだけで、数秒で上記のような月間売上サマリーを作成できることをご存知でしょう。私たちはこれを行うことができます - そしてもっとたくさん!

ピボットテーブルを作成する方法

まず、Excelのワークシートに生データがあることを確認します。財務取引のリストは一般的ですが、従業員の連絡先詳細、CDコレクション、または会社の自動車の燃費に関するリストなど、さまざまなもののリストにすることができます。.

それでExcelを起動します…そしてそのようなリストをロードします…

Excelでリストを開いたら、ピボットテーブルの作成を始める準備が整いました。.

リスト内の任意の1つのセルをクリックしてください。

それから、 インサート タブをクリックします。 ピボットテーブル アイコン：

の ピボットテーブルを作成する ボックスが表示され、2つの質問が表示されます。新しいピボットテーブルの基になるデータとその作成先は？（上記の手順で）リスト内のセルを既にクリックしているので、そのセルを囲むリスト全体が既に選択されています（$ A $ 1：$ G $ 88 に 支払い この例ではシート）他のワークシートの他の領域、またはAccessデータベーステーブル、MS-SQL Serverデータベーステーブルなどの外部データソースのリストを選択することもできます。また、新しいピボットテーブルを新しいピボットテーブルに作成するかどうかを選択する必要があります。 新しい ワークシート、または 既存の 1。この例では、 新しい 1：

新しいワークシートが作成され、そのワークシートに空白のピボットテーブルが作成されます。

別のボックスも表示されます。 ピボットテーブルフィールドリスト. このフィールドリストは、ピボットテーブル内のセルをクリックするたびに表示されます（上記）。

ボックスの上部にあるフィールドのリストは、実際には元の生データワークシートからの列見出しのコレクションです。画面の下部にある4つの空白のボックスを使用して、ピボットテーブルで生データを要約する方法を選択できます。これまでのところ、これらのボックスには何もないので、ピボットテーブルは空白です。上のリストからフィールドを下にドラッグして、下のボックスにドロップするだけです。その後、ピボットテーブルが自動的に作成されて私たちの指示に一致します。誤解した場合は、フィールドを元の場所にドラッグしたりドラッグしたりするだけです。 新しい それらを置き換えるために下のフィールド.

の値ボックスは間違いなく4つの中で最も重要です。このボックスにドラッグされたフィールドは、何らかの方法で（合計、平均、最大値、最小値などを見つけることによって）要約する必要があるデータを表します。ほとんどの場合数値データ。サンプルデータのこのボックスに最適な候補は、「金額」フィールド/列です。そのフィールドをにドラッグしましょう。値ボックス：

（a）フィールドの一覧の[Amount]フィールドにチェックマークが付けられ、[Sum of Amount]が追加されています。値金額列が合計されたことを示すボックス.

ピボットテーブル自体を調べると、生データワークシートからすべての「金額」値の合計が実際に見つかります。

最初のピボットテーブルを作成しました。便利ですが、特に印象的ではありません。それより私たちのデータについてもう少し多くの洞察が必要である可能性があります.

サンプルデータを参照して、この合計を分割するために使用できると思われる1つ以上の列見出しを識別する必要があります。たとえば、データがある場合は、データの概要を表示したいと思うかもしれません。 行見出し 私達の会社の異なった販売員のそれぞれのための、そしてそれぞれのための合計。これを達成するために、私たちがする必要があるのは「Salesperson」フィールドをにドラッグすることだけです。 行ラベル ボックス：

今, 最後に、物事は面白くなり始めます！私たちのピボットテーブルは形を取り始めます… .

数回クリックするだけで、手動で実行するのに長い時間がかかるテーブルを作成しました。.

それでは、他に何ができるでしょうか。ある意味で、ピボットテーブルは完成しています。私達は私達のソースデータの役に立つ要約を作成しました。重要なことはすでに学ばれています！この記事の残りの部分では、より複雑なピボットテーブルを作成する方法と、それらのピボットテーブルをカスタマイズする方法について説明します。.

まず、私たちは作成することができます二-寸法表列の見出しとして「支払い方法」を使用してそれをしましょう。「支払い方法」の見出しをにドラッグします。 列ラベル ボックス：

これは次のようになります。

入手し始めます 非常に クール!

それを作りましょう三-寸法表そのようなテーブルはどのように見えるでしょうか。それでは、…

「パッケージ」列/見出しを レポートフィルタ ボックス：

それが終わるところに注意してください… .

これにより、どの「休日パッケージ」を購入したかに基づいてレポートをフィルタリングできます。たとえば、販売員と支払い方法の内訳を確認できます。 すべて または、数回クリックするだけで、「Sunseekers」パッケージと同じ内訳を表示するように変更できます。

それで、あなたがそれについて正しい方法を考えるならば、私たちのピボットテーブルは今三次元です。カスタマイズし続けましょう…

それが分かったら、私たちはただ見たいだけだという 小切手とクレジットカード 取引（現金取引なし）を選択した場合は、列見出しから[現金]項目の選択を解除できます。の横にあるドロップダウン矢印をクリックします。 列ラベル, そして「現金」のチェックを外します：

その様子を見てみましょう…ご覧のとおり、「現金」は消えています.

フォーマット

これは明らかに非常に強力なシステムですが、これまでのところ結果は非常に明白で退屈に見えます。まず最初に、私たちが合計している数字はドルの金額のようには見えません - 単なる古い数字です。それを修正しましょう.

そのような状況で慣れ親しんだことをして、テーブル全体（またはワークシート全体）を選択し、ツールバーの標準の数値フォーマットボタンを使用してフォーマットを完成させるのが誘惑になるかもしれません。そのアプローチの問題点は、将来ピボットテーブルの構造を変更すると（99％の確率で）、これらの数値フォーマットが失われることです。それらを（半）永久的なものにする方法が必要です.

まず、「金額の合計」エントリを値ボックスをクリックし、それをクリックしてください。メニューが表示されます。選択します 値フィールド設定… メニューから：

の 値フィールド設定 ボックスが表示されます.

クリック 数値フォーマット ボタン、そして標準 セルの書式設定ボックス 表示されます：

から カテゴリー リスト、選択（発言）経理, 小数点以下の桁数を0にドロップします。クリック OK ピボットテーブルに戻るために数回…

ご覧のとおり、数値はドル金額として正しくフォーマットされています.

フォーマットの話題にしている間に、ピボットテーブル全体をフォーマットしましょう。これを行うにはいくつかの方法があります。簡単なものを使ってみましょう…

クリック ピボットテーブルツール/デザイン タブ：

次に、の右下にある矢印をドロップします ピボットテーブルのスタイル 組み込みスタイルの膨大なコレクションを見るためのリスト：

アピールするものを1つ選択して、ピボットテーブルの結果を見てください。

別のオプション

私たちはデートを扱うこともできます。現在、通常、トランザクションリストには、最初に開始したものなど、たくさんの日付があります。しかしExcelには日、週、月、年などでデータ項目をグループ化するオプションがあります。これがどのように行われるか見てみましょう。.

まず、[支払い方法]列を 列ラベル ボックスをクリックし（フィールドリストまでドラッグして戻す）、[予約日]列に置き換えます。

ご覧のとおり、これによりピボットテーブルは即座に役に立たなくなり、トランザクションが発生した日付ごとに1列ずつ表示されます - 非常に広いテーブル!

これを修正するには、日付を右クリックしてを選択します。 グループ… コンテキストメニューから：

グループ化ボックスが表示されます。選択します月そしてOKをクリックします。

ほら！ A ずっと より有用な表：

（ちなみに、この表は、この記事の冒頭に示したものとほぼ同じです。手動で作成された元の売上の要約です。）

もう1つ気をつけなければならないことは、複数の行見出し（または列見出し）を設定できることです。

こんな感じ… .

列見出し（あるいはレポートフィルタ）でも同様のことができます。.

物事を単純にして、プロットする方法を見ましょう 平均した 合計値ではなく値.

まず、「金額の合計」をクリックして、 値フィールド設定… 表示されるコンテキストメニューから：

の中に によって値フィールドを要約する のリスト 値フィールド設定 ボックス、選択平均：

ここにいる間に、 カスタム名, 「平均額」からもう少し簡潔なものに。「平均」のように入力します。

クリック OK, そしてそれがどのように見えるかを見てください。すべての値が合計から平均に変わり、表のタイトル（左上のセル）が「Avg」に変わります。

私たちが好きなら、私たちは同じピボットテーブルに合計、平均、そしてカウント（カウント=売上げの数）を持つことさえできます。!

空のピボットテーブルから始めて、そのようなものを適切に配置する手順は次のとおりです。

「営業担当者」を 列ラベル
「金額」欄を下にドラッグします。値ボックス3回
最初の「金額」フィールドで、カスタム名を「合計」に変更し、数値フォーマットを経理（小数点以下第0位）
2番目の「金額」フィールドのカスタム名を「平均」に、関数を平均そしてそれは数のフォーマットです経理（小数点以下第0位）
3番目の「金額」フィールドの名前を「件数」に、関数を カウント
自動的に作成されたをドラッグからのフィールド 列ラベル に 行ラベル

これが最終的に得られるものです。

同じピボットテーブルの合計、平均、カウント!

結論

Microsoft Excelによって作成されたピボットテーブルには、もっと多くの機能やオプションがあります。ピボットテーブルの可能性を完全にカバーするには、小さな本（または大きなWebサイト）が必要です。勇敢な、そして/または、こっけいな読者は、ピボットテーブルをさらに非常に簡単に探索することができます。 2つのリボンタブもあります。 ピボットテーブルツール/オプション そして設計. あなたがミスを犯しても問題ありません - ピボットテーブルを削除してからやり直すのは簡単です - Lotus 1-2-3の昔のDOSユーザーは、決して持っていなかった可能性があります.

Office 2007で作業している場合は、Excel 2007でピボットテーブルを作成する方法についての記事を参照してください。.

ピボットテーブルのスキルを習得するためにダウンロードできるExcelブックを用意しました。 97以降のすべてのバージョンのExcelで動作するはずです。.

練習用Excelワークブックをダウンロードする