☰

タイムストリングを秒に変換する方法

tag-icon • tag-icon microsoft-excel
タイムストリングを秒に変換する方法

約 5000 行の CSV ファイルを Excel にインポートしましたが、問題は、次のような複数の時間文字列があり1h1m1s、それらを秒に変換する必要があることです。

以下にいくつか例を挙げます。

列Aのデータ

0m11s         
2m32s        
3m10s        
1h2m35s

列Bの結果

11 seconds
152 seconds  
190 seconds    
3755 seconds

セルの書式設定を試みましたが、奇妙な結果になってしまいました。

それは可能ですか? もし可能であれば、時間文字列を秒数に変換するにはどうすればよいでしょうか?

答え1

これは VBA を使用すると比較的簡単です。

1 つの方法は、正規表現を使用して文字列を解析し、各セクションに適切な変換を掛け合わせることです。

このユーザー定義関数 (UDF) を入力するには、alt-F11Visual Basic エディターを開きます。プロジェクトがプロジェクト エクスプローラー ウィンドウで強調表示されていることを確認します。次に、上部のメニューからInsert/Module以下のコードを選択し、開いたウィンドウに貼り付けます。

このユーザー定義関数（UDF）を使用するには、次のような数式を入力します。

=convSeconds(A1)

あるセルで。

これは、以下に示すように、秒数のみを返します。単語を追加する場合はSeconds、数式を文字列と連結するか、カスタム数値書式を使用します (これにより、結果の数値の品質が保持されます)。

Option Explicit
Function convSeconds(s As String) As Long
    Dim RE As Object, MC As Object
    Dim SEC As Long
Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .ignorecase = True
    .Pattern = "(?:(\d+)h)?(?:(\d+)m)?(?:(\d+)s)?"
    If .test(s) = True Then
        Set MC = .Execute(s)
        With MC(0)
            SEC = SEC + .submatches(0) * 3600 'hours
            SEC = SEC + .submatches(1) * 60   'minutes
            SEC = SEC + .submatches(2)        'seconds
        End With
    End If
End With
convSeconds = SEC
End Function

ここに画像の説明を入力してください

正規表現の説明はこちら

h/m/s を抽出

(?:(\d+)h)?(?:(\d+)m)?(?:(\d+)s)?

作成者正規表現バディ

答え2

これを行う簡単な方法を次に示します。 すべてを 1 つの数式で実行することもできますが、繰り返しを避けるためにいくつかのヘルパー列を使用しました。

ここに画像の説明を入力してください

あなたの例では、少なくとも分と秒のプレースホルダーが常に存在しますが、時間は時間がある場合のみ含まれます。最初のステップでは、「h」を探します。B1 の式:

=FIND("h",A1)

「h」がない場合、エラーを返します。それ以外の場合は、文字列内の h の位置を返します (時間の値が 9 を超える可能性があると想定し、そうでない場合は左側の文字を取得できます)。

列 C は常に同じになる部分を剥がします。C1 の式:

=IF(ISERROR(B1),A1,MID(A1,B1+1,LEN(A1)))

「h」が見つからない場合は元の文字列を使用し、見つかった場合は h の後のすべてを取得します。

列 D には「m」があります。D1 の数式は B1 の数式と似ています。

=FIND("m",C1)

列 E では、ピースを使用して秒を計算します。E1 の式:

=IF(ISERROR(B1),0,3600*LEFT(A1,B1-1))+LEFT(C1,D1-1)*60+MID(C1,D1+1,LEN(C1)-D1-1)

「h」が見つからない場合、時間の寄与はゼロになります。そうでない場合は、「h」の左側で見つかった値の 3600 倍になります。分の寄与は、「m」の左側で見つかった値の 60 倍になります。秒の寄与は、「m」の後、最後の文字 (「s」) の前にある数値です。

列 B:D を非表示にすることができます。すべてを 1 つの数式にまとめたい場合は、ヘルパー セルへの参照を関連するヘルパー セルの数式に置き換えるだけです。

注意: この数式は、常に分と秒の値を含む質問のデータの特性を扱います。分や秒が必ずしも含まれていない状況では、より複雑な数式が必要になります。

