[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『時間の計算』（かじ）
時間の計算を作っているのですがよくわかりません。
=IF(COUNT(E13,G13)=2,
SUM(ROUND(MAX(0,MIN(G13,"12:00")-MAX(E13,"8:00"))*1440,0),
ROUND(MAX(0,MIN(G13,"16:45")-MAX(E13,"12:45"))*1440,0),
ROUND(MAX(0,MIN(G13,"19:00")-MAX(E13,"17:00"))*1440,0) ROUND(MAX(0,MIN(G13,"23:30")-MAX(E13,"19:30"))*1440,0),"")
これではうまくいきません。
8:00〜16:45までが定時です。
残業などの時間を集計したいのです
時間はこのような時間です。
自 １６時４５分
至 １７時００分
（１５分間）
自 １９時００分
至 １９時３０分
（３０分間）
自 ２３時３０分
至 ０時１５分
（４５分間）
自 ４時１５分
至 ４時３０分
（１５分間）
自 ６時３０分
至 ７時００分
(３０分間
宜しくお願いします。
表はどのような状態なのでしょうか？ >これではうまくいきません。 ご自分で作られた式なのでしょうか？どこかに掲載されていたものですか？
D E F G H 12 名前 開始 休憩 終了 残業 13 あああ 8：00 0：45 16：45 0：00 14 いいい 8：00 1：00 18：00 1：00 15 ううう 8：15 0：45 16：45 16 えええ 8：30 1：00 18：00 17
終了が12時前 12：00-8：00（休憩なし） 16時45分前 終了時間-12：45（休憩12：00-12：45） 19時30分前 終了時間-17：00（休憩12：00-12：45/16：45-17：00） 23時30分前 終了時間-19：30（休憩12：00-12：45/16：45-17：00） （以下続く）
↑こういう状況？ ”ううう”や”えええ”のような場合、どう処理しますか？ （勉強したがり＠リベンジ！） 17:30修正
MAX(0,MIN(G13,"12:00")-MAX(E13,"8:00"))*1440 → MAX(0,MIN(G13,"12:00")*1440-MAX(E13,"8:00")*1440) （NB)
>これではうまくいきません。 どのようになればいいのかな
式のスペースの部分が , の間違い =IF(COUNT(E13,G13)=2,SUM(ROUND(MAX(0,MIN(G13,"12:00")-MAX(E13,"8:00"))*1440,0),ROUND(MAX(0,MIN(G13,"16:45")-MAX(E13,"12:45"))*1440,0),ROUND(MAX(0,MIN(G13,"19:00")-MAX(E13,"17:00"))*1440,0),ROUND(MAX(0,MIN(G13,"23:30")-MAX(E13,"19:30"))*1440,0)),"")
まとめて =IF(COUNT(E13,G13)=2,ROUND(SUM(MAX(0,MIN(G13,"12:00")-MAX(E13,"8:00")),MAX(0,MIN(G13,"16:45")-MAX(E13,"12:45")),MAX(0,MIN(G13,"19:00")-MAX(E13,"17:00")),MAX(0,MIN(G13,"23:30")-MAX(E13,"19:30")))*1440,0),"")
どのような値になればいいのか説明してください
By
8:00〜8:30までは朝礼等で実際に入力するのは8:30からになります。
8:30〜12:00が午前中の仕事です。
12:00〜12:45までがお昼です。
12:45〜16:45が午後の仕事です。
16:45〜17:00までは休憩です。
17:00〜19:00までは残業2時間です。
19:00〜19:30までは休憩30分です。
19:00〜23:30までも残業になります。
23:30〜0:15までは休憩です
。
以下は上に書いてある通りです。
実際はA1セルに8:30と入力して、B1のセルに16:45を入力、C1のセルに自動計算で7.5ｈの表示をしたいのです。休憩の45分を引いた値を表示させたいのです。
B1のセルが19:00の場合は9.5ｈと表示させたいです。お昼の休憩45分と残業前の休憩15分を引いた値です。
19:00以降も同じ考えでやりたいのです。
教えていただいのですがうまくいきません。
何で1440を乗算しているの? 分を計算するためなのでは?
時間を求めたいのであれば24を乗算すればいいのでは
ROUND(数値,桁数)の桁数も1または2にしましょう
By
マクロなら [[20101221205345]] 『作業時間と休憩時間』（FA) で同じ様な事をしているので、此れのタイムテーブルを1つにした、簡易版で善いのですが？ 数式で無ければ行けないのでしょうね？
Sheet2のA1を先頭にして、以下のタイムテーブルを記述します
A B C
1 シフト 開始 終了
2 朝礼 8:00 8:30
3 作業 8:30 12:00
4 休憩 12:00 12:45
5 作業 12:45 16:45
6 休憩 16:45 17:00
7 残業 17:00 19:00
8 休憩 19:00 19:30
9 残業 19:00 23:30
10 休憩 23:30 0:15
時刻を入力するシートモジュールに以下を記述
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Dim lngRow As Long
Dim vntStart As Variant
Dim vntEnd As Variant
Dim lngTime As Long
With Target
If .Count > 1 Then
Exit Sub
End If
'開始時間、終了時間の何れかが入力された場合
If Not (.Column = 1 Or .Column = 2) Then
Exit Sub
End If
'入力行位置を取得
lngRow = .Row
'開始時間、終了時間を取得
vntStart = Me.Cells(lngRow, "A").Value
vntEnd = Me.Cells(lngRow, "B").Value
End With
'入力された開始時間、終了時間を検査
If VarType(vntStart) <> vbDouble Then
Exit Sub
Else
If vntStart < 0 Or 1 <= vntStart Then
Exit Sub
End If
End If
If VarType(vntEnd) <> vbDouble Then
Exit Sub
Else
If vntEnd < 0 Or 1 <= vntEnd Then
Exit Sub
End If
End If
'作業時間を分で計算
lngTime = TimeCalc(vntStart, vntEnd)
'作業時間が有るなら
If lngTime <> -1 Then
Application.EnableEvents = False
Me.Cells(lngRow, "C").Value = lngTime / 60
Application.EnableEvents = True
End If
End Sub
Private Function TimeCalc(vntStart As Variant, vntEnd As Variant) As Long
Dim i As Long
Dim lngRows As Long
Dim vntTabl As Variant
Dim lngStart As Long
Dim lngEnd As Long
Dim lngSum As Long
Dim vntTop As Variant
'作業開始時間を午前0時からの経過分に変換
lngStart = ConvMinute(vntStart)
'作業終了時間を午前0時からの経過分に変換
If vntStart > vntEnd Then
vntEnd = vntEnd + 1
End If
lngEnd = ConvMinute(vntEnd)
'タイムテーブルの先頭セル位置を基準とする（先頭列の列見出しのセル位置）
With Worksheets("Sheet2").Cells(1, "A")
'行数の取得
lngRows = .Offset(Rows.Count - .Row).End(xlUp).Row - .Row
If lngRows <= 0 Then
TimeCalc = -1
Exit Function
End If
'タイムテーブルを配列に取得
vntTabl = .Offset(1).Resize(lngRows, 3).Value
End With
'先頭データを変数に退避
vntTop = vntTabl(1, 2)
'タイムテーブルに就いて繰り返し
For i = 1 To lngRows
If vntTabl(i, 1) = "作業" Or vntTabl(i, 1) Like "残業" Then
'タイムテーブルの開始を午前0時からの経過分に変換
'開始がタイムテーブル開始先頭時刻より小さい場合、
'翌日とする為、1日を加算
If vntTabl(i, 2) < vntTop Then
vntTabl(i, 2) = vntTabl(i, 2) + 1
End If
'経過分に変換
vntTabl(i, 2) = ConvMinute(vntTabl(i, 2))
'タイムテーブルの終了を午前0時からの経過分に変換
If vntTabl(i, 3) < vntTop Then
vntTabl(i, 3) = vntTabl(i, 3) + 1
End If
'経過分に変換
vntTabl(i, 3) = ConvMinute(vntTabl(i, 3))
'タイムテーブルの開始、終了が作業時間内に在るなら
If lngStart <= vntTabl(i, 3) And vntTabl(i, 2) <= lngEnd Then
'タイムテーブルの開始が作業開始時刻より前なら
'タイムテーブルの開始を作業開始時刻に
If lngStart > vntTabl(i, 2) Then
vntTabl(i, 2) = lngStart
End If
'タイムテーブルの終了が作業終了時刻より後なら
'タイムテーブルの終了を作業終了時刻に
If vntTabl(i, 3) > lngEnd Then
vntTabl(i, 3) = lngEnd
End If
'作業時間を加算
lngSum = lngSum + (vntTabl(i, 3) - vntTabl(i, 2))
End If
End If
Next i
TimeCalc = lngSum
End Function
Private Function ConvMinute(vntValue As Variant) As Long
'シリアル値を午前0時からの経過分に変換
ConvMinute = Int(vntValue) * 24 * 60 + Hour(vntValue) * 60 + Minute(vntValue)
End Function
(Bun)
（かじ）
何がどう上手く無いのですか？ 「入力したのですがうまく起動しません。」では、回答のしようが有りませんが？
(Bun)
マクロが使えないと言う方なら？ 後、私が考え就くのは、計算結果を中間表（予備計算表）経由で計算する方法位かな？
方法としては
開始時間、終了時間を入力して、結果を求めるシートをSheet1とします Sheet1に就いて A2に入社時間を入力する物とします B2に退社時間を入力する物とします C2に結果を求める物とします
中間表をSheet2に作成する物とします Sheet2に就いて A2に：=IF(COUNT(Sheet1!A2:B2)<>2,"",Sheet1!A$2) B2に：=IF(COUNT(Sheet1!A2:B2)<>2,"",IF(Sheet1!A2<Sheet1!B2,Sheet1!B2,Sheet1!B2+1)) の式を入力します（Sheet1の入力値を検査して、終了時間が翌日の場合1を足します） C1に「8:30〜12:00が午前中の仕事」の「8:30」シリアル値を入力します D1に「8:30〜12:00が午前中の仕事」の「12:00」シリアル値を入力します C2に：=IF($B2<C$1,"",IF($A2>=C$1,$A2,C$1)) D2に：=IF(C2<>"",IF($A2>D$1,"",IF(C$1<D$1,IF($B2>=D$1,D$1,$B2),IF($B2>=D$1+1,D$1,$B2))),"") E2に：=IF(C2="",0,(INT(D2)*60*24+HOUR(D2)*60+MINUTE(D2))-(INT(C2)*60*24+HOUR(C2)*60+MINUTE(C2))) とします これで、E2に「午前中の仕事」をした時間が分単位で計算されます 同様に、 F1に「12:45〜16:45が午後の仕事」の「12:45」シリアル値を入力します G1に「12:45〜16:45が午後の仕事」の「16:45」シリアル値を入力します F2に：=IF($B2<F$1,"",IF($A2>=F$1,$A2,F$1)) G2に：=IF(F2<>"",IF($A2>G$1,"",IF(F$1<G$1,IF($B2>=G$1,G$1,$B2),IF($B2>=G$1+1,G$1,$B2))),"") H2に：=IF(F2="",0,(INT(G2)*60*24+HOUR(G2)*60+MINUTE(G2))-(INT(F2)*60*24+HOUR(F2)*60+MINUTE(F2))) とします （F2、G2、H2の式は、C2、D2、E2の式をCopyして下さい） これで、H2に「午後の仕事」をした時間が分単位で計算されます 同様に、 I1に「17:00〜19:00までは残業2時間」の「17:00」シリアル値を入力します J1に「17:00〜19:00までは残業2時間」の「19:00」シリアル値を入力します I2に：=IF($B2<I$1,"",IF($A2>=I$1,$A2,I$1)) J2に：=IF(I2<>"",IF($A2>J$1,"",IF(I$1<J$1,IF($B2>=J$1,J$1,$B2),IF($B2>=J$1+1,J$1,$B2))),"") K2に：=IF(I2="",0,(INT(J2)*60*24+HOUR(J2)*60+MINUTE(J2))-(INT(I2)*60*24+HOUR(I2)*60+MINUTE(I2))) とします（式は、C2、D2、E2の式をCopyして下さい） これで、K2に「残業2時間」をした時間が分単位で計算されます 同様に、 L1に「19:30〜23:30までも残業」の「19:30」シリアル値を入力します M1に「19:30〜23:30までも残業」の「23:30」シリアル値を入力します L2に：=IF($B2<L$1,"",IF($A2>=L$1,$A2,L$1)) M2に：=IF(L2<>"",IF($A2>M$1,"",IF(L$1<M$1,IF($B2>=M$1,M$1,$B2),IF($B2>=M$1+1,M$1,$B2))),"") N2に：=IF(L2="",0,(INT(M2)*60*24+HOUR(M2)*60+MINUTE(M2))-(INT(L2)*60*24+HOUR(L2)*60+MINUTE(L2))) とします（式は、C2、D2、E2の式をCopyして下さい） これで、N2に「残業2時間」をした時間が分単位で計算されます 次に、 O2に：=E2+H2+K2+N2 とすれば、午前中の仕事+午後の仕事+残業2時間+他の残業の集計時間が分単位で出ます
Sheet1に就いて C2に：=IF(Sheet2!O2=0,"",Sheet2!O2/60) とすれば、Sheet1のC1に作業時間が時間単位で表示されます
この中間表で行っている事は、入社時間〜退社時間の区間を取得し 午前中の仕事、午後の仕事、残業2時間、他の残業の各区間が、上記区間に対し 丸々入っているか、全く入っていないか、遅刻か、早退かを見て居ます 其の先頭時刻がC2、F2、I2、L2に 終了時刻がD2、G2、J2、M2に表示され、 E2、H2、K2、N2に作業時間が分単位で表示されます
(Bun)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.