エクセル 【Excel】で営業日数を出して、その日に対応する日割り計算をする方法！

エクセルでは、各企業で異なる営業日数を計算する関数はありません。

でもアイデア次第で解決できます！

現場では「朝の会議で各課の実績の進捗状況を知りたい。」という依頼をよく受けました。

【進捗状況】というのは予算を日割りで導きだすということを意味しています。

そしてそれを自動で行うことが重要です。

エクセルでは、期日を求めるWORKDAY関数や期間内の営業日数を出すNETWORKDAYS関数があります。

この２つは、土日は計算除外しますが別表で祭日の一覧を作成する必要があります。

さらに困った事には、企業によっては土日祝が休みではないのです。

「祭日の一覧を作る」⇒「営業日の一覧を作る」ことによって

TODAY関数とVLOOKUP関数を使ってその日の営業日を確定します。

なじみのある関数なので分かりやすいと思います。

作業工程は、次の２点です。

①営業日の一覧を作ってそこに TODAY関数とVLOOKUP関数 を組み込む。

②集計表に計算式を入れる。

１.営業日の一覧を作り TODAY関数とVLOOKUP関数を入れる

集計表の右に「現在」「営業日数」「営業日」の項目で表を作りました。

J列の「9月1日」「9月2日」などをあらかじめ入力します。

ここでK列の営業日数の数字を下まで入れる”小技”を紹介します。

１.TODAY関数を入力する

J2のセルには、=TODAY()という関数を入力します。

お詫び
最初にあげた記事では、TODAY()-1としておりましたが、VLOOKUP関数で対応日が無い場合エラーとなりますので、
VLOOKUP関数―1に変更しました。
会社が休日の日に出社して、作ったファイルを開くとエラーが出ます。

２.VLOOKUP関数を入力する

さあここが重要です。

XLOOKUP関数も実装されましたが、Office 365ユーザーにしか利用できないのでVLOOKUP関数を使います。

VLOOKUP関数の構文は、

=VLOOKUP(検索値,範囲,列番号,検索の型)です。

具体的には、

=VLOOKUP(J2,J3:K33,2,FALSE)-1

範囲を多めに取っている理由は、月が替わってこのsheetをコピーして使う場合を考えて31行にしました。

31日を超える月はありませんので。

式の最後のFALSE（完全一致）を忘れないで入力してください。

【実践テクニック】

「－1を入れる理由」

現場では、当日の実績数字が確定するのはその日の遅くか翌日の朝です。

そのため予算も前日の予算を計算します。

K2のセルの書式設定を「数値」に変更してください。

TODAY関数を検索値にしているので、計算結果には影響はないのですが日付が表示されるので。

３.COUNTA関数も入れてみる

L2に　=COUNTA(J3:J33)　という式を入れて空白でないセルの個数を数えさせます。

これも31日まであるかもしれないということで、多めに設定しました。

31日対応するので、他の月にSheetをコピーしても使えます。

２.集計表に計算式を入れる

さてここからは、算数の世界になります。

１課のA部門「日割達成率」は、

「A部門9月実績 ÷ ( 「A部門9月予算 ÷ 「営業日 × 「営業日数」）です。

「営業日 と「営業日数」 は、$を付けて固定です。

範囲指定をしてF4キーで絶対値になります。

これをE列、H列にコピーして「数式だけ貼り付け」て完成です。

エクセルを使う時は、なんとなく作り始めてはいけません。

必ず設計図を書いてから、計画的に作りましょう。