エクセル 合計を時間表記としたい

エクセル　合計を時間表記としたい
勤務実績表を作成しています。
以下の表があります。（例として８月第１週のみ）

ひにち　　１　２　３　４　５　６　７　　
曜日　　　日　月　火　水　木　金　土　夜間手当
勤務時間　Ａ　Ｂ　Ｃ　休　休　Ａ　Ｂ　 8:30　

勤務時間は次のとおり（俗にいう４交代勤務）

Ａ　０８：３０～１７：００
Ｂ　１６：００～２４：００（夜間手当２時間）
Ｃ　００：００～０９：００（夜間手当４時間３０分）
休　公休日

８月中の深夜手当ての分を計上しようとしています。（実際は月単位でまとめます）
Ｂ勤務は２時間、C勤務は４時間３０分の夜間手当てがあります。
どのような式を作れば、夜間手当8:30（8時間30分）と求めることができるのでしょうか？

よろしくお願いします。

No.2 ベストアンサー 回答者： kmetu 回答日時： 2010/08/07 22:41

=COUNTIF(B3:H3,"B")*TIME(2,0,0)+COUNTIF(B3:H3,"C")*TIME(4,30,)

にして
書式設定の表示形式を　[h]:mm　にしてください。

この回答へのお礼

大変参考になります。これまで、時間だけの計算が、分単位で報告書を作成する様式に変わってしまったため、苦労しておりました。貴殿の回答が今までの計算式に最も近かったです。ありがとうございました。

お礼日時：2010/08/08 11:01

No.4 回答者： KURUMITO 回答日時： 2010/08/08 07:35

４交代勤務は祝日などが含まれる場合にはどのようになるのかわかりませんが、仮にＡ1セルからＧ1セルに日曜日から土曜日までの曜日が入力されており、2行目にはお示しのようなＡ，Ｂ，Ｃ，休などの文字が入力されているとします。

これは人によってそのパターが違うでしょう。
次に8月のＢ勤務の回数と時間、Ｃ勤務の回数と時間を求めるとしたら例えばＡ4セルには2010/8/1のように日付を入力します。
そこでＢ勤務の回数ですが次の式で計算できます。

=(WEEKNUM(DATE(YEAR(A4),MONTH(A4)+1,0))-WEEKNUM(A4)-1)*2+COUNTIF(INDEX(2:2,WEEKDAY(A4)):G2,"B")+COUNTIF(A2:INDEX(2:2,WEEKDAY(DATE(YEAR(A4),MONTH(A4)+1,0))),"B")

また、Ｃ勤務の回数は次の式であらわされます。

=(WEEKNUM(DATE(YEAR(A4),MONTH(A4)+1,0))-WEEKNUM(A4)-1)*1+COUNTIF(INDEX(2:2,WEEKDAY(A4)):G2,"C")+COUNTIF(A2:INDEX(2:2,WEEKDAY(DATE(YEAR(A4),MONTH(A4)+1,0))),"C")

ここでＢやＣについては2行目で使われている文字形式と同じであることが必要です。どちらも半角英数で入力すればよいでしょう。

勤務の回数が求められれば夜間手当の時間をかけることでＢ勤務、Ｃ勤務の時間が求められますね。なお、時間は24時間を超える場合でも25:00の表示させるためにはセルの書式設定から表示形式のユーザー定義で　[h]:mm　のようにすればよいでしょう。

この回答へのお礼

大変参考になります。これまで、時間だけの計算が、分単位で報告書を作成する様式に変わってしまったため、苦労しておりました。ありがとうございました。

お礼日時：2010/08/08 11:02

No.3 回答者： mike_g 回答日時： 2010/08/07 23:31

I3: =SUMPRODUCT((B3:H3="Ｂ")*"2:0")+SUMPRODUCT((B3:H3="Ｃ")*"4:30")

この回答へのお礼

大変参考になります。これまで、時間だけの計算が、分単位で報告書を作成する様式に変わってしまったため、苦労しておりました。ありがとうございました。

お礼日時：2010/08/08 11:02

No.1 回答者： tom04 回答日時： 2010/08/07 22:28

こんばんは！

一例です。

単純にやってみます。

夜間手当はB・Cの2種類しかないということなので
数式に時間を入力しても良いのですが、
とりあえず↓の画像のようにB・Cの夜間手当時間を表にしてみました。

I3セルに表示形式はユーザー定義から
[h]:mm　としておきます。

I3セルに
=COUNTIF(B3:H3,K2)*L2+COUNTIF(B3:H3,K3)*L3

という数式を入れると画像のような感じになります。

以上、参考になればよいのですが・・・m(__)m

この回答へのお礼

大変参考になります。これまで、時間だけの計算が、分単位で報告書を作成する洋式に変わってしまったため、苦労しておりました。ありがとうございました。

お礼日時：2010/08/08 11:00