[[20230327133835]]　『分：秒の加算を行いたい』（まさとし）　ページの最後に飛ぶ

『分：秒の加算を行いたい』（まさとし）

以下のような場合で分：秒の加算を行いたいのですが
単純に[D:12]に=sum(D1:D11)としても求められません。
（h:mm:ss形式でないmm:ssの場合が対象）

どんな計算式が必要ですか、教えてください。

     |[D] 
 [1] |3:12
 [2] |5:22
 [3] |6:02
 [4] |4:34
 [5] |6:31
 [6] |6:02
 [7] |4:56
 [8] |5:18
 [9] |8:14
 [10]|8:03
 [11]|9:59
 -----------------
 [12] =sum(D1:D11)

＜使用 Excel：Excel2021、使用 OS：Windows10 ＞

 D12のセルの表示形式を
 [mm]:ss
 としてみてくれ・
（ねむねむ） 2023/03/27(月) 13:46:12

すいません。
前提条件に不備がありました。

sumの結果が1時間を超える場合は、[hh]:mm:ssで表示します。
（まさとし） 2023/03/27(月) 14:02:36

 どういう表示になるのをのぞんでいるのだろうか？
（ねむねむ） 2023/03/27(月) 14:04:25

仮セルを利用して計算すれば以下のようになりますが
仮セルを使用せずに計算できる方法もあるかと思いました。

     |[D] |[E]         
 [1] |3:12|=D1/60      
 [2] |5:22|=D2/60      
 [3] |6:02|=D3/60      
 [4] |4:34|=D4/60      
 [5] |6:31|=D5/60      
 [6] |6:02|=D6/60      
 [7] |4:56|=D7/60      
 [8] |5:18|=D8/60      
 [9] |8:14|=D9/60      
 [10]|8:03|=D10/60     
 [11]|9:59|=D11/60     
 [12]|    |=SUM(E1:E11) ----->1:08:13　（書式設定 [h]:mm:ss)

（まさとし） 2023/03/27(月) 14:35:54

 D列が3分12秒ではなく3時間12分と入力されているということか。
 EXCELではXX:YYはXX時間:YY分と入力されたものとみなしている。

 全部足してから60で割ってはどうだろうか。
 =SUM(D1:D11)/60
（ねむねむ） 2023/03/27(月) 14:42:44

＞D列が3分12秒ではなく3時間12分と入力されているということか。

D列は、3時間12分ではなく3分12秒とみなします。

ありがとうございます。
　=SUM(D1:D11)/60　で仮セルを使用せずに計算されました。

できれば、書式設定（[h]:mm:ss）をしなくて希望の数値が算出されれば良いのですが
やはりむりな相談ですよね。
（無理なら書式設定で対処するので手間はかかりますが算出上は問題ないです。）

（まさとし） 2023/03/27(月) 15:04:39

＞できれば、書式設定（[h]:mm:ss）をしなくて希望の数値が算出されれば良いのですが
0:3:12 と時分秒で入力すればいいのでは。
=sum(D1:D11) で求められますけどね。

（きく） 2023/03/27(月) 17:33:32

D列は既に記入済み（他箇所からコピペ）なので
正規の方式（h:mm:ss)で記入するとなると、手打ち入力となります。
今回は、個数が少ないのでなんとかなるかも知れませんが
100個を超える場合もあるので手打ち入力は正直面倒です。

無理な相談内容なのでその後回答が付きません。
諦めました。

ありがとう、お世話になりました。

（まさとし） 2023/03/28(火) 05:25:19

 Vbaなら一発変換できますけどね
（稲葉） 2023/03/28(火) 06:39:31

＞Vbaなら一発変換できますけどね

興味があります。
VBAでオリジナル関数を作成すると言うことでしょうか　？

（まさとし） 2023/03/28(火) 06:46:29

 関数(出力)ではなく、元のデータをコピペする時に
 ＞0:3:12 と時分秒で入力
 されるようにした方が良いのでは？

 ＞（まさとし） 2023/03/28(火) 05:25:19
 の発言では、データ型と表示はh:mm:ssでも良さそうですし
（稲葉） 2023/03/28(火) 07:07:59

＞元のデータをコピペする時に
＞0:3:12 と時分秒で入力
＞されるようにした方が良いのでは？

もちろん。理想は、DATA形式がh:mm:ssが良いのは理解しています。

しかし、コピペ前のDATAがmm:ss形式なのです。
（DATAに１時間以上が無い）

