エクセルで特殊な繰り上がり、繰り下がり

質問

仕事で映像関係の資料管理を
することになり以下のような
計算をしています。

10:10:23;06～10:11:07;22（時間表示）
の時間を求める

ただし「；」以下は「フレーム」といい
３０進法で３０F＝1秒

０F以上１４F以下は繰り下げ
１５F以上２９F以下は繰り上げ

ついさっきまで「；」より右のフレームは
切捨てて計算していたのですが
（区切り位置作業で削除）
上司に「実時間と2秒近く変わるぞ」って
注意を受け悩んでます。

関数で探しましたがいまいち分からず
ここにきました。すみませんがどなたか
ご教授いただけますか？

回答No.5

mshr1962 · ベストアンサー

A1が10:10:23;06、B1が10:11:07;22
それぞれの状態で繰り下げ、繰り上げの場合は
=LEFT(A1,8)+ROUND(RIGHT(A1,2)/30,0)/86400
=LEFT(B1,8)+ROUND(RIGHT(B1,2)/30,0)/86400
上記の減算でできます。結果を繰り下げ、繰り上げの場合は
=LEFT(B1,8)-LEFT(A1,8)+ROUND((RIGHT(B1,2)-RIGHT(A1,2))/30,0)/86400
になります。

書式は別の方の回答にあるので省かせてもらいます。

お礼

あーこれが今の私にはベストな
気がします。ありがとうございました

この場を借りて皆さんに感謝いたします
（しかし残業代は減るなぁ・・・）

回答No.4

maruru01

こんにちは。maruru01です。

A1に「10:10:23;06」、B1に「10:11:07;22」と入力されているとします。
フレームの四捨五入（14捨15入）を、
(1)差を出す前に行う場合

=TEXT(LEFT(B1,FIND(";",B1)-1)+MID(B1,FIND(";",B1)+1,LEN(B1))/30*"0:0:1","[h]:m:s")-TEXT(LEFT(A1,FIND(";",A1)-1)+MID(A1,FIND(";",A1)+1,LEN(A1))/30*"0:0:1","[h]:m:s")

(2)差を出してから行う場合

=--TEXT(LEFT(B1,FIND(";",B1)-1)+MID(B1,FIND(";",B1)+1,LEN(B1))/30*"0:0:1"-LEFT(A1,FIND(";",A1)-1)-MID(A1,FIND(";",A1)+1,LEN(A1))/30*"0:0:1","[h]:m:s")

です。
なお、両方ともセルの表示形式はユーザー定義で、
「[h]:mm:ss」
などの時刻にして下さい。

>いいえ、繰上げ、繰り下げしてからですm(_ _)m
より正確に計算するなら、差を出してから（上記の(2)）だと思うんですが。

補足

ありがとうございます。

確かに(2)の方が良いですね。

ただ私の質問の仕方が悪く
因数？のほうの繰り上がり下がりの
結果も表記しなくてはいけないんです。
（計算結果だけだなく）
繰り上がり、繰り下がりがうまくいけば
解を求めるのは簡単でした。

で、どこまでが繰り上がり下がりを求める
式なんでしょうか？(T_T）

回答No.3

Tibian

適当にマクロつくりました。
列A,Bに値がはいっていて1-300行あると仮定してです。実値に置き換えてみてください。
値がはいっていないところがあるとエラーでますんで。（何しろ適当）
Private Sub test()

Dim strb As String
Dim stra As String
Dim dtmb As Date
Dim dtma As Date
Dim dtmc As Date
Dim lngrow As Long

For lngrow = 1 To 300
strb = Sheet1.Cells(lngrow, 2)
stra = Sheet1.Cells(lngrow, 1)

dtmb = CDate(Mid(strb, 1, (Len(strb) - 3)))
dtma = CDate(Mid(stra, 1, Len(stra) - 3))

If (Mid(strb, 10, 11)) < 15 Then
dtmb = DateAdd("s", -1, dtmb)
Else
dtmb = DateAdd("s", 1, dtmb)
End If

If (Mid(stra, 10, 11)) < 15 Then
dtma = DateAdd("s", -1, dtma)
Else
dtma = DateAdd("s", 1, dtma)
End If

dtmc = dtmb - dtma
Sheet1.Cells(lngrow, 3) = dtmc
Next
End Sub

補足

回答ありがとうございます。

うひゃ～マクロですか・・・
がんばって理解します。
ここでの苦労があとあとの
楽につながるならって感じです。

回答No.2

tsukasa-12r

とりあえず、繰り上げ／繰り下げは、例えば、セル A1 に 10:11:07;22 が入っているとすると、適当なセルに
=IF(VALUE(RIGHT(A1,2))>14,LEFT(A1,8)+1/24/60/60,LEFT(A1,8))
とするとできます。（この数式を記述したセルの書式は時刻にしておいてください。）

＞０F以上１４F以下は繰り下げ
＞１５F以上２９F以下は繰り上げ
というのは、10:10:23;06 ～ 10:11:07;22 の例だと、
(1) まず繰り上げ／繰り下げを行い、10:10:23 ～ 10:11:07 の計算をするということでよいのでしょうか？
それとも、
(2) 10:11:07;22 と 10:10:23;06 の差に対して繰り上げ／繰り下げを行うのでしょうか？
(2) だとすると、上の数式を用いて、10:11:07;22、10:10:23:06 のそれぞれに対して繰り上げ／繰り下げ後の値を表示するセルを用意し、その結果を減算すればよいと思います。
(1) だと、ちょっと面倒ですが、hh:mm:ss の部分とフレームの部分を別々に計算して、フレームの計算結果が繰り下げが生じるなら 1/24/60/60 を引いてやればよいと思います。その減算の結果に対して、上の数式を適用してやれば求められると思います。

お礼

まずは回答ありがとうございます。

なんども読み返して理解しようと
奮闘中です。しかしみなさん
いとも簡単に・・・・脱帽です

回答No.1

Tibian

フレーム部は計算してから繰り上げ・繰り下げをするのですか？

補足

いいえ、繰上げ、繰り下げしてからですm(_ _)m
あっ、何かいい方法があるのですか？（期待大）
３００近い計算が２０枚・・・・(ToT)