エクセルについて教えてください。給与計算で使用

質問

1日 　　　　　2日 　3日・・・　　　　　　　　　　　　　　　　　　　　　　　　月合計深夜労働時間
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　○○時間
　　　　　　火曜日 水曜日 木曜日・・・
出勤時 　17.00 17.00 17.00
退勤時 　23.00 26.00 22.50
休憩時間 　労働時間 　6.00 9.00 5.50

というような表を作っています。AM：PMなどは使用せず単純計算で労働時間を計算できるようにしているのですが、新たに　深夜労働時間を計算するために22時を超えた時間のみを合計して一箇所のセルに表示させるために計算できるような式はないでしょうか？24時を超えた時間は深夜1時であれば25時という考え方でよいのですが・・

ようするに　上記であれば火曜日から木曜日の深夜労働5.5時間となるような感じです。
曜日ごとに小計するセルスペースがないので一括で集計させたいのですが・・
よろしく御願いいたします。　

うまくエクセルの表を表現できていませんが規則正しくセルの内容はならんでいます。

回答No.7

K Kazz（@JazzCorp）

勤務時間の計算には、厳密な休息時間帯の設定が必須、このサンプルは殆どない。
中には休息時間が自己申告、なんていう笑えるものもある。
シフト（変則勤務）があっても休息時間帯の設定は共通になるだろう。要は数が増えるだけ。
深夜、早朝、休日の時間外割増しは、原則法定なんだが、休日出勤とか変則勤務手当てに加味されている場合は、割増しの対象にならないケースも出てくる。
ココに詳しく解説されている。表のレイアウトは、縦が日付、横が時刻（時間）、必然的にこうなる。
勤務時間帯、残業時間帯（深夜、早出もあり）と休息時間帯とを明確に分け、それぞれを計算すること。（計算欄は、勤務1、休息1、勤務2、休息2、残業、休息3、深夜、・・・と水平方向に並べておく）
開始、終了時刻から直接計算するのは、賢明なやり方ではない。（計算式が複雑になるだけでなく、メンテナンス性が極端に悪化する）
計算単位が分の場合は、開始、終了時刻も分換算し、個々の時間計算も分単位にして、結果を時間値に戻すのも一つの方法。

勤務時間の計算例(関数)
http://www.eurus.dti.ne.jp/yoneyama/Excel/nyumon/kinmu/index.html

回答No.6

tom04

No.1です。

質問文をよく見ると
23.00　のような入力となっていますね！
前回の数式は
セルの表示形式をユーザー定義から
[h]:mm
として、23：00　のように入力するという前提での数式でした。

時間の「分」の部分を小数点以下で、仮に45分の場合　0.45　のように入力してしまうと
質問内の　22.50　は22時50分というコトで入力されているのですかね？

もしそうであると、時間部分は10進数・分の部分は60進数で考えなくてはならないので
非常に厄介になります。

他の方からの回答にあるように、せっかく表ができていても
入力方法を
22：50　のように「コロン」付きにすればExcelは時刻（時間）と判断してくれますので、
No.1の数式で対応できるはずです。

関数を駆使すれば計算はできると思いますが
できることなら、入力方法を変えることをおススメします。

この程度でごめんなさいね。m(_ _)m

回答No.5

tsubuyuki

申し訳ないですが、思いつかないなら素直に行きましょう。
現段階ではそのほうが早そうですよ。

出勤時間が３行目、１日がＢ列（つまり、１日（ついたち）の出勤時間はＢ３セル）として
以下、４行目に退勤時間、５行目に休憩時間、６行目に労働時間。
で、７行目に「深夜労働時間」を日別に出しましょう。
見た目に邪魔だと感じたら、「行を非表示したら良いだけですから。

で、
> 24時を超えた時間は深夜1時であれば25時
とのことなので、質問者さんご本人はご存知かもしれないので省略しても良いかな、
とも思うのですが念のために説明させて頂きますが、
Ｂ３セル～Ｂ７セルは表示形式を「　[h]:mm　」としてやりましょう。
これで、２４時以降もいわゆる「時間」の表示が可能です。
つまり、「２６時間」と言う表現が可能です。

よく見ると、エクセルがセルの書式設定であらかじめ用意している表示形式は「時刻」ですよね。
「時間」と「時刻」の違いは面倒くさいのでザックリで留めますが、
単純に日本語としての「意味が違う」、というだけの問題です。
興味がおありなら辞書でも引いてください。

以降、ごく初歩的な関数しか使わずにやってみます。

Ｂ３・Ｂ４・Ｂ５セルは入力項目として、自力で入力。
労働時間は計算しましょう。
　　Ｂ６セル：=B4-B3-B5
単純に「労働時間＝退勤時間－出勤時間－休憩時間」ですね。

