エクセルで時給計算教えてください

質問

エクセル2010で出勤簿、時給計算をすることになりました。

Ｄ列に出勤時間
Ｅ列に退社時間
Ｆ列に勤務時間（休憩時間も拘束時間として給与あり）

Ｆ列の勤務時間を１５分単位で計算したいのですが
どういう計算式を入れたら良いのかわかりません。

またＦ列の最下段には月の合計勤務時間数を入れたいのですが
ＳＵＭで良いのでしょうか？

難しくてよくわかりません。
よろしくお願いいたします。

回答No.2

bunjii · ベストアンサー

>Ｆ列の勤務時間を１５分単位で計算したいのですがどういう計算式を入れたら良いのかわかりません。
端数を切り捨てする場合は次のようにすれば良いでしょう。
F2=FLOOR(E2-D2,VALUE("0:15"))
但し、深夜勤務で翌日に跨ぐときは出勤および退社の時刻に日付を付加しないと正しい計算ができません。

>またＦ列の最下段には月の合計勤務時間数を入れたいのですがＳＵＭで良いのでしょうか？
SUM関数で良いでしょう。
但し、1時間は1/24であることを忘れないようにしてください。

お礼

セルの表示形式の問題もユーザー定義[h]:mmに変えることで
求めたい値が表示されるようになりました。
ありがとうございました。

補足

bunjiiさま

ありがとうございます。
端数切り上げなので
FLOORをCEILINGに変えることで
無事望みどおりのものにすることができました。

ただ月の合計勤務時間を計算したいのですが、
SUMの合計だと？な数字が入っています。

合計109時間45分がエクセルのセルには『13：45』と表示されています。
セルの表示形式の問題？
どこをどのように変えると『109：45』と表示されるのでしょうか？
よろしくお願いいたします。

回答No.3

kagakusuki

回答No.1です。

>全て「15分未満の端数を切り上げる」です。

というのは、例えば実際の出勤時刻が8:16で実際の退社時刻が16:29である場合には、16:29の15分未満の端数を切り上げた時刻である16:30から、8:16の15分未満の端数を切り上げた時刻である8:30を差し引いた8.0時間を勤務時間として表示させれば良いという事でしょうか？
(多くの企業では、出勤時刻の端数を切り上げて、退社時刻の端数を切り捨てる事で、人件費を節約している様です)

>また残業はありません。
>あったとしても時給は同じです。

　私は質問者様の職場の勤務形態を存じませんが、例えば定められた出勤時刻が21:00である場合には、残業時間が無くとも深夜勤務時間が発生する場合もあり得る訳で、前回御補足頂いた内容の中には深夜勤務についてはどうすれば宜しいのかに関して何も触れておられない様ですが、深夜勤務に関しましても残業時間と同様に、単純に合計してしまえば良いと考えれば宜しいのでしょうか？

　まだ不明な点が残ってはおりますが、取り敢えず、上記の考えで構わないものと"仮定して"話を進める事に致しますので、もしこの仮定が間違っていた場合には御指摘願います。

　まず、D8セルとE8の書式設定の表示形式を[ユーザー定義]の

[h]:mm

にして下さい。(もし書式設定のやり方を御存じない場合にはその旨を御教え願います)
　次に、F8セルの書式設定の表示形式を[数値]とした上で、「小数点以下の桁数」を2桁に設定して下さい。
　次に、F8セルに次の様な関数を入力して下さい。

=IF(AND(ISNUMBER($D8),ISNUMBER($E8),$D8>=0,$D8<1,$E8>=0,$E8<2),(CEILING(TEXT($E8+($D8>$E8),"[m]"),15)-CEILING(TEXT($D8,"[m]"),15))/60,"")

　次に、F8セルをコピーして、F9～F38のセル範囲に貼り付けて下さい。
　次に、F39セルに次の様な関数を入力して下さい。

=SUM($F$8:$F$38)

　以上です。

お礼

ありがとうございます。

上手く説明できず、
また補足も足りず申し訳ありませんでした。

出勤、退社時刻はそのまま
日々の勤務時間数を15分単位、端数切り上げ
また深夜勤務もなければ、残業もほぼない状況でしたので

No.2の方の
F2=FLOOR(E2-D2,VALUE("0:15"))を
F8=CEILING(E8-D8,VALUE("0:15"))に
変えることで算出することができました。

本当に助かりました。
ありがとうございました。

回答No.1

kagakusuki

条件に不明な点があります。

>勤務時間を１５分単位で計算

との事ですが、15分未満の端数をどのように扱うのでしょうか？
　例えば、単純に「退社時刻から出勤時刻を差し引いた値」から、「15分未満の端数を切り捨てる」という方法もあれば、「15分未満の端数を切り上げる」という方法、「15分未満の端数が7.5分未満の場合には切り上げ、7.5分以上の場合は切り上げ」という方法もあります。
　或いは、退社時刻の「15分未満の端数を切り捨てる」、「15分未満の端数を切り上げる」、「15分未満の端数が7.5分未満の場合には切り上げ、7.5分以上の場合は切り上げ」の内の何れかの方法で15分単位で丸めた退社時刻から、出勤時刻の「15分未満の端数を切り捨てる」、「15分未満の端数を切り上げる」、「15分未満の端数が7.5分未満の場合には切り上げ、7.5分以上の場合は切り上げ」の内の何れかの方法で15分単位で丸めた出勤時刻を差し引いた値を勤務時間とするという方法も考えられます。
　ですから、どういった方法で勤務時間を１５分単位で丸めれば良いのかを御教え願います。

　それから、月の合計勤務時間数を表示させたいという「最下段」とは、Excelのシート上の何行目の事なのでしょうか？
　また、

>またＦ列の最下段には月の合計勤務時間数を入れたいのですが

との事ですが、一般的な企業では残業や深夜勤務の時間があった場合には、それらの時間帯における時給は通常の勤務時間における時給よりも高くなる事が多いのですが、残業や深夜勤務の時間があった場合にもその事を考慮せずに単純に合計時間を計算しても良いものなのでしょうか？

この投稿のマルチメディアは削除されているためご覧いただけません。

補足

kagakusukiさま

ありがとうございます。
そうですよね…
全て「15分未満の端数を切り上げる」です。
また残業はありません。
あったとしても時給は同じです。

表の上段にはタイトルなど色々入れているので
月の1日目は
出勤時間　Ｄ8
退社時間　Ｅ8
勤務時間　Ｆ8

月の勤務時間合計　Ｆ39　の位置にあります。