Excelで集計するデータの形を考える

2018.04.13基礎・入門データベース

Excelはセルが縦横にたくさん配置されているので、データをどこにどのように入れるのか自由自在にレイアウトすることができます。
その反面、実際に作成してみたら使いにくいデータになったりデータの本質が見えない表になったりするときもあります。
今回は、様々なExcelの一覧表の形を紹介しながら、理想となるデータの形を考えていきたいと思います。

1項目ごとに見たい

普通の一覧表は、項目ごとにデータを見比べるために使います。
下の表は日付ごとに売上金額を集計したものになります。

1項目ごと

 

この表は、日付ごとに商品の売上個数を求め、そこに商品単価を掛け合わせるという計算を電卓や他の手段でするという手順で値を求めています。
シンプルで見やすいのですが、日付に合わせて商品ごとにも見たいという時には、元の売上データから再度仕分けをした上で計算をし直して求めることになります。

2項目ごとに見たい

日付に合わせて商品ごとにも見たい場合、日付を縦にレイアウトしていたのであれば、商品は横にレイアウトする形になります。
下の表は、日付を縦、商品を横に、売上個数を集計し、最終行で合計したものに単価を掛け合わせた表です。

2項目ごと

 

この場合も、元の売上データから電卓などで日付ごと商品ごとの売り上げ個数を計算した値を入力したものになるでしょう。
さらにこの表に売上部署ごとにも分けてみたい場合、またさらに元の売上データを、日付ごと商品ごと元部署ごとに仕分けして、電卓などで再度計算する必要があります。

3項目ごとに見たい

下の表は、日付を縦、商品を横に合わせて、部署ごとに見るために部署を横にレイアウトしたものです。

3項目ごと

 

横にレイアウトされている項目が、商品と部署の二つになるため、同じ商品の項目が部署ごとにレイアウトする必要があります。

エクセルの表の特徴

上記のことから、Excelの表は次のような特徴があると言えます。

3項目以上の項目のレイアウトが苦手

シンプルな構造だと、縦と横それぞれに合計2項目までを配置できますが、3項目以上になると、だんだん表が大きくなっていき、扱いづらくなります。

1項目の中のデータの種類が増えると増えるたびにデータが大きくなる

販売している商品などは日々追加されていくもので、縦に日付、横に商品をレイアウトしていた場合、商品が増えていくごとに、横にどんどん表が大きくなっていきます。

一覧表を再利用する時に項目やデータの種類が増えるたびに大改造になる

さらに部署ごとに集計している場合、部署ごとにその商品の項目を増やさなければなりません。
一度作った表のレイアウトを続けて使うには、大きな改造が必要になってくる場合もあるでしょう。
日付ごと商品ごとにレイアウトされた一覧表で、日付ごと部署ごとに見てみたいという時に、再度始めから集計をやり直しになるのです。

エクセルの表を作成するときに考えたいこと

Excelの特徴は、計算や集計が簡単にできることです。
この特徴を最大に利用することで、様々な一覧表のレイアウトの表を人間の手で何度も集計しなおさずに、素早くいろんな角度でデータを見ることができます。
入力したデータは、色々なケースで使えるように再利用することを考えなければなりません。
つまり集計した後のデータを作成するのではなく、できるだけ再利用可能な元のデータを記録することが重要です。
そのためには、データを出来事ごとに記録するとよいでしょう。

1行に一つの出来事で入力する

下の表は、1行に、何月何日に何の商品がどこの部署で何個販売したかという一つの現象を入力しています。
売上金額は単価一覧表から商品名を検索するVLOOKUP関数を使って一行ごとに計算しています。
VLOOKUP関数の詳細はこちらです。

1現象ごと

 

もっと細かく、誰かに販売したごとに、何月何日の何時何分に何の商品がどこの部署で何個販売したかという記録をしたのが下の表です。

一件ごと

 

このように記録することで、商品が増えても、部署が増えても表が横に広がることはありません。縦方向にどんどん大きくなるので扱いにくくなるようにも思えます。
Excel2016では、縦方向にセルが104万行以上あります。
対して、横方向には16834列しかありません。
確かに1万列以上あるので余裕があるように感じますが、100商品、10部署の他に、10の販売先も管理するとなると、それだけで10000列必要になりギリギリになります。
できるだけExcelは横方向よりも縦方向に大きくするように記録すると良いでしょう。

集計する手法

縦方向に大きくなるように一件一件記録したデータは、そのままでは非常に見づらいのですが、ピボットテーブル機能や、COUNTIFS関数、SUMIFS関数を使うことで縦横に項目を配置したレイアウトの集計表を作成することができます。
ピボットテーブル機能の詳細はこちらです。
COUNTIFS関数、SUMIFS関数の詳細はこちらです。

まとめ

はじめに、集計した表を作成すると他のレイアウトで集計してみたい時に再度データの仕分けから計算までしなければなりません。
データはできるだけ、一つの出来事に対して1行になるように記録しておいて、Excelの計算機能を使って、好きなレイアウトで集計表を作るという形で作成すると良いでしょう。

Excel業務をもっと「ラク」にするなら

Excel業務を自動化したい!
Excelをデータベース化したい!

 

Back to top