エクセル ピボットテーブルで集計項目をまとめるグループ化の方法。文字列・日付・数字の場合

シェアする

エクセルを使って入力したデータを集計・分析するのには、ピボットテーブルを使うと便利です。

記事「ピボットテーブルを使って簡単にデータ集計できます」では、ピボットテーブルを使った集計のやり方を紹介しました。

ピボットテーブルで、集計項目が詳細に分類されているときなどで、項目をまとめて集計もしたいときがあります。

上の記事では、日付・金額・用途の家計簿メモから用途別に支出を集計してみましたが、例えば「用途」の中の外食昼と外食夜の2つの項目をまとめて外食費として集計したかったりします。

ピボットテーブルには「グループ化」の機能があり、行フィールドや列フィールドの項目をまとめる事ができます。以下に説明いたします。

文字列からなる項目のグループ化の方法

記事「ピボットテーブルを使って簡単にデータ集計できます」では、次のようなピボットテーブルを作成しました。

文字列からなる[用途」のうち、「外食昼」と「外食夜」を「外食費」としてまとめ、「食費」と「食費予定外」を「食費」としてまとめたいと思います。

手順1. 「外食費」をまとめる。

①ピボットテーブル内の行ラベルにある「外食昼」と「外食夜」を選択して、右クリックする。
②コンテキストメニューの中からグループ化を選択する。

手順2.グループ名の変更

次に、新しく作成された「用途2」項目の「グループ1」の名前を変更します。
手順1により、新しい項目「用途2」が作成されました。右側の行フィールドを見ると、
「用途2」は「用途」の上に並んでいますね。これは、「用途2」で分類し、更に、「用途」で細分化していることを意味しています。
左側のピボットテーブルを見ると「用途」の「外食昼」と「外食夜」が「用途2」では、「グループ1」と言う要素でまとめられていることがわかります。
①この「グループ1」のセルを選択して、「外食費」と入力します。

手順3. 「食費」と[食費予定外」をグループ化

同様に、「用途」の「食費」と「食費予定外」をグループ化します。
①下図のように「用途」の[食費」、「食費予定外」を選択して下さい。
※セルの位置が離れているので、マウスをドラッグして選択する方法は使えません。
この場合、まず、12行目の「食費」を選択(左クリック)、次に、Ctrlキーを押しながら、14行の「食費予定外」を選択します。

②2つのセルが選択できたら、どちらかのセルで右クリックをして、コンテキストメニューから「グループ化」を選択します。

③「グループ2」が作成されますので、選択して名称を「食費」と変更します。

手順4. 「用途2」の小計を表示

「用途2」のどれかの項目で右クリックして、コンテキストメニューから「”用途2″の小計」を選択します。

「用途2」の集計金額が表示されるようになります。

手順5. 「用途」を行フィールドから削除

また、不要であれば、右側の行フィールドから「用途」を削除すると、「用途2」だけの集計になります。

削除の手順ですが、もっとも簡単な方法は、行フィールドから「用途」をつまんで(マウスの左ボタンおしたまま)、枠外へマウスを移動(ドラッグ)し、アイコンに×が表示されているところで、左ボタンをはなします(ドロップ)。

あるいは、行フィールドの「用途」の右側の下三角をクリックして、プルダウンメニューから「フィールドの削除」を選択しても削除することが出来ます。

列フィールドへの「日付」の追加の方法と、「日付」のグループ化による月ごとの集計方法

次に、月ごとの支出の変化を分析してみましょう。列方向に月ごとに集計する方法を説明いたします。

手順1. 「日付」を列フィールドに追加

まず「ピボットテーブルのフィールド」上部より、「日付」を下部の列フィールドへ追加します。
追加の方法は、今まで出てきたように ドラッグ&ドロップです。上部フィールドより「日付」を左クリックし、左ボタンを押したままマウスを列フィールドまで動かし、そこで左ボタンをはなします。

手順2. 日付のグループ化

①手順1により列方向に日付ごとの支出が集計されます。月ごとの集計とするために、ピボットテーブル内の列ラベルの日付のどれか一つを選択し、右クリックし、コンテキストメニューから「グループ化」を選択します。

②「グループ化」ウインドウが表示されますので、「月」と「年」の2つを選択状態(青く反転した状態)にします。
ウインドウが開いたときは「月」のみ青色に反転していると思いますので、その場合は、「年」を一回左クリックすれば、図のように「月」と「年」を選択状態に出来ます。
③変更出来たら「OK」でウインドウを閉じます。

以上の手順により、「月」ごとの支出の変化が集計できました。

表示の必要のない項目の削除方法

※行ラベル、列ラベルともに表示の必要のない項目があります。

行では「用途2」の「(空白)」、「用途」の「(空白)」、
列では、「年」の「<2016/10/10」、「月」の「(空白)」などです。

表示を消すには、
行であれば、行ラベルの横の下三角をクリックします。
列であれば、列ラベルの横の下三角をクリックします。

ここでは、「月」の「(空白)」を消してみます。

手順

列ラベルの横の下三角をクリックします。表示されるプルダウンメニューから
①フィールドの選択で、下三角を押して、「日付」を選択します。
②下部のフィルタ設定から「(空白)」の左側のチェックボックスを外します。クリックすればチェック状態を変更できます。
③「OK」で確定します。

補足:数字からなる項目のグループ化の方法

ここまで、文字列からなる項目、日付の項目のグループ化の方法を説明しました。

数字の項目別の集計をする際、項目のグループ化の方法が違いますので、合わせて説明します。

ここでは、行に金額を追加し、金額ごとの集計をしてみたいと思います。

手順1.行フィールドへ「金額」の追加

①「ピボットテーブルのフィールド」の上部より、「金額」を行フィールドへドラッグ&ドロップします。ドラッグ&ドロップとは、「対象物をマウスの左ボタンを押してつかんだまま、マウスを動かして目的の場所でにボタンをはなす」でしたね。

下図のように、行フィールドには「用途2」や「用途」がありますので、その下に追加してみましょう。

すると、行方向に「金額」が表示されるようになりました。

手順2. 行ラベル「金額」のグループ化

この状態では全ての「金額」の要素が表示されていますので、金額帯別に集計したいと思います。

①行ラベルの中の「金額」の要素をどれか選択して右クリックします。
②コンテキストメニューから「グループ化」を選択します。

「グループ化」ウインドウが開きます。

数字の範囲を入力することでグループ化します。ここでは5,000円ごとの範囲で集計してみます。

③「先頭の値」の欄に0を入力し、「単位」の欄に5000を入力します。入力したら「先頭の値」項目の自動設定チェックがはずれ、入力した値が有効になります。
④「OK」で確定します。

「金額」で5,000円ごとに集計することが出来ました。

最後に

今回は、ピボットテーブルにおいて、文字列・日付・数字などで構成された部類項目をグループ化して集計する方法を説明しました。

また、行フィールド、列フィールドの項目で表示不要な項目を表示しない方法を説明しました。

これで、ピボットテーブルの基本的な使い方はほぼマスターできています。ぜひ、使ってみましょう。

この記事が気に入ったら
いいね ! しよう