PR

 前回は請求明細に記載されている店名などからその種別を分類するワークシートを作成しました。今回は「コンビニ」「通信費」などと分類された明細を種別ごとに集計して分析できるようにしました。前提にしているデータはクレジットカードの請求明細ですが、それ以外のデータにも応用できるはずです。

 図1は毎月の明細をSUMIF関数を使って集計したものです。D2セルの数式を図に示しました。D3からD7までのセルにはこの数式をコピーしてあります。今回のデータは種別を「スーパー」「コンビニ」「通信費」「ガソリン」「有料道路」「その他」の6種類に分類しています。

図1 利用明細の種別ごとに小計を求める。請求明細を毎月分析して管理しておくのに便利な手法
図1 利用明細の種別ごとに小計を求める。請求明細を毎月分析して管理しておくのに便利な手法
[画像のクリックで拡大表示]

 金額を合計する種別は左隣のセルに入力しています。例えばD1セルなら「スーパー」の集計で、D6セルなら「有料道路」の集計という具合です。

 分類する種別を変更したいときはC2~C7セルの文字を書き換えてください。数式を変更する必要はありません。

 このシートは毎月コピーして使います。月ごとのシートにデータを入力していけば毎月の請求が種別ごとに分類・集計される仕組みです。

年間集計はピボットで

 1カ月単位ではなく、もっと長い期間の集計をするにはExcelのピボットテーブル機能を使った方がいいでしょう。図2は2008年9月から2009年8月まで1 年分のデータを集計した例です。数式を考える必要もありません。

 スペースの関係で作成手順まではお見せできませんが、ぜひファイルをダウンロードしてピボットテーブルの機能をお確かめください。同じファイルに1年分、650件のサンプルデータも収納しています。

 ピボットテーブルを使えば各数値の内訳も簡単に見ることができます。例えば図2のD5セルは2008年9月分のコンビニ利用総額が8333円だということを示しています。このセルをダブルクリックすると利用の内訳が分かります。ここには掲載していませんが、このデータでは1カ月に26件のコンビニ利用があって、それぞれの店名も表示されます。実際にファイルをExcelで開いてダブルクリックしてみてください。

図2 大量の請求明細がある場合はピボットテーブルを使って分析することが可能。種別、月別に集計されている
図2 大量の請求明細がある場合はピボットテーブルを使って分析することが可能。種別、月別に集計されている
[画像のクリックで拡大表示]

 ご用意したサンプルデータだけではなく、お手持ちのデータがあればそれで分析してみてください。データの羅列を眺めているだけでは分からなかったことが見えてくるはずです。クレジットカードのWebサイトで得られる利用明細や、ネットショッピングの履歴などを分析できるのではないでしょうか。

 利用履歴だけではなく、売る側の立場の方なら販売データなどを分析してみるのも面白いかもしれません。

 Webから得られるCSV形式のデータはExcelでそのまま開くことができます。ただし、日付がExcelの日付シリアルデータとして正しく読み込まれているか、金額のデータが数値として読み込まれているかなどをよく確認してから分析を始めてください。

サンプルファイルの公開は終了しました。