答え3

入力データは列 A の行 1 にあると想定します。

次の数式を試してみてください (コピーして B1 に貼り付け、必要に応じて下にドラッグします)。

=(IF(ISERROR(FIND("h",A1)),0,INT(MID(A1,1,FIND("h",A1)-1)))*3600)+(INT(IF(ISERROR(FIND("h",A1)),LEFT(A1,FIND("m",A1)-1),MID(A1,IF(ISERROR(FIND("h",A1)),0,FIND("h",A1)+1),FIND("m",A1)-FIND("h",A1)-1)))*60)+INT(MID(A1,FIND("m",A1)+1,FIND("s",A1)-FIND("m",A1)-1))

答え4

この質問には、質問の問題に対処する回答が既にあり、分と秒が常に含まれるという単純化が含まれています。必要な時間コンポーネントのみが含まれているため、1 つ以上のコンポーネントが欠落している可能性があるという、同様だがより一般的な問題でこの質問にたどり着く人もいるかもしれません。

Ron Rosenfeld のすばらしい回答で、すでにこの問題は解決されています。VBA や正規表現に詳しくなく、より簡単に適応および維持できる数式ベースのソリューションを好む読者にとっては、他の現在の回答では一般的なケースは解決されません。この数式ベースのソリューションは、この質問の限定されたケースに必要な範囲よりも複雑であり、他の回答と組み合わせると両方のソリューションが使いにくくなるため、別の回答として投稿します。

ここに画像の説明を入力してください

このソリューションでも、数式の繰り返しを避けるためにヘルパー列をいくつか使用しますが、アプローチが少し異なります。列 B から D はそれぞれ異なる時間構成要素の文字を探します。最初の行の数式は次のようになります。

B1:  =FIND("h",A1)
C1:  =FIND("m",A1)
D1:  =FIND("s",A1)

文字が存在する場合は、元の文字列内のその文字の位置を返します。存在しない場合はエラーを返します。"s" が存在する場合は常に最後の文字になりますが、その文字位置を見つけることで秒の値の抽出が簡単になり、FIND はそれが存在するかどうかを識別するという 2 つの目的を果たします。

列 E は、各時間コンポーネント値を抽出し、秒の変換係数を乗算して合計します。列 B から D にあるマーカーの位置に基づいて、MID を使用して値を抽出します。

複雑なのは、分と秒の場合、先行する時間コンポーネントの一部またはすべてが存在しない可能性があることです。数式では、先行するコンポーネントのどれが存在するかに基づいて、現在のコンポーネントが文字列のどこから始まるかを判断する必要があります。E1 の数式は次のとおりです。

=3600*IFERROR(LEFT(A1,B1-1),0)+
 60*IFERROR(MID(A1,1+IFERROR(B1,0),C1-1-IFERROR(B1,0)),0)+
 IFERROR(MID(A1,1+MAX(IFERROR(B1,0),IFERROR(C1,0)),D1-1-MAX(IFERROR(B1,0),IFERROR(C1,0))),0)

読みやすくするために、数式に時間要素の間に改行を追加しました。数式をコピーして貼り付ける場合は、改行を削除してください。

Hours は非常に簡単です。"h" の前の左側の文字 ("h" がない場合は 0) を取得し、それを 3600 倍します。

分の値の前には時間のみを指定できます。分の値が存在する場合は、時間コンポーネントがあるかどうかに基づいて MID の開始点と長さが計算されます。

秒の値の位置は、存在する場合、時間と分のコンポーネントのいずれかまたは両方が存在するかどうかによって決まります。「m」の位置は、存在する場合、文字列内で常に「h」の位置よりも後になります。どちらかが存在しない場合、数式ではそのコンポーネントに 0 の値が使用されます。次に、MAX 関数は、元の文字列内で秒の値の前の最も遠い位置を提供し、文字列の長さのうち前の時間コンポーネントが占める割合を決定します。

ヘルパー列は非表示にすることができます。列 E の数式に統合することもできますが、それらの値が何回使用されているかを考慮すると、結果として得られる数式は非常に長くなり、維持が困難になります。

関連情報