はじめに
週次や日次でKPIのレポートなどを提出する際、システムから吐き出したCSVファイルや別のExcelシートからデータをコピペして、、、とかなり面倒な作業をしなければいけないことってありますよね。
そんな時、レポートのExcelシートの構成を少しだけ工夫するだけで、データの更新やメンテがとてもやりやすくなります。
よくあるExcelレポートの問題
たとえば、レポートを作成するのに、
- CSVファイルを一旦ピボットテーブルで整理して、
- 前回作成したExcelレポートの中に値貼り付けして、
- 別のExcelファイルから一部分を切り取って値貼り付けして、
- 一部の数字を手で書き換えて
なんてなことをやっていませんか？
このようなやり方だと、
- Excelレポートの中の一部の値を更新し忘れてたり、
- せっかく苦労して作ったセルの計算式をうっかり壊してしまったり、
- 前任者から引き継いだExcelレポートの計算式が複雑すぎてメンテできない、
といった問題が絶えません。Excelデータと格闘して一日が終わる、という笑えないことになりかねません。
データ更新の必要なシートを切り出す
一つのExcelレポートを作成するために、通常、複数のデータを参照し集計する必要があります。
よくありがちなExcelレポートの問題は、提出するレポートの表の中のセルを直接手で値を更新しなければいけない、というものです。表の中のあっちこっちを更新しなければいけないとなると、作業も煩雑になり、更新漏れや間違って別のセルを書き換えてしまうというミスがおきます。
そこで、データを更新しなければいけないものを切り出して、データ入力のための専用のシートに分けましょう。
このときできれば、データソースごと・種別ごとに別々のシートにしてしまうのがオススメです。無理に一つのシートにいろいろなデータを混在させるのは混乱の元です。
データの更新で注意すべき事項や操作方法についてのメモをこのシートの余白に書いておくようにしましょう。あとで、ほかの人に作業を引き継ぐのがとても楽になります。
データの集計処理を1つのシートにまとめる
データの集計処理も別シートに切り出しましょう。
提出用のレポートの表の中で複雑な計算をすると、表示内容や位置を変更するたびに計算式を修正しなければならなくなってしまいます。
データ入力シートからレポートに作成に必要な集計処理を行い、レポートの表示に必要な集計をすべてこの集計シートの中で行います。
ポイントはデータ入力シートを更新するだけで、すべての集計が自動で計算式の中だけで行われるように組み立てることです。データを手作業でピボットテーブルに組むなどの作業が必要ないように、計算式だけで処理できるようにします。どうしても計算式だけで処理できない手作業が発生する場合は、VBAでマクロにします。
このようにしておくことで、不用意に計算式を壊してしまうなどのミスを最小限にすることができます。
最後に報告用のシートを作る
報告用のレポートシートは、これまで作ってきたデータ入力シートや集計シートで計算した内容を元に、見やすくレイアウトを作っていきます。レポートの中は、集計シートなどで計算した結果を参照するだけにして、極力レポートの中では計算はしないようにしましょう。
このようにすることで、データの更新も簡単になり、あとからレポートの体裁を修正するのも簡単になります。また、レポートの元データが同じExcelファイル内に残っていることから、集計結果の検算や別のレポートへの加工をする際に、元データのありかを探したりする必要もなくなります。