エクセルと数式

質問

こんにちは。

エクセル2007を勉強中です。

今、下の画像のようなエクセルの表を作成しました。

B2セルには勤務の終了時間、C２セルには勤務の開始時間
が入っています。

ここでしたい処理なのですが、まず、B２からC２を引いて
勤務時間を計算し、その値によって、
以下のように、F２セルに表示したいです。

値が、6時間を超えて8時間までなら、F２に「45」の表示、8時間を超えたら
「1」の表示、表示形式は標準の数字です。

さらにB２からC2を引いて、8時間以上なら、勤務時間と残業時間を
D2とE2にそれぞれ分離して表示したいのです。

数式が複雑でわかりませんでしたので、よろしくお願いします。

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

回答No.4

MarcoRossiItaly · ベストアンサー

「4.5」時間を求めたいとの追加質問に回答いたします。関数は必要ありません。逆に、1つの関数だけでスパッと「4.5」を求めることはできません。

時刻のシリアル値の元の意味から考えて、換算します。

時間4:30は、「4 + 30分/60分」（= 4.5）時間ですね。この時間のシリアル値は"4:30"であり、また、「(4 + 30/60)/24」（= 4.5/24）という値に等しいのです。つまり、24時間、すなわち1日のシリアル値が「1」に等しくなるよう、各時間のシリアル値が定められているわけです。

もうお分かりですね？シリアル値を24倍すれば、時間に換算できるということです。

="4:30"*24
あるいは
=a1*24　　（A1セルに4:30と入力している場合）

注意点は、2本目の式では、Excelのおせっかい機能により、式を入力したセルの書式（表示形式）が勝手に「標準」でなくなってしまったりすることです。「標準」に設定し直してやれば、「4.5」と表示されるはずです。

なおHOURといった関数もあるのですが、これらは整数を返す（小数点以下を切り捨ててしまう）ので、ご要望の結果は得られません。

以上を踏まえると、No.3の各式は、次のように書き換えてあげればいいでしょう。

D2　=min("8:00",b2-C2)*24
E2　=max(,b2-c2-"8:00")*24
F2　=if(e2,1,if(d2>"6:00"*24,45,))

※F2の式で「e2」を「e2*24」と書き換える必要はありません。この部分は、ゼロ（FALSE）か非ゼロ（TRUE）かだけが問題になるからです。

お礼

なるほど、よく理解できました。

どうもありがとうございました。詳細な回答で
助かりました。

別の回答をしていただきた方も
ありがとうございます。

引き続き勉強していきます。

回答No.3

MarcoRossiItaly

次のとおり入力してください。また、B～E列などの書式設定（[h]:mmなど）にご注意ください。

D2　=min("8:00",b2-C2)
E2　=max(,b2-c2-"8:00")
F2　=if(e2,1,if(d2>"6:00"+0,45,))

※最後の式中の「+0」という部分は、その代わりに、「-0」、「*1」、「/1」、「^1」と書いても構いません。

補足

ありがとうございます。

追加で質問なのですが、たとえば、残業時間が4：30の場合、それを「4.5」と
数字に変換する関数もあるのでしょうか。

回答No.2

keithin

Ｆ２：値が、6時間を超えて8時間までなら、F２に「45」の表示、
　　　8時間を　『超えたら』　「1」

=IF(COUNT(B2:C2)<2,"",IF((B2-C2)/"1:0">8,1,IF((B2-C2)/"1:0">6,45,"")))

Ｄ２：B２からC2を引いて、8時間　『以上なら』、勤務時間

=IF(COUNT(B2:C2)<2,"",IF((B2-C2)/"1:0">=8,勤務時間数の計算式,""))
Ｄ２に入れたい勤務時間数の説明がありませんが、８でいいなら８と入れます。

Ｅ２：
=IF(D2="","",(B2-C2)/"1:0"-E2-IF(F2=45,0.75,IF(F2=1,1,0)))

回答No.1

KURUMITO

次のようにしてはどうでしょう。
Ｄ２セルには

=IF(COUNT(B2:C2)<>2,"",(B2-C2)*24-SUM(E2:F2))

Ｅ2セルには

=IF(COUNT(B2:C2)<>2,"",IF(B2-C2>"8:00"*1,(B2-C2-F2/24-"8:00")*24,0))

Ｆ2セルには

=IF(COUNT(B2:C2)<>2,"",IF(B2-C2>="8:00"*1,1,0))