[[20160309104101]]　『勤務時間の時間外合計の出し方』（ヒデパパ）　ページの最後に飛ぶ

『勤務時間の時間外合計の出し方』（ヒデパパ）

時間外の合計時間数の計算式をお願いします。

１時間３０分の時間外を　1.50で計算します
１時間１５分の時間外を　1.25で計算
１時間４５分の時間外を　1.45で計算　　

　　A（時間外）

　1　1.30　

　2　1.15

　3 1.45

　4 1.30　

　5　6.00（合計時間数）

＜使用 Excel：Excel2007、使用 OS：WindowsXP ＞

条件が矛盾していますよ？１時間４５分の時間外は、1.75なのでは？
でもって、作業列を用意して、「=INT(A1)+(A1-INT(A1))/6*10」とかしておき、こっちでSUMするとか。
（???） 2016/03/09(水) 10:56

 A5 =SUMPRODUCT(TEXT(A1:A4*100,"0!:00")*24)

 こういうこと？
（笑） 2016/03/09(水) 11:21 修正11:25

すません。1.75の記載間違いでした。上手く説明できなくて申し訳ないです。
教えて頂いた計算式では、エラーが出ましたので
再度教えて頂けないでしょうか
会社規定の出勤簿へ勤務状況は手書き記入してもらって、時間外数の合計を平日と土曜日と日曜日毎で
エクセルで計算したいです。
（毎月　電卓で手計算しているので・・・）

１時間３０分の時間外を　1.50で計算
１時間１５分の時間外を　1.25で計算
１時間４５分の時間外を　1.75で計算　　

【例】
　　
３月分出勤簿

　A　　　　B　　　　C　　　　
日付　　曜日　　時間外数

1　　　火　　　　1.30

2 　水　　　　1.30
　
3　　　木　　　　1.15

4　　　金　　　　1.45

5　　　土　　　　3.30

6　　　日　　　　1.30

・　　　・　　　　　・
・　　　・　　　　　・
・　　　・　　　　　・

31　　　木　　　　6.30　　

平日の時間外合計数　　　　○．○○　

土曜日の時間外合計数　　　○．○○

日曜日の時間外合計数　　　○．○○

（ヒデパパ） 2016/03/09(水) 11:52

 質問

 1）「教えて頂いた計算式」とは、どんな式を試して、どんなエラーが出たのか？
 2）A列の日付はシリアル値なのか？
 3）B列の曜日は文字列なのか、それとも表示形式によるものなのか？
 4）C列には数式が入っているのか？　入っているのならどんな数式か？
 5）祝日は関係ないのか？

 条件の後出しはやめてくださいね。
（笑） 2016/03/09(水) 12:13

 追加質問

 6）C列を「1:30」のようにシリアル値で入力しない理由は？

 とりあえず以上です。
（笑） 2016/03/09(水) 12:50

後出し条件を出してしまい申し訳ありません。

ご質問の返答を致します。

　1）合計時間数にSUMPRODUCT(TEXT(C1:C31*100,"0!:00")*24)計算式で
　　　「＃」と表記されました。

 2）シリアル値ではありません
 3）文字列です
 4）C列には数式が入っていません。
 5）祝日も関係あります。
6)　「1.30｣ピリオドで明記しないといけないからです。

宜しくお願いします。
（ヒデパパ） 2016/03/09(水) 13:30

私の方法でも、笑さんの方法でも、どちらでも結果が得られるはずですが…。
実は1行目からではなかった、とか、まだまだ後出し要素が残っていたりしませんか？
祝日も…、と言っていながら、それをどこに表示するのか書いていませんし。

例えば2行目からの場合で、土曜日だけ合計するならば、以下。

 =SUMPRODUCT((B$2:B$32="土")*TEXT(C$2:C$32*100,"0!:00")*24)
（???） 2016/03/09(水) 13:48

あと、A列がシリアル値でない、との事ですが、つまり毎月B列を手作業で全部埋めている、ということでしょうか？
シリアル値にしておけば、書式設定を「d(aaa)」とするだけで、間違えずに曜日まで埋まるのですが。

シリアル値にしておかないと、祝日対応が面倒になると思いますよ。
（???） 2016/03/09(水) 13:56

