Excel 勤務時間計算

Question

度々お世話になっております。

現在、Excelで会社の出勤日数と月の合計勤務時間を計算する表を作成しております。

今は、DAY関数とEOMONTH関数を使い日数を取得できています。
やりたいことは

1. B4セルはプルダウンから年を選択。（2018~2022)
2. 週5勤務者と週4勤務者がいるため。B5セルでフルタイムか毎週の休みの曜日を選択（月曜～金曜）
3. D5~D16セルに対象年月の土日祝日を除いた勤務日数を表示。（週5勤務者の場合）
4. D5~D16セルに対象年月の土日祝日毎週休みの日を除いた勤務日数を表示。（週4勤務者の場合）
5. E5E16セルにD5D16の勤務日数×8hの時間を算出。

祝日はSheet2に2018年から2022年までの祝日を表にして入れています。

※有給休暇や半休などは考慮しません。土日祝祭日+週4日勤務者の毎週の固定休みの曜日だけを考慮します。

以上のことを行いたいと考えています。
1.,2.に関してはできているのですが、選択された年の値を呼ぶことが出来ません。
日数の計算はDAY関数とEOMONTH関数を用いて2017年の月の日数を表示はできるのですが、ここから土日祝日を引くという作業のやり方で詰まっています。

以下追記
週4及びフルタイムの時の日数をセルに出力しました。

最終的に時間（E列）に算出するためには
1.年選択から年を選択。
2.勤務形態から曜日を選択
3.上記のイメージの表に行き、月曜休みであれば月曜日の列のセルの日数を計算して時間を算出する。

E列の時間の算出の式はどのようにしたらよいのでしょうか？
休みの曜日によってとってくる値が異なりますので、VBAでIF文を回すのでしょうか？
よろしくお願いいたします。

よろしくお願いいたします。

Answer 1

日付を取得する

年・月・日を指定して日付型にするDate関数を使えばできると思います。
Date関数は例えば=Date(2017,12,24)とすると2017/12/24という日付を返してくれます。
また=Date(2017,12 + 1,1)-1のように記述すると2017/13/01を2018/01/01と認識し、そこから1日引いた2017/12/31を返してくれるという、割と無茶も聞いてくれる関数です。

しかし、年・月・日はそれぞれ数値で指定する必要があるため、プルダウンリストの作り方や月の入力でもこれを考慮してあげる必要があります。

まず年選択のプルダウンリストについて。
これは2018～2022の範囲とのことですが、このとおりの数値を選択させているのなら大丈夫です。
もし"2018年"～"2022年"のように数値以外の文字列を含んでいるようでしたらそのままでは利用できません。
この場合、リストの選択値は2018～2022の数値としてください。
後ろに"年"を付けたい場合は、セルの書式設定を####"年"とすればセル上には2018年のように表示されます。

次に月の入力です。
これは提示いただいたイメージでは"4月"、"5月"のように文字列で入力されているように見えます。
これもセルへの入力は数値の4だけを入力し、書式設定で##"月"とすればよいでしょう。

あとは=DATE($B$4, $C5, 1)のように記述すればとすれば2017/4/1といった日付が取得できます。

祝日について

上記で各月の期間開始日と終了日(1日と末日)をつくり出すことはできそうです。

次に解決しなければならないのは日数計算です。
しかしこれには祝日を把握する必要があります。

日本の祝日は日曜日と重なった場合翌日に繰り越したりしますし、春分の日や秋分の日などは天文学から日付が決まるので固定日ではありませんので、毎年同じ日付が祝日にはなりません。
この祝日の日付を一覧にしたデータをどこかに用意しておく必要があるのです。

自分でこれを作るのは大変なので「excel　祝日一覧」といったキーワードでweb検索すると有志が作ってくれたものが見つかります。
私はよくこちらのサイトさんの情報を使わせていただいています。
⇒日本の祝日一覧｜Excelリファレンス - エクセルの神髄

日数の算出

こうして祝日一覧が用意できれば、土日祝を除いた日数を取るのは簡単です。

まずフルタイム（週５勤務）の場合、ワークシート関数にNetworkDays関数というものが使えます。
これは期間を入力すると土日を除いた日数を返してくれるというものです。
さらに祝日テーブルを指定すると、土日祝を除いた日数を返してくれるのです。

