EXCEL集計表設計の3つのポイント-改善実例を基にコツを紹介

最近、仕事で他の方の業務を引き継いだのですが、非常に効率が悪かったので、設計改善を行う機会がありました。

サイトの分析結果をExcelでレポートする業務なのですが、Analyticsから貼り付ける箇所が400箇所以上あり、作成だけで40時間近くかかる問題がありました。

また、問題はそれだけでなく、集計表が入り組んでいるのでレポートを読む側もよく分からない、という状況でした。

私が精査した結果、先月だけで7箇所ほどコピペミスや関数ミスがあり、何年も前からずっと間違えたまま気付かずにいたミスも発覚しました。(作る側も読む側も気付かなかったんですね)

さて、その業務を引き継いだ私がどのような改善を行ったのかを、まとめてみたいと思います。

ポイント1:「データ貼り付けシート」と「結果を見せるシート」を分ける

なんと言っても一番重要なのがこの点です。

これを行うだけで「毎週400箇所以上のコピペ」→「毎週1箇所のコピペ」に大幅な作成効率アップになります。

やり方としては「データを貼りつけるシート」には、Analyticsから落としてきたCSVを貼り付けします。

「結果を見せるシート」には、CSVで貼り付けたデータを引っ張ってきて見せるだけです。

引っ張ってくる方法は、主にVLOOKUP関数を用いています。

様々な種類の関数を用いると、管理やメンテナンスが面倒になるため、基本的にデータ引用はVLOOKUP関数で統一しています。

参考:データを引用する(VLOOKUP関数)

この方法の良い点は、見せ方を変えたい時や項目の追加・削除に対して柔軟に対応できる点です。

例えば、「この項目はやっぱり右側に配置してくれ」「この指標を計算して3列目に並べてくれ」「やっぱりこの項目はいらないから削除してくれ」などがあっても、『CSV出力→貼り付け』までは手順は変わらず、最後の「結果を見せるシート」だけ変更すれば良いので、Analytics側での配置の順番やコピペの時の場所を気にする必要が全くないのです。

実現のためのポイント:間接参照を使う

実際にやろうとすると、行き詰ったことがありました。

それは、「検索値が重複する」ということです。

例えば、Aページの”訪問時間”、Bページの”訪問時間”のようなデータのとり方をしていると、VLOOKUP関数で引用する時に”訪問時間”という項目が被っているので、上手く引用してくれなかったのです。

そこで用いたのが間接参照を使って、「参照エリアを分けること」でした。

例えば上記の画像の例を挙げるなら、1行目~13行目までがページAの範囲、14行目~25行目までがページBの範囲、というものです。

そして、J列の内容をINDIRECT関数で参照範囲として指定することで、VLOOKUPでも上手く引用が出来るようになります。


この参照エリアの一覧を別のシートに作っておくことで、検索値の重複問題を解消させました。

参考:間接参照でメンテナンスを簡単にする(INDIRECT関数)

ポイント2:「データを判断するエリア」と「データを貯めておくエリア」を分けて設ける

ポイントの2つ目は「きちんとデータを判断する場所を作ること」です。


例えば上図のようにデータがずらずら並んでいて思うことは、「これ見て何をどう判断しろと?」ということです。

きちんとデータを出す意図を考え、適切に判断できる形に並べる必要があります。

しかし、データ量が多くなってくるとそれも難しくなってくることがあります。

上図ではページA~Dの4つなので、並び替えてきれいに表示することが出来るかもしれませんが、私が実際に引き継いだ業務では50程度のボリュームがありました。

並び替えて表示するだけでは、上手く判断できる表示にはなりません。

そこで考えたのが、「結果を見せるシート」を「データを判断するエリア」「データを蓄積するエリア」に分けたことです。

例えば上図では、赤枠が「データを判断するエリア」で、青枠が「データを蓄積するエリア」になります。

A3セル「ページA」と書いてある部分はリスト形式になっていて、どのページの情報を見たいのかを選択できます。

このように「判断するエリア」では、見たい情報を選択して各種指標を参考に判断できるようにします。

一方で一覧で見たいとか、もっと詳細なデータが欲しいという要望に応えるために、データを蓄積するエリアがあります。

上記では「判断するエリア」と「蓄積するエリア」は同じ項目ですが、「判断するエリア」は判断に必要な項目だけにする、など項目に差異をつけることも可能です。

ポイント3:値が「大きい」のか、「小さい」のか判断できる指標を設ける

最後のポイントは、判断のための指標の追加です。

例えば、あるページのPVは月1万でした。良いのか、悪いのか、どちらだと思いますか?

答えは「わからない」です。

月1000PVを目指しているページであれば十分良いでしょうし、毎月100万PVを達成しているページであれば異常に悪いと言うことが出来ます。

つまるところ、ページの目的や前月、前年比較など諸々の状況を加味しないと判断できないということです。

目標値を持っていれば、それを用いれば済みますが、私が対応したケースでは目標値の設定が難しく、「前年同月」を用いることにしました。

前年同月と比べて、何PV上がったか、下がったかを示すものです。

前年のデータも「データを蓄積するエリア」に表示させることで、さきほどと同様のフローで比較用指標の引用を実行しました。

まとめ

Excel集計表の効率化を図った経験をベースに

  • 「データ貼り付けシート」と「結果を見せるシート」を分ける
  • 「データを判断するエリア」と「データを貯めておくエリア」を分けて設ける
  • 値が「大きい」のか、「小さい」のか判断できる指標を設ける

の3つが集計表設計のポイントである、とご紹介しました。

今回はサイトのアクセス解析をサンプルとしましたが、売上などの基幹データやAccessを用いたデータ連携などにも同じ考え方で流用できますので、ぜひご参考ください。

参考:AccessとExcelのオススメ連携法