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

【Excel初心者向け】予実管理表は「会計データを貼るだけ」で実績が動く仕組みにできる

    この記事の結論

    予実管理(予算と実績を月次で突き合わせる管理手法)は、ExcelのINDEX+MATCHとOFFSETという3つの関数を組み合わせると、「会計ソフトから出力した試算表を貼り付けるだけで、実績列が自動更新される」表にできます。

    ポイントは1つだけです。「今どの月まで実績が確定しているか」を1つのセルで管理し、そのセルの値を基準に「実績で埋める範囲」と「予算で埋める範囲」の境界を毎月スライドさせること。この記事では、サンプルファイルで考え方と実際に使っている関数を解説します。

    対象読者は、Excelを勉強中の経理担当者・会計事務所スタッフの方です。関数の名前を覚えるよりも、「何をどうしたいを言語化する →合う関数を調べる」という考え方が上達の近道になります。


    1. 予実管理とは何か

    予実管理とは、あらかじめ立てた「予算」と、実際の月次決算から出てくる「実績」を並べて比較し、差異(ズレ)を早期に把握する管理業務です。経理担当者にとっての実務上の価値は、決算が締まった後にではなく、まだ実績が出ていない先の月についても「このままいくと年間でどうなりそうか」という着地見込みを早期確認し、対策の選択肢と対応期間を確保することにあります。

    この着地見込みを、サンプルファイルでは「実績+予算 見込」という列名で表現しています。この考え方の中身は3章で詳しく扱います。


    2. Excelの見た目を確認する

    サンプルファイルは2枚のシートで構成されています。

    • 「2026年 予実管理サンプル」シート:勘定科目ごとに、年間予算・月次の予算/実績/差異・累計・年間見込みを並べた本体の表

    • 「会計データ」シート:会計ソフトから出力した「残高試算表(年間推移)」をそのまま貼り付けたシート

    まず本体シートの左側部分を見てみます。


    画像
    。

    A列に勘定科目、B列に年間予算、C列に売上高に対する構成比、D〜E列に「実績+予算 見込」、F列以降に1月度から12月度までの「予算・実績・差異」が3列1組で12か月分並んでいます(実際のファイルではF〜AO列)。さらに右側には各月末時点の累計予算・累計実績・累計差異も同じ3列1組の形式で12か月分続きます(AP〜BY列)。この累計部分は画面には映っていませんが、後述する「見込み」の計算で使われている裏方の列です。

    シート左上のB2セルには「実績集計月(この月まで実績、それ以降は予算を採用)」という値が入っており、このサンプルでは 8(8月)です。これが、この表全体の「今どこまで実績が確定しているか」を表す基準セルです。


    3. 会計データを貼ると実績が自動更新される仕組み

    3-1. 「会計データ」シートの中身


    画像

    このシートは、弥生会計の「残高試算表(年間推移)」をExcel出力してそのままコピペするシートです。

    月次決算が締まるたびに、この「会計データ」シートをコピペで上書きします。

    3-2. 科目名と月度が一致するデータを引っ張ってくる

    本体シートの「実績」列には、次のような数式が入っています(1月度・給料手当のセルを例にします)。

    =INDEX(会計データ!B9:R200, MATCH(A16, 会計データ!A9:A200, 0), MATCH(F4, 会計データ!B8:R8, 0))

    言葉で分解すると、やっていることは3ステップです。

    1. MATCH(A16, 会計データ!A9:A200, 0) … 本体シートのA16セル(=「給料手当」)が、会計データシートのA列の中の何行目にあるかを探す

    2. MATCH(F4, 会計データ!B8:R8, 0) … 本体シートのF4セル(=「1月度」)が、会計データシートの8行目の見出しの中の何列目にあるかを探す

    3. INDEX(会計データ!B9:R200, ①の行, ②の列) … ①と②で特定した「行と列の交差点」の値を取り出す


    画像

    「勘定科目名」と「月度」という2つの見出しが両方のシートで一致していると、その交差点の数字を自動で探し出して表示します。弥生会計に修正が入っても、会計データシートにコピペすればどの数字が変わったかを人間が忘れてしまっても、実績値は自動的に更新されます。



    4. 「実績+予算 見込」の考え方(この記事のポイント)

    4-1. まず言葉で整理する

    D列の「実績+予算 見込」は、次のルールで作られています。

    実績集計月(B2セル)までの月は「実績」を使い、それより後の月は「予算」を使って、年間の着地見込みを合計する

    具体例で確認します。実績集計月が 4月 のとき、

    • 1月〜4月:実績を合計

    • 5月〜12月:予算を合計

    • 見込み = (1〜4月の実績合計)+(5〜12月の予算合計)

    翌月になり、実績集計月が 5月 に進むと、

    • 1月〜5月:実績を合計

    • 6月〜12月:予算を合計

    • 見込み = (1〜5月の実績合計)+(6〜12月の予算合計)


    画像

    月が1つ進むと「実績側に組み込む月」が1か月増え、「予算側に残る月」が1か月減る、このルールは何月になっても変わることがなく、変わるのは「どこまでが実績か」という境界の位置だけです。

    4-2. Excel上の「位置」でこれをどう表現するか

    ここが今回のサンプルファイルの作りの肝です。本体シートの右側(AP〜BY列)には、各月末時点の「累計予算」「累計実績」を月ごとに3列1組で12か月分、あらかじめ計算済みの列として持たせてあります(例:AP・AQ列=1月末累計、AS・AT列=2月末累計…BW・BX列=12月末累計)。

    「実績集計月が何月か」によって、この12個の累計ブロックのうち「どのブロックを見ればよいか」が変わります。この「見るべきブロックを選ぶ」処理に使われているのがOFFSET関数です。


    画像

    先ほどのB2セル(実績集計月=8)と、それによって決まる国内売上のD7セル(実績+予算 見込=42,384,000)の対応関係を示したのが上の画像です。国内売上の「実績+予算 見込」(D7セル)には、実際に次の数式が入っています。

    =OFFSET(AQ7,0,3*($B$2-1))+(B7-OFFSET(AP7,0,3*($B$2-1)))

    これも3つの要素に分けて読みます。

    • OFFSET(AQ7,0,3($B$2-1))* … 「1月末累計実績」の列(AQ列)を基準に、右へ 3×(B2−1) 列分ずらした位置の値を取る。累計ブロックは3列1組なので、1か月進むごとに3列ずつ右へずれる計算になっている。B2=8(8月)なら 3×(8−1)=21列右へずれ、ちょうど「8月末累計実績」の列にたどり着く

    • OFFSET(AP7,0,3($B$2-1))* … 同じ考え方で、「1月末累計予算」の列(AP列)を基準に同じ列数だけずらし、「8月末累計予算」を取る

    • B7 − (8月末累計予算) … 年間予算の総額から、8月までの累計予算を引くことで「9月〜12月の予算残り」を算出する

    最後にこの2つ、「8月末累計実績」と「9月〜12月の予算残り」を足せば、見込みが完成します。

    言語化した4-1のルールと見比べると:実績集計月が4月から5月に進んでも、この数式自体は1文字も変わりません。変わるのはB2セルの値(4→5)だけで、その結果としてOFFSETが指し示す列が自動的に3列右(=1か月分)にずれる、という仕組みです。つまり「1つの固定されたルールの中で、参照位置だけがB2の値に連動してスライドする」というのが正確な言い方になります。


     
     
    上場企業の財務部を経て、中小企業向けの会計ソフトや販売管理ソフト・建設業ソフトの導入コンサルをずっとやっておりました。現在は会計事務所で記帳や決算申告補助と経理DXの導入支援をしています。経理担当者が明日から使えるExcel・AI・業務改善のコツを書きます。

    あなたへのおすすめ