例えば祝日一覧シートのA列に2017年の祝日を列記しておき、=NetworkDays("2017/11/1", "2017/11/30", 祝日一覧!A$1:A$50)のような式を記述すると、全30日から土日祝を除いた20日という日数を返してくれます。

もうひとつの週４勤務の方はNetworkDays.INTL関数というものが使えます。
これは先のNetworkDays関数を拡張したような関数で、指定の曜日を休日として扱うことができます。
もちろん祝日テーブルの指定も可能です。

休日とする曜日の指定は、あらかじめ用意されたパターンにあるものなら数値指定もできますが、週休３日の場合は文字列での指定となります。
休日は"1"、休日でなければ"0"の文字を月火水・・・日の順で並べた文字列が休日パターンの文字列となります。
（例えば木・土・日が休日なら"0001011"となります。）

これをNetworkDays.INTL関数の第3引数として指定します。
例えば=NetworkDays.INTL("2017/11/1", "2017/11/30","0001011", 祝日一覧!A$1:A$50)のような式を記述すると、全30日から木土日祝を除いた16日という日数を返してくれます。
ちなみに=NetworkDays.INTL("2017/11/1", "2017/11/30","0000011", 祝日一覧!A$1:A$50)のような式を記述すると、全30日から土日祝を除いた20日という日数を返してくれますので、フルタイム用としても使用可能です。

---

祝日カレンダーの作成や、休日パターン文字列の作成など、少し面倒な部分がありますががんばってみてください。
参考になれば幸いです。

Comments

[jawa]

回答の記載中に追記があったようで、見落としておりました。
祝日テーブルは作成済みだったようですね。

あとは休日パターン文字列の作成ですが、例えば勤務形態の入力がリストからの選択となっている場合、
リスト候補の文字列の後ろに
"フルタイム 　　　　　　　　0000011"
"月曜日　　　　　　　　　　1000011"
"火曜日　　　　　　　　　　0100011"
～（略）～
"金曜日　　　　　　　　　　0000111"
といった具合にパターン文字列も用意しておく方法があります。
この場合、リストを開いたときにパターン文字列が見えないよう、リスト表示する文字列と休日パターン文字列の間に十分な空白を用意してあげるのが肝です。

使うときにはこのリストセルの値から`=Right($B$5, 7)`のように後ろ７文字を切り出してあげればパターン文字列が取得できます。

一つの方法としてご参考まで。

[cayribaka]

詳しい回答ありがとう御座います。
日数の取得についてわからない点があるのですが、
=DATE($B$4, $C5, 1)で日付を取得することはできるのですが、ここから日数の取得はどのようにしたらいいのでしょうか？

[jawa]

日付を取得することができて、祝日カレンダーもできているならあと少しで完成です。

回答の「日数の算出」という見出しのところでも説明させていただきましたが、日数の算出にはNetworkDays関数やNetworkDays.INTL関数が使えます。

使用方法はそれぞれ
`=NetworkDays(期間の開始日,期間の終了日,[祝日テーブル])`
`=NetworkDays.INTL(期間の開始日,期間の終了日,休日パターン,[祝日テーブル])`
です。

ここで期間の開始日は各月の1日なので、例えば`=DATE($B$4, $C5, 1)`のように日付を1に指定します。
期間の終了日は各月の末日です。
EOMONTHを使って`=EOMONTH(DATE($B$4, $C5, 1), 0)`という方法でも取得できますし、回答中でご紹介したように「翌月１日の１日前」という考え方で`=DATE($B$4, $C5 + 1, 1 - 1)`という方法でも取得できます。

この開始日・終了日をセル上に用意して参照してもいいですし、NetworkDays関数に直接記述しても大丈夫です。
`=NetworkDays(DATE($B$4, $C5, 1), EOMONTH(DATE($B$4, $C5, 1), 0), Sheet2!$A$2:$A$999)`

[cayribaka]

すみません。ひとつ上の質問（2017/11/09 10:09）ですが、日数の取得できました。
Sheet1の下の方のセルで開始日と終了日を表示させ、月の日数から土日を加味した勤務日数を出しました。週5は出せました。

[cayribaka]

