通常時間と残業時間を分けて計算するには？

質問

EXCELの関数の事でご相談です。
1つのEXCELにデータが300件程入っています。
Ａ列　ＩＤ
Ｂ列　氏名
Ｃ列～Ｐ列　18日～31日の1日の労働時間(8：00や10：20や11：30等）
Ｒ列～Ｕ列は月全体の労働時間の内訳（通常・残業・深夜）
Ｖ列　月全体の総労働時間（上記内訳の合計）
の構成となっています。

Ｒ列からのデータの合計とＶ列の合計は月全体（1日～31日まで）の
合計となってしまっていますが今回は
18日～31日の通常時間・残業時間・深夜時間
それぞれの合計をEXCELの関数でどうにか出力できないかなと考えています。
sumifを使ってみましたが上手く出来なかったので
アドバイスや具体的な関数を教えて頂けると助かります。

以上、宜しくお願い致します。

回答No.5

kagakusuki

>基本的には9時-18時の割り振りです。
>8時間までが通常で8時間以上12時間未満を残業と計算し、それ以上は深夜単価になります。

との事ですが、9時から18時までの間に経過する時間は9時間で、8時間よりも長い時間となります。
　おそらく9時から18時までの間に1時間の休憩時間を入れて労働時間を8時間としているのだと思いますが、基本的には9時-18時であっても、早退等で18時になる前に退勤する場合も考えられますから、何時から何時までがまとまった休憩時間になっていのかが判らなければ、早退した場合において休憩をどの程度の長さで摂ったのかという事を計算で求める事が出来ません。
　同様に、残業時間や深夜時間に突入した場合、休憩時間が何時から何時までであったのかが判らなければ、通常時間、残業時間、深夜時間のどこでどれだけ休憩を摂ったのかという事が判りませんから、通常時間、残業時間、深夜時間のどれからどれだけの長さの時間を差し引けば良いのかも解らない事になります。
　ですから、18日～31日の通常時間・残業時間・深夜時間のそれぞれの合計を求めるためには、個々の休憩時間に関して休憩を始めた時刻と、休憩を終えた時刻のデータを併記しておく必要があります。
　尚、もし1日の勤務の中で休憩を複数回に分けて摂っていた場合には、それら複数の休憩時間の各々に関しても、休憩を始めた時刻と、休憩を終えた時刻のデータを併記しておく必要があります。
　その様な個々の休憩時間ごとの記録は取っておられるのでしょうか？

回答No.4

bunjii

>日付は1行目になります。
>1日　2日　3日　・・・・
質問の文言と不鮮明な貼付画像からはC1～P1へ18日～31日が入力されているように解釈できます。
正しい情報が提示されていないので数式の組みようがありません。

尚、通常、残業、深夜の区分がC列～P列の時間数から分割する方法の提示も無いので計算できないのではないでしょうか？
従って、「sumifを使ってみましたが上手く出来なかったので」と言うことなので、SUMIF関数を使った失敗の数式を提示して頂かないと「アドバイス」も無理です。
普通の考え方では出社時刻と退社時刻から通常勤務と残業時間と深夜勤務時間を算出します。
フレックスタイムの場合は既定の時間を超える部分を残業時間として計算できますが、深夜勤務については各自から申告するか、勤務時間帯から深夜に該当する時間帯に勤務した記録が無ければ計算できません。
十分な模擬データと質問の文言を吟味して再質問された方が良いでしょう。

回答No.3

weavaest

1日の時間の割り振りは決まっているのですか。
例えば、8時間までは通常、8時間を超えたところから12時間までが残業、12時間を超えると深夜残業であれば、日毎の労働時間から、日毎の通常、残業、深夜に割り振れます。これが時刻に依存するもので、9時から18時までは通常、18時から22時までは残業、22時を超えたら深夜残業となった場合だと、出社時刻の情報は必要になります。同じ8時間でも朝から働いた場合と、午前休などの場合で違ってきますよね。

補足

ご回答ありがとうございます。
基本的には9時-18時の割り振りです。
おっしゃっていた通り8時間までが通常で8時間以上12時間未満を残業と計算し、それ以上は深夜単価になります。
元から出社退社時刻の情報はなく、その日の労働時間のみですので
かなり無理がありますよね。
何か良い方法があればと思って質問させて頂きました。
お時間頂きありがとうございました。

回答No.2

bunjii

>Ｒ列～Ｕ列は月全体の労働時間の内訳（通常・残業・深夜）
R～Uは4列で労働時間の内訳が3項目ですね。
列数が合いません。

>今回は18日～31日の通常時間・残業時間・深夜時間それぞれの合計をEXCELの関数でどうにか出力できないかなと考えています。
日付はどの列にあるのでしょうか？
尚、Excelの関数は「出力」するための機能ではなく、数式を入力したセルに計算結果を「表示」する機能になります。

>sumifを使ってみましたが上手く出来なかったので アドバイスや具体的な関数を教えて頂けると助かります。
貼付画像から情報が読み取れないので、数式を提示して頂ければ添削します。
SUMIF関数は条件に合うセルの合計を算出しますが複数の条件を定義することはできません。
複数の条件を定義したいときはSUMIFS関数を使ってください。
貼付画像はSUMIF関数とSUMIFS関数の数式入力ウィザードです。
分らないときはヘルプ機能で例題を参考に目的に合うよう引数を入力してください。

補足

ご回答ありがとうございます。
列数はだいたいのものを載せてます。
申し訳ありません。

日付は1行目になります。
1日　2日　3日　・・・・
というような形で。

画像の添付ありがとうございます。
参考にさせて頂きます。

回答No.1

msMike

「Ｃ列～Ｐ列」に「労働時間の内訳（通常・残業・深夜）」がない限りはどうにもナランでしょ。
「Ｒ列～Ｕ列」の「月全体」の値の計算式は？
つまり「どの部分を合計しているのか？」と訊いています。

補足

ご回答ありがとうございます。
そうですよね、かなり無理がありますよね。

Ｒ列からの月全体の数値はシステムから出力しているため
計算式等は入っておりません。
※1日～17日のデータは不必要と思いデータ上からは
削除してあります。