Microsoft Excel でのピボットテーブルの操作

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

注: この記事は Excel 2010 (ベータ版) を使用して書かれています。ピボットテーブルの概念は何年にもわたってほとんど変わっていませんが、ピボットテーブルの作成方法は Excel を繰り返すたびにほぼ変化しています。 2010 以外のバージョンの Excel を使用している場合は、この記事に表示されている画面とは異なる画面が表示されることが予想されます。

ちょっとした歴史

スプレッドシートプログラムの初期の頃は、Lotus 1-2-3 が主流でした。その優位性はあまりに完全だったので、人々はマイクロソフトがロータスと競争するためにわざわざ独自の表計算ソフトウェア (Excel) を開発するのは時間の無駄だと考えました。 2010 年に遡ると、スプレッドシート市場における Excel の支配力は、Lotus のこれまで以上に大きくなっていますが、Lotus 1-2-3 を依然として実行しているユーザーの数はゼロに近づいています。どうしてそうなった？何がこのような劇的な運命の逆転を引き起こしたのでしょうか？

業界アナリストは、原因を 2 つ挙げています。まず、ロータスは、「Windows」と呼ばれるこの派手な新しい GUI プラットフォームは一時的な流行であり、決して普及しないと判断しました。彼らは、Windows バージョンの Lotus 1-2-3 の作成を (とにかく数年間は) 拒否し、DOS バージョンのソフトウェアさえあれば誰でも必要になると予測していました。 Microsoft は当然ながら Windows 専用に Excel を開発しました。次に、Microsoft は、Lotus が 1-2-3 で提供しなかった Excel 用の機能、つまりピボットテーブルを開発しました。 Excel 専用のピボットテーブル機能は驚くほど便利であると考えられていたため、人々はそれを持たないプログラム (1-2-3) に固執するよりも、新しいソフトウェアパッケージ (Excel) 全体を学習することに積極的になりました。この 1 つの機能は、Windows の成功に対する誤った判断とともに、Lotus 1-2-3 の終焉を告げるものであり、Microsoft Excel の成功の始まりでした。

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

では、ピボットテーブルとは正確には何でしょうか?

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

ピボットテーブルを使用して分析するデータは、単なるデータであってはなりません。これは、未処理 (未要約) の生データである必要があります。通常は、ある種のリストです。この例としては、過去 6 か月間の会社の販売取引のリストが挙げられます。

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

これは生データではないことに注意してください。実際、それはすでにある種の要約になっています。セル B3 には 30,000 ドルが表示されており、これは明らかにジェームスクックの 1 月の売上の合計です。では、生データはどこにあるのでしょうか?どうやって 30,000 ドルという数字に到達したのでしょうか?この数字を生成した元の販売取引リストはどこにありますか?どこかで、誰かがわざわざ過去 6 か月間のすべての販売取引を照合して、上記の概要を作成したに違いないことは明らかです。これにはどれくらい時間がかかったと思いますか? 1時間？十？

おそらくそうです。ご存知のとおり、上のスプレッドシートは実際にはピボットテーブルではあり ません 。これは別の場所に保存されている生データから手動で作成されたもので、コンパイルには実際に数時間かかりました。ただし、これはまさにピボットテーブルを使用して作成 できる 種類の概要であり、その場合はわずか数秒で作成できます。その方法を見てみましょう…

販売トランザクションの元のリストを追跡すると、次のようになります。

Excel のピボットテーブル機能を使用すると、マウスを数回クリックするだけで、上記のような月次売上概要を数秒で作成できることに驚かれるかもしれません。こんなこともできます – それ以外にもたくさんのことができます!

ピボットテーブルの作成方法

まず、Excel のワークシートに生データがあることを確認します。金融取引のリストが一般的ですが、従業員の連絡先の詳細、CD のコレクション、会社の車両の燃料消費量など、ほぼ何でもリストにすることができます。

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

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

リスト内の任意の 1 つのセルをクリックします。

次に、 「挿入」 タブから 「ピボットテーブル」 アイコンをクリックします。

[ピボットテーブルの作成] ボックスが表示され、新しいピボットテーブルはどのデータに基づいて作成する必要があるか、およびどこに作成する必要があるかという 2 つの質問が表示されます。 (上記の手順で) リスト内のセルをすでにクリックしているため、そのセルを囲むリスト全体がすでに選択されています (この例では、 Payments シートの $A$1:$G$88 )。他のワークシートの他の領域にあるリストを選択したり、Access データベーステーブルや MS-SQL Server データベーステーブルなどの外部データソースを選択したりすることもできることに注意してください。新しいピボットテーブルを新しいワークシートに作成するか、既存のワークシートに作成するかを選択する必要もあります。この例では、新しいものを選択します。

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

別のボックス [ ピボットテーブルフィールドリスト] も表示されます。このフィールドリストは、ピボットテーブル (上) 内の任意のセルをクリックするたびに表示されます。

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

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

(a) フィールドのリストの「金額」フィールドがチェックされ、「金額の合計」が値ボックスに追加され、金額列が合計されたことを示していることに注目してください。

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

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

サンプルデータを参照して、この合計を分割するために使用できる可能性のある 1 つ以上の列見出しを特定する必要があります。たとえば、社内のさまざまな営業担当者ごとに行見出しがあり、それぞれの合計が示されているデータの概要を表示したいと判断する場合があります。これを実現するには、「Salesperson」フィールドを [行ラベル] ボックスにドラッグするだけです。

