プログラマが考える劇的に効率が上がるExcelシートの作り方

そこそこの複雑な計算をするのにExcelはものすごく便利です。何かのプログラミング言語を使って書いたら1時間くらいかかるような計算が、ものの数分でシート上にできあがります。
この素晴らしいExcelを、より使いやすくするワザを、プログラマ的な視点から書きたいと思います。

拡張性のないシート

そういうわけで、Excelは素晴らしいんですが、使う人によってはなんとも「拡張性のないシート」ができあがります。
僕はプログラマなので、こういう素晴らしいツールを使いはじめると、どうしても拡張性とか保守性みたいなものが気になってしまいます。プログラマは同じことを繰り返すのが苦手です。シートにデータを入力してから完成形ができあがるまでに人手を使うことを避けて、全部を自動化したくなります。
次のようなことをしていたら、Excelを正しく使えていないような気がします。

• 途中で電卓を使って計算してセルを埋めている
• あるセルで計算した結果を他のセルにコピペしている
• 手順通りにデータを埋めていかないと途中で値に不整合が起きてしまう
• 毎月、月末に同じ体裁の報告書を数時間かけて作っている

ただ、マクロやプログラミング言語を使うのは難しいので、ここではあくまでExcelのシート上でできることを考えていきます。

DOVパターン

拡張性が高くて、必要な処理は全部Excelがやってくれて、誰がデータを入れてもすぐに結果が見えたら、嬉しいです。
保守性が高いものを作るために、プログラマは要素を分割して、責任をわけます。ぼくはExcelを「3つのシート」にわけようと思います。
そういえばExcelで新規ファイルを作成すると、空のシートが3つ入っていたりしませんか？

これは、「入力」「演算」「表示」の3つに分けるためのものなのだと、最近考えるようになりました。

• 入力(Data)
• 演算(Operation)
• 表示(View)

この3つに分けるので「Data-Operation-Viewパターン」、頭文字をとって「DOVパターン」とかっこつけて名づけます。「ドブパターン」です。かっこわるいです。

「入力シート」には生のデータを入力してもらう

「入力シート」の責任は何かというと「データの入力を受け付けること」です。それ以外のことはしません。
たとえば、毎日の売上と費用を入力していきます。

お店の売上を店長さんが毎日埋めていくイメージです。
ここでは、計算などはせずに、「入力してもらうこと」に集中します。入力のセルと計算のセルが一緒になっていると、「間違えて計算セルを書き換えてしまいました」というようなことが起こりますので、ここでは計算はしません。

「演算シート」で複雑な計算をする

「演算シート」の責任は、「入力シートのデータを計算して必要な値を得ること」です。それ以外のことはしません。
ここでは、毎日の売上と費用から、月間の売上と費用の合計を求めて、更に利益や利益率を求めています。

このシートを作るのはExcelの腕の見せ所なのですが、関数などをうまく組み合わせれば、かなり複雑な計算もできます。
入力シートと行ったり来たりするのは面倒なので、先に入力シートへの参照を作ってしまうと楽です。上の図では、左の3列が入力シートへの参照になっています。

「表示シート」で綺麗にまとめる

「表示シート」の責任は、「演算シートで得た値を綺麗に表示すること」です。
このシートが綺麗かどうかは置いておいて、年間の収支報告書みたいなものができます。

Excelでこういうシートを作るときに、月を横方向に並べることが多いようです。横方向にデータを並べると、Excelでは著しく扱いづらくなるのですが、このシートではこれ以上の計算をしないので問題になりません。計算をするのは「演算シート」で、こちらでは縦に並べることができるからです。
小さくて見えないですが、3ヶ月ごとに四半期の合計なども表示させています。月が並んでいる間に、月でないものが混ざってくると、Excelの計算式を書くのが面倒になりますが、これも計算は「演算シート」で全部終わっているので、問題になりません。

まとめ

こんな感じにExcelのシートを「入力」「演算」「表示」に分けると、とても扱いやすく、最終的に見やすいものができます。
このシートでは、店長さんが「入力シート」に毎日の売上と費用を入力すると、その瞬間に「演算シート」が計算をして、「表示シート」にはきれいな収支報告書ができあがっています。
ありがちな「データ入力中に計算式を上書きしてしまった」「見た目を整えるためにデータが横に並んでいて計算しづらい」「見た目を変更しようと思って計算式がおかしくなってしまった」などの問題もなくなり幸せになれると思います。