Excelでピボットテーブルを使ったデータの整理や分析

Excel

Excelのピボットテーブルを使用すると、大量のデータを簡単に集計し、分析することができます。今回は、学歴ごとに年齢分布を集計し、任意の順序で並べ替える方法をご紹介します。

ピポットテーブルとは

ピポットテーブル

ピボットテーブルは、Excelでデータを効率的に集計・分析するための強力なツールです。特定の条件でデータをフィルタリングしたり、集計結果を視覚的に整理してレポートを作成したりするのに非常に便利です。

ピボットテーブルの利点

ピボットテーブルを使うと、以下のような利点があります：

  • データの集計: 大量のデータを簡単に集計できます。
  • データの分析: 特定の条件でデータをフィルタリングして分析できます。
  • レポート作成: データを視覚的に整理し、レポートを作成するのに役立ちます。
  • 柔軟な操作: ドラッグ＆ドロップで簡単にデータの視点を変更できます。

データの準備

データを用意します。
今回は社員の最終学歴と年齢を書き出してみました。（サンプルデータです）

ピポットテーブルを作ってみる

このデータがどのようになるのか一度作ってみましょう。
まず使う範囲を囲みメニューの挿入からピポットテーブルを選択

するとこのような感じになります。

今回作りたいイメージは縦に年齢、横に卒業です。
なので右のピポットテーブルのフィールドで年齢を行に、学歴を列と値に入れます。（ドラッグ＆ドロップ）

この操作で左に表を作ることができました。
しかし、左の行ラベルのところは年齢が細かすぎて見にくいので年齢を5歳刻みで表示してみたいと思います。

年齢カテゴリの作成

年齢を5歳刻みで区分し、「年齢カテゴリ」という列を作成します。これにはIF関数とFLOOR関数を使用します。

新しい列「年齢カテゴリ」を作成し、以下の式を入力します

=IF(B2<=20, "20歳以下", FLOOR(B2, 5) & "-" & (FLOOR(B2, 5) + 4))

ここで、B2は年齢が入力されているセルです。この式を列全体にコピーします。

式の説明

この式は、年齢を5歳刻みで分類し、20歳以下の場合は「20歳以下」と表示するためのものです。

  • IF(B2<=20, "20歳以下", ...): もしB2セルの値（年齢）が20以下の場合、「20歳以下」と表示します。
  • FLOOR(B2, 5): B2セルの値を5の倍数に切り捨てます。例えば、34なら30、29なら25になります。
  • FLOOR(B2, 5) & "-" & (FLOOR(B2, 5) + 4): 切り捨てた値に4を加え、年齢の範囲を表示します。例えば、34なら「30-34」、29なら「25-29」になります。

任意の順序で並べ替え

このままでは最終学歴の並びがバラバラなので、ピボットテーブルの行ラベル（学歴）を任意の順序で並べ替えるようにします。

順序リストの作成

年齢カテゴリの同じシートに、学歴のリストを作成します。（じゃまに感じるのならばシートを変えても大丈夫です。）
今回は左から高卒・短大卒・大卒・大学院卒のようにしたいので下記のような感じに作成。

順序番号の追加

元のデータに「順序番号」という新しい列を追加し、VLOOKUP関数を使用して順序番号を取得します

=VLOOKUP(A2, $F$2:$G$5, 2, FALSE)

ここで、A2は元のデータの学歴列のセル、$F$2:$G$5は順序リストの範囲です。この式を列全体にコピーします。

式の説明

この式は、学歴に基づいて順序番号を取得するためのものです。

  • VLOOKUP(A2, $F$2:$G$5, 2, FALSE): VLOOKUP関数は指定した範囲から値を検索します。
    • A2: 検索値です。この場合は学歴のセルです。
    • $F$2:$G$5: 検索範囲です。ここでは、順序リストが存在する範囲を指定しています。
    • 2: 検索範囲の中で、検索値に対応する結果を返す列の番号です。この場合は、順序番号が格納されている2列目です。
    • FALSE: 完全一致を要求する指定です。

ピボットテーブルの更新

年齢カテゴリと順序番号ができたので、ピボットテーブルを更新します。

  1. ピポットテーブルのあるSheetでピポットテーブルを選択（テーブルだったらどこでもよし）。
  2. メニューに「ピポットテーブル分析」という項目ができているのでそちらを選択。
  3. データソースの変更を選択。
  4. 画面がSheet１（最初のデータのSheet）に変わりピポットテーブルの移動というウィンドウが開く。
  5. テーブル/範囲のところの「入力フォーム」を選択。
  6. 使用するデータの範囲を選択しなおす。
  7. 「入力フォーム」内が変わったら「OK」を押す。

行と列の更新

  1. ピポットテーブルのフィールドで行と列を修正します。
  2. 行：年齢を削除し、新たに年齢カテゴリを持ってくる。
  3. 列：学歴の上に順序番号を持ってくる
  4. ピポットテーブルの行ラベルと列ラベルの横にある▼を押し並べ替えを行う。（今回は降順）
  5. 列の項目がちょっと見にくいので「順序番号」を非表示にするためにフィルタ設定の小計とフィルターで「なし」を選択

    これにより行は上から年配順、列は左から高卒順で表示されるようになります。

    おしまいに

    Excelは極めるとすごい事ができます。
    今回の方法も仕事の売り上げデータ分析などに使えばグンとやりやすくなると思います。
    仕事で使う機会が有ったので、皆さんにとっても有益な情報だと思いまとめてみました。

    この記事を参考に、Excelでのデータ集計や並べ替えを簡単に行えるようになります。他にもさまざまな方法がありますので、自分に合った方法を見つけてみてください。

    ご感想をお聞かせください

    Excelでのデータ集計や並べ替えを簡単に行えるようになるので一度やってみてください。そしてこの記事が役に立ったと感じたら、ぜひ共有してください。感想などまってます。

    コメント

    タイトルとURLをコピーしました