コピペ前にテキストエディターなどで「0:」を元DATAの前配置するようにすれば
形態は正規の「h:mm:ss」になりますが
　変更なしで計算式(=SUM(D1:D11)/60)のほうが簡単に処理できます。

＞（まさとし） 2023/03/28(火) 05:25:19
＞の発言では、データ型と表示はh:mm:ssでも良さそうですし

発言され意味が汲み取れません。

D列のDATAは、DATA型は「mm:ss」で表示として正規の「h:mm:ss」ではありません。
（まさとし） 2023/03/28(火) 08:24:08

 ＞D列のDATAは、DATA型は「mm:ss」で表示として正規の「h:mm:ss」ではありません。
 だからそれをh:mmからmm:ssにVBAで変換しましょうって話です。

 ＞発言され意味が汲み取れません。
 の意味は
 ＞100個を超える場合もあるので手打ち入力は正直面倒です。
 その発言を受け、

 「h:mmをmm:ss形式に変更することに異論はない」
  　　→の発言では、データ型と表示はh:mm:ssでも良さそうですし
 の意です。

 VBA実行前　E列は列の書式設定をわかりやすく表示したもので、VBAに含まれません。
     |[D] |[E]   
 [1] |3:12|h:mm;@
 [2] |5:22|      
 [3] |6:02|      
 [4] |4:34|      
 [5] |6:31|      
 [6] |6:02|      
 [7] |4:56|      
 [8] |5:18|      
 [9] |8:14|      
 [10]|8:03|      
 [11]|9:59|      
 [12]|    |h:mm;@

 実行後
     |[D]    |[E]   
 [1] |03:12  |mm:ss 
 [2] |05:22  |      
 [3] |06:02  |      
 [4] |04:34  |      
 [5] |06:31  |      
 [6] |06:02  |      
 [7] |04:56  |      
 [8] |05:18  |      
 [9] |08:14  |      
 [10]|08:03  |      
 [11]|09:59  |      
 [12]|1:08:13|[h]:mm:ss

 実行前　1時間に満たない場合
     |[D] |[E]                
 [1] |3:12|h:mm;@             
 [2] |5:22|                   
 [3] |6:02|                   
 [4] |4:34|                   
 [5] |6:31|                   
 [6] |6:02|                   
 [7] |4:56|                   
 [8] |5:18|                   
 [9] |8:14|                   
 [10]|8:03|                   
 [11]|0:00|1時間に満たない場合
 [12]|    |h:mm;@

 実行後
     |[D]  |[E]                
 [1] |03:12|mm:ss              
 [2] |05:22|                   
 [3] |06:02|                   
 [4] |04:34|                   
 [5] |06:31|                   
 [6] |06:02|                   
 [7] |04:56|                   
 [8] |05:18|                   
 [9] |08:14|                   
 [10]|08:03|                   
 [11]|00:00|1時間に満たない場合
 [12]|58:14|mm:ss

 １）以下のコードを標準モジュールに貼り付けてください
 参考
http://officetanaka.net/excel/vba/beginner/10.htm
 ２）コード内の注釈部分を、実際のシート名に変更してください
 ３）D列にコピペした後、実行してください。
 ４）D列の見た目は変わりませんが、内部データは時分→分秒に変換されてます。
 　　そのため、最終行のSUMでは、60で除算しなくとも計算ができるようになっています。
 　　また、1時間に満たない場合は、mm:ssの書式を保ったまた表示されます。

 コード
    Sub hr2min()
        Dim ws As Worksheet
        Dim adr As String
        Set ws = Sheets("Sheet1") '<-ここを実際のシート名に変更してください
        With ws.Range("D1", ws.Range("D1").End(xlDown))
            If Not .NumberFormatLocal Like "*ss" Then
                .NumberFormatLocal = "mm:ss"
                .Value = Evaluate(.Address & "/60")
            End If
            adr = .Address(0, 0)
        End With
        With ws.Range("D1").End(xlDown).Offset(1)
            .Formula = "=SUM(" & adr & ")"
            If .Value >= TimeValue("1:00") Then
                .NumberFormatLocal = "[h]:mm:ss"
            Else
                .NumberFormatLocal = "mm:ss"
            End If
        End With
        MsgBox "変換しました"
    End Sub

（稲葉） 2023/03/28(火) 08:43:01

稲葉さん、ありがとうございます。
　発言された意味が汲み取れました。