開始日：=DATE($B$4,$C5,1)
終了日：=DATE($B$4,$C5+1,1)-1
とし、
週5勤務者出勤日：=NETWORKDAYS(B20,C20,Sheet2!A$2:A$89)
としました。Sheet2は祝日の表です。

[jawa]

別セルに開始日・終了日を用意するのであれば、各月ごとに開始日・終了日のセルが必要になりますので同じ行に用意した方がよさそうですね。
自分ならそんなセルを用意するのが煩わしいのでNetworkDays関数内に直接Date関数を記述してしまうと思いますが、ここらへんは好みの問題もありますので。。

フルタイム版ができたのならあとは休日パターンを作ってNetwork.INTL関数に載せ替えれば週４勤務もできそうですね。
この回答の１つめのコメントに書いた方法が使えるなら話は早そうです。

あとひといき、がんばってください。

[cayribaka]

別セルに開始日・終了日を用意するのであれば、各月ごとに開始日・終了日のセルが必要になりますので同じ行に用意した方がよさそうですね。
＞＞確かにそうですね。少し再考してみます。ありがとうございます。

フルタイム版ができたのならあとは休日パターンを作ってNetwork.INTL関数に載せ替えれば週４勤務もできそうですね。
この回答の１つめのコメントに書いた方法が使えるなら話は早そうです。
＞＞週4勤務が勤務形態のセルと連動させなくてはならないので大変です。

あとひといき、がんばってください。
＞＞あと何度か質問を投げるかもしれません。よろしくお願いいたします。

[cayribaka]

勤務日数：=NETWORKDAYS(DATE($B$4, $C5, 1), EOMONTH(DATE($B$4, $C5, 1), 0), Sheet2!$A$2:$A$89)
としてみたのですが、結果が"1900/1/20"と出力されます。

[cayribaka]

すみません。勤務日数：=NETWORKDAYS(DATE($B$4, $C5, 1), EOMONTH(DATE($B$4, $C5, 1), 0), Sheet2!$A$2:$A$89)
ちゃんと値が出力されました。何かの式が残ってしまっていたようです。

[jawa]

セルの書式が日付型になってしまったようですね。
式の結果は20という日数が取れていそうです。

[cayribaka]

そうでした。ご迷惑おかけいたしました。
ひとまず週5勤務者の勤務時間は出力できました。

[jawa]

週４勤務についてですが仰る通り、勤務形態のセルと連動し、休日パターン文字列を作る必要があります。
ちなみに私の考えでは、週５勤務でも週４勤務でもNetworkDays.INTL関数を利用すればいいと考えています。
以下のように、先ほど作成された勤務日数の式をNetworkdays.INTL関数に置き換えて、土日のみ休日としたパターン文字列を指定してあげれば同じ結果が得られるはずです。
`=NETWORKDAYS.INTL(DATE($B$4, $C5, 1), EOMONTH(DATE($B$4, $C5, 1), 0), "0000011",Sheet2!$A$2:$A$89)`

休日パターン文字列を作る方法はいくつかありますが、現状では勤務形態のセルはリスト選択ですか？
リスト選択でなければ少々ゴリ押しの方法になってしまうかもしれません。

リスト選択なら、その候補はどうなっているでしょうか？
特に、週５勤務の時に何が入力されているのかが気になります。

[cayribaka]

お付き合いいただきありがとうございます。感謝です。
勤務形態のセルは現状はリスト形式で上からフルタイム、月曜日、....　、金曜日が選択できるものとなっています。
そこから選択された曜日が休日扱いになることを想定しています。
週5勤務であればフルタイムを選択するので、リストの中での処理が変わってきてしまうことになりますね？
ExcelVBAer様が回答して下さっていることを考えると、休曜日と祝日が被った時の処理が大変そうです。

[cayribaka]

デフォルトで週5の勤務時間が表示されるようにして、週4の場合のみ、勤務形態からリストで休曜日を選択できるようにすれば少しシンプルになるでしょうか。

[jawa]

＞休曜日と祝日が被った時の処理が大変そう
NetworkDays関数もNetworkDays.INTL関数も、指定した休日と祝日がかぶっていいる日は１日としかカウントしないはずです。
一度、
=NetworkDays.INTL("2017/11/1","2017/11/30","0001011",Sheet2!$A$2:$A$89)
という式を試してみてください。
これは木曜日と土日を休日とし、祝日も考慮した日数を返す式です。

