エクセルを使って入力したデータを集計・分析するのには、ピボットテーブルを使うと便利です。
記事「ピボットテーブルを使って簡単にデータ集計できます」では、ピボットテーブルを使った集計のやり方を紹介しました。
ピボットテーブルで、集計項目が詳細に分類されているときなどで、項目をまとめて集計もしたいときがあります。
上の記事では、日付・金額・用途の家計簿メモから用途別に支出を集計してみましたが、例えば「用途」の中の外食昼と外食夜の２つの項目をまとめて外食費として集計したかったりします。
ピボットテーブルには「グループ化」の機能があり、行フィールドや列フィールドの項目をまとめる事ができます。以下に説明いたします。
文字列からなる項目のグループ化の方法
記事「ピボットテーブルを使って簡単にデータ集計できます」では、次のようなピボットテーブルを作成しました。
文字列からなる［用途」のうち、「外食昼」と「外食夜」を「外食費」としてまとめ、「食費」と「食費予定外」を「食費」としてまとめたいと思います。
手順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円ごとに集計することが出来ました。
最後に
今回は、ピボットテーブルにおいて、文字列・日付・数字などで構成された部類項目をグループ化して集計する方法を説明しました。
また、行フィールド、列フィールドの項目で表示不要な項目を表示しない方法を説明しました。
これで、ピボットテーブルの基本的な使い方はほぼマスターできています。ぜひ、使ってみましょう。
コメント