いただいたコードを改良していますが、
計算すべき列をクリックして選択したいのですが教えてください。

VBAは呼び出すと"計算する列を指定して下さい。"と表示して
マウスで処理列をクリックして選択するようなイメージです。
（この場合、途中でCANCELする場合のVBAの処理が問題になるかと思います。
　　InputBoxのように「Cancel」がクリックできない）
　　
そこまでするのはするのはやり過ぎ感はありますが、選択肢の一つとして知りたいと思います。

 Option Explicit
 Sub test()

    Dim Cor As String

    Cor = InputBox("計算する列を指定して下さい。")
    If Cor = "" Then
        MsgBox "処理をキャンセルします。"
        Exit Sub
    Else
        Call Sum_mmss(Cor)
    End If

 End Sub

'「mm:ss」を「h:mm:ss」に変換して合計時間を計算

 Function Sum_mmss(Cor As String)
    Dim ws As Worksheet
    Dim adr As String
    Dim Fcor As String

    Set ws = ActiveSheet '<-ここを実際のシート名に変更してください

    Fcor = Cor & "1"

    'D列の見た目は変わりませんが、内部データは時分→分秒に変換されてます。
    With ws.Range(Fcor, ws.Range(Fcor).End(xlDown))
        If Not .NumberFormatLocal Like "*ss" Then
            .NumberFormatLocal = "mm:ss"
            .Value = Evaluate(.Address & "/60")
        End If
        adr = .Address(0, 0)
    End With

    'DATAの最下行に時間の合計を計算して書き出す
    With ws.Range(Fcor).End(xlDown).Offset(1)
        .Formula = "=SUM(" & adr & ")"
        If .Value >= TimeValue("1:00") Then      '合計が1時間を超える場合
            .NumberFormatLocal = "[h]:mm:ss"
        Else
            .NumberFormatLocal = "mm:ss"         '合計が1時間以下の場合
        End If
    End With

    MsgBox "合計時間を計算しました。"

 End Function

（まさとし） 2023/03/28(火) 11:00:07

 まず希望通りなのかどうか、応えてもらえると嬉しいかな。

 改良については、ヒントは出しますので自分で最後まで頑張ってみてくださいね。
 引数は文字じゃなくてRangeオブジェクトを渡したほうが扱いやすいと思いますよ
 キーワード
 Application.Inputbox   'Typeに8を指定するとRangeオブジェクトを取得。キャンセル時はブール型
 Intersect(Range,Range) '想定した範囲に選択したセルがあるか
 Range.Columns.Count    '複数列選択された場合に、1列だけ指定させるなど条件分岐
 Range.EntireColumn　   'Rangeオブジェクトから1列取得
 Range.Parent           'Rangeオブジェクトから親であるシートオブジェクトを取得

http://officetanaka.net/excel/vba/tips/tips37.htm
https://dailyrecords.blog/archives/18903
https://www.relief.jp/docs/018364.html
https://excel-ubara.com/excelvba1/EXCELVBA402.html

 列番号を文字に変換するのはちょっと面倒
https://www.tipsfound.com/vba/09003

（稲葉） 2023/03/28(火) 11:23:09

稲葉さんのアドバイスと違うと思いますが、
ネットの力を借りて以下のコードを考えました。

一応、エラー無く処理できました。

素人の考えたこーどなので不備があれば指摘して下さい。

 Option Explicit
  Sub test()

    Dim adrs As String
    Dim Cor As String
    Dim cellRange As Range

    On Error Resume Next
    Set cellRange = Application.InputBox("処理列をクリックして選択してください", "処理列の指定", Type:=8)

    If cellRange Is Nothing Then
        MsgBox "処理をキャンセルします。"
        Exit Sub
    Else
        cellRange.Select
    End If

    adrs = ActiveCell.Address
    Cor = Split(adrs, "$")(1)

    Call Sum_mmss(Cor)

 End Sub
（まさとし） 2023/03/28(火) 12:26:56

 できているならいいんじゃないですか？
（稲葉） 2023/03/28(火) 13:45:19

稲葉さん、お世話になりました。

追加のアドバイスもなさそうなのでこの辺で終了とさせていただきます。

（まさとし） 2023/03/28(火) 15:30:36

コメント返信：

[ 一覧(最新更新順) ]

[[20230327133835]] 『分：秒の加算を行いたい』（まさとし） ページの最後に飛ぶ

[[20230327133835]]　『分：秒の加算を行いたい』（まさとし）　ページの最後に飛ぶ