時間集計で形式は（hh:mm:ss:00）の文字列

質問

1行目は、全て見出し行です。

C列に文字列（分：秒）を記入済み
　C2　　05：22
　C3　　00：22
　C4　　04：56
　C5　　04：31
　C6　　05：46
　C7　　05：48

D列はC列の時間集計相当で文字列、形式は（hh:mm:ss:00）を求めたい
　D2　　00：00：00
　D3　　05：22：00　<---- 5分２2秒　表示は、C2相当＋(：00）
　D4　　05：44：00　　C2相当＋C3相当
　D5　　10：40：00　　D4(C2相当＋C3相当）＋C4相当
　D6　　15：11：00　　D5(C2相当＋C3相当＋C4相当）＋C5相当
　D7　　20：57：00　　　（以下同じ集計式）
　D8　　26：45：00
　　（途中省略）
　Dxx　　01:05:25:00 <-----1時間を超えた場合

----------------
D列に入力する計算式を教えて下さい。

仮計算で列の追加が必要な場合、D列以降になっても問題ありません。

環境
office2019 + windows10

回答No.11

SI299792 · ベストアンサー

条件が変わったのなら、ここを締切って新しく質問した方がいいです。
どうせここは只ですから。

１列右にずれて、時：分：秒、に変わったのですね。
サンプルの「：」が全角なので、全角と解釈しました。

文字列にして、仮計算を使いたくないなら
E2: '00：00：00
E3: =TEXT(LEFT(IF(LEN(E2)=8,"00：","")&E2,8)+D2,"[>0.041666666]hh""：""mm""：""ss;mm""：""ss")&"：00"
下へコピペ。

自分用ならこの方法は使いません。複雑な数式はメンテナンスが大変だからです。
作業域を作るか、セルの書式設定を使います。右詰めがまずけれは左詰めを指定します。
後からＥ列を何かに使うのなら、文字列にしなければなりませんが、そうでないならセルの書式設定でやった方がいいです。

お礼

S1299792さん、途中でセル構造が変わったり
　　文字列で表示されるようにと後出しジャンケンのような内容になり失礼しました。

アドバイスいただいた式でも
　　文字列で集計が出来るのを確認しました。

　　　　=TEXT(LEFT(IF(LEN(E2)=8,"00:","")&E2,8)+D2,"[>0.041666666]hh"":""mm"":""ss;mm"":""ss")&":00"

ありがとうございます。

>後からＥ列を何かに使うのなら、文字列にしなければなりませんが、そうでないならセルの書式設定でやった方がいいです。

文字式が必要なのは、
最終的に他のシートに以下のようなコードで順番に書き出す必要があり
文字列形式が必要になったからです。

Worksheets("LIST").Cells(3 * i + 3, "A") = "INDEX 01 " & Worksheets("LIST").Cells(i + 2, "E")

最初は、FORMAT関数で何とかなりそうだと思ったのですが
　単純なhh:mm:ssでなく、６０分以下はmm:ss:00で１時間を超えるとhh:mm:ss:00なので
　FORMATの書式設定だ利用できそうにないと思ったからです。
　（単に私の能力不足の言い訳です。）

回答No.10

toshi_2010

D2 '00：00：00
D3 =TEXT(TIME(0,LEFT(C2,2),RIGHT(C2,2))+TIMEVALUE(IF(LEN(D2)=8,"0:"&LEFT(D2,5),LEFT(D2,8))),"[>0.041666666]hh""：""mm""：""ss;mm""：""ss")&"：00"
でも出来ます。

回答No.9

kkkkkm

No8の訂正です。
1時間を超えた場合No8だと駄目でしたので

E3に
=IF(HOUR(D2+(IF(LEN(E2)>8,LEFT(E2,LEN(E2)-3),"00:"&LEFT(E2,LEN(E2)-3))))>=1,TEXT(D2+(IF(LEN(E2)>8,LEFT(E2,LEN(E2)-3),"00:"&LEFT(E2,LEN(E2)-3))),"hh:mm:ss"),TEXT(D2+(IF(LEN(E2)>8,LEFT(E2,LEN(E2)-3),"00:"&LEFT(E2,LEN(E2)-3))),"mm:ss")) &":00"
やたら長くて気持ちが悪いですが・・・。

回答No.8

kkkkkm

No7のF列を利用しない式です。

E2に
=TEXT(0,"mm:ss") & ":00"
E3に
=IF(HOUR(D2+("00:"&LEFT(E2,LEN(E2)-3)))>=1,TEXT(D2+("00:"&LEFT(E2,LEN(E2)-3)),"hh:mm:ss"),TEXT(D2+("00:"&LEFT(E2,LEN(E2)-3)),"mm:ss")) &":00"
以下下にコピー

で試してみてください。

お礼

kkkkkmさん、教えてもらった式で上手く処理されて
結果も文字書式になりました。

E２=TEXT(0,"mm:ss")&":00"
E3に
=IF(HOUR(D2+(IF(LEN(E2)>8,LEFT(E2,LEN(E2)-3),"00:"&LEFT(E2,LEN(E2)-3))))>=1,TEXT(D2+
(IF(LEN(E2)>8,LEFT(E2,LEN(E2)-3),"00:"&LEFT(E2,LEN(E2)-3))),"hh:mm:ss"),TEXT(D2+
(IF(LEN(E2)>8,LEFT(E2,LEN(E2)-3),"00:"&LEFT(E2,LEN(E2)-3))),"mm:ss")) &":00"
以下　下にずずずぃーとコピー

おかげさまで何とか処理も完成しました。
最後までお付き合い願い改めてお礼申し上げます。

しばらく解決済みにしないでおきますので
　何かありましたらコメントをお願いします。

回答No.7

kkkkkm

現在の計算式を入れて
は説明不足でした。

F3
=D2
F4
=D3+F3
(もとはD2+D3だったと思いますがD2=F3なので)
以下下にコピー

E2に
=TEXT(0,"mm:ss") & ":00"
E3に
=IF(HOUR(F3)>=1,TEXT(F3,"hh:mm:ss"),TEXT(F3,"mm:ss")) &":00"
以下下にコピー

で試してみてください。

質問でのC列がD列に、D列がE列になっているとしての式です。

回答No.6

SI299792

セルの書式設定でいいのなら
D2: 0
D3: =D2+C2/60
セルの書式設定、ユーザー設定
[>0.041666666]hh"："mm"："ss"：00";mm"："ss"：00"

文字列にしたい、：は全角にしたいのであれば、
E2: =TEXT(D2,"[>0.041666666]hh""：""mm""：""ss;mm""：""ss")&"：00"
E3へコピペ。
D3: =D2+C2/60
D3～E3を下へコピペ。
Ｅ列に結果が出ます。

文字列にして、仮計算を使いたくないなら
D2: '00：00：00
D3: =TEXT(LEFT(IF(LEN(D2)=8,"00：","")&D2,8)+C2/60,"[>0.041666666]hh""：""mm""：""ss;mm""：""ss")&"：00"
下へコピペ。

お礼

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

現在、セル構造を変更してしまって集計はE列に変更済みです。
以下の参考図を参照ください。

　　https://imgur.com/W4wD0ff

個人的理解でアドバイスを下記のように理解して見ました。

仮セルとしてF列を利用して
　　F2=0
　　F3=D2*1
　　F4＝E3+D3/60
　　F5以下はF4の式をコピペ

　F列のセルの書式設定、ユーザー設定
　　　[>0.041666666]hh":"mm":"ss"：00";mm":"ss":00"
　　　や
　　　[>0.041666666]hh"："mm"："ss"：00";mm"："ss"：00"
　　　としても
　　　　　結果は、どちらも
　　　　　　　数値相当で右寄せで表示されてしまいます。
　　　　　　　又、集計結果の数値がE列と違います。

　　　　　　　参照図(1)、(2)を見てください。
　　　　　　　　　https://imgur.com/SpDBjFA
--------------------------------------
仮セルを使わない式は、試していませんが
　　セル構造を最初と変えてしまって申し訳ないのですが
　　アドバイスあればお願いします。

回答No.5

kkkkkm

図では集計はE列になってますのでE列として
E3が00:05:25ですが05:25:00にならないとだめなのではないでしょうか
E3に=D2*1
とすれば05:25:00になりますが他のセルと同じになります。
エクセルは文字列みたいに入力されていても計算すると見た目の形式(時間とか日付とか)に自動で変換されてしまいます。
左寄せだけ考えれば配置で左寄せにしてしまえば済みますが、コピーで値貼り付けしたときに数値で表示されてしまいます。

とりあえず、いい案かどうかは分かりませんがF列に現在の計算式を入れて、表示形式はhh:mm:ssで
E列の表示形式は標準か文字列で
E2に
=TEXT(0,"mm:ss") & ":00"
E3に
=IF(HOUR(F3)>=1,TEXT(F3,"hh:mm:ss"),TEXT(F3,"mm:ss")) &":00"
として下にコピーしてみてはいかがでしょう。

この場合でも、分:秒:00の表示の時は計算時には時:分:秒と解釈されてしまいます。値貼り付けは見た目のまま貼り付けられます。

No4の式も作業列を作ってと思っていたのですが間違ってD列を作業列にして他の列に結果を出す式を出してしまってましたm(__)m

補足

kkkkkmさん、継続しての回答ありがとうございます。

私の理解不足で回答が読み解けません。

>F列に現在の計算式を入れて
　　　どこの計算式ですか　？

　 E列の式だ結果が＃VALUE!となったり表示が変になります。

回答No.4

kkkkkm

> １）1時間より少ない場合は
> 　　　　　00:05:25では無く、05:25:00　と表示したいのです。
> （分：秒：00）の形式で表示したい。

かなり勘違いしてましたm(__)m

解決してるようですので蛇足となりますが

D2
=TEXT(0,"mm:ss") & ":00"
D3
=IF(HOUR(IF($C$2=D2,C1+C2,IF(D2=0,C2,D2+C2)))>=1,TEXT(IF($C$2=D2,C1+C2,IF(D2=0,C2,D2+C2)),"hh:mm:ss"),TEXT(IF($C$2=D2,C1+C2,IF(D2=0,C2,D2+C2)),"mm:ss"))&":00"

下にコピー

式が長くなりますがこんな感じでしょうか。

お礼

kkkkmさん、回答ありがとうございます。

列構造を変更してしまったので
　すいません。
　回答いただいた式を試さずに先に進んでしまいました。

--------------------------------------------------------------
新しく質問で投稿したほうが良いかもしれませんが、
問題が発生したので付随した追加の質問ですが、よろしくお願いします。

参考図を参照ください。
　　https://imgur.com/W4wD0ff

Higurashi777さんの式を利用させていただいて
　D列に時間集計は出来たのですが

D列の計算の結果は
　D2＝00:00:00　----＞　数値
　D3＝ｃ2　-----------＞　文字列
　D4=ｃ3＋D3　------＞　数値
　D5以下も数値
です。

この数値（D2,D4、D5，----）を見た目そのままに文字列に変換したいのですが
　EXCELのワークシート関数を利用するとどのような式になりますか　？

回答No.3

imogasi

質問者は、データ例の書き方、質問の記述方法からして、エクセルに
相当経験があると察しますが、
何をしたくて聞いている（困っている）かわからない。
ーー
C列に「文字列」で時間＋分＋秒の入力があるとする。
C2:C６
15:20
00:22
4:56
4:31
4:31:00
このままだと、左詰めの表記になるが、右詰めに設定されていても良い。
セル単位で、文字列の時間は、日付シリアル値の値に変換（関数があるので）して、それらで計算するのは鉄則でしょう。だから
D列において
D2セルの式を　=TIMEVALUE(C2)　　と入れて、下方向にD6まで式を複写。
D2:D6の表示形式は、そのままでよい。
D2:D6の1部セルか全体加算であれば、
全体ではSIM
一部ではD2+D3のような＋演算子を使えばしまい。
隣接行のデータの加算のばあいは、２４時間を超える場合もあるが
日に繰り上げないならば
例えばD8セルに、=SUM(D2:D6)と入れて
表示形式を　[h]:mm:ss　にすれば、29:40:00
に見える。
mmやssの部分は,必要に応じて、m、sもあり得る。
ーー
こういうことはエクセルのFAQなで、今更と思うので、小生が、質問趣旨を勘違いしていたらすみませんが。

補足

＞imogashiさん

>何をしたくて聞いている（困っている）かわからない。

時間モドキの文字列を利用するかとの思ってしまいますよね。

音楽ファイルの分割に利用するための秒以下に：00を付加した
へんてこな書式がシナリオシートを作成するのに必要なのです。

私自身は、質問はたくさんしますが
　EXCELに関しては素人レベルです。

回答No.2

kkkkkm

D2は
=TEXT(0,"[hh]:mm:ss")
D3に
=TEXT(IF($C$2=D2,C1+C2,IF(D2=0,C2,D2+C2)),"[hh]:mm:ss")
として下にコピーとしたら文字列にならないでしょうか。

お礼

>kkkkmさん

　　C列は、以下の式から抽出された文字列で
　　　=MID(A2,FIND("(",A2)+1,((FIND(")",A2))-(FIND("(",A2))-1))

　　この式のままでは、提示いただいた式で上手く処理出来ませんでした。

　　C列の式を以下に変更すれば
　　　=”00：”＆MID(A2,FIND("(",A2)+1,((FIND(")",A2))-(FIND("(",A2))-1))

　　集計自体は、上手く処理されていますが
　　最初に記載したように
　　　１）1時間より少ない場合は
　　　　　00:05:25では無く、05:25:00　と表示したいのです。
　　　　　　　（分：秒：00）の形式で表示したい。
　　　　　先頭の時間部分(00:)は不要です。

　　　　　例えば、
　　　　　00:16:40　ーーー＞　16:40:00

　　　２）1時間を超える場合は、
　　　　　01:08:05では、01:08:05:00と表示したいのです。
　　　　　　　（時間：分：秒）の形式に：00を追加した形式

　　　　　時間が２４時間を超えることはありません。

　　　-----　１）、２）共に全て秒の右に：00は必ず追加した形式　------

回答No.1

Higurashi777

D3以降の計算式は普通に＝C3+D2で問題ありません。
セルの書式設定をユーザー定義にして
[>0.041666]h:mm:ss":00";mm:ss":00"
と記載すればＯＫです。
但し、セルＣ列の記載がmm:ssではなく、hh:mm:ssである必要があります。
なので、C列の数値をhh:mm:ssになるように別途変換する必要があります。

以上、ご参考まで。

お礼

>Higurashi777さん

>セルＣ列の記載がmm:ssではなく、hh:mm:ssである必要があります。

C列は、以下の式から抽出された文字列で
　　=MID(A2,FIND("(",A2)+1,((FIND(")",A2))-(FIND("(",A2))-1))

これをhh:mm:ssに変換するために下記式に変更
　="00:"&MID(A2,FIND("(",A2)+1,((FIND(")",A2))-(FIND("(",A2))-1))

D列の計算式を
　D2＝00:00:00
　D3＝ｃ2
　D4=ｃ3＋D3
　　D以下にD4をコピペ

　D列の書式を以下に変更
[>0.041666]h:mm:ss":00";mm:ss":00"

これで上手く処理されました。