Excelのピボットテーブルツールを使ってデータを意味のある情報に変える方法 - TechRepublic

Excelのピボットテーブルツールを使ってデータを意味のある情報に変える方法 - TechRepublic

イラスト: リサ・ホーヌング

Microsoft Excelにピボットテーブルツールが追加される前は、データの整理と分析には集計関数や機能を使う必要がありました。これはしばしば大変な作業であり、さらに悪いことに、理解しにくいものでした。もしあなたが今でもそのような方法で作業しているなら、それはあまりにも大変なことです。Excelのピボットテーブルツールを使って、生のデータを意味のあるレポートに変換してみましょう。複雑に思えるかもしれませんが、実際にはそうではありません。生産性を向上させるのに役立つ柔軟なツールです。

Microsoft 365を使用していますが、以前のバージョンでもご利用いただけます。.xlsxデモファイルをダウンロードするか、ご自身のデータで作業することも可能です。ブラウザ版はピボットテーブルツールを完全にサポートしています。

ピボットテーブルとは何ですか?

Excelのピボットテーブルツールは使いやすいですが、使い慣れていないと最初は少し戸惑うかもしれません。ピボットテーブルとは、レポート作成のために特定の範囲のデータを再編成・集計することでデータを分析できるツールです。表示は元のデータとは別で、元のデータは一切変更されません。Excelでは、ピボットテーブルとはピボットテーブルを作成するためのツールを指します。データをピボットすることで、 必要 な情報を分かりやすいレイアウトで素早く取得できるため、データの量に関係なく非常に便利なツールです。具体的には、並べ替え、グループ化、集計、カウントなど、データを再編成できます。

話を進める前に、データ情報という用語について説明しましょう。データとは、2次元の列と行の形式で表された値を指します。情報とは、そのデータから意味のある形で抽出されるものです。例えば、どの営業担当者がノルマを達成したか、各クラスでどの生徒が最もGPAが高いかなどです。ピボットテーブルを使えば、商品別、営業地域別、あるいは担当者別の月間売上を表示できます。教師であれば、クラスや科目別の生徒平均を表示するかもしれません。私はピボットテーブルを使って、顧客別の収入合計を集計しています。2次元のスプレッドシートでは、意味のある情報を見分けるのは容易ではありません。

さあ、ピボットを始めましょう!

参照:  Windows 10 のロゴキーショートカットで作業をスピードアップ (無料 PDF)  (TechRepublic)

ステップ1: ソースデータを特定する

ピボットテーブルを作成する前に、分析するデータを特定する必要があります。データには空白の行や列、小計が含まれていてはならず、表形式である必要があります。図Aは、日付、従業員、地域別のシンプルな売上データを示しています。それでは、ピボットテーブルを使って従業員別の売上合計を表示するプロセスを始めましょう。データセット内の任意の場所をクリックし、以下の操作を行います。

  1. [挿入] タブをクリックし、[テーブル] グループの [ピボットテーブル] をクリックします。
  2. 表示されるダイアログで、範囲が正しいことを確認します (範囲は常に正しいですが、これは画面外の空白の行または列を検出するのに適した方法です)。
  3. 新しいピボットテーブルを既存のワークシートに配置するか、新しいワークシートに配置するかを指定します。データとピボットテーブルの両方を確認できるように、既存のワークシートで作業します。そのためには、「位置」コントロールの矢印をクリックします。すると、ダイアログがそのコントロールのみに縮小されます。次に、新しいフレームのアンカーセル(シート内)をクリックし、矢印をクリックしてダイアログに戻ります。図Aに示すように、アンカーセルとしてF3キーを選択しました。
  4. [OK]をクリックします。

図A

この時点では、ピボットテーブルフレーム(図B)は作成されていますが、まだ空です。次のセクションでは、フレームオプションを使用してピボットテーブルを作成します。

図B

ステップ2: フィールドと値を特定する

フレームを配置したら、列、行、値を配置する準備が整いました。フィールドをドラッグする前に、何を実現したいのか、そしてそれをどのように実現するのかを考えてみましょう。売上合計を表示したいとします。

ピボットテーブルフィールドペインでは、フレームを埋めるために必要な設定を行うことができます。ペインが表示されない場合は、フレーム内をクリックしてください。それでも表示されない場合は、フレームを右クリックし、表示されるサブメニューから「フィールドリストの表示」を選択してください。ペインが表示されたら、上部ペインから「Personnel」を「行」フィールドにドラッグし、「Amount」を「値」フィールドにドラッグしてください。ピボットテーブルツールでは、デフォルトでそのフィールドの合計が表示されます。(下矢印をクリックして「値フィールドの設定」を選択することで、この設定を変更できます。)

現状のピボットテーブルは少し粗雑です(図C)。幸い、書式を追加したりフィールド名を変更したりすることで、より見栄えの良いレポートを作成できます。まず、F3キーを押して「行ラベル」を「人事」に置き換えます。次に、G3キーを押して「金額合計」を「売上合計」に置き換えます。最後に、2列目の値を以下のように書式設定します。

  1. 総計ヘッダー セルを右クリックします。
  2. 表示されるサブメニューから「数値書式」を選択します。
  3. カテゴリリストで通貨を選択します。
  4. [OK]をクリックします。

図C

図Dに示した微妙な変更で全体像が完成しました。数回クリックしてドラッグするだけで、全従業員の合計売上がわかります。おめでとうございます!これで最初のピボットテーブルが作成できました。それでは、同じピボットテーブルに表示される情報を変更する方法をいくつか見ていきましょう。

図D

練習してみましょう

この表の作成は簡単でした。表を修正することで、表に表示される情報を補足したり、完全に変更したりできます。図Eは、「日付」フィールドを「列」リストにドラッグした結果です。結果は、日付ごとの人員の小計です。変更点は1つだけです。最終的な合計(総計)は元の表と同じです。

図E

では、「地域」を「行」フィールドにドラッグして、実際に変化をつけてみましょう。そうすることで、ソースデータの詳細がさらに表示され、日付の小計が地域ごとに分割されます(図F)。人物と地域の総計は、日付の小計と依然として同じであることに注目してください。ここで行っているのは、詳細情報を表示することで、より多くの情報を追加するだけです。

図F

もう1つ変更を加えてみましょう。日付の合計を四半期ごとに表示してみましょう。これを行うには、日付ヘッダーセル(G4からJ4)のいずれかをクリックし、表示されるサブメニューから「グループ化」を選択します。グループ化オプションは非常に柔軟で、後で試してみる価値があります。今は、「基準」リストで「月」の選択を解除し、「四半期」を選択して「OK」をクリックします(図G )。図Hに示すように、すべての日付が第2四半期に該当します。日付を追加すると、ピボットテーブルをすばやく更新するだけで、第1四半期、第2四半期、第3四半期、第4四半期の日付が自動的に表示されます。

図G

図H

他にもたくさんあります

氷山の一角を見ただけです。もっと詳しく知るには、実際に操作してみるしかありません。フィールドの追加や削除、行を列へ、列を行へドラッグ、様々なフィールドやグループ化の設定を確認してみましょう。ごちゃごちゃしてしまっても心配しないでください!それも学習プロセスの一部です。私自身、ピボットテーブルを操作しているときに「こんなことをするつもりじゃなかった」と思ったことが何度あったか分かりません。一度コツをつかめば、ピボットテーブルツールを使ってデータを意味のある情報に変換する理由がたくさんあることに気づくでしょう。

Tagged: