みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

エクセルと時間の計算

エクセルと時間の計算

学校勤務で勤務時間計算の事務を以下のようなエクセルシートでしています。

表の上段は、残業のない通常の場合ですが、下段は、残業があった場合の
シートになっています。

Aさんは8時間勤務で休憩45分、Bさんは4時間勤務で休憩なしです。
それ以上は残業になるのですが、下段の表がその場合です。
ここで、通常の勤務時間合計と残業時間合計を分けて計算したいです。
その結果を
F列6,7とG列6,7に表示したいのですが。

Aさんは8時間を超えた時間は、G6の合計に、
Bさんは4時間を超えた時間は、G7の合計に
入れたいのですが、どんな数式を入れればいいのでしょうか。

関数初心者で申し訳ありませんが、よろしくお願いします。

マルチメディアファイルは削除されたか見つかりません。

投稿日時 - 2010-10-30 18:45:19

QNo.6285706

すぐに回答ほしいです

質問者が選んだベストアンサー

こんばんは!
横からお邪魔します。
すでに hallo-2007さんの回答で解決していると思いますので、
余計なお世話になるかもしれませんが・・・

↓の画像のように「休憩」の「なし」(文字列)をマイナスするとエラーになりますので、
「なし」を「0」と入力しておきます。

そして、F2セルに
=IF(A2="","",IF((D2-C2-E2)*24>=B2,B2,(D2-C2-E2)*24))
G2セルに
=IF(A2="","",IF((D2-C2-E2)*24>B2,(D2-C2-E2)*24-F2,0))
という数式を入れ、F2・G2セルを範囲指定し、G2セルのフィルハンドルで下へコピーすると
画像のような感じになります。
尚、F2・G2セルの表示形式は「標準」にしておきます。

こんな感じがご希望なのですかね?m(__)m

投稿日時 - 2010-10-30 21:37:45

ANo.7

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(8)

ANo.8

E2セルには次の式を入力してから、セルの表示形式は時刻にして下方にオートフィルドラッグします。

=IF(A2="","",IF(AND(C2<"12:00"*1,D2>="12:45"*1),"0:45","なし"))

この式では12時から12時45分までが休憩時間としています。適宜変更して使用してください。

F2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",ROUND(MIN((D2-C2-IF(E2="なし",0,E2))*24,B2),2))

G2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",ROUND((D2-C2-IF(E2="なし",0,E2)-F2/24)*24,2))

なお、F列およびG列の表示形式は標準にします。

投稿日時 - 2010-10-31 08:39:32

ANo.6

>できれば勤務時間合計、残業時間合計ともに
8:00のような時間ではなく
契約時間のように単なる数値の形式で表示したいのですが。

この場合は「1:20」は「1.333333」となりますがOKですか?

OKなら提示した数式に「24」を掛けてください。

「1.2」と表示したいなら以下のような式にしてください(セルの書式は標準)。

=TEXT(元の数式,"[h].mm")*1

投稿日時 - 2010-10-30 21:34:56

ANo.5

G6セルに以下の式を入力して下方向にオートフィルしてください。

=MAX(D6-SUM(C6,E6)-B6/24,0)

投稿日時 - 2010-10-30 21:25:11

ANo.4

>契約時間のように単なる数値の形式で表示したいのですが。
時間を数値に変更する方法ですが 時間の場合は 24をかけます
ちなみに 分 の場合は*24*60 です。
エクセルに シリアル値ですが 1日(24時間)を数値の 1 であらわしています。
試しに開いているセルに 12:00 と入れてみてください。
そして セルの表示形式を 数値にすると 0.5 時刻にすると 12:00 なのです。
つまり
F6セルには =(今の式)*24 書式を数値
G6セルには =(D6-C6-E6)*24-F6
となります。

投稿日時 - 2010-10-30 20:23:46

ANo.3

B6=8,C6="8:30",D6="18:15",E6="0:45"
F6=MIN((D6-C6-IF(E6="なし",0,E6))*24,B6)
G6=MAX((D6-C6-IF(E6="なし",0,E6))*24-B6,0)

E列の表示形式が、ユーザー定義で [h]:mm;;"なし" の場合は E7=0 として
F6=MIN((D6-C6-E6)*24,B6)
G6=MAX((D6-C6-E6)*24-B6,0)

投稿日時 - 2010-10-30 20:09:13

ANo.2

時間の引き算ですよね。
F6セルに =MIN(TIME(B6,0,0),D6-C6-E6)
G6セルに =D6-C6-E6-F6
でご希望になりませんか?

投稿日時 - 2010-10-30 19:23:59

補足

ありがとうございます。
追加で、できれば勤務時間合計、残業時間合計ともに
8:00のような時間ではなく
契約時間のように単なる数値の形式で表示したいのですが。

変更してすいません。

投稿日時 - 2010-10-30 19:45:46

ANo.1

> Aさんは8時間を超えた時間は、G6の合計に

=IF(D6-C6-TIME(8,45,0)>0,D6-C6-TIME(8,45,0),"")

> Bさんは4時間を超えた時間は、G7の合計に

=IF(D7-C7-TIME(4,0,0)>0,D7-C7-TIME(4,0,0),"")

でいかかがでしすか

投稿日時 - 2010-10-30 19:17:40

あなたにオススメの質問