ExcelのTIPS

ピボットテーブルを使って、数字の範囲ごとの件数を集計する方法

ExcelのTIPS

今回の記事では、Excelのピボットテーブルを使って、数字の範囲毎の集計をする方法を書いていきます。

ここでいう数字の範囲毎の集計というのはこういう形です。

年齢を10歳きざみで件数を集計

「売上金額が書かれた表を~1000万、1000-2000万、2000万-3000円といった範囲ごとに集計する」といったことも同じ方法で集計できます。

こうした集計は実務でもよくありますが、手作業で行うよりもピボットテーブルの「グループ化」機能を使う方が圧倒的に早いです。

例によって、なんちゃって個人情報のデータを使って、「年齢別の件数を集計する」という内容で解説していきます。

ピボットテーブルの作成

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

集計を行うテーブルを開いた状態で、「テーブルデザイン」タブの、「ピボットテーブルで集計」をクリックします。(テーブル化されていない表の場合、「挿入」タブの「ピボットテーブル」をクリック)

すると次のような画面がでます。

ピボットテーブルをどこに作成するか、複雑な集計をするためのデータを作成するかどうか聞かれますが、よくわからなければそのままOKを押しても大丈夫です。

これで新しいシートにピボットテーブルが作成されます。次はピボットテーブルの設定を行います。

ピボットテーブルの「行」と「値」に、集計したい数値のカテゴリをドロップする。

ピボットテーブルの「行」と「値」に、集計したい要素をドラッグ・アンド・ドロップします。

今回の場合は「年齢」をそれぞれの項目にドロップします。

画像のように表が構成されたら、正しく操作できています。

次は、「行」を一定の数字ごとにグループ化します。

「行のグループ化」を設定する。

次に、「行」の年齢をグループ化します。

年齢の列を選択した状態で、「ピボットテーブル分析」のタブから「フィールドのグループ化」をクリックします。

すると、次のような画面が出てきます。

「先頭の値」に設定した数値未満の数字は、ピボットテーブル上で「<30」という形で表現されます。
したがって、その数字未満は「~」と表現して構わない数字をいれます。

同じように、「末尾の値」に設定した数値より大きい数字は、「>70」という形で表現されます。
こちらも、その数字より上は「~」と表現して構わない数字をいれます。

「単位」は、いくら刻みで表示を行うか設定します。年齢なら10、売上金額などであれば10,000,000といった感じで入力します。

今回は、「30~70歳の年齢層を10歳刻みで集計する」という形で設定してみます。

年齢が10歳毎でグループ化されました

このようになりました。30歳未満、70歳超のデータもありますが、~という形でまとめて集計されます。

ちなみに上記画像の例は年齢の集計が「合計」されてしまっているので「件数」で集計するように、設定を変えます。ここまでの手順で件数で集計できている方は、ここまでの手順で完了です。

数値の集計方法を変更する

ここからは合計になってしまっている年齢の集計を、件数で集計するように設定を変えます。

集計列のセルを選択した状態で、「ピボットテーブル分析」の「フィールドの設定」をクリックします。(集計列を右クリックしてもいいです)

すると次のような画面が出てきます。

集計方法のうち、「個数」を選択してOKを押します

すると、件数で集計が行われるようになります。

これで完成です。

まとめ

今回は、数字の範囲ごとの件数集計を、ピボットテーブルで行う方法について書きました。

手順をまとめると以下のようになります。

ピボットテーブルで数字の範囲ごとの件数を集計する方法

1.集計を行うテーブルを、ピボットテーブルにする。

2.ピボットテーブルの「行」と「値」に、集計したい要素を設定する。

3.行をグループ化する。

4.値の集計を件数に変更する。

一度、この形にできれば、そのままピボットグラフにすることもできるので、レポート用のグラフ作成も簡単です。

範囲毎の集計作業は手作業でやるよりもピボットテーブルを使う方が圧倒的に早くて正確なので、こうした集計作業を頼まれたときは、ぜひこの方法を使ってみてください!

ここまで読んでいただき、ありがとうございました!

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