質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.04%

Excel 勤務時間計算

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 3,989

score 19

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

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

イメージ説明

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

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

祝日はSheet2に2018年から2022年までの祝日を表にして入れています。
イメージ説明

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

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

以下追記
週4及びフルタイムの時の日数をセルに出力しました。
イメージ説明
最終的に時間(E列)に算出するためには
1.年選択から年を選択。
2.勤務形態から曜日を選択
3.上記のイメージの表に行き、月曜休みであれば月曜日の列のセルの日数を計算して時間を算出する。

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

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+2

日付を取得する

年・月・日を指定して日付型にするDate関数を使えばできると思います。
Date関数は例えば=Date(2017,12,24)とすると2017/12/24という日付を返してくれます。
また=Date(2017,12 + 1,1)-1のように記述すると2017/13/012018/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リファレンス - エクセルの神髄

日数の算出

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

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

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

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

休日とする曜日の指定は、あらかじめ用意されたパターンにあるものなら数値指定もできますが、週休3日の場合は文字列での指定となります。
休日は"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日という日数を返してくれますので、フルタイム用としても使用可能です。


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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/11/10 09:28

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

    キャンセル

  • 2017/11/10 16:32 編集

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

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

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

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

    キャンセル

  • 2017/11/10 18:33

    ありがとうござます。おかげさまで無事に動かすことが出来ました。
    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

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

    キャンセル

+2

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

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

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

月の日数 ― 土日祝の日数 x 8h(?) だけでいいのですか?

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

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

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/11/09 11:11

    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で判定できるので(1~7の番号に変換されます)、
    後はなんとかしてみてください。

    キャンセル

  • 2017/11/09 11:23

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

    キャンセル

  • 2017/11/13 12:01

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.04%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る