すべての時間帯の所要時間を隣列にだすChatGPTと一緒に作ったマクロ
この説明は、ChatGPTで作成しています。
このVBAマクロは、選択されたセルに書かれている時間帯の情報を解析し、その所要時間(例:何時間かかったか)を計算して、隣のセルに表示するものです。具体的には、例えば「09:00~12:00」といった形式で書かれている時間帯を認識して、所要時間を計算します。
プロシージャの仕組み
正規表現を使って時間帯を抽出
このプログラムでは、正規表現を使って「09:00~12:00」のような時間帯を含むテキストを解析します。正規表現はテキスト内の特定のパターン(ここでは時間帯の形式)を探すために使われます。`Pattern = "\b([01]?[0-9]|2[0-3]):[0-5][0-9]~((24:00)|([01]?[0-9]|2[0-3]):[0-5][0-9])\b"`
これは「時間:分~時間:分」の形式にマッチするパターンを定義しています。
時間帯の開始時間と終了時間を分割して処理
正規表現でマッチした時間帯(例:「09:00~12:00」)を、開始時間と終了時間に分けて、それぞれを`startTime`と`endTime`に格納します。24:00を特別に扱う
終了時間が「24:00」の場合、Excelの時間計算に合わせて「00:00」+1日分として扱い、24時間を表現しています。所要時間の計算
`duration = endTime - startTime`で、所要時間を計算します。もし終了時間が開始時間よりも早い場合は、24時間を経過したと見なして計算が修正されます。隣の列に所要時間を表示
計算された所要時間は、対象セルの隣のセルに「時間.分」という形式でまとめて表示されます(例:「3.00」=3時間)。
まとめ
このマクロは、複数の時間帯を含むセルでも、そのすべての時間帯の所要時間を隣の列に表示することができます。たとえば、1つのセルに「09:00~12:00, 14:00~18:00」と書かれている場合、隣に「3.00, 4.00」と表示されます。
Sub すべての時間帯の所要時間を隣列にだすChatGPTと一緒につくったやつ()
Application.ScreenUpdating = False
Dim reg As Object
Set reg = CreateObject("VBScript.RegExp") 'オブジェクト作成
Dim myRng As Range
Dim txt As String
Dim matches, match As Object
Dim startTime, endTime As Date
Dim duration As Double
Dim totalDuration As String ' 所要時間をまとめて格納する変数
' 正規表現パターンの設定
With reg
.Pattern = "\b([01]?[0-9]|2[0-3]):[0-5][0-9]~((24:00)|([01]?[0-9]|2[0-3]):[0-5][0-9])\b"
.IgnoreCase = True
.Global = True
End With
On Error Resume Next
' セル範囲のループ処理
For Each myRng In Selection
txt = myRng.Value
totalDuration = "" ' 初期化
If reg.Test(txt) Then
Set matches = reg.Execute(txt)
' マッチした場合
For Each match In matches
' 時間帯の解析
Dim times() As String
times = Split(match.Value, "~")
' 開始時間と終了時間を変数に格納
startTime = TimeValue(times(0))
' 終了時間が「24:00」の場合の処理
If times(1) = "24:00" Then
endTime = TimeValue("00:00") + 1 ' 24時間を表す
Else
endTime = TimeValue(times(1))
End If
' 所要時間を計算
duration = endTime - startTime
' 所要時間が負の場合、24時間経過とみなす
If duration < 0 Then
duration = duration + 1 ' 1は24時間を表す
End If
' 所要時間をフォーマットして格納
If totalDuration = "" Then
totalDuration = Format(duration * 24, "0.00")
Else
totalDuration = totalDuration & ", " & Format(duration * 24, "0.00")
End If
Next match
' 所要時間を隣列に一括で出力する
myRng.Offset(0, 1).Value = totalDuration
End If
Next myRng
Application.ScreenUpdating = True
End Sub
キーワード
#excel #できること #vba #時間帯 #所要時間 #正規表現 #VBScript #時間計算 #24時間処理 #自動化 #セル範囲 #時間解析 #マクロ #プログラミング初心者 #ビジネス効率化 #データ処理 #日付と時刻 #エクセル自動化 #範囲操作 #フォーマット
英語訳
Calculate and Output Time Duration for All Time Slots to Adjacent Cells
This explanation is created using ChatGPT.
This VBA macro processes the time range written in the selected cells, calculates the required time (e.g., how many hours it took), and outputs it in the adjacent cells. Specifically, it recognizes time ranges like "09:00~12:00" and calculates the duration.
How the Procedure Works
Extract Time Ranges Using Regular Expressions
This program uses regular expressions to parse text containing time ranges like "09:00~12:00". Regular expressions are used to find specific patterns in the text, in this case, the time range format.`Pattern = "\b([01]?[0-9]|2[0-3]):[0-5][0-9]~((24:00)|([01]?[0-9]|2[0-3]):[0-5][0-9])\b"`
This pattern matches the format "hour:minute~hour:minute".
Split the Time Ranges into Start and End Times
Once a time range (e.g., "09:00~12:00") is matched, it is split into start time and end time, which are stored in `startTime` and `endTime`, respectively.Handling 24:00 Specifically
If the end time is "24:00", it is treated as "00:00" + 1 day to represent 24 hours, aligning with Excel's time calculations.Calculate Duration
The duration is calculated using `duration = endTime - startTime`. If the end time is earlier than the start time, it assumes 24 hours have passed and adjusts the calculation accordingly.Display Duration in the Adjacent Column
The calculated duration is displayed in the adjacent column as "hours.minutes" (e.g., "3.00" = 3 hours).
Summary
This macro can handle multiple time slots in a single cell and display all their respective durations in the adjacent column. For example, if a cell contains "09:00~12:00, 14:00~18:00", it will output "3.00, 4.00" in the next cell.
Keywords
#excel #whatispossible #vba #timerange #duration #regex #VBScript #timecalculation #24hourhandling #automation #cellrange #timeanalysis #macro #beginnerprogramming #businessefficiency #dataprocessing #dateandtime #excelautomation #rangeoperations #formatting
この記事が気に入ったらサポートをしてみませんか?