さて、いよいよ、物事が面白くなり始めます。ピボットテーブルが形になり始めます…

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

では、他に何ができるでしょうか?さて、ある意味ではピボットテーブルが完成しました。ソースデータの有用な概要を作成しました。重要なことはすでに学んでいます！この記事の残りの部分では、より複雑なピボットテーブルを作成するいくつかの方法と、それらのピボットテーブルをカスタマイズする方法を検討します。

まず、2 次元のテーブルを作成します。これを行うには、列見出しとして「支払い方法」を使用します。「支払い方法」の見出しを 「列ラベル」 ボックスにドラッグするだけです。

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

とても涼しくなり始めています！

これを立体的な表にしてみましょう。そのようなテーブルはどのようなものになるでしょうか?さて、見てみましょう…

「パッケージ」列/見出しを 「レポートフィルター 」ボックスにドラッグします。

それがどこに終わるのかに注目してください….

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

したがって、正しく考えると、ピボットテーブルは 3 次元になりました。カスタマイズを続けてみましょう…

たとえば、小切手とクレジットカードの取引のみを確認したい (つまり、現金取引は不要) ことが判明した場合は、列見出しから「現金」項目の選択を解除できます。 [列ラベル] の横にあるドロップダウン矢印をクリックし、[現金] のチェックを外します。

それがどのようなものかを見てみましょう…ご覧のとおり、「現金」が消えています。

書式設定

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

このような状況では、テーブル全体 (またはワークシート全体) を選択し、ツールバーの標準の数値書式設定ボタンを使用して書式設定を完了するという、これまでのやり方を実行したくなるかもしれません。このアプローチの問題は、将来ピボットテーブルの構造を変更した場合 (99% の可能性が高い)、それらの数値形式が失われることです。それらを（半）永久的にする方法が必要です。

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

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

[数値の書式設定] ボタンをクリックすると、標準の [セルの書式設定] ボックス が表示されます。

[カテゴリ ] リストから、(たとえば) [会計 ] を選択し、小数点以下の桁数を 0 にドロップします。 [OK] を数回クリックして、ピボットテーブルに戻ります…

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

書式設定の話のついでに、ピボットテーブル全体を書式設定してみましょう。これを行うにはいくつかの方法があります。簡単なものを使ってみましょう…

[ピボットテーブルツール/デザイン] タブをクリックします。

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

魅力的なものを選択し、ピボットテーブルで結果を確認します。

その他のオプション

日付も操作できます。通常、最初のトランザクションリストには、非常に多くの日付が含まれます。ただし、Excel には、データ項目を日、週、月、年などでグループ化するオプションが用意されています。これがどのように行われるかを見てみましょう。

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

ご覧のとおり、これによりピボットテーブルはすぐに役に立たなくなり、トランザクションが発生した日付ごとに 1 つの列が与えられ、非常に幅の広いテーブルになります。

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

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

出来上がり！さらに便利な表:

(ちなみに、この表は、この記事の冒頭に示したもの (手動で作成された元の売上概要) と実質的に同じです。)

もう 1 つ注意すべき点は、複数の行見出し (または列見出し) のセットを指定できることです。

…こんな感じです….

列見出し (またはレポートフィルター) でも同様のことができます。

話を再び単純にして、合計値ではなく平均値をプロットする方法を見てみましょう。

まず、[合計金額] をクリックし、表示されるコンテキストメニューから [値フィールドの設定…] を選択します。

[値フィールドの設定] ボックスの [値 フィールドの要約 ] リストで、 [平均] を選択します。

ここで、 Custom Name を 「Average of Amount」からもう少し簡潔なものに変更してみましょう。「平均」のようなものを入力します。

[OK] をクリックして、どのようになるかを確認します。すべての値が合計から平均に変わり、テーブルのタイトル (左上のセル) が「Avg」に変わっていることに注目してください。

必要に応じて、合計、平均、カウント (カウント = 売上高) をすべて同じピボットテーブル上に表示することもできます。

このようなものを適切な場所に配置する手順は次のとおりです (空のピボットテーブルから開始します)。

「Salesperson」を 列ラベル にドラッグします。
「金額」フィールドを値ボックスに 3 回ドラッグします。
最初の「金額」フィールドのカスタム名を「合計」に変更し、数値形式を「会計」(小数点以下 0 桁) に変更します。
2 番目の「金額」フィールドのカスタム名を「平均」、関数を「 平均」 、数値形式を 「会計」 (小数点以下 0 桁) に変更します。
3 番目の「Amount」フィールドの名前を「Count」に変更し、その機能を「 Count」 に変更します。
自動作成されたフィールドを 列ラベル から 行ラベル にドラッグします。

最終的には次のようになります。

合計、平均、カウントを同じピボットテーブル上で実行できます。

結論

Microsoft Excel で作成されたピボットテーブルには、他にもたくさんの機能とオプションがあります。このような記事では列挙するには多すぎます。ピボットテーブルの可能性を完全にカバーするには、小さな書籍 (または大規模な Web サイト) が必要になります。勇敢な読者や熱心な読者は、ピボットテーブルをさらに簡単に探索できます。ほぼすべてのものを右クリックするだけで、どのオプションが利用可能になるかを確認できます。また、 「ピボットテーブルツール/オプション」 と 「デザイン」 という 2 つのリボンタブもあります。間違っても問題ありません。ピボットテーブルを削除してやり直すのは簡単です。これは、Lotus 1-2-3 の古い DOS ユーザーには決してなかったことでした。