2017年11月は全30日のうち木曜日が5日、土日が8日、祝日が2日ありますが、11/23は木曜日と祝日が重なっているので実質的に祝日は1日となり、30-5-8-1=16日という日数が返されるはずです。

＞週5勤務であればフルタイムを選択するのでリストの中での処理が変わってきてしまう
この処理をわけるとなると、面倒な式をかかなければならなくなってしまいそうですが、そうしなくて済むように考えたのがこの回答の１つ目のコメントに記載した「リスト候補となる文字列の後ろに休日パターン文字列を付け加える方法」

リストは入力規則で設定していると思います。
そして現状では"フルタイム"とか"月曜日"などの選択候補が設定されていると思います。
これらの選択候補の文字列を、それぞれ"フルタイム　　　　0000011"や"月曜日　　　　　1000011"などのようにしてしまうのです。
リストボックスにはセル幅の文字までしか表示されませんので、表示したい文字と休日パターン文字列の間に十分な空白を挟んでおけば、パターン文字の部分はリストに表示されません。

それでも選択したセルの値には後ろの方にパターン文字列がくっついていますので、文字列を後ろから切り出すRight関数で最後の７文字だけを取得すれば労せずパターン文字列が取得できるという訳です。

いかがでしょうか？

[cayribaka]

確かに16日が返ってきました。
「リスト候補となる文字列の後ろに休日パターン文字列を付け加える方法」
＞＞リストをつくるときに文字列も一緒に入力すればいいのですね。
　　Right関数を使って取得してみます。ありがとうございます。

[cayribaka]

何度もすみません。休曜日を選択するリスト（文字列付）を作成したのですが、Right関数はどこで使えばいいのでしょうか？

[cayribaka]

週4日勤務：=NETWORKDAYS.INTL(DATE($B$4, $C5, 1), EOMONTH(DATE($B$4, $C5, 1), 0), Right(B5,7),Sheet2!$A$2:$A$89)
ということでしょうか？

[jawa]

そんな感じで大丈夫だと思います。
数式を行コピーすることを考えるとB5は＄B$5と固定したほうがいいですね。

[cayribaka]

週4日勤務：=NETWORKDAYS.INTL(DATE($B$4, $C5, 1), EOMONTH(DATE($B$4, $C5, 1), 0), Right($B$5,7),Sheet2!$A$2:$A$89)
で値を出力したのですが月曜日だけValueになってしまいます。他の曜日を設定すると16と出るのですが、どの曜日を選択しても16となってしまいます。

[jawa]

リストの右端からうまくパターン文字列をとれていないのかもしれませんね。

勤務形態をリストから選択した後、B5セルにカーソルをあてるとどんな内容になっていますか？
また、使っていないセルに`=Right($B$5,7)`という式を記述するとどんな結果が表示されますか？

[ExcelVBAer]

テーブル+vlookupではなく、リスト+スペース+値+rightにこだわるのは何故ですか？

また、質問者さんは、1つのセルに詰め込むのではなく、数式を小分けしてセルを分けた方が、どこでエラーになってるか分かりやすいかと。
後でおかしくなった、という時にも一目で原因が分かるようにもなりますので。

[cayribaka]

月曜日の右端にスペースが入っていてしまいパターン文字列がうまく取得できていませんでした。お騒がせいたしました。他の曜日も日数を取得できました。

[cayribaka]

@ExcelVBAer様
こだわっているわけではないのですが、リストのプルダウンの休曜日選択で選択した曜日を月曜日なら月曜日とPCに判断させるために文字列にしてその選択された文字列に応じて勤務日数を算出できればと考えています。
VLOOKUP関数でもやってみます。ありがとうございます。

[jawa]

私もリスト+スペース+値+rightにこだわっているわけではなく、一案としてご紹介していたつもりです。

また「1つのセルに詰め込まず小分けにした方がわかりやすい」というのは同感ですが、反面、「当初の予定にないセルやシートを強要したくない」という思いもあります。
これは少なからずデザインや操作性に影響を与えてしまうからです。