A列とB列もシリアル値にして、計算式を入力したら平日と土曜日の時間外が何とか結果が出来ました！

祝日の事を忘れておりました・・・土曜日の時間外として計上したいのですが・・・

ほんとに小出しで申し訳ございません。

（ヒデパパ） 2016/03/09(水) 15:29

 > A列とB列もシリアル値にして、計算式を入力したら
 > 平日と土曜日の時間外が何とか結果が出来ました！

 その式を提示してください。平日、土曜どちらとも。
　
 > 土曜日の時間外として計上したいのですが

 日曜日が祝日だったらどうするんですか？
 土曜の時間外なのか、日曜の時間外なのか？

 とりあえず以上です。
（笑） 2016/03/09(水) 17:49

日曜日が祝日の時は、「日曜日の時間外」として計上します。

祝日は、土曜日の時間外と併せて計上します。

　A　　　　B　　　　C　　　　
日付　　曜日　　時間外数

1　　　火　　　　1.30
2 　　　水　　　　1.30 　
3　　　木　　　　1.15
4　　　金　　　　1.45
5　　　土　　　　3.30
6　　　日　　　　1.30
・　　　・　　　　　・
・　　　・　　　　　・
・　　　・　　　　　・
31　　　木　　　　6.30　　

平日の時間外計算式
SUMPRODUCT(TEXT(C$2:C$32*100,"0!:00")*24)

土曜日の時間外計算式
SUMPRODUCT((B$2:B$32="土")*TEXT(C$2:C$32*100,"0!:00")*24)

日曜日の時間外計算式
SUMPRODUCT((B$2:B$32="日")*TEXT(C$2:C$32*100,"0!:00")*24)

祝日の時間外が、平日の時間外へ計算されてしまいます。

宜しくお願いします。

（ヒデパパ） 2016/03/10(木) 09:08

 ということは、B列はシリアル値ではなく「文字列」なんでしょ？

 A列には「2016/3/1」のような日付（シリアル値）が入っている、ということで間違いないですか？

 A列がシリアル値なら、B列の曜日は下の式にしてください（されてるかもしれませんが念のため）

 B2 =TEXT(A2,"aaa;;")　　B32までコピー

 正しい曜日になるか確認のこと。
　
　
 で、ここからが本題（A列はシリアル値、B列は文字列、という前提です！）

 祝日かどうかを判定するためには、祝日の日付を入力した表が別途必要です。
 作成するのは、同じシートでも別シートでもいいんですが、ここでは例として同じシートのJ列だとします。

	J
1	祝日
2	2016/3/20
3	2016/3/21
4	2016/4/29
　　　　　・
　　　　　・　以下略
　
　
 時間外合計を出すセルは
 C34: 平日
 C35: 土曜
 C36: 日曜
 だとして･･･

 C35（土曜） =SUMPRODUCT(SIGN(COUNTIF(J2:J50,A2:A32)+(B2:B32="土"))*(B2:B32<>"日")*TEXT(C2:C32*100,"0!:00")*24)

 C36（日曜） =SUMPRODUCT((B2:B32="日")*TEXT(C2:C32*100,"0!:00")*24)

 C34（平日） =SUMPRODUCT(TEXT(C2:C32*100,"0!:00")*24)-C35-C36

 検証はお任せします（演算誤差は考慮してません）
（笑） 2016/03/10(木) 13:00

 ちなみに、A列の日付ですが、

	F	G
1	2016	3

 ↑ のように、作成したい日付の「年」をF1に、「月」をG1に入力しておけば
（配置的にはアレですが、あくまで例です･･･）

 A2 =IFERROR(($F$1&-$G$1&-ROW(A1))*1,"")

 これをA32までコピーしておけば、F1とG1の値を変えるだけで、A列はその月の日付になります。
 表示形式「日付」

 表の配置を変更しても ROW(A1) はそのままにしておくこと！

 小の月、うるう年にも対応しています。
（笑） 2016/03/10(木) 13:01

コメント返信：

[ 一覧(最新更新順) ]

[[20160309104101]] 『勤務時間の時間外合計の出し方』（ヒデパパ） ページの最後に飛ぶ

[[20160309104101]]　『勤務時間の時間外合計の出し方』（ヒデパパ）　ページの最後に飛ぶ