メインコンテンツへスキップ
見出し画像

予算実績比較表をExcelで自動化。INDEX×MATCHとSUMPRODUCTで、対象月を変えるだけで当月・累計の予実と要確認の科目を出す方法

    1. 予算と実績の比較、毎月コピーして作り直していませんか

    月次決算が終わったら、試算表の数字を予算と並べて、差異の大きい科目を報告する。経理や管理部門では、毎月の定番の作業です。

    予算の表から当月の列をコピーし、試算表から実績を転記して、差額と達成率を計算する。累計も出すとなると、4月から当月までを足し上げる式を毎月作り直すことになります。

    科目が増えたり、月がずれたりすると、どこかで貼り付け位置を間違えて、別の月の予算と比べてしまうこともあります。

    会計事務所にいたころ、関与先に出した予実比較で、予算の列を1か月ずらして貼り付けていたことに、社長からの質問で気づいたことがあります。

    この記事では、予算と実績を「科目×月」の表で持っておき、「対象月」を変えるだけで、当月・累計の予算と実績、差異、達成率、要確認の判定までを自動で出す方法を紹介します。

    使うのは、INDEX、MATCH、SUMPRODUCTといった関数です。どれも、古いバージョンのExcelでも使えます。

    2. 予実比較が毎月の手作業になる原因

    原因①:当月の列を、毎月コピーで取り出している

    予算の表から当月の列を探してコピーし、比較表に貼る。この作業を毎月していると、月を間違えたり、科目の並びがずれたりします。

    原因②:累計の式を、毎月作り直している

    4月から当月までの合計は、月が進むたびに足す範囲が1列ずつ広がります。式を毎月直していると、範囲の直し忘れが起こります。

    原因③:「差異が大きい」の基準が人によって違う

    どのくらいの差なら報告するのか、収益と費用で差異の向きをどう読むのか。基準が決まっていないと、毎月の確認の目線がぶれます。

    費用の差異がマイナスなのを「悪い」と読み違えて、予算より少なく済んでいた科目を要確認に入れてしまったことがあります。

    つまり、予実比較が毎月の手作業になるのは、科目が多いからではなく、「どの月を見るか」「どこまで足すか」「何を要確認とするか」が、式になっていないからです。

    次の章では、対象月を1つ変えるだけで、当月と累計の予実比較が自動で更新される仕組みを作ります。

     
     
    「毎月同じ作業に追われている」事務・経理の方へ。会計事務所で5年、不動産会社の経理で3年。日々の仕訳から決算、給与計算まで見てきた経験をもとに、関数で作業を仕組みにする方法を書いています。XLOOKUP、SUMIFS、COUNTIFSなど、実務で使える形で紹介します。

    あなたへのおすすめ