今回、勤務形態は
・選択候補が少ない
・候補が追加・変更される可能性も低く、マスタ化する必要性が薄い
・vlookupは左端列(検索キーワードの列)でソートされていなければならず、曜日順(リスト表示したい順)に並べた状態ではマスタにならない
⇒つまりリスト候補のマスタではなくvlookup専用のマスタを用意しなければならない
⇒それを回避するためにわざわざmatch＋index検索をするほどの内容でもない

強いていうならここらへんがvlookupではなくrightを勧めた理由です。
ちなみに勤務形態がリスト選択でなかった場合の「ゴリ押し」で考えていた方法もvlookupではなく条件文を考えていました。
候補は8項目しかないからです。本当にゴリ押しですけどね(^^;;

[cayribaka]

ありがとうござます。おかげさまで無事に動かすことが出来ました。
jawa様に提案して頂いたRight関数を用いて曜日を取得しました。
最終的に契約時間を出力するセルに式をまとめて書きました。
=(NETWORKDAYS.INTL(DATE($B$4, $C$5, 1), EOMONTH(DATE($B$4, $C$5, 1), 0), RIGHT(B5,7),祝祭日!$A$2:$A$94))*8

お二人ともありがとうござました。

Answer 2

まず、集計をする場合は見た目より、
集計しやすいデータの形にするのが鉄則です。

そして、提示の表を見るに、
「月」「日数」が２つに分かれているので、
１つにまとめる方が良いかと。
（通常、データは行方向へ追加するので、C列を「月」、D列を「日数」等にするのが適当でしょう）

で、本題ですが、最終目標は「勤務時間の集計」ですよね？

月の日数 ― 土日祝の日数 ｘ ８ｈ(？) だけでいいのですか？

会社の創立記念日、有給、病欠、災害等による休暇、は考慮不要ですか？

半日単位、時間単位の考慮は不要ですか？

目的によって設計が大きく変わりますので、
細かい条件の提示があれば、皆さんが答えやすくなります。

※正直、プログラムは不要です。
関数と日付の一覧等で完成できるでしょう。

Comments

[cayribaka]

回答ありがとうございます。情報不足でした。
有給、病欠、災害等による休暇は考慮しません。

但し、週4日勤務者がいますのでこれもプルダウンで月曜から金曜の中で毎週休みの日を選択し、（（月の日数）－（土日祝日＋週4の場合の休みの日数））＊8hで計算できればと考えています。

質問に追記いたします。

[ExcelVBAer]

週４日勤務者がいるのでしたら、
その方が「月曜」を休日として設定している場合に、
月曜の祝日とかぶったらどうなるのでしょう？

[cayribaka]

現状では祝日と毎週休みの日が区別できないので、祝日と休みが被ると本来であれば一日とカウントされるところが2日とカウントされしまいますので、被った場合の判定を行わなければなりません。

[ExcelVBAer]

ということは、
休曜日と祝日がかぶった場合でも、
振替等はなく週４日勤務、ということでしょうか？

[cayribaka]

そうです。
分かりにくいシステムですみません。

[ExcelVBAer]

NetWorkDaysで、祝日と休曜日を合わせて計算するのは
恐らく不可能ではないかと思います。
5日勤務だけならよかったのですがね。

勉強がてら作成してみましたが、
必要な項目は、
月 (今もシートにありますが念の為)
開始日（例：2018/4/1）DATE等
終了日（例：2018/4/30）EOMONTH等
平日日数 （例：20）NETWORKDAYS等
祝日日数 ※COUNTIFSで祝日一覧から、該当年月 and 土日＋休曜日を除外して抽出
※後で休曜日をカウントする際にかぶるので、先に除外しておく。
※COUNTIFの条件設定は、ご自分で調べてください。

そして、休曜日の計算はクイズに近い感じです。
1ヶ月は28日～31日ですよね？
だから、休曜日は4日～5日ですよね？（祝日の計算で除外するので考慮不要です）
つまり、「1ヶ月の日数―28＝約3日」に、
休曜日が含まれたら5日、
そうでなければ4日ですよね？

曜日はWEEKDAYで判定できるので(１～７の番号に変換されます)、
後はなんとかしてみてください。

[cayribaka]

NEWTORKDAYSは合わせて計算するのは不可能なんですね。

[ExcelVBAer]

NEWTORKDAYSをキチンと理解できておらず、
誤った返答をしてしまいすみませんでした。