【Excel】月次レポートの更新が面倒くさいので数式を追加した

会社でずっと疑問に思っていたこと。毎月のように会議で、上の人達が「資料のレビューをきちんとするようにしてね」と必ずコメントして会議を締めること。

なぜ、このやり取りは繰り返されるのでしょう?

正解は、毎月、ミスが見つかるからです。

作成補助にあたっている同僚の仕事をここ数ヶ月見てきたけど、結構しっかりしている感じだし、不思議だなー、と思っていました。

今回、レポートの一部作成を引き継ぐことになって原因が見えてきました。

構成が練られていない、行きあたりばったりレポート

以前の会社でも、汚いエクセルを引き継いだことはあったので、多少の免疫はついていましたが、久しぶりの遭遇に閉口。

データの置き方もさることながら、色がたくさん使われすぎてて、目がチカチカする。こんなファイル使いたくないっ!!!

ヒアリングしてみたところ、どうやら、上司が作成したものらしかった。本人は至って満足しているようで、「それまでレポートの型がなかったのをオレが作った」と自慢しているので、このチカチカレポートは変えられない。(→変えてもいいけど、未来永劫ネチネチ文句を言われそう。)

そして、意味もなく、複製されている同じテーブルたち。(→これらに関しては、本人もおぼえてないだろうとバッサリ削除。3M近く軽くなった。)

こういう部分は、気分を萎えさせはしても、まだマシ。問題は、入力シートと計算シートが明らかにされていない、ということ。本当にひどいのは、左右上下にスクロールしてやっと出てくるデータ入力セル。

こんな自分勝手なファイル作ってよく自慢できたな、と反対に感心。ミスるのも当たり前です。

既存のフォーマットに数式を追加:INDEXとMATCH

1年分のデータを集計出来る表にして、グループ化などで、表示・非表示を変更すればいいのに、と思うのだけれど、なぜか、シートには半期分だけを集計する形式になっている。

ここがミスが生じやすいポイントの1つ。半期ごとに別シートから参照するよう数式を更新しているらしい。面倒くさい。せめて年1回の更新にしましょうよ。

そして、上司や同僚の頭には、数値は数式で参照、文字列はベタ打ち、というルールがあるのか、対象月や四半期などの表示をいちいちぜーんぶ書き換えているらしく、私にも同じことさせようとしてたのかと思っただけで吐き気。虐待に近い所業。

ということで、シートを2枚追加して、その作業はエクセルにお願いすることにしました。

ここで活躍するのは、INDEX関数とMATCH関数です。(個人的にはSUMPRODUCT関数が好きですが、INDEXとMATCHの方がよく知られているので。)これらを使うと、条件に合う行列指定して、対象セルの値を返すことができます。

数式の使い方の説明ではなく、数式を利用したラクする方法のシェアが目的なので、使い方は割愛します。

追加したシート:ルール用、計画用

ラクするためにシートを2枚追加したと書きましたが、追加したのは、ルール用と計画値用の2枚です。

ルール用

日本の会計年度の条件分岐って難しいです。CHOOSE関数を使ったりすることも考えましたが、表に考え方をまとめておいて、その表を参照する形にした方が、手順が見えやすいと思ったので、追加しました。

2020-05-12 01.33.49

ラベル用の表は、どの月までが実績で、どの月からが見通し値かを表します。(レポートの実績&見通しの部分に貼り付けられます。)

計画用

このブログ用に作ったブックでは、実績&見通しの表も入っていますが、本物のブックは計画値だけを集計したシートを作成しました。というのも、最初こそ、実績用のセルに計画値が入っているのですが、どんどん実績値に塗り替わっていくため、後日、別ブックからデータを取り出したりする手間が発生します。そのため、確定済み予算を別シートに集計したわけです。

2020-05-12 01.34.25

実績&見通しは元から集計用のシートが存在しており、毎月、データベースから抽出したデータを反映します。(ここについても、ラクするための手順を別途作成済み)

完成形|対象月を選ぶだけでレポート(の表示形式)を更新

文章では上手く説明できないので、下のGIFをご参照ください。

200512_budgetRep

対象となる会計月を選択すると、レポートの値が、対象付きの含まれる半期のデータに変更されます。

2020-05-12 01.34.52

実績・見通しのラベルも変更されます。ここでは、変化が分かるように実績のデータ列にハイライトを入れてみました。(実際のレポートはハイライトなしです。)

2020-05-12 01.34.06

元ファイルの作りの理解から、数式の動作確認などを含めて半日ちょっとかけてしまいましたが、これでミスの可能性が減るのであれば、いいのかなと。(とはいえ、まだまだ修正すべき箇所はあります。)