で、問題の深夜時間です。
「残業時間」と言う表現ではないので、単純に「２２時以降の労働時間」を出すものとして、
　　Ｂ７セル：=IF(B4>="22:00"*1,B4-"22:00",0)
退勤時間が２２：００以降なら「退勤時間－２２時」、未満なら「０」を返します。
「　"22:00"*1　」の表現は、時間（及び時刻）を表す文字列をシリアル値に直す簡単な方法です。
ＩＦ関数など、時間の比較をするにあたってよく使われるやりかたですので、
理屈は後で考えるとして、この形で覚えてしまうと便利ですよ。

例えば、出勤時間が２２：００移行である可能性があるなら、
　　=IF(B4>="22:00"*1,B4-"22:00"-IF(B3>="22:00"*1,B3-"22:00",0),0)
これでできます。
中身は・・・読み解いてください。

なお、ここまで「深夜時間の終了時間＝通常時間へ戻る時間」の考慮は一つもしていません。
これもＩＦを重ねてやれば出来ます。

ここまでできたら、あとは右方向に日数分コピー（フィル）してやれば
（３１日間としてＡＦ列までですかね）１か月分は完成です。
あとは、この深夜時間をＳＵＭで合計してやれば、どこのセルにでも配置できます。
「５時以降の分は深夜分から引いてやる」などですね。

こんな感じで、ＩＦ関数とＳＵＭ関数だけでどうにかなりましたね。
もちろん他の関数を使えば、より高度なものも作れます。
が、質問を拝読する限り、これで十分機能するのではないか？と思うのが正直なところです。

> 小計するセルスペースがない
前述しましたが、無ければ作ったら良いだけです。
行単位・列単位で「非表示」、わかりやすく言うと「行高・列幅＝０」に出来るのですから、
目触りが悪ければそうしてやれば良いだけです。
添付図のように行を選択し右クリック→「表示しない」を選ぶだけですから、難しくないはずです。
スクリーンショットは２００３のものですが、ここは２００７以降も変わっていません。

なお、表現の問題だけですが、計算すると「５時間５０分」です。

回答No.4

keithin

＞-"22:0">0　の式の意味がよく解りません。

割と見た目通りですが、"22:0"は２２時００分の事です。

指定のセル(B5:AF5)からそれぞれ22時00分を引き算した結果がゼロより大きい、つまり２２時以降に退社しているという意味です。
深夜帯（２２時以降）に退社している日を探して、それぞれの日について２２時以降何時間勤務しているかを求めるための、ただの「計算上のテクニック」（こうすれば出来る）です。

なお、回答した数式では念のため「２２時以降に出社」してても計算を間違えないように、出社時刻に対しても同様の計算を追加しています。
もしも２２時以降に出社する事は「絶対にない」と保証できるなら、退社時刻だけの計算としても問題ありません。

回答No.3

keithin

数式を教わりたいのでしたら、「規則正しく」はいいですから一体「どこ番地のセルに」何を記入しているのか、ちゃんと説明してください。

たとえばＢ４：ＡＦ４に出社時刻、Ｂ５：ＡＦ５に退社時刻のように。
とりあえず「休憩時間」は無視することにして、深夜だけ一括で計算したいなら
=SUMPRODUCT((B5:AF5-"22:0">0)*(B5:AF5-"22:0")-(B4:AF4-"22:0">0)*(B4:AF4-"22:0"))
といった計算が出来ます。

まぁ、毎日（Ｂ列からＡＦ列）の、表に余裕が無いならどこか全然離れた１００行目とか、シート２とかでもいいですけど、毎日の深残時間数をちゃんと計算して間違いないか確認できる世にしておいた方が、もちろん安全ですけどね。

お礼

keithin様
質問がうまく説明できてなくて申し訳けありません。
今後はルールを守り解りやすく内容を説明いたします。すみません！

SUMPRRODUCT関数は勉強した事がありませんでしたのでこれから理解できるように
勉強します。大変助かります。
本当にありがとうございます。

補足

申し訳けありませんが再度教えていただけますでしょうか。
上記式の中の(B5:AF5-"22:0">0)の部分の　-"22:0">0　の式の意味がよく解りません。
この式の指す意味を教えていただけませんか。

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

回答No.2

maiko0318

23:59を超えると（25:00）入力できないんじゃないかな

回答No.1

tom04

こんばんは！

退勤時の行は4行目にあるとした場合、
表示したいセル（セルの表示形式はユーザー定義から　[h]:mm　としておきます）に

=IF(COUNTIF(4:4,">=22:00"),SUMIF(4:4,">=22:00",4:4)-COUNTIF(4:4,">=22:00")*"22:00","")

としてみてはどうでしょうか？m(_ _)m

お礼

ご回答ありがとうございます。
質問のしかたが悪くてわかりにくい中大変ありがとうございます。
当方のシートにうまくあてはめる事ができるかやってみます。
考えかたを理解し頑張ってみます。
ありがとうございます。