▼ この記事の内容
Excelで月次の予実管理表を作るなら、A1〜F1に列名を入れます。差額と達成率の式を入れ、入力欄と計算欄を分けます。売上が未確定の月と、0円で確定した月を区別しましょう。締め後は確定実績を写し、元の集計表と金額を見比べます。
Excelでは、営業チームの月次売上を1か月につき1行で管理します。まず列を作り、差額と達成率の式を入れます。次に入力欄を決め、締め後に確定実績を写して元の集計表と見比べます。
実績が未確定の月は空欄にし、0円で確定した月は数値の0を入れます。月次更新では、確定した金額をどの表から写したかも残します。
まずはExcelを開き、A1〜F1に列名を入れましょう。
Excelで月次の予実管理表に必要な列をそろえる
営業チームの月次売上は、1か月につき1行で管理します。列を作り、数式を入れ、入力欄を決めてから、締め後の転記と照合に進みます。まずはA1〜F1に見出しを入れましょう。
A1〜F1に予実管理表の列名を入力する
Excelの1行目に、対象月・予算・確定実績・差額・達成率・転記元と確認メモの6列を作ります。A〜C列とF列は手で入力します。D列とE列は数式用に空けておき、月ごとのデータは2行目から入れます。
A列に対象月、B列にその月の予算を入れます。C列には、同じチームの同じ月に確定した売上を入れます。予算と確定実績は円単位にそろえます。
D列には、確定実績から予算を引いた差額を表示します。E列には、予算に対する確定実績の割合を表示します。どちらも、同じ行の予算と確定実績を使います。
F列には、実績を写した集計表と照合の記録を残します。下のひな型に沿って見出しを入れたら、2行目に対象月と予算を入れます。
| A1 対象月 | B1 予算（円） | C1 確定実績（円） | D1 差額（円） | E1 達成率（%） | F1 転記元・確認メモ |
|---|---|---|---|---|---|
2行目に対象月と予算額を入力する
2行目は、1チームの1か月分に使います。複数のチームを扱う場合は、チームごとにシートを分け、シート名にチーム名を入れます。
A2に対象月を入れ、B2にそのチームの月次予算を入れます。B2には「円」の文字を付けず、金額を数値で入力します。担当者と表を開くときは「今月分はこの行に入れます」と伝えます。
締め前はC2を空けておきます。月の途中で売上の見込みが出ても、C2に入力するのは締め後に確定した金額です。
締め後に使う売上集計表が決まっていれば、F2に「転記予定」と添えて表の名前を書きます。対象月と予算を入れたら、D2とE2に数式を入れます。
差額と達成率を計算し、未入力と0円を区別する
D2に差額の式、E2に達成率の式を入れます。E列をパーセント表示にしたら、実績が空欄の場合と0円の場合を試します。
D2とE2に差額と達成率の数式を入力する
差額も達成率も、予算のB2と確定実績のC2を参照します。式のCOUNT(B2:C2)=2で、2つのセルに数値が入っているかを確かめます。
D2に=IF(COUNT(B2:C2)=2,C2-B2,"")を入力します。差額は確定実績から予算を引くので、予算を下回るとマイナスになります。
E2に=IF(AND(COUNT(B2:C2)=2,B2>0),C2/B2,"")を入力します。B2が0より大きいときに、確定実績を予算で割ります。E列の表示形式は「パーセンテージ」に設定します。
| セル | 何を入れるか |
|---|---|
| A2 対象月 | 入力欄／4月 |
| B2 予算（円） | 入力欄／1,000,000 |
| C2 確定実績（円） | 入力欄／800,000 |
| D2 差額（円） | 計算欄／=IF(COUNT(B2:C2)=2,C2-B2,"")／表示 −200,000 |
| E2 達成率（%） | 計算欄／=IF(AND(COUNT(B2:C2)=2,B2>0),C2/B2,"")／表示 80% |
| F2 転記元・確認メモ | 入力欄／4月売上集計表・営業Aチームの月合計 |
数字は説明のための仮の例です。A2に4月、B2に100万円、C2に80万円を入れ、F2に転記元を書きます。D2には差額の−20万円、E2には達成率の80%が表示されます。
実績が未入力・0円・入力済みの場合の表示を確認する
予算が100万円でも、C2が空欄なら差額と達成率は空欄です。元の集計表が届くまで、この表示のままにしておきます。
同じ予算で、確定実績が0円ならC2に数値の0を入れます。D2には−100万円、E2には0%が表示されます。
予算が0円で確定実績が80万円なら、D2は80万円です。予算が0円のときは、E2の達成率を空欄のままにします。
翌月分を3行目に作るときは、D2とE2の数式をD3とE3にコピーします。数式バーで、どちらも3行目のB3とC3を参照しているか確かめます。
入力するセルを定め、数式を誤って変更しないよう保護する
月次更新で使う入力欄と、数式を置く計算欄を分けます。先に予算と実績に入れる値を決めてから、数式のあるセルを保護します。
予算欄と実績欄に入力する値を決める
入力規則は、見出しを除いたB列とC列の入力範囲に設定します。Microsoft Supportの手順では、入力規則でセルに入れる値の種類や範囲を指定できます。[1]
予算と実績が0円以上になる表では、円単位の整数を受け付けます。「データ」から「データの入力規則」を開き、整数で0以上を指定します。実績が未確定の行は空欄を許し、エラーメッセージのスタイルは「停止」にします。返品などで月の確定実績がマイナスになる運用では、実績欄の下限を集計ルールに合わせます。
設定後は、実績欄に数値の0と「未確定」の文字を直接入力して試します。0が入り、文字は「停止」で受け付けないことを確かめます。試した値は消し、実際に金額を貼り付けた月は元の集計表と照合します。
参考：セルにデータの入力規則を適用する｜Microsoft Support
入力欄と計算欄を分け、数式を残す
毎月書き換えるのはA〜C列とF列です。保護をかける前に、使う月の行を用意し、D列とE列に数式をコピーしておきます。
Microsoft Supportの手順では、編集を許可するセルのロックを外してから、シートを保護します。[2] A〜C列とF列の入力セルを選び、「セルの書式設定」の「保護」で「ロック」を外します。次に「校閲」から「シートの保護」を選びます。
保護後は、入力欄に書き込めて、D列とE列の数式は上書きできないことを確かめます。用意した行を使い切ったら、表の管理者が保護を外して行と数式を足し、再び保護します。引き継ぎでは「更新するのは入力欄です」と伝えます。
参考：ワークシートを保護する｜Microsoft Support
月次更新で実績を転記し、元の集計表と照合する
月次締めが済んだら、対象月とチームが同じ集計表から確定実績を写します。転記後は元表の合計と照らし、差額と達成率の式が当月の行を見ているか確かめます。
締め後に確定した実績だけをC列へ入力する
更新する月の行を用意し、A列に対象月、B列にその月の予算を入れます。前月の行を元にする場合は、前月の実績やメモを新しい行に残さないよう、C列とF列を空けます。
次に、対象月とチームが一致する売上集計表を開きます。担当営業には、どの表のどの行が月の確定合計かを聞きます。
月の売上が確定していれば、該当行の合計をC列に写します。集計表が未着の場合や、合計が見込みの段階では、C列を空けておきます。
次は、月次締め後に営業マネージャーと担当営業が、対象月の行と売上集計表を見ながら話す作例です。集計表がそろっているかで、次の作業とF列に残す内容を変えます。
確定済みの場合
上司「今月の確定売上は、元の集計表のどの行ですか？」
担当営業「このチームの月合計です。」
上司「C列に写してください。F列には元表と、照合した人と日付を残してください。」
担当営業「C列に写して、照合してからF列に書きます。」
集計表が未着の場合
上司「今月の確定売上は、元の集計表のどの行ですか？」
担当営業「集計表がまだ届いていません。」
上司「C列は空けて、F列に誰にいつまでに聞くかを書いてください。」
担当営業「確認先と期限を書きます。届いたら対象月と合計を確かめます。」
元表の合計と数式の計算結果を照合して記録する
まず対象月、チーム、金額の単位を照らし合わせます。元表が万円単位なら円に換算し、月の合計がC列の金額と一致するか確かめます。
金額が違う場合は、元表で別の月やチームの行を拾っていないか見直します。合計の行と単位を確かめ、転記した値を直してから、もう1度照合します。
金額が合ったら、D列とE列が当月のB列とC列を参照しているか見ます。同じ行の予算と確定実績から差額と達成率を別に計算し、表示と照らし合わせます。
照合が済んだら、F列に元表の名前、参照した行、確認者、確認日を残します。翌月に別の担当者が引き継ぐときも、F列から照合に使った数字をたどれます。
月次更新を終えた表を会議に出すときは、どの差額から見るかも決めておきます。列の見せ方や、差額の理由と次の確認先の残し方は、予実管理表を見やすく整えて営業会議で使う方法を参考にしてください。
営業戦略・KPI設計 予実管理表を見やすくする作り方｜営業会議で確かめる列と売上の記入例
まとめ
Excelで月次売上を管理するときは、1か月につき1行を使います。列をそろえたら、D列とE列に差額と達成率の式を入れます。毎月書き換えるのは、A〜C列とF列です。
締め後は、同じ月とチームの確定実績をC列に写します。元の集計表と金額を見比べ、数式が当月の行を参照しているか確かめます。F列には元表の名前、参照した行、確認した人と日付を残します。
表を作るときは、まずA1〜F1に列名を入れましょう。
次に追う営業指標を決めたい方は、営業KPI設計ガイドをご覧ください。
